请各位高手帮偶看看下面的存储过程能不能优化，现在运行特别占用资源,先在这里多谢了。

[zensyunkou]

CREATE OR REPLACE PROCEDURE "PROC_GETCONTRELABYPLEDCONT" (pledcontno varchar2) IS

--声明动态游标
type r1_cur is ref cursor;
cur_cont r1_cur ;

cur_pledcont r1_cur ;
--结果变量存放
str_pledcontno varchar2(32000):=''''||pledcontno||'''';
str_contno varchar2(32000):=''',''';

--临时变量
temp_cont varchar2(300);
temp_pledcont varchar2(300);
--退出循环的开关
i int:=0;
--sql
ssql_pledcont varchar2(32000);
ssql_cont varchar2(32000);


BEGIN

packg_cont.cont_array:=packg_cont.onearary_type();

packg_cont.pledcont_array:=packg_cont.onearary_type();

packg_cont.pledcont_array.extend;
packg_cont.pledcont_array(packg_cont.pledcont_array.count):=pledcontno;

while 1=1 loop


i:=0;
ssql_cont:='select distinct loancont_No from sta_cont_assure_rela where Status=''0'' and loancont_No not in ('||str_contno||') and PledCont_No in ('||str_pledcontno||')';

open cur_cont for ssql_cont;

fetch cur_cont into temp_cont;
while cur_cont%found loop

i:=1;
str_contno:=str_contno||','''||temp_cont||'''';

packg_cont.cont_array.extend;
packg_cont.cont_array(packg_cont.cont_array.count):=temp_cont;
--去掉不用的字符
if substr(str_contno,2,1)=',' then
str_contno:=substr(str_contno,5);
end if;

ssql_pledcont:='select distinct PledCont_No from sta_cont_assure_rela where Status=''0'' and loancont_No in ('||str_contno||') and PledCont_No not in ('||str_pledcontno||')';
--2 循环
-- DBMS_OUTPUT.put_line('ssql_cont========'||ssql_pledcont);

fetch cur_cont into temp_cont;

open cur_pledcont for ssql_pledcont;

fetch cur_pledcont into temp_pledcont;

while cur_pledcont%found loop

packg_cont.pledcont_array.extend;
packg_cont.pledcont_array(packg_cont.pledcont_array.count):=temp_pledcont;

str_pledcontno:=str_pledcontno||','''||temp_pledcont||'''';

fetch cur_pledcont into temp_pledcont;
end loop;

close cur_pledcont;
--end 2
fetch cur_cont into temp_cont;
end loop;

close cur_cont;




exit when i=0 ;
end loop;

--给数组是否存在数据赋值，方便以后使用
begin
if packg_cont.cont_array.count >0 then
packg_cont.ary_isexits:=true;
else
packg_cont.ary_isexits:=false;
end if;
exception
when others then
packg_cont.ary_isexits:=false;

end ;
END proc_getcontrelabypledcont;

多谢！

[hyee]

你使用select .. bulk collect into ... from...不就好了吗，没必要使用循环吧

[zensyunkou]

谢谢hyee。
是下面这样吗？
fetch cur_cont bulk collect into temp_cont;

while cur_cont%found loop这个循环就不用了吗

本人没写过存储过程，是菜鸟，希望hyee多帮忙，这个问题比较急。

[complexgg]

用隐式游标比显式游标快...

[zensyunkou]

回复：complexgg

隐式游标怎么写啊，救下急吧

[complexgg]

[Quote=引用 4 楼 zensyunkou 的回复:]
回复：complexgg

隐式游标怎么写啊，救下急吧
[/Quote]
for x in (.select .. from...) Loop
...
...
End Loop;

[hyee]

wo zhe li mei you huan jing, jin gong can kao:

CREATE OR REPLACE PROCEDURE "PROC_GETCONTRELABYPLEDCONT" (pledcontno varchar2) IS

--声明动态游标
type r1_cur is ref cursor;
cur_cont r1_cur ;

cur_pledcont r1_cur ;
--结果变量存放
str_pledcontno varchar2(32000):=''''||pledcontno||'''';
str_contno varchar2(32000):=''',''';

--临时变量
temp_cont varchar2(300);
temp_pledcont varchar2(300);
--退出循环的开关
i int:=0;
--sql
ssql_pledcont varchar2(32000);
ssql_cont varchar2(32000);


BEGIN

packg_cont.cont_array:=packg_cont.onearary_type();

packg_cont.pledcont_array:=packg_cont.onearary_type();

packg_cont.pledcont_array.extend;
packg_cont.pledcont_array(packg_cont.pledcont_array.count):=pledcontno;
execute immediate 'select distinct loancont_No from sta_cont_assure_rela where Status=''0'' and loancont_No not in ('||str_contno||') and PledCont_No in ('||str_pledcontno||')' bulk collect into packg_cont.cont_array;

execute immediate 'select distinct PledCont_No from sta_cont_assure_rela where Status=''0'' and loancont_No in ('||str_contno||') and PledCont_No not in ('||str_pledcontno||')' bulk collect into packg_cont.pledcont_array;
END proc_getcontrelabypledcont;

[zensyunkou]

多谢hyee，我试试

[zensyunkou]

hyee:

这样还是特别慢,还是要谢谢你！