达梦数据库、OceanBase和oracle数据使用存储过程批量插入数据

表：
CREATE TABLE dm_varchar
(
    id              int IDENTITY(1,1) NOT NULL PRIMARY KEY,
    aes             varchar(1000) DEFAULT NULL,
    sm4             varchar(1000) DEFAULT NULL,
    sm4_a           varchar(1000) DEFAULT NULL,
    email           varchar(1000) DEFAULT NULL,
    phone           varchar(1000) DEFAULT NULL,
    ssn             varchar(1000) DEFAULT NULL,
    military        varchar(1000) DEFAULT NULL,
    credit_card     varchar(1000) DEFAULT NULL,
    job	varchar(1000) DEFAULT NULL
);

 达梦数据库使用存储过程批量插入100w条数据

存储过程：

declare
    i int:=1;            
begin
  loop                  
    i:=i+1;             
    INSERT INTO dm_varchar (aes, sm4, sm4_a, email, phone, ssn, military,  credit_card, job) VALUES ('胡小芬', '北京xx网络技术有限公司', '北京市', 'xxx@yahoo.com', '15652996964', '210302199608124861', '武水电字第3632734号',  '6227612145830440', '测试开发工程师');
    exit when i=1000000;   
  end loop;
 end;

 OceanBase数据库使用存储过程批量插入100w条数据

2.1、创建表

CREATE TABLE ocean_varchar (
  ID int(11) NOT NULL AUTO_INCREMENT,
  aes varchar(1000) CHARACTER SET utf8 DEFAULT NULL,
  sm4 varchar(1000) CHARACTER SET utf8 DEFAULT NULL,
  sm4_a varchar(1000) CHARACTER SET utf8 DEFAULT NULL,
  email varchar(1000) CHARACTER SET utf8 DEFAULT NULL,
  phone varchar(1000) CHARACTER SET utf8 DEFAULT NULL,
  ssn varchar(1000) CHARACTER SET utf8 DEFAULT NULL,
  military varchar(1000) CHARACTER SET utf8 DEFAULT NULL,
  credit_card varchar(1000) CHARACTER SET utf8 DEFAULT NULL,
  job varchar(1000) CHARACTER SET utf8 DEFAULT NULL,
  PRIMARY KEY (ID) USING BTREE
);

2.2、存储过程

create procedure test()
begin
declare i INT;
set i=0;
while i<5000000 do
 INSERT INTO ocean_varchar (aes, sm4, sm4_a, email, phone, ssn, military,  credit_card, job) VALUES ('胡小芬', '北京xx网络技术有限公司', '北京市', 'cpq@yahoo.com', '15652996964', '210302199608124861', '武水电字第3632734号',  '6227612145830440', '测试开发工程师');
set i=i+1;
end while;
select count(*) from ocean_varchar;
end;

call test();

Oracle数据库存储结构插入数据

存储过程和达梦类似

1、创建表

create table TESTSDK(
       id int primary key,     
       name varchar2(1000),
       email varchar2(1000)
);


--建立自定义序列
CREATE SEQUENCE varchar2_sequence
increment by 1                  -- 每次加几个
start with 1                    -- 从1开始计数
nomaxvalue                      -- 不设置最大值
nocycle                         -- 一直累加，不循环
nocache                         -- 不建缓冲区

create trigger varchar2_trig before
insert on TESTSDK for each row when (new.id is null)
begin
 select varchar2_sequence.nextval into:new.id from dual;
 end;

INSERT INTO TESTSDK (name,email) VALUES ('MDEBI0VniavN7wEjRWeJq83vcDWnwbaWwDz2WEZWq2IuPukh3bZrG1fyl8ex|^|','test@sina.com');

2、存储过程插入10w条数据

declare
    i int:=1;            
begin
  loop                  
    i:=i+1;             
    INSERT INTO TESTSDK (name,email) VALUES ('MDEBI0VniavN7wEjRWeJq83vcDWnwbaWwDz2WEZWq2IuPukh3bZrG1fyl8ex|^|','test@sina.com');
    exit when i=1000001;   
  end loop;
 end;

 

3、查询结果