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;多谢!
解决方案 »
- 相同查询条件,新增了关联的表,确提升了查询效率!?求大师
- 求教分析函数 last_value
- oracle 寻找10g rman备份与恢复电子书
- 访问远程数据库的问题!!求救!
- oracle执行完sql文件在表docManagement中插入数据后,数据为啥都用不了呢。跪求各路高手指点!!急
- 已知V_SUM:=$100,000+$250,000那么v_sum是什么类型?
- 100分急教!建立了两个数据库,但在DBA STUDIO里只能看到一个,SQLPLUS里不知怎么访问?
- 请问如何在decode里判断非空
- 请问:在客户端可以执行备份数据库的程序么??我用exp80 txm/111111 file=ccc.bak执行不成功
- EM RemoteOperationException: ERROR: Invalid username and/or password
- oracle 主键自增.net
- jdbc如何在嵌套查询中处理连接的关闭问题?
是下面这样吗?
fetch cur_cont bulk collect into temp_cont;while cur_cont%found loop这个循环就不用了吗本人没写过存储过程,是菜鸟,希望hyee多帮忙,这个问题比较急。
隐式游标怎么写啊,救下急吧
...
...
End Loop;
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;