MariaDB/MySQL 中的 CREATE PROCEDURE 出现神秘错误

2022-01-15 00:00:00 mariadb mysql stored-procedures

我尝试在 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; //

相关文章