delimiter //

drop procedure if exists operate_tables //

create procedure operate_tables (in db_name varchar(100), in operate varchar(100))

begin

	declare finish int default 0;

	declare tab_name varchar(100);

	-- 把select 出来的数据放到游标中

	declare cur_tables cursor for select table_name from information_schema.tables

	where table_schema = db_name and table_type = 'base table';

	-- 若没有数据返回,程序继续,并将变量finish设为1

	declare continue handler for not found set finish = 1;

	open cur_tables;

	exce_loop:loop

		if finish = 1 then

			leave exce_loop;

		end if;

		fetch cur_tables into tab_name;

		set @str = concat(operate , ' ', db_name, '.', tab_name);

		prepare stmt from @str; -- 预定义sql

		execute stmt; -- (如果sql有参数的话, USING xxx,xxx); 这里USING的只能是会话变量

		deallocate prepare stmt; -- 释放连接

	end loop;

	close cur_tables;

end; //

delimiter ;

-- call operate_tables('test', 'truncate');

-- call operate_tables('test', 'drop table');