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

Database Interaction with PL/SQL, Explicit Cursors

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.

  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



Please note that all the examples in this series have been tested only with Oracle 10g. I didn't test them with all the previous versions of Oracle. I suggest you to refer the documentation of the respective version you are using if any of the programs fail to execute.

Introduction to Explicit Cursor

When a cursor name is explicitly assigned to a SELECT statement through CURSOR statement, it is called an explicit cursor. Explicit cursors are generally used to work with more than one row within PL/SQL. There exist mainly four steps to work with explicit cursor. The following are those four steps:

  • Declare cursor
  • Open cursor
  • Fetch rows (or values) from cursor (each row at a time)
  • Close cursor

Declaring a cursor is possible through CURSOR statement. Opening a cursor is possible through OPEN statement. Fetching rows from the cursor is possible through FETCH statement. Closing a cursor is possible through CLOSE statement. So, it is very easy to remember all four operations with the cursor. These four options are all automatically handled by Oracle in the case of IMPLICIT cursor (like implicit FOR loop with SELECT).

Using the CURSOR statement, we provide a name to the cursor along with a SELECT statement. Make sure that it is only a declaration and no rows will be fetched from the database (nothing happens in this stage). So it has to be declared only within declaration section. Declaring a cursor alone is not enough. Instead we need to open it. The SELECT statement provided within the CURSOR statement gets executed when we open the cursor. After the execution, all the rows get stored in memory (context area). We then fetch each and every row (one by one) of the cursor using a loop. Finally, we deallocate the memory occupied by the cursor using CLOSE statement.

The above paragraph looks like a big story. Is it that much necessary to work with more than one row? Why can't we simply use BULK COLLECT with SELECT..INTO or FOR with SELECT? The answer is flexibility. That means we will have total control over allocations and deallocations of the rows we fetched. Most of all, the implementation is so simple, when we really understand the concept. The next section shows you how to implement the entire story using a simple example.

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

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort


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