sql 用户定义函数

Transact-SQL 程式語法的參考說明

CREATE FUNCTION

建立使用者定義函數，它是一個會傳回值的預存 Transact-SQL 常式。使用者定義函數不能被用來執行會修改全域資料庫狀態的一整組動作。使用者定義函數和系統函數一樣，可以從查詢中叫用。它們也和預存程序一樣可以透過 EXECUTE 陳述式來執行。

使用者定義函數可以使用 ALTER FUNCTION 來修改，也可以使用 DROP FUNCTION 來卸除。

語法

純量函數

CREATE FUNCTION [ owner_name.] function_name
    ( [ { @parameter_name 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 scalar_parameter_data_type [ = default ] } [ ,...n ] ] )

RETURNS TABLE

[ WITH < function_option > [ [,] ...n ] ]

[ AS ]

RETURN [ ( ] select-stmt [ ) ]

多陳述式資料表值函數

CREATE FUNCTION [ owner_name.] function_name
    ( [ { @parameter_name 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

擁有該使用者定義函數的使用者 ID 名稱。owner_name 必須為現有的使用者 ID。

function_name

使用者定義函數的名稱。函數名稱必須符合識別項規則，而且在資料庫中及對其擁有者都必須是唯一的。

@parameter_name

使用者定義函數中的參數。在 CREATE FUNCTION 陳述式中可以宣告一個或一個以上參數。每一函數最多可以有 1,024 個參數。每一個宣告的參數在函數執行時必須由使用者來提供值 (除非已定義了參數的預設值)。 如果函數的參數擁有預設值，在呼叫函數時必須指定關鍵字 "default" 才能取得預設值。 此一行為不同於預存程序中有預設值的參數，在後者省略參數亦隱含預設值。

指定參數名稱時請用 at 符號 (@) 作為第一個字元。參數名稱必須與識別項的規則一致。參數僅存在該函數區域中；相同的參數名稱也可以用在其他函數中。參數只能取代常數；不能用來取代資料表名稱、資料行名稱或其他資料庫物件的名稱。

scalar_parameter_data_type

為參數的資料型別。所有純量資料型別，包括 bigint 和 sql_variant，都可以作為使用者定義函數的參數。 不支援 timestamp及使用者自訂的資料型別。非純量型別 (如資料指標和資料表) 都不能被指定。

scalar_return_data_type

為純量使用者自訂函數的傳回值。scalar_return_data_type 可以是 SQL Server 所支援的任何純量資料型別，text、ntext、image 和 rowversion (timestamp) 等除外。

scalar_expression

指定純量函數傳回的純量值。

TABLE

指定資料表值函數的傳回值為一資料表。

在內嵌資料表值函數中，TABLE 的傳回值是由單一的 SELECT 陳述式所定義。內嵌函數沒有關聯的傳回變數。

在多陳述式資料表值函數中，@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 陳述式)。

函數與其所參照物件之間的繫結只有在發生下列兩種情況時會被移除：

1. 該函數被卸除。
   
   
2. 該函數被更改 (使用 ALTER 陳述式) 為 SCHEMABINDING 選項未指定。

一個函數只有在以下條件都成立時才能被結構描述繫結：

3. 該函數所參照的使用者定義函數及檢視表均為結構描述繫結。
   
   
4. 該函數所參照的物件不是使用兩部分名稱所參照。
   
   
5. 該函數與其所參照之物件屬於相同的資料庫。
   
   
6. 執行 CREATE FUNCTION 陳述式的使用者具有該函數所參照所有資料庫物件的 REFERENCES 權限。

如果以上條件不成立的話，指定了 SCHEMABINDING 選項的 CREATE FUNCTION 陳述式將會失敗。

備註

使用者定義函數可以是純量值或者是資料表值。如果 RETURNS 子句指定了某一純量資料型別，則函數便是純量值。純量值函數可以使用多個 Transact-SQL 陳述式來定義。

如果 RETURNS 子句指定了 TABLE，則函數便是資料表值。依據函數主體被定義的方式，資料表值函數可區分為內嵌函數和多陳述式函數。

如果 RETURNS 子句指定了 TABLE 但沒有附資料行清單，那麼該函數便是內嵌函數。內嵌函數是以構成函數主體的單一 SELECT 陳述式所定義之資料表值函數。函數所傳回資料表的資料行 (包括資料型別) 是由定義該函數之 SELECT 陳述式的 SELECT 清單所衍生。

如果 RETURNS 子句指定了 TABLE 型別並附有資料行及其資料型別，則該函數為多陳述式資料表值函數。

下列陳述式可用在多陳述式函數主體中。本清單未列的陳述式則不允許用於函數的主體中：

9. 指派陳述式。
   
   
10. 流程控制陳述式。
    
    
11. 定義函數區域內資料變數和資料指標的 DECLARE 陳述式。
    
    
12. 含有選取清單及指派值給函數區域變數之運算式的 SELECT 陳述式。
    
    
13. 參照到在函數內部宣告、開啟、關閉及配置之區域資料指標的資料指標動作。只允許使用 INTO 子句指派值給區域變數的 FETCH 陳述式；不允許傳回資料給用戶端的 FETCH 陳述式。
    
    
14. 修改函數區域 table 變數的 INSERT、UPDATE 及 DELETE 陳述式。
    
    
15. 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) 在輸入一個 Employee 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