0045153数据库脚本.txt

这是我大三学完dephli是自己根据自己所学和查阅了一些相关资料后自己开发一个学校运动会管理系统．具体功能有：系统用户管理

rem ****************************************************
rem **         大作业数据库设计
rem **
rem **  功能：建立运动会的相关信息
rem **  作者：刘道欢
rem **  学号：0045153
rem **  首次创建时间：2007年5月8日
rem **  最后修改时间：2007年5月14日
rem ****************************************************

rem *****************************************************
rem **删除表
rem *****************************************************
drop table sportUser;
drop table AthleteOther;
drop table RecordBreaking; 
drop table PersonMatch;
drop table Matcharrangment;
drop table Judge;
drop table Athlete;
drop table Class;
/

rem ****************************************************
rem ** 创建系统登录用户表                                    
rem ****************************************************
create table sportUser(
UserID    varchar2(6)    not null,
Userpwd   varchar2(6)   not null,
Competence   varchar2(10)  not null,
constraint sportUser_PK primary key (UserID)      /*设置主键*/
);

  insert into sportUser values('u45153','u45153','管理员');
  insert into sportUser values('123','123','普通用户');

    

rem ****************************************************
rem ** 创建参赛班级表                                    /*此次运动会涉及到06，05，04 共12个班级*/
rem ****************************************************
create table Class (
  ClassID    char(5)   not null,            /*班级编号*/
  ClassName  varchar2(15) not null,          /*班级名称*/
  UpperNo    char(3)      null,              /*上级部门*/
  monintor   varchar2(8)  not null,           /*负责人姓名*/
  telephone  varchar2(15)  not null,   /*联系电话*/ 
constraint Class_PK primary key (ClassID)      /*设置主键*/
);

 insert into Class  values('001','江西财经大学','000','   ','13715620461');
 insert into Class  values('002','信息管理学院','001','  ','13517002937');
 insert into Class  values('003','电子学院','001','  ','13517002937');
 insert into Class  values('004','电子1班','003','王三','13517002936');
 insert into Class  values('005','06计1','002','张三','13715620461');
 insert into Class  values('006','06计2','002','张四','13715620462');
 insert into Class  values('007','06信管1','002','张六','13715620463');
 insert into Class  values('008','06信管2','002','张七','13715620464');

 insert into Class  values('009','05计1','002','李三','13715620465');
 insert into Class  values('010','05计2','002','李四','13715620466');
 insert into Class  values('011','05信管1','002','李五','13715620467');
 insert into Class  values('012','05信管2','002','李六','13715620468');

rem ****************************************************
rem ** 创建运动员表
rem ****************************************************
create table Athlete(
     AthleteID       char(6)    not null,                     /*运动员编号*/
     AthleteName     varchar2(10)   not null,                        /*运动员姓名*/
     sex             varchar2(3)  check(sex in ('男','女'))  not null,  
     ClassID       char(5)    not   null,                      /*所在班级*/ 
     PersonScore    number(2)    null,            
     constraint Athlete_PK primary key (AthleteID),
     constraint Athlete_FK1 foreign key(ClassID) references class(classID)
);
rem *****************************************************
rem ** 创建触发器
rem ** 功能：当插入运动员编号时，编号必须满足为数字
rem ******************************************************

CREATE or REPLACE TRIGGER AthleteIns
before insert
ON Athlete
FOR each row
declare
  myError   EXCEPTION;  /*出错处理*/
  varLeft   char(3);    /*编号前3位*/
  varMid    char(4);    /*编号第4位*/
  varRight  char(2);    /*编号后2位*/
  i         number;
begin
  if LENGTH(:new.AthleteID) !=6 then
     RAISE myError;
  end if;
  varLeft  :=  SUBSTR(:new.AthleteID,1,3);
  varMid   :=  SUBSTR(:new.AthleteID,4,1);
  varRight :=  SUBSTR(:new.AthleteID,5,2);
  if varMid != '-' then
     RAISE myError;
  end if;
  for i in 1..3
  loop
    if (SUBSTR(varLeft,i,1)<'0' or SUBSTR(varLeft,i,1)>'9') then
       RAISE myError;
       exit;
    end if;
  end loop;
  for i in 1..2
  loop
    if (SUBSTR(varRight,i,1)<'0' or SUBSTR(varRight,i,1)>'9') then
       RAISE myError;
       exit;
    end if;
  end loop;
  EXCEPTION
    WHEN myError
    THEN
      :new.AthleteID := NULL;
end;
/
/
rem *****************************************************
rem ** 创建触发器
rem ** 功能：当修改运动员编号时，编号必须满足为数字
rem ******************************************************

CREATE or REPLACE TRIGGER AthleteUpdate
before update of AthleteID
ON Athlete
FOR each row
declare
  myError   EXCEPTION;  /*出错处理*/
  varLeft   char(3);    /*编号前3位*/
  varMid    char(4);    /*编号第4位*/
  varRight  char(2);    /*编号后2位*/
  i         number;
begin
  if LENGTH(:new.AthleteID) !=6 then
     RAISE myError;
  end if;
  varLeft  :=  SUBSTR(:new.AthleteID,1,3);
  varMid   :=  SUBSTR(:new.AthleteID,4,1);
  varRight :=  SUBSTR(:new.AthleteID,5,2);
  if varMid != '-' then
     RAISE myError;
  end if;
  for i in 1..3
  loop
    if (SUBSTR(varLeft,i,1)<'0' or SUBSTR(varLeft,i,1)>'9') then
       RAISE myError;
       exit;
    end if;
  end loop;
  for i in 1..2
  loop
    if (SUBSTR(varRight,i,1)<'0' or SUBSTR(varRight,i,1)>'9') then
       RAISE myError;
       exit;
    end if;
  end loop;
  EXCEPTION
    WHEN myError
    THEN
   :new.AthleteID   := :old.AthleteID;
      :new.AthleteName := :old.AthleteName;
      :new.sex          := :old.sex;
      :new.classID := :old.ClassID;
      :new.PersonScore     := :old.PersonScore;
end;
/
insert into Athlete  values('004-01','小小','女','004',0);
insert into Athlete   values('005-01','陈小幻','男','005',0);
insert into Athlete   values('005-02','付 爽' ,'男','005',0);
insert into Athlete   values('006-01','李 里' ,'男','006',0);
insert into Athlete   values('007-01','陈小灵' ,'女','007',0);

insert into Athlete   values('008-01','陈小东','男','008',0);
insert into Athlete   values('008-02','刘 爽' ,'男','008',0);

insert into Athlete   values('009-01','陈小名','男','009',0);
insert into Athlete   values('009-02','付林' ,'男','009',0);
insert into Athlete   values('009-03','李有才' ,'女','009',0);

insert into Athlete   values('010-01','陈幻','男','010',0);

insert into Athlete   values('012-01','陈小只','男','012',0);
insert into Athlete   values('012-02','寒爽' ,'男','012',0);