CREATE OR REPLACE PROCEDURE TOPUP4.sp_tab_maintain_partitions (
   user_name   IN VARCHAR2
)
IS
   g_user_name   VARCHAR2 (50);                                     --用户名称

   PROCEDURE sp_tab_add_partitions (user_name IN VARCHAR2)
   IS
      PRAGMA AUTONOMOUS_TRANSACTION;                                --自治事务
      v_user_name      VARCHAR2 (50);                               --用户名称
      v_tab_name       VARCHAR2 (100);                            --分区表名称
      v_owner          VARCHAR2 (50);                             --所有者名称
      v_day            DATE;                                  --第二天表分区名
      v_next_date      DATE;                                      --第三天日期
      v_date_format1   VARCHAR2 (4000);                     --调整日期显示格式
      v_date_format2   VARCHAR2 (4000);                     --调整日期显示格式
      v_add_sql_1      VARCHAR2 (4000);                --增加表分区SQL第一部分
      v_add_sql_2      VARCHAR2 (4000);                --增加表分区SQL第二部分
      v_add_sql        VARCHAR2 (4000);                        --增加表分区SQL
   BEGIN
      v_user_name := user_name;                                     --用户名称

      v_tab_name := '0';                                          --分区表名称

      v_owner := UPPER (v_user_name);                             --所有者名称

      --定义游标
      DECLARE
         CURSOR part_table_name
         IS
            SELECT   table_name
              FROM   all_part_tables
             WHERE   wner = v_owner;
      --开始使用游标取数据

      BEGIN
         OPEN part_table_name;

         LOOP
            FETCH part_table_name INTO   v_tab_name;

            --游标取不到数据则退出
            EXIT WHEN part_table_name%NOTFOUND;

            --调整日期格式YYYYMMDD
            v_date_format1 :=
                  'alter session set nls_date_format='
               || ''''
               || 'yyyymmdd'
               || '''';

            EXECUTE IMMEDIATE v_date_format1;

            SELECT   SYSDATE + 1 INTO v_day FROM DUAL;

            --增加表分区SQL第一部分
            v_add_sql_1 :=
                  'ALTER TABLE '
               || v_user_name
               || '.'
               || v_tab_name
               || '  ADD PARTITION P'
               || v_day
               || ' VALUES LESS THAN (TIMESTAMP'
               || '''';

            --调整日期格式YYYY-MM-DD
            v_date_format2 :=
                  'alter session set nls_date_format='
               || ''''
               || 'yyyy-mm-dd'
               || '''';

            EXECUTE IMMEDIATE v_date_format2;

            SELECT   SYSDATE + 2 INTO v_next_date FROM DUAL;

            --增加表分区SQL第二部分
            v_add_sql_2 :=
                  v_next_date
               || ' 00'
               || ':'
               || '00'
               || ':'
               || '00'
               || ''
               || ''''
               || ')';

            --增加表分区SQL

            v_add_sql := v_add_sql_1 || v_add_sql_2;

            --增加表分区SQL
            --      v_add_sql :=
            --            'ALTER TABLE TOPUP4.TAB_TOPUP_CARDTOPLOG_TEST  ADD PARTITION P'
            --         || v_day
            --         || ' VALUES LESS THAN (TIMESTAMP'
            --         || ''''
            --         || v_next_date
            --         || ' 00'
            --         || ':'
            --         || '00'
            --         || ':'
            --         || '00'
            --         || ''
            --         || ''''
            --         || ')';
            --
            --         dbms_output.put_line(v_add_sql);

            EXECUTE IMMEDIATE v_add_sql;
         --   END;

         END LOOP;

         CLOSE part_table_name;
      END;
   --发生异常时返回错误码
   EXCEPTION
      WHEN OTHERS
      THEN
         DBMS_OUTPUT.put_line(v_owner || ':' || v_tab_name
                              || ' ADD PARTITION ERROR,PLEASE CHECK OPERATION AGAIN,THANKS!');
         ROLLBACK;
   END;

   PROCEDURE sp_tab_del_partitions (user_name IN VARCHAR2)
   IS
      PRAGMA AUTONOMOUS_TRANSACTION;                                --自治事务

      v_user_name     VARCHAR2 (50);                                --用户名称
      v_tab_name      VARCHAR2 (100);                             --分区表名称
      v_owner         VARCHAR2 (50);                              --所有者名称
      v_7d_ago_date   DATE;                                       --前七天日期
      v_date_format   VARCHAR2 (4000);                      --调整日期显示格式
      v_del_sql       VARCHAR2 (4000);                         --增加表分区SQL
   BEGIN
      v_user_name := user_name;                                     --用户名称
      v_tab_name := '0';                                          --分区表名称
      v_owner := UPPER (v_user_name);                             --所有者名称

      --定义游标
      DECLARE
         CURSOR part_table_name
         IS
            SELECT   table_name
              FROM   all_part_tables
             WHERE   wner = v_owner;
      --开始使用游标取数据

      BEGIN
         OPEN part_table_name;

         LOOP
            FETCH part_table_name INTO   v_tab_name;

            --游标取不到数据则退出
            EXIT WHEN part_table_name%NOTFOUND;

            --调整日期格式YYYYMMDD
            v_date_format :=
                  'alter session set nls_date_format='
               || ''''
               || 'yyyymmdd'
               || '''';

            EXECUTE IMMEDIATE v_date_format;

            --删除七天以前的分区
            SELECT   SYSDATE - 3 INTO v_7d_ago_date FROM DUAL;

            v_del_sql :=
                  'ALTER TABLE '
               || v_user_name
               || '.'
               || v_tab_name
               || ' DROP PARTITION P'
               || v_7d_ago_date
               || ' update global indexes';

            EXECUTE IMMEDIATE v_del_sql;
         --END;
         END LOOP;

         CLOSE part_table_name;
      END;
   --发生异常时返回错误码
   EXCEPTION
      WHEN OTHERS
      THEN
         DBMS_OUTPUT.put_line(v_owner || ':' || v_tab_name
                              || ' DEL PARTITION ERROR,PLEASE CHECK OPERATION AGAIN,THANKS!');
         ROLLBACK;
   END;
BEGIN
   --验证DDL对事务的提交影响
   /*
   INSERT INTO m
     VALUES   (3);
   */
   g_user_name := user_name;                                        --用户名称

   --增加第二天的表分区
   sp_tab_add_partitions (g_user_name);

   --删除七天以前的分区
   sp_tab_del_partitions (g_user_name);
--验证DDL对事务的提交影响
/*
ROLLBACK;
*/
END sp_tab_maintain_partitions;
/