Oracle插入随机生成的数据行

发布时间：2020-12-12 15:30:59 所属栏目：百科来源：网络整理

导读：我们将向OE.ORDERS表中插入10万行数据，这10万行数据是随机生成的 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..10

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

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,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)),'+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)),'under development','planned','obsolete','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,PROD_STATUS,CATALOG_URL);
     END LOOP;
     COMMIT;
   END LOOP;
END;
/

遇到的问题： ORA-01438: value larger than specified precision allowed ... 检查后发现当是Product_id>1000000 后报错，定义的长度不够使用以下语句将Product_id长度修改为 ALTER TABLE oe.product_information MODIFY PRODUCT_ID NUMBER(10);

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!