Oracle中SQL使用(数据迁移和merge使用)

7 篇文章 0 订阅

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;

 

  • 0
    点赞
  • 4
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值