Home arrow Oracle arrow Page 4 - Working with REF CURSOR in PL/SQL

Dealing with REF CURSOR in the sub-programs of a PL/SQL block - Oracle

This article introduces you to the REF CURSOR in Oracle PL/SQL. I've included numerous examples to help you understand how to work with REF CURSOR.

TABLE OF CONTENTS:
  1. Working with REF CURSOR in PL/SQL
  2. Working with RECORD and REF CURSOR
  3. Working with more than one query with the same REF CURSOR
  4. Dealing with REF CURSOR in the sub-programs of a PL/SQL block
By: Jagadish Chatarji
Rating: starstarstarstarstar / 448
February 12, 2007

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

Sub-programs can also be called sub-routines.  These are nothing but the divisions of the main program.  These divisions are named and are executed when they are called by name from the main program.  They will not get executed unless they are called. 

The following is an 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;
  procedure PrintEmployeeDetails is
  begin
    loop
      fetch c_emp into er;
      exit when c_emp%notfound;
      dbms_output.put_line(er.name || ' - ' || er.sal);
    end loop;
  end;
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('--------------');
    PrintEmployeeDetails;
    close c_emp;  
  end loop;
end;

In the above program, the sub-routine is named "PrintEmployeeDetails."  You can observe that I am executing (or calling) the sub-routine from within the loop as follows:

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

According to the above loop, the sub-routine gets executed for every iteration, which displays the employee information for the respective department.

Passing REF CURSOR as parameters to sub-programs

In the previous section, we already started working with sub-programs (or sub-routines).  In this section, I shall extend the same with the concept of "parameters" (or arguments).  Every sub-program (or sub-routine) can accept values passed to it in the form of "parameters" (or arguments).  Every parameter is very similar to a variable, but gets declared as part of a sub-program.

Let us consider the following program:

declare
  type r_cursor is REF CURSOR;
  c_emp r_cursor;
  type rec_emp is record
  (
    name  varchar2(20),
    sal   number(6)
  );
  procedure PrintEmployeeDetails(p_emp r_cursor) is
    er rec_emp;
  begin
    loop
      fetch p_emp into er;
      exit when p_emp%notfound;
      dbms_output.put_line(er.name || ' - ' || er.sal);
    end loop;
  end;
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('--------------');
    PrintEmployeeDetails(c_emp);
    close c_emp;  
  end loop;
end;

From the above program, you can observe the following declaration:

  procedure PrintEmployeeDetails(p_emp r_cursor) is

In the above declaration, "PrintEmployeeDetails" is the name of the sub-routine which accepts "p_emp" as a parameter (of type "r_cursor") and we can use that parameter throughout that sub-routine.

I hope you enjoyed the article and any comments, suggestions, feedback, bugs, errors, enhancements etc. are highly appreciated at http://jagchat.spaces.live.com



 
 
>>> More Oracle Articles          >>> More By Jagadish Chatarji
 

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

ORACLE ARTICLES

- Oracle Java Security Woes Continue
- Oracle's New IaaS Cloud Option: There's a Ca...
- Oracle Acquires Eloqua to Boost Cloud Presen...
- Choosing Innovation: Oracle Survey Insights
- Oracle Fixes Privilege Escalation Bug
- Oracle`s Communications Service Availability...
- Oracle Releases Exalytics, Taleo Plans
- Oracle Releases Communications Network Integ...
- Oracle Releases Communications Data Model 11...
- Oracle Releases PeopleSoft PeopleTools 8.52
- Oracle Integrates Cloudera Apache Distro, My...
- Oracle Releases MySQL 5.5.18
- Oracle Announces NoSQL Database Availability
- Sorting Database Columns With the SELECT Sta...
- Retrieving Table Data with the LIKE Operator

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: