Using Output Parameters : Parameter OUT « Stored Procedure Function « Oracle PL / SQL






Using Output Parameters

    
SQL>
SQL> CREATE TABLE emp (
  2     empID INT NOT NULL PRIMARY KEY,
  3     Name      VARCHAR(50) NOT NULL);

Table created.

SQL> INSERT INTO emp (empID,Name) VALUES (1,'Tom');

1 row created.

SQL> INSERT INTO emp (empID,Name) VALUES (2,'Jack');

1 row created.

SQL> INSERT INTO emp (empID,Name) VALUES (3,'Mary');

1 row created.

SQL> INSERT INTO emp (empID,Name) VALUES (4,'Bill');

1 row created.

SQL> INSERT INTO emp (empID,Name) VALUES (5,'Cat');

1 row created.

SQL> INSERT INTO emp (empID,Name) VALUES (6,'Victor');

1 row created.

SQL>
SQL> CREATE OR REPLACE PROCEDURE GetempName(i_empID IN INT,o_empName OUT VARCHAR)
  2  AS
  3  BEGIN
  4     SELECT Name INTO o_empName FROM emp
  5     WHERE empID = i_empID;
  6  END;
  7  /

SP2-0804: Procedure created with compilation warnings

SQL> SET SERVEROUT ON
SQL> DECLARE
  2     empName VARCHAR(50);
  3  BEGIN
  4     GetempName(3, empName);
  5     dbms_output.put_line(empName);
  6  END;
  7  /
Mary

PL/SQL procedure successfully completed.

SQL>
SQL> drop table emp;

Table dropped.

SQL>

   
    
    
    
  








Related examples in the same category

1.Define 'out' parameters
2.Using out parameter
3.Use out parameter to get value out
4.Parameter Modes
5.Out with NOCOPY modifier.
6.Unhandled exceptions and OUT variables
7.behavior of unhandled exceptions and OUT variables.
8.Behavior of OUT variables and raised exceptions
9.This procedure takes a single OUT. Out parameter is assignable
10.Out parameter is assignable