oracle没有直接的返回List的方法，这里需要有包定义一个Cursor（游标）的返回类型。下面是我在计算收益率的一段代码，只做参考。
oracle包的写法如下：
CREATE OR REPLACE PACKAGE CAL_RTN_PACK IS TYPE mycursor IS REF CURSOR;
PROCEDURE getRtnListByPid_proc ( p_pid IN tbsi_ante_port.p_id%TYPE, x_rs OUT mycursor ); END CAL_RTN_PACK;
create or replace package body cal_rtn_pack is
PROCEDURE getRtnListByPid_proc ( p_pid IN tbsi_ante_port.p_id%TYPE, x_rs OUT mycursor ) IS v_start_date DATE; --每年第一个工作日 v_bm_id tbsi_ante_port.bm_id%TYPE; v_bm_date DATE; --基准计算时间 v_p_date DATE; --基金开始时间 BEGIN SELECT min(bm_id) INTO v_bm_id FROM tprt_bench_drill b WHERE b.p_id = p_pid AND b.isdefault = DECODE(b.isdefault, 1, 1, b.isdefault); SELECT min(BEG_DATE) INTO v_bm_date FROM tbsi_ante_port WHERE p_id = p_pid AND bm_id = v_bm_id; SELECT min(BEG_DATE) INTO v_bm_date FROM tbsi_ante_port WHERE p_id = p_pid; IF v_bm_date > v_start_date AND v_p_date < v_start_date THEN --当基准开始时间不在年初且基金生效时间小于年初 不计算 NULL; ELSE OPEN x_rs FOR SELECT bm_id, beg_date, inv_rtn, inv_rtn_bench FROM tbsi_ante_port WHERE p_id = p_pid AND bm_id = v_bm_id; END IF; END getRtnListByPid_proc; end cal_rtn_pack;
2. 要在java端接收这个返回的游标 publicList<PLMTbsiAntePort> getRtnListByPid(String pid) {
Session session =null;
Connection conn =null;
CallableStatement st =null;
String sql ="{call CAL_RTN_PACK.getRtnListByPid_proc(?, ?)}";
List<PLMTbsiAntePort> list;
try{
session =this.getMetaDBContext().getSession();
conn = session.connection();
st = conn.prepareCall(sql);
st.setString(1, pid);
st.registerOutParameter(2, OracleTypes.CURSOR);
st.execute();
ResultSet rs =null;
rs = (ResultSet)st.getObject(2);
list =newArrayList<PLMTbsiAntePort>();
while(rs.next())
{
PLMTbsiAntePort antePort =newPLMTbsiAntePort();
antePort.setBmId(rs.getString(1));
antePort.setBegDate(rs.getDate(2));
antePort.setpId(pid);
antePort.setInvRtn(rs.getDouble(3));
antePort.setInvRtnBench(rs.getDouble(4));
list.add(antePort);
}
}catch(Exception e) {
throw newRuntimeException(e);
}
returnlist;
}