Mysql database supports queries similar to oracle connect by prior

Mysql Databases implement queries similar to oracle connect by prior -- note that the following statement uses the drop table if exists 'A _ sys_org 'TABLE '; create table 'A _ sys_org '('org _ id' varchar (32) not null comment 'Organization No.', 'org _ name' varchar (256) not null comment 'Organization name', 'parent _ org_id 'varchar (32) not null comment 'parent organization No.', primary key ('org _ id ')) ENGINE = InnoDB default charset = utf8 COMMENT = 'System mechanism table'; -- Method 1, function implementation -- Note SELECT getChildOrg ('20140901 '); -- supports string connection up to 102400 drop function if exists getChildidList; create function getChildidList (rootId VARCHAR (32) RETURNS text begin declare sTemp text; DECLARE sTempChd text; SET @ resolve = 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 -- method 2, stored Procedure + intermediate table -- Stored Procedure implements mysql recursive query, similar to oracle start with connect by prior -- note call getChildOrg ('123'); -- there is a deep limit on recursion, the maximum value is 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. 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; -- Method 3, stored Procedure + temporary table -- Stored Procedure implements mysql recursive query, similar to oracle start with connect by prior -- note call getChildOrg ('123'); -- there is a deep limit on recursion, the maximum is layer 255 -- this stored PROCEDURE uses the temporary table 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); -- INSERT the subnode 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;