Oracle Table函數詳解

一、概覽

Table函數是Oracle中一種高級SQL操作，它可以將複雜的表達式轉換成虛擬表來供查詢使用。使用Table函數，可以作為輸入多個行，返回一張臨時表。Table函數可以用在select, from, where, group by語句中，具有很高的靈活性。

Table函數的基本語法如下：

SELECT ...
FROM TABLE(function_name(...))
WHERE ...
GROUP BY ...

二、基於單個表的Table函數

Table函數可以用於單個表的查詢，它可以將多個列的值組合成一行並返回結果。

1、將多行轉換成一列

將多行數據合併成一個列表示的情況很常見，例如將一張表中所有城市名字列成一個分號分隔的字元串。可以使用LISTAGG函數結合Table函數來實現：

SELECT department_id, LISTAGG(last_name, ';') WITHIN GROUP (ORDER BY last_name) employees
FROM (SELECT department_id, last_name FROM employees ORDER BY department_id)
GROUP BY department_id;

該代碼片段將會返回一個包含每個部門中人員名單的一張表，以分號作為分隔符，多個名字用分號隔開：

DEPARTMENT_ID | EMPLOYEES
------------- | ------------------------
10               | Chen, Gietz, Higgins
20               | Baida, Colmenares, Partners, Sciarra, Vargas
30               | Feeney, Fripp, Kaufling, OConnell, Raphaely, Taylor, Zlotkey
40               | Mavris, Sullivan
50               | Atkinson, Bell, Bloom, Bull, Fox, Greene, ...
60               | Austin, Ernst, Lorentz, Pataballa, Sarchand, Urman
70               | Baer, Friebel, Hunold, King, Klein, ...
80               | Amin, Chen, Davalle, ...

2、將多列轉換成一行

有時候，將多列轉換成一行後進行比較更方便，可以使用UNPIVOT函數將行轉換成列，然後再結合Table函數將多列轉換成一行。下面的代碼演示了如何將多個字元串列轉換成一列並篩選出包含字母a的行：

SELECT *
FROM (
  SELECT ROW_NUMBER() OVER (ORDER BY NULL) AS ID, t.*
  FROM (
    SELECT 'a' AS col1, 'b' AS col2, 'c' AS col3 FROM dual UNION ALL
    SELECT 'd', 'e', 'f' FROM dual UNION ALL
    SELECT 'g', 'h', 'i' FROM dual
  ) tbl
  UNPIVOT (string FOR column_name IN (COL1, COL2, COL3)) t
)
WHERE string LIKE '%a%';

三、基於多個表的Table函數

使用Table函數還可以查詢多個表，可以用於模擬join操作。

1、左連接

左連接是一種將兩個表連接起來的方法，並且保持左邊表的所有記錄。下面的代碼演示了如何使用Table函數進行左連接操作：

SELECT d.department_name, SUM(e.salary) AS total_salary
FROM departments d LEFT OUTER JOIN TABLE(
  CAST(MULTISET(
    SELECT DISTINCT e.employee_id, e.department_id, e.salary
    FROM employees e
  ) AS emp_tab_t)
) sam ON d.department_id = sam.department_id
GROUP BY d.department_name;

該代碼片段將會返回部門名稱及其總薪資：

DEPARTMENT_NAME | TOTAL_SALARY
----------------- | ---------------
Administration       | 44400
Marketing               | 10900
Purchasing             | 24900
Shipping                  | 45400
IT                            | 17600
...

2、內連接

內連接是一種只保留兩個表中都存在的記錄的鏈接方式。下面的代碼演示了如何使用Table函數進行內連接操作：

SELECT d.department_name, COUNT(e.employee_id) AS num_of_employees
FROM departments d JOIN TABLE(
  CAST(MULTISET(
    SELECT DISTINCT e.employee_id, e.department_id
    FROM employees e
  ) AS emp_tab_t)
) sam ON d.department_id = sam.department_id
GROUP BY d.department_name;

該代碼片段將會返回每個部門的總人數：

DEPARTMENT_NAME | NUM_OF_EMPLOYEES
----------------- | ----------------
Administration       | 3
Marketing               | 2
Purchasing             | 6
Shipping                  | 13
IT                            | 5
...

四、高級Table函數應用

除了基本的查詢操作外，Table函數還可以應用於各種高級場景，下面是一些示例：

1、動態拆分字元串

為了實現動態拆分字元串，可以使用Table函數。下面的代碼演示了如何將一個逗號分隔的字元串拆分成單獨的字元：

SELECT * 
FROM TABLE(
  CAST(mypkg.split_string('Al,John,Bob,Steve', ',') AS string_tab_t)
);

調用split_string函數，該函數接收要拆分的字元串和分隔符，返回包含拆分結果的Table虛擬表。執行以上代碼片段後，將會返回如下結果：

COLUMN_VALUE
---------------
Al
John
Bob
Steve

2、分解表達式

有時候，需要將複雜的表達式分解成多個值進行計算，可以使用Table函數將表達式分解成多個行：

SELECT * FROM TABLE(
  CAST(MULTISET(
    SELECT REGEXP_SUBSTR('1+2*3', '[^*+]+', 1, LEVEL) AS val
    FROM dual
    CONNECT BY REGEXP_SUBSTR('1+2*3', '[^*+]+', 1, LEVEL) IS NOT NULL
  ) AS string_tab_t)
);

執行以上代碼片段後，將會返回如下結果：

VAL
----
1
2
3

3、將值分組

有時候，需要將值分組後進行計算，可以使用Table函數將值分組：

SELECT mypkg.group_concat('a,b,c,d,e,f,g,h', 2) string FROM dual;