Oracle的存储过程支持数组, 这样对于批量数据的操作,可以大大提高性能. 如需要插入1000条记录, 表test_info结构为:
--------------------------------------------------------------------
id_seq number, login_id varchar(64), info varchar(100)
--------------------------------------------------------------------
三个字段, id_seq是个sequence;
由于使用数组,因此需要在Oracle中创建一个全局的数组type,如下:
CREATE OR REPLACE TYPE t_StringArray AS TABLE OF VARCHAR2(128)
然后定义一个存储过程,如下:
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;
然后开发程序,Java 和 C 都支持,分别就Java和C的写法举例
Java 写法如下:
PreparedStatement pstmt = null;
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 写法如下:
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);