一般常見函數回傳為單一純量值，MSSQL 2005以上版本提供資料表值函式 （table-valued function），而ORACLE可用巢狀資料表（Nested
Table）作為函數回傳型態即可回傳運算結果集。先前文章『[2008]
MS SQL 所提供類似ORACLE之RowId 功能 [%%physloc%%]』所使用之fn_PhysLocCracker函數即為資料表值函數其傳回值為資料表。
SQL
|
結果
|
SELECT A.*
, %%physloc%% AS [%%physloc%%]
, C.file_id
, C.page_id
, C.slot_id
FROM Test A
CROSS APPLY fn_PhysLocCracker(%%physloc%%) C
|
|
本範例將延伸『查詢若具有部門代碼時，則列出該部門員工，若無則為全部』開發，此案例並不具實用性，主要是強調資料表函數開發及使用。前以EXEC命令執行預儲程序（Stored
Procedure）以得到結果集，MSSQL支援程序(Procedure)中執行SELECT敍述句並將查詢結果直接傳回，而無需額外處理或設定，傳入參數為部門代碼，傳回則為部門所有員工。如下圖，若欲列出AUTH權限資料表中3個部門中擁有權限及人員時，通常以游標(Cursor)方式以迴圈逐一進行查詢，概念如下圖所示：
權限（Auth)
|
指令(Loop)
|
結果
|
||
|
EXEC ListEmp 'IT'
|
 |
||
EXEC ListEmp 'MA'
|
|
|||
EXEC ListEmp 'HR'
|
|
但此作法與其他SQL的應用/整合上有些許限制，不管是呼叫時之參數傳入或運算結果回傳均需額外處理，難以搭配其他查詢語法使用。一般而言，程序（Procedure）與函數（Fuction）差異在於運算完成後是否具有回傳值，函數定義上必須具有回傳值，程序則否，一般常見函數回傳為單一純量值，MSSQL 2005以上版本提供資料表值函式 （table-valued function），而ORACLE可用巢狀資料表（Nested
Table）作為函數回傳型態即可回傳運算結果集。建立及使用方法如下:
SQL
|
說明
|
|
MSSQL
|
CREATE FUNCTION ListDeptEmp
(
@Dept_No varchar(10)
)
RETURNS @Ret TABLE --1.數@Ret,值型態為Table，定義如下
(
EMP_NO VARCHAR(20) NOT NULL,
EMP_NAME
VARCHAR(25) NOT NULL,
EXT VARCHAR(10) NULL,
DEPT_NO VARCHAR(20)
)
AS
BEGIN
--將結果Insert至回傳變數@Ret
INSERT INTO @Ret
(EMP_NO, EMP_NAME, EXT, DEPT_NO)
SELECT EMP_NO, EMP_NAME, EXT, DEPT_NO
FROM Emp
WHERE 1=1
AND (1=CASE WHEN @Dept_No = ''
THEN 1
ELSE 0
END
OR DEPT_NO =@Dept_No
);
RETURN; --回傳
END
|
ü 變數型態使用Table變數，Schema直接定義於後。
ü 將運算結果直接寫入回傳變數。
ü 由於運算結果直接寫入此FUNCTION所定義之回傳變數中，RETURN關鍵字後為空。
ü 由於運算結果直接寫入此FUNCTION所定義之回傳變數中，RETURN關鍵字後為空。
ü COALESCE(@Dept_No, '') =''
|
ORACLE
|
--1.建立輸出格式Emp_obj(介面)
CREATE OR REPLACE TYPE Emp_obj
AS OBJECT
(
EMP_NO VARCHAR2(10),
EMP_NAME VARCHAR2(10),
EXT VARCHAR2(5),
DEPT_NO VARCHAR2(10)
);
--2.建立回傳資料型態(Nested Table) Emp_tbl
CREATE OR REPLACE TYPE Emp_tbl AS TABLE OF Emp_obj;
--3.建立FUNCTION
CREATE OR REPLACE FUNCTION
ListDeptEmp
(
vDept_No IN
VARCHAR2
) RETURN
Emp_tbl
AS
vRet Emp_tbl;
BEGIN
SELECT
CAST(MULTISET(
----查詢資料--(Start)-------
SELECT EMP_NO, EMP_NAME, EXT, DEPT_NO
FROM Emp
WHERE 1=1
AND (1=CASE WHEN vDept_No IS
NULL THEN
1
ELSE 0
END
OR
DEPT_NO = vDept_No
)
------------(End)---------
) AS Emp_tbl -- 轉換為Emp_tble型態
) -- CAST
INTO vRet -- 寫入vRet變數中
FROM dual; -- SELECT
INTO
RETURN vRet; -- 回傳Nest Table
END;
|
ü 傳回變數型態使用Nested Table，Schema須由外部先定義成物件（Object），再設定Nested Table由此Schema物件組成。
ü PL/SQL中之SELECT INTO
ü MULTISET為pseudo function用以搭配CASTs函數以擷取多筆資料並轉換為集合型式(Collection Type)。
ü CAST(
|
上述SQL中，ORACLE使用集合（Collections）運算，其中將一群資料轉換為集合之特殊語法，如下：
SELECT CAST(MULTISET(SELECT field FROM table) AS collection-type)
FROM DUAL
|
1.
單獨查詢
SQL
|
說明
|
|
MSSQL
|
SELECT E.*
FROM ListDeptEmp('IT') E
|
|
ORACLE
|
SELECT E.*
FROM
TABLE(ListDeptEmp('IT')) E
|
TABLE()函數可將集合值轉換虛擬資料表。
|
將部門代碼為傳入函數中，兩種資料庫均回傳資料表。其中，Oracle需透過TABLE()函數可將集合（Collections）值轉換為被SQL語法所查詢之資料（虛擬資料表），此函數為MULTISET之反向函數。
2.
連結（Table
Join）查詢
資料表值函式 （Table-valued
function）應用於單獨查詢時則與先前程序（Procedure）相似，資料表值函式應與其他外部資料表連結進行查詢，才能發揮其優點及效益，MSSQL提供APPLY運算子以處理I外部資料表（或結果集）之資料列（Row）呼叫資料表函數之用，APPLY有兩種形式：CROSS
APPLY 與 OUTER APPLY；兩者使用方式與概念與Table
Join類似，保留資料表（Preseved Table）為外部資料表。
l CROSS APPLY
CROSS APPLY，類似INNER JOIN概念，傳回兩個資料表中相符(匹配)的記錄，即傳入資料表值函式可產生結果集之其他資料表資料列。
SQL
|
說明
|
|
MSSQL
|
SELECT A.DEPT_NO
, A.FUNC
, E.EMP_NO
, E.EMP_NAME
FROM AUTH A
CROSS APPLY ListDeptEmp(A.DEPT_NO) E
WHERE 1=1
AND A.DEPT_NO IN ('IT', 'HR')
ORDER BY 1, 2, 3
|
|
ORACLE
|
SELECT A.DEPT_NO
, A.FUNC
, E.EMP_NO
, E.EMP_NAME
FROM AUTH A
CROSS JOIN TABLE(ListDeptEmp(A.DEPT_NO)) E
WHERE 1=1
AND A.DEPT_NO IN ('IT', 'HR')
ORDER BY 1,
2, 3
|
CROSS JOIN可更換為傳統INNER JOIN語法，即使用逗號(,)。
|
lOUTER APPLY
OUTER APPLY類似OUTER JOIN，外部資料表為保留資料表（Preseved
Table），將保有所有資料，資料表值函式可能產生或不會產生結果集的資料列（未符合為NULL）。
SQL
|
說明
|
|
MSSQL
|
SELECT A.DEPT_NO
, A.FUNC
, E.EMP_NO
, E.EMP_NAME
FROM AUTH A
OUTER APPLY ListDeptEmp(A.DEPT_NO) E
WHERE 1=1
AND A.DEPT_NO IN ('IT', 'HR')
ORDER BY 1, 2, 3
|
|
ORACLE
|
SELECT A.DEPT_NO
, A.FUNC
, E.EMP_NO
, E.EMP_NAME
FROM AUTH A
LEFT OUTER JOIN TABLE(ListDeptEmp(A.DEPT_NO)) E
ON 1=1 --重點(當無任何資料時,可用此方式)
WHERE 1=1
AND A.DEPT_NO IN ('IT', 'HR')
ORDER BY 1, 2, 3
|
沒有留言:
張貼留言