[mariadb] WITH 재귀쿼리 계층구조(조직도)

WITH RECURSIVE CTE AS (
         SELECT DEPT_ID,
         DEPT_NAME,
         UP_DEPT_ID,
         DEPT_LOC,
         DEPT_SORT_ORDR,
         DEPT_STATUS,              
         DEPT_DEPTH,
         TOP_DEPT_YN, 
         1 AS LEVEL            
        FROM ORG_DEPARTMENT
        UNION ALL
         SELECT  A.DEPT_ID,
          A.DEPT_NAME,
          A.UP_DEPT_ID,
          A.DEPT_LOC,
          A.DEPT_SORT_ORDR,
          A.DEPT_STATUS,              
          A.DEPT_DEPTH,
          A.TOP_DEPT_YN,
          1 + B.LEVEL AS LEVEL
        FROM ORG_DEPARTMENT A
        INNER JOIN CTE B ON A.UP_DEPT_ID = B.DEPT_ID
)
SELECT DISTINCT 
        FUNC_DEPT_LOC_NAME(DEPT_ID,'ko'),
        DEPT_ID,
         DEPT_NAME,
         UP_DEPT_ID,
         DEPT_LOC,
         DEPT_SORT_ORDR,
         DEPT_STATUS,              
         DEPT_DEPTH,
         TOP_DEPT_YN 
FROM CTE
WHERE DEPT_STATUS ='U'
AND FUNC_DEPT_LOC_NAME(DEPT_ID,'ko') NOT REGEXP 'ESG|TFT|안전보건|운영지원|조사역|헬스키퍼'
ORDER BY  FUNC_DEPT_LOC_NAME(DEPT_ID,'ko') ASC, DEPT_SORT_ORDR ASC

 

 

===========================================================================

 

#DROP FUNCTION IF EXISTS FUNC_DEPT_LOC_NAME;
CREATE FUNCTION `FUNC_DEPT_LOC_NAME`(deptId VARCHAR(30), locale VARCHAR(2)) RETURNS varchar(4000) CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci
BEGIN
DECLARE flag         CHAR(1);
DECLARE endIdx       INT;
DECLARE tmpPathName  VARCHAR(100);
DECLARE pathName     VARCHAR(4000);
DECLARE deptLoc      VARCHAR(4000);

SET flag     = 'Y';
SET pathName = '';

SELECT CONCAT(DEPT_LOC,'/')
INTO deptLoc
FROM ORG_DEPARTMENT
WHERE DEPT_ID = deptId;

IF deptLoc IS NOT NULL THEN
loop1: WHILE flag = 'Y' DO
SET endIdx = LOCATE('/',deptLoc);

IF endIdx = 0 THEN
LEAVE loop1;
END IF;

SELECT FUNC_I18N(DEPT_ID, LOCALE, 'DEPT_NAME')
INTO tmpPathName
FROM ORG_DEPARTMENT
WHERE DEPT_ID = SUBSTR(deptLoc, 1, endIdx-1);

SET deptLoc = SUBSTR(deptLoc,endIdx+1);
SET pathName = CONCAT(pathName,tmpPathName,'>');
SET tmpPathName = NULL;
END WHILE loop1;

SET pathName = SUBSTR(pathName,1,CHAR_LENGTH(pathName)-1);
END IF;

RETURN pathName;
END;