JDBC/PLSQL:我如何获得一个匿名块的返回值

Question

我有一个匿名代码块。

String plsql = "DECLAREn";
plsql += "tret NUMBER;n";
plsql += "tretsum NUMBER := 0;n";
plsql += "BEGINn";
plsql += "tret := 1;n";
plsql += "tretsum := ret + retsum;n";
plsql += "END;n";

我通过JDBC调用它。

CallableStatement cs = con.prepareCall (plsql);
boolean ret = cs.execute ();
cs.close ();

一切正常

我怎样才能使我的匿名块返回值(例如retsum)到我的java代码?

我知道存储函数和处理返回-但我不能在这里做。

CallableStatement cst = con.prepareCall ("{? = call foo ();}");
cst.registerOutParameter (1, Types.INTEGER);
cst.execute ();
int ret = cst.getInt (1);

Answer 1

在匿名块中使用绑定变量(用于匿名变量的?或用于命名变量的:something):

String plsql = "DECLARE
ret NUMBER;
retsum NUMBER := 0;
BEGIN
ret := 1;
retsum := ret + retsum;
? := retsum;
END;";
CallableStatement cst = con.prepareCall (plsql);
cst.registerOutParameter (1, Types.INTEGER);
cst.execute ();
int ret = cst.getInt (1);

Answer 2

自Oracle 12c以来，另一种可能的方法是声明内联函数并使用通用select语句:

with function f
return number
as
ret NUMBER;
retsum NUMBER := 0;
BEGIN
ret := 1;
retsum := ret + retsum;
return retsum;
END;
select f() as res
from dual

| RES ||——:|| 1 |

db<此处小提琴>