Bind Variables with PL/SQL

Static SQL
========
When you are using the static SQL with PL/SQL then the PL/SQL will automatically bind the variables in WHERE clause with the Values provided as parameter to PL/SQL block.

For Example

CREATE OR REPLACE PROCEDURE fetchEmpName(id IN NUMBER)

IS 

BEGIN

           SELECT empName FROM emp
           WHERE empId = id
END;


For the above PL/SQL block, the id parameter is bind with the WHERE clause empId variable. Oracle will use the same compiled code for the execution of the enclosed query in PL/SQL block. This will save the time required to parse the query again and again. Will execute the same query plan for each call. This will dramatically increase the performance of execution.



Dynamic SQL
==========
In case of Dynamic SQL we need to bind the variable explicitly with the WHERE clause column by USING.

In the below sample procedure we are doubling the salary by binding the procedure IN parameter
'p_empno ' with WHERE clause column 'empno' by USING.


Example:
            CREATE OR REPLACE PROCEDURE doubleSalary(p_empno IN NUMBER)
            AS
           BEGIN
                    execute immediate
                       'update emp set
                       sal = sal * 2
                       WHERE empno = :x' USING p_empno;
                     COMMIT;
           END;