将数组传递到预言机过程

下面是如何做到这一点的示例。

以下脚本在数据库中设置表、类型和存储过程。该过程采用数组类型的参数，并将数组的每一行插入到表中：

CREATE TABLE strings (s VARCHAR(4000));

CREATE TYPE t_varchar2_array AS TABLE OF VARCHAR2(4000);
/

CREATE OR REPLACE PROCEDURE p_array_test(
    p_strings      t_varchar2_array
)
AS
BEGIN
  FOR i IN 1..p_strings.COUNT
  LOOP
    INSERT INTO strings (s) VALUES (p_strings(i));
  END LOOP;
END;
/

然后，Java 代码演示如何将数组传递到此存储过程中：

import java.sql.*;
import oracle.jdbc.*;
import oracle.sql.*;

public class ArrayTest {
    public static void main(String[] args) throws Exception {
        DriverManager.registerDriver(new OracleDriver());
        Connection conn = DriverManager.getConnection(
            "jdbc:oracle:thin:@localhost:1521:xe", "user", "pass");

        CallableStatement stmt = conn.prepareCall("BEGIN p_array_test(?); END;");

        // The first parameter here should be the name of the array type.
        // It's been capitalised here since I created it without using
        // double quotes.
        ArrayDescriptor arrDesc =
            ArrayDescriptor.createDescriptor("T_VARCHAR2_ARRAY", conn);

        String[] data = { "one", "two", "three" };
        Array array = new ARRAY(arrDesc, conn, data);
        stmt.setArray(1, array);
        stmt.execute();

        conn.commit();
        conn.close();
    }
}

如果先运行 SQL 脚本，然后运行 Java 类，然后查询表 ，则应发现所有数据都已插入到表中。strings

当你说“一个字符数组”时，我猜你的意思是一个Java数组。如果我猜对了，那么我认为你最好将s转换为s，然后使用与上面相同的方法。charcharString

看这里： http://download.oracle.com/docs/cd/B19306_01/java.102/b14355/oraarr.htm#i1058512

这是我的简短例子：

1） 在数据库上

SQL> create or replace type string_array as table of varchar2(100);
  2  /

Type created.

SQL> create or replace function to_string(p_array in string_array) return varchar2
  2  as
  3     l_string varchar2(32767);
  4     i binary_integer;
  5  begin
  6     i := p_array.first();
  7     while i is not null loop
  8        l_string := l_string || p_array(i) || ';';
  9        i := p_array.next(i);
 10     end loop;
 11     l_string := rtrim(l_string, ';');
 12     return l_string;
 13  end;
 14  /

Function created.

2） 在 java 中

public class ArrayTest {
    public static void main(String[] args) throws SQLException {
        DriverManager.registerDriver(new OracleDriver());
        OracleConnection connection = (OracleConnection) DriverManager.getConnection(...);

        String[] elements = {"abc", "def", "geh"};
        ArrayDescriptor descriptor = ArrayDescriptor.createDescriptor("STRING_ARRAY", connection);
        ARRAY array = new ARRAY(descriptor, connection, elements);

        OracleCallableStatement stmt = (OracleCallableStatement) connection.prepareCall("{? = call to_string(?)}");
        stmt.registerOutParameter(1, Types.VARCHAR);
        stmt.setARRAY(2, array);
        stmt.execute();

        String result = stmt.getString(1);
        System.out.println("to_string returned: " + result);
    }
}

似乎有效：输出说

to_string returned: abc;def;geh