在没有参数的情况下调用存储过程时，Mysql 错误 #1064 接近 Null

Question

我对 MySQL 还很陌生。我必须使用输出参数调用存储过程。我在互联网上搜索了很多，但没有找到解决我的问题的正确方法。如果我使用 @outputParamName 调用存储过程，它会说我在 NULL 附近有错误 #1064。如果我使用不带 @ 的“outputParamName”调用该过程，则表示它不是 OUT 或 INOUT 正确的参数。有人可以帮助我吗？ 存储过程只需检查数据库中的姓氏和姓名是否存在于同一行:

CREATE PROCEDURE InsertProc (INOUT existsInDb BOOLEAN, 
                         IN dbName VARCHAR(50)
                         IN tableName VARCHAR(50)
                         IN surnameNew VARCHAR(50)
                         IN nameNew VARCHAR(50))
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
BEGIN
 DECLARE rowSurnameName int;
 SET @sqlSel = CONCAT('SELECT COUNT(*) INTO ', rowSurnameName, ' FROM ', dbName, '.', tableName, ' WHERE COGNOME=', surnameNew, ' AND NOME=', nameNew); 
 PREPARE stmtSel FROM @sqlSel; 
 EXECUTE stmtSel; 
 DEALLOCATE PREPARE stmtSel; 
 IF (rowSurnameName=0) THEN 
   SET @sqlIns = CONCAT('INSERT INTO ', dbName, '.', tableName, ' (NOME, COGNOME) VALUES (', nameNew, ', ', surnameNew,')'); 
   PREPARE stmtIns FROM @sqlIns; 
   EXECUTE stmtIns; 
   DEALLOCATE PREPARE stmtIns; 
   SELECT false INTO existsInDb; 
 ELSE SELECT true INTO existsInDb; 
 END IF; 
END

CALL 语句是:

SET @dbName = 'DBNAME';
SET @tableName = 'DBTABLE';
SET @surname = 'SURNAME';
SET @name = 'NAME';

PREPARE s FROM 'CALL InsertProc(?,?,?,?,?)';
EXECUTE s USING @existsInDB, @dbName, @tableName, @surname, @name;
SELECT @existsInDB;

错误行是:

#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 'NULL' at line 1 

Answer 1

一些注意事项:

• 不能在准备好的语句中使用局部变量。

  C.1 Restrictions on Stored Programs

  ...

  SELECT ... INTO local_var cannot be used as a prepared statement.

  ...

  出现问题中显示的错误是因为局部变量rowSurnameName的值为NULL，请参阅:

  mysql> DROP PROCEDURE IF EXISTS `InsertProc`;
  Query OK, 0 rows affected, 1 warning (0.00 sec)
  
  mysql> DELIMITER //
  
  mysql> CREATE PROCEDURE `InsertProc`()
      -> BEGIN
      ->   DECLARE `rowSurnameName` INT;
      ->   SELECT `rowSurnameName`;
      ->   SET @`sqlSel` := CONCAT('SELECT COUNT(*) INTO ',  `rowSurnameName`);
      ->   SELECT @`sqlSel`;
      -> END//
  Query OK, 0 rows affected (0.00 sec)
  
  mysql> DELIMITER ;
  
  mysql> CALL `InsertProc`;
  +------------------+
  | `rowSurnameName` |
  +------------------+
  |             NULL |
  +------------------+
  1 row in set (0.00 sec)
  
  +-----------+
  | @`sqlSel` |
  +-----------+
  | NULL      |
  +-----------+
  1 row in set (0.00 sec)
  
  Query OK, 0 rows affected (0.00 sec)
  

  如果您尝试在准备好的语句中使用rowSurnameName局部变量，您将收到错误:

  mysql> DROP PROCEDURE IF EXISTS `InsertProc`;
  Query OK, 0 rows affected (0.00 sec)
  
  mysql> DELIMITER //
  
  mysql> CREATE PROCEDURE `InsertProc`()
      -> BEGIN
      ->   DECLARE `rowSurnameName` INT;
      ->   SET @`sqlSel` := CONCAT('SELECT 100 INTO `rowSurnameName`');
      ->   SELECT @`sqlSel`;
      ->   PREPARE `stmtSel` FROM @`sqlSel`;
      ->   EXECUTE `stmtSel`;
      ->   DEALLOCATE PREPARE `stmtSel`;
      -> END//
  Query OK, 0 rows affected (0.00 sec)
  
  mysql> DELIMITER ;
  
  mysql> CALL `InsertProc`;
  +----------------------------------+
  | @`sqlSel`                        |
  +----------------------------------+
  | SELECT 100 INTO `rowSurnameName` |
  +----------------------------------+
  1 row in set (0.00 sec)
  
  ERROR 1327 (42000): Undeclared variable: rowSurnameName
  

• 您需要使用9.4 User-Defined Variables在你准备好的声明中:

  mysql> DROP PROCEDURE IF EXISTS `InsertProc`;
  Query OK, 0 rows affected (0.00 sec)
  
  mysql> DELIMITER //
  
  mysql> CREATE PROCEDURE `InsertProc`()
      -> BEGIN
      ->   SET @`sqlSel` := CONCAT('SELECT 100 INTO @`rowSurnameName`');
      ->   SELECT @`sqlSel`;
      ->   PREPARE `stmtSel` FROM @`sqlSel`;
      ->   EXECUTE `stmtSel`;
      ->   DEALLOCATE PREPARE `stmtSel`;
      ->   IF (@`rowSurnameName` = 0) THEN
      ->     SELECT 'NotExistsInDbAndInsert';
      ->   ELSE
      ->     SELECT 'existsInDb';
      ->   END IF;
      -> END//
  Query OK, 0 rows affected (0.00 sec)
  
  mysql> DELIMITER ;
  
  mysql> CALL `InsertProc`;
  +-----------------------------------+
  | @`sqlSel`                         |
  +-----------------------------------+
  | SELECT 100 INTO @`rowSurnameName` |
  +-----------------------------------+
  1 row in set (0.00 sec)
  
  +------------+
  | existsInDb |
  +------------+
  | existsInDb |
  +------------+
  1 row in set (0.00 sec)
  
  Query OK, 0 rows affected (0.00 sec)