如何通过休眠访问pl / sql proc中的out参数 [英] How do i access an out parameter in a pl/sql proc via hibernate

问题描述

  PROCEDURE pr_log_process_started（
 p_process_id IN log_process_status.process_id ％TYPE，
 p_run_id IN OUT log_process_status.run_id％TYPE）; 
  

我怎样才能通过Hibernate调用这个过程并访问第二个参数的值调用？

解决方案

如何通过Hibernate调用此过程并访问调用后的第二个参数？

我不认为你可以。至少，这不是我对第16章原生SQL 官方文档：

16.2.2。使用存储过程进行查询

Hibernate3通过存储过程和
函数提供对
查询的支持。以下大部分
文档对于两者都是等效的。
存储过程/函数必须
返回结果集作为第一个
out参数才能够使用
Hibernate。在Oracle 9及更高版本中存储的
函数的一个例子是
：

  CREATE OR REPLACE FUNCTION selectAllEmployments 
 RETURN SYS_REFCURSOR 
 AS 
 st_cursor SYS_REFCURSOR; 
开始为
选择雇员，雇主，
 STARTDATE，ENDDATE，
 REGIONCODE，EID，价值，货币
起价就业
打开st_cursor; 
 RETURN st_cursor; 
 END; 
  

要在Hibernate中使用这个查询，
需要通过命名查询映射它。 / p>

 < sql-query name =selectAllEmployees_SPcallable =true> 
< return alias =empclass =Employment> 
< return-property name =employeecolumn =EMPLOYEE/> 
< return-property name =employercolumn =EMPLOYER/> 
< return-property name =startDatecolumn =STARTDATE/> 
< return-property name =endDatecolumn =ENDDATE/> 
< return-property name =regionCodecolumn =REGIONCODE/> 
< return-property name =idcolumn =EID/> 
< return-property name =salary> 
< return-column name =VALUE/> 
< return-column name =CURRENCY/> 
< / return-property> 
< / return> 
 {？ =调用selectAllEmployments（）} 
< / sql-query> 
  

存储过程目前只有
返回标量和实体。不支持
< return-join> 和
< load-collection> 。

16.2.2.1。使用存储过程的规则/限制

除非遵循一些
过程/函数规则，否则不能将存储过程与
Hibernate一起使用。如果他们做
不遵循这些规则，他们不是
可用于Hibernate。如果你还有
想要使用这些程序，你可以通过
session.connection（）执行
。对于每个数据库，这些规则是
不同的，因为
数据库供应商具有不同的存储的
过程语义/语法。

存储过程查询不能使用
分页
setFirstResult（） / setMaxResults（）。

推荐的调用形式为标准
SQL92： {？ = call
functionName（< parameters>）} 或 {？
= call procedureName（< parameters>} 。不支持本地调用语法。

• 函数必须返回一个结果集。过程
  的第一个参数必须是返回结果$ b的OUT $ b set。这是通过在Oracle 9或10中使用
  SYS_REFCURSOR类型完成的。
  在Oracle中，您需要定义REF
  CURSOR类型。信息。

对于Sybase或MS SQL服务器，
以下规则适用：

• 该过程必须返回一个结果集。请注意，由于这些服务器可以返回多个结果集并更新
  计数，因此Hibernate将迭代
  结果并取第一个结果，即
  是一个结果集作为其返回值。
  其他所有内容都将被丢弃。




• 如果您可以在您的程序中启用SET NOCOUNT ON尽管它可能会更有效率，但这不是
  要求。

总之，要么遵循规则，要么通过 session.connection（）使用原始JDBC。

I have a pl/sql procedure with the following signature

PROCEDURE pr_log_process_started (
p_process_id IN log_process_status.process_id%TYPE, 
p_run_id IN OUT log_process_status.run_id%TYPE);

How can i make a call to this proc via Hibernate and access the value of the second parameter after the call?

解决方案

How can i make a call to this proc via Hibernate and access the value of the second parameter after the call?

I don't think you can. At least, that's not my understanding of the Chapter 16. Native SQL of the official documentation:

16.2.2. Using stored procedures for querying

Hibernate3 provides support for queries via stored procedures and functions. Most of the following documentation is equivalent for both. The stored procedure/function must return a resultset as the first out-parameter to be able to work with Hibernate. An example of such a stored function in Oracle 9 and higher is as follows:

CREATE OR REPLACE FUNCTION selectAllEmployments
    RETURN SYS_REFCURSOR
AS
    st_cursor SYS_REFCURSOR;
BEGIN
    OPEN st_cursor FOR
 SELECT EMPLOYEE, EMPLOYER,
 STARTDATE, ENDDATE,
 REGIONCODE, EID, VALUE, CURRENCY
 FROM EMPLOYMENT;
      RETURN  st_cursor;
 END;

To use this query in Hibernate you need to map it via a named query.

<sql-query name="selectAllEmployees_SP" callable="true">
    <return alias="emp" class="Employment">
        <return-property name="employee" column="EMPLOYEE"/>
        <return-property name="employer" column="EMPLOYER"/>
        <return-property name="startDate" column="STARTDATE"/>
        <return-property name="endDate" column="ENDDATE"/>
        <return-property name="regionCode" column="REGIONCODE"/>
        <return-property name="id" column="EID"/>
        <return-property name="salary">
            <return-column name="VALUE"/>
            <return-column name="CURRENCY"/>
        </return-property>
    </return>
    { ? = call selectAllEmployments() }
</sql-query>

Stored procedures currently only return scalars and entities. <return-join> and <load-collection> are not supported.

16.2.2.1. Rules/limitations for using stored procedures

You cannot use stored procedures with Hibernate unless you follow some procedure/function rules. If they do not follow those rules they are not usable with Hibernate. If you still want to use these procedures you have to execute them via session.connection(). The rules are different for each database, since database vendors have different stored procedure semantics/syntax.

Stored procedure queries cannot be paged with setFirstResult()/setMaxResults().

The recommended call form is standard SQL92: { ? = call functionName(<parameters>) } or { ? = call procedureName(<parameters>}. Native call syntax is not supported.

For Oracle the following rules apply:

• 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 for further information.

For Sybase or MS SQL server the following rules apply:

• The procedure must return a result set. Note that since these servers can return multiple result sets and update counts, Hibernate will iterate the results and take the first result that is a result set as its return value. Everything else will be discarded.

• If you can enable SET NOCOUNT ON in your procedure it will probably be more efficient, but this is not a requirement.

To sum up, either follow the rules or use raw JDBC via session.connection().

这篇关于如何通过休眠访问pl / sql proc中的out参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！