java - 使用 createNativeQuery 调用 Oracle 存储过程

Question

我需要使用 JPA 调用存储过程并找到这篇文章:

http://www.oracle.com/technology/pub/articles/vasiliev-jpql.html

解释了如何使用 EntityManager.createNativeQuery。但是，该示例实际上调用了一个具有返回参数的函数。我试过搜索调用没有返回值的存储过程的示例，但没有找到任何内容。

我可以使用 createNativeQuery 来调用过程吗？或者是否需要将过程修改为函数(可能返回成功/失败结果)？

谢谢!

Answer 1

来自 JPA wiki:

1.4 Stored Procedures

JPA does not have any direct support for stored procedures. Some types of stored procedures can be executed in JPA through using native queries. Native queries in JPA allow any SQL that returns nothing, or returns a database result set to be executed. The syntax to execute a stored procedure depends on the database. JPA does not support stored procedures that use OUTPUT or INOUT parameters. Some databases such as DB2, Sybase and SQL Server allow for stored procedures to return result sets. Oracle does not allow results sets to be returned, only OUTPUT parameters, but does define a CURSOR type that can be returned as an OUTPUT parameter. Oracle also supports stored functions, that can return a single value. A stored function can normally be executed using a native SQL query by selecting the function value from the Oracle DUAL table.

Some JPA providers have extended support for stored procedures, some also support overriding any CRUD operation for an Entity with a stored procedure or custom SQL. Some JPA providers have support for CURSOR OUTPUT parameters.

Example executing a stored procedure on Oracle

EntityManager em = getEntityManager();
Query query = em.createNativeQuery("BEGIN VALIDATE_EMP(P_EMP_ID=>?); END;");
query.setParameter(1, empId);
query.executeUpdate();

所以我的建议是:

• 做一些实验(即尝试)
• 如果需要(并且可能的话)修改存储过程
• 考虑提供商特定的扩展(作为最后的手段)