远程调用MySql函数失败

Question

当我尝试使用远程连接调用用户定义的函数时，它给出了错误。

问题是，没有任何函数的查询可以很好地用于远程连接。

那么，我如何远程调用函数呢？

查询：select jobcardid,sonno,sonnumber,getSalesOrderCountByStatus('completed',1)as finished,getSalesOrderCountByStatus('pending',sonnumber)as inprocess,getSalesOrderCountByStatus('',1)as total from tblm_jobcard where sonnumber like 'A121';

功能：

CREATE DEFINER=root@localhost FUNCTION getSalesOrderCountByStatus(v_status varchar(12), v_salesorderid integer) RETURNS int(11)
    READS SQL DATA
BEGIN
DECLARE cnt integer(10);
if(length(v_status)>0) then 
select count(1) into cnt from tblm_jobcard where sonno = v_salesorderid and status = v_status;
else
select count(1) into cnt from tblm_jobcard where sonno = v_salesorderid ;
end if;
RETURN cnt;

在远程连接上，没有得到任何东西就挂断了。

Answer 1

试一试

DELIMITER $$

CREATE FUNCTION getSalesOrderCountByStatus(v_status VARCHAR(12), v_salesorderid INTEGER) RETURNS INT(11)
BEGIN
DECLARE cnt INTEGER(10);
IF(LENGTH(v_status)>0) THEN 
SELECT COUNT(1) INTO cnt FROM tblm_jobcard WHERE sonno = v_salesorderid AND STATUS = v_status;
ELSE
SELECT COUNT(1) INTO cnt FROM tblm_jobcard WHERE sonno = v_salesorderid ;
END IF;
RETURN cnt;
END$$
DELIMITER ;