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.