oracle sql遞歸如何實現

在Oracle SQL中，遞歸查詢是通過使用遞歸公共表達式（Recursive Common Table Expression，簡稱CTE）來實現的。遞歸CTE允許我們對具有層次結構或遞歸關聯的數據進行查詢。

以下是實現Oracle SQL遞歸的步驟：

1. 創建遞歸CTE：首先，我們需要創建一個遞歸CTE。遞歸CTE的基本語法如下：

WITH RECURSIVE cte_name (column1, column2, ...) AS (
  -- 基本查詢（Base case）
  SELECT column1, column2, ...
  FROM table_name
  WHERE condition
  UNION ALL
  -- 遞歸查詢（Recursive case）
  SELECT column1, column2, ...
  FROM table_name
  WHERE condition
)

其中，cte_name是CTE的名稱，column1, column2, ...是CTE中的列名，table_name是包含遞歸關聯數據的表名，condition是用于過濾數據的條件。

2. 基本查詢：在遞歸CTE的基本查詢部分，我們從一個或多個表中檢索數據。這是遞歸的終止條件。

3. 遞歸查詢：在遞歸CTE的遞歸查詢部分，我們再次從相同的表中檢索數據，并使用UNION ALL將其與基本查詢的結果合并。為了實現遞歸，我們需要使用一個或多個列來表示數據之間的層次關系。

4. 查詢遞歸CTE：創建遞歸CTE后，我們可以像查詢普通表一樣查詢它。在查詢遞歸CTE時，Oracle會自動處理遞歸，直到滿足終止條件。

以下是一個簡單的示例，說明如何使用遞歸CTE查詢具有層次結構的數據。假設我們有一個包含部門層次結構的公司表（departments），其中每個部門都有一個上級部門ID（parent_id）：

CREATE TABLE departments (
  id NUMBER PRIMARY KEY,
  name VARCHAR2(50),
  parent_id NUMBER
);

INSERT INTO departments (id, name, parent_id)
VALUES (1, 'Head Office', NULL);

INSERT INTO departments (id, name, parent_id)
VALUES (2, 'Sales', 1);

INSERT INTO departments (id, name, parent_id)
VALUES (3, 'Marketing', 1);

INSERT INTO departments (id, name, parent_id)
VALUES (4, 'North', 2);

INSERT INTO departments (id, name, parent_id)
VALUES (5, 'South', 2);

現在，我們可以使用遞歸CTE查詢所有部門及其子部門：

WITH RECURSIVE department_cte (id, name, parent_id, level) AS (
  SELECT id, name, parent_id, 1
  FROM departments
  WHERE parent_id IS NULL
  UNION ALL
  SELECT d.id, d.name, d.parent_id, dept_cte.level + 1
  FROM departments d
  JOIN department_cte dept_cte ON d.parent_id = dept_cte.id
)
SELECT id, name, parent_id, level
FROM department_cte
ORDER BY level, id;

這將返回以下結果：

ID  NAME      PARENT_ID LEVEL
1   Head Office NULL        1
2   Sales     1          2
4   North     2          3
5   South     2          3
3   Marketing 1          2

在這個示例中，我們首先創建了一個名為department_cte的遞歸CTE，它包含部門ID、名稱、上級部門ID和層級。然后，我們在基本查詢中檢索根部門（parent_id為NULL的部門），并在遞歸查詢中檢索其父部門。最后，我們按層級和部門ID對結果進行排序。