问题描述
需要访问从PostgreSQL返回setof refcursor的过程。
Need to access a procedure that return setof refcursor from PostgreSQL.
我能够访问第一个对象，但不能访问其余对象而不是其余对象。
I am able to access the first object but not rest of object not rest of objects.
con.setAutoCommit(false);
try (CallableStatement proc =
con.prepareCall("{ ? = call usp_sel_article_initialdata_new1() }")) {
proc.registerOutParameter(1, Types.OTHER);
proc.execute();
ResultSet results = (ResultSet) proc.getObject(1);
while (results.next()) {
System.out.println("Result Id" + results.getString(1));
System.out.println("Results Name" + results.getString(2));
}
这给了我第一个refcursor值，但当我尝试使用第二个refcursor时给我错误
我用这一行：
This give me the first refcursor values but when i try to use second refcursor it give me errorI use this line:
proc.registerOutParameter(2, Types.OTHER);
这是错误的。还试过：
proc.registerOutParameter(1, Types.REF);
这也行不通。
过程示例是：
This also not work.Procedure Example is:
CREATE OR REPLACE FUNCTION usp_sel_article_initialdata_new1()
RETURNS SETOF refcursor AS
$BODY$
Declare swv_refcur refcursor;
swv_refcur2 refcursor;
DECLARE r record;
BEGIN
open SWV_RefCur for Select OM.opID as ID,OM.OperatorName as Name from operator
AS OM (OPid bigint,OperatorName character varying(100),status boolean)
where OM.status = true
order By OperatorName;
return next SWV_RefCur;
open SWV_RefCur2 for Select CC.cirid as ID,CC.cirnm as Name from circle
AS CC (cirid bigint,cirnm character varying(100),status boolean)
where Status = true and cirid not in(28,31)
order by cirnm;
return next SWV_RefCur2;
请帮我看看如何访问第二个对象。
Please help me how to access second object.
推荐答案
返回setof refcursor 表示您获得常规 ResultSet 调用 getObject（）时，row包含另一个 ResultSet：
returns setof refcursor means you get a regular ResultSet where each "row" contains another ResultSet when calling getObject():
以下作品对我来说：
ResultSet rs = stmt.executeQuery("select * from usp_sel_article_initialdata_new1()");
if (rs.next())
{
// first result set returned
Object o = rs.getObject(1);
if (o instanceof ResultSet)
{
ResultSet rs1 = (ResultSet)o;
while (rs1.next())
{
int id = rs1.getInt(1);
String name = rs1.getString(2);
.... retrieve the other columns using the approriate getXXX() calls
}
}
}
if (rs.next())
{
// process second ResultSet
Object o = rs.getObject(1);
if (o instanceof ResultSet)
{
ResultSet rs2 = (ResultSet)o;
while (rs2.next())
{
......
}
}
}
从 psql 中你也可以使用 select * from usp_sel_article_initialdata_new1（）之后您只需要使用 FETCH ALL 。有关示例，请参阅手册：
From within psql you can also use select * from usp_sel_article_initialdata_new1() you just need to use FETCH ALL afterwards. See the manual for an example: http://www.postgresql.org/docs/current/static/plpgsql-cursors.html#AEN59018
postgres=> select * from usp_sel_article_initialdata_new1();
usp_sel_article_initialdata_new1
----------------------------------
<unnamed portal 1>
<unnamed portal 2>
(2 rows)
postgres=> fetch all from "<unnamed portal 1>";
?column?
----------
1
(1 row)
postgres=> fetch all from "<unnamed portal 2>";
?column?
----------
2
(1 row)
postgres=>
（我为上面的例子创建了一个虚函数，只返回值<$的单行c $ c> 1 表示第一个游标， 2 表示第二个游标）
(I created a dummy function for the above example that only returns a single row with the value 1 for the first cursor and 2 for the second cursor)
编辑：
为了实现这一点，需要在事务中运行。因此必须关闭自动提交：
In order for this to work, this needs to be run inside a transaction. Therefor autocommit must be turned off:
connection.setAutoCommit(false);
这篇关于如何在Java中访问从PostgreSQL返回setof refcursor的过程？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！