create or replace type rec_def as object (
pvtrsid int
);
//不能声明成record,只有局部的类型(sp 中)才可以声明record
create or replace type my_table_type is table of rec_def;
declare
rec rec_def;
arr my_table_type;
begin
rec := rec_def(1);
arr := my_table_type();
arr.extend();
arr(1) := rec;
end;
=======jdbc
public List<AssetPriceRecon> varryQuery() throws Exception {
Object obj = jdbcTemplate.execute(new CallableStatementCreator() {
@Override
public CallableStatement createCallableStatement(Connection con)
throws SQLException {
CallableStatement cs = con.prepareCall("{call pkg_trustee.varrayQuery(?)}");
cs.registerOutParameter(1,
oracle.jdbc.OracleTypes.ARRAY, "MY_TABLE_TYPE");
return cs;
}
}, new CallableStatementCallback() {
@Override
public Object doInCallableStatement(CallableStatement cs)
throws SQLException, DataAccessException {
// TODO Auto-generated method stub
cs.execute();
Array array = cs.getArray(1);
return array;
}
});
if (obj == null)
return null;
ARRAY rs = (ARRAY)obj;
Datum [] data = rs.getOracleArray();
ResultSet r = rs.getResultSet();
while (r.next()) {
System.out.println(r.getInt(1));
}
for (Datum da : data) {
STRUCT st = (STRUCT)da;
Map m = st.getMap();
Object [] o = st.getAttributes();
System.out.println(st.getMap());
}
return null;
}====================