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

Other Approaches of Using Explicit Cursor - 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

There are several approaches to work with explicit cursors. The approach given above is a very traditional approach being followed from very early versions of Oracle. Now, let us try another approach.

Declare
  CURSOR c_emp IS
    select empno, ename from emp;
  v_empno emp.empno%type;
  v_ename emp.ename%type;
Begin
  OPEN c_emp;
  FETCH c_emp into v_empno,v_ename;
  WHILE c_emp%FOUND
  Loop
    Dbms_output.put_line(v_empno || ', ' || v_ename);
    FETCH c_emp into v_empno,v_ename;
  End loop;
  CLOSE c_emp;
End;

The above program looks almost similar to the previous section, except that I am using a WHILE loop with '%FOUND' cursor attribute. Let us consider another approach using FOR loop:

Declare
  CURSOR c_emp IS
    select empno, ename from emp;
Begin
  FOR r_emp in c_emp
  Loop
    Dbms_output.put_line(r_emp.empno || ', ' || r_emp.ename);
  End loop;
End;

In the case of the above program, the FOR statement itself opens the cursor 'c_emp' and closes after completion. We need not specifically open the cursor in the case of FOR statement. But another issue is that I am using 'r_emp' with FOR loop, which is never declared. PL/SQL allows it! The variables used with the FOR statement (in the form of IMPLICIT cursor) need not be declared. All the values of the row get stored within 'r_emp'. We extract each of those values using dot notation (as demonstrated within the DBMS_OUTPUT statement above).

We can also restrict number of rows being displayed as demonstrated in the following example:

Declare
  CURSOR c_emp IS
    select empno, ename from emp;
  v_empno emp.empno%type;
  v_ename emp.ename%type;
Begin
  OPEN c_emp;
  FETCH c_emp into v_empno,v_ename;
  WHILE c_emp%FOUND and c_emp%rowcount <=5
  Loop
    Dbms_output.put_line(v_empno || ', ' || v_ename);
    FETCH c_emp into v_empno,v_ename;
  End loop;
  CLOSE c_emp;
End;

The %ROWCOUNT cursor attribute is explained in my previous article (part-9). It just counts the number of rows fetched. I hope the rest is the same. If you are not using WHILE loop or FOR loop (like in the first example), we can replace that EXIT WHEN statement using the following statement:

Exit when c_emp%NOTFOUND or c_emp%rowcount > 5;

If you carefully observe, the WHILE loop condition is quite opposite to that of EXIT WHEN statement.



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