DOC/3设计阶段/DB/02_function/getParentCat.sql

DELIMITER $$

DROP FUNCTION IF EXISTS `getParentCat`$$

CREATE FUNCTION `getParentCat`(catCode VARCHAR(10000)) RETURNS VARCHAR(10000) CHARSET utf8
BEGIN   
        DECLARE sParentList VARCHAR(10000);   
        DECLARE sParentTemp VARCHAR(10000);   
          
       SET sParentTemp =catCode;   
          
        WHILE sParentTemp IS NOT NULL DO   
           IF (sParentList IS NOT NULL) THEN   
               SET sParentList = CONCAT(sParentTemp,',',sParentList);   
           ELSE   
                SET sParentList = CONCAT(sParentTemp);   
            END IF;   
            SELECT GROUP_CONCAT(CAT_PARENT_CODE) INTO sParentTemp FROM HC_CATEGORY_INF WHERE FIND_IN_SET(CAT_CODE,sParentTemp)>0;   
        END WHILE;   
        RETURN sParentList;   
   END$$

DELIMITER ;