merge into用法

刚开始用游标，后来听取同事的建议采用merge into,效率上确实提升一大截，这个方法是从oracle10开始引入的。
create or replace procedure P_CRM_TF_SM_3GEND(--必须传1个参数，不然GTM报错
                                              in_channel IN VARCHAR2,
                                              v_resultcode    OUT NUMBER,
                                              v_resulterrinfo OUT VARCHAR2) IS
                                             
     --3G到期用户数据提取                                             
     v_begin_time        DATE := sysdate-1/24;
     v_end_time          DATE := sysdate;     
     --v_begin_time        DATE := to_date('2012-05-11 00:10:00','yyyy-mm-dd hh24:mi:ss');
     --v_end_time          DATE := to_date('2012-05-11 14:10:00','yyyy-mm-dd hh24:mi:ss'); 
/*     --取前两小时所有用户数据
     CURSOR c1 is
     SELECT EPARCHY_CODE,SERIAL_NUMBER,IS_REGISTER,IS_ORDER,REGISTER_TIME,ORDER_TIME,CREATE_TIME,CANCEL_TIME,REMARK FROM uop_uif1.TF_SM_3GEND_DZQD_JK@DBLNK_UIF1 t
      WHERE t.CREATE_TIME BETWEEN v_begin_time AND  v_end_time ;
      t_c1  c1%rowtype;
      v_num             NUMBER;
begin
     v_resultcode    := 0;
     v_resulterrinfo := '存储过程执行成功';
     if c1%isopen = false then
        open c1;   
     end if;
      loop
        FETCH c1 INTO t_c1;
        exit when c1%notfound;
        if (c1%found) then
          --统计该用户是否存在记录
          select count(1) into v_num from TF_SM_3GEND_DZQD_JK t where t.SERIAL_NUMBER = t_c1.serial_number;
          if (v_num > 0 ) then
            --如果用户已有数据，取用户最新的一条记录更新
            update TF_SM_3GEND_DZQD_JK set EPARCHY_CODE=t_c1.Eparchy_Code,IS_REGISTER=t_c1.Is_Register,IS_ORDER=t_c1.Is_Order,REGISTER_TIME=t_c1.Register_Time,ORDER_TIME=t_c1.Order_Time,CREATE_TIME=t_c1.Create_Time,CANCEL_TIME=t_c1.Cancel_Time,REMARK=t_c1.REMARK 
             where SERIAL_NUMBER = t_c1.serial_number and CREATE_TIME<t_c1.create_time;
             commit;
          ELSIF v_num = 0 THEN
           --如果没有用户记录，则插入该用户数据
            insert into TF_SM_3GEND_DZQD_JK (EPARCHY_CODE,SERIAL_NUMBER,IS_REGISTER,IS_ORDER,REGISTER_TIME,ORDER_TIME,CREATE_TIME,CANCEL_TIME,REMARK)
            values(t_c1.eparchy_code,t_c1.serial_number,t_c1.is_register,t_c1.is_order,t_c1.register_time,t_c1.order_time,t_c1.create_time,t_c1.cancel_time,t_c1.REMARK);
            commit;
          end if;
        end if;
      end loop;
     if c1%isopen then
     Close c1;
     end if;
     commit;*/
     --采用merge into 提升效率
begin
      v_resultcode    := 0;
      v_resulterrinfo := '存储过程执行成功';
      MERGE INTO TF_SM_3GEND_DZQD_JK t   
      USING (select * from uop_uif1.TF_SM_3GEND_DZQD_JK@DBLNK_UIF1 tt where  tt.create_time =
            (SELECT max(oo.create_time) FROM uop_uif1.TF_SM_3GEND_DZQD_JK@DBLNK_UIF1 oo
            WHERE oo.serial_number=tt.serial_number
            and oo.create_time BETWEEN v_begin_time AND  v_end_time group by oo.serial_number)) ot   
        ON (t.SERIAL_NUMBER = ot.SERIAL_NUMBER)
        WHEN MATCHED THEN  
        UPDATE  
         SET t.EPARCHY_CODE=ot.Eparchy_Code,
         t.IS_REGISTER=ot.Is_Register,
         t.IS_ORDER=ot.Is_Order,
         t.REGISTER_TIME=ot.Register_Time,
         t.ORDER_TIME=ot.Order_Time,
         t.CREATE_TIME=ot.Create_Time,
         t.CANCEL_TIME=ot.Cancel_Time,
         t.REMARK=ot.REMARK   
        WHERE t.CREATE_TIME < ot.create_time
        and ot.SERIAL_NUMBER in (select SERIAL_NUMBER from Tf_Sm_3gend_Dzqd_Jk)
        WHEN NOT MATCHED THEN  
         INSERT  
         VALUES (ot.serial_number,ot.is_register,ot.is_order,ot.register_time,
         ot.order_time,ot.cancel_time,ot.create_time,ot.REMARK,ot.eparchy_code)
         WHERE ot.serial_number not in (select SERIAL_NUMBER from Tf_Sm_3gend_Dzqd_Jk );
         commit;
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        v_resultcode    := -1;
        v_resulterrinfo := '运行过程中出错' || SQLERRM;
        RETURN;
end P_CRM_TF_SM_3GEND;