(ORA-00024) - Mệnh đề WITH

Mệnh đề WITH trong Oracle là gì?

• Mệnh đề With cho phép gán tên cho khối truy vấn con (Subquery). Sau đó bạn có thể tham chiếu khối truy vấn con đó ở nhiều vị trí trong câu truy vấn bằng cách chỉ định tên truy vấn. Oracle tối ưu hóa truy vấn bằng cách coi tên truy vấn là một dạng xem nội tuyến hoặc là một bảng tạm (temporary table).
• Bạn có thể chỉ định mệnh đề này trong bất kỳ câu lệnh SELECT nào và trong hầu hết các loại truy vấn con.
• Mệnh đề With đơn giản hóa những câu query phức tạp. Vì vậy trong một số trường hợp, dùng mệnh đề With có thể cải thiện được hiệu suất truy vấn.
• Mệnh đề With  được sử dụng tốt nhất khi kết quả của truy vấn dùng mệnh đề With được dùng nhiều hơn 1 lần (> 1) bên trong một câu truy vấn.

Oracle gọi là WITH Syntax (Common Table  Expressions - CTE - tạm dịch là Biểu thức bảng chung) là một tập kết quả tạm thời được đặt tên, tồn tại trong phạm vi một câu lệnh và có thể được dùng nhiều lần trong câu lệnh đó.

WITH
    CTE1 AS (SELECT A, B FROM TABLE1),
    CTE2 AS (SELECT C, C FROM TABLE2)
SELECT B, D FROM CTE1 JOIN CTE2
WHERE CTE1.A = CTE2.C;

Cú pháp:

WITH cte_name [(col_name [, col_name] ...)] AS (subquery)
    [, cte_name [(col_name [, col_name] ...)] AS (subquery)] ...

cte_name: tên của CTE được sử dụng làm tham chiếu đến bảng trong câu lệnh chưa mệnh đề With.
subquery:  được gọi là truy vấn con của CTE và là phần tạo ra tập kết quả của CTE. Subquery phải nằm trong dấu ngoặc đơn.
Đối với col_name được xác định như sau:
- Nếu danh sách các tên được đặt trong dấu ngoặc đơn phía sau cte_name, thì danh sách đó là tên các cột:

SQL> WITH cte (col1, col2) AS
     (
        SELECT 1, 2 FROM DUAL
        UNION ALL
        SELECT 3, 4 FROM DUAL
     )
     SELECT col1, col2 FROM cte;

      COL1       COL2
---------- ----------
         1          2
         3          4


2 rows selected.

Số lượng tên trong danh sách phải bằng số lượng cột trong tập kết quả subquery.'
- Mặt khác ta có thể xác định tên cột dựa trên câu lệnh SELECT đầu tiên trong phần subquery bằng từ khóa AS:

SQL> WITH cte AS
     (
        SELECT 1 AS col1, 2 AS col2 FROM DUAL
        UNION ALL
        SELECT 3, 4 FROM DUAL
     )
     SELECT col1, col2 FROM cte;

      COL1       COL2
---------- ----------
         1          2
         3          4


2 rows selected.

Một số ví dụ:
Đối với mỗi nhân viên, chúng ta muốn biết có bao nhiêu người làm trong bộ phận của họ. Thông thường ta sẽ viết như sau:

SQL> SELECT E1.EMP_NAME, E2.DEPT_COUNT
     FROM EMPLOYEE E1,
     (SELECT DEPT_ID, COUNT(*) DEPT_COUNT
      FROM EMPLOYEE
      GROUP BY DEPT_ID) E2
WHERE E1.DEPT_ID = E2.DEPT_ID;

EMP_NAME                                           DEPT_COUNT
-------------------------------------------------- ----------
ADAMS                                                       7
TURNER                                                      7
CLARK                                                       7
MARTIN                                                      7
WARD                                                        7
ALLEN                                                       7
BLAKE                                                       7
ADAMS                                                       5
SCOTT                                                       5
SMITH                                                       5
FORD                                                        5
JONES                                                       5
MILLER                                                      2
KING                                                        2


14 rows selected.

Nếu như dùng mệnh đề WITH:

SQL> WITH DEPT_COUNT AS
     (SELECT DEPT_ID, COUNT(*) DEPT_COUNT
      FROM EMPLOYEE
      GROUP BY DEPT_ID)
     SELECT E1.EMP_NAME, E2.DEPT_COUNT
     FROM EMPLOYEE E1, DEPT_COUNT E2
     WHERE E1.DEPT_ID = E2.DEPT_ID;

EMP_NAME                                           DEPT_COUNT
-------------------------------------------------- ----------
ADAMS                                                       7
TURNER                                                      7
CLARK                                                       7
MARTIN                                                      7
WARD                                                        7
ALLEN                                                       7
BLAKE                                                       7
ADAMS                                                       5
SCOTT                                                       5
SMITH                                                       5
FORD                                                        5
JONES                                                       5
MILLER                                                      2
KING                                                        2

14 rows selected.

Tiếp theo, sự khác biệt giữa 2 câu lệnh khá đáng kể ở ví dụ này:
Ngoài yêu cầu như ở ví dụ trên, ta muốn biết tên người quản lý mỗi nhân viên đó và số lượng thành viên mà người đó quản lý.

SQL> SELECT E1.EMP_NAME, E2.DEPT_COUNT, E3.EMP_NAME MNG_NAME,               E4.DEPT_COUNT MNG_DEPT_COUNT
     FROM EMPLOYEE E1,
     (SELECT DEPT_ID, COUNT(*) DEPT_COUNT
      FROM EMPLOYEE
      GROUP BY DEPT_ID) E2,
      EMPLOYEE E3,
     (SELECT DEPT_ID, COUNT(*) DEPT_COUNT
      FROM EMPLOYEE
      GROUP BY DEPT_ID) E4
     WHERE E1.DEPT_ID = E2.DEPT_ID
       AND E1.MNG_ID = E3.EMP_ID
       AND E3.DEPT_ID = E4.DEPT_ID;

EMP_NAME   DEPT_COUNT   MNG_NAME   MNG_DEPT_COUNT
---------- ------------- ----------------- ------
ALLEN       7           BLAKE                 7
WARD        7           BLAKE                 7
MARTIN      7           BLAKE                 7
TURNER      7           BLAKE                 7
ADAMS       7           BLAKE                 7
BLAKE       7           KING                  2
CLARK       7           KING                  2
ADAMS       5           BLAKE                 7
SMITH       5           FORD                  5
FORD        5           JONES                 5
SCOTT       5           JONES                 5
JONES       5           KING                  2
MILLER      2           BLAKE                 7

13 rows selected.

Nếu sử dụng mệnh đề WITH, ta có thể gọi lại CTE DEPT_COUNT thay vì phải viết 2 lần câu subquery như ở trên.

SQL> WITH DEPT_COUNT AS
     (SELECT DEPT_ID, COUNT(*) DEPT_COUNT
      FROM EMPLOYEE
      GROUP BY DEPT_ID)
     SELECT E1.EMP_NAME, E2.DEPT_COUNT, E3.EMP_NAME MNG_NAME,                     E4.DEPT_COUNT MNG_DEPT_COUNT
     FROM EMPLOYEE E1, DEPT_COUNT E2, EMPLOYEE E3, DEPT_COUNT E4
     WHERE E1.DEPT_ID = E2.DEPT_ID
       AND E1.MNG_ID = E3.EMP_ID
       AND E3.DEPT_ID = E4.DEPT_ID;

EMP_NAME   DEPT_COUNT   MNG_NAME   MNG_DEPT_COUNT
---------- ------------- ----------------- ------
ALLEN       7           BLAKE                 7
WARD        7           BLAKE                 7
MARTIN      7           BLAKE                 7
TURNER      7           BLAKE                 7
ADAMS       7           BLAKE                 7
BLAKE       7           KING                  2
CLARK       7           KING                  2
ADAMS       5           BLAKE                 7
SMITH       5           FORD                  5
FORD        5           JONES                 5
SCOTT       5           JONES                 5
JONES       5           KING                  2
MILLER      2           BLAKE                 7

13 rows selected.

Từ các ví dụ ở trên, mệnh đề WITH cho phép:
- Chúng ta có thể gọi CTE không giới hạn số lần.
- Chúng ta có thể tạo ra bất kỳ số lượng CTE.
- Một CTE có thể tham chiếu đến một CTE khác.
- Các CTE chỉ có thể được dùng trong nội bộ mệnh đề WITH.

Vì vậy ưu điểm lớn nhất khi sử dụng mệnh đề WITH là khả năng xây dựng các thành phần có thể tái sử dụng bên trong một câu lệnh SELECT.

Ngoài câu lệnh SELECT, ta có thể dùng mệnh đề WITH với các lệnh DML sau:
1. INSERT:
Cú pháp:
INSERT INTO TABLE_NAME(COL1, COL2)
WITH CTE_NAME (COL1, COL2) AS
(SELECT 1, 2 FROM DUAL
 UNION ALL
SELECT 3, 4 FROM DUAL)
SELECT COL1, COL2 FROM CTE_NAME;

2. UPDATE:
Cú pháp:
 UPDATE MYTABLE T
SET Z = (
WITH COMP AS
 (SELECT B.*, 42 AS COMPUTED
  FROM MYTABLE T
  WHERE BS_ID = 1)
 )
SELECT C.COMPUTED
FROM COMP C
WHERE C.ID = T.ID;

3. MERGE:
Cú pháp:
MERGE INTO EMP E USING
(WITH AVERAGE AS
  (SELECT DEPTNO, AVG(SAL) AVG_SAL FROM EMP GROUP BY DEPTNO)
  SELECT * FROM AVERAGE
 ) U
ON (E.DEPTNO = U.DEPTNO)
WHEN MATCHED THEN
   UPDATE SET E.SAL = CASE WHEN E.SAL <= U.AVG_SAL THEN E.SAL*1.05
                                                       ELSE E.SAL*1.03
                                           END;


Hãy tham gia và để lại nhận xét của bạn vào bên dưới. Càng nhiều người tham gia, chúng ta càng được hưởng lợi nhiều =)). Vì vậy, để lại những suy nghĩ của bạn trước khi rời khỏi trang nhé.