MySQL遍历上级组织并给组织向上排序

业务 场景：

表中存有上级组织 字段，组织负责人字段，现需返回当前组织负责人及其所有上级组织的负责人，  并以当前组织排序为1，上级组织排序为2，以此类推。

实现方法如下：

DROP PROCEDURE IF EXISTS pt_structure_getprincipal;
CREATE  PROCEDURE  pt_structure_getprincipal(
v_stru_id varchar(80) -- 组织id
)
SQL SECURITY INVOKER
BEGIN
DECLARE lev INT;
SET lev=1;

-- 创建结果临时表
DROP  TABLE IF EXISTS temp_pt_structure;
CREATE  TABLE temp_pt_structure(
stru_id VARCHAR(36),
org_name VARCHAR(100),
parent_stru_id VARCHAR(36),
principal_staff_id VARCHAR(36),
lev INT
) engine=memory;

INSERT INTO temp_pt_structure(stru_id,org_name,parent_stru_id,principal_staff_id,lev)
  SELECT stru_id,org_alias,parent_stru_id,principal_staff_id,0 
  FROM pt_structure 
  WHERE stru_id=v_stru_id;
 
INSERT INTO temp_pt_structure(stru_id,org_name,parent_stru_id,principal_staff_id,lev)
  SELECT ps.stru_id,ps.org_alias,ps.parent_stru_id,ps.principal_staff_id,1
  FROM pt_structure ps
  JOIN temp_pt_structure tps ON ps.stru_id=tps.parent_stru_id;
  
-- 遍历上级组织
WHILE ROW_COUNT()>0 
 DO SET lev=lev+1;
 INSERT INTO temp_pt_structure(stru_id,org_name,parent_stru_id,principal_staff_id,lev)
  SELECT ps.stru_id,ps.org_alias,ps.parent_stru_id,ps.principal_staff_id,lev
  FROM pt_structure ps
  JOIN temp_pt_structure tps ON ps.stru_id=tps.parent_stru_id  AND tps.lev=lev-1;
END WHILE;
SELECT principal_staff_id,(@rownum:=@rownum+1) num FROM temp_pt_structure JOIN (select @rownum:=0) t;

DROP  TABLE temp_pt_structure;
END;