使用游标并在带有Java/JDBC的Oracle PL/SQL中获得结果 [英] Using cursors and getting result in Oracle PL/SQL with Java/JDBC
问题描述
我有一个像这样构造的PL/SQL查询:
I have a PL/SQL query constructed like this :
DECLARE
a NUMBER;
B NUMBER;
CURSOR cursor
IS
( SOME SELECT QUERY);
BEGIN
OPEN cursor;
LOOP
SOME STUFF;
END LOOP;
CLOSE cursor;
END
如何使用jdbc从Java代码运行此查询并获取结果集?我尝试不使用游标来运行查询,并且其运行正常.我想不出一种在Java代码中执行此操作的方法.如果我直接将查询运行到oracle客户端上,它将正常工作.因此查询没有问题.
How can I run this query from a java code using jdbc and get the resultset? I have tried running the query without using cursor, and its running correctly. I couldn't figure out a way to do this in java code. If I run the query directly onto oracle client, it works with no problems. So there is no problem with the query.
P.S.由于某些限制,我不想将代码存储为存储过程并调用它.
P.S. I dont want to store the code as stored procedure and call that due to some constraints.
推荐答案
这是不可能的.您无法从匿名PL/SQL块返回结果集(因此无法从JDBC获取结果集).
This is not possible. You cannot return a result set from an anonymous PL/SQL block (and therefor there is no way to get it from JDBC).
您将需要直接从JDBC运行select.
You will need to run the select directly from JDBC.
唯一,非常丑陋的解决方法是使用dbms_output.put_line()
,然后读取.但这是一个非常丑陋的技巧,直接在JDBC中处理SELECT查询的结果要好得多.
The only, really ugly workaround would be to use dbms_output.put_line()
and the read that afterwards. But that is a really ugly hack and processing the result of the SELECT query directly in JDBC is much better.
这里是使用dbms_output的小例子:
Here is a little example using dbms_output:
Connection con = ....;
// turn on support for dbms_output
CallableStatement cstmt = con.prepareCall("{call dbms_output.enable(32000) }");
cstmt.execute();
// run your PL/SQL block
Statement stmt = con.createStatement();
String sql =
"declare \n" +
" a number; \n" +
" cursor c1 is select id from foo; \n" +
"begin \n" +
" open c1; \n" +
" loop \n" +
" fetch c1 into a; \n" +
" exit when c1%notfound; \n" +
" dbms_output.put_line('ID: '||to_char(a)); \n" +
" end loop; \n" +
"end;";
stmt.execute(sql);
// retrieve the messages written with dbms_output
cstmt = con.prepareCall("{call dbms_output.get_line(?,?)}");
cstmt.registerOutParameter(1,java.sql.Types.VARCHAR);
cstmt.registerOutParameter(2,java.sql.Types.NUMERIC);
int status = 0;
while (status == 0)
{
cstmt.execute();
String line = cstmt.getString(1);
status = cstmt.getInt(2);
if (line != null && status == 0)
{
System.out.println(line);
}
}
编辑2(对于评论来说太长了)
嵌套循环来检索数据几乎总是一个坏主意.如果您发现自己在做这样的事情:
Edit 2 (this is too long for a comment)
Nesting loops to retrieve data is almost always a bad idea. If you find your self doing something like this:
begin
for data_1 in (select id from foo_1) loop
dbms_output.put_line(to_char(data_1.id));
for data_2 in (select f2.col1, f2.col2 from foo_2 f2 where f2.id = data_1.id) loop
... do something else
end loop;
end loop;
end;
/
这样做会更有效:
begin
for data_1 in (select f2.col1, f2.col2 from foo_2 f2
where f2.id in (select f1.id from foo_1 f1)) loop
... do something
end loop;
end;
/
使用以下类似的方法可以在JDBC中进行处理,而不会占用过多的内存:
This can be processed without an excessive memory in JDBC using something like this:
String sql = "select f2.col1, f2.col2 from foo_2 f2 where f2.id in (select f1.id from foo_1 f1)";
Statement stmt = connection.createStatement();
ResultSet rs = stmt.executeQuery(sql);
while (rs.next())
{
String col1_value = rs.getString(1);
int col2_value = rs.getInt(2);
... do something
}
即使处理数十亿行,上述代码也只会在内存中保留一行.准确地说:JDBC驱动程序实际上将预取多行.默认值为10,可以更改.但是即使那样,您也不会占用过多的内存.
The above code will only hold one row in memory, even if you process billions of rows. To be precise: the JDBC driver will actually pre-fetch more than one row. The default is 10 and can be changed. But even then you don't have any excessive memory usage.
这篇关于使用游标并在带有Java/JDBC的Oracle PL/SQL中获得结果的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!