时间戳

首页 > 解决方案 > 我的mysql存储过程有什么问题？

mysql - 我的mysql存储过程有什么问题？

问题描述

我有一个存储过程来检查表中是否已经存在新条目。如果存在，则不会发生插入。当我运行它时，出现错误

DROP PROCEDURE IF EXISTS AddPriority2;
DELIMITER $$

CREATE PROCEDURE AddPriority2
(
    IN strName VARCHAR(100),
    OUT itExists INT
)
BEGIN
DECLARE 
SELECT COUNT(Id) INTO itExists
FROM priorities
WHERE Name = strName AND StatId = 1;

IF(itExists = 0) THEN
INSERT INTO priorities
(
    NAME,
    StatId
)
VALUES
(
    strName,
    1
);
END IF;
END

这是错误

Query: CREATE PROCEDURE AddPriority2 ( IN strName VARCHAR(100), OUT itExists INT ) BEGIN DECLARE SELECT COUNT(Id) INTO itExists FROM pr...

Error Code: 1064
You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'SELECT COUNT(Id) INTO itExists
FROM priorities
WHERE Name = strName AND StatId =' at line 8

标签： mysqlif-statementstored-procedures

解决方案

1）你不能声明一个选择语句 - 一个声明必须是一个变量..（我不会为此使用输出参数）2）或者你可以使用exists代替

if not exists (select 1 from priorities WHERE Name = strName AND StatId = 1) then
   insert...
end if;

推荐阅读