Mysql 存储过程。这个有什么问题吗？

Question

这是存储过程。我想做的是循环遍历所有数据库并查找特定表中的行数。如果count为0，则打印表格。

  DELIMITER $$
  CREATE PROCEDURE checkTableData()
  BEGIN
     DECLARE done INT DEFAULT 0;
     DECLARE db VARCHAR(255);
     DECLARE appDBs CURSOR FOR SELECT schema_name FROM information_schema.schemata WHERE     schema_name LIKE 'db_%';
     DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
     DECLARE intCount INT default;

     OPEN appDBs;
     REPEAT
        FETCH appDBs INTO db;
          IF NOT done THEN
              SET @SQL = CONCAT('SELECT @intCount = COUNT(1) FROM ', db, '.SMS_CODES');
              EXECUTE @SQL;
              IF @intCount = 0 THEN
                 SELECT  db;
              END IF;    
         END IF;    
     UNTIL done END REPEAT;

     CLOSE appDBs;
  END $$
  delimiter ;

错误出现在行

  IF NOT done THEN

编辑: 尝试IF完成!= 0 THEN。错误信息:

You have an error in your SQL; Check the manual that corresponds to your MySQL server version for the right syntax to use near ;

     OPEN appDBs;
     REPEAT
        FETCH appDBs INTO db;
              `IF don' at line 7

编辑: 根据@Gerve，我更新了过程并将 intCount 声明移至顶部、光标上方，然后将其设置为 0。这样错误就消失了。现在另一个错误位于:

 You have an error in your SQL; Check the manual that corresponds to your MySQL server version     for the right syntax to use near '@SQL 
 @SQL = CONCAT('SELECT @intCount = COUNT(1) FROM ', db, '.ECR_SMS_COUNTRY_CODES');
            EXECUTE @SQL;
    END IF;         
 UNTIL done END REPEAT;
     CLOSE appDBs;
  EN' at line 15

Answer 1

第一次错误

DECLARE intCount INT default;

没有默认值，只需将其更改为:

DECLARE intCount INT default 0;

• MySQL 说错误是near，这意味着就在之前。

第二个错误

第二个错误是因为无法执行字符串而导致的，需要先准备一条语句:Prepare , Execute

PREPARE stmt1 FROM @SQL;
EXECUTE stmt1;