MariaDB/MySQL 中的 CREATE PROCEDURE 出现神秘错误
我尝试在 MariaDB 10.2 中创建一个简单的过程,但遇到了关于变量定义的问题.
I tried to make a simple procedure in MariaDB 10.2 but I encountered an issue regarding variables defining.
我收到 (conn:107) 您的 SQL 语法有错误;检查与您的 MariaDB 服务器版本相对应的手册,了解在我声明变量时在第 3 行消息附近使用的正确语法.
I am receiving (conn:107) 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 '' at line 3 message when I declare a variable.
我阅读了 MariaDB 文档,它说变量是这样定义的 DECLARE var_name [, var_name] ... type [DEFAULT value]
I read the MariaDB documentation and I it says that a variable is defined like this DECLARE var_name [, var_name] ... type [DEFAULT value]
我哪里错了?我来自 Oracle SQL,并且为我连接了一些 sintax.
Where I am wrong? I am coming from Oracle SQL and some sintax is wired for me.
我使用 Eclipse 和 MariaDB JDBC 来连接 SQL.
I use Eclipse with MariaDB JDBC to connect on SQL.
CREATE PROCEDURE nom_jobs_insert(IN p_name varchar(100) CHARACTER SET 'utf8')
BEGIN
DECLARE counter INT DEFAULT 0;
SELECT count(*) INTO counter
FROM nom_jobs
WHERE lower(name) = lower(p_name)
IF counter = 1 THEN
INSERT INTO nom_jobs(name) VALUES (p_name);
END IF;
END;
推荐答案
我找到了解决方案.
在 MariaDB 中,您必须在创建过程之前定义一个 分隔符,并且您需要标记过程代码的完成位置.
In MariaDB you have to define a delimiter before create a procedure and you need to mark where the procedure code is finished.
DELIMITER //
CREATE PROCEDURE nom_jobs_insert(IN p_name varchar(100) CHARACTER SET 'utf8')
BEGIN
DECLARE counter INT DEFAULT 0;
SELECT count(*) INTO counter
FROM nom_jobs
WHERE lower(name) = lower(p_name);
IF counter = 1 THEN
INSERT INTO nom_jobs(name) VALUES (p_name);
END IF;
END; //
相关文章