rsgl.sql

主要是对人员信息和工资进行管理

prompt PL/SQL Developer import file
prompt Created on 2006年3月20日 by yb
set feedback off
set define off
prompt Dropping COUNTER...
drop table COUNTER cascade constraints;
prompt Dropping DEPARTMENT...
drop table DEPARTMENT cascade constraints;
prompt Dropping EDU_LEVEL...
drop table EDU_LEVEL cascade constraints;
prompt Dropping JOB...
drop table JOB cascade constraints;
prompt Dropping PERSON...
drop table PERSON cascade constraints;
prompt Dropping PERSONNEL...
drop table PERSONNEL cascade constraints;
prompt Dropping PERSONNEL_CHANGE...
drop table PERSONNEL_CHANGE cascade constraints;
prompt Creating COUNTER...
create table COUNTER
(
  ID            CHAR(1) not null,
  COUNTER_VALUE INTEGER not null,
  DESCRIPT      VARCHAR2(10)
)
;
comment on column COUNTER.ID
  is '计数器';
comment on column COUNTER.COUNTER_VALUE
  is '计数值';
comment on column COUNTER.DESCRIPT
  is '描述';

alter table COUNTER
  add constraint COUNTER_ID primary key (ID);

prompt Creating DEPARTMENT...
create table DEPARTMENT
(
  ID      CHAR(3) not null,
  NAME    VARCHAR2(20),
  MANAGER CHAR(6),
  INTRO   VARCHAR2(4000)
)
;
comment on column DEPARTMENT.ID
  is '部门编号';
comment on column DEPARTMENT.NAME
  is '部门名称';
comment on column DEPARTMENT.MANAGER
  is '部门经理';
comment on column DEPARTMENT.INTRO
  is '简介';
alter table DEPARTMENT
  add constraint DEPARTMENTID primary key (ID);

prompt Creating EDU_LEVEL...
create table EDU_LEVEL
(
  CODE     CHAR(1) not null,
  DESCRIPT VARCHAR2(20) not null
)
;
comment on column EDU_LEVEL.CODE
  is '代码';
comment on column EDU_LEVEL.DESCRIPT
  is '描述';
alter table EDU_LEVEL
  add constraint EDU_CODE primary key (CODE);

prompt Creating JOB...
create table JOB
(
  CODE     CHAR(1) not null,
  DESCRIPT VARCHAR2(20)
)
;
comment on column JOB.CODE
  is '代码';
comment on column JOB.DESCRIPT
  is '描述';
alter table JOB
  add constraint JOBCODE primary key (CODE);

prompt Creating PERSON...
create table PERSON
(
  ID         CHAR(6) not null,
  PASSWD     CHAR(25) not null,
  AUTHORITY  CHAR(1),
  NAME       VARCHAR2(10),
  SEX        CHAR(1) default 'M' not null,
  BIRTHDAY   DATE,
  DEPARTMENT CHAR(3),
  EDU_LEVEL  CHAR(1),
  SPECIALTY  VARCHAR2(20),
  ADDRESS    VARCHAR2(50),
  TEL        VARCHAR2(20),
  EMAIL      VARCHAR2(50),
  STATE      CHAR(1),
  REMARK     VARCHAR2(4000),
  JOB        CHAR(1)
)
;
comment on column PERSON.ID
  is '员工号';
comment on column PERSON.PASSWD
  is '密码';
comment on column PERSON.AUTHORITY
  is '用户权限';
comment on column PERSON.NAME
  is '姓名';
comment on column PERSON.SEX
  is '性别';
comment on column PERSON.BIRTHDAY
  is '生日';
comment on column PERSON.DEPARTMENT
  is '所在部门';
comment on column PERSON.EDU_LEVEL
  is '受教育程度';
comment on column PERSON.SPECIALTY
  is '专业技能';
comment on column PERSON.ADDRESS
  is '家庭住址';
comment on column PERSON.TEL
  is '联系电话';
comment on column PERSON.EMAIL
  is '电子邮箱';
comment on column PERSON.STATE
  is '当前状态T在职F离职';
comment on column PERSON.REMARK
  is '备注';
comment on column PERSON.JOB
  is '职务';
alter table PERSON
  add constraint PERSONID primary key (ID);
alter table PERSON
  add constraint DEPARTMENTCODE foreign key (DEPARTMENT)
  references DEPARTMENT (ID);
alter table PERSON
  add constraint EDUCODE foreign key (EDU_LEVEL)
  references EDU_LEVEL (CODE);
alter table PERSON
  add constraint JOB foreign key (JOB)
  references JOB (CODE);
alter table PERSON
  add constraint SEX
  check (sex='M' or sex='F');

prompt Creating PERSONNEL...
create table PERSONNEL
(
  ID          INTEGER not null,
  PERSONID    CHAR(6) not null,
  CHANGECODE  CHAR(1) not null,
  DESCRIPT    VARCHAR2(4000),
  RECORD_TIME DATE not null
)
;
comment on column PERSONNEL.ID
  is '记录编号';
comment on column PERSONNEL.PERSONID
  is '员工号';
comment on column PERSONNEL.CHANGECODE
  is '变更代码';
comment on column PERSONNEL.DESCRIPT
  is '详细记录';
comment on column PERSONNEL.RECORD_TIME
  is '记录';
alter table PERSONNEL
  add constraint PERSONNEL_ID primary key (ID);
alter table PERSONNEL
  add constraint PERSON1 foreign key (PERSONID)
  references PERSON (ID);

prompt Creating PERSONNEL_CHANGE...
create table PERSONNEL_CHANGE
(
  CODE     CHAR(1) not null,
  DESCRIPT VARCHAR2(20)
)
;
comment on column PERSONNEL_CHANGE.CODE
  is '代码';
comment on column PERSONNEL_CHANGE.DESCRIPT
  is '描述';
alter table PERSONNEL_CHANGE
  add constraint PERSONNEL_CODE primary key (CODE);

prompt Loading COUNTER...
insert into COUNTER (ID, COUNTER_VALUE, DESCRIPT)
values ('P', 2, null);
insert into COUNTER (ID, COUNTER_VALUE, DESCRIPT)
values ('C', 2, null);
commit;
prompt 2 records loaded
prompt Loading DEPARTMENT...
insert into DEPARTMENT (ID, NAME, MANAGER, INTRO)
values ('0  ', '设计部', null, null);
insert into DEPARTMENT (ID, NAME, MANAGER, INTRO)
values ('1  ', '公关部', null, null);
insert into DEPARTMENT (ID, NAME, MANAGER, INTRO)
values ('2  ', '财务部', null, null);
insert into DEPARTMENT (ID, NAME, MANAGER, INTRO)
values ('3  ', '销售部', null, null);
insert into DEPARTMENT (ID, NAME, MANAGER, INTRO)
values ('4  ', '技术部', null, null);
insert into DEPARTMENT (ID, NAME, MANAGER, INTRO)
values ('5  ', '信息中心', null, null);
commit;
prompt 6 records loaded
prompt Loading EDU_LEVEL...
insert into EDU_LEVEL (CODE, DESCRIPT)
values ('0', '小学');
insert into EDU_LEVEL (CODE, DESCRIPT)
values ('1', '初中');
insert into EDU_LEVEL (CODE, DESCRIPT)
values ('2', '高中');
insert into EDU_LEVEL (CODE, DESCRIPT)
values ('3', '职高');
insert into EDU_LEVEL (CODE, DESCRIPT)
values ('4', '大专');
insert into EDU_LEVEL (CODE, DESCRIPT)
values ('5', '本科');
insert into EDU_LEVEL (CODE, DESCRIPT)
values ('6', '硕士');
insert into EDU_LEVEL (CODE, DESCRIPT)
values ('7', '博士');
insert into EDU_LEVEL (CODE, DESCRIPT)
values ('8', '博士后');
commit;
prompt 9 records loaded
prompt Loading JOB...
insert into JOB (CODE, DESCRIPT)
values ('0', '总经理');
insert into JOB (CODE, DESCRIPT)
values ('1', '总经理助理');
insert into JOB (CODE, DESCRIPT)
values ('2', '部门主管');
insert into JOB (CODE, DESCRIPT)
values ('3', '会计师');
insert into JOB (CODE, DESCRIPT)
values ('4', '高工');
insert into JOB (CODE, DESCRIPT)
values ('5', '员工');
commit;
prompt 6 records loaded
prompt Loading PERSON...
insert into PERSON (ID, PASSWD, AUTHORITY, NAME, SEX, BIRTHDAY, DEPARTMENT, EDU_LEVEL, SPECIALTY, ADDRESS, TEL, EMAIL, STATE, REMARK, JOB)
values ('P00001', 'BXCHAWAJGWEWINCDBJAF     ', '3', '黄药师', 'M', null, '5  ', null, null, null, null, null, null, null, '2');
insert into PERSON (ID, PASSWD, AUTHORITY, NAME, SEX, BIRTHDAY, DEPARTMENT, EDU_LEVEL, SPECIALTY, ADDRESS, TEL, EMAIL, STATE, REMARK, JOB)
values ('P00002', 'BXCHAWAJGWEWINCDBJAF     ', '3', '杨过', 'M', to_date('12-12-1973', 'dd-mm-yyyy'), '0  ', '6', '计算机应用', '江西财经大学', '3891481', '[e-mail]', 'T', '挚爱小龙女,天若有情，天亦老！', '4');
commit;
prompt 2 records loaded
prompt Loading PERSONNEL...
insert into PERSONNEL (ID, PERSONID, CHANGECODE, DESCRIPT, RECORD_TIME)
values (1, 'P00001', '1', 'sdcascasc', to_date('16-03-2006', 'dd-mm-yyyy'));
insert into PERSONNEL (ID, PERSONID, CHANGECODE, DESCRIPT, RECORD_TIME)
values (2, 'P00002', '0', '加入公司', to_date('19-03-2006 18:51:42', 'dd-mm-yyyy hh24:mi:ss'));
commit;
prompt 2 records loaded
prompt Loading PERSONNEL_CHANGE...
insert into PERSONNEL_CHANGE (CODE, DESCRIPT)
values ('0', '新员工加入');
insert into PERSONNEL_CHANGE (CODE, DESCRIPT)
values ('1', '职务变动');
insert into PERSONNEL_CHANGE (CODE, DESCRIPT)
values ('2', '辞退');
insert into PERSONNEL_CHANGE (CODE, DESCRIPT)
values ('3', '退休');
insert into PERSONNEL_CHANGE (CODE, DESCRIPT)
values ('4', '调离');
commit;
prompt 5 records loaded
set feedback on
set define on
prompt Done.