Use arrays to access Oracle to improve performance

 

Oracle stored procedures support arrays, which greatly improves the performance of batch data operations. If you need to insert 1000 records, the test_info table structure is:

--------------------------------------------------------------------

Id_seq number, login_id varchar (64) and info varchar (100)

--------------------------------------------------------------------

Three fields. id_seq is a sequence;

 

Because arrays are used, you need to create a global array type in Oracle, as shown below:

Create or replace type t_stringarray as table of varchar2 (128)

 

Define a stored procedure as follows:

Function test_addmultipleidinfos (v_ids t_stringarray, v_infos t_stringarray) return number

Is

I number: = 0;

Begin

While I <v_ids.count Loop

I: = I + 1;

Insert into test_info (id_seq, login_id, Info) values (test_info_id_seq.nextval, v_ids (I), v_infos (I ));

End loop;

Commit;

Return 0;

Exception
When others then
Rollback;
Return-1;
End;

 

 

Then the development program is supported by both Java and C, and examples of writing Java and C are given respectively.

Java Syntax:

Preparedstatement pstmt = NULL;

String strsql = "{call test. test_addmultipleidinfos (?,?)} ";
Pstmt = con. preparecall (strsql );
String [] IDs = new string [2];

String [] Infos = new string [2];

IDS [0] = "test1 ";

IDS [1] = "Test2 ";

Infos [0] = "info1 ";

Infos [1] = "info2"

Oracle. SQL. arraydescriptor DESC = oracle. SQL. arraydescriptor. createdescriptor ("t_stringarray", con );
Oracle. SQL. array idarray = new Oracle. SQL. Array (DESC, Con, IDS );
Pstmt. setarray (1, idarray );

Oracle. SQL. array infoarray = new Oracle. SQL. Array (DESC, Con, Infos );

Pstmt. setarray (2, infoarray );
Resultset rset = pstmt.Executequery();

Int ret = rset. getint (1 );

 

 C ++ OCI is written as follows:

Vector <string> IDs, Infos;

IDs. push_back ("test1 ");

IDs. push_back ("Test2 ");

Infos. push_back ("info1 ");

Infos. push_back ("info2 ");

M_stmt = m_con-> createstatement ("begin: v_err: = test. test_addmultipleidinfos (: v_ids,: v_infos); end ;");
M_stmt-> registeroutparam (1, occinumber );
Setvector (m_stmt, 2, IDs, "t_stringarray ");
Setvector (m_stmt, 3, Infos, "t_stringarray ");

M_stmt-> executeupdate ();
Ret = m_stmt-> getnumber (1 );

M_con-> terminatestatement (m_stmt );