Oracle PL/SQL

Marco Skulschus studierte Ökonomie in Wuppertal und Paris und setzt im Rahmen seiner Arbeit.NET und Java sowie XML für das Datenbanksystem Oracle ein. Er arbeitet als Dozent und Projektleiter für Business Intelligence-Anwendungen und betriebliche Software. Marcus Wiederstein studierte Elektrotechnik in Bochum und Dortmund und ist verantwortlich für die Durchführung von Projekten im Bereich Systemintegration und Datenbanken (Sicherheit, Hochverfügbarkeit, Datenintegration). Zusammen haben sie eine Reihe von Büchern zu Datenbanken (Oracle und MS SQL Server) sowie zu XML geschrieben.

1. Einführung   25
1. 1.  Beispieldatenbank   25
1. 1. 1.  Entitäten   25
1. 1. 2.  Szenario   26
1. 1. 3.  Tabellen und Beziehungen   30
1. 2.  Einrichtung der Umgebung   32
1. 2. 1.  Oracle Database 10g Express Edition   32
1. 2. 2.  Entwicklungsumgebung   36
1. 3.  Einrichtung der Beispiel-Datenbank   39
1. 3. 1.  Dateien   39
1. 3. 2.  Tabellen und Daten aufspielen   40
2. Einführung in PL/SQL   47
2. 1.  Wozu überhaupt PL/SQL?   47
2. 1. 1.  SQL- und PL/SQL-Variante eines Datenzugriffs   47
2. 1. 2.  Architektur von PL/SQL-Anwendungen   50
2. 2.  Die Struktur von PL/SQL   54
2. 2. 1.  Schreibweise und Benennungskonventionen für Variablen   54
2. 2. 2.  Blockstruktur   56
2. 2. 3.  Einsatzbereiche von PL/SQL   58
2. 2. 4.  Guter Programmierstil: Kommentare und Formatierungen   60
3. Grundlegende Syntaxvorstellung   69
3. 1.  Basis-Syntax   69
3. 1. 1.  Gültige Zeichenmenge   69
3. 1. 2.  Literale   70
3. 2.  Variablen   74
3. 2. 1.  Deklaration   75
3. 2. 2.  Verwendung von %TYPE   76
3. 2. 3.  Vererbung von Datentypen   77
3. 2. 4.  Gültigkeit und Sichtbarkeit   77
3. 3.  Datentypen und Operatoren   81
3. 3. 1.  Oracle- und PL/SQL-Datentypen   82
3. 3. 2.  Subtypen – Eigene Datentypen   84
3. 3. 3.  Automatische und explizite Typumwandlung   86
3. 3. 4.  Operatoren   90
3. 4.  Ausgabemöglichkeiten im Puffer und in Dateien   93
3. 4. 1.  Puffer-Ausgaben mit DBMS_OUTPUT   93
3. 4. 2.  Datei-Ausgaben mit UTL_FILE   98
3. 4. 3.  Ausnahmen des UTL_FILE-Pakets   106
3. 4. 4.  Textformatierungen und Ersetzungen   107
3. 5.  Verzweigungen   110
3. 5. 1.  IF-Verzweigung   111
3. 5. 2.  IF-ELSE-Verzweigung   112
3. 5. 3.  IF-ELSIF-ELSE-Verzweigung   113
3. 5. 4.  CASE mit einzelnem Selektor   114
3. 5. 5.  Ausnahmen bei CASE   115
3. 5. 6.  Selektorlose CASE-Anweisung   116
3. 5. 7.  Programmmuster bei Verzweigungen   117
3. 6.  Schleifen   118
3. 6. 1.  LOOP – Einfache Schleife   118
3. 6. 2.  Schleifensteuerung mit EXIT und CONTINUE   119
3. 6. 3.  Verschachtelte Schleifenkonstruktionen mit Labeln   120
3. 6. 4.  WHILE-Schleife   122
3. 6. 5.  Numerische FOR-Schleife   124
3. 7.  Weitere Anweisungen   126
3. 7. 1.  GOTO-Strukturen mit Labeln   126
3. 7. 2.  NULL im Anweisungsabschnitt   134
3. 8.  Datensätze und %RECORD   135
3. 9.  Vorteile bei der Verwendung   136
3. 9. 1.  Definition eines Datensatztyps   137
3. 9. 2.  Verwendung von Datensätzen   139
3. 9. 3.  Datensatz-Erzeugung mit %ROWTYPE   142
4. Transaktionssteuerung    147
4. 1.  SQL in PL/SQL    147
4. 1. 1.  Zulässigkeit von Anweisungen   147
4. 1. 2.  Dynamisches SQL   148
4. 2.  Kontextwechsel   150
4. 2. 1.  Grundprinzip   151
4. 2. 2.  Zugriffe über DB-Links   154
4. 2. 3.  Einsatz von Synonymen   155
4. 2. 4.  Aufruf von Pseudospalten   156
4. 3.  Steuerung von Transaktionen   161
4. 3. 1.  Grundproblem   161
4. 3. 2.  Bestätigen und zurücksetzen von Anweisungen   163
4. 3. 3.  Sicherungspunkte   167
4. 3. 4.  Transaktionen festlegen   171
4. 3. 5.  Tabellen sperren   173
5. Cursor und Mengen   179
5. 1.  Prinzip von Cursorn   179
5. 1. 1.  Erzeugen und verarbeiten   179
5. 1. 2.  Cursor-Operationen   181
5. 2.  Explizite Cursor    182
5. 2. 1.  Deklaration   182
5. 2. 2.  Verarbeitung   184
5. 2. 3.  Cursor-Schleifen und Cursor-Attribute   188
5. 2. 4.  Parametrisierte Cursor und Wertzuweisungen   193
5. 3.  Implizite Cursor   195
5. 3. 1.  Definition   195
5. 3. 2.  Attribute   197
5. 3. 3.  Beispiel    198
5. 3. 4.  Zeilensperren im Cursor   200
5. 4.  Cursor-Variablen   202
5. 4. 1.  Deklaration   202
5. 4. 2.  Verarbeitung   204
5. 5.  Weitere Techniken   209
5. 5. 1.  Cursor-Ausdrücke   209
5. 5. 2.  Cursor-Ausnahmen   212
6. Fehler und Ausnahmen   217
6. 1.  Einführung   217
6. 1. 1.  Typologie von Ausnahmen   217
6. 1. 2.  Allgemeine Funktionsweise von Ausnahmen   218
6. 1. 3.  Vordefinierte Ausnahmen   221
6. 2.  Selbst definierte Ausnahmen   223
6. 2. 1.  Deklaration und Verwendung   223
6. 2. 2.  Fehlermeldungen und Fehlerwerte   225
6. 2. 3.  Fehler und Ausnahmen in verschachtelten Blöcken   228
6. 2. 4.  Verschachtelte Blöcke und besondere Ausnahmen   231
6. 2. 5.  Verwendung von Ausnahmen bei Schleifen und Blöcken   234
7. Collections und ihre Verarbeitung   243
7. 1.  Collections   243
7. 1. 1.  Typen   243
7. 1. 2.  Assoziative Arrays (Index-By-Tabellen)   244
7. 1. 3.  Verschachtelte Tabellen (Nested Tables)   247
7. 1. 4.  Varrays   250
7. 1. 5.  Multidimensionale Strukturen   251
7. 1. 6.  Collection-Methoden   261
7. 1. 7.  Ausnahmen und Collections   268
7. 2.  Mengenbindung und Mengenverarbeitung   268
7. 2. 1.  Bindungstypen   269
7. 2. 2.  Leistungsunterschiede im Vergleich   270
7. 2. 3.  Mengenübertragung mit FORALL   271
7. 2. 4.  Verwendung von Cursor-Attributen   274
7. 2. 5.  Ausnahmebehandlung bei der Mengenbindung   277
7. 2. 6.  Mengenabfrage mit BULK COLLECT   280
7. 2. 7.  Einsatz von RETURNING   284
8. Dynamisches SQL   291
8. 1.  Einführung   291
8. 1. 1.  Grundproblem   291
8. 1. 2.  Verwendungszweck und Einsatzbereiche   293
8. 2.  SQL-Befehle mit EXECUTE IMMEDIATE ausführen   293
8. 2. 1.  Allgemeine Syntax   294
8. 2. 2.  Beispiel   295
8. 3.  Dynamisches SQL im Kontext    302
8. 3. 1.  Verwendung von Cursor-Variablen   302
8. 3. 2.  Mengenverarbeitung   305
8. 3. 3.  Beispiele   307
9. Prozeduren und Funktionen   315
9. 1.  Modulare Strukturen in PL/SQL   315
9. 1. 1.  Typen von Modulen in PL/SQL   315
9. 1. 2.  Parameter und Parameter-Modi   318
9. 1. 3.  Fehlersuche und Korrektur   325
9. 2.  Funktionen und Prozeduren erstellen   332
9. 2. 1.  Allgemeine Syntax für Funktionen   332
9. 2. 2.  Blockstruktur von Funktionen   334
9. 2. 3.  Entscheidungen für Parameter-Modi   339
9. 2. 4.  Ausnahmebehandlung   343
9. 2. 5.  Übergabe von Werten bei IN und IN OUT   346
9. 2. 6.  Rückgaben mit RETURN   348
9. 2. 7.  PL/SQL-Funktionen in SQL   354
9. 3.  Eigene Prozeduren verwenden   365
9. 3. 1.  Die allgemeine Syntax von Prozeduren   365
9. 3. 2.  Blockstruktur von Prozeduren   367
9. 3. 3.  Speicherort von Prozeduren   369
9. 3. 4.  Wahl der Parameter-Modi und Wertübergabe   370
9. 3. 5.  Ausnahmebehandlung für Prozeduren   375
9. 3. 6.  Übergabe per Referenz und Wert   378
9. 4.  Modul-Spezialitäten   379
9. 4. 1.  Überladen von Modulen   379
9. 4. 2.  Vorwärtsdeklaration und Rekursion   383
9. 4. 3.  Einsatz von lokalen Modulen   389
10. Pakete   399
10. 1.  Einführung   399
10. 1. 1.  Definition und Strukturen   399
10. 1. 2.  Vorteile durch die Verwendung von Paketen   400
10. 1. 3.  Aufbau von Paketen   401
10. 1. 4.  Sichtbarkeit und Gültigkeit   404
10. 2.  Eigene Pakete    406
10. 2. 1.  Ein Beispielpaket   406
10. 2. 2.  Speicherort von Paketen und ihre Bearbeitung   409
10. 2. 3.  Abhängigkeiten zwischen Körpern und Spezifikationen   410
10. 2. 4.  Aufruf von Paketen   413
10. 2. 5.  Öffentliche und private Elemente   415
10. 2. 6.  Berechtigungskonzepte bei gespeicherten Unterprogrammen   417
10. 2. 7.  Beispiel-Programm: Testdaten-Erzeugung   422
10. 2. 8.  Quelltextschutz und Verschlüsselung   448
10. 3.  Eingebaute PL/SQL-Pakete   451
10. 3. 1.  Systemüberwachung mit DBMS_UTILITY   452
10. 3. 2.  Große Objekte mit DBMS_LOB bearbeiten   457
10. 3. 3.  Zugriff auf den Shared Pool   468
10. 3. 4.  Nachrichtenaustausch über Pipes   470
10. 3. 5.  Datenaustausch via Alarme   474
10. 3. 6.  Anwendungsoptimierung und -analyse
von PL/SQL-Programmen   477
11. Trigger   501
11. 1.  Einführung   501
11. 1. 1.  Typologie   502
11. 1. 2.  Einsatzbereiche   503
11. 1. 3.  Einschränkungen   504
11. 2.  Trigger programmieren   505
11. 2. 1.  Allgemeine Trigger-Befehle   506
11. 2. 2.  DML-Trigger   506
11. 2. 3.  Syntax-Erweiterungen von Triggern   510
11. 2. 4.  Instead-of-Trigger   518
11. 2. 5.  System-Trigger   525

Vorwort Herzlich Willkommen zu einem Fachbuch aus dem Verlag Comelio Medien. Dieses Buch aus dem Bereich Datenbanken soll Sie dabei unterstützen, die umfangreiche SQL-Erweiterung Oracle PL/SQL zu lernen, mit der Sie neben DB-Skripten auch programmierbare DB-Objekte wie Prozeduren, Funktionen und Trigger sowie ganze (Teil-)Anwendungen in Paketen erstellen können. Zusammen mit unseren anderen Büchern zum Thema Oracle lernen Sie das nötige Handwerkzeug zur Programmierung von Oracle über SQL und PL/SQL sowie auch den Einsatz von XML. Aufbau und Struktur Ähnlich wie dieses Buch sind auch andere Bücher zum Thema Oracle bei Comelio Medien gestaltet, sodass Sie sehr leicht nebeneinander verwendet werden können, um so dieses interessante Thema aus verschiedenen Blickwinkeln betrachten zu können. Zielgruppe Dieses Buch richtet sich an Einsteiger in die PL/SQL-Programmiersprache. Sie sollten schon Erfahrung mit SQL und evtl. auch schon erste Kenntnisse von Oracle haben und nun lernen wollen, wie Sie mit Hilfe von PL/SQL Skripte schreiben und programmierbare DB-Objekte wie Prozeduren erstellen können. Erfahrungen mit anderen Programmiersprachen sind nicht unbedingt notwendig. Da das Buch sehr beispielorientiert aufgebaut ist und sich PL/SQL auch sehr gut als erste Programmiersprache eignet, ist ein Einstieg in die allgemeine Softwareentwicklung sehr gut möglich. Zusätzlich sehen Umsteiger von anderen Sprachen, mit denen man bspw. Desktop- oder Web-Anwendungen erstellen kann, welche allgemeinen Konzepte bei einer Sprache, die so datenbanknah ist wie PL/SQL, vorhanden sind und wie nützlich eine derartig umfassende Erweiterung zu SQL für die eigene Arbeit ist. Schreibkonventionen Das Thema und damit auch diese Buch leben von Beispielen. Quelltext in SQL und PL/SQL wird in diesem Buch in dicktengleicher Schrift geschrieben. Sollte es doch einmal wichtig sein, einen besonderen Textbereich hervorzuheben, geschieht dies durch eine fette, dicktengleiche Schrift. Wichtige Begriffe, Datei- oder Ordnernamen sind durch kursive Schrift gekennzeichnet. Weitere Buch-Informationen Wie alle anderen wird auch dieses Buch auf der Comelio Medien-Webseite vorgestellt. Dort können Sie sich über eventuelle Änderungen und Korrekturen sowie Neuauflagen dieses Buches informieren. Dort können Sie auch die Beispiel-Datenbank sowie alle Beispiele dieses Buchs als Quelltext herunterladen. Die Adresse lautet: www.comelio-medien.com/buch-katalog/oracle/oracle_pl_sql Kontaktmöglichkeiten Die Autoren erreichen Sie an ihrem Arbeitsplatz unter marco.skulschus@comelio.com und marcus.wiederstein@comelio.com. Sie arbeiten beide bei der Comelio GmbH. Webseite: www.comelio.com. Den Verlag erreichen Sie unter der Webseite www.comelio-medien.com. Weitere Informationen Offizielle Dokumente finden Sie natürlich auf der sehr umfangreichen Oracle-Webseite, wobei die folgenden Dokumente besonders interessant sind: http://www.oracle.com/technology/products/database/oracle11g/index.html (Portalseite der Oracle 11g-R2-Datenbank) http://www.oracle.com/pls/db112/homepage (Allgemeine Dokumentation zur 11g-R2-Datenbank) http://www.oracle.com/pls/db112/portal.portal_db?selected =5&frame=#sql_and_pl_sql_languages (Übersichtsseite zu den Handbüchern von PL/SQL und SQL, wobei insbesondere PL/SQL Language Reference und PL/SQL Packages and Types Reference für PL/SQL wichtige Informationsquellen sind) Seminare und Schulungen zu den vorgestellten Technologien: http://www.comelio-seminare.com/seminare/titel/oracle_11g für Oracle-Seminare http://www.comelio-seminare.com/seminare/titel/xml für XML- Seminare Weitere Bücher zur Oracle-Technologie im gleichen Verlag: Oracle SQL - ISBN 978-3-939701-41-5 Oracle, PL/SQL und XML – ISBN 978-3-939701-10-1 Oracle PL/SQL - Objekte und Objektrelationale Techniken - ISBN 978-3-939701-42-2 Weitere kostenlose Lernmaterialien zu Datenbanken und zur Oracle-Technologie im gleichen Verlag: Kostenlose Kurzreferenzen mit Syntax-Darstellungen zu vielen Technologien: http://www.comelio-medien.com/leserservice/kurz-referenzen Kostenloses E-Book als PDF SQL und relationale Datenbanken (http://www.comelio-medien.com/buch-katalog/e-books/sql) Inhalt nach Kapiteln Die Themen der einzelnen Kapitel werden im Folgenden kurz aufgelistet: Im ersten Kapitel lernen Sie die Beispieldatenbank kennen und sehen Sie, wie Sie Ihre Arbeitsumgebung einrichten, damit Sie unsere Beispiele nachvollziehen können. Sie wird bei all unseren Oracle-Büchern verwendet. Als Arbeitsumgebung wäre eine Installation der Oracle 11g-Datenbank auf Ihrem lokalen System optimal. Jedoch ist die Verwendung der kostenlosen Oracle Express Edition für die meisten Themen ebenfalls völlig ausreichend und lässt sich sehr einfach auf einem privaten Computer durchführen, da diese Ausgabe nicht so viele Systemressourcen benötigt. Das zweite Kapitel gibt einen Überblick über PL/SQL und seinen Einsatzmöglichkeiten bei der Datenbank- und Software-Entwicklung. Dazu zählen erste Beispiele in Form von Skripten, die aus einer Entwicklungsumgebung wie bspw. des kostenlosen Oracle SQL Developer direkt zur Datenbank geschickt und dort dann unmittelbar ausgeführt werden. Das dritte Kapitel stellt die grundlegende Syntax von PL/SQL vor. Dazu zählen die Deklaration von Variablen und die Verwendung geeigneter Datentypen oder die verschiedenen Ausgabemöglichkeiten von Ergebnissen in der Standardausgabe oder in Dateien. Für die Programmlogik lernen Sie die verschiedenen Kontrollanweisungen für Fallunterscheidungen und Schleifen kennen. Es schließt mit der Darstellung von Datensatzypen zur Gestaltung von komplexen Datenstrukturen. Das vierte Kapitel zeigt, wie Sie in SQL und PL/SQL Transaktionen beginnen und entweder beenden oder abbrechen und welche verschiedenen Erweiterungen es in PL/SQL gibt, um mehrere Befehle zu einer gemeinsamen Transaktionen zusammenzufassen. Das fünfte Kapitel beschäftigt sich damit, wie man mit Hilfe von Cursorn mehrere Zeilen aus Abfragen abrufen kann, um diese dann zu manipulieren oder für sonstige Verarbeitungen zu nutzen. Es stellt dabei die verschiedenen Cursor-Typen mit einzelnen Beispielen vor. Das sechste Kapitel fokussiert die Behandlung von Ausnahmen und Fehler im Programm und zeigt die verschiedenen Möglichkeiten, Fehlernummern und ihre Meldungen von der Datenbank abzurufen, eigene Fehler auszugeben oder kritische Programmbereiche mit Hilfe von Ausnahmen robuster zu gestalten. Das siebte Kapitel greift noch einmal das Thema der Behandlung von mehreren Datenreihen auf und führt die Collections ein. Sie erlauben die Speicherung von mehreren einzelnen Werten einfacher Datentypen oder Datensatztypen sowie die Verschachtelung mit weiteren Collections zum Aufbau von komplexen Datengebilden und ihrer gemeinsamen Behandlung. Das achte Kapitel bietet eine Zusammenfassung verschiedener Themen vorheriger Kapitel, zeigt sie allerdings unter dem Aspekt, wie man die SQL-Anweisungen nicht statisch, sondern dynamisch zusammensetzen und dann ausführen kann. Das neunte Kapitel verlässt den Bereich der so genannten anonymen Blöcke, d.h. der einfachen PL/SQL-Skripte, die direkt zur Datenbank gesendet und dort ausgeführt werden. Es erklärt, wie Sie Prozeduren und Funktionen erstellen und diese dann über ihren Namen parametrisiert aufrufen können. Es enthält auch weitere Techniken wie lokale Module oder Überladung. Das zehnte Kapitel führt die Pakete ein. Zum einen zeigt es, wie Sie selbst Pakete mit öffentlichen und privaten Elemente erstellen und dort Prozeduren, Funktionen und Typen wie Datensatztypen oder Cursor unter einem gemeinsamen Namen sammeln. Zum anderen stellt es auch einige schon vorhandene nützliche PL/SQL-Pakete vor. Das elfte Kapitel präsentiert das Konzept der Trigger-Programmierung und enthält Beispiele für die verschiedenen Arten von Triggern. Autoren Marco Skulschus Marco Skulschus studierte Ökonomie in Wuppertal und Paris und schrieb seine Diplomarbeit über die Modellierung von semantischen Informationen in XML-Strukturen. Er ist Autor von vielen Büchern zu Softwaretechnik, darunter insbesondere das Thema XML und Datenbanken. Thematisch arbeitet er in Projekten als Berater und Projektleiter und unterstützt Marktforschungs- und Beratungsunternehmen sowie auch mittelständische Unternehmen dabei, Business Intelligence-Systeme aufzubauen sowie individuelle kaufmännische Software einzuführen. Marcus Wiederstein Marcus Wiederstein studierte Elektrotechnik in Bochum und Dortmund. Als Autor konzentriert er sich auf Datenbanken wie Oracle und MS SQL Server sowie die Softwareentwicklung mit Microsoft-Technologien. In Projekten arbeitet er als Projektleiter und Berater. Dort ist er dann für Server-Architekturen und die Themen wie Sicherheit und Hochverfügbarkeit verantwortlich. Thematisch betreffen sie die Bereiche Business Intelligence und klassische kaufmännische Software-Lösungen.