Using Execute Immediate to Execute a Non-select Statement
EXECUTE IMMEDIATE executes an SQL statement specified using a string literal or host language variable. Use this statement to execute all but a few of the SQL statements; the exceptions are listed in
Execute Immediate Statement (see
Execute Immediate Statement).
For non-select statements, the syntax of EXECUTE IMMEDIATE is as follows:
EXEC SQL EXECUTE IMMEDIATE statement_string;
For example, the following statement executes a DROP statement specified as a string literal:
/*
** Statement specification included
** in string literal. The string literal does
** NOT include 'exec sql' or ';'
*/
exec sql execute immediate 'drop table employee';
The following example reads SQL statements from a file into a host string variable buffer and executes the contents of the variable. If the variable includes a statement that cannot be executed by EXECUTE IMMEDIATE , or if another error occurs, the loop is broken.
exec sql begin declare section;
character buffer(100);
exec sql end declare section;
open file;
loop while not end of file and not error
read statement from file into buffer;
exec sql execute immediate :buffer;
end loop;
close file;
If only the statement parameters (such as an employee name or number) change at runtime, EXECUTE IMMEDIATE is not needed. A value can be replaced with a host language variable. For instance, the following example increases the salaries of employees whose employee numbers are read from a file:
loop while not end of file and not error
read number from file;
exec sql update employee
set sal = sal * 1.1
where eno = :number;
end loop;