CREATE FUNCTION
创建用户定义函数,它是返回值的已保存的 Transact-SQL 例程。用户定义函数不能用于执行一组修改全局数据库状态的操作。与系统函数一样,用户定义函数可以从查询中唤醒调用。也可以像存储过程一样,通过 EXECUTE 语句执行。 中.国.站.长.站
用户定义函数用 ALTER FUNCTION 修改,用 DROP FUNCTION 除去。 中国站.长站
语法 Chinaz
标量函数 Www@Chinaz@com
以下为引用的内容:
CREATE FUNCTION [ owner_name.] function_name
( [ { @parameter_name [AS] scalar_parameter_data_type [ = default ] } [ ,…n ] ] )
Chinaz@com
RETURNS scalar_return_data_type 中国.站长站
[ WITH < function_option> [ [,] …n] ]
中国.站.长站
[ AS ] 中.国.站长站
BEGIN
function_body
RETURN scalar_expression
END
中.国站长站
Www^Chinaz^com
内嵌表值函数
中国站长.站
以下为引用的内容:
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 ] ] Chinaz_com
[ AS ]
中国站长.站
BEGIN
function_body
RETURN
END
中国.站长站
< function_option > ::=
{ ENCRYPTION | SCHEMABINDING } Chinaz@com
< table_type_definition > ::=
( { column_definition | table_constraint } [ ,…n ] )
Www^Chinaz^com
中.国.站.长.站
参数
站长.站
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)。
Chinaz^com
scalar_return_data_type Www^Chinaz^com
是标量用户定义函数的返回值。scalar_return_data_type 可以是 SQL Server 支持的任何标量数据类型(text、ntext、image 和 timestamp 除外)。 Chinaz@com
scalar_expression
中国站.长.站
指定标量函数返回的标量值。 Chinaz
TABLE 站.长站
指定表值函数的返回值为表。
中.国.站长站
在内嵌表值函数中,通过单个 SELECT 语句定义 TABLE 返回值。内嵌函数没有相关联的返回变量。 站长.站
在多语句表值函数中,@return_variable 是 TABLE 变量,用于存储和累积应作为函数值返回的行。 站长.站
function_body
Chinaz
指定一系列 Transact-SQL 语句定义函数的值,这些语句合在一起不会产生副作用。function_body 只用于标量函数和多语句表值函数。 站长.站
在标量函数中,function_body 是一系列合起来求得标量值的 Transact-SQL 语句。 中国站.长站
在多语句表值函数中,function_body 是一系列填充表返回变量的 Transact-SQL 语句。 Www.Chinaz.com
select-stmt
Www_Chinaz_com
是定义内嵌表值函数返回值的单个 SELECT 语句。 Www~Chinaz~com
ENCRYPTION
Www~Chinaz~com
指出 SQL Server 加密包含 CREATE FUNCTION 语句文本的系统表列。使用 ENCRYPTION 可以避免将函数作为 SQL Server 复制的一部分发布。 中.国.站长站
SCHEMABINDING
站.长站
指定将函数绑定到它所引用的数据库对象。如果函数是用 SCHEMABINDING 选项创建的,则不能更改(使用 ALTER 语句)或除去(使用 DROP 语句)该函数引用的数据库对象。
中国站长_站,为中文网站提供动力
函数与其所引用对象的绑定关系只有在发生以下两种情况之一时才被解除: 中国.站长站
除去了函数。 中国站长.站
在未指定 SCHEMABINDING 选项的情况下更改了函数(使用 ALTER 语句)。
站.长站
只有在满足以下条件时,函数才能绑定到架构:
Www^Chinaz^com
该函数所引用的用户定义函数和视图也已绑定到架构。
Chinaz^com
该函数所引用的对象不是用两部分名称引用的。
[中国站长站]
该函数及其引用的对象属于同一数据库。
Www@Chinaz@com
执行 CREATE FUNCTION 语句的用户对所有该函数所引用的数据库对象都具有 REFERENCES 权限。 Www^Chinaz^com
如果不符合以上条件,则指定了 SCHEMABINDING 选项的 CREATE FUNCTION 语句将失败。
Chinaz^com
注释 Www_Chinaz_com
用户定义函数为标量值函数或表值函数。如果 RETURNS 子句指定一种标量数据类型,则函数为标量值函数。可以使用多条 Transact-SQL 语句定义标量值函数。 中.国.站.长.站
如果 RETURNS 子句指定 TABLE,则函数为表值函数。根据函数主体的定义方式,表值函数可分为行内函数或多语句函数。 Chinaz
如果 RETURNS 子句指定的 TABLE 不附带列的列表,则该函数为行内函数。行内函数是使用单个 SELECT 语句定义的表值函数,该语句组成了函数的主体。该函数返回的表的列(包括数据类型)来自定义该函数的 SELECT 语句的 SELECT 列表。
中国站.长.站
如果 RETURNS 子句指定的 TABLE 类型带有列及其数据类型,则该函数是多语句表值函数。
Www~Chinaz~com
多语句函数的主体中允许使用以下语句。未在下面的列表中列出的语句不能用在函数主体中。 Chinaz
赋值语句。
Chinaz.com
控制流语句。 [中国站长站]
DECLARE 语句,该语句定义函数局部的数据变量和游标。
中国.站.长站
SELECT 语句,该语句包含带有表达式的选择列表,其中的表达式将值赋予函数的局部变量。 Www~Chinaz~com
游标操作,该操作引用在函数中声明、打开、关闭和释放的局部游标。只允许使用以 INTO 子句向局部变量赋值的 FETCH 语句;不允许使用将数据返回到客户端的 FETCH 语句。 Www_Chinaz_com
INSERT、UPDATE 和 DELETE 语句,这些语句修改函数的局部 table 变量。 Www@Chinaz@com
EXECUTE 语句调用扩展存储过程。
Www~Chinaz~com
函数的确定性和副作用 Chinaz@com
函数可以是确定的或不确定的。如果任何时候用一组特定的输入值调用函数时返回的结果总是相同的,则这些函数为确定的。如果每次调用函数时即使用的是相同的一组特定输入值,返回的结果总是不同的,则这些函数为不确定的。 Www.Chinaz.com
不确定的函数会产生副作用。副作用是更改数据库的某些全局状态,比如更新数据库表或某些外部资源,如文件或网络等(例如,修改文件或发送电子邮件消息)。
站.长.站
不允许在用户定义函数主体中内置不确定函数;这些不确定函数如下: 中.国站长站
以下为引用的内容:
@@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 特权时应考虑这一点。
Chinaz_com
函数调用 中.国.站长站
在可使用标量表达式的位置可唤醒调用标量值函数,包括计算列和 CHECK 约束定义。当唤醒调用标量值函数时,至少应使用函数的两部分名称。
站长.站
[database_name.]owner_name.function_name ([argument_expr][,…]) 中国.站.长站
如果用户定义函数用于定义计算列,则该函数的确定性同样决定了是否可在该计算列上创建索引。只有当函数具有确定性时,才可以在使用该函数的计算列上创建索引。如果在输入相同的情况下函数始终返回相同的值,则该函数具有确定性。 Chinaz^com
可以使用由一部分组成的名称唤醒调用表值函数。 Chinaz^com
[database_name.][owner_name.]function_name ([argument_expr][,…]) 中国站.长站
对于 Microsoft? SQL Server? 2000 中包含的系统表函数,唤醒调用时需在函数名的前面加上前缀"::"。 Chinaz@com
以下为引用的内容:
SELECT *
FROM ::fn_helpcollations()
Chinaz~com
对于导致语句停止执行然后从存储过程中的下一语句继续执行的 Transact-SQL 错误,在函数中的处理方式不同。在函数中,这类错误会导致函数停止执行。这反过来使唤醒调用该函数的语句停止执行。 [中国站长站]
权限 Www@Chinaz@com
用户应具有执行 CREATE FUNCTION 语句的 CREATE FUNCTION 权限。
Www.Chinaz.com
CREATE FUNCTION 的权限默认地授予 sysadmin 固定服务器角色和 db_owner 和 db_ddladmin 固定数据库角色的成员。
Chinaz~com
sysadmin 和 db_owner 的成员可用 GRANT 语句将 CREATE FUNCTION 权限授予其它登录。 中.国站长站
函数的所有者对其函数具有 EXECUTE 权限。其他用户不具有 EXECUTE 权限,除非给他们授予了特定函数上的 EXECUTE 权限。
站长.站
若要创建或更改在 CONSTRAINT、DEFAULT 子句或计算列定义中引用了用户定义函数的表,用户还必须对这些函数有 REFERENCES 权限。
站.长站
示例 Www_Chinaz_com
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
Www~Chinaz~com
下面是函数调用。注意 DATEFIRST 设置为 1。 Chinaz.com
以下为引用的内容:
SET DATEFIRST 1
SELECT master.dbo.ISOweek(’12/26/1999′) AS ‘ISO Week’
Www.Chinaz.com
下面是结果集。 Chinaz
以下为引用的内容:
ISO Week
—————-
52
Www^Chinaz^com
B. 内嵌表值函数
Www^Chinaz^com
下例返回内嵌表值函数。
中.国.站长站
以下为引用的内容:
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. 多语句表值函数 中国.站长站
假设有一个表代表如下的层次关系: Chinaz@com
以下为引用的内容:
CREATE TABLE employees (empid nchar(5) PRIMARY KEY,
empname nvarchar(50),
mgrid nchar(5) REFERENCES employees(empid),
title nvarchar(30)
)
Chinaz
表值函数 fn_FindReports(InEmpID) 有一个给定的职员ID,它返回与所有直接或间接向给定职员报告的职员相对应的表。
Chinaz^com
该逻辑无法在单个查询中表现出来,不过可以实现为用户定义函数。
Chinaz
以下为引用的内容:
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 Www.Chinaz.com
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 Chinaz.com
— copy to the result of the function the required columns
INSERT @retFindReports
SELECT empid, empname, mgrid, title
FROM @reports
RETURN
END
GO Chinaz~com
— Example invocation
SELECT *
FROM fn_FindReports(‘11234’)
GO
中国站.长.站