[FGL] 列出树状表所有节点路径的思路与实作

在ERP体系内，除了一般表格之外，就剩下树状结构了：部门组织、产品结构、销售网路等等，都是透过标定上阶与下阶来描述『树状结构』。

树状资料，如果透过拆阶储存在资料表上，会有阅读不易的状况。这个可以透过一些程序技巧来组合列出，更可以将展出的资料暂时储存，减少资料库重复操作展开的工作，降低不必要的负担。

表格设计

此处描述的应该是一般人所熟悉的树状结构表：例如：本阶节点001 / 父阶节点002 讲究的 (想让每棵树可以对树枝、树叶取相同名字的时候) 还可以将根结点另存 (例如栏位 000)

根结点 ed000
本阶node ed001
父阶node ed002

透过设定後，则识别为 root 以及各阶的资料如下

取得这样的结构後，可以从表格中很容易选出树根及各阶节点，但是对於树型，是难以理解的。

传统的递回 recursive 作法

会利用 FGL可做 FUNCTION recursive呼叫的方式，逐层展开，进入抓取资料，例如：

#据点展开 使用 Genero 4.00语法
PUBLIC FUNCTION site_expand(lc_ed000 VARCHAR(20),lc_ed002 VARCHAR(20)) RETURNS () 
DEFINE ls_sql  STRING
DEFINE lc_ed001 VARCHAR(20)
DEFINE li_cnt  INT
   LET ls_sql = "SELECT ed001 FROM 树状表 WHERE ed000=? AND ed002= ? "
   #不存在阵列里的, 填入才能往下展
   DECLARE cs CURSOR FROM ls_sql
   FORECAH cs USING lc_ed000,lc_ed002 INTO lc_ed001
      SELECT COUNT(1) INTO li_cnt FROM 树状表 WHERE ed000=lc_ed000 AND ed002=lc_ed001
      IF li_cnt > 0 THEN
         CALL site_expand(lc_ed000,lc_ooed001)   #重复呼叫自己 recursive
      END IF
   END FOREACH
END FUNCTION

这种方式，透过不断的触发查询，进而将树展开的做法 (也可以改成透过手动) 比较耗费资料库与 FGL主机的资源，而且必须透过字串的组合，才能看出实际上的路径样式。程序写法上也较为复杂。优点是所有逻辑能掌控在自己手中。

使用 ORACLE function: sys_connect_by_path

其实递回可以交给 ORACLE 做

#LEVEL是阶层隔了几层?  path 就是展开後的路径，父阶栏位是 ed002，用反斜线分隔
   LET ls_sql = "SELECT unique LEVEL, sys_connect_by_path(ed001,'/') as path ",
                 " FROM 树状表 ",
                " WHERE ed001<>ed002 ",         #如果本表中包含其他资料，利用此条件滤除
                " START WITH ed001='TREE-A' ",  #条件1:要做哪一棵根节点的条件
              " CONNECT BY PRIOR ooed001 = ooed002 " #条件2:子与父之间的关系是哪一条，找出的条件
   DECLARE cs CURSOR FROM ls_sql

ORACLE 9i之後，开始提供 sys_connect_by_path function，可以交由 ORACLE将整棵树的结构展出来。

WHERE条件中，因为 ORACLE sys_connect_by_path function 无法接受父阶=子阶的资料，因此需要将上方此类资料进行排除，ORACLE才能进行资料检索。

CONNECT 判断的部分：摘录 https://www.itread01.com/content/1549039892.html
提到：可做正树 (从根到叶)，还是倒树(从叶到根), 关键在於connect by的条件.
正树: 必须是 ‘父’= prior ‘子’
倒树: 必须是 ‘子’= prior ‘父’

最终选出的资料就会是

          3   /TREE-A/brh-A1/brh-A11
          2   /TREE-A/brh-A1
          2   /brh-A1/brh-A11
          1   /TREE-A
          1   /brh-A1
          1   /brh-A11

透过这样的方式，就会对树状的结构，比较清晰。

[更新注记] 10/07更新范例程序码