Login

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.

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.

{mospagebreak title=Working With Explicit Cursor}

I spoke too much in the previous section without giving any practical example. So, first of all let us go through an example:

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.

{mospagebreak title=Other Approaches of Using Explicit Cursor}

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.

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:

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.

{mospagebreak title=Retrieving More Than One Value With FETCH}

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.

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:

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.