我在存储过程中定义如下存储过程:
create or replace procedure pro_test(pString in varchar) as
.....
CURSOR c_test IS
select id,name from test_t
where id in (...);
.......
我希望能把pString 这个串放入in里面,这样形成语句的动态拼装,但事实上,pString是一个形如'1,2,3,4...'的字符串,而in里面需要的是1,2,3,4....这样的数字集合,如此一来编译就不能通过,故向各位请教解决方法。
create or replace procedure pro_test(pString in varchar) as
.....
CURSOR c_test IS
select id,name from test_t
where id in (...);
.......
我希望能把pString 这个串放入in里面,这样形成语句的动态拼装,但事实上,pString是一个形如'1,2,3,4...'的字符串,而in里面需要的是1,2,3,4....这样的数字集合,如此一来编译就不能通过,故向各位请教解决方法。
create or ....
is
type cur_type is ref cursor;
c cur_type;
...
begin
open c for select .... where ... in (''||someString||'');
...
/
create or replace function strtab(p_str in varchar2)
return mytabletype
as
lstr varchar2(1000) default p_str||',';
ln number;
ldata mytabletype:=mytabletype();
begin
loop
ln:=instr(lstr,',');
exit when (nvl(ln,0)=0);
ldata.extend;
ldata(ldata.count):=ltrim(rtrim(substr(lstr,1,ln-1)));
lstr:=substr(lstr,ln+1);
end loop;
return ldata;
end;
/
SQL> select * from table(cast(strtab('11,12,13') as mytabletype));COLUMN_VALUE
------------
11
12
13create or replace procedure pro_test(pString in varchar) as
.....
CURSOR c_test IS
select id,name from test_t
where id in (select * from table(cast(strtab(pString) as mytabletype)));
1、
像以上用对象类型
2、
动态sql,但这种接受参数也要进行处理,不好做