プロシージャの SELECT 文でのカーソルの使用

次に、SELECT 文でカーソルを使用するプロシージャの例を示します。この例では、プロシージャから結果セットを返すで説明する ListCustomerValue プロシージャに使用するのと同じクエリをベースとして、ストアド・プロシージャ言語の特徴を示します。

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;

注意

この TopCustomerValue プロシージャには、次の特徴があります。

「ローが見つかりません。」という例外が宣言されます。この例外は、プロシージャの後でクエリの結果のループが完了するときに通知されます。

例外の詳細については、プロシージャとトリガでのエラーと警告を参照してください。
クエリの各ローの結果を入れる 2 つのローカル変数 ThisName と ThisValue が宣言されます。
カーソル ThisCompany が宣言されます。SELECT 文は会社名とその会社からの注文の合計額のリストを作成します。
ループで使うため、TopValue の初期値は 0 に設定されています。
ThisCompany カーソルが開きます。
LOOP 文はクエリの各ローをループして、各会社の名前を変数 ThisName と ThisValue に入れます。ThisValue が現在の最大値よりも大きければ、TopCompany と TopValue は ThisName と ThisValue の値にリセットされます。
プロシージャの最後にカーソルは閉じられます。
SELECT 文に ORDER BY 値 DESC 句を追加して、ループを使わずにこのプロシージャを作成することもできます。その場合、カーソルの最初のローのみをフェッチする必要があります。

この TopCompanyValue プロシージャでの LOOP 文は標準的な形式で、最後のローを処理して終了します。FOR ループを使うと、このプロシージャはさらに簡潔になります。FOR 文は 1 つの文に、上記のプロシージャのいくつかの要素を組み込みます。

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;