问题一:
create function get_str(p_table in varchar2)
return varchar2
is
type t_sor is ref cursor;
v_name varchar2(30);
v_sor t_sor;
sqlstr varchar2(100);
begin
open v_sor is 'select name from '||p_table;
loop
fetch v_sor into v_name;
exit when v_sor%notfound;
sqlstr:=sqlstr||v_name||',';
end loop;
sqlstr:=substr(sqlstr,1,length(sqlstr)-1);
return sqlstr;
end;
/
create function get_str(p_table in varchar2)
return varchar2
is
type t_sor is ref cursor;
v_name varchar2(30);
v_sor t_sor;
sqlstr varchar2(100);
begin
open v_sor is 'select name from '||p_table;
loop
fetch v_sor into v_name;
exit when v_sor%notfound;
sqlstr:=sqlstr||v_name||',';
end loop;
sqlstr:=substr(sqlstr,1,length(sqlstr)-1);
return sqlstr;
end;
/
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 王五
create function get_str(p_table in varchar2)
return varchar2
is
type t_sor is ref cursor;
v_name varchar2(30);
v_sor t_sor;
sqlstr varchar2(100);
begin
open v_sor is 'select name from '||p_table;
loop
fetch v_sor into v_name;
exit when v_sor%notfound;
sqlstr:=sqlstr||v_name||',';
end loop;
close v_sor;
sqlstr:=substr(sqlstr,1,length(sqlstr)-1);
return sqlstr;
end;
/