使用dbms(时间范围)在四列中插入带有年份日期和时间的表中的数据

Question

亲爱的，我有一个表，里面有DT、DT2、DT3、DT4列。我可以插入一列，但不能同时插入所有列。如果你有任何想法，请提前告诉我谢谢，我的代码是

insert into calendar_dateS4 (DT,DT2,DT3, DT4, DT5 )
SELECT DT, DT2, DT3, DT4, DT5

FROM   (
SELECT DATE '2020-01-01' + FLOOR( (LEVEL - 1) )
+  INTERVAL '08:45' HOUR TO MINUTE
+ DBMS_RANDOM.VALUE() * INTERVAL '00:15' HOUR TO MINUTE

+  INTERVAL '16:45' HOUR TO MINUTE
+ DBMS_RANDOM.VALUE() * INTERVAL '00:15' HOUR TO MINUTE

+  INTERVAL '05:45' HOUR TO MINUTE
+ DBMS_RANDOM.VALUE() * INTERVAL '00:15' HOUR TO MINUTE

+  INTERVAL '13:45' HOUR TO MINUTE
+ DBMS_RANDOM.VALUE() * INTERVAL '00:15' HOUR TO MINUTE

+  INTERVAL '21:45' HOUR TO MINUTE
+ DBMS_RANDOM.VALUE() * INTERVAL '00:15' HOUR TO MINUTE


AS DT, DT2, DT3, DT4, DT5
FROM   DUAL
CONNECT BY
DATE '2020-01-01' + FLOOR( (LEVEL - 1) )
< ADD_MONTHS( DATE '2020-01-01', 12 )
)

Answer 1

由于SELECT语句无效，您的INSERT失败。获取一个具有所需结果的有效SELECT语句，然后将其转换为INSERT语句。

要在所有5列中插入相同的时间，可以使用以下语句：

INSERT INTO calendar_dateS4 (DT,
DT2,
DT3,
DT4,
DT5)
SELECT dt,
dt,
dt,
dt,
dt
FROM (    SELECT   DATE '2020-01-01'
+ FLOOR ((LEVEL - 1))
+ INTERVAL '08:45' HOUR TO MINUTE
+ DBMS_RANDOM.VALUE () * INTERVAL '00:15' HOUR TO MINUTE
+ INTERVAL '16:45' HOUR TO MINUTE
+ DBMS_RANDOM.VALUE () * INTERVAL '00:15' HOUR TO MINUTE
+ INTERVAL '05:45' HOUR TO MINUTE
+ DBMS_RANDOM.VALUE () * INTERVAL '00:15' HOUR TO MINUTE
+ INTERVAL '13:45' HOUR TO MINUTE
+ DBMS_RANDOM.VALUE () * INTERVAL '00:15' HOUR TO MINUTE
+ INTERVAL '21:45' HOUR TO MINUTE
+ DBMS_RANDOM.VALUE () * INTERVAL '00:15' HOUR TO MINUTE    AS DT
FROM DUAL
CONNECT BY DATE '2020-01-01' + FLOOR ((LEVEL - 1)) < ADD_MONTHS (DATE '2020-01-01', 12))