批量删除分区表

单个分区表删除

 alter table tabName drop partition ""；

存储过程：

DELIMITER $$

DROP PROCEDURE IF EXISTS delete_partitions$$
CREATE PROCEDURE delete_partitions(IN tabName VARCHAR(500),IN YearMonth VARCHAR(20) )
BEGIN
declare parName VARCHAR(50);
DECLARE sql_oper_revcord VARCHAR(100);
declare curl CURSOR FOR SELECT partition_name FROM INFORMATION_SCHEMA.partitions  WHERE TABLE_SCHEMA = schema()  AND TABLE_NAME = tabName;
DECLARE EXIT HANDLER FOR NOT FOUND CLOSE curl;
-- select tabName as cc;
OPEN curl;
FETCH curl INTO parName;
WHILE ( parName is not null) DO
if locate(YearMonth,parName)>0  THEN
select parName;
 set @sql_oper_revcord=CONCAT("alter table ",tabName,' drop partition ',parName);
PREPARE stmt FROM @sql_oper_revcord;
EXECUTE stmt;
END IF; 
-- 游标向下走一步 
FETCH curl INTO parName;
END WHILE;
CLOSE curl; 

END $$

事件：

 call delete_partitions(tableName,datePar);