实验要求

1．设计与建立上课考勤表Attend_???，能登记每个学生的考勤记录包括正常、迟到、旷课、请假。能统计以专业为单位的出勤类别并进行打分评价排序，如迟到、旷课、请假分别扣2，5，1分。可以考虑给一初始的分值，以免负值。

2．为major表与stud表增加sum_evaluation 数值字段，以记录根据考勤表Attend_???(Attendance)中出勤类别打分汇总的值。

3．建立个人考勤汇总表stud_attend与专业考勤表major_attend，表示每个学生或每个专业在某时间周期（起始日期，终止日期）正常、迟到、旷课、请假次数及考勤分值。

4．根据major表中的值与stud中的值，为考勤表Attend输入足够的样本值，要求每个专业都要有学生，有部分学生至少要有一周的每天5个单元（12，34，56，78，90，没有课的单元可以没有考勤记录）的考勤完整记录，其中正常、迟到、旷课、请假可以用数字或字母符号表示。

5．建立触发器，当对考勤表Attend表进行相应插入、删除、修改时，对stud表的sum_evaluation 数值进行相应的数据更新。

6．建立过程，生成某专业某时段（起、止日期）的考勤汇总表major_attend中各字段值，并汇总相应专业，将考勤分值的汇总结果写入到major表中的sum_evaluation中。

create table T_attendscore_J122(    mstatus nchar(2) primary key,    score int);insert into T_attendscore_J122 values('正常',0);insert into T_attendscore_J122 values('请假',1);insert into T_attendscore_J122 values('迟到',2);insert into T_attendscore_J122 values('旷课',5);drop table T_attend_J122;create table T_attend_J122(    sno char(10),    mno char(2),    sday date,    unit char(2) check(unit in('12','34','56','78','90')),    mstatus nchar(2) check(mstatus in('正常','请假','迟到','旷课')),    constraint pk_T_attend primary key(sno,sday,unit),    constraint fk_T_attend_sno foreign key(sno) references T_stud_J122(sno),    constraint fk_T_attend_mno foreign key(mno) references T_major_J122(mno));alter table T_stud_J122 add(sum_evaluation number);alter table T_major_J122 add(sum_evaluation number);insert into T_attend_J122 values('0902160122','02','11-11月-2018','12','正常');insert into T_attend_J122 values('0902160122','02','11-11月-2018','34','迟到');insert into T_attend_J122 values('0902160122','02','12-11月-2018','12','正常');insert into T_attend_J122 values('0902160122','02','12-11月-2018','78','正常');insert into T_attend_J122 values('0902160122','02','12-11月-2018','90','正常');insert into T_attend_J122 values('0902160122','02','13-11月-2018','12','旷课');insert into T_attend_J122 values('0902160122','02','13-11月-2018','34','请假');insert into T_attend_J122 values('0902160122','02','13-11月-2018','90','旷课');insert into T_attend_J122 values('0902160122','02','14-11月-2018','34','正常');insert into T_attend_J122 values('0902160122','02','14-11月-2018','56','正常');insert into T_attend_J122 values('0902160122','02','14-11月-2018','78','正常');insert into T_attend_J122 values('0902160122','02','15-11月-2018','12','请假');insert into T_attend_J122 values('0902160122','02','15-11月-2018','34','请假');insert into T_attend_J122 values('0902160122','02','15-11月-2018','78','正常');insert into T_attend_J122 values('0902160121','02','11-11月-2018','12','正常');insert into T_attend_J122 values('0902160121','02','11-11月-2018','34','迟到');insert into T_attend_J122 values('0902160121','02','12-11月-2018','12','迟到');insert into T_attend_J122 values('0902160121','02','12-11月-2018','78','正常');insert into T_attend_J122 values('0902160121','02','12-11月-2018','90','旷课');insert into T_attend_J122 values('0902160121','02','13-11月-2018','12','旷课');insert into T_attend_J122 values('0902160121','02','13-11月-2018','34','请假');insert into T_attend_J122 values('0902160121','02','13-11月-2018','90','旷课');insert into T_attend_J122 values('0902160121','02','14-11月-2018','34','正常');insert into T_attend_J122 values('0902160121','02','14-11月-2018','56','正常');insert into T_attend_J122 values('0902160121','02','14-11月-2018','78','正常');insert into T_attend_J122 values('0902160121','02','15-11月-2018','12','请假');insert into T_attend_J122 values('0902160121','02','15-11月-2018','34','请假');insert into T_attend_J122 values('0902160121','02','15-11月-2018','78','正常');insert into T_attend_J122 values('0902160120','02','11-11月-2018','12','正常');insert into T_attend_J122 values('0902160120','02','11-11月-2018','34','迟到');insert into T_attend_J122 values('0902160120','02','12-11月-2018','12','迟到');insert into T_attend_J122 values('0902160120','02','12-11月-2018','78','正常');insert into T_attend_J122 values('0902160120','02','12-11月-2018','90','正常');insert into T_attend_J122 values('0902160120','02','13-11月-2018','12','旷课');insert into T_attend_J122 values('0902160120','02','13-11月-2018','34','旷课');insert into T_attend_J122 values('0902160120','02','13-11月-2018','90','旷课');insert into T_attend_J122 values('0902160120','02','14-11月-2018','34','正常');insert into T_attend_J122 values('0902160120','02','14-11月-2018','56','正常');insert into T_attend_J122 values('0902160120','02','14-11月-2018','78','正常');insert into T_attend_J122 values('0902160120','02','15-11月-2018','12','请假');insert into T_attend_J122 values('0902160120','02','15-11月-2018','34','请假');insert into T_attend_J122 values('0902160120','02','15-11月-2018','78','正常');insert into T_attend_J122 values('0919160122','19','11-11月-2018','12','正常');insert into T_attend_J122 values('0919160122','19','11-11月-2018','34','迟到');insert into T_attend_J122 values('0919160122','19','12-11月-2018','12','迟到');insert into T_attend_J122 values('0919160122','19','12-11月-2018','78','正常');insert into T_attend_J122 values('0919160122','19','12-11月-2018','90','正常');insert into T_attend_J122 values('0919160122','19','13-11月-2018','12','旷课');insert into T_attend_J122 values('0919160122','19','13-11月-2018','34','请假');insert into T_attend_J122 values('0919160122','19','13-11月-2018','90','旷课');insert into T_attend_J122 values('0919160122','19','14-11月-2018','34','正常');insert into T_attend_J122 values('0919160122','19','14-11月-2018','56','正常');insert into T_attend_J122 values('0919160122','19','14-11月-2018','78','正常');insert into T_attend_J122 values('0919160122','19','15-11月-2018','12','请假');insert into T_attend_J122 values('0919160122','19','15-11月-2018','34','旷课');insert into T_attend_J122 values('0919160122','19','15-11月-2018','78','正常');insert into T_attend_J122 values('0919160121','19','11-11月-2018','12','正常');insert into T_attend_J122 values('0919160121','19','11-11月-2018','34','正常');insert into T_attend_J122 values('0919160121','19','12-11月-2018','12','迟到');insert into T_attend_J122 values('0919160121','19','12-11月-2018','78','正常');insert into T_attend_J122 values('0919160121','19','12-11月-2018','90','旷课');insert into T_attend_J122 values('0919160121','19','13-11月-2018','12','旷课');insert into T_attend_J122 values('0919160121','19','13-11月-2018','34','请假');insert into T_attend_J122 values('0919160121','19','13-11月-2018','90','旷课');insert into T_attend_J122 values('0919160121','19','14-11月-2018','34','正常');insert into T_attend_J122 values('0919160121','19','14-11月-2018','56','正常');insert into T_attend_J122 values('0919160121','19','14-11月-2018','78','正常');insert into T_attend_J122 values('0919160121','19','15-11月-2018','12','请假');insert into T_attend_J122 values('0919160121','19','15-11月-2018','34','旷课');insert into T_attend_J122 values('0919160121','19','15-11月-2018','78','正常');insert into T_attend_J122 values('0919160120','19','11-11月-2018','12','正常');insert into T_attend_J122 values('0919160120','19','11-11月-2018','34','迟到');insert into T_attend_J122 values('0919160120','19','12-11月-2018','12','迟到');insert into T_attend_J122 values('0919160120','19','12-11月-2018','78','正常');insert into T_attend_J122 values('0919160120','19','12-11月-2018','90','请假');insert into T_attend_J122 values('0919160120','19','13-11月-2018','12','迟到');insert into T_attend_J122 values('0919160120','19','13-11月-2018','34','请假');insert into T_attend_J122 values('0919160120','19','13-11月-2018','90','正常');insert into T_attend_J122 values('0919160120','19','14-11月-2018','34','正常');insert into T_attend_J122 values('0919160120','19','14-11月-2018','56','正常');insert into T_attend_J122 values('0919160120','19','14-11月-2018','78','正常');insert into T_attend_J122 values('0919160120','19','15-11月-2018','12','请假');insert into T_attend_J122 values('0919160120','19','15-11月-2018','34','旷课');insert into T_attend_J122 values('0919160120','19','15-11月-2018','78','正常');insert into T_attend_J122 values('0921160122','21','11-11月-2018','12','请假');insert into T_attend_J122 values('0921160122','21','11-11月-2018','34','迟到');insert into T_attend_J122 values('0921160122','21','12-11月-2018','12','正常');insert into T_attend_J122 values('0921160122','21','12-11月-2018','78','请假');insert into T_attend_J122 values('0921160122','21','12-11月-2018','90','迟到');insert into T_attend_J122 values('0921160122','21','13-11月-2018','12','迟到');insert into T_attend_J122 values('0921160122','21','13-11月-2018','34','请假');insert into T_attend_J122 values('0921160122','21','13-11月-2018','90','正常');insert into T_attend_J122 values('0921160122','21','14-11月-2018','34','旷课');insert into T_attend_J122 values('0921160122','21','14-11月-2018','56','正常');insert into T_attend_J122 values('0921160122','21','14-11月-2018','78','正常');insert into T_attend_J122 values('0921160122','21','15-11月-2018','12','迟到');insert into T_attend_J122 values('0921160122','21','15-11月-2018','34','旷课');insert into T_attend_J122 values('0921160122','21','15-11月-2018','78','正常');insert into T_attend_J122 values('0921160121','21','11-11月-2018','12','正常');insert into T_attend_J122 values('0921160121','21','11-11月-2018','34','迟到');insert into T_attend_J122 values('0921160121','21','12-11月-2018','12','正常');insert into T_attend_J122 values('0921160121','21','12-11月-2018','78','请假');insert into T_attend_J122 values('0921160121','21','12-11月-2018','90','迟到');insert into T_attend_J122 values('0921160121','21','13-11月-2018','12','请假');insert into T_attend_J122 values('0921160121','21','13-11月-2018','34','请假');insert into T_attend_J122 values('0921160121','21','13-11月-2018','90','正常');insert into T_attend_J122 values('0921160121','21','14-11月-2018','34','正常');insert into T_attend_J122 values('0921160121','21','14-11月-2018','56','正常');insert into T_attend_J122 values('0921160121','21','14-11月-2018','78','正常');insert into T_attend_J122 values('0921160121','21','15-11月-2018','12','迟到');insert into T_attend_J122 values('0921160121','21','15-11月-2018','34','旷课');insert into T_attend_J122 values('0921160121','21','15-11月-2018','78','正常');insert into T_attend_J122 values('0921160120','21','11-11月-2018','12','正常');insert into T_attend_J122 values('0921160120','21','11-11月-2018','34','迟到');insert into T_attend_J122 values('0921160120','21','12-11月-2018','12','正常');insert into T_attend_J122 values('0921160120','21','12-11月-2018','78','请假');insert into T_attend_J122 values('0921160120','21','12-11月-2018','90','迟到');insert into T_attend_J122 values('0921160120','21','13-11月-2018','12','迟到');insert into T_attend_J122 values('0921160120','21','13-11月-2018','34','请假');insert into T_attend_J122 values('0921160120','21','13-11月-2018','90','正常');insert into T_attend_J122 values('0921160120','21','14-11月-2018','34','正常');insert into T_attend_J122 values('0921160120','21','14-11月-2018','56','正常');insert into T_attend_J122 values('0921160120','21','14-11月-2018','78','正常');insert into T_attend_J122 values('0921160120','21','15-11月-2018','12','请假');insert into T_attend_J122 values('0921160120','21','15-11月-2018','34','旷课');insert into T_attend_J122 values('0921160120','21','15-11月-2018','78','正常');select T_attend_J122.mstatus,mno,sum(score)from T_attend_J122,T_attendscore_J122where T_attend_J122.mstatus=T_attendscore_J122.mstatus and T_attend_J122.mstatus<>'正常'group by mno,T_attend_J122.mstatusorder by sum(score);create table T_stud_attend_J122(    sno char(10),    stime date,    etime date,    normalcnt int,    leavecnt int,    latecnt int,    absentcnt int,    score number,    constraint pk_T_stud_attend_time primary key(sno,stime,etime),    constraint fk_T_stud_attend_sno foreign key(sno) references T_stud_J122(sno));create table T_major_attend_J122(    mno char(2),    stime date,    etime date,    normalcnt int,    leavecnt int,    latecnt int,    absentcnt int,    score number,    constraint pk_T_major_attend_time primary key(mno,stime,etime),    constraint fk_T_major_attend_J122 foreign key(mno) references T_major_J122(mno));drop trigger tg_T_attend_J122;create trigger tg_T_attend_J122    before insert or update or delete on T_attend_J122    for each rowdeclare    mnewscore binary_integer;    moldscore binary_integer;begin     if inserting then        select score into mnewscore from T_attendscore_J122 where mstatus=:new.mstatus;        update T_stud_J122 set sum_evaluation=nvl(sum_evaluation,0)+mnewscore where T_stud_J122.sno=:new.sno;    elsif updating then        select score into mnewscore from T_attendscore_J122 where mstatus=:new.mstatus;        select score into moldscore from T_attendscore_J122 where mstatus=:old.mstatus;        update T_stud_J122 set sum_evaluation=nvl(sum_evaluation,0)-moldscore where T_stud_J122.sno=:old.sno;        update T_stud_J122 set sum_evaluation=nvl(sum_evaluation,0)+mnewscore where T_stud_J122.sno=:new.sno;    else         select score into moldscore from T_attendscore_J122 where mstatus=:old.mstatus;        update T_stud_J122 set sum_evaluation=nvl(sum_evaluation,0)-moldscore where T_stud_J122.sno=:old.sno;    end if;end;select sno,sum_evaluation from T_stud_J122 where sno='0902160122';insert into T_attend_J122 values('0902160122','02','16-11月-2018','56','请假');select sno,sum_evaluation from T_stud_J122 where sno='0902160122';drop procedure P_attend_J122;create procedure P_attend_J122 (pmno char,pstime date,petime date)as    pscore int:=0;begin    insert into T_major_attend_J122 values(pmno,pstime,petime,0,0,0,0,0);    for cur in(        select T_attend_J122.mstatus,count(T_attend_J122.mstatus)mstatus_cnt,sum(score)pscore        from T_attend_J122,T_attendscore_J122        where T_attend_J122.mstatus=T_attendscore_J122.mstatus and T_attend_J122.mno=pmno        and sday>=pstime and sday<=petime        group by T_attend_J122.mstatus)loop        if cur.mstatus='正常' then        update T_major_attend_J122 set normalcnt=cur.mstatus_cnt where mno=pmno and stime=pstime and etime=petime;        elsif cur.mstatus='请假' then        update T_major_attend_J122 set leavecnt=cur.mstatus_cnt where mno=pmno and stime=pstime and etime=petime;        elsif cur.mstatus='迟到' then        update T_major_attend_J122 set latecnt=cur.mstatus_cnt where mno=pmno and stime=pstime and etime=petime;        elsif cur.mstatus='旷课' then        update T_major_attend_J122 set absentcnt=cur.mstatus_cnt where mno=pmno and stime=pstime and etime=petime;        end if;        pscore:=pscore+cur.pscore;    end loop;    update T_major_attend_J122 set score=pscore where mno=pmno and stime=pstime and etime=petime;    update T_major_J122 set sum_evaluation=pscore where mno=pmno;end;./set linesize 200;select * from T_major_attend_J122;select * from T_major_J122;exec P_attend_J122('02','11-11月-2018','15-11月-2018');exec P_attend_J122('19','11-11月-2018','15-11月-2018');exec P_attend_J122('21','11-11月-2018','15-11月-2018');select * from T_major_attend_J122;select * from T_major_J122;