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;

参考：http://www.oracle-base.com/articles/misc/RollupCubeGroupingFunctionsAndGroupingSets.php