用集合操作 bulk colloct forall 给你个例子 declare type dept_no is table of number; d_no dept_no;
begin select deptno bulk collect into d_no from dept; forall i in 1..d_no.count insert into emp_2 select * from emp where deptno=d_no(i); for i in 1..d_no.count loop
-- Count how many rows were inserted for each department; that is, -- how many employees are in each department. DBMS_OUTPUT.put_line( 'Dept ' || d_no(i) || ': inserted ' || SQL%BULK_ROWCOUNT(i) || ' records'); END LOOP; DBMS_OUTPUT.put_line('Total records inserted =' || SQL%ROWCOUNT); end;
http://yumianfeilong.com/2007/03/20/sqlldr%E8%AF%AD%E6%B3%95/
你可以用java写一个date.txt文件,比如内容如下:1|wang|China
2|zhang|USA然后写一个sql loader的配置文件test.ctl:load data
infile 'date.txt'
replace into table myTable
fields terminated by "|"
(Number,
UserName,
Country
)最后用sqlloader的命令插入数据库, cmd下就可以用
sqlldr scott/tiger@orcl control=test.ctl log=test.log bad=test.bad direct=true
替换会清空表中原来的数据,要注意
或者insert /* +append */ into
bulk colloct
forall
给你个例子
declare type dept_no is table of number;
d_no dept_no;
begin
select deptno bulk collect into d_no from dept;
forall i in 1..d_no.count
insert into emp_2
select * from emp
where deptno=d_no(i);
for i in 1..d_no.count loop
-- Count how many rows were inserted for each department; that is,
-- how many employees are in each department.
DBMS_OUTPUT.put_line( 'Dept '
|| d_no(i)
|| ': inserted '
|| SQL%BULK_ROWCOUNT(i)
|| ' records');
END LOOP; DBMS_OUTPUT.put_line('Total records inserted =' || SQL%ROWCOUNT);
end;
2、批量提交,而不是每插入一条提交一次。