在java中调用pl/sql函数？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26550465/
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
Call pl/sql function in java?
提问by SqlNoob
So I've got a function that checks how many cancellations are in my booking table:
所以我有一个函数来检查我的预订表中有多少取消：
CREATE OR REPLACE FUNCTION total_cancellations
RETURN number IS
t_canc number := 0;
BEGIN
SELECT count(*) into t_canc
FROM booking where status = 'CANCELLED';
RETURN t_canc;
END;
/
To execute his in sql I use:
要在 sql 中执行他，我使用：
set serveroutput on
DECLARE
c number;
BEGIN
c := total_cancellations();
dbms_output.put_line('Total no. of Cancellations: ' || c);
END;
/
My result is:
我的结果是：
anonymous block completed
Total no. of Cancellations: 1
My question is can someone help me call the function in JAVA, I have tried but with no luck.
我的问题是有人可以帮我在 JAVA 中调用该函数，我已经尝试过但没有运气。
采纳答案by Santhosh
Java provides CallableStatementsfor such purposes .
Java 提供CallableStatements了这样的目的。
CallableStatement cstmt = conn.prepareCall("{? = CALL total_cancellations()}");
cstmt.registerOutParameter(1, Types.INTEGER);
cstmt.setInt(2, acctNo);
cstmt.executeUpdate();
int cancel= cstmt.getInt(1);
System.out.print("Cancellation is "+cancel);
will print the same as you do in the pl/sql. As per docs Connection#prepareCall(),
将打印与您在 pl/sql 中所做的相同。根据文档Connection#prepareCall()，
Creates a CallableStatement object for calling database stored procedures. The CallableStatement object provides methods for setting up its IN and OUT parameters, and methods for executing the call to a stored procedure.
创建用于调用数据库存储过程的 CallableStatement 对象。CallableStatement 对象提供用于设置其 IN 和 OUT 参数的方法，以及用于执行对存储过程的调用的方法。
You can also pass parameters for the function . for ex ,
您还可以为函数传递参数。例如，
conn.prepareCall("{? = CALL total_cancellations(?)}");
cstmt.setInt(2, value);
will pass the values to the function as input parameter.
将值作为输入参数传递给函数。
Hope this helps !
希望这可以帮助 ！
回答by giannis christofakis
Prepare a Callable Statement
There are two formats available, the familiar block syntax used by Oracle and the ANSI 92 standard syntax. In the case of our sample program, the block syntax has the form:
CallableStatement vStatement = vDatabaseConnection.prepareCall( "begin ? := javatest( ?, ? ); end;" );The ANSI 92 syntax has the form:
CallableStatement vStatement = vDatabaseConnection.prepareCall( "{ ? = call javatest( ?, ? )}");
准备可调用语句
有两种可用的格式，Oracle 使用的熟悉的块语法和 ANSI 92 标准语法。在我们的示例程序中，块语法具有以下形式：
CallableStatement vStatement = vDatabaseConnection.prepareCall( "begin ? := javatest( ?, ? ); end;" );ANSI 92 语法具有以下形式：
CallableStatement vStatement = vDatabaseConnection.prepareCall( "{ ? = call javatest( ?, ? )}");
If you receive the below error, you might want to use the first format.
如果您收到以下错误，您可能需要使用第一种格式。
total_cancellations is not a procedure or is undefined error.
total_cancellations 不是过程或未定义的错误。
Sample code.
示例代码。
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@xx.xxx.xx.xxx:1521:xxx", "user","pass");
CallableStatement cstmt = conn.prepareCall("begin ? := TEST_FUNC(?,?); end;");
cstmt.registerOutParameter(1, Types.INTEGER);
cstmt.setString(2, "Test");
cstmt.setInt(3, 1001);
cstmt.execute();
int result = cstmt.getInt(1);
System.out.print("Result: " + result);
cstmt.close();
conn.close();
