动态SQL与绑定变量

aqqbjeedc

谢谢楼主的分享.很实用!

zhaosj1726

好久没来开发版转悠了,一下发现这么多的精华帖来.

对楼主的文章提出些补充:
1.
where owner like nvl(i_phone_name, '%')
在i_phone_name不输入值时,就是where owner like '%'
它等价于where owner is not null.
和原意在owner上不限定条件还是不一样的.
当然如果业务和表的约束定义里要求owner not null的话,其实也是一样的.

2.
其中1占95% 2占5%并且改列有索引的情况下。
如果第一次运行（硬解析）时字面值为1 那么之后的软解析都将使用索引，即使绑定变量传入的值是2
这里将使用索引,应该是笔误吧,应该是使用全表扫描吧!

3.
CURSOR_SHARING=similar时,
如果程序中使用了字面值,相应的字段上又收集了柱状图统计信息的话,实际上性能上更是个问题.
因为这时oracle会将字面值先替换成绑定变量,然后使用传入的值看是否需要重新优化,如果需要的话,oracle会插入一个新的版本,但使用不同的执行计划(sql语句都是一样的,因为都使用绑定变量了),这样这个sql语句的不同版本会很多,这样软分析时,library cache latch的持有时间会偏长,从而导致性能上的问题.

zhaosj1726

补充些以前工作中遇到的实际例子:
数据库版本:10.2.0.4

create table zsj_objs as select * from dba_objects;

使用本地动态SQL实现:

create or replace function f_zsj_cursor(v_owner varchar2,v_object_type varchar2,v_object_id number,v_created date) return sys_refcursor
is
   v_cursor sys_refcursor;
   v_sql varchar2(32000);
   type t_strlist is table of varchar2(100);
   v_strlist t_strlist:=t_strlist();
   v_nls_date_format NLS_SESSION_PARAMETERS.value%type;
begin
   v_sql:='select * from zsj_objs where ';
   if(v_owner is not null) then
       v_strlist.extend();
       v_strlist(v_strlist.count):=upper(v_owner);
       v_sql:=v_sql||'owner=:'||v_strlist.count||' and ';
   end if;
   if(v_object_type is not null) then
       v_strlist.extend();
       v_strlist(v_strlist.count):=upper(v_object_type);
       v_sql:=v_sql||'object_type=:'||v_strlist.count||' and ';
   end if;
   if(v_object_id is not null) then
       v_strlist.extend();
       v_strlist(v_strlist.count):=v_object_id;
       v_sql:=v_sql||'object_id>=:'||v_strlist.count||' and ';
   end if;
   if(v_created is not null) then
       v_strlist.extend();
       v_strlist(v_strlist.count):=to_char(v_created,'yyyy-mm-dd hh24:mi:ss');
       v_sql:=v_sql||'created>=:'||v_strlist.count;
   end if;
   v_sql:=regexp_replace(v_sql,' and $');
   v_sql:=regexp_replace(v_sql,' where $');
   SELECT VALUE into v_nls_date_format FROM NLS_SESSION_PARAMETERS WHERE PARAMETER='NLS_DATE_FORMAT';  
   execute immediate 'alter session set nls_date_format=''yyyy-mm-dd hh24:mi:ss''';


   --下面这部分是静态的,而不是动态的,在最大绑定变量数量可知的情况下,代码冗长些,但至少还可以实现,但如果最大绑定变量数量都不确定的话,这里的代码实现不了
   case v_strlist.count
       when 0 then
            open v_cursor for v_sql;
       when 1 then
            open v_cursor for v_sql using v_strlist(1);
       when 2 then
            open v_cursor for v_sql using v_strlist(1),v_strlist(2);
       when 3 then
            open v_cursor for v_sql using v_strlist(1),v_strlist(2),v_strlist(3);
       when 4 then
            open v_cursor for v_sql using v_strlist(1),v_strlist(2),v_strlist(3),v_strlist(4);
   end case;
   execute immediate 'alter session set nls_date_format='''||v_nls_date_format||'''';
   /*不是太明白nls_变量是open cursor时起作用还是fetch cursor时起作用,如果是fetch cursor时起作用的话,也只能是在调用这个函数的客户端关闭这个引用游标之后把这个nls_变量改回原来的设置值了*/
   return v_cursor;

/*这里object_id,created列上的索引都是可供使用的,因为这些列上并不会应用任何表达式的
where object_id>='50000' 实际上执行的是where object_id>=to_number('50000')
where created>='2009-07-26 12:00:00' 实际上执行的是where created>=to_date(' 2009-07-26 12:00:00','syyyy-mm-dd hh24:mi:ss')
*/
end;
/


但其实很长时间以来一直不知道,如果绑定变量的最大数量都不确定的话,应该如何解决这个问题。
直到看这个帖子时,看到newkid版主的http://www.itpub.net/viewthread.php?tid=1019164
这个帖子才发现其实可以用dbms_sql实现.

那就贴一下dbms_sql的实现版本

使用dbms_sql实现的话,确实要灵活得多.但在11g前,dbms_sql好像还不支持sys_refcursor,所以如果要使用dbms_sql实现上面的逻辑的话,我现在只能使用一个临时表了,不知道是否还有好的实现方式.

CREATE GLOBAL temporary TABLE zsj_objs_temp ON COMMIT PRESERVE rows AS SELECT * FROM zsj_objs WHERE 1=0;

create or replace function f_zsj_cursor2(v_owner varchar2,v_object_type varchar2,v_object_id number,v_created date) return sys_refcursor
is
   v_cursor sys_refcursor;
   v_sql varchar2(32000);
   type t_strlist is table of varchar2(100);
   v_strlist t_strlist:=t_strlist();
   v_nls_date_format NLS_SESSION_PARAMETERS.value%type;
   l_theCursor  INTEGER;
   l_status     INTEGER;
BEGIN
   DELETE FROM zsj_objs_temp;
   v_sql:='insert into zsj_objs_temp select * from zsj_objs where ';
   if(v_owner is not null) then
       v_strlist.extend();
       v_strlist(v_strlist.count):=upper(v_owner);
       v_sql:=v_sql||'owner=:'||v_strlist.count||' and ';
   end if;
   if(v_object_type is not null) then
       v_strlist.extend();
       v_strlist(v_strlist.count):=upper(v_object_type);
       v_sql:=v_sql||'object_type=:'||v_strlist.count||' and ';
   end if;
   if(v_object_id is not null) then
       v_strlist.extend();
       v_strlist(v_strlist.count):=v_object_id;
       v_sql:=v_sql||'object_id>=:'||v_strlist.count||' and ';
   end if;
   if(v_created is not null) then
       v_strlist.extend();
       v_strlist(v_strlist.count):=to_char(v_created,'yyyy-mm-dd hh24:mi:ss');
       v_sql:=v_sql||'created>=:'||v_strlist.count;
   end if;
   v_sql:=regexp_replace(v_sql,' and $');
   v_sql:=regexp_replace(v_sql,' where $');
   SELECT VALUE into v_nls_date_format FROM NLS_SESSION_PARAMETERS WHERE PARAMETER='NLS_DATE_FORMAT';  
   execute immediate 'alter session set nls_date_format=''yyyy-mm-dd hh24:mi:ss''';
   l_theCursor:=Dbms_Sql.OPEN_cursor;
   Dbms_Sql.parse(c=>l_theCursor,STATEMENT=>v_sql,language_flag=>Dbms_Sql.native);
   FOR i IN 1..v_strlist.Count
   LOOP
      Dbms_Sql.bind_variable(c=>l_theCursor,NAME=>':'||i,Value=>v_strlist(i));
   END LOOP;
   l_status:=Dbms_Sql.EXECUTE(l_theCursor);
   COMMIT;
   execute immediate 'alter session set nls_date_format='''||v_nls_date_format||''''; --alter session操作不会commit的
   OPEN v_cursor FOR SELECT * FROM zsj_objs_temp;
   return v_cursor;
end;
/



下面这个执行报错,因为顺序不对,应该是解析后才能绑定变量的.
CREATE OR REPLACE function f_zsj_cursor3(v_owner varchar2,v_object_type varchar2,v_object_id number,v_created date) return sys_refcursor
is
   v_cursor sys_refcursor;
   v_sql varchar2(32000);
   l_theCursor  INTEGER;
   l_status     INTEGER;
   v_var_num    INTEGER:=0;
BEGIN
   DELETE FROM zsj_objs_temp;

   v_sql:='insert into zsj_objs_temp select * from zsj_objs where ';
   if(v_owner is not null) then
       v_var_num:=v_var_num+1;
       v_sql:=v_sql||'owner=:'||v_var_num||' and ';
       Dbms_Sql.bind_variable(c=>l_theCursor,NAME=>':'||v_var_num,Value=>upper(v_owner));
   end if;

   if(v_object_type is not null) then
       v_var_num:=v_var_num+1;
       v_sql:=v_sql||'object_type=:'||v_var_num||' and ';
       Dbms_Sql.bind_variable(c=>l_theCursor,NAME=>':'||v_var_num,Value=>upper(v_object_type));
   end if;

   if(v_object_id is not null) then
       v_var_num:=v_var_num+1;
       v_sql:=v_sql||'object_id>=:'||v_var_num||' and ';
       Dbms_Sql.bind_variable(c=>l_theCursor,NAME=>':'||v_var_num,Value=>v_object_id);
   end if;

   if(v_created is not null) then
       v_var_num:=v_var_num+1;
       v_sql:=v_sql||'created>=:'||v_var_num;
       Dbms_Sql.bind_variable(c=>l_theCursor,NAME=>':'||v_var_num,Value=>v_created);
   end if;

   v_sql:=regexp_replace(v_sql,' and $');
   v_sql:=regexp_replace(v_sql,' where $');


   l_theCursor:=Dbms_Sql.OPEN_cursor;
   Dbms_Sql.parse(c=>l_theCursor,STATEMENT=>v_sql,language_flag=>Dbms_Sql.native);

   l_status:=Dbms_Sql.EXECUTE(l_theCursor);

   COMMIT;

   OPEN v_cursor FOR SELECT * FROM zsj_objs_temp;
   return v_cursor;
end;
/

[ 本帖最后由 zhaosj1726 于 2010-5-14 12:05 编辑 ]

zhaosj1726

使用dbms_sql,应用重载函数,什么样类型的变量就给它绑定什么类型的变量,这样就不需要任何的类型转换了,该用的索引就都可以使用了.
但我觉得代码还是冗长了些,是否可以像我上面那种错误的方法一样实现的简洁些,不要每个if都要写两遍
create or replace function f_zsj_cursor4(v_owner varchar2,v_object_type varchar2,v_object_id number,v_created date) return sys_refcursor
is
   v_cursor sys_refcursor;
   v_sql varchar2(32000);
   l_theCursor  INTEGER;
   l_status     INTEGER;
   v_var_num    INTEGER:=0;
BEGIN
   DELETE FROM zsj_objs_temp;
   v_sql:='insert into zsj_objs_temp select * from zsj_objs where ';
   if(v_owner is not null) then
       v_var_num:=v_var_num+1;
       v_sql:=v_sql||'owner=:'||v_var_num||' and ';
   end if;
   if(v_object_type is not null) then
       v_var_num:=v_var_num+1;
       v_sql:=v_sql||'object_type=:'||v_var_num||' and ';
   end if;
   if(v_object_id is not null) then
       v_var_num:=v_var_num+1;
       v_sql:=v_sql||'object_id>=:'||v_var_num||' and ';
   end if;
   if(v_created is not null) then
       v_var_num:=v_var_num+1;
       v_sql:=v_sql||'created>=:'||v_var_num;
   end if;
   v_sql:=regexp_replace(v_sql,' and $');
   v_sql:=regexp_replace(v_sql,' where $');

   l_theCursor:=Dbms_Sql.OPEN_cursor;
   Dbms_Sql.parse(c=>l_theCursor,STATEMENT=>v_sql,language_flag=>Dbms_Sql.native);
   v_var_num:=0;
   if(v_owner is not null) then
       v_var_num:=v_var_num+1;
       Dbms_Sql.bind_variable(c=>l_theCursor,NAME=>':'||v_var_num,Value=>upper(v_owner));
   end if;
   if(v_object_type is not null) then
       v_var_num:=v_var_num+1;
       Dbms_Sql.bind_variable(c=>l_theCursor,NAME=>':'||v_var_num,Value=>upper(v_object_type));
   end if;
   if(v_object_id is not null) then
       v_var_num:=v_var_num+1;
       Dbms_Sql.bind_variable(c=>l_theCursor,NAME=>':'||v_var_num,Value=>v_object_id);
   end if;
   if(v_created is not null) then
       v_var_num:=v_var_num+1;
       Dbms_Sql.bind_variable(c=>l_theCursor,NAME=>':'||v_var_num,Value=>v_created);
   end if;
   l_status:=Dbms_Sql.EXECUTE(l_theCursor);
   COMMIT;
   OPEN v_cursor FOR SELECT * FROM zsj_objs_temp;
   return v_cursor;
end;
/

zhaosj1726

使用context,sys_context来实现
先是授权:

sys> grant create any context to btocuser;                                                                                    
Grant succeeded.
btocuser> create context zsj_cursor_context using f_zsj_cursor5;  
--创建context,并与f_zsj_cursor5关联起来(只能在这个名称的包,函数,过程里使用dbms_session.set_context来设置它的属性值)

CREATE OR REPLACE function f_zsj_cursor5(v_owner varchar2,v_object_type varchar2,v_object_id number,v_created date) return sys_refcursor
is
   v_cursor sys_refcursor;
   v_sql varchar2(32000);
   v_nls_date_format NLS_SESSION_PARAMETERS.value%type;
   v_var_num INTEGER:=0;
begin
   v_sql:='select * from zsj_objs where ';
   if(v_owner is not null) THEN
       v_var_num:=v_var_num+1;
       v_sql:=v_sql||'owner=sys_context(''zsj_cursor_context'',''n'||v_var_num||''') and ';
       dbms_session.set_context('zsj_cursor_context','n'||v_var_num,upper(v_owner));
   end if;
   if(v_object_type is not null) THEN
       v_var_num:=v_var_num+1;
       v_sql:=v_sql||'object_type=sys_context(''zsj_cursor_context'',''n'||v_var_num||''') and ';
       dbms_session.set_context('zsj_cursor_context','n'||v_var_num,upper(v_object_type));
   end if;
   if(v_object_id is not null) then
       v_var_num:=v_var_num+1;
       v_sql:=v_sql||'object_id>=sys_context(''zsj_cursor_context'',''n'||v_var_num||''') and ';
       dbms_session.set_context('zsj_cursor_context','n'||v_var_num,v_object_id);
   end if;
   if(v_created is not null) THEN
       v_var_num:=v_var_num+1;
       v_sql:=v_sql||'created>=sys_context(''zsj_cursor_context'',''n'||v_var_num||''')';
       dbms_session.set_context('zsj_cursor_context','n'||v_var_num,To_Char(v_created,'yyyy-mm-dd hh24:mi:ss'));
   end if;
   v_sql:=regexp_replace(v_sql,' and $');
   v_sql:=regexp_replace(v_sql,' where $');
   SELECT VALUE into v_nls_date_format FROM NLS_SESSION_PARAMETERS WHERE PARAMETER='NLS_DATE_FORMAT';
   execute immediate 'alter session set nls_date_format=''yyyy-mm-dd hh24:mi:ss''';
   open v_cursor for v_sql;
   execute immediate 'alter session set nls_date_format='''||v_nls_date_format||'''';
   return v_cursor;
end;
/
上面这个实现里也存在着和f_zsj_cursor同样的注意点:
就是dbms_session.set_context设置的属性值都是varchar2的,sys_context的返回值都是varchar2的.
所以需要一些特殊设置,可以在你传入的值端进行隐式的类型转换,而不是在表字段端进行类型转换.

[ 本帖最后由 zhaosj1726 于 2010-5-14 13:03 编辑 ]

zhaosj1726

因为这个dbms_sql包再翻看tom的《oracle专家高级编程》的时候,才发现其实tom在第16章:动态sql中这些问题和解决方案都提到了,自己居然还在书上写了很多注释,明显当时没有理解到位呀,当时只是觉得dbms_sql包使用太麻烦了.看来经典真的需要常常翻一下呀!

zhaosj1726

补充一个in串的实现(使用绑定变量的),只写本地动态SQL的实现形式了
这里不讨论绑定变量实现的优劣.

CREATE OR REPLACE TYPE t_strtable IS TABLE OF VARCHAR2(256);

CREATE OR REPLACE FUNCTION seperate_str(p_str IN VARCHAR2) RETURN t_strTable
IS
   l_str   LONG DEFAULT p_str||',';
   l_n     NUMBER;
   l_data  t_strTable:=t_strTable();
BEGIN
   LOOP
      l_n:=InStr(l_str,',');
      EXIT WHEN (Nvl(l_n,0)=0);
      l_data.extend();
      l_data(l_data.count):=LTrim(RTrim(SubStr(l_str,1,l_n-1)));
      l_str:=SubStr(l_str,l_n+1);
   END LOOP;
   RETURN l_data;
END;
/



create or replace function f_zsj_cursor8(v_owners varchar2,v_object_type varchar2,v_object_id number,v_created date) return sys_refcursor
is
   --v_owners可能是sys 或者sys,system,scott之类的形式
   v_cursor sys_refcursor;
   v_sql varchar2(32000);
   type t_strlist is table of varchar2(100);
   v_strlist t_strlist:=t_strlist();
   v_nls_date_format NLS_SESSION_PARAMETERS.value%type;
begin
   v_sql:='select * from zsj_objs where ';
   if(v_owners is not null) then
   --这里一个owner和多个(两个及两个以上)owner分开处理了
       if( instr(v_owners,',')>0 ) then
           --这里假定owner的数据分布是均匀的,且典型输入情况下是输入5个owner连成的串,所以使用了cardinality(5)的提示
           v_strlist.extend();
           v_strlist(v_strlist.count):=upper(v_owners);
           v_sql:=v_sql||'owner in(select /*+ cardinality(5)*/column_value from TABLE(seperate_str(:'||v_strlist.count||'))) and ';           
       else
           v_strlist.extend();
           v_strlist(v_strlist.count):=upper(v_owners);
           v_sql:=v_sql||'owner=:'||v_strlist.count||' and ';
       end if;
   end if;

   if(v_object_type is not null) then
       v_strlist.extend();
       v_strlist(v_strlist.count):=upper(v_object_type);
       v_sql:=v_sql||'object_type=:'||v_strlist.count||' and ';
   end if;

   if(v_object_id is not null) then
       v_strlist.extend();
       v_strlist(v_strlist.count):=v_object_id;
       v_sql:=v_sql||'object_id>=:'||v_strlist.count||' and ';
   end if;

   if(v_created is not null) then
       v_strlist.extend();
       v_strlist(v_strlist.count):=to_char(v_created,'yyyy-mm-dd hh24:mi:ss');
       v_sql:=v_sql||'created>=:'||v_strlist.count;
   end if;

   v_sql:=regexp_replace(v_sql,' and $');
   v_sql:=regexp_replace(v_sql,' where $');

   SELECT VALUE into v_nls_date_format FROM NLS_SESSION_PARAMETERS WHERE PARAMETER='NLS_DATE_FORMAT';  

   execute immediate 'alter session set nls_date_format=''yyyy-mm-dd hh24:mi:ss''';

   case v_strlist.count
       when 0 then
            open v_cursor for v_sql;
       when 1 then
            open v_cursor for v_sql using v_strlist(1);
       when 2 then
            open v_cursor for v_sql using v_strlist(1),v_strlist(2);
       when 3 then
            open v_cursor for v_sql using v_strlist(1),v_strlist(2),v_strlist(3);
       when 4 then
            open v_cursor for v_sql using v_strlist(1),v_strlist(2),v_strlist(3),v_strlist(4);
   end case;

   execute immediate 'alter session set nls_date_format='''||v_nls_date_format||'''';

   return v_cursor;
end;
/

matin_mo

格式看得难受，原创还是要顶

guitar1314

学习。。。

xiaopingzhang

文档写得不是很好。