oracle procedure简单的将临时表的数据插入或更新到目标表

CREATE OR REPLACE PROCEDURE DEAL_SYNC_SCH_CUSTPHONE_NEW AS
  CURSOR C_CURU IS
    SELECT *
      FROM CBS_COS.SCH_CUSTPHONE_TEMP S
     WHERE S.ID IN (SELECT T.ID
                      FROM CBS_COS.SCH_CUSTPHONE_TEMP T
                    MINUS
                    SELECT C.ID FROM CBS_COS.SCH_CUSTPHONE C);
  CURSOR C_CUR IS
    SELECT T.*
      FROM CBS_COS.SCH_CUSTPHONE_TEMP T, CBS_COS.SCH_CUSTPHONE C
     WHERE T.ID = C.ID;
  C_ROW  C_CUR%ROWTYPE;
  C_ROWU C_CURU%ROWTYPE;
  C_NN   NUMBER := 0;
BEGIN
  FOR C_ROW IN C_CUR LOOP
    UPDATE CBS_COS.SCH_CUSTPHONE C
       SET C.ID          = C_ROW.ID,
           C.PHONENO     = C_ROW.PHONENO,
           C.TYPE        = C_ROW.TYPE,
           C.CUSTID      = C_ROW.CUSTID,
           C.OPID        = C_ROW.OPID,
           C.CREATE_TIME = C_ROW.CREATE_TIME,
           C.UPDATE_TIME = C_ROW.UPDATE_TIME
     WHERE C.ID = C_ROW.ID;
    C_NN := C_NN + 1;
    IF MOD(C_NN, 100000) > 0 THEN
      COMMIT;
    END IF;
    COMMIT;
  END LOOP;

  FOR C_ROWU IN C_CURU LOOP
    INSERT INTO CBS_COS.SCH_CUSTPHONE
      (ID, PHONENO, TYPE, CUSTID, OPID, CREATE_TIME, UPDATE_TIME)
    VALUES
      (C_ROWU.ID,
       C_ROWU.PHONENO,
       C_ROWU.TYPE,
       C_ROWU.CUSTID,
       C_ROWU.OPID,
       C_ROWU.CREATE_TIME,
       C_ROWU.UPDATE_TIME);
    C_NN := C_NN + 1;
    IF MOD(C_NN, 100000) > 0 THEN
      COMMIT;
    END IF;
  END LOOP;
  COMMIT;
END DEAL_SYNC_SCH_CUSTPHONE_NEW;

最简单实现，但是查询次数会更多的另一种方法

CREATE OR REPLACE PROCEDURE DEAL_SYNC_SCH_CUSTPHONE AS
      CURSOR C_CUR IS SELECT *  FROM  CBS_COS.SCH_CUSTPHONE_TEMP;
    C_ROW C_CUR%ROWTYPE;
    C_NUM NUMBER   :=0;
    C_NN  NUMBER   :=0;
BEGIN
  FOR C_ROW IN C_CUR LOOP
    SELECT COUNT(1) INTO C_NUM FROM CBS_COS.SCH_CUSTPHONE C WHERE C.ID= C_ROW.ID;
    IF C_NUM >0 THEN
      UPDATE CBS_COS.SCH_CUSTPHONE C SET
      C.ID=C_ROW.ID,
      C.PHONENO=C_ROW.PHONENO,
      C.TYPE =C_ROW.TYPE,
      C.CUSTID  =C_ROW.CUSTID,
      C.OPID    =C_ROW.OPID,
      C.CREATE_TIME  =C_ROW.CREATE_TIME,
      C.UPDATE_TIME=  C_ROW.UPDATE_TIME
      WHERE C.ID= C_ROW.ID;
    ELSE
      INSERT INTO CBS_COS.SCH_CUSTPHONE  (ID, PHONENO, TYPE, CUSTID, OPID, CREATE_TIME, UPDATE_TIME)
      VALUES (C_ROW.ID, C_ROW.PHONENO, C_ROW.TYPE, C_ROW.CUSTID, C_ROW.OPID, C_ROW.CREATE_TIME, C_ROW.UPDATE_TIME);
    END IF;
    C_NN :=C_NN+1;
    IF MOD(C_NN,10000)>0  THEN
      COMMIT;
      END IF;
      END LOOP;
      COMMIT;
      END DEAL_SYNC_SCH_CUSTPHONE;

 

转载于:https://www.cnblogs.com/lianshan/p/5810422.html