第 15 章 自訂函數與順序物件
本章重點 15-1 自訂函數的特色 15-2 自訂函數的建立、使用與修改 15-3 自訂函數的使用技巧 15-4 使用順序物件
15-1 自訂函數的特色 預存程序只能傳回一個整數值 預存程序可以經由參數來傳回資料 (將參數設為OUTPUT) 在預存程序中可以做任何的資料異動, 例如新增或修改資料、更改資料庫的設定…等 預存程序必須以 EXECUTE 來執行, 因此不能使用在運算式之中, 例如 myProc 會傳回 2, 那麼 『SET @var = myProc』或『SELECT * FROM myProc』都會造成錯誤。
自訂函數的特色 在預存程序中, 可以使用 TRY...CATCH 敘述來處理錯誤狀況。但在自訂函數中則禁止使用。
15-2 自訂函數的建立、使用與修改 純量值函數 (Scalar-valued function) 會傳回單一的資料值。 資料表值函數 (Table-valued function) 內嵌資料表值函數 (Inline table-valued function) 多重陳述式資料表值函數 (Multistatement table-valued function)
自訂函數的建立、使用與修改 純量值函數 內嵌資料表值函數 多重陳述式資料表值函數 如何建立、修改、或刪除自訂函數
純量值函數
純量值函數 CREATE FUNCTION function_name @param_name scalar_data_type [=default] [READONLY]
純量值函數 RETURNS scalar_return_data_type WITH <function_option> [[,] ...n] AS function_body RETURN scalar_expression
純量值函數
純量值函數
純量值函數
純量值函數
純量值函數
內嵌資料表值函數
內嵌資料表值函數 RETURNS TABLE RETURN [( ] select-statement [ )]
內嵌資料表值函數
內嵌資料表值函數
內嵌資料表值函數
多重陳述式資料表值函數
多重陳述式資料表值函數 RETURNS @return_variable TABLE <table_type_definition> RETURN
多重陳述式資料表值函數
多重陳述式資料表值函數
多重陳述式資料表值函數
如何建立、修改、或刪除自訂函數
如何建立、修改、或刪除自訂函數
如何建立、修改、或刪除自訂函數
15-3 自訂函數的使用技巧 執行自訂函數時的注意事項 在資料表的設定中使用函數 在自訂函數中使用敘述的限制 決定性與非決定性函數 設定 SCHEMABINDING 時的限制
執行自訂函數時的注意事項
執行自訂函數時的注意事項
在資料表的設定中使用函數
在資料表的設定中使用函數
在資料表的設定中使用函數
在資料表的設定中使用函數
在資料表的設定中使用函數
在資料表的設定中使用函數
在資料表的設定中使用函數
在資料表的設定中使用函數
在自訂函數中使用敘述的限制 可用 DECLARE 敘述來宣告要使用於函數中的變數, 而這些變數的有效範圍只限於函數中；當函數結束時, 宣告的變數也會跟著消失。 可用 SET 敘述將資料指定給變數。 若使用 SELECT 敘述, 則必須將查詢結果指定給變數。 可使用 INSERT、UPDATE、DELETE 敘述針對函數中的 table 變數做處理。
在自訂函數中使用敘述的限制 可使用流程控制敘述, 如 IF、WHILE 等, 但禁用TRY...CATCH 敘述。 可用 EXECUTE 敘述來執行延伸預存程序。其他預存程序或 SQL 字串則不可執行。 可在函數中建立 CURSOR 、前後移動 CURSOR 中的記錄位置、或使用 FETCH...INTO 將資料存入變數中。
在自訂函數中使用敘述的限制 在函數中不可更改到資料庫的任何設計、設定、或資料表內容。我們只能擷取、計算資料, 或是修改在函數中宣告的變數內容。 所執行的敘述不可直接傳回資料集給應用程式, 例如不可用 “SELECT 姓名 FROM ...”, 而應改為“SELECT @var= 姓名 FROM ..." 將查詢結果存入變數中。
決定性與非決定性函數 決定性函數 非決定性函數
決定性與非決定性函數 自訂函數已設定了 SCHEMABINDING 選項。 在自訂函數中沒有呼叫任何『非決定性』的自訂函數或內建函數。 在自訂函數中沒有使用到任何的資料庫物件 (但在函式中宣告的 table 變數除外), 例如資料表、檢視表等。 自訂函數中沒有呼叫任何的延伸預存程序。
決定性與非決定性函數 當資料表或檢視表的計算欄位中有使用到『非決定性』函數時, 該欄位將不允許用來建立索引。 當檢視表中有使用到『非決定性』函數時, 則該檢視表將不允許建立叢集索引 ( Clustered index)。
設定 SCHEMABINDING 時的限制 在自訂函數中所使用到的檢視表及其他自訂函數, 都必須是已經設定為 SCHEMABINDING 的。 自訂函數與其中使用到的所有物件, 都必須位於同一個資料庫內。 建立函數的人, 必須對函數中使用到的所有物件 (資料表、檢視表、及自訂函數) 擁有 References 權限：
設定 SCHEMABINDING 時的限制
15-4 使用順序物件
使用順序物件
使用順序物件
使用順序物件
使用順序物件 用 SQL 語法建立順序物件 用 SQL 語法修改順序物件或重新開始編號 用 SQL 語法刪除順序物件 在 SSMS 中新增、修改、更名、刪除順序物件 NEXT VALUE FOR 的使用技巧
用 SQL 語法建立順序物件
用 SQL 語法建立順序物件 CREATE SEQUENCE sequence_name 建立順序物件,在 sequence_name 中也可包含結構描述 (Schema)。 AS integer_type 指定順序物件的數值型別,必須為下表中的整數型別,或是小數位數為 0 的 numeric(decimal)。
用 SQL 語法建立順序物件 START WITH <constant> INCREMENT BY <constant> MINVALUE <constant>、MAXVALUE <constant> CYCLE | { NO CYCLE } CACHE [<constant> ] | NO CACHE
順序物件的快取原理 第 1 次取號時, 系統會先由順序物件中取出 50 個編號 (1~50) 第 2 次取號時, 會傳回 2 並將記憶體中的二個值改為 2 及 48 第 51 次取號時, 系統會再由順序物件中取出 50 個編號 (51~100)
順序物件的快取原理
用 SQL 語法修改順序物件或重新開始編號 1. 沒有 AS integer_type： 2. 須將 START 改為 RESTART：
用 SQL 語法修改順序物件或重新開始編號
用 SQL 語法刪除順序物件
在 SSMS 中新增、修改、更名、刪除順序物件
在 SSMS 中新增、修改、更名、刪除順序物件
NEXT VALUE FOR 的使用技巧 在 DECLARE、SET、或 SELECT 子句中設定變數的值：
NEXT VALUE FOR 的使用技巧 使用 SELECT...INTO 或 INSERT...VALUES 來新增記錄：
NEXT VALUE FOR 的使用技巧 使用於 UPDATE 子句更改記錄：
NEXT VALUE FOR 的使用技巧 用來做為欄位的預設值。底下將順序物件設為序號欄的預設值：
NEXT VALUE FOR 的使用技巧 順序物件用於 SELECT 敘述時若需要排序, 不可使用ORDER BY 子句來排序 (會視為錯誤), 而必須改用 OVER(ORDER BY...) 子句才行：
NEXT VALUE FOR 不適用的場合 當資料庫在唯讀模式中。 不能做為資料表值函式、彙總函式的參數。 不能用於子查詢、檢視表、使用者自訂函數、或計算欄位中。 不能用在包含 DISTINCT、UNION、UNION ALL 的敘述中。 不能用在包含 ORDER BY 子句的敘述中 (但可以搭配 OVER (ORDER BY...) 子句來排序)。
NEXT VALUE FOR 不適用的場合 不能用在下列子句中： FETCH、OVER、OUTPUT、ON、PIVOT、UNPIVOT、GROUP BY、HAVING、或 FOR XML。 在使用 CASE、CHOOSE、COALESCE、IIF、ISNULL 或 NULLIF 的條件運算式中。 不能用在 WHERE 子句、或不屬於 INSERT 陳述式的 VALUES 子句中。 不能用在檢查條件約束的定義中 (但可以用在預設值條件約束的定義中)。
NEXT VALUE FOR 不適用的場合 不能用在規則物件或預設值物件的定義中, 也不能做為使用者定義資料表類型的預設值 (有關規則物件、預設值物件、及使用者定義資料表類型, 可參見附錄 B)。 不能用在包含 TOP、OFFSET 的敘述中，或是設定 ROWCOUNT 選項時。 不能用在 MERGE 敘述中 (除非 NEXT VALUE FOR 已用於目標資料表的預設值條件約束中, 而且預設值用於 MERGE 陳述式的 CREATE 敘述中)。
NEXT VALUE FOR 的使用技巧 如果想要向順序物件一次取得 “連續的” 多個編號, 可改用 sp_sequence_get_range 預存程序來取 號, 例如：