17,086
社区成员
发帖
与我相关
我的任务
分享
create or replace procedure qgzt_trigger_job is
CURSOR cursor_1 is
select * from (select * from t_qg_ztry_trigger a
left outer join t_qg_ztry b on a.ztryid=b.r_rybh
order by a.id
) c
where rownum<=1 and isuse=0;
c_row cursor_1%ROWTYPE;
begin
for c_row in cursor_1 loop
if c_row.state=1 then
insert into "wl_national_pursuit"@mysqlodbc values(c_row.r_rybh,c_row.r_xm,c_row.r_bmch,c_row.r_xb,c_row.r_csrq,
c_row.r_sfzh,c_row.r_hjdxz,c_row.r_xzdxz,c_row.r_sg,c_row.r_ajbh,c_row.r_ajlb,
c_row.r_tprq,c_row.r_dbjb,c_row.r_tjljb,c_row.r_jj,c_row.r_ladwxc,c_row.r_zbr,c_row.r_lxfs,c_row.r_jyaq,c_row.r_mz);
elsif c_row.state=2 then
update "wl_national_pursuit"@mysqlodbc set r_rybh=c_row.r_rybh,r_xm=c_row.r_xm,r_bmch=c_row.r_bmch,r_xb=c_row.r_xb,r_csrq=c_row.r_csrq,
r_sfzh=c_row.r_sfzh,r_hjdxz=c_row.r_hjdxz,r_xzdxz=c_row.r_xzdxz,r_sg=c_row.r_sg, r_ajbh=c_row.r_ajbh,r_ajlb=c_row.r_ajlb,
r_tprq=c_row.r_tprq,r_dbjb=c_row.r_dbjb,r_tjljb=c_row.r_tjljb,r_jj=c_row.r_jj,r_ladwxc=c_row.r_ladwxc,r_zbr=c_row.r_zbr,
r_lxfs=c_row.r_lxfs,r_jyaq=c_row.r_jyaq,r_mz=c_row.r_mz where r_rybh=c_row.r_rybh;
elsif c_row.state=3 then
delete from "wl_national_pursuit"@mysqlodbc where r_rybh=c_row.r_rybh;
end if;
delete from t_qg_ztry_trigger where id=c_row.id;
--EXCEPTION
--when others then
--continue;
end loop;
commit;
dbms_output.put_line('--------------:'|| 'fish');
end;