Declares a cursor.
With cursors, the conditions that define the result table
are specified by the select expression in the DECLARE CURSOR
statement. SQL creates the result table when it executes an OPEN
statement. The result table for a cursor exists until a CLOSE,
COMMIT, or ROLLBACK statement executes, the program stops, or you
exit from interactive SQL. However, the result table can exist
across transactions if you define a holdable cursor. A holdable
cursor can remain open and retain its position when a new SQL
transaction begins.
Host language programs require cursors because programs must
perform operations one row or element at a time, and therefore
may execute statements more than once to process an entire result
table or list.
The scope of a cursor describes the portion of a module or
program where the cursor is valid. The extent of a cursor tells
how long it is valid. All cursors in SQL have the scope of the
entire module.
You can create three classes of cursors, depending on which
DECLARE CURSOR statement you use:
o The DECLARE CURSOR statement is executed immediately. A cursor
that you create with this statement, sometimes called a static
cursor, exists only within the scope and extent of its module.
Both the cursor name and SELECT statement are known to your
application at compile time.
o The dynamic DECLARE CURSOR statement is executed immediately.
The cursor name is known at compile time, and the SELECT
statement is determined at run time. You must supply a name
for the SELECT statement that is generated at run time. A
dynamic cursor exists within the scope of its module, but
its extent is the entire run of the program or image. For
information about the dynamic DECLARE CURSOR statement, see
the DECLARE Dynamic_CURSOR statement.
o The extended dynamic DECLARE CURSOR statement must be
precompiled or used as part of a procedure in an SQL module.
You must supply parameters for the cursor name and for the
identifier of a prepared SELECT statement that is generated at
run time. An extended dynamic cursor exists within the scope
and extent of the entire module. For information about the
extended dynamic DECLARE CURSOR statement, see the DECLARE
Extended_Dynamic_CURSOR statement.
Within each class, you can create two types of cursors:
o Table cursors are a method that SQL provides to access
individual rows of a result table. (A result table is a
temporary collection of columns and rows from one or more
tables or views.)
o List cursors are a method that SQL provides to access
individual elements in a list.
A list is an ordered collection of elements, or segments, of
the data type LIST OF BYTE VARYING. For more information about
the LIST OF BYTE VARYING data type, see the Data_Types HELP
topic.
List cursors enable users to scan through a very large data
structure from within a language that does not provide support
for objects of such size. Because lists exist as a set of
elements within a row of a table, a list cursor must refer
to a table cursor because the table cursor provides the row
context.
Cursors are further divided according to the modes of operations
that they can perform. Table cursors have four modes:
o Update cursors are the default table cursor. Rows are first
read and locked for SHARED READ or PROTECTED READ and then
later, when an UPDATE is performed, the rows are locked for
EXCLUSIVE access. If the table is reserved for EXCLUSIVE
access, the subsequent update lock is not required.
o Read-only cursors can be used to access row information from
a result table whenever you do not intend to update the
database. For example, you could use a read-only cursor to
fetch row and column information for display.
o Insert-only cursors position themselves on a row that has just
been inserted so that you can load lists into that row.
o Update-only cursors are used whenever you intend to modify
many rows in the result table. When the UPDATE ONLY option
is used, SQL uses a more aggressive lock mode that locks
the rows for EXCLUSIVE access when first read. This mode
avoids a lock promotion from SHARED READ or PROTECTED READ to
EXCLUSIVE access. It may, therefore, avoid deadlocks normally
encountered during the lock promotion.
List cursors have two modes:
o Read-only cursors are the default list cursor. They enable you
to read existing lists. By adding the SCROLL keyword to the
read-only list cursor clause, you enable Oracle Rdb to scroll
forward and backward through the list segments as needed.
o Insert-only cursors enable you to insert data into a list.
The following table lists the classes, types, and modes of
cursors that SQL provides.
Table 1-2 Classes, Types, and Modes of Cursors
Dynamic Extended Dynamic
DECLARE CURSOR DECLARE CURSOR DECLARE CURSOR
Table List Table List Table List
Insert- Insert- Insert- Insert- Insert- Insert-
only only only only only only
Read- Read- Read- Read- Read- Read-
only only only only only only
Update- Update- Update-
only only only
For example, you must declare an insert-only table cursor to
insert data into a table. If the table includes lists, use the
table cursor to position on the correct row, and declare an
insert-only list cursor to load the lists into that row. For
details about using cursors to load data into your database, see
the INSERT statement.
To process the rows of a result table formed by a DECLARE CURSOR
statement, you must use the OPEN statement to position the cursor
before the first row. Subsequent FETCH statements retrieve the
values of each row for display on the terminal or processing in a
program. (You must close the cursor before you attempt to reopen
it.)You can similarly process the elements of a list by using an
OPEN statement to position the cursor before the first element
in the list and repeating FETCH statements to retrieve successive
elements.
1 – Environment
You can use the DECLARE CURSOR statement:
o In interactive SQL
o Embedded in host language programs to be precompiled
o As part of the DECLARE section in an SQL module
o In a context file
2 – Format
DECLARE <cursor-name> --------------------+
+-----------------------------------------+
+-+-+----------------+--> TABLE CURSOR ---+----------------+-+
| +-> INSERT ONLY -+ +-> with-clause -+ |
| +-> READ ONLY ---+ |
| +-> UPDATE ONLY -+ |
| +--------------------------------------------------------+
| +-> FOR -> select-expr --+----------------------+--+
| +-> for-update-clause -+ |
| +---------------------------------------------+
| +-+--------------------+----------------------------+-->
| +-> optimize-clause -+ |
++---------------++---------+-+-> LIST CURSOR FOR SELECT + |
+> READ ONLY ---++> SCROLL + | | |
+> INSERT ONLY --------------+ | |
+--------------------------------------------------------+ |
+> <column-name> WHERE CURRENT OF <table-cursor-name> -----+
with-clause =
---> WITH --> HOLD -+-------------------------------+->
+-> PRESERVE --+-> ON COMMIT ---+
+-> ON ROLLBACK -+
+-> ALL ---------+
+-> NONE --------+
select-expr =
-+-+-> select-clause ------------+-+------+
| +-> ( select-expr ) -----------+ | |
| +-> TABLE table-ref ----------+ | |
+------ select-merge-clause <-------+ |
+------------------- <-------------------+
+-+--------------------+--+------------------+--+--------------------+->
+-> order-by-clause -+ +-> offset-clause -+ +-> limit-to-clause -+
for-update-clause =
--> FOR UPDATE -+-------------------------+->
+-+-> OF <column-name> -+-+
+-------- , <---------+
optimize-clause =
--+---------------------------------------------------------------+--->
+-> OPTIMIZE --+-+-> FOR -+-> FAST FIRST --------+----------+-+-+
| | +-> TOTAL TIME --------+ | |
| | +-> SEQUENTIAL ACCESS -+ | |
| +-> USING <outline-name> ------------------+ |
| +-> WITH -+-> DEFAULT --+-> SELECTIVITY -+ |
| | +-> SAMPLED --+ | |
| | +-> AGGRESSIVE + | |
| +-> AS <query-name> -----------------------+ |
+---------------- <----------------------------+
3 – Arguments
3.1 – cursor-name
Specifies the name of the cursor you want to declare. Use a name
that is unique among all the cursor names in the module. Use any
valid SQL name. See the User_Supplied_Names HELP topic. for more
information on user-supplied names.
You can use a parameter to specify the cursor name at run time
in an extended dynamic DECLARE CURSOR statement. See the DECLARE
Extended_Dynamic_CURSOR statement for more information on the
extended dynamic DECLARE CURSOR statement.
3.2 – FOR select expr
A select expression that defines which columns and rows of which
tables SQL includes in the cursor. See the Select_Expressions
HELP topic for more information on select expressions.
3.3 – FOR_UPDATE_OF
Specifies the columns in a cursor that you or your program might
later modify with an UPDATE statement. The column names in the
FOR UPDATE clause must belong to a table or view named in the
FROM clause.
You do not have to specify the FOR UPDATE clause of the DECLARE
CURSOR statement to later modify rows using the UPDATE statement:
o If you do specify a FOR UPDATE clause and later specify
columns in the UPDATE statement that are not in the FOR UPDATE
clause, SQL issues a warning message and proceeds with the
update modifications.
o If you do not specify a FOR UPDATE clause, you can update
any column using the UPDATE statement. SQL does not issue any
messages.
The FOR UPDATE OF clause in a SELECT statement provides UPDATE
ONLY CURSOR semantics by locking all the rows selected.
3.4 – INSERT_ONLY
Specifies that a new list or a new row is created or opened.
If you specify a list cursor but do not specify the INSERT ONLY
clause, SQL declares a read-only list cursor by default.
If you specify a table cursor but do not specify the INSERT ONLY
clause, SQL declares an update cursor by default.
When you specify an insert-only cursor, all the value expressions
in the select list must be read/write. When you declare an
insert-only table cursor to insert lists, you must specify both
table column and list column names in the FROM clause.
For more information about how to use insert-only cursors, see
the INSERT statement.
3.5 – LIST_CURSOR
Specifies a cursor that is used to manipulate columns of the data
type LIST OF BYTE VARYING.
3.6 – OPTIMIZE AS query name
Assigns a name to the query. You must define the SET FLAGS
'STRATEGY' statement to see the access methods used to produce
the results of the query.
3.7 – OPTIMIZE_FOR
The OPTIMIZE FOR clause specifies the preferred optimizer
strategy for statements that specify a select expression. The
following options are available:
o FAST FIRST
A query optimized for FAST FIRST returns data to the user as
quickly as possible, even at the expense of total throughput.
If a query can be cancelled prematurely, you should specify
FAST FIRST optimization. A good candidate for FAST FIRST
optimization is an interactive application that displays
groups of records to the user, where the user has the option
of aborting the query after the first few screens. For
example, singleton SELECT statements default to FAST FIRST
optimization.
If optimization strategy is not explicitly set, FAST FIRST is
the default.
o TOTAL TIME
If your application runs in batch, accesses all the records in
the query, and performs updates or writes a report, you should
specify TOTAL TIME optimization. Most queries benefit from
TOTAL TIME optimization.
The following examples illustrate the DECLARE CURSOR syntax
for setting a preferred optimization mode:
SQL> DECLARE TEMP1 TABLE CURSOR
cont> FOR
cont> SELECT *
cont> FROM EMPLOYEES
cont> WHERE EMPLOYEE_ID > '00400'
cont> OPTIMIZE FOR FAST FIRST;
SQL> --
SQL> DECLARE TEMP2 TABLE CURSOR
cont> FOR
cont> SELECT LAST_NAME, FIRST_NAME
cont> FROM EMPLOYEES
cont> ORDER BY LAST_NAME
cont> OPTIMIZE FOR TOTAL TIME;
o SEQUENTIAL ACCESS
Forces the use of sequential access. This is particularly
valuable for tables that use the strict partitioning
functionality.
3.8 – OPTIMIZE USING outline name
Explicitly names the query outline to be used with the select
expression even if the outline IDs for the select expression and
for the outline are different.
See the CREATE OUTLINE statement for more information on creating
an outline.
3.9 – OPTIMIZE_WITH
Selects one of three optimization controls: DEFAULT (as used by
previous versions of Oracle Rdb), AGGRESSIVE (assumes smaller
numbers of rows will be selected), and SAMPLED (which uses
literals in the query to perform preliminary estimation on
indices).
3.10 – preserve-clause
Syntax options:
PRESERVE ON COMMIT
PRESERVE ON ROLLBACK
PRESERVE ALL
PRESERVE NONE
Specifies when a cursor remains open.
o PRESERVE ON COMMIT
On commit, all cursors close except those defined with the
WITH HOLD PRESERVE ON COMMIT syntax. On rollback, all cursors
close including those defined with the WITH HOLD PRESERVE ON
COMMIT syntax.
This is the same as specifying the WITH HOLD clause without
any preserve options.
o PRESERVE ON ROLLBACK
On rollback, all cursors close except those defined with the
WITH HOLD PRESERVE ON ROLLBACK syntax. On commit, all cursors
close including those defined with the WITH HOLD PRESERVE ON
ROLLBACK syntax.
o PRESERVE ALL
All cursors remain open after commit or rollback. Cursors
close with the CLOSE statement or when the session ends.
o PRESERVE NONE
All cursors close after a CLOSE, COMMIT, or ROLLBACK
statement, when the program stops, or when you exit from
interactive SQL.
This is the same as not specifying the WITH HOLD clause at
all.
3.11 – READ_ONLY
Specifies that the cursor is not used to update the database.
3.12 – SCROLL
Specifies that Oracle Rdb can read the items in a list from
either direction (up or down) or at random. The SCROLL keyword
must be used if the following fetch options are desired:
o NEXT
o PRIOR
o FIRST
o LAST
o RELATIVE
o ABSOLUTE
If SCROLL is not specified, the default for FETCH is NEXT. SCROLL
is only supported for LIST cursors.
3.13 – TABLE_CURSOR
Specifies that the cursor you want to declare is a table cursor,
rather than a list cursor. If you do not specify a cursor type,
SQL declares a table cursor by default.
3.14 – UPDATE_ONLY
Specifies that the cursor is used to update the database.
Use an update-only cursor when you plan to update most of the
rows you are fetching. The update-only cursor causes Oracle
Rdb to apply more restrictive locking during the initial read
operation, so that locks do not need to be upgraded later from
READ to exclusive WRITE. This reduces the total number of lock
requests per query, and may help to avoid deadlocks.
Use update-only table cursors to modify table rows. SQL does not
allow update-only list cursors.
3.15 – WHERE_CURRENT_OF
Specifies the table cursor that provides the row context for
the list cursor. The table cursor named must be defined using a
DECLARE CURSOR statement.
3.16 – WITH_HOLD
Indicates that the cursor remain open and maintain its position
after the transaction ends. This is called a holdable cursor.
4 – Examples
Example 1: Declaring a table cursor in interactive SQL
The following example declares a cursor named SALARY_INFO. The
result table for SALARY_INFO contains the names and current
salaries of employees and is sorted by last name.
SQL> --
SQL> DECLARE SALARY_INFO CURSOR FOR
cont> SELECT E.FIRST_NAME, E.LAST_NAME, S.SALARY_AMOUNT
cont> FROM EMPLOYEES E, SALARY_HISTORY S
cont> WHERE E.EMPLOYEE_ID = S.EMPLOYEE_ID
cont> AND
cont> S.SALARY_END IS NULL
cont> ORDER BY
cont> E.LAST_NAME ASC;
SQL> --
SQL> -- Use an OPEN statement to open the cursor and
SQL> -- position it before the first row of the
SQL> -- result table:
SQL> OPEN SALARY_INFO;
SQL> --
SQL> -- Finally, use two FETCH statements to see the
SQL> -- first two rows of the cursor:
SQL> FETCH SALARY_INFO;
E.FIRST_NAME E.LAST_NAME S.SALARY_AMOUNT
Louie Ames $26,743.00
SQL> FETCH SALARY_INFO;
E.FIRST_NAME E.LAST_NAME S.SALARY_AMOUNT
Leslie Andriola $50,424.00
Example 2: Declaring a table cursor in a C program
This simple program uses embedded DECLARE CURSOR, OPEN, and FETCH
statements to retrieve and print the names and departments of
managers.
#include <stdio.h>
void main ()
{
int SQLCODE;
char FNAME[15];
char LNAME[15];
char DNAME[31];
/* Declare the cursor: */
exec sql
DECLARE MANAGER CURSOR FOR
SELECT E.FIRST_NAME, E.LAST_NAME, D.DEPARTMENT_NAME
FROM EMPLOYEES E, DEPARTMENTS D
WHERE E.EMPLOYEE_ID = D.MANAGER_ID ;
/* Open the cursor: */
exec sql
OPEN MANAGER;
/* Start a loop to process the rows of the cursor: */
for (;;)
{
/* Retrieve the rows of the cursor
and put the value in host language variables: */
exec sql
FETCH MANAGER INTO :FNAME, :LNAME, :DNAME;
if (SQLCODE != 0) break;
/* Print the values in the variables: */
printf ("%s %s %s\n", FNAME, LNAME, DNAME);
}
/* Close the cursor: */
exec sql
CLOSE MANAGER;
}
Example 3: Using table and list cursors to retrieve list data in
interactive SQL
The following example declares a table and list cursor to
retrieve list information:
SQL> DECLARE TBLCURSOR INSERT ONLY TABLE CURSOR FOR
cont> SELECT EMPLOYEE_ID, RESUME FROM RESUMES;
SQL> DECLARE LSTCURSOR INSERT ONLY LIST CURSOR FOR SELECT RESUME WHERE
CURRENT OF TBLCURSOR;
SQL> OPEN TBLCURSOR;
SQL> INSERT INTO CURSOR TBLCURSOR (EMPLOYEE_ID) VALUES ('00164');
1 row inserted
SQL> OPEN LSTCURSOR;
SQL> INSERT INTO CURSOR LSTCURSOR VALUES ('This is the resume for 00164');
SQL> INSERT INTO CURSOR LSTCURSOR VALUES ('Boston, MA');
SQL> INSERT INTO CURSOR LSTCURSOR VALUES ('Oracle Corporation');
SQL> CLOSE LSTCURSOR;
SQL> CLOSE TBLCURSOR;
SQL> COMMIT;
SQL> DECLARE TBLCURSOR2 CURSOR FOR SELECT EMPLOYEE_ID,
cont> RESUME FROM RESUMES;
SQL> DECLARE LSTCURSOR2 LIST CURSOR FOR SELECT RESUME WHERE
CURRENT OF TBLCURSOR2;
SQL> OPEN TBLCURSOR2;
SQL> FETCH TBLCURSOR2;
00164
SQL> OPEN LSTCURSOR2;
SQL> FETCH LSTCURSOR2;
RESUME
This is the resume for 00164
SQL> FETCH LSTCURSOR2;
RESUME
Boston, MA
SQL> FETCH LSTCURSOR2;
RESUME
Oracle Corporation
SQL> FETCH LSTCURSOR2;
RESUME
%RDB-E-STREAM_EOF, attempt to fetch past end of record stream
SQL> CLOSE LSTCURSOR2;
SQL> SELECT * FROM RESUMES;
EMPLOYEE_ID RESUME
00164 1:701:2
1 row selected
SQL> CLOSE TBLCURSOR2;
SQL> COMMIT;
Example 4: Using the scroll attribute for a list cursor
The following example declares a table and read-only scrollable
list cursor to retrieve list information by scrolling back and
forth between segments of the list:
SQL> DECLARE CURSOR_ONE
cont> TABLE CURSOR FOR
cont> (SELECT EMPLOYEE_ID,RESUME FROM RESUMES);
SQL> --
SQL> DECLARE CURSOR_TWO
cont> READ ONLY
cont> SCROLL
cont> LIST CURSOR
cont> FOR SELECT RESUME
cont> WHERE CURRENT OF CURSOR_ONE;
Example 5: Declaring a holdable cursor
SQL> -- Declare a holdable cursor that remains open on COMMIT
SQL> --
SQL> DECLARE curs1 CURSOR
cont> WITH HOLD PRESERVE ON COMMIT
cont> FOR SELECT e.first_name, e.last_name
cont> FROM employees e
cont> ORDER BY e.last_name;
SQL> OPEN curs1;
SQL> FETCH curs1;
FIRST_NAME LAST_NAME
Louie Ames
SQL> FETCH curs1;
FIRST_NAME LAST_NAME
Leslie Andriola
SQL> COMMIT;
SQL> FETCH curs1;
FIRST_NAME LAST_NAME
Joseph Babbin
SQL> FETCH curs1;
FIRST_NAME LAST_NAME
Dean Bartlett
SQL> ROLLBACK;
SQL> FETCH curs1;
%SQL-F-CURNOTOPE, Cursor CURS1 is not opened
SQL> --
SQL> -- Declare another holdable cursor that remains open always
SQL> --
SQL> DECLARE curs2 CURSOR
cont> WITH HOLD PRESERVE ALL
cont> FOR SELECT e.first_name, e.last_name
cont> FROM employees e
cont> ORDER BY e.last_name;
SQL> OPEN curs2;
SQL> FETCH curs2;
FIRST_NAME LAST_NAME
Louie Ames
SQL> FETCH curs2;
FIRST_NAME LAST_NAME
Leslie Andriola
SQL> COMMIT;
SQL> FETCH curs2;
FIRST_NAME LAST_NAME
Joseph Babbin
SQL> FETCH curs2;
FIRST_NAME LAST_NAME
Dean Bartlett
SQL> ROLLBACK;
SQL> FETCH curs2;
FIRST_NAME LAST_NAME
Wes Bartlett