Oracle 11g: Verwendung von Cursoren innerhalb einer Prozedur

Question

Ich versuche, zwei Ref-Cursor aus einer Prozedur zurückzugeben und habe ein paar Probleme. Was ich versuche zu tun ist, greifen die Informationen aus dem ersten Cursor, wählen Sie ein paar Felder aus und verknüpfen Sie es mit einigen anderen Informationen und stecken Sie das Ergebnis in eine Tabellenvariable... dann wählen Sie verschiedene Elemente aus dieser Tabelle in den zweiten Cursor. Aber ich kann das nicht kompilieren. Kann mir bitte jemand sagen, was ich falsch mache?

type T_CURSOR is REF CURSOR

procedure FetchSL3Details_PRC
(
c_items out T_CURSOR,
c_identifiers out T_CURSOR,
p_niin in char
) as
v_idents IDENTIFIER_TABLE_TYPE:= IDENTIFIER_TABLE_TYPE();
BEGIN

open c_items for
  select
    its.item_set_id,
    its.niin,
    its.parent_niin,
    its.commodity_id,
    its.service_type,
    its.sl3_type,
    its.qty,
    its.created_id,
    its.created_dt,
    its.modified_id,
    its.modified_dt
  from
    item_set its
  start with its.niin = p_niin
  connect by prior its.niin = its.parent_niin;

  for item in c_items loop
    v_idents.extend;
    v_idents(v_idents.LAST) := identifier_row_type(item.commodity_id,
                                                      get_group_name_fun(item.commodity_id),
                                                      0);
    v_idents.extend;
    v_idents(v_idents.LAST) := identifier_row_type(item.created_id,
                                                      get_formatted_name_fun(item.created_id),
                                                      0);
    v_idents.extend;
    v_idents(v_idents.LAST) := identifier_row_type(item.modified_id,
                                                      get_formatted_name_fun(item.modified_id),
                                                      0);
  end loop;

  open c_identifiers for
    select 
      distinct(v.id),
      v.name,
      v.type
    from
      v_idents v;

END FetchSL3Details_PRC;

Answer 1

Sie können dieses Konstrukt nicht verwenden:

for item in c_items loop

mit einem REF CURSOR. Er erwartet c_items ein Standard-PL/SQL zu sein CURSOR . Das ist die unmittelbare Ursache für den Fehler, den Sie erhalten. Wenn Sie eine Schleife über einen REF CURSOR ziehen wollen, müssen Sie, soweit ich weiß, explizite FETCH-Anweisungen verwenden und die Schleifenbedingung selbst behandeln.

Außerdem macht das, was Sie sagen, dass Sie es versuchen, nicht wirklich Sinn. Wenn Sie von der c_items Cursor innerhalb des Prozedurenkörpers zu verwenden, ist es verwirrend, ihn auch an den Aufrufer zurückzugeben. In Ihrem Kommentar verwenden Sie die Formulierung "select into the cursor", was darauf hindeutet, dass Sie sich den Cursor als statische Sammlung vorstellen, über die Sie wiederholt iterieren können. Das ist nicht der Fall - ein Cursor stellt eine aktive Abfrage im Speicher dar. Sobald eine Zeile aus dem Cursor abgerufen wurde, kann sie nicht erneut abgerufen werden.

Ich bin nicht sicher, was ich genau vorschlagen kann, da ich das Ziel des Codes nicht verstehe. Wenn Sie wirklich sowohl die Zeilen von c_items und ihn als brauchbaren REF CURSOR zurückgeben, dann kann die einzige Möglichkeit darin bestehen, ihn zu schließen und erneut zu öffnen.

Answer 2

Ändern Sie dies:

open c_identifiers for
    select 
      distinct(v.id),
      v.name,
      v.type
    from
      v_idents v;

zu:

open c_identifiers for
    select 
      distinct(v.id),
      v.name,
      v.type
    from
      TABLE(v_idents) v; -- Use TABLE