MySQL创建递归型的存储过程

第一步：SET max_sp_recursion_depth=12;

第二步：创建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_departments 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 ;http://www.linuxidc.com第三步：创建showChildDeptDELIMITER $$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 zk_user_departments.dept_id,zk_user_departments.user_id from zk_user_departments left join tmpLst on tmpLst.id=zk_user_departments.dept_id and zk_user_departments.dept_id>rootId order by tmpLst.sno;
END$$DELIMITER ;第四步：CALL showChildDept（128）Oracle数据库用SQL实现快速分页修改Oracle 11g账户密码过期时间相关资讯 MySQL入门基础教程 MySQL创建存储过程 MySQL递归

MySQL创建存储过程实例（03月08日）
再说MySQL中的 table_id （06/09/2014 16:58:44）

MySQL新旧版本ORDER BY 处理方法（06/24/2014 15:18:10）
MySQL 生成随机密码（03/06/2013 20:01:29）

本文评论查看全部评论（0）