1.14. Dynamic SQL and Dynamic PL/SQLDynamic SQL refers to SQL statements that are constructed and executed at run-time. Dynamic is the opposite of static. Static SQL refers to SQL statements that are fixed at the time a program is compiled. Dynamic PL/SQL refers to entire PL/SQL blocks of code that are constructed dynamically, then compiled and executed. Ever since Oracle7 Release 1, we PL/SQL developers have been able to use the built-in DBMS_SQL package to execute dynamic SQL . In Oracle8i Database, we were given a second option for executing dynamically constructed SQL statements: native dynamic SQL (NDS). NDS is a native part of the PL/SQL language; it is much easier to use than DBMS_SQL, and, for many applications, it will execute more efficiently. 1.14.1. The EXECUTE IMMEDIATE StatementUse EXECUTE IMMEDIATE to execute (immediately!) the specified SQL statement. Here is the syntax of this statement: EXECUTE IMMEDIATE SQL_string [INTO {define_variable[, define_variable]... | record}] [USING [IN | OUT | IN OUT] bind_argument [, [IN | OUT | IN OUT] bind_argument]...]; where:
You can use EXECUTE IMMEDIATE for any SQL statement or PL/SQL block except for multiple-row queries. If SQL_string ends with a semicolon, it will be treated as a PL/SQL block; otherwise, it will be treated as a SELECT, DML (INSERT, UPDATE, or DELETE), or DDL (e.g., CREATE TABLE). The string may contain placeholders for bind arguments, but you cannot use bind values to pass in the names of schema objects, such as table names or column names.
When the statement is executed, the runtime engine replaces each placeholder (an identifier with a colon in front of it, such as :salary_value) in the SQL string with its corresponding bind argument (by position). You can pass numeric, date, and string expressions. You cannot, however, pass a Boolean, because it is a PL/SQL datatype. Nor can you pass a NULL literal value. Instead, you must pass a variable of the correct type that has a value of NULL. NDS supports all SQL datatypes. So, for example, define variables and bind arguments can be collections, large objects (LOBs), instances of an object type, and REFs. On the other hand, NDS does not support datatypes that are specific to PL/SQL, such as Booleans, associative arrays, and user-defined record types. The INTO clause may, however, contain a PL/SQL record. Let's take a look at a few examples:
That is a very small amount of code to achieve all that flexibility! This example introduces the bind argument: after the UPDATE statement is parsed, the PL/SQL engine replaces the :the_value, :lo, and :hi placeholders with the values in the USING clause. Notice also that I am able to rely on the SQL%ROWCOUNT cursor attribute that I have already been using for static DML statements. As you can see, EXECUTE IMMEDIATE provides a very easy and accessible syntax! 1.14.2. The OPEN FOR StatementThe OPEN FOR statement was not introduced into PL/SQL for NDS; it was first offered in Oracle7 to support cursor variables. Now it is deployed in an especially elegant fashion to implement multiple-row dynamic queries. With DBMS_SQL, you go through a painful series of steps to implement multi-row queries: parse, bind, define each column individually, execute, fetch, extract each column value individually. That's a lot of code to write! For native dynamic SQL, Oracle took an existing feature and syntaxthat of cursor variablesand extended it in a very natural way to support dynamic SQL. Let's look at the syntax of the OPEN FOR statement: OPEN {cursor_variable | :host_cursor_variable} FOR SQL_string [USING bind_argument[, bind_argument]...]; where:
Following is an example that demonstrates the declaration of a weak REF CURSOR type, a cursor variable based on that type, and the opening of a dynamic query using the OPEN FOR statement : CREATE OR REPLACE PROCEDURE show_parts_inventory ( parts_table IN VARCHAR2, where_in IN VARCHAR2) IS TYPE query_curtype IS REF CURSOR; dyncur query_curtype; BEGIN OPEN dyncur FOR 'SELECT * FROM ' || parts_table ' WHERE ' || where_in; ... Once you have opened the query with the OPEN FOR statement, the syntax used to fetch rows, close the cursor variable, and check the attributes of the cursor are all the same as for static cursor variables and hardcoded explicit cursors. 1.14.3. Dynamic PL/SQLDynamic PL/SQL offers some of the most interesting and challenging coding opportunities. Think of it: while a user is running your application, you can take advantage of NDS to do any of the following:
There are some rules and tips you need to keep in mind when working with dynamic PL/SQL blocks and NDS:
Here is an example of using dynamic PLS/SQL. This is a true story, I kid you not. During a consulting stint at an insurance company in Chicago, I was asked to see what I could do about a particularly vexing program. It was very large and continually increased in sizesoon it would be too large to even compile. Much to my amazement, this is what the program looked like: CREATE OR REPLACE PROCEDURE process_line (line IN INTEGER) IS BEGIN IF line = 1 THEN process_line1; ELSIF line = 2 THEN process_line2; ... ELSIF line = 514 THEN process_line514; ... ELSIF line = 2057 THEN process_line2057; END IF; END; For each line number, there was a "process_line" program that handled those details. And as the insurance company added more and more exceptions to the policy, the program got bigger and bigger. Not a very scalable approach to programming! To avoid this kind of mess, a programmer should be on the lookout for repetition of code. If you can detect a pattern, you can either create a reusable program to encapsulate that pattern, or explore the possibility of expressing that pattern as a dynamic SQL construction. I was able to replace those thousands of lines of code with nothing more than this: CREATE OR REPLACE PROCEDURE process_line (line IN INTEGER) IS BEGIN EXECUTE IMMEDIATE 'BEGIN process_line' || line || '; END;'; END; From thousands of lines of code down to one executable statement! Of course, in most cases, identification of the pattern and conversion of that pattern into dynamic SQL will not be so straightforward. Still, the potential gains are enormous. |