Übung zu pgSQL - MultiAugustinum

PostgreSQL
16. November 2010
E3A
Übung zu PostgreSQL
PL/pgSQL
Verwende die Datenbank aus den letzten Stunden (bestellungen oder so).
1. Schreibe eine Funktion, die die Artikel mit ihrer zugehörigen Kategorie ausgibt. Verwende die
Tabellen artikel sowie kategorie. Die Ausgabe soll in etwa so aussehen:
bestellungen=# select f_artkat(11, 14);
f_artkat
---------------------------------------------------------------------------------------("Word 2007 Basis","An Beispielen lernen. Mit Aufgaben üben..",Internet-Technologie)
("Excel 2007 Basis","An Beispielen lernen. Mit Aufgaben üben...",Internet-Technologie)
("AJAX - Frische Ansätze für das Web-Design",,"MS Office")
("My SQL",,Wirtschaft)
(4 Zeilen)
bestellungen=# select * from f_artkat(11, 14);
titel
|
untertitel
|
kategoriename
-------------------------------------------+--------------------------------------------+------------------Word 2007 Basis
| An Beispielen lernen. Mit Aufgaben üben.. | Internet-Technologie
Excel 2007 Basis
| An Beispielen lernen. Mit Aufgaben üben... | Internet-Technologie
AJAX - Frische Ansätze für das Web-Design |
| MS Office
My SQL
|
| Wirtschaft
(4 Zeilen)
Als Parameter werden Artikelnummern in einem Bereich angegeben, in diesem Beispiel werden
von den Artikeln mit den Nummern 11 bis 14 die Werte ausgegeben.
Da die Werte aus zwei Tabellen stammen, verwende dazu eine eigene Kategorie (siehe
http://www.multiaugustinum.com/~guch/images/1/1f/Udf_funktionen_postgresql.pdf)
2. Erstelle eine Tabelle mit folgendem Aussehen:
test=# select * from autor_test order by aid;
aid | name
-----+-------4 | fritz
5 | maxi
6 | mitzi
Schreibe eine Funktion, die die Werte bei Vorhandensein aktualisiert. Sind sie jedoch nicht vorhanden, sollen sie neu eingetragen werden. Angenommen, ich rufe die Funktion so auf (Abb. 1),
dann erhalte ich folgendes Ergebnis (Abb. 2), (weil der Wert 6 schon in der Tabelle war).
test=# select * from autor_test;
aid |
name
-----+-----------4 | fritz
5 | maxi
6 | mitzt-tant
test=# select f_autortest1(6, 'mitzt-tant');
f_autortest1
---------------(6,mitzt-tant)
(1 Zeile)
Abbildung 1: Aufruf der Funktion
Abbildung 2: Ergebnis der Funktion
1
PostgreSQL
16. November 2010
E3A
Rufe ich die Funktion so auf (Abb. 3), erhalte ich folgendes Ergebnis (Abb. 4),
test=# select * from autor_test;
aid |
name
-----+------------4 | fritz
5 | maxi
6 | mitzt-tant
7 | beppi-onkel
test=# select f_autortest1(7, 'beppi-onkel');
f_autortest1
----------------(7,beppi-onkel)
Abbildung 3: Aufruf derFunktion
Abbildung 4: Ergebnis
dann wird ein Wert neu eingetragen, weil er noch nicht existiert.
3. Wandle die FOR-Schleife aus Punkt 2.3.2 in
http://www.multiaugustinum.com/~guch/images/1/1f/Udf_funktionen_postgresql.pdf so ab, dass
das Ergebnis mit RETURN (Abb. 6) statt mit RAISE (Abb. 5) ausgegeben wird.
Tip: Im CREATE-Teil der Funktion genügt nicht mehr ein einfaches return, da mehrere Werte
ausgegeben werden (Stichwort setof).
test=# select schleife_for1();
schleife_for1
--------------1
2
3
4
5
(5 Zeilen)
test=# select schleife_for();
HINWEIS: 1
HINWEIS: 2
HINWEIS: 3
HINWEIS: 4
HINWEIS: 5
schleife_for
--------------
Abbildung 6: mit RETURN
Abbildung 5: mit RAISE
4. Schreibe eine Funktion, die von einem bestimmten Artikel den Netto- und Bruttopreis ausgibt.
Als Parameter werden die Artikelnummer und der Steuersatz angegeben. Die Ausgabe soll so
aussehen (Beispiel mit Artikel 11 und 20% Steuer).
bestellungen=# select * from f_artpreis(11, 20);
artikelnummer | netto |
brutto
---------------+-------+--------------------------11
| 14.95 | 17.9400000000000000000000
(1 Zeile)
5. Schreibe eine Funktion, die die Kunden in einem Bereich auflistet. Wenn der Vorname leer ist,
dann soll statt dessen die eMail-Adresse ausgegeben werden. In diesem Beispiel werden die Kunden von 100 bis 108 aufgelistet (1. und 2. Parameter), gleichzeitig müssen sie seit mindestens
2005 Kunde sein (3. Parameter). Kunde 103 und 106 haben keinen Vornamen, daher wird die
eMail-Adresse ausgegeben. [ Verwende eine Schleife ]
bestellungen=# select f_kunden_email(100, 108, 2005);
f_kunden_email
---------------------------------------------100 * Name: Müller, Hans
102 * Name: Meyer, Richard
103 * Firma: Firma * email: Beta-Design
104 * Name: Lange, Anna
105 * Name: Meier, Bernd
106 * Firma: Firma * email: Damaschke-Kleber
107 * Name: Beckman, Daniel
108 * Name: Wendtland, Hans
(8 Zeilen)
2
PostgreSQL
16. November 2010
E3A
1. Lösung
bestellungen=# CREATE TYPE t_artkat AS
(titel text,
untertitel text,
kategoriename text);
bestellungen=# CREATE OR REPLACE FUNCTION f_artkat(integer, integer)
RETURNS SETOF t_artkat AS
$BODY$
declare
ar1 alias for $1;
ar2 alias for $2;
erg t_artkat%rowtype;
begin
for erg in
select titel, untertitel, kategoriename from artikel, kategorie where
artikel.kategorienr = kategorie.kategorienr and artikel.artikelnr between ar1 and ar2
loop
return next erg;
end loop;
end $BODY$
LANGUAGE 'plpgsql';
2. Lösung
test=# CREATE OR REPLACE FUNCTION f_autortest1(integer, text)
RETURNS record AS
$BODY$
declare
nummer alias for $1;
namen alias for $2;
anz int;
autortest record;
begin
select count(*) into anz from autor_test where aid = nummer;
if anz = 0 then
insert into autor_test values(nummer, namen);
else
update autor_test set aid = nummer, name = namen where aid = nummer;
end if;
select aid, name into autortest from autor_test where aid = nummer;
return autortest;
end $BODY$
3. Lösung
test=# CREATE OR REPLACE FUNCTION schleife_for1()
RETURNS SETOF integer AS
$BODY$
declare zahl int;
begin
zahl := 1;
for zahl in 1..5 loop
/* 1 bis 5 */
return query select zahl;
end loop;
--return null;
end; $BODY$
LANGUAGE 'plpgsql';
4. Lösung
bestellungen=# CREATE FUNCTION f_artpreis(IN artnr integer, IN steuer numeric, OUT artikelnummer integer,
OUT netto numeric, OUT brutto numeric)
RETURNS SETOF record AS
$BODY$
declare
artnr alias for $1;
steuer alias for $2;
begin
return query select artikelnr, artikelpreis, (artikelpreis + artikelpreis*(steuer/100)) from artikel where
artikelnr = artnr;
end $BODY$
LANGUAGE 'plpgsql';
3
PostgreSQL
16. November 2010
E3A
5. Lösung
bestellungen=# CREATE FUNCTION f_kunden_email1(integer, integer, integer)
RETURNS SETOF text AS
$BODY$
DECLARE
kundennr1 alias FOR $1;
kundennr2 alias FOR $2;
jahr alias for $3;
ergebnis Text;
zielzeile kunden%ROWTYPE;
BEGIN
FOR zielzeile IN SELECT *
FROM kunden
WHERE kundennr BETWEEN kundennr1 AND kundennr2 and
EXTRACT(year FROM kundeseit) > jahr
LOOP
if zielzeile.vorname is NULL --or zielzeile.name is null
then
ergebnis := zielzeile.kundennr || ' * Firma: '||zielzeile.firma|| ' * email: ' ||zielzeile.email;
RETURN next ergebnis;
else
ergebnis := zielzeile.kundennr || ' * Name: ' || zielzeile.name || ', ' || zielzeile.vorname;
RETURN next ergebnis;
end if;
/*ergebnis := zielzeile.kundennr || ' * ' || zielzeile.email;
RETURN NEXT ergebnis;*/
END LOOP;
END
$BODY$
LANGUAGE 'plpgsql';
4