Oracle中SQL使用（数据迁移和merge使用）

1. 数据迁移使用

1）row_cur ：每一行的变量

2）not exists：筛选出差异的部分，不存在的则进行同步（可用于后续增量跑，可重复跑，保证数据整体一致）

3）每1000条提交一次

4）以每天的维度同步数据

--迁移数据
--初始化报表
create or replace procedure ticketFlowMigrate(v_cnt in number) is
      cursor cur_query is select a.FLOW_NO,a.CREATE_TIME,a.UPDATE_TIME,a.BUSI_SYS,a.BUSI_TIME,a.BUSI_CODE,a.BUSI_FLOW_NO,a.COUPON_CODE,a.BATCH_ID,a.OPT_TYPE,a.REDUCE_AMOUNT,a.RESULT_CODE,a.RESULT_MSG from TICKET_FLOW a where a.batch_id in
       (select distinct ac.batch_id from AWARD_GROUP g,AWARD a,AWARD_COUPON_ASSOCIATION ac where g.awards_id=a.awards_id and a.award_id=ac.award_id and g.activity_id in (select 
        activity_id
        from activity
        where start_date < = sysdate
        and end_date >= sysdate
        and activity_state = '04'))
        and a.CREATE_TIME >= to_date('2018-09-01 00:00:00','yyyy-MM-dd hh24:mi:ss')+v_cnt and a.CREATE_TIME < to_date('2018-09-01 00:00:00','yyyy-MM-dd hh24:mi:ss')+v_cnt+1
        and not exists (select 1 from TICKET_FLOW_20201026 t where a.FLOW_NO=t.FLOW_NO);
row_cur cur_query%rowtype;
v_insertCnt number(9);
begin
   v_insertCnt := 0;
   for row_cur in cur_query loop
 insert into TICKET_FLOW_20201026(FLOW_NO,CREATE_TIME,UPDATE_TIME,BUSI_SYS,BUSI_TIME,BUSI_CODE,BUSI_FLOW_NO,COUPON_CODE,BATCH_ID,OPT_TYPE,REDUCE_AMOUNT,RESULT_CODE,RESULT_MSG)    values(row_cur.FLOW_NO,row_cur.CREATE_TIME,row_cur.UPDATE_TIME,row_cur.BUSI_SYS,row_cur.BUSI_TIME,row_cur.BUSI_CODE,row_cur.BUSI_FLOW_NO,row_cur.COUPON_CODE,row_cur.BATCH_ID,row_cur.OPT_TYPE,row_cur.REDUCE_AMOUNT,row_cur.RESULT_CODE,row_cur.RESULT_MSG);
 v_insertCnt := v_insertCnt+1; 
    if mod(v_insertCnt,1000)=0 then
       commit;
    end if;
   end loop;
   commit;
end;
    

DECLARE
    max_loop number(9);
    begin
      select ceil(sysdate-to_date('2018-08-01 00:00:00','yyyy-MM-dd hh24:mi:ss')+1) into max_loop from dual;
      for v_cnt in 0..max_loop loop
         ticketFlowMigrate(v_cnt);  
      end loop;
    end;

2. 数据更新或者插入

--存在更新，不存在插入，可使用merge into
merge into的形式：

MERGE INTO [target-table] A USING [source-table sql] B ON([conditional expression] and [...]...)
WHEN MATCHED THEN
    [UPDATE sql]
WHEN NOT MATCHED THEN
    [INSERT sql]

MERGE INTO A_MERGE A USING (select B.AID,B.NAME,B.YEAR from B_MERGE B) C ON (A.id=C.AID)
WHEN MATCHED THEN
  UPDATE SET A.YEAR=C.YEAR 
WHEN NOT MATCHED THEN
  INSERT(A.ID,A.NAME,A.YEAR) VALUES(C.AID,C.NAME,C.YEAR);
commit;