プログラム変数を参照するビュー

プログラム変数への参照を含むビューを作成すると便利な場合があります。たとえば、特定の顧客を識別する変数をプロシージャー内に定義するとします。その顧客の購入履歴を問い合わせる場合、同様の情報に何度もアクセスしたり、複数の集合関数を使用する必要が出じるのであれば、その特定の顧客に関する情報を含むビューを作成すると便利です。

ビューのスコープをプロシージャーのスコープに制限する方法はないため、プログラム変数を参照するビューを作成することはできません。ビューは、一度作成すると、このような目的以外にも使用できます。しかし、プロシージャーのクエリ内で共通テーブル式を使用することもできます。共通テーブル式のスコープはその文に制限されるため、変数を参照してもあいまい性はなく、変数の参照は許可されます。

次の文は、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 で意見交換できます