Follow us: Connect on YouTube 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;
/
 
You May Like:

PLSQL Record

Share this article :

2 comments:

  1. Hi There,

    Your writing shines! There is no room for gibberish here clearly you have explained about PL/SQL Ref Cursor . Keep writing!

    My name is Donal and I'm new to the site so just wanted to introduce myself and say hi and post my first question so let me know if I am making any stupid mistakes as I may be a bit green on how this works!

    Basically I have data stored in a 2 fields as follows:

    Org_ Parent
    Org_child

    The Org_Parent field is repeated with the same value 3 times but the org_child has 3 separate values. I need a query that will represent the data in a flat format .i.e in 4 separate fields as follows:

    cost centre(org_parent field):
    Sub-Division(first value in org_child field): will always start with a 6 digit number
    Division(second value in org_child field): will always start with a 4 digit number ending with 2 zeros
    Branch(third value in org_child field): will always start with a 4 digit number ending with 3 zeros

    See attached excel spread sheet for how data currently looks and how it should look after the query

    Follow my new blog if you interested in just tag along me in any social media platforms!

    Best Regards,
    Preethi

    ReplyDelete
  2. Hello There,

    I genuinely do look forward for the time where you post some new write ups. Your blog make me feel so educated! Continue soaring and writing please.

    I have to collect the past execution plan of specific statement(which runs somewhere around midnight everyday).
    Then I will have to compare the last execution plans of the 3 same statement . I run AWR stats collection every hour and have history upto 30 days.

    I am trying to run the following but it is not showing me even the last 3 executions of specific statement ( identified by .... like '%SUBSCRIBED_PRODUCT.BUSINESS_AREA_ID=2%'.......) I have to collect the past execution plan of specific statement(which runs somewhere around midnight everyday).
    Then I will have to compare the last execution plans of the 3 same statement . I run AWR stats collection every hour and have history upto 30 days.

    I am trying to run the following but it is not showing me even the last 3 executions of specific statement ( identified by .... like '%SUBSCRIBED_PRODUCT.BUSINESS_AREA_ID=2%'.......)

    Once again thanks for your tutorial.

    Kind Regards,
    Preethi.

    ReplyDelete