循环TRUNCATE表，再ENABLE约束索引等

 

CREATE OR REPLACE PROCEDURE STG.FP_REMOVE_MST_OLD_DATA (EXITCODE OUT NUMBER)
IS
   /***********************************************************************
          **  存储过程名称:  FP_REMOVE_OLD_DATA
         **  存储过程创建日期: 
         **  存储过程创建人: 
         **  目的:  删除FP上次在ODS的输出数据

         **  输入参数:
         **  输出参数:EXITCODE 预留接口给外部调用者，让其知道存储过程的执行结果，0.预定逻辑执行完成; 1.遇到异常，预定逻辑未执行完成
         **  返回值:
         **  用法:
         **
         ** 修订版本:
         **  版本号     修订时间    修订人        描述
         **  ---------  ----------  ------------  ------------------------
         **    1.0      
         **
    **********************************************************************/
   ----------------------- 以下定义存储过程使用的变量---------------------------------
   V_PKGName          VARCHAR2 (40) := 'PKG_FP';
   V_ProName          VARCHAR2 (40) := 'FP_REMOVE_MST_OLD_DATA';
   V_Action           VARCHAR2 (1);
   V_Step             NUMBER := 0;
   V_MainTable        VARCHAR2 (40);
   E_ErrMessage       VARCHAR2 (3000);

----------------------- 以下是存储过程的主体------------------------------------------

BEGIN
   EXITCODE := 0;
   
   
   DELETE FROM STG.DISABLED_R_CONSTRAINT;
   
   INSERT INTO STG.DISABLED_R_CONSTRAINT SELECT  CONSTRAINT_NAME from all_constraints where constraint_type='R' and status='DISABLED' AND OWNER='ABPPMGR';
   
   COMMIT;
  
   V_Step := V_Step + 1;
   V_Action := PKG_CONSTANT.CONS_ACTION_DELETE;
   V_MainTable := 'ALL';
   PKG_UTIL.CREATELOG (
      V_PKGName,                                       --Put package name here
      V_ProName,                                     --Put procedure name here
      V_Step,                                       --Step seqence No. if have
      V_Action,                                       --Action 'I','D','U','T'
      PKG_CONSTANT.CONS_MSG_INFO,     --Message tppe, 'SUCCESS' or 'EXCEPTION'
      'start to delete old data.', --Any generic information here
      V_MainTable);                               --Major table name processed
   FOR c_cons_dis
      IN (SELECT    'alter table '
                 || T2.owner
                 || '.'
                 || T2.table_name
                 || ' disable constraint '
                 || t2.CONSTRAINT_NAME
                    sqltext
           FROM all_constraints T2
           WHERE     t2.owner = 'ABPPMGR'
                 AND T2.CONSTRAINT_TYPE = 'R'
                 AND  T2.table_name IN
              (select phy_mst from abppmgr.SYS_TABLE_MAP))
   LOOP
      EXECUTE IMMEDIATE c_cons_dis.sqltext;
   END LOOP;

   FOR c_trunc
      IN (SELECT 'truncate table ABPPMGR.' || table_name || ' drop storage' sqltext
  FROM all_tables
 WHERE owner = 'ABPPMGR'
       AND table_name IN
              ('MST_BOMCOMPONENTS',
'MST_ROUTINGOPERATION',
'MST_ROUTINGHEADER',
'MST_BOMHEADER',
'MST_BOMCOMPONENTS_P',
'MST_ITEMMASTER',
'MST_ITEMSITEMASTER',
'MST_ITEMBOMROUTING_P',
'MST_ITEMGROUPMASTER',
'MST_OPRESOURCE',
'MST_ITEMSITEIPPARM',
'MST_APPRSUPITEM',
'MST_UOMCONVERSION',
'MST_ITEMGROUPDETAIL',
'MST_APPRSUPITEM_P',
'MST_ITEMBOMROUTING',
'MST_SUPPLIERCALENDAR',
'PURCHORDLINE',
'INVENTORY',
'INVENTORYPROPERTY',
'SALESORDERLINE',
'SALESORDERLINE_P',
'FORECASTDETAIL'))
   LOOP
      EXECUTE IMMEDIATE c_trunc.sqltext;
   END LOOP;

   FOR c_cons_en
      IN (SELECT    'alter table '
                 || T2.owner
                 || '.'
                 || T2.table_name
                 || ' enable novalidate constraint '
                 || t2.CONSTRAINT_NAME
                    sqltext
            FROM all_constraints T2
           WHERE     t2.owner = 'ABPPMGR'
                 AND T2.CONSTRAINT_TYPE = 'R'
                 AND  T2.table_name IN
              (select phy_mst from abppmgr.SYS_TABLE_MAP) AND T2.CONSTRAINT_NAME NOT IN (SELECT CONSTRAINT_NAME FROM STG.DISABLED_R_CONSTRAINT) )
   LOOP
      EXECUTE IMMEDIATE c_cons_en.sqltext;
   END LOOP;
   V_Step := V_Step + 1;
   V_Action := PKG_CONSTANT.CONS_ACTION_DELETE;
   V_MainTable := 'ALL';
   PKG_UTIL.CREATELOG (V_PKGName,                      --Put package name here
                       V_ProName,                    --Put procedure name here
                       V_Step,                      --Step seqence No. if have
                       V_Action,                      --Action 'I','D','U','T'
                       PKG_CONSTANT.CONS_MSG_INFO, --Message tppe, 'SUCCESS' or 'EXCEPTION'
                       'remove data completed.', --Any generic information here
                       V_MainTable);
EXCEPTION
   --总程序异常处理部分
   WHEN OTHERS
   THEN
      BEGIN
         ROLLBACK;
         EXITCODE := 1;
         e_ErrMessage :=
            SUBSTR (SQLERRM, 1, 1000)
            || SUBSTR (DBMS_UTILITY.FORMAT_ERROR_BACKTRACE, 1, 2000);
         PKG_UTIL.CREATELOG (V_PKGName,
                             V_ProName,
                             V_Step,
                             V_Action,
                             PKG_CONSTANT.CONS_MSG_EXCEPTION,
                             e_ErrMessage,
                             V_MainTable);
      END;
END;
/