|
|
|
Cursors enable embedded SQL programs to process, one at a time, the result rows returned by a select statement. After a cursor has been opened, it can be advanced through the result rows. When the cursor is positioned to a row, the data in the row can be transferred to host language variables and processed according to the requirements of the application. The row to which the cursor is positioned is referred to as the current row.
A typical cursor application uses SQL statements to perform the following steps:
|
|
|
The following is an example of cursor processing:
exec sql include sqlca;
exec sql begin declare section;
name character_string(15);
salary float;
exec sql end declare section;
exec sql whenever sqlerror stop;
exec sql connect personnel;
exec sql declare c1 cursor for
select ename, sal
from employee
for update of sal;
exec sql open c1;
exec sql whenever not found goto closec1;
loop while more rows
/* The WHENEVER NOT FOUND statement causes
the loop to be broken as soon as a row
is not fetched. */
exec sql fetch c1 into :name, :salary;
print name, salary;
if salary less than 10000 then
exec sql update employee
set salary = 10000
where current of c1;
end if;
end loop;
closec1:
exec sql close c1;
exec sql disconnect;
|
|
|
Before using a cursor in an application, the cursor must be declared. The syntax for declaring a cursor is:
exec sql declare cursor_name cursor for
select_statement;
The declare cursor statement assigns a name to the cursor and associates the cursor with a select statement to be used to retrieve data. A cursor is always associated with a select statement. The select is executed when the cursor is opened. Updates can be performed only if the cursor select statement refers to a single table (or updateable view) and does not include any of the following elements:
These elements can be present in subselects within the select statement, but must not occur in the outermost select statement.
The cursor_name can be specified using a string literal or a host language string variable. The cursor name cannot exceed 32 characters, and can be assigned dynamically. For details, see Summary of Cursor Positioning.
|
|
|
To open a cursor, use the open statement:
exec sql open cursor_name [for readonly];
Opening a cursor executes the associated select statement. The rows returned by the select statement are stored in a temporary result set. The cursor is positioned before the first row in the result table.
Note: If a cursor is closed and reopened, the cursor is repositioned to the beginning of the result table, and does not resume the position it had before it was closed.
|
|
|
Readonly cursors specify that the data does not intend to be updated. The for readonly clause can be specified even if the cursor was declared for update; if this is done, updates on the data cannot be performed.
To improve performance, the DBMS Server pre-fetches (buffers) rows for readonly cursors. Use the set_sql(prefetchrows) statement to disable prefetching or to specify the number of rows to prefetch. To determine the number of rows that is prefetched for a particular readonly cursor, open the cursor, issue the inquire_sql(prefetchrows) statement.
By default the DBMS Server calculates the number of rows it can prefetch, taking into consideration the size of the row being fetched and the dimensions of internal buffers. If, using set_sql(prefetchrows), a value larger than the maximum number of rows the DBMS Server can prefetch is specified, prefetchrows is reset to its calculated maximum.
Note: Prefetchrows cannot be set for readonly cursors that return long varchar or long byte columns.
|
|
|
Cursors affect transaction processing as follows:
|
|
|
The fetch statement advances the position of the cursor through the result rows returned by the select. Using the fetch statement, your application can process the rows one at a time. The syntax of the fetch statement is:
exec sql fetch cursor_name
into variable{, variable};
The fetch statement advances the cursor to the first or next row in the set, and loads the values indicated in the select clause of the declare cursor statement into host language variables.
To illustrate, the example of cursor processing shown previously contains the following declare cursor statement:
exec sql declare c1 cursor for
select ename, sal
from employee
for update of sal;
Later in the program, the following fetch statement appears:
exec sql fetch c1 into :name, :salary;
This fetch statement puts the values from the columns, ename and sal, of the current row into the host language variables, name and salary.
Because the fetch statement operates on a single row at a time, it is ordinarily placed inside a host language loop.
You can detect when you have fetched the last row in the result table in the following ways:
Cursors can only move forward through a set of rows. To refetch a row, close and reopen a cursor.
|
|
|
While a cursor is open, your application can append rows using non-cursor insert statements. If rows are inserted after the current cursor position, the rows are or are not be visible to the cursor, depending on the following criteria:
|
|
|
To use a cursor to update rows, specify the for update clause when declaring the cursor:
exec sql declare cursor_name cursor for
select_statement
for [deferred | direct] update from column1 {,column};
The for update of clause must list any columns in the selected database table that are intended to be updated. Columns cannot be updated unless they have been declared for update. To delete rows, the cursor does not need to be declared for update.
The cursor update statement has the following syntax:
exec sql update tablename
set column= expression {, column = expression}
where current of cursor_name;
|
|
|
There are two cursor modes: direct and deferred. The default cursor mode is specified when the DBMS Server is started. The default for ANSI/ISO Entry SQL-92 compliance is direct mode.
|
|
|
Direct mode allows changes to be seen by the program before the cursor is closed. In direct mode, if a row is updated with a value that causes the row to move forward with respect to the current position of the cursor (for example, a key column is updated), the program sees this row again and takes appropriate steps to avoid reprocessing that row.
|
|
|
In a Deferred Mode, changes made to the current row of a cursor are not visible to the program that opened the cursor until the cursor is closed. Transaction behavior, such as the release of locks and external visibility to other programs, is not affected if deferred update is used. There can be only one cursor open for update in deferred mode at any given time.
|
|
|
The where clause of the cursor version specifies the row to which the cursor currently points. The update affects only data in that row. Each column referenced in the set clause must have been previously declared for updating in the declare cursor statement.
The cursor must be pointing to a row (a fetch has been executed) before a cursor update is performed. The update statement does not advance the cursor; a fetch is required to move the cursor forward one row. Two cursor updates not separated by a fetch generally cause the same row to be updated twice if the cursor was opened in direct mode, or cause an error if the cursor was opened in deferred mode.
|
|
|
The cursor version of the delete statement has the following syntax:
exec sql delete from tablename where current of cursor_name;
The delete statement deletes the current row. The cursor must be positioned on a row (as the result of a fetch statement) before a cursor delete can be performed. After the row is deleted, the cursor points to the position after the row (and before the next row) in the set. To advance the cursor to the next row, issue the fetch statement.
A cursor does not have to be declared for update to perform a cursor delete.
|
|
|
The following example illustrates updating and deleting with a cursor:
exec sql include sqlca;
exec sql begin declare section;
name character_string(15);
salary float;
exec sql end declare section;
exec sql whenever sqlerror stop;
exec sql connect personnel;
exec sql declare c1 cursor for
select ename, sal
from employee
for update of sal;
exec sql open c1;
exec sql whenever not found goto closec1;
loop while more rows
exec sql fetch c1 into :name, :salary;
print name, salary;
/* Increase salaries of all employees earning
less than 60,000. */
if salary < 60,000 then
print 'Updating ', name;
exec sql update employee
set sal = sal * 1.1
where current of c1;
/* Fire all employees earning more than
300,000. */
else if salary > 300,000 then
print 'Terminating ', name;
exec sql delete from employee
where current of c1;
end if;
end loop;
closec1:
exec sql close c1;
exec sql disconnect;
|
|
|
To close a cursor, issue the close cursor statement:
exec sql close cursor_name;
After the cursor is closed, no more processing can be performed with it unless another open statement is issued. The same cursor can be opened and closed any number of times in a single program. A cursor must be closed before it can be reopened.
|
|
|
The following table summarizes the effects of cursor statements on cursor positioning:
Statement |
Effect on Cursor Position |
|---|---|
Open |
Cursor positioned before first row in set. |
Fetch |
Cursor moves to next row in set. If it is already on the last row, the cursor moves beyond the set and its position becomes undefined. |
Update(cursor) |
Cursor remains on current row. |
Delete(cursor) |
Cursor moves to a position after the deleted row (but before the following row). |
Close |
Cursor and set of rows become undefined. |
For extended examples of the use of cursors in embedded SQL, see the Embedded SQL Companion Guide.
|
|
|
A dynamically specified cursor name (a cursor name specified using a host string variable) can be used to scan a table that contains rows that are related hierarchically, such as a table of employees and managers.
In a relational database, this tree structure is represented as a relationship between two columns. In an employee table, employees are assigned an ID number. One of the columns in the employee table contains the ID number of each employee's manager. The ID number column establishes the relationships between employees and managers.
To use dynamically specified cursor names to scan this kind of table, do the following:
The following example retrieves rows from the employee table, which has the following format:
exec sql declare employee table
(ename varchar(32),
title varchar(20),
manager varchar(32));
This program scans the employee table and prints out all employees and the employees that they manage.
/* This program will print out, starting with
** the top manager, each manager and who they
** manage for the entire company. */
exec sql include sqlca;
/* main program */
exec sql begin declare section;
manager character_string(32)
exec sql end declare section;
exec sql connect dbname;
exec sql whenever not found goto closedb;
exec sql whenever sqlerror call sqlprint;
/* Retrieve top manager */
exec sql select ename into :topmanager
from employee
where title = 'President';
/* start with top manager */
print "President", topmanager
call printorg(1, "President");
closedb:
exec sql disconnect;
/* This subroutine retrieves and displays employees who report to a given manager. This subroutine is called recursively to determine if a given employee is also a manager and if so, it will display who reports to them.
*/
subroutine printorg(level, manager)
level integer;
exec sql begin declare section;
manager character_string(32)
ename character_string(32)
title character_string(20);
exec sql end declare section;
/* set cursor name to 'c1', 'c2', … */
cname = 'c' + level
exec sql declare :cname cursor for
select ename, title, manager from employee
where manager = :manager
order by ename;
exec sql whenever not found goto closec;
exec sql open :cname;
loop
exec sql fetch :cname into :ename, :title,
:manager;
/* Print employee's name and title */
print title, ename
/* Find out who (if anyone) reports to this
employee */
printorg(level+1, ename);
end loop
closec:
exec sql close :cname;
|
|
|
A select loop is a block of code associated with an embedded select statement; the select loop processes the rows returned by the select. Select loops are typically used when the select returns more than one row.
The select loop is an enhancement of standard SQL. ANSI SQL does not allow more than one row to be retrieved by a select statement. If multiple rows are to be retrieved, the ANSI standard requires the use of a cursor even if the rows are not updated.
Cursors enable an application to retrieve and manipulate individual rows without the restriction of the select loop. Within a select loop, statements cannot be issued that access the database. Use cursors in the following situations:
The following two examples do the same thing. The first example uses a select loop and the second uses a cursor. Because there are no nested updates and only one result table is being processed, the select method is preferred.
//Select Loop Version
exec sql select ename, eno, sal
into :name, :empnum, :salary
from employee
order by ename;
exec sql begin;
print name, salary, empnum;
exec sql end;
//Cursor Version
exec sql declare c1 cursor for
select ename, eno, sal/* No INTO clause */
from employee
order by ename;
exec sql open c1;
exec sql whenever not found goto closec1;
loop while more rows
exec sql fetch c1 into :name, :salary, :empnum;
print name, salary, empnum;
end loop;
closec1:
exec sql close c1;