DBMaster_ZD_T7_2

CREATE TABLE DBMASTER.SALESAGGREGATED_MATERIALIZED (
  productname   VARCHAR2(20),
  qtysum        NUMBER(18,6),
  qtyavg        NUMBER(18,6),
  totalpricesum NUMBER(18,6),
  totalpriceavg NUMBER(18,6),
  ct            INTEGER);

CREATE INDEX DBMASTER.IU_SAM_PRODUCT ON DBMASTER.SALESAGGREGATED_MATERIALIZED (PRODUCTNAME);

-- Polecenie merge umożliwiające aktualizację tabeli ze statystyką
merge INTO dbmaster.salesaggregated_materialized sa
  USING (
    SELECT NVL(p.productname, sam.productname) AS productname, s.*, DECODE(p.productid, NULL, 'Y', 'N') AS to_delete
      FROM (SELECT od.productid, COUNT(*) AS ct,
        SUM(od.qty) AS qtysum, AVG(od.qty) AS qtyavg,
        SUM(od.totalprice) AS totalpricesum, AVG(od.totalprice) AS TOTALPRICEAVG
        FROM dbmaster.salesorderdetail od
        GROUP BY od.productid) s
        join dbmaster.product p ON p.productid = s.productId
        full outer join dbmaster.salesaggregated_materialized sam ON sam.productname = p.productname) v
  ON (sa.productname = v.productname)
  WHEN matched THEN UPDATE SET
    sa.ct = v.ct,
    sa.qtysum = v.qtysum,
    sa.qtyavg = v.qtyavg,
    sa.totalpricesum = v.totalpricesum,
    sa.totalpriceavg = v.totalpriceavg
    DELETE WHERE (v.to_delete = 'Y')
  WHEN NOT matched THEN INSERT (productname, ct, qtysum, qtyavg, totalpricesum, totalpriceavg)
    VALUES (v.productname, v.ct, v.qtysum, v.qtyavg, v.totalpricesum, v.totalpriceavg);

-- Powtórzenie polecenia merge w wyzwalaczu typu statement level
CREATE OR REPLACE TRIGGER dbmaster.tg_stats_update
  after INSERT OR UPDATE OR DELETE
  ON DBMASTER.SALESORDERDETAIL
BEGIN
  merge INTO dbmaster.salesaggregated_materialized sa
  USING (
    SELECT NVL(p.productname, sam.productname) AS productname, s.*, DECODE(p.productid, NULL, 'Y', 'N') AS to_delete
      FROM (SELECT od.productid, COUNT(*) AS ct,
        SUM(od.qty) AS qtysum, AVG(od.qty) AS qtyavg,
        SUM(od.totalprice) AS totalpricesum, AVG(od.totalprice) AS TOTALPRICEAVG
        FROM dbmaster.salesorderdetail od
        GROUP BY od.productid) s
        join dbmaster.product p ON p.productid = s.productId
        full outer join dbmaster.salesaggregated_materialized sam ON sam.productname = p.productname) v
  ON (sa.productname = v.productname)
  WHEN matched THEN UPDATE SET
    sa.ct = v.ct,
    sa.qtysum = v.qtysum,
    sa.qtyavg = v.qtyavg,
    sa.totalpricesum = v.totalpricesum,
    sa.totalpriceavg = v.totalpriceavg
    DELETE WHERE (v.to_delete = 'Y')
  WHEN NOT matched THEN INSERT (productname, ct, qtysum, qtyavg, totalpricesum, totalpriceavg)
    VALUES (v.productname, v.ct, v.qtysum, v.qtyavg, v.totalpricesum, v.totalpriceavg);
END tg_stats_update;