从存储过程中获取输出参数而不调用execute()。

huangapple go评论87阅读模式
英文:

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).

huangapple
  • 本文由 发表于 2020年8月1日 18:13:00
  • 转载请务必保留本文链接:https://go.coder-hub.com/63204077.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定