Executing a Non-select Statement Using Execute Immediate
EXECUTE IMMEDIATE executes an OpenSQL statement specified using a string literal or host variable. The EXECUTE IMMEDIATE statement can be used to execute all but a few of the OpenSQL statements. For a list of statements that you cannot execute with the EXECUTE IMMEDIATE statement, see
Execute Immediate Statement (see page
Execute Immediate Statement).
When the EXECUTE IMMEDIATE statement is used to execute a statement that is not a select, its syntax 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 employee';
As another example, the following code reads OpenSQL statements from a file into a host string variable named 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 a statement's parameters, such as an employee name or number, change at runtime, then you do not need to use EXECUTE IMMEDIATE; you can replace a value with a host variable. For example, the following fragment increases the salaries of all employees with a specific employee number (read out of a file into variable, number):
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;