Database Interaction with PL/SQL, Explicit Cursors

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.