具有遞迴 CTE 的 Oracle CONNECT BY 功能

Oracle 的 CONNECT BY 功能提供了許多有用且非常重要的功能，這些功能在使用 SQL 標準遞迴 CTE 時不是內建的。此示例使用 SQL Server 語法複製這些功能（為了完整性，新增了一些功能）。對於 Oracle 開發人員來說，在其他資料庫的分層查詢中發現許多缺少的功能是最有用的，但它也可以用來展示分層查詢通常可以做些什麼。

  WITH tbl AS (
       SELECT id, name, parent_id
         FROM mytable)
     , tbl_hierarchy AS (
       /* Anchor */
       SELECT 1 AS "LEVEL"
            --, 1 AS CONNECT_BY_ISROOT
            --, 0 AS CONNECT_BY_ISBRANCH
            , CASE WHEN t.id IN (SELECT parent_id FROM tbl) THEN 0 ELSE 1 END AS CONNECT_BY_ISLEAF
            , 0 AS CONNECT_BY_ISCYCLE
            , '/' + CAST(t.id   AS VARCHAR(MAX)) + '/' AS SYS_CONNECT_BY_PATH_id
            , '/' + CAST(t.name AS VARCHAR(MAX)) + '/' AS SYS_CONNECT_BY_PATH_name
            , t.id AS root_id
            , t.*
         FROM tbl t
        WHERE t.parent_id IS NULL                            -- START WITH parent_id IS NULL
       UNION ALL
       /* Recursive */
       SELECT th."LEVEL" + 1 AS "LEVEL"
            --, 0 AS CONNECT_BY_ISROOT
            --, CASE WHEN t.id IN (SELECT parent_id FROM tbl) THEN 1 ELSE 0 END AS CONNECT_BY_ISBRANCH
            , CASE WHEN t.id IN (SELECT parent_id FROM tbl) THEN 0 ELSE 1 END AS CONNECT_BY_ISLEAF
            , CASE WHEN th.SYS_CONNECT_BY_PATH_id LIKE '%/' + CAST(t.id AS VARCHAR(MAX)) + '/%' THEN 1 ELSE 0 END AS CONNECT_BY_ISCYCLE
            , th.SYS_CONNECT_BY_PATH_id   + CAST(t.id   AS VARCHAR(MAX)) + '/' AS SYS_CONNECT_BY_PATH_id
            , th.SYS_CONNECT_BY_PATH_name + CAST(t.name AS VARCHAR(MAX)) + '/' AS SYS_CONNECT_BY_PATH_name
            , th.root_id
            , t.*
         FROM tbl t
              JOIN tbl_hierarchy th ON (th.id = t.parent_id) -- CONNECT BY PRIOR id = parent_id
        WHERE th.CONNECT_BY_ISCYCLE = 0)                     -- NOCYCLE
SELECT th.*
     --, REPLICATE(' ', (th."LEVEL" - 1) * 3) + th.name AS tbl_hierarchy
  FROM tbl_hierarchy th
       JOIN tbl CONNECT_BY_ROOT ON (CONNECT_BY_ROOT.id = th.root_id)
 ORDER BY th.SYS_CONNECT_BY_PATH_name;                       -- ORDER SIBLINGS BY name

上面演示了 CONNECT BY 功能，並附有說明：

• 條款
  • CONNECT BY：指定定義層次結構的關係。
  • START WITH：指定根節點。
  • 訂購 SIBLINGS BY：正確訂購結果。
• 引數
  • NOCYCLE：檢測到迴圈時停止處理分支。有效層次結構是有向非迴圈圖，迴圈引用違反此構造。
• 運算子
  • PRIOR：從節點的父節點獲取資料。
  • CONNECT_BY_ROOT：從節點的根獲取資料。
• 偽列
  • LEVEL：表示節點與其根的距離。
  • CONNECT_BY_ISLEAF：表示沒有子節點的節點。
  • CONNECT_BY_ISCYCLE：表示具有迴圈引用的節點。
• 功能
  • SYS_CONNECT_BY_PATH：返回從其根節點到該節點的路徑的展平/連線表示。