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();}}}}}?
?
?
?
?
?
?
?
?
?
?
?