一、新建数据

  1. -- 1. 创建一个对象类型
  2. create or replace type STUDENT as object(
  3.     id          number(4),
  4.     name        varchar2(50),
  5.     age         number(3)
  6.  );
  7. -- 2. 创建一个数组类型 (任意选择下面的一句执行)
  8. -- 方式1：用table来盛放对象
  9. create or replace type STU_LIST as table of STUDENT;
  11. -- 方式2：用数组来盛放对象，同时可指定数组的大小[用as varray(100)也是可以滴]
  12. create or replace type STU_LIST as array(100) of STUDENT;
  14. -- 3. 创建一个过程引用集合类型的参数
  15. create or replace procedure test_in_list(
  16. -- 参数区域
  17.     studentlist in STU_LIST,
  18.     message out varchar2
  19. )
  20. is
  21. -- 变量区域
  22. begin
  23. -- 执行区域
  24.     for i in 1 .. studentlist.count loop
  25.         message:=message||'{id:'||studentlist(i).id||', name:'||studentlist(i).name||', age'||studentlist(i).age||'},';
  26.     end loop;
  27. end test_in_list;

二、java代码调用

  1. import java.sql.CallableStatement;
  2. import java.sql.Connection;
  3. import java.sql.DriverManager;
  4. import java.sql.ResultSet;
  5. import java.util.ArrayList;
  7. import oracle.sql.ARRAY;
  8. import oracle.sql.ArrayDescriptor;
  9. import oracle.sql.STRUCT;
  10. import oracle.sql.StructDescriptor;
  12. public class TestProInList {
  14.     // 测试main
  15.     public static void main(String[] args) {
  16.         new TestProInList().testProInList();
  17.     }
  19.     // 调用存储过程并返回执行结果
  20.     public void testProInList() {
  21.         // 定义需要的变量
  22.         Connection ct = null;
  23.         CallableStatement cs = null;
  24.         ResultSet rs = null;
  26.         try {
  27.             // 加载驱动
  28.             Class.forName("oracle.jdbc.driver.OracleDriver");
  30.             // 得到连接
  31.             ct = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:myora1", "sccot", "tiger");
  33.             // 创建CallableStatement接口
  34.             cs = ct.prepareCall("{call TEST_IN_LIST(?,?)}");
  36.             // 给in?赋值
  37.             ArrayList<Student> list = new ArrayList<Student>() {
  38.                 {
  39.                     this.add(new Student(1, "ZhangSan", 23));
  40.                     this.add(new Student(2, "LiSi", 22));
  41.                     this.add(new Student(3, "WangWu", 21));
  42.                 }
  43.             };
  44.             ARRAY array = getArray(ct, "STUDENT", "STU_LIST", list);// 该函数调用的第二三个参数就是上面自定义的两个类型，在此必须大写
  45.             // 设置入参
  46.             cs.setArray(1, array);
  47.             // 注册出参
  48.             cs.registerOutParameter(2, oracle.jdbc.OracleTypes.VARCHAR);
  50.             // 执行
  51.             cs.execute();
  53.             // 获取返回结果
  54.             String message = cs.getString(2);
  55.             System.out.println(message);
  56.             // 打印结果：
  57.             // {id:1, name:ZhangSan, age23},{id:2, name:LiSi, age22},{id:3,
  58.             // name:WangWu, age21},
  60.         } catch (Exception e) {
  61.             e.printStackTrace();
  62.         } finally {
  63.             // 关闭资源
  64.             try {
  65.                 if (cs != null) {
  66.                     cs.close();
  67.                 }
  68.                 if (rs != null) {
  69.                     rs.close();
  70.                 }
  71.                 if (ct != null) {
  72.                     ct.close();
  73.                 }
  74.             } catch (Exception e) {
  75.                 e.printStackTrace();
  76.             }
  77.             cs = null;
  78.             rs = null;
  79.             ct = null;
  80.         }
  81.     }
  83.     private ARRAY getArray(Connection con, String OraObjType, String OraArrType, ArrayList<Student> stuList)
  84.             throws Exception {
  85.         ARRAY list = null;
  86.         if (stuList != null && stuList.size() > 0) {
  87.             // Oracle识别的集合对象,匹配java对象集合
  88.             STRUCT[] structs = new STRUCT[stuList.size()];
  90.             // Oracle识别的对象模板,匹配单个java对象
  91.             StructDescriptor structdesc = new StructDescriptor(OraObjType, con);
  93.             // 遍历stuList,将每个Student对象转换为Oracle可识别的模板对象
  94.             for (int i = 0; i < stuList.size(); i++) {
  95.                 // java对象
  96.                 Student student = stuList.get(i);
  98.                 // 数组大小应和你定义的数据库对象(STUDENT)的属性的个数
  99.                 Object[] oneRow = new Object[3];
  100.                 oneRow[0] = student.getId(); // 将Student对象的每个属性按顺序设置到oneRow数组中
  101.                 oneRow[1] = student.getName();
  102.                 oneRow[2] = student.getAge();
  103.                 structs[i] = new STRUCT(structdesc, con, oneRow);
  104.             }
  106.             // 匹配list
  107.             ArrayDescriptor desc = ArrayDescriptor.createDescriptor(OraArrType, con);
  108.             list = new ARRAY(desc, con, structs);
  109.         }
  110.         return list;
  111.     }
  112. }
  114. class Student {
  115.     private int id;
  116.     private String name;
  117.     private int age;
  119.     // 构造函数
  120.     public Student(int id, String name, int age) {
  121.         super();
  122.         this.id = id;
  123.         this.name = name;
  124.         this.age = age;
  125.     }
  127.     // getters/setters(略)
  129. }

引用

https://blog.csdn.net/weixin_34381687/article/details/86228963