#mysql mysql存储过程 表名作为变量的问题

Question

大概意思是想要从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

Answer 1

根据你提供的代码，报错信息显示在执行IF EXISTS语句时出现了语法错误。具体来说，应该将IF EXISTS的括号内的EXECUTE STMT语句放在括号外面，如下所示：

IF EXISTS(SELECT 1 FROM information_schema.tables WHERE table_name = vtable) THEN
    set @sqlcmd = concat("select (1) from ",vtable," where ahdm = vahdm");
    prepare stmt from @sqlcmd;
    execute stmt;
ELSE
    select concat(vtable,' is clean');
    deallocate prepare stmt;
END IF;

注意到我还调整了IF EXISTS语句内部的查询方式，这里通过查询信息schema的tables表来判断表是否存在。这种方法可以避免使用动态查询语句的副作用，也更加简洁和高效。希望能够帮助到你，祝好运！