CREATE FUNCTION sqlserver使用者定義函數_MsSql

建立使用者定義函數，它是傳回值的已儲存的 Transact-SQL 常式。使用者定義函數不能用於執行一組修改全域資料庫狀態的操作。與系統函數一樣，使用者定義函數可以從查詢中喚醒調用。也可以像預存程序一樣，通過 EXECUTE 語句執行。
使用者定義函數用 ALTER FUNCTION 修改，用 DROP FUNCTION 除去。

文法
純量涵式

CREATE FUNCTION [ owner_name.] function_name ( [ { @parameter_name [AS] scalar_parameter_data_type [ = default ] } [ ,...n ] ] ) RETURNS scalar_return_data_type [ WITH < function_option> [ [,] ...n] ] [ AS ] BEGIN function_body RETURN scalar_expression END

內嵌資料表值函式

CREATE FUNCTION [ owner_name.] function_name ( [ { @parameter_name [AS] scalar_parameter_data_type [ = default ] } [ ,...n ] ] ) RETURNS TABLE [ WITH < function_option > [ [,] ...n ] ] [ AS ] RETURN [ ( ] select-stmt [ ) ] 

多語句資料表值函式

CREATE FUNCTION [ owner_name.] function_name ( [ { @parameter_name [AS] scalar_parameter_data_type [ = default ] } [ ,...n ] ] ) RETURNS @return_variable TABLE < table_type_definition > [ WITH < function_option > [ [,] ...n ] ] [ AS ] BEGIN function_body RETURN END < function_option > ::= { ENCRYPTION | SCHEMABINDING } < table_type_definition > ::= ( { column_definition | table_constraint } [ ,...n ] ) 

參數
owner_name
擁有該使用者定義函數的使用者識別碼 的名稱。owner_name 必須是現有的使用者識別碼。
function_name
使用者定義函數的名稱。函數名稱必須符合標識符的規則，對其所有者來說，該名稱在資料庫中必須是唯一的。
@parameter_name
使用者定義函數的參數。CREATE FUNCTION 語句中可以聲明一個或多個參數。函數最多可以有 1,024 個參數。函數執行時每個已聲明參數的值必須由使用者指定，除非該參數的預設值已經定義。 如果函數的參數有預設值，在調用該函數時必須指定"default"關鍵字才能獲得預設值。這種行為不同於預存程序中有預設值的參數，在預存程序中省略參數也意味著使用預設值。
使用 @ 符號作為第一個字元來指定參數名稱。參數名稱必須符合標識符的規則。每個函數的參數僅用於該函數本身；相同的參數名稱可以用在其它函數中。參數只能代替常量；而不能用於代替表名、列名或其它資料庫物件的名稱。
scalar_parameter_data_type
參數的資料類型。所有純量資料型別（包括 bigint 和 sql_variant）都可用作使用者定義函數的參數。不支援 timestamp 資料類型和使用者定義資料類型。不能指定非標量類型（例如 cursor 和 table）。
scalar_return_data_type
是標量使用者定義函數的傳回值。scalar_return_data_type 可以是 SQL Server 支援的任何純量資料型別（text、ntext、image 和 timestamp 除外）。
scalar_expression
指定純量涵式返回的標量值。
TABLE
指定資料表值函式的傳回值為表。
在內嵌資料表值函式中，通過單個 SELECT 語句定義 TABLE 傳回值。內嵌函數沒有相關聯的返回變數。
在多語句資料表值函式中，@return_variable 是 TABLE 變數，用於儲存和累積應作為函數值返回的行。
function_body
指定一系列 Transact-SQL 陳述式定義函數的值，這些語句合在一起不會產生副作用。function_body 只用於純量涵式和多語句資料表值函式。
在純量涵式中，function_body 是一系列合起來求得標量值的 Transact-SQL 陳述式。
在多語句資料表值函式中，function_body 是一系列填充表返回變數的 Transact-SQL 陳述式。
select-stmt
是定義內嵌資料表值函式傳回值的單個 SELECT 語句。
ENCRYPTION
指出 SQL Server 加密包含 CREATE FUNCTION 語句文本的系統資料表列。使用 ENCRYPTION 可以避免將函數作為 SQL Server 複製的一部分發布。
SCHEMABINDING
指定將函數綁定到它所引用的資料庫物件。如果函數是用 SCHEMABINDING 選項建立的，則不能更改（使用 ALTER 語句）或除去（使用 DROP 語句）該函數引用的資料庫物件。
函數與其所引用對象的綁定關係只有在發生以下兩種情況之一時才被解除：
除去了函數。

在未指定 SCHEMABINDING 選項的情況下更改了函數（使用 ALTER 語句）。
只有在滿足以下條件時，函數才能綁定到架構：
該函數所引用的使用者定義函數和視圖也已綁定到架構。

該函數所引用的對象不是用兩部分名稱引用的。

該函數及其引用的對象屬於同一資料庫。

執行 CREATE FUNCTION 語句的使用者對所有該函數所引用的資料庫物件都具有 REFERENCES 許可權。
如果不符合以上條件，則指定了 SCHEMABINDING 選項的 CREATE FUNCTION 語句將失敗。
注釋
使用者定義函數為純量值函式或資料表值函式。如果 RETURNS 子句指定一種純量資料型別，則函數為純量值函式。可以使用多條 Transact-SQL 陳述式定義純量值函式。
如果 RETURNS 子句指定 TABLE，則函數為資料表值函式。根據函數主體的定義方式，資料表值函式可分為行內函數或多語句函數。
如果 RETURNS 子句指定的 TABLE 不附帶列的列表，則該函數為行內函數。行內函數是使用單個 SELECT 語句定義的資料表值函式，該語句組成了函數的主體。該函數返回的表的列（包括資料類型）來自訂該函數的 SELECT 語句的 SELECT 列表。
如果 RETURNS 子句指定的 TABLE 類型帶有列及其資料類型，則該函數是多語句資料表值函式。
多語句函數的主體中允許使用以下語句。未在下面的列表中列出的語句不能用在函數主體中。
指派陳述式。

控制流程語句。

DECLARE 語句，該語句定義函數局部的資料變數和遊標。

SELECT 語句，該語句包含帶有運算式的挑選清單，其中的運算式將值賦予函數的局部變數。

遊標操作，該操作引用在函數中聲明、開啟、關閉和釋放的局部遊標。只允許使用以 INTO 子句向局部變數賦值的 FETCH 語句；不允許使用將資料返回到用戶端的 FETCH 語句。

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

EXECUTE 語句調用擴充預存程序。
函數的確定性和副作用
函數可以是確定的或不確定的。如果任何時候用一組特定的輸入值調用函數時返回的結果總是相同的，則這些函數為確定的。如果每次調用函數時即使用的是相同的一組特定輸入值，返回的結果總是不同的，則這些函數為不確定的。
不確定的函數會產生副作用。副作用是更改資料庫的某些全域狀態，比如更新資料庫表或某些外部資源，如檔案或網路等（例如，修改檔案或寄送電子郵件訊息）。
不允許在使用者定義函數主體中內建不確定函數；這些不確定函數如下：

@@CONNECTIONS	@@TOTAL_ERRORS
@@CPU_BUSY	@@TOTAL_READ
@@IDLE	@@TOTAL_WRITE
@@IO_BUSY	GETDATE
@@MAX_CONNECTIONS	GETUTCDATE
@@PACK_RECEIVED	NEWID
@@PACK_SENT	RAND
@@PACKET_ERRORS	TEXTPTR
@@TIMETICKS

儘管在使用者定義函數主體中不允許有不確定函數，這些使用者定義函數在調用擴充預存程序時仍會產生副作用。

由於擴充預存程序會對資料庫產生副作用，因此調用擴充預存程序的函數是不確定的。當使用者定義函數調用會對資料庫產生副作用的擴充預存程序時，不要指望結果集保持一致或執行函數。

從函數中調用擴充預存程序
從函數內部調用時擴充預存程序無法向用戶端返回結果集。任何向用戶端返回結果集的 ODS API 都將返回 FAIL。擴充預存程序可以串連回 Microsoft® SQL Server™；但是，它不應嘗試聯結與喚醒調用擴充預存程序的函數相同的事務。

與從批處理或預存程序中喚醒調用相似，擴充預存程序在運行 SQL Server 的 Windows® 安全帳戶的上下文中執行。預存程序的所有者在授予使用者 EXECUTE 特權時應考慮這一點。

函數調用
在可使用標量運算式的位置可喚醒調用純量值函式，包括計算資料行和 CHECK 條件約束定義。當喚醒調用純量值函式時，至少應使用函數的兩部分名稱。

[database_name.]owner_name.function_name ([argument_expr][,...])

如果使用者定義函數用於定義計算資料行，則該函數的確定性同樣決定了是否可在該計算資料行上建立索引。只有當函數具有確定性時，才可以在使用該函數的計算資料行上建立索引。如果在輸入相同的情況下函數始終返回相同的值，則該函數具有確定性。

可以使用由一部分組成的名稱喚醒調用資料表值函式。

[database_name.][owner_name.]function_name ([argument_expr][,...])

對於 Microsoft® SQL Server™ 2000 中包含的系統資料表函數，喚醒調用時需在函數名的前面加上首碼"::"。

SELECT *
FROM ::fn_helpcollations()

對於導致語句停止執行然後從預存程序中的下一語句繼續執行的 Transact-SQL 錯誤，在函數中的處理方式不同。在函數中，這類錯誤會導致函數停止執行。這反過來使喚醒調用該函數的語句停止執行。

許可權
使用者應具有執行 CREATE FUNCTION 語句的 CREATE FUNCTION 許可權。

CREATE FUNCTION 的許可權預設地授予 sysadmin 固定伺服器角色和 db_owner 和 db_ddladmin 固定資料庫角色的成員。sysadmin 和 db_owner 的成員可用 GRANT 語句將 CREATE FUNCTION 許可權授予其它登入。

函數的所有者對其函數具有 EXECUTE 許可權。其他使用者不具有 EXECUTE 許可權，除非給他們授予了特定函數上的 EXECUTE 許可權。

若要建立或更改在 CONSTRAINT、DEFAULT 子句或計算資料行定義中引用了使用者定義函數的表，使用者還必須對這些函數有 REFERENCES 許可權。

樣本
A. 計算 ISO 周的標量值使用者定義函數
下例中，使用者定義函數 ISOweek 取日期參數並計算 ISO 周數。為了正確計算該函數，必須在調用該函數前喚醒調用 SET DATEFIRST 1。

CREATE FUNCTION ISOweek (@DATE datetime) RETURNS int AS BEGIN DECLARE @ISOweek int SET @ISOweek= DATEPART(wk,@DATE)+1 -DATEPART(wk,CAST(DATEPART(yy,@DATE) as CHAR(4))+'0104') --Special cases: Jan 1-3 may belong to the previous year IF (@ISOweek=0) SET @ISOweek=dbo.ISOweek(CAST(DATEPART(yy,@DATE)-1 AS CHAR(4))+'12'+ CAST(24+DATEPART(DAY,@DATE) AS CHAR(2)))+1 --Special case: Dec 29-31 may belong to the next year IF ((DATEPART(mm,@DATE)=12) AND ((DATEPART(dd,@DATE)-DATEPART(dw,@DATE))>= 28)) SET @ISOweek=1 RETURN(@ISOweek) END

下面是函數調用。注意 DATEFIRST 設定為 1。

SET DATEFIRST 1
SELECT master.dbo.ISOweek('12/26/1999') AS 'ISO Week'
下面是結果集。

ISO Week
----------------
52

B. 內嵌資料表值函式
下例返回內嵌資料表值函式。

USE pubs GO CREATE FUNCTION SalesByStore (@storeid varchar(30)) RETURNS TABLE AS RETURN (SELECT title, qty FROM sales s, titles t WHERE s.stor_id = @storeid and t.title_id = s.title_id)

C. 多語句資料表值函式
假設有一個表代表如下的層次關係：

CREATE TABLE employees (empid nchar(5) PRIMARY KEY, empname nvarchar(50), mgrid nchar(5) REFERENCES employees(empid), title nvarchar(30) ) 

資料表值函式 fn_FindReports(InEmpID) 有一個給定的職員ID，它返回與所有直接或間接向給定職員報告的職員相對應的表。該邏輯無法在單個查詢中表現出來，不過可以實現為使用者定義函數。

CREATE FUNCTION fn_FindReports (@InEmpId nchar(5)) RETURNS @retFindReports TABLE (empid nchar(5) primary key, empname nvarchar(50) NOT NULL, mgrid nchar(5), title nvarchar(30)) /*Returns a result set that lists all the employees who report to given employee directly or indirectly.*/ AS BEGIN DECLARE @RowsAdded int -- table variable to hold accumulated results DECLARE @reports TABLE (empid nchar(5) primary key, empname nvarchar(50) NOT NULL, mgrid nchar(5), title nvarchar(30), processed tinyint default 0) -- initialize @Reports with direct reports of the given employee INSERT @reports SELECT empid, empname, mgrid, title, 0 FROM employees WHERE empid = @InEmpId SET @RowsAdded = @@rowcount -- While new employees were added in the previous iteration WHILE @RowsAdded > 0 BEGIN /*Mark all employee records whose direct reports are going to be found in this iteration with processed=1.*/ UPDATE @reports SET processed = 1 WHERE processed = 0 -- Insert employees who report to employees marked 1. INSERT @reports SELECT e.empid, e.empname, e.mgrid, e.title, 0 FROM employees e, @reports r WHERE e.mgrid=r.empid and e.mgrid <> e.empid and r.processed = 1 SET @RowsAdded = @@rowcount /*Mark all employee records whose direct reports have been found in this iteration.*/ UPDATE @reports SET processed = 2 WHERE processed = 1 END -- copy to the result of the function the required columns INSERT @retFindReports SELECT empid, empname, mgrid, title FROM @reports RETURN END GO -- Example invocation SELECT * FROM fn_FindReports('11234') GO