创建过程错误

Question

CREATE PROCEDURE search
@id INT

AS
BEGIN
SET NO COUNT ON;
SELECT  cteachID,
        cfname,
        cmname,
        clname
FROM tbl_teacher
WHERE cteachID = @id
END

MySQL说：文档

1064年的今天，您的SQL语法出现错误；查看与您的MariaDB服务器版本对应的手册，了解第2行“@id INT AS BEGIN SET NO COUNT ON”附近使用的正确语法

Answer 1

你的代码是为sql服务器工作的。对于mysql，存储过程的语法有一些不同

    DROP PROCEDURE IF EXISTS search;

    DELIMITER $$
    CREATE PROCEDURE search(IN id INT)
    BEGIN
        SELECT  cteachID,
                cfname,
                cmname,
                clname
                    FROM tbl_teacher
                    WHERE cteachID = id;
    END$$

    DELIMITER;

Answer 2

您可以在此处找到有关存储过程的MariaDB文档：https://mariadb.com/kb/en/library/create-procedure/

DELIMITER //

CREATE PROCEDURE search
(IN id INT)
BEGIN

    SELECT  cteachID,
            cfname,
            cmname,
            clname
    FROM tbl_teacher
    WHERE cteachID = id;

END;
//

DELIMITER ;

除此之外，您还需要使用delimiter关键字，因为您的创建过程使用开始/结束块。

• https://mariadb.com/kb/en/library/stored-procedure-overview/