SQL> desc aa
名称 是否为空? 类型
----------------------------------------- -------- -----------
ID NUMBER(2)
NAME VARCHAR2(8)
SQL> select * from aa;
ID NAME
---------- --------
1 aa
2 gg
50 ddgg
create or replace procedure test(condition in varchar2)
as
type mycursor is ref cursor;
cursor_select mycursor;
sqlstr varchar2(500);
my_record aa%rowtype;
begin
sqlstr := 'select * from aa where id in ('||condition ||')';
open cursor_select for sqlstr;
loop
fetch cursor_select into my_record;
exit when cursor_select%notfound;
dbms_output.put_line(my_record.id||' '||my_record.name);
end loop;
close cursor_select;
end;
/
create or replace procedure test(content varchar2) is
i pls_integer;
j pls_integer;
str varchar2(100);
type tarr is varray(100) of number;
arr tarr:=tarr();
rec emp%rowtype;
begin
i:=0;
str:=content;
loop
j:=instr(str,',');
if j<=0 then
exit;
end if;
arr.extend;
i:=i+1;
arr(i):=substr(str,1,j-1);
str:=substr(str,j+1);
end loop;
arr.extend;
i:=i+1;
arr(i):=str;
for i in arr.first..arr.last loop
select * into rec from emp where employee_id=arr(i);
dbms_output.put_line(rec.employee_id||' '||rec.first_name);
end loop;
end;
改了一下,速度应该更快
SQL> desc aa
名称 是否为空? 类型
----------------------------------------- -------- -----------
ID NUMBER(2)
NAME VARCHAR2(8)
SQL> select * from aa;
ID NAME
---------- --------
1 aa
2 gg
50 ddgg
10 孙悟空
create or replace procedure test(condition in varchar2)
as
type mycursor is ref cursor;
cursor_select mycursor;
sqlstr varchar2(500);
my_record aa%rowtype;
begin
sqlstr := 'select * from aa where instrb('','||condition||','','',''||id||'','') > 0';
--dbms_output.put_line(sqlstr);
open cursor_select for sqlstr;
loop
fetch cursor_select into my_record;
exit when cursor_select%notfound;
dbms_output.put_line(my_record.id||' '||my_record.name);
end loop;
close cursor_select;
end;
/
过程已创建
SQL> select * from aa;
ID NAME
---------- --------
1 aa
2 gg
50 ddgg
10 孙悟空