存储过程、触发器实例.doc

CREATE TABLE Student ( Sno CHAR(9) PRIMARY KEY, Sname CHAR(20) , Ssex CHAR(2), Sage int , Sdept CHAR(20) ) CREATE TABLE SC ( Sno CHAR(9), Cno CHAR(4), Grade int ) 存储过程例1带有 SELECT 语句的存储过程：列出计算机系学生的信息CREATE PROCEDURE p_student1 ASSELECT * FROM student WHERE sdept = 计算机系调用：EXEC p_student1 例2带有输入参数的存储过程：查询指定系部的学生信息。CREATE PROCEDURE p_student2 sdept as varchar(20) ASSELECT * FROM student WHERE sdept = city调用：EXEC p_student2 计算机 EXEC p_student2 信息技术例3带有多个输入参数并有默认值的存储过程：查询系部、指定年龄段的学生的信息。CREATE PROCEDURE p_student3 sdept char(20)=计算机,ag1 int, ag2 intAS SELECT * FROM student WHERE zdept = zh and sage = ag1 and sage 70 then 1.1 when 成绩 between 60 and 70 then 1.2 else 1.3 end WHERE 课程号 = code 例7建立删除数据的存储过程。对成绩表，删除指定课程号的不及格的学生的成绩记录。CREATE PROCEDURE p_delete code char(20)AS DELETE FROM 成绩表 WHERE 成绩 60 and 课程号 = code 从存储过程中返回数据例1对学生student表，建立查询指定学号的学生信息的存储过程，如果用户没有输入学号，则在屏幕上显示“请输入学生学号”，并从存储过程中退出。如果用户输入了学生的学号，则执行相应的查询。CREATE PROCEDURE p_Findsid sid varchar(13) = NULLAS IF sid IS NULL BEGIN PRINT 必须输入学生的学号 RETURN END ELSE BEGIN SELECT * FROM student WHERE sid = sid END END例2. 有返回状态代码的存储过程。student表，检查用户指定的学号的性别。如果性别是男性则返回状态代码“1”，否则，状态代码“2”CREATE PROCEDURE p_studentsex sid varchar(12)AS IF (SELECT ssex FROM student WHERE sid= sid ) = 男 RETURN “1” ELSE RETURN “2”执行1：指定学号为：0200001DECLARE return_status intEXECUTE return_status = p_studentsex 0200001Select 状态=return_status Go执行2：指定学号为：0700005DECLARE return_status intEXECUTE return_status = p_studentsex 0700005Select 状态=return_status Go触发器：CREATE TABLE Student ( Sno CHAR(9) PRIMARY KEY, Sname CHAR(20) , Ssex CHAR(2), Sage int , Sdept CHAR(20) ) CREATE TABLE SC ( Sno CHAR(9), Cno CHAR(4), Grade int ) 例1：创建如下触发器，在插入、修改和删除记录时，自动显示表中内容create trigger studenttrig1 on student for insert,update,delete as select * from student 执行如下语句，激发触发器 insert into student values (0000a,张三,男,19,cs ) update student set sname=张五 where sno=0000adrop table Student CREATE TABLE Student ( Sno CHAR(9) PRIMARY KEY, Sname CHAR(20) , Ssex CHAR(2), Sage int , Sdept CHAR(20) ) v 例2：下例说明 inserted 表和 deleted 表的作用v v create trigger student _trig2 on studentv for insert,update,deletev asv print inserted表v select * from insertedv print deleted表v select * from deleted v 执行触发语句：v 执行触发语句：v insert student values (00ab , pppp , 男, 20 , cs)v update student set sage=21 where sno=00abv delete student where sno=00abdrop table Student CREATE TABLE Student ( Sno CHAR(9) PRIMARY KEY, Sname CHAR(20) , Ssex CHAR(2), Sage int , Sdept CHAR(20) ) v 1. insert 和 update触发器v 这两种触发器用来检查插入或者修改后的数据是否满足要求v 例3，创建触发器，检查插入记录的学生年龄是否在18-20之间v create trigger student_trig3 on studentv for insert,updatev asv declare s_age intv select s_age = sage from insertedv if s_age 20v beginv rollbackv print (年龄值必须在18-20)v endv 执行触发语句： v insert student values (0044,张户,女,23,cs)v select * from studentv insert student values (0033,赵强,女,20,cs)v update student set sage =30 where sno=0033drop table Student CREATE TABLE Student ( Sno CHAR(9) PRIMARY KEY, Sname CHAR(20) , Ssex CHAR(2), Sage int , Sdept CHAR(20) ) v 2. delete 触发器v delete触发器通常用于下面的情况：v 在删除数据记录时，保持数据的一致性v 级联删除操作v 例4，关于学生表和成绩表的数据删除v delete studentv delete scv insert student values (01,aaaa,男,19,sc)v insert sc values (01,1,90)v insert sc values (01,2,96)v select * from studentv select * from scv 创建触发器v create trigger student_tr1 on studentv for deletev as v delete sc where sc.sno=(select sno from deleted)v 执行触发语句：v delete student where sno=01drop table Student CREATE TABLE Student ( Sno CHAR(9) PRIMARY KEY, Sname CHAR(20) , Ssex CHAR(2), Sage int , Sdept CHAR(20) ) v 3. 触发器的其它应用v 例5，在student 表上创建一个禁止删除和插入操作的触发器v create trigger student_tr1 on student for insert,updatev asv begin v print 不允许你插入或删除任何数据！v rollback transactionv returnv endv 执行触发语句：insert student values(003,aa,女,20,cs)v 例6，在student表上创建一个禁止修改主键操作的触发器v create trigger student_tr2 on studentv for updatev asv if update(sno)v beginv print 不能修改学号v rollbackv endv 执行触发语句： update student set sno=001 where sname=张力v 嵌套触发器的应用v 嵌套触发器指一个触发器触发后，执行了某个动作，该动作又触发了第二个触发器，以此类推，最多可32级。 v 例6，创建一个级联删除操作的触发器v create trigger student_tr1 on studentv for deletev as v delete sc where sc.sno=(select sno from deleted)create trigger sc_tr1 on scv for deletev asv print 该生的成绩信息均已删除！v 执行触发语句： delete student where sno=01v instead of 触发器的应用v instead off 指定执行