Mysql游标+执行动态SQL

功能描述，找出指定条件的表（游标定义），

拼接成一个 drop table XXX 的动态SQL语句，

执行生成动态SQL。

CREATE DEFINER=`root`@`%` PROCEDURE `CleanDb`()
BEGIN
	DECLARE nodata int DEFAULT 0;
	DECLARE tnm VARCHAR(128);
	declare cnt int default 0;
	declare sql_str VARCHAR(2000);
	##
	declare cur_tbls CURSOR FOR SELECT DISTINCT table_name
		from information_schema.`TABLES` WHERE TABLE_schema = 'db_dl' ORDER BY table_name;
	DECLARE CONTINUE handler for not found set nodata = 1; 
	##
	open cur_tbls;
	##
	ll:loop
		fetch cur_tbls INTO tnm;
		if nodata = 1 then
			leave ll;
		end if;
		##
		set sql_str = '';
		if tnm not like 't_%' && tnm <> 'comp' then
			#if cnt = 10 then
				set sql_str = CONCAT('drop table ',tnm,';');
				set @sql = sql_str;
				prepare s1 from @sql;
				EXECUTE s1;
				DEALLOCATE PREPARE s1;
				##select sql_str;
			#end if;
			##
			set cnt = cnt + 1;
		end if;
	end loop;
	commit;
	select CONCAT('完了',cnt);
	CLOSE cur_tbls;
END