Open SYS_REFCURSOR for select ... from : REFCURSOR « Cursor « Oracle PL/SQL Tutorial






SQL> CREATE TABLE emp (
  2    id         NUMBER PRIMARY KEY,
  3    fname VARCHAR2(50),
  4    lname  VARCHAR2(50)
  5  );

Table created.

SQL>
SQL> INSERT INTO emp (id, fname, lname)VALUES (1, 'A', 'B');

1 row created.

SQL> INSERT INTO emp (id, fname, lname)VALUES (2, 'C', 'D');

1 row created.

SQL> INSERT INTO emp (id, fname, lname)VALUES (3, 'Enn', 'F');

1 row created.

SQL> INSERT INTO emp (id, fname, lname)VALUES (4, 'G', 'H');

1 row created.

SQL> INSERT INTO emp (id, fname, lname)VALUES (5, 'G', 'Z');

1 row created.

SQL>
SQL> SET SERVEROUTPUT ON ESCAPE OFF
SQL>
SQL> CREATE OR REPLACE PROCEDURE emp_sel(cv_results IN OUT SYS_REFCURSOR) IS
  2  BEGIN
  3     OPEN cv_results FOR SELECT id, fname, lname FROM emp;
  4  END;
  5  /

Procedure created.

SQL>
SQL> COL fname FORMAT A12
SQL> VARIABLE x REFCURSOR
SQL> EXEC emp_sel(:x)

PL/SQL procedure successfully completed.

SQL> PRINT x

        ID FNAME        LNAME
---------- ------------ --------------------------------------------------
         1 A            B
         2 C            D
         3 Enn          F
         4 G            H
         5 G            Z

5 rows selected.

SQL>
SQL>
SQL> drop table emp;

Table dropped.








25.13.REFCURSOR
25.13.1.Define refcursor variable
25.13.2.Output value in a refcursor
25.13.3.The use of REF CURSOR
25.13.4.An example of using SYS_REFCURSOR for cursor variable processing
25.13.5.Cursor expressions as arguments to functions called from SQL
25.13.6.Subprograms returning resultsets by using SYS_REFCURSOR
25.13.7.Row type Reference cursor
25.13.8.Open SYS_REFCURSOR for select ... from
25.13.9.SYS_REFCURSOR type parameter