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)asctcOrder 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我的异常网推荐解决方案：oracle存储过程,http://www.myexception.cn/oracle-develop/177537.html