Java中传入数组调用oracle存储过程

数据库部分：

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

为了保证写出来的都是可以运行的，所以每次创建表、类型等等，都先drop一下。第一次运行的朋友，可能会发现drop语句报×××不存在，可以忽略。引号

drop table T_VarcharArray;
create table T_VarcharArray(
??? id number(10),
??? name varchar2(100)
??? );

drop type T_VARCHAR;

--创建一个与T_VarcharArray的name同类型的不定长数组的引定义
create or replace type T_VARCHAR as table of varchar2(100);?
/

drop type T_NUMBER;

--创建一个与T_VarcharArray的id同类型的不定长数组的定义
create or replace type T_NUMBER as table of number(10);
/

create or replace procedure P_VARCHAR2_LST(
????i_t_varchar IN t_varchar, --输入参数为定义的不定长varchar2数组
??? o_n_ret OUT number --输出参数，正常结束，则输出0，否则抛出异常
??? )
is
begin
??? FOR i in 1..i_t_varchar.COUNT loop --注意下标从1开始。
??????? insert into T_VarcharArray values (i,i_t_varchar(i));
??? end loop;
??? o_n_ret :=0;
??? exception when others then
??? raise;
end;
/

create or replace function F_NUMBER_LST(
??? i_t_number IN t_number --输入参数为定义的不定长数值数组
??? )
return number --函数输出结果
is
begin
??? FOR i in 1..i_t_number.COUNT loop --注意下标从1开始。
??????? insert into T_VarcharArray values (i_t_number(i),to_char(i));
??? end loop;
??? return 0;
??? exception when others then
??? raise;
end;

?

?

Java部分：

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

?

package test.oracle.oj;???
import java.sql.*;???
import oracle.jdbc.driver.OracleTypes;???
import oracle.sql.ARRAY;???
import oracle.sql.ArrayDescriptor;???
import test.oracle.conn.OConnection;???
public class Varchar2NumberArray {???
??? // 注意：T_VARCHAR2必须全部大写???
??? private static final String T_VARCHAR = "T_VARCHAR";???
??? private static final String T_NUMBER = "T_NUMBER";???
??? // 注意：call必须小写，过程名字必须全部大写???
??? private static final String P_VARCHAR2_LST = "{call P_VARCHAR2_LST(?,?)}";???
??? // 注意：call必须小写，过程名字必须全部大写，返回值在java中是第一个???
??? private static final String F_NUMBER_LST = "{? = call F_NUMBER_LST(?)}";???
??? public static int varchar2LstTest(String[] lst) {???
??????? int retVal = -1;???
??????? Connection con = null;???
??????? CallableStatement cstmt = null;???
??????? try {???
??????????? con = OConnection.getConn();???
??????????? // 创建一个数组描述符???
????????????ArrayDescriptor varchar2Desc = ArrayDescriptor.createDescriptor(???
??????????????????? T_VARCHAR, con);???
??????????? // 将字符串数组转换为oralce能识别的数组???
??????????? ARRAY vArray = new ARRAY(varchar2Desc, con, lst);???
??????????? cstmt = con.prepareCall(P_VARCHAR2_LST);???
????????????cstmt.setArray(1, vArray);???
??????????? cstmt.registerOutParameter(2, OracleTypes.INTEGER);???
??????????? cstmt.execute();???
??????????? retVal = cstmt.getInt(2);???
??????? } catch (Exception ex) {???
??????????? ex.printStackTrace();???
??????? } finally {???
??????????? // 最好都在finally里面关闭用到的cs、ps、rs以及con，???
??????????? // 以确保出异常时,该释放的都被释放???
??????????? try {???
??????????????? if (cstmt != null) {???
??????????????????? cstmt.close();???
??????????????? }???
??????????????? if (con != null) {???
??????????????????? con.close();???
??????????????? }???
??????????? } catch (SQLException sqle) {???
??????????????? sqle.printStackTrace();???
??????????? }???
??????? }???
??????? return retVal;???
??? }???
??? public static int numberLstTest(int[] lst) {???
??????? int retVal = -1;???
??????? Connection con = null;???
??????? CallableStatement cstmt = null;???
??????? try {???
??????????? con = OConnection.getConn();???
??????????? // 创建一个数组描述符???
??????????? ArrayDescriptor varchar2Desc = ArrayDescriptor.createDescriptor(???
??????????????????? T_NUMBER, con);???
??????????? // 将字符串数组转换为oralce能识别的数组???
??????????? ARRAY vArray = new ARRAY(varchar2Desc, con, lst);???
??????????? cstmt = con.prepareCall(F_NUMBER_LST);???
??????????? // 返回值在java中是第一个，所以先注册输出参数???
??????????? cstmt.registerOutParameter(1, OracleTypes.INTEGER);???
??????????? cstmt.setArray(2, vArray);???
??????????? cstmt.execute();???
??????????? retVal = cstmt.getInt(1);???
??????? } catch (Exception ex) {???
??????????? ex.printStackTrace();???
??????? } finally {???
??????????? // 最好都在finally里面关闭用到的cs、ps、rs以及con，???
??????????? // 以确保出异常时,该释放的都被释放???
??????????? try {???
??????????????? if (cstmt != null) {???
??????????????????? cstmt.close();???
??????????????? }???
??????????????? if (con != null) {???
??????????????????? con.close();???
??????????????? }???
??????????? } catch (SQLException sqle) {???
??????????????? sqle.printStackTrace();???
??????????? }???
??????? }???
??????? return retVal;???
??? }???
??? public static void main(String[] args) throws Exception {???
??????? String[] lst = { "test1", "test2", "test2" };???
??????? int ret = Varchar2NumberArray.varchar2LstTest(lst);???
??????? System.out.println("测试字符串数组作为参数传入存储过程，结果：" + ret);???
??????? int[] lst1 = { 1, 2, 3 };???
??????? ret = Varchar2NumberArray.numberLstTest(lst1);???
??????? System.out.println("测试整型数组作为参数传入存储过程，结果：" + ret);???
??? }???
}?

?

如果无法将数组中数据插入到数据库中，则将oracle的nls_charset12.jar加入到工程，重新编译即可！

1 楼 hobyjoe 2012-05-04 我导入了nls_charset12.jar导入数据库中的字符串依然为空，不知为何
我的异常网推荐解决方案：oracle存储过程,http://www.myexception.cn/oracle-develop/177537.html