Oracle随机填充数据,Oracle插入随机生成的数据行

DECLARE

order_id INT;

customer_id INT;

ORDER_MODE VARCHAR(10);

ORDER_TOTAL NUMBER(10,2);

ORDER_STATUS INT;

BEGIN

SELECT MAX(order_id) INTO order_id FROM oe.orders;

FOR i IN 1..100000 LOOP

order_id := order_id + 1;

--generate a random CUSTOMER_ID from customers table

SELECT CUSTOMER_ID INTO customer_id

FROM (SELECT ROWNUM AS ROWNUM_, CUSTOMER_ID FROM OE.CUSTOMERS)

WHERE ROWNUM_ = (SELECT trunc(dbms_random.value(1,319)) AS ROWNUM_ FROM dual);

--generate a random ORDER_MODE

SELECT decode(trunc(dbms_random.value(1,3)),1,'direct','online') INTO ORDER_MODE FROM dual;

--generate a random ORDER_TOTAL

SELECT trunc(dbms_random.value(5000,50000),2) INTO ORDER_TOTAL FROM dual;

--generate a random ORDER_STATUS

SELECT trunc(dbms_random.value(1,11)) INTO ORDER_STATUS FROM dual;

insert into OE.ORDERS (ORDER_ID, ORDER_DATE, ORDER_MODE, CUSTOMER_ID, ORDER_STATUS, ORDER_TOTAL, SALES_REP_ID, PROMOTION_ID)

values (order_id, SYSDATE, ORDER_MODE, CUSTOMER_ID, ORDER_STATUS, ORDER_TOTAL, null, null);

END LOOP;

COMMIT;

END;

/

我们将向OE.product_information表中插入1000万行数据，这1000万行数据是随机生成的

DECLARE

PROD_ID INT;

PROD_NAME VARCHAR2(50);

PROD_DESC VARCHAR2(2000);

CATE_ID INT;

WEIGHT_CLASS NUMBER(1) ;

WARRANTY_PERIOD INTERVAL YEAR(2) TO MONTH;

SUPPLIER_ID INT;

PROD_STATUS VARCHAR(20);

LIST_PRICE NUMBER(8,2);

MIN_PRICE NUMBER(8,2);

CATALOG_URL VARCHAR2(50) := 'http://www.supp-102055.com/cat/hw/p2289.html';

BEGIN

SELECT MAX(PRODUCT_ID) INTO PROD_ID FROM oe.product_information;

FOR t IN 1..100 LOOP

FOR i IN 1..100000 LOOP

PROD_ID := PROD_ID + 1;

SELECT 'PROD_NAME_TEST_' || i INTO PROD_NAME FROM DUAL;

SELECT 'PROD_DESC_TEST_' || i INTO PROD_DESC FROM DUAL;

SELECT TRUNC(DBMS_RANDOM.VALUE(1,40)) INTO CATE_ID FROM DUAL;

SELECT TRUNC(DBMS_RANDOM.VALUE(1,6)) INTO WEIGHT_CLASS FROM DUAL;

SELECT DECODE(TRUNC(DBMS_RANDOM.VALUE(1,16)),1,'+01-00',

2,'+02-00',

3,'+10-00',

4,'+15-00',

5,'+20-00',

6,'+00-02',

7,'+00-06',

8,'+03-00',

9,'+01-06',

10,'+00-03',

11,'+05-00',

12,'+00-09',

13,'+25-00',

14,'+00-01',

15,'+00-00') INTO WARRANTY_PERIOD FROM DUAL;

SELECT SUPPLIER_ID INTO SUPPLIER_ID FROM

(SELECT SUPPLIER_ID, ROWNUM ROWNUM_ FROM SUPPLIERS)

WHERE ROWNUM_ = (SELECT trunc(dbms_random.value(1,63)) FROM dual);

SELECT DECODE(TRUNC(DBMS_RANDOM.VALUE(1,5)),1,'under development',2,'planned',3,'obsolete',4,'orderable') INTO PROD_STATUS FROM DUAL;

SELECT CAST(TRUNC(DBMS_RANDOM.VALUE(100,500)) AS NUMBER (10, 2)) INTO LIST_PRICE FROM DUAL;

MIN_PRICE := LIST_PRICE - TRUNC(DBMS_RANDOM.VALUE(0,51));

insert into oe.product_information (PRODUCT_ID, PRODUCT_NAME, PRODUCT_DESCRIPTION, CATEGORY_ID, WEIGHT_CLASS, WARRANTY_PERIOD, SUPPLIER_ID, PRODUCT_STATUS, LIST_PRICE, MIN_PRICE, CATALOG_URL)

values (PROD_ID, PROD_NAME, PROD_DESC, CATE_ID, WEIGHT_CLASS, WARRANTY_PERIOD, SUPPLIER_ID, PROD_STATUS, LIST_PRICE, MIN_PRICE, CATALOG_URL);

END LOOP;

COMMIT;

END LOOP;

END;

/

遇到的问题：