5.20

今日学习的内容主要是有关数据库操作中的触发器和储存过程。

触发器（trigger）就相当于事件绑定，当你进行某类sql语句操作时将会自动调用你你所设置的触发器来进行操作。

储存过程（procedure）就相当于我们Java中的方法，可以带有参数和返回值，可以在储存过程中设置多条语句，调用储存过程就相当于自动调用这些语句。调用时可以直接使用exec+储存器名称+参数。

--建立 course 与 sc 表间来维护参照完整性而使用的一个级联删除触发器、一个级联修改触发器和一个受限插入触发器。

create trigger trg_CascadeDelete on Course --级联删除触发器
for delete
as
begin
    delete from sc where Cno in(select Cno from  deleted)
end;
go

create trigger trg_CascadeUpdate on Course --级联修改触发器
for update
as
begin 
    if update(Cno)
    begin
        declare @OldCno char(10),@NewCno char(10);
        select @OldCno=Cno from deleted;
        select @NewCno=Cno from inserted;
        update SC set Cno=@NewCno where Cno=@OldCno;
    end
end;
go

create trigger trg_RestrictInsert ON SC --受限插入触发器
for insert 
as 
begin
    if exists(select * from inserted where Cno not in(Select Cno from Course))
    begin
        raiserror('该课程号不存在',16,1);
        rollback transaction;
    end
end;
go

--统计成绩 60 分以下的人数
create procedure UnderSixty
as
begin
    select count(*) 低于60分的人数 from sc where Grade<60;
end;
exec UnderSixty;
go

--统计给定 cno 的课程的平均成绩，并返回平均成绩
create procedure CourseAvgGrade(@Cno char(10),@Avg float output)
as
begin
    select @Avg=avg(Grade) from sc where Cno=@Cno;
end;
declare @avggrade float
exec CourseAvgGrade '1',@avggrade
select @avggrade as 平均分;
go

--将 sc 表中 grade 从百分制改为等级制（5、4、3、2、1）。即 0-20 分为 1，21-40为 2，4 1-60 为 3，61-80 为 4,81-100 为 5。
create procedure Grades
as
begin
    update sc
    set Grade=
    case
        when Grade between 81 and 100 then 5
        when Grade between 61 and 80 then 4
        when Grade between 41 and 60 then 3
        when Grade between 21 and 40 then 2
        when Grade between 0 and 20 then 1
        else Grade
    end
end;
exec Grades;