Section 1.14. Dynamic SQL and Dynamic PLSQL


1.14. Dynamic SQL and Dynamic PL/SQL

Dynamic 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 Statement

Use 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:


SQL_string

String expression containing the SQL statement or PL/SQL block.


define_variable

Variable that receives a column value returned by a query.


record

Record based on a user-defined TYPE or %ROWTYPE that receives an entire row returned by a query.


bind_argument

Expression whose value is passed to the SQL statement or PL/SQL block, or an identifier that serves as an input and/or output variable to the function or procedure that is called in the PL/SQL block.


INTO clause

Used for single-row queries; for each column value returned by the query, you must supply an individual variable or field in a record of a compatible type.


USING clause

Allows you to supply bind arguments for the SQL string. This clause is used for both dynamic SQL and PL/SQL, which is why you can specify a parameter mode. This usage is relevant only for PL/SQL; however, the default is IN, which is the only kind of bind argument you would have for SQL statements.

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 you execute a DDL statement in your program, you will also perform a commit. If you don't want the DDL-driven commit to affect outstanding changes in the rest of your application, place the dynamic DDL statement within an autonomous transaction procedure. See auton_ddl.sql on the book's web site for a demonstration of this technique.


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:

  • Create an index:

         EXECUTE IMMEDIATE 'CREATE INDEX emp_u_1 ON employee (last_name)';

    PL/SQL does not support native DDL statements; instead, you must use dynamic SQL.

  • Obtain the count of rows in any table, in any schema, for the specified WHERE clause:

         /* File on web: tabcount_nds.sf */     CREATE OR REPLACE FUNCTION tabcount (        tab IN VARCHAR2, whr IN VARCHAR2 := NULL)        RETURN PLS_INTEGER AUTHID CURRENT_USER     IS        str      VARCHAR2 (32767) := 'SELECT COUNT(*) FROM ' || tab;        retval   PLS_INTEGER;     BEGIN        IF whr IS NOT NULL        THEN           str := str || ' WHERE ' || whr;        END IF;        EXECUTE IMMEDIATE str INTO retval;        RETURN retval;     EXCEPTION        WHEN OTHERS        THEN           DBMS_OUTPUT.put_line (              'TABCOUNT ERROR: ' || DBMS_UTILITY.FORMAT_ERROR_STACK);           DBMS_OUTPUT.put_line (str);           RETURN NULL;     END;     /

    So now I never again have to write SELECT COUNT(*), whether in SQL*Plus or within a PL/SQL program. Instead, I can do the following:

         BEGIN        IF tabCount ('emp', 'deptno = ' || v_dept) > 100        THEN           DBMS_OUTPUT.PUT_LINE ('Growing fast!');        END IF;

  • Here's a function that lets you update the value of any numeric column in the employee table. It's a function because it returns the number of rows that have been updated.

         /* File on web: updnval.sf */     CREATE OR REPLACE FUNCTION updNVal (        col IN VARCHAR2,        val IN NUMBER,        start_in IN DATE,        end_in IN DATE)        RETURN PLS_INTEGER     IS     BEGIN        EXECUTE IMMEDIATE           'UPDATE employee SET ' || col || ' = :the_value             WHERE hire_date BETWEEN :lo AND :hi'          USING val, start_in, end_in;        RETURN SQL%ROWCOUNT;     END;

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 Statement

The 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:


cursor_variable

Weakly typed cursor variable


:host_cursor_variable

Cursor variable declared in a PL/SQL host environment such as an Oracle Call Interface (OCI) program


SQL_string

Contains the SELECT statement to be executed dynamically


USING clause

Follows the same rules as in the EXECUTE IMMEDIATE statement

If you are using Oracle9i Database Release 2 or Oracle Database 10g, you can use EXECUTE IMMEDIATE with BULK COLLECT to retrieve multiple rows with a dynamic query. This approach requires much less code and can improve the performance of your query operation.


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/SQL

Dynamic 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:

  • Create a program, including a package that contains globally accessible data structures

  • Obtain (and modify) by name the value of global variables

  • Call functions and procedures whose names are not known at compile time

There are some rules and tips you need to keep in mind when working with dynamic PL/SQL blocks and NDS:

  • The dynamic string must be a valid PL/SQL block. It must start with the DECLARE or BEGIN keyword and end with an END statement and semicolon. The string will not be considered PL/SQL code unless it ends with a semicolon.

  • In your dynamic block, you can access only PL/SQL code elements that have global scope (standalone functions and procedures, and elements defined in the specification of a package). Dynamic PL/SQL blocks execute outside the scope of the local enclosing block.

  • Errors raised within a dynamic PL/SQL block can be trapped and handled by the local block in which the string was run with the EXECUTE IMMEDIATE statement.

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.




Oracle PL(s)SQL For DBAs
Oracle PL(s)SQL For DBAs
ISBN: N/A
EAN: N/A
Year: 2005
Pages: 122

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net