781.txt
=====================================================================
Using Cursors
=====================================================================
PRODUCT: R:BASE VERSION: 4.5+ or Higher
=====================================================================
CATALOG: Programming In R:BASE AREA : Logic & Data Manipulation
=====================================================================
A cursor is a valuable programming tool. It is a pointer to rows in a
table. A cursor lets you step through rows one by one, performing the
same action on each row. You can set a cursor to point to all the
rows in a table or to a subset of rows. A cursor is set using the
DECLARE CURSOR command.
The DECLARE CURSOR command does not work by itself, but is really a
sequence of commands. In addition to the DECLARE CURSOR, the OPEN and
FETCH commands are required. A WHILE loop is used to step through the
rows and perform the programmed action on each row. The CLOSE or DROP
command is used after the cursor has stepped through all the rows.
The basic sequence of commands for a cursor is as follows:
DECLARE c1 CURSOR FOR +
SELECT custid, company FROM customer
OPEN c1
FETCH c1 INTO vcustid1 ind1, vcompany ind2
WHILE SQLCODE <> 100 THEN
-- Place code for row by row actions here.
FETCH c1 INTO vcustid1 ind1, vcompany ind2
ENDWHILE
DROP CURSOR c1
The DECLARE CURSOR command names the cursor and defines the set of
rows. The cursor name is then used in the OPEN, FETCH, CLOSE, and
DROP commands that reference it. A cursor name can be up to 18
characters long and follows the same naming conventions as all
other names in R:BASE.
More than one cursor can be defined and open at a time. SELECT is
used in the DECLARE CURSOR to identify the rows to step through. The
SELECT part of a cursor declaration can point to rows from a single
table or from multiple tables, and can choose all or only some of the
columns from a table. You can use the GROUP BY clause as well as the
WHERE and ORDER BY clauses of SELECT.
The OPEN command initializes the cursor and tells R:BASE you are ready
to retrieve a row of data from the cursor. The OPEN command positions
the cursor at the first row of the set of data defined by the SELECT
in the cursor declaration.
The FETCH command retrieves a row of data into the specified
variables. The number of variables must match the number of columns
listed in the SELECT part of the DECLARE CURSOR command. Each variable
has a corresponding indicator variable, which tells if a NULL value
was retrieved. The list of variable pairs - data variable and
indicator variable - is separated by commas.
The FETCH command sets SQLCODE, the SQL error variable. If a row was
retrieved, SQLCODE is set to 0. After the last row is retrieved, FETCH
sets SQLCODE to 100 - no more data. Using SQLCODE as the condition for
the WHILE loop lets you easily retrieve and act on each successive
row. Placing a second FETCH command immediately before the ENDWHILE
command keeps fetching rows until the end of data is reached. Then
the loop exits.
Within the WHILE loop, place whatever commands are needed to operate on
each row. You can look up additional data, perform mathematical
calculations, update data, and so on. Other articles in this Exchange
demonstrate actions that can be performed each row using a cursor.
When the cursor completes and the WHILE loop is exited, the cursor is
dropped with the DROP CURSOR command. A cursor name must be dropped
before it can be declared again. DROP removes a cursor definition from
memory; to use the cursor again, it must be declared with the DECLARE
CURSOR command. CLOSE leaves a cursor definition in memory; to use the
cursor again, it is opened with the OPEN command. After a cursor has
been closed, an OPEN repositions the pointer at the first row of the
cursor definition. CLOSE is most often used with nested cursors, DROP
with individual cursors.
When a cursor is open, you can use a special WHERE clause option,
WHERE CURRENT OF cursorname. This WHERE clause works with the UPDATE,
DELETE, SET VAR, and SELECT commands to perform the specified action
on the row the cursor is currently pointing at. The DELETE deletes
the entire row; the SET VAR, SELECT, and UPDATE only operate on
columns included in the SELECT part of the DECLARE CURSOR command.
Note that not every cursor definition supports use of the WHERE
CURRENT OF cursorname.
You don't have to use the WHERE CURRENT OF cursorname in your WHERE
clause. You can use a WHERE clause that explicitly points to a row of
data using values stored in variables. The unique row identifier is
fetched into a variable, then that value is used to access rows in the
cursor table or other tables.
-- The special WHERE clause WHERE CURRENT OF
-- points to the current row of the cursor.
SELECT custid, company FROM customer +
WHERE CURRENT OF c1
UPDATE customer SET custid = (custid + 1000) +
WHERE CURRENT OF c1
DELETE FROM customer WHERE CURRENT OF c1
-- Alternatively, use an explicit WHERE
-- clause to access a row.
SELECT custid, company FROM customer +
WHERE custid = .vcustid
UPDATE customer SET custid = (custid + 1000) +
WHERE custid = .vcustid
DELETE FROM customer WHERE custid = .vcustid
This is the basic cursor structure. Other types of cursors and
cursor structures that are used are: multi-table cursors, non-
updatable cursors, nested cursors, resetable cursors, and scrolling
cursors. Each is briefly described below.
Multi-table Cursor
A multi-table cursor includes more than one table in the SELECT part
of the cursor declaration. You can link tables directly in the
DECLARE CURSOR command; you don't need to define a view to retrieve
data from more than one table. The DECLARE CURSOR command has the
full capabilities of the SELECT command to do multi-table queries.
As with the SELECT command itself, you list the columns to retrieve,
the tables to get the data from, then link the tables in the WHERE
clause. For example,
-- Select data from both the Customer
-- and Transmaster tables.
DECLARE C1 CURSOR FOR SELECT +
custid, company, transid, transdate, invoicetotal +
FROM customer, transmaster +
WHERE customer.custid = transmaster.custid
OPEN C1
-- The fetch retrieves all the specified columns
-- into variables.
FETCH C1 INTO vcustid1 ind1, vcompany ind2 +
vtransid ind3, vtransdate ind4, vinvoicetotal ind5
WHILE SQLCODE <> 100 THEN
-- Place code for row by row actions here.
-- An explicit WHERE clause must be used,
-- WHERE CURRENT OF is not supported with
-- multi-table cursors.
-- Get the next row
FETCH C1 INTO vcustid1 ind1, vcompany ind2 +
vtransid ind3, vtransdate ind4, vinvoicetotal ind5
ENDWHILE
DROP CURSOR C1
Notice that the basic structure of the cursor commands doesn't change.
You still declare the cursor, open it, fetch the first row, then use a
WHILE loop to step through each row. There is no limit to the number
of tables that can be included in a DECLARE CURSOR command. The tables
are joined together in the same way they are joined with a regular
SELECT command.
A multi-table cursor definition is a non-updatable cursor, however.
You cannot update the cursor directly by using WHERE CURRENT OF
cursorname. You must use explicit WHERE clauses to access the cursor
tables.
Non-updatable cursors
A non-updatable cursor is one that does not support use of the special
WHERE clause WHERE CURRENT OF cursorname. An explicit WHERE clause
must be used to access data in the tables.
A non-updatable cursor is a multi-table cursor, or a cursor that is
defined, for example, using the GROUP BY clause. The SELECT command
that defines the cursor rows does not allow the cursor to point back
to a single specific row in a table.
Non-updatable cursors are a very useful part of the DECLARE CURSOR
structure. Use the power of the SELECT command in the DECLARE CURSOR
declaration to dramatically improve the performance of a cursor. The
more work the cursor does, the less your program has to do and the
faster and more efficiently it will run.
When using a non-updatable cursor, make sure you fetch a unique row
identifier for use in WHERE clauses.
Nested cursors
A nested cursor involves two DECLARE CURSOR definitions. The second
cursor is dependent on the first and its cursor definition uses a
variable value fetched by the first cursor.
There is a specific structure recommended for nested cursors - a row
is retrieved from cursor one, then the matching rows in cursor two
are retrieved and stepped through. Then the next row is retrieved from
cursor one and its matching rows from cursor two are stepped through.
The process continues until all rows have been retrieved from cursor
one.
-- The DECLARE commands are done together
-- at the top of the program.
-- An OPEN cursor does not need to immediately
-- follow the corresponding DECLARE CURSOR command
SET VAR vcustid INTEGER
DECLARE c1 CURSOR FOR SELECT custid, company +
FROM customer ORDER BY company
-- The second cursor uses a variable in the
-- WHERE clause. This variable, vcustid, must be
-- defined earlier in the program.
-- The cursor retrieves rows for a single customer only
DECLARE c2 CURSOR FOR +
SELECT custid, contfname, contlname +
FROM contact WHERE custid = .vcustid
-- Cursor c1 is opened and the first row retrieved
-- from the Customer table
OPEN c1
FETCH c1 INTO vcustid1 ind1, vcompany ind2
WHILE SQLCODE <> 100 THEN
-- Cursor c2 is opened, it points to all the
-- rows in the Contact table that match the
-- custid fetched into vcustid by cursor c1.
OPEN c2
-- Get the first row from the contact table and step
-- through all matching rows.
FETCH c2 INTO vcustid1 ind1, vfirstname ind2, +
vlastname ind3
WHILE SQLCODE <> 100 THEN
-- Place code here to do row by row actions
--Get the next row for cursor c2
FETCH c2 INTO vcustid1 ind1, vfirstname ind2, +
vlastname ind3
ENDWHILE
-- After all the matching rows in the contact table
-- have been processed, close cursor c2 and get the
-- next row from the Customer table.
-- Cursor c2 is closed and not dropped because
-- the definition will be reused for the next
-- row from cursor c1.
CLOSE c2
-- Get the next row for cursor c1
FETCH c1 INTO vcustid1 ind1, vcompany ind2
ENDWHILE
-- Both cursors are dropped when all the rows
-- in the Customer table have been retrieved.
DROP CURSOR c2
DROP CURSOR c1
You can use the same WHILE loop condition, SQLCODE <> 100, for both
cursors. This works very well and there is no conflict between the
two loops. The relative FETCH command sets the value of SQLCODE.
Notice that the FETCH from cursor c2 is right before the ENDWHILE of
the inner WHILE loop ensuring that that FETCH command is the one
being tested by the WHILE loop. The FETCH from cursor c1 is right
before the ENDWHILE of the outer WHILE loop, which then continues
based on cursor c1. This placement of the DECLARE, OPEN, FETCH, WHILE,
and ENDWHILE statements will always work. Just make sure the ENDWHILE
is the next command after the FETCH.
With nested cursors, the inner cursor is closed and opened so that it
always references the matching rows from the outer cursor. An
alternative to opening and closing the inner cursor is to use the
RESET option on the OPEN command.
Resetable cursors
A DECLARE CURSOR can use a variable in its WHERE clause. Each time
the cursor is opened, the WHERE clause is reevaluated using the
current variable value and identifies a new set of data.
You can CLOSE and OPEN a defined cursor, or use the OPEN cursorname
RESET command. Don't use the CLOSE command if you place the RESET
option on the OPEN command. The RESET option automatically
reevaluates the variable value and identifies a new set of data for
the cursor.
OPEN cursorname RESET is commonly used with nested cursors. The second
cursor is dependent on a variable fetched by the first cursor. By
using RESET, you won't need to CLOSE the inner cursor each time.
Using the RESET option on OPEN is faster using than the OPEN, CLOSE
sequence of commands.
Scrolling cursors
Normally, cursors move through the data in one direction only, from
top to bottom. They move forward one-by-one through the set of
defined rows. Once a row has been accessed and passed over, you can't
get back to it. The rows can be ordered in the cursor definition -
the top to bottom order is not necessarily the table order.
In 4.5 Plus! the SCROLL keyword was added to the DECLARE CURSOR
command. Declaring a cursor as a scrolling cursor allows you to use
directional keywords with the FETCH command to move forwards and
backwards through the set of rows.
See "Scrolling Cursors" in this Exchange for more information about
using scrolling cursors.