利用存储过程删除库里表

因为 RDS 做了权限隔离，不能直接删除库，但又得删除库里的所有表，而表又无限多，为了一劳永逸，就写了个存储过程来解决。

drop PROCEDURE if exists delete_table;
delimiter //
create PROCEDURE delete_table(t_name VARCHAR(64))
top: begin
declare SQL_FOR_DELETE VARCHAR(500);
if  t_name is NULL OR  t_name = "undelete_table" then
  LEAVE top;
end IF;
SET SQL_FOR_DELETE = CONCAT("DROP TABLE ", t_name);
SET @sql = SQL_FOR_DELETE;
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END

DROP  PROCEDURE IF EXISTS delete_all_table;
DELIMITER //
CREATE PROCEDURE delete_all_table()
BEGIN
  DECLARE t_name VARCHAR(64);
  DECLARE `stop` INT DEFAULT 0;
  DECLARE table_cursor SELECT  TABLE_NAME FROM  `information_schema`.`TABLES` WHERE `TABLE_SCHEMA` = 'database_name';
  DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET `stop` = 1;
  OPEN table_cursor;
  FETCH table_cursor INTO t_name;
  WHILE (`stop` <> 1) DO 
      call delete_table(t_name);
        FETCH table_cursor INTO t_name;
  END WHILE;
  CLOSE table_cursor;
END