甚麼是 Table Function? 就是 Function 用起來就像 Table。PL/SQL Function 有什麼作用?
Function
Function 可以返回一個值。 該值可以是純量值,例如字符串:
1 | CREATE OR REPLACE FUNCTION longer_string ( |
SQL> select longer_string('Hello Tainan!', 20) as hello from dual; |
Function 還可以返回更複雜的數據類型,例如,記錄(record)、甚至集合(collection)。為了展示這一點,首先聲明一個 Schema-level 的 Nested table 類型:
1 | CREATE OR REPLACE TYPE strings_t IS TABLE OF VARCHAR2(100) |
然後定義一個 function,該 function 返回該類型 (strings_t) 的隨機生成的字符串 (varchar2) 的集合 (collection):
1 | CREATE OR REPLACE FUNCTION random_strings ( |
這是一個返回集合的函數,我們可以將它當成 Table Function 使用,這意思是,您可以使用 SELECT 語句查詢函數返回值的內容,就像使用一般的 Table 一樣。
SQL> select * from table(random_strings(5)); |
當然你也可以將它當成一般普通的函式這樣用:
SQL> select random_strings(5) from dual; |
但在 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 顯示生成的字符串:
1 | set serveroutput on |
RNOOEODDOH |
現在直接在 SELECT 語句中調用該函數:
1 | SELECT COLUMN_VALUE my_string |
MY_STRING |
這就是 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,要達到此目地我們就必須使用物件來達到此目的:
1 | CREATE OR REPLACE TYPE two_strings_ot |
1 | CREATE OR REPLACE TYPE two_strings_nt IS TABLE OF two_strings_ot |
現在我們可以使用這兩個類型:
1 | CREATE OR REPLACE FUNCTION three_pairs |
1 | SELECT * FROM TABLE (three_pairs()) |
STRING1 STRING2 |
一旦在 TABLE 子句中嵌入了函數調用,就可以像使用 Table 或 inline view 中的結果集一樣使用結果集。你可以 join 該集合,可以將 columns 使用於 WHERE 子句中,也可以使用集合運算符 (set operator)。
以下是使用 three_pairs 函數以及一般 Table 的例子。首先我們需要一個簡單的一般 Table:
1 | CREATE TABLE string_pairs ( |
1 | BEGIN |
現在使用 UNION ALL 集合運算符(set operator),組合 Table 和 Table function:
1 | SELECT * FROM string_pairs |
STRING1 STRING2 |
接下來,將 Table 和 Table function join 在一起:
1 | SELECT t.string1 string1, t.string2 t_string2, tf.string2 tf_string2 |
STRING1 T_STRING2 TF_STRING2 |
甚至可以隱藏使用 Table function,將它放在 View 中:
1 | CREATE OR REPLACE VIEW three_pairs_v |
1 | SELECT t.string1 string1, t.string2 t_string2, tf.string2 tf_string2 |
STRING1 T_STRING2 TF_STRING2 |
Table function 為資料庫開發人員提供了靈活性和功能性。 你可以使用 Table function 將 SQL 功能與 PL/SQL 的過程控制相結合,以應對各種挑戰。