MYSQL CONNECT BY 예제

MYSQL에서 CONNECT BY를 구현하는 방법 중 함수를 만들어서 구현하는 방법이 있다.

CONNECT BY를 사용하기 위한 함수는 해당 테이블에서만 사용 가능한 함수이다.



DROP FUNCTION IF EXISTS DEV_UCI_DB.FN_CONNECT_BY_ROOT_MTTR;
CREATE FUNCTION DEV_UCI_DB.`FN_CONNECT_BY_ROOT_MTTR`(v_no INT) RETURNS int(11)
    READS SQL DATA
BEGIN
        DECLARE v_mttr_iem_no INT;
        DECLARE v_upper_mttr_iem_no INT; -- 부모 항목
        DECLARE v_count INT;
        SET v_mttr_iem_no := v_no;
        SET v_count := 0;
       
        IF v_mttr_iem_no IS NULL THEN
                RETURN NULL;
        END IF;
        LOOP
                SELECT UPPER_MTTR_IEM_NO
                  INTO v_upper_mttr_iem_no
                  FROM TN_MM_MTTR_IEM_I
                 WHERE MTTR_IEM_NO = v_mttr_iem_no;
                IF v_upper_mttr_iem_no = 0 THEN
                        RETURN v_mttr_iem_no;
                END IF;
                SET v_mttr_iem_no := v_upper_mttr_iem_no;
                SET v_count := v_count + 1;
               
                IF v_count >= 10 THEN
                  RETURN 0;
                END IF;
        END LOOP;
END;

함수를 이용해서 CONNECT BY 기능을 구현할 수 있다.

SELECT A.MTTR_IEM_NM -- 물질 항목 명
     , A.MTTR_IEM_NO -- 물질 항목 번호
     , A.UPPER_MTTR_IEM_NO -- 상위 물질 항목 번호
     , LEVEL
     , (SELECT COUNT(*) C_CNT
          FROM TN_MM_MTTR_IEM_D
         WHERE MTTR_ID = '91235'
           AND MTTR_IEM_NO = A.MTTR_IEM_NO
           AND USE_AT = 'Y'
       ) C_CNT  -- 항목 내용 결과 수
     , (SELECT COUNT(*) I_CNT
          FROM TN_MM_MTTR_IEM_I
         WHERE UPPER_MTTR_IEM_NO = A.MTTR_IEM_NO
           AND USE_AT = 'Y'
               LIMIT 1
       ) I_CNT  -- 상위 물질 항목 번호 수 (0이면 최하위 항목)
  FROM (
       SELECT CONCAT(REPEAT('   ', LEVEL - 1) , CAST(B.MTTR_IEM_NM AS CHAR)) AS MTTR_IEM_NM
            , B.MTTR_IEM_NO
            , LEVEL
            , B.UPPER_MTTR_IEM_NO
         FROM (
              SELECT FN_CONNECT_BY_MTTR(MTTR_IEM_NO) AS UPPER_MTTR_IEM_NO
                   , MTTR_IEM_NO
                   , @LEVEL AS LEVEL
                FROM (
                     -- 0 : 전체, 1 : 기본정보, 87 : 위험유해성정보, 200 : 법규 규제 정보, 229 : INVENTORY, 484 : 안전관리정보, 539 : 측정방법
                     SELECT @START_WITH := 1 -- 2단계 표준항목 번호 (파라미터)
                          , @ID := @START_WITH
                          , @LEVEL := 0
                     ) VARS
                   , TN_MM_MTTR_IEM_I -- 물질관리 물질 항목 정보엔티티
              WHERE @ID IS NOT NULL
                AND USE_AT = 'Y'
              ) A
              JOIN TN_MM_MTTR_IEM_I B -- 물질관리 물질 항목 정보엔티티
                ON A.UPPER_MTTR_IEM_NO = B.MTTR_IEM_NO
       ) A
;

-- CONNECT BY ROOT

DROP FUNCTION IF EXISTS DEV_UCI_DB.FN_CONNECT_BY_ROOT_MTTR;
CREATE FUNCTION DEV_UCI_DB.`FN_CONNECT_BY_ROOT_MTTR`(v_no INT) RETURNS int(11)
    READS SQL DATA
BEGIN
        DECLARE v_mttr_iem_no INT;
        DECLARE v_upper_mttr_iem_no INT; -- 부모 항목
        DECLARE v_count INT;
        SET v_mttr_iem_no := v_no;
        SET v_count := 0;
       
        IF v_mttr_iem_no IS NULL THEN
                RETURN NULL;
        END IF;
        LOOP
                SELECT UPPER_MTTR_IEM_NO
                  INTO v_upper_mttr_iem_no
                  FROM TN_MM_MTTR_IEM_I
                 WHERE MTTR_IEM_NO = v_mttr_iem_no;
                IF v_upper_mttr_iem_no = 0 THEN
                        RETURN v_mttr_iem_no;
                END IF;
                SET v_mttr_iem_no := v_upper_mttr_iem_no;
                SET v_count := v_count + 1;
               
                IF v_count >= 10 THEN
                  RETURN 0;
                END IF;
        END LOOP;
END;