mysql - 从 UDF 返回一个值。 (MySQL)

Question

尝试创建以下函数时，总是提示“不允许从函数返回结果集”……谷歌对我毫无爱意。也没有阅读 MySQL 文档/示例。

DELIMITER $$
CREATE
    FUNCTION `sparks`.`ChildCheck`(ParentType VARCHAR(2), ParentID VARCHAR(4)) RETURNS INTEGER
    BEGIN
DECLARE ParentString VARCHAR(8);
DECLARE Kids INTEGER;
SET ParentString=CONCAT(ParentType, ':',ParentID);
SET Kids = 0;
SELECT Kids=Kids+1 AS KidCount
FROM (SELECT parent FROM mod_CMS WHERE dept_id=1 AND parent=ParentString
    UNION
    SELECT parent FROM mod_External_Links WHERE dept_id=1 AND parent=ParentString
    UNION
    SELECT parent FROM dept_mods WHERE dept_id=1 AND parent=ParentString) t1;
RETURN (KidCount);
END$$
DELIMITER ;

那行不通。

以下确实有效。

DELIMITER $$
DROP FUNCTION IF EXISTS `next_april1`$$
CREATE FUNCTION `next_april1`(inDate DATETIME) RETURNS DATE
DETERMINISTIC
BEGIN
  DECLARE outDate DATE;
  SET outDate = (SELECT CASE
              WHEN MONTH(inDate) < 4 THEN CONCAT(YEAR(inDate), '-04-01')
              ELSE CONCAT(YEAR(inDate) + 1, '-04-01')
              END);
RETURN (outDate);
END$$
DELIMITER;

工作语法是:

select next_april1(NOW());

它返回

'2012-04-01'

我正在寻找第一个像这样工作的人:

select KidCount('C','3')

输出: 4

我一定是在这两个函数之间遗漏了一些东西。

Answer 1

根据 Create Procedure 的文档:

Statements that return a result set can be used within a stored procedure but not within a stored function. This prohibition includes SELECT statements that do not have an INTO var_list clause and other statements such as SHOW, EXPLAIN, and CHECK TABLE.