Opens a cursor so that rows of its result table can be retrieved
through FETCH statements. The OPEN statement places the cursor
before the first row of its result table.
1 – Environment
You can use the OPEN statement:
o In interactive SQL
o Embedded in host language programs to be precompiled
o As part of a procedure in an SQL module
2 – Format
OPEN -+-> <cursor-name> ++---------------->----------------------+->
+-> <parameter> --++-> USING --+-> <parameter> -----------++
| +-> <qualified-parameter> -+|
| +------ , <----------------+|
+-> USING DESCRIPTOR <descriptor-name> -+
3 – Arguments
3.1 – cursor-name
Specifies the name of the cursor you want to open. Use a
parameter if the cursor referred to by the cursor name was
declared at run time with a dynamic DECLARE CURSOR statement.
Specify the parameter used for the cursor name in the extended
dynamic DECLARE CURSOR statement.
You can use a parameter to refer to the cursor name only when the
OPEN statement refers to an extended dynamic cursor.
3.2 – parameter
Specifies the name of the cursor you want to open. Use a
parameter if the cursor referred to by the cursor name was
declared at run time with a dynamic DECLARE CURSOR statement.
Specify the parameter used for the cursor name in the extended
dynamic DECLARE CURSOR statement.
You can use a parameter to refer to the cursor name only when the
OPEN statement refers to an extended dynamic cursor.
3.3 – USING
Syntax options:
USING parameter
USING qualified-parameter
USING DESCRIPTOR descriptor-name
Specifies in dynamic SQL parameters (host language variables in
a precompiled OPEN statement or formal parameters in an OPEN
statement that is part of an SQL module language procedure)
or qualified parameters (structures) whose values SQL uses to
replace parameter markers in a prepared SELECT statement named
in the cursor declaration. These parameters are not for use in
interactive SQL. SQL replaces the parameter markers with the
values of the host language variables when it evaluates the
SELECT statement of the cursor. See the Oracle Rdb SQL Reference
Manual for more information on the SQL module language and the
SQL precompiler, respectively.
You must specify the USING clause when both of the following
conditions exist:
o The declaration of the cursor you are opening specifies a
prepared SELECT statement name.
o The statement string for the prepared SELECT statement
includes parameter markers.
SQL does not allow the USING clause in an OPEN statement for a
cursor that is not based on a prepared SELECT statement. For
more information on parameter markers, see the PREPARE statement,
and the chapter on dynamic SQL in the Oracle Rdb Guide to SQL
Programming.
There are two ways to specify parameters in a USING clause:
o With a list of parameters. The number of parameters in the
list must be the same as the number of parameter markers in
the prepared SELECT statement. (If any of the parameters in
an OPEN statement is a host structure, SQL counts the number
of variables in that structure when it compares the number of
parameters in the USING clause with the number of parameter
markers in the prepared SELECT statement.)
o With the name of a descriptor that corresponds to an SQLDA.
Specify the name of the descriptor in the USING DESCRIPTOR
clause. If you use the INCLUDE statement to insert the SQLDA
into your program, the descriptor name is simply SQLDA.
The SQLDA is a collection of variables used only in dynamic
SQL. In an OPEN statement, the SQLDA points to a number
of host language variables with which SQL replaces the
parameter markers in a prepared SELECT statement. The number
of variables must match the number of parameter markers.
The data types of host language variables must be compatible with
the values of the corresponding column of the cursor row.
4 – Examples
Example 1: Opening a cursor declared in a PL/I program
This program fragment uses embedded DECLARE CURSOR, OPEN, and
FETCH statements to retrieve and print the name and department of
managers. The OPEN statement places the cursor at the beginning
of rows to be fetched.
/* 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: */
DO WHILE (SQLCODE = 0);
/* Retrieve the rows of the cursor
and put the value in host language variables: */
EXEC SQL FETCH MANAGER INTO :FNAME, :LNAME, :DNAME;
/* Print the values in the variables: */
.
.
.
END;
/* Close the cursor: */
EXEC SQL CLOSE MANAGER;
Example 2: Opening a cursor to insert list data
The following interactive SQL example uses cursors to add a new
row to the RESUMES table of the sample personnel database:
SQL> DECLARE TBLCURSOR INSERT ONLY TABLE CURSOR FOR
cont> SELECT EMPLOYEE_ID, RESUME FROM RESUMES;
SQL> DECLARE LSTCURSOR INSERT ONLY LIST CURSOR FOR
cont> SELECT RESUME WHERE CURRENT OF TBLCURSOR;
SQL> OPEN TBLCURSOR;
SQL> INSERT INTO CURSOR TBLCURSOR (EMPLOYEE_ID)
cont> VALUES ("00167");
1 row inserted
SQL> OPEN LSTCURSOR;
SQL> INSERT INTO CURSOR LSTCURSOR
cont> VALUES ("This is the resume for 00167");
SQL> INSERT INTO CURSOR LSTCURSOR
cont> VALUES ("Boston, MA");
SQL> INSERT INTO CURSOR LSTCURSOR
cont> VALUES ("Oracle Corporation");
SQL> CLOSE LSTCURSOR;
SQL> CLOSE TBLCURSOR;
SQL> COMMIT;