mysql数据库实现类似oracle connect by prior的查询

--注下面的语句用到了表 
DROP TABLE IF EXISTS `a_sys_org`; 
CREATE TABLE `a_sys_org` ( 
  `org_id` varchar(32) NOT NULL COMMENT '机构号', 
  `org_name` varchar(256) NOT NULL COMMENT '机构名称', 
  `parent_org_id` varchar(32) NOT NULL COMMENT '父机构号', 
  PRIMARY KEY (`org_id`) 
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='系统机构表'; 


-- 方法1，函数实现 
-- 注意  SELECT getChildOrg('00010'); 
-- 最大支持字符串连接102400 

DROP FUNCTION IF EXISTS getChildidList; 
CREATE FUNCTION getChildidList(rootId VARCHAR(32)) 
RETURNS text 
BEGIN 
DECLARE sTemp text; 
DECLARE sTempChd text; 
SET@@group_concat_max_len = 102400; 
SET sTemp = '$'; 
SET sTempChd = rootId; 

WHILE sTempChd IS NOT NULL DO 
SET sTemp = concat(sTemp, ',', sTempChd); 
SELECT 
group_concat(org_id) INTO sTempChd 
FROM 
a_sys_org 
WHERE 
FIND_IN_SET(parent_org_id, sTempChd) > 0; 
END WHILE; 
SET@@group_concat_max_len = 1024; 
RETURN SUBSTRING(sTemp,3); 
END 

-- 方法2，存储过程+中间表 
-- 存储过程实现mysql递归查询，类似oracle start with connect by prior 
-- 注意  call getChildOrg('00010'); 
-- 递归有深度限制，最大是255层 

DROP PROCEDURE IF EXISTS getChildOrg; 
CREATE PROCEDURE getChildOrg (IN rootid VARCHAR(32)) 
BEGIN 
DECLARE _level_var INT; 

DROP TABLE IF EXISTS temp_child_list; 
CREATE TABLE temp_child_list ( 
_id VARCHAR(32), 
_level INT 
); 
SET _level_var = 0; 
  INSERT INTO temp_child_list (_id, _level) VALUE(rootid,_level_var); 
SET _level_var = _level_var + 1; 
INSERT INTO temp_child_list (_id, _level) 
SELECT 
org_id, 
_level_var 
FROM 
a_sys_org 
WHERE 
parent_org_id = rootid; 
WHILE ROW_COUNT() > 0 
DO 
SET _level_var = _level_var + 1; 
INSERT INTO temp_child_list SELECT 
a.org_id, 
_level_var 
FROM 
a_sys_org a, 
temp_child_list b 
WHERE
a.parent_org_id = b._id 
AND b._level = _level_var - 1; 
END WHILE; 
  SELECT _id FROM temp_child_list ORDER BY _level; 
  DROP TABLE IF EXISTS temp_child_list; 
END; 

-- 方法3，存储过程+临时表 
-- 存储过程实现mysql递归查询，类似oracle start with connect by prior 
-- 注意  call getChildOrg('00010'); 
-- 递归有深度限制，最大是255层 
-- 此存储过程利用了临时表 

DROP PROCEDURE IF EXISTS showChildList; 
CREATE PROCEDURE showChildList (IN rootId VARCHAR(32)) 
BEGIN 
  SET@@max_sp_recursion_depth=99; 
DROP TEMPORARY TABLE IF EXISTS temp_child_list; 
CREATE TEMPORARY TABLE temp_child_list ( sno INT PRIMARY KEY auto_increment, _id VARCHAR (32), _depth INT ); 
-- 插入当前节点 
INSERT INTO temp_child_list (_id, _depth) VALUES (rootId ,- 1); 
-- 插入子节点 
CALL createChildList (rootId, 0); 
SELECT o.org_id FROM temp_child_list t, a_sys_org o WHERE t._id = o.org_id ORDER BY t.sno; 
END; 
DROP PROCEDURE IF EXISTS createChildList; 
CREATE PROCEDURE createChildList ( IN rootId VARCHAR (32), IN depth_var INT ) 
BEGIN 
DECLARE done INT DEFAULT 0; 
DECLARE b VARCHAR (32); 
DECLARE cur1 CURSOR FOR SELECT org_id FROM a_sys_org WHERE parent_org_id = rootId; 
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; 
INSERT INTO temp_child_list (_id, _depth) SELECT org_id, depth_var FROM a_sys_org WHERE parent_org_id = rootId; 
OPEN cur1; 
FETCH cur1 INTO b; 
WHILE done = 0 DO CALL createChildList (b, depth_var + 1); 
FETCH cur1 INTO b; 
END WHILE; 
CLOSE cur1; 
END;