具有遞迴 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:返回從其根節點到該節點的路徑的展平/連線表示。