oraclecode.txt

delphi renyuanguanlixinxioxitong

rem ****************************************************
rem **         大作业数据库设计
rem **
rem **  功能：建立企业的部门和员工信息
rem **  作者：张山
rem **  学号：9910234
rem **  首次创建时间：2001年4月20日
rem **  最后修改时间：2001年4月27日
rem ****************************************************

rem *****************************************************
rem **删除表
rem *****************************************************
drop table EmployeeOther;
drop table Employee;
drop table Department;
drop table DCNation;
drop table DCPost;
/

rem ****************************************************
rem ** 创建部门表
rem ****************************************************
CREATE TABLE Department (
	DepartmentNo 	     char(3) 	        NOT NULL ,  /*部门编号*/
	DepartmentName 	     varchar2(30) unique NULL,       /*部门名称*/
	UpperDepartmentNo    char(3)		NULL,       /*上级部门*/
        Manager              varchar(20)        NULL,       /*部门负责人*/
        Address              varchar2(30)       NULL,       /*部门地址*/
        Telephone            varchar2(20)       Null,       /*部门电话*/
        constraint Department_PK primary key (DepartmentNo) 
) ;


insert into Department values('001','江西财经大学','000', null,null,null);
insert into Department values('002','人事处',      '001',null,null,null);
insert into Department values('003','教务处',      '001',null,null,null);

insert into Department values('004','信息学院',  '001',null,null,null);
insert into Department values('005','信息系',    '004',null,null,null);
insert into Department values('006','信息研究室','005',null,null,null);
insert into Department values('007','计算机系',  '004',null,null,null);
insert into Department values('008','数量经济系','004',null,null,null);
insert into Department values('009','办公室',    '004',null,null,null);

insert into Department values('010','会计学院',   '001',null,null,null);
insert into Department values('011','会计系',     '010',null,null,null);
insert into Department values('012','会计教研室', '011',null,null,null);
insert into Department values('013','会计研究室', '012',null,null,null);
insert into Department values('014','政策研究室', '012',null,null,null);
insert into Department values('015','会电教研室', '011',null,null,null);
insert into Department values('016','审计系',     '010',null,null,null);
insert into Department values('017','审计教研室', '016',null,null,null);
insert into Department values('018','财务系',     '010',null,null,null);

insert into Department values('019','工商学院', '001',null,null,null);
insert into Department values('020','经济学院', '001',null,null,null);
insert into Department values('021','统计系',   '001',null,null,null);

rem ******************************************************
rem ** 创建民族表
rem ******************************************************
CREATE TABLE DCNation (
   NationNo 	char(3) 		                            NOT NULL ,      /*编号*/
   NationName 	varchar2(30)   UNIQUE	                            NULL ,          /*名称*/
   IfVoid       Number(1) default 0 check (IfVoid between 0 and 1)  NOT NULL,       /*是否作废*/
   constraint DCNation_PK primary key(NationNo)
) ;

insert into DCNation values('001','汉族',0);
insert into DCNation values('002','壮族',0);
insert into DCNation values('003','苗族',0);

rem ******************************************************
rem ** 创建岗位表
rem ******************************************************
CREATE TABLE DCPost (
   PostNo 	char(3) 		                            NOT NULL ,      /*编号*/
   PostName 	varchar2(30)   UNIQUE	                            NULL ,          /*名称*/
   IfVoid       Number(1) default 0 check (IfVoid between 0 and 1)  NOT NULL,       /*是否作废*/
   constraint DCPost_PK primary key(PostNo)
) ;

insert into DCPost values('001','校长',0);
insert into DCPost values('002','书记',0);
insert into DCPost values('003','院长',0);
insert into DCPost values('004','教师',0);

rem ******************************************************
rem ** 创建员工基本信息表
rem ******************************************************
CREATE TABLE Employee (
  EmployeeNo 	char(8) 			    NOT NULL ,      /*编号*/
  EmployeeName 	varchar2(30) 			    NOT NULL ,      /*姓名*/
  DepartmentNo  char(3)                             Not null ,      /*所属部门*/
  sex 	        varchar(2) check (sex in('男','女'))   NULL ,          /*性别*/
  EmployeeDate 	date 			            NULL ,          /*出生日期*/
  WorkDate 	date 			            NULL ,          /*工作日期*/
  NationNo      char(3)                             NULL,           /*民族*/
  PostNo        char(3)                             Null,           /*岗位*/
  constraint Employee_PK primary key (EmployeeNo),
  constraint EmployeeDepartment foreign key(DepartmentNo) references Department(DepartmentNo),
  constraint EmployeeNation foreign key(NationNo) references DCNation(NationNo),
  constraint EmployeePost   foreign key(PostNo)   references DCPost(PostNo)
) ;

rem ******************************************************
rem ** 创建员工简历和照片表
rem ******************************************************
CREATE TABLE EmployeeOther(
  EmployeeNo 	char(8) 		    NOT NULL ,  /*编号*/
  EmployeeMemo 	varchar2(1000) 		    NULL ,      /*简历*/
  EmployeeImage  blob                       null ,      /*照片*/
  constraint EmployeeImage_PK primary key (EmployeeNo),
  constraint EmployeeImageEmployeeFK1 foreign key(EmployeeNo) references Employee(EmployeeNo)
) ;

rem *****************************************************
rem ** 创建触发器
rem ** 功能：当插入编号时，编号必须满足为数字
rem ******************************************************
CREATE or REPLACE TRIGGER EmployeeIns
before insert
ON Employee
FOR each row
declare
  myError   EXCEPTION;  /*出错处理*/
  varLeft   char(4);    /*编号前4位*/
  varMid    char(4);    /*编号第5位*/
  varRight  char(3);    /*编号后3位*/
  i         number;
begin
  if LENGTH(:new.EmployeeNo) !=8 then
     RAISE myError;
  end if;
  varLeft  :=  SUBSTR(:new.EmployeeNo,1,4);
  varMid   :=  SUBSTR(:new.EmployeeNo,5,1);
  varRight :=  SUBSTR(:new.EmployeeNo,6,3);
  if varMid != '-' then
     RAISE myError;
  end if;
  for i in 1..4
  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..3
  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.EmployeeNo := NULL;
end;
/
/
rem *****************************************************
rem ** 创建触发器
rem ** 功能：当修改编号时，编号必须满足为数字
rem ******************************************************
CREATE or REPLACE TRIGGER EmployeeUpdate
before update of EmployeeNo
ON Employee
FOR each row
declare
  myError   EXCEPTION;  /*出错处理*/
  varLeft   char(4);    /*编号前4位*/
  varMid    char(4);    /*编号第5位*/
  varRight  char(3);    /*编号后3位*/
  i         number;
begin
  if LENGTH(:new.EmployeeNo) !=8 then
     RAISE myError;
  end if;
  varLeft  :=  SUBSTR(:new.EmployeeNo,1,4);
  varMid   :=  SUBSTR(:new.EmployeeNo,5,1);
  varRight :=  SUBSTR(:new.EmployeeNo,6,3);
  if varMid != '-' then
     RAISE myError;
  end if;
  for i in 1..4
  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..3
  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.EmployeeNo   := :old.EmployeeNo;
      :new.EmployeeName := :old.EmployeeName;
      :new.DepartmentNo := :old.DepartmentNo;
      :new.sex          := :old.sex;
      :new.EmployeeDate := :old.EmployeeDate;
      :new.WorkDate     := :old.WorkDate;
      :new.NationNo     := :old.NationNo;
      :new.PostNo       := :old.PostNo;
end;
/

insert into Employee values('1968-001','王红',  '001','女',TO_DATE('1952 12 21','YYYY MM DD'),TO_DATE('1968 12 21','YYYY MM DD'),'001','001');
insert into Employee values('1968-002','黎明',  '002','女',TO_DATE('1952 11 11','YYYY MM DD'),TO_DATE('1968 12 21','YYYY MM DD'),'001','002');
insert into Employee values('1970-001','涂洪',  '003','男',TO_DATE('1954 10 01','YYYY MM DD'),TO_DATE('1970 12 21','YYYY MM DD'),'001','003');
insert into Employee values('1974-001','王小兵','004','男',TO_DATE('1968 05 20','YYYY MM DD'),TO_DATE('1974 12 21','YYYY MM DD'),'002','004');
insert into Employee values('1975-001','黄奋',  '005','男',TO_DATE('1968 05 26','YYYY MM DD'),TO_DATE('1975 12 21','YYYY MM DD'),'002','004');
insert into Employee values('1988-001','李志强','006','男',TO_DATE('1971 12 21','YYYY MM DD'),TO_DATE('1988 12 21','YYYY MM DD'),'001','004');
insert into Employee values('1988-002','李立',  '007','女',TO_DATE('1971 10 21','YYYY MM DD'),TO_DATE('1988 12 21','YYYY MM DD'),'001','004');
insert into Employee values('1990-001','黄小红','008','女',TO_DATE('1972 01 09','YYYY MM DD'),TO_DATE('1990 12 21','YYYY MM DD'),'003','003');
insert into Employee values('1990-002','黄勇',  '009','男',TO_DATE('1972 11 21','YYYY MM DD'),TO_DATE('1990 12 21','YYYY MM DD'),'001','003');
insert into Employee values('1990-003','王红',  '010','男',TO_DATE('1972 07 15','YYYY MM DD'),TO_DATE('1990 12 21','YYYY MM DD'),'001','004');

insert into Employee values('2001-001','王丹',  '004','男',TO_DATE('1972 08 15','YYYY MM DD'),TO_DATE('2001 12 21','YYYY MM DD'),'001','001');
insert into Employee values('2001-002','王红',  '004','男',TO_DATE('1971 07 23','YYYY MM DD'),TO_DATE('2001 06 28','YYYY MM DD'),'001','004');
insert into Employee values('2002-001','蒋洪',  '010','女',TO_DATE('1973 05 25','YYYY MM DD'),TO_DATE('2002 07 21','YYYY MM DD'),'002','003');
insert into Employee values('2002-002','王正鸣','010','男',TO_DATE('1973 08 05','YYYY MM DD'),TO_DATE('2002 07 12','YYYY MM DD'),'003','003');
insert into Employee values('2002-003','杨杨欢','010','女',TO_DATE('1973 08 13','YYYY MM DD'),TO_DATE('2002 10 09','YYYY MM DD'),'003','003');
insert into Employee values('2002-004','陆路',  '010','男',TO_DATE('1974 09 18','YYYY MM DD'),TO_DATE('2002 11 30','YYYY MM DD'),'001','002');
insert into Employee values('2003-002','杨小兵','010','男',TO_DATE('1974 11 19','YYYY MM DD'),TO_DATE('2003 10 21','YYYY MM DD'),'001','004');


rem *****************************************************
rem ** 产生员工编号函数
rem ** 功能：根据输入的年份，自动产生员工编号
rem ** 输入参数：sYear 年份
rem ** 返回值：员工编号
rem ******************************************************
CREATE or REPLACE FUNCTION funEmployeeNo(sYear varchar2)
return varchar2
is
  x number;                             /*得到员工编号后三位的流水号*/
  varEmployeeNo1 varchar2(8);           /*某年份的最大员工编号*/
  varEmployeeNo2 varchar2(8);           /*新的员工编号*/
begin
  varEmployeeNo1 :='';
  select NVL(max(EmployeeNo),'0') into varEmployeeNo1
  from Employee
  where EmployeeNo like sYear || '%';
  if varEmployeeNo1 = '0' then          /*有该年份的员工，编号从001开始*/
     varEmployeeNo2 := sYear || '-001';
  else
    x := to_number(SUBSTR(varEmployeeNo1,6,3))+1;
    if x <10 then varEmployeeNo2 := sYear || '-00' || ltrim(to_char(x));
    elsif x<100 then varEmployeeNo2 := sYear || '-0' || ltrim(to_char(x));
    else
        varEmployeeNo2 := sYear || '-' || ltrim(to_char(x));
    end if;
  end if;
  return(varEmployeeNo2);
end funEmployeeNo;
/

rem *****************************************************
rem ** 产生员工编号过程
rem ** 功能：根据输入的年份，自动产生员工编号
rem ** 输入参数：sYear 年份
rem ** 输出参数：returnEmployeeNo 员工编号
rem ******************************************************
CREATE or REPLACE PROCEDURE proEmployeeNo(sYear varchar2,returnEmployeeNo OUT varchar2)
is
  x number;			/*得到员工编号后三位的流水号*/
  varEmployeeNo1 varchar2(8);	/*某年份的最大员工编号*/
  varEmployeeNo2 varchar2(8);   /*新的员工编号*/
begin
  varEmployeeNo1 :='';
  select NVL(max(EmployeeNo),'0') into varEmployeeNo1
  from Employee
  where EmployeeNo like sYear || '%';
  if varEmployeeNo1 = '0' then   /*有该年份的员工，编号从001开始*/
     varEmployeeNo2 := sYear || '-001';
  else
    x := to_number(SUBSTR(varEmployeeNo1,6,3))+1;
    if x <10 then varEmployeeNo2 := sYear || '-00' || ltrim(to_char(x));
    elsif x<100 then varEmployeeNo2 := sYear || '-0' || ltrim(to_char(x));
    else
        varEmployeeNo2 := sYear || '-' || ltrim(to_char(x));
    end if;
  end if;
  returnEmployeeNo := varEmployeeNo2;
end proEmployeeNo;
/

rem *****************************************************
rem ** 产生员工编号过程(使用动态SQL)
rem ** 功能：根据输入的年份，自动产生员工编号(用动态SQL语句)
rem ** 输入参数：sYear 年份
rem ** 输出参数：ReturnEmployeeNo 员工编号
rem ******************************************************
CREATE or REPLACE PROCEDURE proEmployeeNo1 (sYear varchar2,ReturnEmployeeNo out varchar2)
is
  x number;
  varEmployeeNo1 varchar2(8);
  varEmployeeNo2 varchar2(8);
  sql_stmt       varchar2(100);
  syear1         char(5);
begin
  varEmployeeNo1 :='';
  syear1 :=  sYear || '%' ;
  sql_stmt := 'select max(EmployeeNo) from Employee where EmployeeNo like :sYear';
  EXECUTE IMMEDIATE sql_stmt INTO varEmployeeNo1 USING syear1;
  if varEmployeeNo1 is null then   /*没有该年份的员工，编号从001开始*/
     varEmployeeNo2 := sYear || '001';
  else
    x := to_number(SUBSTR(varEmployeeNo1,6,3))+1;
    if x <10 then varEmployeeNo2 := sYear || '00' || ltrim(to_char(x));
    elsif x<100 then varEmployeeNo2 := sYear || '0' || ltrim(to_char(x));
    else
        varEmployeeNo2 := sYear || ltrim(to_char(x));
    end if;
  end if;
  returnEmployeeNo := varEmployeeNo2;
end proEmployeeNo1;
/