Native Dynamic SQL使用的最佳实践

使用NDS的最佳实践

Steven Feuerstein

最近几年中，动态SQL已经成为PL/SQL程序中更加普遍使用的特性。在Oracle8i提供Native Dynamic SQL(NDS)之前，PL/SQL中只能通过DBMS_SQL包来实现动态SQL。DBMS_SQL尽管非常健壮，但非常难以使用，而且相对来说较慢。目前，Oracle Database 10g 中，实际上NDS能够处理所有最普通的动态SQL，并且比起DBMS_SQL API来使用NDS真的是一种乐趣。

无论使用NDS来编写动态SQL多么容易，但它依然是动态的（运行时解析），而且依然是SQL语句，这意味着你依然必须尽责的编写和运行NDS代码以及进行异常处理。本文为启动动态应用和平滑运行提供NDS用法的最佳实践。

 

什么是动态SQL？

动态SQL是指在运行态来构造、解析和执行DDL，DML和查询语句。它之所以被称为动态，是因为在编写和编译代码时并不能完全了解所想要运行的SQL语句（或者称为静态）。大多数情况下，你需要用户输入，例如用户想看到的列或WHERE子句的某些元素，来完成整个SQL语句。

下面是动态DDL语句的例子：

'DROP TABLE ' || l_my_table

动态PL/SQL也可以是运行时构造、编译、执行的匿名PL/SQL块。下面是动态PL/SQL块的例子：

'BEGIN report_pkg.run_report (' || l_report_id || ');'

注意：动态PL/SQL语句必须以分号结尾，而动态SQL语句无须以分号结尾。

使用NDS

为使用NDS，必须学习如何编写EXECUTE IMMEDIATE语句。该语句的语法是：

EXECUTE IMMEDIATE sql_string

   [INTO variable_list ]

   [USING bind_variable_list ]

其中sql_string是包含SQL语句或PL/SQL块的字符串，variable_list是得到查询SQL语句所返回数据的变量列表，这是可选语法。bind_variable_list是在动态语句中使用绑定变量的列表，也是可选语法。设置INTO和USING子句当然会有若干特性，但是当你构造任意复杂的和泛型的SQL语句时，仍然会遇到最头疼的情况。

请思考列表1中的代码。tabcount函数返回指定表中的记录行数，WHERE子句可选，SELECT语句本身非常普通：

 

表1: 从表中返回记录行数的函数 CREATE OR REPLACE FUNCTION tabcount (    tab IN VARCHAR2, whr IN VARCHAR2 := NULL)    RETURN PLS_INTEGER IS    retval PLS_INTEGER; BEGIN    EXECUTE IMMEDIATE       'SELECT COUNT(*) FROM ' || tab ||       ' WHERE ' || NVL (whr, '1=1')    INTO retval;      RETURN retval; END tabcount;

 

因此当我以参数值“EMP”和whr变量为“DEPTNO = 10 ” 来调用tabcount函数时，那么将执行下列查询：

 

SELECT COUNT(*)

  FROM EMP

 WHERE DEPTNO = 10

 

在下面的例子中用这些参数来调用tabcount函数。

 

SQL> BEGIN

  2     DBMS_OUTPUT.PUT_LINE (tabcount ('EMP', 'DEPTNO = 10'));

  3  END;

  4  /

 

另一方面，假如我传递一个非法参数值“DEPTNO - 10 ” ，将会最终得到一个错误：

SQL> BEGIN

2 DBMS_OUTPUT.PUT_LINE (tabcount ('EMP', 'DEPTNO - 10'));

3 END;

4 /

BEGIN * ERROR at line 1: ORA-00920: invalid relational operator ORA-06512: at

"SCOTT.TABCOUNT", line 7 ORA-06512: at line 2

 

由于我是在SQL*PLUS中运行这个简单的脚本，那么当然可以立即看见这个错误，并快速的找到第2行，并检查语句出现的问题。然而在一个正式应用程序中，这个错误可能在嵌套的程序调用中已经被处理了，并且该信息不会显示出来。更有甚者，该语句或许没有采用硬编码（hard-coded），而是采用更复杂的方式，比如通过参数传递变量值，这样就更难跟踪问题的根源。

 

NDS中的错误处理模式

鉴于动态SQL的不透明特性，我们需要捕获、处理和记录所有与动态SQL语句有关的可能信息，尽可能地接近错误的根源。

 

列表2是对tabcount函数的重写，示范了动态SQL错误控制机制的相关技术：

 

表2: 有着健壮错误处理的tabcount函数 CREATE OR REPLACE FUNCTION tabcount (tab IN VARCHAR2, whr IN VARCHAR2 := NULL)    RETURN PLS_INTEGER IS    e_no_such_table   EXCEPTION;    PRAGMA EXCEPTION_INIT (e_no_such_table, -942);    str               VARCHAR2 (32767);    retval            PLS_INTEGER;      PROCEDURE pl (str IN VARCHAR2, len IN INTEGER := 80)    IS       v_len     PLS_INTEGER     := LEAST (len, 255);       v_len2    PLS_INTEGER;       v_chr10   PLS_INTEGER;       v_str     VARCHAR2 (2000);    BEGIN       IF LENGTH (str) > v_len       THEN          v_chr10 := INSTR (str, CHR (10));            IF v_chr10 > 0 AND v_len >= v_chr10          THEN             v_len := v_chr10 - 1;             v_len2 := v_chr10 + 1;          ELSE             v_len := v_len - 1;             v_len2 := v_len;          END IF;            v_str := SUBSTR (str, 1, v_len);          DBMS_OUTPUT.put_line (v_str);          pl (SUBSTR (str, v_len2), len);       ELSE          DBMS_OUTPUT.put_line (str);       END IF;    EXCEPTION       WHEN OTHERS       THEN          DBMS_OUTPUT.ENABLE (1000000);          DBMS_OUTPUT.put_line (v_str);    END pl;      PROCEDURE report_error (errmsg_in IN VARCHAR2)    IS    BEGIN       pl ('tabCount ERROR:');       pl (errmsg_in);       -- Oracle 10g only pl ('tabCount Backtrace:');       -- Oracle 10g only pl (DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);       pl ('Dynamic query:');       pl (str);    END report_error; BEGIN    str := 'SELECT COUNT(*) FROM ' || tab || ' WHERE ' || NVL (whr, '1=1');      EXECUTE IMMEDIATE str                 INTO retval;      RETURN retval; EXCEPTION    WHEN e_no_such_table    THEN       report_error ('Unable to find a table or view named "' || tab || '"');       RETURN NULL;    WHEN OTHERS    THEN       report_error (DBMS_UTILITY.format_error_stack);       RETURN NULL; END; /

 

提示1——声明SQL语句的变量：

尽量不要在EXECUTE IMMEDIATE语句中构建自己的动态SQL语句，而是应当把整个语句串接好并赋值给一个变量，然后传递这个变量给EXECUTE IMMEDIATE语句。假如动态SQL语句出现错误，就可以把语句显示或记录在自己的错误处理块中。

 

提示2——错误处理块：

在包含EXECUTE IMMEDIATE语句的块代码中包含一个例外部分。换句话来说，错误处理语句尽可能接近它出现的地方。那么对于错误发生的地方，可以收集到尽可能多的信息。

将所有错误处理逻辑放置在一个本地存储过程中，来确保一致性的错误处理机制，并减少代码冗余。表2的代码使用了DBMS_UTILITY.FORMAT_ERROR_STACK来显示错误堆栈信息，使用了DBMS_UTILITY.FORMAT_ERROR_BACKTRACE（仅在ORACLE 10g 可用，它可以得到错误发生的精确行号）来显示错误跟踪信息，还有最重要的，显示动态SQL语句。对于经常发生的错误，应当在例外部分包含特定的处理机制，但是必须确保同时也得包含一个WHEN OTHERS子句。比如在编写表2时，意识到当用户传递一个非法表名时，将会发生一个已知错误。因此可以声明一个本地的、有命名的例外来处理ORA-00942错误，然后提供一个简单的、清晰的信息来帮助解决这个问题，正如演示的那样，当以不正确的表名来运行tabcount函数时：

SQL> BEGIN   2    DBMS_OUTPUT.put_line (tabcount ('EMPX', 'DEPTNO = 10'));   3  END;   4  /   tabcount ERROR: Unable to find a table or view named "EMPX"   Dynamic query: SELECT COUNT(*) FROM EMPX WHERE DEPTNO = 10

 

不管是否预计到一个错误并为它编写了信息，当前面的例子中，或者在下面例子中使用了WHEN OTHERS子句来显示ORACLE错误信息，我都在“Dynamic query:”标签后面显示了所运行的SQL字符串，这就可以使我很快的检索出SQL语句并发现问题部分。

 

SQL> BEGIN   2    DBMS_OUTPUT.put_line (tabcount ('EMP', 'DEPTNO - 10'));   3  END;   4  /   tabcount ERROR: ORA-00920: invalid relational operator   Dynamic query: SELECT COUNT(*) FROM EMP WHERE DEPTNO – 10

 

提示3——DBMS_OUTPUT的限制：

关于错误处理的最终建议：这些例子简单地依赖于DBMS_OUTPUT来显示错误信息，而在正式应用程序中，更可能的方式是将数据写入表或文件中。然而，假如你决定通过DBMS_OUTPUT来显示信息，不应当直接调用DBMS_OUTPUT.PUT_LINE。很显然得，SQL语句、错误堆栈和跟踪信息肯定超过255字符，因此直接调用DBMS_OUTPUT.PUT_LINE将会产生一个例外信息。这种情况下，你应当使用一个例如存储过程之类的程序来处理长信息来避免这个错误。

 

AUTHID CURRENT_USER是必要的！

现在假定自己工作在积极鼓励代码重用的环境中，并且设置了容易共享代码的基础框架。tabcount函数看起来很有用，因此将它放置在库中。为了避免在数据库的多模式中多次声明该函数，我在CODESHARE模式中创建了tabcount函数，并用下列命令授权给所有用户：

 

GRANT EXECUTE ON tabcount TO PUBLIC;

CREATE PUBLIC SYNONYM tabcount

FOR tabcount;

 

作完这些事情，我骄傲地向项目组成员发送了备忘录，邀请他们使用这个功能。然而在第二天，我就从愤怒的编码人员那里得到了一封EMAIL：“亲爱的Steven，我尝试使用你的函数，结果非常失败。我创建了一张表，并插入一行，然后调用tabcount函数。非常不幸，我得到一个错误，到底怎么回事？”她同时传递了一份LOG文件附件，见表3。

表3: 运行tabcount函数的错误信息 SQL> CREATE TABLE xyz (abc DATE); SQL> INSERT INTO xyz VALUES (SYSDATE); SQL> COMMIT; SQL> exec DBMS_OUTPUT.PUT_LINE (tabcount('xyz')); tabcount ERROR: Unable to find a table or view named "xyz" Dynamic query: SELECT COUNT(*) FROM xyz WHERE 1=1 BEGIN DBMS_OUTPUT.PUT_LINE (tabcount('xyz')); END;   * ERROR at line 1: ORA-00942: table or view does not exist ORA-06512: at "SCOTT.TABCOUNT", line 64 ORA-06512: at line 1

 

我开始比较困惑，然后就意识到了问题：我的函数运行在“定义者权限”执行模式下，于是尝试者在CODESHARE模式下找出XYZ表，而不是编码人员的开发模式下。

 “定义者权限”意味着，将在定义者或拥有者的权限下运行一个存储过程。而我的函数就是在CODESHARE模式下创建的，并依赖于默认的执行模式：定义者权限。这样，当编码人员在她的模式下调用tabcount函数时，tabcount将尝试计算 CODESHARE模式下XYZ表的行数。

为解决这个问题，在函数头部分加了一个AUTHID子句，指定在执行模式中采用CURRENT_USER或调用者权限。

 

CREATE OR REPLACE FUNCTION tabcount (

   tab IN VARCHAR2,

   whr IN VARCHAR2 := NULL)

   RETURN PLS_INTEGER

   AUTHID CURRENT_USER

 

指定调用者权限，意味着用户将在调用者权限内运行存储的过程。现在当编码人员使用tabcount，她可以得到正确的结果了：

 

SQL>  exec DBMS_OUTPUT.PUT_LINE (

tabcount('xyz'));

 

选择绑定，结束串接

正如先前提及，动态SQL最困难的地方在于正确拼接SQL语句。因为这个SQL语句是动态的，无论通过串接或通过绑定变量值都将在运行时得到这个语句。第一定律是：只要可能就使用绑定而减少串接。现在让我们比较这两种方式：

 

表4提供了一个非常普通的存储过程，date_range_col_update用来在指定日期范围内更新所有行的一个数字列的值。它非常依赖绑定，请注意值（:val），开始日期（:lodate），结束日期（:hidate）等占位符。即使这个SQL语句非常普通，使用绑定也是简单易读和易于维护的。

 

表 4: 绑定变量方式 CREATE OR REPLACE PROCEDURE date_range_col_update (    tab_in       IN   VARCHAR2   ,datecol_in   IN   VARCHAR2   ,start_in     IN   DATE   ,end_in       IN   DATE   ,valcol_in    IN   VARCHAR2   ,val_in       IN   NUMBER ) IS    str   VARCHAR2 (32767)       :=    'UPDATE '          || tab_in          || ' SET '          || valcol_in          || ' = :val WHERE '          || datecol_in          || ' BETWEEN :lodate AND :hidate'; BEGIN    EXECUTE IMMEDIATE str                USING val_in, start_in, end_in;      DBMS_OUTPUT.put_line ('Rows updated: ' || TO_CHAR (SQL%ROWCOUNT)); -- Exception handled left out to save space END date_range_col_update; /   -- Sample invocation of procedure BEGIN    date_range_col_update (       'emp', 'hiredate', sysdate-100, sysdate, 'sal', 10000); END;

 

现在考虑串接变量值的方式来实现同样的功能，如表5。现在这个SQL语句就非常复杂了。

 

Code Listing 5: 串接方式 CREATE OR REPLACE PROCEDURE date_range_col_update (    tab_in       IN   VARCHAR2   ,datecol_in   IN   VARCHAR2   ,start_in     IN   DATE   ,end_in       IN   DATE   ,valcol_in    IN   VARCHAR2   ,val_in       IN   NUMBER ) IS    str   VARCHAR2 (32767)       :=    'UPDATE '          || tab_in          || ' SET '          || valcol_in          || ' = '          || TO_CHAR (val_in)          || ' WHERE '          || datecol_in          || ' BETWEEN TO_DATE ('''          || TO_CHAR (start_in)          || ''') AND TO_DATE ('''          || TO_CHAR (end_in)          || ''')'; BEGIN    EXECUTE IMMEDIATE str                USING val_in, datecol_in, start_in, end_in;      DBMS_OUTPUT.put_line ('Rows updated: ' || TO_CHAR (SQL%ROWCOUNT)); -- Exception handled left out to save space END date_range_col_update; /

 

在串接时，必须对付字符串中的单引号，这意味着除非在Oracle Database 10g 下运行，否则你必须输入多个单引号在一起以使语句正确。在Oracle 10g 下可以利用新引入的引号转义符功能。

在Oracle Database 10g 之前版本，必须这样编写：

 

'TO_DATE (''' || TO_CHAR (start_in) || ''')'

 

而在Oracle Database 10g 中，在字符串中输入单引号，可以使用不同的字符（比如[和]）来指出字符串的开始和结束，而不必担心到底需要多少单引号。

 

q'[ TO_DATE (']' || TO_CHAR (start_in)  || q'[')]'

 

最后，注意在表名、列表或SQL语句本身的一部分中不能使用绑定变量，只能在SQL语句的变量部分使用。

 

何时选用DBMS_SQL？

有些时候，不使用NDS而使用DBMS_SQL包也是有意义的。这里给出使用DBMS_SQL的部分建议。虽然一般来说，NDS是易于使用和易于编写的，但是在某些场合，也必须使用DBMS_SQL包。

 

下面是部分场景：

1、需要执行的动态SQL超过32K字符。EXECUTE IMMEDIATE只能解析和运行单一字符串，这意味着它受限于32K字符。在很多情况下，比如产生非常大的SQL语句或者DDL的编译（如CREATE OR REPLACE PACKAGE），将会超过这个限制。在这些情况下，使用DBMS_SQL.PARSE来接受字符串的集合，并解析由所有字符串串接形成的语句，是合适的。

2、需要使用DYNAMIC METHOD 4类型的动态SQL语句。DYNAMIC METHOD 4是最复杂和普遍的动态SQL方式。它一般用于在编写代码时，你不知道查询将会返回多少行，或者在字符串到底绑定多少变量的情况下。虽然可以用NDS实现DYNAMIC METHOD 4动态SQL，但是使用DBMS_SQL更容易解决。

 

3、需要最小化SQL语句的解析工作量。NDS的一个缺点是每次执行动态SQL语句，都会被解析，甚至就在片刻之前执行过同样的语句也会进行解析。如果语句非常庞大和复杂，超载是非常严重的。使用DBMS_SQL就可以缩短解析阶段，并且简单地利用不同的绑定变量重复执行同样的语句。

 

表6演示了基于集合的动态SQL解析过程的一种可能应用。我需要用PL/SQL编程实现从文件中编译PL/SQL源代码，这些文件相当长，因此我不能使用EXECUTE IMMEDIATE，而是在compile_from_file功能中应用了DBMS_SQL，如表6所列：

 

Code Listing 6: 使用DBMS_SQL来解析非常长的SQL语句 CREATE OR REPLACE PROCEDURE compile_from_file (    dir_in    IN   VARCHAR2   ,file_in   IN   VARCHAR2 ) IS    l_file    UTL_FILE.file_type;    l_lines   DBMS_SQL.varchar2s;    l_cur     PLS_INTEGER        := DBMS_SQL.open_cursor; BEGIN    l_file := UTL_FILE.fopen (dir_in, file_in, 'R');      BEGIN       LOOP          UTL_FILE.get_line (l_file, l_lines (l_lines.COUNT + 1));       END LOOP ;    EXCEPTION       WHEN NO_DATA_FOUND       THEN          NULL;    END;      UTL_FILE.fclose (l_file);    DBMS_SQL.parse (c => l_cur                   ,statement => l_lines                   ,lb => l_lines.FIRST                   ,ub => l_lines.LAST                   ,lfflg => TRUE                   ,language_flag =>DBMS_SQL.native                   );    DBMS_SQL.close_cursor (l_cur); EXCEPTION    WHEN OTHERS    THEN       UTL_FILE.fclose (l_file);       DBMS_SQL.close_cursor (l_cur);       DBMS_OUTPUT.put_line ('Compile from file failure: ');       DBMS_OUTPUT.put_line (SUBSTR (SQLERRM, 1, 255)); END compile_from_file; /

 

在该程序中，非常重要的两步：将文件内容读取到集合中，然后解析这个集合，并且执行了DDL语句（包括提交commit过程）。利用DBMS_SQL.PARSE来解析基于集合的语句，需要使用程序能够理解的集合类型，因此声明了一个l_lines的集合：

   l_lines   DBMS_SQL.varchar2s;

然后，在通过调用UTL_FILE.GET_LINE向集合传递了数据后，调用如下的解析程序：

DBMS_SQL.parse (c => l_cur

       ,statement => l_lines

   ,lb => l_lines.FIRST

   ,ub => l_lines.LAST

   ,lfflg => TRUE

   ,language_flag =>DBMS_SQL.native

   );

 

第一个参数是游标，第二个参数是集合，第三四个参数指定了想要解析的行（上界和下界）。在本例中，我指定的是所有行。第五个参数指定了在每行后插入一个换行符，这是相当重要的，它保证得到的源代码变得易读。最后一个参数language_flag的值指示DBMS_SQL按照本地版本来解析SQL。

在上面所做的工作中，还算是易读易写的简单代码。因为使用的是DBMS_SQL，这样必须显式打开游标，还必须记得在执行完程序或遇到错误时关掉游标。对于文件处理的PL/SQL部分也是同样适用的。

 

慎重使用

随着动态SQL变得更加容易和有效，在应用程序中使用得更普遍、更深入。因此，开发人员必须慎重使用NDS，并利用最佳实践来确保代码易于维护和持续提高是非常重要的。