从存储过程获取输出参数，而无需调用execute（） [英] Get Output parameter from stored procedure without calling execute()

问题描述

我想通过实体管理器从Java程序中调用PL / SQL存储过程：

I want to call a PL/SQL stored procedure from within a Java program via an entity manager:

StoredProcedureQuery storedProcedureQuery = entityManager.createStoredProcedureQuery("someProcedure");

现在，我的理解是我必须在此storageProcedureQuery上调用execute（）才能执行过程并能够检索OUT参数。但是，我可以无需检索这些值，而只需调用

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

这怎么可能？此代码段何时确切执行存储过程？调用getOutputParameterValue（）时，是否会每次执行该过程？有任何官方文件证明这种行为吗？

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 ?

推荐答案

我是该领域的新手，对这个问题像您一样好奇。但是我利用这次机会进行了测试，下面是我的观察

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.

因此，我会说，返回的true或false并不表示成功或不执行。

therefore I would say, the returned true or false doesn't mean a successful or not execution.

同样，我们必须在调用 getOutputParameterValue 之前注册参数。如果我们研究 getOutputParameterValue 的实现，我们将能够准确找到休眠提供程序（在我的情况下为JPA）调用实际执行的位置。

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

通过表test_procedure_call的调用，我们可以确认其每次测试仅执行一次。 （如上例所示）。

By this out of the table test_procedure_call, we can confirm it executes only once per test. (as we saw in above example).

这篇关于从存储过程获取输出参数，而无需调用execute（）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！