jdbc调用oracle function返回结果集

申明oracle函数，及其结果集结构

• 结果集必须为全局的、table of 不能用index by

CREATE OR REPLACE TYPE EMPARRAY is object (corporationId number)；

CREATE OR REPLACE TYPE EMPARRAY2 is table of EMPARRAY；

 create or replace function func_empl return sys_refcursor is Result sys_refcursor; list emparray2 := emparray2(); item emparray ; begin select 1 into item.corporationId from dual; list.extend; list(list.count) := item; item.corporationId := 2; list.extend; list(list.count) := item; item.corporationId := 3; list.extend; list(list.count) := item; OPEN Result FOR SELECT * FROM TABLE(cast(list as emparray2)); return(Result); end func_empl;

获取返回游标。

 

  public void callFuncQuery(String sql, RowCallbackHandler rch, String returnType) throws DataAccessObjectException { Connection con = null; CallableStatement cstmt = null; ResultSet rs = null; logger.info(sql); try { con = getDataSource().getConnection(); cstmt = con.prepareCall(sql); //适用于数组 //cstmt.registerOutParameter(1, OracleTypes.ARRAY, returnType); //cstmt.registerOutParameter(1, OracleTypes.STRUCT, returnType); //适用于游标 cstmt.registerOutParameter(1, OracleTypes.CURSOR); cstmt.executeUpdate(); /*Array out=cstmt.; System.out.println("Array is of type " + out.getBaseTypeName()); System.out.println("Array element is of type code " + out.getBaseType());*/ rs = (ResultSet)cstmt.getObject(1); while (rs.next()) { rch.processRow(rs); } } catch (SQLException e) { throw new DataAccessObjectException(e); } finally { try { if (rs != null) { rs.close(); rs = null; } if (cstmt != null) { cstmt.close(); cstmt = null; } if (con != null) { con.close(); con = null; } } catch (SQLException e) { throw new DataAccessObjectException(e); } } }

 

• oracle集合类应用

 --全局声明

create or replace type LIST is table of number(8)；

 

inList list

 --批量保存

select id bulk collect
into inList
from user
 

 --批量匹配

from ...

where id in (select * from table(inList));