Oracle遞歸查詢

一、Oracle遞歸查詢概述

Oracle遞歸查詢是指在Oracle SQL中使用CONNECT BY語句實現遞歸查詢。遞歸查詢可以處理對自身表的查詢，也可以處理對其他表的查詢。使用遞歸查詢可以讓我們輕鬆地查詢樹形結構數據，如組織架構、商品分類等，從而簡化了查詢的複雜度。

二、Oracle遞歸查詢的語法

使用Oracle遞歸查詢需要用到CONNECT BY語句，它的語法結構為：

SELECT ...
FROM table_name
[WHERE condition]
START WITH condition
CONNECT BY [NOCYCLE] condition
[ORDER SIBLINGS BY column_name [, column_name ...]]

其中，
• table_name：要查詢的表名。
• condition：連結表的條件，顯然需要會做基本的SQL查詢。
• START WITH：從指定的記錄開始遞歸查詢。
• CONNECT BY：連結的條件，控制遞歸的執行過程。
• NOCYCLE：防止出現環形遞歸。
• ORDER SIBLINGS BY：兄弟節點排序，這隻有在使用指定列順序顯示子節點的時候才有用。

三、Oracle遞歸查詢的案例分析

Case 1：查詢樹形結構數據

需求：有一個公司組織架構，其中涉及到多個部門和員工。現在需要查詢每個部門的所有員工及其子部門的員工。

假設有以下表結構：departments表保存部門信息，其中DEPARTMENT_ID為部門編號，DEPARTMENT_NAME為部門名稱，PARENT_ID為其上級部門的編號；employees表保存員工信息，其中EMPLOYEE_ID為員工編號，EMPLOYEE_NAME為員工名稱，DEPARTMENT_ID為其所在部門的ID。

CREATE TABLE departments (
    department_id NUMBER(10),
    department_name VARCHAR2(100),
    parent_id NUMBER(10)
);

INSERT INTO departments VALUES (1, '總經理辦公室', NULL);
INSERT INTO departments VALUES (10, '市場部', 1);
INSERT INTO departments VALUES (11, '銷售部', 10);
INSERT INTO departments VALUES (12, '運營部', 10);
INSERT INTO departments VALUES (20, '技術部', 1);
INSERT INTO departments VALUES (21, '研發部', 20);
INSERT INTO departments VALUES (22, '測試部', 20);
INSERT INTO departments VALUES (30, '行政部', 1);
INSERT INTO departments VALUES (31, '人事部', 30);
INSERT INTO departments VALUES (32, '財務部', 30);

CREATE TABLE employees (
    employee_id NUMBER(10),
    employee_name VARCHAR2(100),
    department_id NUMBER(10)
);

INSERT INTO employees VALUES (1001, '張三', 31);
INSERT INTO employees VALUES (1002, '李四', 31);
INSERT INTO employees VALUES (2001, '王五', 21);
INSERT INTO employees VALUES (2002, '趙六', 21);
INSERT INTO employees VALUES (11001, '小明', 11);
INSERT INTO employees VALUES (11002, '小紅', 11);
INSERT INTO employees VALUES (21001, '小李', 21);
INSERT INTO employees VALUES (21002, '小劉', 21);
INSERT INTO employees VALUES (22001, '小白', 22);
INSERT INTO employees VALUES (22002, '小黑', 22);

現在我們可以編寫SQL語句來實現遞歸查詢員工信息：

SELECT d.department_name,
       e.employee_id,
       e.employee_name,
       LEVEL
FROM departments d
LEFT JOIN employees e ON d.department_id = e.department_id
START WITH d.department_id = 1
CONNECT BY PRIOR d.department_id = d.parent_id
ORDER SIBLINGS BY d.department_id, e.employee_id;

這個SQL語句由以下幾部分組成：

• 首先是SELECT子句，用於選擇要返回的列。
• 然後是FROM子句，聯結了departments表和employees表，在後續的遞歸查詢中用到。
• 接着是START WITH關鍵字，表示從哪個節點開始遞歸查詢，這裡從根節點1開始。
• 然後是CONNECT BY關鍵字，表示查詢條件，即兩張表之間的連結條件。
• PRIOR關鍵字是一個佔位符，指代上一級關係的行。
• ORDER SIBLINGS BY關鍵字用於根據指定列對同一父節點的子節點進行排序。

查詢結果如下：

DEPARTMENT_NAME    EMPLOYEE_ID   EMPLOYEE_NAME    LEVEL
總經理辦公室       NULL         NULL              1
人事部             1001         張三              2
人事部             1002         李四              2
市場部             NULL         NULL              2
市場部             11001        小明              3
市場部             11002        小紅              3
運營部             NULL         NULL              3
技術部             NULL         NULL              2
研發部             2001         王五              3
研發部             2002         趙六              3
測試部             NULL         NULL              3
行政部             NULL         NULL              2
財務部             NULL         NULL              2

Case 2：查詢備選關係

需求：有一批供應商有備選關係，即可用B替代A、C替代B、D替代C，現在需要查詢所有備選關係。

假設有以下備選關係表:

CREATE TABLE supplier_relationship (
    id NUMBER(10),
    src_id NUMBER(10),
    tgt_id NUMBER(10)
);

INSERT INTO supplier_relationship VALUES (1, 1, 2);
INSERT INTO supplier_relationship VALUES (2, 2, 3);
INSERT INTO supplier_relationship VALUES (3, 3, 4);

現在我們可以編寫SQL語句來實現遞歸查詢備選關係信息：

WITH supplier_cte(id, src_id, tgt_id, recursion_level) AS (
  SELECT id, src_id, tgt_id, 0 FROM supplier_relationship 
  UNION ALL 
  SELECT s.id, s.src_id, cte.tgt_id, cte.recursion_level + 1 
	FROM supplier_cte cte JOIN supplier_relationship s ON cte.src_id = s.tgt_id
	WHERE cte.recursion_level < 3 
) 
SELECT DISTINCT src_id, tgt_id FROM supplier_cte;

這個SQL語句由以下幾部分組成：

• 使用WITH關鍵字創建CTE，suppler_cte是遞歸查詢的佔位符。
• 在初始查詢中，我們選擇了所有行，並把它們的Recursion_Level設為0。
• UNION ALL連接了兩個SELECT語句。在第二個SELECT語句中，我們添加了更深一層的層次，並將Recursion_Level加1。
• WHERE子句中的cte.recursion_level<3表示遞歸深度小於3時才進行下一次遞歸。

查詢結果如下：

SRC_ID  TGT_ID
1       2
2       3
3       4
1       3
2       4
1       4

四、Oracle遞歸查詢的注意事項

1、遞歸查詢存在性能問題。在大量數據的情況下，遞歸查詢可能會出現性能問題，建議在執行查詢時限定遞歸的深度。

2、避免環形遞歸。在編寫遞歸查詢時，應該注意依賴關係的循環問題，防止出現環形遞歸。

3、使用時應該根據需求優化SQL語句。Oracle遞歸查詢雖然功能強大，但我們在使用的時候一定要根據需求對SQL語句進行優化，保證查詢效率。

五、總結

Oracle遞歸查詢可以輕鬆地查詢樹形結構數據，從而簡化了查詢的複雜度。在編寫遞歸查詢時，我們應該注意依賴關係的循環問題，防止出現環形遞歸，並對SQL語句進行優化，保證查詢效率。同時，我們也可以將遞歸查詢用於查詢備選關係，這也是Oracle遞歸查詢的一種十分實用的應用場景。

原創文章，作者：小藍，如若轉載，請註明出處：https://www.506064.com/zh-hk/n/179981.html