SYS_REFCURSOR type parameter : REFCURSOR « Cursor « Oracle PL/SQL Tutorial

1. Oracle PL/SQL Tutorial
2. Cursor
3. REFCURSOR

SQL>
SQL>
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, 'E', '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_regexp_like (cv_emp IN OUT SYS_REFCURSOR)
  2  IS
  3  BEGIN
  4
  5     OPEN cv_emp FOR
  6     SELECT id, fname, lname
  7     FROM emp
  8     WHERE REGEXP_LIKE(lname, '^h', 'i');
  9
 10  EXCEPTION
 11     WHEN OTHERS
 12     THEN
 13        DBMS_OUTPUT.PUT_LINE(sqlerrm);
 14  END;
 15  /

Procedure created.

SQL>
SQL> COL fname FORMAT A20
SQL> COL lname FORMAT A20
SQL>
SQL> VARIABLE x REFCURSOR
SQL> EXEC emp_sel_regexp_like(:x)

PL/SQL procedure successfully completed.

SQL>
SQL> print x

no rows selected

SQL> drop table emp;

Table dropped.

SQL>