使用 QuickSQL 快速建立 tables

9 minute read

ER-Diagram

本次 QuickSQL 教材之用的 ER-Diagram example:

撰寫原則

  1. 每一個 table 一個 line
  2. 欄位 indent
1
2
3
4

-- 料件
item /insert 5
    item_num
    name
  1. Parent table 寫在 child table 之前
  2. 在 child table 要參考 parent table 時，使用 <table_name> id
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

-- 服務請求單
service_request /insert 2
    doc_num
    file_date date
    descript clob 
    item id 
    customer id 
    requestor id 

-- 料件溯源
-- 必須在 parent table 後建立，否則建立 table 時 FK 會找不到 parent table
-- 在 parent table 之前，所以使用 /fk 指定 parent table
item_trace /insert 2
    dept_name 
    doc_num
    qty int
    service_request id /fk service_request

SQL syntax generation options

在檔案最前處，可設定 SQL 碼產生選項，例如： prefix, primary key 內容產生的方式等。

以下的例子：

  1. 在 table name 之前加上前置字串 hy_
  2. Primary Key 的內容使用 sequence; 系統會自動產生所需的 Sequence 物件
  3. 在 Create table statement 之前產生 Drop statement, 方便重新部署 tables
1
2
3

# prefix: "hy"
# pk: "seq"
# drop: true

自動產生 data

可以為 table 自動產生 random data.

在 table 名稱之後加上 \insert nn 的 table directive. 例如，以下的 QuickSQL 會產生 5 筆 random data 到 requestor table 中：

1
2
3

-- 建檔人
requestor /insert 5
    name

完整的例子

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

-- setting
# prefix: "hy"
# pk: "seq"
# drop: true
#apex: false, auditcols: false 
-- apex: populates audit information using the application user if available.
-- auditcols: true will generate audit columns per table.

-- 建檔人
requestor /insert 5
    name 

-- 客戶
customer /insert 5
    name

-- 料件
item /insert 5
    item_num
    name 


-- 服務請求單
service_request /insert 2
    doc_num
    file_date date
    descript clob 
    item id 
    customer id 
    requestor id 

-- 料件溯源
-- 必須在 parent table 後建立，否則建立 table 時 FK 會找不到 parent table
-- 在 parent table 之前，所以使用 /fk 指定 parent table
item_trace /insert 2
    dept_name 
    doc_num
    qty int
    service_request id /fk service_request

-- 圖片
picture /insert 3
    filename
    img_size int
    upload_date date 
    service_request id

產生的 SQL codes:

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
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410

-- drop objects
drop table hy_requestor cascade constraints;
drop sequence hy_requestor_seq;
drop table hy_customer cascade constraints;
drop sequence hy_customer_seq;
drop table hy_item cascade constraints;
drop sequence hy_item_seq;
drop table hy_service_request cascade constraints;
drop sequence hy_service_request_seq;
drop table hy_item_trace cascade constraints;
drop sequence hy_item_trace_seq;
drop table hy_picture cascade constraints;
drop sequence hy_picture_seq;

-- create tables
create sequence hy_requestor_seq;

create table hy_requestor (
    id                             number default on null hy_requestor_seq.NEXTVAL 
                                   constraint hy_requestor_id_pk primary key,
    name                           varchar2(255 char)
)
;

create sequence hy_customer_seq;

create table hy_customer (
    id                             number default on null hy_customer_seq.NEXTVAL 
                                   constraint hy_customer_id_pk primary key,
    name                           varchar2(255 char)
)
;

create sequence hy_item_seq;

create table hy_item (
    id                             number default on null hy_item_seq.NEXTVAL 
                                   constraint hy_item_id_pk primary key,
    item_num                       varchar2(4000 char),
    name                           varchar2(255 char)
)
;

create sequence hy_service_request_seq;

create table hy_service_request (
    id                             number default on null hy_service_request_seq.NEXTVAL 
                                   constraint hy_service_request_id_pk primary key,
    item_id                        number
                                   constraint hy_service_request_item_id_fk
                                   references hy_item on delete cascade,
    customer_id                    number
                                   constraint hy_service_requ_customer_id_fk
                                   references hy_customer on delete cascade,
    requestor_id                   number
                                   constraint hy_service_requ_requestor_i_fk
                                   references hy_requestor on delete cascade,
    doc_num                        varchar2(4000 char),
    file_date                      date,
    descript                       clob
)
;

-- table index
create index hy_service_request_i1 on hy_service_request (customer_id);
create index hy_service_request_i2 on hy_service_request (item_id);
create index hy_service_request_i3 on hy_service_request (requestor_id);

create sequence hy_item_trace_seq;

create table hy_item_trace (
    id                             number default on null hy_item_trace_seq.NEXTVAL 
                                   constraint hy_item_trace_id_pk primary key,
    service_request_id             number
                                   constraint hy_item_trace_service_reque_fk
                                   references hy_service_request on delete cascade,
    dept_name                      varchar2(255 char),
    doc_num                        varchar2(4000 char),
    qty                            integer
)
;

-- table index
create index hy_item_trace_i1 on hy_item_trace (service_request_id);

create sequence hy_picture_seq;

create table hy_picture (
    id                             number default on null hy_picture_seq.NEXTVAL 
                                   constraint hy_picture_id_pk primary key,
    service_request_id             number
                                   constraint hy_picture_service_request_fk
                                   references hy_service_request on delete cascade,
    filename                       varchar2(255 char),
    img_size                       integer,
    upload_date                    date
)
;

-- table index
create index hy_picture_i1 on hy_picture (service_request_id);


-- triggers
-- load data
 
insert into hy_requestor (
    id,
    name
) values (
    1,
    'Essential Systems Review'
);

insert into hy_requestor (
    id,
    name
) values (
    2,
    'Client Library Upgrade'
);

insert into hy_requestor (
    id,
    name
) values (
    3,
    'JIRA Ticketing System Migration'
);

insert into hy_requestor (
    id,
    name
) values (
    4,
    'Albert AI System'
);

insert into hy_requestor (
    id,
    name
) values (
    5,
    'Physical Management'
);

commit;
-- load data
-- load data
 
insert into hy_customer (
    id,
    name
) values (
    1,
    'Essential Systems Review'
);

insert into hy_customer (
    id,
    name
) values (
    2,
    'Client Library Upgrade'
);

insert into hy_customer (
    id,
    name
) values (
    3,
    'JIRA Ticketing System Migration'
);

insert into hy_customer (
    id,
    name
) values (
    4,
    'Albert AI System'
);

insert into hy_customer (
    id,
    name
) values (
    5,
    'Physical Management'
);

commit;
-- load data
 
insert into hy_item (
    id,
    item_num,
    name
) values (
    1,
    'Venenatis nec. Donec convallis sollicitudin elementum. Nulla facilisi. In posuere blandit leoeget malesuada. Vivamus efficitur ipsum tellus, quis posuere mi maximus vitae. Quisque tortor odio, feugiat eget sagittisvel, pretium ut metus. Duis et commodo arcu, in vestibulum tellus. In sollicitudin nisi mi, pharetra gravida.',
    'Essential Systems Review'
);

insert into hy_item (
    id,
    item_num,
    name
) values (
    2,
    'Viverra lacinialectus, quis consectetur mi venenatis nec. Donec convallis sollicitudin elementum. Nulla facilisi. In posuere blandit leoeget malesuada. Vivamus efficitur ipsum tellus, quis posuere mi maximus vitae. Quisque tortor odio, feugiat eget sagittisvel, pretium ut metus. Duis et commodo arcu, in.',
    'Client Library Upgrade'
);

insert into hy_item (
    id,
    item_num,
    name
) values (
    3,
    'Proin vulputate placerat pellentesque. Proin viverra lacinialectus, quis consectetur mi venenatis nec. Donec convallis sollicitudin elementum. Nulla facilisi. In posuere blandit leoeget malesuada. Vivamus efficitur ipsum tellus, quis posuere mi maximus vitae. Quisque tortor odio, feugiat eget sagittisvel, pretium ut metus. Duis et commodo arcu, in vestibulum tellus. In sollicitudin.',
    'JIRA Ticketing System Migration'
);

insert into hy_item (
    id,
    item_num,
    name
) values (
    4,
    'Nulla ac sapien suscipit tristique ac volutpat risus.Phasellus vitae ligula commodo, dictum lorem sit amet, imperdiet ex. Etiam cursus porttitor tincidunt. Vestibulum ante ipsumprimis in faucibus orci luctus et.',
    'Albert AI System'
);

insert into hy_item (
    id,
    item_num,
    name
) values (
    5,
    'Quis consectetur mi venenatis nec. Donec convallis sollicitudin elementum. Nulla facilisi. In posuere blandit leoeget malesuada. Vivamus efficitur ipsum tellus, quis posuere mi maximus vitae. Quisque tortor odio, feugiat eget sagittisvel, pretium ut metus.',
    'Physical Management'
);

commit;
-- load data
 
insert into hy_service_request (
    id,
    doc_num,
    file_date,
    descript,
    item_id,
    customer_id,
    requestor_id
) values (
    1,
    'Et ultrices posuere cubilia Curae; Proin vulputate placerat pellentesque. Proin viverra lacinialectus, quis.',
    sysdate - 55,
    'x',
    2,
    4,
    5
);

insert into hy_service_request (
    id,
    doc_num,
    file_date,
    descript,
    item_id,
    customer_id,
    requestor_id
) values (
    2,
    'In faucibus. Ut id nulla ac sapien suscipit tristique ac volutpat risus.Phasellus vitae ligula commodo, dictum lorem sit amet, imperdiet ex. Etiam cursus porttitor tincidunt. Vestibulum ante ipsumprimis in faucibus orci luctus et ultrices posuere cubilia Curae; Proin.',
    sysdate - 16,
    'x',
    5,
    5,
    1
);

commit;
-- load data
 
insert into hy_item_trace (
    id,
    dept_name,
    doc_num,
    qty,
    service_request_id
) values (
    1,
    'Essential Systems Review',
    'Malesuada fames ac ante ipsum primis in faucibus. Ut id nulla ac sapien suscipit tristique ac volutpat risus.Phasellus vitae ligula commodo, dictum lorem sit amet, imperdiet ex. Etiam cursus.',
    1,
    1
);

insert into hy_item_trace (
    id,
    dept_name,
    doc_num,
    qty,
    service_request_id
) values (
    2,
    'Client Library Upgrade',
    'Ac ante ipsum primis in faucibus. Ut id nulla ac sapien suscipit tristique ac volutpat risus.Phasellus vitae ligula commodo, dictum lorem sit amet, imperdiet ex. Etiam cursus porttitor tincidunt. Vestibulum ante ipsumprimis in faucibus orci luctus et ultrices posuere cubilia Curae; Proin vulputate placerat pellentesque. Proin viverra lacinialectus, quis consectetur mi venenatis nec. Donec convallis sollicitudin.',
    62,
    1
);

commit;
-- load data
 
insert into hy_picture (
    id,
    filename,
    img_size,
    upload_date,
    service_request_id
) values (
    1,
    'Essential Systems Review',
    20,
    sysdate - 71,
    2
);

insert into hy_picture (
    id,
    filename,
    img_size,
    upload_date,
    service_request_id
) values (
    2,
    'Client Library Upgrade',
    70,
    sysdate - 85,
    2
);

insert into hy_picture (
    id,
    filename,
    img_size,
    upload_date,
    service_request_id
) values (
    3,
    'JIRA Ticketing System Migration',
    68,
    sysdate - 18,
    2
);

commit;
 
-- Generated by Quick SQL Friday October 07, 2022  18:31:49
 
/*
-- setting# pk: "seq"
# drop: true
#apex: false, auditcols: false 
-- apex: populates audit information using the application user if available.
-- auditcols: true will generate audit columns per table.

-- 建檔人
requestor /insert 5
    name 

-- 客戶
customer /insert 5
    name

-- 料件
item /insert 5
    item_num
    name 


-- 服務請求單
service_request /insert 2
    doc_num
    file_date date
    descript clob 
    item id 
    customer id 
    requestor id 

-- 料件溯源
-- 必須在 parent table 後建立，否則建立 table 時 FK 會找不到 parent table
-- 在 parent table 之前，所以使用 /fk 指定 parent table
item_trace /insert 2
    dept_name 
    doc_num
    qty int
    service_request id /fk service_request

-- 圖片
picture /insert 3
    filename
    img_size int
    upload_date date 
    service_request id

# settings = { prefix: "HY", PK: "SEQ", semantics: "CHAR", drop: true, language: "EN" }
*/

QuickSQL 參考資料

  1. APEX Help

  1. Oracle.com

Updated:

You May Also Enjoy