Follow us: Subscribe via RSS Feed Connect on YouTube Connect on YouTube

Pages

Tuesday, 19 April 2016

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;
ENDpara_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;
/
Share this article :

0 comments:

Post a Comment