虚谷数据库按每天三万笔数据循环生成千万级数据

思路：先按1000天 每天一条数据库生成，写进一个临时表，然后写个存储过程从临时表取数写进目标表，可以通过jmeter多线程执行存储过程

步骤一（先按1000天 每天一条数据库生成）：先在数据库执行存储过程：call SYSDBA.SP_INSERT_DATA_PRODUCTS_TEST109（）；

步骤二：用jmeter开多线程调存储过程

配置jmeter线程：

配置数据库连接：

jmeter调存储过程：

执行结果：3000W数据2.1分钟执行完，平均每秒238095

建表SQL：

create table SYSDBA.PRODUCTS(
PRODUCT_NO varchar(50) not null,
PRODUCT_NAME varchar(200),
PRODUCT_INTRODUCE varchar(4000),
MANUFACTURE_DATE date,
SELL_DATES varchar(50),
ADDRESS varchar(200),
PRODUCT_TYPE varchar(50)
);
-- Alter Table Add PrimaryKey Constraint --
alter table SYSDBA.PRODUCTS add constraint PRODUCT_KEY primary key(PRODUCT_NO);
-- Create Table Index --
create index "PRODUCT_MANUF_DATE_INDEX" on SYSDBA.PRODUCTS("MANUFACTURE_DATE") indextype is btree global ;
 

SQL：

create or replace procedure SYSDBA.SP_INSERT_DATA_PRODUCTS_TEST109 IS

begin

 for i in 1..1000 loop

INSERT INTO SYSDBA.PRODUCTS_TEST109

            VALUES ( sys_uuid AS product_no --引用自增序列

                   , DBMS_RANDOM.STRING('x', 8) AS product_name                    , CASE when i<200 --随机选取5个枚举值

                         then '零食大礼包A'

                         when i<400 then '零食大礼包B'

                          when i<600 then '零食大礼包C'

                         when i<800 then '零食大礼包D'

                          when i<1000 then '零食大礼包E'

                         ELSE '零食大礼包E'

                         END AS PRODUCT_INTRODUCE

                    , to_date('2017-01-01 00:00:00','yyyy-mm-dd hh24:mi:ss')+i AS MANUFACTURE_DATE

                    , sysdate AS SELL_DATES

                    , CASE when i<200 --随机选取5个枚举值

                         then '北京'

                         when i<400 then '上海'

                          when i<600 then '深圳'

                         when i<800 then '广州'

                          when i<1000 then '成都'

                         ELSE '武汉'

                         END as address

                   , CASE when i<200 --随机选取5个枚举值

                         then '食品'

                         when i<400 then '饰品'

                          when i<600 then '汽车'

                         when i<800 then '五金'

                          when i<1000 then '军工'

                         ELSE '海鲜'

                         END PRODUCT_TYPE);

end loop;

commit;

end SP_insert_DATA_PRODUCTS_TEST109;

--用jmeter开100个线程即可

create or replace procedure SYSDBA.SP_INSERT_DATA_PRODUCTS_TEST1091 IS

BEGIN

FOR X IN 1..300

        LOOP

INSERT  INTO  SYSDBA.PRODUCTS_TEST1091

SELECT  sys_uuid as PRODUCT_NO,PRODUCT_NAME,PRODUCT_INTRODUCE,MANUFACTURE_DATE,sysdate as SELL_DATES,ADDRESS,PRODUCT_TYPE

FROM

SYSDBA.PRODUCTS_TEST109 s;

commit;

        END LOOP;   

END SP_insert_DATA_PRODUCTS_TEST1091;