mysql创建函数语法错误

Question

DROP PROCEDURE IF EXISTS fn_get_entity_by_username;

DELIMITER $$
CREATE function fn_get_entity_by_username
(
    in in_username     varchar,
    out my_entity       integer
)
RETURNS integer DETERMINISTIC
language SQL
BEGIN
    declare my_entity integer(11);
    select entity
      into my_entity
      from tb_entity
     where username = in_username;

END $$

我一直有语法错误

ERROR 1064 (42000) at line 4: 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 'in in_username varchar, out my_entity integer ) RETURNS integer DE' at line 3'

Answer 1

你的问题是你正在混合 CREATE FUNCTION和 CREATE PROCEDURE语法。

如果您的意思是它是一个函数而不是过程，则会出现几个问题:

1. 您应该使用DROP FUNCTION而不是DROP PROCEDURE
2. MySQL 函数仅支持 IN 参数
3. 您应该使用RETURN子句返回函数的结果
4. 不需要局部变量 my_entity (顺便说一句，如果您出于某种原因想在过程中使用它，那么您应该更改其名称以消除是否引用 OUT 参数的歧义或局部变量)。您可以直接RETURN该值。

话虽这么说，你的代码可能看起来像这样

DROP FUNCTION IF EXISTS fn_get_entity_by_username;
CREATE FUNCTION fn_get_entity_by_username
(
    in_username VARCHAR(255)
)
RETURNS INT 
RETURN (SELECT entity
         FROM tb_entity
        WHERE username = in_username);

注意:由于它是一个单语句函数，因此无需更改 DELIMITER 并使用 BEGIN...END block

使用示例:

SELECT fn_get_entity_by_username('user2') my_entity;

示例输出:

| MY_ENTITY ||-----------||         1 |

---

If on the other hand you meant it to be a stored procedure then your code might look like this

DROP PROCEDURE IF EXISTS sp_get_entity_by_username;
CREATE PROCEDURE sp_get_entity_by_username
(
    IN  in_username VARCHAR(255),
    OUT my_entity   INT
)
  SET my_entity = (SELECT entity
                     FROM tb_entity
                    WHERE username = in_username);

使用示例:

CALL sp_get_entity_by_username('user1', @my_entity);
SELECT @my_entity my_entity;

示例输出:

| MY_ENTITY ||-----------||         1 |

Here is SQLFiddle demo (both for a function and a procedure)

---

Based on your comments code with BEGIN...END block for a function

DROP FUNCTION IF EXISTS fn_get_entity_by_username;
DELIMITER $$
CREATE FUNCTION fn_get_entity_by_username
(
    in_username VARCHAR(255)
)
RETURNS INT 
BEGIN
  RETURN (SELECT entity
            FROM tb_entity
           WHERE username = in_username);
END$$
DELIMITER ;

办理手续

DROP PROCEDURE IF EXISTS sp_get_entity_by_username;
DELIMITER $$
CREATE PROCEDURE sp_get_entity_by_username
(
    IN  in_username     VARCHAR(255),
    OUT my_entity       INT
)
BEGIN
  SET my_entity = (SELECT entity
                     FROM tb_entity
                    WHERE username = in_username);
END$$
DELIMITER ;