How to use outputs from a function?-CodePudding

I have a function with three outputs and one input. But how do I use the value that comes from the outputs? I need to check the values coming out of the outputs. Can someone explain to me how I do it?

CodePudding user response：

As Koen has already said, you don't want to use a function in this case - use a procedure instead. Here's an example: procedure accepts empno and returns their name and salary via OUT parameters.

SQL> create or replace procedure p_test (par_empno in number, par_ename out varchar2, par_sal out number)
  2  is
  3  begin
  4    select ename, sal
  5      into par_ename, par_sal
  6      from emp
  7      where empno = par_empno;
  8  end;
  9  /

Procedure created.

This is one employee; use its empno when calling the procedure:

SQL> select empno, ename, sal from emp where rownum = 1;

     EMPNO ENAME             SAL
---------- ---------- ----------
      7369 SMITH             920

SQL> set serveroutput on

To call the procedure, declare local variables (because you have to put the result into something):

SQL> declare
  2    l_ename emp.ename%type;
  3    l_sal   emp.sal%type;
  4  begin
  5    p_test(7369, l_ename, l_sal);
  6    dbms_output.put_line(l_ename ||' earns ' || l_sal);
  7  end;
  8  /
SMITH earns 920

PL/SQL procedure successfully completed.

SQL>

If it must be a function, code changes slightly as function has to return a value anyway (not just via OUT parameters); I chose to return deptno value:

SQL> create or replace function f_test (par_empno in number, par_ename out varchar2, par_sal out number)
  2    return varchar2
  3  is
  4    l_deptno emp.deptno%type;
  5  begin
  6    select ename, sal, deptno
  7      into par_ename, par_sal, l_deptno
  8      from emp
  9      where empno = par_empno;
 10
 11    return l_deptno;
 12  end;
 13  /

Function created.

SQL> declare
  2    l_ename  emp.ename%type;
  3    l_sal    emp.sal%type;
  4    l_deptno emp.deptno%type;
  5  begin
  6    l_deptno := f_test(7369, l_ename, l_sal);
  7    dbms_output.put_line(l_ename ||' earns ' || l_sal || ' and works in department ' || l_deptno);
  8  end;
  9  /
SMITH earns 920 and works in department 20

PL/SQL procedure successfully completed.

SQL>