在java中调用pl / sql函数？ [英] Call pl/sql function in java?

问题描述

 创建或替换函数total_cancellations 
 RETURN number IS 
 t_canc number：= 0; 
 BEGIN 
 SELECT count（*）into t_canc 
 FROM booking where status ='CANCELED'; 
 RETURN t_canc; 
 END; 
 / 
  

执行他在sql中的使用：

 在
上设置serveroutput DECLARE 
c number; 
 BEGIN 
 c：= total_cancellations（）; 
 dbms_output.put_line（'取消总数：'|| c）; 
 END; 
 / 
  

我的结果是：

 完成的匿名区块
总数的取消：1 
  

我的问题是有人可以帮我调用JAVA中的函数，没有运气。

解决方案

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（取消是+取消）; 
  

会像在pl / sql中一样打印。按照文档 Connection＃prepareCall（） ，

为调用创建一个CallableStatement对象数据库存储过程。 CallableStatement对象提供了设置IN和OUT参数的方法，以及执行对存储过程调用的方法。

您可以也传递函数的参数。例如：

  conn.prepareCall（{？= CALL total_cancellations（？）}）; 
 cstmt.setInt（2，value）; 
  

会将值作为输入参数传递给函数。

希望这有助于！

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:

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 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);

will print the same as you do in the pl/sql. As per docs 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.

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 !

这篇关于在java中调用pl / sql函数？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！