OUT Mode:

The OUT parameter mode is used to return values to the program. Relatively it is a write-only variable. A particular value can be assigned to this variable. Let consider a condition where the employee name and the salary are to be displayed. The subsequent example displays the usage of the OUT parameter.

CREATE OR REPLACE PROCEDURE DISP_SAL (ENO IN NUMBER, SALARY OUT NUMBER) AS #to create or replace a procedure #

MNAME VARCHAR2(20); # declaration of variable #

BEGIN

SELECT SAL, ENAME INTO SALARY,MNAME FROM EMP WHERE EMPNO=ENO;

DBMS_OUTPUT.PUT_LINE('THE EMPLOYEE NAME IS '||MNAME);

END;

Execution cannot be done like the execution of the previous example. Because there is an OUT parameter, a placeholder must be given for the out variable value to be assigned. The two techniques in execution of this program at SQL prompt are:

First one is to declare a session variable or local variable using the keyword VAR as display below:

VAR <variablename> <datatype>

Another, execute the procedure with the arguments specified and replace the out parameter with the variable defined.

EXEC <procedurename>(value,:<variablename>);

Because the variable is a session variable, session variables can be known as using colon delimiter

(Refer PL/SQL Delimiters)

At last, print the value placed on the variable using PRINT statement

PRINT <variablename>

The example and the output is display below:

VAR n NUMBER

EXEC disp_sal(7902,:n);

the employee name is FORD

PL/SQL procedure successfully finished.

PRINT n

N

---------

3000

In the other words, this procedure can be executed inside a PL/SQL block as shown below:

DECLARE

MSAL NUMBER;

MENO NUMBER:=&N;

BEGIN

DISP_SAL(MENO,MSAL);

DBMS_OUTPUT.PUT_LINE('THE SALARY IS '||MSAL);

END;

When this block gets executed, it shows

Enter value for n: 7902

old       3: Meno number:=&n;

new     3: Meno number:=7902;

the employee name is FORD

the salary is 3000

PL/SQL procedure successfully completed.