8. Dynamic SQL#

This chapter describes how to use dynamic SQL in stored procedures and functions.

Overview#

With dynamic SQL, the user can create queries as desired at runtime and then execute them. In static execution, which is the standard way to execute SQL statements in stored procedures, an execution plan for all SQL statements in a stored procedure is created when the stored procedure is executed for the first time. Using dynamic SQL is the only way to execute SQL statements that did not exist when the stored procedure was compiled.

Executing Dynamic SQL#

The following diagram compares the tasks involved in executing static vs. dynamic SQL statements in stored procedures.

dynamic_sql

[Figure 8-1] Execution of Static SQL vs. Dynamic SQL

[In Figure 8-1, the stored procedure on the left processes the 'DELETE FROM T1' statement statically, whereas the stored procedure on the right uses the EXECUTE IMMEDIATE statement to processes the same DELETE statement dynamically at runtime.

For the stored procedure on the left side, an execution plan for the DELETE statement is created at the time point that the procedure is executed for the first time, stored in the Plan Cache, queried and executed on repeated invocations. Likewise, an execution plan for the DELETE statement is created at the time point that the procedure is executed for the first time and stored in the Plan Cache for the stored procedure on the right side as well.

Features#

The advantage of dynamic SQL is that it allows the user to freely change SQL statements as desired during runtime. Furthermore, the user can execute almost any type of SQL statement, as long as it is supported by the DBMS.

Dynamic SQL is useful in the following cases:

When the name of the table to be queried can vary during runtime
When it is appropriate to change a query hint depending on the circumstances, or when it is necessary to change a conditional operator for a condition clause
When SQL statements that are used in stored procedures and functions need to be optimized frequently due to the frequent execution of DDL and DML statements
When it is necessary to frequently execute SQL statements for which the execution cost exceeds the optimization cost. When it is desired to create versatile, reusable stored procedures

However, in some situations, using dynamic SQL may realize lower performance than using static SQL. This is attributable to the high cost of creating and deleting statements and binding variables to them. Although the use of dynamic SQL statements permits greater flexibility when designing applications, it may result in reduced performance.

EXECUTE IMMEDIATE#

This statement is used to dynamically execute a DDL, DCL or DML statement, including a SELECT query that returns a single record.

Syntax#

Description#

dynamic_string#

This is the string containing the query statement to be executed.

INTO#

The optional INTO clause indicates the variables in which to store the retrieved result set, in the same manner as a SELECT … INTO statement.

bulk_collect_clause#

BULK COLLECT clause retrieves the execution results of the SELECT statement at once. The array of host variables corresponding to the number of columns in the array of host variable should come immediately after the INTO clause in order to store the records returned by the SELECT statement.

Retrieving the result set of a query as arrays by using the BULK COLLECT clause is much more effective, rather than retrieving the result row by using the LOOP statement at a time.

USING#

The optional USING clause is used to specify parameters to bind to the SQL statement at runtime. The parameters are bound to the statement at the positions indicated by question marks ("?") in the order in which they appear. IN, OUT and IN/OUT parameters can all be specified.

Example#

The following is an example of the use of dynamic SQL to execute a DML statement.

CREATE PROCEDURE fire_emp(v_emp_id INTEGER) AS
BEGIN
  EXECUTE IMMEDIATE
  'DELETE FROM employees WHERE eno = ?'
    USING v_emp_id;
END;
/

CREATE PROCEDURE insert_table (
      table_name  VARCHAR(100),
      dept_no     NUMBER,
      dept_name   VARCHAR(100),
      location    VARCHAR(100))
AS
      stmt    VARCHAR2(200);
BEGIN
   stmt := 'INSERT INTO ' || table_name ||
           ' values (?, ?, ?)';
   EXECUTE IMMEDIATE stmt
           USING dept_no, dept_name, location;
END;
/

The syntax "EXECUTE IMMEDIATE dynamic_string" is used to execute a query in Direct-Execute mode. The variables that follow USING are binding parameters. In addition to DML statements, DDL and DCL statements can also be executed using EXECUTE IMMEDIATE.

Restrictions#

The following SQL statements are supported for execution as dynamic SQL in stored procedures:

DML
SELECT, INSERT, UPDATE, DELETE, MOVE, MERGE, LOCK TABLE, ENQUEUE, DEQUEUE
DDL
CREATE, ALTER, DROP
DCL
ALTER SYSTEM, ALTER SESSION, COMMIT, ROLLBACK

The following statements are not supported for use with dynamic SQL:

Statements that can only be executed from iSQL
SELECT * FROM tab;
DESC table_name
SET TIMING
SET AUTOCOMMIT
CONNECT
DISCONNECT

OPEN FOR#

This statement is used to initialize a cursor variable (REF CURSOR), execute the query, and determine the result set, so that data can be retrieved using the FETCH statement or can be passed to a client using stored procedure parameters. The USING clause is used to bind parameters.