欧美bbbwbbbw肥妇,免费乱码人妻系列日韩,一级黄片

Oracle全量同步與增量同步方式

 更新時(shí)間：2025年06月11日 09:19:30   作者：雷神樂(lè)樂(lè)  
這篇文章主要介紹了Oracle全量同步與增量同步方式,具有很好的參考價(jià)值,希望對(duì)大家有所幫助,如有錯(cuò)誤或未考慮完全的地方,望不吝賜教

etl——Extract, Transform, Load，即“提取、轉(zhuǎn)換、加載”

一、全量同步(T+1)

邏輯：用源表的數(shù)據(jù)直接覆蓋目標(biāo)表。

實(shí)現(xiàn)的邏輯：在往目標(biāo)表中插入數(shù)據(jù)之前，【先清空目標(biāo)表】，然后查詢(xún)?cè)幢淼臄?shù)據(jù)，直接插入目標(biāo)表。適用于數(shù)據(jù)量小的情況。

(一)全量同步步驟

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ù)據(jù)同步的目標(biāo)表

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)建全量同步的存儲(chǔ)過(guò)程并調(diào)用

CREATE OR REPLACE PROCEDURE P_FULL
    IS
BEGIN
    ----清空目標(biāo)表數(shù)據(jù)
    EXECUTE IMMEDIATE 'truncate table emp_tar';
    ---插入數(shù)據(jù)到目標(biāo)表
    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;
    ----寫(xiě)入異常
EXCEPTION
    WHEN OTHERS
        THEN dbms_output.put_line(SQLERRM);
END;

BEGIN
    P_FULL;
END ;

SELECT * FROM emp_tar;

目標(biāo)表同步成功：

(二)全量同步練習(xí)

通過(guò)入?yún)?P_JOB 工種，將非這個(gè)工種的數(shù)據(jù)，全量同步到 EMP_0318 這個(gè)表(全量用TRUNCATE 實(shí)現(xiàn),使用動(dòng)態(tài)SQL實(shí)現(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'; -- 清空目標(biāo)表數(shù)據(jù)
  -- 插入數(shù)據(jù)到目標(biāo)表
  insert into EMP_0318
    select * from emp where job != P_JOB;
  commit;
  -- 寫(xiě)入異常
exception
  when others then
    rollback;
end;

begin
  p_fullsync(P_JOB => 'CLERK');
end;

SELECT * FROM EMP_0318;

(三)存儲(chǔ)過(guò)程 & 動(dòng)態(tài)SQL & 全量同步之間的區(qū)別

1.存儲(chǔ)過(guò)程：是封裝一段 數(shù)據(jù)的 同步 &轉(zhuǎn)換邏輯；

2.動(dòng)態(tài)SQL：當(dāng)SQL中存在不穩(wěn)定因素，比如，表名不確定，篩選條件不確定，或者是 DDL語(yǔ)句(TRUNCATE / DROP)不能直接運(yùn)行，這個(gè)時(shí)候需要 拼接一個(gè)變量的SQL語(yǔ)句字符串，然后用 EXECUTE IMMEDIATE SQL語(yǔ)句字符串 去動(dòng)態(tài)執(zhí)行。如果這個(gè)SQL是 SELECT 語(yǔ)句 一般后面還有 INTO 變量賦值。

3.全量同步：在插入目標(biāo)表的時(shí)候，需要先清空目標(biāo)表，這樣才能保證目標(biāo)表的數(shù)據(jù)不會(huì)重復(fù)。(否則 我們調(diào)用一次存儲(chǔ)過(guò)程，目標(biāo)表的數(shù)據(jù)就會(huì)重復(fù)一次)。

二、增量同步——MERGE INTO

(一)相關(guān)概念介紹

1.什么是增量

增量指的是那一天新增的或者發(fā)生修改的數(shù)據(jù)。

2.什么是增量同步

邏輯：用源表的數(shù)據(jù) 更新 目標(biāo)表 ，如果這條數(shù)據(jù)在目標(biāo)表中存在則更新，數(shù)據(jù)不存在，則插入。

實(shí)現(xiàn)的邏輯：首先判斷 目標(biāo)表中是否有源表中的數(shù)據(jù)：如果有，則用 源表的數(shù)據(jù) 更新目標(biāo)表中對(duì)應(yīng)的數(shù)據(jù)；如果沒(méi)有，則查詢(xún)?cè)幢淼挠涗?，直接插入目?biāo)表。

通常用 MERGE INTO 的方式來(lái)做增量同步數(shù)據(jù)。

3.全量同步與增量同步的區(qū)別

全量同步是同步整張表的數(shù)據(jù)，增量同步只同步增量數(shù)據(jù)（比如今天只同步昨天新增的或者修改的數(shù)據(jù)）

全量同步之前要清空目標(biāo)表的數(shù)據(jù)，增量同步不用清空表，有則更新，無(wú)則插入；

4.增量同步MERGE INTO語(yǔ)法結(jié)構(gòu)

MERGE INTO 目標(biāo)表
USING (增量數(shù)據(jù)的查詢(xún)結(jié)果集) --子查詢(xún) 查詢(xún)?cè)幢淼脑隽拷Y(jié)果集
ON (匹配字段)
--用來(lái)判斷增量結(jié)果集里的數(shù)據(jù)到底是更新,目標(biāo)表里的數(shù)據(jù)還是插入到目標(biāo)表中
WHEN MATCHED THEN UPDATE SET 目標(biāo)表的字段 = 增量結(jié)果集字段   
--UPDATE和SET之間不需要加表名
WHEN NOT MATCHED THEN INSERT(目標(biāo)表字段) VALUES(增量結(jié)果集字段) ; 
--INSERT和VALUES之間不需要加 INTO 表名

(二)增量同步練習(xí)

1.練習(xí)1

示例：假如在昨天公司里新增一個(gè)員工和 7788 這個(gè)員工的薪資發(fā)生了變化，用存儲(chǔ)過(guò)程實(shí)現(xiàn)，將 EMP_SOURCE 表的數(shù)據(jù)增量同步到 EMP_TAR

源表數(shù)據(jù)：

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;

目標(biāo)表數(shù)據(jù)：

編寫(xiě)存儲(chǔ)過(guò)程進(jìn)行增量同步，并進(jìn)行調(diào)用：

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
  --子查詢(xún) 查詢(xún)?cè)幢淼脑隽拷Y(jié)果集
  on (a.empno = b.empno)
--用來(lái)判斷增量結(jié)果集里的數(shù)據(jù)到底是更新,目標(biāo)表里的數(shù)據(jù)還是插入到目標(biāo)表中
  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;

目標(biāo)表數(shù)據(jù)：

SELECT * FROM EMP_TAR;

2.練習(xí)2

--書(shū) book表
drop table book;
create table book(bno varchar2(20),--圖書(shū)編號(hào)
                  bname varchar2(50),--圖書(shū)名稱(chēng)
                  aid int,--作者
                  pid int,--出版社
                  tid varchar2(20),--種類(lèi)
                  buy date,--進(jìn)貨日期
                  price number(7,2),--價(jià)格
                  buynum int); --數(shù)量 
                  
insert into book values('J0001','計(jì)算機(jī)基礎(chǔ)',2001,1001,'J001',date '2016-1-5',12.5,5);
insert into book values('J0002','oracle從入門(mén)到精通',2002,1004,'J001',date '2016-8-8',30,10);
insert into book values('Y0001','常見(jiàn)病例及用藥',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入門(mén)',2001,1004,'J001',date '2016-2-15',32,20);
insert into book values('J0004','SQL基礎(chǔ)課程',2002,1001,'J001',date '2016-6-6',28,10);
COMMIT;
SELECT * FROM book;  --書(shū)(主表)

DROP TABLE BOOK_SOURCE;
CREATE TABLE BOOK_SOURCE
AS 
SELECT T.*
       ---假如昨天寫(xiě)入的這些數(shù)據(jù)
       ,SYSDATE -1 AS CREATE_DATE  
       ,SYSDATE -1 AS LAST_UPDATE_DATE  
FROM book T;

SELECT  * FROM BOOK_SOURCE;

---目標(biāo)表
DROP TABLE BOOK_TARGET;
create table BOOK_TARGET(bno varchar2(20) ,--圖書(shū)編號(hào)
                  bname varchar2(50),--圖書(shū)名稱(chēng)
                  aid int,--作者
                  pid int,--出版社
                  tid varchar2(20),--種類(lèi)
                  buy date,--進(jìn)貨日期
                  price number(7,2),--價(jià)格
                  buynum int
                  ,ETL_DATE DATE);
                  
SELECT  * FROM BOOK_TARGET;

  -- 創(chuàng)建存儲(chǔ)過(guò)程，將源表數(shù)據(jù)同步到目標(biāo)表中
  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 -- 這里必須是當(dāng)天的日期
  
  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;
-- 沒(méi)有匹配到數(shù)據(jù)，是新增數(shù)據(jù)
begin
  p_BOOK_SOURCE_TARGET(P_DT => trunc(SYSDATE) - 1); -- 傳入昨天的日期
end;

SELECT * FROM BOOK_TARGET;

源表數(shù)據(jù)成功INSERT到目標(biāo)表中：

insert into BOOK_SOURCE
SELECT T.*
       ---假如昨天寫(xiě)入的這些數(shù)據(jù)
       ,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;

總結(jié)

以上為個(gè)人經(jīng)驗(yàn)，希望能給大家一個(gè)參考，也希望大家多多支持腳本之家。

相關(guān)文章

最新評(píng)論