oracle -实战项目2-教务系统

这个代码是根据博客进行代码的复现，在实现学生功能和教师功能时进行改进，使用存储过程进行实现，表的结构不变，可以参考的我上一篇博客

--教务系统
--学生表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;