oracle产生随机数插入,oracle批量插入随机数的方法

创建表dimension_tab

DROP TABLE dimension_tab;

CREATE TABLE dimension_tab (

fact_1_id NUMBER NOT NULL,

fact_2_id NUMBER NOT NULL,

fact_3_id NUMBER NOT NULL,

fact_4_id NUMBER NOT NULL,

sales_value NUMBER(10,2) NOT NULL

);

INSERT INTO dimension_tab

SELECT TRUNC(DBMS_RANDOM.value(1,  3)) AS fact_1_id,

TRUNC(DBMS_RANDOM.value(1, 6)) AS fact_2_id,

TRUNC(DBMS_RANDOM.value(1, 11)) AS fact_3_id,

TRUNC(DBMS_RANDOM.value( 1, 11)) AS fact_4_id

,ROUND(DBMS_RANDOM.value(1, 100), 2) AS sales_value

FROM   dual

CONNECT BY level <= 1000;

COMMIT;

【模型如下】

INSERT INTO dimension_tab

SELECT TRUNC(DBMS_RANDOM.value(low => 1, high => 3)) AS fact_1_id,

TRUNC(DBMS_RANDOM.value(low => 1, high => 6)) AS fact_2_id,

TRUNC(DBMS_RANDOM.value(low => 1, high => 11)) AS fact_3_id,

TRUNC(DBMS_RANDOM.value(low => 1, high => 11)) AS fact_4_id,

ROUND(DBMS_RANDOM.value(low => 1, high => 100), 2) AS sales_value

FROM dual

CONNECT BY level <= 1000;

COMMIT;