用戶定義函式

用戶定義函式是由一個或多個 Transact-SQL 語句組成的子程式，可用於封裝代碼以便重新使用。Microsoft® SQL Server™ 並不將用戶限制在定義為 Transact-SQL 語言一部分的內置函式上，而是允許用戶創建自己的用戶定義函式。

基本介紹

• 中文名：用戶定義函式
• 外文名：User defined function
• 套用學科：計算機科學

函式定義

可使用 CREATE FUNCTION 語句創建、使用 ALTER FUNCTION 語句修改、以及使用 DROP FUNCTION 語句除去用戶定義函式。每個完全合法的用戶定義函式名 (database_name.owner_name.function_name) 必須唯一。

必須被授予 CREATE FUNCTION 許可權才能創建、修改或除去用戶定義函式。不是所有者的用戶在 Transact-SQL 語句中使用某個函式之前，必須先給此用戶授予該函式的適當許可權。若要創建或更改在 CHECK 約束、DEFAULT 子句或計算列定義中引用用戶定義函式的表，還必須具有函式的 REFERENCES 許可權。

在函式中，區別處理導致刪除語句並且繼續在諸如觸發器或存儲過程等模式中的下一語句的 Transact-SQL 錯誤。在函式中，上述錯誤會導致停止執行函式。接下來該操作導致停止喚醒調用該函式的語句。

用戶定義函式的類型

函式分類

標量函式內嵌表值函式多語句表值函式

用戶定義函式採用零個或更多的輸入參數並返回標量值或表。函式最多可以有 1024 個輸入參數。當函式的參數有默認值時，調用該函式時必須指定默認 DEFAULT 關鍵字才能獲取默認值。該行為不同於在存儲過程中含有默認值的參數，而在這些存儲過程中省略該函式也意味著省略默認值。用戶定義函式不支持輸出參數。

標量函式返回在 RETURNS 子句中定義的類型的單個數據值。可以使用所有標量數據類型，包括 bigint 和 sql_variant。不支持 timestamp 數據類型、用戶定義數據類型和非標量類型（如 table 或 cursor）。在 BEGIN...END 塊中定義的函式主體包含返回該值的 Transact-SQL 語句系列。返回類型可以是除 text、ntext、image、cursor 和 timestamp 之外的任何數據類型。

表值函式返回 table。對於內嵌表值函式，沒有函式主體；表是單個 SELECT 語句的結果集。對於多語句表值函式，在 BEGIN...END 塊中定義的函式主體包含 TRANSACT-SQL 語句，這些語句可生成行並將行插入將返回的表中。有關內嵌表值函式的更多信息，請參見內嵌用戶定義函式。有關表值函式的更多信息，請參見返回 table 數據類型的用戶定義函式。

BEGIN...END 塊中的語句不能有任何副作用。函式副作用是指對具有函式外作用域（例如資料庫表的修改）的資源狀態的任何永久性更改。函式中的語句唯一能做的更改是對函式上的局部對象（如局部游標或局部變數）的更改。不能在函式中執行的操作包括：對資料庫表的修改，對不在函式上的局部游標進行操作，傳送電子郵件，嘗試修改目錄，以及生成返回至用戶的結果集。

有效語句類型

DECLARE 語句，該語句可用於定義函式局部的數據變數和游標。

為函式局部對象賦值，如使用 SET 給標量和表局部變數賦值。

游標操作，該操作引用在函式中聲明、打開、關閉和釋放的局部游標。不允許使用 FETCH 語句將數據返回到客戶端。僅允許使用 FETCH 語句通過 INTO 子句給局部變數賦值。

控制流語句。

SELECT 語句，該語句包含帶有表達式的選擇列表，其中的表達式將值賦予函式的局部變數。

INSERT、UPDATE 和 DELETE 語句，這些語句修改函式的局部 table 變數。

EXECUTE 語句，該語句調用擴展存儲過程。

在查詢中指定的函式的實際執行次數在最佳化器生成的執行計畫間可能不同。示例為 WHERE 子句中的子查詢喚醒調用的函式。子查詢及其函式執行的次數會因最佳化器選擇的訪問路徑而異。

用戶定義函式中不允許使用會對每個調用返回不同數據的內置函式。用戶定義函式中不允許使用以下內置函式：

@@CONNECTIONS @@PACK_SENT GETDATE

@@CPU_BUSY @@PACKET_ERRORS GetUTCDate

@@IDLE@@TIMETICKS NEWID

@@IO_BUSY @@TOTAL_ERRORS RAND

@@MAX_CONNECTIONS @@TOTAL_READ TEXTPTR

@@PACK_RECEIVED @@TOTAL_WRITE

架構綁定函式

CREATE FUNCTION 支持 SCHEMABINDING 子句，後者可將函式綁定到它引用的任何對象（如表、視圖和其它用戶定義函式）的架構。嘗試對架構綁定函式所引用的任何對象執行 ALTER 或 DROP 都將失敗。

必須滿足以下條件才能在 CREATE FUNCTION 中指定 SCHEMABINDING：

該函式所引用的所有視圖和用戶定義函式必須是綁定到架構的。

該函式所引用的所有對象必須與函式位於同一資料庫中。必須使用由一部分或兩部分構成的名稱來引用對象。

必須具有對該函式中引用的所有對象（表、視圖和用戶定義函式）的 REFERENCES 許可權。

可使用 ALTER FUNCTION 刪除架構綁定。ALTER FUNCTION 語句將通過不帶 WITH SCHEMABINDING 指定函式來重新定義函式。

調用用戶定義函式

當調用標量用戶定義函式時，必須提供至少由兩部分組成的名稱：

SELECT *, MyUser.MyScalarFunction()

FROM MyTable

可以使用一個部分構成的名稱調用表值函式：

SELECT *

FROM MyTableFunction()

然而，當調用返回表的 SQL Server 內置函式時，必須將前綴 :: 添加至函式名：

SELECT * FROM ::fn_helpcollations()

可在 Transact-SQL 語句中所允許的函式返回的相同數據類型表達式所在的任何位置引用標量函式，包括計算列和 CHECK 約束定義。例如，下面的語句創建一個返回 decimal 的簡單函式：

CREATE FUNCTION CubicVolume

-- Input dimensions in centimeters

(@CubeLength decimal(4,1), @CubeWidth decimal(4,1),

@CubeHeight decimal(4,1) )

RETURNS decimal(12,3) -- Cubic Centimeters.

AS

BEGIN

RETURN ( @CubeLength * @CubeWidth * @CubeHeight )

END

然後可以在允許整型表達式的任何地方（如表的計算列中）使用該函式：

CREATE TABLE Bricks

(

BrickPartNmbr int PRIMARY KEY,

BrickColor nchar(20),

BrickHeight decimal(4,1),

BrickLength decimal(4,1),

BrickWidth decimal(4,1),

BrickVolume AS

(

dbo.CubicVolume(BrickHeight,

BrickLength, BrickWidth)

)

)

dbo.CubicVolume 是返回標量值的用戶定義函式的一個示例。RETURNS 子句定義由該函式返回的值的標量數據類型。BEGIN...END 塊包含一個或多個執行該函式的 Transact-SQL 語句。該函式中的每個 RETURN 語句都必須具有一個參數，可返回具有在 RETURNS 子句中指定的數據類型（或可隱性轉換為 RETURNS 中指定類型的數據類型）的數據值。RETURN 參數的值是該函式返回的值。