mysql存储过程递归调用发作树数据

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 ;

?