[Chapter 4] 4.2 NDS Statement SummaryChapter 4Native Dynamic SQL in Oracle8i 4.2 NDS Statement Summary One of the nicest things about NDS is its simplicity. Unlike DBMS_SQL, which has dozens of programs and lots of rules to follow, NDS has been integrated into the PL/SQL language by adding one new statement, EXECUTE IMMEDIATE, and by enhancing the existing OPEN FOR statement:EXECUTE IMMEDIATEExecutes a specified SQL statement immediatelyOPEN FORAllows you to perform multiple-row dynamic queries4.2.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]...]; SQL_string A string expression containing the SQL statement or PL/SQL blockdefine_variableA variable that receives a column value returned by a queryrecord A record based on a user-defined TYPE or %ROWTYPE that receives an entire row returned by a querybind_argument An expression whose value is passed to the SQL statement or PL/SQL block INTO clauseUse for single-row queries; for each column value returned by the query, you must supply an individual variable or field in a record of 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 only relevant 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 either DML (Data Manipulation Language -- SELECT, INSERT, UPDATE, or DELETE) or DDL (Data Definition Language, such as 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 available in Oracle8i. 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, index-by tables, 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)';It can't get much easier than that, can it?Create a stored procedure that will execute any DDL statement:CREATE OR REPLACE PROCEDURE execDDL (ddl_string IN VARCHAR2) IS BEGIN EXECUTE IMMEDIATE ddl_string; END; /With execDDL in place, I can create that same index as follows:execDDL ('CREATE INDEX emp_u_1 ON employee (last_name)');Obtain the count of rows in any table, in any schema, for the specified WHERE clause:/* Filename on companion disk: tabcount.sf */ CREATE OR REPLACE FUNCTION tabCount ( tab IN VARCHAR2, whr IN VARCHAR2 := NULL, sch IN VARCHAR2 := NULL) RETURN INTEGER IS retval INTEGER; BEGIN EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM ' || NVL (sch, USER) || '.' || tab || ' WHERE ' || NVL (whr, '1=1') INTO retval; RETURN retval; END; /So now I never again have to write SELECT COUNT(*), whether in SQL*Plus or within a PL/SQL program, as in 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 any table. It's a function because it returns the number of rows that have been updated./* Filename on companion disk: updnval.sf */ CREATE OR REPLACE FUNCTION updNVal ( tab IN VARCHAR2, col IN VARCHAR2, val IN NUMBER, whr IN VARCHAR2 := NULL, sch IN VARCHAR2 := NULL) RETURN INTEGER IS BEGIN EXECUTE IMMEDIATE 'UPDATE ' || NVL (sch, USER) || '.' || tab || ' SET ' || col || ' = :the_value WHERE ' || NVL (whr, '1=1') USING val; RETURN SQL%ROWCOUNT; END; /Where I come from, that is a very small amount of code to achieve all of that flexibility! This example introduces the bind argument: after the UPDATE statement is parsed, the PL/SQL engine replaces the :the_value placeholder with the value in the val variable. Notice also that I am able to rely on the SQL%ROWCOUNT cursor attribute that I have already been using for static DML statements. Suppose that I need to run a different stored procedure at 9 a.m. each day of the week. Each program's name has this structure: DAYNAME_set_schedule. Each procedure has the same four arguments: you pass in employee_id and hour for the first meeting of the day; it returns the name of the employee and the number of appointments for the day. I can use dynamic PL/SQL to handle this situation:/* Filename on companion disk: run9am.sp */ CREATE OR REPLACE PROCEDURE run_9am_procedure ( id_in IN employee.employee_id%TYPE, hour_in IN INTEGER) IS v_apptCount INTEGER; v_name VARCHAR2(100); BEGIN EXECUTE IMMEDIATE 'BEGIN ' || TO_CHAR (SYSDATE, 'DAY') || '_set_schedule (:id, :hour, :name, :appts); END;' USING IN id_in, IN hour_in, OUT v_name, OUT v_apptCount; DBMS_OUTPUT.PUT_LINE ( 'Employee ' || v_name || ' has ' || v_apptCount || ' appointments on ' || TO_CHAR (SYSDATE)); END; /This is a very easy and accessible syntax!4.2.2 The OPEN FOR StatementThe OPEN FOR statement is not brand-new to PL/SQL in Oracle8i; 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 particularly painful series of steps to implement multirow queries: parse, bind, define each column individually, execute, fetch, extract each column value individually. My gosh, what a lot of code to write!For native dynamic SQL, Oracle took an existing feature and syntax -- that of cursor variables -- and extended it in a very natural way to support dynamic SQL. The next section explores multirow queries in detail; let's take a look now specifically at the syntax of the OPEN FOR statement:OPEN {cursor_variable | :host_cursor_variable} FOR SQL_string [USING bind_argument[, bind_argument]...]; cursor_variableA weakly typed cursor variable:host_cursor_variable A 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 dynamicallyUSING clause Follows the same rules as it does in the EXECUTE IMMEDIATE statementMany PL/SQL developers are not very familiar with cursor variables, so a quick review is in order (for lots more details, check out Chapter 6 of Oracle PL/SQL Programming, 2nd Edition.A cursor variable is a variable of type REF CURSOR, or referenced cursor. Here is an example of a declaration of a cursor variable based on a "weak" REF CURSOR (the sort you will use for NDS):DECLARE TYPE cv_type IS REF CURSOR; cv cv_type;A cursor variable points to a cursor object; it is, however, a variable. You can have more than one variable pointing to the same cursor object, you can assign one cursor variable to another, and so on. Once you have declared a cursor variable, you can assign a value to it by referencing it in an OPEN FOR statement:DECLARE TYPE cv_type IS REF CURSOR; cv cv_type; BEGIN OPEN cv FOR SELECT COUNT(guns) FROM charlton_heston_home;In this example, the query is static -- it is not contained in single quotes, and it is frozen at compilation time. That is the only way we have been able to work with cursor variables until Oracle8i. Now we can use the same syntax as before, but the query can be a literal or an expression, as in the following:OPEN dyncur FOR SQL_string;or, to show the use of a bind argument:OPEN dyncur FOR 'SELECT none_of_the_above FROM senate_candidates WHERE state = :your_state_here' USING state_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, for that matter. The next section demonstrates all of this syntax through examples.To summarize, there are two differences between the OPEN FOR statement for static and dynamic SQL:The static version does not support the USING clause of NDS.The static version requires a static SQL statement after the FOR keyword, whereas with NDS, the SQL string is always either a literal string or an expression that evaluates to a string.4.1 DBMS_SQL Versus NDS4.3 Multirow Queries with Cursor Variables Copyright (c) 2000 O'Reilly & Associates. All rights reserved.