mysql存储过程实现递归查询

-- 建表

create table _organization(

    orgcode varchar(40) not null primary key comment '机构编号',

    parentcode varchar(40) comment '父机构编号',

    orgname varchar(100) comment '机构名称' ,

    banktypeid varchar(10) comment '行别代码' , 

    orgnumber varchar(10) comment '机构号码'

) comment = '机构信息表' ;

-- 插入数据

insert into _organization values 

('C1010413004153' , '' , '中国银行股份有限公司燕郊分行'       , '004'       , '0520' ),

('C1010413004006'       , 'C1010413004153'       , '中国银行股份有限公司三河支行'  , '004'  , '0519' ),

('C1010413004017'       , 'C1010413004006'       , '中国银行股份有限公司三河迎宾北路支行'  , '004'  , '0532' ),

('C1010413004039'       , 'C1010413004006'       , '中国银行股份有限公司三河世纪花苑支行'  , '004'  , '0534' ),

('C1010413004120'       , 'C1010413004153'       , '中国银行廊坊市燕郊开发区北蔡路支行'  , '004'  , '0526' ),

('C1010413004175'       , 'C1010413004153'       , '中国银行股份有限公司廊坊市燕郊开发区迎宾路支行'  , '004'  , '0524' ),

('C1010413004186'       , 'C1010413004153'       , '中国银行股份有限公司廊坊市燕郊开发区海油大街支行'  , '004'  , '0527' ),

('C1010413005195'       , 'C1010413004153'       , '中国银行股份有限公司廊坊市燕郊燕顺支行'  , '004'  , '0528' ),

('C1010413004164'       , 'C1010413004153'       , '中国银行股份有限公司廊坊市燕郊开发区市场街支行'  , '004'  , '0525' ),

('C1010413005184'       , 'C1010413004153'       , '中国银行股份有限公司廊坊市燕郊燕高支行'  , '004'  , '0529' ),

('C1010413004051'       , 'C1010413004040'       , '中国银行股份有限公司香河迎宾路支行'  , '004' ,'' ),

('C1010413004062'       , 'C1010413004040'       , '中国银行股份有限公司香河第一城支行'  , '004' ,'' ),

('C1010413004084'       , 'C1010413004073'       , '中国银行股份有限公司大厂大安街支行'  , '004' ,'' ),

('C1010413004028'       , 'C1010413004006'       , '中国银行股份有限公司三河泃阳西大街支行'  , '004'  , '0533' ),

('C1010413004073'       , 'C1010413004153'       , '中国银行股份有限公司大厂支行'  , '004' ,'' ),

('C1010413004040'       , 'C1010413004153'       , '中国银行股份有限公司香河支行'  , '004' ,'' );

-- 创建存储过程

-- 实现功能：查询出机构编号为orgcode的所有下级机构（递归，利用orgcode、parentcode的关系进行递归查找）

-- 创建名为“allChildOrganization” 的存储过程

create procedure allChildOrganization(

in orgcode varchar(40)   -- 输入参数为 orgcode  类型为：varchar(40)

)

begin

declare p_done tinyint unsigned default(0); -- 下文中while循环结束的标志

declare p_depth smallint unsigned default(0); -- 记录查询的深度（循环的次数）

-- 创建临时表resultTemp，用于临时存放查询出的子孙级的机构信息

create temporary table resultTemp( 

parentcode varchar(40) ,

orgcode varchar(40) ,

depth smallint unsigned

) engine = memory;

insert into resultTemp values( null , orgcode , p_depth ); -- 先将 输入的orgcode 最为结点的最高级，插入到临时表 resultTemp中

-- 创建第二个临时表tempTab，用于存放子级或者孙级（同一级）的机构信息，然后用该临时表，去查询出下一级的机构信息

create temporary table tempTab engine = memory select * from resultTemp; 

while p_done <> 1 do  -- 循环开始

if exists(  -- 判断机构表_organization ， 是否有相应的下级机构

select 1 from _organization org , tempTab  where  org.parentcode = tempTab.orgcode 

) then

    -- 根据临时表tempTab 查询出相应的下级机构信息，插入临时表resultTemp中

insert into resultTemp select org.parentcode , org.orgcode , p_depth + 1 from _organization org , tempTab where org.parentcode = tempTab.orgcode and tempTab.depth = p_depth;

set p_depth = p_depth + 1; 

truncate table tempTab; -- 清空临时表tempTab

insert into tempTab select * from resultTemp where depth = p_depth; -- 将刚查询出的子级机构信息，插入临时表tempTab中

else

set p_done = 1;

end if;

end while;

select org.orgcode , org.parentcode , org.orgname , org.banktypeid , org.orgnumber from _organization org , resultTemp re where org.orgcode = re.orgcode;

-- 删除临时表

drop temporary table if exists resultTemp; 

drop temporary table if exists tempTab;

end

-- 调用存储过程

call allChildOrganization( 'C1010413004153');

显示出的结果：