deleteptestdata.sh:
sqlplus dbuser/dbpwd <<EOF >deleteptestdata.log
@deleteptestdata.sql
exit
EOF
insertptestdata.sql:
sqlplus dbuser/dbpwd@EP1
alter session Enable parallel dml;
--将测试中要用到的表的日志模式关闭，提高速度
alter table table1 nologging;
alter table table2 nologging;
alter table table3 nologging;
--删除上一轮的测试数据
delete /*+ PARALLEL(t1,4) */ from table1 t1 where id like '20451477%';
commit;
delete /*+ PARALLEL(t2,4) */ from table2 t2 where id like '20451477%';
commit;
delete /*+ PARALLEL(t3,4) */ from table3 t3 where id like '20451477%';
commit;
--创建存储过程开始造数
CREATE or REPLACE PROCEDURE proc_hd_SYS_LOG_RECEPTION
as
ID number(8):=30000000;
IMEI number(8):=30000000;
begin
For i In 1 .. 12000001 Loop
Insert Into tablename1 (ID, REQUESTURL, REQUESTBODY, CREATEDATE, CREATEUSER, SYSVERSION, APPVERSION, IMEI, NETTYPE)
Select 20451477590 || Lpad(To_Char(ID), 8, '0'),
'/web/mobile/reception/qmData/syncHtData.do',
'{"info":{"token":"+PGxVM1Zmn6eNIUrcuKRdhbLOVlP9GRfxPbUKoSijCsZjkGO93S6Uc8vOE4DSd26hsLKwBI126fcBieVzvQmaDr\/KSQoFj7oT8Y5XWg5VWc=","sysVersion":"TA-1054@@Android8.0.0","appVersion":"竖屏2.5.4","imei":"358548080910173","netType":"4"},"param":{"userId":"052218171"},"postData":{"userCode":"052218171","projecCodeList":["H\/020-1517","H\/020-1518","H\/020-1519"]}}',
sysdate,
'052218171',
'TA-1054@@Android8.0.0',
'竖屏2.5.4',
3585480||Lpad(To_Char(ID), 8, '0'),
'WiFi'
from dual;
ID:=ID+1;
If Mod(i,10000)=0 then
Commit;
IMEI:=IMEI+1;
End If;
End Loop;
end;
/
exec proc_hd_SYS_LOG_RECEPTION;
commit;
---------------------------------------------
CREATE or REPLACE PROCEDURE proc_hd_qm_check_header
as
ID number(8):=30000000;
begin
For i In 1 .. 12000001 Loop
Insert Into tablename2 (ID, PROJECTCODE, ZJAVAID, ZFL, ZMANSION_NO, ZUNIT_NO, ZXLID, TASK_ID, ZJCRQ, ZSGDWID, DELETED, CREATED_BY, CREATED_ON, CREATED_TS, CHANGED_BY, CHANGED_ON, CHANGED_TS, EXT1, EXT2, EXT3, ZXTHTBH, ZWGRQ, ZJGRQ, ZBLRQ, NETWORK_ID, ZFJ_NO, USERID, VERSION, CREATE_USERNAME, USER_DEPARTMENT)
Select 20451477590 || Lpad(To_Char(ID), 8, '0'),
'H/010-1502',
'153784163'|| TRUNC( DBMS_RANDOM.VALUE(1000, 9999) )||'_'|| TRUNC( DBMS_RANDOM.VALUE(100, 999) ),
'04',
'H/010-1502-002',
'H/010-1502-002-01',
'00001',
null,
sysdate,
'00144FFBA8851ED6BD9C6607'||TRUNC( DBMS_RANDOM.VALUE(1000, 9999) )||'_'|| TRUNC( DBMS_RANDOM.VALUE(100, 999)),
'0',
'052218171',
sysdate,
sysdate,
'052218171',
sysdate,
sysdate,
'0',
null,
null,
null,
null,
null,
null,
null,
null,
null,
sysdate,
null,
null
from dual;
ID:=ID+1;
If Mod(i,10000)=0 then
Commit;
End If;
End Loop;
end;
/
exec proc_hd_qm_check_header;
commit;
-----------------------------------------------------------------------------
CREATE or REPLACE PROCEDURE proc_hd_qm_check_detail
as
ID number(8):=30000000;
begin
For i In 1 .. 12000001 Loop
insert into tablename3 (ID, PROJECTCODE, JAVAID, REL_ZJAVAID, ZMANSION_NO, ZUNIT_NO, TASK_ID, REL_GUID, ZFH, ZJCDBW, ZJCJG, ZSFHG, ZSFZG, ZPL, DWID, ZJCRQ, ZYSQK, REMARK, EXT1, EXT2, EXT3, EXT4, EXT5, EXT6, CHECKPROJECTCODE, ZXCQKSM, ZZGTG, ZFCXH, ZFCRQ, FCJAVAID, ZWTFL, ZXZB, ZYZB, USERID, VERSION, ZBSLC)
Select 20451477590 || Lpad(To_Char(ID), 8, '0'),
'H/010-1502',
'253784163'|| TRUNC( DBMS_RANDOM.VALUE(1000, 9999) )||'_'|| TRUNC( DBMS_RANDOM.VALUE(100, 999) ),
'253784163'|| TRUNC( DBMS_RANDOM.VALUE(1000, 9999) )||'_'|| TRUNC( DBMS_RANDOM.VALUE(100, 999) ),
'H/020-'||TRUNC( DBMS_RANDOM.VALUE(1000, 9999) )||'-'||TRUNC( DBMS_RANDOM.VALUE(100, 999)),
'H/020-1613-069-01',
null,
'00144FFBA8851ED6BD9C6607'||TRUNC( DBMS_RANDOM.VALUE(1000, 9999) )||'_'|| TRUNC( DBMS_RANDOM.VALUE(100, 999)),
null,
null,
null,
'1',
'0',
'0.00',
null,
'0000-00-00',
null,
null,
'1',
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
sysdate,
null
from dual;
ID:=ID+1;
If Mod(i,10000)=0 then
Commit;
End If;
End Loop;
end;
/
exec proc_hd_qm_check_detail;
commit;
-----------------------------------------------------------------------------------
--SELECT to_char((SYSDATE - TO_DATE('1970-1-1 8', 'YYYY-MM-DD HH24')) * 86400 ) AS MILLIONS FROM DUAL; /*生成秒级时间截*/
--SELECT to_char((SYSDATE - TO_DATE('1970-1-1 8', 'YYYY-MM-DD HH24')) * 86400000 + TO_NUMBER(TO_CHAR(SYSTIMESTAMP(3), 'FF'))) AS MILLIONS FROM DUAL; /*生成毫秒级时间截*/
CREATE or REPLACE PROCEDURE proc_hd_qm_check_photo
as
ID number(8):=30000000;
begin
For i In 1 .. 12000001 Loop
insert into tablename4 (ID, JAVAID, REL_DETAILID, REL_GUID, ZPL_ATTACHID, ZBUCKET, ZOBJECT_NAME, ZDISPLAY, ZIMGDESC, PROJECTCODE, IMGNAME, IMGPATH, ZZPLX, PHOTODATE, ALTERDATE, EXT1, ISCHECKED, LONGITUDE, LATITUDE, USERID, VERSION, PHOTOTYPE)
Select 20451477||TRUNC( DBMS_RANDOM.VALUE(100, 399))|| Lpad(To_Char(ID), 8, '0'),
to_char((SYSDATE - TO_DATE('1970-1-1 8', 'YYYY-MM-DD HH24')) * 86400000 + TO_NUMBER(TO_CHAR(SYSTIMESTAMP(3), 'FF')))||'_'|| TRUNC( DBMS_RANDOM.VALUE(100, 999) ),
to_char((SYSDATE - TO_DATE('1970-1-1 8', 'YYYY-MM-DD HH24')) * 86400000 + TO_NUMBER(TO_CHAR(SYSTIMESTAMP(3), 'FF')))||'_'|| TRUNC( DBMS_RANDOM.VALUE(100, 999) ),
null,
null,
'hdpstest',
'img/052218171/3/H010-1502/C/'||to_date(sysdate, 'YYYY-MM-DD')||'/'||'2000001478'||'_'||to_char((SYSDATE - TO_DATE('1970-1-1 8', 'YYYY-MM-DD HH24')) * 86400000 + TO_NUMBER(TO_CHAR(SYSTIMESTAMP(3), 'FF')))||'_'||TRUNC( DBMS_RANDOM.VALUE(100, 999) )||'.jpg',
'1',
'工程3',
'H/010-1502',
'2000001478_'||to_char((SYSDATE - TO_DATE('1970-1-1 8', 'YYYY-MM-DD HH24')) * 86400000 + TO_NUMBER(TO_CHAR(SYSTIMESTAMP(3), 'FF')))||'_'||TRUNC( DBMS_RANDOM.VALUE(100, 999) )||'.jpg',
'valueaaaaaaaaaa'||to_date(sysdate, 'YYYY-MM-DD')||'/'||to_char((SYSDATE - TO_DATE('1970-1-1 8', 'YYYY-MM-DD HH24')) * 86400000 + TO_NUMBER(TO_CHAR(SYSTIMESTAMP(3), 'FF')))||'_'||TRUNC( DBMS_RANDOM.VALUE(100, 999) )||'.jpg',
'100',
sysdate,
null,
'1',
'1',
null,
null,
null,
sysdate,
'1'
from dual;
exec proc_hd_qm_check_photo;
commit;
--恢复表日志记录
alter table user.dbname1 logging;
alter table user.dbname2 logging;
alter tableuser.dbname3 logging;
alter table user.dbname1 logging;