0045153数据库脚本.txt

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

rem ****************************************************
rem ** 创建裁判人员表
rem ****************************************************
create table judge(
     JudgeID       char(3)    not null,                     /*裁判编号*/
     JudgeName     varchar2(10)   not null,                        /*裁判员姓名*/
      sex             varchar2(5)  check(sex in ('男','女'))  not null,  
     telephone       varchar2(20)   not null,                      /*联系方法*/
     constraint Judge_PK primary key (JudgeID) 
);
 
  insert into Judge values('J01','刘一','男','13815279510');
  insert into Judge values('J02','刘二','女','13815279511');
  insert into Judge values('J03','刘三','女','13815279512');
  insert into Judge values('J04','刘四','女','13815279513');
  insert into Judge values('J05','刘五','女','13815279515');
  insert into Judge values('J06','刘六','男','13815279526');
  insert into Judge values('J07','刘七','男','13815279516');
  insert into Judge values('J08','刘八','女','13815279517');
  insert into Judge values('J09','刘九','女','13815279518');
  insert into Judge values('J10','曾一','男','13815279519');
  insert into Judge values('J11','曾二','男','13815279520');
  insert into Judge values('J12','曾三','女','13815279521');



rem ******************************************************
rem ** 创建赛程安排表                                              /*所有的比赛仅预赛和决赛*/
rem ******************************************************
create table MatchArrangment(
    MatchID       char(3)   not null,                                                  /*项目编号*/
    MatchName     varchar2(20)   not null,                                           /*项目名称*/
    MatchType     varchar2(4)   check(MatchType in('预赛','决赛'))   not null ,          /*赛事类型*/  
    JudgeID       char(3) not null,                                                   /*裁判员编号*/
    Time          date   not null,                                                  /*项目开始时*/
    places        varchar2(20) not null,                                              /*项目场地*/      
    constraint MatchArrangment_PK primary key (MatchID),
    constraint Judge_FK1 foreign key(JudgeID) references Judge(JudgeID)
);

insert into MatchArrangment values('m01','男子100米','预赛','J01',to_date('2006 10 10 ','yyyy mm dd '),'跑道');

insert into MatchArrangment values('m02','男子200米','预赛','J02',to_date('2006 10 10 ','yyyy mm dd'),'跑道');

insert into MatchArrangment values('m03','男子400米','预赛','J03',to_date('2006 10 10 ','yyyy mm dd'),'跑道');

insert into MatchArrangment values('m04','男子800米','预赛','J04',to_date('2006 10 10 ','yyyy mm dd'),'跑道');

insert into MatchArrangment values('m05','女子100米','预赛','J01',to_date('2006 10 11 ','yyyy mm dd'),'跑道');

insert into MatchArrangment values('m06','女子200米','预赛','J02',to_date('2006 10 11 ','yyyy mm dd'),'跑道');

insert into MatchArrangment values('m07','女子400米','预赛','J03',to_date('2006 10 11 ','yyyy mm dd'),'跑道');


insert into MatchArrangment values('m08','男子100米','决赛','J01',to_date('2006 10 12 ','yyyy mm dd'),'跑道');

insert into MatchArrangment values('m09','男子200米','决赛','J02',to_date('2006 10 12 ','yyyy mm dd'),'跑道');

insert into MatchArrangment values('m10','男子400米','决赛','J03',to_date('2006 10 12 ','yyyy mm dd'),'跑道');

insert into MatchArrangment values('m11','男子800米','决赛','J04',to_date('2006 10 13 ','yyyy mm dd'),'跑道');

insert into MatchArrangment values('m12','女子100米','决赛','J01',to_date('2006 10 13 ','yyyy mm dd'),'跑道');



rem ****************************************************
rem ** 创建运动员参赛表                          /*初赛名单*/
rem ****************************************************
create table PersonMatch(
   AthleteID   char(6) not null,
   AthleteName  varchar2(6)  not null,
   MatchName    varchar2(12)  not null,
   Matchtype    varchar2(12)   not null,
 constraint PersonMatch_PK primary key (AthleteID) 
);

insert into PersonMatch   values('005-01','陈小幻','男子100','预赛');
insert into PersonMatch   values('006-01','付 爽' ,'男子200米','预赛');

insert into PersonMatch  values('007-01','陈小名' ,'男子200米','预赛');
insert into PersonMatch   values('008-01','李 里' ,'男子400米','预赛');
insert into PersonMatch  values('009-01','李有才','女子100米','预赛');

rem ****************************************************
rem ** 破记录表                                        
rem ****************************************************

create table RecordBreaking(
MatchID       char(3)      not null,                                                  /*打破记录的项目编*/
MatchName     varchar2(20)   not null,                                                /*打破记录的项目名*/
AthleteName   varchar(8)     not null,                                                /*破记录者姓名*/
newRecord     varchar(20)    not null,                                                /*新的校园记录*/
constraint RecordBreaking_pk primary key(MatchID),
constraint  MatchArrangment_Fk1 foreign key(MatchID) references MatchArrangment(MatchID)
);

insert into RecordBreaking values('m01','男子100米','陈小幻','11秒20');
insert into RecordBreaking values('m02','男子200米','陈小名' ,'25秒40');

/
rem *****************************************************
rem ** 创建运动员其他信息和照片表
rem ******************************************************
CREATE TABLE AthleteOther(
  AthleteID 	char(6) 		    NOT NULL ,  /*编号*/
  AthleteMemo 	varchar2(1000) 		    NULL ,      /*简历*/
  AtheleteImage  blob                       null ,      /*照片*/
  constraint AthleteOther_PK primary key (AthleteID),
  constraint AthleteOther_FK1 foreign key(AthleteID) references Athlete(AthleteID)
);
/
rem *****************************************************
rem ** 产生运动员编号函数
rem ** 功能：根据输入的班级号，自动产生运动员编号
rem ** 输入参数：sClassID 班级号
rem ** 返回值：运动员编号
rem ******************************************************
CREATE or REPLACE FUNCTION funAthleteID(sClassID varchar2)
return varchar2
is
  x number;                             /*得到运动员编号后两位的流水号*/
  varAthleteID1 varchar2(8);            /*某班级的最大运动员编号*/
  varAthleteID2 varchar2(8);            /*新的运动员编号*/
begin
  varAthleteID1 :='';
  select NVL(max(AthleteID),'0') into varAthleteID1
  from Athlete
  where AthleteID like sClassID || '%';
  if varAthleteID1 = '0' then          /*没有该班级的运动员，编号从01开始*/
     varAthleteID2 := sClassID || '-01';
  else
    x := to_number(SUBSTR(varAthleteID1,5,2))+1;
    if x <10 then varAthleteID2 := sClassID || '-0' || ltrim(to_char(x));
    else
        varAthleteID2 := sClassID || '-' || ltrim(to_char(x));
    end if;
  end if;
  return(varAthleteID2);
end funAthleteID;
/
rem *****************************************************
rem ** 产生运动员编号过程
rem ** 功能：根据输入的班级，自动产生运动员编号
rem ** 输入参数：sAthleteID 班级
rem ** 输出参数：returnAthleteID 运动员编号
rem ******************************************************
CREATE or REPLACE PROCEDURE proAthleteID(sAthleteID varchar2,returnAthleteID OUT varchar2)
is
  x number;			/*得到运动员编号后两位的流水号*/
  varAthleteID1 varchar2(8);	/*某班级的最大运动员编号*/
  varAthleteID2 varchar2(8);   /*新的运动员编号*/
begin
  varAthleteID1 :='';
  select NVL(max(AthleteID),'0') into varAthleteID1
  from Athlete
  where AthleteID like sAthleteID || '%';
  if varAthleteID1 = '0' then   /*没有该班级的运动员，编号从01开始*/
     varAthleteID2 := sAthleteID || '-01';
  else
    x := to_number(SUBSTR(varAthleteID1,5,2))+1;
    if x <10 then varAthleteID2 := sAthleteID || '-0' || ltrim(to_char(x));
    else
        varAthleteID2 := sAthleteID || '-' || ltrim(to_char(x));
    end if;
  end if;
  returnAthleteID := varAthleteID2;
end proAthleteID;
/