create or replace procedure DongjieToTable --创建一个名为donjietotable的存储过程
as --定义数据区
cursor c_qydjsx --定义一个游标
is select nbxh from aqydjsx;
cursor c_sttzqk
is select tznbxh from asttzqk;
V_nbxh varchar2(40); --定义一个变量
V_tznbxh varchar2(40);
begin --代码块开始区
open c_qydjsx(); --打开一个游标
fetch c_qydjsx into V_nbxh;
while c_qydjsx%found loop
update aqydjsx set djlx = '01'
where '1' in
(select sfjd from qydongjie where qynbxh=V_nbxh and djjdjzsj = (select max(djjdjzsj) from qydongjie where qynbxh=V_nbxh))
and '0' not in
(select sfjd from qydongjie where qynbxh=V_nbxh and djjdjzsj = (select max(djjdjzsj) from qydongjie where qynbxh=V_nbxh))
and nbxh = V_nbxh;
fetch c_qydjsx into V_nbxh;
end loop;
commit;
open c_sttzqk();
fetch c_sttzqk into V_tznbxh;
while c_sttzqk%found loop
update asttzqk set djlx = '01'
where '1' in
(select sfjd from qydongjie where qynbxh=V_tznbxh and djjdjzsj = (select max(djjdjzsj) from qydongjie where qynbxh=V_tznbxh))
and '0' not in
(select sfjd from qydongjie where qynbxh=V_tznbxh and djjdjzsj = (select max(djjdjzsj) from qydongjie where qynbxh=V_tznbxh))
and tznbxh = V_tznbxh;
fetch c_sttzqk into V_tznbxh;
end loop;
commit;
EXCEPTION --异常块,当以上代码执行有异常时将执行这里
WHEN others THEN
rollback;
end; --结束代码块
要执行创建好的存储过程在Command中exec dongjietotable;
如果在代码中要根据一个动态的值去查一个表,那么可以用有参游标
创建方法
cursor c_qynjjbqk(EpNbxh varchar2) --变量名为EpNbxh,类型为varchar2
is select max(nd) from qynjjbqk where nbxh=EpNbxh;
调用方法
open c_qynjjbqk(nbxh); --"()"内为要传入的参数值