Rückgabe des Oracle ref Cursors und Anhängen mehrerer Ergebnisse

Question

Ich habe dieses Problem, das ich hoffe, jemand weiß die Antwort auf. Ich habe eine Oracle gespeicherte Prozedur, die eine Kunden-ID nimmt und alle Bestellungen des Kunden in einem ref_cursor zurückgibt. Vereinfacht gesagt, habe ich folgendes Problem:

Orders
- orderId
- siteID

Customers
- siteID
- Name

GetOrder(siteID, outCursor) /* returns all orders for a customer */

Jetzt muss ich eine andere Prozedur schreiben, die einen Kundennamen nimmt und tut eine LIKE-Abfrage, um alle custIds zu erhalten, dann muss ich die GetOrder-Methode wiederverwenden, um alle Aufträge für die custIds gefunden, etwas wie dieses zurückzugeben:

   PROCEDURE GetOrderbyCustName(
      p_name       IN        VARCHAR2,
      curReturn    OUT       sys_refcursor
   )
   IS
      siteid    number;
   BEGIN
      FOR rec in SELECT site_id FROM customers WHERE name LIKE p_name
      LOOP 
      -- This will replace curReturn in each iteration
      -- how do I append instead?
        GetOrder(rec.site_id,
                   curReturn
                  );
      END LOOP;
   END GetOrderbyCustName;

Meine Frage ist, wie ich die Rückkehr von GetOrder an curReturn in jeder Iteration anhängen? So wie es im Moment geschrieben ist, wird es in jedem Zyklus der Schleife überschrieben. Danke!!!

Answer 1

So geht es nicht - Cursor können nicht angehängt oder zusammengeführt werden. Machen Sie stattdessen einfach dies:

PROCEDURE GetOrderbyCustName(
   p_name       IN        VARCHAR2,
   curReturn    OUT       sys_refcursor
)
IS
BEGIN
   OPEN curReturn FOR 
      SELECT o.orderID, o.siteID
      FROM Orders o
      JOIN Customers c ON c.siteID = o.siteID
      WHERE c.name LIKE p_name;
END GetOrderbyCustName;

Answer 2

Wenn es sich um eine einfache Frage handelt, würde ich mich an Tonys Antwort halten. Dies ist nicht nur einfach, sondern wahrscheinlich auch besser als die Ausführung einer Abfrage für jede SiteID.

Wenn es ziemlich komplex ist, könnte es sich lohnen, die GetOrder-Prozedur wiederzuverwenden, damit Sie nur eine Abfrage pflegen müssen.

Dazu müssten Sie die Daten bei jeder Iteration der Schleife aus dem Refcursor abrufen und in eine andere Datenstruktur einfügen.

Eine Möglichkeit, wenn sie für die Schnittstelle sinnvoll ist, besteht darin, GetOrderbyCustName so zu ändern, dass es statt eines Refcursors eine PL/SQL-Index-by-Tabelle als Ausgabeparameter hat. Bei jeder Iteration durch die Schleife wird an diese Tabelle angehängt.

Wenn Sie wirklich einen Refcursor zurückgeben müssen, können Sie stattdessen einen verschachtelten Tabellentyp verwenden und dann einen Cursor zurückgeben, der diese verschachtelte Tabelle abfragt. Etwa so (nicht getesteter Code):

CREATE TYPE number_table_type AS TABLE OF NUMBER;

PROCEDURE GetOrderbyCustName(
      p_name       IN        VARCHAR2,
      curReturn    OUT       sys_refcursor
   )
   IS
      cursor_source_table  number_table_type := number_table_type();
      single_site_cursor  sys_refcursor;
      orderID  NUMBER;
   BEGIN
      FOR rec in SELECT site_id FROM customers WHERE name LIKE p_name
      LOOP 
      -- This will replace curReturn in each iteration
      -- how do I append instead?
        GetOrder(rec.site_id,
                   single_site_cursor
                  );

        -- Fetch all rows from the refcursor and append them to the nested table in memory
        LOOP
          FETCH single_site_cursor INTO orderID;
          EXIT WHEN single_site_cursor%NOTFOUND;
          cursor_source_table.extend();
          cursor_source_table( cursor_source_table.COUNT+1) := orderID;
        END LOOP;
      END LOOP;

      OPEN curReturn FOR
        SELECT * FROM TABLE( cursor_source_table );

   END GetOrderbyCustName;