Dynamic SQL Statements

Dynamic SQL statements are not completely embedded in the source code; instead, portions are stored in program variables that can be modified at run time. Dynamic SQL statements consist of character strings that can contain question marks (?) as parameter markers, which act as place holders for input data. For example:


DELETE AUTHORS WHERE AU_FNAME = ? AND AU_LNAME = ?

Within an application, you can use the PREPARE, EXECUTE, and EXECUTE IMMEDIATE Embedded SQL statements to process a dynamic SQL statement. In general, dynamic SQL statements are prepared by using the PREPARE statement and then executed by using the EXECUTE statement. Optionally, you can combine the two statements by using the EXECUTE IMMEDIATE statement.

Using dynamic SQL statements, you can write an application that prompts a user or scans a file for information (such as database object names) that is unavailable at compile time. Use dynamic SQL statements when you must build an ad hoc SQL statement.