文档库

最新最全的文档下载
当前位置:文档库 > Oracle跨实例数据全量、增量抽取例子

Oracle跨实例数据全量、增量抽取例子

1、创建数据库链

create database link DBL_CNRMB

connect to 用户名 identified by "密码"

using '(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=IP地

址)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=数据库实例名)))';

注:在PLSQL工具中执行,用户名、密码、IP地址、数据库实例名均要替换,创建成功后在本地数据库执行带数据库链(@dbl_CNRMB)SQL远程查询测试 select * from 用户名.表名@dbl_CNRMB

2、创建增量记录表

create table PZ_FJCNRMB_CQ

(

zlpdsj DATE,

zxsj DATE,

msg VARCHAR2(2000)

)

;

insert into PZ_FJCNRMB_CQ (zlpdsj)

values (to_date('05-08-2016 10:19:40', 'dd-mm-yyyy hh24:mi:ss'));

commit;

注:在PLSQL工具中执行

3、创建本地数据库表

参照源数据库表结构,在本地数据库创建结构相同的数据表,以表名:T_CNRMB_SJ为例

4、创建数据抽取存储过程(首次全量)

CREATE OR REPLACE PROCEDURE SP_FJCNRMB_CQ_FULL IS

V_SJL NUMBER := 0;

BEGIN

FOR CUR_CQ IN (SELECT * FROM 用户名.表名@DBL_CNRMB WHERE XZQH = '4500') LOOP INSERT INTO T_CNRMB_SJ

(字段1, 字段2, 字段3, 字段4, 字段5, .. .)

VALUES

(CUR_CQ.字段1,

CUR_CQ.字段2,

CUR_CQ.字段3,

CUR_CQ.字段4,

CUR_CQ.字段5,

.. .);

V_SJL := V_SJL + 1;

--每2000条提交一次到本地数据库表

IF MOD(V_SJL, 2000) = 0 THEN

COMMIT;

END IF;

END LOOP;

COMMIT;

EXCEPTION

WHEN OTHERS THEN

COMMIT;

END;

/

注:(1)替换代码段中的中文为实际表名\字段名\查询条件

(2)全量可直接在PLSQL命令窗口中执行 exec SP_FJCNRMB_CQ_FULL;

5、创建数据抽取存储过程(增量)

CREATE OR REPLACE PROCEDURE SP_FJCNRMB_CQ IS

V_SJL NUMBER := 0;

V_FOUND NUMBER;

V_TIME_Q DATE;

V_TIME_Z DATE;

BEGIN

--提取本次起始时间

SELECT ZLPDSJ INTO V_TIME_Q FROM PZ_FJCNRMB_CQ WHERE ROWNUM <= 1;

--计算本次截止时间

SELECT MAX(增量时间字段)

INTO V_TIME_Z

FROM 用户名.表名@DBL_CNRMB

WHERE 增量时间字段 >= V_TIME_Q;

--数据抽取

FOR CUR_CQ IN (SELECT *

FROM 用户名.表名@DBL_CNRMB

WHERE XZQH = '4500'

AND 增量时间字段 BETWEEN V_TIME_Q AND V_TIME_Z) LOOP

SELECT COUTN(*) INTO V_FOUND FORM T_CNRMB_SJ WHERE 主键 = CUR_CQ.主键; IF V_FOUND = 0 THEN

INSERT INTO T_CNRMB_SJ

(字段1, 字段2, 字段3, 字段4, 字段5, .. .)

VALUES

(CUR_CQ.字段1,

CUR_CQ.字段2,

CUR_CQ.字段3,

CUR_CQ.字段4,

CUR_CQ.字段5,

.. .);

V_SJL := V_SJL + 1;

END IF;

--每2000条提交一次到本地数据库表

IF MOD(V_SJL, 2000) = 0 THEN

COMMIT;

END IF;

END LOOP;

--记录本次截止时间与执行情况

UPDATE PZ_FJCNRMB_CQ

SET ZLPDSJ = V_TIME_Z,

ZXSJ = SYSDATE,

MSG = '本次成功抽取:' || V_SJL || '条数据';

COMMIT;

--抽取异常处理

EXCEPTION

WHEN OTHERS THEN

UPDATE PZ_FJCNRMB_CQ

SET ZXSJ = SYSDATE, MSG = '执行失败:' || SUBSTR(SQLERRM, 1, 200);

COMMIT;

END;

/

注:(1)替换代码段中的中文为实际表名\字段名\查询条件

(2)如果判断增量的时间字段类型为“字符型”,则PZ_FJCNRMB_CQ.zlpdsj字段类型改为字符型、存储过程代码变量定义中的 V_TIME_Q DATE 改为 V_TIME_Q VARCHAR2(14),V_TIME_Z DATE 改为 V_TIME_Z VARCHAR2(14)

6、创建定时执行任务(10分钟执行一次)

begin

sys.dbms_job.submit(job => :job,

what => 'SP_FJCNRMB_CQ;',

interval => 'sysdate + 10/60/24');

commit;

end;

/

注:以上例子为10分钟执行频率,可以根据实际需求调整,如:30分钟(sysdate + 30/60/24)、1个小时(sysdate + 1/24)、6个小(sysdate + 6/24)