Cursors in Database Management Systems

John Charles Olamendy
May 20, 2012
16.7k
0
0
Article

Introduction.

This article is intended to show the principles and uses of cursors in relational database management systems. We're going to discuss how cursors are implemented in Microsoft SQL Server and Oracle database and how you can use it in your applications. I want to explain that this is another technique for data retrieval, and I recommend using cursors as the last option for querying your data because they consume a lot of memory, lock resources, and create excessive amounts of code, thus it's better to use SQL statements because is a lower-cost set-based language.

Although sometimes your requirements may constrain to you to manipulate and process result sets row by row, then the cursors act as an iterator over the collection of rows in the result set. In general, you should only consider using cursors after exhausting other methods of implementing your requirements for doing row-level processing sequentially such as while loops, sub-queries, temporary tables and table variables.

The general lifecycle of the cursor is:

Declaration of the cursor. DECLARE cursor_name CURSOR FOR SELECT ... FROM ...
Before you can use a cursor, you must open it. OPEN cursor_name.
After a cursor is opened, then it's positioned on a specific row in the result set with the FETCH statement. This operation transfers the data to the application and position the cursor to the next row until the end of the result set. FETCH cursor_name INTO ...
The last step is to close the cursor. CLOSE cursor_name.

A cursor may be scrollable or not. If the cursor is scrollable, then it may be move in any direction unlike non-scrollable cursors that moves forward only.

Cursors in Microsoft SQL Server.

You can use two forms to declare a cursor in SQL Server. The first form uses the SQL-92 syntax (see Listing 1).

DECLARE cursor_name [INSENSITIVE][SCROLL] CURSOR FOR select_statement
[FOR {READ ONLY | UPDATE [OF column_name[...]]}]

Listing 1.

The second form uses the DECLARE CURSOR statement (see Listing 2).

Let's explain some of the options in the cursors in SQL Server (see Table 1).

Option	Description
LOCAL \| GLOBAL	Local indicates that the cursor is within the scope of batches, triggers and stored procedures.
FORWARD_ONLY \| SCROLL	FORWARD_ONLY option allows you to move forward from the first row on onward. SCROLL allows you move forward and backward.
STATIC \| KEYSET \| DYNAMIC \| FAST_FORWARD	STATIC indicates that the cursor is a snapshot held on the tempdb database. KEYSET allows you to see the changes made outside, although you cannot see inserts or deletes. DYNAMIC allows you to see updates, inserts, and deletes. FAST_FORWARD defines two behaviors: setting the cursor to read only and move forward and onward.
READ_ONLY \| SCROLL_LOCKS \| OPTIMISTICS	READ_ONLY option means that updates cannot be made through the cursor. SCROLL_LOCKS option places locks on rows so that updates and deletes are guaranteed to be made after the cursor is closed. OPTIMISTICS option does not place locks on rows for updates and deletes and will only maintain modifications if an update has not occurred outside of the cursor.
TYPE_WARNINGS	A warning is being sent to the users if the cursor is converted from one type to a different type.

Table 1.

The select_statement is the query to define the data in the cursor. The UPDATE FOR clause is used to indicate the columns of the result set that can be updated.

In order to fetch data from a cursor, then the cursor must be open using the OPEN statement. Then the FETCH statement is used to get the data from the current row into local variables (see Listing 3). @@FETCH_STATUS variable returns the status of the fetch operation (if the value of this variable is equal to zero, then we have more rows to fetch).

FETCH cursor_name INTO variable [, variable ...];

Listing 3.

After you have finished the processing of the rows within the cursor, you must close and release the resources associated the cursor (see Listing 4).

CLOSE cursor_name;
DEALLOCATE cursor_name;

Listing 4.

Let's analyze a cursor example (see Listing 5).

Begin

declare @ProductID int;

declare @ProductName nvarchar(50);

declare @ProductListPrice money;

declare v_prod_cursor cursor

forward_only read_only

for select ProductID, [Name], ListPrice

from Production.Product;

open v_prod_cursor;

fetch next from v_prod_cursor

into @ProductID, @ProductName, @ProductListPrice;

while @@FETCH_STATUS = 0

begin

print 'ProductID is '+cast(@ProductID as varchar(20))+' Product Name is '+@ProductName+' Product List Price is '+cast(@ProductListPrice as varchar(20));

fetch next from v_prod_cursor

into @ProductID, @ProductName, @ProductListPrice;

end;

close v_prod_cursor;

deallocate v_prod_cursor;

end;

Listing 5.

First of all, we declare the local variables to hold individual values retrieved from the cursor.

Then the cursor is declared using the DECLARE CURSOR statement. After that, the cursor is open and values are retrieved from the cursor into the local variables. The WHILE loop is executed and allows printing the values retrieved from the cursor. The WHILE loop ends when there is no more rows to retrieve (WHILE @@FETCH_STATUS = 0).

When the WHILE loop exits, the cursor is close using the CLOSE statement and the memory allocated to the cursor is release by using the DEALLOCATE statement.

Cursors in Oracle database.

When you execute a SQL statement, then Oracle database system assigns a private work area for the statement to store information such as the result set, its metadata and amount of rows affected. It is logically as a pointer to the result set. You can move inside the result set using the cursor and process rows one row at time.

In Oracle PL/SQL there are two types of cursors: implicit and explicit.

An implicit cursor is automatically declared by Oracle database system whenever an SQL statement is executed. The user is not aware and cannot control the underlying result set.

An explicit cursor is defined by the program for controlling the holds more than one row. It allows the application sequentially to process the rows one at time and gives the developer more programmatic control. There is no such thing as an explicit cursor for UPDATE, DELETE, and INSERT statements.

In order to use a cursor in PL/SQL, you must first declare it like any other variable of your program using the following syntax (Listing 6).

CURSOR cursor_name [ (parameter, [parameter...])] [RETURN return_specification]
IS sql_statement [FOR UPDATE [OF [column_list]]];

Listing 6.

After a cursor is declared, you can cause the execution of the underlying SQL statement by opening it. The OPEN syntax is shown (see Listing 7).

OPEN cursor_name [(argument [, argument ...])];

Listing 7.

The arguments are the values to be passed if the cursor was declared with a parameter list.

The OPEN statement does not retrieve any row; this action is performed by the FETCH statement (Listing 8). Because the multi-version read consistency mechanism in Oracle database, then the data retrieved is guaranteed to be consistent according to the time when cursor was opened. Thus, the INSERT, UPDATE, DELETE operations, performed after the cursor is open, are not visible to the data fetched by the cursor. Furthermore, if the SELECT statement within the cursor declaration uses the FOR UPDATE clause, all the rows identified by the query are locked when the cursor is opened.

FETCH cursor_name INTO variable [, variable ...];

Listing 8.

Once you opened a cursor, you can move until the last row in the result set. Oracle offers four attributes (%FOUND, %NOTFOUND, %ISOPEN, %ROWCOUNT) that allow you to retrieve information about the state of the cursor. You reference these attributes as shown below (see Listing 9).

cursor_name%attribute

Listing 9.

The following table describes the meaning of each attribute.

Name	Description
cursor_name%FOUND	Returns true if a record was fetched successfully.
cursor_name%NOTFOUND	Returns true if a record was not fetched successfully.
cursor_name%ROWCOUNT	Returns the number of records fetched from the specified cursor at that point in time.
cursor_name%ISOPEN	Returns true if the specified cursor is open.

Table 1.

Like any other variable, cursor takes some memory and need to be closed and destroyed in order to release the underlying resources.

You must also close the cursor before you reopen it to run the query again (see Listing 10).

CLOSE cursor_name;

Listing 10.

Let's illustrate the concepts with an example. A cursor is associated to a query that retrieves the data about employee from the emp table in the scott schema. Then the cursor is open and for each row the underlying data is written to the output device (sees Listing 11).

Declare
cursor v_emp_cur is select * from scott.emp;
v_emp_row scott.emp%rowtype;
begin
open v_emp_cur;
loop
    fetch v_emp_cur into v_emp_row;

    exit when v_emp_cur%NOTFOUND;

    dbms_output.put_line('employee number '||v_emp_row.empno||' employee name '||v_emp_row.ename||' job '||v_emp_row.job);
end loop;
close v_emp_cur;
end;

Listing 11.

There is an alternative way of handling cursors. It's called the cursor FOR loop because of the simplified syntax used. Using this method, the process of opening, fetching, and closing is handled implicitly. This makes your business logic simpler and easier to maintain.

In order to understand the FOR loop, let's modify the previous listing (see Listing 12).

Declare
cursor v_emp_cur is select * from scott.emp;
begin
for v_emp_row in v_emp_cur
loop
dbms_output.put_line('employee number '||v_emp_row.empno||' employee name '||v_emp_row.ename||' job '||v_emp_row.job);
end loop;
end;

Listing 12.

Finally, let's analyze another type of cursor in PL/SQL: the cursor variable. A cursor variable is variable that references a cursor. A cursor variable can be opened for any query, even for different queries in the same application. The most important benefit of this type of cursor is to enable passing result set of queries between different programs between the server and the client. Cursor variables employ the full functionality of static cursors (implicit and explicit cursors) such as OPEN, FETCH, CLOSE and attributes such as %ISOPEN, %FOUND, %NOTFOUND and %ROWCOUNT. In order to declare a cursor variable, we need to use the REF CURSOR type (see Listing 13). Cursor variables are dynamics because they may reference any other static cursor.

type t_refcursor1 is ref cursor;
type t_refcursor2 is ref cursor [return return_type];

Listing 13.

Let's see an example (see Listing 14). Let's define a weak cursor (generic cursor) and strong cursor which expects to receives records from the table emp. Let's open both cursor variables, and fetch information from the first record and finally close the cursors;

declare
     type t_generic_curs is ref cursor;
     type t_emp_curs is ref cursor return emp%rowtype;
     v_gen_curs_variable t_generic_curs;
     v_emp_curs_variable t_emp_curs;
     v_sal emp.sal%type;
begin
    open v_gen_curs_variable for select * from emp;
    open v_emp_curs_variable for select * from emp;
    fetch v_gen_curs_variable into v_sal;
    fetch v_emp_curs_variable into v_sal;
    close v_gen_curs_variable;
    close v_emp_curs_variable;
end;
/

Listing 14.

When you open a cursor for a variable cursor, if the cursor variable is not assigned to any cursor, then a cursor object is created for the variable. If the cursor variable references already a cursor, then a new cursor object is not created, instead it's reused the existing object and the new created object is attached to the cursor.

Conclusion.

In this article, I covered the principles and uses of cursors in relational database systems specifically in Microsoft SQL Server and Oracle database, through several examples illustrating the main concepts.