SYS_CONNECT_BY_PATH mysql ( tree 구조의 계층 표시 )

CREATE DEFINER=`prcsadmin`@`%` FUNCTION `menu_sys_connect_by_path`(

`rootId` VARCHAR (50),

`langCode` VARCHAR(3)

 

 

)

RETURNS varchar(1000) CHARSET utf8

LANGUAGE SQL

NOT DETERMINISTIC

CONTAINS SQL

SQL SECURITY DEFINER

COMMENT ''

BEGIN

DECLARE sParentList VARCHAR (1000);

DECLARE sParentTemp VARCHAR(1000); 

DECLARE sParentNameTemp VARCHAR(1000); 

 

SET sParentTemp =CAST(rootId AS CHAR); 

 

WHILE sParentTemp IS NOT NULL DO 

 

IF (sParentList IS NOT NULL) THEN 

SET sParentList = CONCAT(sParentNameTemp,' > ',sParentList); 

ELSE 

SET sParentList = CONCAT(sParentNameTemp); 

END IF; 

 

SELECT 

GROUP_CONCAT(P_MENU_CODE) INTO sParentTemp

FROM ITS_MENU

WHERE FIND_IN_SET(MENU_CODE,sParentTemp)>0; 

 

SELECT ifnull((SELECT MESSAGE_NAME FROM ITS_MESSAGE A2 WHERE A2.MESSAGE_CODE = A1.MESSAGE_CODE AND A2.LANG_CODE = 'KO'), A1.MENU_NAME) into sParentNameTemp FROM ITS_MENU A1 WHERE A1.MENU_CODE = sParentTemp;

 

END WHILE; 

 

/* 위 쿼리의 결과로는 자기 자신을 가져올 수 없기 때문에 자기 자신을 추가하기 위한 코드를 추가한다. */

SELECT ifnull((SELECT MESSAGE_NAME FROM ITS_MESSAGE A2 WHERE A2.MESSAGE_CODE = A1.MESSAGE_CODE AND A2.LANG_CODE = 'KO'), A1.MENU_NAME) into sParentNameTemp FROM ITS_MENU A1 WHERE A1.MENU_CODE = rootId ;

 

IF (sParentList IS NOT NULL) THEN 

SET sParentList = CONCAT(sParentList,' > ',sParentNameTemp); 

ELSE 

SET sParentList = CONCAT(sParentNameTemp, ''); 

END IF; 

 

RETURN sParentList; 

END