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 ;