Oracle PL/SQL

Die SQL-Erweiterung PL/SQL der Oracle 10g/11g-Datenbank bietet eine umfangreiche Syntax für die Entwicklung von programmierbaren DB-Objekten wie Prozeduren/Funktionen und Triggern. So kann man vollständige datenbanknahe Anwendungen direkt in der Datenbank entwickeln und ihre Module in anderen Anwendungen, die in Java,.NET oder auch PHP geschrieben sind, wieder integrieren. Dieses Buch bietet Einsteigern einen umfassenden Überblick über die PL/SQL-Syntax und ihre Fähigkeiten.

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.

VorwortHerzlich 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.ZielgruppeDieses 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.SchreibkonventionenDas 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-InformationenWie 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_sqlKontaktmöglichkeitenDie 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 InformationenOffizielle 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-Seminarehttp://www.comelio-seminare.com/seminare/titel/xml für XML-SeminareWeitere Bücher zur Oracle-Technologie im gleichen Verlag:Oracle SQL - ISBN 978-3-939701-41-5Oracle, PL/SQL und XML " ISBN 978-3-939701-10-1Oracle PL/SQL - Objekte und Objektrelationale Techniken - ISBN 978-3-939701-42-2Weitere 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-referenzenKostenloses E-Book als PDF SQL und relati 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.AutorenMarco SkulschusMarco 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 WiedersteinMarcus 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.

1. Einführung 251.1.Beispieldatenbank 251.1.1.Entitäten 251.1.2.Szenario 261.1.3.Tabellen und Beziehungen 301.2.Einrichtung der Umgebung 321.2.1.Oracle Database 10g Express Edition 321.2.2.Entwicklungsumgebung 361.3.Einrichtung der Beispiel-Datenbank 391.3.1.Dateien 391.3.2.Tabellen und Daten aufspielen 402. Einführung in PL/SQL 472.1.Wozu überhaupt PL/SQL? 472.1.1.SQL- und PL/SQL-Variante eines Datenzugriffs 472.1.2.Architektur von PL/SQL-Anwendungen 502.2.Die Struktur von PL/SQL 542.2.1.Schreibweise und Benennungskonventionen für Variablen 542.2.2.Blockstruktur 562.2.3.Einsatzbereiche von PL/SQL 582.2.4.Guter Programmierstil: Kommentare und Formatierungen 603. Grundlegende Syntaxvorstellung 693.1.Basis-Syntax 693.1.1.Gültige Zeichenmenge 693.1.2.Literale 703.2.Variablen 743.2.1.Deklaration 753.2.2.Verwendung von %TYPE 763.2.3.Vererbung von Datentypen 773.2.4.Gültigkeit und Sichtbarkeit 773.3.Datentypen und Operatoren 813.3.1.Oracle- und PL/SQL-Datentypen 823.3.2.Subtypen Eigene Datentypen 843.3.3.Automatische und explizite Typumwandlung 863.3.4.Operatoren 903.4.Ausgabemöglichkeiten im Puffer und in Dateien 933.4.1.Puffer-Ausgaben mit DBMS_OUTPUT 933.4.2.Datei-Ausgaben mit UTL_FILE 983.4.3.Ausnahmen des UTL_FILE-Pakets 1063.4.4.Textformatierungen und Ersetzungen 1073.5.Verzweigungen 1103.5.1.IF-Verzweigung 1113.5.2.IF-ELSE-Verzweigung 1123.5.3.IF-ELSIF-ELSE-Verzweigung 1133.5.4.CASE mit einzelnem Selektor 1143.5.5.Ausnahmen bei CASE 1153.5.6.Selektorlose CASE-Anweisung 1163.5.7.Programmmuster bei Verzweigungen 1173.6.Schleifen 1183.6.1.LOOP Einfache Schleife 1183.6.2.Schleifensteuerung mit EXIT und CONTINUE 1193.6.3.Verschachtelte Schleifenkonstruktionen mit Labeln 1203.6.4.WHILE-Schleife 1223.6.5.Numerische FOR-Schleife 1243.7.Weitere Anweisungen 1263.7.1.GOTO-Strukturen mit Labeln 1263.7.2.NULL im Anweisungsabschnitt 1343.8.Datensätze und %RECORD 1353.9.Vorteile bei der Verwendung 1363.9.1.Definition eines Datensatztyps 1373.9.2.Verwendung von Datensätzen 1393.9.3.Datensatz-Erzeugung mit %ROWTYPE 1424. Transaktionssteuerung 1474.1.SQL in PL/SQL 1474.1.1.Zulässigkeit von Anweisungen 1474.1.2.Dynamisches SQL 1484.2.Kontextwechsel 1504.2.1.Grundprinzip 1514.2.2.Zugriffe über DB-Links 1544.2.3.Einsatz von Synonymen 1554.2.4.Aufruf von Pseudospalten 1564.3.Steuerung von Transaktionen 1614.3.1.Grundproblem 1614.3.2.Bestätigen und zurücksetzen von Anweisungen 1634.3.3.Sicherungspunkte 1674.3.4.Transaktionen festlegen 1714.3.5.Tabellen sperren 1735. Cursor und Mengen 1795.1.Prinzip von Cursorn 1795.1.1.Erzeugen und verarbeiten 1795.1.2.Cursor-Operationen 1815.2.Explizite Cursor 1825.2.1.Deklaration 1825.2.2.Verarbeitung 1845.2.3.Cursor-Schleifen und Cursor-Attribute 1885.2.4.Parametrisierte Cursor und Wertzuweisungen 1935.3.Implizite Cursor 1955.3.1.Definition 1955.3.2.Attribute 1975.3.3.Beispiel 1985.3.4.Zeilensperren im Cursor 2005.4.Cursor-Variablen 2025.4.1.Deklaration 2025.4.2.Verarbeitung 2045.5.Weitere Techniken 2095.5.1.Cursor-Ausdrücke 2095.5.2.Cursor-Ausnahmen 2126. Fehler und Ausnahmen 2176.1.Einführung 2176.1.1.Typologie von Ausnahmen 2176.1.2.Allgemeine Funktionsweise von Ausnahmen 2186.1.3.Vordefinierte Ausnahmen 2216.2.Selbst definierte Ausnahmen 2236.2.1.Deklaration und Verwendung 2236.2.2.Fehlermeldungen und Fehlerwerte 2256.2.3.Fehler und Ausnahmen in verschachtelten Blöcken 2286.2.4.Verschachtelte Blöcke und besondere Ausnahmen 2316.2.5.Verwendung von Ausnahmen bei Schleifen und Blöcken 2347. Collections und ihre Verarbeitung 2437.1.Collections 2437.1.1.Typen 2437.1.2.Assoziative Arrays (Index-By-Tabellen) 2447.1.3.Verschachtelte Tabellen (Nested Tables) 2477.1.4.Varrays 2507.1.5.Multidimensionale Strukturen 2517.1.6.Collection-Methoden 2617.1.7.Ausnahmen und Collections 2687.2.Mengenbindung und Mengenverarbeitung 2687.2.1.Bindungstypen 2697.2.2.Leistungsunterschiede im Vergleich 2707.2.3.Mengenübertragung mit FORALL 2717.2.4.Verwendung von Cursor-Attributen 2747.2.5.Ausnahmebehandlung bei der Mengenbindung 2777.2.6.Mengenabfrage mit BULK COLLECT 2807.2.7.Einsatz von RETURNING 2848. Dynamisches SQL 2918.1.Einführung 2918.1.1.Grundproblem 2918.1.2.Verwendungszweck und Einsatzbereiche 2938.2.SQL-Befehle mit EXECUTE IMMEDIATE ausführen 2938.2.1.Allgemeine Syntax 2948.2.2.Beispiel 2958.3.Dynamisches SQL im Kontext 3028.3.1.Verwendung von Cursor-Variablen 3028.3.2.Mengenverarbeitung 3058.3.3.Beispiele 3079. Prozeduren und Funktionen 3159.1.Modulare Strukturen in PL/SQL 3159.1.1.Typen von Modulen in PL/SQL 3159.1.2.Parameter und Parameter-Modi 3189.1.3.Fehlersuche und Korrektur 3259.2.Funktionen und Prozeduren erstellen 3329.2.1.Allgemeine Syntax für Funktionen 3329.2.2.Blockstruktur von Funktionen 3349.2.3.Entscheidungen für Parameter-Modi 3399.2.4.Ausnahmebehandlung 3439.2.5.Übergabe von Werten bei IN und IN OUT 3469.2.6.Rückgaben mit RETURN 3489.2.7.PL/SQL-Funktionen in SQL 3549.3.Eigene Prozeduren verwenden 3659.3.1.Die allgemeine Syntax von Prozeduren 3659.3.2.Blockstruktur von Prozeduren 3679.3.3.Speicherort von Prozeduren 3699.3.4.Wahl der Parameter-Modi und Wertübergabe 3709.3.5.Ausnahmebehandlung für Prozeduren 3759.3.6.Übergabe per Referenz und Wert 3789.4.Modul-Spezialitäten 3799.4.1.Überladen von Modulen 3799.4.2.Vorwärtsdeklaration und Rekursion 3839.4.3.Einsatz von lokalen Modulen 38910. Pakete 39910.1.Einführung 39910.1.1.Definition und Strukturen 39910.1.2.Vorteile durch die Verwendung von Paketen 40010.1.3.Aufbau von Paketen 40110.1.4.Sichtbarkeit und Gültigkeit 40410.2.Eigene Pakete 40610.2.1.Ein Beispielpaket 40610.2.2.Speicherort von Paketen und ihre Bearbeitung 40910.2.3.Abhängigkeiten zwischen Körpern und Spezifikationen 41010.2.4.Aufruf von Paketen 41310.2.5.Öffentliche und private Elemente 41510.2.6.Berechtigungskonzepte bei gespeicherten Unterprogrammen 41710.2.7.Beispiel-Programm: Testdaten-Erzeugung 42210.2.8.Quelltextschutz und Verschlüsselung 44810.3.Eingebaute PL/SQL-Pakete 45110.3.1.Systemüberwachung mit DBMS_UTILITY 45210.3.2.Große Objekte mit DBMS_LOB bearbeiten 45710.3.3.Zugriff auf den Shared Pool 46810.3.4.Nachrichtenaustausch über Pipes 47010.3.5.Datenaustausch via Alarme 47410.3.6.Anwendungsoptimierung und -analyse von PL/SQL-Programmen 47711. Trigger 50111.1.Einführung 50111.1.1.Typologie 50211.1.2.Einsatzbereiche 50311.1.3.Einschränkungen 50411.2.Trigger programmieren 50511.2.1.Allgemeine Trigger-Befehle 50611.2.2.DML-Trigger 50611.2.3.Syntax-Erweiterungen von Triggern 51011.2.4.Instead-of-Trigger 51811.2.5.System-Trigger 525