Musterlösung

Eidgenössische
Technische Hochschule
Zürich
Ecole polytechnique fédérale de Zurich
Politecnico federale di Zurigo
Swiss Federal Institute of Technology Zurich
Dr. C. Türker
Objektrelationale, erweiterbare Datenbanken
WS03/04
Übung 9
Musterlösung
Aufgabe 1: Entwurf einer Funktion in Oracle-PL/SQL
Schreiben Sie eine Funktion, die als Parameter einen Primärschlüssel und das monatliche Einkommen eines Kunden erhält, dessen Kreditlimit errechnet werden
soll. Dieses Limit beträgt normalerweise das dreifache seines Gehalts, minimal
aber 500 und maximal 5000 Franken. Der so ermittelte Wert soll zurückgegeben
werden.
Aufgabe 2: Entwurf einer Prozedur in Oracle-PL/SQL
Entwerfen Sie eine Prozedur, die wiederum den Primärschlüssel eines Kunden
und eine neue Telefonnummer übergeben bekommt. Diese Telefonnummer soll
nun zusätzlich zu den bisher vorhandenen gespeichert werden.
Beispiellösung für Aufgabe 1 und 2:
Bevor wir die Funktion bzw. Prozedur umsetzen, wollen wir die zugrunde liegende Tabellen erzeugen, damit wir die Routinen später auch testen können. In
Übung 8 hatten wir bereits einen Teil des Anwendungsszenarios aus Übung 1
umgesetzt. Darauf aufbauend implementieren wir zunächst die restlichen Klassen und danach die geforderten Routinen.
CREATE TYPE TelefonTabellenTyp
AS TABLE OF VARCHAR(20);
/
CREATE TYPE AdresseTyp AS OBJECT (
Strasse VARCHAR(30),
Nr
DECIMAL(4),
PLZ
Ort
Land
DECIMAL(5),
VARCHAR(40),
VARCHAR(25)
)
NOT FINAL;
/
CREATE TYPE PositionTyp AS OBJECT (
Artikel REF ArtikelTyp,
Anzahl INTEGER
);
/
CREATE TYPE PositionTabellenTyp
AS TABLE OF PositionTyp;
/
CREATE TYPE BestellungsTyp AS OBJECT (
Bestellnummer INTEGER,
Eingangsdatum DATE,
Status VARCHAR(15),
Positionen PositionTabellenTyp
);
/
CREATE TYPE BestellungsTabellenTyp
AS TABLE OF REF BestellungsTyp;
/
CREATE TYPE PersonTyp AS OBJECT (
Name
VARCHAR(30),
Anschrift
AdresseTyp,
Telefone
TelefonTabellenTyp,
Email
VARCHAR(25)
)
NOT FINAL
NOT INSTANTIABLE;
/
CREATE TYPE KundeTyp UNDER PersonTyp (
Kundennummer INTEGER,
Bestellungen BestellungsTabellenTyp,
Kreditlimit DECIMAL(12,2)
)
FINAL;
/
CREATE TYPE AngestellterTyp UNDER PersonTyp (
Personalnummer INTEGER,
Gehalt
DECIMAL(12,2)
)
FINAL;
/
CREATE TABLE Bestellung OF BestellungsTyp (
PRIMARY KEY(Bestellnummer),
CHECK(Status IN (’InBearbeitung’, ’Eingegangen’,
’Geliefert’, ’Bezahlt’))
)
NESTED TABLE Positionen STORE AS Positionen;
CREATE TABLE Kunde OF KundeTyp (
PRIMARY KEY(Kundennummer),
Name
NOT NULL,
Anschrift
NOT NULL,
CHECK(Anschrift.PLZ IS NOT NULL),
CHECK(Kreditlimit <= 5000)
)
NESTED TABLE Bestellungen STORE AS Kundenbestellungen
NESTED TABLE Telefone STORE AS Kundentelefone;
CREATE TABLE Angestellte OF AngestellterTyp (
PRIMARY KEY(Personalnummer),
Name
NOT NULL,
Anschrift
NOT NULL,
CHECK(Anschrift.PLZ IS NOT NULL),
CHECK(Gehalt >= 2000)
)
NESTED TABLE Telefone STORE AS Angestelltentelefone;
---------------------------------------------------- Funktion Kundenkredit berechnet das Kreditlimit
-- basierend auf dem Gehalt eines Kunden
--------------------------------------------------CREATE FUNCTION Kundenkredit(gehalt DECIMAL)
RETURN DECIMAL
AS
limit DECIMAL;
BEGIN
limit := 3 * gehalt;
IF limit < 500.0 THEN limit:= 500;
ELSE IF limit > 5000.0 THEN limit:= 5000; END IF;
END IF;
RETURN limit;
END;
/
---------------------------------------------------- PROCEDURE NeueNummer fügt eine (neue)
-- Telefonnummer in die tabellenwertige Spalte
-- Telefone eines bestimmten Kunden ein
--------------------------------------------------CREATE PROCEDURE NeueNummer(knr INTEGER, nn VARCHAR)
AS
BEGIN
INSERT INTO TABLE(SELECT Telefone
FROM Kunde
WHERE Kundennummer = knr)
VALUES(nn);
END;
/
---------------------------------------------------- Basierend auf den Artikel-Daten aus Uebung 8
-- erzeugen wir nun Daten für Bestellungen und
-- Kunden, um die neue Routinen zu testen
--------------------------------------------------INSERT INTO Bestellung
VALUES (7989,
CURRENT_DATE,
’Eingegangen’,
PositionTabellenTyp(
PositionTyp((SELECT REF(a)
FROM Artikel a
WHERE Nummer = ’123-4567-98’),
1)));
INSERT INTO Bestellung
VALUES (6134,
CURRENT_DATE,
’InBearbeitung’,
PositionTabellenTyp(
PositionTyp((SELECT REF(a)
FROM Artikel a
WHERE Nummer = ’989-4567-98’),
1),
PositionTyp((SELECT REF(a)
FROM Artikel a
WHERE Nummer = ’481-4427-15’),
12)));
---------------------------------------------------- Funktion Kundenkredit wird beim Erzeugen
-- eines Kunden verwendet
--------------------------------------------------INSERT INTO Kunde
VALUES(’James’,
AdresseTyp(’Haldeneggsteig’, 4, 8092, ’Zürich’, ’CH’),
TelefonTabellenTyp(’0041-1-9876543’,
’0049-611-6733219’),
’[email protected]’,
1704,
CAST(MULTISET(SELECT REF(b)
FROM Bestellung b
WHERE Bestellnummer = 7989)
AS BestellungsTabellenTyp),
Kundenkredit(1000.0));
INSERT INTO Kunde
VALUES(’John’,
AdresseTyp(’Seestrasse’, 13, 8008, ’Zürich’, ’CH’),
TelefonTabellenTyp(’0041-1-2612345’),
’[email protected]’,
6970,
NULL,
Kundenkredit(3000.0));
---------------------------------------------------- Aufruf der Prozedur NeueNummer
--------------------------------------------------CALL NeueNummer(1704, ’0041-1-45678912’);
---------------------------------------------------- Es ist auch möglich, die folgende Funktion zu
-- definieren
--------------------------------------------------CREATE FUNCTION Kundenlimit(knr INTEGER,
gehalt DECIMAL)
RETURN DECIMAL
AS
limit DECIMAL;
BEGIN
limit := 3 * gehalt;
IF limit < 500.0 THEN limit:= 500;
ELSE IF limit > 5000.0 THEN limit:= 5000; END IF;
END IF;
UPDATE Kunde
SET Kreditlimit = limit
WHERE Kundennummer = knr;
RETURN limit;
END;
/
---------------------------------------------------- Ein Aufruf dieser Funktion ist innerhalb einer
-- Anfrage nicht erlaubt
--------------------------------------------------SELECT Kundenlimit(Kundennummer, 1500)
FROM Kunde
WHERE Kundennummer = 6970;
---------------------------------------------------- Oracle gibt die folgende Fehlermeldung aus:
---------------------------------------------------- FEHLER in Zeile 1:
-- ORA-14551: DML-Vorgang kann innerhalb einer
-Abfrage nicht ausgeführt werden
-- ORA-06512: in "SYSTEM.KUNDENLIMIT", Zeile 10
---------------------------------------------------- Ebenso ist ein Aufruf ein solchen Funktion
-- nicht in einer DML-Anweisung gestattet
--------------------------------------------------UPDATE Kunde
SET Kreditlimit = Kundenlimit(Kundennummer, 1500)
WHERE Kundennummer = 6970;
---------------------------------------------------- Oracle gibt die folgende Fehlermeldung aus:
---------------------------------------------------- FEHLER in Zeile 2:
-- ORA-04091: Tabelle SYSTEM.KUNDE wird gerade
-geändert, Trigger/Funktion sieht
-dies möglicherweise nicht
-- ORA-06512: in "SYSTEM.KUNDENLIMIT", Zeile 10
---------------------------------------------------- Folgender Aufruf in einem PL/SQL-Block ist
-- dagegen möglich
--------------------------------------------------DECLARE
test DECIMAL;
Begin
test := Kundenlimit(6970, 1500);
END;
/