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;