存储过程 MySQL 语法错误

Question

存储过程 MySQL 语法错误

3

#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 = -1 else SELECT audit_id INTO t_new_id FROM a_audit_reg at line 11



delimiter //
CREATE  FUNCTION get_audit_id (p_pubco_id int(10),
                              p_audit_id int(10),
                              p_fiscal_date date) 
       RETURNS int(10) 
BEGIN 

   DECLARE t_new_id int(10);

#check paremeters here
if p_pubco_id = 0 or p_audit_id = 0
then set t_new_id = -1
else
   set t_new_id = (SELECT audit_id
     FROM a_audit_reg 
    WHERE p_pubco_id = a_audit_reg.pubco_id
      and p_audit_id = a_audit_reg.audit_id
      and p_fiscal_period = a_audit_reg.fiscal_period_date);

   if found_rows() = 0
   then
        insert into a_audit_reg (pubco_id, audit_id, fiscal_period_date)
               values (p_pubco_id, p_audit_id, p_fiscal_date);
        set t_new_id = last_insert_id();
   end if;
end if;
   return t_new_id;

END //
delimiter ;

- Ben

我已经修复了分隔符，但现在我收到了这个错误，请看完整的错误信息如下。

＃1064 - 您的SQL语法出错；请检查您的MySQL服务器版本对应的手册以获取正确的语法，在第11行附近使用“=  -1 else SELECT audit_id INTO t_new_id FROM a_audit_reg

。 - Ben

你需要使用 SET，请参考下面。 - John Woo

非常感谢，我已经更新了上面的代码以展示我所使用的内容，正如您所看到的，我使用了SET。问题在于-1后没有分号。再次感谢您的帮助，伙计！ - Ben

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- John Woo · Accepted Answer

更改DELIMITER
使用SET

查询，

DELIMITER //
CREATE  FUNCTION get_audit_id
(
    p_pubco_id INT, 
    p_audit_id INT, 
    p_fiscal_date DATE
) 
RETURNS INT
BEGIN 
    DECLARE t_new_id INT;

    IF p_pubco_id = 0 or p_audit_id = 0 THEN    
        SET t_new_id = -1;
    ELSE
        SET t_new_id = (SELECT audit_id
                        FROM a_audit_reg 
                        WHERE p_pubco_id = a_audit_reg.pubco_id
                            and p_audit_id = a_audit_reg.audit_id
                            and p_fiscal_period = a_audit_reg.fiscal_period_date);

        IF found_rows() = 0 then
            insert into a_audit_reg (pubco_id, audit_id, fiscal_period_date)
            values (p_pubco_id, p_audit_id, p_fiscal_date);

            SET t_new_id = last_insert_id();
        end if;
    end if;
    return t_new_id;
END //
DELIMITER ;