< Day Day Up > |
EXECUTE IMMEDIATEEXECUTE IMMEDIATE implicitly prepares and executes complete SQL statements coded in host variables . Only a subset of SQL statements is available when you use the EXECUTE IMMEDIATE class of dynamic SQL. The most important SQL statement that is missing is the SELECT statement. Therefore, EXECUTE IMMEDIATE dynamic SQL cannot retrieve data from tables. If you don't need to issue queries, you can write the SQL portion of your program in two steps. First, move the complete text for the statement to be executed into a host variable. Second, issue the EXECUTE IMMEDIATE statement specifying the host variable as an argument. The statement is prepared and executed automatically. Listing 12.1 shows a simple use of EXECUTE IMMEDIATE that DELETE s rows from a table. The SQL statement is moved to a string variable and then executed. Listing 12.1. A COBOL Program Using EXECUTE IMMEDIATEWORKING-STORAGE SECTION. . . . EXEC SQL INCLUDE SQLCA END-EXEC. . . . 01 STRING-VARIABLE. 49 STRING-VAR-LEN PIC S9(4) USAGE COMP. 49 STRING-VAR-TXT PIC X(100). . . . PROCEDURE DIVISION. . . . MOVE +45 TO STRING-VAR-LEN. MOVE "DELETE FROM DSN8810.PROJ WHERE DEPTNO = 'A00'" TO STRING-VARIABLE. EXEC SQL EXECUTE IMMEDIATE :STRING-VARIABLE END-EXEC. . . . You can replace the DELETE statement in Listing 12.1 with any of the following supported statements: ALTER COMMENT ON COMMIT CREATE DELETE DROP EXPLAIN GRANT INSERT LABEL ON LOCK TABLE REVOKE ROLLBACK SET UPDATE Despite the simplicity of the EXECUTE IMMEDIATE statement, it usually is not the best choice for application programs that issue dynamic SQL for two reasons. One, as I mentioned, EXECUTE IMMEDIATE does not support the SELECT statement. Two, performance can suffer when you use EXECUTE IMMEDIATE in a program that executes the same SQL statement many times. After an EXECUTE IMMEDIATE is performed, the executable form of the SQL statement is destroyed . Thus, each time an EXECUTE IMMEDIATE statement is issued, it must be prepared again. This preparation is automatic and can involve a significant amount of overhead. A better choice is to code non- SELECT dynamic SQL using PREPARE and EXECUTE statements. EXECUTE IMMEDIATE GuidelinesWhen developing dynamic SQL programs that use EXECUTE IMMEDIATE , observe the following guidelines. Verify Dynamic SQL SyntaxVerify that the SQL statement to be executed with dynamic SQL uses the proper SQL syntax. This way, you can reduce the overhead incurred when improperly formatted SQL statements are rejected at execution time. Use EXECUTE IMMEDIATE for Quick, One-Time TasksThe EXECUTE IMMEDIATE class of dynamic SQL is useful for coding quick-and-dirty one-time processing or DBA utility-type programs. Consider using EXECUTE IMMEDIATE in the following types of programs:
Declare EXECUTE IMMEDIATE Host Variables ProperlyThe definition of the host variable used with EXECUTE IMMEDIATE must be in the correct format. Assembler, COBOL, and C programs must declare a varying-length string variable. FORTRAN programs must declare a fixed-list string variable. PL/I programs can declare either type of variable. |
< Day Day Up > |