CREATE OR REPLACE PROCEDURE PRC_ADD_PARTITION_MON IS --/************************************************************************************ -- 程序名称: PRC_ADD_PARTITION_MON -- 功能描述: 增加表月分区存储过程 -- 期达目的: 将此程序挂起 每月1自动在最大表分区上面加1个月 无需人为来操作 -- 注意: 表分区里面切忌不要出现2个表空间 不然这个会有问题 -- 输入参数: 'YYYYMM' - <作业的处理日期> -- 输出参数: 0 - <0为正常结束,其余为异常> -- 输入资源: -- 输出资源: -- 中间资源: <用户名>.<中间表或视图等对象名> -- 创建人员: XXXX -- 创建日期: 20120725 -- 版本说明: 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);-- 要拓展的表分区的月份的下一个月 BEGIN v_prc:='PRC_ADD_PARTITION_MON'; SELECT to_char(SYSDATE,'yyyymmdd') INTO v_date FROM dual; --正文SQL处理 --参考思路 --先把表,表用户,表分区最大值,表分区所在表空间拿出来 --循环迭代 取出这些表的月份的下一个月份值 --取到下一个月的值 添加表分区 -- 1 拿出需要拓展月分区的表的相关信息 OPEN v_open_cur FOR 'SELECT TABLE_NAME,TABLE_OWNER,MAX(SUBSTR(PARTITION_NAME,2,6)),TABLESPACE_NAME FROM ALL_TAB_PARTITIONS WHERE TABLE_OWNER=''JXZZJY'' --AND TABLE_NAME LIKE ''MD%'' AND LENGTH(RTRIM(PARTITION_NAME))=7 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; -- 把循环里面的最大月份加1 作为下一个月 为开始拓展表分区做好准备 SELECT TO_CHAR(ADD_MONTHS(TO_DATE(v_max_part_mon,'YYYYMM'),1),'YYYYMM') INTO v_max_next_mon FROM DUAL; SELECT TO_CHAR(ADD_MONTHS(TO_DATE(v_max_part_mon,'YYYYMM'),2),'YYYYMM') INTO v_max_next2_mon FROM DUAL; -- 开始拓展表空间 v_stepnum :=v_stepnum+1; 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; END LOOP; CLOSE v_open_cur; -- 存储过程错误信息记录 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;