Oracle插入和连接
我正在尝试通过调用2个功能将一些测试数据加载到表T1中，但我似乎无法将插入物和连接到工作。
任何帮助将不胜感激。
ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'DD-MON-YYYY HH24:MI:SS.FF';
ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';
CREATE OR REPLACE FUNCTION random_date(
p_from IN DATE,
p_to IN DATE
) RETURN DATE
IS
BEGIN
RETURN p_from + DBMS_RANDOM.VALUE() * (p_to -p_from);
END random_date;
/
CREATE OR REPLACE FUNCTION random_timestamp(
p_from IN TIMESTAMP,
p_to IN TIMESTAMP
) RETURN TIMESTAMP
IS
BEGIN
RETURN p_from + DBMS_RANDOM.VALUE() * (p_to -p_from);
END random_timestamp;
/
CREATE TABLE t1 (
seq_num NUMBER GENERATED BY DEFAULT AS IDENTITY (START WITH 1) NOT NULL,
dt DATE,
ts TIMESTAMP
);
INSERT into t1 (dt, ts) VALUES
random_date (DATE'2022-04-01',DATE '2022-04-30'),
random_timestamp (DATE'2022-04-01',DATE '2022-04-30')
CONNECT BY LEVEL<=1000;
I'm trying to load some test data into table t1 by calling 2 functions but I can't seem to get the INSERT and CONNECT by to work.
Any help would be greatly appreciated.
ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'DD-MON-YYYY HH24:MI:SS.FF';
ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';
CREATE OR REPLACE FUNCTION random_date(
p_from IN DATE,
p_to IN DATE
) RETURN DATE
IS
BEGIN
RETURN p_from + DBMS_RANDOM.VALUE() * (p_to -p_from);
END random_date;
/
CREATE OR REPLACE FUNCTION random_timestamp(
p_from IN TIMESTAMP,
p_to IN TIMESTAMP
) RETURN TIMESTAMP
IS
BEGIN
RETURN p_from + DBMS_RANDOM.VALUE() * (p_to -p_from);
END random_timestamp;
/
CREATE TABLE t1 (
seq_num NUMBER GENERATED BY DEFAULT AS IDENTITY (START WITH 1) NOT NULL,
dt DATE,
ts TIMESTAMP
);
INSERT into t1 (dt, ts) VALUES
random_date (DATE'2022-04-01',DATE '2022-04-30'),
random_timestamp (DATE'2022-04-01',DATE '2022-04-30')
CONNECT BY LEVEL<=1000;
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
不是
value，而是从dual中进行选择（未插入1000行； 10将足够；另外，seq_num值不会从<<代码> 1 我多次运行该代码，但这是无关紧要的）：结果：
Not
values, butselect from dual(didn't insert 1000 rows; 10 will suffice; also,SEQ_NUMvalues don't start from1as I ran that code several times, but that's irrelevant):Result: