oracle数据库建表及增加主键、索引、约束、授权等操作
-- Create table
create table MCCDATA.MCC_PUSH_CLUE
(
external_id VARCHAR2(10) not null,
call_event_id VARCHAR2(20) not null,
client_phone VARCHAR2(20) not null,
client_name VARCHAR2(50) not null,
client_sex VARCHAR2(1),
branch_code VARCHAR2(10) not null,
valid_flag VARCHAR2(1) not null,
updated_user VARCHAR2(50) not null,
updated_date DATE not null,
created_user VARCHAR2(50) not null,
created_date DATE not null,
task_status VARCHAR2(1),
push_time DATE,
deal_user VARCHAR2(50),
push_status VARCHAR2(1)
)
tablespace MCCTBS
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 1M
next 1M
minextents 1
maxextents unlimited
pctincrease 0
);
-- Add comments to the table
comment on table MCCDATA.MCC_PUSH_CLUE
is '线索系统推送记录';
-- Add comments to the columns
comment on column MCCDATA.MCC_PUSH_CLUE.external_id
is '外部渠道线索id';
comment on column MCCDATA.MCC_PUSH_CLUE.call_event_id
is '呼入事件id';
comment on column MCCDATA.MCC_PUSH_CLUE.client_phone
is '客户电话';
comment on column MCCDATA.MCC_PUSH_CLUE.client_name
is '客户姓名';
comment on column MCCDATA.MCC_PUSH_CLUE.client_sex
is '客户性别:1男, 2女';
comment on column MCCDATA.MCC_PUSH_CLUE.branch_code
is '机构代码';
comment on column MCCDATA.MCC_PUSH_CLUE.valid_flag
is '有效标志';
comment on column MCCDATA.MCC_PUSH_CLUE.updated_user
is '修改人';
comment on column MCCDATA.MCC_PUSH_CLUE.updated_date
is '修改日期';
comment on column MCCDATA.MCC_PUSH_CLUE.created_user
is '录入人';
comment on column MCCDATA.MCC_PUSH_CLUE.created_date
is '录入日期';
comment on column MCCDATA.MCC_PUSH_CLUE.task_status
is '任务状态0-待分配,1-处理中,2-复核通过,3-撤销';
comment on column MCCDATA.MCC_PUSH_CLUE.push_time
is '推送时间';
comment on column MCCDATA.MCC_PUSH_CLUE.deal_user
is '处理人';
comment on column MCCDATA.MCC_PUSH_CLUE.push_status
is '推送状态0-推送失败,1-推送成功';
-- Create/Recreate indexes
create index MCCDATA.IX_MCC_PUSH_CLUE_PHONE on MCCDATA.MCC_PUSH_CLUE (CLIENT_PHONE)
tablespace MCCTBS
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 1M
next 1M
minextents 1
maxextents unlimited
pctincrease 0
);
-- Create/Recreate primary, unique and foreign key constraints
alter table MCCDATA.MCC_PUSH_CLUE
add constraint PK_MCC_PUSH_CLUE primary key (EXTERNAL_ID)
using index
tablespace MCCTBS
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 1M
next 1M
minextents 1
maxextents unlimited
pctincrease 0
);
alter table MCCDATA.MCC_PUSH_CLUE
add constraint FK_MCC_PUSH_CLUE_CEID foreign key (CALL_EVENT_ID)
references MCCDATA.MCC_CALL_EVENT (CALL_EVENT_ID);
-- Create/Recreate check constraints
alter table MCCDATA.MCC_PUSH_CLUE
add constraint CK_MCC_PUSH_CLUE_PT
check (PUSH_STATUS in ('0','1'));
alter table MCCDATA.MCC_PUSH_CLUE
add constraint CK_MCC_PUSH_CLUE_TS
check (TASK_STATUS in ('0','1','2','3'));
alter table MCCDATA.MCC_PUSH_CLUE
add constraint CK_MCC_PUSH_CLUE_V
check (valid_flag in('Y','N'));
-- Grant/Revoke object privileges
grant select, insert, update on MCCDATA.MCC_PUSH_CLUE to MCCOPR;