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;

PL SQL Parameters (IN, OUT, IN OUT)

Three types assigning values to variables in PL/SQL

1.       By assignment expression à variable := value;

Example:

ord_id := 291;

           2.       Assign value to variable by fetching the database values into it

Example:

                DECLARE

amount NUMBER(9,2);

ord_id NUMBER(6) := 291;

BEGIN

SELECT bill_amount INTO amount FROM Orders

WHERE order_id = ord_id;

END;

          3.       Assign value to variable by passing it as an OUT, IN or IN OUT parameter to a sub program.

a.       OUT parameter returns the value from PL SQL function or procedure.

Example:

CREATE OR REPLACE PROCEDURE P_GET_SAL (P_EMPID NUMBER, P_SAL OUT NUMBER)

IS

BEGIN

SELECT SALARY INTO P_SAL

FROM EMPLOYEE

WHERE EMPLOYEE_ID=P_EMPID;

END;

Usage:

SQL> VAR G_SAL NUMBER;

SQL> EXEC P_GET_SAL(100,:G_SAL);

PL/SQL procedure successfully completed.

SQL> PRINT G_SAL;

 G_SAL

----------

      2300

b.      IN parameter passed the value in PL SQL function or procedure.

Example:

CREATE OR REPLACE PROCEDURE empName (empid IN NUMBER, empName OUT VARCHAR2(100))

                IS

                BEGIN

                                SELECT firstName INTO empName

                                FROM emp WHERE empID = empid;

                END;

               

c.       IN OUT parameter both pass in the value in function or procedure and can return back after modify the value with in the procedure.

Example:

CREATE OR REPLACE PROCEDURE empNameAndSal (empid IN NUMBER, empName OUT VARCHAR2(100), empSal IN OUT NUMBER)

                IS

                BEGIN

                                SELECT firstName INTO empName

                                FROM emp WHERE empID = empid;

                                empSal := empSal *2;

                END;