Working with REF CURSOR in PL/SQL - Working with more than one query with the same REF CURSOR

(Page 3 of 4 )

As defined earlier, a REF CURSOR can be associated with more than one SELECT statement at run-time.  Before associating a new SELECT statement, we need to close the CURSOR.  Let us have an example as follows:

declare
  type r_cursor is REF CURSOR;
  c_emp r_cursor;
  type rec_emp is record
  (
    name  varchar2(20),
    sal   number(6)
  );
  er rec_emp;
begin
  open c_emp for select ename,sal from emp where deptno = 10;
  dbms_output.put_line('Department: 10');
  dbms_output.put_line('--------------');
  loop
      fetch c_emp into er;
      exit when c_emp%notfound;
      dbms_output.put_line(er.name || ' - ' || er.sal);
  end loop;
  close c_emp;
  open c_emp for select ename,sal from emp where deptno = 20;
  dbms_output.put_line('Department: 20');
  dbms_output.put_line('--------------');
  loop
      fetch c_emp into er;
      exit when c_emp%notfound;
      dbms_output.put_line(er.name || ' - ' || er.sal);
  end loop;
  close c_emp;
end;

In the above program, the skeleton looks like the following:

declare
.
.
Begin
.
.
  open c_emp for select ename,sal from emp where deptno = 10;
.
.
      fetch c_emp into er;
.
.
  close c_emp;
.
.
  open c_emp for select ename,sal from emp where deptno = 20;
.
.
      fetch c_emp into er;
.
.
  close c_emp;
.
.
end;

From the above skeleton, you can easily understand that every CURSOR is opened, used and closed before opening the same with the next SELECT statement.

Working with REF CURSOR inside loops

Sometimes, it may be necessary for us to work with REF CURSOR within loops.  Let us consider the following example:

declare
  type r_cursor is REF CURSOR;
  c_emp r_cursor;
  type rec_emp is record
  (
    name  varchar2(20),
    sal   number(6)
  );
  er rec_emp;
begin
  for i in (select deptno,dname from dept)
  loop
    open c_emp for select ename,sal from emp where deptno = i.deptno;
    dbms_output.put_line(i.dname);
    dbms_output.put_line('--------------');
    loop
      fetch c_emp into er;
      exit when c_emp%notfound;
      dbms_output.put_line(er.name || ' - ' || er.sal);
    end loop;
    close c_emp;  
  end loop;
end;

As you can observe from the above program, I implemented a FOR loop as follows:

  for i in (select deptno,dname from dept)
  loop
      .
      .
  end loop;

The above loop iterates continuously for each row of the "dept" table.  The details of each row in "dept" (like deptno, dname etc.) will be available in the variable "i."  Using that variable (as part of the SELECT statement), I am working with REF CURSOR as follows:

    open c_emp for select ename,sal from emp where deptno = i.deptno;

The rest of the program is quite commonplace.

Next: Dealing with REF CURSOR in the sub-programs of a PL/SQL block >>
 

More Oracle Articles
More By Jagadish Chatarji

Comments On: Working with REF CURSOR in PL/SQL

· This is a simple introduction to REF CURSOR in PL/SQL. Hope you will enjoy it. If...    · dear sir, this article is very use full for the beginner like me.Thanks a...    · It is very good article for the starters in PL/SQL    · thanks!this was a great article for me and gave me new insights.    · I would like to know how to handle data from a ref cursor to a record type when i...    · I liked they way,all your topics are put in.This one was also very easy to...    · This is really to good explanation with examples. I was able to understand it in a...    · very good one    · great way of explaining the concept    · Its really a good way of explaining ina simple way..ThanksJeg   >>> Post your comment now!