关闭

Oracle 逐条和批量插入数据方式对比

499人阅读 评论(0) 收藏 举报

创建测试表

create table base_users

(

  userid         varchar2(16),

  username  varchar2(32),  

  passwd      varchar2(16)

)tablespace cat_data;


采用一条一条插入的方式

create or replace procedure insert_data_one_by_one(n in number)

as

    currentId number(16) := 0;

    l_userid varchar2(16);

    l_username varchar2(32);

    sqltext varchar2(256);

begin
    dbms_output.put_line('begin ...' || to_char(current_timestamp, 'HH24:MI:SSxFF'));

    sqltext := 'insert into base_users(userid, username, passwd) values(:userid, :username,' || '111111 )';

    loop
        currentId:=currentId + 1;
        l_userid:= to_char(currentId);
        l_username:= to_char(18600000000 + currentId);

        execute immediate sqltext using l_userid, l_username;
        exit when currentId >= n;
    end loop;
    commit;
    dbms_output.put_line('end commit ...' || to_char(current_timestamp, 'HH24:MI:SSxFF'));
end insert_data_one_by_one;
/


采用批量插入的方式

create or replace procedure insert_data_bulk(n in number)

as

  i int;

  tmp_userid         number;

  tmp_username  number;

  type useridArray is table of varchar2(16) index by binary_integer;

  type usernameArray is table of varchar2(32) index by binary_integer;

  puserid useridArray;

  pusername usernameArray;

begin

  dbms_output.put_line('begin ...' || to_char(current_timestamp, 'HH24:MI:SSxFF'));

  tmp_userid := 1;

  tmp_username := 18600000000;

  for i in 1 .. n loop

    puserid(i) := tmp_userid;

    pusername(i) := tmp_username;

    tmp_userid := tmp_userid + 1;

    tmp_username := tmp_username + 1;

  end loop;

  forall i in 1 ..n

    insert into base_users(userid, username, passwd)

                 values(puserid(i), pusername(i), '111111');

  commit;

  dbms_output.put_line('end ...' || to_char(current_timestamp, 'HH24:MI:SSxFF'));

endinsert_data_bulk;

/


测试1千万条数据的插入

SQL>set serveroutput on

SQL>begin

insert_data_one_by_one(10000000);

end;

/

begin ...22:14:01.572928000
end commit ...22:20:43.911104000


SQL>truncate table base_users;


SQL>begin

insert_data_bulk(10000000);

end;

/

begin ...22:25:31.497810000
end ...22:27:23.801515000

0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:70053次
    • 积分:1135
    • 等级:
    • 排名:千里之外
    • 原创:48篇
    • 转载:5篇
    • 译文:0篇
    • 评论:3条
    文章分类
    最新评论