Oracle bulk Insert的用法

首先创建一个简单的数据库表：

create table T_NAME
(
  id   NUMBER not null,
  name VARCHAR2(20)
)

alter table T_NAME
  add constraint PRIMARY_ID primary key (ID);

字段

字段长度

Nullable

Key

ID

NUMBER(6)

Not Nulll

Primary

NAME

varchar2(20)

 

 

根据相应的表结构创建过程

create or replace type ID_TABLE is table of number(10);  
create or replace type NAME_TABLE is table of varchar2(20);  
create or replace procedure pro_foralls_insert(v_1 ID_TABLE ,v_2 NAME_TABLE )  
as  
c integer;  
begin  
forall i in 1.. v_1.count   
insert into T_NAME values(v_1(i),v_2(i));  
end;  

接着就写相应的java程序做测试

import java.sql.*;  
import oracle.sql.ARRAY;
import oracle.sql.ArrayDescriptor;


public class TestTest {  
private static final String sql="select max(id) from T_NAME";
private static final String jdbcUrl="jdbc:oracle:thin:@10.108.67.51:1521:orcl";
private static final String jdbcUser="b2cmonitor";
private static final String jdbcPassword="b2cmonitor";
private static int maxID;
public TestTest() {  
Connection conn = null;
Statement stmt=null;
CallableStatement cstmt = null;  
ResultSet rs=null;

try {  
Class.forName("oracle.jdbc.driver.OracleDriver");  
conn = DriverManager.getConnection(jdbcUrl, jdbcUser,jdbcPassword);  
conn.setAutoCommit(false);  
stmt=conn.createStatement();
rs=stmt.executeQuery(sql);
while(rs.next()){
maxID=rs.getInt("MAX(ID)");
}
}
catch (ClassNotFoundException ex) {
ex.printStackTrace();
}  
catch (SQLException ex) {  
ex.printStackTrace();  
} 

ArrayDescriptor a = null;  
ArrayDescriptor b = null;  
Object[] s1 = new Object[100000];  
Object[] s2 = new Object[100000];  
System.out.println(maxID);
for (int i = 0; i < 100000; i++) {  
s1[i] = i+1+maxID;
s2[i] = "SSSS";  
}  
try {
a = oracle.sql.ArrayDescriptor.createDescriptor("ID_TABLE", conn);
b = oracle.sql.ArrayDescriptor.createDescriptor("NAME_TABLE", conn);  
ARRAY a_test = new ARRAY(a, conn, s1);  
ARRAY b_test = new ARRAY(b, conn, s2);  
cstmt = conn.prepareCall("{ call pro_foralls_insert(?,?) }"); 
cstmt.setObject(1, a_test);  
cstmt.setObject(2, b_test);  
System.out.println(System.currentTimeMillis());  
cstmt.execute();  
conn.commit();  
rs.close();
rs=null;
stmt.close();
stmt=null;
cstmt.close();
cstmt=null;
conn.close();
conn=null;

} catch (SQLException e) {
e.printStackTrace();
}  

}
public static void main(String args[]) {  
TestTest a = new TestTest();  
}  


}