Sql*Plus User’s Guide and Reference

Fetching Iterative Results from a SELECT inside a PL/SQL Block
SQL*Plus can iteratively fetch and format the results of a SELECT statement contained 
in a PL/SQL block or stored procedure. You do not need to define local REFCURSOR 
variables. 
Example 5–17
Creating a PL/SQL Procedure
Create a PL/SQL procedure P4 which calls two statements.
create procedure p4 as
c1 sys_refcursor;
c2 sys_refcursor;
begin
open c1 for SELECT * FROM DEBT;
dbms_sql.return_result(c1);
open c2 for SELECT * FROM EMP;
dbms_sql.return_result(c2);
end;
/
Procedure created.
Function created.
PL/SQL procedure successfully completed.
EMPLOYEE_ID SALARY
----------- ----------
145 14000
146 13500
147 12000
148 11000
149 10500
PL/SQL procedure successfully completed.

---

Fetching Iterative Results from a SELECT inside a PL/SQL Block
5-26
SQL*Plus User's Guide and Reference
Next, run the procedure to retrieve results iteratively from the SELECT statements in 
the procedure. 
exec p4
PL/SQL procedure successfully completed.
The results from the SELECT statements are displayed.
ResultSet #1
ResultSet #2
DEPTNO DNAME LOC
------ ---------- ---------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
4 rows selected
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ------ --------- ---- --------- ---- ---- --
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 05-APR-11 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 09-MAY-11 1100
14 rows selected

---