In order to make a cursor more dynamic, a SQL statement can contain parameter markers that indicate the values that will be bound to the actual parameter values at execute time. The question mark symbol (?) is used as a parameter marker.
For example:
EXEC SQL PREPARE sel_tabs SELECT table_name FROM sys_tables WHERE table_name LIKE ? AND table_schema LIKE ?;
The execution statement is adapted by including a USING keyword to accommodate the binding of a variable to the parameter marker.
EXEC SQL EXECUTE sel_tabs USING ( var1, var2 ) INTO (tabs);
In this way, a single cursor can be used multiple times without having to re-prepare the cursor. As preparing a cursor involves also the parsing and optimizing of the statement, significant performance gains can be achieved by using reusable cursors.
Note that the USING list only accepts variables; data can not be directly passed in this way. For example, if you want to insert data into a table where one column should always have the same value (status = 'NEW'), then the following syntax is incorrect:
EXEC SQL EXECUTE ins_tab USING (nr, desc, dat, 'NEW');
The correct way is to define the constant value in the prepare section:
Note that variables can be used multiple times in the USING list.
The parameters in an SQL statement have no intrinsic data type or explicit declaration. Therefore, parameter markers can be included in an SQL statement only if their data types can be inferred from another operand in the statement.
For example, in an arithmetic expression such as ? + COLUMN1, the data type of the parameter can be inferred from the data type of the named column represented by COLUMN1. A procedure cannot use a parameter marker if the data type cannot be determined.
The following table describes how a data type is determined for several types of parameters.
Location of parameter
Assumed data type
One operand of a binary arithmetic or comparison operator
Same as the other operand
The first operand in a BETWEEN clause
Same as the other operand
The second or third operand in a BETWEEN clause
Same as the first operand
An expression used with IN
Same as the first value or the result column of the subquery
A value used with IN
Same as the expression
A pattern value used with LIKE
VARCHAR
An update value used with UPDATE
Same as the update column
An application cannot place parameter markers in the following locations:
▪ as an SQL identifier (name of a table, name of a column and so on),
▪ in a SELECT list,
▪ as both expressions in a comparison-predicate,
▪ as both operands of a binary operator,
▪ as both the first and second operands of a BETWEEN operation,
▪ as both the first and third operands of a BETWEEN operation,
▪ as both the expression and the first value of an IN operation,
▪ as the operand of a unary + or - operation,
▪ as the argument of a set-function-reference.
For more information, see the ANSI SQL-92 specification.
In the following example, a stored procedure reads rows from one table and insert parts of them in another table by using multiple cursors:
"CREATE PROCEDURE tabs_in_schema (schema_nm VARCHAR) RETURNS (nr_of_rows INTEGER) BEGIN DECLARE tab_nm VARCHAR; EXEC SQL PREPARE sel_tab SELECT table_name FROM sys_tables WHERE table_schema = ?; EXEC SQL PREPARE ins_tab INSERT INTO my_table (table_name, schema) VALUES (?,?);
nr_of_rows := 0;
EXEC SQL EXECUTE sel_tab USING (schema_nm) INTO (tab_nm); EXEC SQL FETCH sel_tab; WHILE SQLSUCCESS LOOP nr_of_rows := nr_of_rows + 1; EXEC SQL EXECUTE ins_tab USING(tab_nm, schema_nm); IF SQLROWCOUNT <> 1 THEN RETURN SQLERROR OF ins_tab; END IF EXEC SQL FETCH sel_tab; END LOOP END";