资源简介

(共96张PPT)
网站数据库应用技术SQL Server 2008
（项目教程）
项目九: Transact-SQL编程
SQL Server编程
使用系统内置函数
用户自定义函数
任务一
任务二
任务三
Transact-SQL编程
任务四
游标
任务1: SQL Server编程
SQL Server编程
在数据库编程中除了使用基本的SQL语句实现数据库的添加、修改、删除和查询操作外，还可以使用程序逻辑进行更为复杂的SQL编程。
9.1.1任务分析
SQL Server编程
掌握Transact-SQL编程的基本语法，包括批处理、注释符、标识符、常量、变量、运算符、表达式、输出语句和流控语句。会综合运用Transact-SQL编程的语法知识，进行SQL Server编程，以解决实际问题。
9.1.2相关知识
SQL Server编程
1．批处理
批处理是由一个或多个Transact-SQL语句组成的，应用程序将这些语句作为一个单元提交给SQL Server执行。GO命令标志一个批处理的结束，通常把两个GO之间的SQL语句看作一个批处理。除了CREATE DATABASE（创建数据库）、CREATE TABLE（创建表）和CREATE INDEX（创建索引）语句之外的其他大多数的CREATE语句要单独作为一个批处理。
例如：创建一个视图，该视图要求显示班级表的所有记录。视图创建完成后，再使用SELECT语句查询课程表中的所有信息。要求使用批处理来完成上述问题。
9.1.2相关知识
SQL Server编程
创建显示班级表所有记录的视图V_Class的语句如下：
CREATE VIEW V_Class
AS
SELECT * FROM 班级表
查询课程表所有记录的语句为SELECT * FROM 课程表。
如果在查询窗口中执行如下SQL语句：
USE Student
CREATE VIEW V_Class
AS
SELECT * FROM 班级表
SELECT * FROM 课程表
9.1.2相关知识
SQL Server编程
则执行结果如图9-1所示。
为了解决上述错误，将CREATE VIEW前面加上GO，使USE Student单独成为一个批处理，这样CREATE VIEW就成了另一个批处理的第一个语句。
USE Student
GO
CREATE VIEW V_Class
AS
SELECT * FROM 班级表
SELECT * FROM 课程表
9.1.2相关知识
SQL Server编程
出现上述错误，是因为CREATE VIEW要单独作为一个批处理，所以在（SELECT * FROM 课程表）的前面加上GO。
USE Student
GO
CREATE VIEW V_Class
AS
SELECT * FROM 班级表
GO
SELECT * FROM 课程表
执行上述语句，就不会出现错误信息了。
9.1.2相关知识
SQL Server编程
2．注释符
注释，也称为注解，是写在代码中的说明性文字，对程序的结构及功能进行文字说明。注释内容不被系统编译，也不被程序执行。使用注释对代码进行说明，不仅能使程序易读易懂，而且有助于日后的管理和维护。在程序设计过程中，程序员要严格遵守代码编写规范，而注释就是其中很重要的一部分。SQL Server提供了两类注释符。
（1）单行注释：使用“--”表示，将注释内容写在--的后面。
（2）多行注释：使用“/*…*/”表示，将注释内容写在/*和*/之间。
例如：
USE Student --打开Student数据库
/*创建显示班级表所有记录的视图V_Class*/
CREATE VIEW V_Class
9.1.2相关知识
SQL Server编程
3．标识符
标识符是用来标识服务器、数据库和数据库对象（列、表、视图和索引等），Transact-SQL的保留字不能用做标识符。SQL Server的标识符有两种：常规标识符和分隔标识符。
（1）常规标识符：常规标识符的第一个字符必须是下列字符之一：26个大小写字母a～z、A～Z，来自其他语言的字母字符，还可以是下划线、@或#。其他字符可以为大小写字母，也可以为其他国家或地区字符中的十进制数字。常规标识符中不允许有空格或其他特殊字符。例如：使用“学生表”来表示学生表的表名称，“学生表”就是常规标识符
9.1.2相关知识
SQL Server编程
（2）分隔标识符：对于不符合常规标识符的标识符，用双引号或方括号进行分隔，才能够作为标识符使用，这样的标识符叫做分隔标识符。
例如：要查询一个名为My Table的表的所有信息。查询语句为：SELECT * FROM My Table，执行该语句会出现如下错误信息“关键字'Table'附近有语法错误。”这是因为My Table内含有空格，不是常规标识符，若要将My Table作为标识符使用，必须用双引号或方括号对其进行分隔，构成分隔标识符。故将查询语句修改为SELECT * FROM "My Table"，或修改为SELECT * FROM [My Table]，执行这两条语句都不会出现错误信息。
9.1.2相关知识
SQL Server编程
4．常量
常量是指在程序的生命周期内，其值不改变的量。常量可以直接用其值表示，它的格式取决于它所表示的数据类型，主要有几下以种类型：
（1）字符串常量，字符串常量括在单引号内。‘网络数据库’，N‘Lucy’都是字符串常量。
（2）数值常量。0X1AC，14，19.21，0.6E-2，$12.6都是数值常量。
（3）日期常量。'2014/11/19'，'20141119'都是日期常量。
5．变量
变量是指在程序的生命周期内，其值可以改变的量。变量分为局部变量和全局变量。
（1）局部变量。局部变量是用户定义的变量，其作用范围仅在程序内部。
声明局部变量的语法如下：
DECLARE @变量名1 数据类型,@变量名2 数据类型,…,@变量名n 数据类型
9.1.2相关知识
SQL Server编程
可以使用SET或SELECT为变量赋值，具体语法如下：
SET @变量名=表达式
SELECT @变量名1=表达式1, @变量名2=表达式2,…,@变量名n=表达式n
用SET为变量赋值，一次只能给一个变量赋值；用SELECT为变量赋值，一次可以为多个变量赋值。
（2）全局变量
全局变量名以@@开始，是SQL Server系统提供并赋值的变量，用户不能建立全局变量，也不能用SET或SELECT赋值语句修改全局变量的值。
@@LANGUAGE返回当前使用的语言；@@ROWCOUNT返回上一个SQL语句影响的行数。
6．运算符
9.1.2相关知识
SQL Server编程
表9-1 Transact-SQL运算符
类别 所含运算符 优先级
一元运算符 +（正）、–（负）、～（取反） 1
算术运算符 *（乘）、/（除）、%（取模） 2
算术运算符 +（加）、–（减） 3
字符串连接运算符 +（连接） 3
比较运算符 =（等于）、>（大于）、>=（大于等于）、<（小于）、<=（小于等于）、<>（或!=，不等于）、!<（不小于）、!>（不大于） 4
按位运算符 &（位与）、∣（位或）、^（位异或） 5
逻辑运算符 NOT（非） 6
逻辑运算符 AND（与） 7
逻辑运算符 OR（或）、ALL（所有）、ANY（任意一个）、BETWEEN（两者之间）、EXISTS（存在）、IN（在范围内）、LIKE（匹配）、SOME（任意一个） 8
赋值运算符 =（赋值） 9
9.1.2相关知识
SQL Server编程
7．表达式
表达式是由变量、常量、函数和运算符构成，分为算术表达式和逻辑表达式。
8．输出语句
在Transact-SQL中经常要向客户输出信息，显示数据结果。在SQL Server中用于输出的主要是PRINT语句和SELECT语句。
使用PRINT语句输出信息，结果以文本方式显示，而使用SELECT语句输出信息，结果以表格方式显示。
9．流控语句
（1）BEGIN…END语句
BEGIN…END用来定义语句块，必须成对出现。语法格式如下：
BEGIN
语句块
END
9.1.2相关知识
SQL Server编程
（2）IF…ELSE语句
IF…ELSE语句用来实现程序的选择结构，语法格式如下：
IF 逻辑表达式 语句块1
[ELSE 语句块2]
（3）CASE…END
①简单CASE表达式，语法格式如下：
CASE 输入表达式
WHEN when表达式1 THEN 结果表达式1
WHEN when表达式2 THEN 结果表达式2
[…]
[ELSE 结果表达式n]
END
9.1.2相关知识
SQL Server编程
②CASE搜索表达式，语法格式如下：
CASE
WHEN 逻辑表达式1 THEN 结果表达式1
WHEN 逻辑表达式2 THEN 结果表达式2
[…]
[ELSE 结果表达式n]
END
（4）WHILE语句，语法格式如下：
WHILE 逻辑表达式
BEGIN
语句块 [BREAK] [CONTINUE]
END
（5）RETURN语句，语法格式如下：RETURN [整形表达式]
9.1.3任务实施
SQL Server编程
1. 求给定的三个整数中最大的那个数是多少？
方法一：
DECLARE @x int , @y int , @z int , @max int
SELECT @x = 50 , @y = 60 , @z = 40
IF @x > @y
SET @max = @x
ELSE
SET @max = @y
IF @z > @max
SET @max = @z
SELECT @max
GO
9.1.3任务实施
SQL Server编程
方法二：
DECLARE @x int , @y int , @z int , @max int
SELECT @x = 50 , @y = 60 , @z = 40
IF @x > @y
BEGIN
IF @x > @z SET @max = @x
ELSE SET @max = @z
END
ELSE
BEGIN
IF @y > @z SET @max = @y
ELSE SET @max = @Z
END
SELECT @max
GO
9.1.3任务实施
SQL Server编程
2. 计算1+2+3+…+10000的和，并显示计算结果。
方法一：
DECLARE @i int , @sum int
SELECT @i = 1 , @sum = 0
WHILE @i <= 10000
BEGIN
SET @sum = @sum + @i --求和
SET @i = @i + 1 --计数变量加1
END
SELECT '1+2+3+...+10000的和' = @sum
GO
9.1.3任务实施
SQL Server编程
2. 计算1+2+3+…+10000的和，并显示计算结果。
方法二：
DECLARE @i int , @sum int
SELECT @i = 0 , @sum = 0
WHILE @i < 10000
BEGIN
SET @i = @i + 1 --计数变量加1
SET @sum = @sum + @i --求和
END
SELECT '1+2+3+...+10000的和' = @sum
GO
9.1.3任务实施
SQL Server编程
3．按用户给定的用户名来查询该用户的用户类别信息，要求显示用户名和用户类别。
方法一：
USE Student
GO
DECLARE @user_ID nvarchar(12)
SET @user_ID = '01201401001'
SELECT 用户名 , '用户类别' = CASE 权限
WHEN 'admin' THEN '管理员用户'
WHEN 'teacher' THEN '教师用户'
WHEN 'student' THEN '学生用户'
ELSE '其他用户'
END
FROM 用户表 WHERE 用户名 = @user_ID
9.1.3任务实施
SQL Server编程
3．按用户给定的用户名来查询该用户的用户类别信息，要求显示用户名和用户类别。
方法二：
USE Student
GO
DECLARE @user_ID nvarchar(12)
SET @user_ID = '01201401001'
SELECT 用户名, '用户类别' = CASE
WHEN 权限 = 'admin' THEN '管理员用户'
WHEN 权限 = 'teacher' THEN '教师用户'
WHEN 权限 = 'student' THEN '学生用户'
ELSE '其他用户'
END
FROM 用户表 WHERE 用户名 = @user_ID
9.1.3任务实施
SQL Server编程
4．按用户给定的学号和课程编号来查询该学生本门课的成绩信息，要求显示学号、姓名、课程名称、成绩等级。
DECLARE @student_ID varchar(12) , @course_No varchar(3)
SELECT @student_ID = '01201401001' , @course_No = '001'
SELECT 成绩表.学号 ,姓名 ,课程名称 , '成绩等级' = CASE
WHEN 成绩>= 90 THEN '优秀'
WHEN 成绩>= 70 AND 成绩 < 90 THEN '良好'
WHEN 成绩>= 60 AND 成绩 < 70 THEN '及格'
ELSE '不及格'
END
FROM 学生表 JOIN 成绩表 ON 学生表.学号 = 成绩表.学号
JOIN 课程表 ON 成绩表.课程编号 = 课程表.课程编号
WHERE 成绩表.学号 = @student_ID AND 成绩表.课程编号 =@course_No
任务2: 使用系统内置函数
使用系统内置函数
SQL Server 2008的大部分系统内置函数和其他程序设计语言的函数功能类似，都是系统已开发好的功能模块，供用户直接调用。系统内置函数是软件开发的工具，程序开发人员必须熟悉系统内置函数，以提高程序设计的效率。
9.2.1任务分析
使用系统内置函数
掌握常用系统内置函数的使用方法，学会运用系统内置函数解决实际问题。
9.2.2相关知识
使用系统内置函数
1．聚合函数
聚合函数 功能描述
MAX ( [ ALL | DISTINCT ] 表达式 ) 计算一组数据的最大值
MIN ( [ ALL | DISTINCT ] 表达式 ) 计算一组数据的最小值
SUM ( [ ALL | DISTINCT ] 表达式 ) 计算一组数据的和
AVG ( [ ALL | DISTINCT ] 表达式 ) 计算一组数据的平均值
COUNT ( { [ ALL | DISTINCT ] 表达式 } | * ) 计算总行数
CHECKSUM ( * | 表达式 [ ,…n ] ) 返回按照表的某一行或一组表达式计算校验和值
STDEV ( 表达式 ) 返回给定表达式中所有值的统计标准偏差
举例1：查询课程编号为“001”课程的最高分，最低分和平均分。
SELECT 课程编号 , MAX (成绩) '最高分' , MIN (成绩) '最低分' , AVG (成绩) '平均分'
FROM 成绩表
GROUP BY 课程编号
HAVING 课程编号 = '001'
9.2.2相关知识
使用系统内置函数
举例2：查询学号为01201401001学生所有课程的总分和平均分。
SELECT 学号, SUM (成绩) '总分' , AVG (成绩) '平均分'
FROM 成绩表
WHERE 学号 = '01201401001'
GROUP BY 学号
举例3：统计课程表中一共有多少门课程。
SELECT COUNT (*) '课程表中的课程门数'
FROM 课程表
2．字符串函数
字符串函数常用于对字符串进行连接、截取等操作，常用的字符串函数如下表。
9.2.2相关知识
使用系统内置函数
字符串函数 功能描述
ASCII（字符表达式） 返回字符表达式最左边字符的ASCII码
CHAR（整形表达式） 将一个ASCII码转换为字符，ASCII码应在0-255之间
SPACE（整形表达式） 返回由重复的空格组成的字符串
LEN（字符表达式） 返回给定字符串表达式的字符（而不是字节）的个数，其中不包含尾部的空格
RIGHT（字符串，整数） 返回字符串中从右边开始指定个数的字符
LEFT（字符串，整数） 返回从字符串左边开始指定个数的字符
SUBSTRING（字符表达式，起始点，N） 返回字符串表达式中从“起始点”开始的N个字符
STR（浮点表达式 [，总长度 [，小数点右边的位数 ] ]） 由数字数据转换来的字符数据
LTRIM（字符串） 删除字符串左边的空格
RTRIM（字符串） 删除字符串右边的空格
LOWER（字符表达式） 将大写字符数据转换为小写字符数据后返回字符表达式
9.2.2相关知识
使用系统内置函数
字符串函数 功能描述
UPPER（字符表达式） 返回将小写字符数据转换为大写的字符表达式
REVERSE（字符表达式） 返回字符表达式的逆序
CHARINDEX（字符表达式1，字符表达式2，[ 起始位置 ]） 返回字符串上指定表达式的起始位置
DIFFERENCE（字符表达式1，字符表达式2） 以整数返回两个字符表达式的SOUNDEX值之差
REPLICATE（字符表达式，正整数） 以指定的次数重复字符表达式
SOUNDEX（字符表达式） 返回由四个字符组成的代码（SOUNDEX）以评估两个字符串的相似性
STUFF（字符表达式1，start，length，字符表达式2） 删除指定长度的字符并在指定的起始点插入另一组字符
NCHAR（整形表达式） 根据Unicode标准所进行的定义，用给定整数代码返回Unicode字符
UNICODE（字符表达式） 返回字符表达式最左侧的Unicode代码
+ 将字符串进行连接
9.2.2相关知识
使用系统内置函数
举例1：查询“Address”最左边字符“A”的ASCII码值。
SELECT ASCII ( 'Address' )
举例2：查询ASCII码值为65的字符。
SELECT CHAR ( 65 )
举例3：显示信息：将“清楚”显示两次，然后间隔10个空格，再将“明白”显示两次。
SELECT REPLICATE ( '清楚' , 2 ) + SPACE ( 10 ) + REPLICATE ( '明白' , 2 )
举例4：计算字符串“SQL Server 2008网络数据库管理项目”的字符个数。
SELECT LEN ( 'SQL Server 2008网络数据库管理项目' )
举例5：查询字符串“SQL Server 2008网络数据库管理项目”从右边数的4个字符组成的字符串。
SELECT RIGHT ( 'SQL Server 2008网络数据库管理项目' , 4 )
举例6：查询字符串“SQL Server 2008网络数据库管理项目”从左边数的3个字符组成的字符串。
9.2.2相关知识
使用系统内置函数
SELECT LEFT ( 'SQL Server 2008网络数据库管理项目' , 3 )
举例7：查询字符串“SQL Server 2008网络数据库管理项目”从第5个字符开始的6个字符组成的字符串。
SELECT SUBSTRING ( 'SQL Server 2008网络数据库管理项目' , 5 , 6 )
举例8：将字符串“Address”中的字符全部转换成小写字符。
SELECT LOWER ( 'Address' )
举例9：将字符串“Address”中的字符全部转换成大写字符。
SELECT UPPER ( 'Address' )
举例10：显示字符串“Address”的逆序。
SELECT REVERSE ( 'Address' )
举例11：查找字符串“数据库”在“SQL Server 2008网络数据库管理项目”中的开始位置。
SELECT CHARINDEX ( '数据库' , 'SQL Server 2008网络数据库管理项目' )
9.2.2相关知识
使用系统内置函数
3．日期函数
日期函数 功能描述
GETDATE（） 返回当前系统日期和时间
DATENAME（日期元素，日期） 返回表示指定日期的指定日期部分的字符串
DATEPART（日期元素，日期） 返回表示指定日期的指定日期部分的整数
DATEDIFF（日期元素，日期1，日期2） 返回两个日期间的差值并转换为指定日期元素的形式
DATEADD（日期元素，数值，日期） 将日期元素加上日期产生新的日期
YEAR（日期） 返回日期中“年”的部分（整数）
MONTH（日期） 返回日期中“月”的部分（整数）
DAY（日期） 返回日期中“天”的部分（整数）
GETUTCDATE（） 返回表示当前UTC时间（世界时间坐标或格林尼治标准时间）的日期值
9.2.2相关知识
使用系统内置函数
举例1：给出当前系统的日期和时间。
SELECT GETDATE ()
举例2：输出日期“1980/05/18”的月份信息。
SELECT DATENAME ( MONTH , '1980/05/18' )
举例3：张航的生日是“1994/05/02”，使用日期函数计算张航的年龄。
SELECT DATEDIFF ( YEAR , '1994/05/02' , GETDATE () )
举例4：使用日期函数获取明天的日期和时间。
SELECT DATEADD ( DAY , 1 , GETDATE () )
举例5：查询当前系统日期和时间的年份。
SELECT YEAR ( GETDATE () )
9.2.2相关知识
使用系统内置函数
4．数学函数
数学函数 功能描述
ABS（数字表达式） 返回表达式的绝对值
PI（） 返回 的值3.14159265358979
CEILING（数字表达式） 返回大于或等于所给数字表达式的最小整数
FLOOR（数值表达式） 返回小于或等于数值表达式的最大整数
EXP（浮点表达式） 返回数值的指数形式
POWER（数字表达式，指定次方） 返回给定表达式乘指定次方的值
SQUARE（浮点表达式） 返回给定表达式的平方
SQRT（浮点表达式） 返回给定表达式的平方根
SIGN（数值表达式） 返回给定表达式的正（+1）、零（0）或负（-1）号
9.2.2相关知识
使用系统内置函数
数学函数 功能描述
ROUND（数值表达式，整型表达式） 返回数字表达式并四舍五入为指定的长度或精度
RAND（整型表达式） 返回0到1之间的随机float值
LOG（浮点表达式） 返回所给浮点表达式的自然对数
LOG10（浮点表达式） 返回底数为10的对数
SIN（浮点表达式） 返回给定角度（以弧度为单位）的三角正弦值
COS（浮点表达式） 返回给定表达式中指定角度（以弧度为单位）的三角余弦值
TAN（浮点表达式） 返回给定表达式的正切值（以弧度为单位）
COT（浮点表达式） 返回给定浮点表达指定角度（以弧度为单位）的三角余切值
ASIN（浮点表达式） 反正弦函数。返回以弧度表示的角度值，该角度值的正弦为给定的浮点表达式
ACOS（浮点表达式） 反余弦函数。返回以弧度表示的角度值，该角度值的余弦为给定的浮点表达式
ATAN（浮点表达式） 反正切函数。返回以弧度表示的角度值，该角度值的正切为给定的浮点表达式
9.2.2相关知识
使用系统内置函数
举例1：使用数学函数计算 的值。
SELECT PI ()
举例2：使用数学函数计算-21.5的绝对值。
SELECT ABS ( -21.5 )
举例3：使用数学函数计算大于或等于105.45的最小整数。
SELECT CEILING ( 105.45 )
举例4：使用数学函数计算小于或等于105.45的最大整数。
SELECT FLOOR ( 105.45 )
举例5：使用数学函数计算45的结果。
SELECT POWER ( 4 , 5 )
举例6：使用数学函数计算100的平方。SELECT SQUARE ( 100 )
举例7：使用数学函数计算100的平方根。SELECT SQRT ( 100 )
9.2.2相关知识
使用系统内置函数
5．系统函数
系统函数 功能描述
CONVERT ( 目标数据类型 [ ( 长度 ) ]，表达式 [，样式 ] ) 将表达式显示转换成另一种数据类型
CAST ( 表达式 AS 目标数据类型 [ ( 长度 ) ] ) 将表达式显示转换成另一种数据类型
CASE表达式 计算条件列表，并返回表达式的多个可能结果之一
DATALENGTH（表达式） 返回表达式所占用的字节数
HOST_NAME（） 返回主机名称
ISDATE（表达式） 表达式为有效日期格式时返回1，否则返回0
ISNULL（表达式，替换值） 表达式的值为NULL时，用指定的替换值进行替换
ISNUMERIC（表达式） 表达式为数值类型时返回1，否则返回0
NEWID（） 生成全局唯一标识符
NULLIF（表达式1，表达式2） 如果两个指定的表达式相等，则返回空值
9.2.2相关知识
使用系统内置函数
举例1：将字符串3.1415926转换为数值，要求小数位数保留两位。
SELECT CONVERT ( decimal ( 3 , 2 ) , '3.1415926' )
举例2：获取当前系统日期。
SELECT CONVERT ( varchar ( 10 ) , GETDATE () , 120 )
举例3：计算字符串“SQL Server 2008网络数据库管理项目”所占用的字节数。
SELECT DATALENGTH ( 'SQL Server 2008网络数据库管理项目' )
6．配置函数
7．排序函数
排序函数 功能描述
ROW_NUMBER（）OVER（排序语句） 在查询结果中给出每行的序号
RANK（）OVER（排序语句） 在查询结果中给出每行的序号，排序有可能会间断
DENSE_RANK（）OVER（排序语句） 在查询结果中给出每行的排序，排序没有间断
9.2.2相关知识
使用系统内置函数
举例1：查询学号为01201401001学生的所有成绩信息，要求查询结果按照成绩排序，并且返回每一行的序号。
SELECT ROW_NUMBER () OVER ( ORDER BY 成绩 ) AS '序号' , *
FROM 成绩表 WHERE 学号 = '01201401001'
举例2：查询学号为01201401001学生的所有成绩信息，要求查询结果按照成绩排序，并且返回有间断的每一行的序号。
SELECT RANK () OVER ( ORDER BY 成绩 ) AS '序号' , *
FROM 成绩表 WHERE 学号 = '01201401001'
举例3：查询学号为01201401001学生的所有成绩信息，要求查询结果按照成绩排序，并且返回没有间断的每一行的序号。
SELECT DENSE_RANK() OVER (ORDER BY 成绩) AS '序号',*
FROM 成绩表 WHERE 学号 = '01201401001'
9.2.3任务实施
使用系统内置函数
1.查询学生的基本信息，要求显示学号、姓名、性别和年龄。
USE Student
GO
SELECT 学号 , 姓名 , 性别 , DATEDIFF ( YEAR , 出生日期 , GETDATE () ) '年龄'
FROM 学生表
2．按班级编号统计各个班级开设课程的门数。
USE Student
GO
SELECT 班级编号 , COUNT ( * ) '班级开设课程的门数'
FROM 班级排课表
GROUP BY 班级编号
9.2.3任务实施
使用系统内置函数
3．统计用户给定学号和学期的学生成绩信息，要求显示学号、姓名、学期、总成绩和平均成绩（小数位数保留两位）。
USE Student
GO
DECLARE @student_ID varchar ( 12 ) , @tearm varchar ( 10 )
SELECT @student_ID = '01201401001' , @tearm = '2013-2' --给局部变量赋值
SELECT 成绩表.学号 , 姓名 , 学期 , SUM ( 成绩 ) '总成绩' , CONVERT ( decimal ( 5 , 2 ) , AVG ( 成绩 ) ) '平均成绩'
FROM 学生表 JOIN 成绩表 ON 学生表.学号 = 成绩表.学号
JOIN 班级排课表 ON 成绩表.课程编号 = 班级排课表.课程编号
WHERE 成绩表.学号 = @student_ID AND 学期 = @tearm
GROUP BY 成绩表.学号 , 姓名 , 学期
9.2.3任务实施
使用系统内置函数
4．统计用户给定学号和学期的学生成绩信息，要求显示学号、姓名、学期、课程名称、成绩、总成绩和平均成绩。
USE Student
GO
DECLARE @student_ID varchar ( 12 ) , @tearm varchar ( 10 )
SELECT @student_ID = '01201401001' , @tearm = '2013-2' --给局部变量赋值
SELECT 成绩表.学号 , 姓名 , 学期 , 课程名称 , 成绩
FROM 学生表 JOIN 成绩表 ON 学生表.学号 = 成绩表.学号
JOIN 班级排课表 ON 成绩表.课程编号 = 班级排课表.课程编号
JOIN 课程表 ON 班级排课表.课程编号 = 课程表.课程编号
WHERE 成绩表.学号 = @student_ID AND 学期 = @tearm
ORDER BY 成绩表.学号, 学期
COMPUTE SUM ( 成绩 ) , AVG ( 成绩 ) BY 成绩表.学号, 学期
9.2.3任务实施
使用系统内置函数
5．按学期统计用户给定学号的学生成绩信息，要求显示该学生各个学期所有课程的总成绩和平均成绩（小数位数保留两位）。
USE Student
GO
DECLARE @student_ID varchar ( 12 )
SELECT @student_ID = '01201401001' --给局部变量赋值
SELECT 学期 , SUM ( 成绩 ) '总成绩' , CONVERT ( decimal ( 5 , 2 ) , AVG ( 成绩 ) ) '平均成绩'
FROM 成绩表 JOIN 班级排课表
ON 成绩表.课程编号 = 班级排课表.课程编号
WHERE 学号 = @student_ID
GROUP BY 学期
9.2.3任务实施
使用系统内置函数
6．统计用户给定课程编号的成绩信息，要求显示课程编号、课程名称、最高分、最低分、平均分（小数位数保留两位）。
USE Student
GO
DECLARE @course_No varchar ( 3 )
SELECT @course_No = '001' --给局部变量赋值
SELECT 成绩表.课程编号 ,课程名称 , MAX ( 成绩 ) '最高分' , MIN ( 成绩 ) '最低分' , CONVERT ( decimal ( 5 , 2 ) , AVG (成绩 ) ) '平均成绩'
FROM 成绩表 JOIN 课程表
ON 成绩表.课程编号 = 课程表.课程编号
WHERE 成绩表.课程编号 = @course_No
GROUP BY 成绩表.课程编号 , 课程名称
任务3:用户自定义函数
用户自定义函数
SQL Server不但提供了系统内置函数，还允许用户自己定义函数。用户自定义函数是由一个或多个Transact-SQL语句组成的子程序，创建用户自定义函数，是为了提高软件的可重用性和可维护性。
9.3.1任务分析
用户自定义函数
学会创建、修改和删除用户自定义函数，能够灵活运用用户自定义函数解决实际问题。
9.3.2相关知识
用户自定义函数
1．标量值函数
CREATE FUNCTION [ 所有者名称. ] 函数名称
[ ( { @参数名称 [ AS ] 数据类型 = [ 默认值 ] } [ …N ] ) ]
RETURNS 数据类型
[ WITH { ENCRYPTION | SCHEMABINDING } ]
[ AS ]
BEGIN
函数体
RETURN 标量表达式
END
参数说明：
（1）ENCRYPTION：加密选项，以防止用户自定义函数作为SQL Server复制的一部分被发布。
（2）SCHEMABINDING：计划绑定选项，将用户自定义函数绑定到它所引用的数据库对象。如果指定了此选项，函数则不能被删除和修改，除非删除绑定。
9.3.2相关知识
用户自定义函数
修改标量值函数的语法如下：
ALTER FUNCTION [ 所有者名称. ] 函数名称
[ ( { @参数名称 [ AS ] 数据类型 = [ 默认值 ] } [ …N ] ) ]
RETURNS 数据类型
[ WITH { ENCRYPTION | SCHEMABINDING } ]
[ AS ]
BEGIN
函数体
RETURN 标量表达式
END
删除标量值函数的语法如下：
DROP FUNCTION [ 所有者名称. ] 函数名称
9.3.2相关知识
用户自定义函数
举例1：创建名为CalcCj的用户自定义函数，返回给定两个整数的乘积，其中一个整数的默认值为8。
CREATE FUNCTION dbo.CalcCj ( @i int , @j int = 8 )
RETURNS int
AS
BEGIN
RETURN @i * @j
END
举例2：调用名为CalcCj的用户自定义函数。
SELECT dbo.CalcCj(2,DEFAULT)
举例3：修改名为CalcCj的用户自定义函数，返回给定两个整数的和。
ALTER FUNCTION dbo.CalcCj ( @i int , @j int )
RETURNS int
AS
BEGIN
RETURN @i + @j END
9.3.2相关知识
用户自定义函数
举例4：删除名为CalcCj的用户自定义函数。
DROP FUNCTION dbo.CalcCj
2．内嵌表值函数，创建内嵌表值函数的语法如下：
CREATE FUNCTION [ 所有者名称. ] 函数名称
[ ( { @参数名称 [ AS ] 数据类型 = [ 默认值 ] } [ …N ] ) ]
RETURNS TABLE
[ WITH { ENCRYPTION | SCHEMABINDING } ]
[ AS ]
RETURN [ ( SELECT 语句 ) ]
修改内嵌表值函数的语法如下：
ALTER FUNCTION [ 所有者名称. ] 函数名称
[ ( { @参数名称 [ AS ] 数据类型 = [ 默认值 ] } [ …N ] ) ]
RETURNS TABLE
[ WITH { ENCRYPTION | SCHEMABINDING } ]
[ AS ]
RETURN [ ( SELECT 语句 ) ]
9.3.2相关知识
用户自定义函数
删除内嵌表值函数的语法如下：
DROP FUNCTION [ 所有者名称. ] 函数名称
举例1：创建名为SelStudent用户自定义函数，返回给定学号的学生基本信息，该信息包括学号、姓名、性别、出生日期。
USE Student
GO
CREATE FUNCTION dbo.SelStudent ( @student_ID varchar ( 12 ) )
RETURNS TABLE
AS
RETURN ( SELECT 学号 , 姓名 , 性别 , 出生日期
FROM 学生表
WHERE 学号 = @student_ID )
举例2：调用名为SelStudent用户自定义函数。
SELECT * FROM dbo.SelStudent ( '01201401001' )
9.3.2相关知识
用户自定义函数
举例3：修改名为SelStudent用户自定义函数，要求对该用户自定义函数进行加密处理。
ALTER FUNCTION dbo.SelStudent ( @student_ID varchar ( 12 ) )
RETURNS TABLE
WITH ENCRYPTION
AS
RETURN (SELECT 学号 , 姓名 , 性别 , 出生日期
FROM 学生表
WHERE 学号 = @student_ID )
举例4：删除名为SelStudent用户自定义函数。
DROP FUNCTION dbo.SelStudent
9.3.2相关知识
用户自定义函数
举例1：创建名为SelStu的多语句表值函数，该函数返回给定学号学生的基本信息，包括学号、姓名和性别。
CREATE FUNCTION dbo.SelStu ( @student_ID varchar ( 12 ) )
RETURNS @StuInfo TABLE (学号 varchar ( 12 ) , 姓名 nvarchar ( 10 ) ,
性别 char ( 2 ) )
AS
BEGIN
INSERT @StuInfo
SELECT 学号 , 姓名 , 性别
FROM 学生表 WHERE 学号 = @student_ID
RETURN
END
举例2：调用名为SelStu的多语句表值函数。
SELECT * FROM dbo.SelStu ( '01201401001' )
9.3.3任务实施
用户自定义函数
1. 创建用户自定义函数，返回给定半径的圆的面积。
/*创建用户自定义标量值函数，该函数返回给定半径的圆的面积。*/
CREATE FUNCTION dbo.CalcArea ( @r float )
RETURNS float
AS
BEGIN
DECLARE @Area float
SET @Area = 3.14 * SQUARE ( @r )
RETURN @Area
END
9.3.3任务实施
用户自定义函数
2. 创建用户自定义内嵌表值函数，返回给定学号和学期的学生成绩信息，成绩信息包括学号、姓名、学期、总成绩和平均成绩（小数位数保留两位）。将该函数修改为用户自定义多语句表值函数，实现上述功能。
/*创建用户自定义内嵌表值函数，返回给定学号和学期的学生成绩信息*/
CREATE FUNCTION dbo.SelCjByID ( @student_ID varchar ( 12 ) , @tearm varchar ( 10 ) )
RETURNS TABLE
AS
RETURN (SELECT 成绩表.学号 , 姓名 , 学期 , SUM ( 成绩 ) '总成绩' ,
CONVERT ( decimal ( 5 , 2 ) , AVG ( 成绩 ) ) '平均成绩'
FROM 学生表 JOIN 成绩表
ON 学生表.学号 = 成绩表.学号
JOIN 班级排课表
ON 成绩表.课程编号 = 班级排课表.课程编号
WHERE 成绩表.学号 = @student_ID AND 学期 = @tearm
GROUP BY 成绩表.学号 , 姓名 , 学期 )
9.3.3任务实施
用户自定义函数
--删除名为SelCjByID的用户自定义内嵌表值函数
DROP FUNCTION dbo.SelCjByID
/* 创建名为SelCjByID的用户自定义多语句表值函数，返回给定学号和学期的学生成绩信息*/
CREATE FUNCTION dbo.SelCjByID ( @student_ID varchar ( 12 ) , @tearm varchar ( 10 ) )
RETURNS @CjByID TABLE ( 学号 varchar ( 12 ) , 姓名 nvarchar ( 10 ) , 学期 varchar ( 10 ) , 总成绩 float , 平均成绩 decimal ( 5 , 2 ) )
AS
BEGIN
INSERT @CjByID SELECT 成绩表.学号 , 姓名 , 学期 , SUM ( 成绩 ) '总成绩' ,
CONVERT ( decimal ( 5 , 2 ) , AVG ( 成绩 ) ) '平均成绩'
FROM 学生表 JOIN 成绩表 ON 学生表.学号 = 成绩表.学号
JOIN 班级排课表 ON 成绩表.课程编号 = 班级排课表.课程编号
WHERE 成绩表.学号 = @student_ID AND 学期 = @tearm
GROUP BY 成绩表.学号 , 姓名 , 学期
RETURN
END
9.3.3任务实施
用户自定义函数
3. 创建用户自定义内嵌表值函数，返回给定学号的学生各学期的成绩信息，成绩信息包括该学生各个学期所有课程的总成绩和平均成绩（小数位数保留两位）。将该函数修改为用户自定义多语句表值函数，实现上述功能。
USE Student
GO
/* 创建用户自定义内嵌表值函数，返回给定学号的学生各学期的成绩信息。*/
CREATE FUNCTION dbo.SelCjByID2 ( @student_ID varchar ( 12 ))
RETURNS TABLE
AS
RETURN (SELECT 学期 , SUM ( 成绩 ) '总成绩' ,
CONVERT ( decimal ( 5 , 2 ) , AVG ( 成绩 ) ) '平均成绩'
FROM 成绩表 JOIN 班级排课表
ON 成绩表.课程编号 = 班级排课表.课程编号
WHERE 学号 = @student_ID
GROUP BY 学期)
9.3.3任务实施
用户自定义函数
--删除名为SelCjByID2的用户自定义内嵌表值函数
DROP FUNCTION dbo.SelCjByID2
GO
/* 创建名为SelCjByID2的用户自定义多语句表值函数，返回给定学号学生各学期的成绩信息。*/
CREATE FUNCTION dbo.SelCjByID2 ( @student_ID varchar ( 12 ))
RETURNS @CjByID2 TABLE ( 学期 varchar ( 10 ) , 总成绩 float , 平均成绩 decimal ( 5 , 2 ) )
AS
BEGIN
INSERT @CjByID2 SELECT 学期 , SUM ( 成绩 ) '总成绩' ,
CONVERT ( decimal ( 5 , 2 ) , AVG ( 成绩 ) ) '平均成绩'
FROM 成绩表 JOIN 班级排课表 ON 成绩表.课程编号 = 班级排课表.课程编号
WHERE 学号 = @student_ID
GROUP BY 学期
RETURN
END
9.3.3任务实施
用户自定义函数
4. 创建用户自定义内嵌表值函数，返回给定课程编号的成绩信息，成绩信息包括课程编号、课程名称、最高分、最低分、平均分（小数位数保留两位）。将该函数修改为用户自定义多语句表值函数，实现上述功能。
USE Student
GO
CREATE FUNCTION dbo.SelCjByCou ( @course_No varchar ( 3 ) )
RETURNS TABLE
AS
RETURN(SELECT 成绩表.课程编号 ,课程名称 , MAX ( 成绩 ) '最高分' ,
MIN ( 成绩 ) '最低分' ,
CONVERT ( decimal ( 5 , 2 ) , AVG (成绩 ) ) '平均成绩'
FROM 成绩表 JOIN 课程表
ON 成绩表.课程编号 = 课程表.课程编号
WHERE 成绩表.课程编号 = @course_No
GROUP BY 成绩表.课程编号 , 课程名称)
9.3.3任务实施
用户自定义函数
--删除用户自定义内嵌表值函数SelCjByCou
DROP FUNCTION dbo.SelCjByCou
/* 创建名为SelCjByCou的用户自定义多语句表值函数，返回给定课程编号的成绩信息。*/
CREATE FUNCTION dbo.SelCjByCou ( @course_No varchar ( 3 ) )
RETURNS @CjByCou TABLE (课程编号 varchar ( 3 ) , 课程名称 nvarchar ( 50 ) ,
最高分 float , 最低分 float , 平均成绩 decimal ( 5 , 2 ) )
AS
BEGIN
INSERT @CjByCou SELECT 成绩表.课程编号 ,课程名称 , MAX ( 成绩 ) '最高分' , MIN ( 成绩 ) '最低分' , CONVERT ( decimal ( 5 , 2 ) , AVG (成绩 ) ) '平均成绩'
FROM 成绩表 JOIN 课程表 ON 成绩表.课程编号 = 课程表.课程编号
WHERE 成绩表.课程编号 = @course_No
GROUP BY 成绩表.课程编号 , 课程名称
RETURN
END
任务4:游标
游标
使用SELECT语句为变量赋值时，只能将结果集的一条记录赋值给一组变量，然而在实际应用开发中，往往需要同时处理一条或多条记录，单纯的变量赋值很难满足工程项目的开发需要，游标提供了实现这种功能的机制。
9.4.1任务分析
游标
掌握游标的使用方法，会灵活运用游标解决实际问题。
9.4.2相关知识
游标
游标主要应用于编写存储过程、触发器和批处理脚本程序中，可以实现对由SELECT语句返回的结果集进行逐条处理。
1．游标支持的功能
（1）在结果集中定位特定的数据行。
（2）从结果集的当前位置检索数据行。
（3）可以修改结果集当前位置数据行的数据。
2．游标的分类
SQL Server支持3种类型的游标：Transact-SQL游标、应用程序编程接口（API）服务器游标及客户端游标，这里主要介绍Transact-SQL游标。
3．使用游标的步骤
使用游标的典型过程如下：声明游标；打开游标；循环从游标中检索记录；关闭游标；释放游标。
9.4.2相关知识
游标
（1）声明游标
语法格式如下：
DECLARE 游标名 CURSOR
[ LOCAL | GLOBAL ]
[ FORWARD_ONLY | SCROLL ]
[ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ]
[ READ_ONLY ]
FOR SELECT语句
[ FOR UPDATE [ OF 列名 [ ,…n ] ] ]
参数说明：
LOCAL：定义游标的作用域仅限在其所在的批处理、存储过程或触发器中。
GLOBAL：指定该游标的作用域是全局的。
9.4.2相关知识
游标
SELECT语句：用来定义游标所要处理的结果集。在SELECT语句中不允许使用关键字COMPUTE、COMPUTE BY、FOR BROWSE和INTO。
FORWARD_ONLY：指定游标只能从第一行滚动到最后一行。
SCROLL：指定所有的提取选项（FIRST、LAST、PRIOR、NEXT、RELATIVE、ABSOLUTE）均可使用。
STATIC：静态游标类型。
KEYSET：键集游标类型。
DYNAMIC：动态游标类型。
FAST_FORWRD：只进游标类型。
READ_ONLY：设定游标为只读。
UPDATE [ OF 列名 [ ,…n ] ]：定义游标中能够更新的列。如果指定“OF 列名 [ ,…n ]”就只允许修改列出的列，如果在UPDATE中未指定列的列表，除非指定了READ_ONLY并发选项，否则所有列均可更新。
9.4.2相关知识
游标
（2）打开游标
语法格式如下：OPEN 游标名
（3）循环从游标中检索记录。
①从游标中提取数据，其语法格式如下：
FETCH [ FIRST | LAST | PRIOR | NEXT | ABSOLUTE { n | @nvar } | RELATIVE { n | @nvar } ]
FROM 游标名
[ INTO @变量名 [ ,…n ] ]
参数说明：
FIRST：返回游标中的第一行并将其作为当前行。
LAST：返回游标中的最后一行并将其作为当前行。
PRIOR：返回当前行的前一行数据，并移动记录指针到当前位置。如果FETCH PRIOR为对游标的第一行提取操作，则没有行返回并且游标置于第一行之前。
NEXT：返回当前行的下一行数据，并移动记录指针到当前位置。如果FETCH NEXT为对游标的第一次提取操作，则返回结果集的第一行。NEXT为默认的游标提取选项。
9.4.2相关知识
游标
ABSOLUTE { n | @nvar }：当n或@nvar为正数时，返回从游标头开始的第n行并将返回的行变成新的当前行。当n或@nvar为负数时，返回游标尾之前的第n行并将返回的行变成新的当前行。当n或@nvar为0时，则没有行返回。n必须为整型常量且@nvar必须为smallint、tinyint或int。
RELATIVE { n | @nvar }：当n或@nvar为正数时，返回当前行之后的第n行并将返回的行变成新的当前行。当n或@nvar为负数时，返回当前行之前的第n行并将返回的行变成新的当前行。当n或@nvar为0时，返回当前行。如果对游标的第一次提取操作时将FETCH RELATIVE的n或@nvar指定为负数或0，则没有行返回。n必须为整型常量且@nvar必须为smallint、tinyint或int。
INTO @变量名 [ ,…n ]：存入变量。允许将当前行的指定列数据存放到所给出的局部变量中。列表中的每个局部变量从左到右要与游标SELECT结果集中的相应列对应，数据类型必须与对应列的数据类型相同或兼容，局部变量的数目必须与游标选择列表中列的数目相同。
②如果需要，可能使用UPDATE或DELETE语句修改游标位置的数据行。
③关于@@FETCH_STATUS
0：FETCH语句成功。
-1：FETCH语句失败或此行不在结果集中。
-2：被提取的行不存在。
9.4.2相关知识
游标
（4）关闭游标
其语法格式如下：CLOSE 游标名
（5）释放游标
其语法格式如下：DEALLOCATE 游标名
9.4.3任务实施
游标
1. 使用游标将学生表中第三条记录的政治面貌列的值改为“预备党员”。
USE Student
GO
--声明游标SelStudent，要处理的结果集是从学生表中检索所有记录。
DECLARE SelStudent CURSOR SCROLL FOR SELECT * FROM 学生表
--打开游标
OPEN SelStudent
--获取游标的第三条记录
FETCH ABSOLUTE 3 FROM SelStudent
--将该记录的政治面貌列的值修改为预备党员
UPDATE 学生表 SET 政治面貌 = '预备党员' WHERE CURRENT OF SelStudent
--关闭游标
CLOSE SelStudent
--释放游标
DEALLOCATE SelStudent
9.4.3任务实施
游标
2. 使用游标将学生表中的最后一条记录删除。
USE Student
GO
--声明游标SelStudent，要处理的结果集是从学生表中检索所有记录。
DECLARE SelStudent CURSOR SCROLL FOR SELECT * FROM 学生表
--打开游标
OPEN SelStudent
--获取游标的最后一条记录
FETCH LAST FROM SelStudent
--将该记录删除
DELETE 学生表 WHERE CURRENT OF SelStudent
--关闭游标
CLOSE SelStudent
--释放游标
DEALLOCATE SelStudent
9.4.3任务实施
游标
3. 使用游标逐行显示班级编号为01201401的学生学号和姓名信息。
DECLARE @student_ID varchar ( 12 ) , @student_Name nvarchar ( 10 )
DECLARE SelStudent CURSOR FOR
SELECT 学号 , 姓名 FROM 学生表 WHERE 班级编号 = '01201401'
OPEN SelStudent
FETCH NEXT FROM SelStudent INTO @student_ID , @student_Name
PRINT SPACE ( 4 ) + '学号' + SPACE ( 12 ) + '姓名'
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @student_ID + SPACE ( 1 ) +
SPACE ( ( 20 - DATALENGTH ( @student_Name ) ) / 2 ) + @student_Name
FETCH NEXT FROM SelStudent INTO @student_ID , @student_Name
END
CLOSE SelStudent
DEALLOCATE SelStudent
9.4.3任务实施
游标
3. 使用游标逐行显示班级编号为01201401的学生学号和姓名信息。
DECLARE @student_ID varchar ( 12 ) , @student_Name nvarchar ( 10 )
DECLARE SelStudent CURSOR FOR
SELECT 学号 , 姓名 FROM 学生表 WHERE 班级编号 = '01201401'
OPEN SelStudent
FETCH NEXT FROM SelStudent INTO @student_ID , @student_Name
PRINT SPACE ( 4 ) + '学号' + SPACE ( 12 ) + '姓名'
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @student_ID + SPACE ( 1 ) +
SPACE ( ( 20 - DATALENGTH ( @student_Name ) ) / 2 ) + @student_Name
FETCH NEXT FROM SelStudent INTO @student_ID , @student_Name
END
CLOSE SelStudent
DEALLOCATE SelStudent
项目小结
本项目主要介绍了Transact-SQL编程的基础知识，包括批处理、注释符、标识符、常量、变量、运算符、表达式、输出语句、流控语句、系统内置函数、用户自定义函数和游标。其中流控语句又包括BEGIN…END语句、IF…ELSE语句、CASE…END语句、 WHILE语句和RETURN语句；系统内置函数又包括聚合函数、字符串函数、日期函数、系统函数和排序函数等；用户自定义函数又包括用户自定义标量值函数、用户自定义内嵌表值函数和用户自定义多语句表值函数。读者应掌握上述知识的使用方法，并能综合运用上述知识编写程序，以解决实际问题。
实 训 项 目
综合实训1：创建用户自定义标量值函数，计算用户给定整数的阶乘值。（假设用户给定值为n，则计算1*2*3*…*n的值，n的取值范围为1到20的任何整数。）
实训项目
实训目的：
掌握Transact-SQL编程的基础知识，包括局部变量的声明和赋值，BGEIN语句、IF语句、WHILE语句、RETURN语句和系统内置函数CONVERT函数的使用方法，用户自定义标量值函数的创建和调用等。并能够综合运用上述知识编写程序，解决实际问题。
实训要求：
该题目要求通过创建用户自定义标量值函数来实现，使用WHILE语句来计算输入参数的阶乘值，并通过IF语句对输入参数进行判断，以决定是返回阶乘值，还是返回错误提示信息。
实训步骤：
实训步骤中涉及的代码如下：
实训项目
CREATE FUNCTION dbo.CalcFactorial ( @n tinyint )
RETURNS nvarchar ( 30 )
AS
BEGIN
IF @n < 1 OR @n > 20
RETURN '您所给定的输入参数不在有效的范围内，请检查后重新调用！'
DECLARE @i tinyint , @Fact bigint , @ReturnFact nvarchar ( 30 )
SELECT @i = 1 , @Fact = 1 --为局部变量赋值
WHILE @i <= @n
BEGIN
SET @Fact = @Fact * @i --进行累乘操作
SET @i = @i + 1 --循环控制变量进行加1操作
END
SET @ReturnFact = CONVERT ( nvarchar ( 30 ) , @Fact )
RETURN @ReturnFact
END
综合实训2：创建用户自定义内嵌表值函数，统计给定日期的图书借出量信息，要求显示借出日期和数量。
实训项目
实训目的：
掌握Transact-SQL编程的基础知识，包括系统内置函数COUNT函数的使用方法，用户自定义内嵌表值函数的创建和调用等。并能够综合运用上述知识编写程序，解决实际问题。
实训要求：
该题目要求通过创建用户自定义表值函数来实现，使用系统内置函数COUNT来统计图书的借出量，使用GROUP BY子句根据借出日期进行分组统计。
实训步骤：
实训步骤中涉及的代码如下：
实训项目
USE Book
GO
/* 创建用户自定义内嵌表值函数，统计给定日期的图书借出量信息，要求显示借出日期和数量。*/
CREATE FUNCTION dbo.StaQuantity ( @Borrow_Date datetime )
RETURNS TABLE --返回值的数据类型为TABLE
AS
RETURN ( SELECT Borrow_Date '借出日期' , COUNT ( * ) '借出数量' --统计数量
FROM Borrow_Info
WHERE Borrow_Date = @Borrow_Date
GROUP BY Borrow_Date )--根据借出日期进行分组统计
综合实训3：创建用户自定义内嵌表值函数，使用简单CASE表达式，查询给定出版社的图书信息，内容包括图书编码、图书名称和图书状态。
实训项目
实训目的：
掌握Transact-SQL编程的基础知识，包括简单CASE表达式的使用方法，用户自定义内嵌表值函数的创建和调用等。并能够综合运用上述知识编写程序，解决实际问题。
实训要求：
该题目要求通过创建用户自定义内嵌表值函数来实现，使用简单CASE表达式来获取“图书状态”列的值，该列的值是由Is_Borrow列的值来决定的。若Is_Borrow列的值为“是”，则图书状态列的值为“已借出”；若Is_Borrow列的值为“否”，则图书状态列的值为“未借出”。
实训步骤：
实训步骤中涉及的代码如下：
实训项目
USE Book
GO
CREATE FUNCTION dbo.SelBook ( @Publisher nvarchar ( 30 ) )
RETURNS TABLE --返回值的数据类型为TABLE
AS
RETURN
( SELECT Book_No '图书编码' , Book_Name '图书名称' , '图书状态' =
CASE Is_Borrow --使用简单CASE表达式，判断Is_Borrow列的值。
--若Is_Borrow列的值为“是”，则图书状态的值为“已借出”
WHEN '是' THEN '已借出'
--若Is_Borrow列的值为“否”，则图书状态的值为“未借出”
WHEN '否' THEN '未借出'
END
FROM Book_Info
WHERE Publisher = @Publisher )
综合实训4：创建用户自定义内嵌表值函数，使用CASE搜索表达式，查询给定读者编码的图书借阅情况，内容包括图书名称、归还日期和借阅状态。
实训项目
实训目的：
掌握Transact-SQL编程的基础知识，包括CASE搜索表达式的使用方法，用户自定义内嵌表值函数的创建和调用等。并能够综合运用上述知识编写程序，解决实际问题。
实训要求：
该题目要求通过创建用户自定义内嵌表值函数来实现，使用CASE搜索表达式来获取“借阅状态”列的值，该列的值是由Is_Return列的值来决定的。若Is_Return列的值为“是”，则借阅状态列的值为“已归还”；若Is_Return列的值为“否”，则借阅状态列的值为“未归还”。由于图书名称来自于图书信息表，故要完成该题目，就要用到多表查询。
实训步骤：
实训步骤中涉及的代码如下：
实训项目
CREATE FUNCTION dbo.SelBorrow ( @Reader_No varchar ( 5 ) )
RETURNS TABLE --返回值的数据类型为TABLE
AS
RETURN
( SELECT Book_Name '图书名称' , Return_Date '归还日期' , '借阅状态' =
CASE --使用CASE搜索表达式，判断Is_Return列的值。
--若Is_Return列的值为“是”，则借阅状态的值为“已归还”
WHEN Is_Return = '是' THEN '已归还'
--若Is_Return列的值为“否”，则借阅状态的值为“未归还”
WHEN Is_Return = '否' THEN '未归还'
END
FROM Borrow_Info JOIN Book_Info --多表连接查询
--两个表的多表连接查询，要有一个连接条件
ON Borrow_Info.Book_No = Book_Info.Book_No
WHERE Reader_No = @Reader_No )
综合实训5：创建用户自定义多语句表值函数，统计用户给定读者编码的罚款信息，要求显示读者编码、读者姓名、最多罚款金额、最少罚款金额和总罚款金额。
实训项目
实训目的：
掌握Transact-SQL编程的基础知识，包括系统内置函数MAX、MIN、SUM函数的使用方法，用户自定义多语句表值函数的创建和调用等。并能够综合运用上述知识编写程序，解决实际问题。
实训要求：
该题目要求通过创建用户自定义多语句表值函数来实现。使用系统内置函数MAX、MIN和SUM函数，获取最多罚款金额、最少罚款金额和总罚款金额。由于读者姓名信息来自于读者信息表，故涉及两个表的多表连接查询，要有一个连接条件。使用GROUP BY子句根据读者编码和读者姓名进行分组统计。
实训步骤：
实训步骤中涉及的代码如下：
实训项目
CREATE FUNCTION dbo.SelFine ( @Reader_No varchar ( 5 ) )
RETURNS @Fine TABLE (读者编码 varchar ( 5 ) , 读者姓名 nvarchar ( 10 ) ,
最多罚款金额 float , 最少罚款金额 float , 总罚款金额 float )
AS
BEGIN
INSERT @Fine
SELECT Fine_Info.Reader_No '读者编码' , Reader_Name '读者姓名' ,
MAX ( Fine_Money ) '最多罚款金额' , MIN ( Fine_Money ) '最少罚款金额' ,
SUM ( Fine_Money ) '总罚款金额'
FROM Fine_Info JOIN Reader_Info
ON Fine_Info.Reader_No = Reader_Info.Reader_No
WHERE Fine_Info.Reader_No = @Reader_No
GROUP BY Fine_Info.Reader_No , Reader_Name
RETURN --返回表变量
END
综合实训6：使用游标逐行显示图书信息表中的图书编码和图书名称信息。
实训项目
实训目的：
掌握Transact-SQL编程的基础知识，包括局部变量的声明和赋值，PRINT语句、BEGIN语句、WHILE语句、系统内置函数SPACE函数和游标的使用方法等。并能够综合运用上述知识编写程序，解决实际问题。
实训要求：
该题目要求使用游标来实现。使用DECLARE语句声明游标；使用OPEN语句打开游标；使用FETCH语句获取游标记录，并将其存储到局部变量中；通过判断@@FETCH_STATUS的值来判断获取记录是否成功，如果成功，使用PRINT语句将信息输出，通过WHILE语句遍历游标的所有记录；使用CLOSE语句关闭游标；使用DEALLOCATE语句释放游标。
实训步骤：
实训步骤中涉及的代码如下：
实训项目
USE Book
GO
DECLARE @Book_No varchar ( 8 ) , @Book_Name nvarchar ( 50 )
DECLARE SelBook CURSOR FOR SELECT Book_No , Book_Name FROM Book_Info
OPEN SelBook
FETCH NEXT FROM SelBook INTO @Book_No , @Book_Name
PRINT '图书编码' + SPACE ( 14 ) + '图书名称'
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @Book_No + SPACE ( 10 ) + @Book_Name
FETCH NEXT FROM SelBook INTO @Book_No , @Book_Name
END
CLOSE SelBook
DEALLOCATE SelBook
综合实训7：使用游标遍历借阅信息表的所有记录，若某读者所借阅的图书已经到期，但还未归还，则输出提示信息，该信息包括读者姓名和图书名称。
实训项目
实训目的：
掌握Transact-SQL编程的基础知识，包括局部变量的声明和赋值，PRINT语句、BEGIN语句、IF语句、WHILE语句、系统内置函数SPACE、DATEDIFF、GETDATE函数和游标的使用方法等。并能够综合运用上述知识编写程序，解决实际问题。
实训要求：
该题目要求使用游标来实现。使用FETCH语句获取游标记录，并将其存储到局部变量中；通过判断@@FETCH_STATUS的值来判断获取记录是否成功，如果成功，还要使用IF语句判断该读者借阅的图书是否到期，有没有归还，如果已经到期且还未归还，则使用PRINT语句将信息输出，通过WHILE语句遍历游标的所有记录。
实训步骤：
实训步骤中涉及的代码如下：
实训项目
USE Book
GO
DECLARE @Reader_Name nvarchar ( 10 ) , @Book_Name nvarchar ( 50 ) , @Return_Date datetime , @Is_Return nchar ( 1 )
DECLARE SelBorrow CURSOR FOR
SELECT Reader_Name , Book_Name , Return_Date , Is_Return
FROM Reader_Info JOIN Borrow_Info ON Reader_Info.Reader_No = Borrow_Info.Reader_No
JOIN Book_Info ON Borrow_Info.Book_No = Book_Info.Book_No
OPEN SelBorrow
PRINT '下列读者的借阅图书已经到期，但还未归还，请及时归还图书！'
PRINT ''
PRINT '读者姓名' + SPACE ( 14 ) + '图书名称'
FETCH NEXT FROM SelBorrow INTO @Reader_Name , @Book_Name , @Return_Date , @Is_Return
WHILE @@FETCH_STATUS = 0
BEGIN
实训项目
IF DATEDIFF ( DAY , @Return_Date , GETDATE () ) >= 0 AND @Is_Return = '否'
PRINT SPACE ( 1 ) + @Reader_Name + SPACE ( 10 ) + @Book_Name
FETCH NEXT FROM SelBorrow INTO @Reader_Name , @Book_Name ,
@Return_Date , @Is_Return
END
CLOSE SelBorrow
DEALLOCATE SelBorrow
THANKS

展开更多......

收起↑