創(chuàng)建對應的包，以方便觸發(fā)調用

/*包聲明*/
CREATE OR REPLACE PACKAGE yjb.pkg_scene_job AS 
	/*創(chuàng)建同步任務*/
	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 
	/*創(chuàng)建任務*/
	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 ;--任務執(zhí)行時間(分鐘)
	    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;
				/*如果任務執(zhí)行時間超過20分鐘 則將任務刪除*/
				IF JOBEXETIME >=20 THEN
		--			sys.dbms_scheduler.drop_job();
					-- 特別說明： force => false  
					-- false：job 執(zhí)行完成后再刪除  
					-- true ：立即刪除（job 未必執(zhí)行完成哦）
					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('===========執(zhí)行' || 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;

觸發(fā)同步任務：

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

沒有結果行時是不會觸發(fā)的，以下方式可觸發(fā)：

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

PS：一定是使用（調用）到 觸發(fā)函數yjb.pkg_scene_job.F_SYNC_DRUG_STOCK()，才可完成觸發(fā)。如：

/*無法觸發(fā)情況*/
with t1 as (select 1 as a,unicorn.pkg_scene_job.F_SYNC_DRUG_STOCK() as f from dual)
select a from t1
; 

/*可觸發(fā)情況*/

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;

優(yōu)化

利用后臺任務方式實現時，每一次執(zhí)行都會產生JOB日志，為了避免日志產生過多，可在執(zhí)行任務前清除對應的日志記錄（調用SYS.DBMS_SCHEDULER.PURGE_LOG），如：

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

調用此函數清除對應JOB日志，需執(zhí)行用戶具有JOB任務管理權限(MANAGE SCHEDULER)：文章來源地址http://www.zghlxwxcb.cn/news/detail-560741.html

GRANT MANAGE SCHEDULER TO USER

到了這里，關于Oracle通過函數調用dblink同步表數據方案（全量/增量）的文章就介紹完了。如果您還想了解更多內容，請在右上角搜索TOY模板網以前的文章或繼續(xù)瀏覽下面的相關文章，希望大家以后多多支持TOY模板網！