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

一、目的与要求

本实验主要是熟悉ORACLE的后台编程，包括触发器与过程的编制，可比较基于SQL Server的触发器与过程。

二、操作环境

同实验一

三、实验内容

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中。

 

四、实验过程

一、实验内容

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

2．为t_major_j524表与t_stud_524表增加sum_evaluation 数值字段，以记录根据考勤表t_attend_j524(Attendance)中出勤类别打分汇总的值（这两张表参考实验一）。

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

 

conn u_j524/p123456
--1.为学生表和专业表各添加一列：考勤分数
desc t_stud_j524;
desc t_major_j524;
--2．为t_major_j524表与t_stud_524表增加sum_evaluation 数值字段
alter table t_stud_j524 add sum_evaluation number(3) default 100;
alter table t_major_j524 add sum_evaluation number(3) default 500;
desc t_stud_j524;
desc t_major_j524;

--3.建立考勤表
--正常：A
--迟到：B
--旷课：C
--请假：D
--没课：E

--学号(sno)为外键 
drop table t_attend_j524;
create table t_attend_j524(
    nowdate date not null,
    sno varchar(10) not null,
    name varchar(16) not null,
    class12 varchar(4) not null,
    class34 varchar(4) not null,
    class56 varchar(4) not null,
    class78 varchar(4) not null,
    class90 varchar(4) not null,
    constraint pk_attend_nowdate_sno primary key(nowdate,sno),
    constraint ck_attend_class12 check(class12 in('A','B','C','D','E')),
    constraint ck_attend_class34 check(class34 in('A','B','C','D','E')),
    constraint ck_attend_class56 check(class56 in('A','B','C','D','E')),
    constraint ck_attend_class78 check(class78 in('A','B','C','D','E')),
    constraint ck_attend_class90 check(class90 in('A','B','C','D','E')),
    constraint ck_attend_sno foreign key(sno) references t_stud_j524(sno));

 

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

 

--2.插入一周数据：2019.10.13至2019.10.19

--数据科学与大数据技术13名同学
insert into t_attend_j524 values(to_date('20191013','yyyymmdd'),'0921170114','李一','A','B','C','B','C');
insert into t_attend_j524 values(to_date('20191013','yyyymmdd'),'0921170115','李二','A','E','C','E','E');
insert into t_attend_j524 values(to_date('20191013','yyyymmdd'),'0921170117','李三','A','B','C','A','E');
insert into t_attend_j524 values(to_date('20191013','yyyymmdd'),'0921170120','李四','A','B','E','A','D');
insert into t_attend_j524 values(to_date('20191013','yyyymmdd'),'0921170122','李五','E','A','E','E','D');
insert into t_attend_j524 values(to_date('20191013','yyyymmdd'),'0921170123','李六','A','C','B','E','E');
insert into t_attend_j524 values(to_date('20191013','yyyymmdd'),'0921170124','李七','A','E','C','E','E');
insert into t_attend_j524 values(to_date('20191013','yyyymmdd'),'0921170207','张一','E','A','E','E','D');
insert into t_attend_j524 values(to_date('20191013','yyyymmdd'),'0921170225','张二','B','A','B','A','D');
insert into t_attend_j524 values(to_date('20191013','yyyymmdd'),'0921170226','张三','E','A','A','E','A');
insert into t_attend_j524 values(to_date('20191013','yyyymmdd'),'0921170227','张四','C','A','E','A','D');
insert into t_attend_j524 values(to_date('20191013','yyyymmdd'),'0921170209','张五','E','A','E','E','D');
insert into t_attend_j524 values(to_date('20191013','yyyymmdd'),'0921170228','张六','A','B','E','A','D');

--计算机科学与技术12名同学
insert into t_attend_j524 values(to_date('20191013','yyyymmdd'),'0902170516','王二','B','A','B','A','D');
insert into t_attend_j524 values(to_date('20191013','yyyymmdd'),'0902170520','王三','A','B','C','D','E');
insert into t_attend_j524 values(to_date('20191013','yyyymmdd'),'0902170521','王四','B','A','A','A','B');
insert into t_attend_j524 values(to_date('20191013','yyyymmdd'),'0902170523','杜豪','A','A','A','A','A');
insert into t_attend_j524 values(to_date('20191013','yyyymmdd'),'0902170524','宋海磊','A','A','A','A','A');
insert into t_attend_j524 values(to_date('20191013','yyyymmdd'),'0902170525','翟树杰','A','A','A','A','A');
insert into t_attend_j524 values(to_date('20191013','yyyymmdd'),'0902170526','胡旭东','A','A','A','A','A');
insert into t_attend_j524 values(to_date('20191013','yyyymmdd'),'0902170601','嬴政','B','A','A','A','B');
insert into t_attend_j524 values(to_date('20191013','yyyymmdd'),'0902170602','刘邦','A','B','A','D','A');
insert into t_attend_j524 values(to_date('20191013','yyyymmdd'),'0902170604','李广','C','A','B','A','D');
insert into t_attend_j524 values(to_date('20191013','yyyymmdd'),'0902170605','李世民','A','A','B','A','D');
insert into t_attend_j524 values(to_date('20191013','yyyymmdd'),'0902170625','赵匡胤','D','A','B','A','C');

--物联网工程11名同学
insert into t_attend_j524 values(to_date('20191013','yyyymmdd'),'0919170112','周一','B','A','A','A','B');
insert into t_attend_j524 values(to_date('20191013','yyyymmdd'),'0919170120','周二','B','A','C','A','B');
insert into t_attend_j524 values(to_date('20191013','yyyymmdd'),'0919170107','周三','A','B','C','B','C');
insert into t_attend_j524 values(to_date('20191013','yyyymmdd'),'0919170109','周四','A','B','E','A','D');
insert into t_attend_j524 values(to_date('20191013','yyyymmdd'),'0919170110','周五','A','B','E','A','D');
insert into t_attend_j524 values(to_date('20191013','yyyymmdd'),'0919170115','周六','A','C','C','A','C');
insert into t_attend_j524 values(to_date('20191013','yyyymmdd'),'0919170214','陈一','A','B','C','A','D');
insert into t_attend_j524 values(to_date('20191013','yyyymmdd'),'0919170215','陈二','B','A','A','A','B');
insert into t_attend_j524 values(to_date('20191013','yyyymmdd'),'0919170217','陈三','A','B','E','A','D');
insert into t_attend_j524 values(to_date('20191013','yyyymmdd'),'0919170220','陈四','A','B','A','A','C');
insert into t_attend_j524 values(to_date('20191013','yyyymmdd'),'0919170225','陈五','A','B','C','A','D');
--3.显示考勤表
set linesize 150;
set pagesize 100;
col name format a7;
col sno format a11;
col class12 format a7;
col class34 format a7;
col class65 format a7;
col class78 format a7;
col class90 format a7;
alter session set nls_date_format='yyyy-mm-dd';
select * from t_attend_j524;

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

 

--15.建立触发器：当对考勤表进行相应插入、删除、修改时，对stud表的sum_evaluation数值进行相应的数据更新
create or replace trigger tg_attend_j524
after insert or update or delete on t_attend_j524

begin
p_stud_atte_j524;
end;
/
 
--16.测试触发器
column sno format a11;
column sname format a7;
column sex format a4;
column tel format a12;
column email format a32;
column birthday format a11;
column mno format a11;
column majorno format a3;
set linesize 150;
set pagesize 100;
alter session set nls_date_format = 'yyyy-mm-dd';

--插入测试
select * from t_stud_j524 where sname='周五';
insert into t_attend_j524 values(to_date('20191020','yyyymmdd'),'0919170110','周五','B','B','B','E','E');
select * from t_stud_j524 where sname='周五';
 
--删除测试
select * from t_stud_j524 where sname='周五';
delete from t_attend_j524 where name='周五' and nowdate=to_date('20191020','yyyymmdd');
select * from t_stud_j524 where sname='周五';
 
--更新测试
select * from t_attend_j524 where name='周五';
select * from t_stud_j524 where sname='周五';
update t_attend_j524 set class12='C' where name='周五' and nowdate=to_date('20191019','yyyymmdd');
select * from t_attend_j524 where name='周五';
select * from t_stud_j524 where sname='周五';

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

--4.建立学生考勤表
drop table t_stud_atte_j524;
create table t_stud_atte_j524(
    startdate date default to_date('21000101','yyyyddmm'),
    stopdate date default to_date('19000101','yyyyddmm'),
    sno varchar2(10),
    sname varchar2(32),
    class_a number(3) default 0,
    class_b number(3) default 0,
    class_c number(3) default 0,
    class_d number(3) default 0,
    result number(3) default 100,
    constraint pk_stud_atte_sno primary key(sno),
    constraint fk_stud_atte_sno foreign key(sno) references t_stud_j524(sno));
    
    
--5.将学生表的所有学生插入学生考勤表
insert into t_stud_atte_j524(sno,sname) select t_stud_j524.sno,t_stud_j524.sname from t_stud_j524;
 
--6.显示学生考勤表
col sname format a7;
alter session set nls_date_format = 'yyyy-mm-dd';
select * from t_stud_atte_j524;
 
--7.建立专业考勤表
drop table t_major_atte_j524;
create table t_major_atte_j524(
    startdate date default to_date('20191001','yyyyddmm'),
    stopdate date default to_date('20191001','yyyyddmm'),
    mno varchar2(2),
    mname varchar2(32),
    class_a number(3) default 0,
    class_b number(3) default 0,
    class_c number(3) default 0,
    class_d number(3) default 0,
    result number(3) default 500,
    constraint pk_major_atte_sno primary key(mno),
    constraint fk_major_atte_sno foreign key(mno) references t_major_j524(mno));

--8.将专业表的所有专业插入专业考勤表
insert into t_major_atte_j524(mno,mname) select t_major_j524.mno,t_major_j524.mname from t_major_j524;
 
--9.显示专业考勤表
col mname format a20;
alter session set nls_date_format = 'yyyy-mm-dd';
select * from t_major_atte_j524;
 
--10.建立过程：汇总学生考勤数据，并将成绩更新到学生表中
//cur_atte t_attend_j524%rowtype;为了使一个变量的数据类型与一个表中记录的各个列的数据类型相对应、一致
/*
class_sa number(3);--正常上课状态A的次数
class_sb number(3);--上课迟到状态B的次数
class_sc number(3);--上课缺勤状态C的次数
class_sd number(3);--上课请假状态D的次数
class_res number(3);--当前考勤成绩
time_min date;--最小日期
time_max date;--最大日期
*/
create or replace procedure p_stud_atte_j524
as
cursor stud_atte_line is select * from t_attend_j524;
cur_atte t_attend_j524%rowtype;

class_sa number(3);
class_sb number(3);
class_sc number(3);
class_sd number(3);
class_res number(3);
 
time_min date;
time_max date;

begin
--先删除学生考勤表的所有记录，然后插入所有学号和学生姓名，成绩默认为100，其他属性默认为0
delete from t_stud_atte_j524;
insert into t_stud_atte_j524(sno,sname) select t_stud_j524.sno,t_stud_j524.sname from t_stud_j524;
for cur_atte in stud_atte_line loop
    --获取学生考勤表的当前记录
    select startdate into time_min from t_stud_atte_j524 where t_stud_atte_j524.sno=cur_atte.sno;
    select stopdate into time_max from t_stud_atte_j524 where t_stud_atte_j524.sno=cur_atte.sno;
    select class_a into class_sa from t_stud_atte_j524 where t_stud_atte_j524.sno=cur_atte.sno;
    select class_b into class_sb from t_stud_atte_j524 where t_stud_atte_j524.sno=cur_atte.sno;
    select class_c into class_sc from t_stud_atte_j524 where t_stud_atte_j524.sno=cur_atte.sno;
    select class_d into class_sd from t_stud_atte_j524 where t_stud_atte_j524.sno=cur_atte.sno;
    --通过比较日期，来获取每个学生不同的考勤时间段
    if(time_min>cur_atte.nowdate)then
        time_min:=cur_atte.nowdate;
    end if;
    if(time_max<cur_atte.nowdate)then
        time_max:=cur_atte.nowdate;
    end if;
 
    if(cur_atte.class12='A')then
        class_sa:=class_sa+1;
    elsif(cur_atte.class12='B')then
        class_sb:=class_sb+1;
    elsif(cur_atte.class12='C')then
        class_sc:=class_sc+1;
    elsif(cur_atte.class12='D')then
        class_sd:=class_sd+1;
    end if;
 
    if(cur_atte.class34='A')then
        class_sa:=class_sa+1;
    elsif(cur_atte.class34='B')then
        class_sb:=class_sb+1;
    elsif(cur_atte.class34='C')then
        class_sc:=class_sc+1;
    elsif(cur_atte.class34='D')then
        class_sd:=class_sd+1;
    end if;
 
    if(cur_atte.class56='A')then
        class_sa:=class_sa+1;
    elsif(cur_atte.class56='B')then
        class_sb:=class_sb+1;
    elsif(cur_atte.class56='C')then
        class_sc:=class_sc+1;
    elsif(cur_atte.class56='D')then
        class_sd:=class_sd+1;
    end if;
 
    if(cur_atte.class78='A')then
        class_sa:=class_sa+1;
    elsif(cur_atte.class78='B')then
        class_sb:=class_sb+1;
    elsif(cur_atte.class78='C')then
        class_sc:=class_sc+1;
    elsif(cur_atte.class78='D')then
        class_sd:=class_sd+1;
    end if;
 
    if(cur_atte.class90='A')then
        class_sa:=class_sa+1;
    elsif(cur_atte.class90='B')then
        class_sb:=class_sb+1;
    elsif(cur_atte.class90='C')then
        class_sc:=class_sc+1;
    elsif(cur_atte.class90='D')then
        class_sd:=class_sd+1;
    end if;
    --重新计算考勤成绩
    class_res:=100-class_sb*2-class_sc*5-class_sd;
    --将相关数据更新到学生考勤表
    update t_stud_atte_j524 set t_stud_atte_j524.startdate=time_min where t_stud_atte_j524.sno=cur_atte.sno;
    update t_stud_atte_j524 set t_stud_atte_j524.stopdate=time_max where t_stud_atte_j524.sno=cur_atte.sno;
    update t_stud_atte_j524 set t_stud_atte_j524.class_a=class_sa where t_stud_atte_j524.sno=cur_atte.sno;
    update t_stud_atte_j524 set t_stud_atte_j524.class_b=class_sb where t_stud_atte_j524.sno=cur_atte.sno;
    update t_stud_atte_j524 set t_stud_atte_j524.class_c=class_sc where t_stud_atte_j524.sno=cur_atte.sno;
    update t_stud_atte_j524 set t_stud_atte_j524.class_d=class_sd where t_stud_atte_j524.sno=cur_atte.sno;
    update t_stud_atte_j524 set t_stud_atte_j524.result=class_res where t_stud_atte_j524.sno=cur_atte.sno;
    update t_stud_j524 set t_stud_j524.sum_evaluation=class_res where t_stud_j524.sno=cur_atte.sno;
end loop;
end;
/
show error
 


--11.显示学生考勤表
exec p_stud_atte_j524;
col sname format a7;
alter session set nls_date_format = 'yyyy-mm-dd';
select * from t_stud_atte_j524;
 
--12.查看两名学生的考勤记录，查看数据汇总是否正确
select * from t_attend_j524 where name='王二';
select * from t_attend_j524 where name='王三';
 
--13.建立过程：汇总专业考勤数据，并将成绩更新到专业表中
create or replace procedure p_major_atte_j524(start_mtime date,stop_mtime date)
as
cursor major_atte_line is select * from t_attend_j524 where t_attend_j524.nowdate>=start_mtime and t_attend_j524.nowdate<=stop_mtime;
cur_atte t_attend_j524%rowtype;
class_ma number(3);
class_mb number(3);
class_mc number(3);
class_md number(3);
class_res number(3);
begin
delete from t_major_atte_j524;
insert into t_major_atte_j524(mno,mname) select t_major_j524.mno,t_major_j524.mname from t_major_j524;
 
for cur_atte in major_atte_line loop
     ---赋值进去 
    select class_a into class_ma from t_major_atte_j524 where t_major_atte_j524.mno=substr(cur_atte.sno,3,2);
    select class_b into class_mb from t_major_atte_j524 where t_major_atte_j524.mno=substr(cur_atte.sno,3,2);
    select class_c into class_mc from t_major_atte_j524 where t_major_atte_j524.mno=substr(cur_atte.sno,3,2);
    select class_d into class_md from t_major_atte_j524 where t_major_atte_j524.mno=substr(cur_atte.sno,3,2);
 
    if(cur_atte.class12='A')then
        class_ma:=class_ma+1;
    elsif(cur_atte.class12='B')then
        class_mb:=class_mb+1;
    elsif(cur_atte.class12='C')then
        class_mc:=class_mc+1;
    elsif(cur_atte.class12='D')then
        class_md:=class_md+1;
    end if;
 
    if(cur_atte.class34='A')then
        class_ma:=class_ma+1;
    elsif(cur_atte.class34='B')then
        class_mb:=class_mb+1;
    elsif(cur_atte.class34='C')then
        class_mc:=class_mc+1;
    elsif(cur_atte.class34='D')then
        class_md:=class_md+1;
    end if;
 
    if(cur_atte.class56='A')then
        class_ma:=class_ma+1;
    elsif(cur_atte.class56='B')then
        class_mb:=class_mb+1;
    elsif(cur_atte.class56='C')then
        class_mc:=class_mc+1;
    elsif(cur_atte.class56='D')then
        class_md:=class_md+1;
    end if;
 
    if(cur_atte.class78='A')then
        class_ma:=class_ma+1;
    elsif(cur_atte.class78='B')then
        class_mb:=class_mb+1;
    elsif(cur_atte.class78='C')then
        class_mc:=class_mc+1;
    elsif(cur_atte.class78='D')then
        class_md:=class_md+1;
    end if;
 
    if(cur_atte.class90='A')then
        class_ma:=class_ma+1;
    elsif(cur_atte.class90='B')then
        class_mb:=class_mb+1;
    elsif(cur_atte.class90='C')then
        class_mc:=class_mc+1;
    elsif(cur_atte.class90='D')then
        class_md:=class_md+1;
    end if;
 
    class_res:=500-class_mb*2-class_mc*5-class_md;
    
    update t_major_atte_j524 set t_major_atte_j524.startdate=start_mtime where t_major_atte_j524.mno=substr(cur_atte.sno,3,2);
    update t_major_atte_j524 set t_major_atte_j524.stopdate=stop_mtime where t_major_atte_j524.mno=substr(cur_atte.sno,3,2);
    update t_major_atte_j524 set t_major_atte_j524.class_a=class_ma where t_major_atte_j524.mno=substr(cur_atte.sno,3,2);
    update t_major_atte_j524 set t_major_atte_j524.class_b=class_mb where t_major_atte_j524.mno=substr(cur_atte.sno,3,2);
    update t_major_atte_j524 set t_major_atte_j524.class_c=class_mc where t_major_atte_j524.mno=substr(cur_atte.sno,3,2);
    update t_major_atte_j524 set t_major_atte_j524.class_d=class_md where t_major_atte_j524.mno=substr(cur_atte.sno,3,2);
    update t_major_atte_j524 set t_major_atte_j524.result=class_res where t_major_atte_j524.mno=substr(cur_atte.sno,3,2);
    update t_major_j524 set t_major_j524.sum_evaluation=class_res where t_major_j524.mno=substr(cur_atte.sno,3,2);
end loop;
end;
/
show error

--14.显示专业考勤表
exec p_major_atte_j524('20191013','20191019');
col mname format a20;
alter session set nls_date_format = 'yyyy-mm-dd';
select * from t_major_atte_j524;

 

二、总代码

 

conn u_j524/p123456
--1.为学生表和专业表各添加一列：考勤分数
desc t_stud_j524;
desc t_major_j524;
alter table t_stud_j524 add sum_evaluation number(3) default 100;
alter table t_major_j524 add sum_evaluation number(3) default 500;
desc t_stud_j524;
desc t_major_j524;

--2.建立考勤表
--正常：A
--迟到：B
--旷课：C
--请假：D
--没课：E

--学号(sno)为外键 
drop table t_attend_j524;
create table t_attend_j524(
    nowdate date not null,
    sno varchar(10) not null,
    name varchar(16) not null,
    class12 varchar(4) not null,
    class34 varchar(4) not null,
    class56 varchar(4) not null,
    class78 varchar(4) not null,
    class90 varchar(4) not null,
    constraint pk_attend_nowdate_sno primary key(nowdate,sno),
    constraint ck_attend_class12 check(class12 in('A','B','C','D','E')),
    constraint ck_attend_class34 check(class34 in('A','B','C','D','E')),
    constraint ck_attend_class56 check(class56 in('A','B','C','D','E')),
    constraint ck_attend_class78 check(class78 in('A','B','C','D','E')),
    constraint ck_attend_class90 check(class90 in('A','B','C','D','E')),
    constraint ck_attend_sno foreign key(sno) references t_stud_j524(sno));

--2.插入一周数据：2019.10.13至2019.10.19

--数据科学与大数据技术13名同学
insert into t_attend_j524 values(to_date('20191013','yyyymmdd'),'0921170114','李一','A','B','C','B','C');
insert into t_attend_j524 values(to_date('20191013','yyyymmdd'),'0921170115','李二','A','E','C','E','E');
insert into t_attend_j524 values(to_date('20191013','yyyymmdd'),'0921170117','李三','A','B','C','A','E');
insert into t_attend_j524 values(to_date('20191013','yyyymmdd'),'0921170120','李四','A','B','E','A','D');
insert into t_attend_j524 values(to_date('20191013','yyyymmdd'),'0921170122','李五','E','A','E','E','D');
insert into t_attend_j524 values(to_date('20191013','yyyymmdd'),'0921170123','李六','A','C','B','E','E');
insert into t_attend_j524 values(to_date('20191013','yyyymmdd'),'0921170124','李七','A','E','C','E','E');
insert into t_attend_j524 values(to_date('20191013','yyyymmdd'),'0921170207','张一','E','A','E','E','D');
insert into t_attend_j524 values(to_date('20191013','yyyymmdd'),'0921170225','张二','B','A','B','A','D');
insert into t_attend_j524 values(to_date('20191013','yyyymmdd'),'0921170226','张三','E','A','A','E','A');
insert into t_attend_j524 values(to_date('20191013','yyyymmdd'),'0921170227','张四','C','A','E','A','D');
insert into t_attend_j524 values(to_date('20191013','yyyymmdd'),'0921170209','张五','E','A','E','E','D');
insert into t_attend_j524 values(to_date('20191013','yyyymmdd'),'0921170228','张六','A','B','E','A','D');

--计算机科学与技术12名同学
insert into t_attend_j524 values(to_date('20191013','yyyymmdd'),'0902170516','王二','B','A','B','A','D');
insert into t_attend_j524 values(to_date('20191013','yyyymmdd'),'0902170520','王三','A','B','C','D','E');
insert into t_attend_j524 values(to_date('20191013','yyyymmdd'),'0902170521','王四','B','A','A','A','B');
insert into t_attend_j524 values(to_date('20191013','yyyymmdd'),'0902170523','杜豪','A','A','A','A','A');
insert into t_attend_j524 values(to_date('20191013','yyyymmdd'),'0902170524','宋海磊','A','A','A','A','A');
insert into t_attend_j524 values(to_date('20191013','yyyymmdd'),'0902170525','翟树杰','A','A','A','A','A');
insert into t_attend_j524 values(to_date('20191013','yyyymmdd'),'0902170526','胡旭东','A','A','A','A','A');
insert into t_attend_j524 values(to_date('20191013','yyyymmdd'),'0902170601','嬴政','B','A','A','A','B');
insert into t_attend_j524 values(to_date('20191013','yyyymmdd'),'0902170602','刘邦','A','B','A','D','A');
insert into t_attend_j524 values(to_date('20191013','yyyymmdd'),'0902170604','李广','C','A','B','A','D');
insert into t_attend_j524 values(to_date('20191013','yyyymmdd'),'0902170605','李世民','A','A','B','A','D');
insert into t_attend_j524 values(to_date('20191013','yyyymmdd'),'0902170625','赵匡胤','D','A','B','A','C');

--物联网工程11名同学
insert into t_attend_j524 values(to_date('20191013','yyyymmdd'),'0919170112','周一','B','A','A','A','B');
insert into t_attend_j524 values(to_date('20191013','yyyymmdd'),'0919170120','周二','B','A','C','A','B');
insert into t_attend_j524 values(to_date('20191013','yyyymmdd'),'0919170107','周三','A','B','C','B','C');
insert into t_attend_j524 values(to_date('20191013','yyyymmdd'),'0919170109','周四','A','B','E','A','D');
insert into t_attend_j524 values(to_date('20191013','yyyymmdd'),'0919170110','周五','A','B','E','A','D');
insert into t_attend_j524 values(to_date('20191013','yyyymmdd'),'0919170115','周六','A','C','C','A','C');
insert into t_attend_j524 values(to_date('20191013','yyyymmdd'),'0919170214','陈一','A','B','C','A','D');
insert into t_attend_j524 values(to_date('20191013','yyyymmdd'),'0919170215','陈二','B','A','A','A','B');
insert into t_attend_j524 values(to_date('20191013','yyyymmdd'),'0919170217','陈三','A','B','E','A','D');
insert into t_attend_j524 values(to_date('20191013','yyyymmdd'),'0919170220','陈四','A','B','A','A','C');
insert into t_attend_j524 values(to_date('20191013','yyyymmdd'),'0919170225','陈五','A','B','C','A','D');



/*
set linesize 150;//输出一行字符个数，缺省为80
set pagesize 0; //输出每页行数，缺省为24,为了避免分页，可设定为0。
*/

--3.显示考勤表
set linesize 150;
set pagesize 100;
col name format a7;
col sno format a11;
col class12 format a7;
col class34 format a7;
col class65 format a7;
col class78 format a7;
col class90 format a7;
alter session set nls_date_format='yyyy-mm-dd';
select * from t_attend_j524;


--4.建立学生考勤表
drop table t_stud_atte_j524;
create table t_stud_atte_j524(
    startdate date default to_date('21000101','yyyyddmm'),
    stopdate date default to_date('19000101','yyyyddmm'),
    sno varchar2(10),
    sname varchar2(32),
    class_a number(3) default 0,
    class_b number(3) default 0,
    class_c number(3) default 0,
    class_d number(3) default 0,
    result number(3) default 100,
    constraint pk_stud_atte_sno primary key(sno),
    constraint fk_stud_atte_sno foreign key(sno) references t_stud_j524(sno));
    
    
--5.将学生表的所有学生插入学生考勤表
insert into t_stud_atte_j524(sno,sname) select t_stud_j524.sno,t_stud_j524.sname from t_stud_j524;
 
--6.显示学生考勤表
col sname format a7;
alter session set nls_date_format = 'yyyy-mm-dd';
select * from t_stud_atte_j524;
 
--7.建立专业考勤表
drop table t_major_atte_j524;
create table t_major_atte_j524(
    startdate date default to_date('20191001','yyyyddmm'),
    stopdate date default to_date('20191001','yyyyddmm'),
    mno varchar2(2),
    mname varchar2(32),
    class_a number(3) default 0,
    class_b number(3) default 0,
    class_c number(3) default 0,
    class_d number(3) default 0,
    result number(3) default 500,
    constraint pk_major_atte_sno primary key(mno),
    constraint fk_major_atte_sno foreign key(mno) references t_major_j524(mno));

--8.将专业表的所有专业插入专业考勤表
insert into t_major_atte_j524(mno,mname) select t_major_j524.mno,t_major_j524.mname from t_major_j524;
 
--9.显示专业考勤表
col mname format a20;
alter session set nls_date_format = 'yyyy-mm-dd';
select * from t_major_atte_j524;
 
--10.建立过程：汇总学生考勤数据，并将成绩更新到学生表中
//cur_atte t_attend_j524%rowtype;为了使一个变量的数据类型与一个表中记录的各个列的数据类型相对应、一致
/*
class_sa number(3);--正常上课状态A的次数
class_sb number(3);--上课迟到状态B的次数
class_sc number(3);--上课缺勤状态C的次数
class_sd number(3);--上课请假状态D的次数
class_res number(3);--当前考勤成绩
time_min date;--最小日期
time_max date;--最大日期
*/
create or replace procedure p_stud_atte_j524
as
cursor stud_atte_line is select * from t_attend_j524;
cur_atte t_attend_j524%rowtype;

class_sa number(3);
class_sb number(3);
class_sc number(3);
class_sd number(3);
class_res number(3);
 
time_min date;
time_max date;

begin
--先删除学生考勤表的所有记录，然后插入所有学号和学生姓名，成绩默认为100，其他属性默认为0
delete from t_stud_atte_j524;
insert into t_stud_atte_j524(sno,sname) select t_stud_j524.sno,t_stud_j524.sname from t_stud_j524;
for cur_atte in stud_atte_line loop
    --获取学生考勤表的当前记录
    select startdate into time_min from t_stud_atte_j524 where t_stud_atte_j524.sno=cur_atte.sno;
    select stopdate into time_max from t_stud_atte_j524 where t_stud_atte_j524.sno=cur_atte.sno;
    select class_a into class_sa from t_stud_atte_j524 where t_stud_atte_j524.sno=cur_atte.sno;
    select class_b into class_sb from t_stud_atte_j524 where t_stud_atte_j524.sno=cur_atte.sno;
    select class_c into class_sc from t_stud_atte_j524 where t_stud_atte_j524.sno=cur_atte.sno;
    select class_d into class_sd from t_stud_atte_j524 where t_stud_atte_j524.sno=cur_atte.sno;
    --通过比较日期，来获取每个学生不同的考勤时间段
    if(time_min>cur_atte.nowdate)then
        time_min:=cur_atte.nowdate;
    end if;
    if(time_max<cur_atte.nowdate)then
        time_max:=cur_atte.nowdate;
    end if;
 
    if(cur_atte.class12='A')then
        class_sa:=class_sa+1;
    elsif(cur_atte.class12='B')then
        class_sb:=class_sb+1;
    elsif(cur_atte.class12='C')then
        class_sc:=class_sc+1;
    elsif(cur_atte.class12='D')then
        class_sd:=class_sd+1;
    end if;
 
    if(cur_atte.class34='A')then
        class_sa:=class_sa+1;
    elsif(cur_atte.class34='B')then
        class_sb:=class_sb+1;
    elsif(cur_atte.class34='C')then
        class_sc:=class_sc+1;
    elsif(cur_atte.class34='D')then
        class_sd:=class_sd+1;
    end if;
 
    if(cur_atte.class56='A')then
        class_sa:=class_sa+1;
    elsif(cur_atte.class56='B')then
        class_sb:=class_sb+1;
    elsif(cur_atte.class56='C')then
        class_sc:=class_sc+1;
    elsif(cur_atte.class56='D')then
        class_sd:=class_sd+1;
    end if;
 
    if(cur_atte.class78='A')then
        class_sa:=class_sa+1;
    elsif(cur_atte.class78='B')then
        class_sb:=class_sb+1;
    elsif(cur_atte.class78='C')then
        class_sc:=class_sc+1;
    elsif(cur_atte.class78='D')then
        class_sd:=class_sd+1;
    end if;
 
    if(cur_atte.class90='A')then
        class_sa:=class_sa+1;
    elsif(cur_atte.class90='B')then
        class_sb:=class_sb+1;
    elsif(cur_atte.class90='C')then
        class_sc:=class_sc+1;
    elsif(cur_atte.class90='D')then
        class_sd:=class_sd+1;
    end if;
    --重新计算考勤成绩
    class_res:=100-class_sb*2-class_sc*5-class_sd;
    --将相关数据更新到学生考勤表
    update t_stud_atte_j524 set t_stud_atte_j524.startdate=time_min where t_stud_atte_j524.sno=cur_atte.sno;
    update t_stud_atte_j524 set t_stud_atte_j524.stopdate=time_max where t_stud_atte_j524.sno=cur_atte.sno;
    update t_stud_atte_j524 set t_stud_atte_j524.class_a=class_sa where t_stud_atte_j524.sno=cur_atte.sno;
    update t_stud_atte_j524 set t_stud_atte_j524.class_b=class_sb where t_stud_atte_j524.sno=cur_atte.sno;
    update t_stud_atte_j524 set t_stud_atte_j524.class_c=class_sc where t_stud_atte_j524.sno=cur_atte.sno;
    update t_stud_atte_j524 set t_stud_atte_j524.class_d=class_sd where t_stud_atte_j524.sno=cur_atte.sno;
    update t_stud_atte_j524 set t_stud_atte_j524.result=class_res where t_stud_atte_j524.sno=cur_atte.sno;
    update t_stud_j524 set t_stud_j524.sum_evaluation=class_res where t_stud_j524.sno=cur_atte.sno;
end loop;
end;
/
show error
 


--11.显示学生考勤表
exec p_stud_atte_j524;
col sname format a7;
alter session set nls_date_format = 'yyyy-mm-dd';
select * from t_stud_atte_j524;
 
--12.查看两名学生的考勤记录，查看数据汇总是否正确
select * from t_attend_j524 where name='王二';
select * from t_attend_j524 where name='王三';
 
--13.建立过程：汇总专业考勤数据，并将成绩更新到专业表中
create or replace procedure p_major_atte_j524(start_mtime date,stop_mtime date)
as
cursor major_atte_line is select * from t_attend_j524 where t_attend_j524.nowdate>=start_mtime and t_attend_j524.nowdate<=stop_mtime;
cur_atte t_attend_j524%rowtype;
class_ma number(3);
class_mb number(3);
class_mc number(3);
class_md number(3);
class_res number(3);
begin
delete from t_major_atte_j524;
insert into t_major_atte_j524(mno,mname) select t_major_j524.mno,t_major_j524.mname from t_major_j524;
 
for cur_atte in major_atte_line loop
     ---赋值进去 
    select class_a into class_ma from t_major_atte_j524 where t_major_atte_j524.mno=substr(cur_atte.sno,3,2);
    select class_b into class_mb from t_major_atte_j524 where t_major_atte_j524.mno=substr(cur_atte.sno,3,2);
    select class_c into class_mc from t_major_atte_j524 where t_major_atte_j524.mno=substr(cur_atte.sno,3,2);
    select class_d into class_md from t_major_atte_j524 where t_major_atte_j524.mno=substr(cur_atte.sno,3,2);
 
    if(cur_atte.class12='A')then
        class_ma:=class_ma+1;
    elsif(cur_atte.class12='B')then
        class_mb:=class_mb+1;
    elsif(cur_atte.class12='C')then
        class_mc:=class_mc+1;
    elsif(cur_atte.class12='D')then
        class_md:=class_md+1;
    end if;
 
    if(cur_atte.class34='A')then
        class_ma:=class_ma+1;
    elsif(cur_atte.class34='B')then
        class_mb:=class_mb+1;
    elsif(cur_atte.class34='C')then
        class_mc:=class_mc+1;
    elsif(cur_atte.class34='D')then
        class_md:=class_md+1;
    end if;
 
    if(cur_atte.class56='A')then
        class_ma:=class_ma+1;
    elsif(cur_atte.class56='B')then
        class_mb:=class_mb+1;
    elsif(cur_atte.class56='C')then
        class_mc:=class_mc+1;
    elsif(cur_atte.class56='D')then
        class_md:=class_md+1;
    end if;
 
    if(cur_atte.class78='A')then
        class_ma:=class_ma+1;
    elsif(cur_atte.class78='B')then
        class_mb:=class_mb+1;
    elsif(cur_atte.class78='C')then
        class_mc:=class_mc+1;
    elsif(cur_atte.class78='D')then
        class_md:=class_md+1;
    end if;
 
    if(cur_atte.class90='A')then
        class_ma:=class_ma+1;
    elsif(cur_atte.class90='B')then
        class_mb:=class_mb+1;
    elsif(cur_atte.class90='C')then
        class_mc:=class_mc+1;
    elsif(cur_atte.class90='D')then
        class_md:=class_md+1;
    end if;
 
    class_res:=500-class_mb*2-class_mc*5-class_md;
    
    update t_major_atte_j524 set t_major_atte_j524.startdate=start_mtime where t_major_atte_j524.mno=substr(cur_atte.sno,3,2);
    update t_major_atte_j524 set t_major_atte_j524.stopdate=stop_mtime where t_major_atte_j524.mno=substr(cur_atte.sno,3,2);
    update t_major_atte_j524 set t_major_atte_j524.class_a=class_ma where t_major_atte_j524.mno=substr(cur_atte.sno,3,2);
    update t_major_atte_j524 set t_major_atte_j524.class_b=class_mb where t_major_atte_j524.mno=substr(cur_atte.sno,3,2);
    update t_major_atte_j524 set t_major_atte_j524.class_c=class_mc where t_major_atte_j524.mno=substr(cur_atte.sno,3,2);
    update t_major_atte_j524 set t_major_atte_j524.class_d=class_md where t_major_atte_j524.mno=substr(cur_atte.sno,3,2);
    update t_major_atte_j524 set t_major_atte_j524.result=class_res where t_major_atte_j524.mno=substr(cur_atte.sno,3,2);
    update t_major_j524 set t_major_j524.sum_evaluation=class_res where t_major_j524.mno=substr(cur_atte.sno,3,2);
end loop;
end;
/
show error

--14.显示专业考勤表
exec p_major_atte_j524('20191013','20191019');
col mname format a20;
alter session set nls_date_format = 'yyyy-mm-dd';
select * from t_major_atte_j524;
 
--15.建立触发器：当对考勤表进行相应插入、删除、修改时，对stud表的sum_evaluation数值进行相应的数据更新
create or replace trigger tg_attend_j524
after insert or update or delete on t_attend_j524

begin
p_stud_atte_j524;
end;
/
 
--16.测试触发器
column sno format a11;
column sname format a7;
column sex format a4;
column tel format a12;
column email format a32;
column birthday format a11;
column mno format a11;
column majorno format a3;
set linesize 150;
set pagesize 100;
alter session set nls_date_format = 'yyyy-mm-dd';

--插入测试
select * from t_stud_j524 where sname='周五';
insert into t_attend_j524 values(to_date('20191020','yyyymmdd'),'0919170110','周五','B','B','B','E','E');
select * from t_stud_j524 where sname='周五';
 
--删除测试
select * from t_stud_j524 where sname='周五';
delete from t_attend_j524 where name='周五' and nowdate=to_date('20191020','yyyymmdd');
select * from t_stud_j524 where sname='周五';
 
--更新测试
select * from t_attend_j524 where name='周五';
select * from t_stud_j524 where sname='周五';
update t_attend_j524 set class12='C' where name='周五' and nowdate=to_date('20191019','yyyymmdd');
select * from t_attend_j524 where name='周五';
select * from t_stud_j524 where sname='周五';