调用时如何访问Oracle过程的OUT参数? [英] how to access an Oracle procedure's OUT parameter when calling it?

问题描述

如果我编写一个简单的函数doSomething，则可以通过执行:

来获得其结果.

select doSomething() from dual;

但是，如果我希望调用一个传递了OUT游标的过程(以及另一个int参数)，那么如何在查询中调用该过程并访问游标的结果?

在查询中调用它不是强制性的.只是我想访问该过程的结果

解决方案

您可以创建类似

的过程

    CREATE OR REPLACE PROCEDURE your_procedure(out_cursor OUT sys_refcursor)
    IS
    BEGIN
        OPEN out_cursor FOR
            SELECT  employee_name
              FROM  employees;

    END;
    /

创建过程后，将过程包装在一个函数中，该函数将返回如下游标

CREATE OR REPLACE FUNCTION your_function
    RETURN sys_refcursor
AS
    o_param  sys_refcursor;
BEGIN
    o_param := NULL;
    your_procedure(o_param);
    RETURN o_param;
EXCEPTION
    WHEN NO_DATA_FOUND
    THEN
        -- raise                                  

    WHEN OTHERS
    THEN
        -- raise
END your_function;
/

要查看sql的结果，请执行以下操作

select your_function from dual;

更新1

要在SQL Developer中查看结果

第1步

双击您在SQL Developer中的结果

[结果] [1]

第2步单击带点的按钮.将会弹出值

[网格] [2]

If I write a simple function doSomething, I can get its result by executing :

select doSomething() from dual;

But, if I wish to call a procedure that has an OUT cursor being passed to it (along with another int parameter), how do I call that procedure inside a query and access the result of the cursor ?

Calling it inside a query is not compulsory.. its just that I want to access the results of that procedure

解决方案

You can create a procedure like

    CREATE OR REPLACE PROCEDURE your_procedure(out_cursor OUT sys_refcursor)
    IS
    BEGIN
        OPEN out_cursor FOR
            SELECT  employee_name
              FROM  employees;

    END;
    /

Once you create your procedure wrap the procedure in a function which returns a cursor like the following

CREATE OR REPLACE FUNCTION your_function
    RETURN sys_refcursor
AS
    o_param  sys_refcursor;
BEGIN
    o_param := NULL;
    your_procedure(o_param);
    RETURN o_param;
EXCEPTION
    WHEN NO_DATA_FOUND
    THEN
        -- raise                                  

    WHEN OTHERS
    THEN
        -- raise
END your_function;
/

To see the results from sql do as

select your_function from dual;

Update 1

To see result in SQL Developer

Step 1

Double click on your results in SQL Developer

[Results][1]

Step 2 Single Click on the button with dots. That will pop up the values

[Grid][2]

这篇关于调用时如何访问Oracle过程的OUT参数?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！