CREATE FUNCTION sqlserver用戶界說函數。本站提示廣大學習愛好者：（CREATE FUNCTION sqlserver用戶界說函數）文章只能為提供參考，不一定能成為您想要的結果。以下是CREATE FUNCTION sqlserver用戶界說函數正文
創立用戶界說函數，它是前往值的已保留的 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
具有該用戶界說函數的用戶 ID 的稱號。owner_name 必需是現有的用戶 ID。
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 語句挪用擴大存儲進程。
函數切實其實定性和反作用
函數可所以肯定的或不肯定的。假如任什麼時候候用一組特定的輸出值挪用函數時前往的成果老是雷同的，則這些函數為肯定的。假如每次挪用函數時即便用的是雷同的一組特定輸出值，前往的成果老是分歧的，則這些函數為不肯定的。
不肯定的函數會發生反作用。反作用是更改數據庫的某些全局狀況，好比更新數據庫表或某些內部資本，如文件或收集等（例如，修正文件或發送電子郵件新聞）。
不許可在用戶界說函數主體中內置不肯定函數；這些不肯定函數以下：
雖然在用戶界說函數主體中不許可有不肯定函數，這些用戶界說函數在挪用擴大存儲進程時仍會發生反作用。
因為擴大存儲進程會對數據庫發生反作用，是以挪用擴大存儲進程的函數是不肯定的。當用戶界說函數挪用會對數據庫發生反作用的擴大存儲進程時，不要期望成果集堅持分歧或履行函數。
從函數中挪用擴大存儲進程
從函數外部挪用時擴大存儲進程沒法向客戶端前往成果集。任何向客戶端前往成果集的 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
