一个数组变量,值为(1,1,1,2,2...组值有重复),将此数组变量作为条件写入到select语句中,如果用类似"select * from table where table.field in(1,1,1,2,2)"的语句时,则只返回2条记录。现在需要返回符合条件的并且记录数为5条的情况,如何处理?
访问数组用下标元素,这样体现不到数组的用途。 create type t_varr as varray(10) of number; / declare v_varr t_varr:=(1,1,2,2....); cursor t_sor(p_varr t_varr) is select * from table where table.field=p_varr; num number:=1; begin for v_sor in t_Sor(v_varr(num)) loop exit when num=10; dbms_output.put_line(.....); num:=num+1; end loop; end; /
那如果用以上的游标t_Sor 做为参数返回的话,在外部能调用出需要的记录集么?
create or replace procudure name_pro(p_v t_varr,p_rc out pack.mytype) as p_v t_varr:=(1,1,2,2,....); str varchar2(100); begin str:='select * from table where table.field in ('; for i in 1..10 loop str:=str||','||p_v(i); end loop; str:=str||')'; open p_rc for str; end; /
create type t_varr as varray(10) of number;
/
declare
v_varr t_varr:=(1,1,2,2....);
cursor t_sor(p_varr t_varr) is
select * from table where table.field=p_varr;
num number:=1;
begin
for v_sor in t_Sor(v_varr(num)) loop
exit when num=10;
dbms_output.put_line(.....);
num:=num+1;
end loop;
end;
/
as
p_v t_varr:=(1,1,2,2,....);
str varchar2(100);
begin
str:='select * from table where table.field in (';
for i in 1..10 loop
str:=str||','||p_v(i);
end loop;
str:=str||')';
open p_rc for str;
end;
/