Oracle通过函数调用dblink同步表数据方案（全量/增量）

创建对应的包，以方便触发调用

/*包声明*/
CREATE OR REPLACE PACKAGE yjb.pkg_scene_job AS 
	/*创建同步任务*/
	FUNCTION F_SYNC_DRUG_STOCK RETURN NUMBER;
	/*同步*/
	PROCEDURE PRC_SYNC_DRUG_STOCK(RUNJOB  VARCHAR2) ;	
END pkg_scene_job;
/*包体*/
CREATE OR REPLACE PACKAGE body yjb.pkg_scene_job AS 
	/*创建任务*/
	FUNCTION F_SYNC_DRUG_STOCK RETURN NUMBER AS
		/*同步*/
		pragma autonomous_transaction;
	    sync_stock_sql varchar2(3000);
	    V_JOB_NAME varchar2(50):='SYNC_DRUG_STOCK_JOB';--JOB任务名称
	    SYNC_DRUG_STOCK_JOBCNT NUMBER:=0;
	    JOBEXETIME NUMBER:=0 ;--任务执行时间(分钟)
	    plsqlblk varchar2(800);
	BEGIN
		BEGIN
			SELECT count(1) INTO SYNC_DRUG_STOCK_JOBCNT FROM user_scheduler_jobs a WHERE a.JOB_NAME=V_JOB_NAME;
			IF SYNC_DRUG_STOCK_JOBCNT =1 THEN
				SELECT to_number((SYSDATE - a.START_DATE)*24*60)  INTO JOBEXETIME FROM user_scheduler_jobs a WHERE a.JOB_NAME=V_JOB_NAME;
				/*如果任务执行时间超过20分钟 则将任务删除*/
				IF JOBEXETIME >=20 THEN
		--			sys.dbms_scheduler.drop_job();
					-- 特别说明： force => false  
					-- false：job 执行完成后再删除  
					-- true ：立即删除（job 未必执行完成哦）
					sys.dbms_scheduler.drop_job(
					  job_name => V_JOB_NAME,
					  force    => true);
			    ELSE 
			    	RETURN 1;
				END IF;
			END IF;
		    plsqlblk:='begin pkg_scene_job.PRC_SYNC_DRUG_STOCK(' ||chr(39)|| V_JOB_NAME ||chr(39)|| '); end;';
		    sys.dbms_scheduler.create_job(job_name => V_JOB_NAME,
		                                job_type => 'PLSQL_Block',
		                                job_action => plsqlblk,
		                                number_of_arguments =>0,
		                                start_date => SYSDATE,
		                                repeat_interval => NULL,
		                                end_date => NULL,
		                                job_class => 'DEFAULT_JOB_CLASS',
		                                enabled => TRUE,
		                                auto_drop => TRUE,
		                                comments => '同步数据',
		                                credential_name => null,
		                                destination_name => NULL);
		END;
		COMMIT;
	    RETURN 1;
	EXCEPTION 
	 	WHEN OTHERS then
		RETURN 0;    
	END F_SYNC_DRUG_STOCK;

	/*同步表数据（全量/增量）数据量上万时 建议将数据量拆分进行*/
	PROCEDURE PRC_SYNC_DRUG_STOCK(RUNJOB  VARCHAR2) 
	    IS
	    pragma autonomous_transaction;
		BEGIN
	--		  SYS.DBMS_LOCK.SLEEP(10);
	--		  EXECUTE IMMEDIATE INBLOCK;
			  merge into table1 v
		      using (SELECT
							i1.HOSPITAL_AREA,
							i1.DRUG_CODE,
							i1.AMOUNT_PER_PACKAGE,
							i1.PACKAGE_UNITS_CODE,
							i1.FIRM_ID,
							i1.STORAGE,
							i1.ITEM_PRICE,
							i1.QUANTITY,
							i1.AVAILABLE_STORAGE,
							i1.SUPPLY_INDICATOR,
							i1.DRUG_SPEC
						FROM
							table2@interface i1) d
				on (v.HOSPITAL_AREA = d.HOSPITAL_AREA AND v.DRUG_CODE=d.DRUG_CODE AND v.STORAGE=d.STORAGE)   -- 这里通过主键判断，数据是否存在
				 when matched then
				       update set 
				            v.QUANTITY = d.QUANTITY,
				            v.AVAILABLE_STORAGE = d.AVAILABLE_STORAGE
				when not matched then
				        insert (
				            v.HOSPITAL_AREA,
							v.DRUG_CODE,
							v.AMOUNT_PER_PACKAGE,
							v.PACKAGE_UNITS_CODE,
							v.FIRM_ID,
							v.STORAGE,
							v.ITEM_PRICE,
							v.QUANTITY,
							v.AVAILABLE_STORAGE,
							v.SUPPLY_INDICATOR,
							v.DRUG_SPEC)
				        values (
				            d.HOSPITAL_AREA,
							d.DRUG_CODE,
							d.AMOUNT_PER_PACKAGE,
							d.PACKAGE_UNITS_CODE,
							d.FIRM_ID,
							d.STORAGE,
							d.ITEM_PRICE,
							d.QUANTITY,
							d.AVAILABLE_STORAGE,
							d.SUPPLY_INDICATOR,
							d.DRUG_SPEC
				        ) ;
	--		  DBMS_OUTPUT.PUT_LINE('===========执行' || INBLOCK || '成功=============');
			  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE(NAME      => RUNJOB,
			                                   ATTRIBUTE => 'auto_drop',
			                                   VALUE     => TRUE);
		  	--SYS.DBMS_SCHEDULER.DISABLE(NAME => RUNJOB);
	    COMMIT;
	    EXECUTE immediate 'ALTER SESSION CLOSE DATABASE link hisinterface' ;
		END PRC_SYNC_DRUG_STOCK;
END pkg_scene_job;

触发同步任务：

SELECT yjb.pkg_scene_job.F_SYNC_DRUG_STOCK() AS a FROM dual WHERE 1=0;

没有结果行时是不会触发的，以下方式可触发：

SELECT yjb.pkg_scene_job.F_SYNC_DRUG_STOCK() AS a FROM dual;

PS：一定是使用（调用）到 触发函数yjb.pkg_scene_job.F_SYNC_DRUG_STOCK()，才可完成触发。如：

/*无法触发情况*/
with t1 as (select 1 as a,unicorn.pkg_scene_job.F_SYNC_DRUG_STOCK() as f from dual)
select a from t1
; 

/*可触发情况*/

with t1 as (select 1 as a,unicorn.pkg_scene_job.F_SYNC_DRUG_STOCK() as f from dual)
select f from t1
; 
或
with t1 as (select 1 as a,unicorn.pkg_scene_job.F_SYNC_DRUG_STOCK() as f from dual)
select a from t1 where f=1
; 

查看后台任务：

SELECT * FROM user_scheduler_jobs;

查看后台任务日志：

SELECT * FROM user_scheduler_job_run_details a WHERE instr(a.job_name,'SYNC_DRUG_STOCK_JOB')>=1 ORDER BY a.log_date DESC;

优化

利用后台任务方式实现时，每一次执行都会产生JOB日志，为了避免日志产生过多，可在执行任务前清除对应的日志记录（调用SYS.DBMS_SCHEDULER.PURGE_LOG），如：

--清除日志
SYS.DBMS_SCHEDULER.PURGE_LOG(job_name => RUNJOB);

调用此函数清除对应JOB日志，需执行用户具有JOB任务管理权限(MANAGE SCHEDULER)：

GRANT MANAGE SCHEDULER TO USER