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.
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 – 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.
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.
5 – LIST_CURSOR
Specifies a cursor that is used to manipulate columns of the data
type LIST OF BYTE VARYING.
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.
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.
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.
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).
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.
11 – READ_ONLY
Specifies that the cursor is not used to update the database.
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.
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.
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.
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.
16 – WITH_HOLD
Indicates that the cursor remain open and maintain its position
after the transaction ends. This is called a holdable cursor.