场景:时间分区表导入历史数据
考虑写for循环读日期数据赋值给变量做分区
--源数据表
create table table_lee_h(column_a varchar2(8),
column_b varchar2(8),
column_date varchar2(8))
--被导入数据历史表
create table table_lee_h(column_a varchar2(8),
column_b varchar2(8),
column_date varchar2(8))
partition by list (column_date)
(partition P_20191121 values('20191121'));
以下是处理过程
--以下为处理过程
is_exists int;
FOR mm IN (SELECT 'P_'||column_date part_id,column_date FROM table_lee) LOOP
SELECT COUNT(*) INTO is_exists
FROM user_tab_partition a
WHERE a.partition_name = mm.part_id
AND table_name = 'table_lee_h';
--如果分区存在则删除
IF is_exists >= 1 THEN
EXECUTE IMMEDIATE 'alter table table_lee_h drop partition'||mm.part_id;
END IF;
END LOOP;
FOR pp IN (SELECT 'P_'||column_date part_id,column_date FROM table_lee) LOOP
SELECT COUNT(*) INTO is_exists
FROM user_tab_partition a
WHERE a.partition_name = mm.part_id
AND table_name = 'table_lee_h';
--如果分区不存在则创建
IF is_exists = 0 THEN
EXECUTE IMMEDIATE 'alter table table_lee_h add partition'||pp.part_id VALUES ('''||pp.column_dat||''')';
END IF;
END LOOP;
通过for循环读取源数据的日期赋值给变量,通过变量动态创建分区;