在Oracle表中生成测试数据

Question

我想为这些Oracle表创建测试数据：

CREATE TABLE AGENT_HISTORY(
  EVENT_ID INTEGER NOT NULL,
  AGENTID INTEGER NOT NULL,
  EVENT_DATE DATE NOT NULL
)
/
CREATE TABLE CPU_HISTORY(
  CPU_HISTORY_ID INTEGER NOT NULL,
  EVENT_ID INTEGER NOT NULL,
  CPU_NAME VARCHAR2(50 ) NOT NULL,
  CPU_VALUE NUMBER NOT NULL
)
/

你能帮我创建一个为100行生成随机值的过程吗？

我试过这个

BEGIN
  FOR loop_counter IN 1..1000
  LOOP
    INSERT INTO AGENT_HISTORY (EVENT_ID, AGENTID, EVENT_DATE)
    VALUES (loop_counter, 22, SYSDATE);
  END LOOP;
  COMMIT;   
  FOR loop_counter IN 1..1000
  LOOP
    INSERT INTO CPU_HISTORY_ID (CPU_HISTORY_ID, EVENT_ID, CPU_NAME, CPU_VALUE)
    VALUES (loop_counter, loop_counter, 'cpu1', dbms_random.value(1,100));
  END LOOP;
  COMMIT;
END;

但我得到了这些错误：

Error report -
ORA-00001: unique constraint (ADMIN.KEY8) violated
ORA-06512: at line 5
00001. 00000 -  "unique constraint (%s.%s) violated"
*Cause:    An UPDATE or INSERT statement attempted to insert a duplicate key.
           For Trusted Oracle configured in DBMS MAC mode, you may see
           this message if a duplicate entry exists at a different level.
*Action:   Either remove the unique restriction or do not insert the key.
Error report -
ORA-06550: line 5, column 16:
PL/SQL: ORA-00942: table or view does not exist
ORA-06550: line 5, column 4:
PL/SQL: SQL Statement ignored
06550. 00000 -  "line %s, column %s:n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:

Answer 1

希望这有帮助（请参阅下面的SQLfiddle工作）

INSERT INTO AGENT_HISTORY (EVENT_ID,AGENTID,EVENT_DATE)
select 
 trunc(dbms_random.value(1,100)) EVENT_ID ,
 trunc(dbms_random.value(1,100)) AGENTID ,
 TO_DATE(
          TRUNC(
               DBMS_RANDOM.VALUE(TO_CHAR(DATE '2000-01-01','J')
                                ,TO_CHAR(DATE '2015-11-10','J')
                                )
                ),'J'
           ) EVENT_DATE from dual
connect by level <=10;
select * from AGENT_HISTORY

正在使用SQL Fiddle：http://sqlfiddle.com/#！4/2bcb6/7

Answer 2

按顺序生成PK（使用rownum），如果您想要随机顺序，请按子句添加顺序。这样可以避免重复的关键点。

 select 
  rownum CPU_HISTORY_ID,
  trunc(dbms_random.value(1,50)) EVENT_ID,
  'CPU NO '||trunc(dbms_random.value(1,10)) CPU_NAME,
  dbms_random.value(1,5) CPU_VALUE
 from dual connect by level <= 100 
 order by dbms_random.value()
 ;

如果外键是简单的，例如从1到50的任何数字，则使用如上所述的随机数生成器。如果您有一组预定义的可能值，请使用查找表。在下面的示例中，表EVENT将整数1..3映射到可能的EVENT_ID，以完全填充FK约束。对事件表使用类似的方法。

 with event as (
    select 1 rn, 1828 EVENT_ID from dual union all
    select 2 rn, 2818 EVENT_ID from dual union all
    select 3 rn, 9898 EVENT_ID from dual),
 gendata as (   
 select 
  rownum CPU_HISTORY_ID,round(dbms_random.value(1,3)) rn,
  'CPU NO '||trunc(dbms_random.value(1,10)) CPU_NAME,
  dbms_random.value(1,5) CPU_VALUE
 from dual connect by level <= 100 
 )
 select CPU_HISTORY_ID,   
  (select EVENT_ID from event where rn = gendata.rn) EVENT_ID,
   CPU_NAME, CPU_VALUE
 from gendata
 order by dbms_random.value()
 ;