jeesite_oracle.sql
4.8 KB
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
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
/* Drop Tables */
DROP TABLE test_data;
DROP TABLE test_data_child;
DROP TABLE test_data_main;
DROP TABLE test_tree;
/* Create Tables */
CREATE TABLE test_data
(
id varchar2(64) NOT NULL,
user_id varchar2(64),
office_id varchar2(64),
area_id nvarchar2(64),
name nvarchar2(100),
sex char(1),
in_date date,
create_by varchar2(64) NOT NULL,
create_date timestamp NOT NULL,
update_by varchar2(64) NOT NULL,
update_date timestamp NOT NULL,
remarks nvarchar2(255),
del_flag char(1) DEFAULT '0' NOT NULL,
PRIMARY KEY (id)
);
CREATE TABLE test_data_child
(
id varchar2(64) NOT NULL,
test_data_main_id varchar2(64),
name nvarchar2(100),
create_by varchar2(64) NOT NULL,
create_date timestamp NOT NULL,
update_by varchar2(64) NOT NULL,
update_date timestamp NOT NULL,
remarks nvarchar2(255),
del_flag char(1) DEFAULT '0' NOT NULL,
PRIMARY KEY (id)
);
CREATE TABLE test_data_main
(
id varchar2(64) NOT NULL,
user_id varchar2(64),
office_id varchar2(64),
area_id nvarchar2(64),
name nvarchar2(100),
sex char(1),
in_date date,
create_by varchar2(64) NOT NULL,
create_date timestamp NOT NULL,
update_by varchar2(64) NOT NULL,
update_date timestamp NOT NULL,
remarks nvarchar2(255),
del_flag char(1) DEFAULT '0' NOT NULL,
PRIMARY KEY (id)
);
CREATE TABLE test_tree
(
id varchar2(64) NOT NULL,
parent_id varchar2(64) NOT NULL,
parent_ids varchar2(2000) NOT NULL,
name nvarchar2(100) NOT NULL,
sort number(10,0) NOT NULL,
create_by varchar2(64) NOT NULL,
create_date timestamp NOT NULL,
update_by varchar2(64) NOT NULL,
update_date timestamp NOT NULL,
remarks nvarchar2(255),
del_flag char(1) DEFAULT '0' NOT NULL,
PRIMARY KEY (id)
);
/* Create Indexes */
CREATE INDEX test_data_del_flag ON test_data (del_flag);
CREATE INDEX test_data_child_del_flag ON test_data_child (del_flag);
CREATE INDEX test_data_main_del_flag ON test_data_main (del_flag);
CREATE INDEX test_tree_del_flag ON test_tree (del_flag);
CREATE INDEX test_data_parent_id ON test_tree (parent_id);
CREATE INDEX test_data_parent_ids ON test_tree (parent_ids);
/* Comments */
COMMENT ON TABLE test_data IS '业务数据表';
COMMENT ON COLUMN test_data.id IS '编号';
COMMENT ON COLUMN test_data.user_id IS '归属用户';
COMMENT ON COLUMN test_data.office_id IS '归属部门';
COMMENT ON COLUMN test_data.area_id IS '归属区域';
COMMENT ON COLUMN test_data.name IS '名称';
COMMENT ON COLUMN test_data.sex IS '性别';
COMMENT ON COLUMN test_data.in_date IS '加入日期';
COMMENT ON COLUMN test_data.create_by IS '创建者';
COMMENT ON COLUMN test_data.create_date IS '创建时间';
COMMENT ON COLUMN test_data.update_by IS '更新者';
COMMENT ON COLUMN test_data.update_date IS '更新时间';
COMMENT ON COLUMN test_data.remarks IS '备注信息';
COMMENT ON COLUMN test_data.del_flag IS '删除标记';
COMMENT ON TABLE test_data_child IS '业务数据子表';
COMMENT ON COLUMN test_data_child.id IS '编号';
COMMENT ON COLUMN test_data_child.test_data_main_id IS '业务主表ID';
COMMENT ON COLUMN test_data_child.name IS '名称';
COMMENT ON COLUMN test_data_child.create_by IS '创建者';
COMMENT ON COLUMN test_data_child.create_date IS '创建时间';
COMMENT ON COLUMN test_data_child.update_by IS '更新者';
COMMENT ON COLUMN test_data_child.update_date IS '更新时间';
COMMENT ON COLUMN test_data_child.remarks IS '备注信息';
COMMENT ON COLUMN test_data_child.del_flag IS '删除标记';
COMMENT ON TABLE test_data_main IS '业务数据表';
COMMENT ON COLUMN test_data_main.id IS '编号';
COMMENT ON COLUMN test_data_main.user_id IS '归属用户';
COMMENT ON COLUMN test_data_main.office_id IS '归属部门';
COMMENT ON COLUMN test_data_main.area_id IS '归属区域';
COMMENT ON COLUMN test_data_main.name IS '名称';
COMMENT ON COLUMN test_data_main.sex IS '性别';
COMMENT ON COLUMN test_data_main.in_date IS '加入日期';
COMMENT ON COLUMN test_data_main.create_by IS '创建者';
COMMENT ON COLUMN test_data_main.create_date IS '创建时间';
COMMENT ON COLUMN test_data_main.update_by IS '更新者';
COMMENT ON COLUMN test_data_main.update_date IS '更新时间';
COMMENT ON COLUMN test_data_main.remarks IS '备注信息';
COMMENT ON COLUMN test_data_main.del_flag IS '删除标记';
COMMENT ON TABLE test_tree IS '树结构表';
COMMENT ON COLUMN test_tree.id IS '编号';
COMMENT ON COLUMN test_tree.parent_id IS '父级编号';
COMMENT ON COLUMN test_tree.parent_ids IS '所有父级编号';
COMMENT ON COLUMN test_tree.name IS '名称';
COMMENT ON COLUMN test_tree.sort IS '排序';
COMMENT ON COLUMN test_tree.create_by IS '创建者';
COMMENT ON COLUMN test_tree.create_date IS '创建时间';
COMMENT ON COLUMN test_tree.update_by IS '更新者';
COMMENT ON COLUMN test_tree.update_date IS '更新时间';
COMMENT ON COLUMN test_tree.remarks IS '备注信息';
COMMENT ON COLUMN test_tree.del_flag IS '删除标记';