日期：2012-07-30 15:00:00 来源：本站整理
CREATE FUNCTION sqlserver用户定义函数[MSSQL防范]
本文“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
拥有该用户定义函数的用户 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 语句调用扩大存储历程.
函数的肯定性和副作用
函数可以是肯定的或不肯定的.假如任什么时刻候用一组特定的输入值调用函数时返回的后果老是相同的,则这些函数为肯定的.假如每次调用函数时即便用的是相同的一组特定输入值,返回的后果老是差别的,则这些函数为不肯定的.
不肯定的函数会产生副作用.副作用是更改数据库的某些全局状况,比方更新数据库表或某些外部资源,如文件或网络等（比方,改正文件或发送电子邮件消息）.
不答应在用户定义函数主体中内置不肯定函数;这些不肯定函数以下:
| @@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
以上是“CREATE FUNCTION sqlserver用户定义函数[MSSQL防范]”的内容，如果你对以上该文章内容感兴趣，你可以看看七道奇为您推荐以下文章：
| 本文地址： | 与您的QQ/BBS好友分享! |
- 好的评价 如果您觉得此文章好，就请您
0%(0) 
- 差的评价 如果您觉得此文章差，就请您
0%(0) 
中查找“CREATE FUNCTION sqlserver用户定义函数”更多相关内容
中查找“CREATE FUNCTION sqlserver用户定义函数”更多相关内容评论内容只代表网友观点，与本站立场无关！
评论摘要(共 0 条，得分 0 分，平均 0 分)
查看完整评论