PL/SQL 계층형 쿼리 카테고리 카테고리명 가져오기

카테고리명 가져오기 ex) 부모1 > 부모2 > 자식1

CREATE DEFINER=`workerman`@`%` FUNCTION `F_GET_CATEGORY_NEW_NAME`(idx bigint) RETURNS varchar(500) CHARSET utf8
    READS SQL DATA
BEGIN
        DECLARE _id bigint;
        DECLARE _p_category_no bigint;
        
        DECLARE _category_name varchar(500);
        DECLARE _name varchar(20);
        
        SET _id = idx;
        set _category_name = '';
        IF _id IS NULL THEN
                RETURN NULL;
        END IF;
        
        IF _id = 0 THEN
                RETURN '0';
        END IF;

        LOOP
                select p_category_no, name -- 130 > 23
                into _p_category_no, _name
                from w_category_new where category_no = _id;
                
                 IF _p_category_no = 0 THEN -- 마지막 제일 상위카테고리까지 갔을때
                 set _category_name := concat((select name from w_category_new where category_no = _id),' > ',_category_name);
                 set _category_name := substr(_category_name,1,CHAR_LENGTH(_category_name)-2);
                 RETURN _category_name;
                 END IF;
                // 함수인자로 들어온 category_no 와 일치하는 카테고리 name부터 더하고 그 다음부터 앞으로 concat
                set _category_name := concat((select name from w_category_new where category_no = _id),' > ',_category_name);
                SET _id := _p_category_no;                
        END LOOP;
END;

 

카테고리 계층형 쿼리 

- 쿼리

select
    wc.name
    , wc.category_no
    , wc.p_category_no
    , wc.create_date
    , wc.update_date
    , wc.working_time
    , wc.people_number
    , wc.image_url
    , wc.price
    , wa.admin_id
    , wa.admin_name
    , fnc.row
    , wc.tag
    , wc.disp_order
    , wc.disp_yn
    , fnc.level
    , wc.comment
    , (select count(*) from w_category_new where p_category_no = wc.category_no) as cnt
    , f_get_category_order_pad_new(wc.category_no) as orders_pad
from
   (select (@rownum:=@rownum+1) as row, start_with_category_new() as category_no, @level as level
      from (select @rownum:=0) row, (select @start_with:=0, @id:=@start_with, @level:=0) vars
        join w_category_new
       where @id is not null) fnc
join w_category_new wc on fnc.category_no = wc.category_no
left join w_admin wa on wa.admin_no = wc.update_no
order by orders_pad asc

 

- START_WITH_CATEGORY_NEW 함수

CREATE DEFINER=`workerman`@`%` FUNCTION `START_WITH_CATEGORY_NEW`() RETURNS bigint(20)
    READS SQL DATA
BEGIN
        DECLARE _id bigint(20); -- _id 선언
        DECLARE _parent bigint(20); -- _parent 선언
        
        DECLARE CONTINUE HANDLER FOR NOT FOUND SET @id = NULL;

        SET _parent = @id; -- 초기값 0 ( == start_with)
        SET _id = -1; 
        
        IF @id IS NULL THEN
                RETURN NULL;
        END IF;

        LOOP 
                SELECT  MIN(category_no) -- w_category_new 에서 가장 작은 category_no = 1
                INTO    @id -- 1 >> SELECT  MIN(category_no) := @id 
                FROM    w_category_new
                WHERE   p_category_no = _parent -- p_category_no = 0
                        AND category_no > _id; -- category_no  > -1
                -- 아래 END IF; loop가 다돌고 다시 _parent가 start_with(0) 이 되면?
                IF @id IS NOT NULL OR _parent = @start_with THEN -- @id(초기값 0)이 0이 아니거나 parent = @start_with level 일때 ++
                        SET @level = @level + 1;
                        RETURN @id; -- 
                END IF;
                SET @level := @level - 1; -- 한개의 카테고리 loop가 끝나면 level -- 다른카테고리 탐색
                SELECT  category_no, p_category_no
                INTO    _id, _parent -- category_no > _id , p_category_no > _parent
                FROM    w_category_new
                WHERE   category_no = _parent; -- 부모와 현재 카테고리가 같은것 출력(계층)
        END LOOP;
END;

 

참고

DROP FUNCTION IF EXISTS fnc_hierarchi;
 
DELIMITER $$
 
CREATE FUNCTION  fnc_hierarchi() RETURNS INT
 
NOT DETERMINISTIC
 
READS SQL DATA
 
BEGIN
 
    DECLARE v_id INT;
    DECLARE v_parent INT;    
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET @id = NULL;
 
    SET v_parent = @id;
    SET v_id = -1;
 
    IF @id IS NULL THEN
        RETURN NULL;
    END IF;
 
    LOOP
    
    /* 첫번쨰 실행시 v_parent에 @id가 들어 있어 자식 노드가 있나 확인.
      다음 실행 부터는 v_id에 @id가 들어있어 형제노드가 있는지 확인용. */
    SELECT MIN(id)
      INTO @id 
      FROM ANIMAL
     WHERE p_id = v_parent
       AND id > v_id;
 
    /* 현재 Root노드이거나 아직 남은 자식,형제노드가 있으면 
       그 노드로 이동해서 id,level 출력 후 다시 함수 호출 */
    IF (@id IS NOT NULL) OR (v_parent = @start_with) THEN
       SET @level = @level + 1;
    RETURN @id;
    END IF;
    

    /* 형제 노드가 없으면 현재 노드의 부모노드로 이동해서 다시 함수 호출 */
    SET @level := @level - 1;
 
    SELECT id, p_id
      INTO v_id , v_parent 
        FROM ANIMAL
       WHERE id = v_parent;
   
    END LOOP;
 
END
 
$$
 
DELIMITER ;