使用存储过程是为了快速在数据库里造一批数据,mac可以使用SQLPRO for Oracle
建表语句
CREATE TABLE "PDMSTEST18"
( "UID" NUMBER(10,0),
"FIRSTNAME" VARCHAR2(20) NOT NULL ENABLE,
"LASTNAME" VARCHAR2(20) NOT NULL ENABLE,
"MYDATE" DATE NOT NULL ENABLE,
"MYNVARCHAR2" NVARCHAR2(20),
"MYBINARY_DOUBLE" BINARY_DOUBLE,
"MYRAW" RAW(20),
"MYCLOB" CLOB,
"MYNCHAR" NCHAR(5),
"MYTIMESTAMP" TIMESTAMP (6),
"TIMESTAMPWITHTIMEZONE" TIMESTAMP (6) WITH TIME ZONE,
"MYCHAR" CHAR(5)
)
oracle数据库
CREATE OR REPLACE PROCEDURE insert100 as BEGIN for i in 1..5000000 loop INSERT INTO PDMSTEST18 VALUES ( trunc(dbms_random.value(0,1000)),#随机整数 dbms_random.string( 'x' , 10),#随机字符串 dbms_random.string( 'x' , 10), to_date(2457024 + trunc(DBMS_RANDOM.VALUE(0, 365)), 'J' ),#随机时间 'dalizi1992' , '1234' , rawtohex( '0x40' ), '0x1b' , dbms_random.string( 'x' , 3), to_timestamp( '1988-09-08 12:12:12' , 'YYYY-MM-DD HH24:MI:SS' ), timestamp '1988-09-08 12:12:12 -3:00' , 'df' ); if mod(i,1000) = 0 then #一次 commit 插入一千条数据 commit ; end if; end loop; END ; |
调用存储过程
call insert100();
查询表空间容量
select tablespace_name, file_id, file_name, round(bytes / (1024 * 1024), 0) total_space from sys.dba_data_files order by tablespace_name;
oracle 扩容(增大单个数据的容量)
alter database datafile '/u01/app/oracle/oradata/XE/system.dbf' resize 5000M;
mysql的存储过程
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 | create or replace procedure insert100( in linenum integer ) begin declare i int ; set i = 1; while i<linenum do insert into pdms_test.decimal_test3 values ( null ,# 自增 DATE_ADD( '2017-8-1 00:00:00' ,INTERVAL FLOOR(1 + (RAND() * 10800)) SECOND ), '2015-09-07' , DATE_ADD( '2010-8-1 00:00:00' ,INTERVAL FLOOR(1 + (RAND() * 108)) MINUTE ),# 随机时间 ADDTIME( CURRENT_TIME (),FLOOR(10000+(RAND()*50))),# 随机的 time ,后面的参数时类似于023000,即2小时30分钟 substring (md5(RAND()),1,10),#随机字符串 FLOOR(1 + (RAND() * 6)),#随机整数 FLOOR(7 + (RAND() * 62)), FLOOR(7 + (RAND() * 62)), FLOOR(7 + (RAND() * 62)), FLOOR(7 + (RAND() * 62)), (RAND() * 62),#随机小数 FLOOR(7 + (RAND() * 62))); if mod(i,1000) = 0 then # 一次 commit 插入1000条数据 commit ; end if; set i = i+1; end while; commit ; end |
调用存储过程
call insert100(20000000);