使用数组方式访问Oracle,提高性能

来源：互联网发布：淘宝靠谱护肤品店编辑：程序博客网时间：2024/06/06 21:53

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

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;

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 写法如下:

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);