56,677
社区成员
发帖
与我相关
我的任务
分享
DROP TABLE IF EXISTS TB1;
DROP TABLE IF EXISTS TB2;
CREATE TABLE TB1(ID INT,INTIME DATETIME ,TXT VARCHAR(255));
INSERT INTO TB1 VALUES(1,'2011-04-01 00:00:00','TEST1');
INSERT INTO TB1 VALUES(1,'2011-04-02 00:00:00','TEST2');
INSERT INTO TB1 VALUES(1,'2011-04-03 00:00:00','TEST3');
INSERT INTO TB1 VALUES(1,'2011-04-04 00:00:00','TEST4');
INSERT INTO TB1 VALUES(1,'2011-04-05 00:00:00','TEST5');
INSERT INTO TB1 VALUES(1,'2011-04-06 00:00:00','TEST6');
CREATE TABLE TB2(ID INT,INTIME DATETIME ,TXT VARCHAR(255));
INSERT INTO TB2 VALUES(2,'2011-04-01 00:00:00','TEST1');
INSERT INTO TB2 VALUES(2,'2011-04-02 00:00:00','TEST2');
INSERT INTO TB2 VALUES(2,'2011-04-03 00:00:00','TEST3');
INSERT INTO TB2 VALUES(2,'2011-04-04 00:00:00','TEST4');
INSERT INTO TB2 VALUES(2,'2011-04-05 00:00:00','TEST5');
INSERT INTO TB2 VALUES(2,'2011-04-06 00:00:00','TEST6');
CREATE TABLE U_Tables(id int,tablename VARCHAR(255) ,isinit INT);
INSERT INTO TB2 VALUES(1,'TB1',1);
INSERT INTO TB2 VALUES(2,'TB2',1);
DROP PROCEDURE IF EXISTS Proc_ClearDB;
delimiter //
CREATE PROCEDURE Proc_ClearDB()
BEGIN
DECLARE tablename varchar(32);
DECLARE strsql varchar(256);
DECLARE done int default 0;
DECLARE cur1 cursor FOR select tablename from U_Tables where isinit = 1 order by id;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
open cur1;
REPEAT
FETCH cur1 INTO tablename;
IF NOT done THEN
BEGIN
set strsql = CONCAT('delete from ', tablename );
#set @strsql1 = strsql;
#select @strsql1,strsql,tablename;
prepare stmt from @strsql1;
execute stmt;
deallocate prepare stmt;
END;
END IF;
UNTIL done END REPEAT;
close cur1;
END//