oracle使用dblink和cursor更新不同数据库的记录

来源：互联网发布：淘宝网运营模式分析编辑：程序博客网时间：2024/05/28 06:05

一、部分SQL文件内容：
drop public database link sde_link;
create public database link sde_link connect to "sde" identified by "sde" using 'zcserver';
select zdid from sde.nzhxm@sde_link nzhxm;

declare
       cursor cursor1 IS select t.zdid as zdid from sde.zdxm@sde_link t where (t.zdid is not null or t.zdid <> '' or t.zdid <> ' ');
       zdid sde.zdxm.zdid@sde_link%TYPE;
begin
       if not cursor1%isopen then
          open cursor1;
       end if;
       loop
          fetch cursor1 into zdid;
          exit when cursor1%notfound;
          update sde.zdxm@sde_link t set t.xmbh = (select dk.sdxmid from zcgt.cbgl_info_zddk dk where dk.id = zdid);
       end loop;
       close cursor1;

commit;
end;

二、bat文件内容
rem 更新[**********updata_gdxm_xmbh.sql**********]
sqlplus sde/sde@zcserver @updata_gdxm_xmbh.sql

rem 更新[**********updata_zdxm_xmbh.sql**********]
sqlplus sde/sde@zcserver @updata_zdxm_xmbh.sql

rem 更新[**********updata_nzhxm_xmbh.sql**********]
sqlplus sde/sde@zcserver @updata_nzhxm_xmbh.sql

rem 结束
pause