/* FORMATTED ON 2012/04/27 15:09 (FORMATTER PLUS V4.8.8) */
--STEP1:CREATE TABLE HMD.R_WIP_TRACKING_T_TEMP
CREATE TABLE hmd.r_wip_tracking_t_temp AS SELECT * FROM sfis4.r_wip_tracking_t@hmdsfc WHERE 1 = 2;
---------------------------------------
--STEP2:CREATE r_wip_tracking_t_archived_SP

CREATE OR REPLACE PROCEDURE sfis4.r_wip_tracking_t_archived_sp
--(RES OUT VARCHAR2)
AS
   movedate   DATE;
   count01    NUMBER;
   count02    NUMBER;
   e_icount   EXCEPTION;
   e_bcount   EXCEPTION;
BEGIN
   movedate :=
      TO_DATE (TO_CHAR (SYSDATE - 365, 'yyyymmdd') || '0000',
               'yyyymmddhh24mi');

/*修改數據歸檔狀態標簽*/
   UPDATE sfis4.r_wip_tracking_t
      SET data_status = 'NO' -----------必需得充分利用該欄位狀態、默認值為NULL；數據流NULL-->NO-->YES
    WHERE data_status IS NULL AND in_station_time <= movedate AND ROWNUM <= 5000;

COMMIT;

/*查詢需ARCHIVE的數據量*/
   SELECT COUNT (1)
     INTO count01
     FROM sfis4.r_wip_tracking_t
    WHERE data_status = 'NO' AND in_station_time <= movedate ;

-------------------------------------------------------
DELETE FROM hmd.r_wip_tracking_t_temp@linkuser
WHERE data_status = 'NO' AND in_station_time <= movedate;--------避免歸檔表中出現重復數據、保證數據的唯一性

COMMIT;

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

/*

IF count01 > 5000 ---當總需求archive數據量>5000筆，則對count01重新賦值
THEN
count01 := 5000;

      INSERT INTO hmd.r_wip_tracking_t_temp@linkuser
         SELECT *
           FROM sfis4.r_wip_tracking_t
          WHERE data_status = 'NO' AND in_station_time <= movedate AND ROWNUM <= 5000;

   ELSE -- -當總需求archive數據量<=5000筆，則直接將數據insert
      INSERT INTO hmd.r_wip_tracking_t_temp@linkuser
         SELECT *
           FROM sfis4.r_wip_tracking_t
          WHERE data_status = 'NO' AND in_station_time <= movedate;

END IF;

COMMIT;

*/

INSERT INTO hmd.r_wip_tracking_t_temp@linkuser

         SELECT *
           FROM sfis4.r_wip_tracking_t
          WHERE data_status = 'NO' AND in_station_time <= movedate;

END IF;

COMMIT;

   SELECT COUNT (1)
     INTO count02
     FROM hmd.r_wip_tracking_t_temp@linkuser
    WHERE data_status = 'NO' AND in_station_time <= movedate;

/*匹配數據是否有遺失*/
   IF count01 = count02
   THEN
      DELETE FROM sfis4.r_wip_tracking_t
            WHERE data_status = 'NO' in_station_time <= movedate AND ROWNUM <= 5000;

      --COMMIT;
      UPDATE hmd.r_wip_tracking_t_temp@linkuser
         SET data_status = 'YES'
       WHERE data_status = 'NO' AND in_station_time <= movedate;

      COMMIT;
   -- RAISE E_ICOUNT;
   ELSE
      DELETE FROM hmd.r_wip_tracking_t_temp@linkuser
            WHERE data_status = 'NO' AND in_station_time <= movedate; --------數據遷移失敗或者只遷移了部分

      COMMIT;
   -- RAISE E_BCOUNT;
   END IF;
/*
EXCEPTION
   WHEN E_ICOUNT
   THEN
--     RES := 'ARCHIVE_OK';
   WHEN E_BCOUNT
   THEN
--      RES := 'INSERT_INTO_ERROR';
   WHEN OTHERS
   THEN
--      RES := 'OWRONG';
*/
END;
/

ITPub博客

archived_sp

最新文章