mysql创建函数function_【MySQL】MySQL创建函数（FUNCTION）

MySQL创建函数(FUNCTION)

代码示例

代码示例

DROP FUNCTION IF EXISTS `getChildID`;

DELIMITER ;;

CREATE DEFINER=`seul`@`%` FUNCTION `getChildID`(areaId varchar(20)) RETURNS varchar(4000) CHARSET utf8

BEGIN

DECLARE sTemp VARCHAR(4000);

DECLARE sTempChd VARCHAR(4000);

SET sTemp='';

SET sTempChd = CAST(areaId AS CHAR);

WHILE sTempChd IS NOT NULL DO

IF sTemp!='' THEN

SET sTemp= CONCAT(sTemp,',',sTempChd);

ELSE

SET sTemp=sTempChd;

END IF;

SELECT GROUP_CONCAT(id) INTO sTempChd FROM z_area WHERE FIND_IN_SET(CAST(parent_id AS SIGNED),sTempChd)>0;

END WHILE;

RETURN sTemp;

END

;;

DELIMITER ;

-- ----------------------------

-- Function structure for getParentID

-- ----------------------------

DROP FUNCTION IF EXISTS `getParentID`;

DELIMITER ;;

CREATE DEFINER=`seul`@`%` FUNCTION `getParentID`(areaId varchar(20)) RETURNS varchar(4000) CHARSET utf8

BEGIN

DECLARE sTemp VARCHAR(4000);

DECLARE sTempChd VARCHAR(4000);

SET sTemp='';

SELECT group_concat(T2.id) INTO sTemp

FROM (

SELECT

@r AS _id,

(SELECT @r := parent_id FROM z_area WHERE id = _id) AS parent_id,

@l := @l + 1 AS lvl FROM (SELECT @r := areaId, @l := 0) vars,

z_area h WHERE @r <> 0 AND parent_id > 0

) T1 JOIN z_area T2 ON T1._id = T2.id ORDER BY T1.lvl DESC;

RETURN sTemp;

END

;;

DELIMITER ;