C + + Complete Oracle stored procedure BULK INSERT (i)

In order to meet the requirement of bulk insertion of large data volumes, we have recently researched the bulk insert of Oracle database, this article first describes the two days to understand and an unsuccessful C + + for the storage procedure Bulk INSERT scenario.

I. Oracle processing BULK INSERT Stored procedures

The related concepts involved in 1.Oracle processing of bulk inserts are: Type, bulk collect, Oracle array, and so on. Now let's take a brief look at them in turn.

1) Type

Type is primarily used for user-created Oracle custom types.

Type common custom types are described in:

A. Sub-type

A constant that is similar to a globally defined one, as long as it is changed to a place where the constant is relevant. Generic subtypes are often used for definitions of currency types, such as number (10,5), we can define subtypes: Subtype Hb_num is number (10,5), so that the precision of the currency needs to be modified, do not search all the place, just a change can be.

B.object type

Defines an employee class

Create or replace type Type_employee as Object (ID varchar2, name varchar2 (), age Integer (3), Experience VARCHAR2 (4000 ));

You can use this type to create a table, as follows:

CREATE TABLE employee of type_employee--creates an employee table

With this object type, you can also create other types, as follows

--Creating an array type Create or replace type Type_employee_arr as table of Type_employee

For more detailed information, see: Oracle Type Introduction

2) Oracle Array

A. Fixed array

Type V_arr is Varray of VARCHAR2 (50);

B. Variable arrays

Type V_arr is Table of VARCHAR2 (50);

C. Multidimensional arrays

--The following definition is the case of creating an array in the same way as 1, but the scope of the two functions is different--an array made of object objects can be used anywhere--the type defined by the table cannot define the global type, only the type v_table is defined at the function, stored procedure definition variable. Table of Poco_test%rowtype index by Binary_integer;

3) Bulk Collect

Bulk collect is used to fetch one data set at a time, which is more efficient than cursor fetching data, but it requires a lot of memory and can use the select INTO, the fetch into, the returning into statement to the bulk collect

Declaretype v_table is table of Poco_test%rowtype index by binary_integer;v_values v_table;beginselect * Bulk collect into V_values from Poco_test;end;

2. After understanding the relevant basic content, the following is the sample code of Oracle BULK INSERT

Create or replace type Poco_test_object_type asobject (ID varchar2 (), name VARCHAR2 ($), code varchar (200));

Create or Replace type Poco_test_object_arr_typeas table of Poco_test_object_type;

Create or Replace procedure Poco_test_arr_pro (V_arr in Poco_test_object_arr_type) istest_obj Poco_test_object_type;

Begin for  idx in V_arr.first (): V_arr.last loop    Test_obj: = V_arr (idx);    INSERT INTO Poco_test    values (test_obj.id,test_obj.name,test_obj.code);  End Loop;end Poco_test_arr_pro;

DECLARE ABC Poco_test_object_arr_type; o poco_test_object_type;begin O:=poco_test_object_type (a); Abc:=poco_test_object_arr_type (O,o,o); Poco_test_arr_pro (ABC); end;

Second, the JDBC call parameter is a stored procedure sample code for the array type

public class Pocotestmodel{private String Id;private string name;private string code; ...}

Connection con = null;       CallableStatement cstmt = null;  try {con = oracleconnection.getconn ();  list<pocotestmodel> orderlist = new arraylist<pocotestmodel> ();  for (int i=0;i<100000;i++) {Orderlist.add (New Pocotestmodel () (Uuid.randomuuid (). toString (), "name" +i, "code" +i));  } structdescriptor Recdesc = Structdescriptor.createdescriptor ("Poco_test_object_type", con);  arraylist<struct> pstruct = new arraylist<struct> ();  for (Pocotestmodel ord:orderlist) {object[] record = new OBJECT[3];  Record[0] = Ord.getid ();  RECORD[1] = Ord.getname ();  RECORD[1] = Ord.getcode ();                  struct item = new struct (Recdesc, con, record);  Pstruct.add (item);              } arraydescriptor Tabdesc = Arraydescriptor.createdescriptor ("Poco_test_object_arr_type", con);                     Array varray = new Array (Tabdesc, con, Pstruct.toarray ());       cstmt = Con.preparecall ("{Call Poco_test_arr_pro (?)}");Cstmt.setarray (1, Varray);  Cstmt.execute ();  Con.commit (); }catch ....

By inheriting the Oracle.sql.ORAData interface and implementing the Todatum function, you can simplify the execution of code writing

public class Pocotestmodel implements Oradata {private String ID;    private String name;       Private String Code;       public static final String _oracle_type_name = "Poco_test_object_type";    protected Mutablestruct _struct;    Static int[] _sqltype = {Oracletypes.varchar, oracletypes.varchar};    Static oradatafactory[] _factory = new Oradatafactory[_sqltype.length];    Public Pocotestmodel () {_struct = new mutablestruct (new Object[_sqltype.length], _sqltype, _factory);        } public Datum Todatum (Connection conn) throws SQLException {_struct.setattribute (0, this.id);        _struct.setattribute (1, this.name); _struct.setattribute (1, This.code);    Return _struct.todatum (conn, _oracle_type_name);        } public Pocotestmodel (string id,string name, string code) {this ();        This.id = ID;    this.name = Name;this.code = code; }....} Connection con = null;     CallableStatement cstmt = null;          try {con = oracleconnection.getconn (); System.out.println (New Date ()); list<pocotestmodel> orderlist = new arraylist<pocotestmodel> (); for (int i=0;i<100000;i++) {        Orderlist.add (New Pocotestmodel (Uuid.randomuuid () toString (), "name" +i, "code" +i));            Arraydescriptor Tabdesc = Arraydescriptor.createdescriptor ("Poco_test_object_arr_type", con);       Array varray = new Array (Tabdesc, con, Orderlist.toarray ());     cstmt = Con.preparecall ("{Call Poco_test_arr_pro (?)}");               Cstmt.setarray (1, Varray); Cstmt.execute (); Con.commit ();} Catch...

Above is the data of the Oracle Bulk Insert found, for the C + + aspect, did not find a similar method like JDBC, provides the array type of parameters of the stored procedure call. The next article will cover a different scenario to complete the bulk insert of Oracle data.

C + + Complete Oracle stored procedure BULK INSERT (i)