使用 JDBC 时，MySQL 中 Oracle 的 REF CURSOR 的等价物是什么？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/273929/
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
What is the equivalent of Oracle’s REF CURSOR in MySQL when using JDBC?
提问by dacracot
In Oracle I can declare a reference cursor...
在 Oracle 中，我可以声明一个引用游标...
TYPE t_spool IS REF CURSOR RETURN spool%ROWTYPE;
...and use it to pass a cursor as the return value...
...并使用它来传递一个游标作为返回值...
FUNCTION end_spool
RETURN t_spool
AS
v_spool t_spool;
BEGIN
COMMIT;
OPEN v_spool FOR
SELECT
*
FROM
spool
WHERE
key = g_spool_key
ORDER BY
seq;
RETURN v_spool;
END end_spool;
...and then capture it as a result set using JDBC...
...然后使用 JDBC 将其捕获为结果集 ...
private Connection conn;
private CallableStatement stmt;
private OracleResultSet rset;
[...clip...]
stmt = conn.prepareCall("{ ? = call " + call + "}");
stmt.registerOutParameter(1, OracleTypes.CURSOR);
stmt.execute();
rset = (OracleResultSet)stmt.getObject(1);
What is the equivalent in MySQL?
MySQL 中的等价物是什么？
回答by Yoni
Mysql has an implicit cursor that you can magically return from a stored procedure if you issue a select.
Mysql 有一个隐式游标，如果你发出一个选择，你可以神奇地从存储过程中返回它。
Here's an example:
下面是一个例子：
CREATE PROCEDURE `TEST`()
MODIFIES SQL DATA
BEGIN
SELECT * FROM test_table;
END;
and in your java code:
并在您的 Java 代码中：
String query = "{CALL TEST()}";
CallableStatement cs = con.prepareCall(query,
ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_READ_ONLY);
ResultSet rs = cs.executeQuery();
回答by Spencer Kormos
Googling on cursors in MySQL, it doesn't seem like you can actually return a Cursor from a Proc or Function. Additionally, I found the following in the MySQL JDBC manual:
在 MySQL 中搜索游标，似乎您实际上无法从 Proc 或函数返回 Cursor。此外，我在MySQL JDBC 手册中发现了以下内容：
"MySQL does not support SQL cursors, and the JDBC driver doesn't emulate them, so "setCursorName()" has no effect."
“MySQL 不支持 SQL 游标，并且 JDBC 驱动程序不模拟它们，因此“setCursorName()”不起作用。”
In general, I believe Oracle's implementation here breaks JDBC, and is not used elsewhere (MySQL, MSSQL, etc). You should be returning your results as a select statement and iterating over the JDBC ResultSet, as is standard (and intended) practice when using JDBC.
总的来说，我相信 Oracle 在这里的实现破坏了 JDBC，并且没有在其他地方使用（MySQL、MSSQL 等）。您应该将结果作为选择语句返回并遍历 JDBC ResultSet，这是使用 JDBC 时的标准（和预期）实践。
回答by manuel rodriguez coria
fill a temporary table in a procedure and just read the temporary table... :)
在过程中填充临时表，然后读取临时表... :)
