Nonstop Database, HiRDB Version 9 UAP Development Guide
SQL statements written directly into the user application program when it is created are called static SQL statements; SQL statements that are constructed during UAP execution instead of being written into UAP is called dynamic SQL statements.
Because the execution characteristics of static and dynamic SQLs are different, evaluate them carefully before you create a UAP.
The following table shows the execution characteristics of static and dynamic SQLs.
Table 3-4 Execution characteristics of static and dynamic SQLs
Type | Advantage | Disadvantage |
---|---|---|
Static SQL | If the UAP is to be executed repeatedly, an executed SQL statement is converted to execute form and can be used again in the shared memory, thus improving processing efficiency. | Because the SQL statements are embedded in the UAP, the ability to change search conditions is limited. |
Dynamic SQL | Because SQL statements are constructed during execution, it is easy to change search conditions. | The SQL statements must be analyzed and converted to execute form each time they are executed, resulting in poor processing efficiency.# |
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.
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
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 |
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
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.
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
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'). |
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:
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.
All Rights Reserved. Copyright (C) 2011, Hitachi, Ltd.