Oracle存储过程⼊参传⼊List集合的⼩例⼦create or replace type STUDENT as object(
id        number(4),
name    varchar2(50),
age        number(3)
);
/
-- ⽅式1：⽤table来盛放对象
create or replace type STU_LIST as table of STUDENT;
/
-- ⽅式2：⽤数组来盛放对象，同时可指定数组的⼤⼩[⽤as varray(100)也是可以滴]
create or replace type STU_LIST as array(100) of STUDENT;
/
create or replace procedure test_in_list(
-- 参数区域
studentlist in STU_LIST,
message out varchar2
)
is
-- 变量区域
begin
-- 执⾏区域
for i in1 .. unt loop
message:=message||'{id:'||studentlist(i).id||', name:'||studentlist(i).name||', age'||studentlist(i).age||'},';
end loop;
end test_in_list;
/
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.util.ArrayList;
import oracle.sql.ARRAY;
import oracle.sql.ArrayDescriptor;
import oracle.sql.STRUCT;
import oracle.sql.StructDescriptor;
public class TestProInList {
// 测试main
public static void main(String[] args) {
new TestProInList().testProInList();
}
// 调⽤存储过程并返回执⾏结果
public void testProInList() {
// 定义需要的变量
Connection ct = null;
CallableStatement cs = null;
ResultSet rs = null;
try {
// 加载驱动
Class.forName("oracle.jdbc.driver.OracleDriver");
// 得到连接
ct = Connection("jdbc:oracle:thin:@127.0.0.1:1521:myora1", "sccot", "tiger");
// 创建CallableStatement接⼝
cs = ct.prepareCall("{call TEST_IN_LIST(?,?)}");
// 给in?赋值
ArrayList<Student> list = new ArrayList<Student>() {
{
this.add(new Student(1, "ZhangSan", 23));
this.add(new Student(2, "LiSi", 22));
this.add(new Student(3, "WangWu", 21));
}
};
ARRAY array = getArray(ct, "STUDENT", "STU_LIST", list);// 该函数调⽤的第⼆三个参数就是上⾯⾃定义的两个类型，在此必须⼤写            // 设置⼊参
cs.setArray(1, array);
// 注册出参
// 执⾏
// 获取返回结果
String message = cs.getString(2);
System.out.println(message);
// 打印结果：
// {id:1, name:ZhangSan, age23},{id:2, name:LiSi, age22},{id:3,
// name:WangWu, age21},
} catch (Exception e) {
e.printStackTrace();
} finally {
// 关闭资源
try {
if (cs != null) {
cs.close();
}
if (rs != null) {
rs.close();
}
if (ct != null) {
ct.close();
}
} catch (Exception e) {
e.printStackTrace();
}
cs = null;
rs = null;
ct = null;
}
}
private ARRAY getArray(Connection con, String OraObjType, String OraArrType, ArrayList<Student> stuList)
throws Exception {
ARRAY list = null;
if (stuList != null && stuList.size() > 0) {
// Oracle识别的集合对象，匹配java对象集合
jdbc连接oracle
STRUCT[] structs = new STRUCT[stuList.size()];
// Oracle识别的对象模板，匹配单个java对象
StructDescriptor structdesc = new StructDescriptor(OraObjType, con);
// 遍历stuList，将每个Student对象转换为Oracle可识别的模板对象
for (int i = 0; i < stuList.size(); i++) {
// java对象
Student student = (i);
// 数组⼤⼩应和你定义的数据库对象(STUDENT)的属性的个数
Object[] oneRow = new Object[3];
oneRow[0] = Id(); // 将Student对象的每个属性按顺序设置到oneRow数组中
oneRow[1] = Name();
oneRow[2] = Age();
structs[i] = new STRUCT(structdesc, con, oneRow);
}
// 匹配list
ArrayDescriptor desc = ateDescriptor(OraArrType, con);
list = new ARRAY(desc, con, structs);
}
return list;
}
}
class Student {
private int id;
private String name;
private int age;
// 构造函数
public Student(int id, String name, int age) {
super();
this.id = id;
this.name = name;
this.age = age;
}
// getters/setters(略)
}
sql部分：
-- 创建数组对象
create or replace type tables_array as varray(100) of varchar2(32);
/
-- 测试⽤存储过程
create or replace procedure test_in_list(
--参数区域
objlist in tables_array,
message out varchar2
) is
-
-变量区域
begin
--执⾏区域
for i in objlist.first .. objlist.last loop
message:=message||objlist(i)||',';
end loop;
commit;
end test_in_list;
/
java部分：
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import oracle.sql.ARRAY;
import oracle.sql.ArrayDescriptor;
public class TestProInList {
// 调⽤测试⽤存储过程
public static void main(String[] args) {
// 定义需要的变量
Connection ct = null;
CallableStatement cs = null;
ResultSet rs = null;
try {
// 加载驱动
Class.forName("oracle.jdbc.driver.OracleDriver");
// 得到连接
ct = Connection("jdbc:oracle:thin:@127.0.0.1:1521:myora1", "sccot", "tiger");
// 创建CallableStatement接⼝
cs = ct.prepareCall("{call TEST_IN_LIST(?,?)}");
// 给in?赋值
ArrayDescriptor descriptor = ateDescriptor("TABLES_ARRAY", ct);
List<String> list = new ArrayList<String>() {
{
this.add("abc");
this.add("edf");
this.add("ghi");
}
};
ARRAY array = new ARRAY(descriptor, ct, Array());
// 设置⼊参
cs.setArray(1, array);
/
/ 注册出参
// 执⾏
// 获取返回结果
String message = cs.getString(2);
System.out.println(message); //打印结果： abc,edf,ghi,        } catch (Exception e) {
e.printStackTrace();
} finally {
// 关闭资源
try {
if (cs != null) {
cs.close();
}
if (rs != null) {
rs.close();
}
if (ct != null) {
ct.close();
}
} catch (Exception e) {
e.printStackTrace();
}
cs = null;
rs = null;
ct = null;
}
}
}

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。