引用程序变量的视图

有时，对于创建包含到程序变量的引用的视图，使用公用表表达式可能会很方便。例如，您可以定义过程中标识特定客户的变量。您要查询该客户的购买历史记录，并且如果您要多次访问类似信息或者可能会使用多个集合函数，则您需要创建一个包含有关该特定客户信息的视图。

您无法创建引用程序变量的视图，因为无法将视图范围限制为您的过程的范围。一旦创建了视图，就可以在其它环境中使用该视图。但是，您可以在您的过程中的查询内使用公用表表达式。因为公用表表达式的范围限制到语句，所以变量引用不会造成任何多义性，因此可以使用变量引用。

以下语句用于在 SQL Anywhere 示例数据库中选择各位不同销售代表的销售总额。

SELECT GivenName || ' ' || Surname AS sales_rep_name,
       SalesRepresentative AS sales_rep_id,
       SUM( p.UnitPrice * i.Quantity ) AS total_sales
FROM Employees LEFT OUTER JOIN SalesOrders AS o
              INNER JOIN SalesOrderItems AS I
              INNER JOIN Products AS p
WHERE OrderDate BETWEEN '2000-01-01' AND '2001-12-31'
GROUP BY SalesRepresentative, GivenName, Surname;

上面的查询是出现在以下过程中的公用表表达式的基础。销售代表的 ID 号和所讨论的年份是即将使用的参数。如同以下过程所演示的那样，可在 WITH 子句中引用过程参数和任何已声明的局部变量。

CREATE PROCEDURE sales_rep_total (
  IN rep  INTEGER,
  IN yyyy INTEGER )
BEGIN
  DECLARE StartDate DATE;
  DECLARE EndDate   DATE;
  SET StartDate = YMD( yyyy,  1,  1 );
  SET EndDate = YMD( yyyy, 12, 31 );
  WITH total_sales_by_rep ( sales_rep_name,
                            sales_rep_id,
                            month,
                            order_year,
                            total_sales ) AS
  ( SELECT GivenName || ' ' || Surname AS sales_rep_name,
           SalesRepresentative AS sales_rep_id, 
           month( OrderDate),
           year( OrderDate ),
           SUM( p.UnitPrice * i.Quantity ) AS total_sales
    FROM Employees LEFT OUTER JOIN SalesOrders o
                       INNER JOIN SalesOrderItems I
                       INNER JOIN Products p
    WHERE OrderDate BETWEEN StartDate AND EndDate
           AND SalesRepresentative = rep
    GROUP BY year( OrderDate ), month( OrderDate ),
             GivenName, Surname, SalesRepresentative )
  SELECT sales_rep_name,
         monthname( YMD(yyyy, month, 1) ) AS month_name,
         order_year,
         total_sales
  FROM total_sales_by_rep
  WHERE total_sales =
    ( SELECT MAX( total_sales) FROM total_sales_by_rep )
  ORDER BY order_year ASC, month ASC;
END;

以下语句调用上述过程。

CALL sales_rep_total( 129, 2000 );

使用DocCommentXchange讨论此页。