資料表值函式 - PolarDB

資料表值函式是一個可以在SELECT......FROM子句後調用的函數，返回的是一個集合類型，後續可以通過TABLE子句轉換為可以在SQL語句中處理的行/列資料。

適用情境

資料表值函式適用於以下情境，可以很大程度簡化操作：

合并特定會話中的資料表。例如，在SQL語句中可以合并兩個放在不同表中的資料。
用編程的方式構造資料集，以某個固定的格式傳遞給使用者終端。如果需要構造某些資料，資料表值函式可以直接通過函數產生構造好的資料，而省去了構造關係表。
使用流水線函數提高並行查詢的效能。FROM子句中的資料表值函式可以將查詢序列化，得到更高的查詢效能。

建立資料表值函式

將函數的RETURN類型定義為集合類型（通常是巢狀表格或者可變數組，但是某些情況下也可使用關聯陣列）。
說明該類型必須在資料庫層級定義（通過CREATE TYPE命令），或者在包中定義（針對於流水線函數）。
確保函數的所有參數類型都是IN模式，並且是SQL命令相容的類型。例如，SQL中不允許輸入RECORD類型。
在TABLE子句中嵌入對函數的調用。

樣本

建立資料表值函式

建立巢狀表格。

CREATE OR REPLACE TYPE polar_strings IS TABLE OF VARCHAR2 (100);

建立資料表值函式，返回上述巢狀表格類型。

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;

在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 行記錄)