CREATE OR REPLACE PROCEDURE ADD_TABLE_PARTITION(I_STARTDATE IN VARCHAR2,
I_ENDDATE IN VARCHAR2,
O_ERRCODE OUT INTEGER,
O_ERRMSG OUT VARCHAR2) IS
/****************************************************************************************
*说明:1.分区名格式为 xxx_xxx_xxx_1991 *
-
2.test结束时间年份为:需要分区结束的年份 *
-
*
****************************************************************************************/
V_SQL VARCHAR2(4000);
V_TEX NUMBER; --最大分区年份
V_TEX_END NUMBER; --结束分区年份
V_TABLE_NAME VARCHAR2(200);–需要分区的表名
V_PARTITION_NAME VARCHAR2(200);–分区名(表名+年份)
V_DATE VARCHAR2(200);–分区时间范围年份
AI NUMBER; —拼接的分区年份
BEGIN
V_TEX_END := SUBSTR(I_ENDDATE, 1, 4);
FOR W IN(SELECT PARTITION_NAME,TEX, TABLE_NAME
FROM (SELECT T.PARTITION_NAME,
T.TABLE_NAME,
REGEXP_SUBSTR(T.PARTITION_NAME, ‘[^_]+’, 1, 4)TEX
FROM USER_TAB_PARTITIONS T
WHERE 1 = 1
ORDER BY PARTITION_NAME DESC)
WHERE ROWNUM = 1) LOOP
FOR I IN W.TEX .. V_TEX_END LOOP
SELECT PARTITION_NAME,TEX, TABLE_NAME
INTO V_PARTITION_NAME,V_TEX, V_TABLE_NAME
FROM (SELECT T.PARTITION_NAME,
T.TABLE_NAME,
REGEXP_SUBSTR(T.PARTITION_NAME, '[^_]+', 1, 4)TEX
FROM USER_TAB_PARTITIONS T
WHERE 1 = 1
ORDER BY PARTITION_NAME DESC)
WHERE ROWNUM = 1;
V_DATE:= V_TEX + 2 ||'-01-01';
AI := V_TEX + 1;
V_PARTITION_NAME:= V_TABLE_NAME||'_'||AI;
V_SQL :='ALTER TABLE'||' '||V_TABLE_NAME||' '||'ADD'||' '||'PARTITION'||' '
||V_PARTITION_NAME||' '|| 'VALUES LESS THAN(TO_DATE('
||''''||V_DATE||''''||','||'''YYYY-MM-DD'''||'))';
EXECUTE IMMEDIATE V_SQL;
END LOOP;
END LOOP;
O_ERRCODE:=1;
O_ERRMSG:=‘添加分区成功!’;
EXCEPTION
WHEN OTHERS THEN
O_ERRCODE := SQLCODE;
O_ERRMSG := SUBSTR(‘未处理异常:’ || SQLERRM || ‘。’ ||
DBMS_UTILITY.FORMAT_ERROR_BACKTRACE,
1,
1000);
END ADD_TABLE_PARTITION;