CREATE PROCEDURE proEntpTypeInfo(iid int(11),lvl int)
BEGIN
-- 局部变量定义
declare tid int(11) default -1 ;
declare ttype_name varchar(255) default '' ;
declare tptype_id int(11) default -1 ;
-- 游标定义
declare cur1 CURSOR FOR select id,type_name,ptype_id from entp_type_info where (ptype_id=iid or id=iid)and type = 20 and is_del = 0;
-- 游标介绍定义
declare CONTINUE HANDLER FOR SQLSTATE '02000' SET tid = null,ttype_name=null,tptype_id=null;
SET @@max_sp_recursion_depth = 13;
-- 开游标
OPEN cur1;
FETCH cur1 INTO tid,ttype_name,tptype_id;
WHILE ( tid is not null )
DO
insert into tmp_entp_type_info values(tid,ttype_name,tptype_id,lvl);
-- 树形结构数据递归收集到建立的临时表中
call proEntpTypeInfo(tid,lvl+1);
FETCH cur1 INTO tid,ttype_name,tptype_id ;
END WHILE;
END;
drop procedure if exists proEntpTypeInfo;
drop temporary table if exists tmp_entp_type_info;
create temporary table if not exists tmp_entp_type_info(id int(20),type_name varchar(255), fid int(11),lvl int);
call proEntpTypeInfo(7,0);
select * from tmp_entp_type_info ;
