第十章 SQL Server函数
10.1 常用系统函数
10.2 创建自定义函数
10.3 用户自定义函数类型
10.4 修改和删除自定义函数
10.5 案例中的自定义函数练 习 题目录
10.1 常用系统函数
10.1.1 标量函数
10.1.2 聚合函数
10.1.1 标量函数标量函数进行单一值操作，
返回单一值。只要表达式有效即可使用标量函数。
1.日期和时间函数对日期和时间输入值执行操作，并返回一个字符串、数字值或日期和时间值。
1）
DATENAME(datepart,date)：
返回某日期指定部分的字符串。
参数描述 datepart,指定应返回的日期部分；
date,指定的日期。
表 10.1 SQL Server识别的 datepart参数日期部分 描述 例 结果
year 指定返回年份 select datename(year,'03/12/1998') 1998
month 指定返回月份 select datename(month,'03/12/1998') 03
day 指定返回日期 select datename(day,'03/12/1998') 12
weekday 指定返回星期 select datename(weekday,'03/12/1998') 星期四
Hour 指定返回钟点 select datename(hour,'14:02:56') 14
minute 指定返回分钟 select datename(minute,'14:02:56') 2
second 指定返回秒钟 select datename(second,'14:02:56') 56
2） GETDATE( ),返回当前系统日期和时间。
例,select getdate() 结果：
2005-06-20 13:55:37
3） DAY( date ),返回代表指定日期的天的日期部分的整数。
参数描述 date：指定的日期
例,select day('03/12/1998')
结果,12
4） MONTH(date),返回代表指定日期月份的整数。
参数描述 date：指定的日期
例,select month('03/12/1998')
结果,3
5） YEAR(date),返回表示指定日期中的年份的整数。
参数描述 date：指定的日期例,select
month('03/12/1998') 结果,1998
2.数学函数对作为参数提供的输入值执行计算，
并返回一个数字值。常用的数学函数有：
1） ABS(x),返回给定数字表达式的绝对值。
例,select abs(-12) 结果,12
2） ACOS(x),返回以弧度表示的角度值。
参数描述 x,是 float 或 real 类型的表达式，其取值范围从 -1 到 1。
例,select acos（ -1） 结果：
3.1415926535897931
3） ASIN(x),返回以弧度表示的角度值。
参数描述 x,是 float 或 real 类型的表达式，其取值范围从 -1 到 1。
4） ATAN(x),返回以弧度表示的角度值。
参数描述 x,是 float类型的表达式 。
5） CEILING(x),返回大于或等于所给数字表达式的最小整数。
例,select ceiling(56.3),ceiling
(-56.3) 结果,57,-56
6） COS(x),返回给定表达式中给定角度的三角余弦值。
参数描述 x,是 float类型的表达式。
7） DEGREES(x),返回以弧度表示的角度值。
例,select degrees(pi()) 结果：
180
8） EXP(x),返回给定表达式的指数值。
9） FLOOR(x),返回小于或等于所给数字表达式的最大整数。
例,select floor(56.3),floor(-56.3)
结果,56,-57
10） LOG(x),返回给定表达式的自然对数。
11） PI( ),返回 PI 的常量值，结果
3.14159265358979。
12） POWER(x,y),返回 x的 y次方。
例,select power（ 2,3） 结果,8
13） RAND( )：返回 0~1之间的随机数。
14） ROUND(x,y),返回以 y指定的精度进行四舍五入后的数值。
参数描述 y：指定的精度。当 y为正数时,x四舍五入为 y 所指定的小数位数。当 y为负数时,x则按 y所指定的在小数点的左边四舍五入。
例,select
round(56.34,1),round(56.34,-1) 结果,56.30,60
15） SIN(x),返回给定表达式中给定角度的三角正弦值。
参数描述 x,是 float类型的表达式。
16） SQUARE(x),返回给定表达式的平方。
例,select square(5) 结果,25
17） SQRT(x),返回给定表达式的平方根。
例,select sqrt(16)
结果,4
3.元数据函数返回有关数据库和数据库对象的信息。
1） COL_LENGTH(table,column),返回列的长度，且以字节为单位。
参数描述 table：表名。 column：列名。
例,use student
select col_length('
专业 ','专业名称 ')
结果,20
2） COL_NAME( table_id,column_id )：
返回数据库列的名称。
参数描述 table_id,column_id：表标识号，列标识号。
例,use student
select col_name(object_id(?专业 ’ ),2)
结果：专业名称注：此例返回? student”数据库,? 专业? 表中第二列的名称。
3） DB_ID(db_name)：返回数据库标识号。
参数描述 db_name：用来返回相应数据库
ID 的数据库名
4） DB_NAME(db_id),返回数据库名。
参数描述 db_id：是应返回数据库的标识号。
4.字符串函数对字符串输入值执行操作，返回字符串或数字值。
1） ASCII(str),返回字符表达式最左端字符的 ASCII 代码值。
参数描述 str,char 或
varchar的表达式
2） CHAR(x)：将 ASCII 代码转换为字符的字符串函数。
参数描述 x,介于 0 和 255
之间的整数。
控制字符 值制表符 CHAR(9)
换行符 CHAR(10)
回车 CHAR(13)
表 10.2 字符串中常用的控制字符
3） LEFT(str,x)：返回字符串中从左边开始指定个数的字符。
参数描述 str：指定字符串； x：
指定返回字符的个数。
例,select left(“command”,4)
结果,comm
4） LEN(str),返回字符串的字符个数，
不包含尾随空格。
参数描述 str：将进行长度计算的字符串。
5） LOWER(str),将大写字符数据转换为小写字符数据，返回类型 varchar。
参数描述 str,是字符或二进制数据表达式。
6） LTRIM(str)：删除起始空格，返回类型
varchar。
参数描述 str：字符或二进制数据表达式。
7） REPLACE(str1,str2,str3)：用第三个表达式替换第一个字符串表达式中出现的所有第二个给定字符串表达式。
参数描述 str1：包含待替换字符串的表达式； str2,待替换字符串表达式； str3：
替换用的字符串表达式。
8） REPLICATE(str,x),以指定的次数重复字符表达式。
参数描述 str,可以是常量或变量，
也可以是字符列或二进制数据列。 x：
指定重复次数。
9） REVERSE(str)：将指定字符串逆序排列。
参数描述 str：待排列的字符串。
10） RIGHT(str,x)：返回字符串中从右边开始指定个数的字符。
参数描述 str：指定字符串； x：指定返回字符的个数。
例,select right(“command”,4) 结果：
mand
11） RTRIM(str),删除尾随空格，返回类型 varchar。
参数描述 str：字符或二进制数据表达式。
12） SPACE(x),产生指定个数的空格。
参数描述 x：空格的个数。
13） SUBSTRING(str,start,len)：截取指定的部分字符串。
参数描述 str：待截取的表达式；
strat：截取部分的起始位置； len：
截取的长度。
14） UPPER(str),将小写字符数据转换为大写字符数据，返回类型 varchar。
参数描述 str,是字符或二进制数据表达式。
5.系统函数对 SQL Server 中的值、
对象和设置进行操作并返回有关信息。
1） APP_NAME( ),返回当前会话的应用程序名称。
2） CAST(expression AS data_type)：
将某种数据类型的表达式转换为另一种数据类型。
参数描述 expression：待转换的表达式； data_type：表达式新的数据类型。
例,selcet cast(123 as
char(1)) 结果：将数字数据 123
转换成长度为 1个字节的字符型数据。
3） CONVERT (data_type[(length)],
expression [,style])：同 cast函数。
参数描述 data_type：表达式新的数据类型；
length：表达式长度。
expression：待转换的表达式。
style：日期或字符串格式样式。
4） CURRENT_USER( )：返回当前的用户。此函数等价于 USER_NAME()。
5） HOST_ID( )：返回工作站标识号。
6） HOST_NAME( )：返回工作站名称。
7） USER_NAME(id),返回给定标识号的用户数据库用户名。
参数描述 id：用户名标识号。
id省略时 user_name( )返回当前用户。
10.1.2 聚合函数对一组值执行计算并返回单一的值。
Transact-SQL 编程语言提供下列聚合函数：
1.AVG([ ALL | DISTINCT ]expression),返回组中值的平均值。
参数描述 ALL：对所有的值进行聚合函数运算。
DISTINCT：指定 COUNT 返回唯一非空值的数量。
expression,待求平均值的表达式。
例：统计学生平均成绩。
use student
select 课程注册,学号,
学生,姓名,
avg(成绩 ) as 平均成绩
from 学生,课程注册
where 学生,学号 =课程注册,学号
group by 课程注册,学号,学生,姓名结果：
图 10.3 avg函数返回结果
2.COUNT({ [ ALL | DISTINCT ]
expression } | * ))：返回组中项目的数量。
参数描述 ALL：对所有的值进行聚合函数运算。
DISTINCT：指定 COUNT
返回唯一非空值的数量，
去除重复值。
expression,待计数的表达式。
*：指定应该计算所有行以返回表中行的总数。
例：统计学生所选课程总数。
use student
select count(distinct 课程号 )
from 课程注册结果,4
3.MAX([ ALL | DISTINCT ]
expression)：返回表达式的最大值。
参数描述 ALL：对所有的值进行聚合函数运算。
DISTINCT：指定 COUNT返回唯一非空值的数量，去除重复值。
expression,待求最大值的表达式。
例：统计每门课程学生成绩的最大值。
use student
select 课程名,max(成绩 ) from
课程注册,课程
where 课程,课程号 =课程注册,课程号
group by 课程注册,课程号,课程,课程名结果：
图 10.4 max函数返回结果
4.MIN([ ALL | DISTINCT ]
expression)：返回表达式的最大值。
参数描述 ALL：对所有的值进行聚合函数运算。
DISTINCT：指定 COUNT
返回唯一非空值的数量，
去除重复值。
expression,待求最小值的表达式。
5.SUM([ ALL | DISTINCT ] expression)：
返回表达式中所有值的和，或只返回 DISTINCT 值。 SUM 只能用于数字列。
参数描述 ALL：对所有的值进行聚合函数运算。
DISTINCT：指定 SUM 返回唯一值的和，即若有相同值则只相加一次。
expression,待求最小值的表达式。
10.2 创建自定义函数
1.用户定义函数函数是由一个或多个 Transact-SQL
语句组成的子程序，可用于封装代码以便重新使用。 Microsoft SQL Server 2000
并不将用户限制在定义为 Transact-SQL 语言一部分的内置函数上，而是允许用户创建自己的用户定义函数。我们可使用
CREATE FUNCTION 语句创建、使用 ALTER
FUNCTION语句修改、以及使用 DROP
FUNCTION 语句除去用户定义函数。用户定义的函数名必须唯一且符合命名规则。
函数名的命名规则：
1）有效字符,SQL 函数名必须以一个字母
（ a-z以及带可区别标记的字母以及非拉丁字母）,@(at符 ),#(数字符 )或下划线开头 （ _）开头，跟在首字符后面的字符可以是字母 ( A - Z,a - z以及其他语言的任何字母符号 )、数字 ( 0 - 9以及其他语言的任何其他数字符号 ),@ ( a t符 )、
$ (美元符 ),# (数字符 )或 _ (下划线 )。
并不是所有以 @符号开头的对象就意味着它是一个局部变量,SQL Server有许多以
@@开头的函数,例如：函数 @@ERROR能返回最后执行的 Transact-SQL 语句的错误代码。
2）有效长度：函数名的有效长度为１～ 128个字符。
3） SQL Server保留的关键字不能用作函数名。
4）嵌入的空格或其他特殊字符不能在函数名中使用。
2.调用用户定义函数
1）当调用标量用户定义函数时，必须提供至少由两部分组成的名称：
SELECT *,
MyUser.MyScalarFunction()
FROM MyTable
2）可以使用一个部分构成的名称调用表值函数：
SELECT *
FROM MyTableFunction()
10.3 用户自定义函数类型
10.3.1 标量函数
10.3.2 内嵌表值函数
10.3.3 多语句表值函数
10.3.1 标量函数标量函数返回在 RETURNS 子句中定义的类型的单个数据值。可以使用所有标量数据类型，包括 bigint 和 sql_variant。
1.标量函数的创建
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
参数描述 owner_name：拥有该用户定义函数的用户 ID 的名称。
function_name：
用户定义函数的名称。该名称在数据库中必须是唯一的。
@parameter_name：用户定义函数的参数。使用 @ 符号作为第一个字符来指定参数名称。参数名称必须符合标识符的规则。每个函数的参数仅用于该函数本身；
相同的参数名称可以用在其它函数中。
scalar_parameter_data_type：
参数的数据类型。
scalar_return_data_type：是标量用户定义函数的返回值。
function_body,是一系列合
Transact-SQL 语句。
scalar_expression：指定标量函数返回的标量值。
10.3.2 内嵌表值函数内嵌用户定义函数是返回 table 的用户定义函数的子集。内嵌函数可用于实现参数化视图的功能。
1,内嵌表值函数的创建
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 [ ] ]
参数描述 select-stmt：是定义内嵌表值函数返回值的单个
SELECT 语句。
其他同创建标量函数参数描述。
10.3.3 多语句表值函数对于多语句表值函数，在
BEGIN...END 块中定义的函数主体包含 TRANSACT-SQL 语句，这些语句可生成行并将行插入将返回的表中。
1,多语句表值函数的创建
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 ] )
参数描述 @return_variable,
table 变量，用于存储和累积应作为函数值返回的行。
function_body：一系列填充表返回变量的 Transact-
SQL 语句。
ENCRYPTION：指出 SQL Server 加密包含
CREATE FUNCTION 语句文本的系统表列。
SCHEMABINDING：指定将函数绑定到它所引用的数据库对象。
其他同创建标量函数参数描述。
10.4 修改和删除自定义函数
10.4.1 修改用户自定义函数
10.4.2 删除用户自定义函数
10.4.1 修改自定义函数
1,使用 ALTER FUNCTION命令修改自定义函数更改先前由 CREATE FUNCTION 语句创建的现有用户定义函数，但不会更改权限，也不影响相关的函数、存储过程或触发器。
1）标量函数
ALTER FUNCTION [ owner_name,]
function_name
( [ { @parameter_name
scalar_parameter_data_type
[ = default ] } [,...n ] ] )
RETURNS
scalar_return_data_type
[ WITH < function_option>
[,...n] ]
[ AS ]
BEGIN
function_body
RETURN scalar_expression
END
参数描述 owner_name：拥有待更改的用户定义函数的用户 ID
名。
function_name是要更改的用户定义函数。
@parameter_name：用户定义函数的参数。
scalar_parameter_data_type：参数的数据类型。
scalar_return_data_type：是标量用户定义函数的返回值。
function_body,是一系列合起来求得标量值的 Transact-SQL 语句。
scalar_expression：指定标量函数返回的标量值。
2）内嵌表值函数
ALTER FUNCTION
[ owner_name,]
function_name
( [ { @parameter_name
scalar_parameter_data_ty
pe [ = default ] } [,...n ] ] )
RETURNS TABLE
[ WITH < function_option >
[,...n ] ]
[ AS ]
RETURN [ ( ) select-
stmt [ ] ]
参数描述 select-stmt：
是定义内嵌表值函数返回值的单个 SELECT 语句。
其他同更改标量函数参数描述。
3）多语句表值函数
ALTER FUNCTION [ owner_name,]
function_name
( [ { @parameter_name
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 ] )
参数描述 @return_variable,TABLE 变量，
用于存储和累积应作为函数值返回的行。
function_body：一系列填充表返回变量的 Transact-SQL 语句
ENCRYPTION：指出 SQL Server 加密包含
CREATE FUNCTION 语句文本的系统表列。
SCHEMABINDING：指定将函数绑定到它所引用的数据库对象。
其他同创建标量函数参数描述。
2.其它方法
10.4.2 删除自定义函数
1,使用 DROP FUNCTION命令删除自定义函数从当前数据库中删除一个或多个用户定义的函数。
DROP FUNCTION { [ owner_name,]
function_name } [,...n ]
参数描述 function_name：是要删除的用户定义的函数名称。可以选择是否指定所有者名称，但能指定服务器名称和数据库名称。
n：是表示可以指定多个用户定义的函数的占位符。
2.其它方法
10.5 案例中的自定义函数创建自定义函数 top_grade( ),根据输入的系部代码统计出该系平均成绩最高的前三名同学的信息。
创建函数的代码如下：
use student
go
CREATE FUNCTION
top_grade(@dept_id char(2))
RETURNS TABLE
AS
RETURN (select top 3
课程注册,学号,
学生,姓名,
avg(成绩 ) as 平均成绩
from 学生,课程注册,专业
where 学生,学号 =课程注册,学号
and 课程注册,专业代码 =专业,
专业代码 and 专业,系部代码
=@dept_id
group by 课程注册,学号,学生,
姓名
order by 平均成绩 asc
)
go
使用函数的代码：
use student
go
select * from top_grade('01')
--‘01’为系部代码
go
结果将返回系部代码为 ‘ 01’的系内成绩最高的三名学生的信息。
练 习 题
1,用户自定义函数分为几类？描述各类中函数的作用。
2,使用 SQL命令创建自定义标量函数的语法规则是什么？
3,怎样使用已经定义好的用户自定义函数？
10.1 常用系统函数
10.2 创建自定义函数
10.3 用户自定义函数类型
10.4 修改和删除自定义函数
10.5 案例中的自定义函数练 习 题目录
10.1 常用系统函数
10.1.1 标量函数
10.1.2 聚合函数
10.1.1 标量函数标量函数进行单一值操作，
返回单一值。只要表达式有效即可使用标量函数。
1.日期和时间函数对日期和时间输入值执行操作，并返回一个字符串、数字值或日期和时间值。
1）
DATENAME(datepart,date)：
返回某日期指定部分的字符串。
参数描述 datepart,指定应返回的日期部分；
date,指定的日期。
表 10.1 SQL Server识别的 datepart参数日期部分 描述 例 结果
year 指定返回年份 select datename(year,'03/12/1998') 1998
month 指定返回月份 select datename(month,'03/12/1998') 03
day 指定返回日期 select datename(day,'03/12/1998') 12
weekday 指定返回星期 select datename(weekday,'03/12/1998') 星期四
Hour 指定返回钟点 select datename(hour,'14:02:56') 14
minute 指定返回分钟 select datename(minute,'14:02:56') 2
second 指定返回秒钟 select datename(second,'14:02:56') 56
2） GETDATE( ),返回当前系统日期和时间。
例,select getdate() 结果：
2005-06-20 13:55:37
3） DAY( date ),返回代表指定日期的天的日期部分的整数。
参数描述 date：指定的日期
例,select day('03/12/1998')
结果,12
4） MONTH(date),返回代表指定日期月份的整数。
参数描述 date：指定的日期
例,select month('03/12/1998')
结果,3
5） YEAR(date),返回表示指定日期中的年份的整数。
参数描述 date：指定的日期例,select
month('03/12/1998') 结果,1998
2.数学函数对作为参数提供的输入值执行计算，
并返回一个数字值。常用的数学函数有：
1） ABS(x),返回给定数字表达式的绝对值。
例,select abs(-12) 结果,12
2） ACOS(x),返回以弧度表示的角度值。
参数描述 x,是 float 或 real 类型的表达式，其取值范围从 -1 到 1。
例,select acos（ -1） 结果：
3.1415926535897931
3） ASIN(x),返回以弧度表示的角度值。
参数描述 x,是 float 或 real 类型的表达式，其取值范围从 -1 到 1。
4） ATAN(x),返回以弧度表示的角度值。
参数描述 x,是 float类型的表达式 。
5） CEILING(x),返回大于或等于所给数字表达式的最小整数。
例,select ceiling(56.3),ceiling
(-56.3) 结果,57,-56
6） COS(x),返回给定表达式中给定角度的三角余弦值。
参数描述 x,是 float类型的表达式。
7） DEGREES(x),返回以弧度表示的角度值。
例,select degrees(pi()) 结果：
180
8） EXP(x),返回给定表达式的指数值。
9） FLOOR(x),返回小于或等于所给数字表达式的最大整数。
例,select floor(56.3),floor(-56.3)
结果,56,-57
10） LOG(x),返回给定表达式的自然对数。
11） PI( ),返回 PI 的常量值，结果
3.14159265358979。
12） POWER(x,y),返回 x的 y次方。
例,select power（ 2,3） 结果,8
13） RAND( )：返回 0~1之间的随机数。
14） ROUND(x,y),返回以 y指定的精度进行四舍五入后的数值。
参数描述 y：指定的精度。当 y为正数时,x四舍五入为 y 所指定的小数位数。当 y为负数时,x则按 y所指定的在小数点的左边四舍五入。
例,select
round(56.34,1),round(56.34,-1) 结果,56.30,60
15） SIN(x),返回给定表达式中给定角度的三角正弦值。
参数描述 x,是 float类型的表达式。
16） SQUARE(x),返回给定表达式的平方。
例,select square(5) 结果,25
17） SQRT(x),返回给定表达式的平方根。
例,select sqrt(16)
结果,4
3.元数据函数返回有关数据库和数据库对象的信息。
1） COL_LENGTH(table,column),返回列的长度，且以字节为单位。
参数描述 table：表名。 column：列名。
例,use student
select col_length('
专业 ','专业名称 ')
结果,20
2） COL_NAME( table_id,column_id )：
返回数据库列的名称。
参数描述 table_id,column_id：表标识号，列标识号。
例,use student
select col_name(object_id(?专业 ’ ),2)
结果：专业名称注：此例返回? student”数据库,? 专业? 表中第二列的名称。
3） DB_ID(db_name)：返回数据库标识号。
参数描述 db_name：用来返回相应数据库
ID 的数据库名
4） DB_NAME(db_id),返回数据库名。
参数描述 db_id：是应返回数据库的标识号。
4.字符串函数对字符串输入值执行操作，返回字符串或数字值。
1） ASCII(str),返回字符表达式最左端字符的 ASCII 代码值。
参数描述 str,char 或
varchar的表达式
2） CHAR(x)：将 ASCII 代码转换为字符的字符串函数。
参数描述 x,介于 0 和 255
之间的整数。
控制字符 值制表符 CHAR(9)
换行符 CHAR(10)
回车 CHAR(13)
表 10.2 字符串中常用的控制字符
3） LEFT(str,x)：返回字符串中从左边开始指定个数的字符。
参数描述 str：指定字符串； x：
指定返回字符的个数。
例,select left(“command”,4)
结果,comm
4） LEN(str),返回字符串的字符个数，
不包含尾随空格。
参数描述 str：将进行长度计算的字符串。
5） LOWER(str),将大写字符数据转换为小写字符数据，返回类型 varchar。
参数描述 str,是字符或二进制数据表达式。
6） LTRIM(str)：删除起始空格，返回类型
varchar。
参数描述 str：字符或二进制数据表达式。
7） REPLACE(str1,str2,str3)：用第三个表达式替换第一个字符串表达式中出现的所有第二个给定字符串表达式。
参数描述 str1：包含待替换字符串的表达式； str2,待替换字符串表达式； str3：
替换用的字符串表达式。
8） REPLICATE(str,x),以指定的次数重复字符表达式。
参数描述 str,可以是常量或变量，
也可以是字符列或二进制数据列。 x：
指定重复次数。
9） REVERSE(str)：将指定字符串逆序排列。
参数描述 str：待排列的字符串。
10） RIGHT(str,x)：返回字符串中从右边开始指定个数的字符。
参数描述 str：指定字符串； x：指定返回字符的个数。
例,select right(“command”,4) 结果：
mand
11） RTRIM(str),删除尾随空格，返回类型 varchar。
参数描述 str：字符或二进制数据表达式。
12） SPACE(x),产生指定个数的空格。
参数描述 x：空格的个数。
13） SUBSTRING(str,start,len)：截取指定的部分字符串。
参数描述 str：待截取的表达式；
strat：截取部分的起始位置； len：
截取的长度。
14） UPPER(str),将小写字符数据转换为大写字符数据，返回类型 varchar。
参数描述 str,是字符或二进制数据表达式。
5.系统函数对 SQL Server 中的值、
对象和设置进行操作并返回有关信息。
1） APP_NAME( ),返回当前会话的应用程序名称。
2） CAST(expression AS data_type)：
将某种数据类型的表达式转换为另一种数据类型。
参数描述 expression：待转换的表达式； data_type：表达式新的数据类型。
例,selcet cast(123 as
char(1)) 结果：将数字数据 123
转换成长度为 1个字节的字符型数据。
3） CONVERT (data_type[(length)],
expression [,style])：同 cast函数。
参数描述 data_type：表达式新的数据类型；
length：表达式长度。
expression：待转换的表达式。
style：日期或字符串格式样式。
4） CURRENT_USER( )：返回当前的用户。此函数等价于 USER_NAME()。
5） HOST_ID( )：返回工作站标识号。
6） HOST_NAME( )：返回工作站名称。
7） USER_NAME(id),返回给定标识号的用户数据库用户名。
参数描述 id：用户名标识号。
id省略时 user_name( )返回当前用户。
10.1.2 聚合函数对一组值执行计算并返回单一的值。
Transact-SQL 编程语言提供下列聚合函数：
1.AVG([ ALL | DISTINCT ]expression),返回组中值的平均值。
参数描述 ALL：对所有的值进行聚合函数运算。
DISTINCT：指定 COUNT 返回唯一非空值的数量。
expression,待求平均值的表达式。
例：统计学生平均成绩。
use student
select 课程注册,学号,
学生,姓名,
avg(成绩 ) as 平均成绩
from 学生,课程注册
where 学生,学号 =课程注册,学号
group by 课程注册,学号,学生,姓名结果：
图 10.3 avg函数返回结果
2.COUNT({ [ ALL | DISTINCT ]
expression } | * ))：返回组中项目的数量。
参数描述 ALL：对所有的值进行聚合函数运算。
DISTINCT：指定 COUNT
返回唯一非空值的数量，
去除重复值。
expression,待计数的表达式。
*：指定应该计算所有行以返回表中行的总数。
例：统计学生所选课程总数。
use student
select count(distinct 课程号 )
from 课程注册结果,4
3.MAX([ ALL | DISTINCT ]
expression)：返回表达式的最大值。
参数描述 ALL：对所有的值进行聚合函数运算。
DISTINCT：指定 COUNT返回唯一非空值的数量，去除重复值。
expression,待求最大值的表达式。
例：统计每门课程学生成绩的最大值。
use student
select 课程名,max(成绩 ) from
课程注册,课程
where 课程,课程号 =课程注册,课程号
group by 课程注册,课程号,课程,课程名结果：
图 10.4 max函数返回结果
4.MIN([ ALL | DISTINCT ]
expression)：返回表达式的最大值。
参数描述 ALL：对所有的值进行聚合函数运算。
DISTINCT：指定 COUNT
返回唯一非空值的数量，
去除重复值。
expression,待求最小值的表达式。
5.SUM([ ALL | DISTINCT ] expression)：
返回表达式中所有值的和，或只返回 DISTINCT 值。 SUM 只能用于数字列。
参数描述 ALL：对所有的值进行聚合函数运算。
DISTINCT：指定 SUM 返回唯一值的和，即若有相同值则只相加一次。
expression,待求最小值的表达式。
10.2 创建自定义函数
1.用户定义函数函数是由一个或多个 Transact-SQL
语句组成的子程序，可用于封装代码以便重新使用。 Microsoft SQL Server 2000
并不将用户限制在定义为 Transact-SQL 语言一部分的内置函数上，而是允许用户创建自己的用户定义函数。我们可使用
CREATE FUNCTION 语句创建、使用 ALTER
FUNCTION语句修改、以及使用 DROP
FUNCTION 语句除去用户定义函数。用户定义的函数名必须唯一且符合命名规则。
函数名的命名规则：
1）有效字符,SQL 函数名必须以一个字母
（ a-z以及带可区别标记的字母以及非拉丁字母）,@(at符 ),#(数字符 )或下划线开头 （ _）开头，跟在首字符后面的字符可以是字母 ( A - Z,a - z以及其他语言的任何字母符号 )、数字 ( 0 - 9以及其他语言的任何其他数字符号 ),@ ( a t符 )、
$ (美元符 ),# (数字符 )或 _ (下划线 )。
并不是所有以 @符号开头的对象就意味着它是一个局部变量,SQL Server有许多以
@@开头的函数,例如：函数 @@ERROR能返回最后执行的 Transact-SQL 语句的错误代码。
2）有效长度：函数名的有效长度为１～ 128个字符。
3） SQL Server保留的关键字不能用作函数名。
4）嵌入的空格或其他特殊字符不能在函数名中使用。
2.调用用户定义函数
1）当调用标量用户定义函数时，必须提供至少由两部分组成的名称：
SELECT *,
MyUser.MyScalarFunction()
FROM MyTable
2）可以使用一个部分构成的名称调用表值函数：
SELECT *
FROM MyTableFunction()
10.3 用户自定义函数类型
10.3.1 标量函数
10.3.2 内嵌表值函数
10.3.3 多语句表值函数
10.3.1 标量函数标量函数返回在 RETURNS 子句中定义的类型的单个数据值。可以使用所有标量数据类型，包括 bigint 和 sql_variant。
1.标量函数的创建
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
参数描述 owner_name：拥有该用户定义函数的用户 ID 的名称。
function_name：
用户定义函数的名称。该名称在数据库中必须是唯一的。
@parameter_name：用户定义函数的参数。使用 @ 符号作为第一个字符来指定参数名称。参数名称必须符合标识符的规则。每个函数的参数仅用于该函数本身；
相同的参数名称可以用在其它函数中。
scalar_parameter_data_type：
参数的数据类型。
scalar_return_data_type：是标量用户定义函数的返回值。
function_body,是一系列合
Transact-SQL 语句。
scalar_expression：指定标量函数返回的标量值。
10.3.2 内嵌表值函数内嵌用户定义函数是返回 table 的用户定义函数的子集。内嵌函数可用于实现参数化视图的功能。
1,内嵌表值函数的创建
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 [ ] ]
参数描述 select-stmt：是定义内嵌表值函数返回值的单个
SELECT 语句。
其他同创建标量函数参数描述。
10.3.3 多语句表值函数对于多语句表值函数，在
BEGIN...END 块中定义的函数主体包含 TRANSACT-SQL 语句，这些语句可生成行并将行插入将返回的表中。
1,多语句表值函数的创建
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 ] )
参数描述 @return_variable,
table 变量，用于存储和累积应作为函数值返回的行。
function_body：一系列填充表返回变量的 Transact-
SQL 语句。
ENCRYPTION：指出 SQL Server 加密包含
CREATE FUNCTION 语句文本的系统表列。
SCHEMABINDING：指定将函数绑定到它所引用的数据库对象。
其他同创建标量函数参数描述。
10.4 修改和删除自定义函数
10.4.1 修改用户自定义函数
10.4.2 删除用户自定义函数
10.4.1 修改自定义函数
1,使用 ALTER FUNCTION命令修改自定义函数更改先前由 CREATE FUNCTION 语句创建的现有用户定义函数，但不会更改权限，也不影响相关的函数、存储过程或触发器。
1）标量函数
ALTER FUNCTION [ owner_name,]
function_name
( [ { @parameter_name
scalar_parameter_data_type
[ = default ] } [,...n ] ] )
RETURNS
scalar_return_data_type
[ WITH < function_option>
[,...n] ]
[ AS ]
BEGIN
function_body
RETURN scalar_expression
END
参数描述 owner_name：拥有待更改的用户定义函数的用户 ID
名。
function_name是要更改的用户定义函数。
@parameter_name：用户定义函数的参数。
scalar_parameter_data_type：参数的数据类型。
scalar_return_data_type：是标量用户定义函数的返回值。
function_body,是一系列合起来求得标量值的 Transact-SQL 语句。
scalar_expression：指定标量函数返回的标量值。
2）内嵌表值函数
ALTER FUNCTION
[ owner_name,]
function_name
( [ { @parameter_name
scalar_parameter_data_ty
pe [ = default ] } [,...n ] ] )
RETURNS TABLE
[ WITH < function_option >
[,...n ] ]
[ AS ]
RETURN [ ( ) select-
stmt [ ] ]
参数描述 select-stmt：
是定义内嵌表值函数返回值的单个 SELECT 语句。
其他同更改标量函数参数描述。
3）多语句表值函数
ALTER FUNCTION [ owner_name,]
function_name
( [ { @parameter_name
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 ] )
参数描述 @return_variable,TABLE 变量，
用于存储和累积应作为函数值返回的行。
function_body：一系列填充表返回变量的 Transact-SQL 语句
ENCRYPTION：指出 SQL Server 加密包含
CREATE FUNCTION 语句文本的系统表列。
SCHEMABINDING：指定将函数绑定到它所引用的数据库对象。
其他同创建标量函数参数描述。
2.其它方法
10.4.2 删除自定义函数
1,使用 DROP FUNCTION命令删除自定义函数从当前数据库中删除一个或多个用户定义的函数。
DROP FUNCTION { [ owner_name,]
function_name } [,...n ]
参数描述 function_name：是要删除的用户定义的函数名称。可以选择是否指定所有者名称，但能指定服务器名称和数据库名称。
n：是表示可以指定多个用户定义的函数的占位符。
2.其它方法
10.5 案例中的自定义函数创建自定义函数 top_grade( ),根据输入的系部代码统计出该系平均成绩最高的前三名同学的信息。
创建函数的代码如下：
use student
go
CREATE FUNCTION
top_grade(@dept_id char(2))
RETURNS TABLE
AS
RETURN (select top 3
课程注册,学号,
学生,姓名,
avg(成绩 ) as 平均成绩
from 学生,课程注册,专业
where 学生,学号 =课程注册,学号
and 课程注册,专业代码 =专业,
专业代码 and 专业,系部代码
=@dept_id
group by 课程注册,学号,学生,
姓名
order by 平均成绩 asc
)
go
使用函数的代码：
use student
go
select * from top_grade('01')
--‘01’为系部代码
go
结果将返回系部代码为 ‘ 01’的系内成绩最高的三名学生的信息。
练 习 题
1,用户自定义函数分为几类？描述各类中函数的作用。
2,使用 SQL命令创建自定义标量函数的语法规则是什么？
3,怎样使用已经定义好的用户自定义函数？
