CREATE OR REPLACE PROCEDURE ADD_PRICE
  (produktnr product.prodid%type, standardpreis price.stdprice%type,
   mindestpreis price.minprice%type)
AS
    PRODID_NOT_FOUND EXCEPTION;
    anz_produkte INTEGER;
BEGIN
    SELECT COUNT(p.prodid) INTO anz_produkte FROM product p
    WHERE p.prodid=produktnr;
    IF anz_produkte=0 THEN raise PRODID_NOT_FOUND;
    END IF;
    UPDATE price SET enddate = SYSDATE-1 WHERE
    startdate = (SELECT MAX(startdate) FROM
                    (SELECT * FROM price p WHERE p.prodid=produktnr))
    AND prodid = produktnr;
    INSERT INTO price(prodid, stdprice, minprice, startdate)
    VALUES(produktnr, standardpreis, mindestpreis, SYSDATE);
EXCEPTION
    WHEN PRODID_NOT_FOUND THEN
        INSERT INTO errors
        VALUES('Ungueltige Produktnummer.', produktnr, -1);
END ADD_PRICE;
/
