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 语句调用扩展存储过程。
函数的确定性和副作用
函数可以是确定的或不确定的。如果任何时候用一组特定的输入值调用函数时返回的结果总是相同的,则这些函数为确定的。如果每次调用函数时即使用的是相同的一组特定输入值,返回的结果总是不同的,则这些函数为不确定的。
不确定的函数会产生副作用。副作用是更改数据库的某些全局状态,比如更新数据库表或某些外部资源,如文件或网络等(例如,修改文件或发送电子邮件消息)。
不允许在用户定义函数主体中内置不确定函数;这些不确定函数如下:

@@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
(0)

相关推荐

  • Sqlserver 自定义函数 Function使用介绍

    一.FUNCTION: 在sqlserver2008中有3中自定义函数:标量函数/内联表值函数/多语句表值函数,首先总结下他们语法的异同点: 同点: 1.创建定义是一样的: a, CREATE FUNCTION F_NAME(传入的参数名称 传入参数的类型) b,RETURNS 返回值类型 c,AS 异点:1.标量函数返回的是一个数据类型值,内联表值函数返回的是一个table,而多语句返回的是一个table的变量(类似前面两个的结合): 2.语法的结构:标量函数和多语句函数都是要有begin,,

  • Sql function 多行中的列合并为一行一列的方法

    复制代码 代码如下: CREATE TABLE tb(standards varchar(50), amount varchar(50), variation varchar(50),statuss varchar(50),Reason varchar(50)) insert into tb values('55','279','4','物量积压','加工人员设备不足;T排制作进度较慢;') insert into tb values('55','279','4','物量积压','部件人员不足;

  • SQl Function 创建函数实例介绍

    在SQL中系统已为我们提供了很非常丰富的函数:例:聚会函数avg, sum,count,max,min 日期函数:Day,Month,Year等等  为我们日常开发节省很多时间  但是有一些特殊需求的话SQL 也提供自己创建函数的功能下面举一小例子:需求: 某一订单表 编号,订单号,产品名称 要知道 某一订单号的产品名称 名称之间用逗号(,)隔开 如下图: 我们创建一个标题函数: StrName 创建过程类似创建存储过程 如下: 点击右键创建 表值函数与标量函数 从名称就可以分出来  一个返回表

  • SQL Function 自定义函数详解

    目录 产生背景(已经有了存储过程,为什么还要使用自定义函数) 发展历史 构成 使用方法 适用范围 注意事项 疑问 内容 产生背景(已经有了存储过程,为什么还要使用自定义函数) 与存储过程的区别(存在的意义): 1.     能够在select等SQL语句中直接使用自定义函数,存储过程不行. 2.     自定义函数可以调用其他函数,也可以调用自己(递归) 3.     可以在表列和 CHECK 约束中使用自定义函数来实现特殊列或约束 4.       自定义函数不能有任何副作用.函数副作用是指对

  • MySQL 自定义函数CREATE FUNCTION示例

    mysql> mysql> delimiter $$ mysql> mysql> CREATE FUNCTION myFunction -> (in_string VARCHAR(255), -> in_find_str VARCHAR(20), -> in_repl_str VARCHAR(20)) -> -> RETURNS VARCHAR(255) -> BEGIN -> DECLARE l_new_string VARCHAR(25

  • MySQL 5.7 create VIEW or FUNCTION or PROCEDURE

    1.视图 a. CREATE ALGORITHM = UNDEFINED DEFINER = `root`@`localhost` SQL SECURITY INVOKER VIEW `sakila`.`actor_info` AS SELECT `a`.`actor_id` AS `actor_id`, `a`.`first_name` AS `first_name`, `a`.`last_name` AS `last_name`, GROUP_CONCAT(DISTINCT CONCAT(`

  • CREATE FUNCTION sqlserver用户定义函数

    创建用户定义函数,它是返回值的已保存的 Transact-SQL 例程.用户定义函数不能用于执行一组修改全局数据库状态的操作.与系统函数一样,用户定义函数可以从查询中唤醒调用.也可以像存储过程一样,通过 EXECUTE 语句执行. 用户定义函数用 ALTER FUNCTION 修改,用 DROP FUNCTION 除去. 语法 标量函数 CREATE FUNCTION [ owner_name.] function_name ( [ { @parameter_name [AS] scalar_p

  • SQL Server创建用户定义函数

    一.UDF的定义 和存储过程很相似,用户自定义函数也是一组有序的T-SQL语句,UDF被预先优化和编译并且可以作为一个单元来进行调用. UDF和存储过程的主要区别在于返回结果的方式: 使用UDF时可传入参数,但不可传出参数.输出参数的概念被更为健壮的返回值取代了. 和系统函数一样,可以返回标量值,这个值的好处是它并不像在存储过程中那样只限于整形数据类型,而是可以返回大多数SQL Server数据类型. UDF有以下两种类型: 返回标量值的UDF. 返回表的UDF. 创建语法: CREATE FU

  • js function定义函数的几种不错方法

    js function定义函数的4种方法 1.最基本的作为一个本本分分的函数声明使用. 复制代码代码如下: 复制代码 代码如下: function func(){} 或 var func=function(){}; 2.作为一个类构造器使用: 复制代码代码如下: 复制代码 代码如下: function class(){} class.prototype={}; var item=new class(); 3.作为闭包使用: 复制代码代码如下: 复制代码 代码如下: (function(){ //

  • JavaScript 中定义函数用 var foo = function () {} 和 function foo()区别介绍

    某天写代码突然县道这个问题,顺势总结一波 JavaScript 函数和变量声明的"提前"(hoist)行为 简单的说 如果我们使用 匿名函数 var a = {} 这种方式, 编译后变量声明a 会"被提前"了,但是他的赋值(也就是a)并不会被提前. 也就是,匿名函数只有在被调用时才被初始化. 如果使用 function a () {}; 这种方式, 编译后函数声明和他的赋值都会被提前. 也就是说函数声明过程在整个程序执行之前的预处理就完成了,所以只要处于同一个作用域

  • VBS基础篇 - VBScript过程(使用sub 与 Function定义函数)

    我们一般都是在重复使用一些功能的时候,例如循环输出内容.分页的时候都会使用过程函数 Sub过程 Sub 过程是包含在 Sub 和 End Sub 语句之间的一组 VBScript 语句.如果 Sub 过程无任何参数,则 Sub 语句必须包含空括号 (). 实例代码如下: Call GetName() '调用Sub过程 Sub GetName() MsgBox "我是我们" '输出字符串 End Sub Sub 过程可以使用参数(由调用过程传递的常数.变量或表达式).实例代码如下: Ca

  • SQLSERVER加密解密函数(非对称密钥 证书加密 对称密钥)使用方法代码

    ENCRYPTBYASYMKEY() --非对称密钥ENCRYPTBYCERT()   --证书加密ENCRYPTBYKEY()   --对称密钥ENCRYPTBYPASSPHRASE()  --通行短语(PassPhrase)加密 复制代码 代码如下: --SQLSERVER中的加密函数 2013-7-11ENCRYPTBYASYMKEY() --非对称密钥ENCRYPTBYCERT()   --证书加密ENCRYPTBYKEY()   --对称密钥ENCRYPTBYPASSPHRASE() 

  • SQLServer RANK() 排名函数的使用

    本文主要介绍了SQLServer RANK() 排名函数的使用,具体如下: -- 例子表数据 SELECT * FROM test; -- 统计分数 SELECT name,SUM(achievement) achievement FROM test GROUP BY name; -- 按统计分数做排行 SELECT RANK() OVER( ORDER BY SUM(achievement) desc) 排行,name,SUM(achievement) achievement FROM tes

  • PowerShell定义函数参数的2种方法和传参方法实例

    本文介绍PowerShell自定义函数定义参数的两种方法,一种是将参数列表放到函数名之后,就跟其它语言定义函数参数一样:另一种则是PowerShell独有的方式,那就是使用param关键词. 先来看看第一种定义参数的方式--将参数列表放到函数名之后 举个例子先看看: 复制代码 代码如下: function Test-Function($参数名1='默认参数值1', $参数名2='默认参数值2') {     Write-Host "参数1=$参数名1,参数2=$参数名2"; } 这种方

  • Windows Powershell 定义函数

    函数是自定义的Powershell代码,有三个原则: 简短:函数名简短,并且显而易见. 聚合:函数可以完成多个操作. 封装和扩展:将一批Powershell语句进行封装,实现全新的功能需求. 函数的结构由三部分组成:函数名,参数,函数体 复制代码 代码如下: Function FuncName (args[]) {       code; } 使用函数作为别名 假如Powershell不支持"cd.." 命令,你可以通过定义函数实现这个功能: 复制代码 代码如下: PS C:Power

随机推荐