Oracle Table Functions |

甚麼是 Table Function? 就是 Function 用起來就像 Table。PL/SQL Function 有什麼作用?

Function

Function 可以返回一個值。該值可以是純量值，例如字符串:

CREATE OR REPLACE FUNCTION longer_string (
  string_in IN VARCHAR2, to_length_in IN INTEGER)
  RETURN VARCHAR2
  AUTHID DEFINER
IS
BEGIN
  RETURN RPAD (string_in, to_length_in, 'x');
END;
/

SQL> select longer_string('Hello Tainan!', 20) as hello from dual;

HELLO
--------------------------------------------------------------------------------
Hello Tainan!xxxxxxx

Function 還可以返回更複雜的數據類型，例如，記錄(record)、甚至集合(collection)。為了展示這一點，首先聲明一個 Schema-level 的 Nested table 類型:

1 2	CREATE OR REPLACE TYPE strings_t IS TABLE OF VARCHAR2(100) /

然後定義一個 function，該 function 返回該類型 (strings_t) 的隨機生成的字符串 (varchar2) 的集合 (collection):

CREATE OR REPLACE FUNCTION random_strings (
  count_in IN INTEGER)
  RETURN strings_t
  AUTHID DEFINER
IS
  l_strings strings_t := strings_t();
BEGIN
  l_strings.EXTEND(count_in);

  FOR indx IN 1 .. count_in
  LOOP
    l_strings(indx) := DBMS_RANDOM.string('u', 10);
  END LOOP;

  RETURN l_strings;
END;
/

這是一個返回集合的函數，我們可以將它當成 Table Function 使用，這意思是，您可以使用 SELECT 語句查詢函數返回值的內容，就像使用一般的 Table 一樣。

SQL> select * from table(random_strings(5));

COLUMN_VALUE
--------------------------------------------------------------------------------
HLSFIPLIWX
ITYWJXMWBD
OTPGAWXFGG
HMNQCQIPKL
EPNLHKYJEG

當然你也可以將它當成一般普通的函式這樣用:

SQL> select random_strings(5) from dual;

RANDOM_STRINGS(5)
--------------------------------------------------------------------------------
STRINGS_T('XJNABIIQLO', 'WTVKOLJHMT', 'JQMOPVDOQE', 'DUOUHJUVHO', 'FFJFVTBWTT')

但在 Oracle 資料庫中這樣的資料可能不是你要的。

Table Functions

Table function 可用在以下的情況中:

合併 Tables 的數據資料
您無法或很難單純的使用 SQL 的功能來獲得一些答案。則可使用 TABLE 子句和 Table Function 來實現。
以程式的方式建構要傳遞到主機環境的數據集
您的網頁需要顯示一些數據。然而，這些數據需要一些複雜的整合。一般的情況，您需要執行一些過程代碼來構造數據集，然後將構造的數據存入一個暫存的 Table (Temporary Table) 中，然後執行 SELECT。但是使用 Table function，您可以立即將數據傳遞到網頁，而無需任何暫存的處理過程。
模擬可參數化的 View (parameterized view)
Oracle database 不支援真正的參數化的 View，如
CREATE OR REPLACE VIEW my_view (param1_in IN INTEGER) ...
您可以使用 system contexts 實現類似的效果，在 WHERE 子句調用 SYS_CONTEXT 函數以從 session 中獲取參數值。

但您也可以使用 Table function 接受參數來模擬可參數化的 View。
限制開發人員對 Tables 的使用權限
如果要嚴格遵循資料庫的資料安全準則，使用者和開發人員都無法直接訪問 Table，即使是 SELECT 權限！對於非查詢 DML (插入，更新，刪除），一值都建議打包成 PL/SQL Package 來執行這些操作。對於查詢 (SELECT)，則可以通過使用 Table function 來實現對 Table 查詢的完全控制。您不必授予 Table 的 SELECT 權限，而是授予 Table function 的 EXECUTE 權限。
執行 Data warehouse 的資料轉換
數據倉儲環境中的一個常見要求是執行轉換 (Transformation)，通常都是從一個 Table 轉換到另一個 Table，如圖:

一種特殊類型的 Table function ，稱為 Streaming table function，可以簡潔優雅的支援這些轉換並兼具高性能。

現在我們看看如何使用 random_strings 函數，既可以作為 PL/SQL 中的 “普通” 函數，也可以作為 Table function。

在下面的 PL/SQL 程式碼中，調用 random_strings 函數，然後使用 DBMS_OUTPUT.PUT_LINE 顯示生成的字符串:

set serveroutput on

DECLARE
  l_strings strings_t := random_strings(5);
BEGIN
  FOR indx IN 1 .. l_strings.COUNT
  LOOP
    DBMS_OUTPUT.put_line(l_strings(indx));
  END LOOP;
END;
/

RNOOEODDOH
LJNWHIUVJA
LFLHRCCURN
ROUGNTXSQU
RJKRTXKGCK

PL/SQL procedure successfully completed.

現在直接在 SELECT 語句中調用該函數：

1
2
3

SELECT COLUMN_VALUE my_string
  FROM TABLE (random_strings(5))
/

MY_STRING
--------------------------------------------------------------------------------
BKYEWJPQJW
CVSINZCLPX
CLFUSFDWOU
MEEQSNYKXW
PANIDFINDJ

這就是 Table function !

Oracle 資料庫中的 SQL 引擎在 table function 方面為我們做了大部分繁重工作。在 SELECT 語句的 TABLE 子句中調用函數時，SQL 引擎會將函數返回的數據集轉換為關係結果集 (Relational result set)。然後就可以像從 Table 或 View 中的結果集那樣操縱該結果集。

當集合類型的每個元素都是單純的量值時（如上面的 strings_t 的情況），該結果集的單個 Column 的名稱將自動設置為 COLUMN_VALUE。當然，您可以使用 column alias 更改它。

當我們從函數返回一個集合 (collection) 時，集合裡面的每個元素 (row) 不會像前面例子，只含單獨的一個欄位(column)，我們需要像 Table 中的每筆資料一樣含有許多的 columns，要達到此目地我們就必須使用物件來達到此目的:

CREATE OR REPLACE TYPE two_strings_ot
  AUTHID DEFINER 
  IS OBJECT
(
  string1 VARCHAR2(10),
  string2 VARCHAR2(10)
)
/

1 2	CREATE OR REPLACE TYPE two_strings_nt IS TABLE OF two_strings_ot /

現在我們可以使用這兩個類型:

CREATE OR REPLACE FUNCTION three_pairs
  RETURN two_strings_nt
  AUTHID DEFINER
IS
  l_strings two_strings_nt;
BEGIN
  RETURN two_strings_nt(
    two_strings_ot('a', 'b'),
    two_strings_ot('c', 'd'),
    two_strings_ot('e', 'f')
  );
END;
/

1 2	SELECT * FROM TABLE (three_pairs()) /

STRING1    STRING2
---------- ----------
a          b
c          d
e          f

一旦在 TABLE 子句中嵌入了函數調用，就可以像使用 Table 或 inline view 中的結果集一樣使用結果集。你可以 join 該集合，可以將 columns 使用於 WHERE 子句中，也可以使用集合運算符 (set operator)。

以下是使用 three_pairs 函數以及一般 Table 的例子。首先我們需要一個簡單的一般 Table：

CREATE TABLE string_pairs (
  string1   VARCHAR2(10),
  string2   VARCHAR2(10)
)
/

BEGIN
  INSERT INTO string_pairs VALUES ('a', 'bb');
  INSERT INTO string_pairs VALUES ('cc', 'dd');
  
  COMMIT;
END;
/

現在使用 UNION ALL 集合運算符(set operator)，組合 Table 和 Table function:

SELECT * FROM string_pairs
UNION ALL
SELECT * FROM TABLE (three_pairs())
/

STRING1    STRING2
---------- ----------
a          bb
cc         dd
a          b
c          d
e          f

接下來，將 Table 和 Table function join 在一起:

SELECT t.string1 string1, t.string2 t_string2, tf.string2 tf_string2
  FROM string_pairs t, 
       TABLE (three_pairs()) tf
 WHERE t.string1 = tf.string1
 ORDER BY string1
/

STRING1  T_STRING2  TF_STRING2
---------- ---------- ----------
a          bb         b

甚至可以隱藏使用 Table function，將它放在 View 中:

CREATE OR REPLACE VIEW three_pairs_v
AS
SELECT * FROM TABLE (three_pairs())
/

SELECT t.string1 string1, t.string2 t_string2, tf.string2 tf_string2
  FROM string_pairs t, 
       three_pairs_v tf
 WHERE t.string1 = tf.string1
 ORDER BY string1
/

STRING1  T_STRING2  TF_STRING2
---------- ---------- ----------
a          bb         b

Table function 為資料庫開發人員提供了靈活性和功能性。你可以使用 Table function 將 SQL 功能與 PL/SQL 的過程控制相結合，以應對各種挑戰。