Oracle全量同步與增量同步方式
更新時間：2025年06月11日 09:19:30 作者：雷神樂樂
這篇文章主要介紹了Oracle全量同步與增量同步方式,具有很好的參考價值,希望對大家有所幫助,如有錯誤或未考慮完全的地方,望不吝賜教
etl——Extract, Transform, Load，即“提取、轉換、加載”
一、全量同步(T+1)
邏輯：用源表的數(shù)據直接覆蓋目標表。
實現(xiàn)的邏輯：在往目標表中插入數(shù)據之前，【先清空目標表】，然后查詢源表的數(shù)據，直接插入目標表。適用于數(shù)據量小的情況。
(一)全量同步步驟
1.創(chuàng)建源表
drop table emp_source; CREATE TABLE emp_source AS SELECT e.*, SYSDATE create_date, SYSDATE last_update_date FROM emp e WHERE 1 = 2; INSERT INTO emp_source SELECT e.*, SYSDATE create_date, SYSDATE last_update_date FROM emp e; commit ; select * from emp_source;
2.創(chuàng)建數(shù)據同步的目標表
drop TABLE emp_tar; CREATE TABLE emp_tar AS SELECT e.*, SYSDATE etl_dt FROM emp e WHERE 1 = 2; select * from emp_tar;
3.創(chuàng)建全量同步的存儲過程并調用
CREATE OR REPLACE PROCEDURE P_FULL
IS
BEGIN
----清空目標表數(shù)據
EXECUTE IMMEDIATE 'truncate table emp_tar';
---插入數(shù)據到目標表
INSERT INTO emp_tar
SELECT e.empno,
e.ename,
e.job,
e.mgr,
e.hiredate,
e.sal,
e.comm,
e.deptno,
SYSDATE etl_dt
FROM emp_source e;
COMMIT;
----寫入異常
EXCEPTION
WHEN OTHERS
THEN dbms_output.put_line(SQLERRM);
END;
BEGIN
P_FULL;
END ;
SELECT * FROM emp_tar;目標表同步成功：
(二)全量同步練習
通過入參 P_JOB 工種，將非這個工種的數(shù)據，全量同步到 EMP_0318 這個表(全量用TRUNCATE 實現(xiàn),使用動態(tài)SQL實現(xiàn));
create table EMP_0318 as select * from emp where 1,2;
create or replace procedure p_fullsync(P_JOB VARCHAR2) is
begin
EXECUTE IMMEDIATE 'TRUNCATE TABLE EMP_0318'; -- 清空目標表數(shù)據
-- 插入數(shù)據到目標表
insert into EMP_0318
select * from emp where job != P_JOB;
commit;
-- 寫入異常
exception
when others then
rollback;
end;
begin
p_fullsync(P_JOB => 'CLERK');
end;
SELECT * FROM EMP_0318;
(三)存儲過程 & 動態(tài)SQL & 全量同步之間的區(qū)別
1.存儲過程：是封裝一段 數(shù)據的 同步 &轉換邏輯；
2.動態(tài)SQL：當SQL中存在不穩(wěn)定因素，比如，表名不確定，篩選條件不確定，或者是 DDL語句(TRUNCATE / DROP)不能直接運行，這個時候需要 拼接一個變量的SQL語句字符串，然后用 EXECUTE IMMEDIATE SQL語句字符串 去動態(tài)執(zhí)行。如果這個SQL是 SELECT 語句 一般后面還有 INTO 變量賦值。
3.全量同步：在插入目標表的時候，需要先清空目標表，這樣才能保證目標表的數(shù)據不會重復。(否則 我們調用一次存儲過程，目標表的數(shù)據就會重復一次)。
二、增量同步——MERGE INTO
(一)相關概念介紹
1.什么是增量
增量指的是那一天新增的或者發(fā)生修改的數(shù)據。
2.什么是增量同步
邏輯：用源表的數(shù)據 更新 目標表 ，如果這條數(shù)據在目標表中存在則更新，數(shù)據不存在，則插入。
實現(xiàn)的邏輯：首先判斷 目標表中是否有源表中的數(shù)據：如果有，則用 源表的數(shù)據 更新目標表中對應的數(shù)據；如果沒有，則查詢源表的記錄，直接插入目標表。
通常用 MERGE INTO 的方式來做增量同步數(shù)據。
3.全量同步與增量同步的區(qū)別
全量同步是同步整張表的數(shù)據，增量同步只同步增量數(shù)據（比如今天只同步昨天新增的或者修改的數(shù)據）
全量同步之前要清空目標表的數(shù)據，增量同步不用清空表，有則更新，無則插入；
4.增量同步MERGE INTO語法結構
MERGE INTO 目標表 USING (增量數(shù)據的查詢結果集) --子查詢 查詢源表的增量結果集 ON (匹配字段) --用來判斷增量結果集里的數(shù)據到底是更新,目標表里的數(shù)據還是插入到目標表中 WHEN MATCHED THEN UPDATE SET 目標表的字段 = 增量結果集字段 --UPDATE和SET之間不需要加表名 WHEN NOT MATCHED THEN INSERT(目標表字段) VALUES(增量結果集字段) ; --INSERT和VALUES之間不需要加 INTO 表名
(二)增量同步練習
1.練習1
示例：假如在昨天公司里新增一個員工和 7788 這個員工的薪資發(fā)生了變化，用存儲過程實現(xiàn)，將 EMP_SOURCE 表的數(shù)據增量同步到 EMP_TAR
源表數(shù)據：
drop table EMP_TAR; drop table emp_source; create table emp_source as SELECT e.*,SYSDATE create_date,SYSDATE last_update_date from emp e; INSERT INTO emp_source(empno,ename,hiredate,create_date,last_update_date) VALUES(1111,'lisa',TRUNC(SYSDATE)-1,TRUNC(SYSDATE)-1,TRUNC(SYSDATE)-1); UPDATE emp_source SET sal=10000,last_update_date=TRUNC(SYSDATE)-1 WHERE empno=7788; commit; SELECT * FROM emp_source;
create table EMP_TAR as SELECT e.*,SYSDATE etl_dt from emp e; SELECT * FROM EMP_TAR;
目標表數(shù)據：
編寫存儲過程進行增量同步，并進行調用：
create or replace procedure p_emp_source(p_dt date) as
begin
merge into EMP_TAR a
using (select * from emp_source where LAST_UPDATE_DATE = p_dt) b
--子查詢 查詢源表的增量結果集
on (a.empno = b.empno)
--用來判斷增量結果集里的數(shù)據到底是更新,目標表里的數(shù)據還是插入到目標表中
when matched then
update
set a.ename = b.ename, --主鍵不能update
a.job = b.job,
a.mgr = b.mgr,
a.hiredate = b.hiredate,
a.sal = b.sal,
a.comm = b.comm,
a.deptno = b.deptno,
a.etl_dt = sysdate --UPDATE和SET之間不需要加表名
when not matched then
insert
(a.empno,
a.ename,
a.job,
a.mgr,
a.hiredate,
a.sal,
a.comm,
a.deptno,
a.etl_dt)
values
(b.empno,
b.ename,
b.job,
b.mgr,
b.hiredate,
b.sal,
b.comm,
b.deptno,
sysdate); --INSERT和VALUES之間不需要加 INTO 表名
commit;
end;
begin
p_emp_source(p_dt=>TRUNC(SYSDATE)-1);
end;目標表數(shù)據：
SELECT * FROM EMP_TAR;
2.練習2
--書 book表
drop table book;
create table book(bno varchar2(20),--圖書編號
bname varchar2(50),--圖書名稱
aid int,--作者
pid int,--出版社
tid varchar2(20),--種類
buy date,--進貨日期
price number(7,2),--價格
buynum int); --數(shù)量
insert into book values('J0001','計算機基礎',2001,1001,'J001',date '2016-1-5',12.5,5);
insert into book values('J0002','oracle從入門到精通',2002,1004,'J001',date '2016-8-8',30,10);
insert into book values('Y0001','常見病例及用藥',2005,1003,'Y001',date '2016-2-4',20,20);
insert into book values('W0001','平凡的世界',2006,1003,'W001',date '2016-5-15',35,30);
insert into book values('W0002','悲慘世界',2007,1004,'W001',date '2016-4-9',31,22);
insert into book values('J0003','SQL入門',2001,1004,'J001',date '2016-2-15',32,20);
insert into book values('J0004','SQL基礎課程',2002,1001,'J001',date '2016-6-6',28,10);
COMMIT;
SELECT * FROM book; --書(主表)
DROP TABLE BOOK_SOURCE;
CREATE TABLE BOOK_SOURCE
AS
SELECT T.*
---假如昨天寫入的這些數(shù)據
,SYSDATE -1 AS CREATE_DATE
,SYSDATE -1 AS LAST_UPDATE_DATE
FROM book T;
SELECT * FROM BOOK_SOURCE;
---目標表
DROP TABLE BOOK_TARGET;
create table BOOK_TARGET(bno varchar2(20) ,--圖書編號
bname varchar2(50),--圖書名稱
aid int,--作者
pid int,--出版社
tid varchar2(20),--種類
buy date,--進貨日期
price number(7,2),--價格
buynum int
,ETL_DATE DATE);
SELECT * FROM BOOK_TARGET;
-- 創(chuàng)建存儲過程，將源表數(shù)據同步到目標表中
create or replace procedure p_BOOK_SOURCE_TARGET(P_DT DATE) is
v_rowcount number;
begin
merge into BOOK_TARGET a
using (select * from BOOK_SOURCE where trunc(LAST_UPDATE_DATE) = P_DT) b
on (a.bno = b.bno)
when matched then
update
set a.bname = b.bname,
a.aid = b.aid,
a.pid = b.pid,
a.tid = b.tid,
a.buy = b.buy,
a.price = b.price,
a.buynum = b.buynum,
a.etl_date = sysdate -- 這里必須是當天的日期
when not matched then
insert
(a.bno,
a.bname,
a.aid,
a.pid,
a.tid,
a.buy,
a.price,
a.buynum,
a.etl_date)
values
(b.bno,
b.bname,
b.aid,
b.pid,
b.tid,
b.buy,
b.price,
b.buynum,
sysdate);
-- 記錄MERGE影響的行數(shù)
v_rowcount := SQL%ROWCOUNT;
DBMS_OUTPUT.PUT_LINE('MERGE影響的行數(shù): ' || v_rowcount);
commit;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
end;-- 沒有匹配到數(shù)據，是新增數(shù)據 begin p_BOOK_SOURCE_TARGET(P_DT => trunc(SYSDATE) - 1); -- 傳入昨天的日期 end; SELECT * FROM BOOK_TARGET;
源表數(shù)據成功INSERT到目標表中：
insert into BOOK_SOURCE
SELECT T.*
---假如昨天寫入的這些數(shù)據
,SYSDATE AS CREATE_DATE
,SYSDATE AS LAST_UPDATE_DATE
FROM book T;
COMMIT;
SELECT * FROM BOOK_SOURCE;
begin p_BOOK_SOURCE_TARGET(P_DT => trunc(SYSDATE)); -- 傳入今天的日期 end; -- 這次匹配到了，是更新操作 SELECT * FROM BOOK_TARGET;
總結
以上為個人經驗，希望能給大家一個參考，也希望大家多多支持腳本之家。
相關文章
本文將詳細介紹Oracle一個用戶如何訪問其它用戶的表，需要的朋友可以參考下2012-11-11
win7安裝oracle10g 提示程序異常終止 發(fā)生未知錯誤
本文將詳細介紹oracle 10g 在win7下安裝提示程序異常終止，發(fā)生未知錯誤的解決方法，需要的朋友可以參考下2012-12-12
這篇文章主要介紹了Oracle中scott表結構與簡單查詢,實例分析Oracle查詢的相關技巧,需要的朋友可以參考下2015-04-04
這篇文章主要為大家詳細介紹了oracle11g卸載完整圖文教程，文中安裝步驟介紹的非常詳細，具有一定的參考價值，感興趣的小伙伴們可以參考一下2021-09-09
Oracle用戶名默認自動轉換為大寫,雙引號可保留原始大小寫,本文主要介紹了Oracle 用戶名大小寫控制的實現(xiàn)示例,感興趣的可以了解一下2025-06-06
Oracle以逗號分隔的字符串拆分為多行數(shù)據實例詳解
做多選功能時為了簡便,會在某個字段中存儲多個值,保存時雖然省事,但后續(xù)的查詢統(tǒng)計時還需要拆分數(shù)據才行,因此這時需要將字段內的值分成多行以便后續(xù)使用,這篇文章主要給大家介紹了關于Oracle以逗號分隔的字符串拆分為多行數(shù)據的相關資料,需要的朋友可以參考下2021-07-07
在使用Oracle時我們需要關注數(shù)據庫的內存使用情況,以確保其正確高效地運行,下面這篇文章主要給大家介紹了關于Oracle數(shù)據庫查看與修改內存配置的相關資料,需要的朋友可以參考下2023-11-11
oracle如何覆蓋導入dmp文件呢？很多朋友對這一問題不是很清楚，今天小編通過分享oracle覆蓋導入dmp文件的2種方法，感興趣的朋友跟隨小編一起看看吧2021-05-05
Oracle的substr和instr函數(shù)簡單用法
這篇文章主要介紹了Oracle的substr和instr函數(shù)簡單用法 的相關資料,需要的朋友可以參考下2015-12-12
這篇文章介紹了Oracle執(zhí)行Update語句的幾種方式，文中通過示例代碼介紹的非常詳細。對大家的學習或工作具有一定的參考借鑒價值，需要的朋友可以參考下2022-05-05