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