动态SQL与动态PL/SQL_PART1

动态sql是在运行期构造和执行的。动态是与静态相对的概念。静态的就是harcode(硬编码)，动态的就是softcode(软编码)，静态sql是固定的，不可改变的，在编译期会检查一些语法错误等。但是动态sql在编译期不会确定这些,<编译期只当字符串对待,所以很多动态SQL的错误，只有等到运行期才会发现>。动态PL/SQL是动态构造整个PL/SQL块，并在运行期动态构造,然后编译和执行。写动态sql，能满足更加灵活和写出更加具有重用功能的代码（如封装一个创建table的过程）。

能用static sql，最好用static sql，因为static sql具有更好的性能和可读性以及可扩展性。静态sql在编译期检查sql是否正确，plsql会验证数据类型大小等，并在数据字典中建立和维护依赖关系，静态sql使sql分析一次，执行多次得以实现，静态sql具有更好的自适应性，而动态sql很难有这些优点。所以应该寻找能够消除动态sql的机会，如果不行，只能用动态sql或动态plsql。

  使用动态SQL和动态PL/SQL，我们能够构造灵活的和更加重用性的程序，并且能解决静态sql和静态plsql解决不了的一些问题。

一些需要动态sql和动态plsql解决的问题：

1.执行DDL语句

在PL/SQL中要执行static sql只能写一些查询和dml语句，事务处理语句等。但是不能执行ddl语句，比如create table，drop index等，可以用动态sql执行。

2.支持ad-hoc查询和update基于web的应用需求

有些internate上的应用需求，比如用户要看到指定的列或多种排序方式。

3.softcode(软编码业务逻辑和规则)
和hardcode的静态sql不一样，业务逻辑和规则是暴露在代码里的，那么softcode业务逻辑和规则，我们可以写到table中，在运行期决定生成这些业务逻辑和规则然后执行，可以隐藏业务逻辑。

1.1 NDS statements

  NDS语句与dbms_sql不同，它的优点是首先就是简单,而dbms_sql有很多程序和规则需要遵循,包括一系列处理步骤，和NDS想比，那真是太复杂了,而且大多数效率不如NDS好,所以强烈建议，如果要用动态SQL或PL/SQL,优先考虑NDS,其次才是DBMS_SQL，后

面会说什么时候不得不用DBMS_SQL的。

1.1.1 execute immediate语句

  EXECUTE IMMEDIATE SQL_string

   [INTO {define_variable[,define_variable]... |record}]

   [USING [IN | OUT | IN OUT]bind_argument

       [, [IN | OUT | IN OUT]bind_argument]...];

1.sql_string可以是一个sql语句<任何合法的sql语句>或者PL/SQL块。如果不以分号结束，那么是sql语句，以分号结束是pl/sql语句。可以是任何sql语句或plsql块，除了多行查询（多行查询可以使用dbms_sql包和动态open for，当然oracle9i release2版本或以上的可以使用execute immediate sql_string bulk collect into实现）。

2.into 后面的是单行查询<如果不是bulk collect into>变量列表或record，是select获得的列值传到这些变量中,如果是oracle9i release2版本或以上使用bulk collect into那么后面的变量就是集合。

3.using后的是绑定变量的实际值，按照sql_string中绑定变量的顺序赋值，传递值遵循规则为：动态SQL，按顺序赋值，不管名字是否重复，全部都要赋值，动态PL/SQL，如果占位符有重复的，只按唯一名字顺序赋值,后面详细讲解。可以带mode，默认是in，这也是sql语句唯一能指定的mode。Returning之后有绑定变量，可以使用out。动态PL/SQL这三种模式都可以使用<使用方式和调用的过程中的参数模式一致>。在sql语句或plsql语句中，可以用占位符(前面家冒号)指定使用绑定变量，using之后按顺序传入，但是不能传入null值,因为动态SQL或PLSQL的绑定变量必须具有存在的类型，比如传入1，Oracle引擎知道它是数值类型的，但是NULL却是无类型的或类型不唯一，这是不可以的（如果可能为空考虑用nvl函数或的确要传入NULL，可以考虑传入未初始化的变量或将NULL转为指定类型，如to_number(NULL)），也要注意顺序和类型。

另外注意绑定变量不能是schema对象，如列名，表名等。

using后的绑定变量必须是SQL类型，比如PL/SQL的boolean等类型是不行的。

4.NDS支持所有的SQL类型，如字符类型，数值类型，日期类型，当然也可以使用集合，LOB，对象类型实例，XML文档，REF类型等。但是PL/SQL特有的类型，比如boolean，associate arrays,user_definded record，可能不行，除非能够对应上。

5.绑定变量不能用schema对象的名字来替换，比如用表名或列名来替换是错误的。（其实绑定变量传入的实际值是字面量，比如是表名，用using 字符串，则会替换到SQL中是加’’的，会报无效的表名，如果列名用字符串替换，也是加’’的，那么sql相当于加了个字符串常量，不会报错，但是拼sql列的效果就没有了）。

6.绑定变量只能代替SQL或PLSQL中的字面量，变量，复杂的表达式，5说的schema对象是不可以的，另外比如一个大的语句，如整个where条件，也是不可以使用绑定变量的。

7.动态ddl语句中不允许出现绑定变量，否则返回ORA-01027: 在数据定义操作中不允许有绑定变量错误，这个原因是对于动态语句，运行期引擎首先要解析这个SQL，保证SQL语法正确,绑定变量为自动考虑为变量，如果schema对象或DDL使用绑定变量，这样表或列等不存在，则SQL错误，这时候可以使用拼字符串实现。

8.动态语句应该在必要的情况下考虑使用绑定变量(静态语句很多情况下可以自动绑定的，所以可以一次解析，执行多次)，因为没有使用绑定变量的语句，大部分情况下效率没有使用绑定变量的好。还有一个很重要的一点是：不使用绑定变量，那么使我们的语句难以编写，当然我们这里说的是PL/SQL,比如JAVA的话，使用绑定变量，编程却麻烦了，但是我们宁可麻烦，也不是牺牲性能和可伸缩性作为代价。当然绑定变量也不是总是好的，比如数据倾斜严重的时候，如果条件中使用绑定变量可能不好，这里就不详细说了。

下面看一些例子：

SQL> set serveroutput on

SQL>

SQL> declare

  2   var_number number;

  3   var_text varchar2(10);

  4   begin

  5   --如果在列中用绑定变量，会替换成字面量，如下替换为select 1,'DUMMY' from dual;

  6    execute immediate 'select :1,:2 from dual' into var_number,var_text using 1,'DUMMY' ;

  7    dbms_output.put_line(var_number||'-'||var_text);

  8   end;

  9  /

1-DUMMY

PL/SQL procedure successfully completed

SQL>

SQL>   declare

  2   var_number number;

  3   var_text varchar2(10);

  4   begin

  5    --如果表名替换，也会替换成字面量，如下替换为select 1,'dj' from 'TEST';报无效表名错误

  6    execute immediate 'select :1,:2 from :3' into var_number,var_text using 1,'dj','TEST' ;

  7    dbms_output.put_line(var_number||'-'||var_text);

  8   end;

  9  /

declare

 var_number number;

 var_text varchar2(10);

 begin

  --如果表名替换，也会替换成字面量，如下替换为select 1,'dj' from 'TEST';报无效表名错误

  execute immediate 'select :1,:2 from :3' into var_number,var_text using 1,'dj','TEST' ;

  dbms_output.put_line(var_number||'-'||var_text);

 end;

ORA-00903: 表名无效

ORA-06512: 在 line 7

从上面的例子可以看出，使用绑定变量，的确有很多注意点。

declare

 vschema varchar2(20):='dingjun123.trademark';

 vname varchar2(10);

 v_sql varchar2(100);

 vcol varchar2(100):='name';

 begin

 --id=:bid,其中bid是要使用绑定变量

 --from 后面的表用了变量

--写动态sql或动态pl/sql最重要的就是拼好sql或pl/sql，因为编译期当字符串，不会报错，但是运行期

--就不一定了，如果写非常复杂的sql，可以先测试一下，用dbms_output,put_line打印一下看对不对

 --如果将from后的表使用绑定变量或 where id的列id使用绑定标量，都是不允许的，schema对象不能作为绑定变--量

v_sql:='select '||vcol|| ' from '||vschema||' where id=:bid';

--注意：v_sql中select和后面的vcol以及和from后面要加空格，否则连在一起不合法。另外不能以分号结束

   execute immediate v_sql into vname using 1;

  dbms_output.put_line(vname);

 end;

下面看一些NDS经常性的使用例子：

建立index：
execute immediate 'create index i_emp_name on emp(name)';

建立一个procedure可以执行任何ddl语句：

create or replace procedure execDDL(ddl_string in varchar2)

 is

 begin

  execute immediate ddl_string;

 end;

这样我们建立一个索引就可以使用上面的过程，比如：

begin

 execDDL('create index i_emp_name on s_emp(last_name)');

 end;

获得任何schema的任何table的指定where语句之后查询出来的count值：

 create or replace function tabcount(tab in varchar2,

                                    whr in varchar2 := null --传入table和where条件

                                    ) return PLS_INTEGER AUTHID CURRENT_USER IS

  str    varchar2(32767) := 'select count(*) from ' || tab;--注意空格

  retval PLS_INTEGER;

begin

  --如果有where条件，加上where条件

  if whr is not null then

    str := str || ' WHERE ' || whr;

  end if;

  --execute

  execute immediate str

    into retval;

  return retval;

  --if exception,return null

exception

  when others then

    dbms_output.put_line('tab_count error:' ||

                         dbms_utility.format_error_stack);

    dbms_output.put_line(str);

    return null;

end;

一个函数，返回更新的数目，更新的列是number类型：

CREATE OR REPLACE FUNCTION updNVal (

   col IN VARCHAR2,

   val IN NUMBER,

   start_in IN DATE,

   end_in IN DATE)

   RETURN PLS_INTEGER

IS

BEGIN

--使用了绑定变量，在update语句被解析之后，pl/sql引擎会用using后面的替换占位符

   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;

做一个每天早上9点执行一个不同的procedure的程序：

这个不同的procedure有一个规则，名称是DAYNAME_set_schedule，对dayname可以用sysdate动态来确定，这个过程有四个参数，前两个是传入只读参数，后两个是返回的只写参数，这样的需求可以使用动态PL/SQL实现这个功能。DAYNAME_set_schedule过程功能是传入员工id和时间传入，得到员工在指定时间需要约会的人的数目。

--传入id和hour，返回name和apptcount

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

--使用动态pl/sql

   EXECUTE IMMEDIATE

      'BEGIN ' || TO_CHAR (SYSDATE, 'DAY','nls_date_language=American') ||

         '_set_schedule (:id, :hour, :name, :appts); END;'--动态plsql结尾有分号

     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,'yyyy-mm-dd'));

END;

/

--只做简单的测试用

create or replace procedure wednesday_set_schedule

       (

       id in employee.employee_id%Type,

       hour in integer,

       vname out varchar2,

       vappCount out integer

       )

as

begin

 select  last_name into vname from employee where employee_id = id;

 vappCount := 10;

 end;

/

下面看一个例子，在动态sql中，唯一对绑定变量赋值的时候可以使用out模式的就是DML语句通过returning返回信息给绑定变量，否则只能是in模式。

/**

||动态sql绑定变量默认是in，也就是将外界参数传给绑定变量，只读，不能是null

||如果动态sql是dml语句，并且后面有returning语句,returning后使用了绑定变量

||,那么在using后面模式要用out，不可用in out，测试发现如果有数据处理则报ora-03113，没有处理的数据则不报此错，外界参数接受绑定变量传过来的值

**/

declare

 vlastname employee.last_name%type;

 vfirstname employee.first_name%type;

 v_sqlstmt varchar2(1000);

 begin

 --delete语句将删除的信息返回给绑定变量

  v_sqlstmt := 'delete from employee where employee_id=1 returning first_name,last_name into :vfirstname,:vlastname';

 --外界变量接受绑定变量返回的值        

  execute immediate v_sqlstmt using out vfirstname, out vlastname;

  dbms_output.put_line('the fired employee'||chr(39)||'name is '||vfirstname||vlastname);

 end;

未完待续，见PART2：