Musterlösung - home.hs

Übung PL/SQL I Lösungen
1) Erstelle die Sequenz schlafplatz_seq und die Tabelle schlafplatz wie in der
Vorlesung beschrieben. Fülle die Tabelle mit mind. 5 Datensätzen.
CREATE SEQUENCE schlafplatz_seq
MINVALUE 1
MAXVALUE 99999999
START WITH 1
INCREMENT BY 1
CACHE 10;
--- Stored Procedures, Trigger
-CREATE TABLE schlafplatz(
schlafplatz_id NUMBER(8,0) NOT NULL,
bezeichnung VARCHAR(255) NULL,
-- der längste Städtename der Welt ist
-- Llanfairpwllgwyngyllgogerychwyrndrobwllllantysiliogogogoch
-- (58 Buchstaben, Ort in Wales)
stadt VARCHAR(60) NOT NULL,
anzahl_plaetze NUMBER(2,0) NOT NULL,
beliebtheit_rang NUMBER(*,0) NOT NULL,
PRIMARY KEY (schlafplatz_id));
INSERT INTO schlafplatz VALUES(
schlafplatz_seq.nextval,
'Seppl Berlin',
'Berlin',
2,
1);
INSERT INTO schlafplatz VALUES(
schlafplatz_seq.nextval,
'Max',
'Köln',
3,
2);
INSERT INTO schlafplatz VALUES(
schlafplatz_seq.nextval,
'Kathrinchen',
'Karlsruhe',
1,
3);
INSERT INTO schlafplatz VALUES(
schlafplatz_seq.nextval,
'Marten',
'Berlin',
2,
4);
2) Füge (mit alter table) eine Spalte anzahl_mitbewohner hinzu, die Spalte darf
nicht leer sein, und soll mit „0“ initialisiert sein. Aktualisiere alle vorhandenen Datensätze mit
einer beliebigen Anzahl der Mitbewohner (mind. 1mal jeweils mit „1“, „2“, „3“, „5“).
ALTER TABLE schlafplatz
ADD (anzahl_mitbewohner NUMBER(2,0) DEFAULT 0 NOT NULL);
Trivial-Lösung:
UPDATE schlafplatz
UPDATE schlafplatz
UPDATE schlafplatz
UPDATE schlafplatz
SET
SET
SET
SET
anzahl_mitbewohner
anzahl_mitbewohner
anzahl_mitbewohner
anzahl_mitbewohner
=
=
=
=
5
1
2
3
WHERE
WHERE
WHERE
WHERE
schlafplatz_id
schlafplatz_id
schlafplatz_id
schlafplatz_id
Oder z.B: mit Cursor:
DECLARE
i NUMBER := 0;
BEGIN
FOR a_schlafplatz IN (SELECT * FROM schlafplatz) LOOP
i := i+1;
=
=
=
=
58;
57;
56;
55;
UPDATE schlafplatz SET anzahl_mitbewohner
a_schlafplatz.schlafplatz_id;
END LOOP;
=
i
WHERE
schlafplatz_id
=
END;
Erstelle einen SQL-Code-Block, der unter Verwendung von DBMS_OUTPUT.PUT_LINE
 Die Summe aller Mitbewohner ausgibt.
SET serveroutput ON
DECLARE
anzahl_mitbewohner_gesamt NUMBER;
BEGIN
SELECT sum(anzahl_mitbewohner) INTO anzahl_mitbewohner_gesamt
FROM schlafplatz;
DBMS_OUTPUT.PUT_LINE('Es gibt '||anzahl_mitbewohner_gesamt||
' Mitbewohner in der Datenbank');
END;
3) Loop, If, Else. Du hast das letzte Mal eine Spalte anzahl_mitbewohner zur Tabelle
schlafplatz hinzugefügt, die Spalte durfte nicht leer sein, und sollte mit „0“ initialisiert
sein. Du hast alle vorhandenen Datensätze mit einer beliebigen Anzahl Mitbewohner (mind.
1mal jeweils mit „1“, „2“, „3“, „5“) aktualisiert. Du hast die Summe aller Mitbewohner ausgegeben.
Erstelle
einen
SQL-Code-Block,
der
unter
Verwendung
von
DBMS_OUTPUT.PUT_LINE
 die Bezeichnungen aller Schlafplätze mit Komma getrennt ausgibt, die 1, 2, und mehr
Mitbewohner haben (LOOP, IF, ELSE). Z.B. „Einen Mitbewohner haben:
Seppl, Kathrinchen; Zwei Mitbewohner haben: Marten; Mehr
Mitbewohner haben: Max, Christian“
DECLARE
CURSOR cur_schlafplatz IS
SELECT *
FROM schlafplatz s;
gruppe_1mitbewohner VARCHAR2(8000);
gruppe_2mitbewohner VARCHAR2(8000);
gruppe_Xmitbewohner VARCHAR2(8000);
BEGIN
FOR ein_schlafplatz IN cur_schlafplatz LOOP
IF ein_schlafplatz.anzahl_mitbewohner = 1 THEN
IF gruppe_1mitbewohner IS NULL THEN
gruppe_1mitbewohner := ein_schlafplatz.bezeichnung;
ELSE
gruppe_1mitbewohner := gruppe_1mitbewohner || ', ' ||
ein_schlafplatz.bezeichnung;
END IF;
ELSIF ein_schlafplatz.anzahl_mitbewohner = 2 THEN
IF gruppe_2mitbewohner IS NULL THEN
gruppe_2mitbewohner := ein_schlafplatz.bezeichnung;
ELSE
gruppe_2mitbewohner := gruppe_2mitbewohner || ', ' ||
ein_schlafplatz.bezeichnung;
END IF;
ELSIF ein_schlafplatz.anzahl_mitbewohner > 2 THEN
IF gruppe_Xmitbewohner IS NULL THEN
gruppe_Xmitbewohner := ein_schlafplatz.bezeichnung;
ELSE
gruppe_Xmitbewohner := gruppe_Xmitbewohner || ', ' ||
ein_schlafplatz.bezeichnung;
END IF;
END IF;
END LOOP;
DBMS_OUTPUT.PUT_LINE('1 Mitbewohner: '|| gruppe_1mitbewohner ||'; '||
'2 Mitbewohner: '|| gruppe_2mitbewohner ||'; '||
'mehr Mitbewohner: '|| gruppe_Xmitbewohner);
END;
4) Ausnahmen, Parametrisierte Cursor. Lösche alle Schlafplätze. Füge Christian aus Heidelberg mit fünf Mitbewohnern, Nora aus Heidelberg mit drei Mitbewohnern, E.T. aus Karlsruhe mit zehn und Buddy aus Karlsruhe mit einem Mitbewohner den Schlafplätzen hinzu.
INSERT INTO schlafplatz VALUES(
schlafplatz_seq.nextval,
'Christian',
'Heidelberg',
2,
6,
5);
INSERT INTO schlafplatz VALUES(
schlafplatz_seq.nextval,
'Nora',
'Heidelberg',
2,
7,
3);
INSERT INTO schlafplatz VALUES(
schlafplatz_seq.nextval,
'E.T.',
'Karlsruhe',
2,
8,
10);
INSERT INTO schlafplatz VALUES(
schlafplatz_seq.nextval,
'Buddy',
'Karlsruhe',
2,
9,
1);
Erstelle einen SQL-Code-Block, der unter Verwendung von DBMS_OUTPUT.PUT_LINE
 mit einer benutzerdefinierten Ausnahme „exception_zu_laut“ unter Angabe
von Stadt und Bezeichnung abbricht, wenn in der Tabelle ein Schlafplatz mit mehr als
vier Mitbewohner gefunden wird, da es dann zu laut ist, z.B.
“Christian in Heidelberg hat mehr als 4 Mitbewohner“
DECLARE
exception_zu_laut EXCEPTION;
CURSOR cur_schlafplatz IS
SELECT *
FROM schlafplatz s;
zu_laut_schlafplatz schlafplatz%ROWTYPE;
BEGIN
OPEN cur_schlafplatz;
FETCH cur_schlafplatz INTO zu_laut_schlafplatz;
WHILE cur_schlafplatz%found LOOP
IF zu_laut_schlafplatz.anzahl_mitbewohner > 4 THEN
RAISE exception_zu_laut;
END IF;
FETCH cur_schlafplatz INTO zu_laut_schlafplatz;
END LOOP;
EXCEPTION
WHEN exception_zu_laut THEN
DBMS_OUTPUT.PUT_LINE(zu_laut_schlafplatz.bezeichnung || ' in ' ||
zu_laut_schlafplatz.stadt || ' hat mehr als 4 Mitbewohner');
END;

mit einem parametrisierten Cursor für die Stadt, der (1) für alle Schlafplätze in Heidelberg mit der Ausnahme „exception_zu_laut_heidelberg“ unter Angabe
von Stadt und Bezeichnung abbricht, wenn mehr als vier Mitbewohner an einem
Schlafplatz wohnen und mit dem gleichen Cursor (2) für alle Schlafplätze in Karlsruhe
mit den Ausnahme „exception_zu_laut_karlsruhe“ unter Angabe von Stadt
und Bezeichnung abbricht, wenn mehr als vier Mitbewohner an einem Schlafplatz
wohnen.
Hinweis: Vor der Wiederverwendung eines Cursors muss dieser mit „CLOSE [cursorname]“ geschlossen werden.
Prüfe durch Verändern der Mitbewohnerzahlen, dass die Ausnahmen auch für beide
Städten greifen.
DECLARE
exception_zu_laut_heidelberg EXCEPTION;
exception_zu_laut_karlsruhe EXCEPTION;
CURSOR cur_schlafplatz(gesuchte_stadt char) IS
SELECT *
FROM schlafplatz s
WHERE s.stadt = gesuchte_stadt;
zu_laut_schlafplatz schlafplatz%ROWTYPE;
BEGIN
OPEN cur_schlafplatz('Heidelberg');
FETCH cur_schlafplatz INTO zu_laut_schlafplatz;
WHILE cur_schlafplatz%found LOOP
IF zu_laut_schlafplatz.anzahl_mitbewohner > 4 THEN
RAISE exception_zu_laut_heidelberg;
END IF;
FETCH cur_schlafplatz INTO zu_laut_schlafplatz;
END LOOP;
CLOSE cur_schlafplatz;
OPEN cur_schlafplatz('Karlsruhe');
FETCH cur_schlafplatz INTO zu_laut_schlafplatz;
WHILE cur_schlafplatz%found LOOP
IF zu_laut_schlafplatz.anzahl_mitbewohner > 4 THEN
RAISE exception_zu_laut_karlsruhe;
END IF;
FETCH cur_schlafplatz INTO zu_laut_schlafplatz;
END LOOP;
EXCEPTION
WHEN exception_zu_laut_heidelberg THEN
DBMS_OUTPUT.PUT_LINE(zu_laut_schlafplatz.bezeichnung ||
' in Heidelberg hat mehr als 4 Mitbewohner');
WHEN exception_zu_laut_karlsruhe THEN
DBMS_OUTPUT.PUT_LINE(zu_laut_schlafplatz.bezeichnung ||
' in Karlsruhe hat mehr als 4 Mitbewohner');
END;