Home arrow Oracle arrow Page 4 - Database Interaction with PL/SQL, Explicit Cursors

Retrieving More Than One Value With FETCH - Oracle

This article introduces the concept of explicit cursor. We will also examine different approaches to work with explicit cursor. This builds on the concept or cursors, which I looked at in my previous article along with looking at SQL cursor and cursor attributes.

TABLE OF CONTENTS:
  1. Database Interaction with PL/SQL, Explicit Cursors
  2. Working With Explicit Cursor
  3. Other Approaches of Using Explicit Cursor
  4. Retrieving More Than One Value With FETCH
By: Jagadish Chatarji
Rating: starstarstarstarstar / 49
July 26, 2005

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

In all of the above examples, we are trying to fetch only two columns of values ('empno' and 'ename'). We can retrieve more than one value (or entire row) using FETCH statement. Let us consider the following example to fetch an entire row from FETCH into a single variable.

Declare
  CURSOR c_emp IS
    select * from emp;
  r_emp emp%rowtype;
Begin
  OPEN c_emp;
  Loop
    FETCH c_emp into r_emp;
    Exit when c_emp%NOTFOUND;
    Dbms_output.put_line(r_emp.empno || ', ' || r_emp.ename);
  End loop;
  CLOSE c_emp;
End;

In the above example we are using 'SELECT *' statement to retrieve all columns of information. 'r_emp' is declared as of type 'emp%rowtype'. That means it can store an entire row with the structure available in the table 'emp'. We directly used only a single variable 'r_emp' with FETCH statement (as it can hold an entire row). We displayed the necessary values using dot notation (as demonstrated in the DBMS_OUTPUT statement).

We can also retrieve only specified columns (without declaring too many variables) using TYPE and RECORD declarations as explained in part-2 of the series. Let us examine that with a simple example:

Declare
  TYPE t_emprec IS RECORD
  (
    ename emp.ename%type,
    sal   emp.sal%type,
    job   emp.job%type
  );
  r_emp   t_emprec;
  CURSOR c_emp IS
    select ename,sal,job from emp;
Begin
  OPEN c_emp;
  Loop
    FETCH c_emp into r_emp;
    Exit when c_emp%NOTFOUND;
    Dbms_output.put_line(r_emp.ename || ', ' || r_emp.sal || ', ' || r_emp.job);
  End loop;
  CLOSE c_emp;
End;

Another wonder is that we can also declare a variable which is directly based on the structure of an existing cursor as shown in the following example:

Declare
  CURSOR c_emp IS
    select ename,sal,job from emp;
  r_emp c_emp%rowtype;
Begin
  OPEN c_emp;
  Loop
    FETCH c_emp into r_emp;
    Exit when c_emp%NOTFOUND;
    Dbms_output.put_line(r_emp.ename || ', ' || r_emp.sal || ', ' || r_emp.job);
  End loop;
  CLOSE c_emp;
End;

In the above program the most important declaration is as follows:

r_emp c_emp%rowtype;

The above declaration says that a variable 'r_emp' should have the same structure as of cursor 'c_emp' to hold the values. It can hold an entire row from 'c_emp'. This type of syntax is quite widely used by PL/SQL developers. As there exists several approaches of using explicit cursor, I leave it to the readers to choose the best approach which suits them.



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