没有办法实现,用游标本来就是将结果集放在内存中了。 看你的数据加工过程是否复杂,如果用一个SQL能写出来,你可以使用INSERT INTO TABLE_NAME SELECT ....
oracle9.2.0 [表2]有10万条纪录的测试 insert into 表1 select * from 表2;耗时 6.969秒 ------------------------------------------------ 分2次插入 耗时 3.332秒 在执行一次为 1.932秒 create or replace procedure sp_insert_test2 asdec_count_wk1 number;--纪录test表的总数dec_js_wk1 number;--开始插入的标记 begin dec_js_wk1 := 0; -- 初始化为0 select count(*) into dec_count_wk1 from test; --test表有10万条纪录
dec_count_wk1 := dec_count_wk1/2;-- 初始化为5万 for iii in 1..2 --插2次 loop insert into test2 select sys_no[字段名], sys_date[字段名] from (select rownum ro, sys_no, sys_date from test) where ro >=dec_js_wk1 and ro <= dec_count_wk1;
end loop; commit; exception when others then ......; rollback; end; 下面是两张表的结构 test 和 test2结构相同Name Type Nullable Default Comments -------- ------------- -------- ------- -------- SYS_NO VARCHAR2(100) Y SYS_DATE DATE Y sysdate
用数组加forall/buck collect到google上搜索一下,例子很多
找了个Thomas Kyte 的 bulk insert的例子,做参考吧 -------------------------------------------set echo oncreate or replace type vcArray as table of varchar2(400) / create or replace type dtArray as table of date / create or replace type nmArray as table of number /procedure native_dynamic_array( p_tname in varchar2, p_arraysize in number default 100, p_rows in number default 500 ) is l_stmt long; l_theCursor integer; l_status number; l_col1 nmArray := nmArray(); l_col2 dtArray := dtArray(); l_col3 vcArray := vcArray(); l_cnt number := 0; begin /* * We will make up data here. When we've made up ARRAYSIZE * rows, we'll bulk insert them. At the end of the loop, * if any rows remain, we'll insert them as well. */ l_col1.extend( p_arraysize ); l_col2.extend( p_arraysize ); l_col3.extend( p_arraysize ); for i in 1 .. p_rows loop l_cnt := l_cnt+1; l_col1( l_cnt ) := i; l_col2( l_cnt ) := sysdate+i; l_col3( l_cnt ) := to_char(i); if (l_cnt = p_arraysize) then execute immediate 'begin forall i in 1 .. :n insert into ' || p_tname || ' q4 ( a, b, c ) values ( :a(i), :b(i), :c(i) ); end;' USING l_cnt, l_col1, l_col2, l_col3; l_cnt := 0; end if; end loop; if (l_cnt > 0 ) then execute immediate 'begin forall i in 1 .. :n insert into ' || p_tname || ' q4 ( a, b, c ) values ( :a(i), :b(i), :c(i) ); end;' USING l_cnt, l_col1, l_col2, l_col3; end if; end;end load_data; /
有一种方法,不过我不知道是提高效率还是减低效率。你可以试试
建一个临时表,将加工后数据插入临时表,最后 insert /* + append */ into tablename select * from tmptable;
看你的数据加工过程是否复杂,如果用一个SQL能写出来,你可以使用INSERT INTO TABLE_NAME SELECT ....
[表2]有10万条纪录的测试
insert into 表1 select * from 表2;耗时 6.969秒
------------------------------------------------
分2次插入 耗时 3.332秒 在执行一次为 1.932秒
create or replace procedure sp_insert_test2
asdec_count_wk1 number;--纪录test表的总数dec_js_wk1 number;--开始插入的标记
begin
dec_js_wk1 := 0; -- 初始化为0
select count(*) into dec_count_wk1 from test; --test表有10万条纪录
dec_count_wk1 := dec_count_wk1/2;-- 初始化为5万 for iii in 1..2 --插2次
loop
insert into test2
select sys_no[字段名],
sys_date[字段名] from
(select rownum ro,
sys_no,
sys_date
from test)
where ro >=dec_js_wk1
and ro <= dec_count_wk1;
dec_js_wk1 := dec_count_wk1 ; --起始为5万
dec_count_wk1 := dec_count_wk1 * 2; --结束为10万
end loop;
commit;
exception
when others then ......;
rollback;
end;
下面是两张表的结构
test 和 test2结构相同Name Type Nullable Default Comments
-------- ------------- -------- ------- --------
SYS_NO VARCHAR2(100) Y
SYS_DATE DATE Y sysdate
-------------------------------------------set echo oncreate or replace type vcArray as table of varchar2(400)
/
create or replace type dtArray as table of date
/
create or replace type nmArray as table of number
/procedure native_dynamic_array( p_tname in varchar2,
p_arraysize in number default 100,
p_rows in number default 500 )
is
l_stmt long;
l_theCursor integer;
l_status number;
l_col1 nmArray := nmArray();
l_col2 dtArray := dtArray();
l_col3 vcArray := vcArray();
l_cnt number := 0;
begin
/*
* We will make up data here. When we've made up ARRAYSIZE
* rows, we'll bulk insert them. At the end of the loop,
* if any rows remain, we'll insert them as well.
*/
l_col1.extend( p_arraysize );
l_col2.extend( p_arraysize );
l_col3.extend( p_arraysize );
for i in 1 .. p_rows
loop
l_cnt := l_cnt+1;
l_col1( l_cnt ) := i;
l_col2( l_cnt ) := sysdate+i;
l_col3( l_cnt ) := to_char(i); if (l_cnt = p_arraysize)
then
execute immediate
'begin
forall i in 1 .. :n
insert into ' || p_tname || '
q4 ( a, b, c ) values ( :a(i), :b(i), :c(i) );
end;'
USING l_cnt, l_col1, l_col2, l_col3;
l_cnt := 0;
end if;
end loop;
if (l_cnt > 0 )
then
execute immediate
'begin
forall i in 1 .. :n
insert into ' || p_tname || '
q4 ( a, b, c ) values ( :a(i), :b(i), :c(i) );
end;'
USING l_cnt, l_col1, l_col2, l_col3;
end if;
end;end load_data;
/