Java调用Oracle集合类型

1、构造统计对象

create or replace type TestObj as object
(
  vname varchar2(20), --名称
  item1 number, --统计项目1
  item2 number, --统计项目2
  item3 number, --统计项目3
  item4 number
)

 

 

2、构造包含对象类型的嵌套表

create or replace type TestNestTable as table of TestObj

 

 

3、定义对索引表"造型"后的输出的游标类型

create or replace package out_param is
  type out_cur is ref cursor;
end out_param;

 

 

4、创建嵌套表作为输出参数的存储过程

create or replace procedure testPro2(o_cur out out_param.out_cur) is
  ---- 包含对象的嵌套表变量的声明
  v_objTable TestNestTable := TestNestTable();
begin
  --嵌套表变量的使用
  v_objTable.extend;
  v_objTable(1) := TestObj('张三', 12, 123, 123, 34);
  v_objTable.extend;
  v_objTable(2) := TestObj('李四', 22, 223, 223, 234);
  --对嵌套表进行"造型"返回游标
  open o_cur for
    select * from Table(cast(v_objTable as TestNestTable));
end testPro2;

 

5、Java程序的编写

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import oracle.jdbc.OracleTypes;

public class StuInfo {
	
	public static void main(String [] args){
		Connection conn = null;
		CallableStatement  stmt=null;
		ResultSet  rest=null;
		try {
			//加载驱动
			Class.forName("oracle.jdbc.driver.OracleDriver");
			//获取连接
			conn = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:orcl", "scott", "123456"); 	
			
			stmt =(CallableStatement )conn.prepareCall("call testPro2(?)");
			//注册游标对象类型
			stmt.registerOutParameter(1,OracleTypes.CURSOR);
			stmt.execute();
			//返回结果集
			rest=(ResultSet)stmt.getObject(1);
			while(rest.next()){				
				System.out.println(rest.getString(1)+"|#|"+rest.getString(2)+"|#|"+rest.getString(3)+"|#|"+rest.getString(4)+"|#|"+rest.getString(5));				
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			if(rest!=null){	try {rest.close();rest=null;} catch (SQLException e) {e.printStackTrace();}}
			if(stmt!=null){	try {stmt.close();stmt=null;} catch (SQLException e) {e.printStackTrace();}}
			if(conn!=null){	try {conn.close();conn=null;} catch (SQLException e) {e.printStackTrace();}}
		}
	}
}