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 (), 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
