实验二、编写ORALCE触发器与过程

实验二、编写ORALCE触发器与过程
一、在stud_417表和class_417表中加入一列sum_evaluation
alter table stud_417 add (sum_evaluation number default 100);
alter table class_417 add(sum_evaluation number default 100);
commit;
二、建立上课考勤总表Attend_417


create table Attend_417
(
riqi date,
unit char,
sno varchar2(10) references stud_417(sno),
cno varchar2(10) references class_417(cno),
condition varchar2(10),
primary key(riqi,unit,sno)
);
alter table attend_417 add constraint ck_attend_con check2016/5/7
(contidion in ('正常','迟到','请假','旷课'));
alter table attend_417 add constraint ck_attend_unit check
(unit in ('1','2','3','4','5'));


三、建立个人考勤汇总表stud_attend和班级考勤汇总表class_attend。
1.个人考勤汇总表stud_attend
create table stud_attend 
(
sno varchar2(10) primary key,
beginDate date,
endDate date,
zhengChangCiShu number,
chiDaoCiShu number,
kuangKeCiShu number,
qingJiaCiShu number,
koufen number
);
2.班级考勤汇总表class_attend
create table class_attend
(
cno varchar2(10) primary key,
beginDate date,
endDate date,
zhengChangCiShu number,
chiDaoCiShu number,
kuangKeCiShu number,
qingJiaCiShu number,
koufen number
);


/*修改主键 为cno,beginDate,endDate*/
三、建立触发器，当对考勤表Attend表进行相应插入、删除、修改时，对stud表的sum_evaluation 数值进行相应的数据更新。
create or replace trigger attend_stud 
before insert or update or delete on Attend_417
for each row 
begin 
if inserting then
if :new.condition = '迟到' then
update stud_417 set sum_evaluation=sum_evaluation-2 where :new.sno = stud_417.sno;
elsif :new.condition = '旷课' then
update stud_417 set sum_evaluation=sum_evaluation-5 where :new.sno = stud_417.sno;
elsif :new.condition = '请假' then
update stud_417 set sum_evaluation=sum_evaluation-1 where :new.sno = stud_417.sno;
end if;
end if;
if deleting then
if :old.condition = '迟到' then
update stud_417 set sum_evaluation=sum_evaluation+2 where :old.sno = stud_417.sno;
elsif :old.condition = '旷课' then
update stud_417 set sum_evaluation=sum_evaluation+5 where :old.sno = stud_417.sno;
elsif :old.condition = '请假' then
update stud_417 set sum_evaluation=sum_evaluation+1 where :old.sno = stud_417.sno;
end if;
end if;
if updating then
if :old.condition = '迟到' then
update stud_417 set sum_evaluation=sum_evaluation+2 where :old.sno = stud_417.sno;
elsif :old.condition = '旷课' then
update stud_417 set sum_evaluation=sum_evaluation+5 where :old.sno = stud_417.sno;
elsif :old.condition = '请假' then
update stud_417 set sum_evaluation=sum_evaluation+1 where :old.sno = stud_417.sno;
end if;

if :new.condition = '迟到' then
update stud_417 set sum_evaluation=sum_evaluation-2 where :new.sno = stud_417.sno;
elsif :new.condition = '旷课' then
update stud_417 set sum_evaluation=sum_evaluation-5 where :new.sno = stud_417.sno;
elsif :new.condition = '请假' then
update stud_417 set sum_evaluation=sum_evaluation-1 where :new.sno = stud_417.sno;
end if;
end if;
end; 
四、输入数据


1.
insert into attend_417 (riqi,unit,sno,cno,condition) values(sysdate-365,'1','0902140101','1401','迟到');
insert into attend_417 (riqi,unit,sno,cno,condition) values(sysdate-365,'1','0902140102','1401','正常');
insert into attend_417 (riqi,unit,sno,cno,condition) values(sysdate-365,'1','0902140103','1401','正常');
insert into attend_417 (riqi,unit,sno,cno,condition) values(sysdate-365,'1','0902140104','1401','正常');
insert into attend_417 (riqi,unit,sno,cno,condition) values(sysdate-365,'1','0902140105','1401','正常');
insert into attend_417 (riqi,unit,sno,cno,condition) values(sysdate-365,'1','0902140106','1401','迟到');
insert into attend_417 (riqi,unit,sno,cno,condition) values(sysdate-365,'1','0902140107','1401','正常');
insert into attend_417 (riqi,unit,sno,cno,condition) values(sysdate-365,'1','0902140108','1401','请假');


insert into attend_417 (riqi,unit,sno,cno,condition) values(sysdate-365,'2','0902140101','1401','旷课');
insert into attend_417 (riqi,unit,sno,cno,condition) values(sysdate-365,'2','0902140101','1401','正常');
insert into attend_417 (riqi,unit,sno,cno,condition) values(sysdate-364,'3','0902140101','1401','正常');
insert into attend_417 (riqi,unit,sno,cno,condition) values(sysdate-363,'4','0902140101','1401','正常');
insert into attend_417 (riqi,unit,sno,cno,condition) values(sysdate-362,'1','0902140101','1401','请假');
insert into attend_417 (riqi,unit,sno,cno,condition) values(sysdate-361,'3','0902140101','1401','正常');
insert into attend_417 (riqi,unit,sno,cno,condition) values(sysdate-360,'1','0902140101','1401','正常');
insert into attend_417 (riqi,unit,sno,cno,condition) values(sysdate-360,'5','0902140101','1401','正常');


insert into attend_417 (riqi,unit,sno,cno,condition) values(sysdate-360,'5','0902140103','1402','正常');
insert into attend_417 (riqi,unit,sno,cno,condition) values(sysdate-359,'5','0902140103','1402','迟到');
insert into attend_417 (riqi,unit,sno,cno,condition) values(sysdate-358,'5','0902140103','1402','正常');
insert into attend_417 (riqi,unit,sno,cno,condition) values(sysdate-357,'5','0902140103','1402','正常');
insert into attend_417 (riqi,unit,sno,cno,condition) values(sysdate-356,'5','0902140103','1402','请假');
insert into attend_417 (riqi,unit,sno,cno,condition) values(sysdate-355,'5','0902140103','1402','正常');
insert into attend_417 (riqi,unit,sno,cno,condition) values(sysdate-350,'5','0902140103','1402','正常');


insert into attend_417 (riqi,unit,sno,cno,condition) values(sysdate-360,'5','0902140104','1402','正常');
insert into attend_417 (riqi,unit,sno,cno,condition) values(sysdate-360,'5','0902140105','1402','正常');
insert into attend_417 (riqi,unit,sno,cno,condition) values(sysdate-360,'5','0902140106','1402','正常');


五、．建立过程，生成某班级某时段（起、止日期）的考勤汇总表class_attend中各字段值，
并汇总相应班级，将考勤分值的汇总结果写入到class表中的sum_evaluation中。
create or replace procedure class_kaoqin
(beginriqi in date,endriqi in date,class in varchar2 )
is 
 
v_zhengChangCiShu number;
v_chiDaoCiShu number;
v_kuangKeCiShu number;
v_qingJiaCiShu number;
v_koufen number;
begin 
select count(*) into v_zhengChangCiShu
from attend_417
where       beginriqi < riqi
and endriqi > riqi
and class = cno
and condition = '正常';
insert into class_attend (cno,beginDate,endDate,zhengChangCiShu) values (class,beginriqi,endriqi,v_zhengChangCiShu);
end class_kaoqin;


execute class_kaoqin(sysdate-370,sysdate,'1401');


//测试正常


create or replace procedure class_kaoqin
(beginriqi in date,endriqi in date,class in varchar2 )
is 
 
v_zhengChangCiShu number;
v_chiDaoCiShu number;
v_kuangKeCiShu number;
v_qingJiaCiShu number;
v_koufen number default 0;
begin 
/*插入正常次数*/
select count(*) into v_zhengChangCiShu
from attend_417
where       beginriqi < riqi
and endriqi > riqi
and class = cno
and condition = '正常';
/*插入迟到次数*/
select count(*) into v_chiDaoCiShu
from attend_417
where       beginriqi < riqi
and endriqi > riqi
and class = cno
and condition = '迟到';


/*插入请假次数*/
select count(*) into v_qingJiaCiShu
from attend_417
where       beginriqi < riqi
and endriqi > riqi
and class = cno
and condition = '请假';
/*插入旷课次数*/
select count(*) into v_kuangKeCiShu
from attend_417
where       beginriqi < riqi
and endriqi > riqi
and class = cno
and condition = '旷课';


/*更新扣分*/
v_koufen := v_chiDaoCiShu*2 + v_qingJiaCiShu + v_kuangKeCiShu*5;

insert into class_attend (cno,beginDate,endDate,zhengChangCiShu,chidaoCiShu,qingJiaCiShu,kuangKeCiShu,koufen) 
values (class,beginriqi,endriqi,v_zhengChangCiShu,v_chiDaoCiShu,v_qingJiaCiShu,v_kuangKeCiShu,v_koufen);

/*修改对应的班级表的sum_evaluation*/
update class_417 set  sum_evaluation = sum_evaluation - v_koufen where cno = class;
end class_kaoqin;


//测试正常
execute class_kaoqin(sysdate-370,sysdate,'1401');
execute class_kaoqin(sysdate-700,sysdate,'1402');