Create a recursive stored procedure in MySQL

Step 1: SETmax_sp_recursion_depth12; Step 2: Create the createChildDept process DELIMITER $ USE 'zhiku '$ DROPPROCEDUREIFEXIS

Step 1: SET max_sp_recursion_depth = 12; Step 2: Create the createChildDept process DELIMITER $ USE 'zhiku '$ DROP PROCEDURE IF EXIS

Step 1: SET max_sp_recursion_depth = 12;

Step 2: Create createChildDept

DELIMITER $

USE 'zhiku '$

Drop procedure if exists 'createchilddept '$

Create definer = 'root' @ '%' PROCEDURE 'createchilddept '(IN rootId INT, IN nDepth INT)
BEGIN
DECLARE done int default 0;
DECLARE B INT;
DECLARE cur1 cursor for select id FROM zk_orders ments WHERE parent_id = rootId;
Declare continue handler for not found set done = 1;
Insert into tmpLst VALUES (NULL, rootId, nDepth );
OPEN cur1;
FETCH cur1 INTO B;
WHILE done = 0 DO
CALL createChildDept (B, nDepth + 1 );
FETCH cur1 INTO B;
End while;
CLOSE cur1;
END $

DELIMITER;

Step 3: Create showChildDept

DELIMITER $

USE 'zhiku '$

Drop procedure if exists 'showchilddept '$

Create definer = 'root' @ '%' PROCEDURE 'showchilddept '(IN rootId INT)
BEGIN
Create temporary table if not exists tmpLst
(Sno int primary key AUTO_INCREMENT, id INT, depth INT );
Delete from tmpLst;
CALL createChildDept (rootId, 0 );
SELECT zk_departments.id, zk_departments.name FROM tmpLst, zk_departments WHERE tmpLst. id = zk_departments.id AND zk_departments.id> rootId order by tmpLst. sno;
# Select region, zk_user_departments.user_id from zk_user_orders ments left join tmpLst on tmpLst. id = zk_user_departments.dept_id and region> rootId order by tmpLst. sno;
END $

DELIMITER;

Step 4: CALL showChildDept (128)

,