Preparing and Executing a Non-select Statement
The PREPARE and EXECUTE statements can also execute dynamic non-select statements. These statements enable your program to save a statement string and execute it as many times as necessary. A prepared statement is discarded when the transaction in which it was prepared is rolled back or committed. If a statement with the same name as an existing statement is prepared, the new statement supersedes the old statement.
The following example demonstrates how a runtime user can prepare a dynamically specified SQL statement and execute it a specific number of times:
read SQL statement from terminal into buffer;
exec sql prepare s1 from :buffer;
read number in N
loop N times
exec sql execute s1;
end loop;
The following example creates a table whose name is the same as the user name, and inserts a set of rows with fixed-typed parameters (the user’s children) into the table:
get user name from terminal;
buffer = 'create table ' + user_name +
'(child char(15), age integer)';
exec sql execute immediate :buffer;
buffer = 'insert into ' + user_name +
'(child, age) values (?, ?)';
exec sql prepare s1 from :buffer;
read child's name and age from terminal;
loop until no more children
exec sql execute s1 using :child, :age;
read child's name and age from terminal;
end loop;
A list of statements that cannot be executed using PREPARE and EXECUTE can be found in
Prepare and Execute Statements (see
Prepare and Execute Statements).