我的存储过程的大致结果如下:
create or replace procedure xxxxx
is
--需要更新操作的数据的游标
CURSOR ctmp IS
select a.* from a_tmp a,b_tmp b
where a.xh=b.xh; rtmp a_tmp%ROWTYPE;
type c_type is ref cursor;
c_xx c_type;
rxx c_tmp%ROWTYPE;
begin OPEN ctmp;
LOOP
BEGIN
fetch ctmp into rtmp;
exit when ctmp%notfound; UPDATE ......
open c_xx for '
select * from c_tmp
where xh=:1'
using rtmp.xh; fetch c_xx into rxx; UPDATE ....... close c_syxx; END;
END LOOP;
CLOSE ctmp;
end xxxxx;
create or replace procedure xxxxx
is
--需要更新操作的数据的游标
CURSOR ctmp IS
select a.* from a_tmp a,b_tmp b
where a.xh=b.xh; rtmp a_tmp%ROWTYPE;
type c_type is ref cursor;
c_xx c_type;
rxx c_tmp%ROWTYPE;
begin OPEN ctmp;
LOOP
BEGIN
fetch ctmp into rtmp;
exit when ctmp%notfound; UPDATE ......
open c_xx for '
select * from c_tmp
where xh=:1'
using rtmp.xh; fetch c_xx into rxx; UPDATE ....... close c_syxx; END;
END LOOP;
CLOSE ctmp;
end xxxxx;
应该两条update就可以搞掂了
游标的效率一般都不高,而且你在这里还有一个嵌套的游标,想想做10000次select也不会快吧
请多多指点,我要在游标A、B里获取数据,从而进行更新或者插入操作。thanks in advace.