Java 使用 createNativeQuery 调用 Oracle 存储过程

Call Oracle Stored Procedure Using createNativeQuery

提问by sdoca

I need to call a stored procedure using JPA and found this article:

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

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

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

which explains how to use EntityManager.createNativeQuery. However, the example actually calls a function that has a return argument. I've tried searching for an example of calling a stored procedure that doesn't have a return, but haven't been able to find anything.

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

Can I use createNativeQuery to call a procedure? Or does the procedure need to be modified to be a function (perhaps returns a success/failure result)?

我可以使用 createNativeQuery 来调用过程吗？还是需要将过程修改为函数（可能返回成功/失败结果）？

Thanks!

谢谢！

采纳答案by Pascal Thivent

From the JPA wiki:

来自 JPA 维基：

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

1.4 存储过程

JPA 对存储过程没有任何直接支持。某些类型的存储过程可以通过使用本机查询在 JPA 中执行。JPA 中的本机查询允许任何不返回任何内容或返回要执行的数据库结果集的 SQL。执行存储过程的语法取决于数据库。JPA 不支持使用 OUTPUT 或 INOUT 参数的存储过程。某些数据库（例如 DB2、Sybase 和 SQL Server）允许存储过程返回结果集。Oracle 不允许返回结果集，只允许返回 OUTPUT 参数，但确实定义了可以作为 OUTPUT 参数返回的 CURSOR 类型。Oracle 还支持可以返回单个值的存储函数。通过从 Oracle DUAL 表中选择函数值，通常可以使用本机 SQL 查询来执行存储的函数。

一些 JPA 提供程序扩展了对存储过程的支持，一些还支持使用存储过程或自定义 SQL 覆盖实体的任何 CRUD 操作。一些 JPA 提供程序支持 CURSOR OUTPUT 参数。

在 Oracle 上执行存储过程的示例

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

So my advices would be:

所以我的建议是：

• do some experimentations (i.e. try it)
• if required (and if possible) modify the stored procedure
• consider provider specific extensions (as last resort)

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

回答by DCookie

If it is possible, you'll likely need to wrap the procedure call this way:

如果可能，您可能需要以这种方式包装过程调用：

em.createNativeQuery("BEGIN yourprocedure; END;")

Getting return values back may be problematic with procedures. Passing them in should be easy.

获取返回值可能对过程有问题。传递它们应该很容易。

回答by Peter Krogh

As already stated, the JPA spec does not yet support StoredProcedures, however the EclipseLink JPA provider does:

如前所述，JPA 规范尚不​​支持 StoredProcedures，但 EclipseLink JPA 提供程序支持：

http://wiki.eclipse.org/Using_EclipseLink_JPA_Extensions_(ELUG)#Using_EclipseLink_JPA_Extensions_for_Stored_Procedure_Query

http://wiki.eclipse.org/Using_EclipseLink_JPA_Extensions_(ELUG)#Using_EclipseLink_JPA_Extensions_for_Stored_Procedure_Query