问题描述
下面是存储过程:
create or replace procedure
proc_emp_name(v_emp out emp.emp_name%TYPE, v_empid in emp.emp_id%TYPE)
is
begin
select emp_name into v_emp from emp where emp_id = v_empid;
dbms_output.put_line('Emp Name: ' || v_emp);
dbms_output.put_line('Procedure created successfully!!!');
end;
我想使用本机SQL调用此链接,但单击此链接,但不确定如何从过程中检索OUT
参数.
I want to invoke this using Native SQL, followed this link but not sure how to retrieve the OUT
parameter from the Procedure.
http://www.mkyong.com /hibernate/如何在休眠状态下调用存储过程/
请告诉我最简单的方法来调用该过程并从中获取结果.
Kindly let me know the simplest way to invoke the procedure and get the results out of it.
编辑
根据建议,检查文档后,我将具有第一个参数的Proc修改为SYS_REFCURSOR
,如下所示:
As suggested, checking the docs, I modified the Proc having first parameter as a SYS_REFCURSOR
as follows:
create or replace procedure
proc_empname_refcursor(v_empname OUT SYS_REFCURSOR, v_deptid in emp.emp_id%type)
is
begin
open v_empname for select * from dept where dept_id = v_deptid;
end;
我可以使用NamedQuery
调用它,但是由于其他一些限制,我不想在映射文件中添加任何内容.我尝试使用下面的代码在不使用NamedQuery
的情况下调用proc,但没有成功:
I am able to invoke it using NamedQuery
but I don't want to add anything in the mapping files because of some other restrictions. I tried the below code for invoking the proc without using NamedQuery
but it did not worked out:
Query query = session.createSQLQuery(
"CALL proc_empname_refcursor(?, :deptId)")
.addEntity(Dept.class)
.setParameter("deptId", new Integer(2));
List<Dept> departments = query.list();
for(int i=0; i<departments.size(); i++){
Dept department = (Dept)departments.get(i);
System.out.println("Dept Id: " + department.getDeptId());
System.out.println("Dept Name: " + department.getDeptName());
}
我遇到了例外情况:
org.hibernate.QueryException: Expected positional parameter count: 1, actual parameters: [] [CALL proc_empname_refcursor(?, :deptId)]
at org.hibernate.impl.AbstractQueryImpl.verifyParameters(AbstractQueryImpl.java:319)
at org.hibernate.impl.SQLQueryImpl.verifyParameters(SQLQueryImpl.java:201)
at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:145)
at com.jdbc.HibernateStartup.main(HibernateStartup.java:70)
请让我知道如何解决此问题.
Kindly let me know how to resolve this.
推荐答案
我已经使用Hibernate和MS SQL Server中的以下代码设法从存储过程中获取out参数:
I've managed to get an out parameter from a stored procedure using the following code in Hibernate and MS SQL Server:
@Override
public Serializable generate(SessionImplementor session, Object object) throws HibernateException {
Connection connection = session.connection();
CallableStatement callable = null;
try {
callable = connection.prepareCall("execute [procedure] ?");
callable.registerOutParameter(1, Types.INTEGER);
callable.execute();
int id = callable.getInt(1);
return id;
} catch (SQLException e) {
(...)
} finally {
(...)
}
}
这篇关于使用本机SQL Hibernate从存储过程中检索值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!