解答例 - 実習課題２ - １３．３．ストアドプロシージャの利用

実習課題 2

販売データベースから、テーブル accept_order 中の受注に対して、支払い金額の平均より、支払い金額が多い受注に対して "Over"、少ない受注に対して "Under" を表示する関数を、PostgreSQL の PL/pgSQL 関数を用いて定義しなさい。また、定義した関数を用いて、受注番号と "Over" または "Under" を表示する SQL 文を記述しなさい。

• 支払い金額は 6章実習課題 2 を参考にすること。

解答例

/*
 * calc_payment(CHAR(4))
 *   引数:受注番号
 *   受注番号に対応する支払い金額を算出する。
 *   rank_payment()で利用される。
 */
CREATE FUNCTION calc_payment(CHAR(4))
RETURNS INTEGER
AS
'
  SELECT p.price * (100 - a.dc_rate) / 100 + a.option_price
    FROM accept_order a NATURAL JOIN product p
    WHERE a.o_num = $1;
'
LANGUAGE 'sql';

/*
 * rank_payment(CHAR(4))
 *   引数:受注番号
 *   受注番号に対応する支払い金額に応じて、'Over', 'Under'を返す。
 */
CREATE FUNCTION rank_payment(CHAR(4))
RETURNS CHAR(20)
AS
'
DECLARE
  order_num ALIAS FOR $1;
  average REAL;
  payment REAL;
BEGIN
  SELECT INTO average AVG(calc_payment(o_num)) FROM accept_order;
  SELECT INTO payment calc_payment(order_num);

  IF payment > average THEN
    RETURN ''Over'';
  ELSIF payment < average THEN
    RETURN ''Under'';
  ELSE
    RETURN '''';
  END IF;
END;
'
LANGUAGE 'plpgsql';

SELECT o_num AS 受注番号, rank_payment(o_num) AS 支払い金額 FROM accept_order;