oracle Oracle中“IN”和“IN OUT”CURSOR参数的区别

Difference between "IN" and "IN OUT" CURSOR parameter in Oracle

提问by Frajmen

From Oracle: "When you declare a cursor variable as the formal parameter of a subprogram that fetches from the cursor variable, you must specify the IN or IN OUT mode. If the subprogram also opens the cursor variable, you must specify the IN OUT mode."

来自Oracle：“当您将游标变量声明为从游标变量中获取的子程序的形参时，必须指定 IN 或 IN OUT 模式。如果子程序也打开游标变量，则必须指定 IN OUT 模式.”

But, I can code that (only OUT parameter):

但是，我可以编码（仅 OUT 参数）：

create or replace procedure mycur_out(mc OUT mycurpkg.mytypecur)  as 
begin
    open mc for select * from mytable; 
end mycur_out;

and works equal to (IN OUT parameter)

并且工作等于（IN OUT 参数）

create or replace procedure mycur_inout(mc IN OUT mycurpkg.mytypecur) 
as
begin
    open mc for select * from table10;
end mycur_inout;

Also, It's work fine with dynamic cursor too:

此外，它也适用于动态游标：

create or replace procedure mycur_out_ref(mc out mycurpkg.mytyperefcur) 
as
begin
    open mc for 'select * from table10';
end mycur_out_ref;

I've tested the 3 cases directly from oracle and from VB6 with ADO, and no problems. So, in that cases, is there any difference between INusing just "OUT" and "IN OUT" cursors parameters?

我已经使用 ADO 直接从 oracle 和 VB6 测试了 3 个案例，没有问题。所以，在这情况下，是没有任何区别在仅仅使用“OUT”和“OUT”游标参数？

UPDATE The reason I'm asking:

更新我问的原因：

• We read data using routines similar to the examples (just open the cursors). The cursor parameters always are "IN OUT" (Don't ask me why, I'm trying to figure out)
• The routines are invoked with ADO/VB6
• Now, we are trying to use some of the routines from JDBC, but the adapter apparently just accepts OUT parameters in this cases.
• Finally, the main reason, I want to change the cursor parameters on DB routines to only OUT, but first I want to know the collaterals effects of that change.

• 我们使用类似于示例的例程读取数据（只需打开游标）。游标参数总是“IN OUT”（别问我为什么，我在想办法）
• 使用 ADO/VB6 调用例程
• 现在，我们正在尝试使用 JDBC 中的一些例程，但在这种情况下，适配器显然只接受 OUT 参数。
• 最后，主要原因，我想将 DB 例程上的游标参数更改为仅 OUT，但首先我想知道该更改的附带影响。

Thanks!

谢谢！

采纳答案by Dave Costa

In the text you quote from the manual, note that it is specifically talking about "a subprogram that fetches from the cursor variable". None of your examples do this, so the quote is not relevant to them.

在您从手册中引用的文本中，请注意它专门讨论“从游标变量中获取的子程序”。您的示例都没有这样做，因此引用与它们无关。

However, it nonetheless appears that there's nothing wrong with using OUTonly in such a situation, if the subprogram both opens and fetches from the cursor variable:

但是，OUT如果子程序同时打开并从游标变量中获取，那么仅在这种情况下使用似乎没有错：

SQL> variable c refcursor

SQL> set serveroutput on
SQL> create or replace procedure no_good (c  OUT sys_refcursor)
  2     as
  3        my_dummy  dual.dummy%type;
  4     begin
  5       open c for select dummy from dual union all select dummy from dual;
  6       fetch  c into my_dummy;
  7       dbms_output.put_line( my_dummy );
  8     end;
  9  /

Procedure created.

SQL> exec no_good( :c )
X

PL/SQL procedure successfully completed.

SQL> print c

D
-
X

I think the the text is actually trying to make two points that are somewhat independent of each other. Firstly, if you want to pass any already-opened cursor variable into a subprogram, which will fetch from it, the parameter must be declare INor IN OUT. Secondly, if you want to pass a cursor variable into a subprogram, which will then open it, the parameter must be declared OUTor IN OUT. This is true regardless of whether you actually care about passing the value of the cursor variable back to the caller:

我认为文本实际上试图提出两个相互独立的观点。首先，如果要将任何已打开的游标变量传递到子程序中，该子程序将从中获取，则参数必须是 declare INor IN OUT。其次，如果要将游标变量传递到子程序中，然后子程序将打开它，则必须声明参数OUT或IN OUT。无论您是否真正关心将游标变量的值传递回调用者，都是如此：

SQL> create or replace procedure no_good (c  IN sys_refcursor)
  2     as
  3        my_dummy  dual.dummy%type;
  4     begin
  5       open c for select dummy from dual;
  6       fetch  c into my_dummy;
  7       dbms_output.put_line( my_dummy );
  8       close c;
  9     end;
 10  /

Warning: Procedure created with compilation errors.

SQL> show error
Errors for PROCEDURE NO_GOOD:

LINE/COL ERROR
-------- -----------------------------------------------------------------
5/6      PL/SQL: SQL Statement ignored
5/11     PLS-00361: IN cursor 'C' cannot be OPEN'ed

This error can be fixed by changing the parameter mode, but actually it would seem to make more sense to simply make the cursor variable a local variable rather than a parameter.

这个错误可以通过改变参数模式来修复，但实际上，简单地将游标变量设置为局部变量而不是参数似乎更有意义。

回答by JulesLt

If I understand the question right, the difference is that with the IN OUT version you can pass in a cursor from outside the procedure, and then change that variable (similar to the difference between OUT and IN OUT for a simple numeric variable).

如果我理解正确，区别在于 IN OUT 版本可以从过程外部传入游标，然后更改该变量（类似于简单数字变量的 OUT 和 IN OUT 之间的区别）。

The OUT parameter cursor starts out as a NULL value / closed cursor.

OUT 参数游标开始时为 NULL 值/关闭游标。

The IN OUT parameter version starts with whatever state is passed in from outside.

IN OUT 参数版本以从外部传入的任何状态开始。

You may want to retry your procedure calls repeatedly passing in the same cursor variable - the OUT version should replace the existing value, the IN OUT version should give an exception on the second time round that you are trying to open an open cursor.

您可能想重试重复传入同一游标变量的过程调用 - OUT 版本应替换现有值，IN OUT 版本应在您尝试打开打开的游标的第二轮时给出异常。

Another thing the IN OUT approach allows, that the OUT approach does not, is to take action based on the passed in cursor, and change the returned cursor.

IN OUT 方法允许但 OUT 方法不允许的另一件事是根据传入的游标采取行动，并更改返回的游标。

PROCEDURE lp_test2 (mc IN OUT mycurpkg.mytypecur)
IS
  lr table10%ROWTYPE;
BEGIN 
   IF mc%ISOPEN THEN 
      FETCH mc INTO lr;
      IF mc%NOTFOUND THEN
         CLOSE mc;
         /* Switch cursor to alternative table */
         open mc for select * from schema2.table10;
      END IF;
   END IF;
END lp_test2;

I am just struggling to think of a real situation where you might want to (take in a cursor variable, cast it back into a SQL statement, append some extra dynamic SQL, and return the whole lot back as the same cursor??).

我只是在努力思考您可能想要的真实情况（接收游标变量，将其转换回 SQL 语句，附加一些额外的动态 SQL，然后将全部返回作为同一个游标？？）。