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

  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;