我可以直接通过SQL select * from语句从java存储过程(oracle)获取结果吗？
在数据库上,我将有一个java存储过程/函数,当它被调用时返回一个多列,多行结果集.
我想直接通过select * from [table]语句访问这些结果.
所以java存储过程应该像表一样.
在MySQL中,以下应该是可能的(但不是java存储过程)：SELECT col1 FROM(EXEC proc1)
这可能在oracle中proc1是一个java存储过程吗？
解决方法:
This answer在不同的论坛可以帮到你.
查看消息底部的示例,了解如何从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)使用返回Collection的静态方法(GetSampleResult)在DB上创建Java类
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 {
// initialize the connection
OracleConnection conn = null;
conn = (OracleConnection) (new oracle.jdbc.OracleDriver()).defaultConnection();
// create the return java array
// There will be two Rows
// 1 abc
// 2 dce
QueryReturn javaArray[] = {
new QueryReturn(conn,1,"abc"),
new QueryReturn(conn,2,"dce")
};
// Map the java class to the Oracle type
Map map = conn.getTypeMap();
map.put("TRY_OBJ", Class.forName("QueryReturn"));
ArrayDescriptor jTryObjArrayDesc = ArrayDescriptor.createDescriptor (
"TRY_OBJ_TAB",
conn
);
// create an Oracle collection on client side to use as parameter
ARRAY oracleCollection = new ARRAY(jTryObjArrayDesc,conn,javaArray);
return oracleCollection;
}
}
3)创建Wrap以在函数中使用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>
标签：java,sql,stored-procedures,oracle
来源： https://codeday.me/bug/0621/1255122.html
