方式一、
-
drop
procedure
if
exists del_all_tb;
-
delimiter $$
-
create
procedure del_all_tb(db
char(
20))
-
begin
-
declare done
int
default
0;
-
declare tb
char(
100);
-
declare cur
cursor
for
select table_name
from infoRmation_schema.tables
where table_schema = db
and table_type =
"BASE TABLE";
-
declare continue
handler
for
not
found
set done =
1;
-
open cur;
-
-
repeat
-
fetch cur into tb;
-
set @
sql :=
concat(
"truncate ", tb,
";");
-
prepare stmt
from @
sql;
-
execute stmt;
-
deallocate
prepare stmt;
-
until done
end
repeat;
-
close cur;
-
end $$
-
delimiter ;
-
call del_all_tb(
"atdps");
-
drop
procedure
if
exists del_all_tb;
方式二、
-
#如果存在del_all_tb存储过程则删除del_all_tb存储过程
-
drop
procedure
if
exists del_all_tb;
-
#如果存在 tmpTable 临时表则删除 del_all_tb 临时表
-
DROP
TABLE
if
EXISTS tmpTable;
-
#创建 del_all_tb存储过程
-
create
procedure del_all_tb(db
char(
20))
-
begin
-
#申明变量
-
DECLARE tntmp
VARCHAR(
100);
-
#创建临时表
-
create
table tmpTable (tablename
VARCHAR(
100),flag
int);
-
#清空临时表
-
truncate
TABLE tmpTable;
-
#将需要清空的表插入到临时表
-
INSERT
INTO tmpTable(tablename , flag ) (
SELECT table_name ,
0
as a
FROM information_schema.tables
-
WHERE table_schema = db
and table_type=
'BASE TABLE');
-
-
#循环获取所有的表明以及删除状态
-
SELECT tablename
into tntmp
FROM tmpTable
WHERE flag =
0
limit
1;
-
WHILE tntmp <> ''
DO
-
-
#拼写删除语句
-
set @sqlText :=
concat(
"truncate ", tntmp,
";");
-
prepare stmt
from @sqlText;
-
#执行语句
-
execute stmt;
-
#释放删除语句
-
deallocate
prepare stmt;
-
#更新表状态
-
UPDATE tmpTable
SET flag=
1
WHERE tablename = tntmp;
-
#选择一下条语句
-
SELECT tablename
into tntmp
FROM tmpTable
WHERE flag =
0
limit
1;
-
END
WHILE;
-
end;
-
call del_all_tb(
"atdps");
-
-
#如果存在del_all_tb存储过程则删除del_all_tb存储过程
-
drop
procedure
if
exists del_all_tb;
-
#如果存在 tmpTable 临时表则删除 del_all_tb 临时表
-
DROP
TABLE
if
EXISTS tmpTable;
