Dynamic SQL is a technique that provides the ability to execute SQL statements that are not known until the statements are about to be executed.
Up to this point, the SQL statements that have been illustrated in SPL programs have been static SQL - the full statement (with the exception of variables) must be known and coded into the program before the program itself can begin to execute. Therefore, by using dynamic SQL, the executed SQL can change during program runtime.
In addition, dynamic SQL is the only method by which data definition statements, such as CREATE TABLE, can be executed from within an SPL program.
However, note that the runtime performance of dynamic SQL will be slower than static SQL.
The EXECUTE IMMEDIATE statement is used to run SQL statements dynamically:
EXECUTE IMMEDIATE 'sql_expression;'
[ INTO { variable [, ...] | record } ]
[ USING expression [, ...] ]
sql_expression is a string expression containing the SQL statement to be dynamically executed. variable receives the output of the result set typically from a SELECT statement. This statement is created as a result of executing the SQL statement 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 fields of the record match the number, order, and are type-compatible with the result set. When the INTO clause is used, exactly one row must be returned in the result set. Otherwise an exception occurs. When the USING clause is used, the value of expression is passed to a placeholder. Placeholders appear embedded within the SQL statement 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 statements as string literals:
DECLARE
v_sql VARCHAR2(50);
BEGIN
EXECUTE IMMEDIATE 'CREATE TABLE job (jobno NUMBER(3),' ||
' jname VARCHAR2(9))';
v_sql := 'INSERT INTO job VALUES (100, ''ANALYST'')';
EXECUTE IMMEDIATE v_sql;
v_sql := 'INSERT INTO job VALUES (200, ''CLERK'')';
EXECUTE IMMEDIATE v_sql;
END;
The following example illustrates the USING clause to pass values to placeholders in the SQL string:
DECLARE
v_sql VARCHAR2(50) := 'INSERT INTO job VALUES ' ||
'(:p_jobno, :p_jname)';
v_jobno job.jobno%TYPE;
v_jname job.jname%TYPE;
BEGIN
V_jobno: = 300;
v_jname := 'MANAGER';
EXECUTE IMMEDIATE v_sql USING v_jobno, v_jname;
v_jobno := 400;
v_jname := 'SALESMAN';
EXECUTE IMMEDIATE v_sql USING v_jobno, v_jname;
v_jobno := 500;
v_jname := 'PRESIDENT';
EXECUTE IMMEDIATE v_sql USING v_jobno, v_jname;
END;
The following example shows both the INTO and USING clauses. Note that the last execution of the SELECT statement returns the results into a record instead of individual variables.
DECLARE
v_sql VARCHAR2(60);
v_jobno job.jobno%TYPE;
v_jname job.jname%TYPE;
r_job job%ROWTYPE;
BEGIN
DBMS_OUTPUT.PUT_LINE('JOBNO JNAME');
DBMS_OUTPUT.PUT_LINE('----- -------');
v_sql := 'SELECT jobno, jname FROM job WHERE jobno = :p_jobno';
EXECUTE IMMEDIATE v_sql INTO v_jobno, v_jname USING 100;
DBMS_OUTPUT.PUT_LINE(v_jobno || ' ' || v_jname);
EXECUTE IMMEDIATE v_sql INTO v_jobno, v_jname USING 200;
DBMS_OUTPUT.PUT_LINE(v_jobno || ' ' || v_jname);
EXECUTE IMMEDIATE v_sql INTO v_jobno, v_jname USING 300;
DBMS_OUTPUT.PUT_LINE(v_jobno || ' ' || v_jname);
EXECUTE IMMEDIATE v_sql INTO v_jobno, v_jname USING 400;
DBMS_OUTPUT.PUT_LINE(v_jobno || ' ' || v_jname);
EXECUTE IMMEDIATE v_sql INTO r_job USING 500;
DBMS_OUTPUT.PUT_LINE(r_job.jobno || ' ' || r_job.jname);
END;
The following code is the output from the previous anonymous block:
JOBNO JNAME
----- -------
100 ANALYST
200 CLERK
300 MANAGER
400 SALESMAN
500 PRESIDENT
You can use the BULK COLLECT clause to assemble the result set from an EXECUTE IMMEDIATE statement into a named collection.