Oracle/CURSOR

CURSOR

Soul-Learner 2008. 7. 5. 20:37
 

Database Interaction with PL/SQL, Explicit Cursors


(Page 1 of 4 )

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.


 

Database Interaction with PL/SQL, Explicit Cursors - Working With Explicit Cursor


(Page 2 of 4 )

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.


 

Database Interaction with PL/SQL, Explicit Cursors - Other Approaches of Using Explicit Cursor


(Page 3 of 4 )

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.


 

Database Interaction with PL/SQL, Explicit Cursors - Retrieving More Than One Value With FETCH


(Page 4 of 4 )

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.

Declare
  CURSOR c_emp IS
    select * from emp;
  r_emp emp%rowtype;
Begin
  OPEN c_emp;
  Loop
    FETCH c_emp into r_emp;
    Exit when c_emp%NOTFOUND;
    Dbms_output.put_line(r_emp.empno || ', ' || r_emp.ename);
  End loop;
  CLOSE c_emp;
End;

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:

Declare
  TYPE t_emprec IS RECORD
  (
    ename emp.ename%type,
    sal   emp.sal%type,
    job   emp.job%type
  );
  r_emp   t_emprec;
  CURSOR c_emp IS
    select ename,sal,job from emp;
Begin
  OPEN c_emp;
  Loop
    FETCH c_emp into r_emp;
    Exit when c_emp%NOTFOUND;
    Dbms_output.put_line(r_emp.ename || ', ' || r_emp.sal || ', ' || r_emp.job);
  End loop;
  CLOSE c_emp;
End;

Another wonder is that we can also declare a variable which is directly based on the structure of an existing cursor as shown in the following example:

Declare
  CURSOR c_emp IS
    select ename,sal,job from emp;
  r_emp c_emp%rowtype;
Begin
  OPEN c_emp;
  Loop
    FETCH c_emp into r_emp;
    Exit when c_emp%NOTFOUND;
    Dbms_output.put_line(r_emp.ename || ', ' || r_emp.sal || ', ' || r_emp.job);
  End loop;
  CLOSE c_emp;
End;

In the above program the most important declaration is as follows:

r_emp c_emp%rowtype;

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.