Oracle PL/SQL · Title: Oracle PL/SQL Author: Marco Skulschus, Marcus Wiederstein Subject: Die...

11
Marco Skulschus Marcus Wiederstein www.comelio-medien.com Oracle PL/SQL

Transcript of Oracle PL/SQL · Title: Oracle PL/SQL Author: Marco Skulschus, Marcus Wiederstein Subject: Die...

Marco Skulschus Marcus Wiederstein

www.comelio-medien.com

Oracle PL/SQL

Webseite zum Buch: www.comelio-medien.com/buch-katalog/oracle/oracle_pl_sql

Oracle PL/SQL

Marco Skulschus Marcus Wiederstein

Webseite zum Buch: www.comelio-medien.com/buch-katalog/oracle/oracle_pl_sql

Webseite zum Buch: www.comelio-medien.com/buch-katalog/oracle/oracle_pl_sql

Oracle PL/SQL

Marco Skulschus Marcus Wiederstein

Webseite zum Buch:

www.comelio-medien.com/buch-katalog/oracle/oracle_pl_sql

© Comelio Medien 2010

Webseite zum Buch: www.comelio-medien.com/buch-katalog/oracle/oracle_pl_sql

Alle Rechte vorbehalten. Das Werk einschließlich aller seiner Teile ist urheber-rechtlich geschützt. Jeder Verwertung außerhalb der engen Grenzen des Urhe-berrechtsgesetzes ist ohne Zustimmung des Verlages unzulässig und strafbar. Das gilt insbesondere für die Vervielfältigung, Übersetzung, Mikroverfilmung und die Einspeicherung und Verbreitung in elektronischen Systemen.

© Comelio GmbH

Comelio GmbH Goethestr. 34 D-13086 Berlin

Fon: +49 (0) 30-8 14 56 22-00 Fax: +49 (0) 30-8 14 56 22-10

www.comelio-medien.com [email protected]

Covergestaltung, Comelio-Grafiken, Layout & Satz: Nadine Kilian

ISBN 978-3-939701-46-0

Webseite zum Buch: www.comelio-medien.com/buch-katalog/oracle/oracle_pl_sql6

Inhaltsverzeichnis

Inhaltsverzeichnis

1. Einführung 201. 1.  Beispieldatenbank 20

1. 1. 1.  Entitäten 201. 1. 2.  Szenario 211. 1. 3.  Tabellen und Beziehungen 25

1. 2.  Einrichtung der Umgebung 271. 2. 1.  Oracle Database 10g Express Edition 271. 2. 2.  Entwicklungsumgebung 31

1. 3.  Einrichtung der Beispiel-Datenbank 341. 3. 1.  Dateien 341. 3. 2.  Tabellen und Daten aufspielen 35

2. Einführung in PL/SQL 392. 1.  Wozu überhaupt PL/SQL? 39

2. 1. 1.  SQL- und PL/SQL-Variante eines Datenzugriffs 392. 1. 2.  Architektur von PL/SQL-Anwendungen 42

2. 2.  Die Struktur von PL/SQL 462. 2. 1.  Schreibweise und Benennungskonventionen für Variablen

462. 2. 2.  Blockstruktur 482. 2. 3.  Einsatzbereiche von PL/SQL 502. 2. 4.  Guter Programmierstil: Kommentare und Formatierungen 52

3. Grundlegende Syntaxvorstellung 583. 1.  Basis-Syntax 58

3. 1. 1.  Gültige Zeichenmenge 583. 1. 2.  Literale 59

3. 2.  Variablen 633. 2. 1.  Deklaration 643. 2. 2.  Verwendung von %TYPE 65

Webseite zum Buch: www.comelio-medien.com/buch-katalog/oracle/oracle_pl_sql 7

Inhaltsverzeichnis

3. 2. 3.  Vererbung von Datentypen 663. 2. 4.  Gültigkeit und Sichtbarkeit 66

3. 3.  Datentypen und Operatoren 703. 3. 1.  Oracle- und PL/SQL-Datentypen 713. 3. 2.  Subtypen – Eigene Datentypen 733. 3. 3.  Automatische und explizite Typumwandlung 753. 3. 4.  Operatoren 79

3. 4.  Ausgabemöglichkeiten im Puffer und in Dateien 823. 4. 1.  Puffer-Ausgaben mit DBMS_OUTPUT 823. 4. 2.  Datei-Ausgaben mit UTL_FILE 873. 4. 3.  Ausnahmen des UTL_FILE-Pakets 953. 4. 4.  Textformatierungen und Ersetzungen 96

3. 5.  Verzweigungen 993. 5. 1.  IF-Verzweigung 1003. 5. 2.  IF-ELSE-Verzweigung 1013. 5. 3.  IF-ELSIF-ELSE-Verzweigung 1023. 5. 4.  CASE mit einzelnem Selektor 1033. 5. 5.  Ausnahmen bei CASE 1043. 5. 6.  Selektorlose CASE-Anweisung 1053. 5. 7.  Programmmuster bei Verzweigungen 106

3. 6.  Schleifen 1073. 6. 1.  LOOP – Einfache Schleife 1073. 6. 2.  Schleifensteuerung mit EXIT und CONTINUE 1083. 6. 3.  Verschachtelte Schleifenkonstruktionen mit Labeln 1093. 6. 4.  WHILE-Schleife 1113. 6. 5.  Numerische FOR-Schleife 113

3. 7.  Weitere Anweisungen 1153. 7. 1.  GOTO-Strukturen mit Labeln 1153. 7. 2.  NULL im Anweisungsabschnitt 123

3. 8.  Datensätze und %RECORD 1243. 9.  Vorteile bei der Verwendung 125

3. 9. 1.  Definition eines Datensatztyps 1263. 9. 2.  Verwendung von Datensätzen 1283. 9. 3.  Datensatz-Erzeugung mit %ROWTYPE 131

4. Transaktionssteuerung 1344. 1.  SQL in PL/SQL 134

4. 1. 1.  Zulässigkeit von Anweisungen 134

Webseite zum Buch: www.comelio-medien.com/buch-katalog/oracle/oracle_pl_sql8

Inhaltsverzeichnis

4. 1. 2.  Dynamisches SQL 1354. 2.  Kontextwechsel 137

4. 2. 1.  Grundprinzip 1384. 2. 2.  Zugriffe über DB-Links 1414. 2. 3.  Einsatz von Synonymen 1424. 2. 4.  Aufruf von Pseudospalten 143

4. 3.  Steuerung von Transaktionen 1484. 3. 1.  Grundproblem 1484. 3. 2.  Bestätigen und zurücksetzen von Anweisungen 1504. 3. 3.  Sicherungspunkte 1544. 3. 4.  Transaktionen festlegen 1584. 3. 5.  Tabellen sperren 160

5. Cursor und Mengen 1645. 1.  Prinzip von Cursorn 164

5. 1. 1.  Erzeugen und verarbeiten 1645. 1. 2.  Cursor-Operationen 166

5. 2.  Explizite Cursor 1675. 2. 1.  Deklaration 1675. 2. 2.  Verarbeitung 1695. 2. 3.  Cursor-Schleifen und Cursor-Attribute 1735. 2. 4.  Parametrisierte Cursor und Wertzuweisungen 178

5. 3.  Implizite Cursor 1805. 3. 1.  Definition 1805. 3. 2.  Attribute 1825. 3. 3.  Beispiel 1835. 3. 4.  Zeilensperren im Cursor 185

5. 4.  Cursor-Variablen 1875. 4. 1.  Deklaration 1875. 4. 2.  Verarbeitung 189

5. 5.  Weitere Techniken 1945. 5. 1.  Cursor-Ausdrücke 1945. 5. 2.  Cursor-Ausnahmen 197

6. Fehler und Ausnahmen 1996. 1.  Einführung 199

6. 1. 1.  Typologie von Ausnahmen 1996. 1. 2.  Allgemeine Funktionsweise von Ausnahmen 200

Webseite zum Buch: www.comelio-medien.com/buch-katalog/oracle/oracle_pl_sql 9

Inhaltsverzeichnis

6. 1. 3.  Vordefinierte Ausnahmen 2036. 2.  Selbst definierte Ausnahmen 205

6. 2. 1.  Deklaration und Verwendung 2056. 2. 2.  Fehlermeldungen und Fehlerwerte 2076. 2. 3.  Fehler und Ausnahmen in verschachtelten Blöcken 2106. 2. 4.  Verschachtelte Blöcke und besondere Ausnahmen 2136. 2. 5.  Verwendung von Ausnahmen bei Schleifen und Blöcken 216

7. CollectionsundihreVerarbeitung 2227. 1.  Collections 222

7. 1. 1.  Typen 2227. 1. 2.  Assoziative Arrays (Index-By-Tabellen) 2237. 1. 3.  Verschachtelte Tabellen (Nested Tables) 2267. 1. 4.  Varrays 2297. 1. 5.  Multidimensionale Strukturen 2307. 1. 6.  Collection-Methoden 2407. 1. 7.  Ausnahmen und Collections 247

7. 2.  Mengenbindung und Mengenverarbeitung 2477. 2. 1.  Bindungstypen 2487. 2. 2.  Leistungsunterschiede im Vergleich 2497. 2. 3.  Mengenübertragung mit FORALL 2507. 2. 4.  Verwendung von Cursor-Attributen 2537. 2. 5.  Ausnahmebehandlung bei der Mengenbindung 2567. 2. 6.  Mengenabfrage mit BULK COLLECT 2597. 2. 7.  Einsatz von RETURNING 263

8. Dynamisches SQL 2688. 1.  Einführung 268

8. 1. 1.  Grundproblem 2688. 1. 2.  Verwendungszweck und Einsatzbereiche 270

8. 2.  SQL-Befehle mit EXECUTE IMMEDIATE ausführen 2708. 2. 1.  Allgemeine Syntax 2718. 2. 2.  Beispiel 272

8. 3.  Dynamisches SQL im Kontext 2798. 3. 1.  Verwendung von Cursor-Variablen 2798. 3. 2.  Mengenverarbeitung 2828. 3. 3.  Beispiele 284

Webseite zum Buch: www.comelio-medien.com/buch-katalog/oracle/oracle_pl_sql10

Inhaltsverzeichnis

9. ProzedurenundFunktionen 2909. 1.  Modulare Strukturen in PL/SQL 290

9. 1. 1.  Typen von Modulen in PL/SQL 2909. 1. 2.  Parameter und Parameter-Modi 2939. 1. 3.  Fehlersuche und Korrektur 300

9. 2.  Funktionen und Prozeduren erstellen 3079. 2. 1.  Allgemeine Syntax für Funktionen 3079. 2. 2.  Blockstruktur von Funktionen 3099. 2. 3.  Entscheidungen für Parameter-Modi 3149. 2. 4.  Ausnahmebehandlung 3189. 2. 5.  Übergabe von Werten bei IN und IN OUT 3219. 2. 6.  Rückgaben mit RETURN 3239. 2. 7.  PL/SQL-Funktionen in SQL 329

9. 3.  Eigene Prozeduren verwenden 3409. 3. 1.  Die allgemeine Syntax von Prozeduren 3409. 3. 2.  Blockstruktur von Prozeduren 3429. 3. 3.  Speicherort von Prozeduren 3449. 3. 4.  Wahl der Parameter-Modi und Wertübergabe 3459. 3. 5.  Ausnahmebehandlung für Prozeduren 3509. 3. 6.  Übergabe per Referenz und Wert 353

9. 4.  Modul-Spezialitäten 3549. 4. 1.  Überladen von Modulen 3549. 4. 2.  Vorwärtsdeklaration und Rekursion 3589. 4. 3.  Einsatz von lokalen Modulen 364

10. Pakete 37110. 1.  Einführung 371

10. 1. 1.  Definition und Strukturen 37110. 1. 2.  Vorteile durch die Verwendung von Paketen 37210. 1. 3.  Aufbau von Paketen 37310. 1. 4.  Sichtbarkeit und Gültigkeit 376

10. 2.  Eigene Pakete 37810. 2. 1.  Ein Beispielpaket 37810. 2. 2.  Speicherort von Paketen und ihre Bearbeitung 38110. 2. 3.  Abhängigkeiten zwischen Körpern und Spezifikationen 38210. 2. 4.  Aufruf von Paketen 38510. 2. 5.  Öffentliche und private Elemente 38710. 2. 6.  Berechtigungskonzepte bei gespeicherten Unterprogrammen 389

Webseite zum Buch: www.comelio-medien.com/buch-katalog/oracle/oracle_pl_sql 11

Inhaltsverzeichnis

10. 2. 7.  Beispiel-Programm: Testdaten-Erzeugung 39410. 2. 8.  Quelltextschutz und Verschlüsselung 420

10. 3.  Eingebaute PL/SQL-Pakete 42310. 3. 1.  Systemüberwachung mit DBMS_UTILITY 42410. 3. 2.  Große Objekte mit DBMS_LOB bearbeiten 42910. 3. 3.  Zugriff auf den Shared Pool 44010. 3. 4.  Nachrichtenaustausch über Pipes 44210. 3. 5.  Datenaustausch via Alarme 44610. 3. 6.  Anwendungsoptimierung und -analyse von PL/SQL-Programmen 449

11. Trigger 47011. 1.  Einführung 470

11. 1. 1.  Typologie 47111. 1. 2.  Einsatzbereiche 47211. 1. 3.  Einschränkungen 473

11. 2.  Trigger programmieren 47411. 2. 1.  Allgemeine Trigger-Befehle 47511. 2. 2.  DML-Trigger 47511. 2. 3.  Syntax-Erweiterungen von Triggern 47911. 2. 4.  Instead-of-Trigger 48711. 2. 5.  System-Trigger 494