계측쿼리에 사용함수 hierarchy_connect_by_parent_eq_prior_id;

---

DROP FUNCTION IF EXISTS ITSM.hierarchy_connect_by_parent_eq_prior_id;

CREATE FUNCTION ITSM.`hierarchy_connect_by_parent_eq_prior_id`(value INT) RETURNS int(11)

    READS SQL DATA

BEGIN

        DECLARE _id INT;

        DECLARE _parent INT;

        DECLARE _next INT;

        DECLARE CONTINUE HANDLER FOR NOT FOUND SET @id = NULL ;

        SET _parent = @id ;

        SET _id = - 1;

        IF @id IS NULL THEN

                RETURN NULL ;

        END IF;

        LOOP

                SELECT  MIN (id)

                INTO    @id

                FROM    t_hierarchy

                WHERE   parent = _parent

                        AND id > _id;

                IF @id IS NOT NULL OR _parent = @start_with THEN

                        SET @level = @level + 1;

                        RETURN @id ;

                END IF;

                SET @level := @level - 1;

                SELECT  id, parent

                INTO    _id, _parent

                FROM    t_hierarchy

                WHERE   id = _parent;

        END LOOP;      

END;

---