mysql - 尝试编译函数时出现语法错误

Question

我正在尝试编译以下函数，但遇到很多语法错误。我犯了什么错误？我错过了什么吗？我们可以在返回类型中给出字符的长度吗？

create function generate_invoice_number(id int) returns varchar(11)
    deterministic 
begin
    declare invoiceId varchar(11) default null;
    /**
         * Here id refers to the ID that is generated in the 
     * invoice table.
     */
    if id is not null and id > 0 
        then
            set invoiceId = concat('QUA-',lpad(id,7,'0'));
    end if;

    return invoiceId;
end;

错误:

Error Code : 1064 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 '' at line 4 (0 ms taken)

Error Code : 1064 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 'if id is not null and id > 0 then set invoiceId = concat('QUA-',lpad(id,7,' at line 5 (0 ms taken)

Error Code : 1064 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 'end if' at line 1 (0 ms taken)

Error Code : 1064 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 'return invoiceId' at line 1 (0 ms taken)

Error Code : 1064 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 'end' at line 1 (0 ms taken)

Answer 1

你必须为此定义一个分隔符:

delimiter $$

create function generate_invoice_number(id int) returns varchar(11)
    deterministic 
begin
    declare invoiceId varchar(11) default null;
    /**
         * Here id refers to the ID that is generated in the 
     * invoice table.
     */
    if id is not null and id > 0 
        then
            set invoiceId = concat('QUA-',lpad(id,7,'0'));
    end if;

    return invoiceId;
end$$

delimiter ;