mysql存储过程递归调用产生树数据 使用finereport的树下拉框时，要求提供有层次结构的数据。例如：一级001,二级001001，三级001001001 等。而我们一般的递归表是这样的，定义一个id和一个pid,id和pid在长度上没有父子关系。这样的数据，finereport是不认的。
mysql存储过程递归调用产生树数据使用finereport的树下拉框时，要求提供有层次结构的数据。例如：一级001,二级001001，三级001001001 等。而我们一般的递归表是这样的，定义一个id和一个pid,id和pid在长度上没有父子关系。这样的数据，finereport是不认的。故只能通过存储过程进行转换。 代码如下： set foreign_key_checks=0; -- ---------------------------- -- table structure for `t_tlm_tree` -- ---------------------------- drop table if exists `t_tlm_tree`; create table `t_tlm_tree` ( `id` varchar(100) character set utf8 collate utf8_unicode_ci not null default '0' , `pid` varchar(100) character set utf8 collate utf8_unicode_ci null default null , `nodename` varchar(200) character set utf8 collate utf8_unicode_ci null default null , `fast` int(11) null default null , primary key (`id`) ) engine=innodb default character set=utf8 collate=utf8_unicode_ci ; -- ---------------------------- -- records of t_tlm_tree -- ---------------------------- begin; insert into `t_tlm_tree` values ('10000', '-1', '中国', '1'), ('10001', '10000', '浙江', '1'), ('10002', '10000', '河南', '1'), ('10003', '10001', '杭州', '1'), ('10004', '10001', '温州', '1'), ('10005', '10002', '郑州', '1'), ('10006', '10002', '信阳', '1'), ('10007', '10006', '息县', '1'), ('10008', '10003', '滨江', '1'), ('10009', '10003', '西湖', '1'), ('10010', '10003', '上城', '1'), ('10011', '10006', '罗山', '1'); commit; -- ---------------------------- -- procedure structure for `p_create_tree_node` -- ---------------------------- drop procedure if exists `p_create_tree_node`; delimiter ;; create definer=`root`@`localhost` procedure `p_create_tree_node`(in `in_pid` varchar(100)) begin declare v_has_child int default 0; declare v_rows int default 0; declare v_id varchar(100) default ''; declare v_pid varchar(100) default ''; declare v_nodename varchar(100) default ''; declare v_tree_id varchar(100) default ''; declare v_tree_pid varchar(100) default ''; declare v_done int default 0; declare v_cur cursor for select id,pid,nodename from t_tlm_tree where `fast`=1 and pid=in_pid; declare continue handler for not found set v_done=1; set @@max_sp_recursion_depth = 100; open v_cur; loop_tag:loop fetch v_cur into v_id,v_pid,v_nodename; if v_done=1 then leave loop_tag; end if; if strcmp(in_pid,'-1')=0 then insert into t_tlm_device_tree(id,pid,nodename,oid) values('001',null,v_nodename,v_id); else select id into v_tree_pid from t_tlm_device_tree where oid = in_pid; set v_rows = v_rows+1; set v_tree_id = concat('000',v_rows); set @len = length(v_tree_id)-2; set v_tree_id = substr(v_tree_id from @len); set v_tree_id = concat(v_tree_pid,v_tree_id); insert into t_tlm_device_tree(id,pid,nodename,oid) values(v_tree_id,v_tree_pid,v_nodename,v_id); end if; set v_has_child = f_has_child_by_pid(v_id); if v_has_child =1 then call p_create_tree_node(v_id); end if; end loop loop_tag; close v_cur; end ;; delimiter ; -- ---------------------------- -- procedure structure for `p_get_device_tree` -- ---------------------------- drop procedure if exists `p_get_device_tree`; delimiter ;; create definer=`root`@`localhost` procedure `p_get_device_tree`() begin declare v_pid varchar(100) default '-1'; drop temporary table if exists t_tlm_device_tree; create temporary table t_tlm_device_tree ( id varchar(100), pid varchar(100), nodename varchar(100), oid int(100), primary key (id) ); call p_create_tree_node(v_pid); select * from t_tlm_device_tree; truncate table t_tlm_device_tree; drop temporary table if exists t_tlm_device_tree; end ;; delimiter ; -- ---------------------------- -- function structure for `f_has_child_by_pid` -- ---------------------------- drop function if exists `f_has_child_by_pid`; delimiter ;; create definer=`root`@`localhost` function `f_has_child_by_pid`(`f_pid` varchar(100)) returns int(11) begin declare v_ret int default 0; select count(1) into @num from t_tlm_tree where pid=f_pid and `fast`=1; if @num >0 then set v_ret = 1; end if; return v_ret; end ;; delimiter ;
【说明】：本文章由站长整理发布，文章内容不代表本站观点，如文中有侵权行为，请与本站客服联系(QQ:)！