SQLServer2005用户定义函数

在SQL Server 2005中，不仅可以使用内置函数，也可以使用用户定义函数。用户定义函数可以使用CREATE FUNCTION语句创建，其中包含一个或多个Transact-SQL语句，可用于封装代码以便重复使用。用户定义函数可以使用ALTER FUNCTION语句修改，也可以使用DROP FUNCTION语句删除。

1.建立用户定义函数

（1）使用CREATE FUNCTION语句来建立用户定义函数

用户定义函数可以使用CREATE FUNCTION语句来建立。CREATE FUNCTION语句的语法格式如下：

说明：

●函数名称必须符合标识符的规则。

●参数名称也必须符合标识符的规则，而且必须使用@符号作为第一个字符，对于每个参数，必须指定一种标量类型，还可以根据需要设置一个默认值。

●RETURNS子句为用户定义函数返回值指定一种标量数据类型。

●ENCRYPTION指出SQL Server加密包含CREATE FUNCTION语句文本的系统表字段，使用ENCRYPTION可以避免将函数作为SQL Server复制的一部分发布。

●SCHEMABINDING指定将函数绑定到它所引用的数据库对象，如果函数是用SCHEM-ABINDING选项创建的，则不能使用ALTER语句更改或使用DROP语句删除该函数引用的数据库对象。

●函数体位于BEGIN和END之间，由一系列Transact-SQL语句组成。

●RETURN子句指定标量函数返回的标量值。

例2-72 在学生管理数据库中创建一个名为udfLxp0的用户定义函数，用于计算两个日期之间相差的天数，该函数带有两个datetime数据类型的参数，第一个参数是指定过去的某一天，第二个参数用于接受当前系统日期（即系统函数GETDATE（）的返回值），然后在查询语句中调用这个用户定义函数。

注意：作者在这里临时将“学生基本情况表”中的年龄字段改为了出生日期，datetime数据类型，特此说明。

例2-73 求圆面积。

例2-74 编写1+2+…+n的函数。

（2）使用企业管理器来建立用户定义函数

用户也可以在企业管理器中建立用户定义函数。操作步骤如下：

1）在企业管理器中展开服务器组，然后展开一个服务器。

2）展开“数据库”文件夹，然后展开目标数据库并在该数据库下面单击“用户定义函数”节点。

3）从“操作”菜单中选择“新建用户定义的函数”，出现“用户定义函数属性—用户定义的新函数”界面在“文本”框中给出了CREATE FUNCTION语句的框架，如图2-75所示。

图2-75 通过企业管理器来创建用户定义函数

4）在“文本”框中输入用户定义函数的各个组成部分，包括所有者、函数名称、参数列表、返回类型及函数体等。

5）单击“检查语法”按钮，检查用于创建用户定义函数的Transact-SQL脚本语法。当语法检查通过时，单击“确定”按钮，将用户定义函数保存到数据库中。

此时，可以在详细信息窗格中看到所建立的用户定义函数。

2.修改用户定义函数

修改用户定义函数有两种方法：一种方法是使用ALTER FUNCTION语句，另一种方法是使用企业管理器。下面只介绍使用企业管理器修改用户定义函数的操作步骤。

1）在企业管理器中展开服务器组，然后展开一个服务器。

2）展开“数据库”文件夹，然后展开目标数据库并在该数据库下面单击“用户定义函数”节点。

3）在详细信息窗格中右键单击要修改的用户定义函数，然后选择“属性”命令。

4）当出现“用户定义函数属性—用户定义的新函数”界面时，在“文本”框中编辑CREATE FUNCTION语句的内容，如图2-76所示。

图2-76 “用户定义函数属性—用户定义的新函数”界面

5）单击“检查语法”按钮，检查用于创建用户定义函数的Transact-SQL脚本语法。当语法检查通过时，单击“确定”按钮，将所做的修改保存到数据库中。

3.删除用户定义函数

删除用户定义函数有两种方法：一种方法是使用DROP FUNCTION语句，另一种方法是使用企业管理器。

DROP FUNCTION语句从当前数据库中删除一个或多个用户定义函数，该语句的语法格式如下：

用户也可以使用企业管理器删除用户定义函数，具体操作步骤如下：

1）在企业管理器中展开服务器组，然后展开一个服务器。

2）展开“数据库”文件夹，然后展开目标数据库并在该数据库下面单击“用户定义函数”节点。

3）在详细信息窗格中选择一个或多个要删除的用户定义函数，然后从“操作”菜单中选择“删除”命令。

4）当出现“除去对象”对话框时，单击“全部除去”按钮，确认删除操作。

读一读

1.系统函数表(https://www.xing528.com)

在Microsoft SQL Server 2005中，常用的系统函数见表2-7。

表2-7 系统函数

2.日期函数表

在Microsoft SQL Server 2005中，常用的日期时间函数见表2-8。

表2-8 日期函数

（续）

3.字符串函数

在Microsoft SQL Server 2005中，常用的字符串函数见表2-9。

表2-9 字符串函数

4.数学函数

在SQL Server 2005中，常用的数学函数见表2-10所示。

表2-10 常用的数学函数

（续）

5.类型转换函数

1）CAST的语法格式如下。

2）CONVERT的语法格式如下。

参数说明如下。

●expression：任何有效的表达式。

●data_type：目标系统所提供的数据类型，包括bigint和sql_variant。不能使用用户自定义的数据类型。

●Length：为nchar、nvarchar、char、varchar、binary或varbinary数据类型的可选参数。

●Style：日期格式样式，借以将datetime或smalldatetime数据转换为字符数据（nchar、nvarchar、char、varchar、nchar或nvarchar类型）；或者字符串格式样式，借以将float、real、money或smallmoney数据转换为字符数据（nchar、nvarchar、char、var-char、nchar或nvarchar类型）。style参数及其对应的日期格式见表2-11。

表2-11 style参数及其对应的日期格式

（续）

6.用户自定义函数介绍

用户自定义函数可以采用零个或更多的输入参数并返回一个标量值或表，一个函数最多可以有1024个输入参数。用户自定义函数不支持输出参数。

SQL Server 2005支持3种用户定义函数，即标量函数、内嵌表值函数和多语句函数。标量函数通过RETURNS子句返回单个数据值，返回类型可以是除text、ntext、mage、cursor和timestamp之外的任何数据类型。对于标量函数，可以在BEGIN…END块中定义函数主体并给出返回标量值的语句系列。表值函数返回table。对于内嵌表值函数，没有函数主体，表是单个SELECT语句的结果集。对于多语句函数，可以在BEGIN…END块中定义函数主体并通过Transact-SQL语句生成记录，然后将记录插入将要返回的表中。

在用户定义函数中，可以使用下列语句。

●DECLARE语句：用于定义函数局部的数据变量和游标。

●赋值语句：为函数局部对象赋值，如使用SET给局部变量赋值。

●游标操作：引用在函数中声明、打开、关闭和释放的局部游标。在用户定义函数中，允许使用FETCH语句通过INTO子句给局部变量赋值，但不允许使用FETCH语句将数据返回到客户端。

●各种流程控制语句。

●SELECT语句：该语句包含带有表达式的选择列表，其中的表达式将值赋予函数的局部变量。

●INSERT、UPDATE和DELETE语句：用于修改函数的局部变量。

●EXECUTE语句：用户调用扩展存储过程。

在用户定义函数中，不允许使用会对每个调用返回不同数据的内置函数，见表2-12。

表2-12 不能用于用户定义函数中的内置函数

归纳总结

通过这次任务的实践，了解了函数的基本作用和用法，熟悉了系统提供的各类函数，掌握了用户自己建立函数的方法，为数据库的应用提供了方便，使数据库程序开发者开发出的代码可以重复调用。