oracle运用(二) oracle数据库完整建表语句

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
--判断表是否存在，如果存在则删除
declare
num
number;
begin
select count(1) into num from all_tables where TABLE_NAME = 'LOGISTICS_SUGGESTION';
--
and OWNER='SCOTT'
if
num=1
then
execute immediate 'drop table LOGISTICS_SUGGESTION';
end
if;
end;
-- Create table
CREATE TABLE LOGISTICS_SUGGESTION
(
id
NUMBER not null,
industry_id
NUMBER,
contact_name
VARCHAR2(128),
contact_phone
VARCHAR2(128),
handle_status
VARCHAR2(32),
suggestion_remark VARCHAR2(500),
handle_remark
VARCHAR2(500),
create_time
DATE default sysdate,
handle_time
DATE
);
--指定表空间(可以不指定)
tablespace DSW
pctfree 10
pctused 40
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
-- Add comments to the table
comment on table LOGISTICS_SUGGESTION is '投诉建议表';
-- Add comments to the columns
comment on column LOGISTICS_SUGGESTION.id is '自增，主键';
comment on column LOGISTICS_SUGGESTION.industry_id is '行业类别';
comment on column LOGISTICS_SUGGESTION.contact_name is '联系人';
comment on column LOGISTICS_SUGGESTION.contact_phone is '联系方式';
comment on column LOGISTICS_SUGGESTION.handle_status is '处理状态（处理中WORKING、已处理FINISH）';
comment on column LOGISTICS_SUGGESTION.suggestion_remark is '投诉建议';
comment on column LOGISTICS_SUGGESTION.handle_remark is '处理备注';
comment on column LOGISTICS_SUGGESTION.create_time is '创建时间';
comment on column LOGISTICS_SUGGESTION.handle_time is '处理时间';
-- Create/Recreate primary, unique and foreign key constraints
alter table LOGISTICS_SUGGESTION
add constraint PK_LOGISTICS_SUGGESTION primary key (ID)
using index
tablespace SYSTEM
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
-- Create sequence
create sequence LOGISTICS_SUGGESTION_SEQ
minvalue 1
-- 最小值=1
maxvalue 999999999999999999999999999
-- 指定最大值
-- 或nomaxvalue
-- 没有最大值
-- NOCYCLE;
-- 不循环
start with 1
-- 从1开始
increment by 1
-- 每次递增1
cache 20;
--触发器
create or replace trigger LOGISTICS_SUGGESTION_TRI
before insert on LOGISTICS_SUGGESTION
REFERENCING OLD AS "OLD" NEW AS "NEW" FOR EACH ROW
begin
SELECT LOGISTICS_SUGGESTION_SEQ.NEXTVAL INTO :NEW.ID FROM DUAL;
end;