SqlServer：编写函数
变量
变量类型
SqlServer的变量分为两种，分别是用户自己定义的局部变量，用 “@” 开头的标识符表示。第二种是系统定义和维护的全局变量，用 “@@” 开头的标识符表示。
局部变量的作用范围为定义局部变量的批处理、存储过程、触发器或语句块，变量不能是 text、ntext 或 image 数据类型。
定义变量
定义局部变量的语法为：
DECLARE @局部变量名 数据类型[ ,…n]
局部变量在赋值之前默认为 NULL，如果在程序中引用它必须要先赋值。可以用 SET 语句给局部变量赋值，语法如下：
SET @局部变量名 = 变量值
也可以在查询语句 SELECT 中给局部变量赋值，语法如下：
SELECT{@局部变量名 = 变量值}[,…n]
要输出局部变量的值，可以使用 SELECT 语句。
SELECT @局部变量
流程控制语句
任何的程序都可以通过 3 种基本结构相互嵌套组成，分别是顺序结构、选择结构和循环结构。控制程序执行顺序的语句称为流程控制语句，T-SQL 提供了如下的流程控制语句，用于编写过程性代码的语法结构。
| 控制流语句 | 说明 |
|---|---|
| BEGIN…END | 程序块语句 |
| IF…ELSE | 条件处理语句 |
| CASE | 分支语句 |
| WHILE | 循环语句 |
| GOTO | 无条件跳转语句 |
| RETURN | 无条件退出语句 |
| WAITFOR | 延迟语句 |
| BREAK | 跳出循环语句 |
| CONTINUE | 跳出本次循环语句 |
BEGIN…END
顺序结构按照语句的先后顺序依次执行，无须使用专门的控制语句。有时候，为了区分不同的语句块，可以采用复合语句的形式。即用 begin…end 括起来进行语句块的界定，相当于 C 语言中的 “{}”，BEGIN 和 END 语句必须成对使用。
BEGIN
{
sql_statement | statement_block
}
END
IF…ELSE
选择结构表示有不同的路径，但需要根据一个条件来判断执行哪条路径。IF…ELSE 条件处理语句，实现编程中的分支结构。
IF Boolean_expression
{ sql_statement | statement_block }
[ ELSE
{ sql_statement | statement_block } ]
CASE
多条件分支选择 CASE 实现多分支选择结构，类似于 C 语言的 Switch。CASE具有以下两种格式，首先是简单表达式，将某个表达式与一组简单表达式进行比较以确定结果。
CASE input_expression
WHEN … THEN …
[ ...n ]
[ ELSE … ]
END
第二种是选择表达式，计算一组布尔表达式以确定结果。
CASE
WHEN … THEN …
[ ...n ]
[ ELSE … ]
END
WHILE
循环就是重复执行的意思，有的语句需要反复执行才能结束，这就是循环语句。循环结构中必须含有循环语句。循环语句是 WHILE，语法为：
WHILE Boolean_expression --布尔表达式，进行条件判断
{ sql_statement | statement_block }
[BREAK] --跳出本层循环
{ sql_statement | statement_block }
[CONTINUE] --跳出本次循环
{ sql_statement | statement_block }
WAITFOR
WAITFOR 语句的功能是，当程序执行到该语句时，暂时停止程序执行。直到所设定的等待时间已过或到了所设定的时间，才继续向下执行程序。
WAITFOR {DELAY ‘延时时间’ | TIME ‘到达时间’}
编写函数
SQL Server 允许用户设计自己的函数，以补充和扩展系统提供（内置）函数的功能。用户定义函数采用零或多个输入参数并返回标量值或表，SQLServer 支持三种用户定义函数。
标量函数
标量函数返回一个标量（单值）结果，可在与标量函数返回的数据类型相同的值所能使用的任何位置使用该标量函数。创建标量函数的语法是：
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
多语句表值函数
多语句表值函数返回一个由一条或多条 Transact-SQL 语句建立的表，类似于存储过程。与存储过程不同的是，多语句表值函数可以在 SELECT 语句的 FROM 子句中被引用。创建多语句表值函数的语法是：
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 语句产生的结果的表，类似于视图。内嵌表值函数可使用参数，提供了更强的适应性，扩展了索引视图的功能。创建内嵌表值函数的语法是：
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 ] )
编写函数样例
样例一
编写函数Fmax，求二数大者。
CREATE FUNCTION Fmax (@x int,@y int)
RETURNS int
AS
BEGIN
DECLARE @Z INT
IF @X>@Y
SET @Z=@X
ELSE
SET @Z=@Y
RETURN(@Z)
END
SELECT DBO.fMAX(1,2)
样例二
编写函数Fc，参数为两个整数和一个运算符（+，-，*，/），计算其结果。
CREATE FUNCTION FC (@x int,@y int,@C CHAR)
RETURNS int
AS
BEGIN
DECLARE @Z INT
SET @Z = CASE @C
WHEN '+' THEN @X+@Y
WHEN '-' THEN @X-@Y
WHEN '*' THEN @X*@Y
WHEN '/' THEN @X/@Y
END
RETURN(@Z)
END
SELECT DBO.fC(1,2,'+')
样例三
编写函数 Fsum 对自然数列 1 ~ n（参数）求和。
CREATE FUNCTION Fsum(@n INT)
RETURNS int AS
BEGIN
DECLARE @sum INT, @i INT
SET @sum = 0
SET @i = 1
WHILE @i <= @n
BEGIN
SET @sum = @sum + @i
SET @i = 1 + @i
END
RETURN(@sum)
END
SELECT DBO.Fsum(100)
样例四
Student 表、Score 表和 Course 表中有如下一些数据。
编写函数 Fs，参数为姓名和课程名，返回该生该课程的成绩。
CREATE FUNCTION FS (@xM NCHAR(4),@KCM VARCHAR(20))
RETURNS DECIMAL(4,1)
AS
BEGIN
DECLARE @Z DECIMAL(4,1)
SELECT @Z = DEGREE
FROM Student S
JOIN Score SC ON SC.SNO=S.SNO
JOIN Course C ON C.CNO=SC.CNO
WHERE SNAME = @XM AND CNAME = @KCM
RETURN(@Z)
END
SELECT DBO.fS('李君帅', '计算机导论')
样例五
编写函数 Fsc，参数为姓名，返回该生的所有成绩。
CREATE FUNCTION Fsc(@XM Nchar(4))
RETURNS TABLE
AS
RETURN (
SELECT SNAME,CNAME,DEGREE
FROM Student S
JOIN Score SC ON SC.SNO = S.SNO
JOIN Course C ON C.CNO = SC.CNO
WHERE SNAME = @XM
)
SELECT * FROM DBO.Fsc('李君帅')
样例六
编写函数 FRANK，参数为学号，返回该生平均分班级排名。
CREATE FUNCTION FRANK(@sno char(3))
RETURNS int
AS
BEGIN
DECLARE @rank INT
DECLARE @class CHAR(5)
SET @class = (SELECT class FROM Student WHERE Sno = @sno)
SET @rank = (
SELECT a_rank
FROM (
SELECT row_number() OVER(ORDER BY AVG(Degree) DESC) a_rank, S.Sno
FROM Score SC
JOIN Student S ON S.Sno = SC.Sno
WHERE Class = @class
GROUP BY s.sno
) T
WHERE T.Sno = @sno
)
RETURN @rank
END
SELECT DBO.FRANK(101)
样例七
编写函数 FCJA，参数为姓名或姓名一部分，返回该生的所有课程的成绩（学号，姓名，课程名，成绩等级）。选修成绩等级 A:90～100 B:80~90 C: 70~80 D:60~70 E:<60。
CREATE FUNCTION FCJA(@sname nvarchar(4))
RETURNS TABLE AS
RETURN(
SELECT SC.Sno, Sname, Cname, Degree,
CASE
WHEN Degree < 60 THEN 'A'
WHEN Degree >= 60 AND Degree < 70 THEN 'B'
WHEN Degree >= 70 AND Degree < 80 THEN 'C'
WHEN Degree >= 80 AND Degree < 90 THEN 'D'
WHEN Degree >= 90 AND Degree <= 100 THEN 'E'
ELSE 'N'
END 等级
FROM Score SC
JOIN Student S ON SC.Sno = S.Sno
JOIN Course C ON C.Cno = SC.Cno
WHERE Sname like '%'+ @sname +'%'
)
SELECT * FROM FCJA('李君')
参考资料
《SqlServer 2014 数据库技术实用教程》，胡伏湘、肖玉朝 主编，清华大学出版社
