[mysql/mariadb] 계층쿼리 함수생성 및 호출로 구현

계층쿼리를 만드는 방법은 함수로 구현하는 방법과 그냥 생쿼리로 짜는 방법이 있다.

그 중 함수로 구현하는 방법을 정리했다.

함수도 int파라미터로 받는 방법과 varchar로 받는 방법이 있는데

두 방법이 큰 차이가 있지는 않다.

 

1. varchar파라미터로 받는 방법

DROP FUNCTION IF EXISTS GET_DEPT_TREE;

DELIMITER $$
CREATE FUNCTION GET_DEPT_TREE(value VARCHAR(50)) RETURNS VARCHAR(50) //파라미터, 리턴타입 맞춰준다.
NOT DETERMINISTIC
READS SQL DATA

BEGIN
        DECLARE _menuCd VARCHAR(50); //함수내 변수 메뉴코드
        DECLARE _parnCd VARCHAR(50); //함수내 변수 부모코드 
        DECLARE CONTINUE HANDLER FOR NOT FOUND SET @MENU_CD = NULL; //@MENU_CD는 컬럼명으로 맞춰준다.

        SET _parnCd = @MENU_CD;
        SET _menuCd = '';
        
        IF @MENU_CD IS NULL THEN
                RETURN NULL;
        END IF;

       LOOP
                SELECT  MIN(MENU_CD)
                INTO    @MENU_CD
                FROM    YE_MENU_MGR
                WHERE   PARN_CD = _parnCd 
                        AND MENU_CD > _menuCd;
                IF @MENU_CD IS NOT NULL OR _parnCd = @start_with THEN
                        SET @level = @level + 1;
                        RETURN @MENU_CD;
                END IF;
                SET @level := @level - 1;
                SELECT MENU_CD, PARN_CD
                INTO    _menuCd, _parnCd
                FROM    YE_MENU_MGR
                WHERE   MENU_CD = _parnCd;
        END LOOP;
END

 

2. int파라미터로 받는 방법

참고 블로그에서 가져온 내용이다.

하지만 varchar파라미터와 내용이 아주 유사하다.

변수 타입만 맞춰주면 된다.

 

DELIMITER $$
CREATE FUNCTION hierarchy_connect_by_parentIdx_eq_prior_idx(value VARCHAR(50)) RETURNS VARCHAR(50)
NOT DETERMINISTIC 
READS SQL DATA 
BEGIN 
DECLARE _idx INT; 
DECLARE _parentIdx INT; 
DECLARE _next INT; 
DECLARE CONTINUE HANDLER FOR NOT FOUND SET @idx = NULL; 

SET _parentIdx = @idx; 
SET _idx = -1; 

IF @idx IS NULL THEN 
RETURN NULL; 
END IF; 

LOOP 
SELECT MIN(idx) 
INTO @idx FROM Category 
WHERE parentIdx = _parentIdx 
AND idx > _idx; 
IF @idx IS NOT NULL OR _parentIdx = @start_with THEN 
SET @level = @level + 1; 
RETURN @idx; 
END IF; 
SET @level := @level - 1; 
SELECT idx, parentIdx 
INTO _idx, _parentIdx 
FROM Category 
WHERE idx = _parentIdx; 
END LOOP; END 
$$ DELIMITER ;

출처: https://wookmania.tistory.com/96 [코드의추억]

 

3. 함수 사용

계층쿼리의 사용 이유는 level을 뽑는데 있다.

그 level을 뽑는 쿼리 예시다.

SELECT  
dc.MENU_CD
, CONCAT(REPEAT('       ', dc2.lev-1), MENU_NM) AS MENU_NM, PARN_CD, CAST(dc2.lev AS CHAR) AS lev
FROM    (
        SELECT  GET_DEPT_TREE(MENU_CD) AS MENU_CD, @level as lev
        FROM    (
                SELECT  @start_with := '',
                        @MENU_CD := @start_with,
                        @level := 0
                ) vars, YE_MENU_MGR
        WHERE   @MENU_CD IS NOT NULL
        ) dc2
JOIN    YE_MENU_MGR dc
ON      dc.MENU_CD = dc2.MENU_CD;

level별로 탭을 넣어 표시하도록 했다.

 

끝