Teil VIII

Fortgeschrittene Konzepte in SQL

Fortgeschrittene Konzepte in SQL

1. Rekursion in SQL

2. Fortgeschrittenes SQL

3. Prozedurale SQL-Erweiterungen: SQL/PSM

8-1
Lernziele fur
\" heute . . .

\bullet Verständnis der Formulierung und Auswertung

rekursiver Anfragen
\bullet Beispiele komplexer SQL-Anfragen
\bullet Prozedurale SQL-Erweiterungen: SQL/PSM

8-2 VL Datenbanksysteme Sattler / Saake

 Rekursion in SQL

8-3 VL Datenbanksysteme Sattler / Saake

Benannte Anfragen

\bullet Anfrageausdruck, der in der Anfrage mehrfach referenziert werden

kann
with anfrage-name [(spalten-liste) ] as
( anfrage-ausdruck )

\bullet Anfrage ohne with

select *
from WEINE
where Jahrgang ?`= (
select avg(Jahrgang) from WEINE) - 2
and Jahrgang !`= (
select avg(Jahrgang) from WEINE) + 2

8-4 VL Datenbanksysteme Sattler / Saake

Benannte Anfragen /2

\bullet Anfrage mit with

with ALTER(Durchschnitt) as (
select avg(Jahrgang) from WEINE)
select *
from WEINE, ALTER
where Jahrgang ?`= Durchschnitt - 2
and Jahrgang !`= Durchschnitt + 2

8-5 VL Datenbanksysteme Sattler / Saake

Rekursive Anfragen

\bullet Anwendung: Bill of Material-Anfragen, Berechnung der

transitiven Hulle
\" (Flugverbindungen etc.)
\bullet Beispiel:

BUSLINIE Abfahrt Ankunft Distanz

Nuriootpa Penrice 7
Nuriootpa Tanunda 7
Tanunda Seppeltsfield 9
Tanunda Bethany 4
Bethany Lyndoch 14

8-6 VL Datenbanksysteme Sattler / Saake

Rekursive Anfrage: Busfahrt mit max. 2x Umsteigen

select Abfahrt, Ankunft from BUSLINIE

where Abfahrt = 'Nuriootpa'
union
select [Link], [Link]
from BUSLINIE B1, BUSLINIE B2
where [Link] = 'Nuriootpa'
and [Link] = [Link]
union
select [Link], [Link]
from BUSLINIE B1, BUSLINIE B2, BUSLINIE B3
where [Link] = 'Nuriootpa'
and [Link] = [Link]
and [Link] = [Link]

8-7 VL Datenbanksysteme Sattler / Saake

Rekursion in SQL:2003

\bullet Formulierung über erweiterte with recursive-Anfrage

\bullet Notation
with recursive rekursive-tabelle as (
anfrage-ausdruck -- \mathrm{r}\mathrm{e}\mathrm{k}\mathrm{u}\mathrm{r}\mathrm{s}\mathrm{i}\mathrm{v}\mathrm{e}\mathrm{r} \mathrm{T}\mathrm{e}\mathrm{i}\mathrm{l}
)
[traversierungsklausel] [zyklusklausel]
anfrage-ausdruck -- \mathrm{n}\mathrm{i}\mathrm{c}\mathrm{h}\mathrm{t} \mathrm{r}\mathrm{e}\mathrm{k}\mathrm{u}\mathrm{r}\mathrm{s}\mathrm{i}\mathrm{v}\mathrm{e}\mathrm{r} \mathrm{T}\mathrm{e}\mathrm{i}\mathrm{l}

\bullet nicht rekursiver Teil: Anfrage auf Rekursionstabelle

8-8 VL Datenbanksysteme Sattler / Saake

Rekursion in SQL:2003 /2

\bullet rekursiver Teil:

-- \mathrm{I}\mathrm{n}\mathrm{i}\mathrm{t}\mathrm{i}\mathrm{a}\mathrm{l}\mathrm{i}\mathrm{s}\mathrm{i}\mathrm{e}\mathrm{r}\mathrm{u}\mathrm{n}\mathrm{g}
select ...
from tabelle where ...
-- \mathrm{R}\mathrm{e}\mathrm{k}\mathrm{u}\mathrm{r}\mathrm{s}\mathrm{i}\mathrm{o}\mathrm{n}\mathrm{s}\mathrm{s}\mathrm{c}\mathrm{h}\mathrm{r}\mathrm{i}\mathrm{t}\mathrm{t}
union all
select ...
from tabelle, rekursionstabelle
where rekursionsbedingung

8-9 VL Datenbanksysteme Sattler / Saake

Rekursion in SQL:2003: Beispiel

with recursive TOUR(Abfahrt, Ankunft) as (

select Abfahrt, Ankunft
from BUSLINIE
where Abfahrt = 'Nuriootpa'
union all
select [Link], [Link]
from TOUR T, BUSLINIE B
where [Link] = [Link])
select distinct * from TOUR

8 - 10 VL Datenbanksysteme Sattler / Saake

Schrittweiser Aufbau der Rekursionstabelle TOUR

Initialisierung Schritt 1
Abfahrt Ankunft Abfahrt Ankunft
Nuriootpa Penrice Nuriootpa Penrice
Nuriootpa Tanunda Nuriootpa Tanunda
Nuriootpa Seppeltsfield
Nuriootpa Bethany
Schritt 2
Abfahrt Ankunft
Nuriootpa Penrice
Nuriootpa Tanunda
Nuriootpa Seppeltsfield
Nuriootpa Bethany
Nuriootpa Lyndoch

8 - 11 VL Datenbanksysteme Sattler / Saake

Rekursion: Beispiel /2

\bullet arithmetische Operationen im Rekursionsschritt

with recursive TOUR(Abfahrt, Ankunft, Strecke) as (

select Abfahrt, Ankunft, Distanz as Strecke
from BUSLINIE
where Abfahrt = 'Nuriootpa'
union all
select [Link], [Link],
Strecke + Distanz as Strecke
from TOUR T, BUSLINIE B
where [Link] = [Link])
select distinct * from TOUR

8 - 12 VL Datenbanksysteme Sattler / Saake

Sicherheit rekursiver Anfragen

\bullet Sicherheit (= Endlichkeit der Berechnung) ist wichtige Anforderung

an Anfragesprache
\bullet Problem: Zyklen bei Rekursion
insert into BUSLINIE (Abfahrt, Ankunft, Distanz)
values ('Lyndoch', 'Tanunda', 12)

\bullet Behandlung in SQL

Begrenzung der Rekursionstiefe

Zyklenerkennung

8 - 13 VL Datenbanksysteme Sattler / Saake

Sicherheit rekursiver Anfragen /2

\bullet Einschränkung der Rekursionstiefe

with recursive TOUR(Abfahrt, Ankunft, Umsteigen) as (
select Abfahrt, Ankunft, 0
from BUSLINIE
where Abfahrt = 'Nuriootpa'
union all
select [Link], [Link], Umsteigen + 1
from TOUR T, BUSLINIE B
where [Link] = [Link] and Umsteigen !` 2)
select distinct * from TOUR

8 - 14 VL Datenbanksysteme Sattler / Saake

Sicherheit durch Zyklenerkennung

\bullet Zyklusklausel

beim Erkennen von Duplikaten im Berechnungspfad von attrib:

Zyklus = '*' (Pseudospalte vom Typ char(1))
Sicherstellen der Endlichkeit des Ergebnisses von Hand``
""

cycle attrib set marke to '*' default '-'

8 - 15 VL Datenbanksysteme Sattler / Saake

Sicherheit durch Zyklenerkennung

with recursive TOUR(Abfahrt, Ankunft, Weg) as (

select Abfahrt, Ankunft,
Abfahrt ------ '-' ------ Ankunft as Weg
from BUSLINIE where Abfahrt = 'Nuriootpa'
union all
select [Link], [Link],
Weg ------ '-' ------ [Link] as Weg
from TOUR T, BUSLINIE B
where [Link] = [Link])
cycle Ankunft set Zyklus to '*' default '-'
select Weg, Zyklus from TOUR

8 - 16 VL Datenbanksysteme Sattler / Saake

Sicherheit durch Zyklenerkennung /2

Weg Zyklus
Nuriootpa-Penrice -
Nuriootpa-Tanunda -
Nuriootpa-Tanunda-Seppeltsfield -
Nuriootpa-Tanunda-Bethany -
Nuriootpa-Tanunda-Bethany-Lyndoch -
Nuriootpa-Tanunda-Bethany-Lyndoch-Tanunda *

8 - 17 VL Datenbanksysteme Sattler / Saake

SQL-Versionen

\bullet Geschichte

SEQUEL (1974, IBM Research Labs San Jose)

SEQUEL2 (1976, IBM Research Labs San Jose)
SQL (1982, IBM)
ANSI-SQL (SQL-86; 1986)
ISO-SQL (SQL-89; 1989; drei Sprachen Level 1, Level 2, + IEF)
(ANSI / ISO) SQL2 (als SQL-92 verabschiedet)
(ANSI / ISO) SQL3 (als SQL:1999 verabschiedet)
(ANSI / ISO) SQL:2003 . . . aktuell SQL:2011

\bullet trotz Standardisierung: teilweise Inkompatibilitäten zwischen

Systemen der einzelnen Hersteller

8 - 18 VL Datenbanksysteme Sattler / Saake

 Fortgeschrittenes SQL

8 - 19 VL Datenbanksysteme Sattler / Saake

Fortgeschrittenes SQL

\bullet SQL ist weitaus mächtiger als SFW

\bullet Unterstützung von komplexen Transformationen, Manipulationen
und Analysen auch extrem großer Datenbestände
\bullet Turing-Vollständigkeit durch prozedurale Erweiterungen (SQL/PSM,
PL/SQL, Transact-SQL, . . . )
\bullet nachfolgend: erweiterte SQL-Konstrukte mit Beispielen als Muster
für Problemlösungen

8 - 20 VL Datenbanksysteme Sattler / Saake

Pivotierung

Problem: Zeilen in Spalten bzw. umgekehrt umwandeln

LDATA Quartal Jahr Umsatz \Leftarrow \Rightarrow RDATA Jahr Q1 Q2 Q3 Q4

1 2020 12 2020 12 10 11 9
2 2020 10 2021 13 12 \bot \bot
3 2020 11
4 2020 9
1 2021 13
2 2021 12

8 - 21 VL Datenbanksysteme Sattler / Saake

Pivotierung /2

L\"
osung (Zeilen in Spalten): Aggregatfilter
select Jahr, sum(Umsatz) filter (where Quartal=1) Q1,
sum(Umsatz) filter (where Quartal=2) Q2,
sum(Umsatz) filter (where Quartal=3) Q3,
sum(Umsatz) filter (where Quartal=4) Q4
from LDATA
group by Jahr

\bullet Warum sum und group by?

\bullet filter nicht in allen Systemen verfügbar \rightsquigarrow Alternative über case

8 - 22 VL Datenbanksysteme Sattler / Saake

Pivotierung /3

L\"
osung (Spalten in Zeilen): union all
select 1 as Quartal, Jahr, Q1 as Umsatz from RDATA
union all
select 2 as Quartal, Jahr, Q2 as Umsatz from RDATA
union all
select 3 as Quartal, Jahr, Q3 as Umsatz from RDATA
union all
select 4 as Quartal, Jahr, Q4 as Umsatz from RDATA

\bullet Warum union all statt union?

8 - 23 VL Datenbanksysteme Sattler / Saake

Telefonverzeichnis

Problem: Pivotierung mit fehlenden Werten \rightarrow Ausgabe der

Telefonnummern zu einer Person in einer Zeile
Personal PNr Vorname Name Kontakt PNr KTyp Nummer
101 Bernd K. 101 Mobil 0175. . .
102 Simone S. 101 Arbeit 5556
103 Franz M. 102 Mobil 0165. . .
103 Arbeit 5557

\bullet zweifacher Verbund mit Kontakt?

8 - 24 VL Datenbanksysteme Sattler / Saake

Telefonverzeichnis

L\"
osung: linker äußerer Verbund
select Vorname, Name, [Link] as Mobil, [Link] as Arbeit
from (Personal P left outer join Kontakt K
on [Link] = [Link] and [Link] = 'Mobil')
left outer join Kontakt K2
on [Link] = [Link] and [Link] = 'Arbeit'

8 - 25 VL Datenbanksysteme Sattler / Saake

Top-k

Problem: Beschränkung der Ergebnismenge auf k Elemente, z.B. nach

Sortierung
RACE Name Distanz Zeit
Klaus HM 1:20
Bernd HM 1:40
Tanja M 3:58
Franz SM 5:42
Martina M 3:05
Heike HM 1:34
Corinna SM 5:53
Jens M 2:51
Herbert SM 6:07
L\"
osungen:
\bullet proprietäre Erweiterungen wie z.B. limit
\bullet Window-Funktionen in SQL + rank()

8 - 26 VL Datenbanksysteme Sattler / Saake

Window-Funktionen: Syntax

Funktion(arg)

OVER( )

Partitionierungs- Ordnungs-
klausel klausel

Fenster
Partitionierung für jedes Attribut
Ordnung innerhalb
des Ergebnisses ohne Verdichtung:
eines Attributs Festlegung des
jeder Eingangswert ↦ Ergebniswert
8 - 27 VL Datenbanksysteme Sattler / Saake
Aggregationsfensters
Window-Funktionen: Prinzip

\bullet Anzahl der Tupel, die in ein Ergebnistupel eingehen entspricht

Position des Tupels bzgl. gegebener Ordnung
\bullet Eingangstupel ti , Ergebnistupel si

t1 - \rightarrow sum(\{ t1 \} ) - \rightarrow s1

t2 - \rightarrow sum(\{ t1 , t2 \} ) - \rightarrow s2
t3 - \rightarrow sum(\{ t1 , t2 , t3 \} ) - \rightarrow s3
...

\bullet Schrittweise Vergrößerung des Analysefensters

8 - 28 VL Datenbanksysteme Sattler / Saake

Window-Funktionen: Anwendung

Was liefert . . .
select count(*) over() count
from RACE 9
9
9
...
und . . .
count
select count(*) over(order by Zeit)
from RACE 1
2
3
...
9

8 - 29 VL Datenbanksysteme Sattler / Saake

Ranking-Funktionen

\bullet rank(): liefert Rang eines Tupels bzgl. vorgegebener Ordnung

innerhalb der Partition
Bei Duplikaten gleicher Rang (mit Lucken)
\"
\bullet dense rank(): wie rank(), jedoch ohne Lücken

select Name, Zeit, rank() over (order by Zeit)

from RACE
where Distanz = 'M'

Name Zeit rank

Jens 2:51 1
Martina 3:05 2
Tanja 3:58 3

8 - 30 VL Datenbanksysteme Sattler / Saake

Top-k mit Ranking-Funktion

select * from (
select Name, Zeit, rank() over (order by Zeit) Rang
from RACE
where Distanz = 'M') T
where Rang !`= 2

\bullet Schachtelung erforderlich, weil Projektion nach Selektion ausgeführt

wird: Rang in der where-Klausel der inneren Anfrage nicht verfügbar
\bullet Top-2: Rang !`= 2

8 - 31 VL Datenbanksysteme Sattler / Saake

Top-k mit Ranking-Funktion /2

\bullet Top-2 pro Distanz durch Gruppierung in der Window-Funktion über

partition by

select * from (
select Name, Distanz, Zeit,
rank() over (partition by Distanz order by Zeit) Rang
from RACE) T
where Rang !`= 2

Name Distanz Zeit Rang

Klaus HM 1:20 1
Heike HM 1:34 2
Jens M 2:51 1
Martina M 3:05 2
Franz SM 5:42 1
Corinna SM 5:53 2

8 - 32 VL Datenbanksysteme Sattler / Saake

Gleitender Durchschnitt

Problem: Glättung von Daten- bzw. Zeitreihen \rightsquigarrow Berechnung des

Mittelwertes über ein Fenster (=Ausschnitt) der Datenwerte
SERIES Zeit Wert
0:01 4.2 6
5.5
0:02 4.3 5
4.5

Wert
0:03 4.9 4
0:04 4.4 3.5
0:05 4.1 3
2.5
0:06 3.9 2
0:07 4.5 0 1 2 3 4 5 6 7 8 9 10
0:08 4.3 Zeit
0:09 4.1
L\"
osung: Window-Funktion mit Aggregationsfenster

8 - 33 VL Datenbanksysteme Sattler / Saake

Aggregationsfenster

explizite Angabe des Fensters

\bullet rows: Anzahl der Tupel; range: Anzahl der wertmäßig
verschiedenen Tupel
ausgehend von definierten Startpunkt bis zum aktuellen Tupel
\bullet unbounded preceding: erstes Tupel der jeweiligen Partition; n
preceding: n-ter Vorgänger relativ zur aktuellen Position; current
row: aktuelles Tupel (nur mit range und Duplikaten sinnvoll)
Angabe der unteren und oberen Schranken
BETWEEN untereGrenze AND obereGrenze

Spezifikation der Grenzen

\bullet unbounded preceding, unbounded following, n preceding, n
following, current row
obereGrenze muss höhere Position als untereGrenze spezifizieren
8 - 34 VL Datenbanksysteme Sattler / Saake
Gleitender Durchschnitt /2

Glättung der Datenreihe über Fenster von 3 Werten

select Zeit,
avg(Wert) over(order by Zeit asc rows 3 preceding)
from SERIES

8 - 35 VL Datenbanksysteme Sattler / Saake

Mengengleichheit

Problem: Test auf Gleichheit von zwei Mengen

Beispiel: Welche zwei Personen haben exakt die gleichen Hobbies?

Name Hobby
Kevin Schach
Kevin Musik
Corinna Parties
Martin Handball
Martin Musik
Katja Handball
Katja Musik

8 - 36 VL Datenbanksysteme Sattler / Saake

Mengengleichheit

Losung:
\" 7 verschiedene Varianten in Celko’s SQL Puzzles; hier:
Mengentheorie

\bullet A = B \updownarrow A \subset B \wedge B \subset A

\bullet in SQL für \subset besser: \not \exists e \in A : e \not \in B und demzufolge A \subset B
\bullet über not exists bzw. except

Teilschritt: verschiedene Hobbies, z.B. für Kevin und Martina vs. Katja
und Martin
select [Link] from Hobbies as h3
where 'Kevin' = [Link]
except
select [Link] from Hobbies as h4
where 'Martina' = [Link])

8 - 37 VL Datenbanksysteme Sattler / Saake

Mengengleichheit: L\"osung

select distinct [Link], [Link]

from Hobbies as h1, Hobbies as h2
where [Link] !` [Link] -- nicht die gleiche Person
and not exists ( -- e in h1 aber nicht in h2
select [Link] from Hobbies as h3
where [Link] = [Link]
except
select [Link] from Hobbies as h4
where [Link] = [Link])
and not exists ( -- e in h2 aber nicht in h1
select [Link] from Hobbies as h5
where [Link] = [Link]
except
select [Link] from Hobbies as h6
where [Link] = [Link])

8 - 38 VL Datenbanksysteme Sattler / Saake

Anschlussbus

Problem: Finde die nächste Busverbindung an einer Haltestelle

Buslinie Abfahrt Ankunft

10 8:00 9:20
11 9:10 10:35
12 9:10 11:00
13 9:55 10:45

z.B. für aktuelle Zeit (current time) oder gegebene Zeit (time
'8:00')

8 - 39 VL Datenbanksysteme Sattler / Saake

Anschlussbus

Losung:
\" Finde minimale Abfahrtszeit, die nach der gewünschten Zeit
liegt
select * from Fahrplan f1
where [Link] = (
select min([Link]) from Fahrplan f2
where [Link] ?`= time '8:05')

mit Berechnung der Wartezeit:

select Buslinie, [Link] - time '8:55'
...

8 - 40 VL Datenbanksysteme Sattler / Saake

Mode -- H\"aufigster Wert

Problem: Bestimmung des häufigsten Wertes in einer Spalte

\bullet Aggregatfunktion avg aber kein mode?

Name Studiengang
Kevin Informatik
Heike BWL
Corinna Mathematik
Martina BWL
Ronny BWL
Katharina Informatik

8 - 41 VL Datenbanksysteme Sattler / Saake

Mode -- H\"aufigster Wert

Losung:
\" Bestimmung der Anzahl des häufigsten Wertes + having
count(*) bezogen auf diesen Wert
select Studiengang, count(*)
from Students
group by Studiengang
having count(*) ?`= all (
select count(*)
from Students group by Studiengang)

8 - 42 VL Datenbanksysteme Sattler / Saake

Sudoku

Problem: Lösen von Sudoku

\bullet 81 Felder (9x9) mit Ziffern 1 . . . 9
\bullet Auffüllen mit Ziffern, so dass in jeder der je neun Zeilen, Spalten
und Bl\" ocke jede Ziffer von 1 bis 9 nur einmal auftritt

Von Wikipit - Eigenes Werk, CC BY-SA 3.0, [Link]

8 - 43 VL Datenbanksysteme Sattler / Saake

Sudoku

Losung:
\" systematisch alle Felder mit Werten von 1 bis 9 auffüllen und
prüfen
Quellen: [Link]
oracle-rdbms-11gr2-solving-a-sudoku-using-recursive-subquery-facto
[Link]
Hilfsfunktionen (PostgreSQL)
\bullet generate series(s,e): erzeugt eine Folge von Werten von s bis e
\bullet substr(s, start, len): liefert einen Substring der Länge len
aus s beginnend bei start
\bullet repeat(s, n): erzeugt einen String durch n-fache Wiederholung
von s
\bullet position(sub in s): liefert die Position von sub in s

8 - 44 VL Datenbanksysteme Sattler / Saake

Sudoku

Einsetzen aller Ziffern von 1 bis 9 an einem bestimmten Feld (hier: 3)

select substr([Link], 1, 3 - 1) ------ z ------ substr([Link], 3 + 1),
position(' ' in repeat('x', 3) ------ substr([Link], 3 + 1))
from (select '53 7 ' ------ '6 195 ' ------ ' 98 6 ' ------
'8 6 3' ------ '4 8 3 1' ------ '7 2 6' ------ ' 6 28 ' ------
' 419 5' ------ ' 8 79'::text as sud) s,
(select gs::text as z from generate series(1,9) gs) z

8 - 45 VL Datenbanksysteme Sattler / Saake

 \"
Sudoku: Uberprufen
\" der L\"osung

\bullet s ist die aktuell betrachtete Lösung

\bullet ind ist die Position, an der Ziffer eingesetzt wurde
\bullet z.z ist die aktuell eingesetzte Ziffer
\bullet lp enthält die zu prüfenden Positionen von 1 bis 9

-- fur
\" ind = 3
select * from generate series(1, 9) lp
-- Zeile: 1, 2, 3, 4, . . .
where z.z = substr(s, ((ind - 1) / 9) * 9 + lp, 1)
-- Spalte: 3, 12, 21, 30, . . .
or z.z = substr(s, mod(ind - 1, 9) - 8 + lp * 9, 1)
-- Block: 1, 2, 3, 10, 11, 12, . . .
+ ((ind - 1) / 27) * 27 + lp + ((lp - 1) / 3) * 6, 1))

8 - 46 VL Datenbanksysteme Sattler / Saake

Sudoku: Vollst\"andige Anfrage

with recursive x(s, ind) as (

select sud, position(' ' in sud)
from (select 'r\"atselstring '::text as sud) xx,
union all
select substr(s, 1, ind - 1) ------ z ------ substr(s, ind + 1),
position(' ' in repeat('x', ind) ------ substr(s, ind + 1))
from x,
(select gs::text as z from generate series(1,9) gs) z
where ind ?` 0 and not exists (
select null from generate series(1,9) lp
where z.z = substr(s, ((ind - 1) / 9) * 9 + lp, 1)
or z.z = substr(s, mod(ind - 1, 9) - 8 + lp * 9, 1)
or z.z = substr(s, mod(((ind - 1) / 3), 3) * 3
+ ((ind - 1) / 27) * 27 + lp + ((lp - 1) / 3) * 6, 1))
)
select s from x where ind = 0

8 - 47 VL Datenbanksysteme Sattler / Saake

Sudoku: Verbesserte Ausgabe

\bullet regexp replace (txt, pat, repl, flag): ersetzt in txt einen
zum Muster pat passenden String durch repl
\bullet regexp split to table (txt, pat): teilt den String txt anhand
des Musters pat und liefert eine Relation

...
select regexp replace(regexp split to table(
regexp replace(s, '.9(?!$)', '\setminus \&-', 'g'), '-'),
'.3(?!$)', '\setminus \&---', 'g')
from x
where position(' ' in s) = 0

8 - 48 VL Datenbanksysteme Sattler / Saake

 Prozedurale SQL-Erweiterungen: SQL/PSM

8 - 49 VL Datenbanksysteme Sattler / Saake

SQL/PSM: Der Standard

\bullet SQL-Standard für prozedurale Erweiterungen

\bullet PSM: Persistent Stored Modules

gespeicherte Module aus Prozeduren und Funktionen

Einzelroutinen
Einbindung externer Routinen (implementiert in C, Java, . . . )
syntaktische Konstrukte fur
\" Schleifen, Bedingungen etc.
Basis fur
\" Methodenimplementierung fur
\" objektrelationale Konzepte

8 - 50 VL Datenbanksysteme Sattler / Saake

Vorteile gespeicherter Prozeduren

\bullet bewährtes Strukturierungsmittel

\bullet Angabe der Funktionen und Prozeduren erfolgt in der
Datenbanksprache selbst; daher nur vom DBMS abhängig
\bullet Optimierung durch DBMS möglich
\bullet Ausführung der Prozeduren erfolgt vollständig unter Kontrolle des
DBMS
\bullet zentrale Kontrolle der Prozeduren ermöglicht eine redundanzfreie
Darstellung relevanter Aspekte der Anwendungsfunktionalität
\bullet Konzepte und Mechanismen der Rechtevergabe des DBMS können
auf Prozeduren erweitert werden
\bullet Prozeduren können in der Integritätssicherung verwendet werden
(etwa als Aktionsteil von Triggern)

8 - 51 VL Datenbanksysteme Sattler / Saake

SQL/PSM: Variablendeklaration

\bullet Variablen vor Gebrauch deklarieren

\bullet Angabe von Bezeichner und Datentyp
\bullet optional mit Initialwert

declare Preis float;

declare Name varchar(50);
declare Menge int default 0;

8 - 52 VL Datenbanksysteme Sattler / Saake

SQL/PSM: Ablaufkontrolle

\bullet Zuweisung
set var = 42;

\bullet Bedingte Verzweigungen

if Bedingung then Anweisungen
[ else Anweisungen ] end if;

8 - 53 VL Datenbanksysteme Sattler / Saake

SQL/PSM: Ablaufkontrolle /2

\bullet Schleifen
loop Anweisungen end loop;
while Bedingung do
Anweisungen end while;
repeat Anweisungen
until Bedingung end repeat;

8 - 54 VL Datenbanksysteme Sattler / Saake

SQL/PSM: Ablaufkontrolle /3

\bullet Schleifen mit Cursor

for SchleifenVariable as CursorName cursor for
CursorDeklaration
do
Anweisungen
end for;

8 - 55 VL Datenbanksysteme Sattler / Saake

SQL/PSM: Ablaufkontrolle /4

declare wliste varchar(500) default ' ';

declare pos integer default 0;

for w as WeinCurs cursor for

select Name from WEINE where Weingut = 'Helena'
do
if pos ?` 0 then
set wliste = wliste ------ ',' ------ [Link];
else
set wliste = [Link];
end if;
set pos = pos + 1;
end for;

8 - 56 VL Datenbanksysteme Sattler / Saake

SQL/PSM: Ausnahmebehandlung

\bullet Auslösen einer Ausnahme (Condition)

signal ConditionName;

\bullet Deklarieren von Ausnahmen

declare fehlendes weingut condition;
declare ungueltige region
condition for sqlstate value '40123';

8 - 57 VL Datenbanksysteme Sattler / Saake

SQL/PSM: Ausnahmebehandlung /2

Ausnahmebehandlung
begin
declare exit handler for ConditionName
begin
-- \mathrm{A}\mathrm{n}\mathrm{w}\mathrm{e}\mathrm{i}\mathrm{s}\mathrm{u}\mathrm{n}\mathrm{g}\mathrm{e}\mathrm{n} \mathrm{z}\mathrm{u}\mathrm{r} \mathrm{A}\mathrm{u}\mathrm{s}\mathrm{n}\mathrm{a}\mathrm{h}\mathrm{m}\mathrm{e}\mathrm{b}\mathrm{e}\mathrm{h}\mathrm{a}\mathrm{n}\mathrm{d}\mathrm{l}\mathrm{u}\mathrm{n}\mathrm{g}
end
-- \mathrm{A}\mathrm{n}\mathrm{w}\mathrm{e}\mathrm{i}\mathrm{s}\mathrm{u}\mathrm{n}\mathrm{g}\mathrm{e}\mathrm{n}, \mathrm{d}\mathrm{i}\mathrm{e} \mathrm{A}\mathrm{u}\mathrm{s}\mathrm{n}\mathrm{a}\mathrm{h}\mathrm{m}\mathrm{e}\mathrm{n} \mathrm{a}\mathrm{u}\mathrm{s}\mathrm{l}\"
\mathrm{o}\mathrm{s}\mathrm{e}\mathrm{n} \mathrm{k}\"
\mathrm{o}\mathrm{n}\mathrm{n}\mathrm{e}\mathrm{n}
end

8 - 58 VL Datenbanksysteme Sattler / Saake

SQL/PSM: Funktionen

Funktionsdefinition
create function geschmack (rz int)
returns varchar(20)
begin
return case
when rz !`= 9 then 'Trocken'
when rz ?` 9 and rz !`= 18 then 'Halbtrocken'
when rz ?` 18 and rz !`= 45 then 'Lieblich'
else 'S\"
u{\ss}'
end
end

8 - 59 VL Datenbanksysteme Sattler / Saake

SQL/PSM: Funktionen /2

\bullet Aufruf innerhalb einer Anfrage

select Name, Weingut, geschmack(Restzucker)
from WEINE
where Farbe = 'Rot' and
geschmack(Restzucker) = 'Trocken'

\bullet Nutzung außerhalb von Anfragen

set wein geschmack = geschmack (12);

8 - 60 VL Datenbanksysteme Sattler / Saake

SQL/PSM: Prozeduren

\bullet Prozedurdefinition
create procedure weinliste (in erz varchar(30),
out wliste varchar(500))
begin
declare pos integer default 0;

for w as WeinCurs cursor for

select Name from WEINE where Weingut = erz
do
-- \mathrm{s}\mathrm{i}\mathrm{e}\mathrm{h}\mathrm{e} \mathrm{B}\mathrm{e}\mathrm{i}\mathrm{s}\mathrm{p}\mathrm{i}\mathrm{e}\mathrm{l} \mathrm{v}\mathrm{o}\mathrm{n} \mathrm{F}\mathrm{o}\mathrm{l}\mathrm{i}\mathrm{e} 13-56
end for;
end; end;

8 - 61 VL Datenbanksysteme Sattler / Saake

SQL/PSM: Prozeduren /2

\bullet Nutzung über call-Anweisung

declare wliste varchar(500);
call weinliste ('Helena', wliste);

8 - 62 VL Datenbanksysteme Sattler / Saake

SQL/PSM: Zugriffscharakteristik

\bullet Eigenschaften von Prozeduren, die Anfrageausführung und

-optimierung beeinflussen
deterministic: Routine liefert fur
\" gleiche Parameter gleiche
Ergebnisse
no sql: Routine enth\"
alt keine SQL-Anweisungen
contains sql:Routine enth\"
alt SQL-Anweisungen (Standard fur
\"
SQL-Routinen)
reads sql data: Routine fuhrt
\" SQL-Anfragen
(select-Anweisungen) aus
modifies sql data: Routine, die DML-Anweisungen (insert,
update, delete) enth\"
alt

8 - 63 VL Datenbanksysteme Sattler / Saake

PostgreSQL: PL/pgSQL

\bullet prozedurale SQL-Erweiterung in PostgreSQL

\bullet ähnlich zu Oracle’s PL/SQL und zu SQL/PSM

Funktionsdefinition
create function geschmack (rz int) returns varchar(20) as $$
begin
return case when rz !`= 9 then 'Trocken'
when rz ?` 9 and rz !`= 18 then 'Halbtrocken'
when rz ?` 18 and rz !`= 45 then 'Lieblich'
else 'S\"
u{\ss}'
end;
end;
$$ language plpgsql;

8 - 64 VL Datenbanksysteme Sattler / Saake

Zusammenfassung

\bullet Rekursion in SQL

\bullet komplexe SQL-Anfragen mit erweiterten Sprachmitteln
\bullet Prozedurale SQL-Erweiterungen

8 - 65 VL Datenbanksysteme Sattler / Saake

Kontrollfragen

\bullet Welchem Zweck dienen rekursive Anfragen in

SQL?
\bullet Wie kann SQL prozedural erweitert werden?

8 - 66 VL Datenbanksysteme Sattler / Saake