-- Create table
create table H_BASIC
(
yuangongid VARCHAR2(10) not null,
name VARCHAR2(20) not null,
sex VARCHAR2(3) not null,
height NUMBER(3),
cardid NUMBER(18) not null,
xueli VARCHAR2(15) not null,
depart VARCHAR2(30) not null,
birthday DATE not null,
school VARCHAR2(50) not null,
schooldate DATE not null,
zhuanye VARCHAR2(30) not null,
telphone NUMBER(11) not null,
minzu VARCHAR2(12),
zhuangtai VARCHAR2(6) not null,
zhengzhimianmao VARCHAR2(6),
techang VARCHAR2(21),
address VARCHAR2(80) not null,
createtime DATE,
jibie NUMBER(1) not null,
"E-MAIL" VARCHAR2(30),
mima VARCHAR2(10) not null,
bankcard NUMBER(20),
contractid VARCHAR2(10)
)
tablespace USERS
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
-- Add comments to the columns
comment on column H_BASIC.yuangongid
is '员工编号(规则)（主键）';
comment on column H_BASIC.name
is '员工姓名';
comment on column H_BASIC.sex
is '年龄';
comment on column H_BASIC.height
is '身高';
comment on column H_BASIC.cardid
is '身份证号';
comment on column H_BASIC.xueli
is '学历';
comment on column H_BASIC.depart
is '部门';
comment on column H_BASIC.birthday
is '生日';
comment on column H_BASIC.school
is '毕业院校';
comment on column H_BASIC.schooldate
is '毕业时间';
comment on column H_BASIC.zhuanye
is '专业';
comment on column H_BASIC.telphone
is '电话';
comment on column H_BASIC.minzu
is '民族';
comment on column H_BASIC.zhuangtai
is '状态(1：在职/2：兼职/3：离职)';
comment on column H_BASIC.zhengzhimianmao
is '政治面貌';
comment on column H_BASIC.techang
is '特长';
comment on column H_BASIC.address
is '住址';
comment on column H_BASIC.createtime
is '创建时间';
comment on column H_BASIC.jibie
is '级别';
comment on column H_BASIC."E-MAIL"
is '邮件';
comment on column H_BASIC.mima
is '密码';
comment on column H_BASIC.bankcard
is '银行卡号';
comment on column H_BASIC.contractid
is '合同编号';
-- Create/Recreate primary, unique and foreign key constraints
alter table H_BASIC
add constraint PK_YGID primary key (YUANGONGID)
using index
tablespace USERS
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
alter table H_BASIC
add constraint FK_CONTRACTID foreign key (CONTRACTID)
references H_CONTRACT (CONTRACTID);
-- Create/Recreate check constraints
alter table H_BASIC
add constraint SEX
check (SEX='男'or SEX='女');
-- Create table
create table H_CONTRACT
(
yuangongid NUMBER(6) not null,
contractid VARCHAR2(10) not null,
starttime DATE not null,
overtime DATE not null,
shiyongtime VARCHAR2(15) default 3,
shiyongmoney NUMBER(6,2) not null,
zhuanzhengmoney NUMBER(6,2) not null,
contracttype VARCHAR2(9)
)
tablespace USERS
pctfree 10
initrans 1
maxtrans 255;
-- Add comments to the columns
comment on column H_CONTRACT.yuangongid
is '员工工号';
comment on column H_CONTRACT.contractid
is '合同编号(主键)';
comment on column H_CONTRACT.starttime
is '合同开始时间';
comment on column H_CONTRACT.overtime
is '合同结束时间';
comment on column H_CONTRACT.shiyongtime
is '试用期';
comment on column H_CONTRACT.shiyongmoney
is '试用期工资';
comment on column H_CONTRACT.zhuanzhengmoney
is '转正后工资';
comment on column H_CONTRACT.contracttype
is '合同类型(1：签合同/2：续合同)';
-- Create/Recreate primary, unique and foreign key constraints
alter table H_CONTRACT
add constraint PK_CONTRACTID primary key (CONTRACTID)
using index
tablespace USERS
pctfree 10
initrans 2
maxtrans 255;
-- Create table
create table H_HUSER
(
userid VARCHAR2(21) not null,
password VARCHAR2(21) not null,
username VARCHAR2(15) not null,
zhuangtai VARCHAR2(1)
)
tablespace USERS
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
-- Add comments to the columns
comment on column H_HUSER.userid
is '登录名';
comment on column H_HUSER.password
is 'µÇ½ÃÜÂë';
comment on column H_HUSER.username
is '̞';
comment on column H_HUSER.zhuangtai
is '人员状态(1：使用2：停用3：离职)';
-- Create/Recreate primary, unique and foreign key constraints
alter table H_HUSER
add constraint PK_USERID primary key (USERID)
using index
tablespace USERS
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
-- Create table
create table H_JIANGCHENG
(
yuangongid NUMBER(10) not null,
type VARCHAR2(3) not null,
time DATE not null,
title VARCHAR2(21) not null,
neirong VARCHAR2(80),
jcid VARCHAR2(4) not null
)
tablespace USERS
pctfree 10
initrans 1
maxtrans 255;
-- Add comments to the columns
comment on column H_JIANGCHENG.yuangongid
is '员工工号';
comment on column H_JIANGCHENG.type
is '奖惩类别';
comment on column H_JIANGCHENG.time
is '奖惩时间';
comment on column H_JIANGCHENG.title
is '标题';
comment on column H_JIANGCHENG.neirong
is '奖惩内容';
comment on column H_JIANGCHENG.jcid
is '奖惩编号(主键)';
-- Create/Recreate primary, unique and foreign key constraints
alter table H_JIANGCHENG
add constraint PK_YUANGONG primary key (JCID)
using index
tablespace USERS
pctfree 10
initrans 2
maxtrans 255;
-- Create table
create table H_WORKING
(
yuangongid NUMBER(6) not null,
company VARCHAR2(50) not null,
depart VARCHAR2(30) not null,
worktype VARCHAR2(30) not null,
score VARCHAR2(50) not null,
starttime DATE not null,
overtime DATE not null,
workid VARCHAR2(4) not null
)
tablespace USERS
pctfree 10
initrans 1
maxtrans 255;
-- Add comments to the columns
comment on column H_WORKING.yuangongid
is '员工工号';
comment on column H_WORKING.company
is '公司名称';
comment on column H_WORKING.depart
is '部门';
comment on column H_WORKING.worktype
is '工作内容';
comment on column H_WORKING.score
is '业绩';
comment on column H_WORKING.starttime
is '开始时间';
comment on column H_WORKING.overtime
is '结束时间';
comment on column H_WORKING.workid
is '履历id号';
-- Create/Recreate primary, unique and foreign key constraints
alter table H_WORKING
add constraint PK_WORDID primary key (WORKID)
using index
tablespace USERS
pctfree 10
initrans 2
maxtrans 255;
