Oracle之with as和update用法

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;

• 附：
  MariaDB之with as和update用法：http://dba.qishuo.xin/?p=1711