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 = null

where 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();