将数组传递给 oracle 过程 [英] pass array to oracle procedure

问题描述

我想将两个数组从 java 发送到 oracle 存储过程.第一个数组是字符串数组，第二个是字符数组我该怎么做??

I want to send two arrays form java to oracle stored procedures. The first Array is array of strings and the second is array of chars how can I make this??

推荐答案

这是一个如何做的例子.

Here's an example of how to do it.

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

The following script sets up a table, a type and a stored procedure in the database. The procedure takes a parameter of the array type and inserts each row of the array into the table:

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 代码随后演示了将数组传递到此存储过程中:

The Java code then demonstrates passing an array into this stored procedure:

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，你会发现所有的数据都已经插入到表中了.

If you run the SQL script and then the Java class, and then query the table strings, you should find that all of the data has been inserted into the table.

当您说字符数组"时，我猜您指的是 Java char 数组.如果我猜对了，那么我认为您最好将 chars 转换为 Strings，然后使用与上述相同的方法.

When you say 'an array of chars', I'm guessing that you mean an array of Java chars. If I've guessed right, then I think you'd be best off converting the chars to Strings and then using the same approach as above.

这篇关于将数组传递给 oracle 过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！