Can an SQL procedure return a table?

Question

Can an Oracle SQL procedure return a table? I'm currently using a dbms_output to print out the outputs of two cursors which are in a loop, although this would look nicer if it was returning two columns instead. Would that be possible within a procedure?

How are you consuming the procedure? You can return a resultset from a Cursor through an OUT parameter of the SYS_REFCURSOR data type, but your client needs to support reading the result set. — N West, Dec 02 '12 at 20:36
http://asktom.oracle.com/pls/asktom/ASKTOM.download_file?p_file=6551171813078805685 — Lee, Dec 02 '12 at 20:36

score 14 · Accepted Answer · answered Dec 02 '12 at 21:05

A PL/SQL function can return a nested table. Provided we declare the nested table as a SQL type we can use it as the source of a query, using the the TABLE() function.

Here is a type, and a nested table built from it:

SQL> create or replace type emp_dets as object (
  2  empno number,
  3  ename varchar2(30),
  4  job varchar2(20));
  5  /

Type created.

SQL> create or replace type emp_dets_nt as table of emp_dets;
  2  /

Type created.

SQL>

Here is a function which returns that nested table ...

create or replace function get_emp_dets (p_dno in emp.deptno%type)
    return emp_dets_nt
is
    return_value emp_dets_nt;
begin
    select emp_dets(empno, ename, job)
    bulk collect into return_value
    from emp
    where deptno = p_dno;
    return return_value;
end;
/

... and this is how it works:

SQL> select * 
  2  from table(get_emp_dets(10))
  3  /

     EMPNO ENAME                          JOB
---------- ------------------------------ --------------------
      7782 CLARK                          MANAGER
      7839 KING                           PRESIDENT
      7934 MILLER                         CLERK

SQL>

SQL Types offer us a great deal of functionality, and allow us to build quite sophisticated APIs in PL/SQL. Find out more.

score 3 · Answer 2 · answered Apr 12 '16 at 08:09

3

I think that you can use Oracle Cursor for this (if your Oracle version supports it):

PROCEDURE myprocedure(
    mycursor OUT SYS_REFCURSOR )
AS
BEGIN
  OPEN mycursor FOR SELECT * FROM mytable;
END;
END;

answered Apr 12 '16 at 08:09

zygimantus

3,649
4
39
54

score 0 · Answer 3 · answered Dec 06 '12 at 15:14

This may also help:

DECLARE
  TYPE t_emptbl IS TABLE OF scott.emp%rowtype;
  v_emptbl t_emptbl; 
  ret_val  t_emptbl; 
  --
  Function getEmployeeList Return t_emptbl 
  IS
  BEGIN
    SELECT * bulk collect INTO v_emptbl FROM scott.emp;
    -- Print nested table of records:
    FOR i IN 1 .. v_emptbl.COUNT LOOP
      DBMS_OUTPUT.PUT_LINE (v_emptbl(i).empno);
    END LOOP;
    RETURN v_emptbl;
  END;
  --
  BEGIN
    ret_val:= getEmployeeList;
  END;
  /

Can an SQL procedure return a table?

3 Answers3

Linked