晴风

场景:热数据表中数据量越来越大,接口查询的效率慢,现在新建了一张历史表HIS,存储三十天之前的历史数据.已把历史数据插到了HIS表,现要把源表的历史数据清空.源表为分区表,之前历史的分区比较多,要一个一个手动删除比较麻烦,所以希望建一个存储过程来删分区

先把需要删的分区名查出来,暂时指定一个分区来测试

这里建的分区都是P20220710,P20220711,P20220712...这种格命名格式的

SELECT PARTITION_NAME FROM DBA_TAB_PARTITIONS
WHERE TABLE_NAME = UPPER('test_20220714_pt') AND LENGTH(PARTITION_NAME)=9 AND SUBSTRING(PARTITION_NAME,2)='20220603'
ORDER BY SUBSTRING(PARTITION_NAME,2) DESC

在存储过程中使用游标来删除分区

-- TABLE_NAME:表名
-- START_DATE：起始日期，小于等于该日期的分区会被删除
CREATE OR REPLACE PROCEDURE CLEAR_CD_PT(TABLE_NAME IN VARCHAR, START_DATE DATE)
AS 
  SQL VARCHAR(200);
  CURSOR MYCUR IS (
   SELECT PARTITION_NAME FROM DBA_TAB_PARTITIONS
   WHERE TABLE_NAME = UPPER(TABLE_NAME) AND LENGTH(PARTITION_NAME)=9 AND SUBSTRING(PARTITION_NAME,2)=TO_CHAR(START_DATE,'YYYYMMDD')
   ORDER BY SUBSTRING(PARTITION_NAME,2) DESC
  );
BEGIN
    FOR VARA IN MYCUR
    LOOP
      SQL := 'ALTER TABLE ' || TABLE_NAME || ' DROP  PARTITION '|| VARA.PARTITION_NAME;
      EXECUTE IMMEDIATE SQL; 
    END LOOP;
END;
-- 表名,起始日期
DM_ODS.CLEAR_CD_PT('test_20220714_pt',TO_DATE('20220603'));

以上SQL报错: The Partition not exist ,经过确认,之前分区确实存在,且执行过后虽报错但分区确实被删除了

让人莫名其妙......

分析结果,怀疑Alter语句被执行了多次,经过多次调试排错,发现是游标中where语句内的表名过滤条件不生效,导致游标返回多行结果,所以在循环内第一次删分区成功了,后面再删理所当然失败.

在网上查了下,发现确实有人也遇到过,存储过程中where语句不生效的,原因是存储过程的参数名与SQL语句中表字段名相同,名称不区分大小写.

修正这个错误(TABLE_NAME改为T_NAME),果然能正常调用了!再把where语句中的日期过滤改为 <= ,没有问题，达到了预期的效果

再添加一下参数与说明

-- ========================================
-- Author name: 晴风
-- Create date: 2022-07-14 14:29:58
-- 清空或删除表分区,起始日期为时间节点
-- OPERATION:DROP或TRUNCATE
-- T_OWNER:表空间
-- T_NAME:表名,不能带前缀,请到对应表空间下执行
-- START_DATE:起始日期，小于等于该日期的分区会被删除
-- ========================================


-- DROP PROCEDURE CLEAR_CD_PT;
CREATE OR REPLACE PROCEDURE CLEAR_CD_PT(OPERATION IN VARCHAR,T_OWNER IN VARCHAR,T_NAME IN VARCHAR, START_DATE DATE)
AS 
  SQL VARCHAR(200);
  CURSOR MYCUR IS (
   SELECT PARTITION_NAME FROM DBA_TAB_PARTITIONS
   WHERE TABLE_NAME = UPPER(T_NAME) AND LENGTH(PARTITION_NAME)=9 AND SUBSTRING(PARTITION_NAME,2)<=TO_CHAR(START_DATE,'YYYYMMDD')
   ORDER BY SUBSTRING(PARTITION_NAME,2) DESC
  );
BEGIN
    FOR VARA IN MYCUR
    LOOP
      SQL := 'ALTER TABLE ' || T_OWNER || '.' || T_NAME || ' ' || OPERATION || ' PARTITION '|| VARA.PARTITION_NAME;
      EXECUTE IMMEDIATE SQL; 
    END LOOP;
END;
-- operation操作,表空间,表名,起始日期
DM_ODS.CLEAR_CD_PT('DROP','DM_ODS','test_20220714_pt',TO_DATE('20220629'));