3.7.9 函数
函数是能够完成特定功能并返回处理结果的一组Transact-SQL语句，处理结果称为“返回值”，处理过程称为“函数体”。SQL Server提供了丰富的内置函数，用户可以利用这些函数完成特定的运算和操作，从而使程序设计过程更加方便。SQL Server提供的函数包括很多类型，主要有数学函数、字符串函数、日期和时间函数、转换函数、聚合函数、排名函数、分析函数等。此外，用户还可以根据需要自己定义函数。
1）数学函数
数学函数用于对数字表达式进行处理，并返回一个数值，常用的数学函数见表3-19。
表3-19 常用的数学函数
【例3.91】 CEILING、FLOOR、RAND示例。
运行结果为：
2）字符串函数
字符串函数用于对字符串输入值执行操作，并返回字符串或数值，常用的字符串函数见表3-20。
表3-20 常用的字符串函数
续表
【例3.92】 使用函数RTRIM和LTRIM分别删除两个字符串右侧和左侧的空格，然后将两个字符串串联形成新的字符串。
运行结果为：
3）日期和时间函数
日期和时间函数用于处理输入的日期和时间值，并返回字符串、数字或者日期时间值，常用的日期和时间函数见表3-21。
表3-21 常用的日期和时间函数
【例3.93】 GETDATE、YEAR、MONTH、DAY函数示例。
可能的运行结果为：
4）转换函数
在同时处理不同数据类型的值时，SQL Server一般会自动进行隐式类型转换，但当数据类型无法自动转换时，用户可以使用SQL Server提供的转换函数来实现，常用的转换函数见表3-22。
表3-22 转换函数
【例3.94】 使用CONVERT函数将系统当前日期转化为某种特定的格式。
可能的运行结果：
5）聚合函数
聚合函数用于对一组值执行计算，并返回单个值。除了COUNT函数外，聚合函数都会忽略NULL值。聚合函数经常与SELECT语句的GROUP BY子句一起使用。常用的聚合函数除3.3节介绍的AVG、COUNT、MAX、MIN、SUM之外，还有GROUPING、STDEV、STDEVP、VAR、VARP等，其他的常用的聚合函数的含义见表3-23。
表3-23 常用的聚合函数
【例3.95】 GROUPING示例。
例3.95在统计每门课程的选课人数的同时，还统计了所有课程的选课人数的合计。通过GROUPING函数的返回值，来指定“课程合计”字符串或返回通常的属性列的值。GROUP BY ROLLUP可以一次计算出不同聚集属性列的组合结果，例3.95就是一次计算出了如下2种组合的聚集结果：
GROUP BY（Cno）：Cno作为分组属性列，此时会得到每门课程的选课人数；
GROUP BY（）：没有聚集属性列，此时会得到全部课程的选课人数，即总计。
例3.95可能的查询结果为：
注意：GROUP BY ROLLUP为每个列表达式的组合创建一个组，此外，它将结果“汇总”到小计和总计；为此，它会从右向左减少创建的组和聚合的列表达式的数量。例如，GROUP BY ROLLUP（col1，col2，col3）为以下列表中的每个列表达式组合创建组：
col1、col2、col3
col1、col2、NULL
col1、NULL、NULL
NULL、NULL、NULL
【例3.96】 STDEV和VAR函数示例。
SELECT STDEV（Grade）AS标准偏差，VAR（Grade）方差，AVG（Grade）平均分
FROM SC
WHERE Cno=C001；
可能的运行结果为：
6）排名函数
排名函数可为分区中的每一行返回一个排名值，常用的排名函数见表3-24。
表3-24 排名函数
【例3.97】 排名函数示例。
可能的查询结果为：
本例中用到了OVER子句。OVER子句用于在应用关联的开窗函数前确定行集的分区和排序，即OVER子句定义查询结果集内的窗口或用户指定的行集，然后开窗函数将计算窗口中每一行的值。常用的开窗函数有排名函数、聚合函数、分析函数等，用户可以将OVER子句和这些函数一起使用，以便计算各种聚合值，例如移动平均值、累积聚合、运行总计或每组结果集的前N个结果等。OVER子句的使用基本格式如下：
说明：
PARTITION BY：将查询结果集分为多个分区，开窗函数分别应用于每个分区，并为每个分区重新启动计算。如果未指定PARTITION BY，则将查询结果集的所有行视为单个分区；
ORDER BY：定义结果集的每个分区中行的逻辑顺序，即指定按其执行开窗函数计算的逻辑顺序。如果未指定ORDER BY，则将对分区中的所有行应用开窗函数。
注意：通过PARTITION BY分区后的记录集合称为“窗口”，代表的是“范围”，这也是“开窗函数”名称的由来。各个窗口在定义上绝对不会包含共通的部分。PARTITON BY可以将表中的数据分为多个窗口。如果不指定PARTITION BY子句，这和使用没有GROUP BY子句的聚合函数时的效果是一样的，也就是将整个表作为一个大的窗口来使用。
【例3.98】 作为开窗函数使用的聚合函数。
可能的查询结果为：
7）分析函数
分析函数基于一组行计算聚合值，与聚合函数不同，分析函数可能针对每个组返回多行。用户可以使用分析函数来计算移动平均线、运行总计、百分比或一个组内的前N个结果等。常用的分析函数见表3-25。
表3-25 分析函数
【例3.99】 CUME_DIST函数返回同一门课程内低于或等于当前学生成绩的百分比的值，使用PERCENT_RANK函数计算学生的成绩在同一门课程内的百分比排名。
本例中为了按课程号对结果集行进行分区，指定PARTITION BY子句。OVER子句中的ORDER BY子句在逻辑上对每个分区中的行进行排序；而SELECT语句中的ORDER BY子句确定结果集的输出顺序。
可能的查询结果为：
8）用户定义函数
用户可以使用Transact-SQL语句创建、修改或删除用户定义函数。与程序设计语言中的函数类似，SQL Server用户定义函数可以接受参数、执行操作（例如复杂计算），并将操作结果以值的形式返回，返回值可以是标量（单个）值或表。根据函数返回值的类型，可以把用户定义函数分为标量函数和表值函数。标量函数返回结果为单个值，表值函数返回结果集（TABLE数据类型），表值函数又分为：内联表值函数和多语句表值函数。
（1）标量函数
标量函数返回一个确定类型的标量值，其函数体由一条或多条Transact-SQL语句组成，这些语句以BEGIN开始，以END结束。可以使用CREATE FUNCTION或ALTER FUNCTION语句创建或修改标量函数，其基本格式如下：
说明：
CREATE：创建用户定义函数；
ALTER：修改函数，只有函数已存在时，才可以进行修改；
schema_name：用户定义函数所属的架构的名称；
function_name：用户定义函数的名称，必须符合标识符规则，且在数据库及架构中唯一；
＠parameter_name：参数，可声明一个或多个参数，一个函数最多可以有2100个参数，即使未指定参数，函数名称后也需要加上括号；
parameter_data_type：参数的数据类型；
［=default］：参数的默认值，执行函数时，如果未定义参数的默认值，则用户必须提供每个已声明参数的值；
READONLY：指示不能在函数定义中更新或修改参数；
return_data_type：标量用户定义函数的返回值类型；
WITH ENCRYPTION：指示数据库引擎会将CREATE FUNCTION语句的原始文本转换为加密格式，加密代码的输出在任何目录视图中都不能直接显示；
function_body：指定一系列定义函数值的Transact-SQL语句；
scalar_expression：指定标量函数返回的标量值。
【例3.100】 创建标量函数Fun1，用于计算某门课程的平均分，课程名作为参数传递。
可能的执行结果为：
（2）内联表值函数
内联表值函数返回数据类型为TABLE，其返回的表值是单个SELECT语句查询的结果，内联表值函数没有由BEGIN...END括起来的函数体。可以使用CREATE FUNTION或ALTER FUNCTION语句创建或修改内联表值函数，其基本格式如下：
说明：
TABLE：指定表值函数的返回值类型为TABLE；
select_stmt：定义内联表值函数返回值的单个SELECT语句。
【例3.101】 创建内联表值函数Fun2，用于查询某门课程的选修情况：学号、姓名、成绩，课程名作为参数传递。
可能的执行结果为：
（3）多语句表值函数
多语句表值函数返回数据类型为TABLE，返回值表中的数据是由函数体中的语句插入的，多语句表值函数的函数体由BEGIN...END括起来。可以使用CREATE FUNTION或ALTER FUNCTION语句创建或修改多语句表值函数，其基本格式如下：
说明：
＠return_variable：TABLE类型变量，用于存储和汇总应作为函数值返回的行；
＜table_type_definition＞：定义表数据类型，表声明包含列定义和列约束（或表约束），其语法格式同CREATE TABLE语句中的＜table_type_definition＞。
【例3.102】 创建多语句表值函数Fun3，用于查询某学生不及格的课程名及成绩，学号作为参数传递。
可能的执行结果为：
