oracle 通过SQL“select * from”从java存储过程返回结果集
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5740717/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
returning a result set from a java stored procedure through SQL "select * from "
提问by Ruben S.
Can I get the result from a java stored procedure (oracle) directly through an SQL select * fromstatement ?
我可以通过 SQLselect * from语句直接从 Java 存储过程 (oracle) 获取结果吗？
On the database I would have a java stored procedure / function, which when it called returns a multi-column, multi-row result set.
I would like to access these results directly through a select * from [table]statement.
在数据库上，我会有一个 java 存储过程/函数，它在调用时返回一个多列、多行的结果集。
我想通过select * from [table]语句直接访问这些结果。
So the java stored procedure should behave like a table.
In MySQL the following should be possible (but not java stored procedures): SELECT col1 FROM (EXEC proc1)
所以java存储过程应该表现得像一个表。
在 MySQL 中，以下应该是可能的（但不是 java 存储过程）：SELECT col1 FROM (EXEC proc1)
Is this possible in oracle where proc1 is a java stored procedure?
这在 proc1 是 java 存储过程的 oracle 中是否可行？
回答by Alessandro Rossi
This answeron a different forum may help you.
其他论坛上的这个答案可能对您有所帮助。
Look at the sample in the bottom of the message to see how to select from a collection returned by a Java method ( that may also be a Java Stored Procedure).
查看消息底部的示例，了解如何从 Java 方法（也可能是 Java 存储过程）返回的集合中进行选择。
Here is a sample on how to do it with Java Stored Procedure
这是有关如何使用 Java 存储过程执行此操作的示例
1) Create DB object to define the type of the returned rows:
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) Create the Java class on the DB with a static method(GetSampleResult) that returns a Collection
2）使用返回集合的静态方法（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) Create the Wrap to use Java Stored Procedure in a function
3) 创建 Wrap 以在函数中使用 Java 存储过程
create or replace function GetSampleResult
return try_obj_tab
AS LANGUAGE JAVA
NAME 'QueryReturn.GetSampleResult() return oracle.sql.ARRAY';
4) Show the result
4) 显示结果
SQL> select *
2 from table(GetSampleResult())
3 /
FIELD_A FIELD_B
---------- ----------
1 abc
2 dce
SQL>
回答by Adeel Ansari
Write a SSP (SQL Stored Procedure) to call JSP (Java Stored Procedure), and then use that SSP in your query. Simple eh.
编写一个 SSP（SQL 存储过程）来调用 JSP（Java 存储过程），然后在您的查询中使用该 SSP。简单嗯。
Further, take a look at CallableStatement.
进一步，看看CallableStatement。
回答by Wivani
I have never used it in combination with Java calling the stored procedure but my guess is it should be possible to use the 'pipelined' functionality in recent Oracle databases.
我从未将它与调用存储过程的 Java 结合使用，但我的猜测是应该可以在最近的 Oracle 数据库中使用“流水线”功能。
See hereor Google/Bing for it to learn more.
请参阅此处或 Google/Bing 以了解更多信息。
回答by Nicholas Sushkin
Tom Kyte says "You cannot return a result set from a java stored procedure to plsql."
Tom Kyte 说“您不能将 Java 存储过程的结果集返回到 plsql。”
回答by Petr Pribyl
I'm afraid it is not possible. But if your database supports functionality of selecting data from stored procedure in way you mentioned, you can create a view and select from it.
恐怕这是不可能的。但是，如果您的数据库支持以您提到的方式从存储过程中选择数据的功能，您可以创建一个视图并从中进行选择。
Anyway Oracle can return resultset from a stored procedure and you can access it from Java. See this link
无论如何，Oracle 可以从存储过程返回结果集，您可以从 Java 访问它。看这个链接
