Oracle：如何向引用光标填充/插入行？

Oracle: How to populate/insert row to a Ref Cursor?

提问by Jemru

Really need help regarding Ref Cursor. I have a Stored Procedure GET_PERSONROLES that have parameter type ref cursor. I just wanted to pupulate this ref cursor manually like inserting a row to the refcursor. Can I insert a row into a refcursor though a loop? Thank you in advance.

确实需要有关 Ref Cursor 的帮助。我有一个具有参数类型引用游标的存储过程 GET_PERSONROLES。我只是想手动 pupulate 这个 ref 游标，就像在 refcursor 中插入一行一样。我可以通过循环将一行插入到 refcursor 中吗？先感谢您。

The procedure depends on this publicly declared type:

该过程取决于此公开声明的类型：

create or replace package types 
as 
    type cursorTypePersonRole is ref cursor; 
end;

Here is my pseudo-codeL

这是我的伪代码L

create or replace PROCEDURE GET_PERSONROLES
( 
  P_CURSOR IN OUT types.cursorTypePersonRole
) AS
  REFCUR SYS_REFCURSOR; 
  TYPE REFTABLETYPE IS RECORD (
    IS_MANAGER_LEVEL1 VARCHAR2(1),
    IS_MANAGER_LEVEL2 VARCHAR2(1)
  );
  TYPE TABLETYPE IS TABLE OF REFTABLETYPE;
  PERSONROLES_TABLETYPE TABLETYPE; 
BEGIN
  --calls another stored proc to populate REFCUR with data without problem
  MY_STOREDPROC('12345', REFCUR);
  LOOP
    FETCH REFCUR BULK COLLECT INTO PERSONROLES_TABLETYPE;
    EXIT WHEN PERSONROLES_TABLETYPE.COUNT = 0;
    FOR indx IN 1 .. PERSONROLES_TABLETYPE.COUNT 
    LOOP
       -- I'm able to query perfectly the values of IS_MANAGER_LEVEL1 and IS_MANAGER_LEVEL 2
       -- I'm aware that the below codes are wrong
       -- However this means I wanted to insert these values to a row of the cursor if possible
       -- Do some logic to know what data will be assigned in the row.
       if PERSONROLES_TABLETYPE(indx).IS_MANAGER_LEVEL1 = 'Y' then
           P_CURSOR := <DO SOME LOGIC AND ASSIGN THE VALUE TO THE ROW>
       end if;
       if PERSONROLES_TABLETYPE(indx).IS_MANAGER_LEVEL2 = 'Y' then
           P_CURSOR := <DO SOME LOGIC AND ASSIGN THE VALUE TO THE ROW>
       end if;
    END LOOP;
  END LOOP;     
  CLOSE REFCUR;
END GET_PERSONROLES;

回答by APC

A ref cursor is not a variable: it is a pointer to a result set which is consumed by the act of reading it. The result set itself is immutable.

引用游标不是一个变量：它是一个指向结果集的指针，该结果集被读取它的行为所消耗。结果集本身是不可变的。

Immutability makes sense, because it reflects Oracle's emphasis on read consistency.

不变性是有道理的，因为它反映了 Oracle 对读取一致性的重视。

The simplest way to produce the output you appear to want is to create a SQL Type

产生您想要的输出的最简单方法是创建一个 SQL 类型

   open P_CURSOR for 
       select IS_MANAGER_LEVEL1,
              IS_MANAGER_LEVEL2 
       from table ( PERSONROLES_TABLETYPE );

This will work in 12c; in earlier versions to use the table()call like this you may need to declare REFTABLETYPEand TABLETYPEas SQL types( rather than in PL/SQL).

这将适用于 12c；在早期版本中，要使用这样的table()调用，您可能需要将REFTABLETYPE和声明TABLETYPE为 SQL 类型（而不是在 PL/SQL 中）。

---

---

"Ok edited it now"

“好的，现在编辑它”

Alas your requirements are still not clear. You haven't given us the structure of the output ref cursor or shown what other processing you want to undertake.

唉，你的要求还不清楚。您没有向我们提供输出引用游标的结构或显示您想要进行的其他处理。

However, given the title of your question, let's have a guess. So:

但是，鉴于您的问题的标题，让我们猜测一下。所以：

create or replace PROCEDURE GET_PERSONROLES ( P_CURSOR IN OUT types.cursorTypePersonRole) AS
  REFCUR SYS_REFCURSOR; 
  TYPE REFTABLETYPE IS RECORD (IS_MANAGER_LEVEL1 VARCHAR2(1),
    IS_MANAGER_LEVEL2 VARCHAR2(1));
  TYPE TABLETYPE IS TABLE OF REFTABLETYPE;
  PERSONROLES_TABLETYPE TABLETYPE; 
  personrole_rec PersonRole%rowtype;
  type personrole_nt is table of PersonRole%rowtype;
  personroles_recs personrole_nt := new personrole_nt() ; 
BEGIN
    MY_STOREDPROC('12345', REFCUR);
    FETCH REFCUR BULK COLLECT INTO PERSONROLES_TABLETYPE;
    FOR indx IN 1 .. PERSONROLES_TABLETYPE.COUNT LOOP
       /* in the absence of requirements I'm just making some stuff up */
       if PERSONROLES_TABLETYPE(indx).IS_MANAGER_LEVEL1 = 'Y' then
           personrole_rec.whatever1 := 'something';
       else
           personrole_recc.whatever1 := null;
       end if;
       if PERSONROLES_TABLETYPE(indx).IS_MANAGER_LEVEL2 = 'Y' then
           personrole_rec.whatever2 := 'something else';
       else
           personrole_recc.whatever2 := null;
       end if;
       if personrole_rec.whatever1 is not null 
       or personrole_rec.whatever2 is mot null then
              personroles_recs.exend();
              personroles_recs(personroles_recs.count()) := personroles_rec;
       end if;
  END LOOP;     
  CLOSE REFCUR;
  open p_cursor for 
      select * from table ( personroles_recs );
END GET_PERSONROLES;

This code uses a second collection to store the desired output. Like your code it reads the populated collection and evaluates the attributes of each row. If a value which means the criteria it sets an attribute in a rowtype variable. If one or both attributes are set it populates a new row in a second collection. At the end of the procedure it opens the ref cursor using a table()function call on the second collection.

此代码使用第二个集合来存储所需的输出。像您的代码一样，它读取填充的集合并评估每一行的属性。如果一个值表示标准，则它在 rowtype 变量中设置一个属性。如果设置了一个或两个属性，它会在第二个集合中填充新行。在过程结束时，它使用table()第二个集合上的函数调用打开引用游标。

Note that you do not need the nested loop: you're not using the LIMIT clause so your coder reads the entire cursor into the collection in one swoop.

请注意，您不需要嵌套循环：您没有使用 LIMIT 子句，因此您的编码器一口气将整个游标读入集合中。

The implemented rules may not be exactly what you want (because you haven't explained exactly what you want) but this should give you the general idea.

实施的规则可能并不完全是您想要的（因为您还没有确切地解释您想要什么），但这应该会给您一个大致的概念。

Note that, depending on exactly what processing is masked by <DO SOME LOGIC AND ASSIGN THE VALUE TO THE ROW>, the simpler approach could still be feasible:

请注意，根据 所掩盖的确切处理<DO SOME LOGIC AND ASSIGN THE VALUE TO THE ROW>，更简单的方法仍然可行：

open P_CURSOR for 
   select case when IS_MANAGER_LEVEL1 = 'Y' then 'YES' else 'NO' end,
          case when IS_MANAGER_LEVEL2 = 'Y' then 'YES' else 'NO' end
   from table ( PERSONROLES_TABLETYPE );