3.7 Dynamic SQL

Table of Contents Previous Next


3 Stored Procedure Language : 3.7 Dynamic SQL

Dynamic SQL is a technique that provides the ability to execute SQL commands that are not known until the commands are about to be executed. Up to this point, the SQL commands that have been illustrated in SPL programs have been static SQL - the full command (with the exception of variables) must be known and coded into the program before the program, itself, can begin to execute. Thus using dynamic SQL, the executed SQL can change during program runtime.
In addition, dynamic SQL is the only method by which data definition commands, such as CREATE TABLE, can be executed from within an SPL program.
The EXECUTE IMMEDIATE command is used to run SQL commands dynamically.
EXECUTE IMMEDIATE 'sql_expression;'
[ INTO { variable [, ...] | record } ]
[ USING expression [, ...] ]
sql_expression is a string expression containing the SQL command to be dynamically executed. variable receives the output of the result set, typically from a SELECT command, created as a result of executing the SQL command in sql_expression. The number, order, and type of variables must match the number, order, and be type-compatible with the fields of the result set. Alternatively, a record can be specified as long as the record’s fields match the number, order, and are type-compatible with the result set. When using the INTO clause, exactly one row must be returned in the result set, otherwise an exception occurs. When using the USING clause the value of expression is passed to a placeholder. Placeholders appear embedded within the SQL command in sql_expression where variables may be used. Placeholders are denoted by an identifier with a colon (:) prefix - :name. The number, order, and resultant data types of the evaluated expressions must match the number, order and be type-compatible with the placeholders in sql_expression. Note that placeholders are not declared anywhere in the SPL program – they only appear in sql_expression.
The following example shows basic dynamic SQL commands as string literals.
The following example illustrates the USING clause to pass values to placeholders in the SQL string.
The following example shows both the INTO and USING clauses. Note the last execution of the SELECT command returns the results into a record instead of individual variables.
You can use the BULK COLLECT clause to assemble the result set from an EXECUTE IMMEDIATE statement into a named collection. See Section 3.12.4, EXECUTE IMMEDIATE BULK COLLECT for information about using the BULK COLLECT clause.

3 Stored Procedure Language : 3.7 Dynamic SQL

Table of Contents Previous Next