How to return multiple values from a procedure (using OUT parameter)

Procedure:

1. CREATE OR REPLACE PROCEDURE p_get_emp_details (

      p_empno       NUMBER,

      p_ename    OUT   VARCHAR2,

      p_sal      OUT   NUMBER,

      p_deptno   OUT   NUMBER

   )

   IS

   BEGIN

      SELECT  e.ename, e.sal, d.deptno

        INTO p_ename, p_sal,p_deptno

        FROM emp e, dept d

       WHERE e.deptno = d.deptno AND empno = p_empno;

   EXCEPTION

      WHEN OTHERS

      THEN

            DBMS_OUTPUT.put_line (' Exception' || SQLCODE || 'Encountered');

   END; 

Execution:

1. DECLARE

      empno    NUMBER;

      ename    VARCHAR2 (50);

      sal      NUMBER;

      deptno   NUMBER;

   BEGIN

        empno :=7839;

        p_get_emp_details (empno, ename, sal, deptno);

      DBMS_OUTPUT.put_line (   'Employee '

                            || ename

                            ||' '

                            || 'Salary is'

                            || ' '

                            || sal

                            || ' '

                            || 'work in the dept'

                            || ' '

                            || deptno

                           );

   END;

Output:

Employee KING salary is 5000 work in the dept 10

Share: