The simplest form of dynamic SQL is provided by the EXECUTE IMMEDIATE statement, shown in Figure 18-2. This statement passes the text of a dynamic SQL statement to the DBMS and asks the DBMS to execute the dynamic statement immediately. To use this statement, your program goes through the following steps:
- The program constructs a SQL statement as a string of text in one of its data areas (usually called a buffer). The statement can be almost any SQL statement that does not retrieve data.
- The program passes the SQL statement to the DBMS with the EXECUTE IMMEDIATE statement.
- The DBMS executes the statement and sets the SQLCODE/SQLSTATE values to indicate the completion status, exactly as if the statement had been hard-coded using static SQL.
Figure 18-3 shows a simple C program that follows these steps. The program prompts the user for a table name and a SQL search condition, and builds the text of a DELETE statement based on the user’s responses. The program then uses the EXECUTE IMMEDIATE statement to execute the DELETE statement. This program cannot use a static SQL-embedded DELETE statement, because neither the table name nor the search condition are known until the user enters them at runtime. It must use dynamic SQL. If you run the program in Figure 18-3 with these inputs:
Enter table name: staff
Enter search condition: quota < 20000
Delete from staff successful.
the program passes this statement text to the DBMS:
delete from staff
where quota < 20000
If you run the program with these inputs:
Enter table name: orders
Enter search condition: cust = 2105
Delete from orders successful
the program passes this statement text to the DBMS:
delete from orders
where cust = 2105
The EXECUTE IMMEDIATE statement thus gives the program great flexibility in the type of DELETE statement that it executes.
The EXECUTE IMMEDIATE statement uses exactly one host variable—the variable containing the entire SQL statement string. The statement string itself cannot include host variable references, but there’s no need for them. Instead of using a static SQL statement with a host variable like this:
exec sql delete from orders
where cust = :cust_num;
a dynamic SQL program achieves the same effect by building the entire statement in a buffer and executing it:
sprintf(buffer, “delete from orders where cust = %d”, cust_num)
exec sql execute immediate :buffer;
The EXECUTE IMMEDIATE statement is the simplest form of dynamic SQL, but it is very versatile. You can use it to dynamically execute most DML statements, including INSERT, DELETE, UPDATE, COMMIT, and ROLLBACK. You can also use EXECUTE IMMEDIATE to dynamically execute most DDL statements, including the CREATE, DROP, GRANT, and REVOKE statements.
The EXECUTE IMMEDIATE statement does have one significant limitation, however. You cannot use it to dynamically execute a SELECT statement, because it does not provide a mechanism to process the query results. Just as static SQL requires cursors and special- purpose statements (DECLARE CURSOR, OPEN, FETCH, and CLOSE) for programmatic queries, dynamic SQL uses cursors and some new special-purpose statements to handle dynamic queries. The dynamic SQL features that support dynamic queries are discussed later in the section “Dynamic Queries.”
Source: Liang Y. Daniel (2013), Introduction to programming with SQL, Pearson; 3rd edition.