PL/SQL Ref Cursor

Ref Cursors are user defined types which is used to process multiple records and also these cursors also internally used record by record process generally, when we are using static cursors PL/SQL runtime engine bounds single select statement at a time to the active set area at compile time, whereas ref cursors PL/SQL runtime engine executes number of select statement dynamically for the single active set area.

Generally, we are not allowed to pass static cursor as a parameter to the Sub Programs to overcome this problem ANSI ISO SQL introduction ref cursor to pass cursor as a parameter to the sub programs.

This is a user defined type, so we are creating in two step process first we are creating the type then only we are creating variable of that type that’s why these cursors are also called as cursor as cursor variables.

There are two types of ref cursors supported by oracle

1.Strong ref cursor

       2.weak ref cursor 

Strong ref cursor is a ref cursor which is having return type whereas weak ref cursor is a ref cursor which does not have return type

Strong ref cursor Syntax:

             Type Typename is ref cursor return recordtypedatatype;

             Variablename typename;

Weak ref cursor Syntax:

          Type Typename is ref cusrcursor

            Varaiablename Typename;

Ref cursors we are executing number of select statement, Using Open for statement

Syntax:

  Open ref cursor variable for select statement where condition;

Example:

 DECLARE

   TYPE first_type IS REF CURSOR;

   v_type   first_type;

   v_emp    emp%ROWTYPE;

BEGIN

   OPEN v_type FOR

      SELECT *

        FROM emp

       WHERE sal > 1000;

   LOOP

      FETCH v_type

       INTO v_emp;

      EXIT WHEN v_type%NOTFOUND;

      DBMS_OUTPUT.put_line (v_emp.ename || ' ' || v_emp.sal);

   END LOOP;

   CLOSE v_type;

END;

/

Example2:

DECLARE

   TYPE t1 IS REF CURSOR;

   v_type     t1;

   v_emp      emp%ROWTYPE;

   v_dept     dept%ROWTYPE;

   v_deptno   NUMBER (10)    := &deptno;

BEGIN

   IF v_deptno = 10

   THEN

      OPEN v_type FOR

         SELECT *

           FROM emp

          WHERE deptno = 10;

      LOOP

         FETCH v_type

          INTO v_emp;

         EXIT WHEN v_type%NOTFOUND;

         DBMS_OUTPUT.put_line (v_emp.ename || ' ' || v_emp.dpetno);

      END LOOP;

      CLOSE v_type;

   ELSIF v_deptno = 20

   THEN

      OPEN v_type FOR

         SELECT *

           FROM dept

          WHERE deptno = 20;

      LOOP

         FETCH v_type

          INTO v_dept;

         EXIT WHEN v_type%NOTFOUND;

         DBMS_OUTPUT.put_line (v_dept.deptno || ' ' || v_dept.loc);

      END LOOP;

      CLOSE v_type;

   END IF;

END;

/

Passing ref cursor as parameter to sub programs:

Example:

Package Spec

CREATE OR REPLACE PACKAGE para_pkg

AS

   TYPE t1 IS REF CURSOR

      RETURN emp%ROWTYPE;

   PROCEDURE p1 (p_t OUT t1);

END para_pkg;

/

Package Body

CREATE OR REPLACE PACKAGE BODY para_pkg

AS

   PROCEDURE p1 (p_t OUT t1)

   AS

   BEGIN

      OPEN p_t FOR

         SELECT *

           FROM emp;

   END p1;

END para_pkg;

/

Execution:

     SQL>Variable a ref cursor;

     SQL>exec para.p1(:a);

     SQL> Print a;

Note: Ref cursors are not allowed to use directly within packages

Return result sets using ref cursors:

Note: Oracle introduced sys_refcusor type in place of weak ref cursor

Syntax:

             Variablename sys_refcursor;

Example:

DECLARE

   v_t   sys_refcursor;

   i     emp%ROWTYPE;

BEGIN

   OPEN v_t FOR

      SELECT *

        FROM emp

       WHERE deptno = 10;

   LOOP

      FETCH v_t

       INTO i;

      EXIT WHEN v_t%NOTFOUND;

      dbms_out.put_line (i.ename || ' ' || i.deptno);

   END LOOP;

   CLOSE v_t;

END;

/