使用游标并在带有Java / JDBC的Oracle PL / SQL中获得结果

徐黎华

我有一个像这样构造的PL / SQL查询：
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客户端上，它将正常工作。因此查询没有问题。
PS我不想将代码存储为存储过程，并由于某些限制而调用它。

解决方案:


            这是不可能的。您无法从匿名PL / SQL块返回结果集（因此无法从JDBC获取结果集）。
您将需要直接从JDBC运行select。
唯一的，非常丑陋的解决方法是使用dbms_output.put_line()和读取之后的内容。但这是一个非常丑陋的技巧，直接在JDBC中处理SELECT查询的结果要好得多。

编辑1
这是一个使用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（对于评论来说这太长了）
嵌套循环来检索数据几乎总是一个坏主意。如果您发现自己在做这样的事情：
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中占用过多的内存：
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，可以更改。但是即使那样，您也不会占用过多的内存。

使用游标并在带有Java / JDBC的Oracle PL / SQL中获得结果

徐黎华 LV1