java数组传入oracle
上一篇 / 下一篇 2011-12-17 10:55:27 / 个人分类:JSP
数据库的环境
1* create
table t (b varchar2(90))
SQL> /
表已创建。
SQL> create or replace procedure insert_t(a varray_list)
2 is
3 begin
4 for i in 1 .. a.count loop
5 insert into t values (a(i));
6 end loop;
7 end;
8 /
SQL> desc varray_list
varray_list TABLE OF VARCHAR2(90)
java代码:
import java.sql.*;
import java.sql.PreparedStatement;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import
oracle.sql.ARRAY;
import oracle.sql.ArrayDescriptor;
public class java_oracle_array {
public static void main(String args[]) {
Statement stmt;
ResultSet rs;
PreparedStatement pstmt;
Connection connMyDB = null;
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
connMyDB = DriverManager.getConnection("jdbc:oracle:thin:@10.52.23.30:1521:orcl", "plsql", "plsql");
} catch (SQLException ex) { System.out.println(ex.getMessage());
} catch (ClassNotFoundException ex) {
}
try {
CallableStatement proc = connMyDB.prepareCall("{ call insert_t(?) }");
oracle.sql.ArrayDescriptor descriptor = oracle.sql.ArrayDescriptor.createDescriptor(
"VARRAY_LIST", connMyDB); //定义描述符
List list = new ArrayList();
String[] newURLs = { "abc", "1234" };
//把list中的元素转换成自定义的类型
ARRAY array = new ARRAY(descriptor, connMyDB, newURLs);
/*重要!这里输出的全是“???”试验NLS
备注:如果在入库的过程中发现字符串的值没有入进去,
请检查有没有加载该类库orai18n.jar(
11g之前:nls_charset12.jar)*/
proc.setArray(1, array); //给过程传参数
proc.execute();
String[] tem = (String[]) array.getArray(); //输出一下
for (String str : tem) {
System.out.println(str);
}
} catch (Exception ex) {
ex.printStackTrace();
} finally {
try {
if(connMyDB != null && !connMyDB.isClosed())
connMyDB.close();
} catch (SQLException ex) {
ex.printStackTrace();
}
}
}
}
运行以后在t表插入两行
SQL> select * from t;
B
-----------------------------------------------
abc
1234