oracle动态SQL绑定集合变量

有如下需求，在存储过程中，传递的参数是字符串，字符串格式是‘1,2,3 ’, 由数字组成，中间用','号分割；然后在SQL语句如下使用：

 

create or replace procedure moveToRight  (v_ids varchar2, v_workid int )
as
i_sql varchar2(1000);
begin
	 /*
	  -- v_ids 的格式 '1,2,3'转换成1,2,3的集合
	  */ 
   -- oracle 动态SQL列名不能使用替代形式，只能拼凑
    i_sql:=' insert into profworktype (workid,worktypeid) select  '|| v_workid ||' ,id from  worktype where  id  in (:1)  and id not in ( select worktypeid  from  profworktype where workid = :2 ) ';
      execute immediate i_sql  using  v_ids, v_workid;
   end;

 注意上面红色标记的参数传递，是显然不对的，因为使用in后面必须是集合，集合要么来自于select语句，要么自己封装，具体参考：http://space.itpub.net/756652/viewspace-242174 ；做法主要是将字符串转换成数字集合，然后通过select语句即可；代码如下：

1、首先创建一个类型，用于存储数据

create or replace type numTableType as table of number ;

 2、创建一个函数负责完成字符串到集合的转换

-- 代码来源于 http://space.itpub.net/756652/viewspace-242174
create or replace function str2numlist (v_ids varchar2)
 return  numTableType 
 as 
  v_str long default v_ids || ','; 
  v_n number; 
  v_data  numTableType := numTableType (); 

begin
    loop 
  v_n := to_number(instr( v_str, ',' )); 
          exit when (nvl(v_n,0) = 0); 
 v_data.extend; 
v_data( v_data.count ) := ltrim(rtrim(substr(v_str,1,v_n-1))); 
 v_str := substr( v_str, v_n+1 ); 
end loop; 
 return v_data; 
 end; 

 3、在存储过程中调用该函数即可；

create or replace procedure moveToRight  (v_ids varchar2, v_workid int )
as
i_sql varchar2(1000);
begin
	 /*
	  -- v_ids 的格式 '1,2,3'转换成1,2,3的集合
	  */ 
   -- oracle 动态SQL列名不能使用替代形式，只能拼凑
    i_sql:=' insert into profworktype (workid,worktypeid) select  '|| v_workid ||' ,id from  worktype where  id  in (select * from the(select cast (str2numlist( :1) as numtableType ) from dual) )  and id not in ( select worktypeid  from  profworktype where workid = :2 ) ';
      execute immediate i_sql  using  v_ids, v_workid;
   end;

 注意select * from the(表函数返回的结果集集合)的用法，查找了半天没有找到官方说明，欢迎指出。但是下面是等价的：

select * from the(select cast (str2numlist( :1) as numtableType ) from dual) 

-- 等价于

select * from table (  cast (str2numlist( '1,2') as numtableType )   )

 

 转载注明来源： http://yuexiaodong.iteye.com/blog/1892360