Dynamic SQL v10
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.
Note, however, that the runtime performance of dynamic SQL will be slower than static SQL.
EXECUTE IMMEDIATE command is used to run SQL commands dynamically.
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
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
USING clauses. Note the last execution of the
SELECT command returns the results into a record instead of individual variables.
The following is the output from the previous anonymous block:
You can use the
BULK COLLECT clause to assemble the result set from an
EXECUTE IMMEDIATE statement into a named collection. See Using the BULK COLLECT Clause,
EXECUTE IMMEDIATE BULK COLLECT for information about using the
BULK COLLECT clause.