Oracle PL/SQL

1452.book Seite 1 Donnerstag, 5. August 2010 3:55 15
Jürgen Sieben
Oracle PL/SQL
Das umfassende Handbuch
1452.book Seite 3 Donnerstag, 5. August 2010 3:55 15
Auf einen Blick
1
Einführung ...........................................................................
17
2
Verwendete Werkzeuge und Ressourcen ..............................
29
TEIL I Grundlagen .........................................................................
53
3
Aufbau der Datenbank aus Sicht eines Programmierers ........
55
4
Datensicherheit, -konsistenz und Transaktion .......................
127
5
Die Datenbank in der Anwendungsarchitektur .....................
181
6
Programmierung der Datenbank ..........................................
223
TEIL II Die Sprache PL/SQL .......................................................... 247
7
Die Blockstruktur und Syntax von PL/SQL ............................
249
8
Events in der Datenbank: Programmierung von Triggern ......
345
9
Das Arbeiten mit Daten .......................................................
395
10
Packages ..............................................................................
441
11
Fehlerbehandlung ................................................................
483
TEIL III PL/SQL im Einsatz ........................................................... 511
12
Erweiterung von SQL ...........................................................
513
13
Arbeiten mit großen Datenstrukturen ...................................
559
14
Arbeiten mit XML ................................................................
603
15
Objektorientierung ...............................................................
681
16
Integration von Oracle in Applikationen ...............................
725
TEIL IV Workshops ...................................................................... 755
17
18
Workshop 1: Die Keimzelle sicherer
Datenbankanwendungen .....................................................
757
Workshop 2: Ein Logging-Package ........................................
777
3
1452.book Seite 5 Donnerstag, 5. August 2010 3:55 15
Inhalt
Die Oracle-Datenbank ist eines der mächtigsten und umfangreichsten relationalen Datenbanksysteme. Schon SQL ist ungeheuer vielseitig einsetzbar. Doch erst mit PL/SQL erschließt sich das gesamte Potenzial. Daher ist die Kenntnis von PL/SQL für jeden, der sich mit Oracle beschäftigt, essenziell. 17
1
Einführung .............................................................................. 17
1.1
1.2
1.3
Für wen ist dieses Buch geschrieben? .........................................
Der Aufbau des Buches ..............................................................
1.2.1 Teil 1: Grundlagen ........................................................
1.2.2 Teil 2: Die Sprache PL/SQL ............................................
1.2.3 Teil 3: PL/SQL im Einsatz ..............................................
1.2.4 Teil 4: Workshops .........................................................
Danksagung ...............................................................................
17
21
21
23
24
26
27
Damit wir uns im Folgenden auf die Themen konzentrieren können, erspare ich mir die dauernden Verweise auf die Online-Dokumentation oder die in diesem Buch verwendeten Werkzeuge. Stattdessen gebe ich Ihnen hier einen Überblick. 29
2
Verwendete Werkzeuge und Ressourcen .............................. 29
2.1
2.2
2.3
2.4
2.5
2.6
2.7
2.8
2.9
2.10
2.11
Oracles Online-Dokumentation .................................................
2.1.1 Wo finde ich die benötigten Informationen? .................
2.1.2 PL/SQL-Grundlagen ......................................................
2.1.3 Oracle Packages ............................................................
2.1.4 Weiterführende Literatur ..............................................
Aufsetzen einer Beispieldatenbank .............................................
SQL*Plus ...................................................................................
SQL-Developer ..........................................................................
»explain plan« ............................................................................
Autotrace ..................................................................................
RunStats ....................................................................................
Trace und TKProf .......................................................................
DBMS_Profiler ...........................................................................
Debugger ..................................................................................
Die Beispielskripte .....................................................................
29
30
33
34
34
35
38
40
41
43
45
46
50
51
52
TEIL I Grundlagen
In einem ersten Rundblick betrachten wir die Oracle-Datenbank aus Sicht eines Entwicklers. Hier lernen Sie die wichtigsten Strukturen und Arbeitsweisen für die Erstellung einer effizienten und skalierbaren Applikation kennen. 55
3
Aufbau der Datenbank aus Sicht eines Programmierers ....... 55
3.1
3.2
Instanz und Speicherstrukturen ..................................................
3.1.1 Die Speicherbereiche der SGA .......................................
3.1.2 Shared Pool ..................................................................
3.1.3 Die Hintergrundprozesse ...............................................
Die physikalische Datenbank .....................................................
3.2.1 Datendateien ................................................................
56
57
59
60
65
65
5
1452.book Seite 6 Donnerstag, 5. August 2010 3:55 15
Inhalt
3.3
3.4
3.5
3.6
3.7
3.8
3.2.2 Redo-Log-Dateien .........................................................
3.2.3 Kontrolldatei .................................................................
Parameter- und Passwortdatei ...................................................
3.3.1 Parameterdatei .............................................................
3.3.2 Passwortdatei ...............................................................
Start der Datenbank ..................................................................
Verbindungsaufbau zur Datenbank ............................................
3.5.1 Verbindungsarten und Treiber .......................................
3.5.2 DEDICATED SERVER-Verbindung .................................
3.5.3 Shared-Server-Verbindung ............................................
3.5.4 Database Resident Connection Pool ..............................
3.5.5 Und nun? Entscheidungshilfen für den
Verbindungsaufbau .......................................................
Logischer Aufbau: Schema, Tablespace & Co. .............................
3.6.1 Schema .........................................................................
3.6.2 Tablespace ....................................................................
3.6.3 Auswirkungen auf die Architektur einer Applikation.......
Datenbankobjekte .....................................................................
3.7.1 Tabellen ........................................................................
3.7.2 Index ............................................................................
3.7.3 Materialisierte Sichten ..................................................
3.7.4 PL/SQL-Konstrukte .......................................................
3.7.5 Sonstige Datenbankobjekte ...........................................
Exkurs: Zeichensatzkodierung ....................................................
3.8.1 Zeichensatzkodierung im Überblick ...............................
3.8.2 Zeichensatzkodierung bei Oracle ...................................
66
67
67
67
68
69
70
72
78
79
81
84
87
88
93
96
99
99
105
113
115
115
120
120
122
Jedes Datenbankmanagementsystem ist anders implementiert. Obwohl viele Anwender hoffen , alle Datenbanken seien gleich und können als homogene Datenpumpen betrachtet werden, unterscheiden sie sich grundlegend und fundamental. Kaum ein Bereich der Implementierungsunterschiede ist so ausgeprägt und gleichzeitig so wichtig wie der Bereich der Datensicherheit,
der Datenkonsistenz und der Transaktion. Dieses Kapitel befasst sich mit der Implementierung dieser Strategien in Oracle. 127
4
Datensicherheit, -konsistenz und Transaktion ...................... 127
4.1
4.2
4.3
6
Lese- und Schreibkonsistenz ......................................................
4.1.1 Lesekonsistenz ..............................................................
4.1.2 Schreibkonsistenz ..........................................................
Transaktion ................................................................................
4.2.1 Transaktion zum Schutz der Lesekonsistenz ...................
4.2.2 Transaktion zur Definition eines Geschäftsvorfalls...........
4.2.3 Zusammenfassung .........................................................
Datenkonsistenz und referenzielle Integrität ..............................
4.3.1 Datenintegrität .............................................................
4.3.2 Performanzüberlegungen zu Datenbank-Constraints ......
4.3.3 Datenkonsistenz ...........................................................
4.3.4 Zusammenfassung .........................................................
128
128
132
132
133
135
136
137
138
143
146
150
1452.book Seite 7 Donnerstag, 5. August 2010 3:55 15
Inhalt
4.4
4.5
4.6
4.7
4.8
Explizites Sperren von Daten durch die Anwendung ..................
4.4.1 Das Problem: Lost Updates ...........................................
4.4.2 Das optimistische Sperren .............................................
4.4.3 Das pessimistische Sperren ............................................
4.4.4 Do it the Oracle way: das vorsichtig optimistische
Sperren .........................................................................
4.4.5 Und nun? Wann sollte welche Sperrstrategie
verwendet werden? ......................................................
Verarbeitung einer SQL-Anweisung ...........................................
4.5.1 Parsen und Optimierung ...............................................
4.5.2 Datenlieferung über Cursor ...........................................
Die Sperrmechanismen von Oracle ............................................
4.6.1 Locks ............................................................................
4.6.2 Latches .........................................................................
Datensicherheit .........................................................................
Beispiel zum Einfluss der Programmierung .................................
4.8.1 Das Ziel unserer Programmierung ..................................
4.8.2 Implementierung des Tests ...........................................
150
151
153
155
156
157
158
159
164
165
165
166
166
169
170
171
Ist die Datenbank eine Datenpumpe? Viele Anwendungen betrachten die Datenbank lediglich als Lieferant für Daten, die von der Anwendung konsumiert werden, und als Speicher für die Anwendungsdaten. Zentrale Konzepte wie die Datenkonsistenz oder die Datensicherheit gehen bei diesem Ansatz verloren, ebenso wie der Großteil der ansonsten möglichen Performanz.
Sehen wir uns also die Datenbank im Kontext der Anwendung genauer an. 181
5
Die Datenbank in der Anwendungsarchitektur ..................... 181
5.1
5.2
5.3
5.4
Das Problem des Impedance Mismatch .....................................
5.1.1 Das Problem der Identität .............................................
5.1.2 Das Problem der Datensuche ........................................
5.1.3 Das Problem der Lesestrategie ......................................
5.1.4 Das Problem der Vererbung ..........................................
5.1.5 Das Problem der Kopplung von Logik und Daten ...........
5.1.6 Das Problem der referenziellen Integrität ......................
Lösungsansatz 1: Die Vision der generischen Datenbank ............
5.2.1 Generisches SQL ...........................................................
5.2.2 Generisches Abfragewerkzeug .......................................
5.2.3 Die Kosten generischer Datenbankprogrammierung .......
Lösungsansatz 2: Objektrelationale Mappingwerkzeuge .............
5.3.1 Abbildung der Objekte auf Tabellen ..............................
5.3.2 Kapselung der SQL-Dialekte ..........................................
5.3.3 Caching-Mechanismen, Lazy Load .................................
5.3.4 Transaktionsverwaltung .................................................
5.3.5 Zusammenfassung .........................................................
Lösungsansatz 3: Die Datenbank als Datenframework ................
5.4.1 Performanz der Datenbearbeitung ................................
5.4.2 Sicherheitsdomäne ........................................................
5.4.3 Integration von PL/SQL und SQL ...................................
183
183
184
186
188
191
192
193
193
197
198
200
200
201
201
201
202
203
204
205
208
7
1452.book Seite 8 Donnerstag, 5. August 2010 3:55 15
Inhalt
5.5
Mächtigkeit von SQL .................................................................
5.5.1 Analytische Funktionen .................................................
5.5.2 Hierarchische Abfragen .................................................
5.5.3 Error Logging ................................................................
5.5.4 Fazit ..............................................................................
209
210
212
216
221
Die Oracle-Datenbank kann von PL/SQL-Programmen in vielerlei Bereichen profitieren. Bevor wir uns der Sprache und ihrer Syntax zuwenden, sehen wir uns an, für welche Einsatzszenarien PL/SQL normalerweise verwendet wird. 223
6
Programmierung der Datenbank ........................................... 223
6.1
6.2
6.3
6.4
6.5
Erweiterung der Datenbankfunktionalität ..................................
Programmierung der Datenkonsistenz .......................................
6.2.1 Datenbanktrigger ..........................................................
6.2.2 Datenzugriff über PL/SQL ..............................................
6.2.3 Datenkonsistenz jenseits referenzieller Integrität ............
Programmierung der Datensicherheit .........................................
Anwendungsprogrammierung mit PL/SQL .................................
6.4.1 PL/SQL auf der Clientseite .............................................
6.4.2 Webanwendungen mit PL/SQL entwickeln ....................
Unterstützung der Administration durch PL/SQL ........................
6.5.1 Einsatz von PL/SQL in Skripten ......................................
6.5.2 Verwaltung wiederkehrender Aufgaben mit Scheduler
und Jobs .......................................................................
6.5.3 Datenbanktrigger im Umfeld der Datensicherung
und des Auditings .........................................................
223
225
225
229
232
233
236
237
238
242
243
244
245
TEIL II Die Sprache PL/SQL
Genug der Vorbereitung: Nun geht es an die Definition der Sprache
PL/SQL und an die Strukturen, die Sie kennen müssen, um eigene Programme entwerfen zu können. Dieses Kapitel führt zunächst in die grundlegenden Strukturen ein. Sie lernen die Blockstruktur sowie die wichtigsten Anweisungen von PL/SQL kennen. 249
7
Die Blockstruktur und Syntax von PL/SQL ............................ 249
7.1
7.2
8
Vom anonymen Block zum Package ...........................................
7.1.1 Das Grundgerüst: Der PL/SQL-Block .............................
7.1.2 Prozeduren ...................................................................
7.1.3 Funktionen ...................................................................
7.1.4 Datenbanktrigger ..........................................................
7.1.5 Packages .......................................................................
7.1.6 Ausführungsrechte von PL/SQL-Blöcken
(AUTHID-Klausel) .........................................................
7.1.7 Kompileranweisungen (PRAGMA-Klausel) ....................
7.1.8 Best Practices ...............................................................
PL/SQL-Datentypen ...................................................................
7.2.1 SQL-Datentypen ...........................................................
7.2.2 Basistypen und Subtypen in PL/SQL ..............................
250
250
256
266
269
272
276
279
281
282
282
284
1452.book Seite 9 Donnerstag, 5. August 2010 3:55 15
Inhalt
7.2.3
7.3
7.4
7.5
7.6
SQL-Datentypen mit abweichender Definition
in PL/SQL ......................................................................
7.2.4 SQL-Datentypen, die in PL/SQL nicht existieren.............
7.2.5 PL/SQL-Datentypen, die in SQL nicht existieren.............
7.2.6 Benutzerdefinierte Datentypen .....................................
7.2.7 Ableitung von Variablentypen aus dem Data
Dictionary .....................................................................
Kontrollstrukturen .....................................................................
7.3.1 Bedingte Anweisung 1 (IF-THEN-ELSE-Anweisung) ........
7.3.2 Bedingte Anweisung 2 (CASE-Anweisung) .....................
7.3.3 Konditionale Kompilierung ...........................................
7.3.4 Einfache Schleifen (LOOP-Anweisung) ..........................
7.3.5 Abweisende Schleife 1 (FOR-LOOP-Anweisung) ............
7.3.6 Abweisende Schleife 2 (WHILE-LOOP-Anweisung) .......
7.3.7 Best Practices ................................................................
7.3.8 Aus der Mottenkiste: Konzepte, die Sie nicht
verwenden sollten .........................................................
Kollektionen in PL/SQL ..............................................................
7.4.1 Record ..........................................................................
7.4.2 Assoziative Tabellen ......................................................
7.4.3 Cursor ...........................................................................
Dynamisches SQL ......................................................................
7.5.1 Natives dynamisches SQL
(Execute-Immediate-Anweisung) ...................................
7.5.2 Dynamisches SQL mit Cursorvariablen ...........................
7.5.3 DBMS_SQL-Package .....................................................
7.5.4 Sicherheit bei dynamischem SQL ...................................
Objektorientierte Datentypen ....................................................
7.6.1 VARRAY .......................................................................
7.6.2 Geschachtelte Tabellen (nested tables) ..........................
286
287
287
288
288
291
291
292
295
300
301
303
304
307
310
310
314
316
324
325
328
330
333
337
337
341
Triggerprogrammierung ist ein komplexes, manchmal etwas hakeliges Thema. Neben den unbestreitbaren Vorteilen von Triggern lauern einige logische Stolperfallen sowie da und dort einige nicht ganz intuitive Einschränkungen. Grund genug, Trigger einmal etwas genauer anzusehen. 345
8
Events in der Datenbank: Programmierung von Triggern ..... 345
8.1
8.2
DML-Trigger ..............................................................................
8.1.1 Anweisungs- versus Zeilentrigger ..................................
8.1.2 Wann wird ein Trigger ausgelöst? ..................................
8.1.3 Das Mutating-Table-Problem ........................................
8.1.4 Compound-Trigger ........................................................
8.1.5 Instead-Of-Trigger ........................................................
Datenbank-Trigger .....................................................................
8.2.1 Ereignisattribute ............................................................
345
347
351
353
355
357
359
360
9
1452.book Seite 10 Donnerstag, 5. August 2010 3:55 15
Inhalt
8.3
8.4
8.2.2 Datenbankereignisse .....................................................
8.2.3 Benutzerbezogene Ereignisse ........................................
Einsatzbereiche von Triggern .....................................................
8.3.1 Datenintegrität durchsetzen ..........................................
8.3.2 DML-Ereignisse, die von Triggern überwacht werden.....
8.3.3 Auditierung mithilfe von Triggern ..................................
8.3.4 Historisierung von Daten ...............................................
8.3.5 Trigger und Datensichten (INSTEAD-OF-Trigger)............
8.3.6 DDL-Ereignisse .............................................................
8.3.7 System-Ereignisse ..........................................................
Zusammenfassung .....................................................................
362
364
367
367
369
375
377
381
389
391
391
Die zentrale Aufgabenstellung für die Programmierung von PL/SQL ist der Umgang mit den Daten der Datenbank. PL/SQL ist für diese Aufgabe besser gerüstet als jede andere Programmiersprache im Oracle-Umfeld, denn als Erweiterung zu SQL setzt sie direkt auf der Datenbanksprache auf. Grund genug, diese Vorteile zu nutzen! 395
9
Das Arbeiten mit Daten ......................................................... 395
9.1
9.2
9.3
Strukturierte Variablen: Records und Typen ...............................
9.1.1 Bindung an das Data Dictionary mit »%TYPE« und
»%ROWTYPE« ..............................................................
9.1.2 Insert- und Update-Anweisungen mit Records ...............
9.1.3 Verwendung explizit deklarierter Records .....................
9.1.4 Verwendung der Returning-Klausel mit Records ............
9.1.5 Alternative zum Record: Objekt ....................................
PL/SQL-Kollektionen .................................................................
9.2.1 Verwendung von assoziativen Tabellen .........................
9.2.2 Massenverarbeitung mit assoziativen Tabellen ...............
9.2.3 Tabellenfunktionen (PIPELINED-Functions) ...................
Mengenverarbeitung mit Cursorn ..............................................
9.3.1 Implizite versus explizite Cursor ....................................
9.3.2 Top-N-Analyse ..............................................................
9.3.3 Cursorvariablen (REF-Cursor) .........................................
9.3.4 Cursor-Ausdrücke .........................................................
395
395
398
401
404
406
407
407
409
415
423
423
429
432
435
PL/SQL-Code wird in Packages organisiert. Doch über das reine Organisieren von Code hinaus bieten Packages einen erheblichen Mehrwert, der die Programmierung in PL/SQL erst zu ihrer vollen Leistungsfähigkeit führt. Dieses Kapitel beleuchtet Strategien und Möglichkeiten des Einsatzes von Packages und wirft einen Blick auf die mitgelieferten Packages von Oracle. 441
10 Packages ................................................................................. 441
10.1
10.2
10
Warum sollten Packages genutzt werden? .................................
10.1.1 Trennung von öffentlicher und privater Logik ................
10.1.2 Überladung in Packages ................................................
10.1.3 Packages und die Abhängigkeitskette ............................
10.1.4 Verschlüsselung von Package-Code ...............................
Oracle-Packages ........................................................................
10.2.1 Das Package »standard« ................................................
10.2.2 Wichtige Oracle-Packages .............................................
441
442
452
458
464
470
473
474
1452.book Seite 11 Donnerstag, 5. August 2010 3:55 15
Inhalt
Die Fehlerbehandlung ist ein zentraler, aber nicht eben beliebter Teil jeder Programmiersprache. Wir beschäftigen uns selbstverständlich dennoch mit der Fehlerbehandlung und zeigen die Möglichkeiten der Sprache PL/SQL auf. 483
11 Fehlerbehandlung ................................................................... 483
11.1
11.2
11.3
11.4
Oracle-Fehler .............................................................................
11.1.1 Benannte Fehler ............................................................
11.1.2 »SQLERRM« und »SQLCODE«-Funktionen und der
Fehlerstack ....................................................................
11.1.3 Nicht benannte Fehler benennen ..................................
Applikationsfehler erstellen und bearbeiten ...............................
11.2.1 Fehler direkt mit RAISE_APPLICATION_ERROR
erzeugen .......................................................................
11.2.2 Fehler aus einem Fehlerpackage erstellen lassen.............
11.2.3 Zentralisierung der Fehlermeldungen über »LMSGEN« .....
Zentralisierter Fehlerhandler mit einem Trigger ..........................
Zusammenfassung .....................................................................
483
489
491
494
495
495
496
498
505
510
TEIL III PL/SQL im Einsatz
Die Erweiterung von SQL ist das natürliche Anwendungsgebiet von PL/SQL. Wir überlegen, wann eine Erweiterung durch PL/SQL sinnvoll ist und welcher Preis dafür gezahlt werden muss, und wir schrecken auch vor fortgeschrittenen Themen nicht zurück. 513
12 Erweiterung von SQL ............................................................. 513
12.1
12.2
12.3
12.4
12.5
Wann SQL erweitert werden sollte ............................................
12.1.1 Bleiben Sie auf dem aktuellen Wissensstand! ................
12.1.2 Voraussetzungen für die Erweiterung von SQL ...............
SQL mit eigenen Funktionen erweitern ......................................
12.2.1 Anforderungen an den PL/SQL-Block ............................
12.2.2 Nebenwirkungsfreiheit (Purity) ......................................
12.2.3 Optimizer Hints ............................................................
12.2.4 Das Pragma »restrict_references« ..................................
12.2.5 Beispielfunktion ............................................................
Code-Beispiel: Berechnung der Fakultät .....................................
Gruppenfunktionen selbst erstellen ...........................................
12.4.1 Arbeitsweise von Gruppenfunktionen ...........................
12.4.2 Beispiel .........................................................................
12.4.3 Test der Gruppenfunktion .............................................
12.4.4 Zusammenfassung .........................................................
Code-Beispiel: Codegenerator für Gruppenfunktionen ...............
513
514
517
519
519
520
520
521
522
528
538
539
542
547
548
549
Oracle unterstützt die Speicherung und Bearbeitung von großen Binär- und Textdateien. Die Arbeit mit diesen Datentypen hat sich zwar der Verarbeitung normaler Zeichenketten oder Raw-Daten angeglichen, doch gibt es immer noch Unterschiede, die beachtet werden müssen.
Dieses Kapitel führt in die Bearbeitung dieser Datenstrukturen ein. 559
13 Arbeiten mit großen Datenstrukturen ................................... 559
13.1
Technische Struktur ................................................................... 560
13.1.1 Einsatz von LOB-Datentypen in der Datenbank.............. 560
13.1.2 LOBs als PL/SQL-Variablen ............................................ 565
11
1452.book Seite 12 Donnerstag, 5. August 2010 3:55 15
Inhalt
13.2
13.3
13.4
13.1.3 LOBs als Methodenparameter .......................................
13.1.4 Secure Files ...................................................................
Die Datentypen CLOB, NCLOB, BLOB und BFILE .......................
13.2.1 CLOB und NCLOB .........................................................
13.2.2 Der binäre Datentyp BLOB ............................................
13.2.3 BFILE ............................................................................
Das Package DBMS_LOB ...........................................................
13.3.1 Schreibzugriff auf temporäre oder persistente LOBs .......
13.3.2 Verwaltung temporärer und persistenter LOBs ...............
13.3.3 API für Bfile-LOBs .........................................................
Hilfsfunktionen zum Arbeiten mit LOBs .....................................
13.4.1 Hilfsfunktion zum Laden von CLOBs und BLOBs aus
dem Dateisystem in die Datenbank ...............................
13.4.2 Hilfsfunktion zum Lesen von CLOBs und BLOBs aus
der Datenbank ..............................................................
571
572
575
575
576
576
578
579
581
583
584
584
592
XML ist eine Kerntechnologie zur Übermittlung von Daten zwischen Systemen, zur Speicherung umfangreicher Dokumente und als Bindeglied zwischen objektorientierter und relationaler Datenhaltung und -verarbeitung. Dieses Kapitel beleuchtet die umfangreiche Unterstützung des Standards durch die verschiedenen Oracle-Technologien. 603
14 Arbeiten mit XML ................................................................... 603
14.1
14.2
14.3
14.4
14.5
14.6
12
Der Datentyp »XMLType« ..........................................................
14.1.1 Verwendung von »XMLType« als Tabellen- oder
Spaltentyp ....................................................................
14.1.2 »XMLType«-Methoden .................................................
Die Speicherung von XML-Daten in der Datenbank ...................
XML aus relationalen Daten erzeugen ........................................
14.3.1 Der SQL/XML-Standard ................................................
14.3.2 Das Package »dbms_xmlgen« ........................................
Relationale Daten aus XML extrahieren .....................................
XML mit PL/SQL verarbeiten .....................................................
14.5.1 Die Programmierung von XML ......................................
14.5.2 Die XML-Packages ........................................................
Die XML-Datenbank ..................................................................
14.6.1 Einführung in die XML-Datenbank ................................
14.6.2 Speicherung und Veröffentlichung binärer und
XML-Dokumente ..........................................................
14.6.3 Dokumente über XDB verwalten ...................................
14.6.4 Zugriffsschutz und Sicherheit der XDB ...........................
14.6.5 Versionierung von Ressourcen ......................................
606
606
611
613
615
615
621
631
638
638
640
651
652
654
659
670
676
1452.book Seite 13 Donnerstag, 5. August 2010 3:55 15
Inhalt
Kaum eine Funktionalität der Oracle-Datenbank hat so kontroverse Diskussionen ausgelöst wie die Integration objektorientierter Techniken. In diesem Kapitel zeige ich die Vor- und Nachteile dieser Techniken auf und stelle Ihnen sinnvolle Einsatzgebiete vor. 681
15 Objektorientierung ................................................................ 681
15.1
15.2
15.3
15.4
Einführung in die Objektorientierung .........................................
15.1.1 Alles ist ein Objekt ........................................................
15.1.2 Das zweite Reizwort: Vererbung! ..................................
15.1.3 Abstrakte und finale Klassen .........................................
15.1.4 Statische Methoden ......................................................
15.1.5 Objektidentität versus »Statement of Truth« ..................
15.1.6 Klassen haben komplexe Strukturen ..............................
15.1.7 Auswirkungen auf die Datenbankprogrammierung .........
Typen ........................................................................................
Anwendungsbeispiel: Der Datentyp »MoneyType« ....................
15.3.1 Vorüberlegungen ..........................................................
15.3.2 Implementierung des »MoneyType« ..............................
15.3.3 Der Typkörper ...............................................................
15.3.4 Implementierung des Packages »coa_money« ................
15.3.5 Der Package-Körper ......................................................
15.3.6 Die Rechtesituation in Version 11g ...............................
15.3.7 Erweiterung durch Vererbung .......................................
Best Practices ............................................................................
683
684
686
687
688
689
691
693
695
697
697
699
701
705
707
718
721
723
Dieses Kapitel beschäftigt sich mit der Integration von Oracle-Datenbanken in Anwendungen. Es wird uns hauptsächlich um strategische Fragen gehen, wie etwa die, welche Aufgaben wo erledigt werden sollten. Aber auch Techniken zur Integration kommen nicht zu kurz. 725
16 Integration von Oracle in Applikationen ............................... 725
16.1
16.2
16.3
16.4
16.5
Sperrung von Daten bei der Datenänderung ..............................
16.1.1 Pessimistisches Locking .................................................
16.1.2 Optimistisches Locking .................................................
16.1.3 Database-Change-Notification-basiertes Locking............
Zugriff auf Daten über PL/SQL-Packages ....................................
16.2.1 Kapselung von DML-Operationen in Packages ...............
16.2.2 Vermeidung von Triggern durch Packages .....................
16.2.3 Integration datenbezogener Geschäftsregeln .................
Zugriff auf Daten über das Web .................................................
16.3.1 Veröffentlichung von Packages über HTTP ....................
16.3.2 Schreiben von Daten in einen HTTP-Stream ..................
16.3.3 Webservices aus PL/SQL ...............................................
Gemeinsamer Zugriff auf Daten über verteilte Cursor .................
16.4.1 Prozeduren mit »REF_CURSOR«-Parametern .................
16.4.2 Arbeit mit LOBs ............................................................
Zusammenfassung und Bewertung .............................................
726
727
733
741
743
744
745
746
748
749
749
750
751
751
752
753
13
1452.book Seite 14 Donnerstag, 5. August 2010 3:55 15
Inhalt
TEIL IV Workshops
In diesem Workshop stelle ich Ihnen eine praktische Umsetzung der Empfehlungen vor, die ich Ihnen bezüglich der Nutzung der Datenbank als Datenframework gegeben habe. Wir werden eine Grundlage erarbeiten, die Sie als Keimzelle eigener Datenbankanwendungen verwenden können. 757
17 Workshop 1: Die Keimzelle sicherer
Datenbankanwendungen ....................................................... 757
17.1
17.2
17.3
17.4
17.5
Das Projekt ................................................................................
17.1.1 Übersicht über die Architektur ......................................
17.1.2 Die Logon-Prozedur ......................................................
Aufsetzen der Schemata ............................................................
Die Packages .............................................................................
Test der Architektur ...................................................................
Zusammenfassung und Ausblick .................................................
757
758
761
762
766
772
774
Zum Abschluss des Buches möchte ich mit Ihnen nun ein größeres Projekt besprechen, das für Ihre Projekte vielleicht wiederverwendbar ist. Es handelt sich um ein Package zum Logging und zur Fehlerbehandlung. 777
18 Workshop 2: Ein Logging-Package ........................................ 777
18.1
18.2
18.3
18.4
18.5
14
Überblick: Die Idee und die Architektur .....................................
18.1.1 Meldung .......................................................................
18.1.2 Kontext .........................................................................
18.1.3 Ausgabemodule ............................................................
18.1.4 Parameter- und Meldungstabelle ..................................
18.1.5 Meldungspackage .........................................................
Umsetzung des Logging-Packages ..............................................
18.2.1 Die Parametertabelle ....................................................
18.2.2 Die Meldungstabelle .....................................................
18.2.3 Das Meldungsobjekt .....................................................
18.2.4 Das Grundmodul ...........................................................
18.2.5 Kontext .........................................................................
18.2.6 Parameterliste ...............................................................
Implementierung des Logging-Packages ....................................
18.3.1 Die Package-Spezifikation des Logging-Packages............
18.3.2 Der Package-Körper des Logging-Packages ...................
18.3.3 Test des Logging-Packages ............................................
Implementierung des Log-Administrations-Packages .................
18.4.1 Funktionsüberblick und Implementierungsstrategie........
18.4.2 Implementierung der Log-Administration-PackageSpezifikation .................................................................
18.4.3 Implementierung des Log-AdministrationPackage-Körpers ...........................................................
Weitere Ausgabemodule ...........................................................
18.5.1 Ausgabe in eigene Fehlerdateien ...................................
18.5.2 Ausgabe in Alert-Log- oder Trace-Dateien ....................
778
780
781
782
784
789
790
790
791
794
800
808
809
811
811
814
827
828
828
830
832
846
847
855
1452.book Seite 15 Donnerstag, 5. August 2010 3:55 15
Inhalt
18.5.3 Ausgabe in Logging-Tabellen ........................................ 856
18.5.4 Meldung als E-Mail versenden ...................................... 857
18.5.5 Meldungen in JMS integrieren ...................................... 859
Index .......................................................................................................... 865
15
1452.book Seite 17 Donnerstag, 5. August 2010 3:55 15
Die Oracle-Datenbank ist eines der mächtigsten und umfangreichsten
relationalen Datenbanksysteme. Schon SQL ist ungeheuer vielseitig einsetzbar. Doch erst mit PL/SQL erschließt sich das gesamte Potenzial.
Daher ist die Kenntnis von PL/SQL für jeden, der sich mit Oracle
beschäftigt, essenziell.
1
Einführung
PL/SQL ist keine Programmiersprache.
Das mag zunächst verwunderlich klingen, schließlich bedeutet PL/SQL Procedural Language for SQL, also eben das Gegenteil des Einleitungssatzes. Doch schon
im ersten Satz von Oracle zur Einführung in PL/SQL heißt es: PL/SQL ist eine prozedurale Erweiterung von SQL. Dieser feine Unterschied ist sehr wichtig, auch für
dieses Buch: Ein Buch über PL/SQL ist nicht komplett ohne ein Buch über SQL.
Sollten Sie also ein Neueinsteiger in die Welt der Oracle-Datenbanken sein, sollte
Ihr erstes Augenmerk auf der Abfragesprache SQL liegen, nicht auf PL/SQL. Die
syntaktischen Grundlagen von PL/SQL, die Namenskonventionen, aber auch die
überwältigende Mehrheit der Funktionen in PL/SQL kommen aus SQL. Das vorweggenommen, ist PL/SQL aber auch eine faszinierende Technologie, mit deren
Hilfe Sie der Datenbank die mächtige Funktionalität abgewinnen können, die Sie
sonst nur in sehr wenigen, anderen Datenbanken finden. Mit diesem Buch
möchte ich Sie in die Sprache PL/SQL einführen und Ihnen erläutern, wie Sie aus
der Datenbank maximalen Nutzen für Ihre Anwendung ziehen können.
1.1
Für wen ist dieses Buch geschrieben?
Nun, ganz eindeutig für Sie. Damit meine ich, dass die Konzeption dieses Buches
natürlich kein Selbstzweck, sondern dem Ziel unterworfen ist, Ihnen zu erklären,
wie und wofür PL/SQL einzusetzen ist, und eben nicht dem Ziel, einfach nur ein
Buch über PL/SQL zu schreiben, das lediglich alle wichtigen Merkmale der Sprache auflistet. Daher habe ich mir zu Beginn Gedanken darüber gemacht, wer Sie
eigentlich sind. Wer liest ein solches Buch? Ich stelle mir vor, dass Sie beruflich
bereits mit Datenbanken zu tun hatten, auch schon programmiert haben und nun
Ihre Kenntnisse und Fähigkeiten um PL/SQL erweitern möchten oder müssen.
17
1452.book Seite 18 Donnerstag, 5. August 2010 3:55 15
1
Einführung
Vielleicht sind Sie ein Administrator, der sich in die Programmierung von Triggern einarbeiten oder PL/SQL-Programme zur Erleichterung seiner Arbeit verfassen muss? Vielleicht sind Sie aber auch ein Anwendungsentwickler, der bislang
in einer der Programmiersprachen für Anwendungsentwicklung programmiert
hat und nun, freiwillig oder aufgrund von Performanzproblemen gezwungen,
einen näheren Blick in die Datenbankinterna werfen will? Vielleicht sind Sie der
Betreuer einer Software, die von anderen geschrieben wurde (vielleicht von Vorgängern im Amt) und Ihnen nun zur weiteren Betreuung überlassen wurde. Sind
Sie ein absoluter Neuling in Datenbanken und wollen den Einstieg über PL/SQL
erreichen? Nein, das glaube ich eigentlich nicht. Daher richte ich mein Buch an
meiner Erwartung an Sie aus.
Dieses Buch richtet sich einerseits an Leser, die SQL-Kenntnisse haben. PL/SQLBücher sind keine Einstiegsliteratur in Datenbanken, sondern setzen den Weg
fort, den Sie durch die Auseinandersetzung mit relationalen Datenmodellen und
SQL bereits begonnen haben. Ich setze nicht voraus, dass Ihre SQL-Kenntnisse
sehr tiefgehend sind, doch ein Grundverständnis dieser Abfragesprache sollte in
jedem Fall vorhanden sein. Dann gehe ich davon aus, dass Sie bereits in irgendeiner Programmiersprache programmiert haben. Ich kann mir einfach nicht recht
vorstellen, dass Sie das Programmieren gerade innerhalb einer Datenbank lernen
möchten (oder müssen). Normalerweise, das lehrt mich auch die Erfahrung aus
den vielen Kursen, die ich zum Thema PL/SQL-Einführung gegeben habe, ist die
Programmierung der Datenbank ein Thema für Menschen, die bereits Programmiererfahrung in anderen Sprachen, sei es Cobol, C, C++, Java oder VisualBasic,
haben. Daher möchte ich Sie nicht langweilen und erläutere die Grundlagen
einer If-Anweisung nur sehr kurz.
Sollten Sie eine der beiden Voraussetzungen, die ich hier genannt habe, nicht
mitbringen, empfehle ich Ihnen, sich zunächst mit diesen Themen zu beschäftigen. Insbesondere gilt diese Empfehlung für die Beschäftigung mit der Abfragesprache SQL. Je besser Sie diese Sprache beherrschen, umso leichter fällt es
Ihnen, Aufgabenstellungen in Bezug auf Daten einer Datenbank zu lösen. Und
auch das gilt: Je besser Sie SQL beherrschen, umso seltener müssen Sie zu einer
Programmiersprache greifen, um ein Problem zu lösen. Das ist leider auch auf
der Zeitachse wahr: Sollten Sie sich sehr gut mit dem SQL der Oracle-Datenbankversion 7.3.4 auskennen, werden Sie staunen, was seit dieser Zeit in SQL an
Fähigkeiten hinzugekommen ist. Die Beschäftigung mit SQL ist immer die
Beschäftigung mit SQL in der Datenbankversion Ihrer Datenbank. Für diejenigen
unter Ihnen, die noch nicht programmiert haben, glaube ich, wird der Stoff relativ schnell und wenig didaktisch vorangehen. Vielleicht sollten Sie ins Auge fassen, die Programmierung vorab zu erlernen. Auch hier geht es weniger darum,
18
1452.book Seite 19 Donnerstag, 5. August 2010 3:55 15
Für wen ist dieses Buch geschrieben?
Experte in einer Programmiersprache zu sein, sondern um ein generelles Verständnis der Vorgehensweise beim Programmieren. Diese Einführung kann dieses Buch nicht leisten.
Darüber hinaus muss ich eine – für mich sehr schwierige – Entscheidung treffen:
Werden Sie als meine Leser eher Entwickler sein, Endanwender oder Administratoren? Welcher konkrete Einsatzzweck liegt Ihrem Interesse an PL/SQL
zugrunde? Diese Entscheidung ist nicht leicht. Ich habe mich dafür entschieden,
aus dem Blickwinkel des Entwicklers und des Administrators zu schauen; der
Schwerpunkt liegt allerdings auf dem Blickwinkel des Entwicklers von Anwendungssoftware. Aus dieser Entscheidung resultieren mehrere Konsequenzen:
왘 Ich gehe davon aus, dass die meisten Anwendungsentwicklungen (insbesondere neu aufgesetzte Projekte) nicht mehr nur in einer Technologie allein entwickelt werden. Die aktuellen Architekturentscheidungen mit Webanwendungen, Applikationsservern und Datenbankservern lassen eine Entwicklung
in einer Programmiersprache eigentlich auch nicht zu. Daher widme ich einigen Raum dieses Buches auch den Problemen der Integration von OracleDatenbanken in Anwendungen, die in anderen Programmiersprachen entwickelt werden.
왘 Ich gehe weiterhin davon aus, dass Datenbanken (nicht nur Oracle, sondern
Datenbanken generell) vielen Anwendungsentwicklern nur als Datenspeicher
geläufig sind und die Interna der Arbeitsweise ihnen nicht immer komplett
bekannt sind. Daher gebe ich eine ausführliche Einführung in die Arbeitsweise von Oracle-Datenbanken. Diese Einführung wird für Administratoren
bekannt und eventuell etwas oberflächlich sein, ist aus meiner Sicht für Entwickler aber unbedingt von Interesse, um zu verstehen, warum die Datenbank auf eine gewisse Weise programmiert werden will. Für Administratoren
schließlich sind Kenntnisse einiger Aspekte der Anwendungsprogrammierung
von unschätzbarem Wert, einfach, um zu verstehen, welche Problemstellungen durch Anwendungsentwickler in der Datenbank gelöst werden müssen.
Daher empfehle ich die Abschnitte, die sich mit der Entwicklung von Anwendungen und der Integration von Oracle-Datenbanken in solche Anwendungen
befassen, auch den Administratoren.
왘 Ich gehe schließlich davon aus, dass es wichtiger ist, zu erklären, warum etwas
getan werden muss, als wie etwas getan werden muss. Anders gesagt: Dieses
Buch ist keine Referenz zu PL/SQL, in der Sie im Index einen Befehl nachschlagen können und auf Seite 371 alle Parameter der Prozedur aufgelistet bekommen. Diese Funktion übernehmen die Online-Ressourcen, die bei Oracle auf
einem sehr hohen Niveau sind, wesentlich besser. Allein die Dokumentation
19
1.1
1452.book Seite 20 Donnerstag, 5. August 2010 3:55 15
1
Einführung
der mitgelieferten Packages umfasst bei der Datenbankversion 11gR2 stolze
5.744 Seiten … Nebenbei sind das über 600 Seiten mehr als zu Datenbankversion 11gR1, und das bringt mich zum ersten Grund für meine Entscheidung,
keine Referenz zu PL/SQL zu schreiben: Diese ist naturgemäß von der Version
der Datenbank abhängig und daher schon veraltet, bevor sie ausgeliefert wird.
Der zweite Grund ist, dass es mir ein besonderes Anliegen ist, Ihnen zu erklären, wie die Datenbank gut programmiert wird. Die Kunst besteht darin, der
Datenbank bei der Erfüllung ihrer Aufgaben möglichst nicht im Wege zu stehen. Das ist gar nicht so leicht und gelingt nur, wenn Sie wissen, was Sie tun.
Und genau dafür benötige ich Platz, den ich nicht durch Auflistungen von Prozedurparametern verschwenden wollte. Ich trete mit dem Ziel an, Ihnen zu
erklären, was Sie tun müssen und warum, um eine gute Anwendung oder
Anwendungsunterstützung zu erhalten. Messen Sie mich daran.
Außerdem ist dies ein Buch, in dem ich meine Meinung kund tue. Vielleicht
sollte ich so etwas nicht tun, und vielleicht reizt dies zum Widerspruch. Ich habe
mich dennoch entschieden, meine persönliche Meinung darüber mitzuteilen,
wie eine Datenbank, speziell eine Oracle-Datenbank, programmiert werden
sollte. Die Meinung basiert auf meiner Berufserfahrung, in der ich viel Code gesehen habe. Aufgrund meines Berufes komme ich vor allem zu Kunden, deren
Code nicht oder nicht gut genug läuft. Daher sehe ich viel Code so, wie er nicht
geschrieben werden sollte. Meine Meinung reflektiert dabei die Erfahrungen, die
ich in diesen Projekten gesammelt habe, aber natürlich auch den Input, den ich
von vielen sehr guten Kollegen und Autoren eingesogen habe. Trotzdem mögen
Sie über die Ausrichtung von Software anderer Meinung sein als ich. Vielleicht
arbeiten Sie in einem Team aus erfahrenen PL/SQL-Entwicklern, die Ihnen sagen,
dass das, was ich hier schreibe, gut und schön, im konkreten Unternehmensumfeld aber nicht umsetzbar sei. Ich bin weit davon entfernt, hier Einspruch anzumelden. Natürlich gibt es viele, gute Gründe für eine Position. Es gibt auch viele
richtige Positionen. Allerdings gibt es noch mehr falsche Positionen. Gerade am
Anfang ist es aber schwer, falsche von richtigen Positionen zu unterscheiden. Da
jedoch gerade am Anfang eines Projekts Entscheidungen zugunsten oder zuungunsten einer Strategie fallen, die später nur schwer korrigierbar sind, empfinde
ich es als richtig, auch in einem Einführungsbuch bereits eine richtige Position zu
beziehen und Sie nicht mit dieser Einschätzung allein zu lassen. Sind Sie anderer
Meinung, ist das natürlich kein Problem. Sie sollten lediglich eine begründete
Meinung vertreten (können). Und schließlich: Ist es gerade eine Position, die ich
vertrete, die Sie zum Widerspruch reizt und dazu anregt, eine eigene Position zu
entwickeln, umso besser!
20
1452.book Seite 21 Donnerstag, 5. August 2010 3:55 15
Der Aufbau des Buches
1.2
Der Aufbau des Buches
Das folgende Kapitel 2 liefert zunächst Grundinformationen zu den verwendeten
Werkzeugen und Ressourcen; dann beginnen die vier großen Teile, in die dieses
Buch gegliedert ist. Diese Teile folgen dem Gedanken, dass ich zunächst die
Grundlagen, sowohl der Datenbank als auch der Programmiersprache PL/SQL,
besprechen möchte. Danach folgt ein Teil, der sich mit der Anwendung von PL/
SQL in konkreten Einsatzszenarien auseinandersetzt und weitergehende, technologische Konzepte erläutert. Den Abschluss bildet schließlich ein Teil mit konkreten Anwendungen in einer Form, die als Keimzelle für Ihre eigenen Projekte
dienlich sein könnte.
1.2.1
Teil 1: Grundlagen
Dieser Teil hat auf den ersten Blick eigentlich wenig mit PL/SQL zu tun. Erläutert
werden der Aufbau und die grundsätzliche Arbeitsweise der Oracle-Datenbank.
Aus meiner Erfahrung aus unzähligen Oracle-Schulungen weiß ich allerdings, dass
das Wissen um diese Strukturen nicht vorausgesetzt werden kann. Andererseits
ist es genau dieses Wissen, dass die meisten Empfehlungen der weiteren
Abschnitte begründet und Sie in die Lage versetzt, die Folgen Ihrer Programmierung besser abschätzen zu lernen. Woraus besteht eine Datenbank? Wie wird ein
Fremdschlüssel eigentlich durchgesetzt? Ist dieser Fremdschlüssel teuer? Solche
Fragen werden gestellt und beantwortet. Allerdings führt dieser Teil Sie noch
erheblich weiter, denn wir werden uns auch um Fragen der Konsistenz von Leseabfragen, um Transaktionsschutz und ähnliche Probleme kümmern. Die Kenntnis
dieser Dinge ist deshalb von fundamentaler Bedeutung für Sie als Entwickler, weil
diese Dinge in jeder Datenbank anders gehandhabt werden und massive Auswirkungen auf die Fehlerfreiheit und die Skalierbarkeit Ihrer Anwendungen haben.
Kapitel 3: Aufbau der Datenbank aus Sicht eines Programmierers
Dieses Kapitel beginnt den Rundblick mit einem Blick auf die Speicherstrukturen
der Datenbank, die Datenbankdateien und die zugeordneten Dateien wie etwa
die Parameter- und Passwortdateien. Die Strukturen werden so erläutert, dass Sie
eine Vorstellung von der Arbeitsweise bekommen und die Auswirkungen auf die
Programmierung abschätzen können, nicht aber so, dass Sie anschließend als
hauptamtlicher Administrator tätig werden können. Wir werden uns in diesem
Kapitel aber auch um die grundlegende Arbeit mit der Datenbank kümmern: um
das Anmelden und das Hoch- und Herunterfahren der Datenbank. Danach sehen
wir uns die Datenstrukturen an, mit denen Sie auf logischer Ebene zu tun haben:
Schema, Tablespace, die verschiedenen Datenbankobjekte. Den Abschluss macht
21
1.2
1452.book Seite 22 Donnerstag, 5. August 2010 3:55 15
1
Einführung
ein Exkurs in die Zeichensatzkodierung, die gerade im Zusammenhang mit
Datenbanken von großer Bedeutung ist und oft Probleme nach sich zieht.
Kapitel 4: Datensicherheit, -konsistenz und Transaktion
Dieses Kapitel ist ein absoluter Schwerpunkt bezogen auf das Wissen, das Sie über
Oracle haben müssen, wenn Sie Anwendungen gegen eine Oracle-Datenbank programmieren. Nirgendwo sonst sind die Implementierungsunterschiede zwischen
Datenbankmanagementsystemen so groß und die Auswirkungen auf die Programmierung so weitgehend. Wir werden in diesem Kapitel die Themen Schreib- und
Lesekonsistenz erläutern und uns um den Begriff der Transaktion kümmern.
Schließlich werfen wir einen genaueren Blick auf das Themenfeld Datenkonsistenz und referenzielle Integrität, denn diese Punkte sind das zentrale Gut, das es
in Ihrem Code zu verteidigen gilt. Anschließend betrachten wir das Sperrverfahren, das Oracle implementiert, um die Datenkonsistenz zu schützen, und sehen
uns an, auf welche Weise Ihre Anwendung aufgebaut sein muss, um diese zu erhalten. Zum Abschluss des Kapitels fragen wir uns, wie Oracle eine SQL-Anweisung
verarbeitet und diese Verarbeitung intern optimiert, und wir fragen uns, welche
Auswirkungen diese Betrachtungen auf Ihre Programmierstrategie haben.
Kapitel 5: Die Datenbank in der Anwendungsarchitektur
Dieses Kapitel nimmt insofern eine Sonderstellung ein, als es eine spezielle Zielgruppe hat: den Anwendungsentwickler mit Erfahrung in objektorientierten Programmiersprachen. Gerade aus der Kombination objektorientierter Programmiersprachen und relationaler Datenbanken ergibt sich eine Reihe schwer zu
lösender Probleme. Diese Probleme werden als Impedance Mismatch zusammengefasst und müssen gelöst werden, damit Ihre Anwendung stabil und sicher
arbeiten kann. In diesem Kapitel beleuchte ich das Problem und zeige die verschiedenen Lösungswege auf, die in der Industrie normalerweise für dieses Problem verwendet werden.
Kapitel 6: Programmierung der Datenbank
Dieses Kapitel fasst die verschiedenen Einsatzbereiche zusammen, in denen PL/
SQL innerhalb der Datenbank eingesetzt werden kann. Die Idee ist, Ihnen eine
Vorstellung von der Vielseitigkeit der Sprache zu geben. Oft ist man gefangen in
den Problemlösungen, die man kennt, obwohl es bessere Wege zur Lösung eines
Problems gibt. Ich werde Ihnen einige dieser Alternativen aufzeigen. Wir
beschäftigen uns in diesem Kapitel mit PL/SQL zur Erweiterung der Datenbankfunktionalität, zur Programmierung der Datenkonsistenz und -sicherheit, sehen
uns die Anwendungsprogrammierung mit PL/SQL an und fragen uns, auf welche
Weise PL/SQL den Datenbankadministrator unterstützt.
22
1452.book Seite 23 Donnerstag, 5. August 2010 3:55 15
Der Aufbau des Buches
1.2.2
Teil 2: Die Sprache PL/SQL
In diesem Teil beschäftigen wir uns mit der Sprache PL/SQL auf grundlegendem
Niveau. Es geht in diesem Teil darum, die Sprache syntaktisch vorzustellen und
zu zeigen, welches Instrumentarium PL/SQL zur Lösung von Problemen bietet.
Was ist das Besondere an PL/SQL? Welche Gemeinsamkeiten, aber auch Unterschiede gibt es zwischen PL/SQL und anderen Programmiersprachen?
Kapitel 7: Blockstruktur und Syntax von PL/SQL
In diesem Kapitel beginnen wir mit dem syntaktischen Aufbau von PL/SQL, seiner Blockstruktur, den Datentypen und Kontrollstrukturen. Dann sehen wir uns
die – naturgemäß sehr ausgebauten – Fähigkeiten der Datenbank in der Verwaltung von Datenmengen an. Es schließt sich eine Einführung in dynamisches SQL
und die objektorientierten Datentypen an. Mit diesem Instrumentarium haben
Sie die Einzelbausteine kennengelernt, aus denen PL/SQL-Programme bestehen.
Das Kapitel schließt mit einem Rundblick über die online verfügbare Dokumentation der Sprache, die Sie für die tägliche Arbeit benötigen, um sich über konkrete Funktionalitäten zu informieren.
Kapitel 8: Events in der Datenbank: Programmierung von Triggern
Trigger sind für viele Entwickler der Einstieg in die Programmierung von Datenbanken. Aber auch Administratoren haben oft mit dieser Art der Programmierung zu tun, um Geschäftsregeln durchzusetzen, Stammdatenänderungen zu protokollieren oder Datensicherheitsbestimmungen durchzusetzen. Dieses Kapitel
gibt einen Überblick über das – alles andere als triviale – Thema der Programmierung von Triggern. Wir beschäftigen uns mit »normalen« DML-Triggern, aber
auch mit DDL-Triggern und sehen uns mögliche Einsatzbereiche an.
Kapitel 9: Das Arbeiten mit Daten
Die Kernaufgabe von PL/SQL-Programmen ist die Verarbeitung von Daten der
Datenbank. Daher kommt den Mechanismen der Datenbearbeitung innerhalb
von PL/SQL eine besondere Bedeutung zu. Dieses Kapitel widmet sich ganz dieser Aufgabe und zeigt zunächst die strukturierten Datentypen, die PL/SQL anbietet, im Kontext dieser Aufgaben. Anschließend sehen wir uns spezialisierte Konstrukte von PL/SQL für diesen Einsatzzweck an, und Sie erlernen den korrekten
Umgang mit Cursorn in der Datenbank.
Kapitel 10: Packages
PL/SQL-Code wird in Packages organisiert. Neben der Organisation von Codeblöcken haben Packages aber noch weitergehende Funktionen, die in diesem Kapitel
23
1.2
1452.book Seite 24 Donnerstag, 5. August 2010 3:55 15
1
Einführung
beleuchtet werden. Wir sehen uns die Konzepte der Packages an und diskutieren
deren Auswirkungen auf die Praxis der Programmierung. Außerdem sehen wir
uns wichtige, mitgelieferte Oracle-Packages an, und ich gebe Ihnen Tipps, wie
und wo Sie sich über weitere Packages informieren können.
Kapitel 11: Fehlerbehandlung
Die Fehlerbehandlung in PL/SQL-Programmen wird in diesem Kapitel untersucht. Wir beginnen mit einer Betrachtung der grundsätzlichen Arbeitsweise der
Fehlerbehandlung und überlegen, welche Alternativen für die Definition und
Behandlung von Fehlern zur Verfügung stehen. Abschließend gebe ich Ihnen
einige Empfehlungen zum sinnvollen Umgang mit Fehlern.
1.2.3
Teil 3: PL/SQL im Einsatz
Der dritte Teil des Buches beschäftigt sich mit dem Einsatz von PL/SQL. War Teil 2
eher technisch orientiert, so gehen wir in diesem Teil die Probleme eher aufgabenorientiert an. Wir überlegen, mit welchen Mitteln und wofür PL/SQL genutzt
werden kann, und betrachten die verschiedenen Möglichkeiten an kurzen Beispielen. Das Ziel dieses Teils ist einerseits, Ihnen Anregungen zum Einsatz von
PL/SQL zu geben und dies anhand von konkretem Code zu verdeutlichen. Andererseits zeige ich Ihnen an dem erstellten Code PL/SQL in etwas größeren Zusammenhängen und begründe Ihnen meine Entscheidung für die eine oder andere
Implementierung.
Kapitel 12: Erweiterung von SQL
Dieses Kapitel betrachtet einige Beispiele für die Erweiterung des Befehlsumfangs von SQL durch eigene PL/SQL-Funktionen. Wir starten damit, dass wir uns
fragen, wann SQL überhaupt durch eigene Funktionen erweitert werden sollte
und sehen uns dann an, wie diese Erweiterung durchgeführt werden kann. Als
Krönung werden wir eigene Gruppenfunktionen erstellen und einen Code-Generator entwickeln, der Sie bei der Erstellung von Gruppenfunktionen unterstützt.
Kapitel 13: Arbeiten mit großen Datenstrukturen
Eine häufige (und häufig nicht vollständig verstandene) Anforderung an Datenbankentwickler ist die Arbeit mit großen Datenstrukturen. Damit sind hier nicht
große Ergebnismengen durch SQL-Abfragen gemeint, sondern große Datenstrukturen wie Videos, Audiodateien oder große Textdateien. Bei Oracle können diese
Strukturen mühelos jeden Arbeitsspeicher sprengen und bedürfen daher einer
besonderen Behandlung. Diese Behandlung erläutere ich in diesem Kapitel. Wir
sehen uns zunächst die technische Struktur dieser großen Datenstrukturen an
24
1452.book Seite 25 Donnerstag, 5. August 2010 3:55 15
Der Aufbau des Buches
und analysieren anschließend die von Oracle zur Verfügung gestellten Packages,
mit denen diese Strukturen verwaltet werden. Das Kapitel schließt mit einigen
Hilfspackages zum Schreiben und Lesen dieser Datenstrukturen vom und ins
Dateisystem ab.
Kapitel 14: Arbeiten mit XML
Schon seit vielen Jahren unterstützt Oracle den Datentyp XML nativ. Dieses Kapitel beschäftigt sich mit diesem enorm umfangreichen Thema und gibt eine Einführung in die verschiedenen Strategien, mit denen XML innerhalb der Datenbank erzeugt, gelesen und auf relationale Tabellen abgebildet werden kann.
Allerdings liefert dieses Kapitel keine Einführung in XML oder die in diesem
Kapitel verwendeten Technologien wie XSLT oder XSD. Einiges wird nur
erwähnt, anderes am Beispiel gezeigt. Gerade dieses Thema wäre ausreichend für
ein eigenes Fachbuch (und solche Bücher gibt es natürlich zu diesem Thema).
Doch für das grundlegende Verständnis dieser Technologie sollte das hier Dargestellte durchaus reichen. Wir sehen uns zunächst den Datentyp XMLType an, mit
dessen Hilfe XML in der Datenbank hauptsächlich gespeichert und bearbeitet
wird. Dann sehen wir uns Strategien an, wie XML aus relationalen Daten erzeugt
werden und mit PL/SQL verarbeitet werden kann. Ein weiterer Teil beschäftigt
sich mit dem Einlesen und Verteilen von XML in die Datenbank. Zum Teil liefert
dieses Kapitel auch spezielle Funktionen von SQL, die ich hier aufgenommen
habe, weil sie nicht zum Standardwissen eines SQL-Entwicklers gehören, allerdings in Kombination mit PL/SQL erforderlich sind, um XML zu verarbeiten. Das
Kapitel schließt mit einer Einführung in die XML-Datenbank, einer Anwendung
der eingangs geschilderten XML-Datentypen zur Speicherung von XML und binären Daten in der Datenbank über das Dateisystem.
Kapitel 15: Objektorientierung
Die Objektorientierung ist für Anwendungsentwickler mit entsprechendem Hintergrund oftmals der Heilsbringer, für Administratoren und Anwendungsentwickler ohne diesen Hintergrund oftmals das Schimpfwort schlechthin. Dieses
Kapitel versucht zu schlichten: Einerseits bietet es eine Einführung in die Objektorientierung für diejenigen, die sich noch nicht entschließen konnten, diese
Weltsicht für sich zu entdecken, andererseits erläutert es für die anderen, warum
die Objektorientierung, gerade im Zusammenhang mit der strukturierten Speicherung großer Datenmengen, nicht die allein selig machende Technologie sein
kann. Wahrscheinlich, das sehe ich schon kommen, werden beide Seiten nach
der Lektüre nicht vollends zufrieden sein: Meine Einführung in die Objektorientierung dürfte denen, die die Technologie bereits mit Begeisterung einsetzen, zu
oberflächlich, die Argumente gegen den hemmungslosen Einsatz von Objektori-
25
1.2
1452.book Seite 26 Donnerstag, 5. August 2010 3:55 15
1
Einführung
entierung innerhalb der Datenbank zu kleinlich sein, und die Gegner der Objektorientierung werden mir möglicherweise ein unvorteilhaft positives Bild der
objektorientierten Programmierung vorwerfen und den Aufwand, diese Technologie innerhalb der Datenbank anzuwenden, als unnötig hoch einstufen. Ich
bleibe dennoch bei meiner Position der vorsichtigen Vermittlung zwischen den
Welten und der Anbindung und Nutzung dort, wo es Sinn macht, und der Ablehnung dort, wo der Sinn zu fehlen scheint. Das Kapitel beginnt also mit einer Einführung in die Objektorientierung »für Dummies« und zeigt an einem konkreten
Typ die aus meiner Sicht sinnvolle Verwendung. Der Code dieses Kapitels ist
etwas länger als sonst üblich, zeigt aber auch mehrere Praktiken, nicht nur aus
dem objektorientierten Umfeld, sondern auch bezüglich der Umsetzung in PL/
SQL.
Kapitel 16: Integration von Oracle in Applikationen
Dieses Kapitel untersucht die verschiedenen Szenarien, in denen Oracle in Datenbanken integriert wird. Wir sehen uns die verschiedenen Sperrverfahren im
Code an und erläutern verschiedene Wege der Umsetzung. Anschließend kümmern wir uns darum, wie Daten einer externen Anwendung über das Netzwerk,
aber auch über das Intra- oder Internet zur Verfügung gestellt werden kann. Die
Idee hierbei ist, aufzuzeigen, dass Oracle sich nicht nur über direkte Datenbankverbindungen befragen lässt, sondern auch Möglichkeiten der losen Kopplung
bietet, bei denen die Datenbank als REST- oder SOAP-basierter Webservice-Provider auftritt und so einen weiteren Zugriff auf die Daten der Datenbank gestattet. Bei Verbindungen über das lokale Netzwerk (ob nun direkt aus der Anwendung oder über einen Connection Pool eines Applikationsservers) zeige ich
darüber hinaus, auf welche Weise Daten durch den Datenbankserver vorgehalten
werden. Spannend wird diese Frage ja vor dem Hintergrund der enormen Datenmengen, die durch eine SQL-Anweisung eventuell über das Netzwerk geschoben
werden können.
1.2.4
Teil 4: Workshops
Der abschließende Teil des Buches widmet sich der Abbildung konkreter Problemstellungen auf PL/SQL. Zum einen stelle ich eine Anwendungsarchitektur
vor, die als Keimzelle für Ihre Projekte herangezogen werden kann, zum anderen
zeige ich an einem horizontalen Dienst, auf welche Weise PL/SQL-Packages in
Ihre Anwendungen integriert werden können.
26
1452.book Seite 27 Donnerstag, 5. August 2010 3:55 15
Danksagung
Kapitel 17: Workshop1 – Die Keimzelle sicherer Datenbankanwendungen
Dieses Kapitel fasst viele der in den vorangegangenen Kapiteln besprochenen
Techniken zu einer Architektur zusammen, in der die Datenbank die Rolle eines
»Datenframeworks« übernimmt. Diesen Begriff habe ich mit Blick auf Java-Entwickler gewählt, weil dort für alles und jedes »Frameworks« verwendet werden.
Mir geht es darum, aufzuzeigen, wie die wirklich staunen machenden Fähigkeiten der Oracle-Datenbank ideal genutzt werden können, um mit Bordmitteln
und ein wenig Programmierung eine Datenbankarchitektur zu erzeugen, die ein
Höchstmaß an Skalierbarkeit und Sicherheit mitbringt und sich dennoch ganz
einfach in eine Anwendungsarchitektur einfügt. Dieses Kapitel ist eine Mischung
aus SQL, Administrationswissen und PL/SQL und ist eigentlich insofern typisch
für die Anwendungsentwicklung gegen Datenbanken, als eine optimale Lösung
immer auch die intime Kenntnis der eingesetzten Technologien erfordert. Dieses
Kapitel bietet sozusagen eine »Guided Tour« durch eine solche Datenbankanwendung und zeigt die Denkweise eines Entwicklers, der Oracle intensiv kennt und
einsetzt.
Kapitel 18: Workshop2 – Ein Logging-Package
Das letzte Kapitel des Buches gibt ein Beispiel für ein komplettes Package für
einen Einsatzzweck, der sich in jedem Projekt findet: ein Package für horizontale
Dienste im Umfeld der Fehlerbehandlung und des Loggings. Als Keimzelle für
ein entsprechendes Package für Ihre Projekte sollte dieses Projekt taugen. Die
Highlights sind: Fehlermeldungen in beliebigen Sprachen, zentralisiertes Fehlerlogging, Logging kompatibel zum weitverbreiteten Java-Standard Log4J (entsprechende Implementierungen gibt es auch für andere Programmiersprachen) und
damit die Fähigkeit, Fehlermeldungen der Datenbank in den normalen Fehlerbehandlungsfluss Ihrer Anwendung zu integrieren, und einiges mehr. Dieses Projekt versammelt in sich einen ganzen Strauß verschiedener Programmiertechniken: Objektorientierung, XML, ein witziges Datenmodell, Advanced Queueing
und einiges mehr. Seien Sie also gespannt!
1.3
Danksagung
Meine Frau bat mich, in diesem Buch auf eine Danksagung nach der Art: »Ich
danke meiner Familie, die mich viele Monate nicht zu Gesicht bekommen hat,
für ihre Unterstützung« abzusehen, sondern – maximal – zu schreiben, dass, hätte
sie mich nicht freundlich, aber bestimmt zur Arbeit gedrängt, das Buch immer
noch nicht fertig sei … Meine Frau hat Spaß daran, und ich habe Spaß an meiner
Frau, also lasse ich das natürlich auch mit der Danksagung.
27
1.3
1452.book Seite 28 Donnerstag, 5. August 2010 3:55 15
1
Einführung
Allerdings muss ich (weil es mir besonders wichtig ist) einen herzlichen Dank an
meinen technischen Lektor, Joachim Zetzsche, aussprechen, der sich direkt und
ohne zu zögern bereit erklärt hat, die Kapitel fachlich gegenzulesen und seine
Anmerkungen zu Stil, Komplexität und Art der Darstellung einzubringen. Die
Diskussion mit ihm über den Inhalt und den Aufbau dieses Buches haben mich
enorm weitergebracht. Joachim ist einer der besten Kenner der Oracle-Datenbanktechnologie, den ich kenne, und mein Zutrauen, dass das, was ich über Oracle
behaupte, der Wahrheit nahekommt, steigt steil, nachdem Joachim sein Plazet
dazu gegeben hat. Herzlichen Dank dafür!
Mein Dank gilt auch den übrigen Fachlektoren und Herrn Mattescheck von Galileo Press, der dieses Projekt betreut und Geduld mit mir hatte, als die Liefertermine von mir ein ums andere Mal verschoben wurden. Allerdings muss ich zu
meiner Verteidigung auch sagen, dass die Aufgabe, neben dem täglichen Projektgeschäft ein so dickes Buch zu schreiben, brachial unterschätzt wird …
Nicht zuletzt gilt mein Dank Ihnen, den Lesern, dafür, dass Sie sich die Zeit nehmen, dieses Buch zu lesen. Ich hoffe, Sie betrachten die investierte Zeit nicht als
eine verlorene. Ich weiß, dass ich Ihnen da und dort erhebliche Konzentration
abverlangen muss, tue das aber in dem Bewusstsein, dass die Inhalte, die ich dort
vermittele, für die Programmierung guter Anwendungen unerlässlich sind. Die
Aufgabe dieses Buches ist es, Ihnen zu erklären, wie man Oracle-Datenbanken
richtig programmiert. Nicht irgendwie. Sollte Ihnen dieses Buch auf dem Weg zu
solchen Anwendungen hilfreich sein, freue ich mich über eine Rückmeldung.
Falls nicht – vielleicht senden Sie eine Mail an den Verlag? Nein, im Ernst, auch
über Kritik freue ich mich, so sie denn konstruktiv ist und berücksichtigt, dass
ich, sollte ich gescheitert sein, dies nicht mit Vorsatz getan habe.
28
1452.book Seite 395 Donnerstag, 5. August 2010 3:55 15
Die zentrale Aufgabenstellung für die Programmierung von PL/SQL ist
der Umgang mit den Daten der Datenbank. PL/SQL ist für diese Aufgabe besser gerüstet als jede andere Programmiersprache im OracleUmfeld, denn als Erweiterung zu SQL setzt sie direkt auf der Datenbanksprache auf. Grund genug, diese Vorteile zu nutzen!
9
Das Arbeiten mit Daten
In Abschnitt 7.4, »Kollektionen in PL/SQL«, haben Sie bereits den grundsätzlichen Aufbau von Records, assoziativen Tabellen und so weiter kennengelernt.
Dieses Kapitel vertieft nun dieses Wissen und stellt die einzelnen Datentypen in
den Kontext von Anwendungen.
9.1
Strukturierte Variablen: Records und Typen
Beginnen wir mit den Records. Den grundsätzlichen Aufbau haben wir bereits
besprochen, doch wo liegen Anwendungsbereiche über die Hauptanwendung,
das Kopieren einer Tabellenzeile, hinaus?
9.1.1
Bindung an das Data Dictionary mit »%TYPE« und »%ROWTYPE«
Bei der Bindung an das Data Dictionary folgt unser Code den Änderungen am
Datenmodell. So gut diese Fähigkeit im Allgemeinen ist, so hinterhältig kann sie
aber auch sein, denn sie stößt bei bestimmen Rahmenbedingungen halt an Grenzen. Nehmen wir z.B. an, eine Tabelle wird durch eine neue Spalte erweitert.
Dann wird dieser neuen Spalte natürlich in unserem Code niemals ein Wert zugewiesen, denn unser Code kennt diese Spalte nicht. Umgekehrt kann es sein, dass
eine gelöschte Spalte einen Fehler auslöst, denn eine Zuweisung auf diese Spalte
funktioniert nun natürlich nicht mehr. Doch haben wir in diesem Beispiel den
großen Vorteil, davon zu erfahren, denn nun kompiliert die Prozedur nicht
mehr. Machen wir uns dies an einem Beispiel klar.
Ich hatte unserer Tabelle dept eine Spalte max_sal hinzugefügt, um einen schnellen Ausweg aus einem Mutating-Table-Problem zu erhalten (siehe Abschnitt
395
1452.book Seite 396 Donnerstag, 5. August 2010 3:55 15
9
Das Arbeiten mit Daten
8.3.5, »Trigger und Datensichten (INSTEAD-OF-Trigger)«). Vielleicht habe ich
mich nun doch entschlossen, einen anderen Weg zu verwenden, denn mir ist erst
jetzt klar geworden, dass die zusätzliche Spalte das Problem eigentlich gar nicht
gelöst, sondern nur verschoben hat: Wie pflege ich denn jetzt das Maximum der
Gehälter in der Tabelle dept, wenn konkurrierend die Gehälter der Benutzer in
Tabelle emp erhöht werden? Daher benötige ich diese Spalte nicht mehr. Leider
habe ich eine Prozedur, die dieser Spalte einen Wert zugewiesen hat. Diese
Zuweisung gelingt nun natürlich nicht mehr. Doch wo war dieser Code noch einmal?
Irgendwo in unserem Code wird eine Zeile etwa dieser Art stehen:
declare
dept_rec dept%ROWTYPE;
begin
…
dept_rec.max_sal := max_sal;
…
Diese Zeile wird vom Compiler nicht mehr kompiliert, denn der Record dept_rec
enthält keine Struktur mit dem Namen max_sal mehr. Nur zur Verdeutlichung:
Hätten wir den Record explizit deklariert und nicht an das Data Dictionary
gebunden, wäre dieser Fehler nicht aufgefallen, sondern zur Laufzeit aufgetreten.
Die eigentliche Kunst für Oracle liegt aber gar nicht so sehr im Kompilieren dieser Struktur, sondern darin, zu erkennen, dass irgendeine Änderung am Data
Dictionary genau diese Zeile Code ungültig macht. Denn zum Zeitpunkt des
Kompilierens existierte die Spalte noch in der Tabelle dept, die Spalte wurde erst
gelöscht, nachdem dieser Code kompiliert war. Dennoch erkennt Oracle selbsttätig, dass diese Struktur nicht mehr gültig ist, weil sich die Voraussetzungen geändert haben. Im konkreten Fall setzt Oracle den Status dieser Prozedur auf den Status invalid und erlaubt die Ausführung nicht mehr. Sie erkennen also sofort,
dass ein Problem vorliegt, und können es anschließend lösen. Die Verwaltung
dieser Abhängigkeiten durch Oracle ist es, wovon Sie auch in diesem Beispiel
profitieren.
Doch auch an diesem Beispiel können wir uns klarmachen, dass es keine Vorteile
ohne Nachteile gibt. Wir profitieren also von der Verwaltung der Abhängigkeit
durch Oracle, weil die Deklarationen aufeinander aufbauen. Denken wir dieses
Thema aber weiter, so wird klar, dass die Änderung einer zentralen Datenstruktur eventuell eine kaskadierende Kette von Neukompilierungen nach sich ziehen
kann. Oracle bezeichnet so etwas als Abhängigkeitskette (dependency chain). Als
Beispiel nehmen wir ein Package mit Konstanten an, das von vielen anderen
Packages referenziert wird. Ändere ich nun dieses zentrale Package, wird es
396
1452.book Seite 397 Donnerstag, 5. August 2010 3:55 15
Strukturierte Variablen: Records und Typen
ungültig und mit ihm alle auf der Deklaration aufbauenden Packages, Prozeduren
und Funktionen. Eine Anpassung eines zentralen Packages hat also das Neukompilieren vieler weiterer Packages zur Folge. Je nach Anwendungssituation kann
dies ein Nachteil sein. Besonders ärgerlich ist dieses Neukompilieren, wenn es
bei laufender Datenbank durchgeführt werden soll. Denn in diesem Fall ist es
sehr wahrscheinlich, dass Packages rekompiliert werden müssen, die von anderen Benutzer gerade verwendet werden. Diese Packages sind dann gesperrt, werden entsprechend nicht kompiliert, und ein Patch kann gefährdet sein.
Aber es gibt noch feinere Probleme. Was ist zum Beispiel, wenn Sie die zulässige
Länge einer Spalte beschränken? Vorher haben Sie 30 Zeichen erlaubt, nun nur
noch 25? Die Folgen von Änderungen dieser Art können von Oracle nicht immer
korrekt ausgewertet werden. Natürlich ist es möglich, einer Variablen den Wert
einer anderen Variablen mit großzügigeren Grenzen zuzuordnen, solange die
maximale Länge der Variablen, der ein Wert zugewiesen wird, nicht überschritten wird:
SQL> declare
2
a varchar2(10 char);
3
b varchar2(20 char) := 'Peter';
4 begin
5
a := b;
6 end;
7 /
PL/SQL-Prozedur erfolgreich abgeschlossen.
Listing 9.1 Beispiel für eine mögliche Variablenzuweisung
Es kann also sein, dass eine Verkürzung der Spaltenbreite für Oracle unproblematisch erscheint, dann aber zur Laufzeit Fehler auslöst, weil eine andere Variable
immer noch von der breiten Spalte ausgeht. Im Gegensatz dazu ist es im Regelfall
unproblematisch, Datentypen zu erweitern. Eventuell können Sie von den erweiterten Möglichkeiten nicht vollständig profitieren, ohne Ihren Code anzupassen,
doch grundsätzlich sollten in dieser Konstellation keine Fehler auftauchen.
Ein anderer Problembereich ist die Änderung des Datentyps. Was geschieht,
wenn eine Spalte, die vorher vom Typ varchar2 war, nun in den Typ date umgewandelt wird? Sie sagen, dass so etwas passiere, sei doch wohl sehr unwahrscheinlich? Ich weiß nicht, wie viele Datenmodelle ich schon gesehen habe, die
Datumsangaben als Zeichenketten speichern und im Zuge eines Redesigns endlich auf den Typ Datum umstellen! Oracle wird versuchen, solange wie möglich
mit impliziten Umwandlungen den Code »am Leben zu erhalten«, doch sind dem
natürlich Grenzen gesetzt. Je nach Art der Überschreitung kann eine solche
397
9.1
1452.book Seite 398 Donnerstag, 5. August 2010 3:55 15
9
Das Arbeiten mit Daten
Grenze automatisch erkannt werden, zum Teil aber auch nicht. Allerdings gilt
natürlich auch hier: Implizite Konvertierungen sollten Sie in Ihrem Code nicht
tolerieren. Manchmal ist dies zwar kaum sichtbar (etwa bei einer Schleifenvariable, die vom Typ pls_integer ist und, wenn sie einer Variable vom Typ number
zugeordnet wird, konvertiert werden muss), doch in jedem Fall ist es schlechter
Programmierstil, etwa auf eine Standardumwandlung von Datumszeichenketten
in ein Datum zu hoffen. Wo immer dies unumgänglich ist, sollten Sie die Datentypen explizit ineinander umwandeln.
Insgesamt gilt jedoch: Binden Sie Ihre Records an das Data Dictionary, wo immer
dies möglich ist. Die Robustheit Ihres Codes nimmt zu, außerdem kommunizieren Sie klarer, was der Code eigentlich tun soll:
declare
l_empno emp.empno%type;
dept_rec dept%rowtype;
begin
…
end;
Bei dieser Variablen müssen Sie nicht lange überlegen, welche Information in ihr
wohl gespeichert werden soll. Ebenso gilt dies für die Deklaration der Recordvariablen eine Zeile tiefer. Code dieser Art dokumentiert sich also selbst. Die Bindung an das Data Dictionary kann aber auch auf andere Weise als über die Bindung an eine Tabelle erfolgen. Eine gute Möglichkeit ist die Bindungen an die
Definition einer Datenbanksicht oder an einen Cursor. Durch diese Möglichkeiten können wir sehr gut steuern, welche Informationen unser Record enthalten
soll und welche nicht.
9.1.2
Insert- und Update-Anweisungen mit Records
Records können im Umfeld von Insert- oder Update-Anweisungen dazu genutzt
werden, Daten »in einem Rutsch« in die Tabellen zu integrieren. Sehen wir uns
dazu einige einfache Beispiele an. Im folgenden Beispiel wird ein Record mittels
des %ROWTYPE-Attributs definiert und anschließend mit Werten für eine neue
Zeile gefüllt. Beachten Sie, dass der Aufruf der Sequenz departments_
seq.nextval in Zeile 4 in dieser direkten Form erst ab Version 11g möglich ist.
In Oracle 10g müssten Sie eine Variable deklarieren und die Sequenz über eine
Select-Anweisung in die lokale Variable umkopieren:
SQL> declare
2
dept_rec departments%rowtype;
3 begin
4
dept_rec.department_id := departments_seq.nextval;
398
1452.book Seite 399 Donnerstag, 5. August 2010 3:55 15
Strukturierte Variablen: Records und Typen
5
dept_rec.department_name := 'Accounting';
6
dept_rec.manager_id := null;
7
dept_rec.location_id := 2700;
8
insert into departments values dept_rec;
9 end;
10 /
PL/SQL-Prozedur erfolgreich abgeschlossen.
SQL> select *
2
from departments
3
where location_id = 2700;
DEPARTMENT_ID DEPARTMENT_NAME
MANAGER_ID LOCATION_ID
------------- ------------------------ ---------- ----------70 Public Relations
204
2700
280 Accounting
2700
Listing 9.2 Insert-Anweisung mittels eines Records
Ähnlich komfortabel ist die Integration eines Records in Update-Anweisungen.
Vorab sollten Sie beachten, dass die einfach zu schreibende und zu benutzende
Update-Variante mittels %ROWTYPE alle Spalten ins Update nimmt, insbesondere
auch die Primärschlüsselspalten, ob sich deren Inhalt nun ändert oder nicht. Dies
hat zur Folge, dass eventuell referenziell abhängige Tabellen gesperrt werden
müssen, wenn deren Fremdschlüsselspalte nicht indiziert ist. (Zur Begründung
und Lösung dieses Problems siehe Abschnitt 4.3.2, »Performanzüberlegungen zu
Datenbank-Constraints«). Zudem können Trigger ausgelöst werden, obwohl
keine relevante Spalte ihren Wert ändert. (Dieses Problem hatten wir in
Abschnitt 8.1.1, »Anweisungs- versus Zeilentrigger«, besprochen). Aber sehen
wir uns trotz dieser Warnhinweise diese Variante an:
SQL> declare
2
dept_rec departments%ROWTYPE;
3 begin
4
dept_rec.department_id := 280;
5
dept_rec.department_name := 'Administration';
6
dept_rec.location_id := 2700;
7
update departments
8
set row = dept_rec
9
where department_id = 280;
10 end;
11 /
PL/SQL-Prozedur erfolgreich abgeschlossen.
Sie erkennen die Pseudospalte row in Zeile 8. Diese Pseudospalte ist lediglich
links des Gleichheitszeichens und hinter der (dann einzigen) set-Klausel erlaubt.
399
9.1
1452.book Seite 400 Donnerstag, 5. August 2010 3:55 15
9
Das Arbeiten mit Daten
Alle Werte, die vorher im Record nicht belegt wurden, werden durch Null-Werte
ersetzt.
Wenn Sie die Kontrolle über die Spalten, die aktualisiert werden sollen, behalten
möchten, können Sie auch folgende Variante schreiben, die etwas länger ist,
dafür aber gezielter agiert:
SQL> declare
2
3
dept_rec departments%ROWTYPE;
begin
4
-- Lies einen Datensatz in den Record
5
select *
6
into dept_rec
7
from departments
8
where department_id = 280;
9
-- Simuliere Datenänderungen durch den Anwender
10
dept_rec.department_name := 'Accounting';
11
-- Schreibe die geänderten Daten in die Datenbank
12
update departments
13
set department_name = dept_rec.department_name,
14
location_id = dept_rec.location_id,
15
manager_id = dept_rec.manager_id
16
where department_id = dept_rec.department_id;
17
end;
18
/
PL/SQL-Prozedur erfolgreich abgeschlossen.
SQL> select *
2
3
from departments
where department_id = 280;
DEPARTMENT_ID DEPARTMENT_NAME
MANAGER_ID LOCATION_ID
------------- ------------------------ ---------- ----------280 Accounting
2700
Listing 9.3 Update-Anweisung mittels eines Records
Hier sehen Sie zudem, wie wir einen Datensatz aus der Datenbank lesen, ein
Attribut ändern und den geänderten Wert in die Datenbank zurückschreiben.
Dies ist natürlich verkürzt, aber wir können uns eine Dialoganwendung vorstellen, die einen Record aus der Datenbank liest, an ein Frontend gibt, den geänderten Record zurückerhält und anschließend in die Datenbank einliest. Gleichzeitig
behalten wir bei diesem Prozess die volle Kontrolle darüber, welche Spalte aktualisiert werden soll oder nicht.
400
1452.book Seite 401 Donnerstag, 5. August 2010 3:55 15
Strukturierte Variablen: Records und Typen
9.1.3
Verwendung explizit deklarierter Records
Alle bisherigen Beispiele haben die Deklaration des Records in der ein oder anderen Form an das Data Dictionary gebunden, sei es über das Attribut %ROWTYPE
oder über das Attribut %TYPE. Dies muss natürlich nicht so sein. Wenn Sie einen
Record explizit selbst deklarieren möchten, erinnert die syntaktische Form sehr
an die Definition einer Tabelle:
SQL>
2
3
4
5
6
7
8
9
10
declare
type my_rec is record (
id number not null := 0,
description varchar2(200 char),
content xmltype);
rec_var my_rec;
begin
< irgendeine Aktion … >
end;
/
Listing 9.4 Deklaration eines Records
Sie sehen, dass alle Einschränkungen, die wir für normale Variablen vornehmen
können, auch in Records erlaubt sind (allerdings keine Konstanten). Objektorientierte Entwickler entdecken in diesen Records vielleicht einen Struct wieder,
aber auch außerhalb dieser Denkwelt sind Records praktische Strukturen, um
z.B. die Anzahl der Parameter zu reduzieren, die an eine Prozedur übergeben
oder von dort zurückerhalten werden. Leider ist aber die Übergabe eines Records
an eine Prozedur nicht ohne Weiteres möglich: Der Record muss außerhalb der
Prozedur bekannt sein, damit die aufrufende Anweisung eine entsprechende
Struktur erzeugen oder entgegennehmen kann. Ein Record kann aber lediglich
innerhalb von PL/SQL definiert werden, ein create type my_rec is record… existiert in SQL nicht.
Daher muss der Record im Rahmen eines Packages definiert und so »von außen«
zugänglich gemacht worden sein. Die Programmierung mit Records als Parameter von Prozeduren oder Funktionen setzt also ein Package voraus. Sehen wir uns
ein kurzes Beispiel für eine solche Konstruktion an. In unserem Beispiel möchten
wir eine strukturierte Information übergeben, um im Rahmen einer Autorisierung einige Angaben zur Validierung zu übergeben. Wir möchten, dass eine
Anmeldung einen korrekten Zeitstempel, einen Programmnamen und einen Versionsstring übergibt. Anhand dieser Informationen können wir erkennen, welches Programm die Autorisierung bei der Datenbank beantragt und in welcher
Version dieses vorliegt. Sie könnten sich vorstellen, dass wir eine solche Informa-
401
9.1
1452.book Seite 402 Donnerstag, 5. August 2010 3:55 15
9
Das Arbeiten mit Daten
tion in Kombination mit einem Benutzernamen und einem Passwort verwenden
können, um uns an einer Anwendung anzumelden. Da ich das Beispiel einfach
halten möchte, verzichte ich derzeit auf das Passwort und den Benutzernamen.
Erstellen wir also zunächst ein Package:
SQL> create or replace package pkg_authorization
2 as
3
type app_rec_t is record (
4
app_name char(5 char),
5
app_version char(5 char),
6
time_stamp date);
7
8
function authorize(app_rec in app_rec_t)
9
return boolean
10 ;
11 end pkg_authorization;
12 /
Package wurde erstellt.
SQL> create or replace package body pkg_authorization
2 as
3
function authorize(app_rec in app_rec_t)
4
return boolean
5
as
6
two_minutes constant number := 2/1440;
7
begin
8
return app_rec.app_name = 'HDMAP'
9
and app_rec.app_version = '1.0.0'
10
and app_rec.time_stamp
between sysdate - two_minutes
and sysdate + two_minutes;
11
end authorize;
12 end pkg_authorization;
13 /
Package Body wurde erstellt.
SQL> set serveroutput on
SQL> declare
2
app_rec pkg_authorization.app_rec_t;
3 begin
4
app_rec.app_name := 'MY_AP';
5
app_rec.app_version := '1.0.0';
6
app_rec.time_stamp := sysdate;
7
if pkg_authorization.authorize(app_rec)
8
then
402
1452.book Seite 403 Donnerstag, 5. August 2010 3:55 15
Strukturierte Variablen: Records und Typen
9
dbms_output.put_line('Autorisiert');
10
else
11
dbms_output.put_line('Nicht autorisiert');
12
end if;
13 end;
14 /
Autorisiert
PL/SQL-Prozedur erfolgreich abgeschlossen.
Listing 9.5 Deklaration eines Records in einem Package
Sie erkennen die Deklaration des Recordtyps in der Package-Spezifikation.
Anschließend wird eine Instanz des Records als Eingabeparameter der Funktion
authorize vereinbart, indem die Variable an die Deklaration des Records im
Package gebunden wird. Dies ist natürlich der Grund dafür, dass die RecordDeklaration in der Package-Spezifikation vorgenommen werden musste, ansonsten wäre sie ja nicht sichtbar. Innerhalb der Prozedur authorize wird der übergebene Parameter sozusagen »ausgepackt« und werden die einzelnen Bestandteile mit vorgegebenen Werten verglichen. Das Ergebnis dieser Prüfung wird
anschließend als Wahrheitswert zurückgeliefert. Mithilfe dieser Technik ist nur
ein Parameter zu übergeben anstatt von drei zusammengehörigen Informationen. Dadurch wird Ihr Code besser lesbar, zudem haben Sie eine leicht zu verarbeitende, selbsterklärende Struktur. Eines geht allerdings auch mit dieser Art der
Deklaration nicht: Ein Record wird niemals direkt in SQL zu benutzen sein. SQL
kennt keine Variablen, sondern maximal objektorientierte Typen, die für ähnliche Zwecke gebraucht werden können.
Wenn Sie sich die Packages ansehen, die von Oracle mitgeliefert werden, stellen
Sie allerdings fest, dass diese Art der Programmierung von Oracle selbst nicht
allzu häufig umgesetzt wird. Hier herrschen immer noch Prozeduren mit vielen
Parametern vor. Auf der Habenseite der herkömmlichen Art der Programmierung steht sicherlich, dass zum Aufruf der Prozedur kein Record erzeugt werden
muss. Diese zusätzliche Komplexität mag einer einfachen Bedienung im Weg stehen. Zudem wird die Anzahl der Records, die Sie in einem umfangreichen
Package deklarieren müssen, doch recht hoch sein, zumal Sie einen Record für
jede überladene Prozedur definieren müssten. Auf der anderen Seite profitieren
Sie von kürzeren Funktionsaufrufen und möglicherweise klarerem Code. Letztlich ist es eine Stilfrage; funktionale Unterschiede sehe ich nicht. Beurteilen Sie
also selbst, ob Sie diese Art der Programmierung mögen oder nicht. Ich entscheide mich tendenziell so: Prozeduren, die von Code außerhalb der Datenbank
aufgerufen werden, versehe ich eher mit solchen Records als Prozeduren, die
innerhalb der Datenbank aufgerufen werden. Das heißt, dass ich Prozeduren, die
403
9.1
1452.book Seite 404 Donnerstag, 5. August 2010 3:55 15
9
Das Arbeiten mit Daten
ich benötige, um einer Anwendung in Java oder C# oder was auch immer eine
API zur Änderung meiner Daten anzubieten, als externes Interface gerne schlank
halten möchte. Gerne verwende ich für solche Parameter auch XML, wenn es
passt, sodass eher eine nachrichtenartige Struktur beim Aufruf dieser Funktionen
resultiert. Allerdings ist das lediglich meine persönliche Auffassung.
9.1.4
Verwendung der Returning-Klausel mit Records
Bei jedem Insert oder Update lassen sich im gleichen Round-Trip zum Server
auch einige Spalten über die Returning-Klausel zurückliefern. Sehen wir uns in
einem Beispiel an, wie eine Returning-Klausel einer SQL-Anweisung im Zusammenhang mit einem Record genutzt werden kann. Die Returning-Klausel erlaubt
es uns, nach einer DML-Anweisung eine oder mehrere Spaltenwerte zurückzuliefern. Wenn wir mehrere Spalten benötigen, können wir zur Übergabe gut einen
Record verwenden. Diesen müssen wir so deklarieren, dass er alle Spalten aufnehmen kann, die durch die Returning-Klausel geliefert werden. Daher können
wir den Record nicht an die Definition der Tabelle binden, denn die Tabelle enthält zu viele Spalten. In unserem Beispiel wollen wir die Bewegung der Gehälter
in einer Protokolltabelle hinterlegen. Dazu erstellen wir zunächst eine Tabelle,
die diese Informationen aufnehmen kann:
SQL> create table emp_log as
2 select *
3
from emp
4
where 1=0;
Tabelle wurde erstellt.
SQL> alter table emp_log add change_action varchar2(1 char);
Tabelle wurde geändert.
SQL> alter table emp_log add change_user varchar2(30 byte);
Tabelle wurde geändert.
SQL> alter table emp_log add change_date date;
Tabelle wurde geändert.
Dann benötigen wir noch eine Prozedur, mit deren Hilfe wir die Gehälter anheben (oder absenken) können. Diese Prozedur soll gleichzeitig auch die Einträge in
die Protokolltabelle vornehmen. Auf diese Weise ersparen wir uns einen Trigger
auf die Tabelle emp. Wir können uns die folgende Prozedur vielleicht als Teil einer
Mitarbeiter-Wartungs-API vorstellen:
SQL> create or replace
2 procedure raise_sal(
3
emp_id in emp.empno%type,
4
raise_factor in number := 1)
404
1452.book Seite 405 Donnerstag, 5. August 2010 3:55 15
Strukturierte Variablen: Records und Typen
5
6
7
8
9
10
11
12
13
14
15
16
17
as
type emp_rec_t is record (
ename emp.ename%TYPE,
sal emp.sal%TYPE);
emp_rec emp_rec_t;
begin
update emp
set sal = sal * raise_factor
where empno = emp_id
returning ename, sal into emp_rec;
if SQL%ROWCOUNT > 0 then
insert into emp_log
(empno, ename, sal,
change_action, change_user, change_date)
18
values (emp_id, emp_rec.ename, emp_rec.sal,
'U', user, sysdate);
19
end if;
20 end;
21 /
Prozedur wurde erstellt.
Listing 9.6 Beispiel für einen Record in einer Returning-Klausel
Beachten Sie, wie die aktualisierten Informationen durch die Returning-Klausel
zurückgeliefert und in der nächsten Insert-Anweisung verwendet werden. Damit
diese Art der Zuweisung funktioniert, muss der Record selbstverständlich positional exakt so definiert werden, wie die Werte in der Returning-Klausel geliefert
werden – sowohl von der Anzahl als auch vom Datentyp her. Das heißt, dass die
Returning-Klausel in unserem Beispiel zwei Record-Attribute erwartet und dass
das erste Attribut ein ausreichend großer Varchar2-Datentyp und das zweite
Attribut ein Number-Datentyp sein muss. Die Datenbank kümmert sich allerdings nicht um die Bezeichnungen der Record-Strukturen und wird auch Datentypen umwandeln, falls das möglich ist.
Eine Erwähnung verdient vielleicht noch die Zeile 15. Dort wird der Cursor SQL
daraufhin geprüft, wie viele Zeilen er bearbeitet hat. Der Cursor SQL ist ein
Bezeichner für den implizit von Oracle genutzten Cursor, um die Update-Anweisung auszuführen. Hier wird das Cursorattribut ROWCOUNT verwendet, das die
Anzahl der verarbeiteten Zeilen enthält. Sollte eine ungültige Mitarbeiter-ID
übergeben worden sein, führt die Update-Anweisung keine Änderung aus, und
es wird auch kein Datensatz in die Logtabelle geschrieben.
Sehen wir uns die Returning-Klausel abschließend für eine Update-Anweisung
an:
405
9.1
1452.book Seite 406 Donnerstag, 5. August 2010 3:55 15
9
Das Arbeiten mit Daten
SQL> declare
2
type emp_rec_type is record (
3
last_name employees.last_name%TYPE,
4
salary employees.salary%TYPE);
5
emp_rec emp_rec_type;
6 begin
7
update employees
8
set salary = salary * 1.05
9
where employee_id = 106
10
returning last_name, salary into emp_rec;
11
-- Ausgabe mit returnierten Werten erzeugen
12
dbms_output.put_line(
13
'Das Gehalt für Mitarbeiter ' || emp_rec.last_name ||
14
' beträgt nun Taler ' || emp_rec.salary);
15* end;
Das Gehalt für Mitarbeiter Pataballa beträgt nun Taler 5040
PL/SQL-Prozedur erfolgreich abgeschlossen.
Listing 9.7 Returning-Klausel in einer Update-Anweisung
9.1.5
Alternative zum Record: Objekt
Als Alternative zum Record ist auch ein selbst definierter Datentyp denkbar,
denn dieser Typ kann ebenfalls mehrere unterschiedliche Datentypen zu einem
Konstrukt zusammenfassen. Mit diesem Typ machen wir den Schritt in die
Objektorientierung, die seit Version 8 langsam, aber unaufhaltsam Einzug in die
Oracle-Datenbank gehalten hat. Der Fokus dieses Abschnittes ist es aber nicht,
diese Technik im Detail zu beschreiben, daher soll nur ein kurzes Beispiel für die
Verwendung eines simplen Typs gegeben werden, und zwar insofern, als sich
dessen Verwendung mit der eines Records deckt. Zur Deklaration eines Typs
wird eine SQL-Anweisung verwendet, dieser Typ ist also ein Datenbankobjekt
und mithin schemaweit sichtbar (mindestens, denn die Sichtbarkeit kann, wie
bei jedem anderen Datenbankobjekt auch, über Grant-Anweisungen an andere
Benutzer erweitert werden). So ist dann die schlichte Verwendung als RecordErsatz etwas aufwendiger in der Definition, bringt aber eben auch keine wesentlichen Vorteile gegenüber einem Record, sondern erfordert neben der externen
Deklaration auch noch den Aufruf der Konstruktormethode <name_des_typs>(),
um eine Instanz dieses Typs zu erstellen:
SQL> -- In SQL:
SQL> create type my_type is object(
2
name varchar2(80 char),
3
job varchar2(30 char));
4 /
Typ wurde erstellt.
406
1452.book Seite 407 Donnerstag, 5. August 2010 3:55 15
PL/SQL-Kollektionen
Achten Sie darauf, dass SQL*Plus das abschließende Zeichen »/« verlangt, wenn
Sie ein Objekt deklarieren.
SQL> set serveroutput on
SQL> declare
2
local_type my_type;
3 begin
4
local_type := my_type('MÜLLER', 'REVISOR');
5
dbms_output.put_line(local_type.job);
6 end;
7 /
REVISOR
PL/SQL-Prozedur erfolgreich abgeschlossen.
Listing 9.8 Objekt als Record-Alternative
In dieser simplen Variante ist der objektorientierte Typ keine Alternative zum
Record. Er wird hier eher der Vollständigkeit halber erwähnt. Weitere Informationen finden Sie in Kapitel 15, »Objektorientierung«, wo ich diesen Typ nicht
nur genauer erklären, sondern auch bestechendere Einsatzszenarien zeigen
werde. Ein Vorteil ist allerdings schon hier sichtbar: Ein Objekt ist in SQL sichtund benutzbar, ein Record als PL/SQL-Variable nicht!
9.2
PL/SQL-Kollektionen
Nachdem wir uns in Abschnitt 7.4, »Kollektionen in PL/SQL«, bereits mit den
Grundlagen von Kollektionen vertraut gemacht haben, folgen nun einige weiterführende Beispiele und Anwendungsbereiche.
9.2.1
Verwendung von assoziativen Tabellen
Die erste Gruppe von Kollektionen stellen die assoziativen Tabellen dar. Diese
Strukturen, die nur in PL/SQL eingesetzt und nicht in der Datenbank gespeichert
werden können, sind im Grunde Tabellen mit einem Schlüssel- und einem Nutzwert. Der Schlüsselwert dient ausschließlich dazu, einen Nutzwert in der assoziativen Tabelle wiederzufinden. Er muss daher eindeutig sein und kann entweder
eine Zahl oder eine Zeichenkette sein. Sie definieren ein solches assoziatives
Array wie folgt:
SQL> declare
2
type name_tab is table of emp.ename%type
3
index by binary_integer;
4 begin
407
9.2
1452.book Seite 408 Donnerstag, 5. August 2010 3:55 15
9
Das Arbeiten mit Daten
5
6
7
…
end;
/
In diesem Beispiel habe ich als Schlüsselwert eine Zahl verwendet. PL/SQL definiert hier den Datentyp binary_integer, der Ganzzahlen zwischen –2 147 483 648
und 2 147 483 647 erlaubt (32 Bit) und im Übrigen mit dem Datentyp pls_integer
synonym ist. Im Beispiel ist es naheliegend, als Schlüsselwert für die Tabelle die
Mitarbeiternummer aus der Spalte empno zu verwenden, was insofern eine gute
Idee ist, als damit direkt auch klar wird, dass die Schlüsselwerte durchaus nicht
bei 1 beginnen und – wie ein Index – hochgezählt werden müssen, sondern beliebig gewählt werden können. Als Nutzwert habe ich hier eine Zeichenkette (den
Nachnamen des Mitarbeiters) gewählt, aber auch das muss nicht so sein. Sie können beliebige Datentypen verwenden und insbesondere auch einen Record.
Damit ergibt sich eine Datenstruktur, die im Notfall eine ganze Tabelle aufnehmen könnte, wie das folgende Beispiel zeigt:
SQL>
2
3
4
5
6
7
declare
type emp_tab is table of emp%rowtype
index by binary_integer;
begin
…
end;
/
Listing 9.9 Einfache assoziative Tabellen
Durch diese Deklaration könnte sich also endlich ein Weg ergeben, mit ganzen
Tabellendaten auf einmal arbeiten zu können. Doch ist diese Möglichkeit wahrscheinlich nicht der beste Weg, denn Sie müssen im Hinterkopf behalten, dass
die Daten eines Records physikalisch in den Arbeitsspeicher umkopiert werden.
Bei sehr großen Tabellen ist das sicherlich keine gute Idee. Besser wäre es, große
Datenmengen lediglich zu referenzieren und dort zu lassen, wo sie ohnehin
bereits existieren, auf der Platte oder im Hauptspeicher der Datenbank nämlich.
Diese Option steht uns mit den Cursorn zur Verfügung und soll dort auch besprochen werden.
Wenn diese Anwendung also nicht sinnvoll ist, wofür benötigen wir dann assoziative Tabellen? Einerseits ist ihr oben genannter Nachteil auch ein entscheidender Vorteil: Da die assoziativen Tabellen die Daten lokal vorhalten, entfällt (eventuell) der Netzwerk- und Festplattenplatz, den die Daten benötigten, wären sie in
Tabellen gespeichert. Zudem können Sie diese Strukturen in Packages deklarieren und initial mit Daten füllen, die für die Dauer der Session genutzt werden sol-
408
1452.book Seite 409 Donnerstag, 5. August 2010 3:55 15
PL/SQL-Kollektionen
len. Dadurch entfällt das andauernde Nachladen der Daten. Ideal ist diese Datenstruktur also dort, wo eine geringere Datenmenge lokal vorgehalten und schnell
verfügbar gemacht werden muss. Natürlich muss zudem sichergestellt sein, dass
diese Daten während der Dauer der Benutzung nicht angepasst werden müssen,
falls die zugrunde liegenden Daten sich ändern sollten. Ich verwende diese Strukturen zum Beispiel gern in Packages, um Startwerte initial zu laden. Stellen Sie
sich vielleicht eine Liste von Währungskursen vor. Für unser Beispiel soll es ausreichen, dass die Umrechnungskurse der Europäischen Zentralbank zugrunde
gelegt werden, die von der Bank einmal täglich veröffentlicht werden. Diese
Aktion ist natürlich zeitlich aufwendig, da die Kurse über eine entfernte Ressource geladen werden müssen. Andererseits sind nicht viele Kurse zu laden,
sodass es sich anbietet, die Daten lokal vorzuhalten und nur nachzuladen, wenn
sie älter als einen Tag sind. Die Implementierung dieses Beispiels werde ich nachholen, wenn ich Ihnen die objektorientierten Fähigkeiten der Datenbank
genauer vorstelle.
Dann sind assoziative Tabellen perfekt geeignet, um eine Menge von Datenbankzeilen im Code zu berechnen und anschließend in einer Bulk-Operation an die
Datenbank zu übergeben. Da diese Verwendung sehr häufig geschieht, stelle ich
sie im folgenden Abschnitt gesondert vor.
9.2.2
Massenverarbeitung mit assoziativen Tabellen
In Abschnitt 4.8, »Beispiel zum Einfluss der Programmierung«, habe ich in einem
etwas fiktionalen Szenario den großen Einfluss der Programmierstrategie auf die
Performanz und Skalierbarkeit der Anwendung gezeigt. Eine Kernaussage des
Kapitels war, dass Datenbanken mengenorientiert und nicht satzweise programmiert werden sollten. Das Beispiel verwendete folgenden Code-Ausschnitt, um
eine mengenorientierte Programmierung zu realisieren:
SQL> create or replace procedure SQL_performance_test_5
2 as
3
type value_table_type is table of pls_integer
4
index by binary_integer;
5
value_table value_table_type;
6
iterations integer := 10000;
7 begin
8
for i in 1..iterations loop
9
value_table(i) := i;
10
end loop;
11
forall indx in 1 .. iterations
12
insert into test_table values(value_table(indx));
13
commit;
409
9.2
1452.book Seite 410 Donnerstag, 5. August 2010 3:55 15
9
Das Arbeiten mit Daten
14
15
end;
/
Listing 9.10 Verwendung einer assoziativen Tabelle in Bulk-Anweisungen
Mit dem bislang aufgebauten Wissen über PL/SQL können wir diesen Code nun
deutlich besser verstehen: Wir erzeugen eine assoziative Tabelle value_table_
type, von dem wir die Variable value_table ableiten. Anschließend kopieren wir
die Zahlen von 1 bis 10.000 in diese assoziative Tabelle. Wir wissen nun, dass
diese Daten mithin nicht in die Datenbank, sondern im Arbeitsspeicher gespeichert werden (müssen, das vermerken wir hier direkt als Nachteil dieser
Methode). Anschließend wird die assoziative Tabelle als Ganzes der InsertAnweisung übergeben, die anschließend die Werte in einem Rutsch in die Datenbanktabelle einfügt. Inhaltlich entsteht folgender Pseudocode:
insert into test_table
select *
from value_table;
Listing 9.11 Pseudocode einer Bulk-Anweisung
Die entscheidende Anweisung ist die Forall-Anweisung in Zeile 11. Sehen wir
uns diese Anweisung etwas genauer an. Auch wenn der Eindruck erweckt wird,
ist die Forall-Anweisung keine Schleife, sondern eine Anweisung, die dazu dient,
den übergebenen Parameter in Form einer SQL-Anweisung als dynamisches SQL
auszuführen und dabei in sinnvoller Weise die übergebenen Parameterwerte zu
binden. Die Verwendung dieser Anweisung erfordert eine gewisse Vorarbeit,
denn die einzufügenden Parameter müssen als Kollektion vorliegen, in unserem
Fall als assoziative Tabelle, es geht aber auch eine geschachtelte Tabelle. Im Fall
einer assoziativen Tabelle funktionieren allerdings lediglich solche mit einem
pls_integer-Schlüsselwert, nicht jedoch solche mit einem varchar2-Schlüssel.
Zudem existieren einige Einschränkungen bezüglich des SQLs und der Referenz
auf die Schlüsselwerte, die sich vielleicht so zusammenfassen lassen:
왘 Verwenden Sie ein SQL, das so einfach wie irgend möglich ist.
왘 Verweisen Sie auf die Indizes lediglich in einfacher (und nicht berechneter)
Form, sagen Sie also value_table(indx) und nicht value_table(indx+1).
왘 Verwenden Sie eine Kollektion nicht sowohl in der Spaltenliste einer UpdateAnweisung als auch in der Where-Klausel der Update-Anweisung.
Die konkreten Einschränkungen variieren mit der Datenbankversion und beziehen sich auch auf Randbereiche wie z.B. spärlich besetzte Kollektionen etc. Zum
410
1452.book Seite 411 Donnerstag, 5. August 2010 3:55 15
PL/SQL-Kollektionen
tieferen Verständnis dieser Einschränkungen möchte ich gern auf die OnlineDokumentation (PL/SQL Language Guide, FORALL-Statement) verweisen, denn
sie sind zum Teil recht speziell und Erläuterungen dazu würden den Rahmen des
Buches definitiv sprengen.
In dieser einfachen Form wie im Beispiel oben haben wir einen hohen Preis für
die Optimierung der Insert-Anweisung gezahlt, denn wir müssen immerhin alle
10.000 Zeilen im Arbeitsspeicher vorhalten. In der Praxis wird so etwas eher selten gemacht, denn die Optimierung stellt sich als Balance zwischen zwei widerstrebenden Anforderungen dar, wie in Abbildung 9.1 schematisch dargestellt ist:
Auf der einen Seite soll der Durchsatz maximal, auf der anderen Seite aber der
Arbeitsspeicherbedarf minimal sein. Bei meinen Untersuchungen zu diesem
Thema bin ich auf folgenden, groben Zusammenhang gestoßen: Die Optimierungseffekte sind bereits bei relativ kleinen Bulkgrößen sehr deutlich spürbar und
nehmen bei größeren Bulkgrößen nicht linear zu, sondern steigen zunehmend
langsamer. So gibt es also ein Optimum, das von vielen Faktoren abhängt, aber
bei meinen Szenarien, die ich getestet habe, bereits bei wenigen 100 Zeilen pro
Bulk absolut akzeptable Ergebnisse erzielte. Ich gehe sicher davon aus, dass die
Zeilenlänge eine ebenso wesentliche Rolle spielt wie die gesamte zur Verfügung
stehende Infrastruktur (Arbeitsspeicher, Prozessorgeschwindigkeit und -anzahl,
Netzwerk- und Plattengeschwindigkeit etc.), daher möchte ich keine konkrete
Empfehlung geben. Wichtiger ist: Wie kontrollieren Sie die Größe des Bulks überhaupt?
Zeit
Arbeitsspeicherbedarf
Bulkgröße
Abbildung 9.1 Abhängigkeit der Ausführungsgeschwindigkeit von der Bulkgröße
411
9.2
1452.book Seite 412 Donnerstag, 5. August 2010 3:55 15
9
Das Arbeiten mit Daten
Die einfachste Variante ist natürlich, die lokale Kollektion immer nur mit entsprechend vielen Zeilen zu füllen und die Forall-Anweisung bei Bedarf aufzurufen. Dieses Codebeispiel liefert zudem endlich einmal eine sinnvolle Anwendung
der MOD-Funktion:
SQL> set timing on
SQL> create or replace procedure SQL_performance_test_5
2 as
3
type value_table_type is table of pls_integer
4
index by binary_integer;
5
value_table value_table_type;
6
iterations integer := 10000;
7
idx integer := 0;
8 begin
9
for i in 1..iterations loop
10
idx := idx + 1;
11
value_table(idx) := i;
12
if mod(i, 100) = 0 or i = iterations then
13
forall indx in 1 .. 100
14
insert into test_table values(value_table(indx));
15
value_table.delete();
16
idx := 0;
17
end if;
18
end loop;
19
commit;
20 end;
21 /
Prozedur wurde erstellt.
SQL> call SQL_performance_test_5();
Aufruf wurde abgeschlossen.
Abgelaufen: 00:00:00.14
Listing 9.12 Balancierung einer Bulk-Anweisung zwischen Performanz und
Ressourcenverbrauch
Eine andere Möglichkeit besteht darin, die Forall-Anweisung so anzupassen, dass
nur Teilmengen eingefügt werden. Dies kann dadurch erreicht werden, dass
eben andere Begrenzungszahlen eingefügt werden. Doch hilft das nicht bei unserem Problem mit dem Arbeitsspeicher, denn auch in diesem Fall müsste die Kollektion ja komplett aufgebaut im Arbeitsspeicher vorhanden sein. Sie sehen, dass
einiger Aufwand betrieben werden muss, um diese Performanzvorteile zu nutzen. Andererseits ist der Erfolg aber auch so spektakulär, dass sich der Aufwand
lohnt.
412
1452.book Seite 413 Donnerstag, 5. August 2010 3:55 15
PL/SQL-Kollektionen
Fehlerbehandlung bei Bulk-Operationen
Beim Schreiben im Bulk stellt sich zudem noch ein wesentliches Problem: Wie
wird mit Fehlern umgegangen, die die Schreibprozesse ausführen? Hier stehen
wieder einmal mehrere Möglichkeiten zur Verfügung. Bei der »normalen« Verwendung, wie im vorigen Abschnitt beschrieben, werden eventuelle DML-Fehler
sofort ausgelöst und die gesamte Aktion rückgängig gemacht. Allerdings steht
eine optionale Klausel zur Verfügung, die dieses Verhalten ändert und alle auftretenden Fehler bis zum Ende der Forall-Anweisung speichert, die erfolgreichen
Aktivitäten jedoch belässt:
forall indx in 1 .. 100
insert into test_table values(value_table(indx))
save exceptions;
Mit dieser zusätzlichen Klausel wird bei eventuell auftretenden Fehlern ein Attribut bulk_exceptions (für den Cursor SQL) gefüllt, das nach der Ausführung abgefragt werden kann. Am Ende der Arbeit wird dann nur noch ein einzelner Fehler
(ora-24381) ausgelöst, der anzeigt, dass überhaupt Fehler während der Verarbeitung aufgetreten sind. Dieser Fehler muss im Code zunächst abgefangen werden,
damit im Exception-Teil des Codes die Fehlerliste explizit geprüft werden kann:
SQL>
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
create or replace procedure SQL_performance_test_5
as
type value_table_type is table of pls_integer
index by binary_integer;
value_table value_table_type;
iterations number := 10000;
err_amount number;
dml_errors exception;
pragma exception_init(dml_errors, -24381);
procedure print(text in varchar2)
as
begin
dbms_output.put_line(text);
end;
begin
for i in 1..iterations loop
value_table(i) := i;
end loop;
forall indx in 1 .. iterations
save exceptions
insert into test_table values(value_table(indx));
commit;
exception
413
9.2
1452.book Seite 414 Donnerstag, 5. August 2010 3:55 15
9
Das Arbeiten mit Daten
24
when dml_errors then
25
err_amount := sql%bulk_exceptions.count;
26
print('Fehler während der Ausführung: ' || err_amount);
27
for I in 1 .. err_amount loop
28
print('.- Nr. ' || i || ', Iteration: ' ||
29
sql%bulk_exceptions(i).error_index);
30
print('. Meldung: ' ||
31
sqlerrm(-sql%bulk_exceptions(i).error_code));
32
end loop;
33 end;
34 /
Prozedur wurde erstellt.
Um bei der Ausführung dieser Prozedur nun Fehler provozieren zu können, nehmen wir folgende Veränderungen vor: Zum einen wird die Tabelle auf Zahlen bis
9999 begrenzt, zum anderen füge ich eine Zeile ein und verschärfe das Datenmodell durch einen Unique-Constraint:
SQL> alter table test_table add constraint u_test_table unique(id);
Tabelle wurde geändert.
SQL> alter table test_table modify (id number(4,0));
Tabelle wurde geändert.
SQL> insert into test_table values(123);
1 Zeile wurde erstellt.
SQL> commit;
Transaktion mit COMMIT abgeschlossen.
Nun sollte ein erneutes Ausführen der Prozedur einen Fehlerbericht ergeben:
SQL> set serveroutput on
SQL> call sql_performance_test_5();
Fehler während der Ausführung: 2
.- Nr. 1, Iteration: 123
. Meldung: ORA-00001: Unique Constraint (.) verletzt
.- Nr. 2, Iteration: 10000
. Meldung: ORA-01438: Wert größer als die angegebene
Gesamststellenzahl, die für
diese Spalte zulässig ist
Aufruf wurde abgeschlossen.
Listing 9.13 Bulk-Anweisung mit Fehlerbehandlung
Bulk-Select
Sehen wir uns noch eine weitere Anwendung an, die ebenfalls häufig verwendet
wird: das Lesen im Bulk. Bei dieser Anwendung müssen viele Werte aus einer
414
1452.book Seite 415 Donnerstag, 5. August 2010 3:55 15
PL/SQL-Kollektionen
Tabelle in eine lokale Variable umkopiert werden. Es soll kein Cursor eingesetzt
werden, denn die Daten sollen z.B. in einem Package persistiert werden. In diesem Fall bietet es sich an, die Leseoperation im Bulk durchzuführen, wie im folgenden Beispiel, in dem die Tabelle dept in eine lokale geschachtelte Tabelle
umkopiert werden soll:
SQL> declare
2
type dept_tab_t is table of dept%rowtype;
3
dept_tab dept_tab_t;
4 begin
5
select *
6
bulk collect into dept_tab
7
from dept
8
order by deptno;
9 end;
10 /
PL/SQL-Prozedur erfolgreich abgeschlossen.
Listing 9.14 Bulk-Select-Anweisung
Das liest sich gut: Hier ist es lediglich erforderlich, eine Kollektion im richtigen
Typ zur Aufnahme der Daten anzubieten und anschließend die Select-Anweisung
um die Klausel bulk collect into zu erweitern. Achten Sie aber auch hier auf den
Speicherverbrauch: Dieses Verfahren ist nicht geeignet, um SQL »besser« zu
machen. Die mit einer SQL-Anweisung verbundenen Datenmengen können
wirklich erheblich sein. Diese Anweisung kopiert diese Daten in den Arbeitsspeicher, und zwar so lange, bis dieser dicke Backen macht. Zudem müssen Sie überlegen, ob Sie bei der Ausführung einer solchen Anweisung noch andere Benutzer
auf der Datenbank haben, die vielleicht Ähnliches vorhaben. Konkret empfehle
ich diese Anweisung, wenn Sie ohnehin eine lokale Kopie der Daten vorhalten
müssen. Dann kommen Sie um den Speicherplatz nicht herum, und dann ist
diese Art der Anweisung wahrscheinlich schneller als die »normale« Iteration
über einen Cursor.
9.2.3
Tabellenfunktionen (PIPELINED-Functions)
Prozeduren oder Funktionen, die Daten, z.B. in einer Schleife, berechnen, tun
dies im Normalfall komplett, bevor sie das Ergebnis zurückliefern. Dieses Verhalten hat den Vorteil, dass es einfach zu implementieren ist, und ist normalerweise
auch ausreichend. In Datenbanken jedoch stehen wir oft vor dem Problem, sehr
große Datenmengen bearbeiten zu müssen. Werden diese großen Datenmengen
durch eine PL/SQL-Funktion geschleust, kann das zur Folge haben, dass die
Anforderungen an den Arbeitsspeicher die Bearbeitung stark verlangsamen oder
415
9.2
1452.book Seite 416 Donnerstag, 5. August 2010 3:55 15
9
Das Arbeiten mit Daten
eventuell sogar unmöglich machen können. Zudem gibt es häufig Situationen, in
denen die Ergebnisse einer Prozedur durch andere Anweisungen weiterverarbeitet werden müssen. Es ist häufig möglich, bereits mit Teilergebnissen weiterzuarbeiten, die Funktion muss also nicht komplett bearbeitet werden, bevor der
nächste Arbeitsschritt ausgeführt werden kann. In solchen Situationen wäre es
sinnvoll, die Teilergebnisse bereits an einen anderen Prozessor weiterzureichen,
während die Funktion noch andere Informationen bearbeitet.
Für solche Funktionen können Funktionen so definiert werden, dass sie Teilergebnisse aus der laufenden Funktion heraus zurückliefern. Damit dies funktioniert, muss die Funktion einen Kollektionstyp zurückliefern und mit der Klausel
pipelined definiert werden. Vielleicht ist es auch hier wieder das Einfachste,
wenn ich Ihnen ein Beispiel zeige. Die XML-Datenbank (XDB) stellt Ihnen eine
Möglichkeit zur Verfügung, Daten in einer Ordnerhierarchie innerhalb der
Datenbank zu speichern. Letztlich wird dabei für jeden Ordner eine Zeile in einer
Datenbanktabelle angelegt und mittels des Protokolls WebDAV so angezeigt, als
sei dies ein Dateisystem. Sie können neue Ordner in dieser Tabelle anlegen,
unterliegen dabei aber der Beschränkung, dass nur Unterordner in bestehenden
Ordnern angelegt werden können. Möchten Sie also auf einen Rutsch ein Verzeichnis, z.B. /etc/examples/pl_sql/chap_2 anlegen, so müssen Sie zunächst
sicherstellen, dass das Verzeichnis /etc, dann das Verzeichnis /etc/examples
und so weiter existiert. Existieren diese Verzeichnisse nicht, so müssen sie nacheinander angelegt werden.
Um nun also beliebige Ordner anlegen zu können, benötigen wir eine Funktion,
die uns diese Ordner in der entsprechenden Reihenfolge anlegt. Zwar können
wir uns eine Funktion vorstellen, die in einer Schleife die Einzelbestandteile der
Pfade ausgibt. Doch ist die Funktion dann doch etwas unhandlich, denn wir
benötigen ja eigentlich nicht die einzelnen Bestandteile, sondern eine Liste von
Ausgaben dieser Form:
/etc
/etc/examples
/etc/examples/pl_sql
/etc/examples/pl_sql/chap_2
Außerdem möchten wir mit diesen Daten anschließend eine Insert-Anweisung
ausstatten, die dann die einzelnen Verzeichnisse anlegt. Wie lösen wir ein solches Problem? Vielleicht sollten wir eine Schleife schreiben, die die einzelnen
Werte der Insert-Anweisungen berechnet und diese dann im Schleifendurchlauf
aufruft. Das ist sicher eine Möglichkeit, doch hätte dies zur Folge, dass wir mehrfach Insert-Anweisungen für einzelne Zeilen aufrufen. Schöner wäre, wir könnten eine Insert-Anweisung für alle Zeilen aufrufen. Eine bessere Variante wäre,
416
1452.book Seite 417 Donnerstag, 5. August 2010 3:55 15
PL/SQL-Kollektionen
diese Informationen im Bulk an die Datenbank zu senden, wie wir dies in
Abschnitt 9.2.2, »Massenverarbeitung mit assoziativen Tabellen«, erläutert
haben. Alternativ können wir dieses Verhalten aber auch mit einer Funktion
erzeugen, die sich für die Insert-Anweisung wie eine Tabelle verhält und eine
Anweisung der Form
insert into zieltabelle
select * from quelltabelle;
ermöglicht. Lassen Sie uns an diesem Beispiel einmal eine solche Funktion erstellen. Zunächst einmal benötigen wir einen Rückgabewert der Funktion, der in der
Lage ist, eine Kollektion von Werten aufzunehmen. In unserem Beispiel wäre das
also eine Tabelle von Zeichenketten. Wir erzeugen also zunächst diesen Rückgabetyp:
SQL> create type varchar_tab as table of varchar2(2000 char);
2 /
Typ wurde erstellt.
Anschließend können wir eine Funktion deklarieren, die diesen Datentyp
zurückliefert und mit der Klausel pipelined zu einer Tabellenfunktion wird:
SQL> create or replace function get_sub_pathes(
2
path in varchar2)
3
return varchar_tab pipelined
4 as
5 begin
6
return;
7 end get_sub_pathes;
8 /
Funktion wurde erstellt.
Listing 9.15 Grundgerüst einer Tabellenfunktion
Durch diese Deklaration ändern sich einige Dinge an der Art, die Funktion zu
programmieren. Zunächst einmal enthält die Return-Klausel nun keinen Wert
mehr, sondern steht allein, und zwar so, wie Sie das in Zeile 6 des Listings sehen
können. An den Stellen (typischerweise natürlich innerhalb einer Schleife), an
denen die Funktion Werte zurückliefern soll, wird nun das Schlüsselwort pipe
row sowie der Rückgabewert in Klammern eingefügt. Durch diese Anweisung
wird die Funktion die Werte an die aufrufende Umgebung zurückliefern. Der
Datentyp, der zurückgeliefert wird, muss dabei dem Basistyp der Kollektion entsprechen. In unserem Fall ist dies also eine Zeichenkette bis 2000 Byte Länge.
417
9.2
1452.book Seite 418 Donnerstag, 5. August 2010 3:55 15
9
Das Arbeiten mit Daten
Sehen wir nun also die Funktion an, die unsere Zeichenkette in Einzelteile zerschneidet und die Einzelteile an die aufrufende Umgebung zurückliefert:
SQL> create or replace
2
function get_sub_pathes(path in varchar2)
3
4
return varchar_tab pipelined
as
5
idx pls_integer := 1;
6
sub_path varchar2(2000);
7
begin
8
while idx > 0 loop
9
idx := instr(path, '/', idx + 1); -- ignore root slash
10
case idx
11
when 0 then sub_path := path;
12
else sub_path := substr(path, 1, idx - 1);
13
end case;
14
pipe row (sub_path);
15
end loop;
16
return;
17
end get_sub_pathes;
18
/
Funktion wurde erstellt.
SQL> select column_value
2
from table(
get_sub_pathes(
'/etc/examples/pl_sql/chap_2'));
COLUMN_VALUE
----------------------------------------------------------------/etc
/etc/examples
/etc/examples/pl_sql
/etc/examples/pl_sql/chap_2
Listing 9.16 Beispiel einer Tabellenfunktion
Wie Sie sehen, werden die Unterpfade als Zeichenkette während der Iterationen
aus der Funktion heraus geliefert. Dies hat den Vorteil, dass der durch das Ergebnis allozierte Speicherbereich freigegeben und das Ergebnis durch die aufrufende
Umgebung weiterbearbeitet werden kann. In unserem Fall sammeln wir die
Ergebnisse und fügen sie in einem Rutsch im Rahmen einer Insert-Anweisung in
eine Tabelle ein. Beachten Sie in unserem Beispiel bei der nachfolgenden Abfrage
die Verwendung des Konstruktors table(), der das Ergebnis der Funktion in
418
1452.book Seite 419 Donnerstag, 5. August 2010 3:55 15
PL/SQL-Kollektionen
eine für SQL verwertbare Tabelle umformt. Bei dieser Umformung wird der
Rückgabewert in einer Tabelle mit dem Spaltennamen column_value überführt,
der dann durch SQL abgefragt werden kann. Die Funktion ist im Übrigen auch
ein schönes Beispiel für die Verwendung einer While-Schleife, denn die Anzahl
der Iterationen durch die Schleife ist nicht bekannt, sondern vom übergebenen
Parameter abhängig, andererseits aber auch gut außerhalb der Schleife überprüfbar und daher besser als eine Exit-Klausel im Schleifenkörper.
Eine andere, sehr witzige Anwendung einer solchen Prozedur stellt die Verwendung als Bindevariable für Listen dar. Das Problem: Sie sollten Bindevariablen
benutzen, um eine SQL-Anweisung im Library-Cache der Datenbank wiederauffindbar zu halten und dadurch die Anzahl der Parse-Vorgänge niedrig zu halten.
Gut. Aber wie machen Sie das bei einer Anweisung wie der folgenden?
SQL> select *
2
3
from emp
where job in ('MANAGER', 'ANALYST');
Wie sollen Sie nun die Liste der Berufe als Bindevariable übergeben? Wenn eine
Variable die Liste der Werte enthält, suchen wir auch nach einem Beruf, der so
heißt wie die Liste der Berufe, die übergeben wird. Das geht also nicht. Stattdessen benötigen wir einen Weg, der SQL-Anweisung klarzumachen, dass eine Liste
von Werten durchsucht werden soll. Die »beweglichen Teile«, die wir benötigen,
um das Problem zu lösen, haben wir eigentlich schon programmiert, denn unsere
Tabellenfunktion von vorhin könnte nach einer kleinen Modifikation ja statt
eines wachsenden Pfades gern auch die einzelnen Ordnernamen ausgeben. Dann
könnten wir die Funktion im Kontext der obigen Abfrage wie eine Bindevariable
für Listenwerte nutzen, und zwar gleich auf zwei Arten:
select *
from emp e, table(string_to_tab(csv_liste)) l
where e.job = l.column_value;
oder:
select *
from emp
where job in (select column_value
from table(
string_to_tab(csv_liste)));
Dabei bestehen gute Chancen, dass der Optimizer der Datenbank beide Varianten mit dem gleichen Ausführungsplan belegt. Im obigen Pseudocode ist der
Parameter csv_liste eine Variable mit einer kommaseparierten Liste der einzel-
419
9.2
1452.book Seite 420 Donnerstag, 5. August 2010 3:55 15
9
Das Arbeiten mit Daten
nen Berufe, die durch die (neu zu schreibende) Tabellenfunktion string_to_tab
in eine virtuelle Tabelle mit den Berufen umgebaut wird. Die Implementierung
dieser neuen Funktion (vielleicht möchten Sie die Funktion auch gleich für Zahlen und Datumsangaben programmieren?) überlasse ich Ihnen als Übung. Einen
Hinweis hätte ich aber zu dieser Aufgabe noch: Das Umwandeln einer kommaseparierten Liste in eine assoziative Tabelle liefert Ihnen Oracle mit: Sehen Sie sich
einmal die Prozedur dbms_utility.comma_to_table an. Dann bliebe nur noch die
Ausgestaltung als Tabellenfunktion.
Tabellenfunktionen werden laut Oracle-Dokumentation vor allem im Bereich
von Datenwarenhäusern empfohlen, um die Verarbeitung von Daten in Queues
zu optimieren. Die Idee: Große Datenmengen werden durch eine Funktion iterativ verarbeitet und an die nächste Verarbeitungsstufe weitergegeben. Dadurch
sinkt die Speicherbelastung, und es steigt die Fähigkeit zur Parallelisierung der
Aufgabe, weil andere Prozessoren die Teilergebnisse bereits verarbeiten können,
während die Funktion noch an der vorhergehenden Stufe arbeitet. Das ist sicherlich richtig, doch möchte ich den Einsatzbereich dieser Funktionen gern erweitern und Ihnen dieses Konzept auch für andere Aufgaben ans Herz legen. Um
Ihnen ein weiteres Einsatzfeld für solche Funktionen aufzuzeigen, programmieren wir uns einen Monatsgenerator, der in der Lage ist, eine beliebige Anzahl
Monate ab einem Startmonat zu liefern. Beginnen wir also wieder mit einem Kollektionstyp für Datumsangaben:
SQL> create type date_tab as table of date;
2 /
Typ wurde erstellt.
Nun folgt die Funktion. Sie erwartet ein Startdatum und eine Anzahl von Monaten, die erzeugt werden sollen:
SQL> create or replace function get_months (
2
start_month in date,
3
month_amount in pls_integer)
4
return date_tab pipelined
5 as
6 begin
7
for i in 1 .. month_amount loop
8
pipe row (add_months(start_month, i));
9
end loop;
10
return;
11 end get_months;
12 /
Funktion wurde erstellt.
420
1452.book Seite 421 Donnerstag, 5. August 2010 3:55 15
PL/SQL-Kollektionen
SQL> select column_value monat
2
from table(
get_months(
to_date('01.01.1980', 'dd.mm.yyyy'), 23));
MONAT
-------01.02.80
01.03.80
…
01.12.81
24 Zeilen ausgewählt.
Listing 9.17 Verwendung einer Tabellenfunktion als virtuelle Tabelle
Beachten Sie wiederum die Verwendung der Pseudospalte column_value in der
Select-Anweisung. Mithilfe dieser Funktion können Sie nun beliebig viele
Monate abrufen, ohne diese in einer Tabelle vorhalten zu müssen. Fragen Sie
sich, wozu eine solche Liste von Monaten gut sein soll? Nun, vielleicht benötigen
Sie eine Auswertung der Anzahl der Einstellungen in Ihr Unternehmen, gruppiert nach Monat. Was geschieht nun, wenn in einem Monat keine Mitarbeiter
eingestellt wurden? In diesem Fall würde kein Ergebnis für diesen Monat ausgegeben werden. Um dies aber zu erfahren, benötigen Sie eine Referenztabelle, die
alle Auswertungsmonate enthält. Folgende, stark vereinfachte Auswertung könnten Sie also verwenden:
SQL> select d.column_value monat,
2
count(trunc(hiredate, 'MM')) einstellungen
3
from emp e right join
4
table(
5
get_months(
6
to_date('01.01.1980', 'dd.mm.yyyy'), 23)) d
7
on trunc(e.hiredate, 'MM') = d.column_value
8
group by d.column_value;
MONAT
EINSTELLUNGEN
-------- ------------01.04.80
0
01.09.80
0
01.10.80
0
01.10.81
0
01.12.81
2
…
01.04.81
1
23 Zeilen ausgewählt.
Listing 9.18 Einsatz einer Tabellenfunktion
421
9.2
1452.book Seite 422 Donnerstag, 5. August 2010 3:55 15
9
Das Arbeiten mit Daten
Tabellenfunktionen verhalten sich also in gewisser Weise wie normale Datenbanktabellen, die in SQL-Abfragen verwendet werden können. Und sie haben es
sich wahrscheinlich schon gedacht: Daher rührt auch der Name. Weil aber Tabellenfunktionen darüber hinaus auch Kollektionen oder Cursor als Eingabeparameter akzeptieren, können sie in einem Workflow eingesetzt werden, in dem die
Ergebnisse z.B. einer SQL-Abfrage iterativ an die Tabellenfunktion und von dort
an eine weitere SQL-Anweisung weitergeben wird, und das ohne übermäßige
Speicherbelastung, denn die kompletten Ergebnisse der Prozedur werden niemals materialisiert, sondern lediglich die einzelnen Teilergebnisse. Das folgende
Beispiel zeigt, wie die Ergebnisse einer Tabellenfunktion in eine weitere Tabellenfunktion gelangen und von dort in eine SQL-Anweisung geleitet werden.
Allerdings liegen diese doch recht speziellen Einsatzgebiete etwas außerhalb des
Fokus dieses Buches, daher dient das folgende Beispiel eher der Demonstration
solcher Konstrukte:
select * from table(f(cursor(select * from table(g()))));
Beachten Sie, wie durch den Konstruktor cursor() aus einer SQL-Ergebnismenge
eine Cursor-Instanz generiert wird, die wiederum als Eingabeparameter der
Tabellenfunktion f genutzt werden kann.
Zum Schluss noch einen etwas obskur wirkenden Hinweis im Zusammenhang
mit Tabellenfunktionen: Es gibt einen Fehler, der, so er denn auftritt, zwar
geworfen wird, das Programm aber nicht zum Abbruch zwingt. Dieser Fehler hat
den Namen no_data_needed und wird immer dann geworfen, wenn ein, in einer
Tabellenfunktion geöffneter, Cursor nicht komplett gelesen wird. Es führt wahrscheinlich ein wenig zu weit, die Interna dieses Fehlers genauer zu beleuchten.
Ich möchte lediglich aus dieser Tatsache den Tipp für Sie ableiten, zum Ende
einer Tabellenfunktion stets folgenden Exception-Handler zu schreiben:
exception
when no_data_needed
return;
Listing 9.19 Fehlerhandler in Tabellenfunktionen
Sie stellen mit diesem Fehlerhandler sicher, dass keine Speicherressourcen offengelassen werden und der Cursor der Tabellenfunktion unter allen Umständen
sauber geschlossen wird.
422
1452.book Seite 423 Donnerstag, 5. August 2010 3:55 15
Mengenverarbeitung mit Cursorn
9.3
Mengenverarbeitung mit Cursorn
Die grundlegenden, syntaktischen Details zur Arbeit mit Cursoren haben wir uns
ja bereits in Abschnitt 7.4, »Kollektionen in PL/SQL«, angesehen. In diesem
Abschnitt sollen nun Anwendungsbeispiele und weitergehende Konzepte folgen.
Wir werden uns ansehen, auf welche Weise die zum Teil ja immens großen
Datenmengen, die durch einen Cursor repräsentiert werden können, am besten
verwaltet werden.
9.3.1
Implizite versus explizite Cursor
Diskussionen unter PL/SQL-Fachleuten drehen sich zuweilen um die Frage, ob
ein Cursor implizit oder immer explizit definiert und genutzt werden sollte. Aus
meiner Sicht handelt es sich bei der Diskussion eigentlich um zwei Diskussionen,
denn einerseits bezieht sie sich darauf, ob stets ein Cursor definiert und abgefragt
werden solle oder eine SQL-Abfrage direkt in PL/SQL eingebunden werden
sollte, andererseits darauf, ob die verkürzte Schreibweise einer Cursor-ForSchleife der expliziten Kontrolle eines Cursors mit open, fetch und close bevorzugt werden sollte. Sehen wir uns beide Bereiche an: Ich nenne die erste Diskussion die Frage nach impliziten oder expliziten Cursorn, die zweite die Diskussion
um implizite oder explizite Cursorkontrolle.
Implizite oder explizite Cursor
Um eine Tabellenzelle aus der Datenbank in eine PL/SQL-Variable umzukopieren, stehen im Grunde zwei Möglichkeiten zur Wahl. Einerseits können wir
einen Cursor definieren, diesen explizit öffnen, eine Zeile herausholen und den
Cursor anschließend schließen. Dies wäre ein expliziter Cursor, denn wir haben
den Cursor unter voller Kontrolle. Die Alternative wäre eine einfache Select-IntoAnweisung, die das Ergebnis direkt in eine PL/SQL-Variable kopiert. Welcher
Weg ist besser?
Gerade in früheren Zeiten wurde der expliziten Methode deutlich der Vorzug
gegeben. Der Grund: Bei der Verwendung eines impliziten Cursors kann die Fehlermeldung too_many_rows auftauchen, wenn die Abfrage mehr als nur eine Zeile
zurückliefert. Um diese Fehlermeldung zu erzeugen – so wurde argumentiert –,
muss die Datenbank mindestens zwei Fetch-Operationen auf den impliziten Cursor durchführen, um die weitere Zeile zu lesen. Dieses Problem existiert beim
expliziten Cursor nicht, denn dort wird ja explizit gelesen. Zum Glück ist dieses
Argument schon seit sehr langer Zeit nicht mehr stichhaltig: Bereits Version 7.1
führte den Pre-Fetch-Mechanismus beim Lesen von Cursorn ein, eine Art
Caching, wie es auch beim Lesen von Festplatten etc. durchgeführt wird. Dieser
423
9.3
1452.book Seite 424 Donnerstag, 5. August 2010 3:55 15
9
Das Arbeiten mit Daten
Mechanismus liest ohnehin mehrere Zeilen in einem Durchgang vom Cursor und
kann so auch den oben beschriebenen Fehler direkt aufdecken. Doch gibt es auch
ohne dieses Argument noch einige Pros und Contras, die wir uns im Folgenden
etwas näher ansehen sollten.
Sehen wir uns die beiden Implementierungen im Vergleich einmal an:
SQL> declare
2
emp_id emp.empno%type;
3 begin
4
select empno
5
into emp_id
6
from emp
7
where ename = 'KING';
8 exception
9
when no_data_found then raise;
10
when too_many_rows then raise;
11 end;
12 /
PL/SQL-Prozedur erfolgreich abgeschlossen.
Und nun das Ganze explizit:
SQL> declare
2
cursor emp_cur (name in varchar2) is
3
select empno
4
from emp
5
where ename = name;
6
emp_id emp_cur%rowtype;
7 begin
8
open emp_cur('KING');
9
fetch emp_cur into emp_id;
10
close emp_cur;
11 end;
12 /
PL/SQL-Prozedur erfolgreich abgeschlossen.
Listing 9.20 Vergleich impliziter versus expliziter Cursor
Der Nachteil der impliziten Variante ist sicher die Verpflichtung, auftretende
Fehler abfangen zu müssen. Insbesondere sind es zwei Fehler, die auftreten können: Es wird keine Zeile gefunden, oder es werden mehrere Zeilen gefunden.
Diese Fehler müssen durch einen Exception-Block aufgefangen werden. Zwar
ließe sich die Abfrage gegen den Fehler too_many_rows dadurch schützen, dass
zur Where-Klausel noch der Ausdruck and rownum = 1 hinzugefügt wird, um meh-
424
1452.book Seite 425 Donnerstag, 5. August 2010 3:55 15
Mengenverarbeitung mit Cursorn
rere Zeilen zu unterdrücken; ein solcher Ausweg existiert jedoch für den Fehler
no_data_found nicht. Sie mögen sagen, es handele sich ja nun auch um Fehler,
daher ist es richtig, diese im Fehlerbehandlungsteil zu behandeln, doch teile ich
diese Auffassung nur bedingt: Mir scheinen diese »Fehler« eher zu erwartende
Ausnahmen zu sein, denn wenn ich z.B. einen Suchbegriff über eine Oberfläche
einfüge, kann es nun mal sein, dass ich zu diesem Begriff kein Ergebnis finde.
Daher könnte mich dieser Fakt dazu zwingen, den impliziten Cursor in einen
Begin-Exception-Block zu stecken, um die Ausführung der gesamten Prozedur
nicht zu gefährden. Dies ist sicher ein Nachteil gegenüber dem expliziten Cursor,
denn ein Fetch auf einen leeren (aber geöffneten!) Cursor hat keine Fehlermeldung zur Folge, ebenso wenig wie das Öffnen eines Cursors mit leerer Ergebnismenge.
Oft wird einem expliziten Cursor auch der Vorzug gegeben, weil er offensichtlich
effizienter eine Frage zu beantworten scheint als ein impliziter Cursor. Sehen wir
uns stellvertretend einmal eine Existenzprüfung an. Bei diesem Problem soll
geprüft werden, ob in Abteilung 20 überhaupt Mitarbeiter arbeiten oder nicht.
Ein erster Ansatz könnte also auf eine SQL-Anfrage herauslaufen wie etwa diese:
SQL> select count(*) anzahl
2
from emp
3
where deptno = 20;
ANZAHL
---------5
Doch wollen wir eigentlich gar nicht wissen, wie viele Mitarbeiter in der Abteilung arbeiten, sondern es reicht uns, zu wissen, dass mindestens ein Mitarbeiter
dort arbeitet. Gerade bei großen Tabellen ist der Aufwand für die komplette Zählung also unnötig teuer. Daher könnte uns folgender Code helfen:
SQL> declare
2
cursor emp_dept is
3
select 1 found
4
from emp
5
where deptno = 20;
6
emp_found emp_dept%rowtype;
7 begin
8
open emp_dept;
9
fetch emp_dept into emp_found;
10
close emp_dept;
11
if emp_found.found = 1 then
12
dbms_output.put_line('Mitarbeiter gefunden');
13
end if;
425
9.3
1452.book Seite 426 Donnerstag, 5. August 2010 3:55 15
9
Das Arbeiten mit Daten
14 end;
15 /
Mitarbeiter gefunden
PL/SQL-Prozedur erfolgreich abgeschlossen.
Doch liegt hier der Fehler im SQL: Es ist einfach nicht der richtige Weg, eine Prüfung auf Existenz über die Anweisung count(*) durchzuführen, dafür gibt es das
Konstrukt
SQL> select 1 found
2
from dual
3
where exists(select 1
4
from emp
5
where deptno = 20);
FOUND
---------1
und mithin die Möglichkeit, diese Abfrage implizit zu formulieren und direkt in
eine lokale Variable umzukopieren. Zusammenfassend kann man es vielleicht so
formulieren: Es ist nichts Falsches daran, einen Cursor explizit zu formulieren
und zu benutzen. Dagegen spricht im Einzelfall wahrscheinlich die höhere Codemenge, die benötigt wird, um ihn zu deklarieren. Für explizite Cursor spricht die
Fehlertoleranz. Es ist aber auch am Gegenteil nichts falsch, und wenn dadurch
die Menge des Codes reduziert wird, plädiere ich für die lesbarere Variante. Im
Übrigen ist es immer richtig, sich zunächst einmal darüber zu informieren, ob
eine gewisse Aussage nicht auch in SQL formuliert werden kann, denn dieser
Weg ist eigentlich immer der beste.
Implizite oder explizite Cursorkontrolle
Cursor können, wie bereits besprochen, explizit über eine eigene Deklaration
und die Behandlung über open, fetch und close gesteuert, oder aber im Rahmen
von Cursor-For-Schleifen implizit verwaltet werden. Dabei stellt sich die Frage,
ob der eine Weg dem anderen vorzuziehen ist und welche Überlegungen angestellt werden sollten, um die Wahl zwischen den beiden Varianten zu treffen.
Zunächst einmal gibt es Fälle, in denen die Wahl nur durch einen expliziten Cursor erfüllt werden kann. Dies ist zum Beispiel dann der Fall, wenn der Cursor
nicht in einem Rutsch, sondern in mehreren, unabhängigen Teilschritten bearbeitet werden soll. Stellen wir uns dazu eine Funktion vor, die einen Cursor öffnet, die ersten 10 Zeilen des Cursors bearbeitet, anschließend eine andere Aufgabe ausführt und dann weitere 10 Zeilen des Cursors abarbeitet. In diesem Fall
ist die Wahl relativ eindeutig, denn der Cursor einer Cursor-For-Schleife wird
426
1452.book Seite 427 Donnerstag, 5. August 2010 3:55 15
Mengenverarbeitung mit Cursorn
beim Verlassen der Schleife implizit geschlossen und die Ergebnismenge verworfen. Zudem ist es schwierig oder zumindest unnötig aufwendig, eine Position im
Cursor gezielt »anzufahren«, um zum Beispiel dort fortzufahren, wo ein früheres
Öffnen und teilweises Bearbeiten des gleichen Cursors geendet hat. Denken Sie
nur daran, dass zwei gleiche Abfragen nacheinander nicht das gleiche Ergebnis
liefern müssen, um das Problem zu verstehen. Und selbst wenn die Abfragen das
gleiche Ergebnis liefern, ist nicht garantiert, dass die Daten in der gleichen Reihenfolge geliefert werden, falls Sie keine explizite Sortierreihenfolge über eine
Order-By-Klausel vorgegeben haben. Ein explizit verwalteter Cursor ist demgegenüber einfach zu handhaben, denn die einmal erkannte Ergebnismenge bleibt
konstant, bis der Cursor geschlossen wird. Ich kann nun also ohne Schwierigkeiten einen Cursor öffnen, eine Teilmenge verarbeiten, eine andere Arbeit ausführen und anschließend mit der Bearbeitung des Cursors fortfahren. Solche Fälle
sind gar nicht so selten, wie dies auf den ersten Blick erscheinen mag: Stellen wir
uns vor, eine Ergebnismenge bestehe aus 250 Zeilen, die durch einen Cursor auf
der Datenbank erkannt wurden. Davon werden 50 Zeilen an die Oberfläche
gesendet, und erst dann, wenn der Benutzer weitere Daten sehen möchte, fordert er weitere Daten an. Dieser Fall könnte mit einem expliziten Cursor leicht
umgesetzt werden. Ebenso wäre es in diesem Fall falsch, die Anfrage bei Neuanforderung erneut auszuführen: Die große Zeitspanne, die zwischen der ersten
und der zweiten Anfrage verginge, beinhaltete eine zu große Gefahr, unterschiedliche Daten zu liefern.
Ebenso verhält es sich, wenn der Cursor als Cursorvariable von mehreren Funktionen bearbeitet werden soll. Auch hier ist die explizite Verarbeitung günstiger.
Ganz besonders gilt das natürlich für Cursorvariablen, die über Systemgrenzen
hinweg genutzt werden sollen. Diesen speziellen Fall werden wir im nächsten
Abschnitt gesondert betrachten.
Keine Entscheidungshilfe kommt im Übrigen aus Überlegungen zur Performanz
des einen oder anderen Cursortyps: Beide Cursor sind im Großen und Ganzen
gleich schnell. Das hat seinen Grund darin, dass Oracle im Laufe der Jahre große
Anstrengungen unternommen hat, die Arbeit mit Cursorn einfach und intuitiv zu
gestalten. Daher sollte aus meiner Sicht folgende Empfehlung gelten: Verwenden
Sie implizit verwaltete Cursor, solange kein wichtiger Grund gegen diese Typen
spricht. Implizite Cursor sind leichter zu schreiben und zu verstehen, genauso
schnell, und die Gefahr, einen Cursor unbeabsichtigt geöffnet zu lassen und
dadurch Speicherlecks zu riskieren, besteht nicht.
Wo wir allerdings gerade dabei sind: PL/SQL kennt derzeit noch keinen Befehl,
der dem Finally-Block in Java entspräche, also einem Block, der in jedem Fall,
427
9.3
1452.book Seite 428 Donnerstag, 5. August 2010 3:55 15
9
Das Arbeiten mit Daten
auch bei Auftreten eines Fehlers, ausgeführt wird. Daher liegt es bei der Verwendung expliziter Cursor in Ihrer Verantwortung, Cursor im Fehlerfall zu schließen.
Hierzu bietet sich natürlich zum Beispiel der Exception-Block an. Achten Sie aber
darauf, dass alle abgefangenen Fehler auch die offenen Cursor schließen. Allerdings: Cursor, die innerhalb einer Funktion oder Prozedur definiert wurden,
werden, wie alle anderen lokalen Variablen auch, nach Verlassen der Prozedur
auch wieder gelöscht. Daher bezieht sich der Hinweis auf Cursor, die z.B. auf
Package-Ebene definiert wurden und ihre Ergebnismenge für die gesamte Datenbanksession behalten.
Ein eher ästhetisches Argument sei noch einmal wiederholt: Da die Deklaration
eines Cursors irgendwann auf einer SQL-Anweisung beruht, muss diese Anweisung irgendwo hinterlegt werden. Dabei bieten sich mehrere Möglichkeiten an:
왘 in einer Cursor-For-Schleife
Dieser Weg ist aus meiner Sicht nur für sehr kurze SQL-Anweisung anwendbar, längere SQL-Anweisungen stören das Verständnis der Schleife doch sehr.
왘 in einer Cursordeklaration im Deklarationsabschnitt der Prozedur
Dieser Ansatz erscheint mir für kurze bis mittlere SQL-Anweisungen dann in
Ordnung, wenn insgesamt relativ wenige Cursor in der Anwendung verwendet werden oder die Cursor nur von internem Interesse für die Funktion oder
Prozedur sind.
왘 in einem Package
Bei diesem Ansatz werden die Cursor in einem Package gesammelt und dort
im Package-Körper deklariert. Dieses Verfahren bietet sich als zentrale Sammelstelle für SQL-Anweisungen für einen Funktionsbereich an, wenn diese
Anweisungen nicht nur interne Bedeutung haben.
왘 als Views in der Datenbank
Dies ist sicher der komfortabelste Weg, denn in diesem Umfeld können Sie
die Anweisungen jederzeit testen, ohne die beteiligten Funktionen oder Prozeduren aufrufen zu müssen. Zudem sind Views gut zu warten und zu dokumentieren. Dieser Ansatz hat seine Schwächen, wenn das Datenbankschema
relativ offen für andere Benutzer ist, da durch die Views eventuell Implementierungsdetails offengelegt werden könnten, die in Packages besser zu verbergen sind. Allerdings sind SQL-Anweisungen fast nicht zu verbergen: Spätestens beim Tracen der Session werden alle SQL-Anweisungen sichtbar, sodass
dieses Argument möglicherweise nicht stichhaltig ist.
Für welche Variante Sie sich auch entscheiden, wichtig ist, dass Sie eine bewusste
Entscheidung fällen. Je umfangreicher das Projekt ist, umso bedeutender werden
428
1452.book Seite 429 Donnerstag, 5. August 2010 3:55 15
Mengenverarbeitung mit Cursorn
diese Entscheidungen. Zur Cursor-For-Schleife noch eine Anmerkung: Diese
Schleifen können ja in den beiden Formen
for rec in cur loop … end loop;
oder
for rec in (SQL-Anweisung) loop … end loop;
geschrieben werden. Gerade bei der zweiten Schreibweise steht uns allerdings
kein Cursorattribut zur Verfügung, denn nach dem Verlassen der Schleife gibt es
keinen Cursor mehr. Daher müssen Sie auf alternative Wege achten, wie Sie z.B.
die Anzahl der verarbeiteten Zeilen ausgeben möchten. Hier bietet sich z.B. eine
count-Variable an oder die Inklusion der Pseudospalte rownum in die SQL-Abfrage
(Achtung, wenn Sie die Ergebnismenge sortieren möchten!).
9.3.2
Top-N-Analyse
Auch wieder mal eines dieser Buzzwords! Unter einer Top-N-Analyse versteht
man schlicht die Abfrage der besten n-Zeilen gemäß einem Sortierkriterium: die
Top-5-Verdiener, die drei besten Produkte, die fünf schönsten Mädchen. Bei der
Abfrage solcher Rangfolgen ist es natürlich zunächst einmal erforderlich, die
Ergebnismenge zu sortieren. Dann aber scheiden sich die Geister: Wie soll die
Abfrage durchgeführt werden? Zur Wahl stehen mindestens die folgenden beiden Wege:
SQL> set serveroutput on;
SQL> declare
2
cursor top_earner is
3
select empno, ename, sal
4
from emp
5
order by sal desc;
6
7
employee top_earner%rowtype;
begin
8
open top_earner;
9
for i in 1 .. 5 loop
10
fetch top_earner into employee;
11
dbms_output.put_line(
12
'Rang ' || i || ': ' || employee.ename);
13
end loop;
14
end;
15
/
Rang 1: KING
Rang 2: FORD
429
9.3
1452.book Seite 430 Donnerstag, 5. August 2010 3:55 15
9
Das Arbeiten mit Daten
Rang 3: SCOTT
Rang 4: JONES
Rang 5: BLAKE
PL/SQL-Prozedur erfolgreich abgeschlossen.
Listing 9.21 Für Ihr Buzzwords-Vokabular: Eine Top-N-Analyse
Oder aber folgende Variante, die sich eigentlich nur im SQL unterscheidet:
SQL> declare
2
cursor top_earner is
3
select *
4
from (select empno, ename, sal
5
from emp
6
order by sal desc)
7
where rownum < 6;
8
employee top_earner%rowtype;
9 begin
10
open top_earner;
11
fetch top_earner into employee;
12
while top_earner%found loop
13
dbms_output.put_line(
14
'Rang ' || top_earner%rowcount ||
15
': ' || employee.ename);
16
fetch top_earner into employee;
17
end loop;
18 end;
19 /
Rang 1: KING
Rang 2: SCOTT
Rang 3: FORD
Rang 4: JONES
Rang 5: BLAKE
PL/SQL-Prozedur erfolgreich abgeschlossen.
Listing 9.22 Top-N-Analyse, Alternative 2
Lassen Sie sich ein wenig Zeit, um sich den Unterschied zwischen beiden Implementierungen klarzumachen. Die Frage reduziert sich letztlich auf folgende: Welche SQL-Anfrage ist besser? Auf den ersten Blick scheint klar zu sein, dass der
Unterschied nur marginal ausfallen wird, denn schließlich müssen beide Anweisungen die gesamte Tabelle emp durchsuchen und die besten Verdiener finden.
Im Zweifel ist die erste Verwendung zumindest kürzer, und da das SQL einfacher
erscheint, sollte diese Variante das Mittel der Wahl sein. Doch ist genau das
430
1452.book Seite 431 Donnerstag, 5. August 2010 3:55 15
Mengenverarbeitung mit Cursorn
Gegenteil der Fall: Die zweite Implementierung ist, zumal bei sehr vielen Zeilen
in emp, drastisch schneller! Woran liegt das?
Oracle kann im zweiten Fall einen besonderen Algorithmus verwenden, der im
ersten Fall nicht offensichtlich ist: Da die Datenbank weiß, dass Sie sich nur für
die besten n Verdiener interessieren, kann sie eine Optimierung anwenden, die
im Ausführungsplan als Order by Stopkey bezeichnet wird. Bei diesem Verfahren
wird die Datenbank die zufällig ersten fünf Zeilen der Tabelle lesen und sortieren.
Es wird in dieser Gruppe einen Mitarbeiter geben, der am wenigsten verdient,
sagen wir, 1250 Taler. Dieses Gehalt wird nun mit dem Gehalt des 6. Mitarbeiters
der Tabelle verglichen. Ist es höher, wird die 6. Zeile ignoriert und die nächste
Zeile verglichen. Lediglich dann, wenn ein Mitarbeiter mehr als diese 1250 Taler
verdient, muss er in die Riege der besten 5 Mitarbeiter aufgenommen werden,
ansonsten kann der Datensatz ignoriert werden. Anschließend kann die Suche
mit dem nun fünftbesten verdienenden Mitarbeiter fortgesetzt werden. Beim ersten Ansatz wird die Datenbank im Gegensatz hierzu gezwungen, die gesamte
Tabelle zu sortieren, obwohl nachher lediglich die ersten 5 Zeilen dieser sortierten
Menge abgefragt werden. Daher muss die gesamte Tabelle auch im UGA der Session gehalten werden (dort liegen die Sortierbereiche einer Session) und nicht nur
die fünf Zeilen, auf die es ankommt. Und dies noch abgesehen von dem eingesparten Aufwand, auch noch den 317.-besten Verdiener sortieren zu müssen.
Man hätte eigentlich drauf kommen können, nicht? Andererseits ist das eines der
vielen Beispiele dafür, dass Sie der Datenbank sagen sollten, was Sie von ihr wollen, damit die Datenbank auch etwas für Sie tun kann. Gerade gestandene Anwendungsentwickler tendieren meiner Beobachtung zufolge dazu, der Datenbank
»helfen« zu wollen, den richtigen Weg zu finden. Ich habe häufig die ernüchternde
Erfahrung machen dürfen, dass die Datenbank meistens besser weiß, was für sie
gut ist, als ich. Daher tendiere ich dazu, der Datenbank die Entscheidung zu überlassen, solange ich keinen starken, nachweisbaren Grund habe, dies nicht zu tun.
Und natürlich ist das wieder einmal ein Argument für die Empfehlung, sich mit
dem SQL der Datenbank auseinanderzusetzen, gegen die man programmiert.
Als Anmerkung zu dieser Abfrage: Vielleicht noch etwas schneller geht die Suche
mit einer Abfrage über eine analytische Funktion. Hier können wir uns die
Abfrage z.B. so vorstellen:
select *
from (select empno, ename, sal,
rank() over (order by sal desc) rang
from emp)
where rang < 6;
Listing 9.23 Top-N-Analyse, Alternative 3
431
9.3
1452.book Seite 432 Donnerstag, 5. August 2010 3:55 15
9
Das Arbeiten mit Daten
Diese analytische Funktion ist noch einmal gegenüber der besseren Abfrage von
vorhin optimiert und daher eventuell (bitte testen!) noch einmal schneller. Wichtig ist mir aber: Sagen Sie der Datenbank, was Sie von ihr wollen, dann kann die
Datenbank auch etwas für Sie tun.
9.3.3
Cursorvariablen (REF-Cursor)
Eine Cursorvariable ist eine Variable, die auf einen Cursor zeigt. Dieser Variablentyp wird verwendet, um auf eine Ergebnismenge zu zeigen, ohne diese tatsächlich zu beinhalten. Durch die Trennung von Cursorvariable und Cursor ergeben
sich viele neue Möglichkeiten, die für die Programmierung von grundlegendem
Interesse sind:
왘 Zunächst erhalten Sie mit einer Cursorvariablen die Möglichkeit, Cursor als
Parameter an Prozeduren zu übergeben oder von dort als Ausgabeparameter
oder Funktionsrückgabetyp zu erhalten.
왘 Dann können Cursorvariablen auf beliebige Cursor zeigen. Dadurch ergibt
sich die Möglichkeit, beliebige Abfrageergebnisse durch eine einzige Methode
verarbeiten zu lassen.
왘 Mehrere Variablen können auf denselben Cursor zeigen, unabhängig davon,
wo sie deklariert sind. Auf diese Weise können z.B. Clientanwendungen auf
Cursor zeigen, die auf dem Server liegen. Diese Cursor müssen also nicht komplett zum Client übertragen werden, sondern es werden lediglich dann Daten
zum Client übertragen, wenn sie angefordert werden.
Cursorvariablen werden als Typ definiert und anschließend genutzt, indem eine
Variable dieses Typs deklariert wird. Das folgende Beispiel definiert eine sogenannte schwache Cursorvariable mit dem Namen my_cur. Schwach ist diese Cursorvariable in Hinblick auf ihre Typsicherheit, weil die Struktur der Ergebnismenge nicht bekannt ist. Diese Variable kann allerdings beliebige Cursor
repräsentieren und ist damit sehr flexibel einsetzbar. Doch kann PL/SQL natürlich erst zur Laufzeit prüfen, ob die folgenden Zuweisungen auf Variablen etc.
überhaupt gültig sind. Hier also der Code für diese Cursorvariable:
declare
type my_cur_t is ref cursor;
my_cur my_cur_t;
…
Sie kennen die grundsätzliche Vorgehensweise bereits aus der Arbeit mit Records
oder assoziativen Tabellen.
Im Gegensatz zu diesem schwachen Cursor steht die starke Cursorvariable, bei
der zum Zeitpunkt der Deklaration bereits die Struktur des Cursors festgelegt
432
1452.book Seite 433 Donnerstag, 5. August 2010 3:55 15
Mengenverarbeitung mit Cursorn
wird. Zwar kann diese Cursorvariable immer noch beliebige Cursor repräsentieren, doch haben diese Cursor alle die gleiche vordefinierte Struktur. Auf diese
Weise kann bereits zur Erstellungszeit geprüft werden, ob die nachfolgenden
Manipulationen mit diesem Cursor machbar sind oder nicht. Sehen wir uns auch
hierzu ein Beispiel an:
declare
type my_strong_cur_t is ref cursor return emp%rowtype;
my_strong_cur my_strong_cur_t;
…
Beiden Cursorvariablentypen ist gemeinsam, dass die Arbeit mit Ihnen weitgehend der mit einem »normalen« Cursor entspricht. Es stehen die gleichen CursorAttribute zur Verfügung: Auch hier wird über ein fetch eine Zeile geliefert und
über die Anweisung close der (durch die Cursorvariable repräsentierte) Cursor
geschlossen. Allerdings muss die Open-Anweisung für Cursorvariablen geändert
werden, denn es muss ja irgendwann definiert werden, wie der Cursor, der
durch diese Variable repräsentiert wird, definiert sein soll. Dies geschieht, indem
die Open-Anweisung durch die For-Klausel erweitert und mit einer SQL-Abfrage
spezifiziert wird. Sehen wir uns hierzu ein Beispiel an:
SQL> declare
2
type my_strong_cur_t is ref cursor return emp%rowtype;
3
my_strong_cur my_strong_cur_t;
4
my_rec my_strong_cur%ROWTYPE;
5 begin
6
open my_strong_cur for
7
select *
8
from emp
9
where deptno = 10;
10
loop
11
fetch my_strong_cur into my_rec;
12
exit when my_strong_cur%NOTFOUND;
13
dbms_output.put_line(my_rec.ename ||
14
' arbeitet als ' || my_rec.job);
15
end loop;
16
close my_strong_cur;
17 end;
18 /
CLARK arbeitet als MANAGER
KING arbeitet als PRESIDENT
MILLER arbeitet als CLERK
PL/SQL-Prozedur erfolgreich abgeschlossen.
Listing 9.24 Verwendung einer stark typisierten Cursor-Variablen
433
9.3
1452.book Seite 434 Donnerstag, 5. August 2010 3:55 15
9
Das Arbeiten mit Daten
Achten Sie bitte darauf, dass Sie, wenn Sie eine schwache Cursorvariable verwenden, selbst dafür verantwortlich sind, keine Laufzeitfehler durch unmögliche
Zuweisungen im Code zu erhalten. PL/SQL löst bei einer solchen unmöglichen
Zuweisung die Fehlermeldung ROWTYPE_MISMATCH aus.
Wir können, wie bereits angesprochen, eine Cursorvariable als Parameter einer
Methode oder auch als Rückgabetyp einer Funktion deklarieren. Allerdings stellt
sich dabei ein Problem: Wir haben ja gesehen, dass eine Cursorvariable als Typ
deklariert und anschließend von diesem Typ abgeleitet werden muss. Dies stellt
sich für eine Methode als sehr schwierig heraus, denn dort muss ein Parametertyp explizit angegeben werden. Folgender Versuch schlägt also fehl:
SQL> create or replace procedure my_cursor_proc
2
(cur_in in ref cursor)
3
as
4
begin
5
null;
6
end;
7
/
Warnung: Prozedur wurde mit Kompilierungsfehlern erstellt.
SQL> show errors
Fehler bei PROCEDURE MY_CURSOR_PROC:
LINE/COL ERROR
-------- ----------------------------------------------------0/0
PL/SQL: Compilation unit analysis terminated
2/18
PLS-00201: Bezeichner 'CURSOR' muss deklariert werden
Listing 9.25 Versuch, eine Cursorvariable als Parameter zu übergeben
Andererseits können wir die Typdeklaration und die Ableitung einer Variablen
in einer Parameterdeklaration nicht unterbringen. Welche Auswege bieten sich
an? Zunächst einmal ist es zwar grundsätzlich möglich, Typen in SQL zu definieren, doch auch dieser Weg schlägt bei einer Cursorvariablen fehl, denn eine Cursorvariable kennt SQL nicht:
SQL> create type refcur is ref cursor;
2
/
create type refcur is ref cursor;
*
FEHLER in Zeile 1:
ORA-21561: OID-Generierung nicht erfolgreich
434
1452.book Seite 435 Donnerstag, 5. August 2010 3:55 15
Mengenverarbeitung mit Cursorn
Oracle versucht, einen SQL-Objekttyp zu erzeugen, und das gelingt nicht. Ein
Ausweg ist die Verwendung eines Packages. Dies ist einer der vielen Vorteile von
Packages. In einer Package-Spezifikation können wir einen Typ in PL/SQL verwenden und diesen anschließend im Prozeduraufruf verwenden. In der PackageSpezifikation können sowohl schwache als auch starke Cursorvariablen deklariert
werden. Analog gehen Sie vor, wenn Sie eine Funktion deklarieren, die einen
Cursor als Rückgabewert definiert:
SQL> create or replace package cursor_pkg
2
as
3
type refcur is ref cursor;
4
procedure my_cursor_proc (cur_in in refcur);
5
function my_cursor_func (sql_stmt in varchar2)
6
return refcur;
7
end cursor_pkg;
8
/
Package wurde erstellt.
Listing 9.26 Erste Variante: Implementierung als Package-Cursor
Ein anderer Weg ist die Verwendung eines vordefinierten Cursortyps in SQL: des
sys_refcursor. Dieser Cursor kann allerdings nur als schwacher Cursortyp verwendet werden, wie im folgenden Beispiel gezeigt:
SQL> create or replace procedure my_cursor_proc(
2
cur in sys_refcursor)
3
as
4
begin
5
null;
6
end my_cursor_proc;
7
/
Prozedur wurde erstellt.
SQL> show errors
Keine Fehler.
Listing 9.27 Zweite Variante: Verwendung von »sys_refcursor«
9.3.4
Cursor-Ausdrücke
Eine weitere Variante der Verwendung von Cursorn sind die Cursor-Ausdrücke.
Stellen wir uns hierzu vor, Sie müssten für eine Auswertung eine hierarchische
Liste erstellen, in der die Mitarbeiter pro Abteilung aufgeführt werden sollen.
435
9.3
1452.book Seite 436 Donnerstag, 5. August 2010 3:55 15
9
Das Arbeiten mit Daten
Der Bericht soll in PL/SQL erstellt werden. Nun könnten Sie die Aufgabe durch
zwei Cursorn lösen: einen für jede Abteilung und einen für die Mitarbeiter der
Abteilung. Sie haben aber auch die Möglichkeit, einen einzigen Cursor mit einem
eingeschachtelten Cursor zu verwenden. Dazu können Sie in SQL eine harmonisierte Unterabfrage als Cursor deklarieren und die dadurch erzeugte Ergebnismenge durch geschachtelte Loop-Anweisungen durchlaufen. Wir sparen uns
dadurch mehrere Roundtrips zum Server und verwalten die verschiedenen Cursor und deren Beziehungen untereinander implizit in einer Struktur.
Die Benutzung solcher Cursor-Ausdrücke ist also ein zweistufiger Prozess.
Zunächst müssen die geschachtelten Cursor in SQL erzeugt werden. Sehen wir
uns eine solche Abfrage einmal an:
select dname, loc,
cursor(select job,
cursor(select ename
from emp n
where n.job = e.job)
from emp e
where e.deptno = d.deptno)
from dept d
Sie erkennen die harmonisierten Unterabfragen, die durch die Funktion
cursor() zu eingeschachtelten Cursorn deklariert werden. Anschließend kann
der Bericht erzeugt werden, indem drei ineinander geschachtelte Loop-Anweisungen die Cursor bearbeiten. Zur Bearbeitung der oben gezeigten Anweisung
erstellen wir uns eine Prozedur, die einen Bericht ausgibt:
SQL> create or replace procedure print_employee_report
2
as
3
type refcur is ref cursor;
4
department_cur refcur;
5
employee_cur refcur;
6
sql_stmt varchar2(400 char) :=
7
'select initcap(dname), initcap(loc),
8
cursor(select initcap(ename), initcap(job)
9
from emp e
10
where e.deptno = d.deptno)
11
from dept d';
12
dept_name dept.dname%TYPE;
13
dept_location dept.loc%TYPE;
14
job emp.job%TYPE;
15
16
436
name emp.ename%TYPE;
begin
1452.book Seite 437 Donnerstag, 5. August 2010 3:55 15
Mengenverarbeitung mit Cursorn
17
open department_cur for sql_stmt;
18
loop
19
fetch department_cur
20
into dept_name, dept_location, employee_cur;
21
exit when department_cur%NOTFOUND;
22
dbms_output.put_line(
23
'Abteilung ' || dept_name ||
24
' in ' || dept_location);
25
loop
26
fetch employee_cur
27
into name, job;
28
exit when employee_cur%NOTFOUND;
29
dbms_output.put_line(
30
'Name: ' || name || ', Beruf: ' || job );
31
end loop;
32
dbms_output.put_line(' ');
33
end loop;
34
close department_cur;
35
end print_employee_report;
36
/
Prozedur wurde erstellt.
SQL> exec print_employee_report;
Abteilung Accounting in New York
Name: Clark, Beruf: Manager
Name: King, Beruf: President
Name: Miller, Beruf: Clerk
Abteilung Research in Dallas
Name: Smith, Beruf: Clerk
Name: Jones, Beruf: Manager
Name: Scott, Beruf: Analyst
Name: Adams, Beruf: Clerk
Name: Ford, Beruf: Analyst
Abteilung Sales in Chicago
Name: Allen, Beruf: Salesman
Name: Ward, Beruf: Salesman
Name: Martin, Beruf: Salesman
Name: Blake, Beruf: Manager
Name: Turner, Beruf: Salesman
Name: James, Beruf: Clerk
437
9.3
1452.book Seite 438 Donnerstag, 5. August 2010 3:55 15
9
Das Arbeiten mit Daten
Abteilung Operations in Boston
PL/SQL-Prozedur erfolgreich abgeschlossen.
Listing 9.28 Verwendung von Cursor-Ausdrücken
Bitte beachten Sie, dass diese Konstruktion auf Cursorvariablen angewiesen ist,
um die in SQL erstellten Cursor mit einem Handler zu versehen, mit dem in PL/
SQL gearbeitet werden kann. Daher deklariere ich zunächst zwei Cursorvariablen, denen anschließend die geschachtelten SQL-Cursor zugewiesen werden.
Außerdem habe ich noch einige Variablen zur Aufnahme der Spaltenwerte deklariert. In Zeile 18 und 25 erkennen Sie die beiden geschachtelten Loop-Anweisungen. Die Zuweisung des in der SQL erzeugten geschachtelten Cursors zur Cursorvariablen employee_cur erfolgt innerhalb der inneren Schleife durch die FetchAnweisung in Zeile 26.
Cursor-Ausdrücke eignen sich insbesondere beim Verschachteln mehrerer Tabellen, die zueinander in einer 1:n-Beziehung stehen. In diesem Kontext können
Cursor-Ausdrücke implizit die Steuerung der geschachtelten Cursor übernehmen. Im Beispiel oben ist lediglich der employee_cur als geschachtelter Cursor
beteiligt, doch lassen sich auch beliebig tief geschachtelte Konstruktionen denken. Natürlich geht solches SQL zulasten der Lesbarkeit, doch muss man andererseits sagen, dass die Verarbeitung dieser Cursor eher klar ist als das herkömmliche Pendant. Zudem ist diese Schachtelung schneller, weil sie vollständig auf der
SQL-Seite durchgeführt werden kann. Geschlossen wird zum Ende der Bearbeitung lediglich der äußere Cursor. Dadurch werden implizit auch die geschachtelten Cursor freigegeben.
Es ist ganz interessant, sich einmal die Ausgabe der SQL-Anweisung anzuschauen, die durch die Cursor-Ausdrücke erzeugt wird. Dadurch wird eventuell
klarer, auf welche Weise die einzelnen Cursor ineinandergeschachtelt sind:
SQL> select initcap(dname) dname,
2
initcap(loc) loc,
3
cursor(select initcap(ename) ename,
4
initcap(job) job
5
from emp e
6
where e.deptno = d.deptno) emp_cur
7
from dept d;
DNAME
LOC
EMP_CUR
-------------- ------------- -------------------Accounting
New York
CURSOR STATEMENT : 3
CURSOR STATEMENT : 3
438
1452.book Seite 439 Donnerstag, 5. August 2010 3:55 15
Mengenverarbeitung mit Cursorn
ENAME
---------Clark
King
Miller
Research
JOB
--------Manager
President
Clerk
Dallas
CURSOR STATEMENT : 3
CURSOR STATEMENT : 3
ENAME
JOB
---------- --------Smith
Clerk
Jones
Manager
Scott
Analyst
Adams
Clerk
Ford
Analyst
Sales
Chicago
CURSOR STATEMENT : 3
CURSOR STATEMENT : 3
ENAME
JOB
---------- --------Allen
Salesman
Ward
Salesman
Martin
Salesman
Blake
Manager
Turner
Salesman
James
Clerk
6 Zeilen ausgewählt.
Operations
Boston
CURSOR STATEMENT : 3
CURSOR STATEMENT : 3
Es wurden keine Zeilen ausgewählt
Listing 9.29 Darstellung eines Cursor-Ausdrucks in »SQL*Plus«
Die Cursor-Funktion wirkt wie eine Gruppenfunktion, erzeugt allerdings keine
einzelne Zahl, sondern einen strukturierten Typ aus mehreren Zeilen, der
anschließend als Cursor interpretiert und von PL/SQL gelesen werden kann. Als
alternative Sicht sehen Sie in Abbildung 9.2, wie der SQL-Developer einen solchen Cursor-Ausdruck darstellt.
439
9.3
1452.book Seite 440 Donnerstag, 5. August 2010 3:55 15
9
Das Arbeiten mit Daten
Abbildung 9.2 Darstellung eines Cursor-Ausdrucks in SQL-Developer
440
1452.book Seite 865 Donnerstag, 5. August 2010 3:55 15
Index
A
Abfragegenerator 197
Abhängigkeitskette 396
Änderung der Spaltendeklaration 397
Änderung des Datentyps 397
Access Control List (ACL) 654, 671, 719,
749, 857
Advanced Queueing (AQ) 27, 29, 288, 478,
481, 743, 859
Advisory 244
Analytische Funktion 210, 548
anonymer PL/SQL-Block 250, 253, 269
Anwendungsarchitektur 22, 181
Apache 238
Apache Tomcat 239
Application Development Framework (ADF)
237
Application Express (APEX) 37, 86, 223,
237, 749, 792
APEX-Listener 239
Applikationsserver 85, 206
Arbeiten mit Daten 23
Arbeiten mit großen Datenstrukturen 24
Arbeiten mit XML 25
Architektur einer Applikation 96, 98
Archive-Log-Modus 64
ASCII 836
ASM 573
Assoziative Tabelle 314, 407, 446, 462,
519, 576
Count-Funktion 314
Delete-Funktion 315
Exists-Funktion 314
First/Last-Funktion 315
Prior/Next-Funktion 315
Atomizität 346, 351
Auditierung 243, 375
Fine Grained Auditing (FGA) 245
Ausführungsplan 110
Ausführungsrechte von PL/SQL-Blöcken
276
Ausgabeparameter 258
Ausnahme 씮 Exception
Authid-Klausel
Aufruferrecht 277, 768
Aufrufrecht 277
Eigentümerrecht 276, 277
AutoCommit 172, 726
Automatic Database Diagnostic Monitor
(ADDM) 479
Automatic Segment Space Management
(ASSM) 573
autonome Transaktion 279, 346, 862
Autotrace 43
Autowert 116
B
Backup & Recovery 95, 98, 104, 167, 244,
480
Base64-Kodierung 106
bedingte Anweisung 252
Beispieldatenbank 35
Beispielskripte 52
Benutzerdefinierte Typen 120
Benutzerprofil 168
Benutzerverwaltung 92
Best Practice
Aufrufsystematik von Prozedurparametern
265
Benennungskonvention 389
Case- versus If-Anweisung 295
Case-Anweisung 292
Definition von Prozedurparametern 265
Funktion mit Ausgabeparameter 268
kaskadierende Trigger 353
Maskierung von Variablen 255
Schema & Tablespace 98
Speicherung von XML in der Datenbank
611, 614
Übergabe von Parametern mit Records 403
Verwendung von Blocktypen 281
Verwendung von SQL 517
Wahl eines Schleifentyps 304
XML-Datenbank 669
Binary Large Objects (BLOB) 77
Bindevariable 164, 174, 195
865
1452.book Seite 866 Donnerstag, 5. August 2010 3:55 15
Index
BLOB 씮 Datentyp
Buchtipp
Effective Oracle Security by Design 35
Mastering Oracle PL/SQL 35, 46
Thomas Kyte 34
C
Change Data Capture (CDC) 478, 481
Character Set Scanner 125
Check-Summe 154
CLOB 씮 Datentyp
Code Smell 747
Code-Generator 780
Connection Pool 759
Constraint 138, 205, 225, 233
Check 141, 233, 369
Fremdschlüssel 141, 143, 146
Primärschlüssel 346
Unique Constraint 142, 149
Cursor 164, 316, 445, 751
Attribut 319
Caching 319
Cursor-Ausdruck 435
Cursor-For-Schleife 323
Cursorvariable 328, 432
Definition 316
expliziter Cursor 423
impliziter Cursor 423
Kontrolle des Cursorzustandes 426
Lesen 318
Öffnen 317
parametrierter Cursor 322
schließen 319
schwache Cursorvariable 432
starke Cursorvariable 432
Top-N-Analyse 429
verteilter Cursor 753
D
Data Access Layer (DAL) 759, 775
Data Dictionary 175
Data Warehouse 97, 103, 107
Database Access Descriptors (DAD) 239
Database Change Notification (DCN) 156,
158, 741
Database Configuration Assistant (DBCA)
654
866
Database Control 253
Database Link 84
Database Resident Connection Pool (DRCP)
78, 81, 84
Verwaltung 83
Data-Modeler 41
Dateisystem in der DB 119
Datenbank als Datenframework 748
Datenbankadministrator 98
Datenbank-Alert 244
Datenbankbenutzer 88, 759
Datenbank-Constraint 씮 Constraint
Datenbank-Konfigurationsassistent 123
Datenbank-Link 117
Datenbankobjekt 씮 Oracle-Datenbank
Datenbanktrigger 씮 Trigger
Datenframework 182, 203
Datenmodelländerung 208
Integration von SQL und PL/SQL 208
Mächtigkeit von SQL 209
Performanz 204
Sicherheitsdomäne 205
Skalierbarkeit 205
syntaktische Prüfung von SQL 208
Datenintegrität 138, 205, 367, 760
Datenkonsistenz 22, 127, 137, 146, 150,
181, 182, 188, 225, 747, 753
Dead Lock 146, 152
explizites Sperren 150
Latch 166
Lock 165
Lock-Escalation 166
Lost Update 151
optimistisches Sperren 153, 157
pessimistisches Sperren 153, 155, 157
Sperrmechanismus 165
Sperrstrategie 157
Datenpumpe 181, 205
Datenschutz 194, 753
Datensicherheit 22, 97, 127, 166, 181, 182,
746
Denial of Service Attack 168
Datensicht 113, 203, 204, 206, 257, 358,
445, 765
Check-Option 358
materialisierte Datensicht 113
materialisierte Sicht 521, 525
Materialized View 113
user_source 468
1452.book Seite 867 Donnerstag, 5. August 2010 3:55 15
Index
Datentyp 223, 249, 251, 258, 282
Ableitung aus dem Data Dictionary 288
abweichende Datentypen 286
ANSI, DB/2 und SQL-DS 283
Any 284
anydata 481
anydataset 481
anytype 481
Assoziative Tabelle 288
Basistypen und Subtypen in PL/SQL 284
benutzerdefinierter Datentyp 283, 288
BFILE 559, 576
BLOB 559, 576
boolean 223, 251, 269, 285, 287, 519
CLOB 559, 575, 613
date 264
Datum 285
DBURIType 655, 657
HttpURIType 655, 710
HttpUriType 472, 712
httpUriType 481
LOB 217, 285, 559, 575, 752
long, raw, long raw 287, 559
Medientypen 284
NCLOB 559, 575
nested table 810
Numerische Datentypen 284
Objektoriente Datentypen 337
Oracle-definierte, komplexe Typen 283
Oracle-Spatial 284
Originäre Oracle-Datentypen 282
PL/SQL-exklusive Datentypen 287
Record 288, 794
ref_cursor 751
SQL-Datentypen 282
Table 283, 337, 341
URIType 284, 655
varchar2 und char 286
Varray 283, 337, 810
XDBURIType 656, 666
XMLType 284, 481, 606, 617, 642
Zeichentypen 285
Datentyp von Tabellenspalten 138
Datenverarbeitung 395
Datenzugriff über das Web 748
Prozedur über HTTP aufrufen 749
Webservices aus PL/SQL 750
Datenzugriff über Packages
Integration von Geschäftsregeln 746
Kapselung von DML-Operationen 744
Vermeidung von Triggern 745
Datenzugriff über verteilte Cursor 751
Arbeit mit LOBs 752
Datumsfunktion 252
DBMS_Profiler 50
Ddefiners Right (DR) 씮 Authid-Klausel
Dead Lock 씮 Datenkonsitenz
Debugger 51
Denormalisierung 113
DIANA 33
DICOM 479, 481
Directory 577, 847, 853
Diskussion
Abgrenzung von Zeitintervallen 377
Einsatz eines Schleifentyps 302
Einsatz von Triggern 391
Einsatz von Triggern für Defaultwerte 372
Implizer oder expliziter Cursor 423
Lösung des Mutating-Table-Problems 389
Refaktorisierung des Meldung-Objekts 795
Speicherung von Daten in objektrelationalen
Tabellen 613
Speicherung von XML in der Datenbank 610
Variablen und das Data Dictionary 289
Wie und wo werden Fehler bearbeitet? 484
Zugriff auf Daten über View oder VPD 765
DRCP 씮 Database Resident Connection Pool
(DRCP)
Dynamisches SQL 249, 324
Bindevariablen 326
DBMS_SQL 330
Execute Immediate 325
mit Cursorvariablen 328
Sicherheit 333
SQL-Injection 333, 334
Vermeidung von SQL-Injection 336
Zuweisung von Bindevariablen 328
E
Eigentümerrecht 768
Ein- und Ausgabeparameter 257, 258, 519
Enterprise Service Bus (ESB) 750
entfernte Ressource 182
Error Logging 216
Erweiterung von SQL 24, 266, 513, 517
Anforderungen an PL/SQL 519
deterministische Funktion 518
867
1452.book Seite 868 Donnerstag, 5. August 2010 3:55 15
Index
Externe Funktion 517
Funktion 519
Funktion existiert nicht 517
Gruppenfunktion erstellen 538
Nebenwirkungsfreiheit (Purity) 520
Optimizer Hint 520
SQL-Fähigkeiten 514
SQL-Lösung unverhältnismäßig 518
Exception 254, 255, 483
exec 254
F
Factory 656
Fassade 204
Fehlerbehandlung 24, 483
Anwendungsfehler erstellen 495
benannte Fehler 489
Fehlerpackage 496
Fehlertrigger 505
nicht benannte Fehler benennen 494
Oracle-Fehler 483
raise_application_error 495
SQLCode 491
SQLErrm 491
Utility lmsgen 498
value_error 303
Fehlerbehandlungsteil 씮 Exception
Framework 151
Fremdschlüssel 146
FTP 651
Funktion 115, 266, 513
analytische Funktion 225
deterministisch 111
Gruppenfunktion 223, 225
Nebenwirkungsfreiheit 224
Textfunktion 223
Umwandlungsfunktion 223
Zeilenfunktion 223
G
generische Datenbankprogrammierung
182, 193, 196, 198, 205
Geschäftslogik 135, 229, 746, 747
gespeicherte Prozedur 씮 Prozedur
Gruppenfunktion 513, 538
Arbeitsweise 539
Initialisierung 539, 540
868
Iteration 539, 540
Terminierung 539, 541
Zusammenführung 539, 541
Gültigkeitsbereich von Variablen 255
H
Hash 163, 737
Hintergrund
Objektorientierung 541
Rechte von PL/SQL-Code 552
Historisierung von Daten 226
HTML 603
HTTP 612, 651, 726, 748, 749
I
I18N 777
imp 124
impd 124
Impedance Mismatch 22, 183
Cache 188
Datensuche 184
Identität 183
Koppelung von Logik und Daten 191
Lazy Load 187
Lesestrategie 186
Objekthierarchie 188
referenzielle Integrität 192
Statement of Truth 184, 191
Table per Class 189
Table per Class Family 190
Vererbung 188
implizite Konvertierung 290
Index 95, 100, 101, 105, 142
Benutzung von Indizes 107
Binärbaum-Index 107, 109
Bitmap-Index 107
funktionsbasierter Index 107, 108, 111,
149, 225, 521, 523
Index-Range-Scan 109, 111
Reverse-Key-Index 107, 110
Unique-Index 112, 143
Initialisierung eigener Fehler 280
Integration von Oracle in Applikationen 26,
725
Integration von SQL in PL/SQL 249
Invokers Right (IR) 씮 Authid-Clausel
1452.book Seite 869 Donnerstag, 5. August 2010 3:55 15
Index
J
Java 478
Java Message Service (JMS) 859
Java Naming and Directory Interface (JNDI)
864
Java Server Faces 748
JDBC 171, 726, 751, 864
Job 244
Journaled Filesystem 574
K
Kapselung 182
Kollektion 249, 310, 407
spärlich besetzt 410
Kompileranweisung 279
Kompilieren 115
Komponententest 52
konditionale Kompilierung 295, 843, 846
Abfrage-Direktive 297
Auswahl-Direktive 297
Error-Direktive 299
Konstruktormethode 338
Kontrollstruktur 249, 291
Case-Anweisung 292, 296
Case-Anweisung (bedingt) 294
Case-Anweisung (einfach) 293
Einfache Schleife 300
For-Schleife 301
if - then - else-Anweisung 291
konditionale Kompilierung 295
Nicht empfohlene Anweisungen 307
Schleifenoptionen 302
While-Schleife 303
L
Language for ADA (DIANA) 465
Latch 166, 170, 171
Lazy Load 201
LDAP 478, 759
Lesekonsistenz 65, 128, 129, 149, 194, 202,
348, 354, 368, 735
Leserecht 91
LOB 118, 559, 752, 753, 778
API für BFILE 583
BFile 118
BLOB 118
CLOB 118
Einsatz in der Datenbank 560
LOB als Parameter 571
LOB als PL/SQL-Variable 565
Long-Datentyp 119
mit Daten füllen 563
NCLOB 118
Null-LOB, leeres LOB 561
persistentes LOB 565, 567
Schreibzugriff 579
Secure Files 572
SQL-Semantik 567
Technische Struktur 560
Variablendeklaration und -initialisierung
566
Verarbeitung mit dbms_lob 578
Lock 165, 170, 171
Log4J 27
log4plsql 777, 781, 782, 864
Logging-Package 27
lost update 727
M
Mächtigkeit von SQL
Fazit 221
Maschinenabhängiger Bytecode (M-Code)
465
Maskierung von Variablen 255
Massenverarbeitung von Daten 409
Bulk-Select 414
Fehlerkontrolle 413
materialisierte Datensicht 씮 Datensicht
M-Code 씮 Pseudocode (P-Code)
Mengenverarbeitung mit Cursorn 423
Message Oriented Middleware (MOM) 859
Model-View-Control (MVC) 748
MoneyType 697
Implementierung 699
Implementierung des Package coa_money
705
Package-Körper coa_money 707
Rechteverwaltung in Oracle 11g 718
Typkörper 701
Typspezifikation 699
Vererbung 721
Vorüberlegungen 697
Multi-Table-Insert 746
Mutating-Table-Problem 520, 745
869
1452.book Seite 870 Donnerstag, 5. August 2010 3:55 15
Index
N
Namenskonvention 257
National Language Support (NLS) 122, 124
Längensemantik 125
National Language Support (NLS) 씮 Zeichensatzkodierung bei Oracle
Nebenläufigkeit 379
.NET Framework 748
Normalisierung 113
Nullwert 112
O
Obektrelationales Mapping (ORM) 200
Cache 201
Hibernate 201
Kapselung der SQL-Dialekte 201
Lazy Load 201
Mapping von Objekten auf Tabellen 200
Oracle Top Link 201
Transaktion 201
Object Change Notification (OCN) 742
Objekt-ID (OID) 183
Objektorientierung 25, 27, 120, 181, 273,
395, 604, 681, 748, 778, 782
abstrakte und finale Klasse 687
Alles ist ein Objekt 684
Auswirkung auf die Datenbankprogrammierung 693
Best Practices 723
Einführung 683
Einsatz von Objekten als Triggerersatz 392
extern implementierte Methode 697
Gruppenfunktion in PL/SQL 540
Instanziierung 338
Klassenstruktur 691
Konstruktorfunktion 696
Member-Methoden 696
objektrelationale Tabelle 613
statische Methode 688, 696
Typ 480, 695
Typkörper 695
Typspezifikation 695
Vererbung 686, 695
Vergleich mit relationalem Weltbild 689
Objektprivileg 91, 167
Objektrelationales Mapping (ORM) 182,
197, 200
OLE-DB 117
870
Online Analytical Processing (OLAP) 60,
479
Online Transactional Processing (OLTP) 85,
87, 97
Online-Dokumentation 29, 249
2-Day Administration Guide 37
AskTom 33
Concepts-Guide 31
Data Warehousing Guide 34
New Features Guide 32
PL/SQL Language Reference 33
PL/SQL Packages and Type Reference 34
SQL*Plus Quick Reference 40
SQL*Plus User’s Guide 40
XML Developers Guide 34
Open Database Connectivity (ODBC) 117
optionale Parameter 264
Oracle Advanced Security 573
Oracle Advisor 479
Oracle by Example (OBE) 30
Oracle Data Mining 479
Oracle Express Edition 36
Oracle Forms und Reports 237
Oracle Heterogenous Services 117
Oracle Managed Files (OMF) 573
Oracle Packages 34
Oracle Packages 씮 Package
Oracle Spatial 29
Oracle Streams AQ 864
Oracle-Datenbank 21, 88, 99
Anmeldung 68
Archive Process 64
Aufbau der Datenbank 21
Backup & Recovery 66, 67, 84
Checkpoint 63
Connection 71
Data Dictionary 65
Database Block Writer 62
Data-Block-Buffer-Cache 58
Data-Dictionary 113, 115
Data-Dictionary-Cache 60
Datenbankblock 136
Datenbankblocknummer 106
Datenbankjob 114
Datenbankobjektnummer 106
Datendatei 55, 65, 67
Datendateinummer 106
Dedicated-Server-Verbindung 78, 85
Default-Pool 58
Dispatcher 79, 80
1452.book Seite 871 Donnerstag, 5. August 2010 3:55 15
Index
DRCP 87
Eazy Connect (EZConnect) 73, 79
filesystem_like_logging 574
Fixed SGA 59
Foreign-Key-Constraint 21
Full-Table-Scan 100
Grundlagen 55
High Watermark 100
Hintergrundprozess 56, 60
indexorganisierte Tabelle 791
Init-Ora-Datei 67
Installation 68
Instanz 55, 69, 96
Java-Objekt 115
Java-Pool 57
JDBC 75
JDBC-Treiber 72
Keep-Pool 58
Kontrolldatei 55, 67, 69
Large-Pool 57
LDAP 72, 77
Library-Cache 60, 162
Listener 70, 79, 169
listener.ora 73
Log-Switch 63, 66, 67
Log-Writer 62
materialisierte Sicht 480
Mount der Datenbank 69
Net8-Protokoll 242
nologging 574
Null-Pool 58
ODP.NET 75
Öffnen der Datenbank 69
Optimizer 108, 160
Oracle Net Service 72
Oracle-Connection-Manager 72
Oracle-Homeverzeichnis 67
Oracle-Net-Services 72
Overflow-Segment 793
Parameter 96
Parameterdatei 67
Passwortdatei 67, 68
physical reads 44
physikalische Dateien 65
PL/SQL-Objekt 115
PMON 156
Process Global Area (PGA) 59, 79
Process Monitor 61
Queue 80, 86, 860
Quota 93
Recovery Manager 57
Recycle-Pool 59
Redo-Log-Archiv 66
Redo-Log-Buffer 59, 62, 66
Redo-Log-Datei 55, 63, 66, 67
RMAN 169
Rollback-Segment 133
Row-ID 100, 105, 106, 111, 730
Savepoint 351
Schema 87
Secure Files 572, 614
Serverprozess 79
Session 71
Shared Server 239
Shared-Pool 57, 59
Shared-Server-Parameter 81
Shared-Server-Prozess 80, 81
Shared-Server-Verbindung 79, 80
SID 74
Slave-Prozess 64
Sonstige 115
Speicher-Pool 57
Speicherstruktur 109
Sperre 368
SPfile-Datei 67
sqlnet.ora 73
Start der Datenbank 69
Streams-Pool 57
System Global Area 55, 57, 67
System Monitor 61
System-Change-Number (SCN) 155, 737
Systemtabelle 65
Tablespace 87
TCP/IP 74
temporärer Tablespace 565
TNS 73, 78
tnsnames.ora 73, 78, 80, 83, 242
Transportabler Tablespace 480
Treiber 72
User Global Area (UGA) 79
Verbindungaufbau 70
Verbindungsaufbau 84
Verteilte Verbindung 85
Webserver 72, 654
XDB 76
XMLType 120
Oracle-Magazine 30
Oracle-Spatial 479
Organisation von PL/SQL-Code 256
ORM 씮 Obektrelationales Mapping (ORM)
871
1452.book Seite 872 Donnerstag, 5. August 2010 3:55 15
Index
P
Package 23, 115, 229, 250, 272, 441, 745
Abhängigkeitskette 458
apex_* 476
Aufruf von Prozeduren und Funktionen 276
ctx_* 479
Datenbank-Utilitys 475
dbms_* 252
dbms_addm 479
dbms_advisor 479
dbms_application_info 477, 789
dbms_aq 862
dbms_aq_* 478
dbms_aqadm 860
dbms_assert 336, 477
dbms_backup_restore 480
dbms_cdc_publish 478
dbms_cdc_subscribe 478
dbms_comparison 479
dbms_connection_pool 478
dbms_crypto 475, 521, 698, 762, 766, 768
dbms_data_ming_* 479
dbms_ddl 467, 469
dbms_debug 51, 477
dbms_describe 477
dbms_epg 477
dbms_errlog 217, 477
dbms_fga 245
dbms_java 478
dbms_job 478
dbms_ldap 478, 480
dbms_lob 562, 566, 575, 577, 578, 590,
629, 843
dbms_meta_data 479
dbms_metadata 243
dbms_mgd_id_util 479
dbms_monitor 477, 480
dbms_mview 34, 480
dbms_network_acl 719
dbms_network_acl_utility 480
dbms_obfuscation_toolkit 475, 698
dbms_odci 477
dbms_olap 479
dbms_output 34, 252, 276, 475, 597, 783,
855
dbms_pipe 478
dbms_profiler 50, 477
dbms_random 303, 475, 520
872
dbms_rcvcat 480
dbms_rcvman 480
dbms_scheduler 478
dbms_server_alert 479
dbms_shared_pool 479
dbms_space_* 479
dbms_spm 480
dbms_sql 330, 468, 480
dbms_sqldiag 480
dbms_sqlpa 479
dbms_sqlune 480
dbms_stats 480
dbms_streams_* 478
dbms_system 855
dbms_trace 477
dbms_tts 480
dbms_utility 344, 420, 475, 491
dbms_version 291, 475
dbms_xdb 253, 660
dbms_xdb_version 677
dbms_xmldom 641, 643
dbms_xmlgen 621, 625, 631, 647
dbms_xmlindex 650
dbms_xmlparser 641, 643
dbms_xmlquery 647
dbms_xmlsave 615, 647
dbms_xmlschema 650, 665
dbms_xmlstore 615, 647
dbms_xmltranslations 650
dbms_xplan 480
dbms_xslprocessor 641
htf 477
htp 477, 597
Implementierungsteil 274
Initialisierungsprozedur 275, 444, 445
Konstante 458
Körper 272, 274
Oracle-Packages 470
owa_* 476
owa_cookie 476
owa_opt_lock 476
Packagekörper 443
Packagespezifikation 272, 442, 447
sdo_* 479
sem_apis 477
sem_perf 477
standard 286, 473, 489
Trennung von öffentlicher und privater Logik
442
1452.book Seite 873 Donnerstag, 5. August 2010 3:55 15
Index
Überladung 452
util_dbms 816
utl_* 475
utl_compress 475
utl_dbws 475
utl_encode 475
utl_file 475, 847, 852
utl_http 476, 749
utl_i18n 476
utl_inaddr 476
utl_lms 476, 494, 503, 810
utl_mail 295, 476, 698, 857
utl_pipe 859
utl_smtp 295, 476, 698
utl_url 476, 477
utl_utcp 476
Verschlüsselung von Code 464
Verwendung 441
Vorteile 272
Vorwärtsdeklaration 276
wpg_docload 472
Wrap-Utility 465
Parameter 256
nls_language 836
session_max_open_files 584
Parameterzuweisung 263
explizit 263
positionell 263
Passwort 168
P-Code 씮 Maschinenabhängiger Bytecode
(M-Code)
Performanz 104, 107, 136, 181, 182, 196,
202, 204, 272, 427, 431, 765, 848
Datenbank-Constraints 143
Einfluss der Programmierung 169
Implementierung eines Tests 171
Latching 128
Locking 128
Mengenverarbeitung 177
Sperren 170
Umgebungswechsel 176
Pivotierung 516
PL/SQL 17, 21, 23, 29
Blockstruktur 23
Syntax 23
Tuning 33
PL/SQL im Einsatz 24
PL/SQL Server Pages 749
PL/SQL-Befehl
%RowType 289
%Type 289
authid current_user 278
Authid-Klausel 277
begin 251, 252
bult_exceptions 413
case 291, 292
continue 309
declare 251, 252, 269
default 264
deterministic 520
end 251
exception 251, 842
execute immediate 325
exit 301
finally (nicht existent!) 427
for - cursor - loop 323
forall 410
For-Loop 301
for-reverse-loop 303
function 267
goto 309
if - then - else 291
in 258
in out 258
in out nocopy 258
label 307
Loop 300
nocopy 258
nocopy-Klausel 571
out 258
package 273
parallel enable 521
pipe row 417
pipelined 416
plsql_warnings 572
pragam autonomous_transaction 280
Pragma-Klausel 279
Pseudospalte row 399
Pseudovariablen new/old 270
raise_application_error 370, 495, 498
ref_cursor 751
return 267
sqlcode 491, 501
sqlerrm 252, 491
when others 484
where-current-of-Klausel 730
While-Loop 303
Zuweisungsoperator 251, 264
873
1452.book Seite 874 Donnerstag, 5. August 2010 3:55 15
Index
PL/SQL-Befehl 씮 Kompileranweisung
PL/SQL-Block 250
Ausführungsteil 250
Deklarationsteil 250
Ende 250, 256
Fehlerbehandlungsteil 250
PL/SQL-Developer 41, 50
PL/SQL-Grundlagen 33
PL/SQL-Profiler 789
PL/SQL-Referenz 29
Pragma
autonomous_transaction 346, 857
exception_init 535, 846
restrict_references 521
Praxisbeispiel
Ableitung einer Variablen aus dem Data Dictionary 289
Ableitung eines Record aus dem Data Dictionary 312
ACL für E-Mail-Versand administrieren 858
Administration der XDB 654
Administrationsaufgabe 253
Advanced-Queueing-Tabelle anlegen 860
Aktualisierung von Daten mit Records 399,
400
Analyse der XML-Datenbank 652
Analytische Funktion 211
anonymer Block 251
API für dbms_crypto 767
API zum Laden von LOBs aus der Datenbank
584
API zum Lesen von LOBs aus der Datenbank
592
Arbeit mit hierarchischen Objekten 801
Assertionsmethoden 486
Auditierung 376
Aufruf einer Prozedur mit Ausgabeparameter 261
Ausgabe eines LOB über http 597
autonome Transaktion 280
bedingte Case-Anweisung 294
bedingter Primärschlüssel 793
Benennung eines Fehlers 489
Benenung eines Fehlers 494
Berechnung der Fakultät 528
Bindevariable für Wertelisten 419
Bindevariablen 163
Check-Constraint 146
CLOB in Datei schreiben 844
874
Code-Generator für Gruppenfunktionen 549
Compound-Trigger 355
Cursorausdruck 436
Cursor-For-Schleife 323
Cursorvariable 433
Cursorvariable als Parameter 434
Datenbank-Link 117
Datenbanktrigger 270
Datenbanktrigger after logon 359
Datensicherheit durch Trigger 233
Datensicht mit Check-Option 358
dbms_metadata 243
Definition einer Assoziativen Tabelle 315
Definition eines parametrierten Cursor 322
Deklaration einer assoziativen Tabelle 407
Deklaration einer Nested Table 341
Deklaration eines Cursors 318
Deklaration eines expliziten Records 401
Deklaration eines Packages 273
Deklaration eines Records in einem Package
402
Deklaration eines VArray 338
Deklaration von Datentypen im Package
standard 286
Ein LOB mit Daten füllen 563
Ein Objekt als Ersatz für einen Record 406
einfache Case-Anweisung 293
einfache Funktion 266
einfache If-Anweisung 292
einfache Prozedur 256
einfache Record-Definition 311
einfache While-Schleife 303
einfacher Package-Körper 274
Einfluss der Programmierung auf die Performanz 169
Einfügen von Daten mit Records 398
Error Logging 216
Erstellung einer ACL 719
Erstellung einer Gruppenfunktion 542
Erstellung einer sicheren Anwendung 762
Erzeugun von XML aus einem Objekt 625
Erzeugung einer ACL 673
Erzeugung einer schemabasierten Tabelle
667
Erzeugung von XML aus hierarchischen
Abfragen 630
Erzeugung von XML mittels dbms_xmlgen
622
Execute Immediate mit Bindevariable 326
1452.book Seite 875 Donnerstag, 5. August 2010 3:55 15
Index
Execute Immediate mit Cursorvariable 329
Execute-Immediate 325
explizite Parameterübergabe 263
Fakultätsfunktion 300, 301
Fehler initialisieren 280
Fehlerbehandlung über einen Fehlertrigger
505
Fehlerdeklaration mit lmsgen 499
Fehlerpackage 496
Fehlerstack 492
Fine Grained Auditing (FGA) 245
For-Schleife 306
Funktion mit Ausgabeparameter 267
Funktionsbasierter Index 111
geschachtelter Block 254
geschachtelter Record 312
Hierarchische Abfrage 214
Hilfsfunktion für die Arbeit mit LOB 584
If-Anweisung 292
Implementierung des Default-Meldungsmoduls 804
Implementierung des Meldung-Objekts 795
Implementierung des Message-Grundmoduls 803
Implementierung eines Packages 444
Impliziter versus expliziter Cursor 424
Index Range Scan 109
Instanziierung eines Objektes nach Namen
817
Instanziierung eines Objekts 339
Instead-Of-Trigger 357, 381
Iteration über eine assoziative Tabelle mit
Textschlüssel 817
Jurassic-Park-Phänomen 144
konditionale Kompilierung 296, 297, 299,
845
Konstanten-Package 459
Lesen einer Ressource aus dem Internet 713
loadpsp 241
LOB 629
LOB, Null-LOB. leeres LOB 561
LOB-Variable deklarieren 566
Lösung des Mutating-Table-Problems 384
Maskierung von Variablen 255
Massenverarbeitung mit assoziativer Tabelle
409
Massenverarbeitung mit Bulk-Kontrolle 412
Massenverarbeitung mit Bulk-Select 415
Massenverarbeitung von Daten mit Fehlerkontrolle 413
Materialisierte Sicht 114
Meldungstabelle 792
Merge-Anweisung 835
MoneyType 697
Mutating-Table-Problem 353
Objekthierarchie erstellen 722
optimistisches Sperren 733
optimitisches Sperren mit SCN 738
ORA_ROWSCN 154
Parametertabelle 463, 550, 790
Parametertabelle über View und Synonym
785
pessimistisches Sperren 727
PL/SQL Server Pages 240
PL/SQL Web Toolkit 239
PL/SQL-Package dynamisch erzeugen 844
Probleme mit impliziter Konvertierung 290
Prozedur mit Aufruferrechten 278
Prozedur mit Ausgabeparameter 260
Prozedur mit eingebetteter Prozedur 262
Prozedur mit optionalem Parameter 264
Prozedur mit Parameterm 259
Prozedur mit problematischen Parametern
265
Prozedur zur Datenänderung 229
Prüfung auf vorhandene Daten 425
Public-Synonym erzeugen 855
Record mit SQL-Returning-Klausel 404
Refaktorisierung der Fakultätsfunktion 484
Registrierung eines XML-Schemas in der XDB
663
Relationale Daten aus XML mittels SQL/XML
extrahieren 636
Scheduler und Job 244
Schemata 90
Schlechte Schleife 305
Schleife mit Label 307
Schreibzugriff auf ein LOB 579
Secure Files 573
Selektiver Primärschlüssel 146
Sequenz 116
Spezifiaktion eines Packages 442
SQL zur Lösung prozeduraler Probleme 515
SQL/XML 609, 617
SQL/XML-Anweisung 839
SQL/XML-Geschwindigkeitstest 619
SQL-Anweisung call 254
875
1452.book Seite 876 Donnerstag, 5. August 2010 3:55 15
Index
SQL-Anweisung zum Pivotieren von Spalten
819
SQL-Injection 333, 335
Synonym 117
Tabellenfunktion als virtuelle Tabelle 420
temporäres versus persistentes LOB 567
Top-N-Analyse 429
Trigger 226, 235
Trigger before drop 390
Trigger on servererror 391
Trigger zur Datenkonsistenzprüfung 369
Trigger zur Erzeugung von Defaultwerten
371
Trigger zur Historisierung von Daten 378
Trigger, um Reihenfolge von Werten zu
garantieren 372
Übergabe beliebig vieler Parameter an eine
Methode 810
Überladung von Methoden 453
Umwandlungsfunktion 224
Verschlüsselung von Code mit dbms_ddl 468
Verschlüsselung von Code mit Wrap-Utility
465
Verwendung der empty_clob()-Anweisung
564
Verwendung der nocopy-Klausel 572
Verwendung der Pseudovariablen 271
Verwendung der URI-Factory 657
Verwendung der XMLTable()-Anweisung
633
Verwendung des DBURIType 657
Verwendung des Package dbms_xdb 660
Verwendung des Packages dbms_crypto 763
Verwendung des Sessionkontextes 764
Verwendung einer Assoziativen Tabelle 315
Verwendung einer Nested Table 341
Verwendung einer Tabellenfunktion 416
Verwendung eines BFILE 577
Verwendung eines Cursors 320
Verwendung eines objektorientierten Typen
in SQL 693
Verwendung eines Record 313
Verwendung eines Varray 339
Verwendung von Aufruferrechten 768
Verwendung von BFILE 583
Verwendung von dbms_sql 331
Verwendung von dbms_xdb_version 677
Verwendung von LOB 569
Verwendung von nicht-SQL-Datentypen als
Eingabeparameter 529
876
Verwendung von Optimizer Hints 522
Verwendung von SQL/XML 615
Verwendung von XMLType 606
Wrapper für utl_lms 501
XML-Aktualisierung mit dbms_xmlstore
648
XML-Programmierung mit dbms_xmldom
642
XMLType-funktionsbasierter Index 610
XQuery-Abfrage 635
XSQL Publishing Framework 242
Zugriff auf Daten mit WebDAV 651
Praxisbericht
Abfragegenerator 198
Datenkonsistenz 232
effizientes SQL 194, 195
generische Datenbankprogrammierung 198
Grenzen von XML-Verarbeitung 605
Konsistene Verwendung von Datentypen
140
Modellierung von XML in der Datenbank
610
objektrelationales Mapping 197
Spaltentyp 138
Tablespace 94
Transaktion 194
Unnötige Programmierung 470
Primärschlüssel 101, 108, 141
Programmiermodell 151
Programmierung 223
Administrationsunterstützung 242
Anwendungsprogrammierung 236
Application Express (APEX) 237
Auditing 245
Client-seitiges PL/SQL 237
clientseitiges PL/SQL 236
Datenkonsistenz 225
Datenkonsistenz mittels PL/SQL 232
Datensicherheit 233
Datensicherung 245
Datenzugriff über PL/SQL 229
Embedded PL/SQL Gateway 238
erweiterte Datenbankfunktionalität 223
mod_plsql 238
PL/SQL Gateway 238
PL/SQL Server Pages 238, 240
PL/SQL Web Toolkit 238, 239
PL/SQL-Skript 243
Webanwendungen in PL/SQL 236
1452.book Seite 877 Donnerstag, 5. August 2010 3:55 15
Index
Webanwendungen mit PL/SQL 238
XSQL Publishing Framework 238, 241
Programmierung der Datenbank 22
Programmierung von Triggern 23
Prozedur 115, 204, 256, 747, 753
Parameter 257
Spezifikation 231
Pseudocode (P-Code) 465
Pseudospalte
column_value 419, 421
level 631
ora_rowscn 737
user 520
Q
Query Result Change Notification (QRCN)
742
R
Radio Frequency Identification (RFID) 479
Record 310, 395, 462
Ableitung aus dem Data Dictionary 312
Definition durch das Data Dictionary 395
expliziter Record# 401
Insert- und Update-Anweisung 398
Objekt als Alternative 406
Returning-Klausel 404
Recovery Manager (RMAN) 84, 480
Recovery Manager (RMAN) 씮 Oracle-Datenbank
Redundanz 167
referentielle Integrität 137
Rekursion 537
Relationales Datenbankmanagementsystem
(RDBMS) 167, 181, 193, 196, 198
Remote Procedure Call (RPC) 604
RFID 481
Rollenkonzept 167
Round-Half-Even-Verfahren 714
Rules and Expressions 481
RunStats 45, 170
S
Schachtelung von Blöcken zur Fehlerbehandlung 254
Scheduler 243, 244
Schema 88, 91, 92, 97, 168, 277
Schleifenkonstruktion 254
Schreibkonsistenz 128, 132
Schreibrecht 91, 229
Secure Application Role (SAR) 760, 762,
765
Secure Files 119, 752
Semaphore 166
Sequenz 116
Sequenz 씮 Autowert
Serialisierung 166
Service Oriented Architecture (SOA) 604,
748, 750
Session 157, 161, 732, 848
Sessionkontext 206
USERENV 206, 233, 234
Sessionvariable 103
Sicherheitsdomäne 168, 205
Skalierbarkeit 196, 202, 205
Smell 796
SOAP 749
Sperren von Daten 726
Database Change Notfication (DCN) 727,
741
optimistisches Sperren 727, 733, 736
pessimistisches Sperren 727
SQL 17, 29
ANSI-Standard 127
hierarchische Abfrage 212
Isolation-Level 129, 130, 131
ISO-Standard 127
SQL Developer 249, 251, 252
SQL*Loader 353
Direct-Path-Load 353
SQL*Plus 38, 41, 150, 249, 251, 252, 338,
775
set serveroutput on 252
Zeichen / 253
SQL/XML 604, 610, 615, 617, 618, 629,
631, 638, 661, 675, 711
SQL-Anweisung 79, 80, 113, 158
after update 270
alter session 334
analytische Funktion 209
analytische Funktionen 514
ANSI-Standard 127
Atomizität 216
Ausführen (lesend) 160
Ausführen (schreibend) 162
877
1452.book Seite 878 Donnerstag, 5. August 2010 3:55 15
Index
avg 193
bfilename() 578
Case 112
case 292, 515
cast 625
column_value 607
columns-Klausel 712
commit 346
create Index 107
create or replace 257
create Sequence 116
create session 698, 759
cursor 422, 435
DDL-Anweisung 520
debug any procedure 51
debug connect session 51
decode 291, 516
delete 100, 104, 345
distinct 576
DML-Anweisung 112, 133, 225, 345
drop 757
empty_clob() 564, 578
equals_path 675
Error-Logging 209
explain plan 41
extract 370
for each row 347
for update-Klausel 729
full join 787
group by 576
hierarcische Abfrage 209
identified-using-Klausel 762
insert 102, 116, 345
intersect 576
ISO-Standard 127, 198
Log-Error-Klausel 514, 746
map-Klausel 697
merge 133, 345, 746, 835
minus 576
multiset 625
new 721
not-final-Klausel 803
not-instantiable-Klausel 803
nowait-Klausel 729
Optimierung 159, 160, 162
order by 576
order-Klausel 697
Parsen 159
Pivot-Klausel 516
878
Projektion 186
reguläre Ausdrücke 376
replace 257, 567
returning-Klausel 404
rollback 346
rowdependencies-Klausel 738
scn_to_timestamp 739
Select for update 156
Set Transaction 133
substr 567
sys_context 764
sys_refcursor 435
sysdate 520
systimestamp 370
table() 607, 637
truncate 101, 757
under-Klausel 722
union / union all 576
update 153, 230, 345
updateXML 638, 675, 839
URIFactory 656
with-grant-option-Klausel 764
xmlagg 618, 633
xmlattributes 616
xmlelement 616
XML-Erzeugung mit SQL 515
xmlforest 616
xmlsequence 637
xmlTable 634, 711
XMLTable() 632, 633
SQL-Developer 40
SQL-Skriptdatei 124, 243
utlxplan.sql 41
Stored Procedure 씮 Prozedur
Synonym 117
Syntax 249, 250
Systemprivileg 88, 167
T
Tabelle 90, 99
exklusiv sperren 369
Full Table Lock 152
Full Table Scan 108, 139, 152
Global Temporary Table 99, 102
Hash-Partition 104
Heap Organized Table 99
Heap-Organized-Table 99, 109
Index Organized Table 99, 101
1452.book Seite 879 Donnerstag, 5. August 2010 3:55 15
Index
List-Partition 104
Partitionierte Tabelle 103
Pseudospalte 105
Pseudospalte ORA_ROWSCN 154
Range-Partition 103
Tabellenfunktion 344, 415
Tablespace 88, 89, 93, 94, 168
Quota 168
Read Only Tablespace 94
Temporary Tablespace 94
TCP/IP 72
temporäres LOB 567
Tiefenkopie 571
TKProf 46
tnsnames.ora 117
tnsping 74
Toad 41
Token 761
TopLink 733
Transaktion 22, 61, 62, 65, 127, 132, 165,
182, 194, 202, 231, 346, 520, 726, 729
autonome Transaktion 279
Transaktionslog 133, 136
Transaktionsschutz 128
Unit of Work (UoW) 136, 201, 732
Verteilte Transaktion 118
Trigger 115, 147, 225, 245, 269, 279, 345,
520
Anforderungen 271
Anweisungstrigger 347
Anwendungstrigger 237
Auditierung durch Trigger 375
Auslösereihenfolge 352
Auslösung 351
benutzerbezogene Ereignisse 364
Compound-Trigger 355
Compound-Trigger und Mutating-Table 384
Datenbankereignis 245, 345, 362
Datenbanktrigger 359
Datenintegrität 367
DDL-Ereignis 389
Defaultwerte setzen 371
Definition 269
DML-Ereignis 369
DML-Trigger 345
Einsatzbereiche 367
Ereignisattribute 360
Erweiterung der Datenkonsistenzprüfung
369
follows-Klausel 352
Historisierung von Daten 377
Instead-Of-Trigger 357, 381
kaskadierende 353
konditionale Ausführung 349
Körper 350
Mutating-Table-Problem 353, 368, 379,
395
Pseudovariablen new/old 350
Spezifikation 349
Systemereignis 225, 391
Update-Reihenfolge festlegen 372
when-Klausel 349
Zeilentrigger 347
zirkuläre Triggerdefinition 354
U
Überladen von Prozeduren und Funktionen 272
Überladung 452
Überladung 씮 Package
Umgebungswechsel 224, 517
Unicode 575
Unit of Work (UoW) 씮 Transaktion
unverzerrtes (mathematisches) Rundungsverfahren 701
URL 577, 710
V
Variable 251
View 씮 Datensicht
Virtual Private Database (VPD) 765
W
WebDAV 72, 76, 416, 651, 654, 671
WebService 182, 225, 604, 750, 753, 781
Weiterführende Literatur 34
Workshop 26
Workshop Logging-Package 777
Architektur und Überblick 778
Ausgabemodul Alert-Datei 855
Ausgabemodul E-Mail 857
Ausgabemodul JMS 859
Ausgabemodul Tabelle 856
Ausgabemodul Trace-Datei 847
Ausgabemodul-Objekt 782
879
1452.book Seite 880 Donnerstag, 5. August 2010 3:55 15
Index
Implementierung 790
Implementierung der Log-Admin-PackageSpezifikation 830
Implementierung der Logging-Package-Spezifikation 811
Implementierung des Kontextes 808
Implementierung des Log-Admin-PackageKörpers 832
Implementierung des Log-Admin-Packages
828
Implementierung des Logging-Package-Körpers 814
Implementierung des Logging-Packages 811
Implementierung des Meldungsgrundmoduls
800
Implementierung einer Parameterliste 809
Implementierung Meldungstabelle 791
Implementierung Parametertabelle 790
Kontext 781
Meldung-Objekt 780
Meldungsobjekt 794
Meldungspackage 789
Tabellen 784
Test des Logging-Packages 827
Weitere Ausgabemodule 846
Workshop Sichere Anwendung 757
Archtitektur 758
Aufsetzen der Schemata 762
Logon-Prozedur 761
Packages 766
Projektbeschreibung 757
Test 772
Token 770
X
XLIFF 829, 838, 840
XML 25, 27, 120, 125, 241, 242, 603, 750,
752, 778, 829
BinaryXML 613
DOM-Baum 605, 615, 629, 637, 638
Erzeugung aus Objekten 624
Erzeugung durch dbms_xmlgen 622
Erzeugung von XML aus hierarchischen
Abfragen 629
For-Let-Order By-Where-Return (FLOWR)
632
getnumberval()-Anweisung 609
Namensraum 639
880
Null-Namespace 668
Oracle-Parser 640
Packages zur Erzeugung und Speicherung von
XML 647
Paginierung von XML-Abfragen 631
PL/SQL-Packages für XML 640
Programmierung mit SAX 638
Relationale Daten aus XML extrahieren 631
SAX 615
Simple API for XML (SAX) 639
Sonstige PL/SQL-Packages 650
Speicherung ind der Datenbank 613
sys$nc_rowinfo 607
Verarbeitung mit PL/SQL 638
Weißraum 639
wohlgeformt 613
XML aus relationalen Daten erzeugen 615
XML Schema Definition Language (XSD)
603
XPath 605, 609, 632
XQuery 632
XSD 613, 650, 662, 667
XSL-FO 604, 640
XSL-T 604
XSLT 628, 632, 638
XML Database (XDB) 72, 76, 239, 416, 478,
605, 641, 651
ACL 671
Arbeit mit ACLs 673
Dokumente per Drag & Drop einfügen 659
Dokumente per PL/SQL einfügen 660
Dokumente verwalten 659
Einführung 652
Registrierung eines Schemas 663
schemabsierte Tabelle 666
Speicherung mit XML-Schema 662
Versionierung von Ressourcen 676
Verwaltung von Dokumenten 654
Zugriffsschutz und Sicherheit 670
XML Localization Interchange File Format
(XLIFF) 650
XML SQL Utility (XSU) 647
XML-Fragment 618
XMLType 25
Analysemethoden 612
appendChild()-Anweisung 612
Bearbeitungsmethoden 612
createxml()-Anweisung 612
deleteXml()-Anweisung 612
1452.book Seite 881 Donnerstag, 5. August 2010 3:55 15
Index
existsNode()-Anweisung 612
extract()-Anweisung 612, 631, 634, 637
extract-Anweisung 609
extractValue()-Anweisung 612, 631
insertXmlBefore()-Anweisung 612
isFragment()-Anweisung 612
isSchemaValid()-Anweisung 612
isSchemaValidated()-Anweisung 612
Konstruktorfunktion 611
Methoden 611
text()-Anweisung 637
transform()-Anweisung 612
Verwendung von XMLType als Spaltentyp
606
XSD 25
XSLT 25, 241
Z
Zeichensatzkodierung 37, 120
ASCII 121
ISO-8859 121
Multi-Byte-Zeichensatz 120
Single-Byte-Zeichensatz 120
Unicode 122
UTF 122
UTF-8 613
Zeichensatzkodierung bei Oracle 122
Nationale Zeichensatzkodierung 124
NLS 124
Zeitstempel 154
Zugriff auf Daten über PL/SQL-Packages
743
881