使用StoredProcedureQuery调用Oracle函数吗? [英] Calling Oracle function with StoredProcedureQuery?

问题描述

我正在尝试使用

I'm trying to call an Oracle PL/SQL function using javax.persistence.StoredProcedureQuery and I get the error PLS-00221: 'function_name' is not a procedure or is undefined

因此，我假设无法使用 StoredProcedureQuery ?

So I assume its not possible to call an Oracle function using StoredProcedureQuery?

我看不到JPA提供了调用函数的接口(与存储过程相反).那我该怎么做呢?

I don't see JPA offering an interface to call functions(as opposed to stored procedures). So how do I accomplish it?

推荐答案

Oracle功能示例:

CREATE OR REPLACE FUNCTION f_get_random_number
  RETURN NUMBER
AS
BEGIN
  RETURN TRUNC(dbms_random.value(1,100));
END;

解决方案1:使用SQL查询

BigDecimal val = (BigDecimal)entityManager
                    .createNativeQuery("SELECT f_get_random_number FROM DUAL")
                    .getSingleResult();

解决方案2:使用JDBC API

Session session = entityManager.unwrap( Session.class );         
    Integer out = session.doReturningWork( 
        connection -> {
        try (CallableStatement function = connection.prepareCall("{ ? = call f_get_random_number }" )) {
            function.registerOutParameter( 1, Types.INTEGER );
            function.execute();
            return function.getInt(1);
        } 
    } );

这篇关于使用StoredProcedureQuery调用Oracle函数吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！