《实验五 存储过程和触发器》由会员分享，可在线阅读，更多相关《实验五 存储过程和触发器（6页珍藏版）》请在金锄头文库上搜索。
1、实验五 存储过程、触发器的创建和使用一. 实验目的：1) 了解存储过程的概念2) 了解使用存储过程的特点及用途3) 掌握创建存储过程的方法4) 掌握执行存储过程的方法5) 了解查看、修改和删除存储过程的方法6) 了解触发器和一般存储过程的主要区别7) 了解使用触发器的优点8) 掌握创建触发器的方法9) 掌握查看触发器信息的方法10) 了解删除触发器的方法二. 实验准备1 .熟悉存储过程的使用2. 熟悉触发器的使用；三. 实验要求1 .在实验之前做好实验准备2.完成数据库设计，并验收实验结果，提交实验报告四. 实验内容实验内容及步骤：(一)存储过程的创建和使用例1：在企业管理器中创建一个名为St
2、uInfo的存储过程，完成的功能是在student表 中查询系号为D2的学号、姓名、性别、年龄、系号的内容。CREATE PROCEDURE StuInfoASSELECT SNO AS 学号，SNAME AS 姓名，SSEX AS 性别，SAGE AS 年龄，DNO AS系号FROM SWHERE DNO= D2例2：创建存储过程StuScoreInfo，完成的功能是在表student,course和study中查询以 下字段：学号、姓名、性别、课程名称、考试分数。程序清单：打开student数据库use mydb查询是否已存在此存储过程，如果存在，就删除它if exists (select
3、 name from sysobjectswhere name = StuScoreInfo and type= P)drop procedure StuScoreInfogo创建存储过程creat proceure StuScoreInfoasselect student.sno as 学号，sname as 姓名，sex as性别，ame as 课程名称，study.grade as 考试分数from student,course,studywhere student.sno=study.sno and o=o例3:创建一个带有参数的存储过程 stu_info，该存储过程根据传入的学生编号
4、，在 t_student中查询此学生的信息。程序清单：删除已存在的存储过程if exists (select name from sysobjects where name = stu_info and type = P ) drop procedure stu_info go创建存储过程create procedure stu_infosno varchar(8)asselectsno as学号，sname as 姓名，sex as性别，birthday as出生日期polity as政治面貌 from studentwhere sno=sno(二) 执行存储过程的方法例1：执行前面例1中创
5、建的StuInfo存储过程。Use mydbExec StuInfo/*或者直接写存储过程的名称StuInfo*/注意：如果省略Exec关键字，则存储过程必须是批处理中的第一条语句，否则会出错。例2：执行前面例2中创建的StuScoreInfo存储过程。Use mydbExec StuScoreInfo例3：执行前面例3中创建的Stu_Info存储过程，该存储过程有一个输入参数“学号”在 执行时要传入一个学号值。Use mydbExec Stu_Info s1或：Use mydbExec Stu_Info sno= s1例4：创建存储过程d_grade,根据指定的课程名（输入参数）返回该课程的
6、最高分、最低 分、平均分（输出参数）。要求在创建存储过程前要先判断该存储过程是否已存在，如果存 在，则将其删除。（三）查看存储过程使用企业管理器，右击要查看的存储过程，在弹出的快捷菜单中选择“属性”选项，弹 出“存储过程属性”对话框，在此对话框中可看到存储过程的源代码。1. 使用系统存储过程查看用户创建的存储过程：sp_help objname=name用于显示存储过程的参数及其数据类型。sp_helptext objname=name用于显示存储过程的代码。sp_depends objname= object用于显示和存储过程相关的数据库对象。（四）修改存储过程使用企业管理器中右击要查看的存
7、储过程，在弹出的快捷菜单中选择“属性”选项，弹 出“存储过程属性”对话框，在此对话框中可直接修改存储过程的代码。1.使用T_SQl修改存储过程例1：修改前面创建的stu_info存储过程，使之完成以下功能：根据传入的学号，在表 student,course,study中查询此学生的学号，姓名、性别、考试课程名称和考试分数。Use mydbAlter procedure stu_infosno varchar（10）asselect sname as 姓名，sex as性别，ame as 课程名称，score as 考试成绩from student，course，studywhere stude
8、nt.sno=sno and student.sno=study.sno and o=oexec stu_info s1（五）重命名存储过程1. 使用企业管理器，右击要操作的存储过程，在弹出的快捷菜单中选择“重命名”选项。2. 使用T_SQl修改存储过程sp_rename原存储过程名称，新存储过程名称（六）删除存储过程3. 使用 SQL Server Management Studio 修改存储过程在 SQL Server Management Studio 中，右击要操作的存储过程，在弹出的快捷菜单中选择“删除”选项。4. 使用T_SQl修改存储过程drop procedure proced
9、ure，n触发器的创建和使用(一) 触发器的创建 例1：创建一个INSERT触发器，当在表student中插入一条新记录时，触发该触发器，并 给出“你插入了一条新记录！”的提示信息。在文本框中输入以下文本： CREATE TRIGGER Stu_Insert on dbo.student FOR INSERT ASDECLARE msg char(30) SET msg= ”你插入了一条新记录！” print msg 例2：创建一个AFTER触发器，要求实现以下功能：在sc表上创建一个插入、更新类型的 触发器scoreCheck，当在grade字段中插入或修改考试分数后，触发该触发器，检查分数
10、 是否在0100之间。 Use student if exists (select name from sysobjects where name =scoreCheck and type= TR ) drop trigger scoreCheck /*创建触发器*/ create trigger scoreCheck on sc for insert,update as if update(score) print AFTER触发器开始执行 begindeclare ScoreValue real select ScoreValue=(select score from inserted)
11、if ScoreValue100 or ScoreValue0print 输入的分数有误，请确认输入的考试分数！ end 创建了 scoreCheck触发器之后，在T-SQL中输入以下SQL语句： use mydb print 在sc中插入记录时触发器执行结果:/*在屏幕上显示引号中内容*/print /*在屏幕上显示一空行*/insert into sc values( s2， 01，-40)/*在屏幕上显示输入错误信息*/update sc set score=123 where sno= s1 and cno= 1/*在屏幕上显示输入错误信息*/例3：创建一个INSTEAD OF触发器，
12、要求实现以下功能：在course表上创建一个删除类 型的触发器NotAllowDelete，当在c表中删除记录时，触发该触发器，显示“本表中的数 据不允许被删除！不能执行删除操作！”的提示信息。 if exists (select name from sysobjectswhere name = NotAllowDelete and type = TR )drop trigger NotAllowDelete/*创建触发器*/create triggerNotAllowDeleteon courseinstead of deleteasprint INSTEAD OF触发器开始执行print
13、本表中的数据不允许被删除！不能执行删除操作！测试NotAllowDelete触发器，在T-SQL中输入以下SQL语句：delete from course where cno=20030101/*屏幕上显示NSTEAD OF触发器开始执行*/例4：为course表创建一个INSERT触发器，当插入的新行中开课学分的值不是16时，就 激活该出发器，撤销该插入操作，并使用RAISERROR语句返回一个错误信息。CREATE TRIGGER tri_INSERT_KC ON COURSEFOR INSERTASDECLARE 开课学分 tinyintSELECT 开课学分=COURSE. credi
14、tFROM COURSE,InsertedWHERE COURSE.课程号=Inserted.课程号如果新插入行的开课学分的值不是16，则撤销插入，并给出错误信息IF 开课学分 NOT BETWEEN 1 AND 6BEGINROLLBACK TRANSACTIONRAISERROR( 开课学分的取值只能是广6!，16,10)END例5：为COURSE表再创建一个UPDATE触发器，当更新了某门课程的课程号信息时，就激活 该触发器级联更新STUDY表中相关的课程号信息，并使用PRINT语句返回一个提示信息。 CREATE TRIGGER tri_UPDATE_KC ON COURSEFOR UPDATEASIF UPDATE(CNO)检测课程号列是否被更新BEGINDECLARE 原课程号char(3),新课程号char(3) 声明变量获取更新前后的课程号的值SELECT 原课程号=Deleted.课程号,新课程号=Inserted.课程号FROM Deleted,InsertedWHERE Deleted.课程名=Inserted.课程名PRINT 准备级联更新STUDY表中的课程号信息级联更新STUDY表中相关成绩记录的课程号信息UPDATE STUDYSET CNO=新课程号WHERE CNO=原 课程号PRINT 已经级联更新STUDY表中原课程号
