Kapitel 5

Kapitel 5
Objektrelationale Datenmodell- und
Spracherweiterungen kommerzieller
Datenbanksysteme
Oracle 9i, Release 9.0.1
DB2 Universal Database, Version 7
Informix Dynamic Server.2000, Version 9.2
Objektrelationale Erweiterungen in Oracle9i
l Neue Basisdatentypen
l Referenztypkonstruktor
l Benutzerdefinierte Typen
–
–
Kollektionstypen
n Tabellentyp
n Variabler Arraytyp
Objekttypen (mit Subtypenbildung)
l Objekttabellen
l Objektsichten (mit Subsichtenbildung)
l Hierarchische Anfragen (als beschränkte Rekursion)
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2000/2001 (H.-J. Schek, C. Türker)
5-2
Lesestoff
Neue Basisdatentypen
l Large Objects
–
–
–
–
–
BLOB ("Internes" Binary Large Object)
CLOB ("Internes" Character Large Object)
BFILE ("Externes" Large Object)
Operationen
n EMPTY_BLOB, EMPTY_CLOB, BFILENAME
n IS [NOT] NULL
Nicht erlaubt
n BLOB/CLOB-Attribute als Teil eines Schlüssels oder in booleschen bzw.
arithmetischen Ausdrücken
n GROUP BY, ORDER BY
l Kein BOOLEAN Datentyp unterstützt
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2000/2001 (H.-J. Schek, C. Türker)
5-3
Lesestoff
Verwendung neuer Basisdatentypen
Definition einer Tabelle mit LOB-Attributen:
CREATE TABLE MitarbeiterTupelTabelle (
Name
VARCHAR(30),
Bild
BLOB,
BildDatei
BFILE,
Bewerbung
CLOB,
BewerbungDatei BFILE
);
Definition von Alias-Ordnern, in denen externe LOBs zu finden sind:
CREATE DIRECTORY MITARBEITER_BILDER AS 'D:\ImgSamp\';
CREATE DIRECTORY MITARBEITER_BEWERBUNGEN AS 'D:\RsmSamp';
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2000/2001 (H.-J. Schek, C. Türker)
5-4
Lesestoff
Erzeugen von LOBs
Erzeugen von LOB-Attributen:
INSERT INTO MitarbeiterTupelTabelle
VALUES ('Billy',
EMPTY_BLOB(),
BFILENAME('MITARBEITER_BILDER', 'billy.gif'),
EMPTY_CLOB(),
BFILENAME('MITARBEITER_BEWERBUNGEN', 'billy.rsm')
);
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2000/2001 (H.-J. Schek, C. Türker)
5-5
Lesestoff
Prozedur zum Belegung eines LOBs
CREATE PROCEDURE SetBewerbung (InputName IN VARCHAR2) IS
textdaten CLOB;
datei BFILE;
BEGIN
SELECT Bewerbung, BewerbungDatei INTO textdaten, datei
FROM MitarbeiterTupelTabelle
WHERE Name = InputName;
DBMS_LOB.FILEOPEN(datei, DBMS_LOB.FILE_READONLY);
DBMS_LOB.LOADFROMFILE(textdaten, datei, DBMS_LOB.GETLENGTH(datei));
DBMS_LOB.FILECLOSE(datei);
UPDATE MitarbeiterTupelTabelle
SET Bewerbung = textdaten
WHERE Name = InputName;
END;
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2000/2001 (H.-J. Schek, C. Türker)
5-6
Lesestoff
Prozedur zum Anzeigen eines LOBs
SET SERVEROUTPUT ON;
CREATE PROCEDURE ShowBewerbung (InputName IN VARCHAR2) IS
puffer VARCHAR(60);
textdaten CLOB;
textlaenge DECIMAL;
position DECIMAL;
BEGIN
textlaenge := 60;
position := 1;
SELECT Bewerbung INTO textdaten
FROM MitarbeiterTupelTabelle
WHERE Name = InputName;
DBMS_LOB.READ(textdaten, textlaenge, position, puffer);
DBMS_OUTPUT.PUT_LINE(puffer);
END;
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2000/2001 (H.-J. Schek, C. Türker)
5-7
Referenztyp
l REF: Referenz auf eine Instanz eines Objekttyps
–
Wert eines Referenzattributs ist eine OID
l Beispiel: Ein Teil referenziert ein anderes Teil
CREATE TYPE TeilTyp AS OBJECT (
Nr
NUMBER(10),
Bezeichnung
VARCHAR(25),
Farbe
VARCHAR(15),
IstTeilVon
REF TeilTyp
);
l Vorwärtsdeklaration ermöglicht zyklische Typdefinitionen
CREATE TYPE LieferantTyp;
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2000/2001 (H.-J. Schek, C. Türker)
5-8
Benutzerdefinierte Datentypen - Tabellentyp
l TABLE: Geordnete Multimenge mit indiziertem Zugriff
–
–
–
–
–
Ohne Grössenbeschränkung
Alle Elemente besitzen denselben Typ
Elementindex als Iterator
Anfragen auf geschachtelte Tabelle anwendbar
Daten der geschachtelte Tabelle sind auch indizierbar
l Syntax: CREATE TYPE Tabellentypname AS TABLE OF Elementtyp
l Beispiel: CREATE TYPE HobbiesTabellenTyp AS TABLE OF VARCHAR(20);
ALTER TABLE MitarbeiterTupelTabelle ADD Hobbies HobbiesTabellenTyp
NESTED TABLE Hobbies STORE AS Kundenhobbies;
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2000/2001 (H.-J. Schek, C. Türker)
5-9
Benutzerdefinierte Datentypen - Variabler Arraytyp
l VARRAY: Geordnete Multimenge mit indiziertem Zugriff
–
–
–
–
Grösse wird bei der Instanzierung festgelegt
Alle Elemente besitzen denselben Typ
Elementindex als Iterator
Achtung: VARRAY-Werte nicht direkt in SQL, sondern nur in PL/SQL nutzbar
l Syntax: CREATE TYPE Arraytypname AS VARRAY(Arraygrösse) OF Elementtyp
l Beispiel: CREATE TYPE TelefonArrayTyp AS VARRAY(30) OF VARCHAR(20);
ALTER TABLE MitarbeiterTupelTabelle ADD Telefone TelefonArrayTyp;
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2000/2001 (H.-J. Schek, C. Türker)
5-10
Verwendung von Typkonstruktoren in Einfügeoperationen
Erzeugen von Instanzen eines Array- bzw. Tabellentyps:
INSERT INTO MitarbeiterTupelTabelle (Name, Hobbies, Telefone)
VALUES ('Johnny',
HobbiesTabellenTyp('Reisen', 'Sport', 'Kino'),
TelefonArrayTyp('0041-1-6327248', '0041-1-7337947'));
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2000/2001 (H.-J. Schek, C. Türker)
5-11
Kollektionen und Nullwerte
Werte für ein kollektionswertiges Attribut (hier Telefone):
Kardinalität:
TelefonArraytyp('0041-1-223456', NULL, '0041-1-3458122')
TelefonArraytyp('0090-313-2445561', NULL)
TelefonArraytyp('0049-6124-13297')
TelefonArraytyp(NULL)
TelefonArraytyp()
NULL
3
2
1
1
0
NULL
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2000/2001 (H.-J. Schek, C. Türker)
5-12
Selektion von array-und tabellenwertigen Attributen
l Zugriff auf komplette Kollektion erfolgt analog zu anderen Attributen
SELECT Name, Telefone, Hobbies
FROM MitarbeiterTupelTabelle;
l Selektion und Belegung einzelner Arrayelemente nur innerhalb von
PL/SQL-Anweisungen möglich
l Selektion und Belegung einzelner Elemente einer inneren Tabelle
hingegen auch in SQL-Anweisungen möglich
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2000/2001 (H.-J. Schek, C. Türker)
5-13
PL/SQL-Methoden für Kollektionen
(i) liefert i-tes Element
COUNT gibt Kardinalität aus
EXTEND vergrössert Kollektion um ein NULL-Element
FIRST bzw. LAST liefern den kleinsten bzw. grössten verwendeten Index
PRIOR bzw. NEXT liefern den orhergehenden bzw. nachfolgenden Index
DELETE(i) löscht das i-te Element
TRIM(i) löscht die letzten i Elemente
LIMIT liefert maximal zulässige Anzahl von Elementen eines Arrays
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2000/2001 (H.-J. Schek, C. Türker)
5-14
Lesestoff
Selektion einzelner Elemente aus Arrays/inneren Tabellen
SET SERVEROUTPUT ON;
DECLARE
CURSOR mcursor IS SELECT * FROM MitarbeiterTupelTabelle;
mitarbeiter mcursor%ROWTYPE;
BEGIN
FOR mitarbeiter IN mcursor LOOP
DBMS_OUTPUT.PUT_LINE('Kundenname: ' || mitarbeiter.Name);
FOR i IN 1..mitarbeiter.Telefone.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(i || '.Telefon: ' || mitarbeiter.Telefone(i));
END LOOP;
FOR i IN 1..mitarbeiter.Hobbies.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(i || '.Hobby: ' || mitarbeiter.Hobbies(i));
END LOOP;
END LOOP;
END;
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2000/2001 (H.-J. Schek, C. Türker)
5-15
Einfügen einzelner Elemente in Arrays
CREATE PROCEDURE NeueNummer (n VARCHAR, nr VARCHAR)
AS
t TelefonArrayTyp;
BEGIN
SELECT Telefone INTO t FROM MitarbeiterTupelTabelle WHERE Name = n;
IF t IS NULL THEN t := TelefonArrayTyp(nr);
ELSE
IF t.COUNT < t.LIMIT THEN
t.EXTEND;
END IF;
t(t.LAST) := nr;
END IF;
UPDATE MitarbeiterTupelTabelle SET Telefone = t WHERE Name = n;
END;
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2000/2001 (H.-J. Schek, C. Türker)
5-16
Belegung kollektionswertiger Attribute mittels SFW-Query
Grundmuster:
INSERT INTO Tabelle (Kollektionsattribut)
VALUES ([CAST(MULTISET] (SFW-Query) [AS Tabellentypkonstruktor)])
Beispiel:
INSERT INTO MitarbeiterTupelTabelle (Name, Hobbies, Telefone)
VALUES ('John',
(CAST (MULTISET(SELECT DISTINCT VALUE(h)
FROM MitarbeiterTupelTabelle, TABLE(Hobbies) h
WHERE name = 'Billy' OR name = 'Can')
AS HobbiesTabellenTyp)),
(SELECT Telefone FROM MitarbeiterTupelTabelle WHERE Name = 'Billy'));
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2000/2001 (H.-J. Schek, C. Türker)
5-17
Lesestoff
Bearbeitung innerer Tabellen
Tupel in eine innere Tabelle einfügen:
INSERT INTO TABLE(SFW-Query-Liefert-Innere-Tabelle)
VALUES (...);
Tupel einer inneren Tabelle ändern:
UPDATE TABLE(SFW-Query-Liefert-Innere-Tabelle)
SET ...
WHERE ...;
Tupel aus einer inneren Tabelle löschen:
DELETE FROM TABLE(SFW-Query-Liefert-Innere-Tabelle)
WHERE ...;
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2000/2001 (H.-J. Schek, C. Türker)
5-18
Bearbeitung innerer Tabellen - Beispiele
INSERT INTO TABLE(SELECT Hobbies
FROM MitarbeiterTupelTabelle
WHERE Name = 'Billy') VALUES('Kino');
UPDATE TABLE(SELECT Hobbies
FROM MitarbeiterTupelTabelle
WHERE Name = 'Billy') h
SET VALUE(h) = 'TV'
WHERE VALUE(h) = 'Kino';
DELETE TABLE(SELECT Hobbies
FROM MitarbeiterTupelTabelle
WHERE Name = 'Billy') h
WHERE VALUE(h) = 'Reisen';
VALUE ist hier notwendig, um
auf den Wert der Tupel der
inneren Tabelle zuzugreifen!
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2000/2001 (H.-J. Schek, C. Türker)
5-19
Benutzerdefinierte Datentypen - Objekttyp
l OBJECT: Strukturierter Typ mit Methoden und Subtypbildung
–
Defaultwerte und Integritätsbedingungen werden nicht unterstützt
l Syntax für die Definition eines Wurzeltyps:
CREATE TYPE Typname AS OBJECT (
Attributdefinitions-und-Methodendeklarationsliste
[Abbildungs-bzw-Ordnungsmethode]
)
[[NOT] FINAL]
[[NOT] INSTANTIABLE]
–
Default: FINAL, INSTANTIABLE
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2000/2001 (H.-J. Schek, C. Türker)
5-20
Lesestoff
Wurzeltypen - Beispiele (1)
CREATE TYPE PositionTyp AS OBJECT (
PosNr INTEGER,
Teil REF TeilTyp,
Menge INTEGER,
Preis INTEGER
);
CREATE TYPE AuftragTabellenTyp
AS TABLE OF REF AuftragTyp;
CREATE TYPE VorratTyp AS OBJECT (
Anzahl DECIMAL(10),
Kosten DECIMAL(12,2),
RefTeil REF TeilTyp
);
CREATE TYPE PositionTabellenTyp
AS TABLE OF PositionTyp;
CREATE TYPE AuftragTyp AS OBJECT (
ANr
INTEGER,
Lieferant REF LieferantTyp,
Eingang DATE,
Bearbeitet DATE,
Positionen PositionTabellenTyp,
MEMBER FUNCTION Anzahl
RETURN INTEGER
);
CREATE TYPE VorratTabellenTyp
AS TABLE OF VorratTyp;
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2000/2001 (H.-J. Schek, C. Türker)
5-21
Wurzeltypen - Beispiele (2)
CREATE TYPE AdresseTyp AS OBJECT (
Strasse VARCHAR(30),
Nr
DECIMAL(4),
PLZ
DECIMAL(5),
Ort
VARCHAR(40),
Land
VARCHAR(25)
) NOT FINAL;
CREATE TYPE LieferantTyp AS OBJECT (
LNr
INTEGER,
Name
VARCHAR(25),
Adresse
AdresseTyp,
Telefone
TelefonArrayTyp,
Vorrat
VorratTabellenTyp
);
CREATE TYPE KundeTyp AS OBJECT (
KNr
INTEGER,
Name
VARCHAR(30),
Anschrift
AdresseTyp,
Telefone
TelefonArrayTyp,
Auftraege
AuftragTabellenTyp,
MEMBER FUNCTION NeueNummer(t VARCHAR) RETURN TelefonArrayTyp
) NOT FINAL;
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2000/2001 (H.-J. Schek, C. Türker)
5-22
Subtypbildung - Aufbau von Typhierarchien
l Syntax:
–
–
–
CREATE TYPE Subtypname UNDER Supertypname (
Attributdefinitions-und-Methodendeklarationsliste
[Überschreibende-Abbildungsmethode]
)
[[NOT] FINAL]
[[NOT] INSTANTIABLE]
Subtyp erbt alle Attribute und Methoden des Supertyps
Subtyp hat nur einen direkten Supertyp (keine Mehrfachvererbung)
Geerbte Methoden sind überschreibbar
l Beispiel: CREATE TYPE BWKundeTyp UNDER KundeTyp (
Hobbies HobbiesTabellenTyp,
Kredit DECIMAL(9,2)
);
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2000/2001 (H.-J. Schek, C. Türker)
5-23
Operationen auf Objekttypen
l Default-Konstruktor heisst wie der Objekttyp und hat für jedes Attribut einen
Parameter
Objekttypname(p1, ..., pn)
l Attributzugriff mittels Punktnotation
o.Attributname
l Objektvergleich basiert auf flacher Gleichheit
o1 =o2
l Referenzvergleich ist auch möglich
REF(o1) =REF(o2)
l Typtest mittels IS OF
o IS OF (Objekttypname)
l Temporäre Typanpassung mittels TREAT
TREAT (o AS Objekttypname)
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2000/2001 (H.-J. Schek, C. Türker)
5-24
Methodendeklaration
l Syntax:
[[NOT] OVERRIDING]
[[NOT] FINAL]
[[NOT] INSTANTIABLE]
{MEMBER|STATIC}
{FUNCTION|PROCEDURE}
Methodenname [(Parameterliste)]
[RETURN Rückgabetyp]
OVERRIDING: überschreibende Methode
FINAL: nicht-überschreibbare Methode
INSTANTIABLE: abstrakte Methoden;
Objekttyp bietet keine Implementierung
DEFAULT:
NOT OVERRIDING, NOT FINAL, INSTANTIABLE
MEMBER: Instanzmethode wird auf einem Objekt aufgerufen (impliziter SELF-Parameter)
STATIC: statische Methode wird auf dem Objekttyp aufgerufen
PROCEDURE: hat keinen Rückgabewert
FUNCTION: hat einen Rückgabewert, darf aber keine Objektattributwerte ändern
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2000/2001 (H.-J. Schek, C. Türker)
5-25
Methodenimplementierung
l Methodendeklaration und -implementierung erfolgen strikt getrennt
–
–
Deklaration erfolgt innerhalb der Definition des Objekttyps
Implementierung in einer separaten Anweisung (hier in PL/SQL)
l Beispiel: CREATE TYPE BODY AuftragTyp AS
MEMBER FUNCTION Anzahl RETURN INTEGER
AS
a INTEGER;
BEGIN
FOR i IN 1..SELF.Positionen.COUNT LOOP
a := a + SELF.Positionen(i).Menge;
END LOOP;
RETURN a;
END;
END;
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2000/2001 (H.-J. Schek, C. Türker)
5-26
Lesestoff
Methodenimplementierung - Weiteres Beispiel
CREATE TYPE BODY KundeTyp AS
MEMBER FUNCTION NeueNummer(nr VARCHAR) RETURN TelefonArrayTyp
IS
t TelefonArrayTyp := SELF.Telefone;
BEGIN
IF t IS NULL THEN t := TelefonArrayTyp(nr);
ELSE
IF t.COUNT < t.LIMIT THEN
t.EXTEND;
END IF;
t(t.LAST) := nr;
END IF;
RETURN t;
END;
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2000/2001 (H.-J. Schek, C. Türker)
5-27
Verwendung von benutzerdefinierten Typen - Beispiel
CREATE TABLE KundeTupelTabelle (
KNr
INTEGER,
Name
VARCHAR(30),
Anschrift
AdresseTyp,
objektwertiges Attribut
Telefone
TelefonArrayTyp,
arraywertiges Attribut
Auftraege
AuftragTabellenTyp
kollektionswertiges Attribut
)
NESTED TABLE Auftraege STORE AS Kundenauftraege;
INSERT INTO KundeTupelTabelle
VALUES (13, 'Billy',
AdresseTyp('Whitesteet', 31, 65307, 'Newtown', 'USA'),
TelefonArrayTyp('0041-1-6789332', '0049-6124-91267'),
AuftragTabellenTyp(NULL));
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2000/2001 (H.-J. Schek, C. Türker)
5-28
Benutzerdefinierte Gleichheit von Objekten
l Systemdefinierte Gleichtheit von Instanzen von Objekttypen basiert auf
paarweise Gleichheit der Attributwerte (Flache Gleichheit)
l Benutzerdefinierte Gleichheit bzw. Ordnung
–
–
MAP: Vergleich bzw. Ordnung basiert auf dem Ergebnis einer
Abbildungsfunktion, die Objektwerte auf Werte von Basisdatentypen (Zahlen,
Zeichenketten, Datum) abbildet
ORDER: Ordnungsfunktion ordnet jeweils zwei Instanzen
l Syntax:
MAP MEMBER FUNCTION Funktioname RETURN Typ
ORDER MEMBER FUNCTION Funktioname (o Objekttyp) RETURN INTEGER
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2000/2001 (H.-J. Schek, C. Türker)
5-29
Benutzerdefinierte Gleichheit von Objekten (Forts.)
Für jeden Objekttyp darf max. eine Map- oder Order-Funktion definiert sein
CREATE TYPE AdresseTyp AS OBJECT (
Attributdefinitionen, ...
MAP MEMBER FUNCTION AdresseMap RETURN VARCHAR2
-- Alternativ könnte folgende ORDER-Funktion definiert werden
-- ORDER MEMBER FUNCTION AdresseOrder (o AdresseTyp) RETURN INT
);
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2000/2001 (H.-J. Schek, C. Türker)
5-30
Map-Funktionen - Beispiel
CREATE TYPE BODY AdresseTyp AS
MAP MEMBER FUNCTION AdresseMap
RETURN VARCHAR2
AS
BEGIN
RETURN Land || Ort || PLZ || Strasse || Nr;
END;
END;
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2000/2001 (H.-J. Schek, C. Türker)
5-31
Order-Funktionen - Beispiel
CREATE TYPE BODY AdresseTyp AS
ORDER MEMBER FUNCTION AdresseOrder (a AdresseTyp)
RETURN INTEGER
AS
a1 VARCHAR2(200);
a2 VARCHAR2(200);
BEGIN
a1 := SELF.Land || SELF.Ort || SELF.PLZ || SELF.Strasse || SELF.Nr;
a2 := a.Land || a.Ort || a.PLZ || a.Strasse || a.Nr;
IF (a1 = a2) THEN RETURN 0;
ELSE IF (a1 < a2) THEN RETURN -1;
ELSE RETURN 1;
END IF;
END IF;
END;
END;
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2000/2001 (H.-J. Schek, C. Türker)
5-32
Verwendung von Map- bzw. Order-Funktionen
Beispielanfragen, in denen eine Map- bzw. Order-Funktion zum Einsatz kommt:
SELECT Name, Anschrift
FROM KundeTupelTabelle
ORDER BY Anschrift;
SELECT Name, Anschrift
FROM KundeTupelTabelle
WHERE Anschrift < AdresseTyp('Seefeldstrasse', 31, 8008, 'Zurich', 'CH');
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2000/2001 (H.-J. Schek, C. Türker)
5-33