表值函数是一个可以在SELECT......FROM子句后调用的函数，返回的是一个集合类型，后续可以通过TABLE子句转换为可以在SQL语句中处理的行/列数据。

适用场景

表值函数适用于以下场景，可以很大程度简化操作：

  • 合并特定会话中的数据表。例如，在SQL语句中可以合并两个放在不同表中的数据。
  • 用编程的方式构造数据集，以某个固定的格式传递给用户终端。如果需要构造某些数据，表值函数可以直接通过函数生成构造好的数据，而省去了构造关系表。
  • 使用流水线函数提高并行查询的性能。FROM子句中的表值函数可以将查询序列化，得到更高的查询性能。

创建表值函数

  1. 将函数的RETURN类型定义为集合类型（通常是嵌套表或者可变数组，但是某些情况下也可使用关联数组）。
    说明 该类型必须在数据库级别定义（通过CREATE TYPE命令），或者在包中定义（针对于流水线函数）。
  2. 确保函数的所有参数类型都是IN模式，并且是SQL命令兼容的类型。例如，SQL中不允许输入RECORD类型。
  3. 在TABLE子句中嵌入对函数的调用。

示例

创建表值函数
  1. 创建嵌套表。
    CREATE OR REPLACE TYPE polar_strings IS TABLE OF VARCHAR2 (100);
  2. 创建表值函数，返回上述嵌套表类型。
    CREATE OR REPLACE FUNCTION get_random_str (count_in IN INTEGER)
RETURN polar_strings
IS
item   polar_strings := polar_strings ();
BEGIN
item.EXTEND (count_in);
FOR i IN 1 .. count_in
LOOP
item (i) := DBMS_RANDOM.string ('u', 10);
END LOOP;
RETURN item;
END;
  3. 在PL/SQL中调用表值函数生成一系列字符。
    DECLARE
item   polar_strings := get_random_str (5);
BEGIN
FOR i IN 1 .. item.COUNT
LOOP
DBMS_OUTPUT.put_line (item (i));
END LOOP;
END;
    返回结果如下：
    CIDKUKWNMV
GRSNSGJULU
XXCMTMLYUI
YWQDIMNEZA
BHTWWLCGFN
使用表值函数
  • 调用get_random_str表值函数，传入的参数值为5，返回5个随机的字符串。
    SELECT rs.COLUMN_VALUE my_string FROM TABLE (get_random_str (5)) rs
    返回结果如下：
     my_string
------------
 JAFSOSYOUA
 VNWSAAAHNA
 MAEDHVHLIU
 PRWUJLPKZJ
 MWZKQZKQJZ
(5 行记录)
  • 调用get_random_str表值函数，显式指定表值函数的参数值count_in，返回5个随机的字符串。
    SELECT COLUMN_VALUE my_string FROM TABLE (get_random_str (count_in => 5))
    返回结果如下：
    my_string
------------
 TRHYTVPPOU
 DJFDIYAYAF
 BKJOYQFAJR
 YCIIBEFSVT
 OYCUDMUDMX
(5 行记录)
  • 调用get_random_str表值函数，返回5个随机字符串。再调用SUM和AVG两个聚合函数计算出返回随机字符串的总长度和平均长度。
    SELECT SUM (LENGTH (COLUMN_VALUE)) total_length,
       AVG (LENGTH (COLUMN_VALUE)) average_length
  FROM TABLE (get_random_str (5))
    返回结果如下：
     total_length |   average_length
--------------+---------------------
           50 | 10.0000000000000000
(1 行记录)