Can an SQL procedure return a table?

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_ntis    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                         CLERKSQL> 

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

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

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

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;  /