英文:
Get Output parameter from stored procedure without calling execute()
问题
我想通过实体管理器从Java程序中调用一个PL/SQL存储过程:
StoredProcedureQuery storedProcedureQuery = entityManager.createStoredProcedureQuery("someProcedure");
根据我的理解,我需要在这个storedProcedureQuery上调用execute()方法才能执行存储过程并能够检索OUT参数。然而,我可以在不调用execute()方法的情况下,通过简单地调用以下方式来检索这些值:
someValue1 = (Integer)storedProcedureQuery.getOutputParameterValue(1);
someValue2 = (Integer)storedProcedureQuery.getOutputParameterValue(2);
这是如何可能的?在这段代码中,存储过程究竟何时被执行?每次调用getOutputParameterValue()时,存储过程都会被执行吗?是否有关于这种行为的官方文档?
英文:
I want to call a PL/SQL stored procedure from within a Java program via an entity manager:
StoredProcedureQuery storedProcedureQuery = entityManager.createStoredProcedureQuery("someProcedure");
Now my understanding was that I have to call execute() on this storedProcedureQuery in order to execute the procedure and be able to retrieve the OUT-parameters. However, I can retrieve these values without calling the execute()-Method by simply calling
someValue1 = (Integer)storedProcedureQuery.getOutputParameterValue(1);
someValue2 = (Integer)storedProcedureQuery.getOutputParameterValue(2);
How is this possible ? When exactly will the stored procedure be executed in this code snippet? Will the procedure be executed every time when getOutputParameterValue() is called ? Is there any official documentation for this behaviour ?
答案1
得分: 5
以下是您要翻译的内容:
我是这个领域的新学习者,和你一样充满好奇心。不过,我抓住这个机会来测试一下,以下是我的观察:
根据文档,execute() 返回:
如果第一个结果对应于结果集,则返回 true,
如果它是更新计数或者除了通过 IN OUT 和 OUT 参数以外没有结果,则返回 false。
因此,我认为返回的 true 或 false 并不意味着执行成功与否。
再次强调,在调用 getOutputParameterValue
之前,我们必须注册参数。如果我们查看 getOutputParameterValue
的实现,我们就能找到 Hibernate 提供程序(在我这里是 JPA)调用实际执行的地方。
关于执行发生的次数,我以一种方式进行了测试,通过在调用过程中插入到另一个表中来检查它。
创建表 test_procedure_call(msg varchar2(100));
创建或替换过程 test (
p_in_1 IN NUMBER,
p_out_1 OUT VARCHAR2,
p_out_2 OUT VARCHAR2
) AS
BEGIN
insert into test_procedure_call values ('已执行..');
commit;
select 'FirstName' || ' ' || 'LastName', 'HR' into p_out_1, p_out_2
from dual
where p_in_1 = 1;
END;
/
@Test
public void testStoredProcedureQuery() {
StoredProcedureQuery sp = em.createStoredProcedureQuery("test");
// 设置参数
sp.registerStoredProcedureParameter("p_in_1", Integer.class, ParameterMode.IN);
sp.registerStoredProcedureParameter("p_out_1", String.class, ParameterMode.OUT);
sp.registerStoredProcedureParameter("p_out_2", String.class, ParameterMode.OUT);
sp.setParameter("p_in_1", 1);
String name = sp.getOutputParameterValue("p_out_1").toString();
String dept = sp.getOutputParameterValue("p_out_2").toString();
System.out.println("姓名: " + name);
System.out.println("部门: " + dept);
}
select * from test_procedure_call;
MSG
已执行..
通过表 test_procedure_call 的输出,我们可以确认它每次测试只执行一次(正如我们在上面的示例中看到的)。
英文:
I am a new learner in his field and as curious as you with the question. However i have taken this opportunity to test it and below is my observation,
According to the documentation execute() returns:
> Return true if the first result corresponds to a result set,
> and false if it is an update count or if there are no results
> other than through IN OUT and OUT parameters, if any.
therefore I would say, the returned true or false doesn't mean a successful or not execution.
Again we must register the parameters before we call to getOutputParameterValue
. If we looked into the implementation of getOutputParameterValue
, we would able to find exactly where the hibernate provider ( which is JPA in case of mine) calls to the actual execution.
Further to how many times the execution happened I tested it in a way to check it by inserting to another table inside the calling procedure.
create table test_procedure_call(msg varchar2(100));
CREATE OR REPLACE PROCEDURE test (
p_in_1 IN NUMBER,
p_out_1 OUT VARCHAR2,
p_out_2 OUT VARCHAR2
) AS
BEGIN
insert into test_procedure_call values ('Executed..');
commit;
select 'FirstName'||' '||'LastName','HR' into p_out_1,p_out_2
from dual
where p_in_1=1;
END;
/
@Test
public void testStoredProcedureQuery() {
StoredProcedureQuery sp = em.createStoredProcedureQuery("test");
// set parameters
sp.registerStoredProcedureParameter("p_in_1", Integer.class, ParameterMode.IN);
sp.registerStoredProcedureParameter("p_out_1", String.class, ParameterMode.OUT);
sp.registerStoredProcedureParameter("p_out_2", String.class, ParameterMode.OUT);
sp.setParameter("p_in_1", 1);
String name = sp.getOutputParameterValue("p_out_1").toString();
String dept = sp.getOutputParameterValue("p_out_2").toString();
System.out.println("Name : " + name);
System.out.println("Department : " + dept);
}
select * from test_procedure_call;
MSG
----------------------------------------------------------------------------------------
Executed..
By this out of the table test_procedure_call, we can confirm it executes only once per test. (as we saw in above example).
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论