实验七 存储过程和触发器

一．实验目的：

1.掌握存储过程的创建及执行

2.掌握触发器的创建及测试

二．实验内容：（所有题写到实验报告中）

\1. 存储过程的创建及执行

   1）①在stuinfo数据库中创建一个存储过程p1，查询指定学号的学生的姓名和平均分，要求姓名和平均分使用返回参数。

   ②执行该存储过程查询学号为101的学生的姓名和平均分。

2）①在stuinfo数据库中创建存储过程P2，根据指定的学号和课程号判断成绩等级（A、B、C、D、E）(如果degree>=90则为A，依次类推，不及格为E)。

   ②执行该存储过程查看学号为101、课程号为3-105的成绩等级。

3）①在stuinfo数据库中创建存储过程P3，检查指定学号的学生是否有选课。有就输出其姓名、课程名和成绩，没有就输出“该生无选课”。

②执行该存储过程分别查看学号为101和888学生的选课情况。

4）①在OrderManagement数据库中创建存储过程P4，查询指定客户号的订单中有多少种器件。

②执行该存储过程查看客户号是C0001的客户所订购的器件种类的数量。

5）①在OrderManagement数据库中创建存储过程P5，查询指定年份的销售总额。

②执行该存储过程查看2001年的销售总额。要求执行完存储过程后按如下格式输出数据：

2001年的销售总额：

---

**元

6）①在OrderManagement数据库中创建存储过程P6，修改指定订单号和器件号的单价。

②执行该存储过程将订单号为OR-01C、器件号为P1001的单价修改为1000。

7）①在OrderManagement数据库中创建存储过程P7，查询订单中至少订购了“CPU P4 1.4G”和“内存”这两种器件的订单号。

②执行该存储过程。

\2. 触发器的创建及测试

1）①在student表上创建触发器t1，在用户插入、修改和删除记录时，都会自动显示表中的内容。

② 测试：对student表分别用insert、update和delete语句进行测试。

2）①建立一个触发器t2，当向student表中插入数据时，如果姓名不重复则插入，如果出现姓名重复的情况，则提示错误（raiserror('姓名重复，不能插入',16,1)）并回滚该事务（即取消插入的行）。

② 测试：对student表分别用insert语句插入一条姓名重复的记录和姓名不重复的记录进行测试。

3）①建立一个触发器t3，当向student表中插入数据时，如果出现性别不正确的情况，不回滚该事务，只提示错误消息。

② 测试：对student表分别用insert语句插入一条性别正确的和性别不正确的记录进行测试。

4）①一个修改触发器t4，该触发器防止用户修改表student的学号。

② 测试：对student表分使用update语句修改学号进行测试。

5）①建立一个触发器t5，将student表中所有修改时改前的记录及修改日期保存到ss表中作为历史记录。

   ② 测试： 使用命令查询student表中的信息，再使用命令将student表中的1031班改为1032班，然后使用命令查询ss中的记录检查触发器t2是否正确。 

6）①创建触发器t6，当删除student表中的某个学生记录时，应该也同时删除score表中该生的选课记录。

②测试：在student表中添加一条学号为222的学生记录，在score表中添加该学号三条选课记录，然后删除学生表中的该学号的学生记录，检查score表中学号为222的记录是否还存在，从而测试该触发器是否正确。

   7）①创建触发器t7，当修改student表中的某个学生学号时，同时也修改score表中该生的学号。   

  ②测试：使用命令分别查询student表和score表中学号为的学生信息，再使用命令将student表中101的学号的改为999，然后使用命令再分别查询student表和score表中学号为101和的999的记录。

create procedure p1(@sno int,@sname varchar(200) out,@avg float out)
as
select sname,AVG(degree) as 'avg' from student,score  where student.sno=score.sno and score.sno=@sno group by sname
go
declare @Sname as varchar(200)
declare @Avg as float

exec p1 101,@Sname,@Avg output
---2
create procedure p2(@sno varchar(5),@cno varchar(5),@grade varchar(4) out)
as
declare @sgrade varchar(4)
select @sgrade=degree from score where sno=101 and cno='3-105'
if @sgrade>=90
set @grade = 'A'
if @sgrade>=80
set @grade = 'B'
if @sgrade>=70
set @grade = 'C'
if @sgrade>=60
set @grade = 'D'
else
set @grade = 'E'
go

declare @Grade varchar(4)
exec p2 '101','3-105',@Grade output
select @Grade
---3
create procedure p3(@sno varchar(5))
as
declare @select int
select @select = COUNT(*) from score where sno = @sno and degree is not null
if @select > 0
select sname,cname,degree from student,course,score where score.sno = @sno and student.sno = score.sno and course.cno = score.cno and degree is not null
else
print '该生无选课'
go

exec p3 101
--4
create procedure p4(@khno varchar(50))
as
select distinct COUNT(*) as '数量' from order_detail,order_list where order_list.客户号 = @khno and order_detail.订单号 = order_list.订单号
go
exec p4 c10001
--5
create procedure p5(@year varchar(50))
as
declare @num int
select @num = SUM(ZJE.总金额) from ZJE,order_list where ZJE.订单号 = order_list.订单号 and order_list.订购日期 like '[' + RTrim(@year) + ']%'
print (RTrim(@year) + '年的销售总额：')
print ('------------------')
print (@num)
go

exec p5 2001
--6
create procedure p6(@orderno varchar(200),@tno varchar(200),@price varchar(200))
as
update order_detail set 单价=@price where 订单号 = @orderno and 器件号 = @tno
go

exec p6 'OR-01C',P1001,1000
--7
create procedure p7
as
select 订单号 from order_detail where 器件名 = 'CPU P4 1.4G ' and 订单号 in (select 订单号 from order_detail where  器件名 = '内存' )
go

exec p7


--2.1
create trigger t1
on student
after insert,update,delete
as
begin
    select * from student
end

insert student(sno) values('1112')
delete from student where sno='1112'
--2.2
create trigger t2 
on student
instead of insert
as
begin
    declare @name char(10)
    select @name=inserted.sname from inserted
    if exists(select sname from student where sname=@name)
    begin
        raiserror('姓名重复，不能插入',16,1)
        rollback
    end
    else
    begin
        insert into student select inserted.* from inserted
    end
end

insert into student(sno, sname, ssex) values(001, 'aaaa', '男')
--2.3
create trigger t3 
on student 
after insert as
begin
    declare @sex char(5)
    select @sex=ssex from inserted
    if @sex <> '男' and @sex <> '女'
        raiserror('性别只能取男或女', 16, 1)
end
---
insert into student(sno, sname, ssex) values(110, '李俊', '他')
delete from student where sno='110'
--2.4
create trigger t4 
on student 
after update 
as
if UPDATE(sno)
begin
    raiserror('不能修改学号', 16, 2)
    rollback
end

update student set sno='102' where sno='101'
--2.5
create trigger t5 
on student 
after update 
as
insert into ss(editdate, sno, sname, ssex, sbirthday, sclass)
select GETDATE(), deleted.* from deleted

update student set sclass='95035' where sclass='95033'
--2.6
create trigger t6 
on student 
after delete 
as
declare @num char(5)
select @num=deleted.sno from deleted
delete from score where score.sno = @num

---
insert into student(sno, sname, ssex) values(222, '李俊', '男')
insert into score(sno, cno, degree) values(222, 'C001', '80')
insert into score(sno, cno, degree) values(222, 'C002', '90')
insert into score(sno, cno, degree) values(222, 'C003', '100')
delete from student where sno = 222
--2.7
create trigger t7 on student after update as
declare @num char(5)
select @num=deleted.sno from deleted
update score set score.sno = inserted.sno from inserted where score.sno = @num

update student set sno = 999 where sno = 101
select * from student where sno = 999
select * from score where sno = 999
select * from student where sno = 101
select * from score where sno = 101