我如何使用Hibernate调用参数为sysrefcursor的Oracle存储过程? [英] How can I call Oracle stored procedure which has one out parameter as sysrefcursor using Hibernate?

问题描述

这是我的存储过程，它具有一个out参数:

Here is my stored procedure which has one out parameter:

create or replace
PROCEDURE CITYMST_LIST 
(
 P_CURSOR OUT sys_refcursor
  ,CMPID IN NUMBER
, STSTATUS IN NUMBER  

) AS 
BEGIN
  OPEN P_CURSOR FOR
  SELECT * FROM city_master WHERE citycmp_id=CMPID AND city_status=STSTATUS;
END CITYMST_LIST;

在我的hbm配置中，我已映射存储过程:

In my hbm configuration I have mapped stored procedure:

<sql-query name="CITYMST_LIST" callable="true">
    <return  class="CityMaster">
    </return>
    { ? = call CITYMST_LIST(?,?) }

</sql-query>

I have also tried : 
    { call CITYMST_LIST(?,?) }

这是我的Java代码:

Here is my java code :

    Query query = session.getNamedQuery("CITYMST_LIST");
    query.setParameter("CMPID",Cmpid);
    query.setParameter("STSTATUS",true);
    list = query.list();

推荐答案

您可以执行以下操作，以调用返回sys_refcursor(注释)的过程

You can do the following in order to call a procedure which return sys_refcursor (annotation)

@javax.persistence.NamedNativeQuery(name = "getProc", query = "{ call
CITYMST_LIST(?,:cmpid,:status) }", resultClass = <EntityClass>.class, hints = {
@javax.persistence.QueryHint(name = "org.hibernate.callable", value = "true") })

在DAOImpl中

Query query = entityManager.createNamedQuery("getProc"); 
query.setParameter("cmpid","123"); 
query.setParameter("status","true"); 
query.getResultList(); 

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