nhibernate，在 Oracle 中调用返回 sys refcursor 的函数

2022-01-02 00:00:00 function oracle nhibernate sys-refcursor

我正在尝试使用 nhibernate 调用一个返回 ref 游标的函数(oracle)，但我没有成功使用 hbm 文件，任何人都可以指导我.

I am trying to call a function (oracle) using nhibernate that return ref cursor, but i am not successful with the hbm file, can any one please guide me with this.

如果我像 <return class ... 那样做，我会收到配置错误.

If i make it like <return class ... I am getting configuration error.

我试过 { ?= call package.function(:a, :b, :c) as result from dual }，即使这样也不起作用.

I tried { ? = call package.function(:a, :b, :c) as result from dual }, even this is also not working.

推荐答案

使用 nHibernate 调用 ORACLE 函数/过程时存在一些限制.
如参考文档 (17.2.2.1) 中所述:

There are some limitations when calling ORACLE functions/procedures with nHibernate.
As stated in the reference documentation (17.2.2.1):

对于 Oracle，适用以下规则:

一个函数必须返回一个结果集.a的第一个参数过程必须是返回结果集的 OUT.这是由在 Oracle 9 或 10 中使用 SYS_REFCURSOR 类型.在 Oracle 中，您需要定义 REF CURSOR 类型，请参阅 Oracle 文献.

A function must return a result set. The first parameter of a procedure must be an OUT that returns a result set. This is done by using a SYS_REFCURSOR type in Oracle 9 or 10. In Oracle you need to define a REF CURSOR type, see Oracle literature.

因为我遇到了同样的问题，所以我试着玩了一下.

I've tried to play a little bit with it as I am having the same problem.

这是包装程序:

头部: 

create or replace
PACKAGE           "MYPACKAGE" AS

    TYPE ReferenceCursor IS REF CURSOR;

    PROCEDURE  usp_GetDual 
    (
    pCursor OUT ReferenceCursor,
    a IN CHAR,
    b IN CHAR
    );

END MYPACKAGE;

身体:

PROCEDURE usp_GetDual
    (
          pCursor OUT ReferenceCursor,
          a IN CHAR,
          b IN CHAR
    )

  IS

    err_code NUMBER;
    err_msg VARCHAR2(200);

  BEGIN

  OPEN pCursor FOR
    SELECT * FROM dual;

   EXCEPTION
    WHEN OTHERS THEN 
        err_code := SQLCODE;
        err_msg := substr(SQLERRM, 1, 200);

END usp_GetDual;

这是我的映射文件:

<?xml version="1.0" encoding="utf-8"?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" assembly="MyAssembly">
    <sql-query name="GetDaul">
        { call MYPACKAGE.usp_GetDual ( :a, :b ) }
    </sql-query>
</hibernate-mapping>

这是我用来测试它的代码:

and this is the code I've used to test it:

var value = Session.GetNamedQuery("GetDaul")
    .SetParameter<string>("a", "AAA")
    .SetParameter<string>("b", "BBB")
    .UniqueResult();

如您所见，REF CURSOR 必须是过程中的第一个参数 (pCursor OUT ReferenceCursor)，您无需在映射或调用中引用它.

As you can see the REF CURSOR must be the first parameter in your procedure (pCursor OUT ReferenceCursor) and you do not need to reference it in your mapping or your call.

如果你想返回实体，事情会变得有点复杂.

If you want to return entities, things get a little bit more complicated.

您的映射文件必须指定返回类型(类):

Your mapping file must specify the return type (class):

<?xml version="1.0" encoding="utf-8"?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" assembly="MyAssembly">
    <sql-query name="GetOrders">
         <return class="MyAssembly.Domain.MyOrder, MyAssembly" />
         { call MYPACKAGE.usp_GetOrders ( :pCompanyCode , :pOrderNumer ) }
    </sql-query>
</hibernate-mapping>

您必须定义您的实体:

public class MyOrder
{
    public virtual string Number { get; set; }
    public virtual int Ver { get; private set; }
    public virtual string Company { get; set; }
    public virtual string Customer { get; set; }

    public override bool Equals(object obj)
    {
        if (obj == null)
            return false;
        Order order = obj as Order;
        if (order == null)
            return false;
        if (this.Number.Trim() == order.Number.Trim() &&
            this.Ver == order.Ver &&
            this.Company.Trim() == order.Company.Trim()
            )
            return true;
        else
            return false;
    }

    public override int GetHashCode()
    {
        int hash = 0;
        hash = hash +
            (null == this.Number ? 0 : this.Number.GetHashCode())
            +
            (this.Ver.GetHashCode())
            +
            (null == this.Company ? 0 : this.Company.GetHashCode());

        return (hash);
    }
}

这是您实体的映射文件:

and this is the mapping file for your entity:

<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" assembly="MyAssembly" namespace="MyAssembly.Domain">
  <class name="MyOrder" table="OCSAORH" mutable="false">
    <composite-id>
      <key-property name="Number" column="OCHORDN" type="String" length="10"></key-property>
      <key-property name="Ver" column="OCHAMND" type="Int32"></key-property>
      <key-property name="Company" column="OCHCOSC" type="String" length="5"></key-property>
    </composite-id>
    <property name="Customer" column="OCHCLII" type="String"></property>
  </class>
</hibernate-mapping>

这是我的 ORACLE 包:

This is my ORACLE package:

PROCEDURE usp_GetOrders 
          (
          pCursor OUT ReferenceCursor,
          pCompanyCode IN CHAR,
          pOrderNumer IN CHAR
      )

  IS

    err_code NUMBER;
    err_msg VARCHAR2(200);

  BEGIN

  OPEN pCursor FOR
       SELECT 
            OCSAORH.*
      FROM OCSAORH 
            WHERE OCSAORH.OCHAMND = 0
                AND OCSAORH.OCHCOSC = pCompanyCode
                AND OCSAORH.OCHORDN = pOrderNumer;              
    EXCEPTION
            WHEN OTHERS THEN 
          err_code := SQLCODE;
          err_msg := substr(SQLERRM, 1, 200);

END usp_GetOrders;

现在您可以使用参数轻松获取订单:

And now you can easily get your orders using parameters:

var listOfOrders = Session.GetNamedQuery("GetOrder")
    .SetParameter<string>("pCompanyCode", "ABC")
        .SetParameter<string>("pOrderNumer", "XYZ")
        .List<Domain.MyOrder>();

这篇文章帮助我了解了如何事情必须要做.

This article helped me to understand how thing must be done.

相关文章