Embedding a statement in an application program
SQL statements can be included in a source program that will be submitted to a 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 SQL.
An executable statement embedded in an application program is executed every time a statement of the host language would be executed if it were 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.
- As input (the current value of the host variable is used in the execution of the statement)
- As output (the variable is assigned a new value as a result of executing the statement)
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.
All executable statements should be followed by a test of the SQL return code. Alternatively, the WHENEVER statement (which is itself non-executable) can be used to change the flow of control immediately after the execution of an embedded statement.
All objects referenced in data manipulation language (DML) statements must exist when the statements are bound to a database.
An embedded non-executable 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 the 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. Whenever an SQL statement description refers to a host-variable, an SQL-variable can be used if the statement is embedded in an SQL procedure.