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

実習課題 3

販売データベースのテーブル accept_order 中のデータで、支払い金額の平均値、最大値、最小値を別のテーブルに書き込むストアドプロシージャを定義しなさい。

• このストアドプロシージャを実行したときに書き込まれるデータは、通し番号、支払い金額の平均値、最大値、最小値及びテーブルにデータを書き込んだ日である。
• 支払い金額は 6章実習課題 2 を参考にすること。
• このストアドプロシージャの定義は ORACLE 上で行うものとする。

解答例

CREATE SEQUENCE summary_seq;

CREATE TABLE order_summary
(
   num           NUMBER PRIMARY KEY,
   average       NUMBER,
   max           NUMBER,
   min           NUMBER,
   summary_date  DATE
);

CREATE PROCEDURE log_summary
IS
  TYPE summary_t is RECORD (average NUMBER, max NUMBER, min NUMBER);
  summary summary_t;
BEGIN
  SELECT AVG(p.price * (100 - a.dc_rate) / 100 + a.option_price),
         MAX(p.price * (100 - a.dc_rate) / 100 + a.option_price),
         MIN(p.price * (100 - a.dc_rate) / 100 + a.option_price)
    INTO summary
    FROM accept_order a NATURAL JOIN product p;
  INSERT INTO order_summary VALUES (
    summary_seq.nextval, summary.average, summary.max, summary.min, CURRENT_DATE);
END;