环境:
MYSQL5.0.83;需求:
根据维护表(U_TABLES)中数据(isinit=1)删除其他表(TB1,TB2)数据;建表SQL如下:
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);我当前采用的写成存储过程调用,
过程SQL如下:
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//执行:
call Proc_ClearDB();发现提示:“SQL ERROR(1064)语法错误”
后来跟踪发现是tablename值为NULL,这是怎么回事呢?另外,我看手册说
prepare stmt from @strsql1;
execute stmt;
deallocate prepare stmt;
不能用在REPEAT 内,难道是这个问题?
MYSQL5.0.83;需求:
根据维护表(U_TABLES)中数据(isinit=1)删除其他表(TB1,TB2)数据;建表SQL如下:
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);我当前采用的写成存储过程调用,
过程SQL如下:
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//执行:
call Proc_ClearDB();发现提示:“SQL ERROR(1064)语法错误”
后来跟踪发现是tablename值为NULL,这是怎么回事呢?另外,我看手册说
prepare stmt from @strsql1;
execute stmt;
deallocate prepare stmt;
不能用在REPEAT 内,难道是这个问题?
解决方案 »
- mysql 的连结查询与行列置换问题
- mysql存储过程
- 求一个mysql trigger, 急!
- 请问PostgreSQL在window上怎么安装
- mysql 有沒有系統變量記錄發生錯誤時的錯誤號及錯誤描述?
- 一个sql问题
- mysql中先判断table存在再删除的语句如何写,即if exists如何使用
- 如何赋予对information_schema的所有表有写的权限
- mysql 添加列的sql
- 不能从ecshop的goods库中导入数据到自己新建的库的表中
- 请教MSSQL的子查询语句在MYSQL中怎么改写?
- 什么地方可以找到linux下的mysql4.1版的service和client的rpm包
tablename 重名了