有如下需求,在存储过程中,传递的参数是字符串,字符串格式是‘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