将数组传递给 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
数组.如果我猜对了,那么我认为您最好将 char
s 转换为 String
s,然后使用与上述相同的方法.
When you say 'an array of chars', I'm guessing that you mean an array of Java char
s. If I've guessed right, then I think you'd be best off converting the char
s to String
s and then using the same approach as above.
这篇关于将数组传递给 oracle 过程的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!