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;
'IT노트 > MYSQL' 카테고리의 다른 글
| mysql grant user 생성 및 권한 설정 (0) | 2015.02.25 |
|---|---|
| MySQLDUMP 사용법과 옵션들 (0) | 2015.02.22 |
| [mysql] function 정보 (0) | 2015.02.20 |
| [MySQL] DB 캐릭터셋 확인하기 (0) | 2015.02.19 |
| Hierarchical data in MySQL: parents and children in one query | EXPLAIN EXTENDED (0) | 2015.02.16 |
