使用createNativeQuery调用Oracle存储过程 [英] Call Oracle Stored Procedure Using createNativeQuery

问题描述

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

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

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

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

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.

我可以使用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)?

谢谢！

推荐答案

来自JPA wiki：

From the JPA wiki:

1.4存储过程

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

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.

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

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.

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

所以我的建议是：

• 做一些实验（即试试）


• 如果需要（如果可能的话）修改存储过程


• 考虑提供商特定的扩展（作为最后的手段）

这篇关于使用createNativeQuery调用Oracle存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！