实验七 SQLSERVER 2000 程序设计
实验目的：
1、领会程序中的批处理、脚本和注释的基本概念和使用方法。2、领会程序中事务的基本语句的使用。
3、掌握程序中的流程控制语句。
4、掌握游标的使用。
5、学会编写简单的SQL程序。
实验准备：
1、了解批处理、脚本和注释的语法格式。
2、了解程序中的流程控制语句BEGIN…END语句的使用。3、了解IF…ELSE语句的使用。
4、了解CASE语句的使用。
5、了解WAITFOR语句的使用。
6、了解WHILE语句的使用。
7、了解PRINT语句的使用。
8、了解游标的概念以及语法格式和使用。
实验任务：
内容一：简单程序设计
一、数据需求分析
设计程序时，经常需要使用各种流程控制语句，用来改变计算机的执行流程。SQL语言是用来进行数据库查询的结构化语言。由以下几部分组成： 1、数据定义语言（DDL）。用来执行数据库的任务，对数据库以及数据库中的各种对象进行创建、删除、修改等操作。
2、数据操纵语言（DML）。用来操纵数据库中各种对象，检索和修改数据。 3、数据控制语言（DCL）。用于安全管理，确定哪些用户可以查看数据库中的数据。
4、T-SQL增加的语言元素。包括变量、运算符、函数、流程控制语句和注
解。
二、内容要点分析
1、批处理
两个GO之间的SQL语句作为一个批处理。在一个批处理中可以包含一条或多条TRANSACT-SQL语句，成为一个语句组。这样的语句组从应用程序一次性发送到SQLSERVER 服务器进行执行。SQLSERVER 服务器将批处理编译成一个可执行单元，称为执行计划。
2、批处理的执行方式
ISQL实用程序、OSQL实用程序、ISQLW实用程序（查询分析器）
? ISQL实用程序、OSQL实用程序的执行方式：
(1)开始->程序->附件->命令提示符
(2)输入命令：OSQL–U SA 或ISQL–U SA 并输入密码
(3)退出：EXIT命令
3、脚本
存储在文件中的一组TRANSACT-SQL语句集合,可以包含一个或多个批处理。? 查询分析器是建立、编辑、使用脚本的最好环境。
? 在ISQL实用程序和OSQL实用程序中执行脚本：
ISQL/OSQL –U SA –I 脚本文件名.SQL
4、注释
（1）单行注释：使用两个连字符“--”作为注释符。
（2）多行注释：使用“/* */”作为注释符。
5、事务
作为单个逻辑工作单元执行的一系列操作，要么成功完成所有操作，要么就是失败，并将一切复原。事务有四个特性，分别是原子性、一致性、性、持久性(ACID)。
启动事务：BEGINTRANSACTION
结束事务：COMMITTRANSACTION
取消事务：ROLLBACKTRANSACTION
6、变量
变量是用来在语句之间传送数据的方式之一，SQL语言中有两种形式的变量，一种是用户定义的局部变量，另一种是系统提供的全局变量。
（1）全局变量：SQLSERVER 系统提供并赋值的变量，其作用范围可在任何程序中随时调用。
注意：
? 用户只能使用预先定义的全局变量（是一组特殊的函数），对用户来说 的只读的。
引用全局变量时，必须以标记符@@开头。?
（2）局部变量：可以作为计数器或控制循环执行的次数，还可以保存程序执行过程中的中间数据值，保存由存储过程返回的数据值等。
? 局部变量的定义
DECLAER@局部变量数据类型 [,…N]
? 局部变量的赋值方法
SET@局部变量=表达式
SELECT@局部变量=表达式[,…N]
?
局部变量的作用域 仅在声明局部变量的批处理、存储过程或触发器中。
? 局部变量必须先定义，后使用。
局部变量被引用时，要在其名称前加@。?
7、程序流程控制语句
主要用来控制SQL语句、语句块或存储过程的执行流程。
（1）BEGIN…END
格式：
BEGIN
语句1
语句2
…
END
作用：将多个T-SQL语句组合成一个语句块，做为一个整体处理。
（2）IF…ELSE
格式：
IF 布尔表达式
语句1
[ELSE
语句2
作用：根据给定的测试条件判断确定程序执行的分支。
（3）CASE语句
CASE 语句能够实现多种选择的计算，并将其中一个符合条件的结果表达式返回。按照使用形式的不同，可分为简单CASE和搜索CASE。
? 简单CASE语句的语法形式
CASE测试表达式
WHEN 测试值1THEN 结果表达式1 WHEN 测试值2THEN 结果表达式2 [ ...N]
[ ELSE 结果表达式N]
END
说明：
1）进行“测试表达式=测试值”的依次比较。
2）若相等，则将THEN后面的结果返回给SELECT语句中的赋值语句。
注意：
1）测试表达式与测试值的数据类型要相同。
2）一次只能有一个WHEN子句指定的结果表达式返回。
? 搜索CASE语句的语法形式
CASE
WHEN 布尔表达式1THEN 结果表达式1
WHEN 布尔表达式2THEN 结果表达式2
[...N]
[ELSE 结果表达式N]
END
说明：
1）测试每个WHEN子句后的布尔表达式，如果结果为TRUE，则返回相应的结 果表达式。
2）否则返回一个NULL值。
注意：
1）搜索CASE关键字后面不跟任何表达式。
2）一次只能返回一个WHEN子句指定的结果表达式。
（4）WAITFOR语句
格式：
WAITFOR DELAY '时间'|TIME'时间'
作用：暂停执行SQL语句，直到所设定的时间已到或已过才继续执行。
说明：
1）DELAY用于指定时间间隔。
2）TIME用于指定某一时刻。
（5）WHILE语句
格式：
WHILE 布尔表达式
BEGIN
语句序列1
[BREAK]
语句序列2
[CONTINUE]
语句序列3
END
作用：当需要多次重复处理某项工作时，就需使用WHILE语句。
说明：
1）通过布尔表达式来设置循环条件，当条件为TRUE，循环执行，否则退出 循环。
2）BREAK语句使程序完全退出循环。
3）CONTINUE语句使程序跳过其后面的语句，回到循环的第一条语句。
注意：
1）合理设置循环条件，避免出现死循环。
2）循环体中的语句序列可以是单个的T-SQL语句，也可以是用BEGIN…END 定义的语句块。
（6）PRINT语句
格式：
PRINT '字符串'|局部变量|全局变量
作用：向客户程序返回信息。
8、游标
一个对表进行操作的T-SQL语句通常可产生或处理一组记录，但是有些应用程序往往不需要将结果集作为一个单元来处理，而只是处理一行或几行。SQLSERVER 通过游标提供了对一个结果集进行逐行处理的功能。游标可看作是一种特殊的指针，它与某个查询结果相联系，可以指向结果集的任意位置。
游标的基本操作：声明游标->打开游标->提取数据->关闭游标->释放游标。
（1）声明游标
使用游标之前，应当先声明。游标的声明包括两个部分：游标的名称和游标所用的SQL语句。声明游标的语法如下：
DECLARE 游标名称CURSOR
FOR<SELECT 语句>
说明：
? SELECT语句主要用来定义游标所要进行处理的结果集。在声明游标的 SELECT语句中,不允许使用COMPUTE,COMPUTEBY 和INTO等关键字。
（2）打开游标
声明游标后，正式操作之前，必须打开它。打开游标的语法如下：
OPEN游标名称
当执行打开游标的语句时，服务器执行声明游标时使用的select语句，?
（3）提取数据
当用OPEN语句打开了游标并在数据库中执行了查询后，并不能立即利用查询结果集中的数据，
必须用FETCH语句来提取数据。一条FETCH语句一次可以将一条记录放入指定的变量中。FETCH语句是游标使用的核心。语法格式： FETCH
[[NEXT| PRIOR|FIRST|LAST|ABSOLUTE{n}|RELATIVE {n}] FROM]游标名称
[INTO@变量[，…n]]
说明：
? n和nvar表示游标相对于作为基准的数据行所偏离的位置。
? FIRST：取第一行数据；
LAST:取最后一行数据；
PRIOR:取前一行数据；
NEXT:取后一行数据；
RELATIVE:按相对位置取数据；
ABSOLUTE:按绝对位置取数据。
? 在使用into子句对变量赋值时，变量的数量和相应的数据类型必须和 声明游标时使用的select语句中引用到的数据列的数目、排列顺序和 数据类型完全保持一致，否则服务器会提示出错。
注意：
? 在默认情况下（fetchfrom 游标名）是表示取下一个数，即
fetchnext from游标名
? 游标只能一次从后台数据库中提取一条记录，在多数情况下，所要做的 是在数据库中从第一条记录开始提取，一直到结束。所以一般要将游标 提取数据的语句放在一个循环体内，直到将结果集中的全部数据提取完 后，跳出循环圈。通过检测全局变量@@fetch_status的值，可以得知 fetch语句是否取到最后一条。当@@fetch_status值为0时表明提取正 常，-1表示已经取到了结果集的末尾，而其他值均表明操作出了问题。 ? 使用游标提取数据的操作要与where循环紧密结合在一起。
（4）关闭游标
sqlserver服务器会专门为游标开辟一定的存储空间存放游在打开游标后，
标操作的数据结果集。在不使用游标的时候，一定要关闭游标，以通知服务器释放标所占的资源。关闭游标的语法如下：
CLOSE游标名称
说明：
?
关闭游标以后，可以再次打开游标。在一个批处理，也可以多次打开 和关闭游标。
（5）释放游标
游标结构本身也会占用一定的计算机资源，所以在使用完游标后，为了回收被游标占用的资源，应该将游标释放。释放游标的语法如下：
DEALLOCATE游标名称
说明：
? 当释放游标后，如要重新使用游标必须重新执行声明游标的语句。
三、实验内容
1、在“学生信息”数据库中，创建存储过程CX_PROC。根据输入的姓名测试“学 生”表中是否存在此人，若存在则显示该学生的资料，否则显示“查无此人！”。
步骤：
（1）打开查询分析器。
（2）在查询子窗口中输入如下程序代码。
USE 学生信息
GO
CREATE PROC CX_PROC
@XM CHAR(10)
AS
IF EXISTS(SELECT * FROM 学生WHERE姓名=@XM) BEGIN
PRINT @XM+'同学的资料如下：'
SELECT * FROM 学生WHERE姓名=@XM
END
ELSE
PRINT '查无此人！'
GO
（3）语法检查
（4）运行代码
（5）在查询结果显示窗口查看结果并分析结果。
本例演示BEGIN…END语句块和IF…ELSE语句及PRINT语句的使用方法。2、根据学生性别为变量赋值。
步骤：
（1）打开查询分析器。
（2）在查询子窗口中输入如下程序代码。
USE学生信息
GO
DECLARE @XB CHAR(4)
SELECT 姓名,@XB=
CASE性别
WHEN '男'THEN '男生'
WHEN '女'THEN '女生'
END
FROM 学生
GO
（3）语法检查
（4）运行代码
（5）在查询结果显示窗口查看结果并分析结果。
本例演示简单CASE表达式的使用方法。
3、从“学生信息”数据库中检索“网页制作”课程成绩，并将成绩划分成四个 等级：低于60分者为“不及格”，大于或等于60分而低于75分者为“中等”， 大于或等于75分而低于85分者为“良好”，大于或等于85分者为“优秀”， 其他情况判定为“无成绩”。
步骤：
（1）打开查询分析器。
（2）在查询子窗口中输入如下程序代码。
USE 学生信息
GO
SELECT 姓名,课程名称,成绩等级=
CASE
WHEN 成绩>=85THEN '优秀'
WHEN 成绩>=75THEN '良好'
WHEN成绩>=60THEN '中等'
WHEN成绩<60 THEN '不及格'
ELSE'无成绩'
END
FROM 学生JOIN学期成绩ON学生.学号=学期成绩.学号
JOIN 课程注册ON学期成绩.课程编号=课程注册.课程编号WHERE课程名称='网页制作'
GO
（3）语法检查
（4）运行代码
（5）在查询结果显示窗口查看结果并分析结果。
本例演示搜索CASE表达式的使用方法。
4、编程实现：求2--100之间的所有素数。
步骤：
（1）打开查询分析器。
（2）在查询子窗口中输入如下程序代码。
DECLARE @I INT,@J INT
SET@I=2
WHILE@I<=100
BEGIN
SET @J=2
WHILE @J<=@I-1
BEGIN
IF @I%@J=0
BREAK
ELSE
SET @J=@J+1
END
IF @I=@J
PRINT CONVERT(VARCHAR,@I)+'是素数' SET @I=@I+1
END
（3）语法检查
（4）运行代码
（5）在查询结果显示窗口查看结果并分析结果。
本例演示WHILE语句的使用方法。
四、实验作业
1、编写程序，实现查询总分成绩大于300分的学生人数。
2、显示字符串“China”中每个字符的ASCII码值和字符。
3、求出1—30000之间所有能够被123整除的整数。
4、根据学生的年龄范围显示相应信息：小于20岁的显示“年龄较小”，大于或等于20岁且小于24岁的显示“年龄适中”，大于或等于24岁的显示“年龄偏大”。
内容二：综合实验
一、实验内容
1、声明一个游标COMPUTER_CURSOR,用以查询计算机系的学生信息。
步骤：
（1）打开查询分析器。
（2）在查询子窗口中输入如下程序代码。
USE 学生信息
GO
DECLARECOMPUTER_CURSOR CURSOR FOR
SELECT学号,姓名,性别,专业
FROM学生
WHERE系别='计算机系'
GO
（3）语法检查
（4）运行代码
（5）在查询结果显示窗口查看结果并分析结果。
本例演示游标的声明方法。
2、对声明好的游标COMPUTER_CURSOR,一条条地取出其中的数据。
步骤：
（1）打开查询分析器。
（2）在查询子窗口中输入如下程序代码。
USE学生信息
GO
OPENCOMPUTER_CURSOR
/*执行第一次取数操作*/
FETCHNEXT FROM COMPUTER_CURSOR
/*检查上一次游标操作所返回的状态值（若成功，该变量值为0）*/
WHILE(@@SCORE_CURSOR=0)
BEGIN
FETCH NEXT FROM COMPUTER_CURSOR
END
说明：
继续执行取下一行数据的操作，
将返回错误当游标移动到最后一行数据时，信息，但这个信息只在@@FETCH_STATUS中体现，同时返回空白的数据。根据判断条件，程序终止循环。
（3）语法检查
（4）运行代码
（5）在查询结果显示窗口查看结果并分析结果。
本例演示游标通过移动指针按行提取记录的方法。
3、使用游标修改“学生信息”数据库中的数据，查询学号为“003”的学生，在 该生的02号课程成绩加上10分。
步骤：
（1）打开查询分析器。
（2）在查询子窗口中输入如下程序代码。
USE学生信息
GO
DECLARE @SCORE DECIMAL
--声明一个可更新的游标
DECLARE 学生成绩游标CURSORKEYSET FOR
SELECT 成绩
FROM 学期成绩
WHERE 学号='003'AND 课程编号='02'
FOR UPDATE
OPEN 学生成绩游标
--将提取的数据存入局部变量
FETCH ABSOLUTE 1 FROM 学生成绩游标INTO@SCORE
PRINT '修改前的成绩：'+CONVERT(VARCHAR,@SCORE) --通过游标修改数据
UPDATE 学期成绩
SET成绩=成绩+10
WHERECURRENT OF 学生成绩游标
FETCHABSOLUTE 1 FROM 学生成绩游标INTO@SCORE PRINT'修改后的成绩：'+CONVERT(VARCHAR,@SCORE) CLOSE学生成绩游标
DEALLOCATE学生成绩游标
GO
（3）语法检查
（4）运行代码
（5）在查询结果显示窗口查看结果并分析结果。
本例演示通过游标更新数据的方法。
二、实验作业
1、使用游标修改“学生信息”数据库中的数据，将所有学生的的02号课程成绩 都加上10分。
2、自行定义游标，然后打开该游标，输出其行数。