如何从java调用oracle函数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24902849/
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
How to call oracle function from java
提问by Java Questions
How to call oracle function from java?.
如何从java调用oracle函数？
I have a oracle function aaa.fucntion(number,date);, this method returns true or false. how do call this from java and get the returned value?
我有一个oracle函数aaa.fucntion(number,date);，这个方法返回true或false。如何从java调用它并获取返回值？
I am using Hibernate
我在用 Hibernate
this is what i tried,
这是我试过的，
Session session = null;
String associateHistorySQL="";
try {
session = HibernateUtil.currentSession();
associateHistorySQL = "SELECT aa.myFunction(:aorId,:givenDate) from dual";
Query associateHistoryQuery = session.createQuery(associateHistorySQL);
associateHistoryQuery.setParameter("aorId", associateOfficeRecordId);
associateHistoryQuery.setParameter("givenDate", date);
List associateHistoryList = associateHistoryQuery.list();
if (associateHistoryList != null && associateHistoryList.size() > 0 && new Integer(associateHistoryQuery.uniqueResult().toString()) > 0)
return true;
else
return false;
} finally {
HibernateUtil.cleanUpHibernateFromDao(false);
}
This is the exception i get unexpected token: aa: line 1:1: unexpected token: aa
这是我得到的例外 unexpected token: aa: line 1:1: unexpected token: aa
thanks
谢谢
回答by Darshan Lila
There are actually multiple ways of doing so. But the easiest of them all is firing a query. Here's how to do it.
实际上有多种方法可以做到这一点。但其中最简单的是触发查询。这是如何做到的。
String sql="select myFunction('"+number+"','"+date"') from dual";
statement.execute(sql);
Set the input and output parameters if you are using JDBC.
如果使用 JDBC，请设置输入和输出参数。
If you are using hibernate use Named Queries something like this: YourMapping.hbm.xml
如果您使用的是休眠状态，请使用类似这样的命名查询： YourMapping.hbm.xml
<sql-query name="my_function" callable="true">
<return alias="demo" class="net.bean.Demo">
<return-property name="id" column="id"/>
<return-property name="fname" column="fname"/>
<return-property name="lname" column="lname"/>
</return>
{?=call demoFunc(:param1,:param2)}
</sql-query>
Now this will create a Named Query for the function
现在这将为函数创建一个命名查询
Next thing to do is simply call it using following code
接下来要做的就是使用以下代码调用它
Query query=session.getNamedQuery("my_function");
query.setParameter("parma1",date);
query.setParameter("parma2",number);
query.executeUpdate();
Note that in hbm.xml file the return class name and properties exists only apply if you have mapped the returning values if the function returning appropriate values.
请注意，在 hbm.xml 文件中，如果函数返回适当的值，则返回类名称和属性仅适用于您已映射返回值的情况。
回答by Ninad Pingale
Use session.doWorkfrom hibernate.
session.doWork从休眠状态使用。
How to call a Oracle function from hibernate with return parameter?
From Oracle documentation-
来自Oracle documentation-
http://docs.oracle.com/cd/F49540_01/DOC/java.815/a64686/04_call5.htm
http://docs.oracle.com/cd/F49540_01/DOC/java.815/a64686/04_call5.htm
FUNCTION balance (acct_id NUMBER) RETURN NUMBER IS
acct_bal NUMBER;
BEGIN
SELECT bal INTO acct_bal FROM accts
WHERE acct_no = acct_id;
RETURN acct_bal;
END;
From a JDBC program, your call to the function balance might look like this:
在 JDBC 程序中，您对函数 balance 的调用可能如下所示：
CallableStatement cstmt = conn.prepareCall("{? = CALL balance(?)}");
cstmt.registerOutParameter(1, Types.FLOAT);
cstmt.setInt(2, acctNo);
cstmt.executeUpdate();
float acctBal = cstmt.getFloat(1);
回答by AtmiyaDas2014
oracle function:
神谕功能：
FUNCTION ap_ch_get_acct_balances (VAR_PI_MOB_NO_ACCT_NO VARCHAR2,
VAR_REPLY_CODE OUT NUMBER, VAR_EXT_RESPONSE OUT VARCHAR2, VAR_PO_ACC_BAL OUT CHAR,
VAR_PO_ACCT_NO OUT CHAR)
call in java:
在java中调用：
String call = "{ ? = call FCRLIVE.AP_CH_GET_ACCT_BALANCES(?, ?, ?, ?, ?) }";
回答by cmg_george
You can use CallableStatement
您可以使用 CallableStatement
String sql="begin ? := aaaa.fucntion(?,?); end;";
CallableStatement stmt = connection.prepareCall(sql);
stmt.registerOutParameter(1, OracleTypes.BOOLEAN);
stmt.setInt(2, number);
stmt.setTimestamp(3, date);
stmt.execute();
After that you can read the returned value with:
之后，您可以使用以下命令读取返回值：
stmt.getBoolean(1)
