在java中调用pl/sql函数? [英] Call pl/sql function in java?
问题描述
所以我有一个函数来检查我的预订表中有多少取消:
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;
/
要在我使用的 sql 中执行他的:
To execute his in sql I use:
set serveroutput on
DECLARE
c number;
BEGIN
c := total_cancellations();
dbms_output.put_line('Total no. of Cancellations: ' || c);
END;
/
我的结果是:
anonymous block completed
Total no. of Cancellations: 1
我的问题是有人能帮我在 JAVA 中调用这个函数吗,我试过了,但没有成功.
My question is can someone help me call the function in JAVA, I have tried but with no luck.
推荐答案
Java 提供 CallableStatements
用于此类目的.
Java provides CallableStatements
for such purposes .
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);
将打印与您在 pl/sql 中所做的相同.根据文档 <代码>Connection#prepareCall(),
will print the same as you do in the pl/sql. As per docs Connection#prepareCall()
,
创建一个 CallableStatement 对象来调用数据库存储过程.CallableStatement 对象提供设置其 IN 和 OUT 参数的方法,以及执行对存储过程的调用的方法.
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.
您还可以为函数传递参数.例如,
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.
希望这有帮助!
这篇关于在java中调用pl/sql函数?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!