存储过程及触发器的练习

1、一、实验目的（1）理解存储过程的概念，掌握存储过程的使用方法，加深对客户/服务机制的理解。（2）学习触发器的使用，体会触发器执行的时机，加深对触发器功能和作用的理解知识回顾1.创建存储过程建立存储过程的语句如下：CREATE PROCEDURE过程名（参数表）AS SQL语句创建存储过程示例创建一个不带参数的存储过程CREATE PROCEDURE brow_student AS SELECT * FROM student WHERE classno=200501执行存储过程的方法(1) 独立执行存储过程:EXEC brow_student (2) 在INSERT语句中执行存储过程: INSE

2、RT INTO student_b EXEC brow_student 创建带参数的存储过程例:显示指定学号的学生信息。CREATE PROCEDURE stu_proc1 sno1 char(8)AS SELECT * FROM student WHERE sno=sno1 例：为参数设置缺省值。CREATE PROCEDURE sc_proc1 (sno1 char(8), cno1 char(20) =c01 )AS SELECT * from sc WHERE sno=sno1 and cno=cno1存储过程的返回值可以用return或select返回存储过程的值例：create p

3、rocedure sc_proc2 (sno1 char(8) asbegin declare sno2 char(8) select sno2=count(*) from sc where sno=sno1 if sno20 return 10 Else return 99endgodeclare s intexec s=sc_proc2 20050101 if s=10 print 找到记录elseprint 未找到记录Go create procedure sc_proc3 (sno1 char(8),cno1 char(4) as Declare var1 char(8) if (se

4、lect grade from sc where sno= sno1 and cno=cno1 )60 set var1 =及格 Else set var1 =不及格 Select 是否及格=var1 更改和删除存储过程更改存储过程：ALTER PROCEDURESQL Server中更改存储过程，就是用新定义的存储过程替换原来的定义。语句格式： ALTER PROCEDURE 同定义从当前数据库删除用户定义的存储过程。语句格式： DROP PROCEDURE 2.触发器（Trigger） 对应于数据的操纵类型，SQL Server 中的触发器可分为:INSERT触发器UPDATE触发器DEL

5、ETE触发器 当表中数据被修改（增、删、改）时，SQL Server自动执行相应的触发器。建立触发器的语句格式CREATE TRIGGER 触发器名 ON 表名 |视图名 WITH ENCRYPTION FOR INSERT,UPDATA,DELETE AS SQL操作语句表名（视图名）：触发该触发器的表名或视图名。WITH ENCRYPTION： 对触发器的定义文本加密。INSERT,UPDATA,DELETE ：说明触发触发器的事件。一个定义语句允许定义多个触发事件。 SQL操作语句：指定触发器动作。如果要指定多个触发器操作，需用BEGINEND将它们组成语句块。触发器中可使用的特殊表使用

6、触发器时，SQL Server提供了两张特殊的临时表： inserted表deleted表 表存在于高速缓存中，与创建触发器的表有相同的结构。用户可以使用该表检查某些修改操作的前后状态。并基于此状态采取行动。例如，可以使用该表的内容作为查询操作的判断条件，但要在FROM中写出使用的表名（inserted 或deleted）。用户不能直接修改该表中的数据。临时表inserted表：存放INSERTE和UPDATE语句的新数据。deleted表：存放DELETE和UPDATE语句的旧数据。UPDATE触发器可使用deleted表和inserted表INSERT类触发器INSERT 触发器：每在学生

7、备份表中增加一条记录时，同时要在学生表中删除一条相应的学生记录。CREATE TRIGGER student_BACKON student_bFOR INSERTAS DELETE from student FROM student_b WHERE student.sno = student_b.snoUPDATE类触发器例：对成绩表SC中的grade属性执行update语句时触发：修改成绩时，如果新成绩比原成绩低，则恢复原成绩而不做修改。CREATE TRIGGER up_grade ON sc FOR UPDATE AS UPDATE sc SET grade=deleted.grade

8、from deleted WHERE sc.sno = deleted.sno And o=o AND sc.gradeY时：返回值12，提示“X应小于Y！”当查找后没有满足条件的记录时：返回值-10，提示“对不起！没有满足条件的记录！”（2）建立存储过程P2，查询选修了某门课程的学生信息（其中课程名，返回值自行设计）（3）建立存储过程P3，将某一专业的所有学生的成绩放在一个临时表中（4）建立存储过程P4，计算某一个专业的某一门课和的平均成绩,显示出专业名称、课程名称和平均成绩 2）在学生表、成绩表和课程表中输入原始数据后，运行上述所建立的存储过程，观察其执行结果。2、触发器的创建与执行注意：在建立以下的触发器时，请删除各个之间的参照完整性和用户定义完整性1）触发器的建立（1）在课程表和成绩表上建立一组触发器，实现课程表中的课程号与成绩表中的课程号之间的参照关系。（2）在学生表上建立一个插入和修改触发器，实现当修改学生性别时，检查是否取值在“男”或“女”之间，如果是，由插入或修改成功，否由失败（3）在成绩表上建立一个修改类的触发器，实现当修改成绩时，检查修改后的成