MYSQL存储过程中 表名 使用变量

DECLARE NEWNAME VARCHAR(50); 
SET NEWNAME =  CONCAT('TB_BACK_EAT_',DATE_FORMAT(NOW(),'%Y%m%d'));
SET @SQLSTR = CONCAT('ALTER TABLE `TB_BACK_EAT` RENAME TO ', NEWNAME);
PREPARE STMT FROM @SQLSTR;
EXECUTE STMT;
注:目前 存储过程 支持动态SQL,函数不支持

大概意思是想要从TS_SCFZ表的tabname字段来历遍所有表,从而查找是否有符合条件的记录 请大神帮帮忙 现在报错了 ``` drop procedure if exists deletecheck; delimiter // CREATE DEFINER = `root`@`%` PROCEDURE `deletecheck`(IN vahdm varchar(20)) BEGIN DECLARE done BOOLEAN DEFAULT FALSE; DECLARE vtable VARCHAR(24); DECLARE cur CURSOR FOR select tabname from TS_SCFZ where SFSC = 1; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; OPEN cur; read_loop: LOOP FETCH cur INTO vtable; IF done THEN LEAVE read_loop; END IF; START TRANSACTION; set @sqlcmd = concat("select (1) from ",vtable," where ahdm = vahdm"); prepare stmt from @sqlcmd; IF EXISTS(execute stmt) THEN select concat('table is ',vtable); ELSE select concat(vtable,' is clean'); deallocate prepare stmt; END IF; COMMIT; END LOOP read_loop; CLOSE cur; END ``` drop procedure if exists deletecheck; delimiter // CREATE DEFINER = `root`@`%` PROCEDURE `deletecheck`(IN vahdm varchar(20)) BEGIN DECLARE done BOOLEAN DEFAULT FALSE; DECLARE vtable VARCHAR(24); DECLARE cur CURSOR FOR select tabname from TS_SCFZ where SFSC = 1; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; OPEN cur; read_loop: LOOP FETCH cur INTO vtable; IF done THEN LEAVE read_loop; END IF; START TRANSACTION; set @sqlcmd = concat("select (1) from ",vtable," where ahdm = vahdm"); prepare stmt from @sqlcmd; IF EXISTS(execute stmt) THEN select concat('table is ',vtable); ELSE select concat(vtable,' is clean'); deallocate prepare stmt; END IF; COMMIT; END LOOP read_loop; CLOSE cur; END ``` [Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'execute stmt) THEN select concat('table is ',vtable); ELSE sele' at line 16 ```
©️2020 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页