SqlServer：编写函数

目录

• 变量
  • 变量类型
  • 定义变量
• 流程控制语句
  • BEGIN…END
  • IF…ELSE
  • CASE
  • WHILE
  • WAITFOR
• 编写函数
  • 标量函数
  • 多语句表值函数
  • 内嵌表值函数
• 编写函数样例
  • 样例一
  • 样例二
  • 样例三
  • 样例四
  • 样例五
  • 样例六
  • 样例七
• 参考资料

变量#

变量类型#

SqlServer的变量分为两种，分别是用户自己定义的局部变量，用 “@” 开头的标识符表示。第二种是系统定义和维护的全局变量，用 “@@” 开头的标识符表示。
局部变量的作用范围为定义局部变量的批处理、存储过程、触发器或语句块，变量不能是 text、ntext 或 image 数据类型。

定义变量#

定义局部变量的语法为：

Copy Highlighter-hljs
DECLARE @局部变量名 数据类型[ ,…n]

局部变量在赋值之前默认为 NULL，如果在程序中引用它必须要先赋值。可以用 SET 语句给局部变量赋值，语法如下：

Copy Highlighter-hljs
SET  @局部变量名 = 变量值

也可以在查询语句 SELECT 中给局部变量赋值，语法如下：

Copy Highlighter-hljs
SELECT{@局部变量名 = 变量值}[,…n]

要输出局部变量的值，可以使用 SELECT 语句。

Copy Highlighter-hljs
SELECT @局部变量

流程控制语句#

任何的程序都可以通过 3 种基本结构相互嵌套组成，分别是顺序结构、选择结构和循环结构。控制程序执行顺序的语句称为流程控制语句，T-SQL 提供了如下的流程控制语句，用于编写过程性代码的语法结构。

控制流语句	说明
BEGIN…END	程序块语句
IF…ELSE	条件处理语句
CASE	分支语句
WHILE	循环语句
GOTO	无条件跳转语句
RETURN	无条件退出语句
WAITFOR	延迟语句
BREAK	跳出循环语句
CONTINUE	跳出本次循环语句

BEGIN…END#

顺序结构按照语句的先后顺序依次执行，无须使用专门的控制语句。有时候，为了区分不同的语句块，可以采用复合语句的形式。即用 begin…end 括起来进行语句块的界定，相当于 C 语言中的 “{}”，BEGIN 和 END 语句必须成对使用。

Copy Highlighter-hljs
BEGIN
    {
        sql_statement | statement_block
    }
END

IF…ELSE#

选择结构表示有不同的路径，但需要根据一个条件来判断执行哪条路径。IF…ELSE 条件处理语句，实现编程中的分支结构。

Copy Highlighter-hljs
IF Boolean_expression
    { sql_statement | statement_block }
[ ELSE
    { sql_statement | statement_block } ]

CASE#

多条件分支选择 CASE 实现多分支选择结构，类似于 C 语言的 Switch。CASE具有以下两种格式，首先是简单表达式，将某个表达式与一组简单表达式进行比较以确定结果。

Copy Highlighter-hljs
CASE input_expression
     WHEN … THEN … 
     [ ...n ]
     [ ELSE … ]
END

第二种是选择表达式，计算一组布尔表达式以确定结果。

Copy Highlighter-hljs
CASE
     WHEN …    THEN … 
     [ ...n ]
     [ ELSE … ]
END

WHILE#

循环就是重复执行的意思，有的语句需要反复执行才能结束，这就是循环语句。循环结构中必须含有循环语句。循环语句是 WHILE，语法为：

Copy Highlighter-hljs
WHILE Boolean_expression    --布尔表达式，进行条件判断
     { sql_statement | statement_block }
     [BREAK]   --跳出本层循环
     { sql_statement | statement_block }
     [CONTINUE] --跳出本次循环
     { sql_statement | statement_block }

WAITFOR#

WAITFOR 语句的功能是，当程序执行到该语句时，暂时停止程序执行。直到所设定的等待时间已过或到了所设定的时间，才继续向下执行程序。

Copy Highlighter-hljs
WAITFOR {DELAY ‘延时时间’ | TIME ‘到达时间’} 

编写函数#

SQL Server 允许用户设计自己的函数，以补充和扩展系统提供（内置）函数的功能。用户定义函数采用零或多个输入参数并返回标量值或表，SQLServer 支持三种用户定义函数。

标量函数#

标量函数返回一个标量（单值）结果，可在与标量函数返回的数据类型相同的值所能使用的任何位置使用该标量函数。创建标量函数的语法是：

Copy Highlighter-hljs
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 子句中被引用。创建多语句表值函数的语法是：

Copy Highlighter-hljs
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 语句产生的结果的表，类似于视图。内嵌表值函数可使用参数，提供了更强的适应性，扩展了索引视图的功能。创建内嵌表值函数的语法是：

Copy Highlighter-hljs
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，求二数大者。

Copy Highlighter-hljs
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，参数为两个整数和一个运算符（+，-，*，/），计算其结果。

Copy Highlighter-hljs
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（参数）求和。

Copy Highlighter-hljs
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，参数为姓名和课程名，返回该生该课程的成绩。

Copy Highlighter-hljs
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，参数为姓名，返回该生的所有成绩。

Copy Highlighter-hljs
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，参数为学号，返回该生平均分班级排名。

Copy Highlighter-hljs
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。

Copy Highlighter-hljs
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 数据库技术实用教程》，胡伏湘、肖玉朝 主编，清华大学出版社