Create a recursive stored procedure in Mysql

Step 1: SET max_sp_recursion_depth = 12; Step 2: CREATE createChildDept process SQL code www.2cto.com 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_units 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 showChildDeptSql code www.2cto.com 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_administrative ments WHERE tmpLst. id = zk_departments.id AND zk_departments.id> rootId order by tmpLst. sno; # select zk_user_departments.dept_id, zk_user_departments.user_id from zk_user_orders ments left join tmpLst on tmpLst. id = zk_user_departments.dept_id and zk_user_departments.dept_id> rootId order by tmpLst. sno; END $ DELIMITER; Step 4: CALL showChildDept (128)