存储过程 - 一行选择语句语法错误 [英] Stored procedure - One Line select statement Syntax ERROR

问题描述

单行select语句在SP中抛出语法错误，但执行查询不会产生任何错误.

One line select statement throws syntax error in SP, but the query if executed doesn't produce any error.

如果我删除 IN _HOSTID 输入参数和 DECLARE 语句，错误会更改为 syntax error near -.很奇怪，我找不到错误的原因.

The error changes to syntax error near - if I remove IN _HOSTID input parameter and DECLARE statements. Its very weird and I can't find the cause of the error.

DELIMITER //

CREATE PROCEDURE getTotalCCU(IN _HOSTID INTEGER)
BEGIN
DECLARE HOSTID INT;
DECLARE TOTAL_ITEM INT;

SELECT  item_count  FROM items WHERE hostid = '12345' and key_ LIKE '%_net%' AND NOT key_  LIKE '%Total_%' AND NOT key_ LIKE '%{#%' AND STATUS = '0';

END //

DELIMITER ;

只有当我尝试导入它时，它才会在第 3 行抛出语法错误.

Only when I try to import it, it throws syntax error on line 3.

mysql -uroot -p DB01 < getTotalNET.sql

ERROR 1064 (42000) at line 3: 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 '-



END' at line 12

推荐答案

找出问题所在.

变量不应与查询中的任何列相同.

Variable should not be the same as any column in the query.

DECLARE HOSTID INT;

SELECT  item_count  FROM items WHERE hostid = '12345' and key_ LIKE '%_net%' AND NOT key_  LIKE '%Total_%' AND NOT key_ LIKE '%{#%' AND STATUS = '0';

由于查询有一个名为 hostid 的列，我们得到一个语法错误.

Since the query has a column named hostid we get a syntax error.

解决方案将 HOSTID 变量重命名为 _HOSTID.

Solution rename the HOSTID variable to _HOSTID.

DECLARE _HOSTID INT;

这篇关于存储过程 - 一行选择语句语法错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！