一、创建存储过程
CREATE PROCEDURE proc_droptable_batch(in db varchar(50),-- 数据库
out rtn int, -- 0 成功 -1 失败
out msg varchar(100))
BEGIN
DECLARE done INT DEFAULT 0;#repeat结束标识
DECLARE dynamicSql VARCHAR(500);
DECLARE tableName VARCHAR(50);
DECLARE tableNameCursor CURSOR FOR select table_name from information_schema.tables where table_schema=db;#声明游标
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;-- #当循环列表完毕后将done设置为1
SET rtn = -1; -- 失败
SET msg = ''; -- 提示信息
-- 开启游标
OPEN tableNameCursor;
REPEAT-- 进入循环
FETCH tableNameCursor INTO tableName;-- 获取游标值
IF NOT done THEN-- 当done!=1继续循环
-- 动态sql
set dynamicSql = CONCAT('DROP TABLE IF EXISTS ',tableName);
set @executeSql = dynamicSql;
PREPARE prepareSql FROM @executeSql;
EXECUTE prepareSql;
DEALLOCATE PREPARE prepareSql;
END IF;
UNTIL done > 0 -- done=1时结束循环
END REPEAT;
CLOSE tableNameCursor;
SET rtn = 0; -- 返回状态
SET msg = '执行成功!'; -- 成功
END;
二、执行存储过程
call proc_droptable_batch('数据库名称',@rtn,@msg);
三、查看返回结果
select concat(@rtn,'#',@msg);
四、mybatis调用:
<select id="callProcDroptableBatch" parameterType="java.util.Map" statementType="CALLABLE">
{call proc_droptable_batch(#{db,mode=IN,jdbcType=VARCHAR},#{rtn,mode=OUT,jdbcType=INTEGER},#{msg,mode=OUT,jdbcType=VARCHAR})}
</select>