1. 更新本级部门名称全路径
BEGIN
FOR se IN (SELECT epid FROM sc_enterprice WHERE epid > 0) LOOP
UPDATE sc_accdep x
SET x.namepath =(WITH depinfo
AS (SELECT a.epid,
a.accdepid,
sys_connect_by_path(a.accdepname,'/') AS depNamePath
FROM (SELECT * FROM sc_accdep WHERE epid = se.epid) a
START WITH a.parentid = -1
CONNECT BY PRIOR a.accdepid = a.parentid)
SELECT y.depNamePath
FROM depinfo y
WHERE x.epid = y.epid
AND x.accdepid = y.accdepid)
WHERE x.epid = se.epid;
END LOOP;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
RAISE_APPLICATION_ERROR('-20002','脚本执行异常:' || SQLERRM);
END;
2. 更新上级部门编号全路径
UPDATE sc_accdep x
SET x.idpath =(WITH depinfo
AS (SELECT a.epid,
a.accdepid,
sys_connect_by_path(a.accdepid,'/') AS idpath
FROM (SELECT * FROM sc_accdep WHERE epid = 1) a
START WITH a.parentid = -1
CONNECT BY PRIOR a.accdepid = a.parentid)
SELECT SUBSTR(y.idpath,1,instr(y.idpath,'/',-1)) as idpath
FROM depinfo y
WHERE x.epid = y.epid
AND x.accdepid = y.accdepid)
WHERE x.epid = 1;
COMMIT;
Post Views: 2,723