自动增加日分区表分区

CREATE OR REPLACE PROCEDURE PRC_ADD_PARTITION_DAY
 IS
  --/************************************************************************************
  -- 程序名称:    PRC_ADD_PARTITION_DAY
  -- 功能描述:    增加表日分区存储过程 
  -- 期达目的:    将此程序挂起  每次增加两个月的分区，每月执行一次
  -- 注意：       表分区里面切忌不要出现2个表空间 不然这个会有问题
  -- 输入参数:    'YYYYMM' - <作业的处理日期>
  -- 输出参数:    0 - <0为正常结束，其余为异常>
  -- 输入资源:
  -- 输出资源:
  -- 中间资源:    <用户名>.<中间表或视图等对象名>
  -- 创建人员:    XXX
  -- 创建日期:    20120726
  -- 版本说明:    V1.0
  -- 修改人员:    (针对程序的任何修改都需要记录修改人员)
  -- 修改日期:    (针对程序的任何修改都需要记录修改日期)
  -- 修改原因:    (针对程序的任何修改都需要记录修改原因，如果多次修改需依次记录)
  -- 版本说明:
  -- 执行说明：
  -- 公司名称:       
/************************************************************************************
  --执行MM月的数据，输入的日期参数为YYYY(MM+1)DD，如'20091201'则是执行11月份的数据。

  -- 编写规则说明
  -- 说明1：所有自定义变量均用小写，并以v_打头；所有字段名均用大写
  -- 说明2: 所有关键值均用大字；模式名、表名、函数名、存储过程名均用大写
  -- 说明3: 缩进同其它程序，里面的逻辑体比外面的逻辑体向有缩进一个TAB键。
  --        对齐主要是针对SQL语句，采用关键字换行和右对齐的方式（如果不方便也可使用左对齐。
  -- 说明4: SQL语句目标和源要齐整
  -- 说明5: 对每个表的数据作改变后都要显示的提交
  v_sql       VARCHAR2(10000) DEFAULT ''; -- 动态SQL变量,注意SQL长度
  v_prc       VARCHAR2(40);
  v_date      VARCHAR2(8);
  v_stepnum   NUMBER          DEFAULT 0;           -- 运行步骤
  v_errmsg    VARCHAR2(1024)  DEFAULT '正常';
  TYPE V_CURTYPE IS REF CURSOR;
  v_open_cur  V_CURTYPE;  -- 定义游标 循环迭代
  v_table_name VARCHAR2(40); -- 表名
  v_table_owner VARCHAR2(40); -- 用户名
  v_max_part_mon VARCHAR2(40); -- 已经存在在系统里面的最大日期
  v_tablespace_name VARCHAR2(40); -- 这个表分区的表空间
  v_max_next_mon VARCHAR2(40); -- 要拓展的表分区的日期
  v_max_next2_mon VARCHAR2(40);-- 要拓展的表分区的日期的下一天
  v_num NUMBER ;

BEGIN
  v_prc:='PRC_ADD_PARTITION_DAY';
  SELECT to_char(SYSDATE,'yyyymmdd') INTO v_date FROM  dual;
  --正文SQL处理

   -- 1 拿出需要拓展月分区的表的相关信息
   FOR v_num IN 1..63 
   LOOP 
       OPEN v_open_cur FOR
       'SELECT TABLE_NAME,TABLE_OWNER,MAX(SUBSTR(PARTITION_NAME,2,8)),TABLESPACE_NAME FROM ALL_TAB_PARTITIONS
       WHERE TABLE_OWNER=''XXX''
       --AND TABLE_NAME LIKE ''MD%'' --例如某一类以MD开头的表
       AND LENGTH(RTRIM(PARTITION_NAME))=9  AND table_name NOT LIKE ''%$%'' 
       GROUP BY TABLE_NAME,TABLE_OWNER,TABLESPACE_NAME';

      -- 开始把值赋给变量 准备循环迭代了
       LOOP
        FETCH v_open_cur INTO v_table_name,v_table_owner,v_max_part_mon,v_tablespace_name;
        EXIT WHEN v_open_cur%NOTFOUND;

         -- 把循环里面的最大day加1 作为下一个天 为开始拓展表分区做好准备
         SELECT to_char(to_date(v_max_part_mon,'YYYYMMDD')+1,'YYYYMMDD') INTO v_max_next_mon FROM DUAL;
         SELECT to_char(to_date(v_max_part_mon,'YYYYMMDD')+2,'YYYYMMDD') INTO v_max_next2_mon FROM DUAL;

         -- 开始拓展表空间
         v_stepnum :=v_stepnum+1;
         if (v_table_name!='D_CHN_G_CEILL_KPI') AND (v_table_name!='D_CHN_W_CEILL_KPI')  then   ----排除 表D_CHN_G_CEILL_KPI
	          v_sql:= 'ALTER TABLE '||v_table_owner||'.'||v_table_name||' ADD PARTITION P'||v_max_next_mon||' VALUES LESS THAN ('''||v_max_next2_mon||''') TABLESPACE '||v_tablespace_name||' ';
            EXECUTE IMMEDIATE v_sql;
            PRC_LOG_WRT(v_date,v_prc,v_stepnum,v_sql,v_errmsg,SQL%ROWCOUNT);
            COMMIT;
            
         ELSIF (v_table_name='D_CHN_G_CEILL_KPI') OR (v_table_name='D_CHN_W_CEILL_KPI') then
		        v_sql:= 'ALTER TABLE '||v_table_owner||'.'||v_table_name||' ADD PARTITION P'||v_max_next_mon||' VALUES LESS THAN 
            (TO_DATE('''||v_max_next2_mon||''',''YYYYMMDD'',''NLS_CALENDAR=GREGORIAN'')) TABLESPACE '||v_tablespace_name;
            EXECUTE IMMEDIATE v_sql;
            PRC_LOG_WRT(v_date,v_prc,v_stepnum,v_sql,v_errmsg,SQL%ROWCOUNT);
            COMMIT;
            
         end if;
       END LOOP;
       CLOSE v_open_cur;

   END LOOP;


     -- 存储过程错误信息记录
  EXCEPTION
  WHEN OTHERS THEN

    v_errmsg := v_stepnum || '步出错：' || SQLERRM;
    ---插入日志
    PRC_LOG_WRT(v_date,v_prc,v_stepnum,v_sql,v_errmsg,-1);
    COMMIT;

END;