mysql树查询及分层级

drop procedure proc_fi_deptment;
DELIMITER //
create PROCEDURE proc_fi_deptment()
BEGIN
/* 标记是否出错 /
declare t_error int default 0;
/* 标记是否跳出循环 /
DECLARE flag int default 1;
#层级
DECLARE cj int default 0;
#下层级
DECLARE cjnext int default 0;
#个数
DECLARE sl int default 0;

/** 如果出现sql异常，则将t_error设置为1后继续执行后面的操作 */  declare continue handler for sqlexception set t_error=1; # 出错处理# 开始事务START TRANSACTION;

#创建未分层级的临时表
delete from ofs_fi_department_temporary ;
insert into ofs_fi_department_temporary
SELECT A.KOKRS,
B.SPRAS,
A.PRCTR DEPT_CODE,
B.KTEXT DEPT_NAME,
A.ZWDLXBM DEPT_TYPE_CODE,
B.ZWDLXMC DEPT_TYPE_NAME,
A.ZZHPRCTR PARENT_DEPT_CODE,
A.DATAB,
A.DATBI,
A.LOCK_IND LOCK_IND,
A.DELFG,
A.LASTUPDATE,
null
FROM sap_fi_network A
LEFT JOIN sap_fi_network_children B
ON A.KOKRS = B.KOKRS
AND A.PRCTR = B.PRCTR
AND A.DATBI = B.DATBI
WHERE A.DELFG IS NULL
AND SYSDATE() BETWEEN A.DATAB AND A.DATBI ;

update ofs_fi_department_temporary ofd set ofd.`level` = 0,ofd.PARENT_DEPT_CODE = nullwhere ofd.DEPT_CODE = '001';create or replace table ofs_fi_department as select * from ofs_fi_department_temporary where DEPT_CODE= '001';ALTER TABLE `ofs_fi_department`ADD INDEX `DEPT_CODE` (`DEPT_CODE`),ADD INDEX `PARENT_DEPT_CODE` (`PARENT_DEPT_CODE`),ADD INDEX `level` (`level`);while flag = 1 do    select count(ofd1.DEPT_CODE) into sl from ofs_fi_department ofd1 where ofd1.`level`=cj ;    if sl > 0 then       set cjnext = cj+1;       insert into ofs_fi_department        select * from ofs_fi_department_temporary ofd2 where ofd2.PARENT_DEPT_CODE in (select ofd3.DEPT_CODE from ofs_fi_department ofd3 where ofd3.`level` = cj);        update ofs_fi_department ofd4 set ofd4.`level` = cjnext where ofd4.`level` is null;       commit;        set cj = cj+1;    else         set flag =0;    end if;end while; if t_error=1 then     rollback; -- 事务回滚  else      commit; -- 事务提交  end if;  

END ;
//
DELIMITER ;

call proc_fi_deptment();