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;