oracle中怎么用&&,Oracle&&触发器练习（行级触发器、语句级触发器、包的混合使用）...

(测试都是截的图，贴图好像有点麻烦，就只有代码了)

创建表：

create table sc_number(sno

varchar2(10) primary key,cnt int,sum_grade int,avg_grade int);

insert into sc_number select

student.sno,count(cno),sum(grade),avg(grade) from sc,student where

student.sno=sc.sno(+) group by student.sno;

alter table sc_number add sum_credit

int;

更新数据：

update sc_number set

sum_credit=(select sum(credit) from course,sc where

course.cno=sc.cno and trim(sno)='001' and grade>=60)

where trim(sno)='001';

包：

create or replace packagePK_trig

is

old_sno varchar2(10);

new_sno varchar2(10);

old_grade varchar2(10);

new_grade varchar2(10);

end PK_trig;

触发器1(行级，用于记录：old和：new的取值)

create or replace trigger trig

after insert or delete or update on sc

for each row

declare

begin

if inserting then

PK_trig.new_sno:=:new.sno;--记录插入选课学生的学号

PK_trig.new_grade:=:new.grade;--记录插入选课学生的成绩

end if;

if deleting then

PK_trig.old_sno:=:old.sno;--记录删除选课学生的学号

PK_trig.old_grade:=:old.grade;--记录删除选课学生的成绩

end if;

if updating then

PK_trig.new_sno:=:new.sno;--记录更改选课学生的学号

PK_trig.old_sno:=:old.sno;--记录插入选课学生的学号

PK_trig.old_grade:=:old.grade;--记录删除选课学生的成绩

PK_trig.new_grade:=:new.grade;--记录更改选课学生的学号

end if;

end trig;

触发器2(语句级，用于查询并使用行级触发器记录下的数值)

create or replace trigger trig_yujuji

after insert or delete or update on sc

declare

Av_grade int:=0;--记录学生的平均成绩

count_cno int:=0;--记录学生的选课门数

SumGrade int:=0;--学生的总成绩

SumCredit int:=0;--总学分

begin

if inserting then

update sc_number set cnt=cnt+1 where sno=PK_TRIG.new_sno;--更新选课门数

select count(cno) into count_cno from sc where sno=PK_TRIG.new_sno;

select sum(grade) into SumGrade from sc where sno=PK_TRIG.new_sno;--计算总成绩

update sc_number set sum_grade=SumGrade where sno=PK_TRIG.new_sno;--更新总成绩

Av_grade:=SumGrade/count_cno; --计算平均成绩

update sc_number set avg_grade=Av_grade where sno=PK_TRIG.new_sno;--更新平均成

select sum(credit) into SumCredit from course,sc where course.cno=sc.cno and sno=PK_TRIG.new_sno

and grade>=60;

update sc_number set sum_credit=SumCredit where sno=PK_TRIG.new_sno;

dbms_output.put_line(PK_TRIG.new_sno||'的学生选课门数和总成绩和学分已经修改');

if sql%notfound then --sc_number表中不存在该学号时

insert into sc_number(sno,cnt,sum_grade,avg_grade，sum_credit) values (PK_TRIG.new_sno,count_cno,SumGrade,Av_grade,SumCredit);

dbms_output.put_line(PK_TRIG.new_sno||'的学生选课信息插入');

end if;

--将选了课但计算出来的学分为空的学生的学分置为0

update sc_number set sum_credit=0 wherecount_cno!=0 and sum_credit is null;

end if;

if deleting then

dbms_output.put_line(PK_trig.old_sno||'的学生选课记录已删除');

update sc_number set cnt=cnt-1 where sno=PK_trig.old_sno;

--计算删除后的总成绩

select sum_grade into SumGrade from sc_number where sno=PK_TRIG.old_sno;

SumGrade:=SumGrade-PK_trig.old_grade;

update sc_number set sum_grade=SumGrade

where sno=PK_trig.old_sno;--更新总成绩

select count(cno) into count_cno from sc where sno=PK_TRIG.new_sno;--记录更改学生的选课数

if count_cno !=0 then--选课不为0时

Av_grade:=SumGrade/count_cno;

--计算平均成绩

update sc_number set avg_grade=Av_grade where sno=PK_TRIG.new_sno;--更新平均成绩

end if;

select sum(credit) into SumCredit from course,sc where course.cno=sc.cno andsno=PK_TRIG.new_sno and grade>=60;

update sc_number set sum_credit=SumCredit where sno=PK_TRIG.new_sno;--更新总学分

if sql%notfound then --sc_number表中不存在该学号时

insert into sc_number(sno,cnt,sum_grade,avg_grade，sum_credit) values (PK_TRIG.new_sno,count_cno,SumGrade,Av_grade,SumCredit);

dbms_output.put_line(PK_TRIG.new_sno||'的学生选课信息插入');

end if;

update sc_number set sum_credit=0 wherecount_cno!=0 and sum_credit is null;

update sc_number set sum_grade=null where cnt=0;

update sc_number set avg_grade=null where cnt=0;

update sc_number set sum_credit=null where cnt=0;

end if;

--3、当修改SC表中数据时对应修改SC_NUMBER表中的选课门数和总成绩，打印“某某(学生姓名)的学生选课信息已经修改”信息。

if updating then

select count(cno) into count_cno from sc where sno=PK_TRIG.new_sno；

update sc_number set cnt=count_cno where sno=PK_TRIG.new_sno;

--更新选课数

select sum(grade) into SumGrade from sc where sno=PK_TRIG.new_sno;--计算总成绩

update sc_number set sum_grade=SumGrade

where sno=PK_trig.new_sno;--更新总成绩

if count_cno !=0 then--选课不为0时

Av_grade:=SumGrade/count_cno; --计算平均成绩

update sc_number set avg_grade=Av_grade where sno=PK_TRIG.new_sno;--更新平均成绩

end if;

select sum(credit) into SumCredit from course,sc where course.cno=sc.cno and sno=PK_TRIG.new_sno

and grade>=60;

update sc_number set sum_credit=SumCredit where sno=PK_TRIG.new_sno;--更新总学分

dbms_output.put_line(PK_TRIG.new_sno||'的学生选课门数和总成绩、平均成绩、学分已经修改');

if sql%notfound then

insert into sc_number(sno,cnt,sum_grade,avg_grade，sum_credit) values (PK_TRIG.new_sno,count_cno,SumGrade,Av_grade,SumCredit);

dbms_output.put_line(PK_TRIG.new_sno||'的学生选课信息插入');

end if;

select count(cno) into count_cno from sc where sno=PK_TRIG.old_sno;

update sc_number set cnt=count_cno where sno=PK_TRIG.old_sno;

--更新选课数

select sum(grade) into SumGrade from sc where sno=PK_TRIG.old_sno;--计算总成绩

update sc_number set sum_grade=SumGrade

where sno=PK_trig.old_sno;--更新总成绩

if count_cno !=0 then--选课不为0时

Av_grade:=SumGrade/count_cno; --计算平均成绩

update sc_number set avg_grade=Av_grade where sno=PK_TRIG.old_sno;--更新平均成绩

end if;

select sum(credit) into SumCredit from course,sc where course.cno=sc.cno and sno=PK_TRIG.old_sno

and grade>=60;

update sc_number set sum_credit=SumCredit where sno=PK_TRIG.old_sno;--更新总学分

dbms_output.put_line(PK_TRIG.old_sno||'的学生选课门数和总成绩、平均成绩、学分已经修改');

if sql%notfound then

insert into sc_number(sno,cnt,sum_grade,avg_grade，sum_credit) values (PK_TRIG.old_sno,count_cno,SumGrade,Av_grade,SumCredit);

dbms_output.put_line(PK_TRIG.new_sno||'的学生选课信息插入');

end if;

if Sumcredit is null then

update sc_number set sum_credit=null wherecount_cno!=0;

end if;

--讲没有选课学生的总成绩、平均成绩、总学分都置为空

update sc_number set sum_grade=null where cnt=0;

update sc_number set avg_grade=null where cnt=0;

update sc_number set sum_credit=null where cnt=0;

end if;

end trig_yujuji;