badEgg > 数据库实验二

数据库实验二

2019-11-02

数据库

数据库实验二

实验一已经完成了，虽然有点小插曲

我什么场面没见过 这场面我真没见过

事不宜迟，开始实验二

实验二要求:

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
2
3
4
alter table major_J314 drop column sum_evaluation;    //删除字段，备用
alter table stud_J314 drop column sum_evaluation;     //删除字段，备用
alter table major_J314 add sum_evaluation int Default 100 Not NULL;
alter table stud_J314 add sum_evaluation int Default 100 NOT NULL;

这个地方注意了，oracle必须NOT NULL 在 Default后面，不然报错= =

建立考勤表

然后我们来捋一捋考勤表的字段与约束

  1. 统计学生，需要学生号SNO字段
  2. 考勤记录，需要记录ATTEND字段
  3. 需要统计以专业为单位，需要MajorNo字段
  4. 考勤需要记录时间，需要SDATE字段
  5. 四中把考勤一天分为5个单元，需要UNIT字段

建表如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
drop table attend_J314;
create table attend_J314
(
    SNO varchar(20),
    ATTEND int,
    MajorNo char(20),
    SDATE date,
    UNIT varchar(10),
    constraint aj_pk
    	primary key(SNO, SDATE, UNIT)，
    constraint aj_unit
    	check (UNIT in ('12','34','56','78','90')),
    constraint aj_attend
    	check (ATTEND in ('0','1','2','5'))
);

初步建表就这样了

对了，发现一个问题，千万不要在最后一个字段后面加逗号，他会把 ) 也当作字段读入，这个和css或者一些编程语言不一样了= =

建立个人考勤表和专业考勤表

个人/专业考勤表

  1. 个人的表需要SNO学号/专业号MajorNo
  2. 需要开始日期SDate
  3. 需要结束日期EDate
  4. 需要正常次数NCount
  5. 需要迟到次数LCount
  6. 需要旷课次数ABCount
  7. 需要成绩score

开始建表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
drop table stud_attend_J314;
drop table major_attend_J314;

create table stud_attend_J314
(
    SNO varchar(10),
    SDate date,
    EDate date,
    NCount int,
    LeCount int,
    LaCount int,
    ABCount int,
    constraint saj_pk
    	primary key(SNO, SDate, EDate),
    constraint saj_sno_fk
    	foreign key(SNO) references stud_J314(SNO)
);

create table major_attend_J314
(
    MajorNo varchar(10),
    SDate date,
    EDate date,
    NCount int,
    LeCount int,
    LaCount int,
    ABCount int,
    score int,
    constraint maj_pk
    	primary key(MajorNo, SDate, EDate),
    constraint maj_sno_fk
    	foreign key(MajorNo) references major_J314(MajorNo)
);

同理，初步建表就这样了

插入数据

又到了喜闻乐见的插（编）入（造）数（脑）据（洞）时间了，特别要求部分学生有一周一天五个单元的记录

代码如下

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
insert into attend_J314 values('0902170314', '0', '02', to_date('20191028', 'YYYYMMDD'), '56');
insert into attend_J314 values('0902170314', '1', '02', to_date('20191028', 'YYYYMMDD'), '78');
insert into attend_J314 values('0902170314', '2', '02', to_date('20191029', 'YYYYMMDD'), '12');
insert into attend_J314 values('0902170314', '0', '02', to_date('20191029', 'YYYYMMDD'), '78');
insert into attend_J314 values('0902170314', '1', '02', to_date('20191030', 'YYYYMMDD'), '90');
insert into attend_J314 values('0902170314', '0', '02', to_date('20191031', 'YYYYMMDD'), '56');
insert into attend_J314 values('0902170314', '0', '02', to_date('20191101', 'YYYYMMDD'), '34');
insert into attend_J314 values('0902170314', '0', '02', to_date('20191101', 'YYYYMMDD'), '90');

insert into attend_J314 values('0903170101', '5', '02', to_date('20191028', 'YYYYMMDD'), '56');
insert into attend_J314 values('0903170102', '0', '02', to_date('20191028', 'YYYYMMDD'), '78');
insert into attend_J314 values('0903170102', '2', '02', to_date('20191029', 'YYYYMMDD'), '12');
insert into attend_J314 values('0903170103', '0', '02', to_date('20191029', 'YYYYMMDD'), '78');
insert into attend_J314 values('0903170105', '1', '02', to_date('20191030', 'YYYYMMDD'), '90');
insert into attend_J314 values('0903170108', '2', '02', to_date('20191031', 'YYYYMMDD'), '56');
insert into attend_J314 values('0903170109', '0', '02', to_date('20191101', 'YYYYMMDD'), '34');
insert into attend_J314 values('0903170109', '5', '02', to_date('20191101', 'YYYYMMDD'), '90');

insert into attend_J314 values('0904170101', '0', '02', to_date('20191028', 'YYYYMMDD'), '56');
insert into attend_J314 values('0904170102', '1', '02', to_date('20191028', 'YYYYMMDD'), '78');
insert into attend_J314 values('0904170102', '0', '02', to_date('20191029', 'YYYYMMDD'), '12');
insert into attend_J314 values('0904170103', '0', '02', to_date('20191029', 'YYYYMMDD'), '78');
insert into attend_J314 values('0904170105', '1', '02', to_date('20191030', 'YYYYMMDD'), '90');
insert into attend_J314 values('0904170108', '0', '02', to_date('20191031', 'YYYYMMDD'), '56');
insert into attend_J314 values('0904170109', '0', '02', to_date('20191101', 'YYYYMMDD'), '34');
insert into attend_J314 values('0904170109', '0', '02', to_date('20191101', 'YYYYMMDD'), '90');

在我准备插入的时候我发现一个问题，题设要求与实际需要都要是在stud和major表有的前提下的，所以我需要在attend表加上上面两个表的外键才对，添加如下重新建表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
drop table attend_J314;
create table attend_J314
(
    SNO varchar(20),
    ATTEND int,
    MajorNo varchar(20),
    SDATE date,
    UNIT varchar(10),
    constraint aj_pk
    	primary key(SNO, SDATE, UNIT)，
    constraint aj_unit
    	check (UNIT in ('12','34','56','78','90')),
    constraint aj_attend
    	check (ATTEND in ('0','1','2','5')),
    constraint aj_sno_fk
    	foreign key(SNO) references stud_J314(SNO),
    constraint aj_MajorNo_fk
    	foreign key(MajorNo) references major_J314(MajorNo)
);

以此类推，stud_attend与major_attend两个表都需要加上SNO的外键约束

1
2
3
4
constraint saj_sno_fk
    	foreign key(SNO) references stud_J314(SNO)
constraint maj_sno_fk
    	foreign key(MajorNo) references major_J314(MajorNo)

做到这一步，表，数据和关系都基本建立完成了，下一步就是触发器和过程了

先是触发器，要求在对attend表修改时，对stud_attend进行相应修改，那么问题来了，我们的stud_attend表还没有插入数据，那就先做数据插入吧= =

1
select 'insert into stud_attend_J314 values ("'||SNO||'", to_date("20190901", "YYYYMMDD"), to_date("20200101", "YYYYMMDD"), 0, 0, 0, 100);' from stud_J314;

在我把数据插入完后发现上一步的insert又走早了，需要先做触发器再做插入才比较好

attend表触发器我的打算是，先修改stud_attend_J314表再通过stud_attend_J314表的触发器触发stud_J314的修改

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
create or replace trigger T_attend_J314 after insert or delete or update on attend_J314 for each row
begin
	if inserting then
		update stud_J314 set sum_evaluation = sum_evaluation - :new.ATTEND where SNO = :new.SNO;
		/*
		if :new.ATTEND = 1 then
			update stud_attend_J314 set LeCount = LeCount + 1 where SNO = :new.SNO;
		elsif :new.ATTEND = 2 then
			update stud_attend_J314 set LaCount = LaCount + 1 where SNO = :new.SNO;
		elsif :new.ATTEND = 5 then
			update stud_attend_J314 set ABCount = ABCount + 1 where SNO = :new.SNO;
		elsif :new.ATTEND = 0 then
			update stud_attend_J314 set NCount = NCount + 1 where SNO = :new.SNO;
		end if;
		*/
	elsif deleting then
		update stud_J314 set sum_evaluation = sum_evaluation + :old.ATTEND where SNO = :old.SNO;
		/*
		if :old.ATTEND = 1 then
			update stud_attend_J314 set LeCount = LeCount - 1 where SNO = :old.SNO;
		elsif :old.ATTEND = 2 then
			update stud_attend_J314 set LaCount = LaCount - 1 where SNO = :old.SNO;
		elsif :old.ATTEND = 5 then
			update stud_attend_J314 set ABCount = ABCount - 1 where SNO = :old.SNO;
		elsif :old.ATTEND = 0 then
			update stud_attend_J314 set NCount = NCount - 1 where SNO = :old.SNO;
		end if;
		*/
	elsif updating then
		update stud_J314 set sum_evaluation = sum_evaluation - :new.ATTEND where SNO = :new.SNO;
		update stud_J314 set sum_evaluation = sum_evaluation + :old.ATTEND where SNO = :old.SNO;
		/*
		if :old.ATTEND = 1 then
			update stud_attend_J314 set LeCount = LeCount - 1 where SNO = :old.SNO;
		elsif :old.ATTEND = 2 then
			update stud_attend_J314 set LaCount = LaCount - 1 where SNO = :old.SNO;
		elsif :old.ATTEND = 5 then
			update stud_attend_J314 set ABCount = ABCount - 1 where SNO = :old.SNO;
		elsif :old.ATTEND = 0 then
			update stud_attend_J314 set NCount = NCount - 1 where SNO = :old.SNO;
		end if;
		
		if :new.ATTEND = 1 then
			update stud_attend_J314 set LeCount = LeCount + 1 where SNO = :new.SNO;
		elsif :new.ATTEND = 2 then
			update stud_attend_J314 set LaCount = LaCount + 1 where SNO = :new.SNO;
		elsif :new.ATTEND = 5 then
			update stud_attend_J314 set ABCount = ABCount + 1 where SNO = :new.SNO;
		elsif :new.ATTEND = 0 then
			update stud_attend_J314 set NCount = NCount + 1 where SNO = :new.SNO;
		end if;
		*/
	end if;
end;

插入就不测试了，上面的都是例子，我们测试一下修改与删除

1
2
3
delete from attend_J314 where SNO = '0904170105';
insert into attend_J314 values('0904170105', '1', '02', to_date('20191030', 'YYYYMMDD'), '90');
update attend_J314 set ATTEND = 5 where SNO = '0904170105';

左调右调应该没什么问题了= =

下一步是建立一个过程，过程就是一个搜索并更新的过程

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
create or replace procedure p_attend_J314
(
    Num in char,
 	s_date in date,
    e_date in date
)is
a int := 0;
scount int := 0;
normal int := 0;
leave int := 0;
late int := 0;
absent int := 0;
begin
	for attend in (select ATTEND from attend_J314 where MajorNo = Num and SDATE >= s_date and SDATE <= e_date)loop
		scount := scount + 1;
		if attend.ATTEND = 0 then
			normal := normal + 1;
		elsif attend.ATTEND = 1 then
			leave := leave + 1;
		elsif attend.ATTEND = 2 then
			late := late + 1;
		elsif attend.ATTEND = 5 then
			absent := absent + 1;
		end if;
	end loop;
	scount := (scount * 100 - leave - late * 2 - absent * 5) / scount;
	insert into major_attend_J314 values(Num, s_date, e_date, normal, leave, late, absent, scount);
end p_attend_J314;

下面就是对过程的测试了

1
exec p_attend_J314('02','20-10月-2019','03-11月-2019');