在过程中在 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 过程具有以下显著的功能：

声明了 [未找到行] 异常。在过程的后期，此异常会在针对查询结果的循环结束时通知用户。

有关异常的详细信息，请参见过程和触发器中的错误和警告。
声明两个局部变量 ThisName 和 ThisValue，以保存来自该查询的每一行的结果。
声明了游标 ThisCompany。SELECT 语句会生成公司名称及公司所下订单总值的列表。
TopValue 的值被设置为初始值 0，以便以后在循环中使用。
ThisCompany 游标打开。
LOOP 语句对该查询的每一行执行循环，依次将每一公司名称及订单值放置在变量 ThisName 和 ThisValue 中。如果 ThisValue 大于当前的最高值，则将 TopCompany 和 TopValue 重置为 ThisName 和 ThisValue。
该游标在过程结束后关闭。
还可以通过在 SELECT 语句中添加 "ORDER BY value DESC" 子句来编写不含循环语句的此过程。那样就只需获取游标的第一行。

TopCompanyValue 过程中的 LOOP 构造是标准形式，在最后一行处理完毕后退出。您可以使用 FOR 循环，以更紧凑的形式重新编写此过程。FOR 语句将上述过程的若干方面合并到单个语句中。

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;