Praktikum: Datenbankprogrammierung in SQL/ORACLE

Praktikum:
Datenbankprogrammierung
in
SQL/O RACLE

Prof. Dr. Wolfgang May

Universität Göttingen

Mit Beiträgen von Erik Behrends, Rainer Himmeröder, Marco Koch, Heiko Oberdiek.

0.0 1
 Praktikum: Datenbankprogrammierung in SQL/ORACLE

I NHALT: SQL-3 S TANDARD /ORACLE

• ER-Modellierung
• Schemaerzeugung
• Anfragen
• Views
• Komplexe Attribute, geschachtelte Tabellen
• Optimierung
• Zugriffskontrolle
• Transaktionen
• Updates, Schemaänderungen
• Referentielle Integrität
• PL/SQL: Trigger, Prozeduren, Funktionen
• Objektrelationale Features
• JDBC, SQLJ (Einbindung in Java)
• SQLX: SQL und XML

0.0 Einführung 2
 Praktikum: Datenbankprogrammierung in SQL/ORACLE

D ISKURSWELT: M ONDIAL

• Kontinente
– Wirtschaft
– Bevölkerung • Flüsse
– Sprachen • Seen
– Religionen • Meere
– Ethn. Gruppen • Inseln
• Länder • Berge
• Landesteile • Wüsten
• Städte
• CIA World Factbook • Organisationen
• “Global Statistics”: Länder, Landesteile, Städte
• 1987: Grundidee und Teile der TERRA-Datenbasis des Instituts für Programmstrukturen
und, Datenorganisation der Universität Karlsruhe,
• . . . einige weitere WWW-Seiten,
• Datenintegration mit FLORID in Freiburg, 1998.
• Ergänzungen in 2009.

0.0 Einführung 3
 Praktikum: Datenbankprogrammierung in SQL/ORACLE

TEIL I: Grundlagen
Teil I: Grundlagen
• ER-Modell und relationales Datenmodell
• Umsetzung in ein Datenbankschema: CREATE TABLE
• Anfragen: SELECT – FROM – WHERE
• Arbeiten mit der Datenbank: DELETE, UPDATE

Teil II: Weiteres zum “normalen” SQL

Teil III: Erweiterungen

Prozedurale Konzepte, OO, Einbettung

0.0 Einführung 4
 Praktikum: Datenbankprogrammierung in SQL/ORACLE

Kapitel 1
Semantische Modellierung
E NTITY -R ELATIONSHIP -M ODELL (C HEN , 1976)

Strukturierungskonzepte zur Beschreibung eines Schemas im ERM:

• Entitäts– (entity) Typen (≡ Objekttypen) und
• Beziehungs– (relationship) Typen

Continent Country Organization

Province City Language

River Lake Sea Religion

Island Desert Mountain Ethnic Grp.

1.0 ER-Modell 5
 Praktikum: Datenbankprogrammierung in SQL/ORACLE

E NTITIES UND B EZIEHUNGEN

is_capital

Province in_Prov City

belongs to is_capital

Country encompasses Continent

borders

1.0 ER-Modell 6
 Praktikum: Datenbankprogrammierung in SQL/ORACLE

E NTITIES
Entitätstyp ist durch ein Paar (E, {A1 , . . . , An }) gegeben, wobei E der Name und
{A1 , . . . , An }, n ≥ 0, die Menge der Attribute des Typs ist.
Attribut: Relevante Eigenschaft der Entitäten eines Typs. Jedes Attribut kann Werte aus
einem bestimmten Wertebereich (domain) annehmen.
Entität: besitzt zu jedem Attribut ihres Entitätstyps E einen Wert.
Schlüsselattribute: Ein Schlüssel ist eine Menge von Attributen eines Entitätstyps, deren
Werte zusammen eine eindeutige Identifizierung der Entitäten eines Zustands
gewährleisten soll (siehe auch Schlüsselkandidaten, Primärschlüssel).

1.0 ER-Modell 7
 Praktikum: Datenbankprogrammierung in SQL/ORACLE

E NTITIES :
Germany
356910 D
area name
code

83536115 ent.0815 federal republic

population Country government

gross product inflation

[Link] independence 2%
1871
Feldberg ent.4711 Black Forest
name Mountain mountains
1493.8 47.5
elevation latitude
geo coord 7.5
longitude

1.0 ER-Modell 8
 Praktikum: Datenbankprogrammierung in SQL/ORACLE

B EZIEHUNGEN
Beziehungstyp: Menge gleichartiger Beziehungen zwischen Entitäten; ein Beziehungstyp ist
durch ein Tripel (B, {RO1 : E1 , . . . , ROk : Ek }, {A1 , . . . , An }) gegeben, wobei B der Name,
{RO1 , . . . , ROk }, k ≥ 2, die Menge der sog. Rollen, {E1 , . . . , Ek } die den Rollen
zugeordnete Entitätstypen, und {A1 , . . . , An }, n ≥ 0, die Menge der Attribute des Typs sind.
Rollen sind paarweise verschieden - die ihnen zugeordneten Entitätstypen nicht
notwendigerweise. Falls Ei = Ej für i 6= j, so liegt eine rekursive Beziehung vor.
Attribut: Relevante Eigenschaft der Beziehungen eines Typs.
Beziehung: eines Beziehungstyps B ist definiert durch die beteiligten Entitäten gemäß den B
zugeordneten Rollen; zu jeder Rolle existiert genau eine Entität und zu jedem Attribut von
B genau ein Wert.

1.0 ER-Modell 9
 Praktikum: Datenbankprogrammierung in SQL/ORACLE

B EZIEHUNGEN

City in Country

Freiburg Germany
attributierte Beziehung

continent encompasses Country

Europe Russia
percent
Beziehung mit Rollen 20

City is is_capital of Country

Berlin Germany
rekursive Beziehung (nicht symmetrisch, mit Rollen)
main river
River flowsInto
tributary river
Rhein, Main

1.0 ER-Modell 10
 Praktikum: Datenbankprogrammierung in SQL/ORACLE

B EZIEHUNGSKOMPLEXITÄTEN

Jedem Beziehungstyp ist eine Beziehungskomplexität zugeordnet, die die Mindest- und
Maximalzahl von Beziehungen ausgedrückt, in denen eine Entität eines Typs unter einer
bestimmten Rolle in einem Zustand beteiligt sein darf.
Ein Komplexitätsgrad eines Beziehungstyps B bzgl. einer seiner Rollen RO ist ein Ausdruck
der Form (min, max).
Eine Menge b von Beziehungen erfüllt den Komplexitätsgrad (min, max) einer Rolle RO, wenn
für jedes e des entsprechenden Entity-Typs gilt: es existieren mindestens min und maximal
max Beziehungen in b, in denen e unter der Rolle RO auftritt.

1.0 ER-Modell 11
 Praktikum: Datenbankprogrammierung in SQL/ORACLE

B EZIEHUNGEN

is_capital

< 1, 1 > < 0, ∗ >

Province < 0, ∗ > in_Prov < 1, ∗ > City

< 1, 1 > < 0, 1 >

belongs to is_capital

< 1, 1 >
< 1, ∗ >

Country < 1, ∗ > encompasses < 1, ∗ > Continent

< 0, ∗ > < 0, ∗ >

borders

1.0 ER-Modell 12
 Praktikum: Datenbankprogrammierung in SQL/ORACLE

S CHWACHE E NTITÄTSTYPEN
area pop.
248678 61170500
Ein schwacher Entitätstyp ist ein Entitätstyp name Country code
ohne Schlüssel. BRD D
ent_4711 pop.
< 0, ∗ >

• Schwache Entitätstypen müssen mit 10272069

mindestens einem (starken) Entitäts- area Province name
in
typ in einer n : 1-Beziehung stehen 35751 ent_1997 Baden-W.
(auf der 1-Seite steht der starke En-
< 0, ∗ >

titätstyp). < 1, 1 >

• Sie müssen einen lokalen Schlüs- in Prov.

sel besitzen, d.h. Attribute, die erwei- < 1, 1 >

name City pop.

tert um den Primärschlüssel des be-
treffenden (starken) Entitätstyps einen Freiburg ent_0815 198496
Schlüssel des schwachen Entitätstyps latitude longitude
ergeben (Schlüsselvererbung). 48 7.8
Es gibt z.B. noch ein Freiburg/CH und Freiburg/Elbe, Niedersachsen

1.0 ER-Modell 13
 Praktikum: Datenbankprogrammierung in SQL/ORACLE

AGGREGATION

Ein Fluss mündet in ein Meer/See/Fluss; genauer kann dieser Punkt durch die Angabe eines
oder zweier Länder beschrieben werden.
Die “einfache” Modellierung als dreistellige Beziehung ist wegen den Kardinalitäten nicht
adäquat:
river < 0, n > flows into < 0, n > sea
< 0, n >

Country

Sinnvoll, einen (reifizierten) Aggregattyp Mündung einzuführen:

Mündung
river < 0, 1 >
flows into < 0, n > sea

< 1, 2 >

in
< 0, ∗ >

Country
1.0 ER-Modell 14
 Praktikum: Datenbankprogrammierung in SQL/ORACLE

G ENERALISIERUNG /S PEZIALISIERUNG
• Generalisierung: Flüsse, Seen und Meere bilden die Menge der Gewässer. Diesen können
z.B. mit Städten in einer liegt-an-Beziehung stehen:

name Water < 0, ∗ > located < 0, ∗ > City

River Lake Sea

length depth area depth area

1.0 ER-Modell 15
 Praktikum: Datenbankprogrammierung in SQL/ORACLE

G ENERALISIERUNG /S PEZIALISIERUNG :
• Spezialisierung: M ONDIAL enthält nicht alle geographischen Merkmale, sondern nur
Flüsse, Seen, Meere, Berge, Wüsten und Inseln (keine Tiefländer, Hochebenen,
Steppengebiete, Moore etc). Allen geo-Merkmalen gemeinsam ist, dass sie in einer
in-Beziehung zu Landesteilen stehen:

name Geo < 1, ∗ > in < 0, ∗ > Province

River Lake Sea Mountain Island Desert

1.0 ER-Modell 16
 Praktikum: Datenbankprogrammierung in SQL/ORACLE

Kapitel 2
Das Relationale Modell

• nur ein einziges Strukturierungskonzept Relation für Entitytypen und Beziehungstypen,

• Relationenmodell von Codd (1970): mathematisch fundierte Grundlage: Mengentheorie

2.0 Relationales Modell 17

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

DAS RELATIONALE M ODELL

• ein Relationsschema besteht aus einem Namen sowie einer Menge von Attributen,
Continent: Name, Area
• Jedes Attribut besitzt einen Wertebereich, als Domain bezeichnet. Oft können Attribute
auch Nullwerte annehmen.
Continent: Name: VARCHAR2(25), Area: NUMBER
• Die Elemente einer Relation werden als Tupel bezeichnet.
(Asia,4.5E7)
Ein (relationales) Datenbank-Schema R ist gegeben durch eine (endliche) Menge von
(Relations-)Schemata.
Continent: . . . ; Country: . . . ; City: . . .
Ein (Datenbank)-Zustand ordnet den Relationsschemata eines betrachteten konzeptuellen
Schemas jeweils eine Relation zu.

2.0 Relationales Modell 18

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

A BBILDUNG ERM IN RM
Seien EER ein Entitätstyp und BER ein Beziehungstyp im ERM.
1. Entitätstypen: (EER , {A1 , . . . , An }) −→ E(A1 , . . . , An ),
2. Beziehungstypen:
(BER , {RO1 : E1 , . . . , ROk : Ek }, {A1 , . . . , Am }) −→
B(E1 _K11 , . . . , E1 _K1p1 , . . . ,
Ek _Kk1 , . . . , Ek _Kkpk , A1 , . . . , Am ) ,
wobei {Ki1 , . . . , Kipi } Primärschlüssel von Ei , 1 ≤ i ≤ k.
Falls BER Rollenbezeichnungen enthält, so wird durch die Hinzunahme der
Rollenbezeichnung die Eindeutigkeit der Schlüsselattribute im jeweiligen Beziehungstyp
erreicht.
Für k = 2 können im Falle einer (1,1)-Beziehungskomplexität das Relationsschema des
Beziehungstyps und das Schema des Entitätstyps zusammengefasst werden.
3. Für einen schwachen Entitätstyp müssen die Schlüsselattribute des identifizierenden
Entitätstyps hinzugenommen werden.
4. Aggregattypen können unberücksichtigt bleiben, sofern der betreffende Beziehungstyp
berücksichtigt wurde.

2.0 Relationales Modell 19

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

E NTITÄTSTYPEN

(EER , {A1 , . . . , An }) −→ E(A1 , . . . , An )

name continent area

Asia ent_79110 4.5E7

Continent
Name Area
VARCHAR2(20) NUMBER
Europe 9562489.6
Africa 3.02547e+07
Asia 4.50953e+07
America 3.9872e+07
Australia 8503474.56

2.0 Relationales Modell 20

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

S CHWACHE E NTITÄTSTYPEN

Für einen schwachen Entitätstyp müssen die Schlüsselattribute des identifizierenden Entitätstyps
hinzugenommen werden.

area pop.
248678 61170500
name Country code
BRD D
ent_4711
< 0, ∗ > pop.
10272069
City
in area Province name
35751 Baden-W. Name Country Province Population ...
ent_1997
< 0, ∗ >
Freiburg D Baden-W. 198496 ..
< 1, 1 >
in Prov. Berlin D Berlin 3472009 ..

< 1, 1 >
.. .. .. .. ..

name City pop.

Freiburg ent_0815 198496
latitude longitude
48 7.8
2.0 Relationales Modell 21
 Praktikum: Datenbankprogrammierung in SQL/ORACLE

B EZIEHUNGSTYPEN

(BER , {RO1 : E1 , . . . , ROk : Ek }, {A1 , . . . , Am }) −→

B(E1 _K11 , . . . , E1 _K1p1 , . . . , Ek _Kk1 , . . . , Ek _Kkpk , A1 , . . . , Am ),
wobei {Ki1 , . . . , Kipi } Primärschlüssel von Ei , 1 ≤ i ≤ k.
(man darf aber umbenennen, z.B. Country für [Link])

encompasses
Europe R
Country Continent Percent
name code
VARCHAR2(4) VARCHAR2(20) NUMBER
continent encompasses Country R Europe 20
R Asia 80
percent
D Europe 100
20
... ... ...

2.0 Relationales Modell 22

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

B EZIEHUNGSTYPEN

Für zweistellige Beziehungstypen können im Falle einer (0,1)- oder

(1,1)-Beziehungskomplexität das Relationsschema des Beziehungstyps und das Schema des
Entitätstyps zusammengefasst werden:

name Country code Country

Germany D Name code Population Capital Province ...

< 1, 1 >

Germany D 83536115 Berlin Berlin

is_capital Sweden S 8900954 Stockholm Stockholm

Canada CDN 28820671 Ottawa Quebec
< 0, 1 >
Poland PL 38642565 Warsaw Warszwaskie
Bolivia BOL 7165257 La Paz Bolivia
name City pop.
.. .. .. .. ..
Berlin ent_0815 3472009

2.0 Relationales Modell 23

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

B EZIEHUNGSTYPEN
Falls BER Rollenbezeichnungen enthält, so werden diese als Name der entsprechenden
(Fremdschlüssel)attribute gewählt:

sublanguage
sublang suplang
English Germanic

Language name German Germanic

Alsatian German
< 0, ∗ > < 0, ∗ >
Letzeburgisch German
sup sub
Yiddish German
isSublang
Bahamian Creole English
Jamaican Patois English
Pitkern English
Norfuk English
: :

2.0 Relationales Modell 24

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

Kapitel 3
SQL = Structured Query Language
• Standard-Anfragesprache
• Standardisierung:
SQL-89, SQL-92 (SQL2), SQL:1999 (SQL3), SQL:2003
• SQL2 in 3 Stufen eingeführt (entry, intermediate und full level).
• SQL3: Objektorientierung
• SQL:2003: XML
• deskriptive Anfragesprache
• Ergebnisse immer Mengen von Tupeln (Relationen)
• Implementierungen: ORACLE (im Praktikum), IBM DB2, Microsoft SQL Server,
PostgreSQL, MySQL, etc.

3.0 SQL 25
 Praktikum: Datenbankprogrammierung in SQL/ORACLE

AUFBAU
Datenbanksprache:
DDL: Data Definition Language zur Definition der Schemata
• Tabellen
• Sichten
• Indexe
• Integritätsbedingungen
DML: Data Manipulation Language zur Verarbeitung von DB-Zuständen
• Suchen
• Einfügen
• Verändern
• Löschen
Data Dictionary: Enthält Metadaten über die Datenbank.
(in Tabellen; Anfragen daran werden auch mit der DML gestellt)
... inzwischen gehen SQL-Systeme weit über diese Dinge hinaus.

3.0 SQL 26
 Praktikum: Datenbankprogrammierung in SQL/ORACLE

3.1 Data Dictionary

Besteht aus Tabellen und Views, die Metadaten über die Datenbank enthalten.
⇒ Wenn man sich in eine unbekannte Datenbank einarbeiten soll, oder zusätzlich zur Doku
weitere Informationen benötigt, wird man hier fündig.
Mit SELECT * FROM DICTIONARY (kurz SELECT * FROM DICT) erklärt sich das Data Dictionary
selber.
TABLE_NAME COMMENTS
ALL_ARGUMENTS Arguments in objects accessible to the user
ALL_CATALOG All tables, views, synonyms, sequences accessible to the user
ALL_CLUSTERS Description of clusters accessible to the user
..
.

3.1 Data Dictionary 27

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

DATA D ICTIONARY
ALL_OBJECTS: Enthält alle Objekte, die einem Benutzer zugänglich sind.
ALL_CATALOG: Enthält alle Tabellen, Views und Synonyme, die einem Benutzer zugänglich sind.
ALL_TABLES: Enthält alle Tabellen, die einem Benutzer zugänglich sind.
Analog für diverse andere Dinge (select * from ALL_CATALOG where TABLE_NAME LIKE
’ALL%’;).
USER_OBJECTS: Enthält alle Objekte, die einem Benutzer gehören.
Analog für die anderen, meistens existieren für USER_... auch Abkürzungen, etwa OBJ für
USER_OBJECTS, TABS für USER_TABLES.
ALL_USERS: Enthält Informationen über alle Benutzer der Datenbank.

Jede der Tabellen besitzt mehrere Spalten, die spezifische Informationen über die jeweiligen
Objekte enthalten.

3.1 Data Dictionary 28

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

Data Dictionary: All tables

SELECT table_name FROM tabs ORDER BY 1;

Table_name Table_name Table_name Table_name

AIRPORT ENCOMPASSES ISLAND ORGANIZATION
BORDERS ETHNIC_GROUP ISLANDIN POLITICS
CITY GEO_DESERT IS_MEMBER POPULATION
CITYLOCALNAME GEO_ESTUARY LAKE PROVINCE
CITYPOPS GEO_ISLAND LAKEONISLAND PROVINCELOCALNAME
CONTINENT GEO_LAKE LANGUAGE PROVPOPS
COUNTRY GEO_MOUNTAIN LOCATED RELIGION
COUNTRYLOCALNAME GEO_RIVER LOCATEDON RIVER
COUNTRYPOPS GEO_SEA MERGES_WITH RIVERONISLAND
DESERT GEO_SOURCE MOUNTAIN RIVERTHROUGH
ECONOMY MOUNTAINONISLAND SEA
SUBLANGUAGE

44 Zeilen wurden ausgewählt.

3.1 Data Dictionary 29

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

Data Dictionary: Schema of a Table

Die Definition einzelner Tabellen und Views wird mit DESCRIBE ‹table› oder kurz DESC
‹table› abgefragt:
DESC City;

Name NULL? Typ

NAME NOT NULL VARCHAR2(50)
COUNTRY NOT NULL VARCHAR2(4)
PROVINCE NOT NULL VARCHAR2(50)
POPULATION NUMBER
LATITUDE NUMBER
LONGITUDE NUMBER
ELEVATION NUMBER

3.1 Data Dictionary 30

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

3.2 Anfragen: SELECT-FROM-WHERE

Anfragen an die Datenbank werden in SQL ausschließlich mit dem SELECT-Befehl formuliert.
Dieser hat prinzipiell eine sehr einfache Grundstruktur:
SELECT Attribute
FROM Relation(en)
WHERE Bedingung
Einfachste Form: alle Spalten und Zeilen einer Relation
SELECT * FROM City;
Name C. Province Pop. Lat. Long.
.. .. .. .. .. ..
. . . . . .
Vienna A Vienna 1583000 48.2 16.37
Innsbruck A Tyrol 118000 47.17 11.22
Stuttgart D Baden-W. 588482 48.7 9.1
Freiburg D Germany 198496 NULL NULL
.. .. .. .. .. ..
. . . . . .
3114 Zeilen wurden ausgewählt.

3.2 SQL: Anfragen 31

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

A LLGEMEINE S YNTAKTISCHE H INWEISE

• SQL ist case-insensitive, d.h. CITY=city=City=cItY.
(Ausnahmen siehe Folie 84)
• Innerhalb von Quotes ist SQL nicht case-insensitive, d.h. City=’Berlin’ 6= City=’berlin’.
• String-Konstanten in der WHERE-Klausel werden in einfache Anführungszeichen
eingeschlossen, nicht in doppelte.
(doppelte Anführungszeichen machen etwas anderes, siehe Folie 84)
• Jeder Befehl wird mit einem Strichpunkt “;” abgeschlossen.
• Kommentarzeilen werden in /∗ . . . ∗/ eingeschlossen, oder mit -- oder rem eingeleitet.

3.2 SQL: Anfragen 32

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

P ROJEKTIONEN : AUSWAHL VON S PALTEN

SELECT ‹attr-list›
FROM ‹table›;

Gebe zu jeder Stadt ihren Namen und das Land, in dem sie liegt, aus.
SELECT Name, Country
FROM City;

Name COUNTRY
Tokyo J
Stockholm S
Warsaw PL
Cochabamba BOL
Hamburg D
Berlin D
.. ..

3.2 SQL: Anfragen 33

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

DISTINCT

SELECT * FROM Island; Name Islands Area ...

.. .. .. ..
. . . .
Jersey Channel Islands 117 ...
Mull Inner Hebrides 910 ...
Montserrat Lesser Antilles 102 ...
Grenada Lesser Antilles 344 ...
.. .. .. ..
. . . .

SELECT Islands Islands SELECT DISTINCT Islands Islands

FROM Island; .. FROM Island; ..
. .
Channel Islands Channel Islands
Inner Hebrides Inner Hebrides
Lesser Antilles Lesser Antilles
Lesser Antilles ..
.
..
.

3.2 SQL: Anfragen 34

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

D UPLIKATELIMINIERUNG
• Duplikateliminierung nicht automatisch:
– Duplikateliminierung teuer (Sortieren + Eliminieren)
– Nutzer will Duplikate sehen
– später: Aggregatfunktionen auf Relationen mit Duplikaten
• Duplikateliminierung: DISTINCT-Klausel
• später: Duplikateliminierung automatisch bei Anwendung der Mengenoperatoren UNION,
INTERSECT, ...

3.2 SQL: Anfragen 35

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

S ELEKTIONEN : AUSWAHL VON Z EILEN

SELECT ‹attr-list›
FROM ‹table›
WHERE ‹predicate›;
<predicate> kann dabei die folgenden Formen annehmen:
• <attribute> <op> <value> mit op ∈ {=, <, >, <=, >=},
• <attribute> [NOT] LIKE <string>, wobei underscores im String genau ein beliebiges
Zeichen repräsentieren und Prozentzeichen null bis beliebig viele Zeichen darstellen,
• <attribute> IN <value-list>, wobei <value-list> entweder von der Form (’val1 ’,. . . ,’valn ’) ist,
oder durch eine Subquery bestimmt wird,
• [NOT] EXISTS ‹subquery›
• NOT (<predicate>),
• <predicate> AND <predicate>,
• <predicate> OR <predicate>.

3.2 SQL: Anfragen 36

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

Selektion (WHERE) und Projektion (SELECT): Beispiele

Beispiel:

SELECT Name, Country, Population Name Country Population

FROM City Tokyo J 7843000
WHERE Country = ’J’;
Kyoto J 1415000
Hiroshima J 1099000
Yokohama J 3256000
Sapporo J 1748000
.. .. ..
. . .

Beispiel:

SELECT Name, Country, Population Name Country Population

FROM City Tokyo J 7843000
WHERE Country = ’J’ AND Population > 2000000
Yokohama J 3256000

3.2 SQL: Anfragen 37

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

Selektion (WHERE) und Projektion (SELECT): Beispiele

Beispiel:

SELECT Name, Country, Population Name Country Population

FROM City Kingston JA 101000
WHERE Country LIKE ’%J_%’;
Amman JOR 777500
Suva FJI 69481
.. .. ..
. . .
Die Forderung, dass nach dem J noch ein weiteres Zeichen folgen muss, führt dazu, dass die
japanischen Städte nicht aufgeführt werden.

3.2 SQL: Anfragen 38

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

ORDER BY

SELECT Name, Country, Population

FROM City
WHERE Population > 5000000
ORDER BY Population DESC; (absteigend)

Name Country Population

Seoul ROK 10.229262
Mumbai IND 9.925891
Karachi PK 9.863000
Mexico MEX 9.815795
Sao Paulo BR 9.811776
Moscow R 8.717000
.. .. ..
. . .

3.2 SQL: Anfragen 39

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

ORDER BY, A LIAS

SELECT Name, Population/Area AS Density

FROM Country
ORDER BY 2 ; (Default: aufsteigend)

Name Density
Western Sahara ,836958647
Mongolia 1,59528243
French Guiana 1,6613956
Namibia 2,03199228
Mauritania 2,26646745
Australia 2,37559768

3.2 SQL: Anfragen 40

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

AGGREGATFUNKTIONEN
• COUNT (*| [DISTINCT] ‹attribute›)
• MAX (‹attribute›)
• MIN (‹attribute›)
• SUM ([DISTINCT] ‹attribute›)
• AVG ([DISTINCT] ‹attribute›)
Beispiel: Ermittle die Anzahl der in der DB abgespeicherten Städte.

SELECT Count (*) Count(*)

FROM City; 3064
Beispiel: Ermittle die Anzahl der Länder, für die Millionenstädte abgespeichert sind.

SELECT Count (DISTINCT Country) Count(DISTINCT(Country))

FROM City 68
WHERE Population > 1000000;

3.2 Aggregatfunktionen 41
 Praktikum: Datenbankprogrammierung in SQL/ORACLE

AGGREGATFUNKTIONEN

Beispiel: Ermittle die Gesamtsumme aller Einwohner von Städten Österreichs sowie die
Einwohnerzahl der größten Stadt Österreichs.

SELECT SUM(Population), MAX(Population) SUM(Population) MAX(Population)

FROM City 2434525 1583000
WHERE Country = ’A’;

Und was ist, wenn man diese Werte für jedes Land haben will??

3.2 Aggregatfunktionen 42
 Praktikum: Datenbankprogrammierung in SQL/ORACLE

G RUPPIERUNG
GROUP BY berechnet für jede Gruppe eine Zeile, die Daten enthalten kann, die mit Hilfe der
Aggregatfunktionen über mehrere Zeilen berechnet werden.
SELECT ‹expr-list›
FROM ‹table›
WHERE ‹predicate›
GROUP BY ‹attr-list›;
gibt für jeden Wert von ‹attr-list› eine Zeile aus. Damit darf ‹expr-list› nur
• Konstanten,
• Attribute aus ‹attr-list›,
• Attribute, die für jede solche Gruppe nur einen Wert annehmen (etwa Code, wenn
‹attr-list› Country ist),
• Aggregatfunktionen, die dann über alle Tupels in der entsprechenden Gruppe gebildet
werden,
enthalten.
Die WHERE-Klausel ‹predicate› enthält dabei nur Attribute der Relationen in ‹table› (also
keine Aggregatfunktionen).

3.2 Gruppierung 43
 Praktikum: Datenbankprogrammierung in SQL/ORACLE

G RUPPIERUNG

Beispiel: Gesucht sei für jedes Land die Gesamtzahl der Einwohner, die in den gespeicherten
Städten leben.

SELECT Country, Sum(Population) Country SUM(Population)

FROM City A 2434525
GROUP BY Country;
AFG 892000
AG 36000
AL 475000
AND 15600
.. ..
. .

3.2 Gruppierung 44
 Praktikum: Datenbankprogrammierung in SQL/ORACLE

B EDINGUNGEN AN G RUPPIERUNGEN

Die HAVING-Klausel ermöglicht es, Bedingungen an die durch GROUP BY gebildeten Gruppen zu
formulieren:
SELECT ‹expr-list›
FROM ‹table›
WHERE ‹predicate1›
GROUP BY ‹attr-list›
HAVING ‹predicate2›;
• WHERE-Klausel: Bedingungen an einzelne Tupel bevor gruppiert wird,
• HAVING-Klausel: Bedingungen, nach denen die Gruppen zur Ausgabe ausgewählt werden.
In der HAVING-Klausel dürfen neben Aggregatfunktionen nur Attribute vorkommen, die
explizit in der GROUP BY-Klausel aufgeführt wurden.

3.2 Gruppierung 45
 Praktikum: Datenbankprogrammierung in SQL/ORACLE

B EDINGUNGEN AN G RUPPIERUNGEN

Beispiel: Gesucht ist für jedes Land die Gesamtzahl der Einwohner, die in den gespeicherten
Städten mit mehr als 10000 Einwohnern leben. Es sollen nur solche Länder ausgegeben
werden, bei denen diese Summe größer als zehn Millionen ist.

SELECT Country, SUM(Population) Country SUM(Population)

FROM City AUS 12153500
WHERE Population > 10000
BR 77092190
GROUP BY Country
HAVING SUM(Population) > 10000000; CDN 10791230
CO 18153631
.. ..
. .

3.2 Gruppierung 46
 Praktikum: Datenbankprogrammierung in SQL/ORACLE

G ESCHACHTELTE AGGREGATIONSOPERATOREN

Welche Organisation hat die meisten Mitglieder?

• erst gruppieren:

SELECT organization, COUNT(*) Organization COUNT(*)

FROM ismember UN 193
GROUP BY organization;
BeNeLux 3
EU 32
UPU 226
.. ..
. .
• nur die zweite Spalte nehmen, MAX bilden:
SELECT MAX(COUNT(*)) Oracle: 226
FROM ismember Postgres: ERROR: aggregate function
GROUP BY organization; calls cannot be nested

3.2 Gruppierung 47
 Praktikum: Datenbankprogrammierung in SQL/ORACLE

Geschachtelte Aggregationsoperatoren

• Oracle:
SELECT organization, COUNT(*) Organization COUNT(*)
FROM ismember
UPU 226
GROUP BY organization
HAVING COUNT(*) = ( SELECT max(count(*))
FROM ismember
GROUP by organization );

• Postgres: mit “> ALL” oder Subquery in der FROM-Klausel:

SELECT organization, COUNT(*)
SELECT organization, COUNT(*)
FROM ismember FROM ismember
GROUP BY organization GROUP BY organization
HAVING COUNT(*) >= HAVING COUNT(*) =
ALL ( SELECT count(*) ( SELECT max(num)
FROM ismember FROM ( SELECT count(*) as num
GROUP by organization ); FROM ismember
GROUP by organization ) bla);

3.2 Gruppierung 48
 Praktikum: Datenbankprogrammierung in SQL/ORACLE

M ENGENOPERATIONEN

SQL-Anfragen können über Mengenoperatoren verbunden werden:

‹select-clause› ‹mengen-op› ‹select-clause›;
• UNION [ALL]
• MINUS [ALL]
• INTERSECT [ALL]
• automatische Duplikateliminierung (kann verhindert werden mit ALL)
Beispiel: Gesucht seien diejenigen Städtenamen, die auch als Namen von Ländern in der
Datenbank auftauchen.

(SELECT Name Name

FROM City) Armenia
INTERSECT
Djibouti
(SELECT Name
FROM Country); Guatemala
..
.

3.2 Mengenoperationen 49
 Praktikum: Datenbankprogrammierung in SQL/ORACLE

3.3 Join-Anfragen
Eine Möglichkeit, mehrere Relationen in eine Anfrage einzubeziehen, sind Join-Anfragen.
SELECT <attr-list›
FROM ‹table-list›
WHERE ‹predicate›;
Prinzipiell kann man sich einen Join als kartesisches Produkt der beteiligten Relationen
vorstellen (Theorie: siehe Vorlesung).
• Attributmenge: Vereinigung aller Attribute
• ggf. durch ‹table›.‹attr› qualifiziert.
• Join “mit sich selbst” – Aliase.

3.3 Join-Anfragen 50
 Praktikum: Datenbankprogrammierung in SQL/ORACLE

Join-Anfragen

Beispiel: Alle Länder, die weniger Einwohner als Tokyo haben.

SELECT [Link], [Link] Name Population

FROM City, Country Albania 3249136
WHERE [Link] = ’Tokyo’
Andorra 72766
AND [Link] < [Link];
Liechtenstein 31122
Slovakia 5374362
Slovenia 1951443
.. ..
. .

3.3 Join-Anfragen 51
 Praktikum: Datenbankprogrammierung in SQL/ORACLE

E QUIJOIN

Beispiel: Es soll für jede politische Organisation festgestellt werden, in welchem Erdteil sie
ihren Sitz hat.
encompasses: Country, Continent, Percentage.
Organization: Abbreviation, Name, City, Country, Province.

SELECT Continent, Abbreviation Continent Abbreviation

FROM encompasses, Organization America UN
WHERE [Link] = [Link];
Europe UNESCO
Europe CCC
Europe EU
America CACM
Australia/Oceania ANZUS
.. ..
. .

3.3 Join-Anfragen 52
 Praktikum: Datenbankprogrammierung in SQL/ORACLE

V ERBINDUNG EINER R ELATION MIT SICH SELBST

Beispiel: Ermittle alle Städte, die in anderen Ländern Namensvettern haben.

SELECT [Link], [Link], [Link] [Link] [Link] [Link]

FROM City A, City B Alexandria ET RO
WHERE [Link] = [Link]
Alexandria ET USA
AND [Link] < [Link];
Alexandria RO USA
Barcelona E YV
Valencia E YV
Salamanca E MEX
.. .. ..
. . .

3.3 Join-Anfragen 53
 Praktikum: Datenbankprogrammierung in SQL/ORACLE

S YNTACTIC S UGAR : J OIN

• bisher: SELECT ... FROM ... WHERE ‹(join-)conditions›
• abkürzend:
SELECT ... FROM ‹joined-tables-spec›
WHERE ‹conditions›
mit ‹joined-tables-spec›:
• kartesisches Produkt:
SELECT ...
FROM ‹table_1› CROSS JOIN ‹table_2›[ CROSS JOIN ‹table_3›... ]
WHERE ...
• natürliches Join (über alle gemeinsamen Spaltennamen):
SELECT ...
FROM ‹table_1› NATURAL JOIN ‹table_2›[ NATURAL JOIN ‹table_3›... ]
WHERE ...
Beispiel: Alle Paare (Fluss, See), die in derselben Provinz liegen:
SELECT country, province, river, lake, sea
FROM geo_river NATURAL JOIN geo_lake
NATURAL JOIN geo_sea;

3.3 Join-Anfragen 54
 Praktikum: Datenbankprogrammierung in SQL/ORACLE

Syntactic Sugar: Join (Cont’d)

• inneres Join mit Angabe der Join-Bedingungen:

SELECT ...
FROM ‹table_1› [INNER] JOIN ‹table_2›
ON ‹conditions›
WHERE ‹more conditions›
SELECT code, [Link]
FROM country x JOIN city y
ON [Link]=[Link] AND [Link]=[Link] AND
[Link] = [Link] AND
[Link] < 4 * [Link];
• kein wesentlicher Vorteil gegenüber SFW.
• kann links-vor-rechts-verkettet werden:
SELECT ...
FROM ‹table_1›
[INNER] JOIN ‹table_2› ON ‹conditions_12 ›
: [INNER] JOIN ‹table_i›ON ‹conditions_12..i-1›
WHERE ‹more conditions›

3.3 Join-Anfragen 55
 Praktikum: Datenbankprogrammierung in SQL/ORACLE

Nicht nur Syntactic Sugar: Outer Join

• äußeres Join:
SELECT ...
FROM ‹table_1›
[LEFT | RIGHT | FULL] OUTER JOIN ‹table_2›
ON ‹conditions›
WHERE ‹more conditions›
SELECT [Link], [Link]
FROM river r FULL OUTER JOIN lake l
ON [Link] = [Link];
deutlich kürzer und klarer als SFW mit UNION um das Outer Join zu umschreiben.

3.3 Join-Anfragen 56
 Praktikum: Datenbankprogrammierung in SQL/ORACLE

3.4 Subqueries
In der WHERE-Klausel können Ergebnisse von Unterabfragen verwendet werden:
SELECT ‹attr-list›
FROM ‹table›
WHERE ‹attribute› ‹op› [ANY|ALL] ‹subquery›;
SELECT ‹attr-list›
FROM ‹table›
WHERE ‹attribute› IN ‹subquery›;
• ‹subquery› ist eine SELECT-Anfrage (Subquery),
• für ‹op› ∈ {=, <, >, <=, >=} muss ‹subquery› eine einspaltige Ergebnisrelation liefern,
mit deren Werten der Wert von ‹attribute› verglichen wird.
• für IN ‹subquery› sind auch mehrspaltige Ergebnisrelationen erlaubt.
• für ‹op› ohne ANY oder ALL muss das Ergebnis von ‹subquery› einzeilig sein.

3.4 Subqueries 57
 Praktikum: Datenbankprogrammierung in SQL/ORACLE

U NKORRELIERTE S UBQUERIES
• unabhängig von den Werten des in der umgebenden Anfrage verarbeiteten Tupels,
• wird vor der umgebenden Anfrage einmal ausgewertet,
• das Ergebnis wird bei der Auswertung der WHERE-Klausel der äußeren Anfrage verwendet,
• streng sequentielle Auswertung, daher ist eine Qualifizierung mehrfach vorkommender
Attribute nicht erforderlich.

... mit einem einzelnen Wert als Ergebnis der Subquery:

Beispiel: Alle Länder, die weniger Einwohner als Tokyo haben.

SELECT [Link], [Link]

FROM Country
WHERE Population <
(SELECT Population
FROM City
WHERE Name = ’Tokyo’);

3.4 Subqueries 58
 Praktikum: Datenbankprogrammierung in SQL/ORACLE

U NKORRELIERTE S UBQUERIES

... mit einem mehrzeiligen Ergebnis der Subquery und IN:

(meistens werden Mengen von (Fremd)Schlüsseln berechnet)
Beispiel: Bestimme alle Länder, in denen es eine Stadt namens Victoria gibt:

SELECT Name [Link]

FROM Country Canada
WHERE Code IN
Malta
(SELECT Country
Seychelles
FROM City
WHERE Name = ’Victoria’);

3.4 Subqueries 59
 Praktikum: Datenbankprogrammierung in SQL/ORACLE

U NKORRELIERTE S UBQUERY MIT MEHRSPALTIGEM IN

(mehrspaltige (Fremd)Schlüssel)

Beispiel: Alle Städte, von denen bekannt ist, dass sie an einem Gewässer liegen:

SELECT * Name Country Province Population ...

FROM CITY Ajaccio F Corse 53500 ...
WHERE (Name,Country,Province) Karlstad S Värmland 74669 ...
IN (SELECT City,Country,Province San Diego USA California 1171121 ...
FROM located); .. .. .. .. ..
. . . . .

3.4 Subqueries 60
 Praktikum: Datenbankprogrammierung in SQL/ORACLE

S UBQUERY MIT ALL

Beispiel: ALL ist z.B. dazu geeignet, wenn man alle Länder bestimmen will, die kleiner als alle
Staaten sind, die mehr als 10 Millionen Einwohner haben:

SELECT Name,Area,Population Name Area Population

FROM Country Albania 28750 3249136
WHERE Area < ALL
Macedonia 25333 2104035
(SELECT Area
Andorra 450 72766
FROM Country .. .. ..
. . .
WHERE Population > 10000000);
Alternative:
... WHERE Area < (SELECT min(area) FROM ...)

3.4 Subqueries 61
 Praktikum: Datenbankprogrammierung in SQL/ORACLE

KORRELIERTE S UBQUERY
• Subquery ist von Attributwerten des gerade von der umgebenden Anfrage verarbeiteten
Tupels abhängig,
• wird für jedes Tupel der umgebenden Anfrage einmal ausgewertet,
• Qualifizierung der importierten Attribute erforderlich.
Beispiel: Es sollen alle Städte bestimmt werden, in denen mehr als ein Viertel der Bevölkerung
des jeweiligen Landes wohnt.

SELECT Name, Country Name Country

FROM City Copenhagen DK
WHERE Population * 4 > Tallinn EW
(SELECT Population Vatican City V
FROM Country Reykjavik IS
WHERE Code = [Link]); Auckland NZ
.. ..
. .

3.4 Subqueries 62
 Praktikum: Datenbankprogrammierung in SQL/ORACLE

D ER EXISTS-O PERATOR

EXISTS bzw. NOT EXISTS bilden den Existenzquantor nach.

SELECT ‹attr-list›
FROM ‹table›
WHERE [NOT] EXISTS
(‹select-clause›);
Beispiel: Gesucht sind die Namen derjenigen Länder, für die Städte mit mehr als einer Million
Einwohnern in der Datenbasis abgespeichert sind.

SELECT Name Name

FROM Country Serbia
WHERE EXISTS
France
( SELECT *
Spain
FROM City ..
.
WHERE Population > 1000000
AND [Link] = [Link]) ;

3.4 Subqueries 63
 Praktikum: Datenbankprogrammierung in SQL/ORACLE

U MFORMUNG EXISTS, S UBQUERY, J OIN

Äquivalent dazu sind die beiden folgenden Anfragen:

SELECT Name
FROM Country
WHERE Code IN
( SELECT Country
FROM City
WHERE [Link] > 1000000);

SELECT DISTINCT [Link]

FROM Country, City
WHERE [Link] = [Link]
AND [Link] > 1000000;

Hinweis: Diese Äquivalenzumformung ist so nur für nicht-negiertes EXISTS möglich.

3.4 Subqueries 64
 Praktikum: Datenbankprogrammierung in SQL/ORACLE

S UBQUERIES MIT NOT EXISTS

Beispiel: Gesucht seien diejenigen Länder, für die keine Städte mit mehr als einer Million
Einwohnern in der Datenbasis abgespeichert sind.

SELECT Name
FROM Country
WHERE NOT EXISTS
( SELECT *
FROM City
WHERE Population > 1000000
AND [Link] = [Link]) ;
Eine äquivalente Anfrage ohne Subquery müsste mit MINUS und einem der obigen gebildet
werden.
(vgl. Umformungen in relationale Algebra)

3.4 Subqueries 65
 Praktikum: Datenbankprogrammierung in SQL/ORACLE

S UBQUERIES IN DER FROM-Z EILE

Eine Subquery kann überall auftreten, wo eine Relation/Tabelle stehen kann.

SELECT ‹attr-list›
FROM ‹table/subquery-list›
WHERE ‹condition›;

Tabellen oder Werte, die auf unterschiedliche Weise zusammengestellt oder berechnet werden,
können in Beziehung zueinander gestellt werden.

Hinweis: dies ist (neben dem Mengenoperatoren UNION, INTERSECT und MINUS/EXCEPT)
die einzige Form, in der Subqueries in der relationalen Algebra verwendet werden können – als
Teilterme.

3.4 Subqueries 66
 Praktikum: Datenbankprogrammierung in SQL/ORACLE

S UBQUERIES IN DER FROM-Z EILE

• Aliase für die Zwischenergebnis-Tabellen
Beispiel: Gesucht sind alle Paare (Land,Organisation), so dass das Land mehr als 50 Millionen
Einwohner hat und in einer Organisation mit mindestens 20 Mitgliedern Mitglied ist.

SELECT [Link], [Link]

FROM
(SELECT Name, Code
FROM Country
WHERE Population > 50000000) c,
isMember,
(SELECT organization
FROM isMember
GROUP BY organization
HAVING count(*) > 20) org
WHERE [Link] = [Link]
AND [Link] = [Link];

3.4 Subqueries 67
 Praktikum: Datenbankprogrammierung in SQL/ORACLE

S UBQUERIES IN DER FROM-Z EILE

• inbesondere geeignet, um geschachtelte Berechnungen mit Aggregatfunktionen

durchzuführen:

Beispiel: Berechnen Sie die Anzahl der Menschen, die in der größten Stadt ihres Landes
leben.

SELECT sum(pop_biggest) sum(pop_biggest)

FROM (SELECT country, max(population) as pop_biggest 274439623
FROM City
GROUP BY country);

3.4 Subqueries 68
 Praktikum: Datenbankprogrammierung in SQL/ORACLE

S UBQUERIES IN DER FROM-Z EILE

• Berechnung von einzelnen Zwischenergebnissen zur Weiterverwendung

Beispiel: Gesucht ist die Zahl der Menschen, die nicht in den gespeicherten Städten leben,
sowie deren Anteil.
SELECT Population, Urban_Residents,
Urban_Residents/Population AS relativ
FROM
(SELECT SUM(Population) AS Population
FROM Country),
(SELECT SUM(Population) AS Urban_Residents
FROM City);

population urban_residents relativ

5761875727 1120188570 .194413872

3.4 Subqueries 69
 Praktikum: Datenbankprogrammierung in SQL/ORACLE

S UBQUERIES IN DER SELECT-Z EILE

... eine Subquery, die einen einzelnen Wert ergibt, kann auch statt einer Konstanten in der
SELECT-Zeile stehen:
(die einelementige Dummy-Tabelle “dual” kann man immer nehmen, wenn man eigentlich keine
FROM-Zeile benötigen würde)
Beispiel: Gesucht ist die Zahl der Menschen, die nicht in den gespeicherten Städten leben.

SELECT (SELECT SUM(Population) FROM Country) - SELECT(...)-SELECT(...)

(SELECT SUM(Population) FROM City) 5373712954
FROM dual

3.4 Subqueries 70
 Praktikum: Datenbankprogrammierung in SQL/ORACLE

WITH: A D - HOC V IEWS ALS BENANNTE S UBQUERIES

• “subquery factoring”
• Subqueries separat entwickeln und schreiben
• mehrfach verwendbar
WITH ‹name1 › AS (‹subquery1 ›), ..., ‹namen › AS (‹subqueryn ›)
‹select-query›
• ‹name1 ›, . . . , ‹namen › in ‹select-query› als Tabellennamen verwendbar.
Beispiel
WITH europcountries AS
tokiopop ist eine einspaltige, einele-
(SELECT * FROM country
mentige Tabelle:
WHERE code IN
... WHERE population > tokiopop
(SELECT country FROM encompasses
ist nicht erlaubt!
WHERE continent=’Europe’)),
tokiopop AS
(SELECT population FROM city WHERE name=’Tokyo’)
SELECT name
FROM europcountries
WHERE population > (SELECT population FROM tokiopop);
3.4 Subqueries 71
 Praktikum: Datenbankprogrammierung in SQL/ORACLE

N ULLWERTE
• Wert ist nicht vorhanden, nicht bekannt, nicht definiert,
• Tatsächliche Bedeutung ist anwendungsabhängig,
• Abfrage: WHERE ... IS [NOT] NULL
SELECT * FROM City WHERE population IS NULL;
• Nullwerte erfüllen keine (Vergleichs)bedingungen (insbesondere auch keine
Join-Gleicheitsbedingung):
SELECT [Link], [Link], [Link]
FROM City c1, City c2
WHERE [Link] = [Link]
AND [Link] <> [Link] ORDER BY 3;
• Nullwerte werden bei ORDER BY als größte Werte angesehen. Mit NULLS LAST|FIRST
kann man dies (passend zu ASC|DESC) beeinflussen:
SELECT name, population FROM city
ORDER BY population [ASC|DESC] [NULLS LAST|FIRST];

3.4 SQL-Anfragen - Diverses 72

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

Nullwerte (Cont’d)

• Nullwerte werden in Aggregationsoperatoren (SUM, COUNT, ...) ignoriert:

SELECT AVG(population) FROM city
WHERE province=’Hawaii’;
• Sonstige Operationen mit NULL ergeben NULL:
SELECT 1 + NULL FROM DUAL => NULL
• Mit der Funktion nvl(attr, wert) kann vorgegeben werden, mit was anstelle von NULL
gerechnet werden soll:
SELECT AVG(nvl(population,0)) FROM city
WHERE province=’Hawaii’;

SELECT 1 + nvl(NULL,2) FROM DUAL => 3

3.4 SQL-Anfragen - Diverses 73

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

F UNKTIONALES CASE WHEN ... THEN ... END-KONSTRUKT

• Fallunterscheidung mit funktionaler Semantik:
nicht prozedurales “if ... then do something”, sondern
• “result = (if cond then expr1 else expr2 )”
select country, name,
case when population > 1000000 then ’big’
when population < 100000 then ’small’
else ’medium’ end
as size
from city

Name Country Size

Frankfurt D medium
Berlin D big
Göttingen D medium
Andorra AND small
: :

3.4 SQL-Anfragen - Diverses 74

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

CASE WHEN ... for Aggregations

• very useful for statistics that should distinguish between cases

Example

select country,
count(case when population >= 1000000 then 1 end) as big,
count(case when population between 100000 and 999999
then 1 end) as medium,
count(case when population < 100000 then 1 end) as small
from city
group by country

Country big medium small

D 7 72 9
CH 0 6 4
: : : :

3.4 SQL-Anfragen - Diverses 75

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

R EKURSIVE A NFRAGEN : CONNECT BY

• Rekursion/Iteration in der relationalen Algebra nicht möglich
• für transitive Hülle und Durchlaufen von Eltern-Kind-Relationen benötigt
SQL: CONNECT BY
• mehrfaches Join einer Relation mit sich selbst:
R ⊲⊳ [Bedingung]R . . . ⊲⊳ [Bedingung]R ⊲⊳ [Bedingung]R
• z.B. für R = borders oder R = river[name,river]
SELECT ...
FROM <relation>
[ START WITH <initial-condition> ]
CONNECT BY [ NOCYCLE ] <recurse-condition>
• ‹relation› kann eine Tabelle, ein View, oder eine Subquery sein,
• ‹initial-condition› ist eine Bedingung, die das oder die Anfangstupel (“root”) auswählt,
• ‹recurse-condition› spezifiziert die Join-Bedingung zwischen Eltern- und Kindtupel,
PRIOR ‹columnnname›, um Bezug zum “Elterntupel” zu nehmen,
• LEVEL: Pseudospalte, die für jedes Tupel die Rekursionsebene angibt

3.4 SQL-Anfragen - Diverses 76

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

CONNECT BY: Beispiel

Transitive Hülle von River mit der Vorschrift:
River R1 ⊲⊳[R1 .name = R2 .river] River R2
Beispiel: Alle Flüsse, die in den Congo fliessen:

SELECT level, name, length Level Name Length

FROM river 1 Congo 4374
START WITH name = ’Congo’
: : :
CONNECT BY PRIOR name = river;
2 Kasai 2153
3 Cuilo 970
4 Cuango 1100
3 Fimi 200
4 Lukenie 900
: : :
Das Ergebnis ist eine Relation, die man natürlich auch wieder als Subquery irgendwo einsetzen
kann.
Hinweis: hier fehlen Flüsse, die über einen See in den Congo fliessen (Aufgabe).

3.4 SQL-Anfragen - Diverses 77

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

Oracle: weitere Funktionalität zu CONNECT BY

SELECT level, name, length

FROM river
START WITH sea is not null -- rivers flowing into seas
CONNECT BY PRIOR name = river;
Aber wer gehört zu wem? – Zugriff über SELECT:
• connect_by_root ‹columnname›: Operator um auf Spalten des Start-Tupels zuzugreifen,
• connect_by_isleaf: true/false wenn das erreichte Tupel ein Blatt (“Ende”) ist,
• sys_connect_by_path(‹columnname›,‹char›): Pfad als String ausgeben.
SELECT level, name AS Fluss1, length,
connect_by_root name AS Fluss2,
connect_by_isleaf AS IstQuellfluss,
connect_by_root sea || sys_connect_by_path(name,’<-’) AS Pfad
FROM river Level Fluss1 Länge Fluss2 QF Pfad
START WITH sea IS NOT NULL
3 Leine 281 Weser 1 North Sea←Weser←Aller
CONNECT BY PRIOR name = river;
: : : : : ←Leine

3.4 SQL-Anfragen - Diverses 78

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

I NTERNE AUSWERTUNG UND O PTIMIERUNG

. . . macht das Datenbanksystem automatisch: algebraische Äquivalenzumformungen,

Erstellung und Benutzung von Indexen und Statistiken (Wertverteilungen etc.).

Auswertungsplan

• Abfolge interner algebraischer Operatoren (auf einer niedrigeren Ebene als die relationale
Algebra; vgl. DB-Vorlesungsabschitt zu Join-Algorithmen):
– table full scan
– table index lookup (select * from country where code=’D’)
– full join
– hash join
– merge join
– index-based join
– etc.

3.4 SQL-Anfragen - Diverses 79

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

AUSWERTUNGSPLAN ANSCHAUEN
• SQL Developer: Anfrage angeben und auf das 3. oder 4. Icon (Autotrace, Explain Plan)
klicken. Stellt das Ergebnis in Tabellenform mit Schritten und erwarteten Kosten dar.
• sqlplus: SET AUTOTRACE ON.
Danach wird nach jedem Anfrageergebnis der Auswertungsplan angegeben.
• sqlplus: explain plan for select ... from ... where ...
schreibt den Auswertungsplan in eine interne Tabelle:
select substr (lpad(’ ’, level-1) || operation ||
’ (’ || options || ’)’,1,30 ) as "Operation",
object_name as "Object",
cost, bytes, cardinality as "Rows", time
from plan_table
start with id = 0
connect by prior id=parent_id;
[Filename: PLSQL/[Link]]
• vor dem nächsten EXPLAIN PLAN sollte man
DELETE FROM PLAN_TABLE
machen.

3.4 SQL-Anfragen - Diverses 80

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

Kapitel 4
Schema-Definition

• das Datenbankschema umfasst alle Informationen über die Struktur der Datenbank,
• Tabellen, Views, Constraints, Indexe, Cluster, Trigger ...
• objektrelationale DB: Datentypen, ggf. Methoden
• wird mit Hilfe der DDL (Data Definition Language) manipuliert,
• CREATE, ALTER und DROP von Schemaobjekten,
• Vergabe von Zugriffsrechten: GRANT.

4.0 Schema-Definition 81
 Praktikum: Datenbankprogrammierung in SQL/ORACLE

E RZEUGEN VON TABELLEN

CREATE TABLE ‹table›

(‹col› ‹datatype›,
.
.
.
‹col› ‹datatype›)

CHAR(n): Zeichenkette fester Länge n.

VARCHAR2(n): Zeichenkette variabler Länge ≤ n.
||: Konkatenation von Strings.
NUMBER: Zahlen. Auf NUMBER sind die üblichen Operatoren +, −, ∗ und / sowie die Vergleiche
=, >, >=, <= und < erlaubt. Außerdem gibt es BETWEEN x AND y. Ungleichheit: ! =, ∧ =,
¬ = oder <>.
DATE: Datum und Zeiten: Jahrhundert – Jahr – Monat – Tag – Stunde – Minute – Sekunde. U.a.
wird auch Arithmetik für solche Daten angeboten.
weitere Datentypen findet man im Manual.
Andere DBMS verwenden in der Regel andere Namen für dieselben oder ähnliche Datentypen!

4.0 Schema-Definition 82
 Praktikum: Datenbankprogrammierung in SQL/ORACLE

TABELLENDEFINITION

Das folgende SQL-Statement erzeugt z.B. die Relation City (noch ohne
Integritätsbedingungen):
CREATE TABLE City
( Name VARCHAR2(50),
Country VARCHAR2(4),
Province VARCHAR2(50),
Population NUMBER,
Latitude NUMBER,
Longitude NUMBER );
Die so erzeugten Tabellen- und Spaltennamen sind case-insensitive.

4.0 Schema-Definition 83
 Praktikum: Datenbankprogrammierung in SQL/ORACLE

TABELLENDEFINITION

Randbemerkung: case-sensitive Spaltennamen

Falls man case-sensitive Spaltennamen benötigt, kann man dies mit doppelten
Anführungszeichen erreichen:
CREATE TABLE "Bla"
("a" NUMBER,
"A" NUMBER);
desc "Bla";
insert into "Bla" values(1,2);
select "a" from "Bla"; -> 1
select "A" from "Bla"; -> 2
select a from "Bla"; -> 2(!) Default ist CAPITALS (siehe auch alle Ausgaben
von Anfragen an das Data Dictionary)

4.0 Schema-Definition 84
 Praktikum: Datenbankprogrammierung in SQL/ORACLE

TABELLENDEFINITION : C ONSTRAINTS

Mit den Tabellendefinitionen können Eigenschaften und Bedingungen an die jeweiligen

Attributwerte formuliert werden.
• Bedingungen an ein einzelnes
oder mehrere Attribute:
• Wertebereichseinschränkungen,
• Angabe von Default-Werten, CREATE TABLE ‹table›
• Forderung, dass ein Wert angege- (‹col› ‹datatype› [DEFAULT ‹value›]
ben werden muss, [‹colConstraint› ... ‹colConstraint›],
.
.
• Angabe von Schlüsselbedingun- .
gen, ‹col› ‹datatype› [DEFAULT ‹value›]
• Prädikate an Tupel. [‹colConstraint› ... ‹colConstraint›],
[‹tableConstraint›,]
.
.
.
• ‹colConstraint› betrifft nur eine
[‹tableConstraint›])
Spalte,
• ‹tableConstraint› kann mehre-
re Spalten betreffen.

4.0 Schema-Definition 85
 Praktikum: Datenbankprogrammierung in SQL/ORACLE

TABELLENDEFINITION : D EFAULT-W ERTE

DEFAULT ‹value›
Ein Mitgliedsland einer Organisation wird als volles Mitglied angenommen, wenn nichts
anderes bekannt ist:
CREATE TABLE isMember
( Country VARCHAR2(4),
Organization VARCHAR2(12),
Type VARCHAR2(50)
DEFAULT ’member’)

Country Org. Type

INSERT INTO isMember VALUES
(’CH’, ’EU’, ’membership applicant’); CH EU membership applicant
INSERT INTO isMember (Land, Organization) R EU member
VALUES (’R’, ’EU’); .. .. ..
. . .

4.0 Schema-Definition 86
 Praktikum: Datenbankprogrammierung in SQL/ORACLE

TABELLENDEFINITION : C ONSTRAINTS

Zwei Arten von Bedingungen:

• Eine Spaltenbedingung ‹colConstraint› ist eine Bedingung, die nur eine Spalte betrifft
(zu der sie definiert wird)
• Eine Tabellenbedingung ‹tableConstraint› kann mehrere Spalten betreffen.

Jedes ‹colConstraint› bzw. ‹tableConstraint› ist von der Form

[CONSTRAINT ‹name›] ‹bedingung›

4.0 Schema-Definition 87
 Praktikum: Datenbankprogrammierung in SQL/ORACLE

TABELLENDEFINITION : B EDINGUNGEN (Ü BERBLICK )

Syntax:
[CONSTRAINT ‹name›] ‹bedingung›
Schlüsselwörter in ‹bedingung›:
1. CHECK (‹condition›): Detailliertere Domain-Einschränkung für eine Spalte. Keine Zeile
darf ‹condition› verletzen. NULL-Werte ergeben dabei ggf. ein unknown, also keine
Bedingungsverletzung.
2. [NOT] NULL: Gibt an, ob die entsprechende Spalte Nullwerte enthalten darf (nur als
‹colConstraint›).
3. UNIQUE (‹column-list›): Fordert, dass jeder Wert nur einmal auftreten darf.
4. PRIMARY KEY (‹column-list›): Deklariert die angegebenen Spalten als Primärschlüssel
der Tabelle.
5. FOREIGN KEY (‹column-list›) REFERENCES ‹table›(‹column-list2›) [ON DELETE
CASCADE|ON DELETE SET NULL]:
gibt an, dass eine Menge von Attributen Fremdschlüssel ist.
Da bei einem ‹colConstraint› die Spalte implizit bekannt ist, fällt der (‹column-list›) Teil
weg.
4.0 Schema-Definition 88
 Praktikum: Datenbankprogrammierung in SQL/ORACLE

TABELLENDEFINITION : S YNTAX

[CONSTRAINT ‹name›] ‹bedingung›

Dabei ist CONSTRAINT ‹name› optional (ggf. Zuordnung eines systeminternen Namens).

• ‹name› wird bei NULL-, UNIQUE-, CHECK- und REFERENCES-Constraints benötigt, wenn das
Constraint irgendwann einmal geändert oder gelöscht werden soll,
• PRIMARY KEY kann man ohne Namensnennung löschen und ändern.
• Angabe von DEFERRABLE: siehe Folie 156 ff.

4.0 Schema-Definition 89
 Praktikum: Datenbankprogrammierung in SQL/ORACLE

TABELLENDEFINITION : CHECK C ONSTRAINTS

• als Spaltenconstraints: Wertebereichseinschränkung
CREATE TABLE City
( Name VARCHAR2(50),
Population NUMBER CONSTRAINT CityPop CHECK (Population >= 0),
...);
• Als Tabellenconstraints: beliebig komplizierte Integritätsbedingungen an ein Tupel.
Bug: Der Parser akzeptiert die Verwendung mehrerer Spalten nur, wenn das Constraint
einen Namen bekommt (April 2023)
– Economy(Country, GDP, Agriculture, Service, Industry, . . . ):
CREATE TABLE Economy ( ...
CONSTRAINT gdpcheck CHECK (industry + service + agriculture <= 102));
– zusammengesetzte Fremdschlüssel: Zusammenhang erzwingen
(einzelne NULL-Werte würden die Bedingung nicht verletzen):
Organization(Abbrev., Name, City, Country, Province, ...)
CREATE TABLE Organization ( ...
CONSTRAINT hq
CHECK ( (City IS NULL AND Country IS NULL AND Province IS NULL)
OR (City IS NOT NULL AND Country IS NOT NULL AND Province IS NOT NULL)))
4.0 Schema-Definition 90
 Praktikum: Datenbankprogrammierung in SQL/ORACLE

TABELLENDEFINITION : PRIMARY KEY, UNIQUE UND NULL

• PRIMARY KEY (‹column-list›): Deklariert diese Spalten als Primärschlüssel der Tabelle.
• Damit entspricht PRIMARY KEY der Kombination aus UNIQUE und NOT NULL.
• UNIQUE wird von NULL-Werten nicht unbedingt verletzt, während PRIMARY KEY NULL-Werte
verbietet.
Eins Zwei
a b
a NULL erfüllt UNIQUE (Eins,Zwei).
NULL b
NULL NULL
• Da auf jeder Tabelle nur ein PRIMARY KEY definiert werden darf, wird NOT NULL und UNIQUE
für Candidate Keys eingesetzt.
Relation Country: Code ist PRIMARY KEY, Name ist Candidate Key:
CREATE TABLE Country
( Name VARCHAR2(50) NOT NULL UNIQUE,
Code VARCHAR2(4) PRIMARY KEY);

4.0 Schema-Definition 91
 Praktikum: Datenbankprogrammierung in SQL/ORACLE

TABELLENDEFINITION : FOREIGN KEY ...REFERENCES

• FOREIGN KEY (‹column-list›) REFERENCES ‹table›(‹column-list2›) [ON DELETE

CASCADE|ON DELETE SET NULL]: gibt an, dass das Attributtupel ‹column-list› der Tabelle
ein Fremdschlüssel ist und das Attributtupel ‹column-list2› der Tabelle ‹table›
referenziert.
• Das referenzierte Attributtupel ‹table›(‹column-list2›) muss ein Candidate Key von
‹table› sein.
• Eine REFERENCES-Bedingung wird durch NULL-Werte nicht verletzt.
• ON DELETE CASCADE|ON DELETE SET NULL: Referentielle Aktionen, siehe Folie 146 ff.

CREATE TABLE isMember

(Country VARCHAR2(4) REFERENCES Country(Code),
Organization VARCHAR2(12) REFERENCES Organization(Abbreviation),
Type VARCHAR2(60) DEFAULT ’member’);

4.0 Schema-Definition 92
 Praktikum: Datenbankprogrammierung in SQL/ORACLE

Tabellendefinition: Fremdschlüssel
Ein Berg liegt in einer Provinz eines Landes:

Country Code

belongs_to
Name

Mountain in Province Name

CREATE TABLE geo_Mountain

( Mountain VARCHAR2(50)
REFERENCES Mountain(Name),
Country VARCHAR2(4) ,
Province VARCHAR2(50) ,
CONSTRAINT GMountRefsProv
FOREIGN KEY (Country,Province)
REFERENCES Province (Country,Name));

4.0 Schema-Definition 93
 Praktikum: Datenbankprogrammierung in SQL/ORACLE

Tabellendefinition

• Die meisten Organisationen haben ihren Sitz in einer Stadt:

Organization < 0, 1 > has_headq < 0, ∗ > City name

abbrev
in in
Name

Province belongs_to Country Code

• Organization(Abbrev., Name, City, Country, Province, ...)

CREATE TABLE Organization ( ... ,
CONSTRAINT orgrefshq
FOREIGN KEY (City, Country, Province)
REFERENCES City (Name, Country, Province) )
• Einzelne Nullwerte würden die FK-Bedingung nicht verletzen:
• INSERT INTO Organization
VALUES (’XX’,’xx’,’Clausthal’,’ZZ’,NULL,NULL)

4.0 Schema-Definition 94
 Praktikum: Datenbankprogrammierung in SQL/ORACLE

Tabellendefinition
• Die meisten Organisationen haben ihren Sitz in einer Stadt:

Organization < 0, 1 > has_headq < 0, ∗ > City name

abbrev
in in
Name

Province belongs_to Country Code

• Organization(Abbrev., Name, City, Country, Province, ...)

• Zusammenhang des Fremdschlüssels erzwingen:
CREATE TABLE Organization ( ... ,
CONSTRAINT orgrefshq
FOREIGN KEY (City, Country, Province)
REFERENCES City (Name, Country, Province),
CONSTRAINT hq
CHECK ( (City IS NULL AND Country IS NULL AND Province IS NULL)
OR (City IS NOT NULL AND Country IS NOT NULL AND Province IS NOT NULL)))

4.0 Schema-Definition 95
 Praktikum: Datenbankprogrammierung in SQL/ORACLE

TABELLENDEFINITION

Vollständige Definition der Relation City mit Bedingungen und Schlüsseldeklaration:

CREATE TABLE City

( Name VARCHAR2(50),
Country VARCHAR2(4) REFERENCES Country(Code),
Province VARCHAR2(50), -- + ‹tableConstraint›
Population NUMBER CONSTRAINT CityPop
CHECK (Population >= 0),
Latitude NUMBER CONSTRAINT CityLat
CHECK ((Latitude >= -90) AND (Latitude <= 90)),
Longitude NUMBER CONSTRAINT CityLong
CHECK ((Longitude > -180) AND (Longitude <= 180)),
CONSTRAINT CityKey PRIMARY KEY (Name, Country, Province),
FOREIGN KEY (Country,Province) REFERENCES Province (Country,Name));

• Wenn eine Tabelle mit einer Spalte, die eine REFERENCES

‹table›(‹column-list›)-Klausel enthält, erstellt wird, muss ‹table› bereits definiert und
‹column-list› dort als PRIMARY KEY deklariert sein.

4.0 Schema-Definition 96
 Praktikum: Datenbankprogrammierung in SQL/ORACLE

V IEWS (=S ICHTEN )

• Virtuelle Tabellen
• nicht zum Zeitpunkt ihrer Definition berechnet, sondern
• jedesmal berechnet, wenn auf sie zugegriffen wird.
• spiegeln also stets den aktuellen Zustand der ihnen zugrundeliegenden Relationen wieder.
• Änderungsoperationen nur in eingeschränktem Umfang möglich.
CREATE [OR REPLACE] VIEW ‹name› (‹column-list›) AS
‹select-clause›;

Beispiel: Ein Benutzer benötigt häufig die Information, welche Stadt in welchem Land liegt, ist
jedoch weder an Landeskürzeln noch Einwohnerzahlen interessiert.
CREATE VIEW CityCountry (City, Country) AS
SELECT *
SELECT [Link], [Link]
FROM CityCountry
FROM City, Country
WHERE Country = ’Cameroon’;
WHERE [Link] = [Link];

4.0 Views 97
 Praktikum: Datenbankprogrammierung in SQL/ORACLE

L ÖSCHEN VON TABELLEN UND V IEWS

• Tabellen bzw. Views werden mit DROP TABLE bzw. DROP VIEW gelöscht:
DROP TABLE ‹table-name› [CASCADE CONSTRAINTS];
DROP VIEW ‹view-name›;
• Tabellen müssen nicht leer sein, wenn sie gelöscht werden sollen.
• Eine Tabelle, auf die noch eine REFERENCES-Deklaration zeigt, kann mit dem einfachen DROP
TABLE-Befehl nicht gelöscht werden.
• Mit
DROP TABLE ‹table› CASCADE CONSTRAINTS
wird eine Tabelle mit allen auf sie zeigenden referentiellen Integritätsbedingungen gelöscht
und die referenzierenden Tupel werden entfernt.

Ändern von Tabellen und Views

Siehe Folie 138 ff.

4.0 Löschen von Tabellen und Views 98

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

PAPIERKORB /R ECYCLEBIN
Seit Version 11 besitzt Oracle einen Recyclebin, wo alles reinfällt, was gedroppt wurde:
• Vorteil: man kann es wiederholen
• Nachteil: es braucht weiterhin Platz im Tablespace.
• Inhalt anschauen (vgl. Data Dictionary: all_objects)
SELECT type, object_name, original_name
FROM RECYCLEBIN;
• (Etwas aus) Recyclebin löschen:
PURGE RECYCLEBIN;
PURGE TABLE ‹tablename›;
• Tabelle droppen und nicht im Recyclebin sichern:
DROP ‹tablename› PURGE;
• Tabelle wieder holen:
FLASHBACK TABLE ‹tablename›
TO {BEFORE DROP | TIMESTAMP ‹timestamp›}
[ RENAME TO ‹name›] ;

4.0 Löschen von Tabellen und Views 99

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

Kapitel 5
Einfügen und Ändern von Daten

• Einfügen (in existierende Tabellen):

– Tupel (als Konstanten)
– Mengen (Ergebnisse von Anfragen)
• Ändern: Einfache Erweiterung des SELECT-FROM-WHERE-Statements.

5.0 Einfügen und Ändern von Daten 100

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

5.1 Einfügen von Daten

• INSERT-Statement.
• Daten einzeln von Hand einfügen,
INSERT INTO ‹table›[(‹column-list›)]
VALUES (‹value-list›);
• Ergebnis einer Anfrage einfügen:
INSERT INTO ‹table›[(‹column-list›)]
‹subquery›;
• Rest wird ggf. mit Nullwerten aufgefüllt.
So kann man z.B. das folgende Tupel einfügen:
INSERT INTO Country (Name, Code, Population)
VALUES (’Lummerland’, ’LU’, 4);

5.1 Einfügen und Ändern von Daten 101

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

Einfügen von Daten: komplette Tabelle

Eine Tabelle Metropolis (Name, Country, Population) kann man z.B. mit dem folgenden
Statement füllen:
INSERT INTO Metropolis
SELECT Name, Country, Population
FROM City
WHERE Population > 1000000;
Es geht auch noch kompakter (implizite Tabellendefinition):
CREATE TABLE Metropolis AS
SELECT Name, Country, Population
FROM City WHERE Population > 1000000;
• Im Gegensatz zu einem View wird diese Tabelle beim Aufruf gefüllt, und ändert sich bei
Änderungen an der Tabelle City nicht automatisch mit.

5.1 Einfügen und Ändern von Daten 102

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

5.2 Löschen von Daten

Tupel können mit Hilfe der DELETE-Klausel aus Relationen gelöscht werden:
DELETE FROM ‹table›
WHERE ‹predicate›;
Dabei gilt für die WHERE-Klausel das für SELECT gesagte.

Mit einer leeren WHERE-Bedingung kann man z.B. eine ganze Tabelle abräumen (die Tabelle
bleibt bestehen, sie kann mit DROP TABLE entfernt werden):
DELETE FROM City;

Der folgende Befehl löscht sämtliche Städte, deren Einwohnerzahl kleiner als 50.000 ist.
DELETE FROM City
WHERE Population < 50000;

5.2 Einfügen und Ändern von Daten 103

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

5.3 Ändern von Tupeln

UPDATE ‹table›
SET ‹attribute› = ‹value› | (‹subquery›),
.
.
.
‹attribute› = ‹value› | (‹subquery›),
(‹attribute-list›) = (‹subquery›),
.
.
.
(‹attribute-list›) = (‹subquery›)
WHERE ‹predicate›;

Beispiel: Beispiel: Die Einwohnerzahl jedes Landes

UPDATE City wird als die Summe der Einwohnerzahlen aller
SET Name = ’Leningrad’, Provinzen gesetzt:
Population = Population + 1000, UPDATE Country
Latitude = NULL, SET Population =
Longitude = NULL (SELECT SUM(Population)
WHERE Name = ’Sankt Peterburg’; FROM Province
WHERE [Link]=[Link]);

5.3 Ändern von Tupeln 104

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

5.4 Insert/Update: Merge (Upsert)

Ziel: Wert einer oder mehrerer Spalten setzen, wenn nicht bekannt ist, ob das Tupel (d.h. der
Schlüsselwert) bereits existiert.
• falls es existiert: Spalteninhalt setzen,
• falls es nicht existiert: neues Tupel anlegen.
⇒ kann nicht mit einfachen SQL Updates ausgedrückt werden,
⇒ kombiniertes Statement “MERGE” (auch als “UPSERT” bezeichnet) seit SQL 2003.
MERGE INTO ‹target_table›
USING ‹source_relation› – Tabellenname, Subquery, oder DUAL
ON (‹condition›) – über ‹source_relation› und ‹target_table›
WHEN MATCHED THEN UPDATE
SET ‹col1 › = ‹expr1 ›, ..., ‹coln › = ‹exprn ›
WHEN NOT MATCHED THEN
INSERT (‹col’1 ›,...,‹col’m ›)
VALUES (‹expr’1 ›,...,‹expr’m ›);
• ‹expri ›, ‹expr’i › sind Konstanten oder Ausdrücke über den Spaltennamen von
‹source_relation›.

5.4 Ändern von Tupeln 105

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

Merge: mit konstanten Werten

MERGE INTO country

USING DUAL
ON (code = ’WAN’)
WHEN MATCHED THEN UPDATE
SET population = 152217341
WHEN NOT MATCHED THEN
INSERT (name, code, population)
VALUES (’Nigeria’, ’WAN’, 152217341);

5.4 Ändern von Tupeln 106

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

Merge: aus anderer Tabelle

• Tabelle NewCountryPops mit neuen Werten für Einwohnerzahlen (evtl. auch neue Länder)
CREATE TABLE NewCountryPops (name VARCHAR2(50), code VARCHAR2(4), pop NUMBER);
INSERT INTO NewCountryPops VALUES(’Nigeria’, ’WAN’, 152217341);
INSERT INTO NewCountryPops VALUES(’Lummerland’, ’LU’, 4);
MERGE INTO country c
USING newCountryPops n
ON ([Link] = [Link])
WHEN MATCHED THEN UPDATE
SET population = [Link]
WHEN NOT MATCHED THEN
INSERT (name, code, population)
VALUES ([Link], [Link], [Link]);
SELECT * FROM country WHERE code IN (’LU’,’WAN’);
• ‹source relation› kann eine Tabelle oder eine Subquery sein.
• Die in der ON-Klausel angegebenen Attribute müssen eindeutig ein Tupel der Quell-
(logisch, sonst wäre nicht klar welcher Wert eingesetzt werden muss) und Zieltabelle (wäre
nicht notwendig) spezifizieren, sonst:
ORA-30926: unable to get a stable set of rows in the source tables

5.4 Ändern von Tupeln 107

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

5.5 Referentielle Integrität – A First Look

• Wenn eine Tabelle mit einer Spalte, die eine REFERENCES

‹table›(‹column-list›)-Klausel enthält, erstellt wird, muss ‹table› bereits definiert und
‹column-list› dort ein Candidate Key sein.
• Eine Tabelle, auf die noch eine REFERENCES-Deklaration zeigt, wird mit DROP TABLE
‹table› CASCADE CONSTRAINTS gelöscht.
• Beim Einfügen, Löschen oder Verändern eines referenzierten Tupels muss die referentielle
Integrität gewährleistet sein.
(Weiteres dazu später, siehe Folie 156).

5.5 Referentielle Integrität 108

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

5.6 Transaktionen in ORACLE

Beginn einer Transaktion
SET TRANSACTION READ [ONLY | WRITE];

Sicherungspunkte setzen
Für eine längere Transaktion können zwischendurch Sicherungspunkte gesetzt werden:
SAVEPOINT ‹savepoint›;

Ende einer Transaktion

• COMMIT-Anweisung, macht alle Änderungen persistent,
COMMIT scheitert, wenn Integritätsbedingungen verletzt sind (dann wird automatisch ein
ROLLBACK ausgeführt).
• ROLLBACK [TO ‹savepoint›] nimmt alle Änderungen [bis zu ‹savepoint›] zurück,
• Auto-COMMIT in folgenden Situationen:
– DDL-Anweisung (z.B. CREATE, DROP, RENAME, ALTER),
– Benutzer meldet sich von O RACLE ab.
• Auto-ROLLBACK bei Abbruch eines Benutzerprozesses.

5.6 Transaktionen 109

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

Kapitel 6
Spezialisierte Datentypen
• (einfache) Built-In-Typen: Zeitangaben
• zusammengesetzte benutzerdefinierte Datentypen
(z.B. Geo-Koordinaten aus Länge, Breite) [seit Oracle 8i/1997]
• Verlassen der 1. Normalform: Mengenwertige Einträge – Geschachtelte Tabellen [seit
Oracle 8i/8.1.5/1997]
• selbstdefinierte Objekttypen (Siehe Folie 256)
– Objekte an Stelle von Tupeln und Attributwerten
– mit Objektmethoden
– basierend auf PL-SQL [seit Oracle 8.0/1997/1998]
– mit Java-Methoden [seit Oracle 8i/8.1.5/1999]
– Objekttypen basierend auf Java-Klassen, Vererbung [seit Oracle 9i/2001]
• Built-In-Typen mit festem Verhalten
– XMLType (siehe Folie 413) [seit Oracle 9i-2/2002]
– Ergänzungen “Extensions” (Spatial Data (seit Oracle 8i/8.1.5) etc.)
6.0 Spezialisierte Datentypen 110
 Praktikum: Datenbankprogrammierung in SQL/ORACLE

6.1 Datums- und Zeitangaben

Der Datentyp DATE speichert Jahrhundert, Jahr, Monat, Tag, Stunde, Minute und Sekunde.
• Oracle: Eingabe-Format mit NLS_DATE_FORMAT setzen,
• Default: ’DD-MON-YY’ eingestellt, d.h. z.B. ’20-Oct-97’.
CREATE TABLE Politics
( Country VARCHAR2(4),
Independence DATE,
Government VARCHAR2(120));
ALTER SESSION SET NLS_DATE_FORMAT = ’DD MM YYYY’;
INSERT INTO politics VALUES (’B’,’04 10 1830’,’constitutional monarchy’);
Beispiel: Alle Länder, die zwischen 1200 und 1600 gegründet wurden:

SELECT Country, Independence Country Independence

FROM Politics MC 08 01 1297
WHERE Independence BETWEEN E 20 01 1479
’01 01 1200’ AND ’31 12 1599’; NL 26 07 1581
THA 01 01 1238

6.1 Datums- und Zeitangaben 111

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

Verwendung von Zeitangaben

• SYSDATE liefert das aktuelle Datum (in Oracle-SQL).
ALTER SESSION SET NLS_DATE_FORMAT = "hh:mi:ss";
SELECT SYSDATE FROM DUAL;
SYSDATE
10:50:43
• Funktion
EXTRACT (
{ YEAR | MONTH | DAY | HOUR | MINUTE | SECOND }
| { TIMEZONE_HOUR | TIMEZONE_MINUTE }
| { TIMEZONE_REGION | TIMEZONE_ABBR }
FROM { datevalue | intervalvalue } )

Beispiel: Alle Länder, die zwischen 1988 und 1992 gegründet wurden:

SELECT Country, EXTRACT(MONTH FROM Independence), Country EXTR... EXTR...

EXTRACT(YEAR FROM Independence) MK 9 1991
FROM Politics SLO 6 1991
WHERE EXTRACT(YEAR FROM Independence) : : :
BETWEEN 1988 AND 1992;

6.1 Datums- und Zeitangaben 112

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

Rechnen mit Datumswerten

ORACLE bietet einige Funktionen um mit dem Datentyp DATE zu arbeiten:

• Addition und Subtraktion von Absolutwerten auf DATE ist erlaubt, Zahlen werden als Tage
interpretiert: SYSDATE + 1 ist morgen, SYSDATE + (10/1440) ist “in zehn Minuten”.
• ADD_MONTHS(d, n) addiert n Monate zu einem Datum d.
• LAST_DAY(d) ergibt den letzten Tag des in d angegebenen Monats.
• MONTHS_BETWEEN(d1 ,d2 ) gibt an, wieviele Monate zwischen zwei Daten liegen.
SELECT MONTHS_BETWEEN(LAST_DAY(D1), LAST_DAY(D2))
FROM (SELECT independence as D1 FROM politics
WHERE country=’R’),
(SELECT independence as D2 FROM politics
WHERE country=’UA’);

MONTHS_BETWEEN(...)
-4

6.1 Datums- und Zeitangaben 113

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

Formattoleranz
• NLS_date_format ist verbindlich für das Ausgabeformat
• für das Eingabeformat wendet Oracle zusätzlich Heuristiken an:
ALTER SESSION SET NLS_DATE_FORMAT = ’DD MM YYYY’;
SELECT to_char(to_date(’24.12.2002’)) FROM dual; -- wird erkannt
SELECT to_char(to_date(’24 JUN 2002’)) FROM dual; -- wird erkannt
SELECT to_char(to_date(’JUN 24 2002’)) FROM dual; -- wird nicht erkannt
-- ORA-01858: a non-numeric character was found where a numeric was expected

ALTER SESSION SET NLS_DATE_FORMAT = ’MON DD YYYY’;

SELECT to_char(to_date(’JUN 24 2002’)) FROM dual;
Explizite Formatvorgabe im Einzelfall
ALTER SESSION SET NLS_DATE_FORMAT = ’DD MM YYYY’;
SELECT to_char(to_date(’JUN 24 2002’,’MON DD YYYY’))
FROM dual;
-- 24 06 2002
SELECT to_char(to_date(’JUN 24 2002’,’MON DD YYYY’), ’MM/DD-YYYY’)
FROM dual;
-- 06/24-2002

6.1 Datums- und Zeitangaben 114

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

Constraints und Sonstiges mit Zeitangaben

• Wenn man ein Datum in einem CREATE TABLE constraint verwenden muss, muss es zu
dem zu diesem Zeitpunkt aktiven Format passen.
• produktabhängig: SYSDATE (in Oracle), CURRENT_DATE (in Postgres) ...
• In Oracle darf SYSDATE in constraints nicht verwendet werden, “da es veränderlich ist und
somit das Constraint zur Laufzeit ohne ein Update verletzt werden könnte”.
• in Postgres ist es erlaubt:
create table test(a date check(a <= current_date),
b date check(b >= current_date));
insert into text values(current_date,current_date);
Das Constraint wird nur zum Eingabe/Updatezeitpunkt geprüft.
Das Tupel ist am nächsten Tag auch noch da und selektierbar.

6.1 Datums- und Zeitangaben 115

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

6.2 Zusammengesetzte Datentypen

• “First Normal Form”: nur atomare Werte
• Erweiterung I: Strukturierte Werte name
latitude
• Syntaktisch elegant in SQL umsetzbar Mountain geo coord
elevation longitude

Neue Klasse von Schemaobjekten: CREATE TYPE

• CREATE [OR REPLACE] TYPE ‹name› AS OBJECT
( ‹attr1 › ‹datatype1 ›,
.
.
.
‹attrn › ‹datatypen ›);
/ ← dieser Slash ist unbedingt notwendig!
• Bei “echten” Objekten kommt noch ein
CREATE TYPE BODY ... dazu, in dem die Methoden in PL/SQL definiert werden ... später.
Ohne Body bekommt man einfache komplexe Datentypen (ähnlich wie Records).
• definiert automatisch eine Konstruktormethode ‹type›( arg1 , ..., argn ) .

6.2 Komplexe Datentypen 116

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

Zusammengesetzte Datentypen: Geographische Koordinaten

name
latitude
Mountain geo coord
elevation longitude

CREATE TYPE GeoCoord AS OBJECT

( Latitude NUMBER,
Longitude NUMBER);
/ ← dieser Slash ist unbedingt notwendig!
CREATE TABLE Mountain
( Name VARCHAR2(50),
Elevation NUMBER,
Coordinates GeoCoord);
INSERT INTO Mountain
VALUES (’Feldberg’, 1493, GeoCoord(47.5, 7.5));
SELECT * FROM Mountain;

Name Elevation Coordinates(Latitude, Longitude)

Feldberg 1493 GeoCoord(47.5, 7.5)

6.2 Komplexe Datentypen 117

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

Z USAMMENGESETZTE DATENTYPEN
Zugriff auf einzelne Komponenten von komplexen Attributen in der bei Records üblichen
dot-Notation.
Hierbei muss der Pfad mit dem Alias einer Relation beginnen (Eindeutigkeit!):
SELECT Name, [Link],
[Link]
FROM Mountain B;

Name [Link] [Link]

Feldberg 47.5 7.5
Constraints in zusammengesetzten Datentypen können nicht gleich bei dem Datentypen
definiert werden, sondern erst bei (jeder!) Tabelle, in der man ihn benutzt:
CREATE TABLE Mountain
(Name VARCHAR2(50),
Elevation NUMBER,
Coordinates GeoCoord,
CHECK (([Link] >= -90) AND ([Link] <= 90)),
CHECK (([Link] > -180) AND ([Link] <= 180)));

6.2 Komplexe Datentypen 118

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

6.3 Collections
• “First Normal Form”: nur atomare Werte
• Erweiterung II: Collections: Wert eines Attributs ist eine Menge
• ... es geht, aber die Syntax dafür wird umständlich und durchbricht die elegante Einfachheit
von SQL und fällt in eine häßliche Programmiersprachenebene.

NestedPolitics
Country Independence Dep. Memberships
D 18-JAN-1871 NULL EU, NATO, OECD, . . .
GBJ NULL GB ∅
: : : :
• Collection kann durch Aggregation aus einem GROUP-BY gebildet werden:
SELECT country, collect(organization)
FROM isMember
GROUP BY country;
• Ergebnis z.B. SYSTPkEqWcRtkgT/gQEyGzFEpmA==(’EU’, ’NATO’, ’OECD’, ...)
• erzeugt ad-hoc einen systemeigenen Typ “SYSTP...”, der die Collection aufnimmt.
6.3 Collections 119
 Praktikum: Datenbankprogrammierung in SQL/ORACLE

Tabellen mit Collections erzeugen

Verwendet eine einfache Form des komplexeren Konzeptes “Nested Tables” (siehe Folie 128 ff.)
CREATE [OR REPLACE] TYPE ‹collection_type› AS
TABLE OF ‹basic_type›;
/
CREATE TABLE ‹table_name›
( ... ,
‹collection-attr› ‹collection_type› ,
... )
NESTED TABLE ‹collection-attr› STORE AS ‹name ›;
TABLE-Typ MON_ORGLIST definieren:
CREATE OR REPLACE TYPE MON_ORGLIST AS TABLE OF VARCHAR2(12);
/
CREATE TABLE NestedPolitics
( country VARCHAR2(4) PRIMARY KEY,
independence DATE,
dependent VARCHAR2(4), -- REFERENCES Country(Code)
memberships MON_ORGLIST)
NESTED TABLE memberships STORE AS o_list;
6.3 Collections 120
 Praktikum: Datenbankprogrammierung in SQL/ORACLE

Tabellen mit Collections füllen (1)

• explizit unter Verwendung der entsprechenden Konstruktormethode:

INSERT INTO NestedPolitics
VALUES(’BAV’, ’01-APR-2010’,
NULL, MON_ORGLIST(’EU’,’OECD’));
INSERT INTO NestedPolitics
VALUES(’SYLT’, NULL, ’D’, MON_ORGLIST());
• eine leere Tabelle ist etwas anderes als NULL.
⇒ damit wird es schwieriger, herauszufinden welche Länder nirgends Mitglied sind!
• man kann keine Bedingungen für die in einer Collection erlaubten Werte formulieren (insb.
keine REFERENCES).

6.3 Collections 121

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

Tabellen mit Collections füllen (2)

• collect(...) erzeugt eine Instanz eines ad-hoc-Typs, der Zeichenketten (oder Zahlen oder
DATE) enthält,
• man muss (leider) explizit mitteilen, dass diese in den Zieltyp (hier MON_ORGLIST) gecastet
werden muss:
CAST(‹instanz-eines-typs› AS ‹kompatibler typ›)
INSERT INTO NestedPolitics
( SELECT [Link], [Link], [Link],
CAST(collect([Link]) AS MON_ORGLIST)
FROM Politics p LEFT OUTER JOIN isMember i
ON [Link] = [Link]
GROUP BY [Link], [Link], [Link]);
SELECT country, memberships
FROM NestedPolitics
WHERE country = ’D’;

Country Organizations
’D’ MON_ORGLIST(’EU’, ’NATO’, ’OECD’, . . . )

6.3 Collections 122

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

Tabellen mit Collections anfragen

• Collections und sie sind eigentlich kleine, sehr einfache Tabellen.
• Mit [THE|TABLE] (‹collection-wertiger Wert›) kann man die Collection wie eine Tabelle
verwenden.
(THE ist die schon länger gebräuchliche Syntax) COLUMN_VALUE

SELECT * FROM TABLE(SELECT memberships EU

FROM NestedPolitics NATO
WHERE country = ’D’); OECD

• Test: mit Konstanten ist nur TABLE, nicht THE erlaubt:

SELECT * FROM TABLE(MON_ORGLIST(’EU’,’NATO’));
• eine Spalte, die nur den Namen COLUMN_VALUE hat,
• oft als SELECT column_value as ‹alias›.
• Hinweis:
SELECT * FROM TABLE(SELECT memberships
FROM NestedPolitics);
ist nicht zulässig, da es ja mehrere Tabellen wären:
⇒ single-row subquery returns more than one row

6.3 Collections 123

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

Tabellen mit Collections anfragen

Mit TABLE(‹attrname›) kann auch innerhalb eines Tupels ein collection-wertiges Attribut als
Tabelle zugreifbar gemacht werden:
(hier ist THE nicht erlaubt)
• in Subqueries:
SELECT country
FROM NestedPolitics
WHERE EXISTS (SELECT *
FROM TABLE(memberships)
WHERE column_value = ’NATO’);
• oder auch als korreliertes Join in der FROM-Zeile:
jede umgebende Zeile mit ihrer geschachtelten Tabelle joinen und ausmultiplizieren:
SELECT country, m.* -- oder m.column_value as membership
FROM NestedPolitics, TABLE(memberships) m;

Country COLUMN_VALUE (bzw. membership)

D EU
D NATO
: :

6.3 Collections 124

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

Vergleich mit 1:n- bzw. m:n-Beziehungen als separate Tabelle

• Man sieht relativ einfach, dass die nested table o_list ähnlich der bestehenden “flachen”
Tabelle isMember gespeichert ist, und dass
SELECT [Link], [Link], [Link]
FROM Politics p, isMember im
WHERE [Link] = [Link];
SELECT [Link], [Link], [Link]
FROM Politics p,
-- korreliertes Join, waere z.B. in OQL zulaessig
(SELECT * FROM isMember where country = [Link]) i
äquivalent ist.
• Anmerkung:
korreliertes Join: i-te Relation in Abhängigkeit von i-1 -ter berechnen
– in SQL nicht erlaubt
– in Sprachen zu Datenmodellen, die Referenzen/Objektwertige Attribute,
mengen-/mehrwertige Attribute oder baumartige Hierarchien besitzen, üblicherweise
erlaubt (OQL, XML/XQuery; Forschungs-Sprachen aus 1995-2000: OEM, F-Logic)
– daher auch für SQL mit Collections naheliegend.

6.3 Collections 125

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

Tabellen mit Collections vergleichen

• Instanzen von Collections können mit “=” verglichen werden

SELECT [Link], [Link], [Link]
FROM NestedPolitics a, NestedPolitics b
WHERE [Link] < [Link]
AND [Link] = [Link];

Collection im Ganzen kopieren

UPDATE NestedPolitics
SET memberships = (SELECT memberships
FROM NestedPolitics
WHERE country = ’D’)
WHERE country=’BAV’;
-- optional THE (SELECT ...)

6.3 Collections 126

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

Einfügen, Ändern und Löschen nur mit THE

• Man kann immer nur eine Collection gleichzeitig anfassen, und muss diese mit einer
SELECT-Anfrage auswählen
(also nicht ’XXX’ in alle Mitgliedschaftslisten einfügen, oder überall ’EU’ durch ’EWG’
ersetzen)
INSERT INTO THE (SELECT memberships
FROM NestedPolitics
WHERE country = ’D’)
VALUES(’XXX’);
DELETE FROM THE (SELECT memberships
FROM NestedPolitics
WHERE country = ’D’)
WHERE column_value = ’XXX’;
UPDATE THE (SELECT memberships
FROM NestedPolitics
WHERE country = ’D’)
SET column_value = ’XXX’
WHERE column_value = ’EU’;

6.3 Collections 127

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

6.4 Geschachtelte Tabellen

... zeigen endgültig, wie häßlich die Syntax einer eigentlich schönen Sprache wird, wenn man
unbedingt etwas machen will, was im zugrundeliegenden Datenmodell (1. Normalform) nicht
möglich ist.

Nested_Spoken
Country Languages
Name Percent
D German 100
CH German 65 • Tabellenwertige Attribute
• Generischer Typ
French 18
TABLE OF <inner_type>
Italian 12 ⇒ Generische Syntax
Romansch 1
FL NULL
F French 100
.. ..
. .

6.4 Geschachtelte Tabellen 128

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

Generische Syntax: Beispiel

CREATE [OR REPLACE] TYPE ‹inner_type› CREATE TYPE Spoken_T AS OBJECT
AS OBJECT (...); ( Name VARCHAR2(50),
/ Percentage NUMBER );
CREATE [OR REPLACE] TYPE ‹inner_table_type› /
AS TABLE OF ‹inner_type›; CREATE TYPE Spoken_list
/ AS TABLE OF Spoken_T;
/
CREATE TABLE ‹table_name›
CREATE TABLE Nested_Spoken
( ... ,
( Country VARCHAR2(4),
‹table-attr› ‹inner_table_type› ,
Languages Spoken_list)
... )
NESTED TABLE Languages
NESTED TABLE ‹table-attr›
STORE AS Lang_nested;
STORE AS ‹name ›;

Einfügen mit Konstruktormethoden

INSERT INTO Nested_Spoken

VALUES (’CH’, Spoken_list (Spoken_T(’German’,65), Spoken_T(’French’,18),
Spoken_T(’Italian’,12), Spoken_T(’Romansch’,1)));
[ Filename (alles): [Link] ]

6.4 Geschachtelte Tabellen 129

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

Geschachtelte Tabellen

SELECT *
FROM Nested_Spoken Country Languages(Name, Percentage)
WHERE Country=’CH’; CH Spoken_List(Spoken_T(’French’, 18),
Spoken_T(’German’, 65),
Spoken_T(’Italian’, 12),
Spoken_T(’Romansch’, 1))

SELECT Languages
FROM Nested_Spoken Languages(Name, Percentage)
WHERE Country=’CH’; Spoken_List(Spoken_T(’French’, 18),
Spoken_T(’German’, 65),
Spoken_T(’Italian’, 12),
Spoken_T(’Romansch’, 1))

6.4 Geschachtelte Tabellen 130

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

Anfragen an Geschachtelte Tabellen

Inhalt von inneren Tabellen:

THE (SELECT ‹table-attr› FROM ...)
SELECT ...
FROM THE (‹select-statement›)
WHERE ... ;
INSERT INTO THE (‹select-statement›)
VALUES ... / SELECT ... ;
DELETE FROM THE (‹select-statement›)
WHERE ... ;
SELECT Name, Percentage
FROM THE ( SELECT Languages Name Percentage
FROM Nested_Spoken German 65
WHERE Country=’CH’ ); French 18
Italian 12
Romansch 1

6.4 Geschachtelte Tabellen 131

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

Füllen von Geschachtelten Tabellen

Geschachtelte Tabelle “am Stück” einfügen: Menge von Tupeln wird als Kollektion strukturiert:
collect() über mehrspaltige Tupel nicht erlaubt
-- nicht erlaubt:
INSERT INTO Nested_Spoken
(SELECT country, collect(name,percentage)
FROM language GROUP BY country)
-- PLS-306: wrong number or types of arguments in call to ’SYS_NT_COLLECT’
... also anders: Tupelmenge als Tabelle casten
CAST(MULTISET(SELECT ...) AS ‹nested-table-type›)
INSERT INTO Nested_Spoken -- syntaktisch zulässig, macht aber etwas falsches !!!!
(SELECT Country,
CAST(MULTISET(SELECT Name, Percentage
FROM Language
WHERE Country = [Link])
AS Spoken_List)
FROM Language A); jedes Tupel (Land, dessen Sprachenliste) n-mal
(n = Anzahl Sprachen in diesem Land), weil jedes Land n Tupel in ’Language’ hat)

6.4 Geschachtelte Tabellen 132

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

Füllen von Geschachtelten Tabellen

... also erst Tupel erzeugen und dann die geschachtelten Tabellen mit einer korrelierten
SET-Subquery hinzufügen:

INSERT INTO Nested_Spoken (Country)

( SELECT DISTINCT Country
FROM Language);

UPDATE Nested_Spoken B
SET Languages =
CAST(MULTISET(SELECT Name, Percentage
FROM Language A
WHERE [Link] = [Link])
AS Spoken_List);

6.4 Geschachtelte Tabellen 133

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

A RBEITEN MIT G ESCHACHTELTEN TABELLEN

Mit THE und TABLE wie für Collections beschrieben:

• Kopieren ganzer eingebetteter Tabellen mit
INSERT INTO ... VALUES(..., THE(SELECT ...),...);
INSERT INTO ... (SELECT ..., THE (SELECT ...)...);
INSERT INTO THE (...) ...;
DELETE FROM THE ( ) ...;
UPDATE THE (...) ...;
• TABLE(‹attr›) in Unterabfrage:
SELECT Country
FROM Nested_Spoken
WHERE ’German’ IN (SELECT name
FROM TABLE (Languages));
• TABLE(‹attr›) als korreliertes Join:
SELECT Country, nll.*
FROM Nested_Spoken nl, TABLE([Link]) nll;

6.4 Geschachtelte Tabellen 134

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

KOMPLEXE DATENTYPEN
SELECT * FROM USER_TYPES

Type_name Type_oid Typecode Attrs Meths

GeoCoord _ Object 2 0
Spoken_T _ Object 2 0
Mon_Orglist _ Collection 0 0
Languages_List _ Collection 0 0
Löschen: DROP TYPE [FORCE]
Mit FORCE kann ein Typ gelöscht werden, dessen Definition von anderen Typen noch gebraucht
wird.
Szenario von oben:
DROP TYPE Spoken_T
“Typ mit abhängigen Typen oder Tabellen kann nicht gelöscht oder ersetzt werden”
DROP TYPE Spoken_T FORCE löscht Spoken_T, allerdings
SQL> desc Languages_List;
FEHLER: ORA-24372: Ungültiges Objekt für Beschreibung

6.4 Geschachtelte Tabellen 135

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

Kapitel 7
TEIL II: Dies und Das
Teil I: Grundlagen
Teil II: Weiteres zum “normalen” SQL
• ER-Modell und relationales Datenmo-
• Änderungen des Datenbankschemas
dell
• Referentielle Integrität
• Umsetzung in ein Datenbankschema:
• Transaktionen und Integritätsbedingungen
CREATE TABLE
• View Updates
• Anfragen: SELECT – FROM – WHERE
• Zugriffsrechte
• Arbeiten mit der Datenbank: DELETE,
• Anpassung der Datenbank an Sprache, Zei-
UPDATE
chensatz etc.
• Optimierung

Teil III: Erweiterungen

Prozedurale Konzepte, OO, Einbettung

7.0 Ändern des Datenbankschemas 136

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

7.1 Ändern von Schemaobjekten

• CREATE-Anweisung
• ALTER-Anweisung
• DROP-Anweisung

• TABLE
• VIEW
• TYPE
• INDEX
• ROLE
• PROCEDURE
• TRIGGER
..
.

7.1 Ändern des Datenbankschemas 137

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

Ä NDERN VON TABELLEN : ALTER TABLE

• Spalten und Bedingungen hinzufügen,
• bestehende Spaltendeklarationen verändern,
• Spalten löschen,
• Bedingungen löschen, zeitweise außer Kraft setzen und wieder aktivieren.
ALTER TABLE ‹table›
ADD ‹add-clause›
MODIFY ‹modify-clause›
DROP ‹drop-clause›
DISABLE ‹disable-clause›
ENABLE ‹enable-clause›
RENAME TO ‹new-table-name›
• jede der obigen Zeilen kann beliebig oft vorkommen (keine Kommas dazwischen!),
• eine solche Zeile enthält eine oder mehrere Änderungs-Spezifikationen, z.B.
MODIFY ‹modify-item›
MODIFY (‹modify-item›, ..., ‹modify-item›)
• Syntaxvielfalt nützlich wenn die Statements automatisch generiert werden.

7.1 Ändern von Tabellen 138

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

Hinzufügen von Spalten zu einer Tabelle

ALTER TABLE ‹table›
ADD (‹col› ‹datatype› [DEFAULT ‹value›]
[‹colConstraint› ... ‹colConstraint›],
.
.
.
‹col› ‹datatype› [DEFAULT ‹value›]
[‹colConstraint› ... ‹colConstraint›],
‹add table constraints›...);
Neue Spalten werden mit NULL-Werten aufgefüllt.
Beispiel: Erweitern der Relation economy um eine Spalte unemployment mit
Spaltenbedingung:
ALTER TABLE Economy
ADD unemployment NUMBER CHECK (unemployment >= 0);

Entfernen von Spalten

ALTER TABLE ‹table›
DROP (‹column-name-list›);
ALTER TABLE ‹table›
DROP COLUMN ‹column-name›;

7.1 Ändern von Tabellen 139

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

H INZUFÜGEN VON TABELLENBEDINGUNGEN

ALTER TABLE ‹table›
ADD (‹... add some columns ... ›,
‹tableConstraint›,
.
.
.
‹tableConstraint›);
Hinzufügen einer unemployment-Spalte und der Zusicherung, dass die Summe der Anteile von
Industrie, Dienstleistung und Landwirtschaft am Bruttosozialprodukt maximal 100% ist:
ALTER TABLE Economy
ADD (unemployment NUMBER CHECK (unemployment >= 0),
CHECK (industry + service + agriculture <= 102));

• Soll eine Bedingung hinzugefügt werden, die im momentanen Zustand verletzt ist, erhält
man eine Fehlermeldung.
ALTER TABLE City
ADD (CONSTRAINT citypop CHECK (population > 100000));

7.1 Ändern von Tabellen 140

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

Spaltendefinitionen einer Tabelle ändern

ALTER TABLE ‹table›

MODIFY (‹col› [‹datatype›] [DEFAULT ‹value›]
[‹colConstraint› ... ‹colConstraint›],
.
.
.
‹col› [‹datatype›] [DEFAULT ‹value›]
[‹colConstraint› ... ‹colConstraint›]);
ALTER TABLE Country MODIFY (Capital NOT NULL);
ALTER TABLE encompasses ADD (PRIMARY KEY (Country,Continent));
ALTER TABLE Desert
MODIFY (area CONSTRAINT DesertArea CHECK (area > 10));
ALTER TABLE isMember
MODIFY (type VARCHAR2(10))) – change maximal length;

• Hinzufügen von Spaltenbedingungen – Fehlermeldung, falls eine Bedingung formuliert

wird, die der aktuelle Datenbankzustand nicht erfüllt.
• Datentypänderungen (z.B. NUMBER zu VARCHAR2(n)) nur erlaubt wenn die Spalte leer ist,
• Änderung der Länge von VARCHAR-Spalten ist jederzeit möglich: VARCHAR2(n) →
VARCHAR2(k).

7.1 Ändern von Tabellen 141

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

Integritätsbedingungen (de)aktivieren
• (Integritäts)bedingungen an eine Tabelle
- entfernen,
- zeitweise außer Kraft setzen,
- wieder aktivieren.
ALTER TABLE ‹table›
DROP PRIMARY KEY [CASCADE] |
UNIQUE (‹column-list›) |
CONSTRAINT ‹constraint›
DISABLE PRIMARY KEY [CASCADE] |
UNIQUE (‹column-list›) |
CONSTRAINT ‹constraint› | ALL TRIGGERS
ENABLE PRIMARY KEY |
UNIQUE (‹column-list›) |
CONSTRAINT ‹constraint› | ALL TRIGGERS;
• PRIMARY KEY darf nicht gelöscht/disabled werden solange REFERENCES-Deklaration besteht.
• DROP PRIMARY KEY CASCADE löscht/disabled REFERENCES-Deklarationen darauf ebenfalls.
• ENABLE: kaskadierend disable’te Constraints müssen manuell reaktiviert werden.
7.1 Ändern von Tabellen 142
 Praktikum: Datenbankprogrammierung in SQL/ORACLE

7.2 Referentielle Integrität

Referentielle Integritätsbedingungen treten dort auf, wo bei der Umsetzung vom ER-Modell
zum relationalen Modell Schlüsselattribute der beteiligten Entities in Beziehungstypen eingehen
(Zusammenhang von Primär- und Fremdschlüsseln):
Europe R
name code

continent encompasses Country

percent

CREATE TABLE Country 20 CREATE TABLE encompasses

(name VARCHAR2(50), ( Continent VARCHAR2(20)
code VARCHAR2(4) PRIMARY KEY, REFERENCES Continent(name),
...); Country VARCHAR2(4)
REFERENCES Country(code),
CREATE TABLE Continent
percentageNUMBER);
(name VARCHAR2(20) PRIMARY KEY,
area NUMBER(2));

7.2 Referentielle Integrität 143

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

R EFERENTIELLE I NTEGRITÄT

Country
Name Code Capital Province
Germany D Berlin Berlin
United States USA Washington Distr. Columbia
... ... ... ...

City
Name Country Province
Berlin D Berlin
Washington USA Distr. Columbia
... ... ...

FOREIGN KEY (‹attr-list›)

REFERENCES ‹table’› (‹attr-list’›)
• (‹attr-list’›) muss Candidate Key der referenzierten Tabelle sein (NOT NULL UNIQUE).

7.2 Referentielle Integrität 144

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

R EFERENTIELLE I NTEGRITÄT
• als Spaltenbedingung:
‹attr› [CONSTRAINT ‹name›]
REFERENCES ‹table’›(‹attr’›)
CREATE TABLE City
(...
Country VARCHAR2(4)
CONSTRAINT CityRefsCountry
REFERENCES Country(Code) );
• als Tabellenbedingung:
[CONSTRAINT ‹name›]
FOREIGN KEY (‹attr-list›)
REFERENCES ‹table’›(‹attr-list’›)
CREATE TABLE Country
(...
CONSTRAINT CapitalRefsCity
FOREIGN KEY (Capital,Code,Province)
REFERENCES City(Name,Country,Province) );

7.2 Referentielle Integrität 145

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

R EFERENTIELLE A KTIONEN
• Bei Veränderungen am Inhalt einer Tabelle sollen automatisch Aktionen ausgeführt
werden, um die referentielle Integrität der Datenbasis zu erhalten.
• Ist dies nicht möglich, so werden die gewünschten Operationen nicht ausgeführt, bzw.
zurückgesetzt.
1. INSERT in die referenzierte Tabelle oder DELETE aus der referenzierenden Tabelle ist immer
unkritisch:
INSERT INTO Country VALUES (’Lummerland,’LU’,...);
DELETE FROM isMember WHERE country=’D’;
2. Ein INSERT oder UPDATE in der referenzierenden Tabelle, darf keinen Fremdschlüsselwert
erzeugen, der nicht in der referenzierten Tabelle existiert:
INSERT INTO City VALUES (’Karl-Marx-Stadt’,’DDR’,...);
Anderenfalls ist es unkritisch:
UPDATE City SET Country=’A’ WHERE Name=’Munich’;
3. DELETE und UPDATE bzgl. der referenzierten Tabelle: Anpassung der referenzierenden
Tabelle durch Referentielle Aktionen sinnvoll:
UPDATE Country SET Code=’UK’ WHERE Code=’GB’; oder
DELETE FROM Country WHERE Code=’I’;

7.2 Referentielle Integrität 146

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

Referentielle Aktionen im SQL-2-Standard

NO ACTION:
Die Operation wird zunächst ausgeführt; nach der Operation wird überprüft, ob “dangling
references” entstanden sind und ggf. die Aktion zurückgenommen:
DELETE FROM River;
Untscheidung zwischen Referenz River - River und located - River !
RESTRICT:
Die Operation wird nur dann ausgeführt, wenn keine “dangling references” entstehen
können:
DELETE FROM Organization WHERE ...;
Fehlermeldung, wenn eine Organisation gelöscht werden müsste, die Mitglieder besitzt.
CASCADE:
Die Operation wird ausgeführt. Die referenzierenden Tupel werden ebenfalls gelöscht bzw.
geändert.
UPDATE Country SET Code=’UK’ WHERE Code=’GB’;
ändert überall:
Country: (United Kingdom, GB, . . . ) ❀ (United Kingdom, UK, . . . )
Province: (Yorkshire, GB, . . . ) ❀ (Yorkshire, UK, . . . )
City: (London, GB, Greater London, . . . ) ❀ (London, UK, Greater London, . . . )

7.2 Referentielle Integrität 147

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

Referentielle Aktionen im SQL-2-Standard

SET DEFAULT:
Die Operation wird ausgeführt und bei den referenzierenden Tupeln wird der
entsprechende Fremdschlüsselwert auf die für die entsprechende Spalten festgelegten
DEFAULT-Werte gesetzt (dafür muss dann wiederum ein entsprechendes Tupel in der
referenzierten Relation existieren). Falls kein DEFAULT-Wert definiert wurde, entspricht das
Verhalten SET NULL (s.u.).
SET NULL:
Die Operation wird ausgeführt und bei den referenzierenden Tupeln wird der
entsprechende Fremdschlüsselwert durch NULL ersetzt (dazu müssen NULLs zulässig sein).
located: Stadt liegt an Fluss/See/Meer
located(Bremerhaven,Bremen,D,Weser,NULL,North Sea)
DELETE FROM River WHERE Name=’Weser’;
located(Bremerhaven,Bremen,D,NULL,NULL,North Sea)

7.2 Referentielle Integrität 148

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

Referentielle Aktionen im SQL-2-Standard

Referentielle Integritätsbedingungen und Aktionen werden bei CREATE TABLE und ALTER TABLE
als
‹columnConstraint› (für einzelne Spalten)
‹col› ‹datatype›
CONSTRAINT ‹name›
REFERENCES ‹table’› (‹attr’›)
[ ON DELETE {NO ACTION | RESTRICT | CASCADE |
SET DEFAULT | SET NULL } ]
[ ON UPDATE {NO ACTION | RESTRICT | CASCADE |
SET DEFAULT | SET NULL } ]
oder ‹tableConstraint› (für mehrere Spalten)
CONSTRAINT ‹name›
FOREIGN KEY (‹attr-list›)
REFERENCES ‹table’› (‹attr-list’›)
[ ON DELETE ...]
[ ON UPDATE ...]
angegeben.

7.2 Referentielle Integrität 149

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

Referentielle Aktionen

Country
Name Code Capital Province
Germany D Berlin Berlin
United States USA Washington Distr. Columbia
... ... ... ...
CASCADE
NO ACTION

City
Name Country Province
Berlin D Berlin
Washington USA Distr. Columbia
... ... ...

1. DELETE FROM City WHERE Name=’Berlin’;

2. DELETE FROM Country WHERE Name=’Germany’;

7.2 Referentielle Integrität 150

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

Referentielle Aktionen in ORACLE:

• ORACLE 9-11: nur ON DELETE/UPDATE NO ACTION, ON DELETE CASCADE und (seit Oracle
8.1.5) ON DELETE SET NULL implementiert.
• Wird ON ... nicht angegeben, wird NO ACTION als Default verwendet.
• ON UPDATE CASCADE fehlt, was beim Durchführen von Updates ziemlich lästig ist.
• Hat aber so seine Gründe ...
Syntax als ‹columnConstraint›:
CONSTRAINT ‹name›
REFERENCES ‹table’› (‹attr’›)
[ON DELETE CASCADE|ON DELETE SET NULL]
Syntax als ‹tableConstraint›:
CONSTRAINT ‹name›
FOREIGN KEY [ (‹attr-list›)]
REFERENCES ‹table’› (‹attr-list’›)
[ON DELETE CASCADE|ON DELETE SET NULL]
• Hinweis: MS SQL Server unterstützt ON UPDATE CASCADE, jedoch kein ON DELETE/UPDATE
CASCADE auf rekursiven Schemata (z.B. River/flows_into) (Stand 2013)!

7.2 Referentielle Integrität 151

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

Referentielle Aktionen: UPDATE ohne CASCADE

Beispiel: Umbenennung eines Landes:
CREATE TABLE Country
( Name VARCHAR2(50) NOT NULL UNIQUE,
Code VARCHAR2(4) PRIMARY KEY);
(’United Kingdom’,’GB’)
CREATE TABLE Province
( Name VARCHAR2(50)
Country VARCHAR2(4) CONSTRAINT ProvRefsCountry
REFERENCES Country(Code));
(’Yorkshire’,’GB’)
Nun soll das Landeskürzel von ’GB’ nach ’UK’ geändert werden.
• UPDATE Country SET Code=’UK’ WHERE Code=’GB’;
❀ “dangling reference” des alten Tupels (’Yorkshire’,’GB’).
• UPDATE Province SET Code=’UK’ WHERE Code=’GB’;
❀ “dangling reference” des neuen Tupels (’Yorkshire’,’UK’).
Behandlung siehe Folie 158.

7.2 Referentielle Integrität 152

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

Referentielle Aktionen: Zyklische Referenzen

Country
Name Code Capital Province
Germany D Berlin Berlin
United States US Washington [Link].
... ... ... ...

Province
Name Country Capital
Berlin D Berlin
[Link]. US Washington
... ... ...

City
Name Country Province
Berlin D B
Washington USA [Link].
... ... ...

7.2 Referentielle Integrität 153

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

Referentielle Aktionen: Problematik

Country
Name Code Capital Province
Germany D Berlin Berlin
United States US Washington [Link].
... ... ... ...
CASCADE
SET NULL
Province
Name Country Capital
Berlin D Berlin
[Link]. US Washington
... ... ...
CASCADE
City
Name Country Province DELETE FROM Country
Berlin D B WHERE Code=’D’
Washington USA [Link].
... ... ...

7.2 Referentielle Integrität 154

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

Referentielle Aktionen: Semantische und Operationale Probleme

Im allgemeinen Fall:
• Schon eine einzelne Operation bringt in Verbindung mit ON DELETE/UPDATE SET NULL/SET
DEFAULT und ON UPDATE CASCADE Mehrdeutigkeiten, Widersprüche etc.
• Aufgrund von SQL-Triggern induziert ein User-Update häufig mehrere Datenbank-Updates,
• nichttriviale Entscheidung, welche Updates getriggert werden sollen,
• im Fall von Inkonsistenzen Analyse der Ursache sowie maximal zulässiger Teilmengen der
User-Requests,
• Stabile Modelle, exponentieller Aufwand.
... siehe dbis-Webseiten

7.2 Referentielle Integrität 155

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

7.3 Transaktionen und Integritätsbedingungen

• Theorie: Integritätsbedingungen werden (erst) zum Ende der Transaktion überprüft (und
nicht nach jedem einzelnen Statement)
• Praxis: Es ist effizienter (und manchmal benutzerfreundlicher), sie nach jedem einzelnen
Statement zu überprüfen.
⇒ konfigurierbar:
IMMEDIATE vs. DEFERRED

7.3 Referentielle Integrität 156

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

Syntax

• Bei Definition (CREATE TABLE und ALTER TABLE) von Constraints kann DEFERRABLE bzw.
NOT DEFERRABLE (Default) und im Fall von DEFERRABLE INITIALLY DEFERRED oder
INITIALLY IMMEDIATE (was auch der Default ist) angegeben werden.
ALTER TABLE isMember
ADD CONSTRAINT MemberCRef
FOREIGN KEY (Country) REFERENCES Country(Code)
ON DELETE CASCADE
DEFERRABLE INITIALLY DEFERRED
ADD CONSTRAINT MemberOrgRef
FOREIGN KEY (Organization) REFERENCES Organization(Abbreviation)
DEFERRABLE;
• (Oracle): Das Setting kann vom Benutzer (lokal) geändert werden (für diejenigen
Constraints, die überhaupt DEFERRABLE sind):
– Für die aktuelle Transaktion (Hinweis: mit einem DDL-Statement würde diese beendet!):
SET CONSTRAINT[S] {‹name›|ALL} {IMMEDIATE|DEFERRED}
– Für die ganze Session, geht dann nur für alle Constraints gemeinsam:
ALTER SESSION SET CONSTRAINT[S] = {IMMEDIATE|DEFERRED}

7.3 Referentielle Integrität 157

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

Beispiel: UPDATE ohne CASCADE

Situation von Folie 152:
Änderung des Landescodes “GB” auf “UK” innerhalb der gesamten Datenbank:
-- mondial mit allen constraints DEFERRABLE INITIALLY DEFERRED erzeugen
-- replace in [Link] ";"->"DEFERRABLE INITIALLY DEFERRED;"
-- oder SET CONSTRAINTS ALL DEFERRED falls sie schon deferrable sind
UPDATE Country SET Code=’UK’ WHERE Code=’GB’;
1 row updated.
-- weitere Updates aller Referenzen auf [Link] --
UPDATE Province SET Country=’UK’ WHERE Country=’GB’;
-- weitere Updates aller Referenzen auf [Link] --
falls zu früh
COMMIT;
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-02292: integrity constraint ([Link]) violated - child record found
• wenn jetzt eine Referenz vergessen wurde, wird beim COMMIT ein komplettes Rollback
ausgeführt, und man muss von vorne anfangen!
⇒ mit Skript arbeiten.

7.3 Referentielle Integrität 158

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

Vor- und Nachteile von DEFERRED/IMMEDIATE

• DEFERRED manchmal notwendig bei Updates,

• Bei großen Mengen von Updates ist DEFERRED effizienter (1x alle prüfen statt mehrmals
dasselbe tun – mengenorientiert statt tupelorientiert),
• Eigentlich Unique-Indexe auf Keys können bei DEFERRED zeitweise verletzt sein
→ DB arbeitet ggf. mit Multiindexen
→ ineffizienter beim Lesen
(deshalb ist IMMEDIATE Default)
• Bei DEFERRED bekommt man Fehlermeldungen erst am Transaktionsende.
• Hinweis: MS SQL Server unterstützt keine DEFERRED-Constraints (Stand 2013).

7.3 Referentielle Integrität 159

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

Einzelanalyse DEFERRED/IMMEDIATE
• CHECK-Constraints: IMMEDIATE meistens sinnvoller,
• PRIMARY KEY, UNIQUE: IMMEDIATE meistens sinnvoller,
• NOT NULL: DEFERRED praktisch um ein eingefügtes Tupel erst später zu vervollständigen,
• Referentielle Integritätsbedingungen:
– wenn man die Einfügereihenfolge nicht weiß, ist DEFERRED sinnvoll,
– bei zyklischen Strukturen (Country/Capital/City) sollte mindestens ein Constraint
DEFERRED sein.
DEFERRED/IMMEDIATE vs. DISABLE/ENABLE
• DISABLE/ENABLE erfordert ALTER TABLE-Rechte,
DEFERRABLE wird einmal vom Admin gesetzt, und dann kann jeder Benutzer in seiner
Session entscheiden, ob er DEFERRED setzen möchte.
• Mehrbenutzerbetrieb: Ausschalten der Constraints gilt für alle. Beim Wiedereinschalten hat
man die Fehler der anderen mit am Hals.
• bei sehr komplexen Updates will man mit Transaktionen arbeiten und
Bedingungsverletzungen zeitweise erlauben
→ alleine, Constraints disablen, schrittweise committen.

7.3 Referentielle Integrität 160

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

7.4 View Updates

Views

• Darstellung des realen Datenbestand für Benutzer in einer veränderten Form.

• Kombination mit der Vergabe von Zugriffsrechten (später)

View Updates

• müssen intern operational auf Updates der Basisrelation(en) abgebildet werden,

• nicht immer möglich.
• Tabelle USER_UPDATABLE_COLUMNS im Data Dictionary:
CREATE VIEW ‹name› AS ...
SELECT * FROM USER_UPDATABLE_COLUMNS
WHERE Table_Name = ’‹NAME›’;

7.4 View Updates 161

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

View Updates

• abgeleitete Werte können nicht verändert werden:

Beispiel:
CREATE OR REPLACE VIEW temp AS
SELECT Name, Code, Area, Population, Table_Name Column_Name UPD INS DEL
Population/Area AS Density temp Name yes yes yes
FROM Country; temp Code yes yes yes
temp Area yes yes yes
SELECT * FROM USER_UPDATABLE_COLUMNS temp Population yes yes yes
WHERE Table_Name = ’TEMP’;
temp Density no no no

INSERT INTO temp (Name, Code, Area, Population)

VALUES (’Lummerland’,’LU’,1,4)
SELECT * FROM temp where Code = ’LU’;
• analog für Werte die als Ergebnis von Aggregatfunktionen berechnet werden (COUNT, AVG,
MAX, . . . )

7.4 View Updates 162

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

View Updates: Beispiel

Beispiel:
CREATE VIEW CityCountry (City, Country) AS
SELECT [Link], [Link]
FROM City, Country Table_Name Column_Name UPD INS DEL
WHERE [Link] = [Link];
CityCountry City yes yes yes
SELECT * FROM USER_UPDATABLE_COLUMNS
CityCountry Country no no no
WHERE Table_Name = ’CITYCOUNTRY’;
• Städte(namen) können verändert werden:
direkte Abbildung auf City:
UPDATE CityCountry
SET City = ’Wien’
WHERE City = ’Vienna’;
SELECT * FROM City WHERE Country = ’A’;

Name Country Province ...

Wien A Vienna ...
.. .. .. ..
. . . .

7.4 View Updates 163

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

View Updates

Beispiel:
• Country darf nicht verändert werden:

City Country
Berlin Germany
Freiburg Germany

Umsetzung auf Basistabelle wäre nicht eindeutig:

UPDATE CityCountry UPDATE CityCountry
SET Country = ’Poland’ SET Country = ’Deutschland’
WHERE City = ’Berlin’; WHERE Country = ’Germany’;
Nur in City werden die Tupel gelöscht:
DELETE FROM CityCountry DELETE FROM CityCountry
WHERE City = ’Berlin’; WHERE Country = ’Germany’;

7.4 View Updates 164

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

View Updates

• O RACLE: Zulässigkeitsentscheidung durch Heuristiken

• basieren nur auf Schemainformation,
• nicht auf aktuellem Datenbankzustand !
• Schlüsseleigenschaften wichtig: Schlüssel einer Basistabelle müssen im View erhalten
bleiben.
• Schlüssel einer Basistabelle = Schlüssel des Views:
Abbildung möglich.
• Schlüssel einer Basistabelle ⊇ ein Schlüssel des Views: Umsetzung möglich.
(bei ( sind eventuell mehrere Tupel der Basistabelle betroffen).
• Schlüssel einer Basistabelle überdeckt keinen Schlüssel des Views: i.a. keine Umsetzung
möglich (siehe Aufgaben).
• die Heuristik ist nicht immer so ganz korrekt (siehe Aufgaben).

7.4 View Updates 165

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

View Updates

Beispiel:
CREATE OR REPLACE VIEW temp AS
SELECT country, population
FROM Province A
WHERE population = (SELECT MAX(population)
FROM Province B
WHERE [Link] = [Link]);
SELECT * FROM temp WHERE Country = ’D’;
Country Name Population
D Nordrhein-Westfalen 17816079
UPDATE temp
SET population = 0 where Country = ’D’;
SELECT * FROM Province WHERE Name = ’D’;
Ergebnis: die Bevölkerung der bevölkerungsreichsten Provinz Deutschlands wird auf 0 gesetzt.
Damit ändert sich auch das View !
SELECT * FROM temp WHERE Country = ’D’; Country Name Population
D Bayern 11921944

7.4 View Updates 166

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

View Updates

• Tupel können durch Update aus dem Wertebereich des Views hinausfallen.
• Views häufig verwendet, um den “Aktionsradius” eines Benutzers einzuschränken.
• Verlassen des Wertebereichs kann durch WITH CHECK OPTION verhindert werden:
Beispiel
CREATE OR REPLACE VIEW UScities AS
SELECT *
FROM City
WHERE Country = ’USA’
WITH CHECK OPTION;
UPDATE UScities
SET Country = ’D’ WHERE Name = ’Miami’;
FEHLER in Zeile 1:
ORA-01402: Verletzung der WHERE-Klausel
einer View WITH CHECK OPTION
Es ist übrigens erlaubt, Tupel aus dem View zu löschen.

7.4 View Updates 167

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

M ATERIALIZED V IEWS
• Views werden bei jeder Anfrage neu berechnet.
+ repräsentieren immer den aktuellen Datenbankzustand.
- zeitaufwendig, ineffizient bei wenig veränderlichen Daten
⇒ Materialized Views
• werden bei der Definition berechnet und
• bei jeder Datenänderung automatisch aktualisiert (u.a. durch Trigger ).
• ⇒ Problem der View Maintenance.

7.4 View Updates 168

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

7.5 Zugriffsrechte
Benutzeridentifikation

• Benutzername
• Password
• sqlplus /: Identifizierung durch UNIX-Account

Zugriffsrechte innerhalb ORACLE

• Zugriffsrechte an ORACLE-Account gekoppelt

• initial vom DBA vergeben

Schemakonzept

• Jedem Benutzer ist sein Database Schema zugeordnet, in dem “seine” Objekte liegen.
• Bezeichnung der Tabellen global durch ‹username›.‹table›
(z.B. [Link]),
• im eigenen Schema nur durch ‹table›.

7.5 Zugriffsrechte 169

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

S YSTEMPRIVILEGIEN
• berechtigen zu Schemaoperationen
• CREATE [ANY] TABLE/VIEW/TYPE/INDEX/CLUSTER/TRIGGER/PROCEDURE:
Benutzer darf die entsprechenden Schema-Objekte erzeugen,
• ALTER [ANY] TABLE/TYPE/TRIGGER/PROCEDURE:
Benutzer darf die entsprechenden Schema-Objekte verändern,
• DROP [ANY] TABLE/VIEW/TYPE/INDEX/CLUSTER/TRIGGER/PROCEDURE:
Benutzer darf die entsprechenden Schema-Objekte löschen.
• SELECT/INSERT/UPDATE/DELETE [ANY] TABLE:
Benutzer darf in Tabellen Tupel lesen/erzeugen/verändern/entfernen.

• ANY: Operation in jedem Schema erlaubt,

• ohne ANY: Operation nur im eigenen Schema erlaubt
Praktikum:
• CREATE SESSION, ALTER SESSION, CREATE TABLE, CREATE VIEW, CREATE SYNONYM,
CREATE PROCEDURE...
• Zugriffe und Veränderungen an den eigenen Tabellen nicht explizit aufgeführt (SELECT
TABLE).
7.5 Zugriffsrechte 170
 Praktikum: Datenbankprogrammierung in SQL/ORACLE

S YSTEMPRIVILEGIEN
Rechte vergeben: GRANT ‹privilege-list› TO ‹user-list› | PUBLIC
[ WITH ADMIN OPTION ];
• PUBLIC: jeder erhält das Recht.
• WITH ADMIN OPTION: Empfänger darf dieses Recht weiter vergeben.
Rechte entziehen: REVOKE ‹privilege-list› | ALL
FROM ‹user-list› | PUBLIC;
nur wenn man dieses Recht selbst vergeben hat (im Fall von ADMIN OPTION kaskadierend).
Beispiele:
• GRANT CREATE ANY INDEX, DROP ANY INDEX erlaubt opti-person, überall Indexe zu
TO opti-person WITH ADMIN OPTION; erzeugen und zu löschen,
• GRANT DROP ANY TABLE TO destroyer; darf überall Tabellen löschen
GRANT SELECT ANY TABLE TO supervisor; darf alle Tabellen aller Benutzer lesen
• REVOKE CREATE TABLE FROM mueller; darf keine Tabellen mehr erzeugen.
Informationen über eigene Systemprivilegien: SELECT * FROM SESSION_PRIVS;
Stichwort: Rollenkonzept – fasst eine Menge von Rechten zusammen

7.5 Zugriffsrechte 171

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

O BJEKTPRIVILEGIEN
berechtigen dazu, Operationen auf existierenden Objekten auszuführen.
• Eigentümer eines Datenbankobjektes
• Niemand sonst darf mit einem solchen Objekt arbeiten, außer
• Eigentümer (oder DBA) erteilt explizit entsprechende Rechte:
GRANT ‹privilege-list› | ALL [(‹column-list›)]
ON ‹object›
TO ‹user-list› | PUBLIC
[ WITH GRANT OPTION ];
• ‹object›: eine Instanz von TABLE, VIEW, PROCEDURE/FUNCTION, TYPE,
• Tabellen und Views: Genauere Einschränkung für INSERT, REFERENCES und UPDATE durch
‹column-list›,
• ‹privilege-list›: DELETE, INSERT, SELECT, UPDATE für Tabellen und Views,
INDEX, ALTER und REFERENCES für Tabellen,
EXECUTE für Prozeduren, Funktionen und TYPEn.
• ALL: alle Privilegien die man an dem Objekt (ggf. auf der angegebenen Spalte) hat.
• GRANT OPTION: Der Empfänger darf das Recht weitergeben.
7.5 Zugriffsrechte 172
 Praktikum: Datenbankprogrammierung in SQL/ORACLE

Objektprivilegien (Forts.)

Rechte entziehen:
REVOKE ‹privilege-list› | ALL
ON ‹object›
FROM ‹user-list› | PUBLIC
[CASCADE CONSTRAINTS];
• CASCADE CONSTRAINTS (bei REFERENCES): alle referentiellen Integritätsbedingungen, die auf
einem entzogenen REFERENCES-Privileg beruhen, fallen weg.
• Berechtigung von mehreren Benutzern erhalten: Fällt mit dem letzten REVOKE weg.
• im Fall von GRANT OPTION kaskadierend.
Überblick über vergebene/erhaltene Rechte:
SELECT * FROM USER_TAB_PRIVS;
• Rechte, die man für eigene Tabellen vergeben hat,
• Rechte, die man für fremde Tabellen bekommen hat
SELECT * FROM USER_COL_PRIVS;
SELECT * FROM USER_TAB/COL_PRIVS_MADE/RECD;

7.5 Zugriffsrechte 173

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

S YNONYME

Schemaobjekt unter einem anderen Namen als ursprünglich abgespeichert ansprechen:

CREATE [PUBLIC] SYNONYM ‹synonym›
FOR ‹schema›.‹object›;
• Ohne PUBLIC: Synonym ist nur für den Benutzer definiert.
• PUBLIC ist das Synonym systemweit verwendbar. Geht nur mit CREATE ANY
SYNONYM-Privileg.

Beispiel: Benutzer will oft die Relation “City”, aus dem Schema “dbis” verwenden.
• SELECT * FROM [Link];
• CREATE SYNONYM DCity
FOR [Link];
SELECT * FROM DCity;

Synonyme löschen: DROP SYNONYM ‹synonym›;

7.5 Zugriffsrechte 174

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

Z UGRIFFSEINSCHRÄNKUNG ÜBER V IEWS

• GRANT SELECT kann nicht auf Spalten eingeschränkt werden.
• Stattdessen: Views verwenden.
GRANT SELECT [‹column-list›] -- nicht erlaubt
ON ‹table›
TO ‹user-list› | PUBLIC
[ WITH GRANT OPTION ];
kann ersetzt werden durch
CREATE VIEW ‹view› AS
SELECT ‹column-list›
FROM ‹table›;
GRANT SELECT
ON ‹view›
TO ‹user-list› | PUBLIC
[ WITH GRANT OPTION ];

7.5 Zugriffsrechte 175

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

Zugriffseinschränkung über Views: Beispiel

pol ist Besitzer der Relation Country, will Country ohne Hauptstadt und deren Lage für geo les-
und schreibbar machen.
View mit Lese- und Schreibrecht für geo:
CREATE VIEW pubCountry AS
SELECT Name, Code, Population, Area
FROM Country;
GRANT SELECT, INSERT, DELETE, UPDATE
ON pubCountry TO geo;
• Referenzen auf Views müssen separat erlaubt werden:
‹pol›: GRANT REFERENCES (Code) ON Country TO geo;
‹geo›: ... REFERENCES [Link](Code);

7.5 Zugriffsrechte 176

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

D URCHGRIFF AUF ANDERE B ENUTZER / REINE S CHEMA -ACCOUNTS

• Man (Admin!) kann einem Benutzer erlauben, “sich” mit dem Rechten eines anderen
Accounts (und dessen Schema) einzuloggen.
• Dabei kann man auch User anlegen, die nur als “Schema-Accounts” existieren, und sich
selber garnicht einloggen können:
ALTER USER ‹data-owner› GRANT CONNECT THROUGH ‹user›
(zu lesen als ‘connect by’, nicht als ‘connect as’)
ALTER USER dbis GRANT CONNECT THROUGH may;
• und dann:
may@login> sqlplus
SQL> connect may[dbis]/may-passwort
SQL> select * from continent
greift automatisch auf [Link] zu,
hat alle Rechte, die dbis hat.
• Die “realen-Personen”-Benutzer benötigen dann nicht einmal ein eigenes Schema, und
können auch keine eigenen (privaten) Tabellen in der “Firmen”-Datenbank anlegen.

7.5 Zugriffsrechte 177

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

7.6 Anpassung der Datenbank an Sprache, Zeichensatz etc.

• Alle Benutzer arbeiten (“session”) auf demselben Datenbestand ( “system”, “database”,

“instance”),
• Lokale Anpassungen: Sprache für Fehlermeldungen, Darstellung von Datum,
Dezimalkomma/punkt, Zeichensatz, ...
• Oracle NLS: Natural Language Support
– NLS_DATABASE_PARAMETERS: bei Erzeugung der Datenbank gesetzt
– NLS_SESSION_PARAMETERS: bei Beginn der Session gesetzt

7.6 Lokale Anpassung 178

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

Anpassungs-Parameter

SELECT * FROM NLS_{SESSION|DATABASE}_PARAMETERS;

Parameter Value
NLS_LANGUAGE {AMERICAN|...}
NLS_NUMERIC_CHARACTERS {.,|,.} Dezimalpunkt/komma, z.B. 50.000,00
NLS_CALENDAR {GREGORIAN|...}
NLS_DATE_FORMAT {DD-MON-YYYY|...}
NLS_DATE_LANGUAGE {AMERICAN|...}
NLS_CHARACTERSET {AL32UTF8|...}
NLS_SORT {BINARY|GERMAN} Behandlung von Umlauten
NLS_LENGTH_SEMANTICS {BYTE|CHAR}
NLS_RDBMS_VERSION {[Link].1.0|[Link]|. . . }

ALTER {SESSION|SYSTEM} SET ‹parameter› = ‹value›;

• NLS_LENGTH_SEMANTICS: Umlaute etc. haben mehrere Bytes (’Göttingen’ hat unter
UTF8 10 Bytes/Zeichen)

7.6 Lokale Anpassung 179

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

7.7 Optimierung der Datenbank

• möglichst wenige Hintergrundspeicherzugriffe
• Daten soweit wie möglich im Hauptspeicher halten

Datenspeicherung:
• Hintergrundspeicherzugriff effizient steuern
−→ Zugriffspfade: Indexe, Hashing
• möglichst viele semantisch zusammengehörende Daten mit einem
Hintergrundspeicherzugriff holen
−→ Clustering

Anfrageoptimierung:
• Datenmengen klein halten
• frühzeitig selektieren
• Systeminterne Optimierung

Algorithmische Optimierung !

7.7 Optimierung 180

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

Z UGRIFFSPFADE : I NDEXE
Zugriff über indizierte Spalte(n) erheblich effizienter.
• Baumstruktur; ORACLE: B∗ -Mehrweg-Baum,
• B∗ -Baum: Knoten enthalten nur Weg-Information, Verzweigungsgrad hoch, Höhe des
Baumes klein.
. 5 . 9 . 13 .

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

• Suche durch Schlüsselvergleich: logarithmischer Aufwand.

• Schneller Zugriff (logarithmisch) versus hoher Reorganisationsaufwand (→
Algorithmentechnik),
• bei sehr vielen Indexen auf einer Tabelle kann es beim Einfügen, Ändern und Löschen von
Sätzen zu Performance-Verlusten kommen,
• logisch und physikalisch unabhängig von den Daten der zugrundeliegenden Tabelle,
• keine Auswirkung auf die Formulierung einer SQL-Anweisung, nur auf die interne
algebrabasierte Auswertung,
• mehrere Indexe für eine Tabelle möglich.
7.7 Optimierung 181
 Praktikum: Datenbankprogrammierung in SQL/ORACLE

Z UGRIFFSPFADE : I NDEXE
Zugriff über indizierte Spalte(n) erheblich effizienter:
• benötigte Indexknoten aus Hintergrundspeicher holen,
• dann nur ein Zugriff um ein Tupel zu bekommen.

SET AUTOTRACE ON;

SELECT Name, Code FROM Country WHERE Code > ’M’;
• Ausgabe alphabetisch nach Code geordnet:
Auf Schlüsselattributen sind automatisch Indexe angelegt und werden verwendet.
SELECT Name, Population
FROM Country
WHERE Population > 50000000;
• Ausgabe nicht sinnvoll geordnet:
kein Index vorhanden, linearer Durchlauf (“Scan”).
CREATE INDEX CountryPopIndex ON Country (Population);
• Ausgabe obiger Anfrage jetzt nach Population geordnet.
(Blätter des Baums linear durchgehen)
DROP INDEX CountryPopIndex;

7.7 Optimierung 182

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

H ASHING

Aufgrund der Werte einer/mehrerer Spalten (Hashkey) wird durch eine Hashfunktion
berechnet, wo das/die entsprechende(n) Tupel zu finden sind.
• Zugriff in konstanter Zeit,
• keine Ordnung.
• gezielter Zugriff auf die Daten über ein bestimmtes Land
Hashkey: [Link]
Hashkey-Wert

Hashfunktion

F D NL
··· 58317450 ··· 83536115 ··· 15568034 ···
547030 356910 37330
In ORACLE ist Hashing nur für Cluster implementiert.

7.7 Optimierung 183

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

C LUSTER
• Zusammenfassung einer Gruppe von Tabellen, die alle eine oder mehrere gemeinsame
Spalten (Clusterschlüssel) besitzen, oder
• Gruppierung einer Tabelle nach dem Wert einer bestimmten Spalte (Clusterschlüssel);
• bei einem Hintergrundspeicherzugriff werden semantisch zusammengehörende Daten in
den Hauptspeicher geladen.

Vorteile eines Clusters:

• geringere Anzahl an Plattenzugriffen und schnellere Zugriffsgeschwindigkeit
• geringerer Speicherbedarf, da jeder Clusterschlüsselwert nur einmal abgespeichert wird
Nachteile:
• ineffizient bei häufigen Updates der Clusterschlüsselwerte, da dies eine physikalische
Reorganisation bewirkt
• schlechtere Performance beim Einfügen in Cluster-Tabellen

7.7 Optimierung 184

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

Clustering

Sea und geo_Sea mit Cl_Sea

Clusterschlüssel [Link]: Mediterranean Sea Depth
5121
Province Country
Catalonia E
Andalusia E
Languedoc-R. F
Provence F
: :
Baltic Sea Depth
459
Province Country
Schleswig-H. D
Mecklenb.-Vorp. D
Szczecin PL
: :

7.7 Optimierung 185

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

Clustering

City nach (Province,Country):

Country Province
D Nordrh.-Westf. City Population ...
Düsseldorf 572638 ...
Solingen 165973 ...
USA Washington City Population ...
Seattle 524704 ...
Tacoma 179114 ...
.. .. .. .. ..
. . . . .

7.7 Optimierung 186

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

E RZEUGEN EINES C LUSTERS IN ORACLE

Cluster erzeugen und Clusterschlüssel angeben:

CREATE CLUSTER ‹name›(‹col› ‹datatype›-list)
[INDEX | HASHKEYS ‹integer› [HASH IS ‹funktion›]];
CREATE CLUSTER Cl_Sea (SeaName VARCHAR2(50));
• Default: indexed Cluster, d.h. die Zeilen werden entsprechend dem Clusterschlüsselwert
indiziert und geclustert.
• Option: HASH mit Angabe einer Hashfunktion, nach der geclustert wird.
• Zuordnung der Tabellen mit CREATE TABLE unter Angabe des Clusterschlüssels:
CREATE TABLE ‹table›
(‹col› ‹datatype›,
.
.
.
‹col› ‹datatype›)
CLUSTER ‹cluster›(‹column-list›);

7.7 Optimierung 187

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

Erzeugen eines Clusters in ORACLE: Beispiel

CREATE TABLE CSea

(Name VARCHAR2(50) PRIMARY KEY,
Depth NUMBER)
CLUSTER Cl_Sea (Name);
CREATE TABLE Cgeo_Sea
(Province VARCHAR2(50),
Country VARCHAR2(4),
Sea VARCHAR2(50))
CLUSTER Cl_Sea (Sea);
Erzeugen des Clusterschlüsselindexes:
(Dies muss vor dem ersten DML-Kommando geschehen).
CREATE INDEX ‹name› ON CLUSTER ‹cluster›;
CREATE INDEX ClSeaInd ON CLUSTER Cl_Sea;

7.7 Optimierung 188

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

TEIL III: Erweiterungen

Teil I: Grundlagen
Teil II: Diverses
Teil III: Prozedurale Konzepte, OO, Einbettung
• PL/SQL: Prozeduren, Funktionen, Trigger
• Objektorientierung
• SQL und Java
• SQL und XML

7.7 Teil III 189

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

S ITUATION
• keine prozeduralen Konzepte in SQL (Schleifen, Verzweigungen, Variablendeklarationen)
• viele Aufgaben nur umständlich über Zwischentabellen oder überhaupt nicht in SQL zu
realisieren
– Transitive Hülle.
• Programme repräsentieren anwendungsspezifisches Wissen, das nicht in der Datenbank
enthalten ist.

E RWEITERUNGEN
• Einbettung von SQL in prozedurale Wirtssprachen (embedded SQL); meistens Pascal, C,
C++, oder auch Java (JDBC/SQLJ),
• Erweiterung von SQL um prozedurale Elemente innerhalb der SQL-Umgebung, PL/SQL
(Procedural language extensions to SQL).
• Vorteile von PL/SQL: Bessere Integration der prozeduralen Elemente in die Datenbank;
Nutzung in Prozeduren, Funktionen und Triggern.
• benötigt für Objektmethoden.

7.7 Teil III 190

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

Kapitel 8
Prozedurale Erweiterungen: PL/SQL

• Erweiterung von SQL um prozedurale Elemente innerhalb der SQL-Umgebung, PL/SQL

(Procedural language extensions to SQL).
• “Stored Procedures/Functions” innerhalb der DB
• direkter Zugriff auf Datenbankinhalt
• Vorteile von PL/SQL: Bessere Integration der prozeduralen Elemente in die Datenbank;
Nutzung in Prozeduren, Funktionen und Triggern

Weitere Nutzung

• Programmierung von Objektmethoden (seit Oracle 8/1997)

8.0 PL/SQL 191

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

8.1 Prozeduren, Funktionen und Kontrollstrukturen in

PL/SQL
Blockstruktur von PL/SQL

Block Header
• Block Header: Art des Objekts (Funktion, Pro-
IS
zedur oder anonym (innerhalb eines anderen
Declaration Section Blocks)), und Parameterdeklarationen.
BEGIN • Declaration Section: Deklarationen der in dem
Block verwendeten Variablen,
Execution Section
• Execution Section: Befehlssequenz des Blocks,
EXCEPTION • Exception Section: Reaktionen auf eventuell auf-
Exception Section tretende Fehlermeldungen.

END;

8.1 PL/SQL 192

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

E INFACHE , A NONYME B LÖCKE

• nur Declaration und Execution Section
• werden direkt ausgeführt
• DECLARE ... BEGIN ... END;
/

Wichtig: nach dem Semikolon noch ein Vorwärtsslash (“/”) in einer separaten Zeile, um die
Deklaration auszuführen!!!

8.1 PL/SQL 193

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

PL/SQL-VARIABLEN UND DATENTYPEN

Deklaration der PL/SQL-Variablen in der Declaration Section:

DECLARE
‹variable› ‹datatype› [NOT NULL] [DEFAULT ‹value›];
.
.
.
‹variable› ‹datatype› [NOT NULL] [DEFAULT ‹value›];

Einfache Datentypen:
BOOLEAN: TRUE, FALSE, NULL,
BINARY_INTEGER, PLS_INTEGER: Ganzzahlen mit Vorzeichen.
NATURAL, INT, SMALLINT, REAL, . . . : Numerische Datentypen.

DECLARE
anzahl NUMBER DEFAULT 0;
name VARCHAR2(50);

8.1 PL/SQL 194

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

anchored T YPDEKLARATION

Angabe einer PL/SQL-Variablen, oder Tabellenspalte (!) deren Typ man übernehmen will:
‹variable› ‹variable’›%TYPE
[NOT NULL] [DEFAULT ‹value›];
oder
‹variable› ‹table›.‹col›%TYPE
[NOT NULL] [DEFAULT ‹value›];
• cityname [Link]%TYPE
• %TYPE wird zur Compile-Time bestimmt.

8.1 PL/SQL 195

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

PL/SQL-DATENTYPEN : R ECORDS

Ein RECORD enthält mehrere Felder, entspricht einem Tupel in der Datenbasis:
TYPE city_type IS RECORD
(Name [Link]%TYPE,
Country VARCHAR2(4),
Province VARCHAR2(50),
Population NUMBER,
Latitude NUMBER,
Longitude NUMBER);
the_city city_type;

anchored Typdeklaration für Records

Records mit Tabellenzeilen-Typ deklarieren: %ROWTYPE:

‹variable› ‹table-name›%ROWTYPE;

Äquivalent zu oben:
the_city city%ROWTYPE;

8.1 PL/SQL 196

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

Z UWEISUNG AN VARIABLEN
• “klassisch” innerhalb des Programms:
a := b;
• Zuweisung des (einspaltigen und einzeiligen!) Ergebnisses einer Datenbankanfrage an
eine PL/SQL-Variable:
SELECT ...
INTO ‹PL/SQL-Variable›
FROM ...

DECLARE
cname [Link]%TYPE;
BEGIN
SELECT name
INTO cname
FROM country
WHERE code=’D’;
dbms_output.put_line(cname);
END;
/ [Filename: PLSQL/[Link]]

8.1 PL/SQL 197

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

AUSGABE -G ENERIERUNG
• verwendet das DBMS_Output Package
• einmalig SET SERVEROUTPUT ON
(z.B., beim Starten von sqlplus)
• innerhalb von PL/SQL-Blocks:
dbms_output.put_line(’bla’);
• Bei Prozeduren etc.: Ausgabe erscheint erst nach kompletter Ausführung der Prozedur etc.
set serveroutput on;
DECLARE
bla NUMBER;
BEGIN
bla := 42;
dbms_output.put_line(bla);
END;
/
[Filename: PLSQL/[Link]]

8.1 PL/SQL 198

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

Zuweisung an Records
• Aggregierte Zuweisung: zwei Variablen desselben Record-Typs:
‹variable› := ‹variable’›;
• Feldzuweisung: ein Feld wird einzeln zugewiesen:
‹[Link]› := ‹variable›|‹value›;
• SELECT INTO: Ergebnis einer Anfrage, die nur ein einziges Tupel liefert:
DECLARE
SELECT ...
c continent%ROWTYPE;
INTO ‹record-variable›
BEGIN
FROM ... ;
SELECT *
INTO c
FROM continent
WHERE name=’Europe’;
dbms_output.put_line([Link] || ’ : ’ || [Link]);
END;
/ [Filename: PLSQL/[Link]]
Vergleich von Records
Beim Vergleich von Records muss jedes Feld einzeln verglichen werden.

8.1 PL/SQL 199

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

P ROZEDUREN
CREATE [OR REPLACE] PROCEDURE ‹proc_name›
[(‹parameter-list›)]
IS ‹pl/sql-body›;
/
• OR REPLACE: existierende Prozedurdefinition wird überschrieben.
• (‹parameter-list›): Deklaration der formalen Parameter:
(‹variable› [IN|OUT|IN OUT] ‹datatype›,
.
.
.
‹variable› [IN|OUT|IN OUT] ‹datatype›)
• IN, OUT, IN OUT: geben an, wie die Prozedur/Funktion auf den Parameter zugreifen kann
(Lesen, Schreiben, beides).
• Default: IN.
• Bei OUT und IN OUT muss beim Aufruf eine Variable angegeben sein, bei IN ist auch eine
Konstante erlaubt.
• ‹datatype›: alle von PL/SQL unterstützten Datentypen; ohne Längenangabe (VARCHAR2
anstelle VARCHAR2(20)).
• ‹pl/sql-body› enthält die Definition der Prozedur in PL/SQL.
8.1 PL/SQL 200
 Praktikum: Datenbankprogrammierung in SQL/ORACLE

F UNKTIONEN

Analog, zusätzlich wird der Datentyp des Ergebnisses angegeben:

CREATE [OR REPLACE] FUNCTION ‹funct_name›
[(‹parameter-list›)]
RETURN ‹datatype›
IS ‹pl/sql body›;
/
• datatype darf dabei nur ein atomarer SQL-Datentyp sein.
Es können damit also keine Tabellen zurückgegeben werden.
• PL/SQL-Funktionen werden mit
RETURN ‹ausdruck›;
verlassen. Jede Funktion muss mindestens ein RETURN-Statement im ‹body› enthalten.
• Eine Funktion darf keine Seiteneffekte auf die Datenbasis haben (siehe
Oracle-Dokumentation PL/SQL User’s Guide and Reference).

8.1 PL/SQL 201

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

P ROZEDUREN UND F UNKTIONEN

• Im Falle von “... created with compilation errors”:
SHOW ERRORS;
ausgeben lassen.
• Prozeduren und Funktionen werden mit DROP PROCEDURE/FUNCTION ‹name› gelöscht.
• Aufruf von Prozeduren im PL/SQL-Skript:
‹procedure› (arg1,...,argn);
(wenn ein formaler Parameter als OUT oder IN OUT angegeben ist, muss das Argument
eine Variable sein)
• Aufruf von Prozeduren in SQLPlus:
execute ‹procedure› (arg1,...,argn);
• Verwendung von Funktionen in PL/SQL:
... ‹function› (arg1,...,argn) ...
wie in anderen Programmiersprachen.
• Die system-eigene Tabelle DUAL wird verwendet um das Ergebnis freier Funktionen in
sqlplus ausgeben zu lassen:
SELECT ‹function› (arg1,...,argn)
FROM DUAL;
8.1 PL/SQL 202
 Praktikum: Datenbankprogrammierung in SQL/ORACLE

Beispiel: Prozedur
• Einfache Prozedur: PL/SQL-Body enthält nur SQL-Befehle

Informationen über Länder sind über mehrere Relationen verteilt.

CREATE OR REPLACE PROCEDURE InsertCountry

(name VARCHAR2, code VARCHAR2,
area NUMBER, pop NUMBER,
gdp NUMBER, inflation NUMBER, pop_growth NUMBER)
IS
BEGIN
INSERT INTO Country (Name,Code,Area,Population)
VALUES (name,code,area,pop);
INSERT INTO Economy (Country,GDP,Inflation)
VALUES (code,gdp,inflation);
INSERT INTO Population (Country,Population_Growth)
VALUES (code,pop_growth);
END;
/ [Filename: PLSQL/[Link]]
EXECUTE InsertCountry (’Lummerland’, ’LU’, 1, 4, 50, 0.5, 0.25);

8.1 PL/SQL 203

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

Beispiel: Funktion

• Einfache Funktion: Einwohnerdichte eines Landes

CREATE OR REPLACE FUNCTION Density (arg VARCHAR2)

RETURN number
IS
temp number;
BEGIN
SELECT Population/Area
INTO temp
FROM Country
WHERE code = arg;
RETURN temp;
END;
/
[Filename: PLSQL/[Link]]
SELECT Density(’D’)
FROM dual;

8.1 PL/SQL 204

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

Prozedur mit IN- und OUT-Parametern

• eine Funktion kann nur einen einzelnen Wert zurückgeben, und darf den
Datenbankzustand nicht verändern.
CREATE OR REPLACE PROCEDURE ChangePop (cc VARCHAR2, pop IN OUT NUMBER, dens OUT NUMBER)
IS
temp number;
BEGIN
SELECT population INTO temp FROM country WHERE code = cc;
UPDATE country SET population = pop WHERE code = cc;
SELECT population/area INTO dens FROM country WHERE code = cc;
pop := temp;
END;
/
DECLARE pop NUMBER; dens NUMBER;
BEGIN
pop := 80000000;
ChangePop(’D’, pop, dens);
dbms_output.put_line(pop); -- 82521653 the before pop
dbms_output.put_line(dens); -- 226.94 new density
END;
/ [Filename: PLSQL/[Link]]

8.1 PL/SQL 205

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

SQL-S TATEMENTS IN PL/SQL

• DML-Kommandos INSERT, UPDATE, DELETE sowie SELECT INTO-Statements.
• Diese SQL-Anweisungen dürfen auch PL/SQL-Variablen enthalten.
• DDL-Statements sind in PL/QL nicht (direkt) erlaubt! (siehe Folie 225)
• Befehle, die nur ein einziges Tupel betreffen, können mit RETURNING Werte an
PL/SQL-Variablen zurückgeben:
UPDATE ... SET ... WHERE ...
RETURNING ‹expr-list›
INTO ‹variable-list›;
Z.B. Row-ID des betroffenen Tupels zurückgeben:
DECLARE tmprowid ROWID;
BEGIN
:
INSERT INTO Politics (Country,Independence) VALUES (Code,SYSDATE)
RETURNING ROWID
INTO tmprowid;
:
END;

8.1 PL/SQL 206

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

KONTROLLSTRUKTUREN
• IF THEN - [ELSIF THEN] - [ELSE] - END IF,
• verschiedene Schleifen:
• Simple LOOP: LOOP ... END LOOP;
• WHILE LOOP: WHILE ‹bedingung› LOOP ... END LOOP;
• Numeric FOR LOOP: FOR ‹loop_index› IN
[REVERSE] ‹Anfang› .. ‹Ende›
LOOP ... END LOOP;
Die Variable ‹loop_index› wird dabei automatisch als INTEGER deklariert.
• EXIT [WHEN ‹bedingung›]: LOOP verlassen.
• den berüchtigten GOTO-Befehl mit Labels:
‹‹label_i›› ... GOTO label_j;
• NULL-Werte verzweigen immer in den ELSE-Zweig.
• GOTO: nicht von außen in ein IF-Konstrukt, einen LOOP, oder einen lokalen Block
hineinspringen, nicht von einem IF-Zweig in einen anderen springen.
• hinter einem Label muss immer mindestens ein ausführbares Statement stehen;
• NULL Statement.
8.1 PL/SQL 207
 Praktikum: Datenbankprogrammierung in SQL/ORACLE

G ESCHACHTELTE B LÖCKE

Innerhalb der Execution Section werden anonyme Blöcke zur Strukturierung verwendet. Hier
wird die Declaration Section mit DECLARE eingeleitet (es gibt keinen Block Header):
BEGIN
-- Befehle des äußeren Blocks --
DECLARE
-- Deklarationen des inneren Blocks
BEGIN
-- Befehle des inneren Blocks
END;
-- Befehle des äußeren Blocks --
END;

8.1 PL/SQL 208

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

8.2 Cursore/Iteratoren zur Verarbeitung von

Ergebnismengen
• Datenbankanfragen: mengenorientiert
• Programmiersprache: variablenbasiert

Design Patterns: Kollektionen und Iteratoren

(vgl. Informatik I)
• Kollektion: Sammlung von Items (Liste, Baum, Heap, Menge)
• Iterator: Hilfsklasse zum Durchlaufen/Aufzählen aller Items
• Methoden:
– Erzeugen/Initialisieren des Iterators,
– Weiterschalten, Test, ob noch weitere Elemente vorhanden sind,
– Zugriff auf ein Element,
– (Schliessen des Iterators)
... Iteratoren werden im Weiteren immer wieder verwendet.

8.2 PL/SQL 209

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

C URSORBASIERTER DATENBANKZUGRIFF
Zeilenweiser Zugriff auf eine Relation aus einem PL/SQL-Programm.
Cursordeklaration in der Declaration Section:
CURSOR ‹cursor-name› [(‹parameter-list›)]
IS ‹select-statement›;
• (‹parameter-list›): Parameter-Liste,
• nur IN als Übergaberichtung erlaubt.
• Zwischen SELECT und FROM auch PL/SQL-Variablen und PL/SQL-Funktionen.
PL/SQL-Variablen können ebenfalls in den WHERE-, GROUP- und HAVING-Klauseln verwendet
werden.

Beispiel: Alle Städte in dem in der Variablen the_country angegebenen Land:

DECLARE
CURSOR cities_in (the_country [Link]%TYPE)
IS SELECT Name
FROM City
WHERE Country=the_country;

8.2 PL/SQL 210

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

Cursore: Grundprinzip

• OPEN ‹cursor-name›[(‹argument-list›)];

Erzeugt mit dem gegebenen SELECT-Statement eine virtuelle Tabelle mit einem “Fenster”,
das über einem Tupel stehen kann und schrittweise vorwärts bewegt wird. Mit OPEN wird der
Cursor initialisiert:
OPEN cities_in (’D’);

OPEN
Name
FETCH
Bonn
FETCH
Kiel
FETCH
Hamburg
..
.

8.2 PL/SQL 211

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

Cursore: Verwendung
• FETCH ‹cursor-name› INTO ‹record-variable›; oder
FETCH ‹cursor-name› INTO ‹variable-list›;
bewegt den Cursor auf die nächste Zeile des Ergebnisses der Anfrage und kopiert diese in
die angegebene Record-Variable oder Variablenliste.
• Diese kann z.B. mit DECLARE CURSOR cities_in
dem Record-Typ des Cursors definiert (crs_country [Link]%TYPE)
werden: IS SELECT Name
‹variable› ‹cursor-name›%ROWTYPE; FROM City
• CLOSE ‹cursor-name›; schließt einen WHERE Country = crs_country;
Cursor. city_in cities_in%ROWTYPE;
BEGIN
OPEN cities_in (’D’);
FETCH cities_in INTO city_in;
dbms_output.put_line(city_in.Name);
FETCH cities_in INTO city_in;
dbms_output.put_line(city_in.Name);
CLOSE cities_in;
END;
/ [Filename: PLSQL/[Link]]
8.2 PL/SQL 212
 Praktikum: Datenbankprogrammierung in SQL/ORACLE

Cursore: Attribute

Kontrolle über die Verarbeitung eines Cursors:

• ‹cursor-name›%ISOPEN: Cursor offen?
• ‹cursor-name›%FOUND: Solange ein Cursor bei der letzten FETCH-Operation ein neues
Tupel gefunden hat, ist ‹cursor-name›%FOUND = TRUE.
• ‹cursor-name›%NOTFOUND: TRUE wenn man alle Zeilen eines Cursors geFETCHt hat.
• ‹cursor-name›%ROWCOUNT: Anzahl der von einem Cursor bereits gelesenen Tupel.
• nicht innerhalb eines SQL-Ausdrucks.

8.2 PL/SQL 213

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

Cursore: Attribute

CREATE OR REPLACE PROCEDURE first_city

(the_country [Link]%TYPE)
IS BEGIN
DECLARE CURSOR cities_in
(crs_country [Link]%TYPE)
IS SELECT Name
FROM City
WHERE Country = crs_country;
city_in cities_in%ROWTYPE;
BEGIN
OPEN cities_in (the_country);
FETCH cities_in INTO city_in;
IF cities_in%FOUND
THEN DBMS_OUTPUT.PUT_LINE(city_in.name);
execute first_city(’D’);
ELSE DBMS_OUTPUT.PUT_LINE(’Nothing found!’);
END IF; execute first_city(’X’);
CLOSE cities_in;
END; Aufgabe: Programmieren Sie eine ex-
END; plizite WHILE-Schleife, die alle Städte
/ [Filename: PLSQL/[Link]] eines Landes ausgibt.

8.2 PL/SQL 214

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

Cursore: Hinweis

nicht möglich:
OPEN cities_in (’D’);
OPEN cities_in (’CH’);
FETCH cities_in INTO ‹variable›;

• ein parametrisierter Cursor,

• nicht eine Familie von Cursoren!

8.2 PL/SQL 215

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

C URSOR FOR LOOP

Spezielle Schleife zur Iteration über den Inhalt eines Cursors:

FOR ‹record_index› IN ‹cursor-name›
LOOP ... END LOOP;
• ‹record_index› wird dabei automatisch als Variable vom Typ ‹cursor-name›%ROWTYPE
deklariert,
• ‹record_index› immer von einem Record-Type – ggf. einspaltig.
• Es wird automatisch ein OPEN ausgeführt,
• bei jeder Ausführung des Schleifenkörpers wird automatisch ein FETCH ausgeführt,
• → Schleifenkörper enthält i.a. keinen FETCH-Befehl,
• am Ende wird automatisch ein CLOSE ausgeführt,
• Spalten müssen explizit adressiert werden.

8.2 PL/SQL 216

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

Cursor FOR LOOP: Beispiel

Beispiel: Für jede Stadt in dem gegebenen Land soll der Name ausgegeben werden:

CREATE OR REPLACE PROCEDURE list_cities (the_country [Link]%TYPE)

IS
BEGIN
DECLARE CURSOR cities_in
(crs_country [Link]%TYPE)
IS SELECT Name
FROM City
WHERE Country = crs_country;
BEGIN
FOR the_city IN cities_in(the_country)
LOOP
dbms_output.put_line(the_city.name);
END LOOP;
END;
END;
/ [Filename: PLSQL/[Link]]
execute list_cities(’D’);
8.2 PL/SQL 217
 Praktikum: Datenbankprogrammierung in SQL/ORACLE

Eingebetteter Cursor FOR LOOP

• SELECT-Anfrage kann auch direkt in die FOR-Klausel geschrieben werden.

CREATE OR REPLACE PROCEDURE list_big_cities

(the_country [Link]%TYPE)
IS
BEGIN
FOR the_city IN
( SELECT Name
FROM City
WHERE Country = the_country
AND Population > 1000000 )
LOOP
dbms_output.put_line(the_city.Name);
END LOOP;
END;
/
[Filename: PLSQL/[Link]]
execute list_big_cities(’D’);

8.2 PL/SQL 218

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

Schreibzugriff via Cursor

Mit WHERE CURRENT OF ‹cursor-name› kann man auf das zuletzt von dem genannten Cursor
geFETCHte Tupel zugreifen:
UPDATE ‹table-name›
SET ‹set_clause›
WHERE CURRENT OF ‹cursor_name›;
DELETE FROM ‹table-name›
WHERE CURRENT OF ‹cursor_name›;
• Dabei bestimmt die Positionierung des Cursors bezüglich der Basistabellen den Ort der
Änderung (im Gegensatz zu View Updates).

8.2 PL/SQL 219

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

PL/SQL-DATENTYPEN : PL/SQL TABLES

Array-artige Struktur, eine Spalte mit beliebigem Datentyp (also auch RECORD), normalerweise
mit BINARY_INTEGER indiziert.
Typ-Definition: TYPE ‹tabtype› IS TABLE OF ‹datatype› [INDEX BY BINARY_INTEGER];
Var-Deklaration: ‹tabname› ‹tabtype›;
Built-in-Funktionen und -Prozeduren:
‹variable› := ‹tabname›.‹built-in-function›;
oder
‹tabname›.‹built-in-procedure›;
• COUNT (fkt): Anzahl der belegten Zeilen.
• EXISTS(i) (fkt): TRUE falls Zeile i der Tabelle nicht leer.
• DELETE (proc): Löscht alle Zeilen einer Tabelle.
• DELETE(i): Löscht Zeile i einer Tabelle.
• FIRST/LAST (fkt): niedrigster/höchster belegter Indexwert.
(ist null falls Tabelle leer ist!)
• NEXT/PRIOR(n) (fkt): Gibt ausgehend von n den nächsthöheren/nächstniedrigen belegten
Indexwert.
8.2 PL/SQL 220
 Praktikum: Datenbankprogrammierung in SQL/ORACLE

PL/SQL Tables als einfache Collections

• dann (implizit) indiziert mit 1..n (falls nicht leer)

DECLARE
SELECT ...
TYPE tabtype IS TABLE OF city%ROWTYPE;
BULK COLLECT INTO ‹tabname›
tab tabtype;
FROM ...
BEGIN
WHERE ...
SELECT *
BULK COLLECT INTO tab
FROM city
WHERE country = ’D’;
IF [Link] > 0 THEN
• analog
FOR i IN [Link] .. [Link] LOOP
TABLE OF ‹table›.‹attr›%TYPE
dbms_output.put_line(tab(i).name);
und dann Zugriff nur mit tab(i).
END LOOP;
• bei BULK COLLECT wird der vor-
END IF;
herige Inhalt der Tabelle über-
END;
schrieben.
/ [Filename: PLSQL/[Link]]

8.2 PL/SQL 221

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

PL/SQL Tables als indizierte Collections

TYPE ‹tabtype› IS TABLE OF ‹datatype›

INDEX BY BINARY_INTEGER;
‹tabname› ‹tabtype›;

• Adressierung: ‹tabname›(n) DECLARE

TYPE plz_table_type IS TABLE OF [Link]%TYPE
• sparse: nur die Zeilen gespei-
INDEX BY BINARY_INTEGER;
chert, die Werte enthalten.
plztab plz_table_type;
• Dann springen mit WHILE und
i NUMBER;
‹tabname›.next: BEGIN
plztab(37077):= ’Goettingen’;
plztab(79110):= ’Freiburg’;
plztab(33334):= ’Kassel’;
i := [Link]; -- 33334
WHILE NOT i IS NULL LOOP
dbms_output.put_line(i || ’ ’ || plztab(i));
i := [Link](i);
END LOOP;
END;
/ [Filename: PLSQL/[Link]]

8.2 PL/SQL 222

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

PL/SQL Tables

• Tabellen können auch als Ganzes zugewiesen werden

andere_table := plz_table;
• Unterschied BULK COLLECT zu Cursor:
– Cursor wird on-demand iteratorbasiert ausgewertet, kann abgebrochen werden,
– BULK COLLECT wertet komplett aus und legt das Ergebnis in PL/SQL-Tabelle ab.
∗ BULK COLLECT ist daher ineffizienter, gibt aber die DB sofort wieder frei (falls eine
andere Transaktion schreibend zugreifen möchte).

8.2 PL/SQL 223

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

PL/SQL Tables als Rückgabewert einer Funktion

CREATE OR REPLACE TYPE membership_type AS OBJECT (
country VARCHAR2(4),
type VARCHAR2(60) );
/
CREATE OR REPLACE TYPE memberships_type AS TABLE OF membership_type;
/
CREATE OR REPLACE FUNCTION members_of(org VARCHAR2)
RETURN memberships_type
IS
toreturn memberships_type;
BEGIN
SELECT membership_type(country, type)
BULK COLLECT INTO toreturn
FROM ismember
WHERE organization = org;
RETURN toreturn;
END;
/
SELECT country, type
FROM TABLE(SELECT members_of(’EU’) FROM DUAL); [Filename: PLSQL/return_table.sql]

8.2 PL/SQL 224

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

8.3 Dynamic SQL

• EXECUTE IMMEDIATE ‹string›
• ‹string› kann dabei eine Konstante sein,
– auf diese Weise kann man auch DDL-Statements in PL/SQL aufrufen:
BEGIN
execute immediate ’drop table continent’;
END;
• oder kann dynamisch zusammengesetzt werden (siehe nächste Folie)
• und Platzhalter für Werte, die dann als Konstanten des entsprechenden Datentyps bei
Ausführung eingesetzt werden, enthalten.
• Escapen der single-quotes in dem auszuführenden String: ’... where name=”foo”’.
DECLARE country VARCHAR2(4) := ’CDN’;
org VARCHAR2(10) := ’EU’;
BEGIN
execute immediate ’insert into isMember VALUES (:1, :2, ’’candidate’’)’
using country, org ; END;
/

8.3 PL/SQL 225

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

Dynamic SQL: Kommandos als String erstellen und ausführen

• Hiermit kann man auch Tabellennamen, Spaltennamen und beliebige Dinge in das
DDL-Statement oder auch in eine Anfrage einfügen.
CREATE OR REPLACE PROCEDURE clean
IS
BEGIN
FOR tn IN
( SELECT table_name FROM all_tables
WHERE table_name LIKE ’TMP_%’)
LOOP
execute immediate ’DROP TABLE ’ || tn.table_name;
END LOOP;
END;
/
[Filename: PLSQL/[Link]]

8.3 PL/SQL 226

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

Dynamic SQL: Anfragen mit einzeiligem Ergebnis

• Wert in eine PL/SQL-Variable einlesen: INTO ‹pl/sql-variable›

(bei einzeiligem Ergebnis)
CREATE OR REPLACE PROCEDURE getcountrypop (ccode [Link]%TYPE)
IS
c country%ROWTYPE;
BEGIN
execute immediate ’select * from country where code= :1’
into c
using ccode;
dbms_output.put_line([Link]);
END;
/
execute getcountrypop(’CH’);
[Filename: PLSQL/[Link]]

8.3 PL/SQL 227

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

Dynamic SQL: Anfragen mit mehrzeiligen Ergebnissen

• mit einem PL/SQL-Cursor (dann geht es ohne “execute immediate”):
CREATE OR REPLACE PROCEDURE getcitiesCursor (ccode [Link]%TYPE)
IS
TYPE CurRefType IS REF CURSOR;
cv CurRefType;
c city%ROWTYPE;
BEGIN
OPEN cv FOR
’select * from city where country = :1’
using ccode;
LOOP
FETCH cv into c;
dbms_output.put_line([Link]);
EXIT WHEN cv%NOTFOUND;
END LOOP;
END;
/
execute getcitiesCursor(’AUS’); [Filename: PLSQL/[Link]]

8.3 PL/SQL 228

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

Dynamic SQL: Anfragen mit mehrzeiligen Ergebnissen

• BULK COLLECT INTO ‹pl/sql table›

CREATE OR REPLACE PROCEDURE getcitiesBulk (ccode [Link]%TYPE)
IS
TYPE ctynametabtype IS TABLE OF [Link]%TYPE
INDEX BY BINARY_INTEGER;
cities ctynametabtype;
i NUMBER;
BEGIN
execute immediate ’select name from city where country = :1’
bulk collect into cities
using ccode;
i := [Link];
WHILE NOT i IS NULL LOOP
dbms_output.put_line(i || ’ ’ || cities(i));
i := [Link](i);
END LOOP;
END;
/
execute getcitiesBulk(’AUS’); [Filename: PLSQL/[Link]]

8.3 PL/SQL 229

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

8.4 Zugriffsrechte auf PL/SQL-Datenbankobjekte

Benutzung von Funktionen/Prozeduren:
• Benutzungsrechte vergeben:
GRANT EXECUTE ON ‹procedure/function› TO ‹user›;
• Prozeduren und Funktionen werden jeweils mit den Zugriffsrechten des Besitzers
ausgeführt.
• nach
GRANT EXECUTE ON ‹procedure/function› TO ‹user›;
kann dieser User die Prozedur/Funktion auch dann aufrufen, wenn er kein Zugriffsrecht auf
die dabei benutzten Tabellen hat.
• Möglichkeit, Zugriffsberechtigungen strenger zu formulieren als mit GRANT ... ON ‹table›
TO ...:
Zugriff nur in einem ganz speziellen, durch die Prozedur oder Funktion gegebenen Kontext.
• Entsprechende Privilegien muss man direkt (GRANT ... TO ‹user›), und nicht nur über eine
Rolle bekommen haben.

8.4 PL/SQL 230

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

8.5 Geschachtelte Tabellen unter PL/SQL

Nested_Spoken
Country Languages
Nutzung geschachtelter Tabellen in
D German 100
O RACLE nicht ganz unproblematisch:
CH German 65 “Bestimme alle Länder, in denen Deutsch
French 18 gesprochen wird, sowie den Anteil der
Italian 12 deutschen Sprache in dem Land”
Romansch 1 Eine solche Anfrage muss für jedes Tupel
FL NULL in Nested_Spoken die innere Tabelle unter-
F French 100 suchen.
.. ..
. .

• SELECT THE kann jeweils nur ein Objekt zurückgeben,

• keine Korrelation mit umgebenden Tupeln möglich.
• Verwendung einer (Cursor-)Schleife.

8.5 PL/SQL 231

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

Geschachtelte Tabellen unter PL/SQL: Beispiel

CREATE TABLE tempCountries CREATE OR REPLACE PROCEDURE Search_Countries

(Land VARCHAR2(4), (the_Language IN VARCHAR2)
Sprache VARCHAR2(20), IS CURSOR countries IS
Anteil NUMBER); SELECT Code
FROM Country;
BEGIN
DELETE FROM tempCountries;
FOR the_country IN countries
LOOP
INSERT INTO tempCountries
SELECT the_country.code,Name,Percentage
FROM THE(SELECT Languages
FROM Nested_Spoken
WHERE Country = the_country.Code)
WHERE Name = the_Language;
END LOOP;
END;
/
EXECUTE Search_Countries(’German’);
SELECT * FROM tempCountries;

8.5 PL/SQL 232

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

(R E )A KTIVES V ERHALTEN

• Bis jetzt: Funktionen und Prozeduren werden durch den Benutzer explizit aufgerufen.
• Trigger: Ausführung wird durch das Eintreten eines Ereignisses in der Datenbank
angestossen.

8.5 PL/SQL 233

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

8.6 Trigger

E INSCHUB : I NTEGRITÄTSBEDINGUNGEN
• Spalten- und Tabellenbedingungen
• Wertebereichsbedingungen (domain constraints),
• Verbot von Nullwerten,
• Uniqueness und Primärschlüssel-Bedingungen,
• CHECK-Bedingungen.
! Alles nur als Bedingungen an eine Zeile innerhalb einer Tabelle formulierbar.

A SSERTIONS
• Bedingungen, die den gesamten DB-Zustand betreffen.
CREATE ASSERTION ‹name› CHECK (‹bedingung›)
• Diese werden allerdings von O RACLE bisher nicht unterstützt.
⇒ Also muss man sich etwas anderes überlegen.

8.6 PL/SQL 234

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

Trigger

• spezielle Form von PL/SQL-Prozeduren,

• werden beim Eintreten eines bestimmten Ereignisses ausgeführt.
• Spezialfall aktiver Regeln nach dem Event-Condition-Action-Paradigma.
• einer Tabelle (oft auch noch einer bestimmten Spalte) zugeordnet.
• Bearbeitung wird durch das Eintreten eines Ereignisses (Einfügen, Ändern oder Löschen
von Zeilen der Tabelle) ausgelöst (Event).
• Ausführung von Bedingungen an den Datenbankzustand abhängig (Condition).
• Action:
– vor oder nach der Ausführung der entsprechenden aktivierenden Anweisung
ausgeführt.
– einmal pro auslösender Anweisung (Statement-Trigger) oder einmal für jede betroffene
Zeile (Row-Trigger) ausgeführt.
– Trigger-Aktion kann auf den alten und neuen Wert des gerade behandelten Tupels
zugreifen.

8.6 PL/SQL 235

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

Trigger
CREATE [OR REPLACE] TRIGGER ‹trigger-name›
BEFORE | AFTER
{INSERT | DELETE | UPDATE} [OF ‹column-list›]
[ OR {INSERT | DELETE | UPDATE} [OF ‹column-list›]]*
ON ‹table›
[REFERENCING OLD AS ‹name› NEW AS ‹name›]
[FOR EACH ROW]
[WHEN (‹condition›)]
‹pl/sql-block›;

• BEFORE, AFTER: Trigger wird vor/nach der auslösenden Operation ausgeführt.

• OF ‹column› (nur für UPDATE) schränkt Aktivierung auf angegebene Spalte ein.
• Zugriff auf Zeileninhalte vor und nach der Ausführung der aktivierenden Aktion mittels OLD
bzw. NEW. Schreiben in NEW-Werte nur mit BEFORE-Trigger.
• FOR EACH ROW: Row-Trigger, sonst Statement-Trigger.
• WHEN (‹condition›): zusätzliche Bedingung; hier werden OLD und NEW verwendet;
Subqueries an die Datenbank sind nicht erlaubt.
• Referenzieren der Variablen im PL/SQL-Teil als :OLD und :NEW.

8.6 PL/SQL 236

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

Trigger: Beispiel

Wenn ein Landes-Code geändert wird, pflanzt sich diese Änderung auf die Relation Province
fort:
CREATE OR REPLACE TRIGGER change_Code
BEFORE UPDATE OF Code ON Country
FOR EACH ROW
BEGIN
UPDATE Province
SET Country = :[Link]
WHERE Country = :[Link];
END;
/
[Filename: PLSQL/[Link]]
UPDATE Country
SET Code = ’UK’
WHERE Code = ’GB’;

SELECT * FROM Province WHERE Country=’UK’;

8.6 PL/SQL 237

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

Trigger: Beispiel

Wenn ein Land neu angelegt wird, wird ein Eintrag in Politics mit dem aktuellen Jahr erzeugt:
CREATE TRIGGER new_Country
AFTER INSERT ON Country
FOR EACH ROW
WHEN (:[Link] > 2)
BEGIN
INSERT INTO Politics (Country,Independence)
VALUES (:[Link],SYSDATE);
END;
/
[Filename: PLSQL/[Link]]
INSERT INTO Country (Name,Code,Population)
VALUES (’Lummerland’, ’LU’, 4);

SELECT * FROM Politics WHERE country=’LU’;

8.6 PL/SQL 238

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

Trigger: Mutating Tables

• Zeilenorientierte Trigger: immer direkt vor/nach der Veränderung einer Zeile aufgerufen
• jede Ausführung des Triggers sieht einen anderen Datenbestand der Tabelle, auf der er
definiert ist, sowie der Tabellen, die er evtl. ändert
• ❀ Ergebnis abhängig von der Reihenfolge der veränderten Tupel
O RACLE: Betroffene Tabellen werden während der gesamten Aktion als mutating
gekennzeichnet, können nicht von Triggern gelesen oder geschrieben werden.
Nachteil: Oft ein zu strenges Kriterium.
• Trigger soll auf Tabelle zugreifen auf der er selber definiert ist.
– Nur das auslösende Tupel soll von dem Trigger gelesen/geschrieben werden:
Verwendung eines BEFORE-Triggers und der :NEW- und :OLD-Variablen
– Es sollen neben dem auslösenden Tupel auch weitere Tupel verwendet werden:
Verwendung eines Statement-orientierten Triggers
• Trigger soll auf andere Tabellen zugreifen: Verwendung von Statement-Triggern und ggf.
Hilfstabellen.

8.6 PL/SQL 239

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

INSTEAD OF-T RIGGER

• View Updates: Updates müssen auf Basistabellen umgesetzt werden.
• View-Update-Mechanismen eingeschränkt.
• INSTEAD OF-Trigger: Änderung an einem View wird durch andere SQL-Anweisungen
ersetzt.
CREATE [OR REPLACE] TRIGGER ‹trigger-name›
INSTEAD OF
{INSERT | DELETE | UPDATE} ON ‹view›
[REFERENCING OLD AS ‹name› NEW AS ‹name›]
[FOR EACH STATEMENT]
‹pl/sql-block›;
• Keine Einschränkung auf bestimmte Spalten möglich
• Keine WHEN-Klausel
• Default: FOR EACH ROW

8.6 PL/SQL 240

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

View Updates und INSTEAD OF-Trigger

CREATE OR REPLACE VIEW AllCountry AS
SELECT Name, Code, Population, Area,
GDP, Population/Area AS Density,
Inflation, population_growth,
infant_mortality
FROM Country, Economy, Population
WHERE [Link] = [Link]
AND [Link] = [Link];
[Filename: PLSQL/[Link]]
INSERT INTO AllCountry
(Name, Code, Population, Area, GDP,
Inflation, population_growth, infant_mortality)
VALUES (’Lummerland’,’LU’,4,1,0.5,0,25,0);
[Filename: PLSQL/[Link]]
Fehlermeldung: Über ein Join-View kann nur eine Basistabelle modifiziert werden.

8.6 PL/SQL 241

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

View Updates und INSTEAD OF-Trigger

CREATE OR REPLACE TRIGGER InsAllCountry
INSTEAD OF INSERT ON AllCountry
FOR EACH ROW
BEGIN
INSERT INTO
Country (Name,Code,Population,Area)
VALUES (:[Link], :[Link],
:[Link], :[Link]);
INSERT INTO Economy (Country,Inflation)
VALUES (:[Link], :[Link]);
INSERT INTO Population
(Country, Population_Growth,infant_mortality)
VALUES (:[Link], :NEW.Population_Growth,
:NEW.infant_mortality);
END;
/ [Filename: PLSQL/[Link]]
• aktualisiert Country, Economy und Population.
• Trigger New_Country (AFTER INSERT ON COUNTRY) aktualisiert zusätzlich Politics.

8.6 PL/SQL 242

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

F EHLERBEHANDLUNG DURCH E XCEPTIONS IN PL/SQL

• Declaration Section: Deklaration (der Namen) benutzerdefinierter Exceptions.
DECLARE ‹exception› EXCEPTION;
• Exception Section: Definition der beim Auftreten einer Exception auszuführenden Aktionen.
WHEN ‹exception›
THEN ‹PL/SQL-Statement›;
WHEN OTHERS THEN ‹PL/SQL-Statement›;
• Exceptions können dann an beliebigen Stellen des PL/SQL-Blocks durch RAISE ausgelöst
werden.
IF ‹condition›
THEN RAISE ‹exception›;

Ablauf

• auslösen einer Exception

• entsprechende Aktion der WHEN-Klausel ausführen
• innersten Block verlassen (oft Anwendung von anonymen Blöcken sinnvoll)

8.6 PL/SQL 243

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

Trigger/Fehlerbehandlung: Beispiel

Nachmittags dürfen keine Städte gelöscht werden:

CREATE OR REPLACE TRIGGER nachm_nicht_loeschen
BEFORE DELETE ON City
BEGIN
IF SYSDATE
BETWEEN to_date(’12:00’, ’HH24:MI’)
AND to_date(’18:00’, ’HH24:MI’)
THEN RAISE_APPLICATION_ERROR
(-20101,’Unerlaubte Aktion’);
END IF;
END;
/
[Filename: PLSQL/[Link]]

8.6 PL/SQL 244

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

Beispiel
CREATE OR REPLACE TRIGGER dummytrigger
INSTEAD OF INSERT ON AllCountry
FOR EACH ROW
BEGIN
-- username must be in capitals!!!
IF user=’MAY’
THEN NULL;
END IF;
... INSERT INTO AllCountry
END; (Name, Code, Population, Area, GDP, Inflation,
/ population_growth, infant_mortality)
VALUES (’Lummerland’,’LU’,4,1,0.5,0,25,0);

1 Zeile wurde erstellt.

SQL> select * from allcountry where Code=’LU’;

Es wurden keine Zeilen ausgewaehlt.

(aus A. Christiansen, M. Höding, C. Rautenstrauch und G. Saake, O RACLE 8 effizient

einsetzen, Addison-Wesley, 1998)

8.6 PL/SQL 245

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

8.7 Zeitgesteuerte Jobs

(eigentlich nicht direkt zu PL/SQL gehörend)
• Implementierung zeitabhängiger Trigger,
• bei jedem Produkt anders.
• Beschreibung hier für Oracle (Stand 12c, 2014)

Jobs und Schedules in Oracle

• Man benötigt die Systemprivilegien CREATE JOB, MANAGE SCHEDULER,

ggf. auch CREATE EXTERNAL JOB
• die Konfiguration benutzt eine objektorientierte Syntax
(ist auch auf Basis der objektrelationalen Interna umgesetzt)
• interne (PL/SQL) und externe (Aufruf eines Programmes via Pfad) Jobs möglich
• komplexe Spezifikationen von Aufrufzeiten via Schedules möglich.

8.7 PL/SQL 246

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

Einmalige zeitgesteuerte Jobs: Beispiel

• der folgende Job trägt nach einer Minute die (dann) aktuelle Zeit in die Tabelle “jobtest” ein,
danach wird der Job gelöscht.
• Syntax in SQLplus:
execute DBMS_SCHEDULER.CREATE_JOB (‹job-spezifikation›);
... dabei werden aber keine Zeilenumbrüche akzeptiert.
• also dasselbe (didaktisch) besser in einen PL/SQL-begin/end-Block packen:
create table jobtest (x DATE);
begin
DBMS_SCHEDULER.CREATE_JOB
(job_name => ’job1’,
job_type => ’PLSQL_BLOCK’,
job_action => ’begin insert into jobtest
values (SYSDATE); end;’,
start_date => SYSDATE+1/1440,
enabled => TRUE);
end;
/ [Filename: PLSQL/[Link]]

8.7 PL/SQL 247

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

Job-Attribute und Aufruf via Scheduler

• enabled: TRUE aktiviert sofort, FALSE hält einen Job deaktiviert
execute DBMS_SCHEDULER.ENABLE(’job1’);
execute DBMS_SCHEDULER.DISABLE(’job1’);
manuell aufrufen bzw löschen:
execute DBMS_SCHEDULER.RUN_JOB(’job1’);
execute DBMS_SCHEDULER.DROP_JOB(’job1’);
Fehlermeldungen erhält man nur bei manuellem Aufruf!
• start_date, end_date: ggf. Anfang und Ende.
• auto_drop: default TRUE; FALSE sorgt für Wiederholung.
• repeat_interval: basierend auf “Schedules”; Details siehe Dokumentation, z.B.
– FREQ = YEARLY|MONTHLY|...|SECONDLY; Basisangabe, wie oft,
– INTERVAL = 1..99 jedes, jedes zweite, jedes 99. von FREQ,
– BYMONTH =...; BYDAY=...; Spezifikation des “wann” innerhalb des “wie oft”,
repeat_interval => ’FREQ = WEEKLY; INTERVAL = 2; jede zweite Woche
BYDAY = MON, THU; BYHOUR = 15, 16; BYMINUTE = 00’ Montags+Donnerstags, 15+16 Uhr
• job_type: ’PL/SQL_BLOCK’, ’STORED_PROCEDURE’, ’EXECUTABLE’ (externer Job)

8.7 PL/SQL 248

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

Wiederholende zeitgesteuerte Jobs: Beispiel

• der folgende Job erhöht alle zwei Minuten zu jeweils 5 angegebenen Sekundenzeitpunkten
die Bevölkerung um 1:
begin
DBMS_SCHEDULER.DROP_JOB(’job2’);
DBMS_SCHEDULER.CREATE_JOB
(job_name => ’job2’,
job_type => ’PLSQL_BLOCK’,
job_action => ’begin
update country set population = population + 1 where code=’’CN’’;
end;’,
auto_drop => FALSE,
repeat_interval => ’FREQ = MINUTELY; INTERVAL = 2;
BYSECOND = 5, 18, 31, 45, 51’,
start_date => SYSDATE+1/2880, -- after 30 secs
end_date => SYSDATE+11/1440, -- after 11 minutes
enabled => TRUE);
end;
/ [Filename: PLSQL/[Link]]

• ... in der 11. Minute nur noch 2x. Zusammen also 5x5 (0,2,4,6,8 min) + 1x2 = 27x.

8.7 PL/SQL 249

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

Externe Jobs

• siehe Dokumentation.
• benötigt das Recht CREATE EXTERNAL JOB,
• werden auf dem Rechner ausgeführt, auf dem Oracle läuft,
• Oracle führt einen Benutzerwechsel durch (konfigurierbar, default: nobody/nogroup),
• job_action enthält nur Pfad des auszuführenden Programms, Parameter müssen separat
übergeben werden,
• Fehlermeldungen erhält man nur bei manuellem Aufruf mit
execute DBMS_SCHEDULER.RUN_JOB(’job1’);

Informationen über bestehende Jobs

SELECT job_name, job_action

FROM user_scheduler_jobs;

8.7 PL/SQL 250

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

8.8 Weitere PL/SQL-Features

• Packages: Möglichkeit, Daten und Programme zu kapseln;

• FOR UPDATE-Option bei Cursordeklarationen;
• Cursorvariablen;
• Exception Handlers;
• benannte Parameterübergabe;
• PL-SQL Built-in Funktionen: Parsing, String-Operationen, Datums-Operationen,
Numerische Funktionen;
• Built-in Packages.

• Definition komplexer Transaktionen,

• Verwendung von SAVEPOINTs für Transaktionen.

8.8 PL/SQL 251

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

8.9 Scripting: SQLplus und PL/SQL

• SQLPlus ist Oracle’s Client für SQL,
– Interface für Queries,
– Ausführen von Skripten aus dem Filesystem des Benutzers.
(z.B. [Link])
• PL/SQL ist die server-interne Skriptsprache.
– innerhalb SQL aufrufbare Prozeduren/Funktionen/Trigger
– anonyme BEGIN ... END-Blöcke
• Man kann beide (ein bisschen) kombinieren.
PL/SQL wird dann client-seitig ausgeführt.

8.9 PL/SQL 252

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

PL/SQL in SQLPlus-Skripten

• PL/SQL begin ... end;-Blöcke können in SQLPlus-Skripten enthalten sein, und werden
auf dem Client ausgewertet.
Um ausgeführt zu werden, müssen sie mit “/” abgeschlossen werden.

Aufruf von Skripten (im lokalen Filesystem) aus PL/SQL in SQLPlus-Skripten

• Mit @ (wie in SQLplus; wobei start hier nicht erlaubt ist!),

• Das aufgerufene Skript muss dann PL/SQL-konform sein
– einige SQL-Kommandos, die in SQLplus direkt zulässig sind, jedoch nicht in PL/SQL,
müssen in
execute immediate ’...’;
eingeschlossen sein.

8.9 PL/SQL 253

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

Beispiel: Das [Link]-Skript

spool [Link]
-- note: the file is located in the directory where the
-- *files* are located,
-- i.e. /afs/[Link]/group/dbis/public/Mondial/[Link]
start mondial-drop-tables;
start mondial-schema; • $ORACLE_PATH muss das Directory
start mondial-inputs; enthalten, in dem die o.g. Dateien lie-
set serveroutput on; gen
-- set read-all for DBIS user • [Link] ist ein PL/SQL-
BEGIN Fragment
IF user = ’DBIS’ -- capitalized!
THEN
dbms_output.put_line(’dbis: set grants’);
@[Link];
ELSE dbms_output.put_line(’not dbis, no grants’);
END IF;
END ;
/
spool off
prompt Logfile ’[Link]’ created ... [Filename: SQLPlus/[Link]]

8.9 PL/SQL 254

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

Beispiel (cont’d)

• [Link] ist ein SQLplus-Skript, das einfach nur das PL/SQL-Skript aufruft:
BEGIN
@[Link];
END ;
/
[Filename: SQLPlus/[Link]]
• [Link] ist ein PL/SQL-Skript, das von anderen Skripten (innerhalb eines
PL/SQL-begin-end-Blocks) aufgerufen werden kann.
(es enthält kein “/” am Ende!)
(in einem reinen SQLPlus-Skript könnte “GRANT ...” alleine stehen)
[Filename: SQLPlus/[Link]]
execute immediate ’GRANT EXECUTE ON geocoord TO student’;

execute immediate ’GRANT SELECT ON Country TO student’;

execute immediate ’GRANT SELECT ON City TO student’;
execute immediate ’GRANT SELECT ON Province TO student’;
execute immediate ’GRANT SELECT ON Economy TO student’;
execute immediate ’GRANT SELECT ON Population TO student’;
8.9execute immediate ’GRANT SELECT ON Politics TO student’;
PL/SQL 255
 Praktikum: Datenbankprogrammierung in SQL/ORACLE

Kapitel 9
Objekt-Relationale Datenbanksysteme
Integration von relationalen Konzepten und Objektorientierung:
• Komplexe Datentypen: Erweiterung des Domain-Konzepts von SQL-2 (vgl. DATE,
Geo-Koordinaten)
• Abstrakte Datentypen (“Objekttypen”):
– Unterscheidung zwischen dem Zustand und Verhalten eines Objektes (Kapselung
interner Funktionalität).
– Im Gegensatz zu einem Tupel besitzt ein Objekt
∗ Attribute (beschreiben seinen Zustand),
∗ Methoden – Abfragen und Ändern des Zustandes: Prozeduren und Funktionen
(Oracle 8: PL/SQL, Oracle 8i/8.1.5: auch Java, siehe Folie 341)
∗ MAP/ORDER-Funktionen: Ordnung auf Objekttyp
• Spezielle Ausprägungen:
– Geschachtelte Tabellen als Attributwerte,
– Erweiternde Datentypen (Spatial etc.),
– Built-In XMLType zur Verarbeitung von XML-Daten (siehe Folie 413).
9.0 Objekt-Relationale Konzepte 256
 Praktikum: Datenbankprogrammierung in SQL/ORACLE

S TUFEN DER O BJEKTORIENTIERUNG

“Konservative” objektrelationale Erweiterungen (seit Oracle 8)

(siehe Folie 258)

• Objekte als “Werte” von Attributen:
Spalten einer Tupeltabelle können objektwertig sein (aber nicht referenzwertig).
• Objekte anstelle von Tupeln:
Tabellen von Tupeln vs. Object Tables aus Objekten, die Methoden anbieten.
• Typ definiert gemeinsame Signatur seiner Instanzen (Objekte)
• bereits behandelt: Komplexe Attributtypen. Besitzen nur Wertattribute, keine Methoden.

Objektorientierte Datenbanken

(siehe Folie 283)

• Beziehungen nicht mehr über Schlüssel/Fremdschlüssel sondern über Referenzen
⇒ Navigation anstatt Joins
• seit O RACLE 9i: Subtypen und Vererbung, Objekttypen aus Java-Klassen.

9.0 Objekt-Relationale Konzepte 257

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

9.1 Objektrelationale Konzepte

Alles funktioniert (fast) genauso wie bisher:

• Spalten einer Tupeltabelle können objektwertig sein
(vgl. Geo-Koordinaten)
• Tabellen von Tupeln vs. Object Tables aus Objekten
INSERT INTO ‹table›
VALUES(‹object-constructor›(attr1 , ..., attrn ))
anstatt
INSERT INTO ‹table›
VALUES(attr1 , ..., attrn )
• Zugriff auf Attribute wie bisher mit [Link],
• zusätzlich Aufruf von Methoden mit [Link](...).

9.1 Objekt-Relationale Konzepte 258

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

9.1.1 Definition von Objekttypen

Typdeklaration

• Attribute,
• Signaturen der Methoden,

Typ-Implementierung

• Type Body: Implementierung der Methoden in PL/SQL

• seit Oracle 8i auch in PL/SQL+Java (siehe Folien 341 und 347)

9.1 Objekt-Relationale Konzepte 259

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

O BJEKTTYPDEKLARATION
CREATE [OR REPLACE] TYPE ‹type› AS OBJECT
( ‹attr› ‹datatype›,
‹attr› ‹datatype›,
.
.
.
MEMBER FUNCTION ‹func-name› [(‹parameter-list›)]
RETURN ‹datatype›,
.
.
.
MEMBER PROCEDURE ‹proc-name› [(‹parameter-list›)],
.
.
.
[ MAP MEMBER FUNCTION ‹func-name›
RETURN ‹datatype›, |
ORDER MEMBER FUNCTION ‹func-name›(‹var› ‹type›)
RETURN ‹datatype›]
);
/ ← dieser Slash ist unbedingt notwendig!
• ‹parameter-list› wie in PL/SQL,
• ähnlich CREATE TABLE, aber keine Integritätsbedingungen (erst bei der
(Objekt)tabellen-Definition)

9.1 Objekt-Relationale Konzepte 260

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

Beispiel: Geo-Koordinaten
• Methode Distance(geo-coord-Wert)
• MAP-Methode: Entfernung von Greenwich.

CREATE OR REPLACE TYPE GeoCoord AS OBJECT

( Latitude NUMBER,
Longitude NUMBER,
MEMBER FUNCTION Distance (other IN GeoCoord) RETURN NUMBER,
MAP MEMBER FUNCTION Distance_Greenwich RETURN NUMBER
);
/ ← dieser Slash ist unbedingt notwendig! [Filename: ObjRel/[Link]]
• wenn der Objekttyp bereits existiert (wie in diesem Fall):
ALTER TYPE GeoCoord
ADD MEMBER FUNCTION Distance (other IN GeoCoord)
RETURN NUMBER,
ADD MAP MEMBER FUNCTION Distance_Greenwich
RETURN NUMBER
CASCADE INCLUDING TABLE DATA; [Filename: ObjRel/[Link]]
• CASCADE: “forwards” definition to tables and data objects where it is used.
9.1 Objekt-Relationale Konzepte 261
 Praktikum: Datenbankprogrammierung in SQL/ORACLE

T YPE B ODY
• Implementierung der Objektmethoden,
• muss der der bei CREATE TYPE vorgegebenen Signatur entsprechen,
• für alle deklarierten Methoden muss die Implementierung angegeben werden.
• Variable SELF, um auf die Attribute des Host-Objektes zuzugreifen.

Funktionen: dürfen den Datenbankzustand nicht verändern,

MAP/ORDER-Funktionen: kein Datenbankzugriff erlaubt
⇒ verwenden nur den Zustand der beteiligten Objekte.

9.1 Objekt-Relationale Konzepte 262

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

Type Body
CREATE [OR REPLACE] TYPE BODY ‹type›
AS
MEMBER FUNCTION ‹func-name› [(‹parameter-list›)] RETURN ‹datatype›
IS
[‹var-decl-list›;]
BEGIN ‹PL/SQL-code› END;
:
MEMBER PROCEDURE ‹proc-name› [(‹parameter-list›)]
IS
[‹var-decl-list›;]
BEGIN ‹PL/SQL-code› END;
:
[ MAP MEMBER FUNCTION ‹func-name› RETURN ‹datatype› |
ORDER MEMBER FUNCTION ‹func-name›(‹var› ‹type›) RETURN ‹datatype›
IS
[‹var-decl-list›;]
BEGIN ‹PL/SQL-code› END; ]
END;
/

9.1 Objekt-Relationale Konzepte 263

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

Beispiel: Geo-Koordinaten
CREATE OR REPLACE TYPE BODY GeoCoord
AS
MEMBER FUNCTION Distance (other IN GeoCoord) RETURN NUMBER
IS
BEGIN
RETURN 6370 * ACOS(COS([Link]/180*3.14)
* COS([Link]/180*3.14)
* COS(([Link] - [Link])/180*3.14)
+ SIN([Link]/180*3.14) * SIN([Link]/180*3.14));
END;
MAP MEMBER FUNCTION Distance_Greenwich RETURN NUMBER
IS
BEGIN
RETURN [Link](GeoCoord(51.5, 0));
END;
END;
/ [Filename: ObjRel/[Link]]

9.1 Objekt-Relationale Konzepte 264

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

E RZEUGUNG VON O BJEKTEN

• Konstruktormethode: ‹type›(‹arg_1›, ..., ‹arg_n›)
Also kein NEW, sondern nur einfach
GeoCoord(48,8)
CityORType(’Berlin’, ’Berlin’, ’D’, 3472009, GeoCoord(52.45, 13.3))

M ETHODENAUFRUF
• Funktionen: in Anfragen oder in PL/SQL-Programmen
• Prozeduren: in PL/SQL-Programmen
• Syntax: ‹object›.‹method-name›(‹argument-list›)
Beispiel
Wie groß ist der Abstand zwischen zwei Längengraden auf der Höhe von Berlin, bzw. am
Äquator?
SELECT geoCoord(52.45,-30).Distance(geoCoord(52.45,-31))
FROM DUAL;
SELECT geoCoord(0,-30).Distance(geoCoord(0,-31))
FROM DUAL;

9.1 Objekt-Relationale Konzepte 265

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

9.1.2 Verwendung von Objekttypen

• Als Werte von Attributen: “Spaltenobjekte”

(vgl. Geo-Koordinaten)
• in Objekttabellen: TABLE OF <objecttype>
“Zeilenobjekte”
vollwertige Objekte

9.1 Objekt-Relationale Konzepte 266

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

S PALTENOBJEKTE
• Attribut eines Tupels oder eines Objekts ist objektwertig:

CREATE TABLE Mountain

( Name VARCHAR2(20)
CONSTRAINT MountainKey PRIMARY KEY,
Elevation NUMBER,
Coordinates GeoCoord CONSTRAINT MountainCoord
CHECK (([Link] >= -90) AND
([Link] <= 90) AND
([Link] > -180) AND
([Link] <= 180)));
[Filename: ObjRel/[Link]]
• Constraints werden wie immer bei der Tabellendefinition angegeben.
INSERT INTO Mountain
VALUES (’Feldberg’, 1493, GeoCoord(48.5, 7.5));
SELECT Name, [Link](geocoord(90, 0))
FROM Mountain mt;
• Tupelvariable mt um den Zugriffspfad zu [Link] eindeutig zu machen.
9.1 Objekt-Relationale Konzepte 267
 Praktikum: Datenbankprogrammierung in SQL/ORACLE

Z EILENOBJEKTE
• Elemente von Objekttabellen,
• ihre Attribute verhalten sich genauso wie die Attribute von Tupeltabellen,
• zusätzlich kann man Methoden aufrufen,
• referentielle Integritätsbedingungen zwischen bestehenden relationalen Tabellen und
solchen Objekttabellen wie üblich formulierbar,
• (erhalten eine eindeutige OID und sind damit referenzierbar)

CREATE TABLE ‹name› OF ‹object-datatype›

[(‹constraint-list›)];

mit ‹constraint-list› wie bisher:

• attributbezogene Bedingungen entsprechen den Spaltenbedingungen:

‹attr-name› [DEFAULT ‹value›]

[‹colConstraint› ... ‹colConstraint›]

• Tabellenbedingungen: Syntax wie bei Tupeltabellen.

9.1 Objekt-Relationale Konzepte 268

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

Zeilenobjekte

Beispiel: CityORType
Objekt-Relationaler City-Typ:
• Spalten des Typs sind literal- oder objektwertig,
• noch keine Objektreferenzen

CREATE OR REPLACE TYPE CityORType AS OBJECT

( Name VARCHAR2(50),
Province VARCHAR2(50),
Country VARCHAR2(4),
Population NUMBER,
Coordinates GeoCoord,
MEMBER FUNCTION Distance (other IN CityORType)
RETURN NUMBER,
MEMBER FUNCTION NoOfOrganizations
RETURN NUMBER);
/
[Filename: ObjRel/[Link]]

9.1 Objekt-Relationale Konzepte 269

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

Zeilenobjekte

CREATE OR REPLACE TYPE BODY CityORType

AS
MEMBER FUNCTION Distance (other IN CityORType) RETURN NUMBER
IS
BEGIN
RETURN [Link]([Link]);
END;
MEMBER FUNCTION NoOfOrganizations RETURN NUMBER
IS
n NUMBER;
BEGIN
SELECT count(*) INTO n
FROM Organization o
WHERE [Link] = [Link]
AND [Link] = [Link] AND [Link] = [Link];
RETURN n;
END; END;
/ [Filename: ObjRel/[Link]]

9.1 Objekt-Relationale Konzepte 270

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

O BJEKTTABELLEN : Z EILENOBJEKTE
• der (ggf. mehrspaltige) Primärschlüssel wird als Tabellenbedingung angegeben,
• Die Fremdschlüsselbedingung auf die relationale Tabelle Country wird ebenfalls als
Tabellenbedingung angegeben:

CREATE TABLE ORCity OF CityORType

( PRIMARY KEY (Name, Province, Country),
FOREIGN KEY (Country) REFERENCES Country(Code));

• Objekte werden unter Verwendung des Objektkonstruktors ‹object-datatype› in

Objekttabellen eingefügt.

INSERT INTO ORCity

SELECT CityORType
( Name, Province, Country, Population,
GeoCoord(Latitude, Longitude))
FROM City;
[Filename (beides zusammen): ObjRel/[Link]]

9.1 Objekt-Relationale Konzepte 271

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

Verwendung von Objekttabellen

Auslesen und Ändern von Attributwerten wie bekannt

• Auslesen:
SELECT Name FROM ORCity;
SELECT * FROM ORCity;
• Ändern:
UPDATE ORCity cty
SET coordinates = NULL
WHERE [Link] IS NULL;

Methodenaufrufe wie erwartet

SELECT Name, [Link]() FROM ORCity c

WHERE [Link]() > 0;

9.1 Objekt-Relationale Konzepte 272

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

Funktionen auf Objekten

... Verwendung von Objekten als Objektinstanzen oder als Objektreferenzen

• Objektinstanzen bestehen aus ihren Feldern, sie haben keine Identität;
• Referenzen sind Referenzen auf Objekte:
• SELECT * und SELECT ‹attrname› wie bisher,
• SELECT ‹var› FROM ‹o-tablename› ‹var› ist nicht erlaubt. Mit der SQL-Variable ‹var›
kann man nur Pfadausdrücke bilden, oder Funktionen darauf anwenden.
• SELECT cty FROM ORCity cty ist nicht erlaubt, Pfadausdrücke [Link] etc. sind erlaubt.
• SELECT VALUE(‹var›): Objektinstanz,
• SELECT REF(‹var›): Referenz auf das Objekt
• (beide natürlich nur auf Zeilenobjekte anzuwenden, denn nur solche können an eine ‹var›
gebunden sein, Spaltenobjekte bekommt man nur mit Pfadausdrücken, und bei denen
braucht man diese Funktionen nicht).

9.1 Objekt-Relationale Konzepte 273

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

Die VALUE()-Funktion

VALUE (‹var›)
selektiert eine Objektinstanz
SELECT VALUE(cty)
FROM ORCity cty; VALUE(Cty)(Name, Province, Country, Population,
Coordinates(Latitude, Longitude))
CityORType(’Berlin’, ’Berlin’, ’D’, 3472009, GeoCoord(52, 13))
CityORType(’Bonn’, ’Nordrh.-Westf., ’D’, 293072, GeoCoord(50, 8))
CityORType(’Stuttgart’, ’Baden-Württ., ’D’, 588482, GeoCoord(49, 9))
:

9.1 Objekt-Relationale Konzepte 274

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

Verwendung von VALUE

• Objekte als Argumente einer Methode (siehe nächste Folie),

• Wenn man zwei Objekte mit VALUE(‹var1 ›) = VALUE(‹var1 ›)
vergleicht, werden die einzelnen Attributwerte verglichen. NULLwerte sind dabei weder
gleich noch ungleich!
• Zuweisung eines Objektes mit einem SELECT INTO-Statement an eine PL/SQL-Variable
set serveroutput on;
DECLARE c cityORtype;
BEGIN
SELECT VALUE(cty) INTO c
FROM ORcity cty
WHERE name=’Berlin’;
dbms_output.put_line([Link]);
END;
/

9.1 Objekt-Relationale Konzepte 275

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

Die REF()-Funktion

REF (‹var›)
• eine Referenz auf das Objekt, die im Prinzip äquivalent zu dessen OID ist
• kann bei objektorientierter Modellierung (Siehe Abschnitt 283) als Wert einer
REF(‹type›)-Spalte verwendet werden,
• kann zur Überprüfung von Objektgleichheit verwendet werden

SELECT [Link], [Link], SELECT [Link], [Link],

[Link]([Link]) [Link](VALUE(cty2))
FROM ORCity cty1, ORCity cty2 FROM ORCity cty1, ORCity cty2
WHERE NOT REF(cty1) = REF(cty2); WHERE NOT REF(cty1) = REF(cty2);

(wenn in der WHERE-Klausel “VALUE” anstatt “REF” verwendet würde, würden alle Städte mit
Population NULL mit sich selbst als “nicht nachgewiesenermaßen ungleich” gesehen und
ausgegeben werden)

9.1 Objekt-Relationale Konzepte 276

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

9.1.3 ORDER- und MAP-Methoden

• Objekttypen besitzen im Gegensatz zu den Datentypen NUMBER und VARCHAR keine

inhärente Ordnung.
• Ordnung auf Objekten eines Typs kann über dessen funktionale Methoden definiert werden.
• Für jeden Objekttyp eine MAP FUNCTION oder eine ORDER FUNCTION.
MAP-Funktion: (Betragsfunktion)
• keine Parameter,
• bildet jedes Objekt auf eine Zahl ab.
• Lineare Ordnung auf dem Objekttyp, “Betragsfunktion”
• sowohl für Vergleiche <, > und BETWEEN, als auch für ORDER BY verwendbar.
ORDER-Funktion: (vgl. Methode compareTo(other) des “Comparable” Interfaces in Java)
• besitzt ein Argument desselben Objekttyps das mit dem Hostobjekt verglichen wird.
• Damit sind ORDER-Funktionen für Vergleiche <, > geeignet, im allgemeinen aber nicht
unbedingt für Sortierung.
• MAP- und ORDER-Funktionen dürfen keinen Datenbankzugriff enthalten.

9.1 Objekt-Relationale Konzepte 277

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

MAP-Methoden: Beispiel

MAP-Methode auf GeoCoord:

CREATE OR REPLACE TYPE BODY GeoCoord
AS
:
MAP MEMBER FUNCTION Distance_Greenwich RETURN NUMBER
IS
BEGIN
RETURN [Link](GeoCoord(51.5, 0));
END;
END;
/
SELECT Name, [Link], [Link],
[Link].Distance_Greenwich()
FROM ORCity cty
WHERE NOT coordinates IS NULL
ORDER BY coordinates; [Filename: ObjRel/[Link]]

9.1 Objekt-Relationale Konzepte 278

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

ORDER-M ETHODEN
• Vergleich von SELF mit einem anderen Objekt desselben Typs, das formal als Parameter
angegeben wird.
• Ergebnis: NUMBER
– x < 0 falls SELF < Parameter,
– 0 (Gleichheit), oder
– x > 0 falls SELF > Parameter.
• Mit
SELECT ...
FROM ‹tablename› x
ORDER BY VALUE(x)
werden die Ausgabeobjekte paarweise verglichen und entsprechend der ORDER-Methode
geordnet.
• Ein Beispiel hierfür ist die Erstellung der Fussball-Bundesligatabelle: Ein Verein wird vor
einem anderen plaziert, wenn er mehr Punkte hat. Bei Punktgleichheit entscheidet die
Tordifferenz. Ist auch diese dieselbe, so entscheidet die Anzahl der geschossenen Tore
(vgl. Aufgabe).

9.1 Objekt-Relationale Konzepte 279

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

V ERGLEICHE ZWISCHEN O BJEKTEN

• <, >, “between” und Ordnung basieren auf der MAP bzw. ORDER-Methode.
• Gleichheit/Ungleichheit:
– bei Zeilenobjekten: diese haben eine Objektidentität; Vergleich mit VALUE(obj1 ) =
VALUE(obj2 ).
– Anmerkung: bei Spaltenobjekten, wie z.B. Instanzen von GeoCoord, wäre ein Vergleich
auf Objektidentität nicht sinnvoll.
– der Vergleich obj1 = obj2
basiert auf der MAP bzw. ORDER-Methode.
⇒ Fehler beim Compilieren, falls keine solche definiert ist!
⇒ falls eine solche definiert ist, wird sie verwendet, und damit “betragsgleiche” Objekte als
gleich behandelt!
⇒ “Echten” Vergleich ggf. ausprogrammieren auf Basis der “Schlüsselattribute” der
Objekte (ggf. in equals(. . . )-Methode).

9.1 Objekt-Relationale Konzepte 280

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

9.1.4 Objektrelationale Modellierung: Zusammenfassung

• Objekte anstatt Tupel oder Attributwerte

• Anfragen praktisch unverändert gegenüber rein relationaler DB (insb. Beziehungen
weiterhin über Schlüssel/Fremdschlüssel und Join-basierte Anfragen)
• zusätzlich Methoden, Ordnungsmethoden.

9.1 Objekt-Relationale Konzepte 281

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

B EISPIEL /AUFGABE

(wird auf Folie 294 analog ausprogrammiert)

• City, Country, Organization als Objekttypen und -tabellen
• Komfortablere Methoden: Mitgliedschaften werden über Methoden eingetragen und
abgefragt (ohne Berücksichtigung der Arten der Mitgliedschaft):
[Link](carcode)
[Link](org-abbrev)
[Link](carcode)
[Link](org-abbrev)
Interne Implementierung z.B. über die bekannte Tabelle isMember.
Hinweis: Boolesche Anfragen der Art “Ist x Mitglied in y” sind damit möglich. Es ist jedoch
keine Methode “alle Mitglieder von y möglich – diese müsste eine Relation bzw. Menge
zurückgeben.
• ... man kann aber diese Implementierung dann auch beliebig ändern.

9.1 Objekt-Relationale Konzepte 282

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

9.2 Objektorientierte Modellierung

bisher: Datentypen im wesentlichen zur Bereitstellung von spezialisiertem Verhalten:
• Built-in: DATE
• zusammengesetzt: Geo-Koordinaten
• Geschachtelte Tabellen (parametrisierter Datentyp)
• benutzerdefinierte Objekttypen
• Grundlage für Datentypen wie XMLType etc.

Objektorientierte Modellierung
Geht über die Nutzung als “Datentypen” hinaus ...
• ... zu Modellierungsaspekten:
• Spezialisierung: Klassenhierarchie; Subtypen als Spezialisierung allgemeiner Typen.
• Objekt-Identität und Referenzen auf Objekte als Werte von Attributen zum Ausdrücken von
Beziehungen,
• Objekte: Wertattribute und Referenzattribute.
• Anfragen durch Navigation etc. (⇒ unsymmetrisch)

9.2 Objekt-Relationale Konzepte 283

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

O BJEKTREFERENZEN
• Weiterer Datentyp für Attribute: Referenzen auf Objekte
‹ref-attr› REF ‹object-datatype›
• Objekttyp als Ziel der Referenz.
• nur Objekte, die eine OID besitzen – also Zeilenobjekte einer Objekttabelle – können
referenziert werden.
• Erzeugen einer Referenz (Selektieren einer OID) mit REF():
SELECT ..., REF(‹var›), ...
FROM ‹object-table› ‹var›
WHERE ... ;

9.2 Objekt-Relationale Konzepte 284

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

Objektreferenzen: Constraints

• Objekttyp kann in verschiedenen Tabellen vorkommen:

Einschränkung auf eine bestimmte Tabelle bei der Deklaration der entsprechenden Tabelle
als Spalten- oder Tabellenconstraints mit SCOPE:
– als Spaltenconstraint (nur bei Tupeltabellen):
‹ref-attr› REF ‹object-datatype›
SCOPE IS ‹object-table›
– als Tabellenconstraint:
SCOPE FOR (‹ref-attr›) IS ‹object-table›
• problemlose Integration referentieller Integritätsbedingungen von Objekttabellen zu
bestehenden relationalen Tabellen.
• PRIMARY KEYs und UNIQUE dürfen keine REF-Attribute umfassen.
• Referentielle Integritätsbedingung
FOREIGN KEY (‹ref-attr›) REFERENCES ‹object-table›
geht auch, wenn auf ‹object-table› kein Primary Key definiert ist (verwendet Object-ID).

9.2 Objekt-Relationale Konzepte 285

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

Beispiel: Objekttyp Organization

CREATE TYPE Member_Type AS OBJECT

(Country VARCHAR2(4),
Type VARCHAR2(60));
/
CREATE TYPE Member_List_Type AS
TABLE OF Member_Type;
/
CREATE OR REPLACE TYPE Organization_Type AS OBJECT
( Name VARCHAR2(80),
Abbrev VARCHAR2(12),
Members Member_List_Type,
Established DATE,
hasHqIn REF CityORType,
MEMBER FUNCTION isMember (the_country IN VARCHAR2) RETURN VARCHAR2,
-- [Link](’SLO’) = ’membership applicant’
MEMBER FUNCTION people RETURN NUMBER,
MEMBER FUNCTION numberOfMembers RETURN NUMBER,
MEMBER PROCEDURE addMember (the_country IN VARCHAR2, the_type IN VARCHAR2));
/
[Filename: Obj/[Link]]

9.2 Objekt-Relationale Konzepte 286

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

Beispiel: Objekttyp Organization

CREATE TABLE Organization_ObjTab OF Organization_Type

( Abbrev PRIMARY KEY,
SCOPE FOR (hasHqIn) IS ORCity)
NESTED TABLE Members STORE AS Members_nested;

• Type Body noch nicht definiert. Erstmal nur mit einem Objekt als Beispiel:
Einfügen unter Verwendung des Objektkonstruktors:

INSERT INTO Organization_ObjTab VALUES

(Organization_Type(’European Community’, ’EU’, Member_List_Type(), NULL, NULL));

Setzen des Referenzattributes hasHqIn:

UPDATE Organization_ObjTab
SET hasHqIn =
( SELECT REF(cty)
FROM ORCity cty
WHERE Name = ’Brussels’ AND Province = ’Brabant’ AND Country = ’B’)
WHERE Abbrev = ’EU’;
[Filename (alles zusammen): Obj/[Link]]

9.2 Objekt-Relationale Konzepte 287

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

S ELEKTION VON O BJEKTATTRIBUTEN

• Wertattribute
SELECT Name, Abbrev, Members
FROM Organization_ObjTab; Name Abbrev Members
European Community EU Member_List_Type(...)
• Referenzattribute:
SELECT ‹ref-attr-name› und Pfadausdrücke die mit einem solchen enden liefern die
Objektreferenz (die als OID dient):
SELECT Name, Abbrev, hasHqIn
FROM Organization_ObjTab; Name Abbrev hasHqIn
European Community EU ‹oid_long_string›

• DEREF(‹oid›) liefert die zugehörige Objektinstanz:

(VALUE(‹var›) kann man nicht auf einen Pfadausdruck anwenden)
SELECT Abbrev, DEREF(hasHqIn)
FROM Organization_ObjTab; Abbrev hasHqIn
EU CityORType(’Bruxelles’, ’Bruxelles’, ’B’,
168576, GeoCoord(50.85, 4.35))

9.2 Objekt-Relationale Konzepte 288

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

V ERWENDUNG VON R EFERENZATTRIBUTEN

• Attribute und Methoden eines referenzierten Objekts werden durch Pfadausdrücke der
Form
SELECT ‹ref-attr-name›.‹attr-name›
adressiert (“navigierender Zugriff ”).
• Aliasing mit einer Variablen ist notwendig um den Pfadausdruck eindeutig zu machen:
SELECT Abbrev, [Link]
FROM Organization_ObjTab org; Abbrev [Link]
EU Bruxelles
• Auch erlaubt:
SELECT name, DEREF(hasHqIn).name
FROM Organization_ObjTab;
Die Funktionen VALUE, REF, DEREF
Mit REF und DEREF lässt sich VALUE ersetzen:
SELECT VALUE(org) FROM Organization_ObjTab org;
und
SELECT DEREF(REF(org)) FROM Organization_ObjTab org;

9.2 Objekt-Relationale Konzepte 289

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

Z YKLISCHE R EFERENZEN

Die Modellierung als Objektgraph (d.h., Beziehungen nicht durch Tabellen, sondern als
Objektreferenzen) führt oft zu Zyklen:
• City_Type: country REF Country_Type
• Country_Type: capital REF City_Type
• Deklaration jedes Datentypen benötigt bereits die Definition des anderen.
• Definition von unvollständigen Typen
“Forward-Deklaration”:
CREATE TYPE ‹name›;
/
• wird später durch eine komplette Typdeklaration ergänzt.

9.2 Objekt-Relationale Konzepte 290

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

Unvollständige Datentypen
Unvollständige Datentypen können nur zur Definition von Referenzen auf sie benutzt werden,
nicht zur Definition von Spalten oder in geschachtelten Tabellen:
CREATE OR REPLACE TYPE City_type;
/
• Die Nutzung in Referenzen ist damit erlaubt:
CREATE TYPE city_list AS TABLE OF REF City_type;
/
CREATE OR REPLACE TYPE Country_Type AS OBJECT
( Name VARCHAR2(50),
• Die direkte Nutzung wäre erst erlaubt, wenn Ci-
Code VARCHAR2(4),
ty_type komplett ist:
Capital REF City_Type);
CREATE TYPE city_list_2 AS TABLE OF City_type;
/
/ – waere eine Tabelle von City-Objekten
CREATE OR REPLACE TYPE Country_Type_2 AS OBJECT
( Name VARCHAR2(50),
Code VARCHAR2(4),
Capital City_Type);
/ – Capital waere ein Spaltenobjekt

9.2 Objekt-Relationale Konzepte 291

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

Zyklische Referenzen: Beispiel

CREATE OR REPLACE TYPE City_Type
/
CREATE OR REPLACE TYPE Country_Type AS OBJECT
( Name VARCHAR2(50),
CREATE OR REPLACE TYPE Province_Type AS OBJECT
Code VARCHAR2(4),
( Name VARCHAR2(50),
Capital REF City_Type,
Country REF Country_Type,
Area NUMBER,
Capital REF City_Type,
Population NUMBER);
Area NUMBER,
Population NUMBER);
/

CREATE OR REPLACE TYPE City_Type AS OBJECT

( Name VARCHAR2(50),
Province REF Province_Type,
Country REF Country_Type,
Population NUMBER,
Coordinates GeoCoord);
/

9.2 Objekt-Relationale Konzepte 292

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

O BJEKTORIENTIERUNG : M ODELLIERUNGSASPEKTE
• Beziehungen durch Referenzattribute,
• Anfragen per Navigation (anstatt Join),
• können nur in einer Richtung verfolgt werden,
• erfordert also ggf. doppelte Speicherung,
• müssen dann auf beiden Seiten separat konsistent gehalten werden.

Beispiel/Aufgabe

• City, Country, Organization als Objektgraph

• Beziehungen immer über Methoden behandeln:
[Link](carcode)
[Link](org-abbrev)
[Link](carcode)
[Link](org-abbrev)
• Interne Implementierung von z.B. Mitgliedschaften wie oben als Collection von Referenzen,
oder über die bekannte Tabelle isMember.

9.2 Objekt-Relationale Konzepte 293

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

9.3 Methoden: Funktionen und Prozeduren

TYPE BODY enthält die Implementierungen der Methoden in PL/SQL

Anpassung von PL/SQL an Objektrelationale Features

• PL/SQL unterstützt keine Navigation entlang Pfadausdrücken (in SQL ist es erlaubt).
• Jede MEMBER METHOD besitzt einen impliziten Parameter SELF, der das jeweilige Host-Objekt
referenziert.
• Tabellenwertige Attribute können innerhalb PL/SQL wie PL/SQL-Tabellen behandelt
werden:
Built-in Methoden für Collections (PL/SQL-Tabellen) können auch auf tabellenwertige
Attribute angewendet werden:
‹attr-name›.COUNT: Anzahl der in der geschachtelten Tabelle enthaltenen Tupel
Verwendung in in PL/SQL eingebetteten SQL-Statements – z.B. SELECT ‹attr›.COUNT –
nicht erlaubt.
• Weitere Erweiterung: Java (siehe Folie 341).

9.3 Objekt-Relationale Konzepte 294

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

Member-Methods: Beispiel

CREATE OR REPLACE TYPE BODY Organization_Type IS

MEMBER FUNCTION isMember (the_country IN VARCHAR2)

RETURN VARCHAR2
IS
BEGIN
IF [Link] IS NULL OR [Link] = 0
THEN RETURN ’no’; END IF;
FOR i in 1 .. [Link]
LOOP
IF the_country = Members(i).country
THEN RETURN Members(i).type; END IF;
END LOOP;
RETURN ’no’;
END; (bitte umblättern)

9.3 Objekt-Relationale Konzepte 295

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

Member-Methods: Beispiel (Forts.)

MEMBER FUNCTION people RETURN NUMBER IS

p NUMBER;
BEGIN
SELECT SUM(population) INTO p
FROM Country ctry
WHERE [Link] IN
( SELECT Country
FROM THE (SELECT Members
FROM Organization_ObjTab org
WHERE [Link] = [Link]));
RETURN p;
END;

MEMBER FUNCTION numberOfMembers RETURN NUMBER

IS
BEGIN
IF [Link] IS NULL THEN RETURN 0; END IF;
RETURN [Link];
END; (bitte umblättern)

9.3 Objekt-Relationale Konzepte 296

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

Member-Methods: Beispiel (Forts.)

MEMBER PROCEDURE addMember (the_country IN VARCHAR2, the_type IN VARCHAR2) IS

BEGIN
IF NOT [Link](the_country) = ’no’
THEN RETURN; END IF;
IF [Link] IS NULL THEN
UPDATE Organization_ObjTab
SET Members = Member_List_Type()
WHERE Abbrev = [Link];
END IF;
INSERT INTO
THE (SELECT Members
FROM Organization_ObjTab org
WHERE [Link] = [Link])
VALUES (the_country, the_type);
END;
END;
/ [Filename: Obj/[Link]]

• FROM THE(SELECT ...) kann nicht durch FROM [Link] ersetzt werden (PL/SQL vs. SQL).

9.3 Objekt-Relationale Konzepte 297

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

M ETHODENAUFRUFE

Funktionen

• MEMBER FUNCTIONS können in SQL und PL/SQL durch

‹object›.‹function›(‹argument-list›) selektiert werden.
• parameterlose Funktionen: ‹object›.‹function›()
• aus SQL: ‹object› ist durch einen Pfadausdruck mit Alias gegeben.
SELECT Name, [Link](’D’)
FROM Organization_ObjTab org
WHERE NOT [Link](’D’) = ’no’;
(noch ist die Tabelle aber nicht sinnvoll gefüllt ...)

Prozeduren

• MEMBER PROCEDURES können nur aus PL/SQL mit

‹objekt›.‹procedure›(‹argument-list›) aufgerufen werden.
⇒ freie Prozeduren in PL/SQL als Wrapper, um MEMBER PROCEDURES aufzurufen

9.3 Objekt-Relationale Konzepte 298

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

Beispiel: Freie Prozedur als Wrapper eines Objektmethodenaufruf

CREATE OR REPLACE PROCEDURE makeMember

(the_org IN VARCHAR2, the_country IN VARCHAR2, the_type IN VARCHAR2)
IS
n NUMBER;
x Organization_Type;
BEGIN
SELECT COUNT(*) INTO n
FROM Organization_ObjTab
WHERE Abbrev = the_org;
IF n = 0
THEN INSERT INTO Organization_ObjTab
VALUES(Organization_Type(NULL, the_org, Member_List_Type(), NULL, NULL));
END IF;
SELECT VALUE(org) INTO x
FROM Organization_ObjTab org
WHERE Abbrev = the_org;
IF [Link](the_country)=’no’ THEN [Link](the_country, the_type);
END IF; EXECUTE makeMember(’EU’, ’USA’, ’special member’);
END; EXECUTE makeMember(’XX’, ’USA’, ’member’);
/ [Filename: Obj/[Link]]
9.3 Objekt-Relationale Konzepte 299
 Praktikum: Datenbankprogrammierung in SQL/ORACLE

Beispiel: Füllen der Objekttabelle

Übertragung des Datenbestandes aus den relationalen Tabellen Organization und isMember in
die Objekttabelle Organization_ObjTab:
INSERT INTO Organization_ObjTab
(SELECT Organization_Type (Name, Abbreviation, NULL, Established, NULL)
FROM Organization);
CREATE OR REPLACE PROCEDURE Insert_All_Members IS
BEGIN
FOR mship IN (SELECT * FROM isMember)
LOOP makeMember([Link], [Link], [Link]);
END LOOP;
END;
/
EXECUTE Insert_All_Members;
UPDATE Organization_ObjTab org
SET hasHqIn =
( SELECT REF(cty)
FROM ORCity cty, Organization old
WHERE [Link] = [Link]
AND [Link] = [Link] AND [Link] = [Link] AND [Link] = [Link]);
[Filename: Obj/[Link]]
9.3 Objekt-Relationale Konzepte 300
 Praktikum: Datenbankprogrammierung in SQL/ORACLE

Beispiel: Nutzung freier Methoden

CREATE OR REPLACE FUNCTION isMemberIn

(the_org IN VARCHAR2, the_country IN VARCHAR2)
RETURN [Link]%TYPE IS
t [Link]%TYPE;
BEGIN
SELECT [Link](the_country) INTO t
FROM Organization_ObjTab org
WHERE Abbrev=the_org;
RETURN t;
END;
/ [Filename: Obj/[Link]]
SELECT isMemberIn(’EU’,’TR’) Es ist (zumindest bis O RACLE 19 – (20.3.2022)) nicht
FROM DUAL; möglich, durch Navigation mit Pfadausdrücken Tabellen-
isMemberIn(’EU’, ’TR’) inhalte zu verändern:
candidate UPDATE Organization_ObjTab org
SET [Link] = ’Big Apple’ -- NICHT ERLAUBT
WHERE [Link] = ’UN’;

9.3 Objekt-Relationale Konzepte 301

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

M ODELLIERUNG VS . I MPLEMENTIERUNG
• Das Beispiel illustriert Objektmethoden und ihre Anbindung durch freie Methoden am
objektorientierten Szenario:
– Headquarter als Referenz,
– Mitglieder als geschachtelte Tabelle,
• man kann dieselben Methoden auch mit einer objektrelationalen Tabelle OROrganization,
und Ablegen der Mitgliedschaften in der Relation isMember implementieren (Aufgabe).
⇒ für den Benutzer bleiben die freien Methoden dieselben.

9.3 Objekt-Relationale Konzepte 302

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

9.4 Klassenhierarchie und Vererbung

(Dieser Abschnitt ist optional)

geo_object

water mountain desert island

sea lake river

9.4 Objekt-Relationale Konzepte 303

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

K LASSENHIERARCHIE UND V ERERBUNG

• seit O RACLE 9i
• Typhierarchie:
• Subtyp: abgeleitet von einem Elterntyp
• Vererbung: Verbindung zwischen Subtypen und ihren Obertypen in einer Typhierarchie
• Subtypen: “Spezialisierung”
– hinzufügen neuer Attribute und Methoden
– overriding (Überschreiben) von geerbten Methoden
• Spezielle Eigenschaften von Klassen:
abstrakte (NOT INSTANTIABLE) und finale (FINAL) Klassen
• abstrakte Klassen können abstrakte Methoden
(NOT INSTANTIABLE) haben
• Klassen können finale Methoden haben:
diese können in Subklassen nicht überschrieben werden

9.4 Objekt-Relationale Konzepte 304

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

E RZEUGEN VON S UBTYPEN

Abstrakte Klasse für geographische Objekte

(die alle einen Namen besitzen):
CREATE OR REPLACE TYPE geo_object_type AS OBJECT (
name VARCHAR2(50),
MEMBER FUNCTION get_name RETURN VARCHAR2,
NOT INSTANTIABLE MEMBER FUNCTION set_name RETURN VARCHAR2
)
NOT INSTANTIABLE -- DEFAULT: INSTANTIABLE
NOT FINAL; -- DEFAULT: FINAL
/

CREATE OR REPLACE TYPE BODY geo_object_type IS

MEMBER FUNCTION get_name RETURN VARCHAR2
IS BEGIN RETURN name; END;
-- no implementation for set_name (to be class-specific)
END;
/

9.4 Objekt-Relationale Konzepte 305

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

Erzeugen von Subtypen

CREATE OR REPLACE TYPE water_type

UNDER geo_object_type (
MEMBER FUNCTION bla RETURN NUMBER
-- empty derivation not allowed in current version
)
NOT FINAL
NOT INSTANTIABLE;
/

• + Angabe eines TYPE BODY, der bla implementiert.

9.4 Objekt-Relationale Konzepte 306

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

Erzeugen von Subtypen

• finale Klassen für Meere, Seen und Flüsse etc.
• müssen alle bis jetzt nicht implementierten Methoden anbieten
• erfordert OVERRIDING
CREATE OR REPLACE TYPE sea_type
UNDER water_type (
depth NUMBER,
OVERRIDING
MEMBER FUNCTION set_name RETURN VARCHAR2,
[OVERRIDING -- optional
MEMBER FUNCTION bla RETURN NUMBER]
)
INSTANTIABLE;
/

• + Angabe eines TYPE BODY, der set_name implementiert.

• optional kann man auch bla überschreiben.
• analog für Meere, Flüsse, Berge, Inseln und Wüsten.

9.4 Objekt-Relationale Konzepte 307

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

TABELLEN ÜBER ALLGEMEINEN K LASSEN

• eine Tabelle für alle geographischen Objekte

CREATE TABLE geo_obj OF geo_object_type;

INSERT INTO geo_obj
SELECT sea_type(name, depth) FROM sea;
INSERT INTO geo_obj
SELECT lake_type(name, area) FROM lake;
INSERT INTO geo_obj
SELECT river_type(name, NULL, NULL, NULL, length)
FROM river;
INSERT INTO geo_obj
SELECT mountain_type(name, elevation, coordinates)
FROM mountain;
INSERT INTO geo_obj
SELECT desert_type(name, area) FROM desert;
INSERT INTO geo_obj
SELECT island_type(name, islands, area, coordinates)
FROM island;

9.4 Objekt-Relationale Konzepte 308

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

Anfragen an Tabellen über allgemeinen Klassen

• die Tabelle geo_obj ist eine Kollektion von Objekten der Klasse geo_obj_type (abstrakt)
• enthält Instanzen der finalen Subklassen, z.B. Flüsse und Berge.
• Substituierbarkeit:
“Ein Objekt eines Typs t kann überall auftreten, wo ein Objekt eines Obertyps von t
erwartet wird”
– Zeilenobjekte in Objekttabellen
– Spaltenobjekte (objektwertige Attribute)
– Referenzattribute
– Argumente und Rückgabewerten von Methoden
• select name from geo_obj;
da alle geo_objects einen Namen haben.

9.4 Objekt-Relationale Konzepte 309

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

Anfragen an klassenspezifische Eigenschaften

• Kollektion von Instanzen einer abstrakten Klasse

• Auswahl der Objekte einer speziellen Subklasse
• Verwendung von klassenspezifischen Eigenschaften
• ähnlich wie in C++/Java: Typumwandlungen

Speziellste Klassenzugehörigkeit

• SYS_TYPEID(‹object›)
ergibt die ID der speziellsten Klasse, zu der ein Objekt gehört
• herausfinden des Klassennamens in all_types

SELECT type_name, typeid, supertype_name

FROM all_types type_name typeid supertype_name
WHERE typeid = (SELECT SYS_TYPEID(value(x)) mountain 08 geo_object
FROM geo_obj x
WHERE name=’Llullaillaco’);

9.4 Objekt-Relationale Konzepte 310

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

T YPTESTS
• ‹object› IS OF(‹type›)
testet ob ‹object› vom Typ ‹type› ist.
• normalerweise testet man Zugehörigkeit zu einem Subtyp des für die Tabelle bekannten
Typs.

• Ausgeben aller Namen von Bergen:

SELECT [Link]
FROM geo_obj x
WHERE value(x) IS OF (mountain_type);
• wie bekommt man die Namen und die Höhe?
SELECT [Link], [Link]
ist nicht erlaubt
(geo_objects haben keine Höhe!)

9.4 Objekt-Relationale Konzepte 311

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

T YPUMWANDLUNGEN
• TREAT (‹object› AS ‹type›)
behandelt ‹object› als eine Instanz des Typs ‹type›
• falls möglich
• sonst: NULL

SELECT [Link],
(TREAT (value(x) AS mountain_type)).elevation
FROM geo_obj x
WHERE value(x) IS OF (mountain_type);

9.4 Objekt-Relationale Konzepte 312

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

9.5 Diverses zu Objekttypen

Änderungen an Objekttypen
Benutzerdefinierte Typen können mit ALTER TYPE verändert werden:
• Hinzunehmen und Löschen von Attributen
• Hinzunehmen und Löschen von Methoden
• Modifikation eines numerischen Attributs (Länge, Präzision)
• VARCHAR kann verlängert werden
• Ändern der FINAL- und INSTANTIABLE-Eigenschaften

ALTER TYPE ‹type›

ADD ATTRIBUTE ‹name› ‹datatype›,
DROP ATTRIBUTE ‹name›,
MODIFY ATTRIBUTE ‹name› ‹datatype›,
ADD MEMBER FUNCTION/PROCEDURE ‹method-spec›
-- dann CREATE TYPE BODY neu machen!
DROP MEMBER FUNCTION/PROCEDURE ‹method-spec›
‹options›

9.5 Objekt-Relationale Konzepte 313

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

Ä NDERUNG VON T YPDEFINITIONEN : A BHÄNGIGKEITEN

Objekttypen-Definitionen und Referenzattribute erzeugen einen Graphen, der dem von

Fremdschlüsseldefinitionen erzeugten ähnlich ist.
• Abhängige Schemaobjekte, die einen Typ referenzieren sind z.B.:
– Tabellen
– Typen, insb. Subtypen
– PL/SQL: Prozeduren, Funktionen, Trigger
– Views, Objekt-Views
• Veränderungen: ALTER TYPE
• Propagieren von Änderungen: CASCADE
• Compilierbare abhängige Datenbankobjekte (PL/SQL, Sichten, . . . ): INVALIDATE
werden als invalid markiert und bei der nächsten Benutzung neu compiliert.
• Tabellen: neue Attribute werden mit NULLwerten initialisiert.

Die Datenbank muss nach Typveränderungen revalidiert werden

(siehe Handbücher).

9.5 Objekt-Relationale Konzepte 314

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

I NDEXE AUF O BJEKTATTRIBUTEN

Indexe können auch auf Objektattributen erstellt werden:

CREATE INDEX ‹name›
ON ‹object-table-name›.‹attr›[.‹attr›]∗ ;
• Indexe können nicht über komplexen Attributen erstellt werden:
-- nicht erlaubt:
CREATE INDEX city_index
ON City_ObjTab(coordinates);
• Indexe können über elementare Teilattribute eines komplexen Attributes erstellt werden:
CREATE INDEX city_index
ON City_ObjTab([Link],
[Link]);
• Funktions-basierte Indexe:
CREATE INDEX name ON
Organization_Obj_Tab (numberOfMembers);
arbeiten mit vorberechneten Werten.

9.5 Objekt-Relationale Konzepte 315

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

Z UGRIFFSRECHTE AUF O BJEKTE

Recht an Objekttypen:
GRANT EXECUTE ON ‹Object-datatype› TO ...
• bei der Benutzung eines Datentyps stehen vor allem die Methoden (u.a. die entsprechende
Konstruktormethode) im Vordergrund.

9.5 Objekt-Relationale Konzepte 316

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

R EFERENTIELLE I NTEGRITÄT
• Vgl. FOREIGN KEY ... REFERENCES ... ON DELETE/UPDATE CASCADE
• Veränderungen an Objekten:
OID bleibt unverändert
→ referentielle Integrität bleibt gewahrt.
• Löschen von Objekten:
dangling references möglich.

Überprüfung durch
WHERE ‹ref-attribute› IS DANGLING

Verwendung z.B. in einem AFTER-Trigger:

UPDATE ‹table›
SET ‹attr› = NULL
WHERE ‹attr› IS DANGLING;

9.5 Objekt-Relationale Konzepte 317

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

9.6 Object-Views
• maßgeschneiderte Object-Views mit sehr weitgehender Funktionalität
Legacy-Datenbanken: Integration bestehender Datenbanken in ein “modernes”
objektorientiertes Modell:
Objekt-Views über relationale Ebene legen:
“Objekt-Abstraktionen”
Effizienz + Benutzerfreundlichkeit:
Die relationale Repräsentation ist oft effizienter:
• Geschachtelte Tabellen intern als separate Tabellen gespeichert.
• n : m-Beziehungen: gegenseitige geschachtelte Tabellen notwendig.
⇒ Definition eines relationalen Basisschemas mit Object-Views.
Einfache Modifizierbarkeit: CREATE OR REPLACE TYPE und ALTER TYPE nur sehr
eingeschränkt
⇒ Veränderungen durch Neudefinition geeigneter Object-Views abfangen.
Häufige Empfehlung: Object Views mit geschachtelten Tabellen, Referenzen etc. auf Basis
eines relationalen Grundschemas verwenden.

9.6 Objekt-Relationale Konzepte 318

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

O BJECT-V IEWS
Benutzer führt seine Änderungen auf dem durch die Objektviews gegebenen externen Schema
durch.
• enthalten Zeilenobjekte, d. h. hier werden neue Objekte definiert.
• Abbildung direkter Änderungen (INSERT, UPDATE und DELETE) durch INSTEAD OF-Trigger auf
das darunterliegende Schema.
• Benutzer darf erst gar keine solchen Statements an das View stellen. Entsprechende
Funktionalität durch Methoden der Objekttypen, die die Änderungen direkt auf den
zugrundeliegenden Basistabellen ausführen.
Syntax
• durch WITH OBJECT OID ‹attr-list› wird angegeben, wie die Objekt-ID berechnet wird
werden soll.
• Verwendung von CAST und MULTISET.
CREATE [OR REPLACE] VIEW ‹name› OF ‹type›
WITH OBJECT OID (‹attr-list›)
AS ‹select-statement›;
• in ‹select-statement› wird kein Objektkonstruktor verwendet!
9.6 Objekt-Relationale Konzepte 319
 Praktikum: Datenbankprogrammierung in SQL/ORACLE

O BJECT V IEWS : Country

CREATE OR REPLACE TYPE Country_Type AS OBJECT
(Name VARCHAR2(50),
Code VARCHAR2(4),
Capital REF City_Type,
Area NUMBER,
Population NUMBER);
/
Sinnvollerweise würde man hier gleich auch noch Methoden definieren.
CREATE OR REPLACE VIEW Country_ObjV OF Country_Type
WITH OBJECT OID (Code)
AS
SELECT [Link], [Link], REF(cty), Area, [Link]
FROM Country, City_ObjTab cty
WHERE [Link] = [Link] AND [Link] = [Link]
AND [Link] = [Link];
SELECT Name, Code, [Link], Area, Population
FROM Country_ObjV c;

9.6 Objekt-Relationale Konzepte 320

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

O BJECT V IEWS : WAS NICHT GEHT

• Object View darf keine geschachtelte Tabelle und
• kein Ergebnis einer funktionalen Methode einer zugrundeliegenden Tabelle enthalten.
Object View auf Basis von Organization_ObjTab:
CREATE OR REPLACE TYPE Organization_Ext_Type AS OBJECT
( Name VARCHAR2(80),
Abbrev VARCHAR2(12),
Members Member_List_Type,
established DATE,
hasHqIn REF City_Type,
numberOfPeople NUMBER);
/

CREATE OR REPLACE VIEW Organization_ObjV OF Organization_Ext_Type

AS
SELECT Name, Abbrev, Members, established, hasHqIn, [Link]()
FROM Organization_ObjTab org;

FEHLER in Zeile 3: ORA-00932: nicht übereinstimmende Datentypen

Beide angegebenen Attribute sind auch einzeln nicht erlaubt.

9.6 Objekt-Relationale Konzepte 321

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

9.7 Objektorientierung und Data Dictionary

• TABS = USER_TABLES(table_name, ...): alle relationalen Tabellen die dem Benutzer
gehören
• USER_OBJECT_TABLES(table_name, table_type, nested...): alle Objekttabellen die dem
Benutzer gehören. table_type gibt den Objekttyp an, aus dessen Instanzen die Tabelle
besteht. Hierzu gehören auch Nested Tables!
• USER_ALL_TABLES(table_name, ...): alle Tabellen die dem Benutzer gehören.
• USER_OBJECTS(object_name, object_type, status): alle Datenbankobjekte, die dem
Benutzer gehören.
• Objekttabellen, deren Type mit DROP TYPE ... FORCE gelöscht wurde, stehen nicht mehr
in USER_OBJECT_TABLES. Sie existieren aber noch (neues CREATE TABLE ergibt eine
Fehlermeldung).
(Bsp. nächste Folie)
• USER_TYPES(type_name): alle Objekttypen, die dem Benutzer gehören.

9.7 Objekt-Relationale Konzepte 322

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

Löschen von Datenbankobjekten

CREATE TYPE bla AS OBJECT (x NUMBER);
/
CREATE TABLE blatab OF bla;
SELECT table_name, table_type FROM user_object_tables; -- name: blatab, type: bla
DROP TYPE bla FORCE;
SELECT table_name, table_type FROM user_object_tables; -- nichts
CREATE TYPE bla AS OBJECT (x NUMBER);
/
CREATE TABLE blatab OF bla; -- ORA-00955: name is already used by an existing object
SELECT * FROM user_objects WHERE object_name=’BLATAB’; -- blatab - invalid
SELECT object_name, object_type, status from user_objects;
SELECT object_name, object_type, status from user_objects WHERE object_type = ’TABLE’;
Problem: alle Tabellen löschen (Skript drop-all-tables)
• Verwendung von user_tables ignoriert invalide Tabellen.
• Nested Tables können nicht mit DROP TABLE gelöscht werden, sondern nur mit der
Tabelle zu der sie gehören.
• Verwendung von “user_objects where object_type = ’TABLE”’ bricht ab, wenn es eine
(möglicherweise invalide, also in user_object_tables auch nicht mehr als nested gelistete)
Nested Table löschen soll.

9.7 Objekt-Relationale Konzepte 323

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

9.8 Fazit

• Objektrelationale Tabellen (Folie 258):

Kompatibilität mit den grundlegenden Konzepten von SQL.
U.a. Fremdschlüsselbedingungen von objektrelationalen Tabellen zu relationalen Tabellen.
• Objektorientiertes Modell (Folie 283):
... etwas kompliziert zu handhaben.
• Object/Objekt-Relationale Views (Folie 318):
erlauben ein objektorientiertes externes Schema. Benutzer-Interaktionen werden durch
Methoden und INSTEAD OF-Trigger auf das interne Schema umgesetzt.
Implementierung auf relationaler Basis.
• Objekttypen-Konzept als Basis für (vordefinierte, in Java implementierte Klassen als)
Datentypen zur Behandlung von nicht-atomaren Werten (XML (siehe Folie 413),
Multimedia etc.).

9.8 Objekt-Relationale Konzepte 324

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

Kapitel 10
Embedded SQL
KOPPLUNGSARTEN ZWISCHEN DATENBANK - UND P ROGRAMMIERSPRACHEN

• Erweiterung der Datenbanksprache um Programmierkonstrukte (z.B. PL/SQL)

• Erweiterung von Programmiersprachen um Datenbankkonstrukte: Persistente
Programmiersprachen (Persistent Java – dann kein SQL als Anfragesprache)
• Datenbankzugriff aus einer Programmiersprache (JDBC)
• Einbettung der Datenbanksprache in eine Programmiersprache: “Embedded SQL” (C,
Pascal, Java/SQLJ)

10.0 Embedded SQL 325

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

10.1 Embedded SQL: Grundprinzipien

... realisiert für C, Pascal, C++, Java (als SQLJ, siehe Folie 401) und weitere.
Impedance Mismatch bei der SQL-Einbettung
• Typsysteme Programmiersprache ↔ Datenbanksystem passen nicht zusammen
• Unterschiedliche Paradigmen: Mengenorientiert vs. einzelne Variablen
Realisierte Lösung
• Abbildung von Tupeln bzw. Attributen auf Datentypen der Hostsprache,
• Iterative Verarbeitung der Ergebnismenge mittels Cursor.
Auswirkungen auf die Hostsprache
• Struktur der Hostsprache bleibt unverändert,
• Spezielle Anweisungen zum Verbindungsaufbau,
• Jede SQL-Anweisung kann eingebettet werden,
• Verwendung von “Hostvariablen” (der umgebenden Programmiersprache) in
SQL-Statements,
• SQL-Anweisungen wird EXEC SQL (oder sonstwas) vorangestellt.

10.1 Embedded SQL 326

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

E NTWICKLUNG EINER E MBEDDED SQL-A PPLIKATION

Embedded-SQL-Programm
z.B. [Link]/[Link]

Metadaten
Embedded-SQL-Precompiler

Quellprogramm
z.B. demo1.c/[Link]

Datenbank
(C-)Compiler/Linker

ausführbares Programm
z.B. demo1/[Link]

• SQLJ (siehe Folie 401): Zwischenschritt bei der Compilierung muss nicht separat
ausgeführt werden.

10.1 Embedded SQL 327

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

10.2 Embedded SQL in C [Legacy]

Hinweis: dieser Abschnitt kann ausgelassen und durch SQLJ (Folie 401) ersetzt werden. Er ist
nur noch für die Arbeit mit Legacy-Datenbanken relevant, die diese Technologie verwenden.

V ERBINDUNGSAUFBAU

Embedded-Anwendung: Verbindung zu einer Datenbank muss explizit hergestellt werden.

EXEC SQL CONNECT :username IDENTIFIED BY :passwd;
• username und passwd Hostvariablen vom Typ CHAR bzw. VARCHAR..
• Strings sind hier nicht erlaubt!
Äquivalent:
EXEC SQL CONNECT :uid;
wobei uid ein String der Form "name/passwd" ist.

10.2 Embedded SQL 328

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

H OSTVARIABLEN
• Kommunikation zwischen Datenbank und Anwendungsprogramm
• Output-Variablen übertragen Werte von der Datenbank zum Anwendungsprogramm
• Input-Variablen übertragen Werte vom Anwendungsprogramm zur Datenbank.
• jeder Hostvariablen zugeordnet: Indikatorvariable zur Verarbeitung von NULL-Werten.
• werden in der Declare Section deklariert:
EXEC SQL BEGIN DECLARE SECTION;
int population; /* host variable */
short population\_ind; /* indicator variable */
EXEC SQL END DECLARE SECTION;
• in SQL-Statements wird Hostvariablen und Indikatorvariablen ein Doppelpunkt (“:”)
vorangestellt
• Datentypen der Datenbank- und Programmiersprache müssen kompatibel sein

10.2 Embedded SQL 329

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

I NDIKATORVARIABLEN

Verarbeitung von Nullwerten und Ausnahmefällen

Indikatorvariablen für Output-Variablen:

• -1 : der Attributwert ist NULL, der Wert der Hostvariablen ist somit undefiniert.
• 0 : die Hostvariable enthält einen gültigen Attributwert.
• >0 : die Hostvariable enthält nur einen Teil des Spaltenwertes. Die Indikatorvariable gibt
die ursprüngliche Länge des Spaltenwertes an.
• -2 : die Hostvariable enthält einen Teil des Spaltenwertes wobei dessen ursprüngliche
Länge nicht bekannt ist.

Indikatorvariablen für Input-Variablen:

• -1 : unabhängig vom Wert der Hostvariable wird NULL in die betreffende Spalte eingefügt.
• >=0 : der Wert der Hostvariable wird in die Spalte eingefügt.

10.2 Embedded SQL 330

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

C URSORE
• Analog zu PL/SQL
• notwendig zur Verarbeitung einer Ergebnismenge, die mehr als ein Tupel enthält

Cursor-Operationen
• DECLARE ‹cursor-name› CURSOR FOR ‹sql statement›
• OPEN ‹cursor-name›
• FETCH ‹cursor-name› INTO ‹varlist›
• CLOSE ‹cursor-name›

Fehlersituationen
• der Cursor wurde nicht geöffnet bzw. nicht deklariert
• es wurden keine (weiteren) Daten gefunden
• der Cursor wurde geschlossen, aber noch nicht wieder geöffnet

Current of-Klausel analog zu PL/SQL

10.2 Embedded SQL 331

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

Beispiel
int main() {
EXEC SQL BEGIN DECLARE SECTION;
char cityName[50]; /* output host var */
int cityEinw; /* output host var */
char* landID = "D"; /* input host var */
short ind1, ind2; /* indicator vars */
char* uid = "/";
EXEC SQL END DECLARE SECTION;
EXEC SQL CONNECT :uid; /* Verbindung zur Datenbank herstellen */
EXEC SQL DECLARE StadtCursor CURSOR FOR /* Cursor deklarieren */
SELECT Name, Einwohner FROM Stadt WHERE Code = :landID;
EXEC SQL OPEN StadtCursor; /* Cursor oeffnen */
printf("Stadt Einwohner\n");
while (1)
{ EXEC SQL FETCH StadtCursor INTO :cityName:ind1 ,
:cityEinw INDICATOR :ind2;
if(ind1 != -1 && ind2 != -1)
{ printf("%s %d \n", cityName, cityEinw); }}; /* keine NULLwerte ausgeben *
EXEC SQL CLOSE StadtCursor; }

10.2 Embedded SQL 332

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

H OSTARRAYS
• sinnvoll, wenn die Größe der Antwortmenge bekannt ist oder nur ein bestimmter Teil
interessiert.
• vereinfacht Programmierung, da damit häufig auf einen Cursor verzichtet werden kann.
• verringert zudem Kommunikationsaufwand zwischen Client und Server.

EXEC SQL BEGIN DECLARE SECTION;

char cityName[50][20]; /* host array */
int cityPop[20]; /* host array */
EXEC SQL END DECLARE SECTION;
...
EXEC SQL SELECT Name, Population
INTO :cityName, :cityPop
FROM City
WHERE Code = ’D’;
holt 20 Tupel in die beiden Hostarrays.

10.2 Embedded SQL 333

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

PL/SQL IN E MBEDDED -A NWEISUNGEN

• Oracle Pro∗ C/C++ Precompiler unterstützt PL/SQL-Blöcke.
• PL/SQL-Block kann anstelle einer SQL-Anweisung verwendet werden.
• PL/SQL-Block verringt Kommunikationsaufwand zwischen Client und Server
• Übergabe in einem Rahmen:
EXEC SQL EXECUTE
DECLARE
...
BEGIN
...
END;
END-EXEC;

DYNAMISCHES SQL

SQL-Anweisungen können durch Stringoperationen zusammengestellt werden. Zur Übergabe

an die Datenbank dienen unterschiedliche Befehle, abhängig von den in der Anweisung
auftretenden Variablen.

10.2 Embedded SQL 334

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

T RANSAKTIONEN
• Anwendungsprogramm wird als geschlossene Transaktion behandelt, falls es nicht durch
COMMIT- oder ROLLBACK-Anweisungen unterteilt ist
• In Oracle wird nach Beendigung des Programms automatisch ein COMMIT ausgeführt
• DDL-Anweisungen generieren vor und nach ihrer Ausführung implizit ein COMMIT
• Verbindung zur Datenbank durch
EXEC SQL COMMIT RELEASE; oder
EXEC SQL ROLLBACK RELEASE;
beenden.
• Savepoints: EXEC SQL SAVEPOINT <name>

10.2 Embedded SQL 335

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

AUSNAHMEBEHANDLUNG : SQLCA (SQL C OMMUNICATIONS A REA )

Enthält Statusinformationen bzgl. der zuletzt ausgeführten SQL-Anweisung
struct sqlca {
char sqlcaid[8];
long sqlcabc;
long sqlcode;
struct { unsigned short sqlerrml;
char sqlerrmc[70];
} sqlerrm;
char sqlerrp[8];
long sqlerrd[6];
char sqlwarn[8];
char sqlext[8];
};
Interpretation der Komponente sqlcode:
• 0: die Verarbeitung einer Anweisung erfolgte ohne Probleme.
• >0: die Verarbeitung ist zwar erfolgt, dabei ist jedoch eine Warnung aufgetreten.
• <0: es trat ein ernsthafter Fehler auf und die Anweisung konnte nicht ausgeführt werden.
10.2 Embedded SQL 336
 Praktikum: Datenbankprogrammierung in SQL/ORACLE

WHENEVER-Statement
spezifiziert Aktionen die im Fehlerfall automatisch vom DBS ausgeführt werden sollen.
EXEC SQL WHENEVER ‹condition› ‹action›;
‹condition›
• SQLWARNING : die letzte Anweisung verursachte eine “no data found” verschiedene Warnung
(siehe auch sqlwarn). Dies entspricht sqlcode > 0 aber ungleich 1403.
• SQLERROR : die letzte Anweisung verursachte einen (ernsthaften) Fehler. Dies entspricht
sqlcode < 0.
• NOT FOUND : SELECT INTO bzw. FETCH liefern keine Antworttupel zurück. Dies entspricht
sqlcode 1403.
‹action›
• CONTINUE : das Programm fährt mit der nächsten Anweisung fort.
• DO flq proc_name› : Aufruf einer Prozedur (Fehlerroutine); DO break zum Abbruch einer
Schleife.
• GOTO ‹label› : Sprung zu dem angegebenen Label.
• STOP: das Programm wird ohne commit beendet (exit()), stattdessen wird ein rollback
ausgeführt.

10.2 Embedded SQL 337

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

Kapitel 11
Java und Datenbanken
• Java: plattformunabhängig
• überall, wo eine Java Virtual Machine (JVM) läuft, können Java-Programme ablaufen.
• APIs: Application Programming Interfaces; Sammlungen von Klassen und Schnittstellen,
die eine bestimmte Funktionalität bereitstellen.

Mehrere der bisher behandelten Aspekte können mit Java gekoppelt werden:

• Prozeduren und Funktionen, Member Methods: Java Stored Procedures (Folie 341),
• Objekttypen: Java Object Types (Folie 347)
(so kann man beliebige Datenstrukturen implementieren und anbieten → XML),
• Low-Level-Infrastruktur für Datenbankzugriff aus Java: JDBC (Folie 354),
• Embedded SQL (intern basierend auf JDBC): SQLJ (Folie 401).

11.0 Java und Datenbanken 338

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

S TANDARDISIERUNG
• JDBC ist ein Java-API, das (DB-produktunabhängigen) low-level-Datenbankzugriff aus
Java erlaubt.
• SQLJ: ANSI-Standard als Teil des SQL-Standards, bestehend aus drei Teilen:
– Part 0: Embedded SQL in Java (ANSI X3.135.10-1998, bzw ISO-Standard “Database
Language SQL:1999 – Part 10: Object Language Bindings (SQL/OLB)”; siehe Abschnitt
“SQLJ”)
– Part 1: SQL routines using Java (ANSI NCITS 331.1-1999, siehe Abschnitt “Java in
Stored Procedures”).
– Part 2: SQL types using Java (ANSI NCITS 331.2-2000, siehe Abschnitt “Java in
SQL-Objekttypen”, u.a. → XMLType).
– Part 1 und 2 bilden zusammen Part 13 des SQL:1999-Standards (ISO/IEC
9075-13:2002) “SQL Routines and Types Using the Java Programming Language
(SQL/JRT)”

11.0 Java und Datenbanken 339

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

Technische Hinweise/Settings im IFI [Juli 2018]

• benötigte jars (in CLASSPATH reinnehmen):

/afs/[Link]/group/dbis/public/oracle/instantclient :
[Link], [[Link]], [[Link]]
/afs/[Link]/group/dbis/public/oracle/lib :
[Link], [[Link] - Konflikt mit XML-P-jars]
/afs/[Link]/group/dbis/public/oracle/sqlj/lib :
[Link], [Link]
• Unix settings für Oracle (incl. CLASSPATH):
/afs/[Link]/group/dbis/public/oracle/.oracle_env
• Wenn JDBC-Verbindungsaufbau zu lange dauert:
java -[Link]=[Link] classfilename
(JDBC-Verbindungen sind verschlüsselt. Normales /random basiert auf externen
Ereignissen (Maus, Portanfragen), die an Poolrechnern nicht genügend vorliegen).

11.0 Java und Datenbanken 340

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

11.1 Java in Stored Procedures und Member Methods

• Oracle hat (seit 8i/8.1.5; Feb. 1999) eine eigene, integrierte JVM
– keine GUI-Funktionalität
– Java-Entwicklung außerhalb des DB-Servers
– keine main()-Methode in Klassen, nur statische Methoden (= Klassenmethoden)
– ab 9i Nutzung von Klassen als Objekttypen
– kein Multithreading
– DB-Zugriff über JDBC/SQLJ, dabei wird der serverseitige JDBC-Treiber verwendet
(siehe Folien 354 und 401).
• Quelldateien (.java), Klassendateien (.class) oder Archive (.jar) können eingelesen
werden.
• Shell: loadjava, dropjava
• DB: CREATE JAVA SOURCE, DROP JAVA SOURCE, DROP JAVA CLASS
• select object_type, object_name from user_objects
where object_type like ’%JAVA%’ order by 1;
• Einbettung in Prozedur/Funktion (Wrapper, call spec) (void-Methoden als Prozeduren,
non-void als Funktionen)
11.1 Java und Datenbanken 341
 Praktikum: Datenbankprogrammierung in SQL/ORACLE

Laden von Java-Code per Shell

Außerhalb der DB wird eine Klasse geschrieben:

public class Greet
{ public static String sayHello (String name)
{ [Link]("This is Java"); // Java output
return "Hello " + name + "!"; // return value
} } [Filename: Java/[Link]]
• Einlesen in die Datenbank mit loadjava.
Empfehlenswert ist hier ein Alias um user/passwd nicht jedesmal angeben zu müssen:
alias loadjava=’loadjava -thin -u uname/passwd@dbis’
dbis@s042> loadjava -r [Link]
• -r: wird sofort compiliert und Referenzen aufgelöst (sonst: on-demand zur Laufzeit)
• Einlesen von .class-Dateien (ohne -r) – die Datenbank muss dieselbe Java-Version, wie auf
dem Rechner wo man es compiliert hat, verwenden:
dbis@s042> loadjava [Link]
• analog mit .jar (das Sourcen und/oder class-Files enthält)
• Löschen einer Java-Klasse: analog mit dropjava Greet (auch hier alias definieren)

11.1 Java und Datenbanken 342

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

Erzeugen von Java-Klassen in SQL

Klasse ausserhalb der DB entwickeln und dann in der DB generieren:

CREATE OR REPLACE JAVA SOURCE NAMED "Hello"
AS
// here also imports are allowed
public class Greet
{ public static String sayHello (String name)
{ [Link]("This is Java"); // Java output
return "Hello " + name + "!"; }}; // return-value
/ [Filename: Java/[Link]]
• Wichtig: Name der Klasse in doppelte Anführungszeichen (case sensitive)
• mit dem als nächstes beschriebenen Wrapper eine PL/SQL-Prozedur daraus erzeugen,
• Löschen mit
DROP JAVA SOURCE "Hello";
• Analog: Klassen als Binaries laden:
CREATE OR REPLACE JAVA CLASS USING BFILE(directory_object, filename );
CREATE OR REPLACE JAVA CLASS USING {BLOB|CLOB|BFILE} subquery ;

11.1 Java und Datenbanken 343

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

Einbinden des Java-Codes in PL/SQL-Funktion/Prozedur

Innerhalb der Datenbank:
• Funktion als Wrapper (call spec):
CREATE OR REPLACE FUNCTION greet (person IN VARCHAR2)
RETURN VARCHAR2 AS
LANGUAGE JAVA
NAME ’[Link] ([Link])
return [Link]’;
/ [Filename: Java/[Link]]
• Bei void-Methoden: Prozedur als Wrapper
• Aufruf: SELECT greet(’Jim’) FROM DUAL;
GREET(’JIM’)
Hello Jim!
• Für die Java-Ausgabe muss man sowohl das Java-Output-Buffering als auch den
SQL-Output aktivieren:
CALL dbms_java.set_output(2000);
SET SERVEROUTPUT ON;
Beispiel: SELECT greet(name) FROM COUNTRY;

11.1 Java und Datenbanken 344

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

Syntax des Prozedur/Funktions-Wrappers

CREATE [OR REPLACE]
{ PROCEDURE ‹proc_name›[(‹parameter-list›)]
| FUNCTION ‹func_name›[(‹parameter-list›)] RETURN sql_type}
{IS | AS} LANGUAGE JAVA
NAME ’‹java_method_name›[(‹java-parameter-list›)]
[return ‹java_type_fullname›]’;
/
• Bei void-Methoden: Prozeduren,
• Bei non-void-Methoden: Funktionen,
• Die ‹parameter-list› muss der ‹java-parameter-list› entsprechen:
– gleiche Länge,
– sich entsprechende Parameter-Typen; Parameter-Typ-Mapping: siehe JDBC
• Achtung: In der NAME-Spezifikation muss return klein geschrieben werden,
• Aufruf des Wrappers eingebettet aus SQL und PL/SQL in Anfragen, DML-Operationen,
Prozeduren, Triggern, ...
Soweit ist noch kein Datenbank-Zugriff aus den Methoden möglich. Dies wird durch JDBC
ermöglicht (siehe Folie 354).
11.1 Java und Datenbanken 345
 Praktikum: Datenbankprogrammierung in SQL/ORACLE

Nullwerte an Java übergeben

• wenn das Argument NULL ist, ist es in Java null,

• return null wird als SQL NULL-Wert interpretiert.
CREATE OR REPLACE JAVA SOURCE NAMED "Hello"
AS
public class Greet
{ public static String sayHello (String name)
{ [Link]("This is Java");
if (name != null)
return "Hello " + name + "!";
else return null;
} };
/
[Filename: Java/[Link]]
• wie vorher per Wrapper eine PL/SQL-Prozedur daraus erzeugen,
• SELECT greet(NULL) FROM DUAL;
• Anmerkung: in Oracle problemlos, in DB2 muss CREATE PROCEDURE mit GENERAL
WITH NULLS bzw. SIMPLE WITH NULLS spezifiziert werden (→ Doku)

11.1 Java und Datenbanken 346

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

11.2 Java in SQL-Objekttypen

Man SQL-Typen auf Basis von Java-Klassen definieren. Die Java-Klasse muss das Interface
[Link] sowie ggf. weitere anwendungsspezifische Methoden implementieren:
• public String getSQLTypeName()
liefert den entsprechenden SQL-Datentyp zurück
“This method is called by the JDBC driver to get the name of the UDT [user-defined
datatype] instance that is being mapped to this instance of SQLData.”
• public void readSQL(SQLInput stream,
String typeName) throws SQLException
liest Daten aus der Datenbank und initialisiert das Java-Objekt
• public void writeSQL(SQLOutput stream)
bildet das Java-Objekt auf die Datenbank ab.
(vgl. Marshalling/Unmarshalling zwischen XML und Java in JAXB)
Diese drei Methoden werden nachher nicht vom Benutzer, sondern intern bei der Umsetzung
aufgerufen.

11.2 Java und Datenbanken 347

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

B EISPIEL : J AVA -K LASSE GeoCoordJ

• Die Java-Klasse kann einen beliebigen Namen haben
• sie muss/soll die folgenden Methoden besitzen (siehe nächste Folie):
– getSQLTypeName MYGEOCOORD: Name des SQL-Typs in Oracle
– die Felddeklarationen (Namen können anders als in SQL sein)
– Felder lesen/setzen in der Reihenfolge der SQL-Definition
– Lese-/Schreibmethoden: [Link]/write‹type›
– Wenn die Klasse ausserdem in Java (mit JDBC) verwendet werden soll, ist ein
Konstruktor und ein toString() sinnvoll.
– die anwendungsspezifischen Methoden, die in Anfragen benutzt werden sollen
(Namen können anders als in SQL sein).
• Klasse GeoCoordJ in Oracle laden :
dbis@s042> loadjava -r [Link]
• jetzt hat man eine Klasse, die man zur Definition eines SQL-Typs verwenden kann.

11.2 Java und Datenbanken 348

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

Beispiel: Java-Klasse GeoCoordJ

import [Link].*;

public class GeoCoordJ implements [Link] {

private double lat, lon;

public String getSQLTypeName() {

return "MYGEOCOORD"; // just to illustrate something
}
public void readSQL(SQLInput stream, String typeName)
throws SQLException {
lat = [Link]();
lon = [Link]();
}
public void writeSQL(SQLOutput stream)
throws SQLException {
[Link](lat);
[Link](lon);
}
// ... continue next slide

11.2 Java und Datenbanken 349

 Praktikum: Datenbankprogrammierung in SQL/ORACLE
// theconstructor is not needed in the database
// butwe need it for "outside" with JDBC
publicGeoCoordJ(double la, double lo) {
lat= la; lon = lo;
}
// then, the standard constructor must be explicitly
// defined because it is needed in the database
public GeoCoordJ() {
lat = 0; lon = 0;
}
// used when printing e.g. in JDBC
public String toString() {
return "GeoCoord(" + lat + "/" + lon + ")";
}
public double distance(GeoCoordJ other) {
return
6370 * [Link](
[Link]([Link]/180*3.14) *
[Link]([Link]/180*3.14) *
[Link]( ([Link] - [Link])
/180*3.14 ) +
[Link]([Link]/180*3.14) *
[Link]([Link]/180*3.14) );
} } [Filename: Java/[Link]]
11.2 Java und Datenbanken 350
 Praktikum: Datenbankprogrammierung in SQL/ORACLE

SQL-W RAPPER -T YPE

• Namen des Typs, der Attribute/Spalten und der Methoden müssen nicht mit der
verwendeten Java-Klasse übereinstimmen (EXTERNAL NAME ...):
CREATE OR REPLACE TYPE jgeocoord -- Typname, der in SQL verwendet wird
AS OBJECT
EXTERNAL NAME ’GeoCoordJ ’ -- Name der Java-Klasse, die verwendet wird
LANGUAGE JAVA
USING SQLData
( latitude NUMBER EXTERNAL NAME ’lat’, -- Mapping der Attributnamen SQL<->Java
longitude NUMBER EXTERNAL NAME ’lon’,
MEMBER FUNCTION distance (other IN jgeocoord) RETURN NUMBER -- Signatur SQL
EXTERNAL NAME ’distance (GeoCoordJ) return double’); -- Signatur in Java
/
CREATE TABLE jcoordtable OF jgeocoord;
INSERT INTO jcoordtable VALUES (jgeocoord(50,0)); LONGITUDE [Link](...)
INSERT INTO jcoordtable VALUES (jgeocoord(-34,151)); 0 166.681667
151 16977.5482
SELECT [Link], [Link](jgeocoord(51.5,0))
[Filename: Java/[Link]]
FROM jcoordtable x;
11.2 Java und Datenbanken 351
 Praktikum: Datenbankprogrammierung in SQL/ORACLE

Java-Objekte als Zeilenobjekte

• Signatur wie im CREATE TYPE vorgegeben.

Diese wird wie optional mit den EXTERNAL NAME-Spezifikationen angegeben auf die
Java-Klasse abgebildet.
• Tabelle sieht aus wie eine normale Tupel-/Objekttabelle:
DESC jcoordtable;
Name Null? Type
LATITUDE NUMBER
LONGITUDE NUMBER
• Einträge sehen aus wie ganz normale Zeilenobjekte:
SELECT * FROM jcoordtable;
Longitude Latitude
50 0
-34 151

11.2 Java und Datenbanken 352

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

Java-Objekte als Spaltenobjekte

• duplicate the “Mountain” table with Java coordinates:

recall: the name of the SQL type is ’jgeocoord’
CREATE TABLE mountainJcoord
AS (SELECT name, mountains, elevation, type,
jgeocoord([Link],
[Link]) as coords
FROM mountain m);

SELECT name, coords,

[Link](jgeocoord(51.5,0))
FROM mountainJcoord m
ORDER BY 3 DESC;
[Filename: Java/[Link]]
• select * from mountainjcoord :
Einträge sind Spaltenobjekte, die z.B. als JGEOCOORD(15.14, 120.3)
(d.h., der Typname der im CREATE TYPE genannt ist)
ausgegeben werden.

11.2 Java und Datenbanken 353

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

11.3 JDBC (Java Database Connectivity): API für

Low-Level-Datenbankzugriff
• Interface für den (entfernten) Datenbankzugriff von Java-Programmen aus,
• Teil des SDK ([Link].*),
• Applikation kann unabhängig vom darunterliegenden DBMS programmiert werden,
• setzt die Idee von ODBC (Open DataBase Connectivity; ein 1992 entwickelter Standard
zum Zugriff auf Datenbanken aus Programmiersprachen) auf Java um,
• gemeinsame Grundlage ist der X/Open SQL CLI (Call Level Interface) Standard.

11.3 Java und Datenbanken 354

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

JDBC-A RCHITEKTUR

DB, die per

JDBC-ODBC-
ODBC-Treiber
Treiber
zugänglich ist

JDBC-Treiber-
Java-Programm Oracle-Treiber Oracle-DB
Manager

.. ..
. .

MySQL-Treiber MySQL-DB

11.3 Java und Datenbanken 355

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

JDBC-A RCHITEKTUR
• Kern: Treiber-Manager ([Link])
• darunter: Treiber für einzelne DBMS’e

JDBC-API
• flexibel:
– Applikation kann unabhängig vom darunterliegenden DBMS programmiert werden
• “low-level”:
– Statements werden durch Strings übertragen
– im Gegensatz zu SQLJ (später) keine Verwendung von Programmvariablen in den
SQL-Befehlen
(d.h. Werte müssen explizit eingesetzt werden)
Darauf aufbauend:
• Embedded SQL für Java (SQLJ)
• direkte Darstellung von Tabellen und Tupeln in Form von Java-Klassen

11.3 Java und Datenbanken 356

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

JDBC-F UNKTIONALITÄT
• Aufbau einer Verbindung zur Datenbank (DriverManager, Connection)
• Versenden von SQL-Anweisungen an die Datenbank (Statement, PreparedStatement und
CallableStatement)
• Verarbeitung der Ergebnismenge (ResultSet)

11.3 Java und Datenbanken 357

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

JDBC-T REIBER -M ANAGER

[Link]
• verwaltet (registriert) Treiber
• wählt bei Verbindungswunsch den passenden Treiber aus und stellt Verbindung zur
Datenbank her.

• Es wird nur ein DriverManager benötigt.

⇒ Klasse DriverManager:
– nur static Methoden (operieren auf Klasse)
– Konstruktor ist private (keine Instanzen erzeugbar)
Benötigte Treiber müssen angemeldet werden:
[Link](driver*)
Im Praktikum für den Oracle-Treiber:
[Link]
(new [Link]());
erzeugt eine neue Oracle-Treiber-Instanz und “gibt” sie dem DriverManager.

11.3 Java und Datenbanken 358

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

V ERBINDUNGSAUFBAU
• DriverManager erzeugt offene Verbindungs-Instanz:
Connection conn = [Link](‹jdbc-url›, ‹user-id›, ‹passwd›);
oder
Connection conn = [Link](‹jdbc-url›, ‹props-filename›);
(Login-Daten aus Datei, via [Link]).
• Datenbank wird eindeutig durch die JDBC-URL bezeichnet:
– jdbc:<subprotocol>:<subname>
– <subprotocol>: Name des produktspezifischen Treiber-Protokolls
– <subname> ist produktspezifisch zusammengesetzt
Bei uns, mit einer Oracle-Datenbank:
jdbc:oracle:‹driver-name›:@//‹IP-Address DB Server›:‹Port›/‹Service›
String url =
’jdbc:oracle:thin:@//[Link]/[Link]’;
(die aktuelle URL steht hoffentlich auf dem Aufgabenblatt)
• Verbindung beenden: [Link]();

11.3 Java und Datenbanken 359

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

V ERSENDEN VON SQL-A NWEISUNGEN

Statement-Objekte
• werden durch Aufruf von Methoden einer bestehenden Verbindung ‹connection› erzeugt.
• Statement: einfache SQL-Anweisungen ohne Parameter
• PreparedStatement: Vorcompilierte Anfragen, Anfragen mit Parametern
• CallableStatement: Aufruf von gespeicherten Prozeduren

11.3 Java und Datenbanken 360

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

K LASSE “S TATEMENT ”
Statement ‹name› = ‹connection›.createStatement();
Sei ‹string› ein SQL-Statement ohne Semikolon.
• ResultSet ‹statement›.executeQuery(‹string›):
SQL-Anfragen an die Datenbank. Dabei wird eine Ergebnismenge zurückgegeben.
• int ‹statement›.executeUpdate(‹string›): SQL-Statements, die eine Veränderung an
der Datenbasis vornehmen (einschliesslich DDL-Statements). Der Rückgabewert gibt an,
wieviele Tupel von der SQL-Anweisung betroffen waren.
• boolean ‹statement›.execute(‹string›) – Sonstiges:
– Aufrufe von Prozeduren/Funktionen (siehe CallableStatements),
– Oracle: PL/SQL-Blocks sind auch erlaubt (siehe Folie 392),
– Statement dynamisch als String erzeugt, und man weiß nicht, ob es eine Query oder ein
Update ist,
– “true” wenn das (erste) Ergebnis ein ResultSet ist; “false” sonst (siehe Folie 389).
Ein Statement-Objekt kann beliebig oft wiederverwendet werden, um SQL-Anweisungen zu
übermitteln.
Mit der Methode close() kann ein Statement-Objekt geschlossen werden.

11.3 Java und Datenbanken 361

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

B EHANDLUNG VON E RGEBNISMENGEN

Klasse “ResultSet” (Iterator-Pattern):
ResultSet ‹name› = ‹statement›.executeQuery(‹string›);
• virtuelle Tabelle, auf die von der “Hostsprache” – hier also Java – zugegriffen werden kann.
• ResultSet-Objekt unterhält einen Cursor, der mit
‹result-set›.next();
auf das nächste (bzw. am Anfang auf das erste) Tupel gesetzt wird.
• ‹result-set›.next()
liefert den Wert false wenn alle Tupel gelesen wurden.
Name code Population
ResultSet countries =
[Link]("SELECT Name, Code, Population Germany D 83536115
FROM Country"); Sweden S 8900954
Canada CDN 28820671
Poland PL 38642565
Bolivia BOL 7165257
.. .. ..

11.3 Java und Datenbanken 362

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

Behandlung von Ergebnismengen

• Zugriff auf die einzelnen Spalten des Tupels unter dem Cursor mit
‹result-set›.get‹type›(‹attribute›)
• ‹type› ist dabei ein Java-Datentyp,
SQL-Typ get-Methode
INTEGER getInt
REAL, FLOAT getFloat
BIT getBoolean
CHAR, VARCHAR getString
– getString funktioniert immer (type casting).
– getObject funktioniert immer, dann mit instanceof prüfen und casten.
• ‹attribute› kann entweder durch Attributnamen oder durch die Spaltennummer gegeben
sein.
[Link]("Code");
[Link]("Population");
[Link](3);
• Bei get‹type› werden die Daten des Ergebnistupels (SQL-Datentypen) in Java-Typen
konvertiert.
11.3 Java und Datenbanken 363
 Praktikum: Datenbankprogrammierung in SQL/ORACLE

Beispiel-Code

import [Link].*;
class JdbcCities {
public static void main (String args []) throws SQLException
{ // Oracle-Treiber laden
[Link] (new [Link]());
// Verbindung zur Datenbank herstellen
String url = "jdbc:oracle:thin://@[Link]/[Link]";
Connection conn = [Link](url,"scott","tiger");
// Anfrage an die Datenbank
Statement stmt = [Link]();
ResultSet rset = [Link]("SELECT Name, Population FROM City");
while ([Link]()) { // Ergebnis verarbeiten
String s = [Link](1);
int i = [Link]("Population");
[Link] (s + " " + i + "\n");
}
[Link](); [Link](); [Link]();
}}

11.3 Java und Datenbanken 364

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

Verbindungsaufbau zu Oracle mit Properties-File

• [Link] (Dateiname ist frei wählbar) ist eine Datei (bzw. Datei im .jar-Archiv), die
folgendermassen aussieht:
#[Link]:
## fuer normales JDBC
url=jdbc:oracle:thin:@//[Link]/[Link]
user=scott
password=tiger
### fuer SQLJ dasselbe nochmal
[Link]=jdbc:oracle:thin:@//[Link]/[Link]
[Link]=scott
[Link]=tiger
[Filename: Java/[Link] – muss jeder selber schreiben]
• Eine Datei dieser Form kann man in eine Instanz von [Link] einlesen (ein
key-Value-Hash) und dann mit getProperty() oder automatisch abfragen.
• [Link] ggf. mit ins .jar legen.

11.3 Java und Datenbanken 365

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

Verbindungsaufbau mit Properties-File

import [Link].*;
import [Link];
import [Link];
class JdbcCities {
public static void main (String args []) throws Exception // File oder SQL Exc
{ // Oracle-Treiber laden
[Link] (new [Link]());
// Verbindung zur Datenbank herstellen
Properties props = new Properties();
[Link](new FileInputStream("[Link]"));
Connection conn = [Link]([Link]("url"), props);
// Anfrage an die Datenbank
Statement stmt = [Link]();
ResultSet rset = [Link]("SELECT Name, Population FROM City");
while ([Link]()) { // Verarbeitung der Ergebnismenge
String s = [Link](1);
int i = [Link]("Population");
[Link] (s + " " + i); }
[Link](); [Link](); [Link](); }} [Filename: Java/[Link]]

11.3 Java und Datenbanken 366

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

Behandlung von Ergebnismengen: JDBC-Datentypen

• JDBC steht zwischen Java (Objekttypen) und SQL (Typen mit unterschiedlichen Namen).
• [Link] definiert generische SQL-Typen, mit denen JDBC arbeitet:
(oft Subklassen der Java-Typen)

Java-Typ JDBC-SQL-Typ in [Link]

[Link] CHAR, VARCHAR
[Link] NUMBER, NUMERIC, DECIMAL
boolean BIT
byte TINYINT
short SMALLINT
int INTEGER
long BIGINT
float REAL
double FLOAT, DOUBLE
convert to [Link].* TIMESTAMP (DATE, TIME)
Diese werden auch verwendet, um Meta-Daten zu verarbeiten.

11.3 Java und Datenbanken 367

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

B EHANDLUNG VON E RGEBNISMENGEN

Im Fall von allgemeinen Anfragen weiß man oft nicht, wieviele Spalten eine Ergebnismenge
hat, wie sie heißen, und welche Typen sie haben.
Instanz der Klasse ResultSetMetaData enthält Metadaten über das vorliegende ResultSet:
ResultSetMetaData ‹name› = ‹result-set›.getMetaData();
erzeugt ein ResultSetMetaData-Objekt, das Informationen über die Ergebnismenge enthält:
• int getColumnCount():
Spaltenanzahl der Ergebnismenge
• String getColumnLabel(int):
Attributname der Spalte ‹int›
• String getTableName(int):
Tabellenname der Spalte ‹int›
• int getColumnType(int):
JDBC-Typ der Spalte ‹int›
(ein SQL Type aus der Enumeration [Link]; 2002 ist [Link])
• String getColumnTypeName(int):
Unterliegender DBMS-Typ der Spalte ‹int›

11.3 Java und Datenbanken 368

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

B EHANDLUNG VON E RGEBNISMENGEN

• keine NULL-Werte in Java:
‹resultSet›.wasNull()
testet, ob der zuletzt gelesene Spaltenwert NULL war.
Beispiel: Ausgabe aller Zeilen eines ResultSets

ResultSetMetaData rsetmetadata = [Link]();

int numCols = [Link]();
while ([Link]()) {
for(int i=1; i<=numCols; i++) {
String returnValue = [Link](i);
if ([Link]())
[Link] ("null");
else
[Link] (returnValue);
}
}
• Mit der Methode close() kann ein ResultSet-Objekt explizit geschlossen werden.

11.3 Java und Datenbanken 369

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

Beispiel: Auslesen einer beliebigen Tabelle

import [Link].*;
import [Link];
import [Link];
class JdbcSelect {
public static void main (String args []) throws Exception {
[Link](new [Link]());
Properties props = new Properties();
[Link](new FileInputStream("[Link]"));
Connection conn = [Link]([Link]("url"), props);
Statement stmt = [Link]();
ResultSet rset = [Link]("SELECT * FROM " + args[0]);
ResultSetMetaData rsetmetadata = [Link]();
int numCols = [Link]();
while ([Link]()) {
for(int i=1; i<=numCols; i++) {
String value = [Link](i);
if ([Link]()) [Link]("null");
else [Link](value);
[Link](" ");
}
[Link]();
} dbis@c42> java JdbcSelect City
[Link](); [Link](); [Link]();
}} [Filename: Java/[Link]]

11.3 Java und Datenbanken 370

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

Auslesen von einfachen Zeilenobjekten

Zeilenobjekte entsprechen Tupeln über atomaren Datentypen (vgl. Folie 352):

dbis@c42> java JdbcSelect jcoordtable

Auslesen von Objekten: benutzerdefinierte SQL-Typen

• Für Instanzen von Spaltenobjekten wird “null” ausgegeben, oder man erhält sogar einen
Fehler:
dbis@c42> java jdbcSelect mountain
• Objekte mit getObject(n) auslesen
(behandelt auch Strings, Zahlen etc. korrekt)
• Instanzen von mit PL/SQL benutzerdefinierten SQL-Typen haben als SQL-Typ (aus den
Metadaten mit [Link](i)) [Link] und
implementieren das Interface [Link].
• Objekttyp-Name (aus der Instanz): String name = [Link]()
• attribute: Object[ ] attrs = [Link]()
enthält dann Strings, Zahlwerte, oder wieder Objekte

11.3 Java und Datenbanken 371

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

Beispiel: Auslesen von SQL-Objekten – Metadaten

• erster Teil: Metadaten der Spalten aus dem ResultSetMetaData:
– JDBC-Typen aus der Enum [Link]
z.B. 12=VARCHAR, 2=NUMERIC, 2002=STRUCT, (Oracle: 2008 = Java-definiert)
– Typnamen im DBMS
z.B. das PL/SQL-definierte [Link] oder das Java-definierte
[Link]
import [Link].*;
import [Link];
import [Link];
class JdbcSelectObj {
public static void main (String args []) throws Exception {
Connection conn = getConn(); // unten rausgescrollt
Statement stmt = [Link]();
ResultSet rset = [Link]("SELECT * FROM " + args[0]);
ResultSetMetaData rsmd = [Link]();
int numCols = [Link]();
for(int i=1; i<=numCols; i++) [Link]([Link](i) + " ");
[Link]();
for(int i=1; i<=numCols; i++) [Link]([Link](i) + " ");
[Link]();
11.3 // continue next slide Java und Datenbanken 372
 Praktikum: Datenbankprogrammierung in SQL/ORACLE
while ([Link]()) {
for(int i=1; i<=numCols; i++) {
Object value = [Link](i);
// [Link]([Link](i));
if ([Link]()) [Link]("null ");
else if ([Link](i) == [Link])
{ [Link] s = ([Link])value;
[Link]([Link]() + "( ");
Object[] attrs = [Link]();
// attributes
for (int j = 0; j < [Link]; j++) [Link](attrs[j] + " ");
[Link](")");
}
// This also covers Java instances (uses their toString())
else [Link](value + " ");
}
[Link](); }
[Link](); [Link](); [Link](); }
private static Connection getConn() throws Exception {
[Link](new [Link]());
Properties props = new Properties();
[Link](new FileInputStream("[Link]"));
Connection conn = [Link]([Link]("url"), props);
return conn; }} [Filename: Java/[Link]]
11.3 Java und Datenbanken 373
 Praktikum: Datenbankprogrammierung in SQL/ORACLE

Auslesen von Objekten von PL/SQL-Typen

• gibt die Instanzen von via PL/SQL definierten Objekttypen generisch als STRUCTS,
Kontruktoraufruf und Parameter (ohne Komma zwischen den Parametern) aus:
dbis@c42> java jdbcSelectObj mountain
Ausgabe der Koordinaten z.B. als [Link](19.2 -98.6) .

11.3 Java und Datenbanken 374

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

Auslesen von Objekten: Instanzen von Java-Klassen

Für Instanzen von Java-basierten SQL-Typen (jgeocoord) in Spaltenobjekten (d.h. nicht für die
Zeilenobjekte von Folie 352, die wie Tupel behandelt werden) wird eine Instanz der
entsprechenden Java-Klasse erzeugt:
• (Folie 351): SQL-Typ “jgeocoord” benutzt die Java-Klasse “GeoCoordJ”.
• [Link]/.class liegt zugreifbar im “Java/”-Subdirectory.
• sonst: [Link]: ... ClassNotFoundException: GeoCoordJ
⇒ Bei der Konvertierung des SQL-Typs aus dem JDBC-ResultSet ist der (abweichende!)
Name der Java-Klasse bekannt, der Java-Code aber nicht (würde evtl. auch auf der JVM
des Clients garnicht laufen).
• Wenn man die “Original-Klasse”, die in der DB verwendet wird, nicht hat, kann man dort
irgendeine Klasse hinlegen, die den entsprechenden Namen hat und die Methoden des
Interfaces [Link] und diejenigen Methoden, die man tatsächlich benutzen
möchte (auch das können mehr sein, als im Original), implementiert.
• java JdbcSelectObj mountainJCoord
ruft generisch im normalen print-Zweig deren toString() auf.
⇒ kein cast notwendig, da für den Compiler toString() bei Object definiert ist, und der Code
zur Laufzeit dynamisch korrekt gebunden wird.

11.3 Java und Datenbanken 375

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

Auslesen und Verwenden von Java-Objekten

• Wenn man Methoden/Felder einer Instanz einer Java-Klasse verwenden will, die bei Object
nicht automatisch definiert sind, muss man entsprechend testen (Java-Typen haben als
[Link] bei Oracle die Konstante 2008) und explizit casten.

import [Link].*;
import [Link];
import [Link];
class JdbcSelectObjCast {
public static void main (String args []) throws Exception {
[Link](new [Link]());
Properties props = new Properties();
[Link](new FileInputStream("[Link]"));
Connection conn = [Link]([Link]("url"), props);
Statement stmt = [Link]();
ResultSet rset = [Link]("SELECT * FROM " + args[0]);
ResultSetMetaData rsmd = [Link]();
int numCols = [Link]();

// continue next slide

11.3 Java und Datenbanken 376
 while ([Link]()) {
Praktikum: Datenbankprogrammierung in SQL/ORACLE
for(int i=1; i<=numCols; i++) {
Object value = [Link](i);
// [Link]([Link](i));
if ([Link]()) [Link]("null");
else if ([Link](i) == 2008 &&
(([Link])value).getSQLTypeName() == "MYGEOCOORD") // <<<< test
{ GeoCoordJ gc = (GeoCoordJ)value; // <<<< cast
[Link](gc + " ");
[Link]([Link](new GeoCoordJ(0,0))); // <<<< call
}
else if ([Link](i) == 2008 && value instanceof GeoCoordJ) // <<<< test
{ GeoCoordJ gc = (GeoCoordJ)value; // <<<< cast
[Link](gc + " ");
[Link]([Link](new GeoCoordJ(0,0))); // <<<< call
}
else if ([Link](i) == [Link])
{ [Link]((([Link])value).getSQLTypeName() + "( ");
Object[] attrs = (([Link])value).getAttributes();
for (int j = 0; j < [Link]; j++) [Link](attrs[j] + " ");
[Link](")");
}
else [Link](value + " "); // also covers Java instances (uses toString())
}
[Link](); }

Java und Datenbanken [Filename: Java/[Link]]

[Link](); [Link](); [Link](); }}
11.3 377
 Praktikum: Datenbankprogrammierung in SQL/ORACLE

P REPARED S TATEMENTS
PreparedStatement ‹name› =
‹connection›.prepareStatement(‹string›);
• SQL-Anweisung ‹string› wird vorcompiliert.
• damit ist die Anweisung fest im Objektzustand enthalten
• effizienter als Statement, wenn ein SQL-Statement häufig (mit ggf. verschiedenen
Parametern) ausgeführt werden soll.
• Abhängig von ‹string› ist eine der (parameterlosen!) Methoden
– ‹prepared-statement›.executeQuery() → ResultSet
oder
– ‹prepared-statement›.executeUpdate() → boolean
anzuwenden.
Wenn man (String automatisch erstellt) das nicht weiss, kann man execute() anwenden
(siehe Folie 389).

11.3 Java und Datenbanken 378

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

Prepared Statements: Parameter

• Eingabeparameter werden durch “?” repräsentiert
PreparedStatement giveCountryPop =
[Link]("SELECT Population FROM Country WHERE Code = ?");
• “?”-Parameter werden mit
‹prepared-statement›.set‹type›(‹pos›,‹value›);
gesetzt, bevor ein PreparedStatement ausgeführt wird.
• ‹type›: Java-Datentyp,
• ‹pos›: Position des zu setzenden Parameters,
• ‹value›: Wert.

Beispielsequenz:
[Link](1,"D");
ResultSet rset = [Link]();
if ([Link]()) [Link]([Link](1));
[Link](1,"CH");
ResultSet rset = [Link]();
if ([Link]()) [Link]([Link](1));

11.3 Java und Datenbanken 379

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

PreparedStatement (Cont’d)

• Nullwerte werden gesetzt durch

setNULL(‹pos›,‹sqlType›);
‹sqlType› bezeichnet den JDBC-Typ dieser Spalte.
• nicht sinnvoll in Anfragen (Abfrage nicht mit “= NULL” sondern mit “IS NULL”), sondern z.B.
bei INSERT-Statements oder Prozeduraufrufen etc.

11.3 Java und Datenbanken 380

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

Beispiel: PreparedStatement
import [Link].*;
import [Link];
import [Link];
class JdbcCountryPop {
public static void main (String args []) throws Exception {
[Link](new [Link]());
Properties props = new Properties();
[Link](new FileInputStream("[Link]"));
Connection conn = [Link]([Link]("url"), props);
PreparedStatement giveCountryPop =
[Link]("SELECT Population FROM Country WHERE Code = ?");
[Link](1,args[0]);
ResultSet rset = [Link]();
if([Link]()) {
dbis@c42> java JdbcCountryPop D
int pop = [Link](1);
dbis@c42> java JdbcCountryPop X
if ([Link]()) [Link]("null");
else [Link](pop); }
else [Link]("No existing country code");
[Link]();
}} [Filename: Java/[Link]]

11.3 Java und Datenbanken 381

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

E RZEUGEN VON F UNKTIONEN , P ROZEDUREN ETC .

• Erzeugen von Prozeduren und Funktionen mit
‹statement›.executeUpdate(‹string›);
(‹string› von der Form CREATE PROCEDURE/FUNCTION ...)
s = ’CREATE PROCEDURE bla() IS BEGIN ... END’;
[Link](s);

C ALLABLE S TATEMENTS : G ESPEICHERTE P ROZEDUREN

Der Aufruf einer Prozedur oder Funktion wird als CallableStatement-Objekt erzeugt:
• Aufrufsyntax von Prozeduren bei den verschiedenen Datenbanksystemen unterschiedlich
⇒ JDBC verwendet eine generische Syntax per Escape-Sequenz (Umsetzung dann durch
Treiber)
CallableStatement ‹name› =
‹connection›.prepareCall("{call ‹procedure/function pattern›}");
CallableStatement cstmt =
[Link]("{call bla()}");

11.3 Java und Datenbanken 382

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

C ALLABLE S TATEMENTS MIT PARAMETERN

s = ’CREATE FUNCTION distance(city1 IN Name, city2 IN Name)
RETURN NUMBER IS BEGIN ... END’;
[Link](s);
• Parameter:
CallableStatement ‹name› =
‹connection›.prepareCall("{call ‹procedurename›(?,...,?)}");
• Rückgabewert bei Funktionen:
CallableStatement ‹name› =
‹connection›.prepareCall ("{? = call ‹functionname›(?,...,?)}");
cstmt = [Link]("{? = call distance(?,?)}");
• Für OUT-Parameter sowie den Rückgabewert muss der JDBC-Datentyp der Parameter mit
‹callable-statement›.registerOutParameter (‹pos›,[Link].‹type›);
registriert werden.
[Link](1,[Link]);

11.3 Java und Datenbanken 383

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

C ALLABLE S TATEMENTS MIT PARAMETERN

• Vorbereitung (s.o.)
cstmt = [Link]("{? = call distance(?,?)}");
[Link](1,[Link]);
• IN-Parameter werden über set‹type› gesetzt:
[Link](2,"Freiburg");
[Link](3,"Berlin");
• Aufruf mit
[Link]();
• Lesen des OUT-Parameters mit get‹type›:
int distance = [Link](1);

11.3 Java und Datenbanken 384

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

Beispiel: CallableStatement
import [Link].*;
import [Link];
import [Link];
class JdbcCallProc {
public static void main (String args []) throws Exception {
[Link](new [Link]());
Properties props = new Properties();
[Link](new FileInputStream("[Link]"));
Connection conn = [Link]([Link]("url"), props);

CallableStatement call = [Link]("{? = call greet(?)}");

[Link](1,[Link]);
[Link](2,args[0]);
[Link]();
String answer = [Link](1);
[Link](answer);
[Link]();
}} [Filename: Java/[Link]]
Wenn die Funktion “Greet” (vgl. Folie 344) für den User verfügbar ist:
dbis@c42> java JdbcCallProc Joe

11.3 Java und Datenbanken 385

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

Beispiel: CallableStatement für Prozedur mit OUT Parametern

• Prozedur ChangePop(↓ country, l pop, ↑ density) von Folie 205:

import [Link].*; import [Link]; import [Link];
class JdbcCallInOutProc {
public static void main (String args []) throws Exception {
[Link](new [Link]());
Properties props = new Properties();
[Link](new FileInputStream("[Link]"));
Connection conn = [Link]([Link]("url"), props);

CallableStatement call = [Link]("{call ChangePop(?,?,?)}");

[Link](2,[Link]);
[Link](3,[Link]);
[Link](1,"D"); // country code
[Link](2,80000000); // new population
[Link]();
[Link]([Link](2)); // old population
[Link]([Link](3)); // density
[Link]();
}} [Filename: Java/[Link]]

11.3 Java und Datenbanken 386

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

F OLGE VON (U PDATE ) S TATEMENTS VERARBEITEN

• jedes Statement mit execute() einzeln abzuschicken kostet Zeit.

[Link](string)/executeBatch()

• ‹statement›.addBatch(‹string›): Statement (keine Query, keine

Prozedur-/Funktionsaufrufe, nur DML-Updates und DDL-Statements) zum Batch
dazunehmen,
• ... beliebig oft ... und dann
• int[] ‹statement›.executeBatch(): alle Statements ausführen; ergibt ein Array mit
updateCount-Werten,
• clearBatch()
⇒ Folge verschiedener Update-Statements erzeugen und ausführen lassen.

11.3 Java und Datenbanken 387

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

[Link]()

• mit [Link](‹string›) mit Parameter-?-Liste erzeugen

• Parameter mit ‹preparedStatement›.set‹type›(‹pos›,‹value›) setzen,
• ‹preparedStatement›.addBatch(): Gesetzte Werte zum Batch dazunehmen,
• ... beliebig oft ... und dann
• int[] ‹preparedStatement›.executeBatch(): Statement für alle Parametertupel
ausführen; ergibt ein Array mit updateCount-Werten,
• clearBatch()
⇒ Folge desselben Statements mit verschiedenen Parametern ausführen lassen.
• [Link](true) (true ist Default) ist dann auch praktisch.

[Link]()

• Für Prozeduren analog.

• Für Funktionen sinnlos.

11.3 Java und Datenbanken 388

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

DYNAMISCHES SQL: G ENERISCHES EXECUTE ()

Sonderfall: ‹string› dynamisch generiert. Wenn man nicht weiß, ob es eine Query oder ein
Update oder sogar ein Call ist, kann man mit execute() generisch vorgehen.
• erste Annahme: liefert nur ein Ergebnis: ein ResultSet oder einen UpdateCount.
• boolean ‹stmt›.execute(‹string›),
boolean ‹prepstmt›.execute(),
(boolean ‹callstmt›.execute() ist immer mit execute(), kein Rückgabeobjekt, boolean =
false.)
• “true” wenn das (erste) Ergebnis ein ResultSet ist;
“false” sonst.
• ResultSet ‹stmt›.getResultSet(): Falls das (erste) Ergebnis eine Ergebnismenge ist,
wird diese zurückgegeben; falls kein Ergebnis mehr vorhanden, oder das (erste) Ergebnis
ein Update-Zähler ist: null zurückgeben.
• int ‹stmt›.getUpdateCount(): Falls das (erste) Ergebnis ein Update-Zähler ist, wird
dieser (n ≥ 0) zurückgegeben; falls kein Ergebnis mehr vorhanden, oder das (erste)
Ergebnis eine Ergebnismenge ist, wird -1 zurückgegeben.

11.3 Java und Datenbanken 389

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

Folge von Ergebnissen verarbeiten

Eher selten: Statements, die mehrere Ergebnisse nacheinander zurückliefern.

• ‹stmt›.execute(‹string›), wobei ‹string› mehrere durch “;” getrennte Statements
enthält.
– in Oracle 19 nicht erlaubt; PL/SQL mit begin/end erlaubt (siehe Folie 392)
– in Postgres (und einigen anderen) erlaubt
– die Oracle-Doku behauptet, das könnte es auch bei Stored Procedures geben, wir
konnten aber bisher kein Beispiel finden/machen.
• boolean ‹stmt›.getMoreResults(): schaltet zum nächsten Ergebnis. true, wenn das
nächste Ergebnis eine Ergebnismenge ist, false, wenn es ein Update-Zähler ist, oder keine
weiteren Ergebnisse.
• alle Ergebnisse verarbeitet:
(( <stmt> .getResultSet() == null) &&
( <stmt> .getUpdateCount() == -1))
bzw.
(( <stmt> .getMoreResults() == false) &&
( <stmt> .getUpdateCount() == -1))

11.3 Java und Datenbanken 390

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

Folge von Ergebnissen verarbeiten

import [Link]; import [Link];
import [Link]; import [Link]; import [Link];
public class JdbcSequence {
public static void main(String[] args){
try { // sequence only allowed with postgres!
Connection con = [Link]("jdbc:postgresql://localhost:5432/thedb", "user", "passwd
String sql = "CREATE TABLE test AS SELECT * FROM continent; " +
"DELETE FROM test; DROP TABLE test; " +
"SELECT * FROM country;";
Statement stmt = [Link]();
boolean hasMoreResultSets = [Link](sql);
while ( hasMoreResultSets || [Link]() != -1 ) {
if ( hasMoreResultSets ) { // if has rs
ResultSet rs = [Link]();
int size = 0; while ([Link]()) { size++; }
[Link]("Result set size: " + size); }
else { // if ddl/dml/...
int queryResult = [Link]();
[Link](queryResult + " tuples updated");
} // check whether to continue in the loop:
hasMoreResultSets = [Link]();
} // while results
[Link](); [Link]();
} catch (SQLException e) { [Link](); } } } [Filename: Java/[Link]]

11.3 Java und Datenbanken 391

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

Test: Folge von Statements als PL/SQL-Block mit execute() verarbeiten, PL/SQL (Oracle)
import [Link]; import [Link]; import [Link];
import [Link]; import [Link]; import [Link];
public class JdbcSequenceOracle {
public static void main(String[] args){
try { Properties props = new Properties();
[Link](new FileInputStream("[Link]"));
Connection con = [Link]([Link]("url"), props);
String sql = "DECLARE s varchar2(20);" +
"BEGIN SELECT name INTO s FROM continent WHERE name=’Asia’; " +
"DELETE FROM continent; UPDATE country SET population=80000000 WHERE code=’D’; END;";
Statement stmt = [Link]();
boolean hasMoreResultSets = [Link](sql);
while ( hasMoreResultSets || [Link]() != -1 ) {
if ( hasMoreResultSets ) { // if has rs
ResultSet rs = [Link]();
int size = 0; while ([Link]()) { size++; }
[Link]("Result set size: " + size); }
else { // if ddl/dml/... • in PL/SQL sind keine DDL-
int queryResult = [Link](); Statements erlaubt,
[Link](queryResult + " tuples updated");
• die Sequenz wird ausgeführt, aber
} // check whether to continue in the loop:
hasMoreResultSets = [Link](); } liefert keinerlei Rückgabewerte.
[Link](); [Link]();
} catch (Exception e) { [Link](); } } } [Filename: Java/[Link]]
11.3 Java und Datenbanken 392
 Praktikum: Datenbankprogrammierung in SQL/ORACLE

T RANSAKTIONSSTEUERUNG
• Hinweis: “commit” als Statement-String ist nicht erlaubt (es tut einfach nichts)!
Per Default ist für eine Connection der Auto-Commit-Modus gesetzt:
• implizites Commit nach jeder ausgeführten Anweisung (Transaktion besteht also nur aus
einem Statement)
• [Link](false) schaltet den Auto-Commit-Modus aus und man muss explizite
Commits ausführen.
Dann hat man die folgenden Methoden:
• [Link](String name) (setzt Sicherungspunkt)
• [Link]() (macht Änderungen persistent)
• [Link]([‹savepoint›)] (nimmt alle Änderungen [bis zu ‹savepoint›] zurück.

11.3 Java und Datenbanken 393

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

Beispiel: Transaktionen in JDBC

import [Link].*; import [Link]; import [Link];
class JdbcTransactions {
public static void main (String args []) throws Exception {
[Link](new [Link]());
Properties props = new Properties();
[Link](new FileInputStream("[Link]"));
Connection conn1 = [Link]([Link]("url"), props);
[Link](false);
Statement stmt1 = [Link]();
[Link]("DROP TABLE TTEST");
[Link]("CREATE TABLE TTEST(A VARCHAR2(4))");
[Link]("INSERT INTO TTEST VALUES(’1BLA’)");
[Link]("COMMIT"); [Link](); // choose one to show which works
[Link] ("select from conn1:");
ResultSet rset1 = [Link]("SELECT A FROM TTEST");
while ([Link]()) { String s = [Link](1); [Link] (s); }

Connection conn2 = [Link]([Link]("url"), props);

Statement stmt2 = [Link]();
[Link]("INSERT INTO TTEST VALUES(’2FOO’)");
[Link] ("select from conn2:");
ResultSet rset2 = [Link]("SELECT A FROM TTEST");
while ([Link]()) { String s = [Link](1); [Link] (s); }
[Link](); [Link](); } } [Filename: Java/[Link]]

11.3 Java und Datenbanken 394

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

F EHLERBEHANDLUNG IN A NWENDUNGEN
• JDBC-Aufrufe werfen ggf. SQLExceptions.
• Nicht geschlossene Verbindungen bleiben offen.
• SQL-Ausführung in try-catch-Block mit finally einbetten:
Connection con = null;
Statement stmt = null;
ResultSet rset = null;
try {
... con, stmt, rset aufbauen und verarbeiten ...
} catch (SQLException e) { [Link](); }
finally { [Link](); [Link](); [Link](); }

11.3 Java und Datenbanken 395

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

F EHLERBEHANDLUNG , E FFIZIENZ
• Wenn die Java-Objekte conn, stmt, rset nur lokal sind, baut der Garbage-Collector sie auch
schnell genug ab und schließt die Verbindungen.
• Bei häufigem Kontakt zu derselben Datenbank ist es effizienter, Connection-Objekte nur
einmal zu erzeugen/aufzubauen und dann in einem Pool zu verwalten:
[Link]‹T›
• [Link]() gibt die Connection dann an den Pool zurück.
• In diesem Fall werden bei Fehlern liegengebliebene Connections ohne try-catch-finally
nicht zurückgegeben, und bleiben offen.
⇒ bei 150 (default) Connections blockiert der Server:
[Link]: I/O-Fehler: Got minus one from a read call

11.3 Java und Datenbanken 396

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

Fehlerbehandlung - Demo
import [Link].*;
import [Link]; import [Link]; import [Link];
class JdbcConnectionOverflow {
public static void main (String args [])
throws Exception {
[Link](new [Link]());
Properties props = new Properties();
[Link](new FileInputStream("[Link]"));
String url = [Link]("url");
Connection conn; Statement stmt; ResultSet rset;
int i = 0;
HashSet<Connection> s = new HashSet<Connection>();
while (true) {
try { [Link](200); i++; [Link](i);
conn = [Link](url, props);
[Link](conn);
stmt = [Link]();
rset = [Link]("select * from qwertz");
} catch (SQLException e) { [Link](); }
catch (InterruptedException ex) { [Link]().interrupt(); }
}}}
[Filename: Java/[Link]]
/* ADMIN only:
select username, count(*) from V$SESSION group by username; */

11.3 Java und Datenbanken 397

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

T ECHNISCHER H INWEIS FALLS JDBC-V ERBINDUNGSAUFBAU STOCKT

Es kann vorkommen, dass JDBC-Aufrufe plötzlich lange dauern (und beim nächsten Versuch
wieder schnell gehen usw.).
Siehe [Link]
the-db-suddenly-become-very-slow-using-sqlcl/
Der Oracle JDBC Driver benötigt eine Zufallszahl, um die Verbindungsdaten zu verschlüsseln.
Die übliche Art, wie diese mit /dev/random generiert werden, kann stocken.
Abhilfe: Aufruf von java mit:
java -[Link]=[Link] java-file argumente

11.3 Java und Datenbanken 398

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

11.4 SQL-Datenbank-Zugriff in Java Stored Procedures

• Java Stored Procedures: JDBC mit dem serverseitigen JDBC-Treiber von Oracle
(jdbc:default:connection:).
• User/Password nicht angeben, da es bereits in der DB abläuft:
import [Link].*;
public class GetCountryData{
public static void getPop (String code) throws SQLException {
String sql = "SELECT name,population FROM country WHERE code = ?";
try {
Connection conn = [Link]("jdbc:default:connection:");
PreparedStatement pstmt = [Link](sql);
[Link](1, code);
ResultSet rset = [Link]();
if ([Link]()) [Link]([Link](2));
[Link]();
}
catch (SQLException e) {
[Link]([Link]());
}}} [Filename: Java/[Link]]

11.4 Java und Datenbanken 399

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

J AVA -K LASSE IN PL/SQL-P ROZEDUR EINBINDEN

Laden in die Datenbank:

loadjava -r [Link]
Definition und Ausführung des Wrappers in der DB:
CREATE OR REPLACE PROCEDURE getPopulation (code IN VARCHAR2) AS
LANGUAGE JAVA
NAME ’[Link]([Link])’;
/
[Filename: Java/[Link]]

... Output aktivieren:

SET SERVEROUTPUT ON;

CALL dbms_java.set_output(2000);

EXEC getPopulation(’D’);
80219712

11.4 Java und Datenbanken 400

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

11.5 SQLJ
Realisierung des “Embedded SQL”-Konzeptes für Java:
• Standardisierte Spracherweiterung, SQLJ-Programm
• Eingebettete SQLJ-Aufrufe werden vom Precompiler [Link]

in pures Java übersetzt und dabei auf JDBC-Aufrufe Metadaten

SQLJ-Precompiler
abgebildet.
• Oracle: sqlj enthält den Precompiler und Compiler. Java-Quellprogramm
Der Aufruf von sqlj [Link] erzeugt [Link] [Link]

und [Link]. Datenbank

Java-Compiler
• die Quelldatei muss die Endung .sqlj haben.
• Wenn man [Link] anschaut, findet man die Um- Java-Bytecode

setzung via JDBC. [Link]

11.5 Java und Datenbanken 401

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

Aktueller Stand (2022)

• SQLJ wird als “deprecated” (veraltet) bezeichnet.

• sqlj ruft [Link] auf (von Oracle-Webseiten).
• Mit Java 8 zur Zeit (Juni 2017/2022) nicht lauffähig
(Klasse sun/io/CharToByteConverter wird nicht gefunden, existiert in Java 8 nicht mehr)
• aktuelle Installation im CIP-Pool: im sqlj-Skript wird der Classpath auf Java 7 gesetzt.

... interne Java-Version bei Oracle

(Stand Juni 2022, Oracle 19c)

SELECT dbms_java.get_ojvm_property(PROPSTRING
=>’[Link]’)
FROM dual;

DBMS_JAVA.GET_OJVM_PROPERTY(PROPSTRING=>’[Link]’)
--------------------------------------------------------------------------------
1.8.0_331

11.5 Java und Datenbanken 402

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

A NWEISUNGEN IN SQLJ
• Anfragen:
#sql anIterator
= {SELECT name, population FROM country};
wobei anIterator ein (auch per SQLJ) geeignet definierter Iterator ist.
• DML und DDL:
#sql{‹statement›};
• Prozeduraufrufe:
#sql{CALL ‹proc_name›[(‹parameter-list›)]};
• Funktionsaufrufe:
#sql ‹variable›=
{VALUES(‹func_name›[(‹parameter-list›)])};
• Aufruf unbenannter Blöcke:
#sql {BEGIN ... END};

11.5 Java und Datenbanken 403

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

V ERBINDUNGSAUFBAU ZU O RACLE

Ausführliche Variante

import [Link].*;
import [Link];
//--------------
import [Link].*;
import [Link];
:
String url = "jdbc:oracle:thin:@//[Link]/[Link]-goettingen.
String user = "...";
String passwd = "...";
[Link] (new [Link]());
Connection con = [Link](url,user,passwd);
DefaultContext ctx = new DefaultContext(con);
[Link](ctx);
[Link](url, user, passwd);
//--------------

11.5 Java und Datenbanken 404

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

Verbindungsaufbau zu Oracle: Kompaktere Variante

• verwendet [Link]
import [Link].*;
import [Link];
:
[Link](<JavaClass>.class, "[Link]");
:
• <JavaClass>.class ist eine Klasse, die im Dateisystem/jar-Archiv im selben Verzeichnis
wie [Link] liegt (der Name dieser Klasse dient nur dazu, [Link] zu finden!).

11.5 Java und Datenbanken 405

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

H OSTVARIABLEN
• Verwendung von Variablen einer Host-Sprache (hier Java) in SQL-Statements
• Dient dem Datenaustausch zwischen Datenbank und Anwendungsprogramm
• in SQLJ-Statements wird Hostvariablen ein Doppelpunkt (“:”) vorangestellt
• Datentypen der Datenbank- und Programmiersprache müssen kompatibel sein (siehe
JDBC)
In Host-Variablen schreiben:
int countries;
#sql{SELECT COUNT(*) INTO :countries FROM country};

Aus Host-Variablen lesen:

int population = 75000000;
#sql{UPDATE country SET population = :population WHERE code=’D’};

11.5 Java und Datenbanken 406

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

I TERATOREN
• Allgemein: Design-Pattern, sequenzieller Zugriff auf alle Objekte, die in einem Container
enthalten sind
• Hier: Iteratoren bilden das Cursor-Konzept auf SQLJ ab.
• Iteratoren mit benannten Spalten:
– Spaltenzugriff über Spaltennamen
‹var›:= ‹iterator›.namei ()
– Weiterschaltung mit ‹iterator›.next()
• Positionsiteratoren:
– Spaltenzugriff über Positionen,
– dabei Weiterschaltung
– im embbedded-SQL-escape
#sql{FETCH :‹iterator› INTO :‹var›1 , ... , :‹var›n }
(Syntax im Stil des Datenbankzugriffs bei Netzwerkdatenbanken im CODASYL-Standard
(1964))

11.5 Java und Datenbanken 407

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

Iteratoren mit benannten Spalten

Hierbei erhalten die Attribute des Iterators Namen (“Schema”):

import [Link].*;
import [Link];
class sqljNamedIteratorExample {
public static void main (String args []){
try { // Datenbank-Verbindung aufbauen
[Link]([Link], "[Link]");
// Deklaration des Iterators mit Spaltennamen und Typen
#sql iterator CountryIter(String name, int population);
// Iteratorinstanz definieren
CountryIter cIter;
// Initialisieren des Iterators mit der SQL-Anweisung
#sql cIter = {SELECT name, population FROM country};
// Abarbeitung der Ergebnismenge durch Iteration
while ([Link]()) {
[Link]([Link]() + " has " +
[Link]() + " inhabitants."); }
[Link]();
} [Filename: Java/[Link]]
catch (SQLException e) { [Link]([Link]()); } }}
11.5 Java und Datenbanken 408
 Praktikum: Datenbankprogrammierung in SQL/ORACLE

Positionsiteratoren
import [Link].*; import [Link];
class sqljPosIteratorExample {
public static void main (String args []){
try { // Datenbank-Verbindung aufbauen
[Link]([Link], "[Link]");
// Deklaration des Iterators nur mit Typen
#sql iterator CountryPosIterator(String, int);
// Hilfsvariablen der Hostsprache
String name = "";
int pop = 0;
CountryPosIterator cIter; // Iteratorinstanz definieren
// Initialisieren des Iterators mit der SQL-Anweisung
#sql cIter = {SELECT name, population FROM country};
// Abarbeitung der Ergebnismenge durch Iteration
while (true) { // hole naechsten Datensatz
#sql{FETCH :cIter INTO :name,:pop};
// Ende des Iterators erreicht?
if([Link]()) break;
[Link](name + " has " + pop + " inhabitants."); }
[Link](); [Filename: Java/[Link]]
} catch (SQLException e) { [Link]([Link]()); } } }
11.5 Java und Datenbanken 409
 Praktikum: Datenbankprogrammierung in SQL/ORACLE

V ERGLEICH : JDBC UND SQLJ

JDBC SQLJ

• Call-Level-Schnittstelle • Embedded SQL

• Dynamisches SQL • Statisches SQL
• Fehlererkennung erst zur Laufzeit • Fehlererkennung bereits zur Übersetzungszeit
• Hohe Flexibilität • Kompakte Syntax

int countries;
Statement stmt = [Link]();
String query = "SELECT COUNT(*) FROM country";
ResultSet rset = [Link](query);
[Link]();
countries = [Link](1);

int countries;
#sql{SELECT COUNT(*) INTO :countries FROM country};

11.5 Java und Datenbanken 410

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

11.6 Weitere SQL/Oracle-Werkzeuge

• seit O RACLE8i (1999; i= internet)
Mit eingebauter Java Virtual Machine, Zugriff auf das Filesystem,
Oracle-Web Server/Internet Application Server (seit 9i):
HTML-Seiten werden abhängig vom Datenbankinhalt erstellt.
• mit den Paketen IAS, Internet File System Server wachsen Datenbank und Betriebssystem
zunehmend zusammen.
• seit O RACLE9i: Integration aus der XML-Welt (XMLType): XPath, XSLT, DOM, XML
Schema.
... siehe weitere Folien.
• O RACLE10g: grid computing
Oracle Rules Manager für Aktive Ereignis-basierte Regeln

11.6 Java und Datenbanken 411

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

E NTWICKLUNGSLINIE O RACLE
• 1977: Gründung durch Larry Ellison, Bob Miner, Ed Oates und Bruce Scott
• 1979: erstes Produkt
• 1992: Oracle 7
• letzte 7er (1997): 7.3.4: erste JDBC-Version
• 1997/1998: Oracle 8 (bis 8.0.4): Objekttypen, Nested Tables
• 3.1999: Oracle 8i/8.1.5 (i = Internet); JVM, Java Stored Procedures & Member Methods,
SQLJ
• 2.2001: Oracle 8.1.6: ein bisschen XML-Support (als Java-Tools)
• 6.2001: Oracle 9i: Java-Klassen als Object Types, Vererbung
• 5.2002: 9i-R2/9.2.0: verbesserter XML-Support (XMLType)
• 2003: Oracle 10g (g = Grid); Recycle Bin, XPath, XQuery
• 2007: 11g
• 2013: 12c: VARCHAR2 bis zu 32767 Bytes; Container-DB mit bis zu 252 Pluggable-DBs

11.6 Java und Datenbanken 412

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

Kapitel 12
SQL und XML
12.1 XML: “Extensible Markup Language”
... mehr als nur “Language”: Datenmodell, viele Sprachen
• Instantiierung von SGML (vgl. HTML)
• mit anwendungsspezifischen Tags
⇒ wer HTML kennt, weiß, wie XML “aussieht”.
• Baumstruktur, rekursiver Aufbau
• Elemente (Name, Attribute und Inhalt)
• Navigation im Baum
• vgl. Pfadausdrücke in Java, OQL, SQL; (z.B. [Link])
• Pfadausdrücke in Unix: (z.B. /home/may/teaching/dbp/[Link])
⇒ Adressierungssprache “XPath”

12.1 XML und SQLX 413

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

XML: B EISPIEL
<country id="D" capital="cty-Germany-Berlin">
<name>Germany</name>
<total_area>356910</total_area>
<population year="2011">80219695</population>
<encompassed continent="europe">100</encompassed>
<ethnicgroup name="German">85</ethnicgroup>
<ethnicgroup name="Polish">2.5</ethnicgroup>
<religion name="Roman Catholic">28.5</religion>
<religion name="Protestant">27.5</religion>
<language name="German">95</language>
<border country="F">451</border>
<border country="A">784</border>
<border country="CZ">646</border>
<province id="prov-Germany-Baden-Wuerttemberg">
<name>Baden Württemberg</name>
<area>35742</area>
<population year="2011">10486660</population>
<city id="cty-Germany-Stuttgart">
<name>Stuttgart</name>
:

12.1 XML und SQLX 414

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

<latitude>48.7</latitude>
<longitude>9.1</longitude>
<population year="2011">585890</population>
</city>
<city id="cty-Germany-Mannheim">
<name>Mannheim</name>
:
</city>
:
</province>
<province id="prov-Germany-Berlin">
<name>Berlin</name>
<area>889</area>
<population year="2011">3292365</population>
<city id="cty-Germany-Berlin">
<name>Berlin</name>
<latitude>52.45</latitude>
<longitude>13.3</longitude>
<population year="2011">3292365</population>
</city>
</province>
:
</country>
12.1 XML und SQLX 415
 Praktikum: Datenbankprogrammierung in SQL/ORACLE

XML
(Siehe Vorlesung “Semistrukturierte Daten und XML”)
• Verwendung:
– Dokumente
– Datenaustausch
– Datenspeicherung
• sehr flexibles “Datenmodell”: DOM-API
rekursiv definierte Baumstruktur aus
– Elementen,
– Attributen und
– Textknoten.
• Schema: DTD (Document Type Description), XML Schema
• Erweiterungen: XPath, XPointer, XLink
• Anfragesprache: XQuery
• Transformationssprache: XSL/XSLT
• Als Repräsentationssprache für viele Anwendungen genutzt

12.1 XML und SQLX 416

 Praktikum: Datenbankprogrammierung in SQL/ORACLE

12.2 Der SQL/XML bzw. SQLX Standard – Kombination

relationaler Daten und XML

• Abbildung von relationalen Daten nach XML

• Speicherung von XML-Daten in RDBMS
• Entwurf eines ISO-Standards seit 2003: [Link]
• SQL-Objektdatentyp “XMLType”
– mit entsprechenden Konstruktoren für XML-Strukturen,
– und Zugriffsmethoden (basierend auf den Standards der XML-Welt),
– benutzbar von SQL und innerhalb von PL/SQL.
• zum Teil noch unvollständig und überraschend ...
• Folien: siehe Vorlesung “Semistructured Data and XML”

12.2 XML und SQLX 417