The SQL statements described in this chapter are classified as executable or nonexecutable. The Invocation section in the description of each statement indicates whether or not the statement is executable.
An executable statement can be invoked in four ways:
| Note: | Statements embedded in REXX are prepared and executed dynamically. |
Depending on the statement, some or all of these methods can be used. The Invocation section in the description of each statement tells which methods can be used.
A nonexecutable statement can only be embedded in an application program.
In addition to the statements described in this chapter, there is one more SQL statement construct: the select-statement. (See select-statement.) It is not included in this chapter because it is used differently from other statements.
A select-statement can be invoked in three ways:
The first two methods are called, respectively, the static and the dynamic invocation of select-statement.
The different methods of invoking an SQL statement are discussed below in more detail. For each method, the discussion includes the mechanism of execution, interaction with host variables, and testing whether or not the execution was successful.
SQL statements can be included in a source program that will be submitted to the precompiler. Such statements are said to be embedded in the program. An embedded statement can be placed anywhere in the program where a host language statement is allowed. Each embedded statement must be preceded by the keywords EXEC and SQL.
An executable statement embedded in an application program is executed every time a statement of the host language would be executed if specified in the same place. Thus, a statement within a loop is executed every time the loop is executed, and a statement within a conditional construct is executed only when the condition is satisfied.
An embedded statement can contain references to host variables. A host variable referenced in this way can be used in two ways:
In particular, all references to host variables in expressions and predicates are effectively replaced by current values of the variables; that is, the variables are used as input. The treatment of other references is described individually for each statement.
All executable statements should be followed by a test of an SQL return code. Alternatively, the WHENEVER statement (which is itself nonexecutable) can be used to change the flow of control immediately after the execution of an embedded statement.
All objects referenced in DML statements must exist when the statements are bound to a DB2 Universal Database.
An embedded nonexecutable statement is processed only by the precompiler. The precompiler reports any errors encountered in the statement. The statement is never processed during program execution. Therefore, such statements should not be followed by a test of an SQL return code.
Statements can be included in the SQL-procedure-body portion of the CREATE PROCEDURE statement. Such statements are said to be embedded in the SQL procedure. Statements that can be embedded in an SQL procedure are specified in SQL Procedure Statement. Unlike statements embedded in an application, there is no need for any keywords preceding the SQL statement. Whenever an SQL statement description refers to a host-variable, an SQL-variable can be used when the statement is embedded in an SQL procedure.
An application program can dynamically build an SQL statement in the form of a character string placed in a host variable. In general, the statement is built from some data available to the program (for example, input from a workstation). The statement (other than a select-statement) so constructed can be prepared for execution by means of the (embedded) statement PREPARE and executed by means of the (embedded) statement EXECUTE. Alternatively, the (embedded) statement EXECUTE IMMEDIATE can be used to prepare and execute a statement in one step.
A statement that is going to be dynamically prepared must not contain references to host variables. It can instead contain parameter markers. (See PREPARE for rules concerning the parameter markers.) When the prepared statement is executed, the parameter markers are effectively replaced by current values of the host variables specified in the EXECUTE statement. (See EXECUTE for rules concerning this replacement.) Once prepared, a statement can be executed several times with different values of host variables. Parameter markers are not allowed in EXECUTE IMMEDIATE.
The successful or unsuccessful execution of the statement is indicated by the setting of an SQL return code in the SQLCA after the EXECUTE (or EXECUTE IMMEDIATE) statement. The SQL return code should be checked as described above. See SQL Return Codes for more information.
A select-statement can be included as a part of the (nonexecutable) statement DECLARE CURSOR. Such a statement is executed every time the cursor is opened by means of the (embedded) statement OPEN. After the cursor is open, the result table can be retrieved one row at a time by successive executions of the FETCH statement.
Used in this way, the select-statement can contain references to host variables. These references are effectively replaced by the values that the variables have at the moment of executing OPEN.
An application program can dynamically build a select-statement in the form of a character string placed in a host variable. In general, the statement is built from some data available to the program (for example, a query obtained from a workstation). The statement so constructed can be prepared for execution by means of the (embedded) statement PREPARE, and referenced by a (nonexecutable) statement DECLARE CURSOR. The statement is then executed every time the cursor is opened by means of the (embedded) statement OPEN. After the cursor is open, the result table can be retrieved one row at a time by successive executions of the FETCH statement.
Used in this way, the select-statement must not contain references to host variables. It can contain parameter markers instead. (See PREPARE for rules concerning the parameter markers.) The parameter markers are effectively replaced by the values of the host variables specified in the OPEN statement. (See OPEN for rules concerning this replacement.)
A capability for entering SQL statements from a workstation is part of the architecture of the database manager. A statement entered in this way is said to be issued interactively.
A statement issued interactively must be an executable statement that does not contain parameter markers or references to host variables, because these make sense only in the context of an application program.