ORACLE批量操作

ORACLE批量操作

---------------------------------------------------------------------------
-- 建立表
----------------------------------------------------------------------------
drop table demo;
create table demo (id number, name varchar2(255));
----------------------------------------------------------------------------
-- 批量操作
----------------------------------------------------------------------------

declare
type id_table_type is table of number index by binary_integer;
type name_table_type is table of varchar2(255) index by binary_integer;
id_table id_table_type;
name_table name_table_type;
start_time number;
end_time number;
v_count number := 100000;
begin
--------------------------------------------------------------------------
-- 1、不使用批量插入資料
--------------------------------------------------------------------------
for i in 1 .. v_count loop
id_table(i) := i;
name_table(i) := 'name' || i;
end loop;

start_time := dbms_utility.get_time;
for i in 1 .. v_count loop
insert /*+append*/
into demo
values
(id_table(i), name_table(i));
end loop;
end_time := dbms_utility.get_time;

dbms_output.put_line('不用批量插入' || v_count || '條:' ||
lpad(to_char((end_time - start_time) / 100), 5, ' '));
--------------------------------------------------------------------------
-- 2、使用批量插入資料
--------------------------------------------------------------------------
for i in v_count + 1 .. v_count * 2 loop
id_table(i) := i;
name_table(i) := 'name' || i;
end loop;

start_time := dbms_utility.get_time;
forall i in v_count + 1 .. v_count * 2
insert /*+append*/
into demo
values
(id_table(i), name_table(i));
end_time := dbms_utility.get_time;

dbms_output.put_line(' 用批量插入' || v_count || '條:' ||
lpad(to_char((end_time - start_time) / 100), 5, ' '));
--------------------------------------------------------------------------
-- 3、使用批量更新資料
--------------------------------------------------------------------------
for i in 1 .. v_count loop
id_table(i) := i;
name_table(i) := 'NB' || i;
end loop;

start_time := dbms_utility.get_time;
for i in 1 .. v_count loop
update demo set name = name_table(i) where id = id_table(i);
end loop;
end_time := dbms_utility.get_time;

dbms_output.put_line('不用批量更新' || v_count || '條:' ||
lpad(to_char((end_time - start_time) / 100), 5, ' '));

start_time := dbms_utility.get_time;
forall i in v_count + 1 .. v_count * 2
update demo set name = name_table(i) where id = id_table(i);
end_time := dbms_utility.get_time;

dbms_output.put_line(' 用批量更新' || v_count || '條:' ||
lpad(to_char((end_time - start_time) / 100), 5, ' '));
--------------------------------------------------------------------------
-- 4、使用批量刪除資料
--------------------------------------------------------------------------
for i in 1 .. v_count loop
id_table(i) := i;
end loop;

start_time := dbms_utility.get_time;
forall i in 1 .. id_table.count
delete from demo where id = id_table(i);
end_time := dbms_utility.get_time;

dbms_output.put_line(' 用批量刪除' || v_count || '條:' ||
lpad(to_char((end_time - start_time) / 100), 5, ' '));
end;
/

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/720091/viewspace-1057796/，如需轉載，請註明出處，否則將追究法律責任。