C ++ completes batch insertion of Oracle stored procedures (1)

C ++ completes batch insertion of Oracle stored procedures (1)

To meet the need for batch insertion of large data volumes, we recently studied batch insertion of Oracle databases, this article first introduces what we have learned over the past two days and an unsuccessful batch insertion solution for C ++ stored procedures.

1. Oracle batch insert Stored Procedure

1. concepts related to batch insert in Oracle include Type, bulk collect, and Oracle array. Now we will give a brief introduction to them in sequence.

1) Type

Type is used to create an Oracle custom Type.

Common custom types:

A. Subtype

For a constant similar to a global definition, you only need to change one place and all the places related to this constant have been modified. Generally, subtypes are commonly used in the definition of currency types, such as number (). We can define the subtype: subtype hb_num is number (). When the precision of the currency needs to be modified, you do not need to search all the places. You only need to make one modification.

B. Object Type

Defines an employee class

create or replace type type_employee as object(id varchar2(36),name varchar2(50),age integer(3),experience varchar2(4000));

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

Create table employee of type_employee -- the employee table is created.

This Object type can also be used to create other types, as shown below:

-- Create or replace type type_employee_arr as table of type_employee

For more details, see Oracle Type introduction.

2) oracle Array

A. Fixed Array

type v_arr is varray(10) of varchar2(50);

B. variable array

type v_arr is table of varchar2(50);

C. Multi-dimensional array

-- The following definition is as follows: 1) Create an array, however, the two functions in different scopes -- the array created by the object can be used anywhere -- the type defined by the table cannot define the global type, type v_table is table of poco_test % rowtype index by binary_integer can be defined only in function and stored procedure definition variables;

3) bulk collect

Bulk collect is used to retrieve a data set at a time, which is more efficient than cursor data retrieval. However, it requires a large amount of memory. You can use the select into, fetch into, returning into statements to 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 learning about the basic content, the following sample code is inserted in batch in Oracle for this study.

create or replace type poco_test_object_type asobject( id varchar2(36),name varchar2(200),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(1,2,3); abc:=poco_test_object_arr_type(o,o,o); poco_test_arr_pro(abc);end;

Ii. Sample Code of the stored procedure whose JDBC call parameters are 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
 
   orderList = new ArrayList
  
   ();  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
   
     pstruct = new ArrayList
    
     ();  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 ....
    
   
  
 

The toDatum function is implemented by inheriting the oracle. SQL. ORAData interface, which simplifies code execution.

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
 
   orderList = new ArrayList
  
   ();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 ...
  
 

The preceding figure shows the Oracle batch insertion data. In terms of C ++, no method similar to JDBC is found to provide the call to the stored procedure of parameters of the array type. The next article will introduce another solution to complete batch Oracle data insertion.