这个答案在另一个论坛上可能会对您有所帮助。
查看消息底部的示例,了解如何从Java方法返回的集合中进行选择(该方法也可以是Java存储过程)。
以下是使用Java存储过程执行此操作的示例:
1)创建DB对象以定义返回行的类型:
create type try_obj as object (
field_a number,
field_b varchar2(10)
)
/
create type try_obj_tab as table of try_obj
/
2) 在数据库上创建一个Java类,该类具有静态方法(GetSampleResult),该方法返回一个集合
create or replace and compile java source named QueryReturn as
import java.sql.*;
import java.util.*;
import oracle.jdbc.*;
import oracle.jdbc.pool.OracleDataSource;
import oracle.sql.*;
public class QueryReturn implements ORADataFactory,ORAData{
private NUMBER field1;
private CHAR field2;
public QueryReturn(OracleConnection conn,int n,String c) throws SQLException {
field1 = new NUMBER(n);
field2 = new CHAR(c,oracle.sql.CharacterSet.make(conn.getStructAttrCsId()));
}
public QueryReturn(NUMBER n, CHAR c) {
field1 = n;
field2 = c;
}
public QueryReturn(Object[] attributes) {
this(
(NUMBER) attributes[0],
(CHAR) attributes[1]
);
}
public QueryReturn(Datum d) throws SQLException {
this(((STRUCT) d).getOracleAttributes());
}
public ORAData create(Datum d, int sqlType) throws SQLException {
if (d == null)
return null;
else {
return new QueryReturn(d);
}
}
public STRUCT toSTRUCT(Connection conn) throws SQLException {
StructDescriptor sd =
StructDescriptor.createDescriptor("TRY_OBJ", conn);
Object [] attributes = { field1,field2 };
return new STRUCT(sd, conn, attributes);
}
public Datum toDatum(Connection conn) throws SQLException {
return toSTRUCT(conn);
}
public static ARRAY GetSampleResult() throws SQLException, ClassNotFoundException {
OracleConnection conn = null;
conn = (OracleConnection) (new oracle.jdbc.OracleDriver()).defaultConnection();
QueryReturn javaArray[] = {
new QueryReturn(conn,1,"abc"),
new QueryReturn(conn,2,"dce")
};
Map map = conn.getTypeMap();
map.put("TRY_OBJ", Class.forName("QueryReturn"));
ArrayDescriptor jTryObjArrayDesc = ArrayDescriptor.createDescriptor (
"TRY_OBJ_TAB",
conn
);
ARRAY oracleCollection = new ARRAY(jTryObjArrayDesc,conn,javaArray);
return oracleCollection;
}
}
3) 创建包装器以在函数中使用Java存储过程
create or replace function GetSampleResult
return try_obj_tab
AS LANGUAGE JAVA
NAME 'QueryReturn.GetSampleResult() return oracle.sql.ARRAY';
4) 展示结果
SQL> select *
2 from table(GetSampleResult())
3 /
FIELD_A FIELD_B
1 abc
2 dce
SQL>
select * from function()
。 - user330315