procedure

DROP PROCEDURE IF EXISTS create_new_partitions;
DELIMITER $
CREATE PROCEDURE create_new_partitions(p_schema varchar(64), p_table varchar(64), p_YEARs_to_add int)
   LANGUAGE SQL
   NOT DETERMINISTIC
   SQL SECURITY INVOKER
BEGIN
   DECLARE done INT DEFAULT FALSE;
   DECLARE current_partition_name varchar(64);
   DECLARE current_partition_ts int;
   DECLARE cur1 CURSOR FOR
   SELECT partition_name
   FROM information_schema.partitions
   WHERE TABLE_SCHEMA = p_schema
   AND TABLE_NAME = p_table
   AND PARTITION_NAME != 'p_first'
   AND PARTITION_NAME != 'p_future'
   AND PARTITION_NAME = @partition_name_to_add;
   DECLARE cur2 CURSOR FOR
   SELECT partition_name, partition_range_ts
   FROM partitions_to_add;
   DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
   DROP TEMPORARY TABLE IF EXISTS partitions_to_add;
   CREATE TEMPORARY TABLE partitions_to_add (
      partition_name varchar(64),
      partition_range_ts int
   );
   SET @partitions_added = FALSE;
   SET @YEARs_ahead = 0;
   WHILE @YEARs_ahead <= p_YEARs_to_add DO
      SET @date = CURDATE();
      SET @q = 'SELECT DATE_ADD(?, INTERVAL ? YEAR) INTO @YEAR_to_add';
      PREPARE st FROM @q;
      EXECUTE st USING @date, @YEARs_ahead;
      DEALLOCATE PREPARE st;
      SET @YEARs_ahead = @YEARs_ahead + 1;
      SET @q = 'SELECT DATE_FORMAT(@YEAR_to_add, ''%Y'') INTO @formatted_YEAR_to_add';
      PREPARE st FROM @q;
      EXECUTE st;
      DEALLOCATE PREPARE st;
      SET @q = 'SELECT CONCAT(''p'', @formatted_YEAR_to_add) INTO @partition_name_to_add';
      PREPARE st FROM @q;
      EXECUTE st;
      DEALLOCATE PREPARE st;
      SET done = FALSE;
      SET @first = TRUE;
      OPEN cur1;
      read_loop: LOOP
         FETCH cur1 INTO current_partition_name;
         IF done AND @first THEN
            SELECT CONCAT('Creating partition: ', @partition_name_to_add);
            SET @q = 'SELECT DATE_FORMAT(@YEAR_to_add, ''%Y-01-01 00:00:00'') INTO @YEAR_to_add';
            PREPARE st FROM @q;
            EXECUTE st;
            DEALLOCATE PREPARE st;
            SET @q = 'SELECT DATE_ADD(?, INTERVAL 1 YEAR) INTO @partition_end_date';
            PREPARE st FROM @q;
            EXECUTE st USING @YEAR_to_add;
            DEALLOCATE PREPARE st;
            SELECT YEAR(@partition_end_date) INTO @partition_end_ts;
            INSERT INTO partitions_to_add VALUES (@partition_name_to_add, @partition_end_ts);
            SET @partitions_added = TRUE;
         END IF;
         IF ! @first THEN
            LEAVE read_loop;
         END IF;
         SET @first = FALSE;
      END LOOP;
     CLOSE cur1;
   END WHILE;
   IF @partitions_added THEN
      SET @schema = p_schema;
      SET @table = p_table;
      SET @q = 'SELECT CONCAT(''ALTER TABLE '', @schema, ''.'', @table, '' REORGANIZE PARTITION p_future INTO ( '') INTO @query';
      PREPARE st FROM @q;
      EXECUTE st;
      DEALLOCATE PREPARE st;
      SET done = FALSE;
      SET @first = TRUE;
      OPEN cur2;
      read_loop: LOOP
         FETCH cur2 INTO current_partition_name, current_partition_ts;
        IF done THEN
            LEAVE read_loop;
         END IF;
         IF ! @first THEN
            SET @q = 'SELECT CONCAT(@query, '', '') INTO @query';
            PREPARE st FROM @q;
            EXECUTE st;
            DEALLOCATE PREPARE st;
         END IF;
         SET @partition_name =  current_partition_name;
         SET @partition_ts =  current_partition_ts;
         SET @q = 'SELECT CONCAT(@query, ''PARTITION '', @partition_name, '' VALUES LESS THAN ('', @partition_ts, '')'') INTO @query';
         PREPARE st FROM @q;
         EXECUTE st;
         DEALLOCATE PREPARE st;
         SET @first = FALSE;
      END LOOP;
      CLOSE cur2;
      SET @q = 'SELECT CONCAT(@query, '', PARTITION p_future VALUES LESS THAN (MAXVALUE))'') INTO @query';
      PREPARE st FROM @q;
      EXECUTE st;
      DEALLOCATE PREPARE st;
      PREPARE st FROM @query;
      EXECUTE st;
      DEALLOCATE PREPARE st;
   END IF;
   DROP TEMPORARY TABLE partitions_to_add;
END$
DELIMITER ;