用户自定义函数概述

1.函数

函数指的是一组编译好的Transact-SQL语句，他们可以带一个或一组数值做参数，也可以不带参数，它返回一个数值、数值集合，或执行一些操作。函数能够重复执行一些操作，从而避免不断重写代码。

2.函数类型

SQL  Server支持用户定义函数和内置函数，其定义和解释如下

（1）内置函数：是一组预定义的函数，是Transact-SQL语言的一部分，按Transact-SQL参考中定义的方式运行且不能修改。内置函数包含：

•聚合函数：

•系统函数：

•日期函数：

（2）用户自定义函数：用户定义函数是用户定义的Transact-SQL函数，它可以拥有零个、一个或多个参数，必须有返回值。返回值可以是单个数值（标量值），也可以是一个表。

用户自定义函数共包含以下三个类别

•标量函数

返回在 RETURNS 子句中定义的数据类型的单个数据值，可重复调用；

•内嵌表值函数

在内嵌表值函数中，RETURN子句中包含有一条单独的SELECT语句，该语句的结果构成了内嵌表值函数所返回的表，可替代视图，可用在T-SQL查询中允许表或视图表达式的地方，可包含附加的语句，使函数所包含的逻辑比视图的逻辑更强，返回表的内嵌表值函数还可替换返回单个结果集的存储过程；

•多语句表值函数

多语句表值函数的主体中允许使用的语句有：赋值、控制流、DECLARE、SELECT、游标操作、INSERT、UPDATE、DELETE、EXECUTE。

用户自定义函数创建、调用和查看

1. 用户自定义函数的创建

创建用户自定义函数的语句如下：

    CREATE FUNCTION [owner_name.] 

    function_name

    ([{@parameter_name 

    scalar_parameter_data_type[=default]}[,…]])

    RESULTS scalar_return_data_type

    [WITH <function_option>[,...n]]

    [AS]

    BEGIN

        function_body

        RETURN scalar_expression

    END

2.用户自定义函数的调用

调用用户自定义函数的语句如下：

    Use 数据库

    go

    Select 函数名(具体参数值|字段名)

    [from 表名]

    [where 条件]

注意：

（1）用户自定义函数的名称应是惟一的；

（2）一个函数最多可以定义1024个参数，每个参数前用“@”符号标明；

（3）参数的作用范围是整个函数；

（4）参数只能替代常量，不能替代表名、列名或其它数据库对象的名称；

（5）用户自定义函数不支持输出参数。

3.用户自定义函数的查看

自定义函数的名称保存在sysobjects系统表中，创建自定义函数的源代码保存在syscomments系统表中，查看用户自定义函数的方法有：

（1）使用系统存储过程查看，语句为：

    EXEC sp_help(sp_helptext) <function_name>

（2）使用系统表或系统信息架构视图：

在SQLServer中存在三个信息架构视图报告有关用户自定义函数的信息，分别为：ROUTINES，PARAMETERS，ROUTINE_COLUMNS，这些信息架构视图也是基于系统表sysobjects和syscomments实现的。

用户自定义函数的修改和删除

1. 修改用户自定义函数

修改用户自定义函数的语句为：

    ALTER FUNCTION [owner_name.] 

    function_name

    ([{@parameter_name 

    scalar_parameter_data_type[=default]}[,…]])

    RESULTS scalar_return_data_type

    [WITH <function_option>[,...n]]

    [AS]

    BEGIN

        function_body

        RETURN scalar_expression

    END

2.删除用户自定义函数

删除用户自定义函数的语句为：

    DROP  FUNCTION[owner.]function_name