mysql 存储过程 （ps:用法自己看 ：）

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');

转载于:https://www.cnblogs.com/manhelp/p/11431380.html