Sql*Plus User’s Guide and Reference

Example 5–15
Using REFCURSOR Variables in Stored Procedures
A REFCURSOR bind variable is passed as a parameter to a procedure. The parameter 
has a REF CURSOR type. First, define the type.
CREATE OR REPLACE PACKAGE EmpPack AS
TYPE EmpInfoTyp IS REF CURSOR;
PROCEDURE EmpInfoRpt (emp_cv IN OUT EmpInfoTyp);
END EmpPack;
/
Next, create the stored procedure containing an OPEN... FOR SELECT statement.
PL/SQL procedure successfully completed.
EMPLOYEE_ID SALARY
----------- ----------
145 14000
146 13500
147 12000
148 11000
149 10500
Package created.

---

Using REFCURSOR Bind Variables
5-24
SQL*Plus User's Guide and Reference
CREATE OR REPLACE PACKAGE BODY EmpPack AS
PROCEDURE EmpInfoRpt (emp_cv IN OUT EmpInfoTyp) AS
BEGIN
OPEN emp_cv FOR SELECT EMPLOYEE_ID, SALARY
FROM EMP_DETAILS_VIEW
WHERE JOB_ID='SA_MAN' ;
END;
END;
/
Execute the procedure with a SQL*Plus bind variable as the parameter.
VARIABLE cv REFCURSOR
EXECUTE EmpPack.EmpInfoRpt(:cv)
Now print the bind variable.
PRINT cv
The procedure can be executed multiple times using the same or a different 
REFCURSOR bind variable.
VARIABLE pcv REFCURSOR
EXECUTE EmpInfo_rpt(:pcv)
PRINT pcv
Example 5–16
Using REFCURSOR Variables in Stored Functions
Create a stored function containing an OPEN... FOR SELECT statement:
CREATE OR REPLACE FUNCTION EmpInfo_fn RETURN -
cv_types.EmpInfo IS
resultset cv_types.EmpInfoTyp;
BEGIN
Procedure created.
PL/SQL procedure successfully completed.
EMPLOYEE_ID SALARY
----------- ----------
145 14000
146 13500
147 12000
148 11000
149 10500
PL/SQL procedure successfully completed.
EMPLOYEE_ID SALARY
----------- ----------
145 14000
146 13500
147 12000
148 11000
149 10500

---

Fetching Iterative Results from a SELECT inside a PL/SQL Block
Using Scripts in SQL*Plus
5-25
OPEN resultset FOR SELECT EMPLOYEE_ID, SALARY
FROM EMP_DETAILS_VIEW
WHERE JOB_ID='SA_MAN';
RETURN(resultset);
END;
/
Execute the function.
VARIABLE rc REFCURSOR
EXECUTE :rc := EmpInfo_fn
Now print the bind variable.
PRINT rc
The function can be executed multiple times using the same or a different 
REFCURSOR bind variable.
EXECUTE :rc := EmpInfo_fn