drop table t_test; create table t_test(a varchar2(10)); insert into t_test values('a1'); insert into t_test values('a2'); insert into t_test values('a3'); insert into t_test values('a4'); insert into t_test values('1'); insert into t_test values('2'); insert into t_test values('3'); commit; / create or replace procedure p_test(str varchar2) is begin for i in (with t1 as (select regexp_substr(str, '[^,]+', 1, level) a from dual connect by level <= regexp_count(str, ',') + 1) select t.* from t_test t, t1 where t.a = trim(t1.a)) loop
dbms_output.put_line(i.a); end loop;end;传入a1,a2,1 可得到结果。
上面那个过程如果传入字符就错了,应该用: create or replace procedure p_test(str varchar2) is begin for i in (with t1 as (select regexp_substr(str, '[^,]+', 1, level) a from dual connect by level <= regexp_count(str, ',') + 1) select t.* from t_test t, t1 where t.a = t1.a) loop dbms_output.put_line(i.a); end loop;end;
create table t_test(a varchar2(10));
insert into t_test values('a1');
insert into t_test values('a2');
insert into t_test values('a3');
insert into t_test values('a4');
insert into t_test values('1');
insert into t_test values('2');
insert into t_test values('3');
commit;
/
create or replace procedure p_test(str varchar2) is
begin for i in (with t1 as
(select regexp_substr(str, '[^,]+', 1, level) a
from dual
connect by level <= regexp_count(str, ',') + 1)
select t.* from t_test t, t1 where t.a = trim(t1.a)) loop
dbms_output.put_line(i.a);
end loop;end;传入a1,a2,1 可得到结果。
create or replace procedure p_test(str varchar2) is
begin for i in (with t1 as
(select regexp_substr(str, '[^,]+', 1, level) a
from dual
connect by level <= regexp_count(str, ',') + 1)
select t.* from t_test t, t1 where t.a = t1.a) loop
dbms_output.put_line(i.a);
end loop;end;
我的意思就是如果你传入的是 12456,54215,54214,就将其拆分为
12456
54215
54214
变为3行。
然后去跟你原有的B.ITEMID 进行关联。