rrQy09Uv

1DROP TABLE IF EXISTS `source_table`;
2
3CREATE TABLE `source_table` (
4  `Type_id` int(11) NOT NULL,
5  `Type_id_p` int(11) DEFAULT NULL,
6  `Type_name` varchar(45) DEFAULT NULL,
7  `C_order` int(11) DEFAULT NULL,
8  PRIMARY KEY (`Type_id`)
9) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
10
11INSERT INTO `source_table` VALUES (0,NULL,'Root',NULL),(1,0,'Food',NULL),(2,1,'Fast food',NULL),(3,2,'American',NULL),(4,2,'Japan',NULL),(5,3,'McDonald',NULL),(6,4,'Ichran',NULL);
12
13DELIMITER //
14
15DROP PROCEDURE IF EXISTS `getParent`//
16
17CREATE PROCEDURE `getParent`(
18  IN `typeId` int,
19  OUT `result` varchar(255)
20)
21BEGIN
22  DECLARE `parentTypeId` int;
23  DECLARE `currentTypeName` varchar(255);
24  SELECT `Type_id_p`, `Type_name` INTO `parentTypeId`, `currentTypeName`
25  FROM source_table
26  WHERE `Type_id` = `typeId`;
27
28  IF `parentTypeId` IS NOT NULL THEN
29    CALL `getParent`(`parentTypeId`, `result`);
30    SET `result` := CONCAT( IFNULL(`result`, ''), ' > ', `currentTypeName`);
31  ELSE
32    SET `result` := CONCAT( IFNULL(`result`, ''), `currentTypeName`);
33
34  END IF;
35
36END//
37
38DELIMITER ;
39
40call getParent( 5, @result);
41
42select @result;