declare cursor mycursor(p_1 varchar2) is select id from t_fl where cx like '%'||p_1||'%'; begin
另外 open mycursor(mmcx); 改成 open mycursor(mcx);
按照bzszp得改法 open mycursor(mcx);
create or replace procedure p_test ( mcx in varchar2, merror out varchar2 ) is type v_sl is table of number; a_v_sl v_sl:= v_sl(); begin declare i number(10); begin declare cursor mycursor(p_1 varchar2) is select id from t_fl where cx like '%'||p_1||'%'; begin open mycursor(mcx); fetch mycursor into a_v_sl(1); i:=2; while mycursor%found loop fetch mycursor into a_v_sl(i); i:=i+1; end loop; close mycursor; end; end; merror:=a_v_sl(1); return; exception when others then merror := 0; rollback; return ; end;谢谢楼上的朋友们,我现在按你们,改成这样的,但结果一直还回0,说明有错,道理应还回1(因第一条数据的ID是1)
你的a_v_sl没有扩展(extend)元素,所以在运行到 fetch mycursor into a_v_sl(1); 一句是就出错,转到 exception 了。所以结果一直是0 a_v_sl.extend; fetch mycursor into a_v_sl(1); a_v_sl.extend; fetch mycursor into a_v_sl(i);
cursor mycursor(p_1 varchar2) is
select id from t_fl where cx like '%'||p_1||'%';
begin
open mycursor(mmcx);
改成
open mycursor(mcx);
open mycursor(mcx);
(
mcx in varchar2,
merror out varchar2
)
is
type v_sl is table of number;
a_v_sl v_sl:= v_sl();
begin
declare
i number(10);
begin
declare
cursor mycursor(p_1 varchar2) is
select id from t_fl where cx like '%'||p_1||'%';
begin
open mycursor(mcx);
fetch mycursor into a_v_sl(1);
i:=2;
while mycursor%found loop
fetch mycursor into a_v_sl(i);
i:=i+1;
end loop;
close mycursor;
end;
end;
merror:=a_v_sl(1);
return;
exception
when others then
merror := 0;
rollback;
return ;
end;谢谢楼上的朋友们,我现在按你们,改成这样的,但结果一直还回0,说明有错,道理应还回1(因第一条数据的ID是1)
fetch mycursor into a_v_sl(1); a_v_sl.extend;
fetch mycursor into a_v_sl(i);
我费一个上午的时间,我自己也刚发现,加了a_v_sl.extend(10)就可以;
前天宿舍被盗,两台5月份买的笔记本和三部手机被盗.这几天情绪抵,工作都不能集中结账