本系统是基于oracle的数据库系统，只支持后台的数据库设计，不涉及前台的界面需求，具体代码如下：

1、首先是表结构的建立


-1.create table: admin

drop table admin cascade constraints;

create table admin(
admin_id number(11) primary key,
admin_name varchar2(10) not null,
admin_password varchar2(11) not null,
admin_telephone varchar2(15) not null,
admin_email vachar2(right)
);

--2.create table: exam_paper

drop table exma_paper cascade constraints;

create table exam_paper(
paper_id number(11) primary key,
paper_type varchar2(10) not null,
paper_sum number(6) not null,
paper_update date not null,
paper_no number(11),
paper_p_no number(11) references paper_type(paper_no)
);



--3.create table: exminee

drop table exmine cascade constraints;

create table exminee(
examinee_id number(11) primary key,
examinee_name varchar(10) not null,
examinee_password varchar2(10) ,
examinee_telephone number(15),
examinee_email varchar2(right),
examinee_sex varchar2(8),
examine_grade number(3),
examniee_right number(1) check(examine_right=0 or examine_right=1),
examinee_no number(11) references examin_type(examine_no)
);


--4.create table: visitor

drop table visitor cascade constraints;

create table visitor(
visitor number(11) primary key,
visitor_right number(1) check(visitor_right=0),
visitor_demo varchar2(100)
);

--5.create table: teacher

--6.create table: paper_type

--7.create table: examine_type

--8.create table: teacher_type

--9.create table: rule



2、建立视图


--1. create view: exam_paper_view

create or replace view exam_paper_view
as 
select snotype,pnotype,tnotype
 from student,exam_paper,teacher
 where student.snotype = teacher.tnotype and teacher.tnotype = exam_paper.pnotype;

--2. create view:  examniee_type_view

create or replace view exam_type_view
as 
select sname,tname, pname
 from examin_type,paper_type,teacher_type where examin_type.sname=teacher_type.tname and teacher_type.tname=paper_type.pname;

3、序列的建立
--1. create sequence: seq_examine,seq_paper,seq_teacher,seq_visitor,seq_examine_type,seq_paper_type,seq_teacher_type,seq_admin
 
drop sequence seq_examine;
drop sequence seq_paper;
drop sequence seq_teacher;
drop sequence seq_visitor;
drop sequence seq_examine_type;
drop sequence seq_paper_type;
drop sequence seq_teacher_type;
drop sequence seq_admin;

create sequence seq_examine start with 1 increment by 1;
create sequence seq_paper start with 1 increment by 1;
create sequence seq_teacher start with 1 increment by 1;
create sequence seq_visitor start with 1 increment by 1;
create sequence seq_examine_type start with 1 increment by 1;
create sequence seq_paper_type start with 1 increment by 1;
create sequence seq_teacher_type start with 1 increment by 1;
create sequence seq_admin start with 1 increment by 1;

4、函数的建立
--1.学生公务员模拟考试时间限制函数
Create  or  replace  function  exam_time(v_snum number,v_exam_paper number)
Return varchar2 as
  v_snotype number;
  v_pnotype number;
  v_time number;

Begin 
   select v_snotype into v_student from student where snum=v_snum;
   select v_pnotype into v_exam_paper from exam_paper where pid=v_exam_paper;
   select time into v_dtime from rule where  v_student=v_snotype  and v_pnotype=v_exam_paper;

Return v_date;
Exception
/*尚未开始答题目*/
 When no_data_found then
  Select max(time) into v_date from rule;
  Return v_date;
End ;
/

--2.学生已答题的题库的数目函数
create or replace function exam_paper_snum(v_snum number,v_exam_paper number)
return number
 as
    v_snotype nummber;
    v_pnotype number
    v_num number;

Begin 
    select v_snotype into v_student from student where snum=v_snum;
    select v_pnotype into v_exam_paper from exam_paper where pid=v_exam_paper;
    select count(*) into v_num from rule where   v_student=v_snotype  and v_pnotype=v_exam_paper;

 Return v_num;

End ;
/

--3.题库更新日期函数
create or replace function paper_date(v_sum number,v_exam_paper number)
return varchar2
 as
    v_snotype nummber;
    v_pnotype number
    v_date varchar2(10);

Begin 
    select student_num into v_student from student where sum=v_sum;
    select paper_num into v_exam_paper from exam_paper where pid=v_exam_paper;
    select to_char(sysdate+days, 'yyyy-mm-dd') into v_date from rule where   v_student=v_snotype  and v_pnotype=v_exam_paper;
    v_days:=extract(day from sysdate);

    IF v_days=7 then 
      
       DBMS_OUTPUT.PUT_LINE('Dear Teacher,The exam_paper should be update!!!');
       v_days=0;

    ELSE 
         v_days--;
         
 Return v_date;

Exception
/*尚未制定题库更新规则*/
 When no_data_found then
  select max(date) into v_date from rule;
  return v_num;
End ;
/
  
--4.学生可答题库的数目函数
create or replace function exam_paper_num(v_sum number,v_exam_paper number)
return number
 as
    v_snotype nummber;
    v_pnotype number
    v_for_num number;

Begin 
    select v_snotype into v_student from student where sum=v_sum;
    select v_pnotype into v_exam_paper from exam_paper where pid=v_exam_paper;
    select num into v_for_num from rule where   v_student=v_snotype  and v_pnotype=v_exam_paper;

 Return v_for_num;
   
Exception
/*尚未制定答题题库数目规则*/
 When no_data_found then
  select max(pnum) into v_for_num from rule;
  return v_num;
End ;
/

5、存储过程的建立

--1.超过答题时间关闭答题权限的存储过程
create or replace procedure exam_time_c_a (v_paper_id number)
as

  v_exam_time rule.exam_time%type;
  v_paper_sum rule.paper_sum%type;
  v_examn examniee%rowtype;

begin

  select (systime-begintime) into v_time from rule where paper_r_id=v_paper_id;
  if v_time>=exam_time then v_examn.examniee_right:=0;
  end if;
exception
  when others then
    v_time:=0;
end;
/

--2.考生：对于在一年内未进行登入考试的考生关闭考试权限的存储过程

create or replace procedure examine_n_l_o_year(v_lastloadingdays  date , v_firstloadingdays date)
as
    cursor c_examinee is select * from examinee e,rule r where e.examinee_id=r.examinee_r_id;
    v_examinee c_examinee%rowtype;

begin
   open c_examinee;
    loop
     fech c_examniee into v_examinee;
     while c_examniee%found then
     if(v_examinee.v_lastloadingdays-v_examinee.v_firstloadingdays>365)
        v_examine.examniee_right:=0;
        dbms_output.put_line("There is not such an examine!");
     
     endif;
  end loop;
 close  c_examinee;
end;
/

/
--3.试卷：检查教师上传试卷是否与试卷题库相同,如果相同则提示无法上传的存储过程

create or replace procedure  paper_same(v_paper_id number,v_teacher_type varchar,v_paper_no number)
as
   cursor c_same is select * from paper;
   v_same c_same%rowtype;
begin
  open c_same;
    loop
      
      fetch c_same into v_same;
      while c_same%FOUND then 
      if(v_same.v_paper_id=paper_id and v_same.v_paper_type=paper_type and v_same.v_paper_no=paper_no ) 
      then
       dbms_output.put_line("There is such a testpaper!Can't upfile~");
      else when c_same%notfound 
        dbms_output.put_line("There is not such a testpaper!Can upfile~");
      endif;
    end loop;
close  c_same;

end;
/      
///

--4.试卷：检查是否到达题库更新时间,超过三个月没有更新题库则提示更新题库的存储过程

create or replace procedure exam_update(v_days date)
as
begin
as
 select months_between(paper_update,sysdate) into v_days from exam_paper where paper_update>=all(select paper_update from exam_paper);
 if v_days>=3 then 
  dbms_output.put_line("You have to update the exampaper!");
 end if;
end;
/ 


--5.访客：判断访客可否进行在线考试的存储过程
create or replace procedure vsitor_exam(v_visitor_id  number)
as
 begin
as
 v_right number;
 select right into v_right from visitor where visitor_id=v_visitor_id;
 if v_right=0 then 
  v_sum:=0;
  dbms_output.put_line("You can not test this exam!");
 end if;
end;
/

6、触发器的建立

--1.删除试卷类型的触发器。
Create or replace trigger tr_delete_paper_type 
  Before delete on paper_type for each row
begin
  Delete from paper where Paper_no =:old.Paper_no cascade constraints;
End;
/
--2．删除考生类型的触发器。
Create or replace trigger tr_delete_exminee_type
  Before delete on exminee_type for each row
begin
  Delete from exminee where Examinee_no=:old.Examinee_no cascade constraints;
End;
/
--3.删除老师类型的触发器。
Create or replace trigger tr_delete_teacher_type
  Before delete on teacher_type for each row
begin
  Delete from teacher where Teacher_no=:old.Teacher_no cascade constraints;
End;
/
--4.删除试卷的触发器。
Create or replace trigger tr_delete_Exam_paper 
  Before delete on Exam_paper for each row
begin
  Delete from paper where paper_id =:old.Paper_id;
  Delete from rule where paper_r_no=:old.paper_no;
End;
/
--5．删除考生的触发器。
Create or replace trigger tr_delete_exminee
  Before delete on exminee for each row
begin
  Delete from exminee where Examinee_id=:old.Examinee_id;
  Delete from rule where Examinee_r_no=:old.Examinee_no;
End;
/
--6.删除老师的触发器。
Create or replace trigger tr_delete_teacher
  Before delete on teacher for each row
begin
  Delete from teacher where Teacher_id=:old.Teacher_id;
  Delete from rule where Teacher_r_no=:old.Teacher_no;
End;
/


还有部分是测试代码，在下述的资源中。