[MySQL] Create Function

DELIMITER //

CREATE FUNCTION FN_CODE( AS_GRP_CODE VARCHAR(10) , AS_CODE VARCHAR(10) ) 

RETURNS VARCHAR(20)

BEGIN

 

DECLARE RTN_VAL VARCHAR(50);

 

SELECT   CODENM

INTO RTN_VAL

 

FROM TB_CODE

WHERE GRP_CODE = AS_GRP_CODE 

AND CODE = AS_CODE;   

 

-- 반복문

DECLARE v1 INT DEFAULT 5;      
WHILE v1 > 0 DO

 

     -- IF ELSE 문

     IF v1 = 2                           
     THEN
          RTN_VAL = AS_GRP_CODE ;
     ELSE
          RTN_VAL = concat(AS_CODE,' > ',RTN_VAL );
     END IF;

 

SET v1 = v1 - 1;
END WHILE;

 

 

RETURN RTN_VAL;

 

END; //

 

 

 

 

 

-- 계층쿼리 function

DELIMITER $$
CREATE FUNCTION menu_connect_by(value VARCHAR(50)) RETURNS VARCHAR(50)
NOT DETERMINISTIC
READS SQL DATA
BEGIN
DECLARE _idx INT;
DECLARE _parentIdx INT;
DECLARE _next INT;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET @idx = NULL;

SET _parentIdx = @idx;
SET _idx = -1;

IF @idx IS NULL THEN
RETURN NULL;
END IF;

LOOP
SELECT MIN(idx)
INTO @idx FROM menu
WHERE parents_idx = _parentIdx
AND idx > _idx;
IF @idx IS NOT NULL OR _parentIdx = @start_with THEN
SET @level = @level + 1;
RETURN @idx;
END IF;
SET @level := @level - 1;
SELECT idx, parents_idx
INTO _idx, _parentIdx
FROM menu
WHERE idx = _parentIdx;
END LOOP; END
$$

 

 

 

## mariadb 10.2 버전 이상

with recursive cte as ( 
select 
idx, 
name, 
parents_idx, 1 AS level 
from menu 
where parents_idx is null 
union all 
select 
p.idx, 
p.name, 
p.parents_idx, 
1+level as level 
from 
menu p 
inner join cte 
on p.parents_idx = cte.idx ) 
select idx,ifnull( parents_idx, 0) as pidx,name, level from cte ;