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

Working With 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

I spoke too much in the previous section without giving any practical example. So, first of all let us go through an 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;
  Loop
    FETCH c_emp into v_empno,v_ename;
    Exit when c_emp%NOTFOUND;
    Dbms_output.put_line(v_empno || ', ' || v_ename);
  End loop;
  CLOSE c_emp;
End;

Let me explain the above example step by step.

CURSOR c_emp IS select empno, ename from emp;

The above statement defines a cursor with a name 'c_emp' based on the provided SELECT statement. Make sure it is only a declaration and does not process anything yet.

OPEN c_emp;

The above statement makes the SELECT statement available with 'c_emp' to execute. When this statement gets executed, all the rows (with only the columns 'empno' and 'ename') from the table 'emp' gets retrieved and stored in a memory context identified by 'c_emp'.

FETCH c_emp into v_empno,v_ename;

The above statement fetches only one consecutive row from the memory. FETCH statement in general, moves only one row forward at a time. That is why we need to use a loop. After fetching the row information, the values get assigned to the variables specified.

Exit when c_emp%NOTFOUND;

The loop gets terminated based on the above statement. You can also observe that the cursor attribute '%NOTFOUND' is being used with an explicit cursor. If no rows are fetched through the previous FETCH statement of the same cursor 'c_emp', it returns 'true' and the control skips out of the loop.

Dbms_output.put_line(v_empno || ', ' || v_ename);

The above statement displays the current values available in 'v_empno' and 'v_ename'.

CLOSE c_emp;

The above statement frees up resources by removing all the allocations of cursor 'c_emp' from the memory. A cursor once closed cannot be used anymore, unless we open it once again. And that terminates the whole story. Is it too complicated? I don't think so, except that we need to remember the syntactical issues.



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