User Defined Functions (UDF)

Objekt-Relationale (OR) Datenbanken
Übersicht
• Einführung: Objekt-relationale Erweiterungen von SQL (ORSQL)
• Objekte und Tabellen
• OR-Create, -Insert, -Update, -Select
• User-Defined Functions (UDFs) und Methoden
Literatur
O´Neil / O´Neil: Database – Principles, Programming, and
Performance, 2nd Edition. Morgan Kaufmann Publishers, 2001.
Karczewski
Datenbanken II
1
ORSQL Möglichkeiten
Relationale Tabellendefinitionen werden erweitert durch user defined
types:
Spalten, die mehr als einen Eintrag besitzen und solche, die eine interne
Struktur haben (wie ein Record), sind erlaubt (Verletzung 1NF).
Beispiel:
eid
ename
position
dep_age
Michael J.
Susan R.
9
7
e01
Smith, John
e02
Andrews, David Superintendent David M. Jr.
10
e03
Jones, Franklin Agent
11
9
4
Karczewski
Agent
dependents
dep_name
Datenbanken II
Andrew K.
Mark W.
Louisa M.
2
Objekte und Tabellen
Ein Objekt-Typ besitzt Attribute unterschiedlicher Typen, analog zu
Spalten einer Tabelle.
Beispiel (Oracle):
create type name_t as object
(
lname varchar(30), -fname varchar(30), -mi
char(1)
-);
/
--
Karczewski
-- „Create Object Type“
last name
first name
middle initial
SQL*Plus
Datenbanken II
3
Objekte und Tabellen
Nach der Definition eines Typs (mit „create type“) kann man diesen
so definierten Typ wie ein gewöhnliches Attribut benutzen.
Beispiel (Oracle):
create table
(
tid
tname
room
);
teachers
-- „Create Table Scheme“
int,
name_t,
int
-- identifier
-- object type defined above
-- room number
Bis hierher existieren noch keine Objekte vom Typ name_t.
Karczewski
Datenbanken II
4
Objekte und Tabellen
Einträge in die zuvor definierte Tabelle sind wie in SQL durch den
insert-Befehl möglich. Insert wird erweitert um die Möglichkeit,
Objekt-Typen zu integrieren. Dies geschieht durch Benutzung eines
Objekt-Konstruktors in dem insert-Befehl.
Beispiel (Oracle):
Objekt-Konstruktor
insert into teachers values
(
1234,
name_t(’Einstein’, ’Albert’, ’E’),
120
);
Karczewski
Datenbanken II
5
Objekte und Tabellen
Nach Ausführung des insert-Befehls liegt folgender Tabelleninhalt
vor:
Beispiel (Oracle):
tname
lname
1234 Einstein
room
tid
Karczewski
fname mi
Albert E
Datenbanken II
120
6
Objekte und Tabellen
Die Punkt-Notation wird konsequent genutzt, um Werte des ObjektTyps zu nutzen:
select t.tid
from
teachers t
where t.room = 120; -- normal SQL-select
select t.tid, t.tname.fname, t.tname.lname
from
teachers t
where t.room = 123; -- extended SQL
select tid, tname.fname, tname.lname
from
teachers
where room = 123; -- doesn´t work
Der alias t muss verwendet werden, damit die mehrfache PunktNotation funktioniert.
Karczewski
Datenbanken II
7
Objekte und Tabellen
Man kann die Objekt-Typ-Definition innerhalb einer anderen ObjektTyp-Definition verwenden.
Beispiel:
create type person_t as object
(
ssno
int,
pname name_t,
-- must be defined first
age
int
);
/
person_t ist abhängig von name_t, d.h. man kann name_t nicht
löschen bevor person_t gelöscht ist.
Karczewski
Datenbanken II
8
Objekte und Tabellen
Eine Tabelle wird Objekt-Tabelle genannt, wenn ihre Zeilen vom
Objekt-Typ sind. Das bedeutet: Jede Zeile enthält ein Objekt dieses
Typs.
Beispiel:
create table people of person_t
(
primary key(ssno)
);
Karczewski
Datenbanken II
9
Objekte und Tabellen
Beispiel-Tabelle people:
people
nameless top-level
column
(Zeilen-Objekte)
named columns (also
known as top-level
attributes)
select value(p)
select *
row objects
ssno
column objects
pname
age
pname.lname pname.fname pname.mi
attributes within pname
row 1
123550123
March
Jacquelin
E
23
row 2
245882134
Delaney
Patrick
X
59
row 3
023894455
Sanchez
Jose
F
30
Karczewski
Datenbanken II
10
Objekte und Tabellen (select-statements)
select p.age from people p where p.ssno = 123550123
(wie bei SQL)
select p.pname from people p where p.age > 25
Spalten werden mit vorangestelltem Typ ausgegeben,
z.B. name_t(´Sanchez´, ´Jose´, ´F´)
select * from people p where p.age > 25
Liefert die top-level Attribute (row objects)
ssno
--------245882134
023894455
Karczewski
pname(lname,fname,mi)
------------------------------name_t(´Delaney´,´Patrick´,´X´)
name_t(´Sanchez´,´Jose´,´F´)
Datenbanken II
age
---59
30
11
Objekte und Tabellen (select-statements)
select value(p) from people p where age > 25
Die Zeilen-Objekte werden ausgegeben (nameless top level column).
Value(p) ist nicht zu verwechseln mit VALUES im insert-Befehl.
Value(p)
-------person_t
person_t
(ssno,
-----(245882134,
(023894455,
pname(lname,fname,mi),
---------------------name_t(´Delaney´,´Patrick´,´X´),
name_t(´Sanchez´,´Jose´,´F´),
age)
---59)
30)
Person_t ist der Objekt-Konstruktur, mit dem die Tabelle definiert wurde.
Das Ergebnis ist ein anderes als das beim „select *“-Befehl.
Durch den Objekt-Konstruktor gibt es eine (neue) Objekt-Sicht auf die Zeilen
der Tabelle.
Karczewski
Datenbanken II
12
Objekte und Tabellen (select-statements)
select value(p) from people p
where p.pname = name_t(´Sanchez´,´Jose´,´F´)
Der Objekt-Konstruktor „name_t(´Sanchez´,´Jose´,´F´)“ kann auch innerhalb
der where-Klausel verwendet werden.
Value(p)
-------person_t
Karczewski
(ssno,
-----(023894455,
pname(lname,fname,mi),
---------------------name_t(´Sanchez´,´Jose´,´F´),
Datenbanken II
age)
---30)
13
Objekte und Tabellen (select-statements)
select p.pname, p.age from people p
where p.pname.fname like ‘Pat%‘ and p.age > 50;
“Nested dot”-Notation ist erlaubt. Der Vorname startet mit Pat und das Alter
ist größer als 50.
pname(lname,fname,mi)
--------------------name_t(´Delaney´,´Patrick´,´X´)
Karczewski
age
--59
Datenbanken II
14
Objekte und Tabellen (select-statements)
Wichtig: Man muss immer den vollqualifizierten Namen mit einem Alias
verwenden, um auf strukturierte Attribute zuzugreifen.
select pname.fname from people
pname.fname ist kein Top Level-Attribut -> Der Befehl funktioniert so
nicht.
select people.pname.fname from people
Auch hier muss ein Alias benutzt werden. Das Voranstellen des
Tabellennamen nutzt nichts. -> Der Befehl funktioniert so nicht.
select p.pname.fname from people p;
Korrekte Alternative!
Karczewski
Datenbanken II
15
Objekte und Tabellen (create und insert)
create table scientists of name_t
(primary key (lname));
Generierung der Tabelle mit Namen “scientists” des zuvor definierten ObjektTyps “name_t”.
insert into scientists select p.pname from people p;
Insert mit Einfügen mehrerer Zeilen durch Selektion aller “pnames” von
“people”. In diesem Fall werden alle Namen aus people eingefügt.
insert into scientists values (‘Einstein’, ‘Albert’, ‘E’);
Direktes Einfügen einer Zeile mit dem insert-Befehl.
Karczewski
Datenbanken II
16
Objects and Tables (insert)
update scientists s
set s = name_t(‘Eisenstein’, ‘Andrew’, ‘F’)
where values(s) = name_t(‘Einstein’, ‘Albert’, ‘E’);
Update einer Zeile mit Hilfe des Objekt-Konstruktors name_t.
insert into people
values (123441998, name_t(’Einstein’, ’Albert’, ’E’), 100);
Einfügen einer Zeile in “scientists” mit direkten Werten. Der ObjektKonstruktor name_t ist nötig bei dem strukturierten Attribut.
insert into people values (321341223, null, null);
insert into people (ssno) values (321341223);
Zwei äquivalente Befehle, um unvollständige Zeilen mit NULL-Werten
einzugeben. “pname” und “age” müssen optional sein.
Karczewski
Datenbanken II
17
Objects and Tables (update)
update people p
set p.pname = name_t(‘Gould’, ‘Ben’, null)
where ssno = 321341223;
Update einer Zeile mit NULL-Werten. Der “middle initial” bleibt NULL.
update people p
set p.pname.mi = ‘C’
where ssno = 321341223;
Update des “middle initial” der vorherigen Änderung.
update people p
set p = Person_t(332341223, name_t(‘Gould’, ‘Glen’, ‘A’), 55)
where ssno = 321341223;
Update einer Person mit dem “row object”-Konstruktor. Auch der primary
key ssno darf verändert werden.
Karczewski
Datenbanken II
18
Relational / Objekt-Relational
Produkt:
Karczewski
Nummer
Bezeichnung Funktion
110222
Tee-Service
Gebrauch
106222
Kanne
Gebrauch
201312
Schale
Deko
Datenbanken II
19
Relational / Objekt-Relational
Markt:
Karczewski
Bezeichnung
Standort
Kategorie
Internationaler
Töpfermarkt
Krefeld
Töpfermarkt
Töpfermarkt
Sommerhausen
Sommerhausen
Töpfermarkt
Internationaler
Töpfermarkt
Hanau
Töpfermarkt
Datenbanken II
20
Relational / Objekt-Relational
WAA:
Karczewski
Nummer
Bezeichnung
Standort
110222
Internationaler
Töpfermarkt
Krefeld
106222
Internationaler
Töpfermarkt
Krefeld
201312
Töpfermarkt
Sommerhausen
Sommerhausen
201312
Internationaler
Töpfermarkt
Hanau
Datenbanken II
21
Beispiel ORDB
Markt:
Bezeichnung
Internationaler
Töpfermarkt
Standort
Krefeld
Kategorie
Töpfermarkt
Marktteilnehmer
Nummer
Bezeichnung
Funktion
110222
Tee-Service
Gebrauch
106222
Kanne
Gebrauch
Töpfermarkt
Sommerhausen
Sommerhausen
Töpfermarkt
201312
Schale
Deko
Internationaler
Töpfermarkt
Hanau
Töpfermarkt
201312
Schale
Deko
Karczewski
Datenbanken II
22
Beispiel ORDB
Markt
Bezeichnung
Standort
Kategorie
Collection of Produkt_T
Karczewski
Datenbanken II
23
Beispiel ORDB
Karczewski
Datenbanken II
24
Beispiel ORDB
Karczewski
Datenbanken II
25
Beispiel ORDB
Karczewski
Datenbanken II
26
Beispiel ORDB
Karczewski
Datenbanken II
27
Beispiel ORDB
Karczewski
Datenbanken II
28
Beispiel ORDB
Karczewski
Datenbanken II
29
Beispiel ORDB
Karczewski
Datenbanken II
30
Beispiel ORDB
Karczewski
Datenbanken II
31
Beispiel ORDB
Karczewski
Datenbanken II
32
Beispiel ORDB
Karczewski
Datenbanken II
33
User Defined Functions
User Defined Functions (UDF) können zur Bindung von Funktionen an
Objekte genutzt werden (Methoden-Begriff).
Die Definition von UDFs erfolgt in zwei Schritten:
1. Definition des Funktionskopfes (function header)
2. Definition des Funktionsrumpfes (function body)
Der function header wird zusammen mit den Attributen des Objekttyps
definiert.
Der function body wird definiert mit einem speziellen Kommando mit einer
Referenz zum header.
Karczewski
Datenbanken II
34
User Defined Functions
Beispiel (Personen und ihre Mitarbeiter (dependents)):
create type name_t as object
-- „Create Object Type“
(
lname
varchar(30),
-- last name
mi
char(1),
-- middle initial
fname
varchar(30)-- first name
);
/
create type person_t as object
(
ssno int,
pname
name_t,
age int
);
/
-- must be defined first
create type depPerson_t as table of person_t;
/
Karczewski
Datenbanken II
35
User Defined Functions
Example (Function Header):
create type Employee_t as object
(
ENR
int,
Person
person_t,
depPerson
depPerson_t,
member function NumberOfDep
return integer,
member function BigBoss
return varchar
);
/
Die function header (auch mehrere möglich) werden hinter allen AttributDefinitionen definiert. Die Schlüsselwörter “member function” und
“return” mit entsprechendem Datentyp sind notwendig.
Karczewski
Datenbanken II
36
User Defined Functions
Beispiel (Funktionsrumpf):
Der Rumpf der Funktionen
erhält den selben
Namen wie der zuvor
definierte Objekt-Typ
(Verbindung zwischen
Kopf und Rumpf).
Der Name und return-Typ
muss hier wiederholt
werden.
Innerhalb “begin” und “end”
wird die Funktion
definiert.
Mindestens ein returnBefehl ist nötig.
create type body Employee_t as
member function NumberOfDep
return integer is
begin
return self.depPerson.count;
end NumberOfDep;
member function BigBoss
return varchar is
begin
if self.depPerson.count > 2
then return 'Big Boss';
else return 'Boss';
end if;
end BigBoss;
end;
/
Karczewski
Datenbanken II
37
User Defined Functions
Beispiel (Tabellen-Definition):
create table Employee of Employee_t
(
primary key (ENR)
) nested table depPerson store as dep_tab;
Diese Tabellendefinition realisiert die gewünschte Tabelle.
Der Primärschlüssel kann erst hier definiert werden.
Die “nested table”-Klausel erlaubt die Nutzung von Tabellen
innerhalb der Tabelle.
Möglich sind auch mehrere “nested tables” innerhalb einer Tabelle.
Karczewski
Datenbanken II
38
User Defined Functions
Beispiel (Insertion):
insert into Employee values
(1,
person_t(11, name_t('Josef', 'R', 'Ewing'), 59),
depPerson_T(person_t(33, name_t('Franz', 'X', 'Nonsense'), 33),
person_t(44, name_t('Uschi', 'K', 'Glas'), 48),
person_t(55, name_t('Mika', 'L', 'Most'), 52))
);
insert into Employee values
(2,
person_t(22, name_t('Karla', 'M', 'Hut'), 34),
depPerson_T(person_t(66, name_t('Hans', 'L', 'Moser'), 72),
person_t(77, name_t('Paul', 'A', 'Popp'), 41))
);
Karczewski
Datenbanken II
Das Einfügen
geschieht wie
bisher. Die UDFs
ändern das
bisherige Vorgehen
nicht!
39
User Defined Functions
Beispiel (Selection):
Beispiel (Selection):
select ENR, E.NumberOfDep()
from Employee E;
select ENR, E.BigBoss()
from Employee E;
ENR E.NUMBEROFDEP()
---------- --------------1
3
2
2
ENR E.BIGBOSS()
---------- -----------1
Big Boss
2
Boss
Der Resultat-Wert des Funktionsaufrufs wird als
eigene Spalte ausgegeben.
Die Klammern müssen angegeben werden.
Der alias ist Pflicht!
Karczewski
Datenbanken II
40
Beispiel ORDB (UDF)
Karczewski
Datenbanken II
41
Beispiel ORDB (UDF)
Karczewski
Datenbanken II
42
Beispiel ORDB (Ref)
Produkt
Nummer: int
Bezeichnung: String
Funktion: String
Subprodukt:
collection of Produkt
anzsub: int
… weitere Methoden
zur Stückliste
Karczewski
Datenbanken II
43
Beispiel ORDB (Ref)
Karczewski
Datenbanken II
44
Beispiel ORDB (Ref)
Karczewski
Datenbanken II
45
Beispiel ORDB (Ref)
Karczewski
Datenbanken II
46
Beispiel ORDB (Ref)
Karczewski
Datenbanken II
47
Beispiel ORDB (Ref)
Karczewski
Datenbanken II
48
Beispiel ORDB (Ref)
Karczewski
Datenbanken II
49