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 in 1 .. studentlist.count loop
        message:=message||'{id:'||studentlist(i).id||', name:'||studentlist(i).name||', age'||studentlist(i).age||'},';
    end loop;
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 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 = DriverManager.getConnection("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);
            // 注册出参
            cs.registerOutParameter(2, oracle.jdbc.OracleTypes.VARCHAR);

            // 执行
            cs.execute();

            // 获取返回结果
            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对象集合
            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 = stuList.get(i);

                // 数组大小应和你定义的数据库对象(STUDENT)的属性的个数
                Object[] oneRow = new Object[3];
                oneRow[0] = student.getId(); // 将Student对象的每个属性按顺序设置到oneRow数组中
                oneRow[1] = student.getName();
                oneRow[2] = student.getAge();
                structs[i] = new STRUCT(structdesc, con, oneRow);
            }

            // 匹配list
            ArrayDescriptor desc = ArrayDescriptor.createDescriptor(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 = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:myora1", "sccot", "tiger");

            // 创建CallableStatement接口
            cs = ct.prepareCall("{call TEST_IN_LIST(?,?)}");

            // 给in?赋值
            ArrayDescriptor descriptor = ArrayDescriptor.createDescriptor("TABLES_ARRAY", ct);
            List<String> list = new ArrayList<String>() {
                {
                    this.add("abc");
                    this.add("edf");
                    this.add("ghi");
                }
            };
            ARRAY array = new ARRAY(descriptor, ct, list.toArray());
            // 设置入参
            cs.setArray(1, array);
            // 注册出参
            cs.registerOutParameter(2, oracle.jdbc.OracleTypes.VARCHAR);

            // 执行
            cs.execute();

            // 获取返回结果
            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;
        }
    }
}

 

转载于:https://www.cnblogs.com/zj0208/p/6093721.html