Sprachkurs SQL - User-Defined Functions

You must execute the code again to save those values in the database.

-- Ich verwende hier PostgeSQL
-- MySQL meckert beim UNIQUE-Constraint der email-Spalte, weil dafür ein Index angelegt werden muss, und VARCHAR(500) ist MySQL zu lang für Keys im Index.
-- Oracle kann kein ON UPDATE CASCADE

CREATE TABLE kunden (kundennummer INT PRIMARY KEY, name VARCHAR(100), email VARCHAR(500) UNIQUE, passwort CHAR(32), land VARCHAR(100), geworben_von INT REFERENCES kunden(kundennummer) ON DELETE SET NULL);
CREATE TABLE hersteller (firma VARCHAR(50) PRIMARY KEY, land VARCHAR(100));
CREATE TABLE produkte (produktnummer INT PRIMARY KEY, bezeichnung VARCHAR(100) NOT NULL, preis DECIMAL(9,2),
                       hersteller VARCHAR(50) REFERENCES hersteller(firma) ON UPDATE CASCADE);
CREATE TABLE bewertungen (kundennummer INT REFERENCES kunden(kundennummer),
                          produktnummer INT REFERENCES produkte(produktnummer),
                          sterne INT DEFAULT 5 CHECK(sterne BETWEEN 1 AND 5),
                          bewertungstext VARCHAR(100000),
                          PRIMARY KEY(kundennummer, produktnummer));
CREATE TABLE bewertungslikes (liker INT REFERENCES kunden(kundennummer),
                              kundennummer INT, produktnummer INT,
                             PRIMARY KEY(liker, kundennummer, produktnummer),
                             FOREIGN KEY(kundennummer, produktnummer) REFERENCES bewertungen(kundennummer, produktnummer));
CREATE TABLE bestellungen (bestellnummer INT PRIMARY KEY, kundennummer INT REFERENCES kunden(kundennummer), zeit TIMESTAMP, preis DECIMAL(9,2));

INSERT INTO kunden VALUES(4,'Ute', 'ute@example.com', NULL, 'Deutschland', NULL);                              
INSERT INTO kunden VALUES(5,'Peter', 'peter@example.com', NULL, 'Deutschland', 4);
INSERT INTO kunden VALUES(8,'Anna', 'anna@example.com', 5, 'Italien', 5);
INSERT INTO hersteller VALUES ('Calgonte', 'Italien');
INSERT INTO hersteller VALUES ('Monsterfood', 'USA');
INSERT INTO hersteller VALUES ('Holzkopf', 'Österreich');
INSERT INTO produkte VALUES(17, 'Schokoriegel', 0.89, 'Monsterfood');
INSERT INTO produkte VALUES(18, 'Müsliriegel', 1.19, 'Monsterfood');
INSERT INTO produkte VALUES(29, 'Spülmaschinentabs', 3.99, 'Calgonte');
INSERT INTO produkte VALUES(88, 'Katzenfutter', 4.99, NULL);
INSERT INTO produkte VALUES(91, 'Maschinenbau-Lehrbuch', 25.90, NULL);
INSERT INTO produkte VALUES(998, 'Geschenkverpackung', 0.00, NULL);
INSERT INTO produkte VALUES(999, 'Katalog', 0.00, NULL);
INSERT INTO bewertungen VALUES(5, 17, 4, 'Guter Schokoriegel, aber die Verpackung geht schwer auf');
INSERT INTO bewertungen VALUES(5, 29, 1, 'Mein Geschirr wird nicht sauber!');
INSERT INTO bewertungslikes VALUES(8,5,17);

INSERT INTO bestellungen VALUES(101, 5, '2018-05-26 20:31:00', 34.80);
INSERT INTO bestellungen VALUES(102, 8, '2018-05-26 20:31:01', 100);
INSERT INTO bestellungen VALUES(103, 8, '2018-05-27 8:15:00', 0.89);

-- Routinen
CREATE FUNCTION alles_leeren() RETURNS void AS
$$ BEGIN 
 TRUNCATE bewertungen CASCADE;
 TRUNCATE produkte CASCADE;
 TRUNCATE hersteller CASCADE;
 TRUNCATE kunden CASCADE;
END $$ LANGUAGE plpgsql;

CREATE FUNCTION preiserhoehung(pnr INT, pr DECIMAL(9,2)) RETURNS void AS
$$ BEGIN 
 UPDATE produkte SET preis = preis + $2 WHERE produktnummer = $1;
END $$ LANGUAGE plpgsql;

CREATE FUNCTION addieren(int, int) RETURNS INT AS 
$$ BEGIN RETURN $1+$2; END $$ LANGUAGE plpgsql;

CREATE FUNCTION produkte_von(VARCHAR) RETURNS TABLE(produktnummer INT, bezeichnung VARCHAR(100), preis DECIMAL(9,2), hersteller VARCHAR(50)) AS 
$$ SELECT produktnummer, bezeichnung, preis, hersteller FROM produkte WHERE hersteller = $1 $$ LANGUAGE sql;

--select alles_leeren();

--select * from bewertungen;
--select * from produkte;
--select * from hersteller;
--select * from kunden;

select * from produkte where produktnummer = 17;
select preiserhoehung(17, 0.10);
select * from produkte where produktnummer = 17;

select addieren(5, 10);

select produkte_von('Calgonte');

produktnummer	bezeichnung	preis	hersteller
17	Schokoriegel	0.89	Monsterfood

preiserhoehung

produktnummer	bezeichnung	preis	hersteller
17	Schokoriegel	0.99	Monsterfood

addieren
15

produkte_von
(29,Spülmaschinentabs,3.99,Calgonte)

CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION