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.