PLSql 动态SQL应用理解

PLSql 动态SQL应用

一、知识点

（1）表类型

表类型变量table
语法如下：
type 表类型 is table of 类型 index by binary_integer;
表变量名 表类型;
类型可以是前面的类型定义，index by binary_integer 子句代表以符号整数为索引，这样访问表类型变量中的数据方法就是“表变量名(索引符号整数)”。table类型就是一个可变长的数组，key（符号整数索引）必须是整数，可以是负数，value（类型）可以是标量，也可以是record类型。可以不按顺序赋值，但必须先赋值后使用。

可以使用count、delete、first、last、next、exists和prior
等属性进行操作，使用方法为“表变量名.属性”，返回的是数字。

（2）动态SQL

动态sql是指在运行时候才构建的sql语句，静态sql指的是在代码编译时刻就已经包含在代码中的那些已经充分明确的固定的sql语句，而动态plsql是指整个跑了sql代码块都是动态构建，然后再编译执行的。

动态sql最大的优点就是可以灵活的变量带入，返回变量。

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]…];

sql_string

这是一个包含了sql语句或者pl/sql代码块的字符串表达式。

define_variable

这是一个用于接收查询中的某一列值得变量

bind_argument

这是一个表达式，表达式的值将传递给sql语句或者plsql，同时可以是一个标识符

into子句

这个子句用于单行的查询；对于查询结果的每一列的值，必须提供单独的变量接收

using子句

利用这个语句，可以给sql字符串提供绑定参数，同时可以用于动态sql和动态plsql，不过这个模式只对plsql有意义，缺省模式是in。详细来讲就是using中使用的绑定参数可以使用在sql和plsql中，如果使用在plsql中需要制定是in、out还是in out模式默认情况下为in，而在sql中没用模式一说。注意不能传入null值，必须使用一个类型正确，并且恰好是null的变量来传入。

不能绑定plsql转悠类型，布尔，关联数组等以及用户自定义的记录类型。其传入的参数如果是

二、测试验证

（1）测试脚本

declare
  type tabletype1 is table of varchar2(20) index by binary_integer;
  table_text tabletype1; --定义表类型
  sqltext    varchar2(1000);

  vnum   number(10);
  o_id   varchar2(30);
  c_id   varchar2(30);
  v_city varchar2(10);
  v_num number(6);

begin
--表类型中增加数据，一维表
  table_text(1) := 'TB_PART_HASH';
  table_text(2) := 'TB_PART_LIST';
  table_text(3) := 'TB_PART_LIST_HASH';
  table_text(4) := 'TB_PART_RANGE';
--测试表类型的方法
  dbms_output.put_line('总记录数：' || to_char(table_text.count));
  dbms_output.put_line('第一条记录：' || table_text.first);
  dbms_output.put_line('最后条记录：' || table_text.last);
  dbms_output.put_line('第二条的前一条记录：' || table_text.prior(2));
  dbms_output.put_line('第三条的后一条记录：' || table_text.next(3));

--动态SQL测试，from 后的表名是动态的，统计结果保存到变量中
  sqltext := 'select count(*) from ' || table_text(2);
  dbms_output.put_line(sqltext);
  execute immediate sqltext
    into vnum;
  dbms_output.put_line(vnum);

--动态SQL测试，两个条件是变量，查询结果是一行两列记录保存到变量中
  o_id    := 'iXQrIXEEAMLWgXwi';
  c_id    := 'HUKNXTMTYDHMDEOS';
  sqltext := 'select city ,order_num from ' || table_text(1) || ' where order_id = ' || '''' || o_id || '''' ||
             ' and ' || ' contract_id = ' || '''' || c_id || '''';

  dbms_output.put_line(sqltext);

  execute immediate sqltext
    into v_city,v_num;
  dbms_output.put_line('City:' || v_city || ' ,Order num: ' ||to_char(v_num));

--使用using 传递变量
  sqltext := 'select city ,order_num from ' || table_text(1) ||' where order_id = :1  and  contract_id = :2 ';

  dbms_output.put_line(sqltext);
--注意into要在using前，否则报错
  execute immediate sqltext
    into v_city,v_num
    using  o_id, c_id;

  dbms_output.put_line('City:' || v_city || ' ,Order num: ' ||to_char(v_num));
end;

（2）输出结果

总记录数：4
第一条记录：1
最后条记录：4
第二条的前一条记录：1
第三条的后一条记录：4
select count(*) from TB_PART_LIST
2103000
select city ,order_num from TB_PART_HASH where order_id = ‘iXQrIXEEAMLWgXwi’ and contract_id = ‘HUKNXTMTYDHMDEOS’
City:GUANGZHOU ,Order num: 7790
select city ,order_num from TB_PART_HASH where order_id = :1 and contract_id = :2
City:GUANGZHOU ,Order num: 7790

三、小结

1、表类型，就相当于数组操作，可以是一维，也可以是多维。
2、拼接执行语句：

sqltext := ‘select city ,order_num from ’ || table_text(1) || ’ where order_id = ’ || ‘’’’ || o_id || ‘’’’ ||

效率低，每次执行需要编译。

用using 绑定变量

sqltext := ‘select city ,order_num from ’ || table_text(1) ||’ where order_id = :1 and contract_id = :2 ';

执行效率高，编写简单。
注意：传递变量只能在等号后面，在from后面还是需用拼接的方式传递变量（表名）。
不可能写成以下方式：

sqltext := 'select city ,order_num from :0 where order_id = :1 and contract_id = :2 ';

3、into必须在using前面

execute immediate sqltext
into v_city,v_num
using o_id, c_id;