编写存储过程实现查询mysql数据库中的表名并删除表中的数据

编写存储过程如下：

 

-- 删除proc1存储过程
drop PROCEDURE proc_delete;
-- ------------------------------------------------------------
-- 查询数据库中的表名，根据查询到的表名，删除该表名中的数据
-- ------------------------------------------------------------
CREATE PROCEDURE proc_delete()
begin
declare done int;
declare table_gis varchar(100);
declare v_delete varchar(100);
declare v_temp varchar(100); -- 存放最终删除sql
-- 查询itsm1库中以'itsm_gis'开头的表名
declare cursor_table_gis cursor for SELECT DISTINCT table_name tableName from information_schema.columns where table_schema='itsm1' and table_name like 'itsm_gis_%' and table_name != 'itsm_gis_multimedia' and table_name !='itsm_gis_line';
declare continue handler for not found set done=1;
open cursor_table_gis;
set @v_delete='delete from ';
cursor_loop:loop
FETCH cursor_table_gis INTO table_gis;
if done=1 then
leave cursor_loop;
end if;
-- 连接字符串函数
set @v_temp=CONCAT_WS(' ',@v_delete,table_gis,';');
select @v_temp;
prepare sqlstr from @v_temp;
execute sqlstr;
deallocate prepare sqlstr;
end loop cursor_loop;
close cursor_table_gis;
commit;
-- 删除线路 itsm_gis_line表与其它gis数据存在主外键关系，所以最后删除
delete from itsm_gis_line;
commit;
--
end
-- 执行proc_delete存储过程
call proc_delete();