直播中
工作時間
9:00 18:00
用戶定義函數(shù)用 ALTER FUNCTION 修改，用 DROP FUNCTION 除去。
語法
標量函數(shù)
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
內(nèi)嵌表值函數(shù)
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 [ ) ]
多語句表值函數(shù)
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 ] )
參數(shù)
owner_name
擁有該用戶定義函數(shù)的用戶 ID 的名稱。owner_name 必須是現(xiàn)有的用戶 ID。
function_name
用戶定義函數(shù)的名稱。函數(shù)名稱必須符合標識符的規(guī)則，對其所有者來說，該名稱在數(shù)據(jù)庫中必須是唯一的。
@parameter_name
用戶定義函數(shù)的參數(shù)。CREATE FUNCTION 語句中可以聲明一個或多個參數(shù)。函數(shù)最多可以有 1,024 個參數(shù)。函數(shù)執(zhí)行時每
個已聲明參數(shù)的值必須由用戶指定，除非該參數(shù)的默認值已經(jīng)定義。 如果函數(shù)的參數(shù)有默認值，在調(diào)用該函數(shù)時必須指
定"default"關鍵字才能獲得默認值。這種行為不同于存儲過程中有默認值的參數(shù)，在存儲過程中省略參數(shù)也意味著使用默
認值。
使用 @ 符號作為第一個字符來指定參數(shù)名稱。參數(shù)名稱必須符合標識符的規(guī)則。每個函數(shù)的參數(shù)僅用于該函數(shù)本身；相同
的參數(shù)名稱可以用在其它函數(shù)中。參數(shù)只能代替常量；而不能用于代替表名、列名或其它數(shù)據(jù)庫對象的名稱。
scalar_parameter_data_type
參數(shù)的數(shù)據(jù)類型。所有標量數(shù)據(jù)類型（包括 bigint 和 sql_variant）都可用作用戶定義函數(shù)的參數(shù)。不支持 timestamp
數(shù)據(jù)類型和用戶定義數(shù)據(jù)類型。不能指定非標量類型（例如 cursor 和 table）。
scalar_return_data_type
是標量用戶定義函數(shù)的返回值。scalar_return_data_type 可以是 SQL Server 支持的任何標量數(shù)據(jù)類型（text、ntext、
image 和 timestamp 除外）。
scalar_expression
指定標量函數(shù)返回的標量值。
TABLE
指定表值函數(shù)的返回值為表。
在內(nèi)嵌表值函數(shù)中，通過單個 SELECT 語句定義 TABLE 返回值。內(nèi)嵌函數(shù)沒有相關聯(lián)的返回變量。
在多語句表值函數(shù)中，@return_variable 是 TABLE 變量，用于存儲和累積應作為函數(shù)值返回的行。
function_body
指定一系列 Transact-SQL 語句定義函數(shù)的值，這些語句合在一起不會產(chǎn)生副作用。function_body 只用于標量函數(shù)和多
語句表值函數(shù)。
在標量函數(shù)中，function_body 是一系列合起來求得標量值的 Transact-SQL 語句。
在多語句表值函數(shù)中，function_body 是一系列填充表返回變量的 Transact-SQL 語句。
select-stmt
是定義內(nèi)嵌表值函數(shù)返回值的單個 SELECT 語句。
ENCRYPTION
指出 SQL Server 加密包含 CREATE FUNCTION 語句文本的系統(tǒng)表列。使用 ENCRYPTION 可以避免將函數(shù)作為 SQL Server
復制的一部分發(fā)布。
SCHEMABINDING
指定將函數(shù)綁定到它所引用的數(shù)據(jù)庫對象。如果函數(shù)是用 SCHEMABINDING 選項創(chuàng)建的，則不能更改（使用 ALTER 語句）
或除去（使用 DROP 語句）該函數(shù)引用的數(shù)據(jù)庫對象。
函數(shù)與其所引用對象的綁定關系只有在發(fā)生以下兩種情況之一時才被解除：
除去了函數(shù)。
在未指定 SCHEMABINDING 選項的情況下更改了函數(shù)（使用 ALTER 語句）。
只有在滿足以下條件時，函數(shù)才能綁定到架構：
該函數(shù)所引用的用戶定義函數(shù)和視圖也已綁定到架構。
該函數(shù)所引用的對象不是用兩部分名稱引用的。
該函數(shù)及其引用的對象屬于同一數(shù)據(jù)庫。
執(zhí)行 CREATE FUNCTION 語句的用戶對所有該函數(shù)所引用的數(shù)據(jù)庫對象都具有 REFERENCES 權限。
如果不符合以上條件，則指定了 SCHEMABINDING 選項的 CREATE FUNCTION 語句將失敗。
注釋
用戶定義函數(shù)為標量值函數(shù)或表值函數(shù)。如果 RETURNS 子句指定一種標量數(shù)據(jù)類型，則函數(shù)為標量值函數(shù)?？梢允褂枚?BR>條 Transact-SQL 語句定義標量值函數(shù)。
如果 RETURNS 子句指定 TABLE，則函數(shù)為表值函數(shù)。根據(jù)函數(shù)主體的定義方式，表值函數(shù)可分為行內(nèi)函數(shù)或多語句函數(shù)。
如果 RETURNS 子句指定的 TABLE 不附帶列的列表，則該函數(shù)為行內(nèi)函數(shù)。行內(nèi)函數(shù)是使用單個 SELECT 語句定義的表值
函數(shù)，該語句組成了函數(shù)的主體。該函數(shù)返回的表的列（包括數(shù)據(jù)類型）來自定義該函數(shù)的 SELECT 語句的 SELECT 列
表。
如果 RETURNS 子句指定的 TABLE 類型帶有列及其數(shù)據(jù)類型，則該函數(shù)是多語句表值函數(shù)。
多語句函數(shù)的主體中允許使用以下語句。未在下面的列表中列出的語句不能用在函數(shù)主體中。
賦值語句。
控制流語句。
DECLARE 語句，該語句定義函數(shù)局部的數(shù)據(jù)變量和游標。
SELECT 語句，該語句包含帶有表達式的選擇列表，其中的表達式將值賦予函數(shù)的局部變量。
游標操作，該操作引用在函數(shù)中聲明、打開、關閉和釋放的局部游標。只允許使用以 INTO 子句向局部變量賦值的 FETCH
語句；不允許使用將數(shù)據(jù)返回到客戶端的 FETCH 語句。
INSERT、UPDATE 和 DELETE 語句，這些語句修改函數(shù)的局部 table 變量。
EXECUTE 語句調(diào)用擴展存儲過程。
函數(shù)的確定性和副作用
函數(shù)可以是確定的或不確定的。如果任何時候用一組特定的輸入值調(diào)用函數(shù)時返回的結果總是相同的，則這些函數(shù)為確定
的。如果每次調(diào)用函數(shù)時即使用的是相同的一組特定輸入值，返回的結果總是不同的，則這些函數(shù)為不確定的。
不確定的函數(shù)會產(chǎn)生副作用。副作用是更改數(shù)據(jù)庫的某些全局狀態(tài)，比如更新數(shù)據(jù)庫表或某些外部資源，如文件或網(wǎng)絡等
（例如，修改文件或發(fā)送電子郵件消息）。
不允許在用戶定義函數(shù)主體中內(nèi)置不確定函數(shù)；這些不確定函數(shù)如下：
@@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
盡管在用戶定義函數(shù)主體中不允許有不確定函數(shù)，這些用戶定義函數(shù)在調(diào)用擴展存儲過程時仍會產(chǎn)生副作用。
由于擴展存儲過程會對數(shù)據(jù)庫產(chǎn)生副作用，因此調(diào)用擴展存儲過程的函數(shù)是不確定的。當用戶定義函數(shù)調(diào)用會對數(shù)據(jù)庫產(chǎn)
生副作用的擴展存儲過程時，不要指望結果集保持一致或執(zhí)行函數(shù)。
從函數(shù)中調(diào)用擴展存儲過程
從函數(shù)內(nèi)部調(diào)用時擴展存儲過程無法向客戶端返回結果集。任何向客戶端返回結果集的 ODS API 都將返回 FAIL。擴展存
儲過程可以連接回 Microsoft? SQL Server?；但是，它不應嘗試聯(lián)接與喚醒調(diào)用擴展存儲過程的函數(shù)相同的事務。
與從批處理或存儲過程中喚醒調(diào)用相似，擴展存儲過程在運行 SQL Server 的 Windows? 安全帳戶的上下文中執(zhí)行。存儲
過程的所有者在授予用戶 EXECUTE 特權時應考慮這一點。
函數(shù)調(diào)用
在可使用標量表達式的位置可喚醒調(diào)用標量值函數(shù)，包括計算列和 CHECK 約束定義。當喚醒調(diào)用標量值函數(shù)時，至少應使
用函數(shù)的兩部分名稱。
[database_name.]owner_name.function_name ([argument_expr][,...])
如果用戶定義函數(shù)用于定義計算列，則該函數(shù)的確定性同樣決定了是否可在該計算列上創(chuàng)建索引。只有當函數(shù)具有確定性
時，才可以在使用該函數(shù)的計算列上創(chuàng)建索引。如果在輸入相同的情況下函數(shù)始終返回相同的值，則該函數(shù)具有確定性。
可以使用由一部分組成的名稱喚醒調(diào)用表值函數(shù)。
[database_name.][owner_name.]function_name ([argument_expr][,...])
對于 Microsoft? SQL Server? 2000 中包含的系統(tǒng)表函數(shù)，喚醒調(diào)用時需在函數(shù)名的前面加上前綴"::"。
SELECT *
FROM ::fn_helpcollations()
對于導致語句停止執(zhí)行然后從存儲過程中的下一語句繼續(xù)執(zhí)行的 Transact-SQL 錯誤，在函數(shù)中的處理方式不同。在函數(shù)
中，這類錯誤會導致函數(shù)停止執(zhí)行。這反過來使喚醒調(diào)用該函數(shù)的語句停止執(zhí)行。
權限
用戶應具有執(zhí)行 CREATE FUNCTION 語句的 CREATE FUNCTION 權限。
CREATE FUNCTION 的權限默認地授予 sysadmin 固定服務器角色和 db_owner 和 db_ddladmin 固定數(shù)據(jù)庫角色的成員。
sysadmin 和 db_owner 的成員可用 GRANT 語句將 CREATE FUNCTION 權限授予其它登錄。
函數(shù)的所有者對其函數(shù)具有 EXECUTE 權限。其他用戶不具有 EXECUTE 權限，除非給他們授予了特定函數(shù)上的 EXECUTE 權
限。
若要創(chuàng)建或更改在 CONSTRAINT、DEFAULT 子句或計算列定義中引用了用戶定義函數(shù)的表，用戶還必須對這些函數(shù)有
REFERENCES 權限。
示例
A. 計算 ISO 周的標量值用戶定義函數(shù)
下例中，用戶定義函數(shù) ISOweek 取日期參數(shù)并計算 ISO 周數(shù)。為了正確計算該函數(shù)，必須在調(diào)用該函數(shù)前喚醒調(diào)用 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
下面是函數(shù)調(diào)用。注意 DATEFIRST 設置為 1。
SET DATEFIRST 1
SELECT master.dbo.ISOweek('12/26/1999') AS 'ISO Week'
下面是結果集。
ISO Week
----------------
52
B. 內(nèi)嵌表值函數(shù)
下例返回內(nèi)嵌表值函數(shù)。
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. 多語句表值函數(shù)
假設有一個表代表如下的層次關系：
CREATE TABLE employees (empid nchar(5) PRIMARY KEY,
empname nvarchar(50),
mgrid nchar(5) REFERENCES employees(empid),
title nvarchar(30)
)
表值函數(shù) fn_FindReports(InEmpID) 有一個給定的職員ID，它返回與所有直接或間接向給定職員報告的職員相對應的表。
該邏輯無法在單個查詢中表現(xiàn)出來，不過可以實現(xiàn)為用戶定義函數(shù)。
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
桂公網(wǎng)安備45010502000253號
電子營業(yè)執(zhí)照
