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;多谢!
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;多谢!
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货