批量truncate table（小心测试，后果自负）

憋了一天终于憋出来了。

1.上午先测试了游标

declare
v_sql VARCHAR2(30);
CURSOR cur_test is select * from user_tables where table_name like '%2018%';
row_test cur_test%ROWTYPE;
BEGIN
OPEN cur_test;
FETCH cur_test into row_test;
WHILE cur_test%FOUND
LOOP
v_sql :='truncate table '||row_test.table_name;
dbms_output.put_line(v_sql);
execute immediate v_sql;
FETCH cur_test into row_test;
END LOOP;
close cur_test;
end;
/

可以打印并truncate表。

2.关于取表名的步骤搞了N久。表名主要有几种，按天、周、月三种。比如table_2020_01_01这是按天的，table_2020_1这是按周的，table_2020_01这是按月的。

3.取三天前的表名

select table_name from user_tables
where table_name like '%2020%'
and regexp_like (table_name,'^[[:alpha:]]')
and length(regexp_replace(table_name,'[^0-9]',''))=8
and regexp_replace(table_name,'[^0-9]','')<to_char(sysdate-3,'YYYYMMDD');

4.最后把查询的sql带入游标中，truncate三天前的表名语句如下：

declare
v_sql VARCHAR2(30);
CURSOR cur_test is select table_name from user_tables where table_name like '%2020%' and regexp_like (table_name,'^[[:alpha:]]') and length(regexp_replace(table_name,'[^0-9]',''))=8 and regexp_replace(table_name,'[^0-9]','')<to_char(sysdate-3,'YYYYMMDD');
row_test cur_test%ROWTYPE;
BEGIN
OPEN cur_test;
FETCH cur_test into row_test;
WHILE cur_test%FOUND
LOOP
v_sql :='truncate table '||row_test.table_name;
dbms_output.put_line(v_sql);
execute immediate v_sql;
FETCH cur_test into row_test;
END LOOP;
close cur_test;
end;
/

 

总结：

还是思路问题，开始想是用regexp_like去匹配字母开头，然后包含数字，还有下划线的，最后在判断长度取类似2020_06_04或2020_06这样的长度再判断位数，长度为10位的就是按年月日来命名的表。后来无意中发现regexp_replace把下划线替换掉和字母替换掉（非数字替换掉），简单多了。