存储过程和触发器(实验报告).doc

长 沙 理 工 大 学计算机与通信工程学院实 验 报 告课程名称 数据库技术与应用 实验项目名称 存储过程和触发器 班级 学号 姓名 实验日期 年 月 日 指导教师签字 一实验目的（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 JOIN grade b ON a.学号 = b.学号 INNER JOIN curriculum 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 JOIN grade 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)BEGIN PRINT 记录插入成功ENDELSEBEGIN RAISERROR(无此课程，不能插入记录,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_infoFOR UPDATEASDECLARE 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)BEGIN RAISERROR(不能修改姓名,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