mysql组织机构本下级查询示例 存储过程

DROP PROCEDURE  IF EXISTS  findOrgList;

CREATE PROCEDURE `findOrgList`(IN orgId VARCHAR(32))
BEGIN
  DROP TEMPORARY TABLE IF EXISTS tmp_org;
    -- 创建临时表
    CREATE TEMPORARY TABLE tmp_org(org_id VARCHAR(32), org_name varchar(150));
    -- 清空临时表数据
    DELETE FROM tmp_org;
    -- 发起调用
    CALL findOrgChildList(orgId);
    -- 从临时表查询结果
    SELECT org_id,org_name FROM tmp_org ORDER BY org_id;
END


DROP PROCEDURE  IF EXISTS  findOrgChildList;
CREATE PROCEDURE `findOrgChildList`(IN orgId VARCHAR(32))
BEGIN
  DECLARE dept_id VARCHAR(32) DEFAULT '';
	DECLARE dept_name VARCHAR(150) DEFAULT '';
  DECLARE done INTEGER DEFAULT 0;
    -- 查询结果放入游标中
  DECLARE C_org CURSOR FOR SELECT d.id,d.depart_name
                           FROM sys_depart d
                           WHERE d.parent_id = orgId;
  DECLARE CONTINUE HANDLER FOR NOT found SET done=1;
   /* 最高允许递归数 */
	SET @@max_sp_recursion_depth = 10;
    
    -- 传入的组织id写入临时表，从这里给临时表插入数据，最终查询的为本级和下级的数据
-- INSERT INTO tmp_org VALUES (orgId,dept_name);
  OPEN C_org;
  FETCH C_org INTO dept_id,dept_name;
  WHILE (done=0)
  DO
    -- 传入的组织id写入临时表，从这里给临时表插入数据，最终查询的只有下级的数据
  INSERT INTO tmp_org VALUES (dept_id,dept_name); 
			 -- 递归调用，查找下级
    CALL findOrgChildList(dept_id);
    FETCH C_org INTO dept_id,dept_name;
  END WHILE;
  CLOSE C_org;
END```