Oracle中的WITH AS語句，也被稱為Common Table Expressions（CTE），是一個用于定義臨時結果集或視圖的子句。這個臨時結果集或視圖只在當前的查詢中存在，并且在查詢完成后會被自動刪除。使用WITH AS可以提高SQL語句的可讀性，并且可以優化查詢性能，特別是在處理復雜的子查詢時。

基本語法

WITH 臨時視圖名 AS (  SELECT ... FROM ... WHERE ...  
)  
SELECT ... FROM 臨時視圖名 ...;

用法

1. 簡單使用：
   假設我們有一個名為employees的表，我們想要從中選擇前10個員工的信息，并對結果進行排序。使用WITH AS可以簡化查詢語句：

WITH top_employees AS (  SELECT * FROM employees WHERE ROWNUM <= 10  
)  
SELECT * FROM top_employees ORDER BY last_name;

1. 多個CTE：
   可以在一個查詢中定義多個CTE，并且后面的CTE可以引用前面已經定義的CTE。

WITH   emp_dept AS (  SELECT department_id, AVG(salary) AS avg_salary  FROM employees  GROUP BY department_id  ),  high_paid_depts AS (  SELECT department_id  FROM emp_dept  WHERE avg_salary > 5000  )  
SELECT d.department_name  
FROM departments d  
JOIN high_paid_depts h ON d.department_id = h.department_id;

1. 遞歸CTE：
   用于處理具有層次結構或遞歸關系的數據。

WITH RECURSIVE emp_hierarchy (employee_id, employee_name, manager_id, level) AS (  SELECT employee_id, employee_name, manager_id, 1 AS level  FROM employees  WHERE manager_id IS NULL -- 頂層員工  UNION ALL  SELECT e.employee_id, e.employee_name, e.manager_id, eh.level + 1  FROM employees e  JOIN emp_hierarchy eh ON e.manager_id = eh.employee_id  
)  
SELECT * FROM emp_hierarchy;

優點

• 提高可讀性：通過將復雜的子查詢分解為多個CTE，可以使查詢結構更加清晰。
• 優化性能：CTE的結果集可以存儲在內存中，當多次引用該結果集時，可以避免重復計算。
• 靈活性：CTE可以在SELECT、INSERT、UPDATE和DELETE語句中使用，并且可以在一個CTE中引用另一個CTE。

總的來說，WITH AS語句在Oracle中是一個強大的工具，可以幫助我們更好地組織和管理復雜的SQL查詢。