마리아DB 최신 버젼에서는 계층구조 조회 기능을 지원한다고하나, 일단 지금 쓰고있는 마리아DB는 구버젼이어서 function으로 구현해야한다.
계층 조회가 필요한 테이블 마다 function을 구현해야하고 구현 방법 또한 명쾌하진 않아 특히 오라클 환경의 개발자라면 더욱 짜증나는 일이다.
이 펑션을 및 수행 과정을 히해하려면 실행되는 순서에 대해 완벽하게 알고있어야하는 불편함이 있다.
구현방법
-펑션 생성
-조회할 쿼리에서 id ,rownul, level 등 각 전역 변수 설정 후 펑션 호출
계층 구조 펑션 로직 설명
-조인걸린 테이블만큼 계층구조 조회 펑션을 반복(재귀)호출 한다
-펑션 내에서 loop를 돌리는데 하위부서가 있으면 하위부서의 id를 리턴하고 depth를 1더한다. 여기서 설정되는 depth, sort_order는 전역 변수에 대입하여 반복한다.
-하위부서가 없으면 이전 상위 depth로 돌아가고, 이 상황에서 하위 부서 중 다음 sort_order 의 조직이 있는지 검사 후 있으면 id리턴하고 1더한다.
-loop가 끝나면 계속 상위 @start_with까지 depth가 타고 올라갈 것이고 여기서 null이 반복 리턴 된다.
제약사항
-mysql 전역변수(현재 사용자 세션에 한하는 전역변수임)를 이용해서 재귀 호출을 한다.
-여러개의 key로 조회가 불가능하다. 즉 조회하려는 group_id가 여러개 일경우 추가적인 커스터마이징이 필요함.
-여러 테이블에 쓴다면 각각 테이블에 맞는 function을 만들어야하는 불편함.
-조인걸리는 레코드 개수만큼 재귀 실행이 되므로, 개수에 대한 보장이 필요하며, 레코드보다 실행회수가 많아지면 null 리턴을 하게 되는데 성능이 느려질 수 있으므로 최대한 조건에 맞는 레코드 개수가 조회되도록 조건을 걸어줘야한다. 레코드 내용은 상관없음. 아래 쿼리에서는 company_id를 where 조건으로 만들어놓았다. >> 빨간 글씨 처리한 부분
원본 테이블 데이터
company_id parent_group_id group_id sort_order
-----------------------------------------------------------
C1 G0 000
C1 G0 G01 001
C1 G0 G02 002
C1 G01 G011 003
C1 G01 G011 004
C1 G02 G021 005
C2 T1 006
조회 xml
SELECT GROUP_ID, level, rownum FROM
(
SELECT
fncChildGroups() AS GROUP_ID,
@level as level,
@rownum as rownum
FROM (
SELECT
@start_with := G_H2.group_id,
@id := @start_with,
@level := 0,
@rownum := 0,
@sort_order := -1,
@portal_id := #company_id#
) G_H1,
group G_H2
WHERE G_H2.company_id = #company_id#
) G_H3
WHERE GROUP_ID IS NOT NULL
function 생성 스크립트(maria db)
DELIMITER $$
DROP FUNCTION IF EXISTS `fncChildGroups` $$
CREATE DEFINER=`생성할 DB명`@`%` FUNCTION `fncChildGroups`()
RETURNS varchar(27) CHARSET utf8
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
DECLARE _group_id varchar(27) default '';
DECLARE _parent_group_id varchar(27);
DECLARE CONTINUE HANDLER FOR NOT FOUND SET @id = NULL;
SET _parent_group_id = @id;
set @rownum := @rownum + 1;
#실제로 group_id가 null이거나 loop가 끝나고 @start_with까지 타고간(depth가 -1인 상황) 상태에서는 null 리턴.
IF @id IS NULL THEN
RETURN NULL;
END IF;
if @level = 0 then
set @level := 1;
return @id;
end if;
LOOP
#sort_order 정렬하여 하위 depth 중 다음 sort_order의 조직을 선택
SELECT group_id, sort_order INTO @id, @sort_order
FROM tbl_group
WHERE parent_group_id = _parent_group_id and sort_order > @sort_order
order by sort_order
limit 1;
#하위 부서가 있는 경우 > 현재 group_id 리턴하고 depth에 1 더함
IF @id IS NOT NULL OR _parent_group_id = @start_with THEN
SET @level = @level + 1;
RETURN @id;
END IF;
#@하위 부서가 없는 경우 > depth 1을 뺀다.
SET @level := @level - 1;
SELECT group_id, parent_group_id, sort_order INTO _group_id, _parent_group_id, @sort_order
FROM tbl_group
WHERE group_id = _parent_group_id;
END LOOP;
END;
$$
DELIMITER ;
