Cursor bei SELECT-Anweisungen in Prozeduren verwenden

Die folgende Prozedur benutzt einen Cursor für die SELECT-Anweisung. Sie basiert auf derselben Abfrage, die in der unter Ergebnismengen von Prozeduren zurückgeben beschriebenen ListCustomerValue-Prozedur verwendet wird, und illustriert mehrere Funktionen der Sprache von gespeicherten Prozeduren.

CREATE PROCEDURE TopCustomerValue(
      OUT TopCompany CHAR(36),
      OUT TopValue INT )
BEGIN
   -- 1. Declare the "row not found" exception
   DECLARE err_notfound
      EXCEPTION FOR SQLSTATE '02000';
   -- 2.  Declare variables to hold
   --    each company name and its value
   DECLARE ThisName CHAR(36);
   DECLARE ThisValue INT;
   -- 3.  Declare the cursor ThisCompany
   --     for the query
   DECLARE ThisCompany CURSOR FOR
   SELECT CompanyName,
         CAST( sum( SalesOrderItems.Quantity *
               Products.UnitPrice ) AS INTEGER )
         AS value
   FROM Customers
      INNER JOIN SalesOrders
      INNER JOIN SalesOrderItems
      INNER JOIN Products
   GROUP BY CompanyName; 
   -- 4. Initialize the values of TopValue
   SET TopValue = 0;
   -- 5. Open the cursor
   OPEN ThisCompany;
   -- 6. Loop over the rows of the query
   CompanyLoop:
   LOOP
      FETCH NEXT ThisCompany
         INTO ThisName, ThisValue;
      IF SQLSTATE = err_notfound THEN
         LEAVE CompanyLoop;
      END IF;
      IF ThisValue > TopValue THEN
         SET TopCompany = ThisName;
         SET TopValue = ThisValue;
      END IF;
   END LOOP CompanyLoop; 
   -- 7. Close the cursor
   CLOSE ThisCompany;
END;

Hinweise

Die Prozedur TopCustomerValue hat folgende bemerkenswerte Merkmale:

Die Ausnahmebedingung Zeile nicht gefunden wird deklariert. Diese Ausnahmebedingung signalisiert später in der Prozedur, dass eine Schleife über die Ergebnisse einer Abfrage abgeschlossen wurde.

Weitere Hinweise über das Verbinden finden Sie unter Fehler und Warnungen in Prozeduren und Triggern.
Zwei lokale Variable ThisName und ThisValue werden deklariert, um die Ergebnisse jeder Zeile der Abfrage aufzunehmen.
Der Cursor ThisCompany wird deklariert. Die SELECT-Anweisung erzeugt eine Liste der Firmennamen und den Gesamtwert der Bestellungen, die von diesen Unternehmen aufgegeben wurden.
Der Wert von TopValue wird auf einen Anfangswert von 0 gesetzt, der später in der Schleife verwendet wird.
Der Cursor ThisCompany wird geöffnet.
Die LOOP-Anweisung fragt in einer Schleife jede Zeile der Abfrage ab und setzt jeden Firmennamen in die Variablen ThisName und ThisValue. Wenn ThisValue größer als der aktuelle Topwert ist, werden TopCompany und TopValue auf ThisName und ThisValue zurückgesetzt.
Der Cursor wird am Ende der Prozedur geschlossen.
Sie können diese Prozedur auch ohne LOOP schreiben, indem Sie eine "ORDER BY Wert"-DESC-Klausel der SELECT-Anweisung hinzufügen. Dann muss nur die erste Zeile des Cursors abgefragt werden.

Das LOOP-Konstrukt in der Prozedur "TopCompanyValue" ist eine Standardform, die beendet wird, nachdem die letzte Zeile verarbeitet wurde. Sie können diese Prozedur in kompakterer Form neu programmieren, indem Sie eine FOR-Schleife verwenden. Die FOR-Anweisung kombiniert einige Aspekte der oben genannten Prozedur in einer einzigen Anweisung.

CREATE PROCEDURE TopCustomerValue2(
      OUT TopCompany CHAR(36),
      OUT TopValue INT )
BEGIN
   -- 1. Initialize the TopValue variable
   SET TopValue = 0;
   -- 2. Do the For Loop
   FOR CompanyFor AS ThisCompany
      CURSOR FOR
      SELECT CompanyName AS ThisName,
         CAST( sum( SalesOrderItems.Quantity *
               Products.UnitPrice ) AS INTEGER )
         AS ThisValue
      FROM Customers
         INNER JOIN SalesOrders
         INNER JOIN SalesOrderItems
         INNER JOIN Products
      GROUP BY ThisName
   DO
      IF ThisValue > TopValue THEN
         SET TopCompany = ThisName;
         SET TopValue = ThisValue;
      END IF;
   END FOR;
END;

Kommentieren Sie diese Seite in DocCommentXchange.