Oracle数据库生成测试数据的常用7个脚本

1.准备环境

1.1创建测试用户和表空间

-- 创建表空间
CREATE TABLESPACE cjc DATAFILE '/oradata/cjc01.dbf' SIZE 100M AUTOEXTEND ON;

-- 创建用户并授权
CREATE USER c##cjc IDENTIFIED BY "a" DEFAULT TABLESPACE cjc;

GRANT CONNECT, RESOURCE, CREATE VIEW TO c##cjc;

1.2创建测试表

CONNECT c##cjc/a

CREATE TABLE t1 (
    id         NUMBER PRIMARY KEY,
    name       VARCHAR2(50),
    salary     NUMBER(8,2),
    email      VARCHAR2(80),
    birth_date DATE,
    join_date  DATE
);

2.生成10万行测试数据

2.1：基本CONNECT BY

INSERT INTO t1
SELECT 
    LEVEL AS id,
    'User_' || LPAD(LEVEL, 6, '0') AS name,
    ROUND(DBMS_RANDOM.VALUE(3000, 20000), 0) AS salary, 
    DBMS_RANDOM.STRING('A', 8) || '@example.com' AS email,
    TO_DATE('1980-01-01', 'YYYY-MM-DD') + DBMS_RANDOM.VALUE(0, 14600) AS birth_date,
    SYSDATE - DBMS_RANDOM.VALUE(0, 3650) AS create_date
FROM DUAL
CONNECT BY LEVEL <= 100000;

COMMIT;

耗时0.82秒；

2.2：笛卡尔积突破限制

INSERT /*+ APPEND */ INTO t1
SELECT
    ROWNUM AS id,
    'User_' || LPAD(ROWNUM, 6, '0'),
    ROUND(3000 + DBMS_RANDOM.VALUE(0, 17000)),
    DBMS_RANDOM.STRING('X', 10) || '@mail.com',
    ADD_MONTHS(SYSDATE, -12*DBMS_RANDOM.VALUE(20, 50)),
    SYSDATE - DBMS_RANDOM.VALUE(0, 365*5)
FROM
    (SELECT 1 FROM DUAL CONNECT BY LEVEL <= 316) a,
    (SELECT 1 FROM DUAL CONNECT BY LEVEL <= 316) b  -- 316*316≈100,000
WHERE ROWNUM <= 100000;
COMMIT;

耗时3.64秒;

2.3：存储过程方法生成10万行

简单循环

CREATE OR REPLACE PROCEDURE gen_data_loop AS
BEGIN
  FOR i IN 1..100000 LOOP
    INSERT INTO t1 VALUES (
      i,
      'User_' || LPAD(i, 6, '0'),
      ROUND(3000 + DBMS_RANDOM.VALUE(0, 17000)),
      DBMS_RANDOM.STRING('A', 5) || i || '@corp.com',
      SYSDATE - DBMS_RANDOM.VALUE(365*20, 365*40),
      SYSDATE - DBMS_RANDOM.VALUE(0, 1800)
    );
  END LOOP;
  COMMIT;
END;
/

-- 执行
SET TIMING ON
EXEC gen_data_loop;

耗时2.27秒;

3.scott示例用户数据：

. . imported "C##CJC"."DEPT"                             5.929 KB       4 rows
. . imported "C##CJC"."EMP"                              8.562 KB      14 rows
. . imported "C##CJC"."SALGRADE"                         5.859 KB       5 rows
. . imported "C##CJC"."BONUS"                                0 KB       0 rows

使用SQL Developer导出成sql格式:

  CREATE TABLE "EMP" 
   (	"EMPNO" NUMBER(4,0), 
	"ENAME" VARCHAR2(10), 
	"JOB" VARCHAR2(9), 
	"MGR" NUMBER(4,0), 
	"HIREDATE" DATE, 
	"SAL" NUMBER(7,2), 
	"COMM" NUMBER(7,2), 
	"DEPTNO" NUMBER(2,0)
   ) ;
REM INSERTING into EMP
SET DEFINE OFF;
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7369,'SMITH','CLERK',7902,to_date('17-12月-80','DD-MON-RR'),800,null,20);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7499,'ALLEN','SALESMAN',7698,to_date('20-2月 -81','DD-MON-RR'),1600,300,30);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7521,'WARD','SALESMAN',7698,to_date('22-2月 -81','DD-MON-RR'),1250,500,30);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7566,'JONES','MANAGER',7839,to_date('02-4月 -81','DD-MON-RR'),2975,null,20);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7654,'MARTIN','SALESMAN',7698,to_date('28-9月 -81','DD-MON-RR'),1250,1400,30);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7698,'BLAKE','MANAGER',7839,to_date('01-5月 -81','DD-MON-RR'),2850,null,30);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7782,'CLARK','MANAGER',7839,to_date('09-6月 -81','DD-MON-RR'),2450,null,10);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7788,'SCOTT','ANALYST',7566,to_date('19-4月 -87','DD-MON-RR'),3000,null,20);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7839,'KING','PRESIDENT',null,to_date('17-11月-81','DD-MON-RR'),5000,null,10);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7844,'TURNER','SALESMAN',7698,to_date('08-9月 -81','DD-MON-RR'),1500,0,30);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7876,'ADAMS','CLERK',7788,to_date('23-5月 -87','DD-MON-RR'),1100,null,20);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7900,'JAMES','CLERK',7698,to_date('03-12月-81','DD-MON-RR'),950,null,30);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7902,'FORD','ANALYST',7566,to_date('03-12月-81','DD-MON-RR'),3000,null,20);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7934,'MILLER','CLERK',7782,to_date('23-1月 -82','DD-MON-RR'),1300,null,10);


  CREATE UNIQUE INDEX "PK_EMP" ON "EMP" ("EMPNO") 
  ;


  ALTER TABLE "EMP" ADD CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO")
  USING INDEX  ENABLE;


  ALTER TABLE "EMP" ADD CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO")
	  REFERENCES "DEPT" ("DEPTNO") ENABLE;

  CREATE TABLE "DEPT" 
   (	"DEPTNO" NUMBER(2,0), 
	"DNAME" VARCHAR2(14), 
	"LOC" VARCHAR2(13)
   ) ;
REM INSERTING into DEPT
SET DEFINE OFF;
Insert into DEPT (DEPTNO,DNAME,LOC) values (10,'ACCOUNTING','NEW YORK');
Insert into DEPT (DEPTNO,DNAME,LOC) values (20,'RESEARCH','DALLAS');
Insert into DEPT (DEPTNO,DNAME,LOC) values (30,'SALES','CHICAGO');
Insert into DEPT (DEPTNO,DNAME,LOC) values (40,'OPERATIONS','BOSTON');


  CREATE UNIQUE INDEX "PK_DEPT" ON "DEPT" ("DEPTNO") 
  ;


  ALTER TABLE "DEPT" ADD CONSTRAINT "PK_DEPT" PRIMARY KEY ("DEPTNO")
  USING INDEX  ENABLE;

  CREATE TABLE "BONUS" 
   (	"ENAME" VARCHAR2(10), 
	"JOB" VARCHAR2(9), 
	"SAL" NUMBER, 
	"COMM" NUMBER
   ) ;
REM INSERTING into BONUS
SET DEFINE OFF;

  CREATE TABLE "SALGRADE" 
   (	"GRADE" NUMBER, 
	"LOSAL" NUMBER, 
	"HISAL" NUMBER
   ) ;
REM INSERTING into SALGRADE
SET DEFINE OFF;
Insert into SALGRADE (GRADE,LOSAL,HISAL) values (1,700,1200);
Insert into SALGRADE (GRADE,LOSAL,HISAL) values (2,1201,1400);
Insert into SALGRADE (GRADE,LOSAL,HISAL) values (3,1401,2000);
Insert into SALGRADE (GRADE,LOSAL,HISAL) values (4,2001,3000);
Insert into SALGRADE (GRADE,LOSAL,HISAL) values (5,3001,9999);

欢迎关注我的公众号《IT小Chen》