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
| -- 1. BIKE_MODEL: 모델 20종
BEGIN
FOR i IN 1..20 LOOP
INSERT INTO BIKE_MODEL (ID, NAME)
VALUES ('M' || LPAD(i, 2, '0'), 'Model_' || i);
END LOOP;
COMMIT;
END;
/
-- 2. BIKE: 자전거 2000대
BEGIN
FOR i IN 1..2000 LOOP
INSERT INTO BIKE (ID, MODEL_ID, NAME)
VALUES (
'B' || LPAD(i, 4, '0'),
'M' || LPAD(TRUNC(DBMS_RANDOM.VALUE(1, 21)), 2, '0'),
'Bike_' || i
);
IF MOD(i, 500) = 0 THEN COMMIT; END IF;
END LOOP;
COMMIT;
END;
/
-- 3. BIKE_PART: 부품 100종
BEGIN
FOR i IN 1..100 LOOP
INSERT INTO BIKE_PART (ID, NAME)
VALUES ('P' || LPAD(i, 3, '0'), 'Part_' || i);
END LOOP;
COMMIT;
END;
/
-- 4. BIKE_PART_CHANGE_RECORD - 교체 이력 2만 건 (7/7일)
BEGIN
FOR i IN 1..20000 LOOP
DECLARE
v_attach_time DATE := TO_DATE('2025-07-07 08:00:00', 'YYYY-MM-DD HH24:MI:SS') + DBMS_RANDOM.VALUE * (8/24);
v_detach_time DATE := v_attach_time + DBMS_RANDOM.VALUE * (1/24);
BEGIN
INSERT INTO BIKE_PART_CHANGE_RECORD (
ID, BIKE_ID, PART_ID, ATTACH_TIME, DETACH_TIME, CREATE_TIME, UPDATE_TIME, REMARK
) VALUES (
'PCR' || LPAD(i, 6, '0'),
'B' || LPAD(TRUNC(DBMS_RANDOM.VALUE(1, 2001)), 4, '0'),
'P' || LPAD(TRUNC(DBMS_RANDOM.VALUE(1, 101)), 3, '0'),
v_attach_time,
v_detach_time,
v_attach_time,
v_detach_time,
'CHANGE_FINISHED'
);
END;
END LOOP;
COMMIT;
END;
/
-- 5. BIKE_PART_CHANGE_LOG - 교체 로그 4만 건 (2건/RECORD)
BEGIN
FOR rec IN (
SELECT ID, BIKE_ID, PART_ID, ATTACH_TIME, DETACH_TIME
FROM BIKE_PART_CHANGE_RECORD
WHERE TRUNC(ATTACH_TIME) = TO_DATE('2025-07-07', 'YYYY-MM-DD')
) LOOP
-- 5.1. 탈거 로그
INSERT INTO BIKE_PART_CHANGE_LOG (
ID, BIKE_ID, PART_ID, DETACH_PART_RECORD_ID, CREATED_TIME, WORKER_ID, PREV_USED_KM, USED_KM, REMARK
) VALUES (
SEQ_CHANGE_LOG_ID.NEXTVAL,
rec.BIKE_ID,
rec.PART_ID,
rec.ID,
rec.DETACH_TIME,
'WORKER' || LPAD(TRUNC(DBMS_RANDOM.VALUE(1, 11)), 2, '0'),
TRUNC(DBMS_RANDOM.VALUE(500, 5000)),
TRUNC(DBMS_RANDOM.VALUE(100, 1000)),
'자동생성: 탈거'
);
-- 5.2. 장착 로그
INSERT INTO BIKE_PART_CHANGE_LOG (
ID, BIKE_ID, PART_ID, ATTACH_PART_RECORD_ID, CREATED_TIME, WORKER_ID, USED_KM, REMARK
) VALUES (
SEQ_CHANGE_LOG_ID.NEXTVAL,
rec.BIKE_ID,
rec.PART_ID,
rec.ID,
rec.ATTACH_TIME,
'WORKER' || LPAD(TRUNC(DBMS_RANDOM.VALUE(1, 11)), 2, '0'),
0,
'자동생성: 장착'
);
END LOOP;
COMMIT;
END;
/
-- 6. BIKE_LOG - 자전거 운영 로그 100만 건
-- 6.1. 시퀀스 생성
CREATE SEQUENCE SEQ_BIKE_LOG START WITH 1 INCREMENT BY 1;
-- 6.2. 100만 건 INSERT (PL/SQL 루프)
BEGIN
FOR i IN 1..1000000 LOOP
INSERT INTO BIKE_LOG (
ID,
CREATE_TIME
) VALUES (
SEQ_BIKE_LOG.NEXTVAL,
TRUNC(SYSDATE - DBMS_RANDOM.VALUE(0, 365)) -- 최근 1년 내 날짜
);
-- 1만건마다 커밋
IF MOD(i, 10000) = 0 THEN
COMMIT;
END IF;
END LOOP;
COMMIT;
END;
/
-- 6.3. SERVICE_TYPE 랜덤 분포 설정 (RENT 95%, MAINTENANCE 5%)
-- 6.3.1. 임시 컬럼 생성
ALTER TABLE BIKE_LOG ADD TEMP_RND NUMBER;
-- 6.3.2. 무작위 값 부여
UPDATE BIKE_LOG
SET TEMP_RND = DBMS_RANDOM.VALUE;
-- 6.3.3. 서비스유형 분기
UPDATE BIKE_LOG
SET SERVICE_TYPE = CASE
WHEN TEMP_RND <= 0.05 THEN 'MAINTENANCE'
ELSE 'RENT'
END;
-- 6.3.4. 임시 컬럼 제거
ALTER TABLE BIKE_LOG DROP COLUMN TEMP_RND;
COMMIT;
|