ORACLE表分区的自动创建（PLSQL）

柚柚柚子、

已于 2024-07-08 13:55:40 修改

阅读量1k

点赞数 4

文章标签：数据库 oracle

于 2024-03-04 13:34:55 首次发布

本文链接：https://blog.youkuaiyun.com/mdaefsc/article/details/136449817

版权

该篇文章详细描述了一个Oracle数据库中的PL/SQL存储过程，用于在指定日期范围内为表添加分区。过程通过循环遍历表的现有分区，创建新的分区以满足特定的时间范围。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

PROCEDURE P_ADD_TABLE_PARTITION(I_STARTDATE IN VARCHAR2,
I_ENDDATE IN VARCHAR2,
O_ERRCODE OUT INTEGER,
O_ERRMSG OUT VARCHAR2) IS
/****************************************************************************

       ===========================================================================

存储过程名：仅支持表分区取名规范为：TABLENAME + 分区年份
作者：
创建日期： 2024-03-21
版本号： 1.1.1
处理流程：如:DIM_SYS_TIME 的分区为DIM_SYS_TIME_1989
修改历史：修改时间修改人修改的哪个步骤修改原因
*****************************************************************************/

V_SQL VARCHAR2(4000);
V_LOOPYEAR NUMBER; --循环年份
V_PARTITION_END NUMBER; --目标分区 ——分区结束
V_PARTITION_TABLENAME VARCHAR2(200); --需要分区的表名
V_PARTITION_NAME VARCHAR2(200); --分区名（表名+年份）
V_PARTITION_ENDYEAR VARCHAR2(200); --分区截至年
V_PARTITION_YEAR NUMBER; --分区年份
V_PARTITION_LENGTH NUMBER; --距离分区年份几个"_"间隔
BEGIN

V_PARTITION_END := SUBSTR(I_ENDDATE, 1, 4);

EXECUTE IMMEDIATE ‘TRUNCATE TABLE TABLE_PARTITION_STATE DROP STORAGE’;

FOR K IN (SELECT DISTINCT LENGTH(T.PARTITION_NAME) -
LENGTH(REPLACE(T.PARTITION_NAME, ‘_’, ‘’)) PARTITION_YEAR,–分区数标识
T.PARTITION_NAME,
T.TABLE_NAME
FROM USER_TAB_PARTITIONS T
WHERE T.TABLE_NAME NOT LIKE’%=%') LOOP

V_PARTITION_LENGTH := K.PARTITION_YEAR + 1;

FOR W IN (SELECT PARTITION_NAME, TABLE_NAME, YEAR_PARTITION_MAX
FROM (SELECT T.PARTITION_NAME,
T.TABLE_NAME,
REGEXP_SUBSTR(T.PARTITION_NAME,
‘[^_]+’,
1,
V_PARTITION_LENGTH) YEAR_PARTITION_MAX
FROM USER_TAB_PARTITIONS T
WHERE T.TABLE_NAME = K.TABLE_NAME
ORDER BY PARTITION_NAME DESC)
WHERE ROWNUM = 1) LOOP
```
FOR I IN W.YEAR_PARTITION_MAX .. V_PARTITION_END LOOP

  SELECT PARTITION_NAME, YEAR_PARTITION_MAX, TABLE_NAME
    INTO V_PARTITION_NAME, V_LOOPYEAR, V_PARTITION_TABLENAME
    FROM (SELECT T.PARTITION_NAME,
                 T.TABLE_NAME,
                 REGEXP_SUBSTR(T.PARTITION_NAME,
                               '[^_]+',
                               1,
                               V_PARTITION_LENGTH) YEAR_PARTITION_MAX    --目前表中最大分区
            FROM USER_TAB_PARTITIONS T
           WHERE T.TABLE_NAME = K.TABLE_NAME
           ORDER BY PARTITION_NAME DESC)
   WHERE ROWNUM = 1;  --取最大

  V_PARTITION_ENDYEAR            := V_LOOPYEAR + 2 || '-01-01';          --分区日期截至'YYYY-01-01'
  V_PARTITION_YEAR               := V_LOOPYEAR + 1;              
  V_PARTITION_NAME := V_PARTITION_TABLENAME || '_' || V_PARTITION_YEAR;  --分区名'DIM_SYS_TIME_YYYY'
```
/ALTER TABLE DIM_SYS_TIME ADD PARTITION
DIM_SYS_TIME_1993 VALUES LESS THAN(TO_DATE(‘1994-01-01’,‘YYYY-MM-DD’))/
V_SQL := ‘ALTER TABLE’ || ’ ’ || V_PARTITION_TABLENAME || ’ ’ || ‘ADD’ || ’ ’ ||
‘PARTITION’ || ’ ’ || V_PARTITION_NAME || ’ ’ ||
‘VALUES LESS THAN(TO_DATE(’ || ‘’‘’ || V_PARTITION_ENDYEAR || ‘’‘’ || ‘,’ ||
‘’‘YYYY-MM-DD’‘’ || ‘))’;
```
  DBMS_OUTPUT.PUT_LINE(V_SQL);
  
  EXECUTE IMMEDIATE V_SQL;
  
   INSERT INTO TABLE_PARTITION_STATE
       (TABLE_NAME, PARTITION_NAME, STATE) 
   SELECT V_PARTITION_TABLENAME,V_PARTITION_NAME,1 FROM DUAL;
   COMMIT;
    
END LOOP;
```
END LOOP;
END LOOP;

O_ERRCODE := 1;
O_ERRMSG := ‘添加分区成功!’;