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

Working with RECORD and REF CURSOR - 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

Until now, we have been working either with %TYPE or %ROWTYPE. This means we are working with either one value or one complete record. How do we create our own data type, with our own specified number of values to hold? This is where TYPE and RECORD come in.  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
  open c_emp for select ename,sal from emp;
  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;

The most confusing aspect from the above program is the following:

  type rec_emp is record
  (
    name  varchar2(20),
    sal   number(6)
  );

The above defines a new data type named "rec_emp" (just like %ROWTYPE with limited specified fields) which can hold two fields, namely "name" and "sal."

  er rec_emp;

The above statement declares a variable "er" based on the datatype "rec_emp."  This means that "er" internally contains the fields "name" and "job."

      fetch c_emp into er;

The above statement pulls out a row of information (in this case "ename" and "sal") and places the same into the fields "name" and "sal" of the variable "er."  Finally, I display both of those values using the following statement:

      dbms_output.put_line(er.name || ' - ' || er.sal);



 
 
>>> 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: