create or replace type mytabletype as table of number; /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 13SQL> create table bb(id varchar2(2),name varchar2(10));Table createdSQL> insert into bb values('11','张三');1 row insertedSQL> insert into bb values('12','李四');1 row insertedSQL> insert into bb values('13','王五');1 row insertedSQL> select * from bb where id in (select * from table(cast(strtab('11,12,13') as mytabletype)));ID NAME -- ---------- 11 张三 12 李四 13 王五
select * from function
where id in(select to_number(userfunciton) from b)
这样的一串
1,2,3,4,5,6是不是因为中间逗号
/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
13SQL> create table bb(id varchar2(2),name varchar2(10));Table createdSQL> insert into bb values('11','张三');1 row insertedSQL> insert into bb values('12','李四');1 row insertedSQL> insert into bb values('13','王五');1 row insertedSQL> select * from bb where id in (select * from table(cast(strtab('11,12,13') as mytabletype)));ID NAME
-- ----------
11 张三
12 李四
13 王五