plsql变量赋值问题

Question

向一个变量传入 10001,10002 这种样式用于查询,但目前这样写会报错,请指教!

--数据准备
create table wareinfo(WareID number(5),WareName varchar(50));
insert into wareinfo values(10001,'A商品');
insert into wareinfo values(10002,'B商品');

select * from wareinfo;

--查询语句
declare var_WareID varchar2(100) ;
var_WareName varchar2(100);
var_WareCursor sys_refcursor;
begin
var_WareID:='10001,10002';
open var_WareCursor for
--select wareinfo.WareName from wareinfo where WareID in (10001,10002) ;
select wareinfo.WareName from wareinfo where WareID in (var_WareID) ; --通过变量得到和上一句等价语句,但目前这样写会报错,请指教!
fetch var_WareCursor into var_WareName;
while var_WareCursor%found loop--遍历游标
dbms_output.put_line(var_WareName);
fetch var_WareCursor into var_WareName;
end loop;
end;

Answer 1

你传入的是一个字符串，需要处理成一个数组，或者转换成一列，下面这段使用了递归来将字符串分割成一列

declare var_WareID varchar2(100) ;
var_WareName varchar2(100);
var_WareCursor sys_refcursor;
begin
var_WareID:='10001,10002';
open var_WareCursor for
select wareinfo.WareName from wareinfo where WareID in (
select regexp_substr(var_WareID,'[^,]+',1,level) from dual connect by level<=regexp_count(var_WareID,'[^,]+')
) ; 
fetch var_WareCursor into var_WareName;
while var_WareCursor%found loop--遍历游标
dbms_output.put_line(var_WareName);
fetch var_WareCursor into var_WareName;
end loop;
end;