定义存储过程:
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