这个代码是根据博客进行代码的复现,在实现学生功能和教师功能时进行改进,使用存储过程进行实现,表的结构不变,可以参考的我上一篇博客
--教务系统
--学生表3
create table student(
student_id number(10) primary key,
student_name varchar2(20) not null,
student_passward varchar2(20) unique,
stu_classid number(10),
foreign key (stu_classid) references class(class_id)
)
--班级表2
create table class(
class_id number(10) primary key,
class_name varchar2(30) not null,
class_teacherid number(20),
foreign key (class_teacherid) references teacher(teacher_id)
)
--教师表1
create table teacher(
teacher_id number(10) primary key,
teacher_name varchar2(30) not null,
teacher_passward varchar2(30) unique
)
/*
* 教学:上课的时间,班级编号,教师编号,课程编号
* 学习:学生的编号,课程的编号,课程的成绩
* 课程:编号,类型,名称,学分
**/
--课程
create table lesson(
lesson_id number(10) primary key,
lesson_name varchar2(20) unique,
lesson_style varchar2(30) not null,
lesson_score number(20)
)
--添加表中的一个字段alter table 表名 add 字段
alter table lesson add (lesson_time date)
--学习
create table study(
study_stuid number(10) primary key,
study_lesid number(10),
study_score number(10),
foreign key (study_stuid) references student(student_id),
foreign key (study_lesid) references lesson(lesson_id)
)
--教学
create table teach(
teach_teaid number(10),
teach_classid number(10),
teach_lesid number(10),
teach_time date,
foreign key (teach_teaid) references teacher(teacher_id),
foreign key (teach_lesid) references lesson(lesson_id),
foreign key (teach_classid) references classes(class_id)
)
--另外一种创建的方法(在teach初始化完成后执行)
create table study(stu_id,cou_id)
as select stu_id,course_id
from teach,student
where student.class_id=teach.class_id;
alter table study add grade int;
/*
* 插入数据 insert into 表名 values()
**/
--教师列表
insert into teacher values('10001','123','唐卷');
insert into teacher values('10002','123','杨朔');
insert into teacher values('10003','123','谭恒良');
insert into teacher values('10004','123','丁子旋');
insert into teacher values('10005','123','陈文彬');
insert into teacher values('10006','123','陶文正');
insert into teacher values('10007','123','杨柳');
insert into teacher values('10008','123','颜国风');
insert into teacher values('10009','123','王显珉');
commit
--修改表名 rename 旧表名 to 新表名
rename class to classes
--班级列表
insert into classes values('40181','计科181','10009');
insert into classes values('40182','计科182','10008');
insert into classes values('40183','计科183','10003');
insert into classes values('40184','计科184','10009');
insert into classes values('40185','计科185','10002');
insert into classes values('40186','计科186','10004');
--修改表字段名
alter table lesson modify(lesson_time number(20))
--课程列表
insert into lesson values('30001','编译原理','专业必修','2','32');
insert into lesson values('30002','编译原理实验','专业必修','0.5','16');
insert into lesson values('30003','机器学习与数据挖掘','专业必修','3','48');
insert into lesson values('30004','机器学习与数据实验','专业必修','0.5','16');
insert into lesson values('30005','人工智能原理','专业必修','3','48');
insert into lesson values('30006','人工智能原理实验','专业必修','1','32');
insert into lesson values('30007','Unix/Linux操作分析','专业选修','2','32');
insert into lesson values('30008','Unix/Linux操作系统','专业选修','0.5','16');
insert into lesson values('30009','算法设计与分析','专业选修','2','32');
commit;
--学生列表
insert into student values('20001','123','张三','40183');
insert into student values('20002','123','李四','40182');
insert into student values('20003','123','吴芳','40183');
insert into student values('20004','123','刘静','40181');
insert into student values('20005','123','金泽','40186');
insert into student values('20006','123','张良仁','40183');
insert into student values('20007','123','陈鹏','40185');
insert into student values('20008','123','林岭','40186');
insert into student values('20009','123','张义谋','40185');
insert into student values('20010','123','陈淇','40181');
insert into student values('20011','123','柳忠','40186');
insert into student values('20012','123','杨阳','40182');
alter table teach modify(teach_time varchar2(50))
select * from teacher
select * from student
select * from classes
select * from teach
drop table teach purge
--教学列表
insert into teach values('10001','40181','30001','1-16周,周三,3-4节');
insert into teach values('10001','40181','30002','1-16周,周三,7-8节');
insert into teach values('10001','40182','30001','1-16周,周三,3-4节');
insert into teach values('10001','40182','30002','1-16周,周三,7-8节');
insert into teach values('10002','40181','30003','1-16周,周三,1-2节');
insert into teach values('10002','40183','30003','1-16周,周三,1-2节');
insert into teach values('10002','40181','30004','1-16周,周三,5-6节');
insert into teach values('10002','40182','30004','1-16周,周三,7-8节');
insert into teach values('10003','40181','30005','1-8周,周一,3-4节');
insert into teach values('10003','40182','30005','1-8周,周一,3-4节');
insert into teach values('10003','40183','30005','1-8周,周一,3-4节');
insert into teach values('10003','40181','30005','1-16周,周五,1-2节');
insert into teach values('10003','40182','30005','1-16周,周五,1-2节');
insert into teach values('10003','40183','30005','1-16周,周五,1-2节');
insert into teach values('10003','40181','30006','1-16周,周五,7-8节');
insert into teach values('10003','40182','30006','1-16周,周五,5-6节');
insert into teach values('10003','40183','30006','1-16周,周五,3-4节');
insert into teach values('10006','40181','30007','1-10周,周二,9-11节');
insert into teach values('10006','40181','30008','1-8周,周二,1-2节');
insert into teach values('10006','40182','30007','1-10周,周二,9-11节');
insert into teach values('10006','40182','30008','1-8周,周三,5-6节');
insert into teach values('10005','40181','30009','1-16周,周一,5-6节');
insert into teach values('10005','40182','30009','1-16周,周一,5-6节');
insert into teach values('10005','40183','30009','1-16周,周一,5-6节');
insert into teach values('10007','40184','30009','1-16周,周一,5-6节');
insert into teach values('10007','40185','30009','1-16周,周一,5-6节');
insert into teach values('10007','40186','30009','1-16周,周一,5-6节');
insert into study values('20002','30001','89')
insert into study values('20004','30001','85')
insert into study values('20003','30002','84')
commit
--更新学习列表的成绩
update study set study_score='89' where stu_id='20002' and cou_id='30001';
update study set study_score='85' where stu_id='20004' and cou_id='30001';
update study set study_score='84' where stu_id='20002' and cou_id='30002';
update study set study_score='91' where stu_id='20004' and cou_id='30002';
select * from student
select * from classes
select * from teach
/*
* 学生功能
**/
--查询课表的功能
create or replace view stu_table(tea_id,tea_name,class_id,lesson_id,lesson_name,study_time) as
select teach_teaid,teacher_name,class_id,lesson_id,lesson_name,teach_time
from student,classes,teach,lesson,teacher
where stu_classid = class_id and class_id = teach_classid and
teach_lesid = lesson_id and teach_teaid = teacher_id
select * from stu_table
--查询分数
create or replace view stu_grade(
lesson_id,lesson_name,study_score,lesson_score
)
as
select lesson_id,lesson_name,study_score,lesson_score
from lesson,student,study
where study_stuid = student_id and study_lesid = lesson_id
select * from stu_grade
select * from study
--查询绩点
create or replace view stu_point(
all_score,avg_grade,grade_point
)
as
select
sum(study_score),round(avg(lesson_score),2),round(sum(study_score*lesson_score)/sum(study_score),2)
from stu_grade
select * from stu_point
/*
* 教师功能
**/
--教师任教课程:查询出每个老师的任课课程
create or replace view tea_course(
cou_id, cou_nam
)
as
select distinct lesson_id,lesson_name
from lesson,teach
where lesson_id = teach_lesid and teach_teaid = '10001'
select * from tea_course
--根据点击的任教课程进行成绩的查询与修改:查看老师所授课程的成绩--一个老师对应的一门课程的成绩
select * from student
create or replace view tea_grade(
cou_id, cou_name,stu_id,stu_name,grade
)
as
select lesson_id,lesson_name,student_id,student_name,study_score
from teach,study,lesson,student
where teach_lesid = lesson_id and lesson_id = study_lesid and
teach_classid = stu_classid and teach_teaid = '10001' and teach_lesid = '30001'
select * from tea_grade
--任课课表查询 --查看某位老师的课表
create or replace view tea_table(
class_id,class_nname,cou_id,cou_name,study_time
)
as
select class_id,class_name,lesson_id,lesson_name,teach_time
from teach,classes,lesson
where teach_lesid = lesson_id and teach_classid = class_id and teach_teaid = '10001'
select * from tea_table
--系主任的整合权限
--创建一个新的视图,将学生的功能视图与教师的功能视图进行整合,
--所有的视图利用关联条件进行关联
/*
* 管理员功能:这里做了一个修改,将其作为过程进行存储
**/
--首先是判断教师上课的时间是否冲突
create or replace procedure teacher_time(teaid in number)
is
--创建一个游标:因为不是一行数据,所以使用游标进行存储
cursor tea is select teach_teaid,teach_time,count(*) c
from teach where teach_teaid = teaid
group by teach_teaid,teach_time;
order_row tea%ROWTYPE;
begin
--teaid :=&teaid;
open tea;
LOOP
FETCH tea INTO order_row;
EXIT WHEN tea%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('Teach_teaid : ' || order_row.teach_teaid || ', Teach_time: ' || order_row.teach_time||',count,'||order_row.c);
END LOOP;
if order_row.c=1 then
dbms_output.put_line('教师编号为:'||teaid||'的课程没有时间冲突');
else
dbms_output.put_line('教师编号为:'||teaid||'的课程有时间冲突');
end if;
CLOSE tea;
end;
/
--调用
DECLARE
teaid NUMBER := '10001'; -- 此处设置要查询的用户ID
--v_order_count NUMBER;
BEGIN
teacher_time(teaid);
--DBMS_OUTPUT.PUT_LINE('Total Order Count: ' || v_order_count);
END;
--判断学生课表是否课程冲突或时间冲突
create or replace procedure student_time(stuid in number)
is
--学生排课情况
cursor stu is
select A.teach_time,teach_lesid,stu_cnt1,teach_classid,stu_cnt2
from
(select teach_time,teach_lesid,count(*) stu_cnt1
from teach
left join student on stu_classid = teach_classid
where student_id = stuid
group by teach_time,teach_lesid) A
full join
(select teach_classid,teach_time,count(*) stu_cnt2
from teach
left join student on stu_classid = teach_classid
where student_id = stuid
group by teach_time,teach_classid) B
on A.teach_time = B.teach_time;
order_row1 stu%ROWTYPE;
begin
--teaid :=&teaid;
open stu;
LOOP
FETCH stu INTO order_row1;
EXIT WHEN stu%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('Teach_classid : ' || order_row1.teach_classid ||',count,'
||order_row1.stu_cnt1||',Teach_lesid:'||order_row1.teach_lesid
||',Teach_time:'||order_row1.teach_time||',cnt2:'||order_row1.stu_cnt2 );
END LOOP;
if (order_row1.stu_cnt1 + order_row1.stu_cnt2 <= 2 )then
dbms_output.put_line('教师编号为:'||stuid||'的课程没有时间冲突');
else
dbms_output.put_line('教师编号为:'||stuid||'的课程有时间冲突');
end if;
CLOSE stu;
end;
/
--调用
DECLARE
stuid NUMBER := '20004'; -- 此处设置要查询的用户ID
--v_order_count NUMBER;
BEGIN
student_time(stuid);
--DBMS_OUTPUT.PUT_LINE('Total Order Count: ' || v_order_count);
END;