java中调用pl sql优点,在java中调用pl / sql函数？

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 !