有时间要把最近总结的存储过程的问题总结一下,先占坑;
1 create or replace procedure test_proc2 as 2 cursor cur is select YWR,YWRZJH from dyrxx ; --定义游标 3 TYPE test_type IS TABLE OF cur%ROWTYPE; 4 test_data test_type; 5 6 v_v1 number(10); 7 v_v2 number(10); 8 9 begin 10 11 v_v1 := 0; 12 v_v2 := 0; 13 14 -- 打开游标. 15 OPEN cur; 16 --开始循环 17 LOOP 18 -- 填充数据批量填充,每次读取2条. 19 FETCH cur BULK COLLECT INTO test_data LIMIT 200; 20 21 FOR i IN 1..test_data.count 22 loop 23 select count(t2.qlid) into v_v1 from ( 24 select t.qlid from gd_fwsyq t 25 left join gd_qlr a on a.qlid=t.qlid and a.qlrlx='qlr' 26 where t.iszx=0 and a.qlr is not null 27 and a.qlr=test_data(i).YWR and a.qlrzjh=test_data(i).YWRZJH 28 group by t.qlid 29 )t2; 30 31 select count(t2.qlid) into v_v2 from ( 32 select a.qlid from bdc_fdcq a 33 left join bdc_qlr b on a.proid=b.proid and b.qlrlx='qlr' 34 where a.qszt=1 35 and b.qlr=test_data(i).YWR and b.qlrzjh=test_data(i).YWRZJH 36 group by a.qlid 37 )t2; 38 39 -- dbms_output.put_line(v_v1+v_v2); 40 41 insert into ZJT_TEST_TEMP VALUES(v_v1+v_v2); 42 43 end loop; 44 45 -- 当没有数据的时候,退出。 46 47 EXIT WHEN cur%NOTFOUND; 48 49 50 END LOOP; 51 commit; 52 --关闭游标. 53 CLOSE cur; 54 55 end test_proc2;