The following table shows the execution characteristics of static and dynamic SQLs.

Table 3-4 Execution characteristics of static and dynamic SQLs

#: Processing efficiency improves when an SQL having the same character string is executed several times.

When static SQL statements are executed, values to be inserted, new values to be set, and search conditions can be modified. When dynamic SQL statements are executed, any part of the SQL statements, such as the table name, column names, and conditional expressions, can be changed, in addition to those values that can be changed during execution of static SQL statements.

The following examples show values that can be changed during execution of static and dynamic SQL statements. Bold letters indicate the areas where values can be changed.

Figure 3-2 Example of values provided at the time of SQL execution

A dynamic SQL provides more flexibility in changing search conditions than a static SQL. However, dynamic SQL statements must be executed each time a condition is changed. For this reason, execution efficiency (processing efficiency) must be considered when deciding whether or not to use a dynamic SQL.

(a) Preprocessing and executing dynamic SQL statements

Dynamic SQL statements need to be processed first by the PREPARE statement and then executed during UAP execution. How a dynamic SQL statement is executed depends on whether the SQL statement to be preprocessed is the dynamic SELECT statement or another statement. If the SQL statement to be preprocessed is the SELECT statement, it is executed with the OPEN, FETCH, and CLOSE statements. If the SQL statement to be preprocessed is a statement other than the dynamic SELECT statement, it is executed with the EXECUTE statement. The EXECUTE IMMEDIATE statement can also be used to both preprocess and execute an SQL statement in a single operation. When the same SQL statement is to be executed dynamically by changing values, ? parameters should be used so that the SQL statement is preprocessed only once, rather than having to preprocess the SQL statement several times; the SQL statement can then be executed repeatedly by changing the values that are assigned to the ? parameters. This results in improved performance (processing efficiency). For details about ? parameters, see the HiRDB Version 9 SQL Reference manual.

Figure 3-3 shows the dynamic SQL execution mode, and Table 3-5 lists the SQLs that can be preprocessed by the PREPARE statement and the SQL statements that can be preprocessed and executed by the EXECUTE IMMEDIATE statement.

Figure 3-3 Dynamic SQL execution mode

#: XCMND declares any embedded variables in the embedded variable SQL declaration section. For details about embedded variables, see the HiRDB Version 9 SQL Reference manual.

Table 3-5 SQL statements preprocessed by the PREPARE statement, and SQL statements preprocessed and executed by the EXECUTE IMMEDIATE statement

Type	SQL statement	PREPARE	EXECUTE IMMEDIATE
Data Manipulation SQL	ASSIGN LIST statement	U#3	U
	CALL	U#3	U
	DELETE#1	U#3	U
	Preparable dynamic DELETE statement: locating	U	U
	DROP LIST statement	U#3	U
	INSERT	U#3	U
	PURGE TABLE	U#3	--
	Single-row SELECT#2	U#3	U
	Dynamic SELECT	U#4	--
	UPDATE#1	U#3	U
	Preparable dynamic UPDATE statement: locating	U	U
	Assignment statement	U#3	--
Control SQL	CALL COMMAND	U	U
	COMMIT	--	--
	CONNECT	--	--
	DISCONNECT	--	--
	LOCK TABLE	U#3	U
	ROLLBACK	--	--
	SET SESSION AUTHORIZATION statement	--	--
Definition SQL	ALLOCATE MEMORY TABLE	U#3	U
	ALTER INDEX	U#3	U
	ALTER PROCEDURE	U#3	U
	ALTER ROUTINE	U#3	U
	ALTER TABLE	U#3	U
	ALTER TRIGGER	U#3	U
	COMMENT	U#3	U
	CREATE AUDIT	U#3	U
	CREATE CONNECTION SECURITY	U#3	U
	CREATE FUNCTION	U#3	U
	CREATE INDEX	U#3	U
	CREATE PROCEDURE	U#3	U
	CREATE SCHEMA	U#3	U
	CREATE SEQUENCE	U#3	U
	CREATE TABLE	U#3	U
	CREATE TRIGGER	U#3	U
	CREATE TYPE	U#3	U
	CREATE VIEW	U#3	U
	DEALLOCATE MEMORY TABLE	U#3	U
	DROP AUDIT	U	U
	DROP CONNECTION SECURITY	U#3	U
	DROP DATA TYPE	U#3	U
	DROP FUNCTION	U#3	U
	DROP INDEX	U#3	U
	DROP PROCEDURE	U#3	U
	DROP SCHEMA	U#3	U
	DROP SEQUENCE	U#3	U
	DROP TABLE	U#3	U
	DROP TRIGGER	U#3	U
	DROP VIEW	U#3	U
	GRANT	U#3	U
	REVOKE	U#3	U

U: Can be used.

--: Cannot be used.

Note

An SQL statement that contains embedded variables cannot be executed dynamically; in this case, ? parameters must be used instead of embedded variables. For details about ? parameters, see the HiRDB Version 9 SQL Reference manual.

#1: Operations requiring the use of a cursor cannot be performed.

#2: The SQL must not contain an INTO clause.

#3: Executed by the EXECUTE statement.

#4: Executed by the OPEN, FETCH, or CLOSE statement.

An example of inserting data into a dynamically-specified table is shown as follows:

Figure 3-4 Example of inserting data into a dynamically specified table

(b) Using the EXECUTE statement and the EXECUTE IMMEDIATE statement

The EXECUTE IMMEDIATE statement is functionally equivalent to executing the PREPARE and EXECUTE statements in succession. When SQL statements are to be executed repeatedly, it is more efficient to execute it iteratively using the EXECUTE statement after first preprocessing it with the PREPARE statement than to execute it several times with the EXECUTE IMMEDIATE statement.

(c) Executing dynamic SQL statements with preprocessing a dynamic SELECT statement

This execution mode varies depending on whether the SQL statement to be preprocessed is a dynamic SELECT statement or a statement other than the dynamic SELECT statement. If the SQL statement to be preprocessed is a dynamic SELECT statement, the SQL statements after preprocessing should be executed using the OPEN, FETCH, or CLOSE statement; if it is not a dynamic SELECT statement, an EXECUTE statement should be used. An example of executing SQL statements with processing a dynamic SELECT statement is shown as follows:

Figure 3-5 Example of dynamic processing when the preprocessed SQL is a dynamic SELECT statement

(d) Dynamic execution of an SQL statement that uses a cursor for a dynamic SELECT statement

When a dynamic SELECT statement is preprocessed and an SQL statement that uses a cursor is executed dynamically for that dynamic SELECT statement, a cursor declared in a cursor declaration is not used. In this case, a cursor allocated with the ALLOCATE CURSOR statement is used for the preprocessed dynamic SELECT statement. An example of dynamic execution of an SQL statement that uses a cursor for a dynamic SELECT statement is shown below.

 
PREPARE GLOBAL :SEL FROM :XCMND;
//Adds an extended statement name (:SEL='SEL1') to the dynamic SELECT statement that was set to an embedded variable (:XCMND).
ALLOCATE GLOBAL :CR CURSOR FOR GLOBAL :SEL;
//Allocates a cursor (:CR='CR1') to the query identified by the extended statement name (:SEL='SEL1').
PREPARE UPD1 FROM
  'UPDATE SET C1=? WHERE CURRENT OF GLOBAL CR1';
//Preprocesses the UPDATE statement that uses the cursor (CR1) and attaches an SQL statement identifier (UPD1).
OPEN GLOBAL :CR;
//Adds a cursor (:CR='CR1').
FETCH GLOBAL :CR INTO :XKEKKA;
//Reads the search results obtained using the cursor (:CR='CR1') into an embedded variable (:XKEKKA).
EXECUTE UPD1 USING :XDATA;
//Executes the UPDATE statement for the preprocessed SQL statement identifier (UPD1). At this time, the embedded variable (:XDATA) corresponding to the ? parameter is specified.
CLOSE GLOBAL :CR;
//Closes the cursor (:CR='CR1').
 

(e) Receiving information determined during dynamic SQL execution

When a UAP dynamically executes SQL statements, it uses an SQL Descriptor Area as the area for notifying HiRDB about information determined during the execution (including the number, attributes, and addresses of data transfer areas). To realize dynamic execution, the UAP receives search item information for SQL statements preprocessed with the PREPARE statement in the SQL Descriptor Area by using one of the following methods:

• Executing the DESCRIBE statement
• Specifying OUTPUT and INPUT when executing the PREPARE statement. (When this method is used, the number of communications might be reduced, because information can be received during execution of the PREPARE statement.)

For details about the DESCRIBE statement, see the manual HiRDB Version 9 SQL Reference. For an example of the use of SQL Descriptor Areas, see B. SQL Descriptor Area.