经过长时间接触Oracle Insert和bulk Insert，我对比了一下他们的执行效率。在这里和大家分享一下，希望你看完本文后有不少收获。

测试java的insert 同使用9i以后的bulk Insert 的速度，结果显示通过bulk Insert 速度相当的快.

100000条记录

insert ,—————93秒

bulk Insert ————-0.441秒

环境:

oracle 10.2.0.3 Windows 2000Server

java

代码:

SQL>desca 
NameTypeNullableDefaultComments --------------------------------------- 
IDINTEGERY NAMEVARCHAR2(20)Y 

bulk Insert 使用的类型及过程

createorreplacetypei_tableistableofnumber(10); 
createorreplacetypev_tableistableofvarchar2(10); createorreplaceprocedurepro_forall_insert(v_1i_table,v_2v_table) 
as cinteger; 
begin foralliin1..v_1.count 
insertintoavalues(v_1(i),v_2(i)); end; 

测试的java代码:

importjava.io.*; 
importjava.sql.*; importjava.util.*; 
importjavax.naming.Context; importjavax.naming.InitialContext; 
importjavax.naming.*; importoracle.jdbc.OracleTypes; 
importoracle.sql.*; importoracle.sql.ARRAY; 
importoracle.sql.ArrayDescriptor; importoracle.sql.STRUCT; 
importoracle.sql.StructDescriptor; importjava.sql.Connection; 
importjava.sql.DriverManager; importoracle.jdbc.OracleCallableStatement; 
publicclasstestOracle{ 
publictestOracle(){ ConnectionoraCon=null; 
PreparedStatementps=null; Statementst=null; 
ResultSetrs=null; try{ 
try{ Class.forName("oracle.jdbc.driver.OracleDriver"); 
}catch(ClassNotFoundExceptionex){} oraCon=DriverManager.getConnection("jdbc:oracle:thin:@192.168.15.234:1521:ora10g","imcs","imcs"); 
oraCon.setAutoCommit(false); }catch(SQLExceptionex){ 
ex.printStackTrace(); } 
CallableStatementcstmt=null; oracle.sql.ArrayDescriptora=null; 
oracle.sql.ArrayDescriptorb=null; if(1==1) 
{ Object[]s1=newObject[100000]; 
Object[]s2=newObject[100000]; for(inti=0;i<100000;i++){ 
s1[i]=newInteger(1); s2[i]=newString("aaa").concat(String.valueOf(i)); 
} try{ 
a=oracle.sql.ArrayDescriptor.createDescriptor("I_TABLE",oraCon); b=oracle.sql.ArrayDescriptor.createDescriptor("V_TABLE",oraCon); 
ARRAYa_test=newARRAY(a,oraCon,s1); ARRAYb_test=newARRAY(b,oraCon,s2); 
cstmt=oraCon.prepareCall("{callpro_forall_insert(?,?)}"); cstmt.setObject(1,a_test); 
cstmt.setObject(2,b_test); longaaaa=System.currentTimeMillis(); 
System.out.println(System.currentTimeMillis()); cstmt.execute(); 
oraCon.commit(); System.out.println(System.currentTimeMillis()-aaaa); 
catch(Exceptione){ e.printStackTrace(); 
} } 
else { 
try { 
PreparedStatementoraPs=null; StringoraInsertSql= 
"insertintoavalues(?,?)"; oraPs=oraCon.prepareStatement(oraInsertSql); 
longaaaa=System.currentTimeMillis(); System.out.println(System.currentTimeMillis()); 
for(inti=0;i<100000;i++) { 
oraPs.setInt(1,i); oraPs.setString(2,newString("aaa").concat(String.valueOf(i))); 
oraPs.executeUpdate(); } 
oraCon.commit(); System.out.println(System.currentTimeMillis()-aaaa); 
} catch(SQLExceptionex) 
{ System.out.print("dddddd"); 
System.out.print(ex.getMessage()); } 
} try{ 
jbInit(); }catch(Exceptionex){ 
ex.printStackTrace(); } 
} publicstaticvoidmain(Stringargs[]){ 
testOraclea=newtestOracle(); } 
privatevoidjbInit()throwsException{ } 
};