mysql存储过程实现树结构查询

定义存储过程：

1、通过sql递归获取数结构数据，并将数据写入临时表；

DELIMITER //
CREATE PROCEDURE findLChild(IN orgId VARCHAR(32),IN layer INT(4))
BEGIN
	-- ----------------------------中间变量-------------------------------------------
	DECLARE v_orgid varchar(32) default '';
	DECLARE v_orgfid varchar(32) default '';
	DECLARE v_orgname varchar(32) default '';
	DECLARE v_orgcode varchar(32) default '';
	DECLARE v_sort int default 0;
	DECLARE done INTEGER default 0;
    -- 查询结果放入游标中
  DECLARE C_org CURSOR FOR 
					SELECT organ_id,organ_fid,organ_name,order_sort,organ_code 
					FROM t_rsgl_order 
					WHERE organ_fid = orgId;
  DECLARE CONTINUE HANDLER FOR NOT found SET done=1;
  
	SET @@max_sp_recursion_depth = 100;
	
  OPEN C_org;
  FETCH C_org INTO v_orgid,v_orgfid,v_orgname,v_sort,v_orgcode;
		-- 传入的组织id写入临时表
		IF layer>0 THEN
		WHILE done<>1 DO
			-- 递归调用，查找下级
			CALL findOrgChildList(v_orgid,layer-1);
			INSERT INTO tmp_org VALUES (v_orgid,v_orgfid,v_orgname,v_sort,v_orgcode);
			FETCH C_org INTO v_orgid,v_orgfid,v_orgname,v_sort,v_orgcode;
		END WHILE;
		END IF;
  CLOSE C_org;
END

2、获取临时表数据，输入参数orgId为父级id，layer为查询树的层级深度。

DELIMITER //
CREATE PROCEDURE findLChild(orgId VARCHAR(32),layer INT(4))
BEGIN
  DROP TEMPORARY TABLE IF EXISTS tmp_org;
    -- 创建临时表
    CREATE TEMPORARY TABLE 
		tmp_org(organ_id VARCHAR(32),organ_fid VARCHAR(32),organ_name VARCHAR(128),organ_sort INT,organ_code VARCHAR(32));
    -- 清空临时表数据
    DELETE FROM tmp_org;
    -- 发起调用
    CALL findOrgChildList(orgId,layer);
    -- 从临时表查询结果
    SELECT organ_id,organ_fid,organ_name,organ_sort,organ_code FROM tmp_org ORDER BY organ_sort;
END