62,614
社区成员
发帖
与我相关
我的任务
分享
CREATE TABLE `tb` (
`id` varchar(20) NOT NULL,
`name` varchar(50) default NULL,
`grade` int(11) default NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE function getTree(@parentId varchar(20), @level int =1)
returns @t table(name varchar(50),id varchar(20),parent varchar(20), grade int)
as
begin
declare @id varchar(20);
declare @name varchar(50);
//游标定义
declare cur1 CURSOR FOR select name,id from table where grade = @level + 1 and substring(id,0,2*level)= parentId ;
//游标介绍定义
// declare CONTINUE HANDLER FOR SQLSTATE '02000' SET tid = null;
//开游标
OPEN cur1;
FETCH cur1 INTO @name, @id;
WHILE ( @id is not null )
DO
insert into @t values(@name,@id, @parentId,@level +1);
//树形结构数据递归收集到建立的临时表中
call useCursor(@id,@level +1);
FETCH cur1 INTO @name,@id ;
END WHILE;
//关闭游标
CLOSE cur1 ;
// 删除游标
DEALLOCATE cur1;
END;//
select getTree('00',1); //调用
CREATE TABLE `tb` (
`id` varchar(20) NOT NULL,
`name` varchar(50) default NULL,
`grade` int(11) default NULL,
PRIMARY KEY (`id`)
);