对RCMS项目做了了解和学习。
一。 Java中调用Oracle的批量插入数据的存储过程:
1. 存储过程语句:
create or replace type v_table is table of nvarchar2(10);
create or replace type i_table is table of number;
CREATE OR REPLACE PROCEDURE InsSell
(v_id in v_table,
v_ticketid in v_table,
v_name in v_table,
v_age in i_table)
as
i integer;
begin
for i in 1..v_id.count loop
insert into sellInfo(seid,ticketid,sname,age) values(v_id(i),v_ticketid(i),v_name(i),v_age(i));
end loop;
end;
说明: 参数v_id in table中的'in'代表是输入的参数,返回的参数为'out'
定义nvarchar, 而不是varchar. nvarchar可用于多国语言中。
2. java 中调用存储过程:
Connection conn1 = null;
conn1 = DBConnection.getConnection();
oracle.sql.ArrayDescriptor a_id = null;
oracle.sql.ArrayDescriptor a_ticketid = null;
oracle.sql.ArrayDescriptor a_name = null;
oracle.sql.ArrayDescriptor a_age = null;
Object[] s_id = new Object[nSize];
Object[] s_ticketid = new Object[nSize];
Object[] s_name = new Object[nSize];
Object[] s_age = new Object[nSize];
for (int i = 0; i < 100; i++) {
js = (JySell) jySellList.get(i);
s_id[i] = new String("name");
s_ticketid[i] = new String("bbb");
s_name[i] = new String("ccccc");
s_age[i] = new Integer(i);
}
try {
oracle.sql.ArrayDescriptor a_nvarchar =
ArrayDescriptor.createDescriptor("V_TABLE", conn1);
oracle.sql.ArrayDescriptor a_number =
ArrayDescriptor.createDescriptor("I_TABLE", conn1);
ARRAY id_test = new ARRAY(a_nvarchar, conn1, s_id);
ARRAY ticketid_test = new ARRAY(a_nvarchar, conn1, s_ticketid);
ARRAY name_test = new ARRAY(a_nvarchar, conn1, s_name);
ARRAY age_test = new ARRAY(a_nvarchar, conn1, s_age);
CallableStatement cstmt = conn1.prepareCall("{ call InsSell(?,?,?,?) }");
cstmt.setArray(1, id_test);
cstmt.setArray(2, ticketid_test);
cstmt.setArray(3, name_test);
cstmt.setArray(4, age_test);
cstmt.execute();
conn1.commit();
} catch (Exception e) {
e.printStackTrace();
System.out.println("Insert into DB error: " + e.getMessage());
}
注意:ArrayDescriptor.createDescriptor("V_TABLE", conn1);此处的"V_TABLE"必须大写。
如果是直接建立的connection没有问题,如果是用连接池中取到的connnection可能会有问题,它的独到的可能只是一个连接的代理,需要做一个转换。