动态SQL与动态PL/SQL_PART2

接PART1：

1.2.2 PL/SQL中绑定变量的使用

  下面主要讲述在一些特别的场合中使用绑定变量可能遇到的情况。本节首先列出PL/SQL中使用绑定变量的一些规则和注意点，然后分别对这些规则和注意点做一些总结，结合开发中常见问题做分析，从而帮助我们熟悉PL/SQL中绑定变量的基本使用。
在NDS中使用绑定变量，要记住下列规则：

规则1：在NDS中使用绑定变量，仅可代替一些表达式,如字面量，变量，复杂的表达式。不能绑定schema对象，如表名，列名等。也不能代替整个SQL语句的一大块,如整个where条件。对于不能使用绑定变量的情形，如果也要实现动态，考虑使用字符串连接。

规则2：另外NULL值不是一个具体的类型，不能用于给绑定变量赋值，可以使用未初始化的变量或将NULL通过函数转为具体类型，比如to_number(NULL)将NULL转为Number类型，动态DDL语句中不可使用绑定变量，对一些SQL，PLSQL中的关键字不可使用绑定变量代替。

规则3：对于不能使用绑定变量的场合，如果要实现动态SQL或动态PLSQL的话，可以使用字符串连接的方法拼语句。

规则4：using后的是绑定变量的实际值，按照sql_string中绑定变量的顺序赋值，传递值遵循规则为：动态SQL，按顺序赋值，不管名字是否重复，全部都要赋值，动态PL/SQL，如果占位符有重复的，只按唯一名字顺序赋值,后面详细讲解。可以带mode，默认是in，这也是sql语句唯一能指定的mode。Returning之后有绑定变量，可以使用out。动态PL/SQL这三种模式都可以使用<使用方式和调用的过程中的参数模式一致>。在sql语句或plsql语句中，可以用占位符(前面家冒号)指定使用绑定变量，using之后按顺序传入。
using后的绑定变量必须是SQL类型，比如PL/SQL的boolean等类型是不行的。

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

规则1情形探讨：
例如，如果一个存储过程要truncate一个表，我们尝试使用绑定变量，过程如下：

CREATE OR REPLACE PROCEDURE truncobj (

   nm IN VARCHAR2,

   tp IN VARCHAR2 := 'TABLE',

   sch IN VARCHAR2 := NULL)

IS

BEGIN

   EXECUTE IMMEDIATE

      'TRUNCATE :trunc_type :obj_name'

      USING tp, NVL (sch, USER) || '.' || nm;

END;

  这个过程编译没有任何问题，因为NDS在编译期不会检查语法和语义的正确性。那么如果执行这个过程，则会报下列错误：

       ORA-03290: Invalid truncate command - missing CLUSTER or TABLE keyword

报告无效的truncate命令，丢失了cluster或table关键字。也就是发生了语法错误。根据上述的错误，我们下部改写动态SQL，改为：

    EXECUTE IMMEDIATE 'TRUNCATE TABLE :obj_name' USING nm; //表名不能使用绑定变量

加了TABLE关键字，继续执行，发现报下列错误：

ORA-00903: invalid table name

报表名无效的错误。

  究其原因，为什么NDS对绑定变量会有这样的限制，我们在使用表名为绑定变量代替的时候会出错？因为PL/SQL引擎在运行期首先会检查NDS中的语句是否完全被定义，是否正确，这些表名如果使用绑定变量，则PL/SQL引擎不会用USING后面的值代替，则会出错。

'UPDATE emp SET sal = :xyz'    //字面量使用绑定变量，正确

 上面这条语句，PL/SQL引擎认识，因为:xyz代替的是一个字面量，PL/SQL引擎认为它是一个完整的语句。

      

'UPDATE emp SET :col_name = :xyz' //列名使用了绑定变量，错误

  这条语句就会错误，因为列名不知道，PL/SQL引擎认为它是一个不完整的语句。

其实PL/SQL中绑定变量使用的规则和SQL中的explain plan语句类似，在explain plan语句中，可以对字面量，复杂表达式等使用绑定变量，但是不能对schema对象使用绑定变量。

  对于上述不能使用绑定变量的场合，我们如果需要动态传入的话，可以使用字符串连接拼SQL或PL/SQL语句。如下，我们改造这个truncate过程。

CREATE OR REPLACE PROCEDURE truncobj (

   nm IN VARCHAR2,

   tp IN VARCHAR2 := 'TABLE',

   sch IN VARCHAR2 := NULL)

IS

BEGIN

   EXECUTE IMMEDIATE

      'TRUNCATE ' || tb || ' ' || NVL(sch,USER) || '.' || nm;

END;

这样就会编译正确，运行正确了。

  还有一点，我经常看到有的人认为绑定变量无所不能，只理解了绑定变量可以作为替换作用的功能，所以经常将语句的一个大块作为绑定变量，从而出错，比如：

我们要实现以个动态where更新，将整个where条件作为绑定变量传递。

declare

  whr varchar2(1000):=' where  name is null';

  v_sql varchar2(4000);

begin

   --本来想动态传入整个where块的

    v_sql:='delete from test :whr';

    execute immediate v_sql using whr;

    commit;

end;

  这是典型的初学者犯的错误，当然如果创建这样的过程，在编译期是不出错的，但是到运行的时候呢？oracle检查语法错误，报ORA-00933：SQL命令未正确结束的错误。
  要知道绑定变量只是代替一些字面量，变量，复杂表达式，而不能代替语句的包含关键字的一个块。ok,记住这些，希望你以后不要犯这样的傻瓜式错误。

规则2情形探讨：
  规则2说的是NULL和绑定变量的关系，我们知道，不管在SQL*PLUS，还是一些Oracle编程中，如JDBC，设置绑定变量，必须传入的值具有具体的类型，在PL/SQL中也是如此，传入绑定变量的实际值，必须是具有具体类型的值，NULL这东西很特殊，它没有具体的类型或者说它可以是任何Oracle合法类型的值，那么如果对一个绑定变量的实际值，我们直接传入NULL，会发生什么情况呢？请看：

declare

  whr varchar2(1000):=' ';

  v_sql varchar2(4000);

begin

--更新test表中id为null的birth为null,注意id is null中的null不可用绑定变量哦

    v_sql:='update test set birth=:vbirth where id is null';

    execute immediate v_sql using null; --传入NULL

    commit;

end; 

  一旦运行，就报PLS-00457错误，在using子句中，表达式必须是SQL类型。当然我们可以不指定类型，比如using 1,using sysdate,using ‘aa’都是可以的，因为oracle知道他们的类型，但是对于NULL,oracle是无法判断类型的，所以报错。
  可能你要说了，那我就要传入NULL，怎么办呢？至少有三种方式可以考虑：

1.Oracle不就是要个类型吗？我给它给类型不就得了，OK，传入NULL不行，我传入个空字符串总行吧，试试，可能你不确定，因为oracle官方文档上不是说NULL和空字符串是一样的吗？注意，大部分情况一样，但是空字符串毕竟是有类型的，而NULL不确定类型。

declare

  whr varchar2(1000):=' ';

  v_sql varchar2(4000);

begin

    v_sql:='update test set birth=:vbirth where id is null';

    execute immediate v_sql using '';  --传入空字符串，成功，这是’’和NULL的区别之一

    commit;

end;

2.就是要类型对吧，我传入个变量给你，有类型，但是没有初始化，不也是NULL吗？

declare

  whr varchar2(1000):=' ';

  v_sql varchar2(4000);

  vbirth date;--未初始化，NULL

begin

    v_sql:='update test set birth=:vbirth where id is null';

    execute immediate v_sql using vbirth;  --成功，传入有类型的变量，值为NULL

    commit;

end;

3.既然NULL可以有任何类型，我们可以用转换函数，比如to_number,to_char,to_date等奖NULL转为指定类型嘛，ok,这也没有问题。

declare

  whr varchar2(1000):=' ';

  v_sql varchar2(4000);

begin

    v_sql:='update test set birth=:vbirth where id is null';

    execute immediate v_sql using to_date(NULL);  --to_date(NULL)将NULL变为有类型

    commit;

end;

我们看下规则5，稍后讨论规则4

 为什么说，在动态语句中使用绑定变量，使编程变得很容易呢？先看一个简单的例子你就明白了。

case1:不使用绑定变量，用字符串拼接，使我们的程序编写变得复杂，而且容易出错。

比如有这样的动态SQL：

v_sql:='delete from test where name='||'aa';

这是很简单的连接字符串，这没有什么问题，再看下面的，如果我们需要做一些复杂的表达式连接，就有问题了。

v_sql:='delete from test where name='||replace(input_string,'x','a');

其中input_string是外界传入的，要知道，oracle中对引号的处理比较特殊，如果传入的字符串中本身就包含引号，那么外界比如java,c,c++等传入一个input_string=”jack’s”,这在java,c,c++等语言中正确的，传入到我们的上述语句中就变成：

v_sql:='delete from test where name='||replace('jack's','x','a');
那是肯定要出错的。
再看一个通过拼凑字符串常犯的错误。

declare

  vname varchar2(10):='dd';

  v_sql varchar2(4000);

  vcnt number;

begin

    v_sql:='select count(*) from test where name='||vname; --这里要出错

    dbms_output.put_line(v_sql);

    execute immediate v_sql into vcnt;

    dbms_output.put_line(vcnt);

end;

   
  还是引号的问题，虽然我们传入的是字符串类型，但是在动态SQL中，oracle可不会把引号带上，结果语句变为：

select count(*) from test where name=dd –引号传入的是’dd’,结果变成了dd,当然错了，如果加引号，语句就变得复杂了。v_sql:='select count(*) from test where name='''||vname||'''';

      
  再看一个典型的拼凑日期的错误。
  我常常看到别人不使用绑定变量，采用拼凑的方式传入日期类型，比如：

declare

  vname varchar2(10):='dd';

  v_sql varchar2(4000);

  vcnt number;

  v_date date:=sysdate;

begin

    v_sql:='select count(*) from test where birth='||v_date; --这里要出错

    dbms_output.put_line(v_sql);

    execute immediate v_sql into vcnt;

    dbms_output.put_line(vcnt);

end;

 
  这里又出什么错呢？请看打印出的语句是什么？

select count(*) from test where birth=08-5月 -10
  原来在运行期，如果采用||date,那么日期自动根据session日期设置参数转换为字符串，而字符串连接不自动加引号，所以就变成上面的了，如果你非要那样做，只能两边加to_char或者birth加to_char,然后转为对应的格式。抛开效率不说，这的确有点复杂。
  以上是典型的拼凑导致的程序难以编写而且容易出错的例子，如果采用绑定变量的方式，以上问题全可迎刃而解。

case2:使用绑定变量，程序编写变简单，而且不容易出错。

declare

  vname varchar2(10):='dd';

  v_sql varchar2(4000);

  vcnt number;

  v_date date:=sysdate;

begin

  --绑定date类型，正确

    v_sql:='select count(*) from test where birth<:1';

    execute immediate v_sql into vcnt using v_date; --plsql引擎会自动将date类型变量传到sql中

 --绑定字符串，正确

     v_sql:='select count(*) from test where name=:1';

    execute immediate v_sql into vcnt using 'dd';   --plsql引擎会自动给sql字面量加上引号

 

end;

  看，使用了绑定变量，程序编写更容易了，而且更重要的是可以共享SQL，提高效率，当然有时候绑定变量也是不好的，就是数据倾斜分布严重的时候，这时候，我们最好不要使用绑定变量或不要用到绑定变量。

下面来看规则4，规则4涉及到NDS中绑定变量使用的两个问题：绑定参数模式以及绑定参数位置。
先看绑定参数模式。

绑定参数具有下列3种模式：

MODE	DESC
IN	只读值(默认模式)
OUT	只写变量
IN OUT	可读可写

  对于in是只读模式，也是动态SQL绑定变量using的默认模式，常不写。动态SQL中唯一可以使用OUT或IN OUT模式的就是returnning子句用使用绑定变量(但是测试发现，如果returnning之后又数据传入，则报ora-03113,否则不报错，所以还是在returnning中尽量不要用in out)，如下：

DINGJUN123>declare

  2   vlastname employee.last_name%type;

  3   vfirstname employee.first_name%type;

  4   v_sqlstmt varchar2(1000);

  5   begin

  6

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

  8

  9    execute immediate v_sqlstmt using  out vfirstname, out vlastname;

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

 11   end;

 12  /

the fired employee'name is jackjones

PL/SQL 过程已成功完成。

  除了在returnning中,out和in out只能在动态PL/SQL中使用。在动态PL/SQL中，绑定变量参数模式必须与PL/SQL的参数模式匹配，也动态PL/SQL块中变量的使用。

动态PL/SQL绑定变量使用：

1.in模式的可以省略in,可以传入字面量，表达式，变量，常量等。

2.对于out,in out的必须定义变量传入，而且模式必须要写，要与存储过程定义的模式一致。

3.传入的绑定变量类型必须是SQL类型，PL/SQL类型不行，比如传入boolean。

create or replace procedure testdynplsql

(id number,

 name out varchar2,

 salary in out number,

 bool out boolean)

 as

 begin

  name:=name||'+++++';

  salary:=salary*2;

  bool:=false;

 end;

下面调用

declare

  name varchar2(100):='dj';

  salary number(10):=1000;

  bool boolean:=true;

 begin

 -----bool类型不能作为绑定变量传入

 --其他out,in out必须写模式，而且与存储过程一致，不一致不对

  execute immediate

  '

    begin

    testdynplsql(:p1,:p2,:p3,:p4);

    end;

' using 1,out name, in out salary,out bool; 

  dbms_output.put_line(name||' ''s salary now is '||salary);

 end;

--报错PLS-00457,绑定变量必须是SQL类型，boolean类型不可以

--注意boolean类型又不能直接传入，因为连接符||不能连接boolean类型，对boolean类型如果要作为参数动态传入到动态语句中，必须经过转换的步骤

create or replace procedure testdynplsql

(id number,

 name out varchar2,

 salary in out number)

 as

 begin

  name:=name||'+++++';

  salary:=salary*2;

end;

 

 

 declare

  name varchar2(100):='dj';

  salary number(10):=1000;

  bool boolean:=true;

 begin

 --其他out,in out必须写模式，而且与存储过程一致，不一致不对

  execute immediate

  '

    begin

    testdynplsql(:p1,:p2,:p3);

    end;

  ' using 1,out name, in out salary; --name,salary必须传入变量，而且参数模式与过程一致

  dbms_output.put_line(name||' ''s salary now is '||salary);

 end;

+++++ 's salary now is 2000   

--因为name是out模式，所以传入的值就算赋值了也是当初始化为null的，所以不是dj+++++

下面看绑定参数的位置
  有时候，我们有相同的绑定名字，那么到底是传入一次还是传入多次呢？动态SQL和动态PL/SQL规则不一样。

1.动态SQL只按位置传入，名字重复不重复没有关系，有多少个位置就传入多少次，按从左到右规则。比如:val,:val,:name,那么要传入3次的，不是2次。

2.动态PLSQL按名字唯一传入，但是传入的位置是从左到右的。比如:val,:val,:name,:val那么只要传入2次，比如using 100,’dj’，按从左到右剔重的规则。