Advertisement
ASiwon

DBMaster_ZD_T7_2

Apr 18th, 2020
791
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. CREATE TABLE DBMASTER.SALESAGGREGATED_MATERIALIZED (
  2.   productname   VARCHAR2(20),
  3.   qtysum        NUMBER(18,6),
  4.   qtyavg        NUMBER(18,6),
  5.   totalpricesum NUMBER(18,6),
  6.   totalpriceavg NUMBER(18,6),
  7.   ct            INTEGER);
  8.  
  9. CREATE INDEX DBMASTER.IU_SAM_PRODUCT ON DBMASTER.SALESAGGREGATED_MATERIALIZED (PRODUCTNAME);
  10.  
  11. -- Polecenie merge umożliwiające aktualizację tabeli ze statystyką
  12. merge INTO dbmaster.salesaggregated_materialized sa
  13.   USING (
  14.     SELECT NVL(p.productname, sam.productname) AS productname, s.*, DECODE(p.productid, NULL, 'Y', 'N') AS to_delete
  15.       FROM (SELECT od.productid, COUNT(*) AS ct,
  16.         SUM(od.qty) AS qtysum, AVG(od.qty) AS qtyavg,
  17.         SUM(od.totalprice) AS totalpricesum, AVG(od.totalprice) AS TOTALPRICEAVG
  18.         FROM dbmaster.salesorderdetail od
  19.         GROUP BY od.productid) s
  20.         join dbmaster.product p ON p.productid = s.productId
  21.         full outer join dbmaster.salesaggregated_materialized sam ON sam.productname = p.productname) v
  22.   ON (sa.productname = v.productname)
  23.   WHEN matched THEN UPDATE SET
  24.     sa.ct = v.ct,
  25.     sa.qtysum = v.qtysum,
  26.     sa.qtyavg = v.qtyavg,
  27.     sa.totalpricesum = v.totalpricesum,
  28.     sa.totalpriceavg = v.totalpriceavg
  29.     DELETE WHERE (v.to_delete = 'Y')
  30.   WHEN NOT matched THEN INSERT (productname, ct, qtysum, qtyavg, totalpricesum, totalpriceavg)
  31.     VALUES (v.productname, v.ct, v.qtysum, v.qtyavg, v.totalpricesum, v.totalpriceavg);
  32.  
  33. -- Powtórzenie polecenia merge w wyzwalaczu typu statement level
  34. CREATE OR REPLACE TRIGGER dbmaster.tg_stats_update
  35.   after INSERT OR UPDATE OR DELETE
  36.   ON DBMASTER.SALESORDERDETAIL
  37. BEGIN
  38.   merge INTO dbmaster.salesaggregated_materialized sa
  39.   USING (
  40.     SELECT NVL(p.productname, sam.productname) AS productname, s.*, DECODE(p.productid, NULL, 'Y', 'N') AS to_delete
  41.       FROM (SELECT od.productid, COUNT(*) AS ct,
  42.         SUM(od.qty) AS qtysum, AVG(od.qty) AS qtyavg,
  43.         SUM(od.totalprice) AS totalpricesum, AVG(od.totalprice) AS TOTALPRICEAVG
  44.         FROM dbmaster.salesorderdetail od
  45.         GROUP BY od.productid) s
  46.         join dbmaster.product p ON p.productid = s.productId
  47.         full outer join dbmaster.salesaggregated_materialized sam ON sam.productname = p.productname) v
  48.   ON (sa.productname = v.productname)
  49.   WHEN matched THEN UPDATE SET
  50.     sa.ct = v.ct,
  51.     sa.qtysum = v.qtysum,
  52.     sa.qtyavg = v.qtyavg,
  53.     sa.totalpricesum = v.totalpricesum,
  54.     sa.totalpriceavg = v.totalpriceavg
  55.     DELETE WHERE (v.to_delete = 'Y')
  56.   WHEN NOT matched THEN INSERT (productname, ct, qtysum, qtyavg, totalpricesum, totalpriceavg)
  57.     VALUES (v.productname, v.ct, v.qtysum, v.qtyavg, v.totalpricesum, v.totalpriceavg);
  58. END tg_stats_update;
Advertisement
RAW Paste Data Copied
Advertisement