Oracle存储过程做大数据量插入

顶 
0

踩 最近在项目中用到了JAVA客户端传递对象数组到Oracle存储过程做大数据量插入,比如10万级别. 
下面做一个插入10万条记录的示例步骤,,为了容易理解,表的结果很简单. 
1,假设表结构如下: 
源码copy to clipboard打印？
01.CREATE TABLE UKBNOVCTCORDER(   
02.LosingLEName varchar2(200),   
03.LosingLECode varchar2(200)   
04.)  
CREATE TABLE UKBNOVCTCORDER(
LosingLEName varchar2(200),
LosingLECode varchar2(200)
)2,在数据库建立一个type,对应JAVA端要传入的对象结构: 
源码copy to clipboard打印？
01.CREATE OR REPLACE TYPE BUT_UKBNOV_CTC_ORDER_REC AS OBJECT (   
02.  losingLEName VARCHAR2(200),   
03.     losingLECode VARCHAR2(200)   
04.);  
CREATE OR REPLACE TYPE BUT_UKBNOV_CTC_ORDER_REC AS OBJECT (
  losingLEName VARCHAR2(200),
     losingLECode VARCHAR2(200)
);3,为了数组传输,建立一个数组类型的type: 
CREATE OR REPLACE TYPE BUT_UKBNOV_CTC_ORDER_TAB AS TABLE OF BUT_UKBNOV_CTC_ORDER_REC 
4,建立存储过程做插入工作: 
源码copy to clipboard打印？
01.CREATE OR REPLACE procedure bulkInsertCTDORDER(i_orders IN BUT_UKBNOV_CTC_ORDER_TAB)   
02.as   
03.ctcOrder BUT_UKBNOV_CTC_ORDER_REC;   
04.begin   
05.    FOR idx IN i_orders.first()..i_orders.last() LOOP   
06.        ctcOrder:=i_orders(idx);   
07.        INSERT INTO UKBNOVCTCORDER   
08.          (LosingLEName,   
09.           LosingLECode)   
10.        VALUES   
11.          (ctcOrder.losingLEName,   
12.           ctcOrder.losingLECode);   
13.    end loop;   
14.    exception when others then   
15.    raise;   
16.end;  
CREATE OR REPLACE procedure bulkInsertCTDORDER(i_orders IN BUT_UKBNOV_CTC_ORDER_TAB)
as
ctcOrder BUT_UKBNOV_CTC_ORDER_REC;
begin
    FOR idx IN i_orders.first()..i_orders.last() LOOP
        ctcOrder:=i_orders(idx);
        INSERT INTO UKBNOVCTCORDER
          (LosingLEName,
           LosingLECode)
        VALUES
          (ctcOrder.losingLEName,
           ctcOrder.losingLECode);
    end loop;
    exception when others then
    raise;
end;5,建立JAVA端java bean对象,(为节省版面,下面的get set方法省略,) 
源码copy to clipboard打印？
01.public class UkbnovCTCOrder  {   
02.    private String losingLEName;   
03.    private String losingLECode;   
04......  
public class UkbnovCTCOrder  {
    private String losingLEName;
    private String losingLECode;
.....在JAVA端访问存储过程插入数据,需要做JAVA数据类型和存储过程类型type的映射,下面的StructDescriptor是mapping Oracle端AS OBJECT类型, 
tabDesc 是mapping Oracle端数组 AS TABLE OF类型的. 
源码copy to clipboard打印？
01.Connection con = null;   
02.CallableStatement cstmt = null;        
03.try {                  
04.    con = OracleConnection.getConn();   
05.    List<UkbnovCTCOrder> orderList = new ArrayList<UkbnovCTCOrder>();   
06.    for(int i=0;i<100000;i++){   
07.        orderList.add(new UkbnovCTCOrder("losingLEName"+i,"losingLECode+"+i));   
08.    }               
09.    StructDescriptor recDesc = StructDescriptor.createDescriptor("BUT_UKBNOV_CTC_ORDER_REC", con);   
10.    ArrayList<STRUCT> pstruct = new ArrayList<STRUCT>();   
11.    for (UkbnovCTCOrder ord:orderList) {                   
12.        Object[] record = new Object[2];   
13.        record[0] = ord.getLosingLEName();   
14.        record[1] = ord.getLosingLECode();   
15.        STRUCT item = new STRUCT(recDesc, con, record);                   
16.        pstruct.add(item);   
17.    }              
18.    ArrayDescriptor tabDesc = ArrayDescriptor.createDescriptor("BUT_UKBNOV_CTC_ORDER_TAB", con);               
19.    ARRAY vArray = new ARRAY(tabDesc, con, pstruct.toArray());                      
20.    cstmt = con.prepareCall("{call bulkInsertCTDORDER(?)}");        
21.    cstmt.setArray(1, vArray);                    
22.    cstmt.execute();   
23.    con.commit();  
        Connection con = null;
        CallableStatement cstmt = null;     
        try {               
            con = OracleConnection.getConn();
            List<UkbnovCTCOrder> orderList = new ArrayList<UkbnovCTCOrder>();
            for(int i=0;i<100000;i++){
                orderList.add(new UkbnovCTCOrder("losingLEName"+i,"losingLECode+"+i));
            }            
            StructDescriptor recDesc = StructDescriptor.createDescriptor("BUT_UKBNOV_CTC_ORDER_REC", con);
            ArrayList<STRUCT> pstruct = new ArrayList<STRUCT>();
            for (UkbnovCTCOrder ord:orderList) {                
                Object[] record = new Object[2];
                record[0] = ord.getLosingLEName();
                record[1] = ord.getLosingLECode();
                STRUCT item = new STRUCT(recDesc, con, record);                
                pstruct.add(item);
            }           
            ArrayDescriptor tabDesc = ArrayDescriptor.createDescriptor("BUT_UKBNOV_CTC_ORDER_TAB", con);            
            ARRAY vArray = new ARRAY(tabDesc, con, pstruct.toArray());                   
            cstmt = con.prepareCall("{call bulkInsertCTDORDER(?)}");     
            cstmt.setArray(1, vArray);                 
            cstmt.execute();
            con.commit();6,如果每次调用都需要做Java bean的到Oracle的"AS OBJECT"类型的mapping,则很繁琐,可以然Java bean实现oracle.sql.ORAData,这样就不用在调用时候在做mapping了. 
java bean对象如下,为节省版面get set方法省略. 
源码copy to clipboard打印？
01.public class UkbnovCTCOrder1 implements ORAData {   
02.    private String losingLEName;   
03.    private String losingLECode;   
04.    public static final String _ORACLE_TYPE_NAME = "BUT_UKBNOV_CTC_ORDER_REC";      
05.    protected MutableStruct _struct;   
06.    static int[] _sqlType = { OracleTypes.VARCHAR, OracleTypes.VARCHAR };   
07.    static ORADataFactory[] _factory = new ORADataFactory[_sqlType.length];   
08.    public UkbnovCTCOrder1() {   
09.        _struct = new MutableStruct(new Object[_sqlType.length], _sqlType, _factory);   
10.    }   
11.    public Datum toDatum(Connection conn) throws SQLException {   
12.        _struct.setAttribute(0, this.losingLEName);   
13.        _struct.setAttribute(1, this.losingLECode);   
14.        return _struct.toDatum(conn, _ORACLE_TYPE_NAME);   
15.    }   
16.    public UkbnovCTCOrder1(String losingLEName, String losingLECode) {   
17.        this();   
18.        this.losingLEName = losingLEName;   
19.        this.losingLECode = losingLECode;   
20.    }   
21.....  
public class UkbnovCTCOrder1 implements ORAData {
    private String losingLEName;
    private String losingLECode;
    public static final String _ORACLE_TYPE_NAME = "BUT_UKBNOV_CTC_ORDER_REC";   
    protected MutableStruct _struct;
    static int[] _sqlType = { OracleTypes.VARCHAR, OracleTypes.VARCHAR };
    static ORADataFactory[] _factory = new ORADataFactory[_sqlType.length];
    public UkbnovCTCOrder1() {
        _struct = new MutableStruct(new Object[_sqlType.length], _sqlType, _factory);
    }
    public Datum toDatum(Connection conn) throws SQLException {
        _struct.setAttribute(0, this.losingLEName);
        _struct.setAttribute(1, this.losingLECode);
        return _struct.toDatum(conn, _ORACLE_TYPE_NAME);
    }
    public UkbnovCTCOrder1(String losingLEName, String losingLECode) {
        this();
        this.losingLEName = losingLEName;
        this.losingLECode = losingLECode;
    }
....调用的时候不需要再做Java bean 到Oracle "AS OBJECT"数据类型的mapping,只需要做数组类型的mapping,如下: 
源码copy to clipboard打印？
01.Connection con = null;   
02.CallableStatement cstmt = null;        
03.try {        
04.    con = OracleConnection.getConn();              
05.    System.out.println(new Date());   
06.    List<UkbnovCTCOrder1> orderList = new ArrayList<UkbnovCTCOrder1>();   
07.    for(int i=0;i<100000;i++){   
08.        orderList.add(new UkbnovCTCOrder1("losingLEName"+i,"losingLECode+"+i));   
09.    }           
10.    ArrayDescriptor tabDesc = ArrayDescriptor.createDescriptor("BUT_UKBNOV_CTC_ORDER_TAB", con);               
11.    ARRAY vArray = new ARRAY(tabDesc, con, orderList.toArray());          
12.       
13.    cstmt = con.prepareCall("{call bulkInsertCTDORDER(?)}");        
14.    cstmt.setArray(1, vArray);                  
15.    cstmt.execute();   
16.    con.commit();  
        Connection con = null;
        CallableStatement cstmt = null;     
        try {     
            con = OracleConnection.getConn();           
            System.out.println(new Date());
            List<UkbnovCTCOrder1> orderList = new ArrayList<UkbnovCTCOrder1>();
            for(int i=0;i<100000;i++){
                orderList.add(new UkbnovCTCOrder1("losingLEName"+i,"losingLECode+"+i));
            }        
            ArrayDescriptor tabDesc = ArrayDescriptor.createDescriptor("BUT_UKBNOV_CTC_ORDER_TAB", con);            
            ARRAY vArray = new ARRAY(tabDesc, con, orderList.toArray());       

            cstmt = con.prepareCall("{call bulkInsertCTDORDER(?)}");     
            cstmt.setArray(1, vArray);               
            cstmt.execute();
            con.commit();上面的示例在插入10万条记录只用了5秒(当然也和这里的表结构字段少有关系). 

原文链接：http://blog.csdn.net/kkdelta/article/details/7226331