Declares an extended dynamic cursor. An extended dynamic DECLARE
CURSOR statement is a DECLARE CURSOR statement in which both the
cursor name and the SELECT statement are supplied in parameters
at run time.
See the DECLARE CURSOR for a detailed description of statement
elements that apply to both dynamic and nondynamic DECLARE CURSOR
statements.
1 – Environment
You can use the extended dynamic DECLARE CURSOR statement:
o Embedded in host language programs to be precompiled
o As part of a procedure in an SQL module
2 – Format
DECLARE <cursor-name-parameter> ---------------+
+----------------------------------------------+
++---+----------------+-> TABLE CURSOR -+----------------++
| +-> INSERT ONLY -+ +-> with-clause -+|
| +-> READ ONLY ---+ |
| +-> UPDATE ONLY -+ |
| +------------------------------------------------------+
| +--> FOR ---> <statement-id-parameter> ------------+------->
+-+--------------++----------++-> LIST CURSOR FOR -+ |
+-> READ ONLY -++> SCROLL -+| | |
+-> INSERT ONLY ------------+ | |
+--------------------------------------------------+ |
+-> <statement-id-parameter> ------------------------+
with-clause =
---> WITH --> HOLD -+-------------------------------+->
+-> PRESERVE --+-> ON COMMIT ---+
+-> ON ROLLBACK -+
+-> ALL ---------+
+-> NONE --------+
3 – Arguments
3.1 – cursor-name-parameter
Contains the name of the cursor you want to declare. Use a
character string parameter to hold the cursor name that the
program supplies at run time.
3.2 – FOR statement id parameter
A parameter that contains an integer that identifies a prepared
SELECT statement. Use an integer parameter to hold the statement
identifier that SQL generates and assigns to the parameter when
SQL executes a PREPARE statement.
3.3 – INSERT_ONLY
Specifies that a new list or a new row is created or opened.
3.4 – LIST_CURSOR_FOR
Specifies that you are declaring a cursor to access the elements
in a list.
3.5 – 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.
3.6 – READ_ONLY
Specifies that the cursor is not used to update the database.
3.7 – SCROLL
Specifies that Oracle Rdb can read the items in a list from
either direction (up or down) or at random.
3.8 – TABLE_CURSOR_FOR
Specifies that you are declaring a cursor to access the rows in a
table.
3.9 – UPDATE_ONLY
Specifies that the cursor is used to update the database.
3.10 – WITH_HOLD
Indicates that the cursor remain open and maintain its position
after the transaction ends. This is called a holdable cursor.
4 – Example
Example 1: Using parameters for statement and cursor names
The following example shows two procedures from the online sample
program SQL$MULTI_STMT_DYN.SQLADA. These procedures show the use
of parameters for statement and cursor names.
.
.
.
-- This procedure prepares a statement for dynamic execution from the string
-- passed to it. This procedure can prepare any number of statements
-- because the statement is passed to it as the parameter, cur_procid.
procedure PREPARE_SQL is
CUR_CURSOR : string(1..31) := (others => ' ');
CUR_PROCID : integer := 0;
CUR_STMT : string(1..1024) := (others => ' ');
begin
-- Allocate separate SQLDAs for parameter markers (sqlda_in) and select list
-- items (sqlda_out). Assign the value of the constant MAXPARMS (set in the
-- declarations section) to the SQLN field of both SQLDA structures. SQLN
-- specifies to SQL the maximum size of the SQLDA.
sqlda_in := new sqlda_record;
sqlda_in.sqln := maxparms;
sqlda_out := new sqlda_record;
sqlda_out.sqln := maxparms;
-- Assign the SQL statement that was constructed in the procedure
-- CONSTRUCT_SQL to the variable cur_stmt.
cur_stmt := sql_stmt;
-- Use the PREPARE...SELECT LIST statement to prepare the dynamic statement
-- and write information about any select list items in it to sqlda_out.
-- It prepares a statement for dynamic execution from the string passed to
-- it. It also writes information about the number and data type of any
-- select list items in the statement to an SQLDA (specifically, the
-- sqlda_out SQLDA specified).
--
-- Note that the PREPARE statement could have prepared the statement without
-- writing to an SQLDA. Instead, a separate DESCRIBE...SELECT LIST statement
-- would have written information about any select list items to an SQLDA.
EXEC SQL PREPARE :cur_procid SELECT LIST INTO :sqlda_out FROM :cur_stmt;
case sqlca.sqlcode is
when sql_success => null;
when others => raise syntax_error;
end case;
-- Use the DESCRIBE...MARKERS statement to write information about any
-- parameter markers in the dynamic statement to sqlda_in. This statement
-- writes information to an SQLDA (specifically, the sqlda_in SQLDA
-- specified) about the number and data type of any parameter markers in
-- the prepared dynamic statement. Note that SELECT statements may also
-- have parameter markers.
EXEC SQL DESCRIBE :cur_procid MARKERS INTO sqlda_in;
case sqlca.sqlcode is
when sql_success => null;
when others => raise syntax_error;
end case;
-- If the operation is "Read," create a unique name for the cursor name
-- so that the program can pass the cursor name to the dynamic DECLARE
-- CURSOR statement.
if cur_op(1) = 'R' then
cur_cursor(1) := 'C';
cur_cursor(2..name_strlng) := cur_name(1..name_strlng - 1);
-- Declare the dynamic cursor.
EXEC SQL DECLARE :cur_cursor CURSOR FOR :cur_procid;
case sqlca.sqlcode is
when sql_success => null;
when others => raise syntax_error;
end case;
end if;
number_of_procs := number_of_procs + 1;
sqlda_in_array(number_of_procs) := sqlda_in;
sqlda_out_array(number_of_procs) := sqlda_out;
procedure_names(number_of_procs) := cur_name;
procedure_ids(number_of_procs) := cur_procid;
if cur_op(1) = 'R' then
cursor_names(number_of_procs) := cur_cursor;
end if;
exception
when syntax_error =>
sql_get_error_text(get_error_buffer,get_error_length);
put_line(get_error_buffer(1..integer(get_error_length)));
put("Press RETURN to continue. ");
get_line(terminal,release_screen,last);
new_line;
end PREPARE_SQL;
.
.
.
begin -- procedure body DISPLAY_DATA
-- Before displaying any data, allocate buffers to hold the data
-- returned by SQL.
--
allocate_buffers;
-- Allocate and assign SQLDAs for the requested SQL procedure.
--
sqlda_in := new sqlda_record;
sqlda_in := sqlda_in_array(stmt_index);
sqlda_out := new sqlda_record;
sqlda_out := sqlda_out_array(stmt_index);
cur_cursor := cursor_names(stmt_index);
-- Open the previously declared cursor. The statement specifies
-- an SQLDA (specifically, sqlda_in) as the source of addresses for any
-- parameter markers in the cursor's SELECT statement.
--
EXEC SQL OPEN :cur_cursor USING DESCRIPTOR sqlda_in;
case sqlca.sqlcode is
when sql_success => null;
when others => raise unexpected_error;
end case;
-- Fetch the first row from the result table. This statement fetches a
-- row from the opened cursor and writes it to the addresses specified
-- in an SQLDA (specifically, sqlda_out).
--
EXEC SQL FETCH :cur_cursor USING DESCRIPTOR sqlda_out;
case sqlca.sqlcode is
-- Check to see if the result table has any rows.
when sql_success => null;
when stream_eof =>
put_line("No records found.");
new_line;
when others => raise unexpected_error;
end case;
-- Set up a loop to display the first row, then fetch and display second
-- and subsequent rows.
rowcount := 0;
while sqlca.sqlcode = 0 loop
rowcount := rowcount + 1;
-- Execute the DISPLAY_ROW procedure.
display_row;
-- To only display 5 rows, exit the loop if the loop counter
-- equals MAXROW (coded as 5 in this program).
if rowcount = maxrows then exit; end if;
-- Fetch another row, exit the loop if no more rows.
EXEC SQL FETCH :cur_cursor USING DESCRIPTOR sqlda_out;
case sqlca.sqlcode is
when sql_success => null;
when stream_eof => exit;
when others => raise unexpected_error;
end case;
end loop;
-- Close the cursor.
EXEC SQL CLOSE :cur_cursor;
case sqlca.sqlcode is
when sql_success => null;
when others => raise unexpected_error;
end case;
exception
when unexpected_error =>
sql_get_error_text(get_error_buffer,get_error_length);
EXEC SQL ROLLBACK;
put_line("This condition was not expected.");
put_line(get_error_buffer(1..integer(get_error_length)));
put("Press RETURN to continue. ");
get_line(terminal,release_screen,last);
-- Stop and let the user look before returning.
skip;
put_line("Press RETURN to proceed. ");
get_line(terminal,release_screen,last);
end DISPLAY_DATA;