Cursors in Database Management Systems

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.