26
数据库技术与应用实验指导——SQL Server 2005
将该代码段连续执行两次，观察两次显示的信息及curriculum表中数据的变化，为什么前后两次执行时显示的信息会不同？
（5）在studentsdb数据库的student_info表中，以“性别”为分组条件，分别统计男生和女生人数。
（6）在grade表中，使用适当函数找出“高等数学”课程的最高分、最低分和平均分。 （7）定义一个datetime型局部变量@studate，以存储当前日期。计算student_info表中的学生的年龄，并显示学生的姓名、年龄。在以下代码的划线部分填入适当内容，以实现上述功能。
DECLARE datetime
SET @studate= --给@studate赋值为当前日期 SELECT 姓名, (@studate)-year(出生日期) AS 年龄
（8）运行以下代码，写出运行结果。
DECLARE @a int,@b int SET @a=168 SET @b=73
FROM student_info
（9）在局部变量@stu_id中存储了学号值。编写代码查询学号为0001的学生的各科平均成绩，如果平均分?60，则显示“你的成绩及格了，恭贺你 ！！”，否则显示“你的成绩不及格”。
IF ((SELECT AVG(分数) FROM grade WHERE 学号='0001')<60) PRINT '你的成绩不及格' ELSE PRINT '你的成绩及格了,恭贺你！！'
SELECT @a & @b,@a|@b,@a^@b
（10）运行以下代码段，写出运行的结果。
DECLARE @counter int SET @counter=1
WHILE @counter<10 BEGIN SELECT '@counter的值现在为： '+CONVERT(CHAR(2),@counter)
SET @counter=@counter+1 END
（11）查询grade表。如果分数大于等于90，显示A； 如果分数大于等于80小于90，显示B；如果分数大于等于70小于80，显示C；如果分数大于等于60小于70，显示D； 其他显示E。在以下代码的划线部分填入适当内容完成上述功能。
SELECT 学号,分数,等级= CASE
数据库技术与应用实验指导——SQL Server 2005
分数>=90 THEN 'A'
WHEN 分数>=80 AND 分数<90 'B' WHEN 分数>=70 AND 分数<80 THEN 'C' WHEN 分数>=60 AND 分数<70 THEN ELSE 'E' END
27
（12）计算grade表的分数列的平均值。如果小于80，则分数增加其值的5%；如果分数的最高值超过95，则终止该操作。在以下代码划线处填入适当的内容以完成上述功能。
WHILE (SELECT (分数) FROM grade)<80 BEGIN
UPDATE grade
SET 分数=分数*1.05
if (SELECT MAX(分数) FROM grade)> BREAK ELSE END
FROM grade
（13）编写代码计算并显示@n=1+2+3+?+20。
（14）编写代码计算并显示1~100之间的所有完全平方数。例如，81=92，则称81为完全平方数。
（15）计算1~100以内的所有素数。
（16）在studentsdb数据库中，使用游标查询数据。 ① 打开查询设计器，声明一个stu_cursor游标，要求返回student_info表中性别为“男”的学生记录，且该游标允许前后滚动和修改。
② 打开stu_cursor游标。 ③ 获取并显示所有数据。 ④ 关闭该游标。
（17）使用游标修改数据。 ① 打开stu_cursor游标。
② 将姓马的男同学的出生日期的年份加1。 ③ 关闭stu_cursor游标。
（18）声明游标变量@stu_c，使之关联stu_cursor游标，利用@stu_c查询年龄在6～9月份出生的学生信息。
（19）使用系统存储过程sp_cursor_list显示在当前作用域内的游标及其属性。
3．实验思考
（1）Transact-SQL语言的运算符主要有哪些？
（2）流程控制语句与其他编程语言提供的语句有何差别？ （3）区分局部变量与全局变量的不同，思考全局变量的用处。
28
数据库技术与应用实验指导——SQL Server 2005
（4）什么函数能将字符串前和尾的空格去掉？
（5）使用什么语句可以打开游标？打开成功后，游标指针指向结果集的什么位置？
注意：请同学们自行保存相关代码，以备下次实验使用，实验完成后，要及时撰写实验报告。
实验8 存储过程和触发器
1．实验目的
（1）掌握通过SQL Server管理平台和Transact-SQL语句CREATE PROCEDURE创建存储过程的方法和步骤。
（2）掌握使用Transact-SQL语句EXECUTE执行存储过程的方法。
（3）掌握通过SQL Server管理平台和Transact-SQL语句ALTER PROCEDURE修改存储过程的方法。
（4）掌握通过SQL Server管理平台和Transact-SQL语句DROP PROCEDURE删除存储过程的方法。
（5）掌握通过SQL Server管理平台和Transact-SQL语句CREATE TRIGGER创建触发器的方法和步骤。
（6）掌握引发触发器的方法。
（7）掌握使用SQL Server管理平台或Transact-SQL语句修改和删除触发器。 （8）掌握事务、命名事务的创建方法，了解不同类型的事务的处理情况。
2．实验内容及步骤
（1）在查询设计器中输入以下代码，创建一个利用流控制语句的存储过程letters_print，该存储过程能够显示26个小写字母。
CREATE PROCEDURE letters_print AS
DECLARE @count int SET @count=0 WHILE @count<26 BEGIN
PRINT CHAR(ASCII('a')+ @count) SET @count=@count +1 END
单击查询分析器的“执行查询”按钮，查看studentsdb数据库的存储过程是否有letters_print。
使用EXECUTE命令执行letters_print存储过程。
（2）输入以下代码，创建存储过程stu_info，执行时通过输入姓名，可以查询该姓名
数据库技术与应用实验指导——SQL Server 2005
对应的学生的各科成绩。
CREATE PROCEDURE stu_info @name varchar(40) AS
SELECT a.学号,姓名,课程编号,分数
FROM student_info a INNER JOIN grade ta ON a.学号= ta.学号
29
使用EXECUTE命令执行存储过程stu_info，其参数值为“马东”。
如果存储过程stu_info执行时没有提供参数，要求能按默认值查询（设姓名为“刘卫平”），如何修改该过程的定义？
（3）使用studentsdb数据库中的student_info表、curriculum表、grade表。
① 创建一个存储过程stu_grade，查询学号为0001的学生的姓名、课程名称、分数。 ② 执行存储过程stu_grade，查询0001学生的姓名、课程名称、分数。 ③ 使用系统存储过程sp_rename将存储过程stu_grade更名为stu_g。 （4）使用student_info表、curriculum表、grade表。
① 创建一个带参数的存储过程stu_g_p，当任意输入一个学生的姓名时，将从3个表中返回该学生的学号、选修的课程名称和课程成绩。
② 执行存储过程stu_g_p，查询“刘卫平”的学号、选修课程和课程成绩。 ③ 使用系统存储过程sp_helptext，查看存储过程stu_g_p的文本信息。 （5）使用student_info表。
① 创建一个加密的存储过程stu_en，查询所有男学生的信息。 ② 执行存储过程stu_en，查看返回学生的情况。
③ 使用Transact-SQL语句DROP PROCEDURE删除存储过程stu_en。 （6）使用grade表。
① 创建一个存储过程stu_g_r，当输入一个学生的学号时，通过返回输出参数获取该学生各门课程的平均成绩。
② 执行存储过程stu_g_r，输入学号0002。 ③ 显示0002号学生的平均成绩。 （7）输入以下代码，复制student_info表命名为stu2，为stu2表创建一个触发器stu_tr，当stu2表插入一条记录时，为该记录生成一个学号，该学号为学号列数据的最大值加1。
--复制student_info表命名为stu2
SELECT * INTO stu2 FROM student_info GO
--为stu2表创建一个INSERT型触发器stu_tr CREATE TRIGGER stu_tr ON stu2 FOR INSERT
AS
DECLARE @max char(4)
SET @max=(SELECT MAX(学号) FROM stu2) SET @max=@max+1
WHERE 姓名= @name
30
数据库技术与应用实验指导——SQL Server 2005
UPDATE stu2 SET 学号=REPLICATE('0',4-len(@max))+@max
FROM stu2 INNER JOIN inserted on stu2.学号=inserted.学号
执行以上代码，查看studentsdb数据库中是否有stu2表，展开stu2，查看其触发器项中是否有stu_str触发器。
在查询设计器的编辑窗口输入以下代码：
运行以上代码，查看stu2表的变化情况，为什么插入记录的学号值发生了改变？ （8）为grade表建立一个名为insert_g_tr 的INSERT触发器，当用户向grade表中插入记录时，如果插入的是在curriculum表中没有的课程编号，则提示用户不能插入记录，否则提示记录插入成功。在进行插入测试时，分别输入以下数据：
学号 课程编号 分数
0004 0003 76 0005 0007 69
观察插入数据时的运行情况，说明为什么？
（9）为curriculum表创建一个名为del_c_tr的DELETE触发器，该触发器的作用是禁止删除curriculum表中的记录。
（10）为student_info表创建一个名为update_s_tr的UPDATE触发器，该触发器的作用是禁止更新student_info表中的“姓名”字段的内容。
（11）使用Transact-SQL语句DROP TRIGGER删除update_s_tr触发器。
（12）为student_info表建立删除触发器del_s_tr，要求当student_info表的记录被删除后，grade表中相应的记录也能自动删除。
（13）在studentsdb数据库中，执行以下事务处理过程，说明这些事务属于哪一种事务类型（隐性事务、显性事务或自动式事务）。
①
BEGIN TRANSACTION
INSERT INTO student_info(学号,姓名) VALUES('0009','李青') INSERT INTO stu2(学号,姓名,性别) VALUES('0001','张主','女')
②
COMMIT TRANSACTION
SET IMPLICIT_TRANSACTIONS ON
GO
INSERT INTO grade(学号,课程编号) VALUES('0005','0007')
GO
IF ((SELECT count(*) FROM curriculum WHERE 课程编号='0007')=0)
注意：请同学们自行保存相关代码，以备下次实验使用，实验完成后，要及时撰写实验报告。