Datenbanken SoSe2016

Blatt 10

Blatt 8, Aufgabe 3

create or replace function nachbestellung_zusammenstellen() RETURNS int as $$
  declare curLager CURSOR FOR select a_nr, bestand from lagerbestand;
  declare artikelnummer varchar;
  declare bestand INT;
  declare preis numeric;

BEGIN
 delete from nachbestellen;
 OPEN curLager;
 LOOP  
   FETCH curLager into artikelnummer, bestand;
   EXIT when not found;
   RAISE NOTICE 'Artikel: % => Lagerbestand: %', artikelnummer, bestand;
   --neuesten Preis laden.
   preis := (select angebot.preis from angebot where angebot.a_nr = artikelnummer order by angebot.datum desc limit 1);
   RAISE NOTICE 'Preis: %', preis;
   if (not preis is null) then
     if (preis > 6) then
       if (bestand < 5) then
         RAISE NOTICE 'Nachbestellung (Preis > 6 Euro): %', (5 - bestand);
         insert into nachbestellen (a_nr, nachzubestellen) values (artikelnummer, 5 - bestand);
       end if;
     else
       if (bestand < 10) then
         RAISE NOTICE 'Nachbestellung (Preis <= 6 Euro): %', (10 - bestand);
         insert into nachbestellen (a_nr, nachzubestellen) values (artikelnummer, 10 - bestand);
       end if;     
     end if;
   end if;
 END LOOP;

 CLOSE curLager;
 RETURN 0;
end $$
LANGUAGE PLPGSQL;

create or replace function nachbestellung_zusammenstellen() RETURNS int as $$
  declare curLager CURSOR FOR select a_nr, bestand from lagerbestand;
  declare lagerzeile RECORD;
  declare preis numeric;

BEGIN
 delete from nachbestellen;
 OPEN curLager;
 LOOP  
   FETCH curLager into lagerzeile;
   EXIT when not found;
   RAISE NOTICE 'Artikel: % => Lagerbestand: %', lagerzeile.a_nr, lagerzeile.bestand;
   --neuesten Preis laden.
   preis := (select angebot.preis from angebot where angebot.a_nr = lagerzeile.a_nr order by angebot.datum desc limit 1);
   RAISE NOTICE 'Preis: %', preis;
   if (not preis is null) then
     if (preis > 6) then
       if (lagerzeile.bestand &lgt; 5) then
         RAISE NOTICE 'Nachbestellung (Preis > 6 Euro): %', (5 - lagerzeile.bestand);
         insert into nachbestellen (a_nr, nachzubestellen) values (lagerzeile.a_nr, 5 - lagerzeile.bestand);
       end if;
     else
       if (lagerzeile.bestand < 10) then
         RAISE NOTICE 'Nachbestellung (Preis <= 6 Euro): %', (10 - lagerzeile.bestand);
         insert into nachbestellen (a_nr, nachzubestellen) values (lagerzeile.a_nr, 10 - lagerzeile.bestand);
       end if;     
     end if;
   end if;
 END LOOP;

 CLOSE curLager;
 RETURN 0;
end $$
LANGUAGE PLPGSQL;

Zuweisungsoperator

Blatt 7

CREATE TABLE lagerbestand
(
  a_nr VARCHAR (13) NOT NULL,
  bestand INT NOT NULL,
  constraint lagerbestand_PK PRIMARY KEY (a_nr),
  constraint lagerbestand_FKa FOREIGN KEY (a_nr) REFERENCES medienartikel (a_nr) on delete cascade
);

--Aufgabe 7.2: Funktion verhindert das Überbuchen des Lagers bei INSERT oder UPDATE: Bestellmenge wird auf verfügbare Lagermenge reduziert.
CREATE OR REPLACE FUNCTION kontrolle() RETURNS TRIGGER AS $$
DECLARE
  lb INT ;
BEGIN
  SELECT bestand INTO lb FROM lagerbestand WHERE lagerbestand.a_nr = NEW.a_nr;
  RAISE NOTICE 'kontrolle: Lagerbestand alt = %, Bestellmenge: %', lb, NEW.Anzahl;
  if tg_op = 'INSERT' then 
    NEW.anzahl :=
      CASE WHEN (NEW.anzahl <= lb) THEN
        NEW.anzahl
      ELSE 
        lb
      END;
  else 
    lb:= lb + OLD.anzahl; 
    NEW.anzahl :=
      CASE WHEN (NEW.anzahl <= lb) THEN
        NEW.anzahl
      ELSE 
        lb 
      END;  
  END if;
  RAISE NOTICE 'kontrolle: neue Bestellmenge: %', NEW.Anzahl;
  RETURN NEW;
END$$
LANGUAGE PLPGSQL;

CREATE TRIGGER trg_bestellungsbegrenzer
  BEFORE UPDATE OR INSERT ON bestellposition
  FOR EACH ROW EXECUTE PROCEDURE kontrolle();


--Aufgabe 7.2: Funktion passt bei Eintragen/Ändern/Löschen einer Bestellposition den Lagerbestand an.
CREATE OR REPLACE FUNCTION updatelagerbestand() RETURNS TRIGGER AS $$
DECLARE
  lagerbestandOriginal INT ;
  lagerbestandNeu INT;
BEGIN
if (TG_OP = 'INSERT') then
  SELECT bestand INTO lagerbestandOriginal FROM lagerbestand WHERE lagerbestand.a_nr = NEW.a_nr;
  lagerbestandNeu := lagerbestandOriginal - NEW.anzahl;
  RAISE NOTICE 'INSERT Lagerbestand alt: %, neue Bestellmenge: %, Lagerbestand neu: %', lagerbestandOriginal, NEW.anzahl, lagerbestandNeu;
  UPDATE lagerbestand SET bestand = lagerbestandNeu WHERE lagerbestand.a_nr = NEW.a_nr;
elsif (TG_OP = 'UPDATE')  then
  SELECT bestand INTO lagerbestandOriginal FROM lagerbestand WHERE lagerbestand.a_nr = NEW.a_nr;
  --Man kommt doch beim Update mit einem einzigen Fall aus: auf vorherigen Lagerbestand wird alte Bestellmenge addiert, davon wird die neue Bestellmenge abgezogen.
  lagerbestandNeu := lagerbestandOriginal + OLD.anzahl - NEW.anzahl;
  RAISE NOTICE 'UPDATE Lagerbestand alt: %, vorherige Bestellmenge: %, neue Bestellmenge: %, Lagerbestand neu: %', lagerbestandOriginal, OLD.anzahl, NEW.anzahl, lagerbestandNeu;
  UPDATE lagerbestand SET bestand = lagerbestandNeu WHERE lagerbestand.a_nr = NEW.a_nr;
elsif (TG_OP = 'DELETE') then
   SELECT bestand INTO lagerbestandOriginal FROM lagerbestand WHERE lagerbestand.a_nr = OLD.a_nr;
   lagerbestandNeu := lagerbestandOriginal + OLD.anzahl;
   RAISE NOTICE 'delete Lagerbestand alt: %, gelöschte Bestellmenge: %, Lagerbestand neu: %', lagerbestandOriginal, OLD.anzahl, lagerbestandNeu;
   UPDATE lagerbestand SET bestand = lagerbestandNeu WHERE lagerbestand.a_nr = OLD.a_nr;
end if;

RETURN NEW;
END$$
LANGUAGE PLPGSQL;

CREATE TRIGGER trg_updatelagerbestand
  AFTER INSERT or update or delete ON bestellposition
  FOR EACH ROW EXECUTE PROCEDURE updatelagerbestand();


--*********************************************************************************
--Testfall 1:
DELETE FROM bestellposition WHERE a_nr='0017-V' ;
update lagerbestand set bestand = 50 where a_nr = '0017-V';

--Eine Überbuchung einfügen: wird automatisch auf 50 abgeschnitten.
INSERT INTO bestellposition VALUES ('0017-V', 'BE-0010', '101', '14.90');

select * from bestellposition where a_nr = '0017-V';
select * from lagerbestand where a_nr = '0017-V';

--Buchung löschen:
DELETE FROM bestellposition WHERE a_nr='0017-V' ;

select * from bestellposition where a_nr = '0017-V';
select * from lagerbestand where a_nr = '0017-V';

--*********************************************************************************
--Testfall 2:
DELETE FROM bestellposition WHERE a_nr='0017-V' ;
update lagerbestand set bestand = 50 where a_nr = '0017-V';

--Eine normale Buchung einfügen:
INSERT INTO bestellposition VALUES ('0017-V', 'BE-0010', '10', '14.90');


select * from bestellposition where a_nr = '0017-V';  --10
select * from lagerbestand where a_nr = '0017-V';  --40

--Weitere 10 Artikel buchen:
update bestellposition set anzahl = 20 where a_nr = '0017-V' and be_nr = 'BE-0010';

select * from bestellposition where a_nr = '0017-V';  --20
select * from lagerbestand where a_nr = '0017-V';  --30

--Fünf Artikel zurücknehmen:
update bestellposition set anzahl = 15 where a_nr = '0017-V' and be_nr = 'BE-0010';

select * from bestellposition where a_nr = '0017-V';  --15
select * from lagerbestand where a_nr = '0017-V';  --35

--Und zuviel aufbuchen:
update bestellposition set anzahl = 55 where a_nr = '0017-V' and be_nr = 'BE-0010';

select * from bestellposition where a_nr = '0017-V';  --50
select * from lagerbestand where a_nr = '0017-V';  --0

--Wieder etwas davon wegnehmen:
update bestellposition set anzahl = 45 where a_nr = '0017-V' and be_nr = 'BE-0010';

select * from bestellposition where a_nr = '0017-V';  --45
select * from lagerbestand where a_nr = '0017-V';  --5

--Buchung löschen:
DELETE FROM bestellposition WHERE a_nr='0017-V' ;

select * from bestellposition where a_nr = '0017-V';  --kein treffer
select * from lagerbestand where a_nr = '0017-V'; --50


drop trigger trg_updatelagerbestand on bestellposition;
drop function updatelagerbestand();

drop trigger trg_bestellungsbegrenzer on bestellposition;
drop function kontrolle();