Home >Database >Mysql Tutorial >mysql存储过程递归调用发作树数据

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

WBOY
WBOYOriginal
2016-06-07 16:27:081515browse

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 ;

?

Statement:
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn