Musterlösung - Informationssysteme

Uni Duisburg-Essen
Fachgebiet Informationssysteme
Prof. Dr. N. Fuhr
Praktikum Datenbanken / DB2
Woche 7: Noch mehr SQL (Fortgeschrittene Konzepte)
Musterlösung
(a) Schreibt zwei UDFs vorname() und nachname(), die zu Zeichenketten
(Strings) des beschriebenen Formats, die Vor- bzw. Nachnamen zurückliefern. Ist der Eingabeparameter nicht in kompatiblem Format, soll vorname()
den leeren String und nachname() die komplette Zeichenkette zurückliefern.
CREATE FUNCTION vorname (name VARCHAR(75))
RETURNS VARCHAR(75)
LANGUAGE SQL
CONTAINS SQL
DETERMINISTIC
BEGIN ATOMIC
DECLARE vor VARCHAR(75);
IF locate(’,’,name)=0 THEN SET vor=’’;
ELSE SET vor=substr(name,locate(’,’,name)+2);
END IF;
RETURN vor;
END @
CREATE FUNCTION nachname (name VARCHAR(75))
RETURNS VARCHAR(75)
LANGUAGE SQL
CONTAINS SQL
DETERMINISTIC
BEGIN ATOMIC
DECLARE nach VARCHAR(75);
IF locate(’,’,name)=0 THEN SET nach=name;
ELSE SET vor=substr(name,1,locate(’,’,name)-1);
END IF;
RETURN nach;
END @
VALUES (vorname(’Bloom, Orlando’))
’Orlando’
VALUES (vorname(’Orlando Bloom’))
’’
VALUES (nachname(’Bloom, Orlando’))
’Bloom’
VALUES (nachname(’Orlando Bloom’))
’Orlando Bloom’
(b) Budgets sind in der Filmdatenbank als Zeichenkette gespeichert, die mit
dem dreistelligen Währungscode beginnt und bei der Dreierblöcke von
Praktikum Datenbanken / DB2
Woche 7: Noch mehr SQL (Fortgeschrittene Konzepte)
Seite 1 von 4
Uni Duisburg-Essen
Fachgebiet Informationssysteme
Prof. Dr. N. Fuhr
Ziffern durch Kommata getrennt werden, z.B. ’DEM 1,000,000’. Um die
Budgets vergleichbar zu machen, schreibt eine UDF, welche einen solchen
String in Euro umrechnet und als Integer zurückgibt. Es soll reichen, die
Umrechnung für drei Währungen zu machen. In anderen Fällen soll die
Funktion eine Fehlermeldung geben.
CREATE FUNCTION euro (budget VARCHAR(75))
RETURNS INT
LANGUAGE SQL
CONTAINS SQL
DETERMINISTIC
BEGIN ATOMIC
DECLARE zahl INTEGER;
DECLARE waehrung CHARACTER(3);
DECLARE eurobudget INTEGER;
SET zahl=decimal(replace(right(budget,length(budget)-3),’,’,’’));
SET waehrung=left(budget,3);
SET eurobudget = (
CASE waehrung
WHEN ’ATS’ THEN zahl/13.7603
WHEN ’BEF’ THEN zahl/40.3399
WHEN ’CHF’ THEN zahl/1.5530
WHEN ’CZK’ THEN zahl/31.7537
WHEN ’DEM’ THEN zahl/1.95583
WHEN ’DKK’ THEN zahl/7.4377
WHEN ’ESP’ THEN zahl/166.386
WHEN ’FIM’ THEN zahl/5.94573
WHEN ’FRF’ THEN zahl/6.55957
WHEN ’GBP’ THEN zahl/0.6938
WHEN ’GRD’ THEN zahl/340.750
WHEN ’IEP’ THEN zahl/0.78756
WHEN ’ITL’ THEN zahl/1936.27
WHEN ’JPY’ THEN zahl/129.2850
WHEN ’LUF’ THEN zahl/40.3399
WHEN ’NLG’ THEN zahl/2.20371
WHEN ’NOK’ THEN zahl/8.1638
WHEN ’PTE’ THEN zahl/200.482
WHEN ’SEK’ THEN zahl/8.9605
WHEN ’USD’ THEN zahl/1.1755
ELSE
NULL
END );
IF eurobudget IS NULL THEN
SIGNAL SQLSTATE ’23ICU’
SET MESSAGE_TEXT = ’Fehler: Unbekannte Waehrung’;
ELSE
RETURN eurobudget;
END IF;
END @
Praktikum Datenbanken / DB2
Woche 7: Noch mehr SQL (Fortgeschrittene Konzepte)
Seite 2 von 4
Uni Duisburg-Essen
Fachgebiet Informationssysteme
Prof. Dr. N. Fuhr
VALUES (euro(’DEM 1,000’))
511
VALUES (euro(’USD 1,000’))
850
VALUES (euro(’FRU 1,000’))
SQL0438N Application raised error with diagnostic text: "Fehler: Unbekannte
Waehrung". SQLSTATE=23ICU
(c) Benutzt die Währungsinformation des Attributs budget für eine UDF
herkunftsland().
CREATE FUNCTION herkunftsland(budget VARCHAR(75))
RETURNS VARCHAR(50)
DETERMINISTIC
LANGUAGE SQL
CONTAINS SQL
BEGIN ATOMIC
DECLARE waehrung CHARACTER(3);
SET waehrung=left(budget,3);
RETURN
CASE waehrung
WHEN ’ATS’ THEN ’Österreich’
WHEN ’BEF’ THEN ’Belgien’
WHEN ’CHF’ THEN ’Schweiz’
WHEN ’CZK’ THEN ’Tschechien’
WHEN ’DEM’ THEN ’Deutschland’
WHEN ’DKK’ THEN ’Dänemark’
WHEN ’ESP’ THEN ’Spanien’
WHEN ’FIM’ THEN ’Finnland’
WHEN ’FRF’ THEN ’Frankreich’
WHEN ’GBP’ THEN ’Großbritannien’
WHEN ’GRD’ THEN ’Griechenland’
WHEN ’IEP’ THEN ’Irland’
WHEN ’ITL’ THEN ’Italien’
WHEN ’JPY’ THEN ’Japan’
WHEN ’LUF’ THEN ’Luxemburg’
WHEN ’NLG’ THEN ’Niederlande’
WHEN ’NOK’ THEN ’Norwegen’
WHEN ’PTE’ THEN ’Portugal’
WHEN ’SEK’ THEN ’Schweden’
WHEN ’USD’ THEN ’USA’
ELSE ’Unbekannt’
END;
END @
VALUES (herkunftsland(’DEM 1,000’))
Deutschland
Praktikum Datenbanken / DB2
Woche 7: Noch mehr SQL (Fortgeschrittene Konzepte)
Seite 3 von 4
Uni Duisburg-Essen
Fachgebiet Informationssysteme
Prof. Dr. N. Fuhr
VALUES (herkunftsland(’USD 1,000’))
USA
VALUES (herkunftsland(’FRU 1,000’))
Unbekannt
(d) Schreibt eine tabellenwertige SQL-UDF kategorie(), die zu einem Genrenamen (als String) alle Tupel aus der Tabelle film (!) zurückliefert, die
zu diesem Genre gehören.
CREATE FUNCTION kategorie(genrename varchar(30))
RETURNS TABLE (titel varchar(100),
jahr integer,
art char(2),
veroeffentlicht char(10))
LANGUAGE SQL
READS SQL DATA
NO EXTERNAL ACTION
DETERMINISTIC
RETURN
SELECT f.*
FROM film AS f RIGHT JOIN genre AS g
ON f.titel=g.produktion
AND f.jahr=g.jahr
WHERE g.genre=genrename;
SELECT *
FROM table(kategorie(’Action’)) AS a
WHERE a.titel like ’A%’;
Praktikum Datenbanken / DB2
Woche 7: Noch mehr SQL (Fortgeschrittene Konzepte)
Seite 4 von 4