SQL增量更新方式

-------------------------数据更新------------
--数据更新分为两种：全量更新和增量更新。
--全量更新：删除整张表的数据，然后再把最新的全部数据插入到表中。
--适用范围：维度表（数据量少），数据量比较少的事实表或者宽表。
--增量更新：不删除或者删除部分表中数据，只把最新产生的数据插入到表中，历史数据不动。
--适用范围：数据量一定范围内增量比较大的表。一般是事实表或者宽表。

--------------------------------增量更新------------------------
--增量更新一共有4种方法。
---方法一：切片增量更新：按照时间将数据切成片，每次更新时只更新某一片或某几片数据。
----更新方法：先把目标时间范围内的数据删除（目标范围内存在数据就删除，不存在就不删），然后再把目标范围内的最新数据插入进去。

CREATE OR REPLACE PROCEDURE SP_DW_ROOM_DETAIL2(P_START_TIME VARCHAR2,P_END_TIME VARCHAR2) AS
V_START_TIME DATE := TO_DATE(P_START_TIME,'YYYY-MM-DD');
V_END_TIME DATE := TO_DATE(P_END_TIME,'YYYY-MM-DD');
BEGIN
  DELETE FROM DW_ROOM_DETAIL WHERE DATEKEY BETWEEN V_START_TIME AND V_END_TIME;
  INSERT INTO dw_room_detail
    (datekey, date_year, date_month, chainid, status_rmng, status_rm, amountrmng, roomamount, revenue)
  SELECT D.DATEKEY,
       D.YEARSS AS date_year,
       D.MONTHSS AS date_month,
       A.CHAINID,
       SUM(A.STATUSRMNG) AS status_rmng,
       SUM(A.STATUSRMNG+A.ROOMDAY+A.ROOMHALFDAY+A.ROOMHOURDAY) AS status_rm,
       SUM(NVL(B.AMOUNTRMNG,0)) AS AmountRmNg,
       SUM(NVL(B.AMOUNTRMNG,0)+NVL(B.AMOUNTRMALLDAY,0)+NVL(B.AMOUNTRMOTHER,0)+NVL(B.AMOUNTRMDISCOUNT,0)) AS RoomAmount,
       SUM(NVL(B.AMOUNTRMNG,0)+NVL(B.AMOUNTRMALLDAY,0)+NVL(B.AMOUNTRMOTHER,0)+NVL(B.AMOUNTRMDISCOUNT,0)+NVL(B.AMOUNTOTHER,0)) AS Revenue
FROM ODS.ODS_T_DATE D
  JOIN ODS.ODS_roomcount_detail A
    ON D.DATEKEY=TO_DATE(A.DATEKEY,'YYYYMMDD')
  JOIN ODS.ODS_ROOMAMOUNT_DETAIL B
    ON A.DATEKEY=B.DATEKEY
    AND A.CHAINID=B.CHAINID
    AND A.ROOMTYPEID=B.ROOMTYPEID
WHERE D.DATEKEY BETWEEN V_START_TIME AND V_END_TIME
GROUP BY D.DATEKEY,
       D.YEARSS,
       D.MONTHSS,
       A.CHAINID;
    COMMIT;
END;

 

 

-----------------------------

--方法二：使用merge..into..更新(只能在Oracle中使用)
--语法：

MERGE INTO 目标表 --目标表指的是要更新数据的表
USING (增量) --增量指的是SELECT查询语句  
ON (匹配字段)  --匹配字段指的是能匹配到一条数据的一个字段或者多个字段（一次只能匹配一条数据）
WHEN MATCHED THEN UPDATE SET --UPDATE 和 SET 之间不需要加表名，匹配字段不需要更新。
  WHEN NOT MATCHED THEN INSERT VALUES --INSERT 和 VALUES 之间不需要加 INTO 表名
    --注释：需要插入数据，说明匹配字段匹配不到，此时需要插入整条数据，一个字段都不能少。

CREATE OR REPLACE PROCEDURE SP_DW_ROOM_DETAIL3(P_START_TIME VARCHAR2,P_END_TIME VARCHAR2) AS
V_START_TIME DATE := TO_DATE(P_START_TIME,'YYYY-MM-DD');
V_END_TIME DATE := TO_DATE(P_END_TIME,'YYYY-MM-DD');
BEGIN
  MERGE INTO DW_ROOM_DETAIL M
  USING (SELECT D.DATEKEY,
                     D.YEARSS AS date_year,
                     D.MONTHSS AS date_month,
                     A.CHAINID,
                     SUM(A.STATUSRMNG) AS status_rmng,
                     SUM(A.STATUSRMNG+A.ROOMDAY+A.ROOMHALFDAY+A.ROOMHOURDAY) AS status_rm,
                     SUM(NVL(B.AMOUNTRMNG,0)) AS AmountRmNg,
                     SUM(NVL(B.AMOUNTRMNG,0)+NVL(B.AMOUNTRMALLDAY,0)+NVL(B.AMOUNTRMOTHER,0)+NVL(B.AMOUNTRMDISCOUNT,0)) AS RoomAmount,
                     SUM(NVL(B.AMOUNTRMNG,0)+NVL(B.AMOUNTRMALLDAY,0)+NVL(B.AMOUNTRMOTHER,0)+NVL(B.AMOUNTRMDISCOUNT,0)+NVL(B.AMOUNTOTHER,0)) AS Revenue
              FROM ODS.ODS_T_DATE D
                JOIN ODS.ODS_roomcount_detail A
                  ON D.DATEKEY=TO_DATE(A.DATEKEY,'YYYYMMDD')
                JOIN ODS.ODS_ROOMAMOUNT_DETAIL B
                  ON A.DATEKEY=B.DATEKEY
                  AND A.CHAINID=B.CHAINID
                  AND A.ROOMTYPEID=B.ROOMTYPEID
              WHERE D.DATEKEY BETWEEN V_START_TIME AND V_END_TIME
              GROUP BY D.DATEKEY,
                     D.YEARSS,
                     D.MONTHSS,
                     A.CHAINID) N
  ON (M.DATEKEY=N.DATEKEY AND M.CHAINID=N.CHAINID)
  WHEN MATCHED THEN UPDATE SET
       M.date_year =   N.date_year,
       M.date_month =  N.date_month,
       M.status_rmng = N.status_rmng,
       M.status_rm =   N.status_rm,
       M.amountrmng =  N.amountrmng,
       M.roomamount =  N.roomamount,
       M.revenue =     N.revenue
   WHEN NOT MATCHED THEN INSERT 
     (M.DATEKEY,
   M.DATE_YEAR,
   M.DATE_MONTH,
   M.CHAINID,
   M.STATUS_RMNG,
   M.STATUS_RM,
   M.AMOUNTRMNG,
   M.ROOMAMOUNT,
   M.REVENUE)
     VALUES 
       (N.DATEKEY,
   N.DATE_YEAR,
   N.DATE_MONTH,
   N.CHAINID,
   N.STATUS_RMNG,
   N.STATUS_RM,
   N.AMOUNTRMNG,
   N.ROOMAMOUNT,
   N.REVENUE);
   COMMIT;
   END;

 

-------------------------
--方法三：使用游标增量更新
--操作方法：先把增量放入游标中，然后在逻辑体中一条一条数据的去判断，如果能够匹配到这条数据，就更新为最新的数据，
--如果匹配不到，就把这条数据插入到目标表中。

CREATE OR REPLACE PROCEDURE SP_DW_ROOM_DETAIL4(P_START_TIME VARCHAR2,P_END_TIME VARCHAR2) AS
V_START_TIME DATE := TO_DATE(P_START_TIME,'YYYY-MM-DD');
V_END_TIME DATE := TO_DATE(P_END_TIME,'YYYY-MM-DD');
CURSOR C_DW_ROOM_DETAIL IS
SELECT D.DATEKEY,
                     D.YEARSS AS date_year,
                     D.MONTHSS AS date_month,
                     A.CHAINID,
                     SUM(A.STATUSRMNG) AS status_rmng,
                     SUM(A.STATUSRMNG+A.ROOMDAY+A.ROOMHALFDAY+A.ROOMHOURDAY) AS status_rm,
                     SUM(NVL(B.AMOUNTRMNG,0)) AS AmountRmNg,
                     SUM(NVL(B.AMOUNTRMNG,0)+NVL(B.AMOUNTRMALLDAY,0)+NVL(B.AMOUNTRMOTHER,0)+NVL(B.AMOUNTRMDISCOUNT,0)) AS RoomAmount,
                     SUM(NVL(B.AMOUNTRMNG,0)+NVL(B.AMOUNTRMALLDAY,0)+NVL(B.AMOUNTRMOTHER,0)+NVL(B.AMOUNTRMDISCOUNT,0)+NVL(B.AMOUNTOTHER,0)) AS Revenue
              FROM ODS.ODS_T_DATE D
                JOIN ODS.ODS_roomcount_detail A
                  ON D.DATEKEY=TO_DATE(A.DATEKEY,'YYYYMMDD')
                JOIN ODS.ODS_ROOMAMOUNT_DETAIL B
                  ON A.DATEKEY=B.DATEKEY
                  AND A.CHAINID=B.CHAINID
                  AND A.ROOMTYPEID=B.ROOMTYPEID
              WHERE D.DATEKEY BETWEEN V_START_TIME AND V_END_TIME
              GROUP BY D.DATEKEY,
                     D.YEARSS,
                     D.MONTHSS,
                     A.CHAINID;
V_CNT NUMBER;
BEGIN
  FOR X IN C_DW_ROOM_DETAIL LOOP
    SELECT COUNT(*) 
    INTO V_CNT
    FROM DW_ROOM_DETAIL B
    WHERE B.DATEKEY = X.DATEKEY
      AND B.CHAINID=X.CHAINID;
   IF V_CNT = 1 THEN 
     UPDATE DW_ROOM_DETAIL A SET
            A.date_year =   X.date_year,
       A.date_month =  X.date_month,
       A.status_rmng = X.status_rmng,
       A.status_rm =   X.status_rm,
       A.amountrmng =  X.amountrmng,
       A.roomamount =  X.roomamount,
       A.revenue =     X.revenue
     WHERE A.DATEKEY = X.DATEKEY
      AND A.CHAINID=X.CHAINID;
      ELSE INSERT INTO dw_room_detail
             (datekey, date_year, date_month, chainid, status_rmng, status_rm, amountrmng, roomamount, revenue)
           VALUES
            (X.DATEKEY,
   X.DATE_YEAR,
   X.DATE_MONTH,
   X.CHAINID,
   X.STATUS_RMNG,
   X.STATUS_RM,
   X.AMOUNTRMNG,
   X.ROOMAMOUNT,
   X.REVENUE);   
   END IF;
  END LOOP;
  COMMIT;
END;

 

 

-----------------------------
--方法四：利用异常处理的逻辑增量更新
--操作方法：先把增量数据放入游标中，在逻辑体中直接把增量数据一条一条的插入到目标表中，
---如果违反主键约束，则进入异常处理，改为更新这条数据。

CREATE OR REPLACE PROCEDURE SP_DW_ROOM_DETAIL5(P_START_TIME VARCHAR2,P_END_TIME VARCHAR2) AS
V_START_TIME DATE := TO_DATE(P_START_TIME,'YYYY-MM-DD');
V_END_TIME DATE := TO_DATE(P_END_TIME,'YYYY-MM-DD');
CURSOR C_DW_ROOM_DETAIL IS
SELECT D.DATEKEY,
                     D.YEARSS AS date_year,
                     D.MONTHSS AS date_month,
                     A.CHAINID,
                     SUM(A.STATUSRMNG) AS status_rmng,
                     SUM(A.STATUSRMNG+A.ROOMDAY+A.ROOMHALFDAY+A.ROOMHOURDAY) AS status_rm,
                     SUM(NVL(B.AMOUNTRMNG,0)) AS AmountRmNg,
                     SUM(NVL(B.AMOUNTRMNG,0)+NVL(B.AMOUNTRMALLDAY,0)+NVL(B.AMOUNTRMOTHER,0)+NVL(B.AMOUNTRMDISCOUNT,0)) AS RoomAmount,
                     SUM(NVL(B.AMOUNTRMNG,0)+NVL(B.AMOUNTRMALLDAY,0)+NVL(B.AMOUNTRMOTHER,0)+NVL(B.AMOUNTRMDISCOUNT,0)+NVL(B.AMOUNTOTHER,0)) AS Revenue
              FROM ODS.ODS_T_DATE D
                JOIN ODS.ODS_roomcount_detail A
                  ON D.DATEKEY=TO_DATE(A.DATEKEY,'YYYYMMDD')
                JOIN ODS.ODS_ROOMAMOUNT_DETAIL B
                  ON A.DATEKEY=B.DATEKEY
                  AND A.CHAINID=B.CHAINID
                  AND A.ROOMTYPEID=B.ROOMTYPEID
              WHERE D.DATEKEY BETWEEN V_START_TIME AND V_END_TIME
              GROUP BY D.DATEKEY,
                     D.YEARSS,
                     D.MONTHSS,
                     A.CHAINID;
BEGIN
  FOR X IN C_DW_ROOM_DETAIL LOOP
    BEGIN
    INSERT INTO dw_room_detail
      (datekey, date_year, date_month, chainid, status_rmng, status_rm, amountrmng, roomamount, revenue)
    VALUES
      (X.datekey, X.date_year, X.date_month, X.chainid, X.status_rmng, X.status_rm, X.amountrmng, X.roomamount, X.revenue);
EXCEPTION --一旦出现异常
  
   WHEN DUP_VAL_ON_INDEX THEN  
     UPDATE dw_room_detail A SET
      A.date_year =   X.date_year,
       A.date_month =  X.date_month,
       A.status_rmng = X.status_rmng,
       A.status_rm =   X.status_rm,
       A.amountrmng =  X.amountrmng,
       A.roomamount =  X.roomamount,
       A.revenue =     X.revenue
      WHERE A.DATEKEY=X.DATEKEY 
        AND A.CHAINID=X.CHAINID;  
      END;
  END LOOP;
  COMMIT;
END;