长 沙 理 工 大 学计算机与通信工程学院实 验 报 告课程名称 数据库技术与应用 实验项目名称 存储过程和触发器 班级 学号 姓名 实验日期 年 月 日 指导教师签字 一实验目的（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） 掌握事务、命名事务的创建方法，了解不同类型的事务的处理情况。二实验平台操作系统：Windows xpDBMS：SQL Server 2005三实验内容和步骤1使用 studentsdb 数据库中的 student_info 表、curriculum 表、grade 表。（1 ）创建一个存储过程 stu_grade，查询学号为 0001 的学生的姓名、课程名称、分数。（2 ）执行存储过程 stu_grade，查询 0001 学生的姓名、课程名称、分数。（3 ）使用系统存储过程 sp_rename 将存储过程 stu_grade 更名为 stu_g。CREATE PROCEDURE stu_grade ASSELECT a.学号, c.课程名称, b.分数FROM student_info a INNER JOINgrade b ON a.学号 = b.学号 INNER JOINcurriculum c ON b.课程编号 = c.课程编号WHERE (a.学号 = 0001)EXEC stu_gradesp_rename stu_grade,stu_g2使用 student_info 表、curriculum 表、grade 表。（1 ）创建一个带参数的存储过程 stu_g_p，当任意输入一个学生的姓名时，将从 3 个表中返回该学生的学号、选修的课程名称和课程成绩。（2 ）执行存储过程 stu_g_p，查询“刘卫平”的学号、选修课程和课程成绩。（3 ）使用系统存储过程 sp_helptext，查看存储过程 stu_g_p 的文本信息。CREATE PROCEDURE stu_g_p stu_name varchar(8) ASSELECT a.学号, c.课程名称, b.分数FROM student_info a INNER JOINgrade b ON a.学号 = b.学号 INNER JOIN curriculum c ON b.课程编号 = c.课程编号WHERE (a.姓名 = stu_name)EXEC stu_g_p 刘卫平sp_helptext stu_g_p3使用 student_info 表。（1 ）创建一个加密的存储过程 stu_en，查询所有男学生的信息。（2 ）执行存储过程 stu_en，查看返回学生的情况。（3 ）使用 Transact-SQL 语句 DROP PROCEDURE 删除存储过程 stu_en。CREATE PROCEDURE stu_enWITH ENCRYPTION AS SELECT *FROM student_infoWHERE 性别=男 EXEC stu_enDROP PROCEDURE stu_en4使用 grade 表。（1 ）创建一个存储过程 stu_g_r，当输入一个学生的学号时，通过返回输出参数获取该学生各门课程的平均成绩。CREATE PROC stu_g_r stu_no varchar(8)=NULL,stu_score real OUTPUTASSELECT stu_score =AVG(分数)FROM gradeWHERE (学号 =stu_no)（2 ）执行存储过程 stu_g_r，输入学号 0002。DECLARE score realEXEC stu_g_r 0002,score OUTPUT（3 ）显示 0002 号学生的平均成绩。SELECT score5为 grade 表建立一个名为 insert_g_tr 的 INSERT 触发器，当用户向 grade 表中插入记录时，如果插入的是在 curriculum 表中没有的课程编号，则提示用户不能插入记录，否则提示记录插入成功。在进行插入测试时，分别输入以下数据：学号 课程编号 分数0004 0003 760005 0007 69观察插入数据时的运行情况，说明为什么？CREATE TIGGER insert_g_tr ON gradeFOR UPDATE,INSERTASDECLARE fkc_id varchar(8)SET fkc_id=(SELECT 课程编号 FROM INSERTED)IF EXISTS(SELECT 1 FROM curriculum WHERE 课程编号=fkc_id)BEGINPRINT 记录插入成功ENDELSEBEGINRAISERROR(无此课程，不能插入记录,16,1) END6为 curriculum 表创建一个名为 del_c_tr 的 DELETE 触发器，该触发器的作用是禁止删除curriculum 表中的记录。CREATE TRIGGER del_c_trON curriculumASRAISERROR(禁止删除 curriculum 表中的记录)ROLLBACK TRANSACTION7为 student_info 表创建一个名为 update_s_tr 的 UPDATE 触发器，该触发器的作用是禁止更新 student_info 表中的“姓名”字段的内容。CREATE TIGGER update_s_tr ON student_infoFORUPDATEASDECLARE name_old varchar(8)DECLARE name_new varchar(8)SET name_old=(SELECT 姓名 FROM Deleted)SET name_new=(SELECT 姓名 FROM Inserted)IF (name_old!=name_new)BEGINRAISERROR(不能修改姓名,16,1) END8使用 Transact-SQL 语句 DROP TRIGGER 删除 update_s_tr 触发器。DROP TRIGGER 称 update_s_tr9为 student_info 表建立删除触发器 del_s_tr，要求当 student_info 表的记录被删除后，grade 表中相应的记录也能自动删除。CREATE TIGGER del_s_tr ON student_infoFORDELETEASDECLARE stu_no varchar(8)SET stu_no=(SELECT 学号 FROM Deleted)DELETE * FROM grade WHERE 学号=stu_no 四命令(代码)清单五调试和测试清单六教师批语与成绩评定