ORACLE PL/SQL Tutorial

1 – Was ist PL/SQL und was ist es nicht
2 – Warum sollte ich PL/SQL benutzen
3 – Kommentare in PL/SQL
4 – Die Blockstruktur von PL/SQL
5 – Variablen und deren Deklaration
6 – Steuerung des Programmfluß
7 – Der Exception-Teil eines PL/SQL Blocks
8 – Arbeiten mit Records
9 – PL/SQL-Tabellen (associative arrays)
10 – Arbeiten mit PL/SQL-Tabellen
11 – PLSQL-Cursor: Das Cursorkonzept
12 – Benannte Blöcke (stored objects)
13 – Prozeduren erzeugen
14 – Funktionen erzeugen
15 – Anlegen von Packages
16 – Trigger
17 – Nested tables Überblick
18 – Nested tables in der Datenbank
19 – Nested tables mit SQL bearbeiten
20 – Arbeiten mit VARRAYs
21 – Dynamisches SQL mit EXECUTE IMMEDIATE
22 – Dynamisches SQL mit REF Cursor
23 – Benutzung von Cursor Expression
24 – Weitere Beispiele zu Cursor Expressions

Was ist PL/SQL und was ist es nicht

Ein Definitionsversuch

In fast jedem PL/SQL Buch wird man lesen, daß PL/SQL die “prozedurale Erweiterung zur Strukturierten Abfragesprache SQL” ist. Wenn dies verwirrend scheint, schauen wir uns an, was wir wissen müssen, um diese Definition zu verstehen.

  • Was eine Computer-Sprache ist
  • Was “prozedural” in diesem Zusammenhang bedeutet
  • Einige Konzepte von SQL (Structured Query Language), einschließlich der Anmerkung, daß SQL nicht prozedural ist
  • Die Idee einer Sprach-“Erweiterung”

Eine Computer-Sprache ist ein bestimmter Weg, Ausführungsanweisungen an einen Computer zu geben (also Programmierung). Computer-Sprachen neigen zu einem geringen Wortschatz gegenüber der mächtigen menschlichen Sprache. Außerdem sind die Möglichkeiten des Vokabular-Einsatzes weit weniger flexibel als bei Menschen. Diese Einschränken rühren daher, daß ein Computer alles literal versteht, also keine Möglichkeiten besitzt, “zwischen den Zeilen zu lesen” und Annahmen über die Anweisungen zu machen (vgl. Semantik, Syntax).

Prozedural meint eine Aneinanderreihung einzelner Schritte, die ein Computer ausführen muß, um das gewünschte Ergebnis zu erhalten. Diese Sprachtypen beinhalten Datenstrukturen, die es erlauben, Informationen mehrfach wiederzuverwenden. Die einzelnen Schritte können in einem Flußdiagramm grafisch dargestellt werden (obwohl Flußdiagramme heutzutage aus der Mode sind). Programme, die in einer solchen Sprache geschrieben werden, nutzen ihre sequentiellen, konditionalen und interaktiven Konstrukte, um Algorithmen auszudrücken. Also sagt dieser Teil der Definition von PL/SQL aus, daß es der gleichen Sprachfamilie angehört wie bspw. BASIC, COBOL, FORTRAN, PASCAL, C…

Die strukturierte Abfragesprache (Structured Query Language) SQL basiert auf der Mengentheorie, es geht hier also stets um die Arbeit mit Datenmengen, und nicht einzelnen Elementen. SQL besteht aus einer relativ geringen Menge von Befehlen wie SELECT, INSERT, CREATE and GRANT. Tatsächlich jedoch erledigt jeder Befehl allein soviel wie manchmal hunderte Zeilen prozeduraler Code. Das ist mit einer der Gründe, warum SQL-basierte Datenbanken so weit verbreitet sind. Das Merkwürdige am Namen SQL ist jedoch, daß es eigentlich nicht wirklich strukturiert ist, nicht nur für Abfragen benutzt werden kann und (manch einer behauptet das) nicht mal eine echte Sprache ist. Nichtsdestotrotz ist SQL am nächsten an einer lingua france für relationale Datenbanken wie ORACLE RDBMS, IBMs DB2 oder Microsofts SQL Server.

Eine Spracherweiterung sind eine Reihe von Eigenschaften, die eine bestehende Sprache erweitern. Diese Aussage scheint trivial und mag fälschlicherweise ausdrücken, daß PL/SQL eine spezielle Version von SQL ist. Dies ist nicht der Fall. PL/SQL ist eine Programmiersprache mit einer eigenen Existenzberechtigung, sie hat ihre eigene Syntax, eigene Regeln und einen eigenen Compiler. Man kann PL/SQL Programme mit oder ohne SQL-Befehlen schreiben. Einige Authoren behaupten, PL/SQL sei eine Obermenge von SQL, dies ist allerdings ein wenig übertrieben, denn nur die einfachen SQL Befehle können einfach in PL/SQL Programmen eingesetzt werden.

Also ist PL/SQL eng verbunden mit SQL, erlaubt jedoch den Einsatz prozeduraler Sprachelemente und damit die Aneinanderkettung einzelner Befehle.

Ein anderer Weg, PL/SQL zu definieren

Ein Programmierer profitiert beim Einsatz von PL/SQL durch folgende Eigenschaften: PL/SQL ist eine prozedurale Sprache mit Unterstützung von benannten Programmeinheiten, viel Syntax ist angelehnt an ADA, und von ORACLE’s SQL werden die Datentypen abgeleitet sowie viele built-in-functions.

Als ein Einsteiger in die Welt der relationalen Datenbanken mag man sich wundern, warum das hochgelobte SQL nicht immer ausreichend ist. Tatsächlich sind die mächtigen SQL Operationen ein großer Vorteil für Programmierer, jedoch enthält die wirkliche Welt der Programmierung viele Aufgaben, die nicht durch eine Manipulation der Datenbank bearbeitet werden können.

SQL ist keine allgemeingültige Sprache, um Algorithmen auszudrücken. Obwohl man “Programme” bauen kann, die aus einer Reihe einzelner Befehle bestehen, können solche Programme keine konditionale Logik beinhalten, soll heißen, SQL kennt keine Möglichkeit, Bedingungen auszudrücken wie „Wenn eine Aussage wahr ist, tue dies, andernfalls jenes“. PL/SQL kann solche Bedingungen bearbeiten.

Zusätzlich hat SQL einige Lücken für die Implementierung von Sicherheitsmechanismen. Versucht man sich einzig auf die SQL-Möglichkeiten zur Gewährleistung von Sicherheit zu beschränken, hat der DBA die Möglichkeit, zu bestimmen, wer die Daten manipulieren darf, aber nur geringe Möglichkeiten, zu beeinflussen, wie sie verändert werden dürfen. Beispielsweise hat der Benutzer OTTO das Recht bekommen, die Buchhaltungstabelle zu verändern (UPDATE). Die Geschäftsprozeßlogik, die beschreibt, wie geändert werden darf, muß ohne PL/SQL in das Front-End (bspw. Visual Basic) gepackt werden. Nun, umgeht OTTO das Front-End und nutzt bspw. Microsoft Excel für den Zugriff auf die Datenbank, kann er damit alle Sicherheitmechanismen umgehen.

Ohne PL/SQL ist es also sehr schwierig, seine Daten vor beabsichtigten und unbeabsichtigten Änderungen zu schützen. Durch den Einsatz von PL/SQL (teilweise in Kombination mit der „Fine Grained Access Control“, eingeführt in ORACLE 8i) kann man versuchen, dieses Loch zu schließen.

Im Umkehrschluß hat PL/SQL also bestimmte Eigenschaften nicht.

Was ist PL/SQL nicht

So brauchbar wie PL/SQL ist, gibt es dennoch Dinge, die es nicht kann (zumindest nicht ohne ein paar Tricks und Kniffe).

Geringe Möglichkeiten der Benutzer-Interaktion

Obwohl PL/SQL viele Konstrukte anbietet, um mit Daten der Datenbank zu interagieren, gibt es nur geringe Möglichkeiten, um direkt mit dem Benutzer zu interagieren. Es gibt einen sehr „basisnahen“ Weg, dem Nutzer Daten in Textform zu liefern, allerdings keinen direkten Weg, um Daten vom Nutzer zu erhalten. Man wird also typischerweise eine andere Sprache nutzen, um ein Front-End zu implementieren, welches dann den Benutzer-Input an PL/SQL weitergibt. Meistens ist das sogar gewollt, um das Datenmanagement von der Präsentation der Daten zu trennen.

Proprietäre Sprache

PL/SQL ist proprietär und abhängig von der Oracle Corporation. Sie ist nicht nutzbar in Produkten von Drittanbietern. Obwohl es Wege gibt, die ORACLE Datenbank in Servern von anderen Herstellern zu integrieren, wird PL/SQL ausschließlich innerhalb der ORACLE Produkte ausgeführt. Das ist das Schicksal der Hersteller, die versuchen, ihre Produkte datenbankunabhängig zu realisieren. Deshalb werden große Hersteller nur ungern auf PL/SQL als ihre Standard-Datenbanksprache migrieren.

Eingeschränkte objektorientierte Möglichkeiten

Bis zur Version 9 fehlen PL/SQL die Möglichkeiten der objektorientierten Sprachen, obwohl bereits in Version 8 Unterstützung für bspw. abstrakte Datentypen implementiert ist. Objektbasierte Entwicklung ist jedoch bereits seit Version 7 implementiert. Oracle 9i enthält nun mehr objektorientierte Verfahren wie Vererbung, Polymorphismus, obwhl es noch Einschränkungen gibt wie bspw. fehlende private Methoden.

Nun, wenn wir bis hier gekommen sind, fragt man sich natürlich, – wenn PL/SQL nur ein Teil einer kompletten Applikation sein kann, wenn es nur innerhalb der ORACLE Produkte verfügbar ist, – warum nutzt man nicht eine andere Sprache wie C oder Java? Warum soll man sich mit PL/SQL überhaupt beschäftigen?

Warum sollte ich PL/SQL benutzen

Um vollständig zu verstehen, warum und wo PL/SQL die richtige Wahl ist, ist es wichtig, die Einschränkungen anderer Alternativen zu betrachten. Gehen wir also zurück in der Zeit und schauen wir uns an, warum PL/SQL überhaupt existiert.

Ich möchte lieber eine “echte” Sprache nutzen

Vor PL/SQL war die einzige Möglichkeit, SQL Befehle innerhalb eines prozeduralen Programms zu nutzen, die SQL Befehle einzubetten (embedding). Dies war notwendig, weil SQL keine Möglichkeiten kennt, Regeln zu etablieren wie „Wenn ein Artikel verkauft ist, erhöhe die Monatsverkäufe um eins und mindere den Artikelbestand um eins“ oder „Nur Personen, die Manager sind, dürfen das Gehalt um mehr als 10 Prozent erhöhen“. Also brauchte man bspw. C-Programme, um diese Geschäftslogik zu implementieren. Während diese “Host-Sprachen” wie C mehr oder minder gut funktionieren (nämlich solange, wie ausschließlich die Applikation eingesetzt wird, und das ist eine wichtige Voraussetzung!), gibt es einige Einschränkungen: C-Compiler und Bibliotheken verschiedener Hersteller sind nicht 100% kompatibel, was es teuer macht, von einer Plattform auf eine andere zu portieren. Selbst, wenn der Code sich nicht verändert, muß man dennoch testen. Weil ORACLE sein PL/SQL so designed hat, daß es auf jeder Plattform läuft, sind Stored Procedures auf verschiedener Server-Hardware und Betriebssystemen wiederverwendbar mit keinem bis geringem Testaufwand. Dies ist nicht nur wichtig für Softwarehersteller, sondern auch für ORACLE selbst, macht es dies doch einfacher, neue Features auf über 80 verschiedenen Plattformen, auf denen ORACLE läuft, zu entwickeln (Eine der Werbeaussagen von ORACLE war langezeit „running everywhere“). Trot vieler Erweiterungen ist die Sprache C eher für andere Anwendungen gedacht als für typische Geschäftsanwendungen. Entwickler werden hier nicht die C-Pointer nutzen und Zeichenkettenverarbeitung ist eher schwieriger als in PL/SQL. Als ORACLE begann, sich auszubreiten, sah die Industrie schnell den Segen in der Verlagerung der Geschäftsprozesslogik auf die Datenbankseite, um unabhängig von der Front-End-Applikation zu werden, mit der auf die Daten zugegriffen wird. Ein C-Programm wird immer außerhalb der Datenbank ausgeführt werden müssen, es kann nicht verwendet werden, um eine „echte“ stored procedure bereitzustellen.

Warum überhaupt Stored Procedures nutzen?

Obwohl es viele Gründe für den Einsatz von Stored Procedures gibt, haben sich diese im Laufe der Zeit verändert. Als Stored Procedures neu waren, hatte man nur zwei Möglichkeiten, Applikationslogik unterzubringen: Entweder auf der Client-Seite, normalerweise ein PC, oder auf der Datenbankserver-Seite. Man hat hier häufig für die Datenbankseite argumentiert mit Hinweisen auf die Zentralisierung von komplexem Code, Sicherung der Datenbank, Wiederverwendbarkeit der Software und erhöhter Performance.

Heutzutage läuft häufig eine Mittelschicht zwischen Client und Datenbank, z.B. ein Webserver. Diese Mittelschicht enthält für gewöhnlich die Applikationslogik. Auch hier gibt es jedoch Gründe, sich für den Einsatz von Stored Procedures zu entscheiden:

Weniger Verteilung notwendig

Verläßt man sich auf den Einsatz von Stored Procedures, gibt es weniger notwendige Verteilung über das Gesamtsystem. Entwicklungsleistung über drei Schichten (Client, Mittelschicht und Server) ist meist schwerer zu koordinieren und daher teurer. Außerdem ist die Erweiterungen eines Systems mit weniger Komponenten einfacher und günstiger. Zentralisierte Konsistenz Stored procedures geben mehr Sicherheit in der Datenintegrität. Es ist einfacher, nicht Sicherheit sowohl in der Datenbank als auch in der Mittelschicht implementieren zu müssen. Der Ausdruck „Sicherheit“ meint hier sowohl Privilegien (OTTO darf auf die Buchhaltungstabelle zugreifen) wie auch Geschäftsprozesse (es dürfen nur Buchungen der letzten 30 Tage verändert werden).

Performance

Stored procedures können die Performanz des Gesamtsystems erhöhen.

Produktivität der Entwicklung

Stored procedures können zu erhöhter Produktivität bei der Softwareentwicklung führen. Wenn bspw. Produkte entwickelt werden, die die Existenz von Tabellen (und damit von einer Datenbank) voraussetzen. Beispielsweise kann innerhalb von ORACLE mit PL/SQL Programme geschrieben werden, die DML-Operationen über Views möglich machen (was sonst nur unter bestimmten Bedingungen geht). Nun aber endlich zum grundlegenden Aufbau von PL/SQL.

Kommentare in PL/SQL

Jeder gute PL/SQL Code verdient es, kommentiert zu werden (spielen Sie ruhig mit der Aussage ;-).

Es gibt zwei Arten der Kommentierung: Entweder ein doppeltes Minus, um eine einzelne Zeile zu kommentieren. Alles hinter dem Doppel-Minus wird bis zum Ende der Zeile ignoriert.

Beispiel:

-- Funktion, um Provision zu berechnen.
IF gehalt > 0 THEN.
  provision := gehalt * 0.1;
END IF;

Genauso kann unser Kommentar in einer Zeile platziert werden, die ausführbaren Code enthält.

Beispiel:

IF gehalt > 0 THEN
  provision := gehalt * 0.1; -- Die Provision ist hart codiert
END IF;

Wenn mehrere Zeilen in PL/SQL kommentiert werden sollen, nutzt man /* und */. Alles zwischen diesen Zeichen wird als Kommentar behandelt.

Beispiel:

PROCEDURE saldo_aendern (kunde_id IN NUMBER) IS
/* Procedure: saldo_aendern
   Author: Martin Bulinski
   Description: Testprozedur
*/
begin
   null;
end;

Die Blockstruktur von PL/SQL

PL/SQL ist in sogenannten Blöcken strukturiert und beinhaltet konditionale Befehle, Schleifen und Verzweigungen, um den Programmablauf zu kontrollieren. Variablen können lokal definiert werden (scoped), um sie nur innerhalb des Blockes sichtbar zu machen, in dem sie definiert wurden.

Es gibt drei Arten von PL/SQL Blöcken: anonyme Blöcke, benannte Prozeduren und benannte Funktionen. All diese Blöcke teilen sich fast alle Blockeigenschaften, so daß wir diese Eigenschaften vorerst für alle Blockarten beschreiben.

Ein anonymer Block ist eine unbenannte Prozedur, sie kann nicht aufgerufen werden. Normalerweise ist sie an einen Trigger oder ein Applikationsereignis (z.B. in ORACLE Forms) gebunden.

Eine benannte Prozedur kann aufgerufen werden, kann Parameter übernehmen, gibt aber nicht explizit einen Wert zurück.

Eine benannte Funktion kann auch aufgerufen werden, Parameter (sog. Argumente) übernehmen und liefert im Gegensatz zu einer Prozedur immer einen Wert zurück.

Struktur

Die Struktur eines anonymen Blockes sieht wie folgt aus

DECLARE 
-- (optional, wenn keine Variablen genutzt werden) 
-- Definition von allen Variablen und Objekten, die innerhalb des Blocks 
-- genutzt werden 
BEGIN 
-- Die Befehle, die den Block ausmachen (mind. eine ausführbare Anweisung) 
EXCEPTION 
-- (optional, wenn keine Fehlerbehandlung erfolgt) 
-- Alle Exception-Handler, die der Fehlerbehandlung dienen 
END; -- Ende des Blocks

Minimalform

Die minimale Form eines anonymen Blockes ist also folgende:

BEGIN
  NULL; 
END;

Dieser Block tut nichts und wird daher immer erfolgreich beendet werden. Der DECLARE-Teil ist optional, wenn keine Variablen-Deklaration erfolgt, kann er weggelassen werden. Der ausführbare Teil, der hinter BEGIN beginnt, muß mindestens eine ausführbare Anweisung enthalten, hier NULL (vgl. NOP, oder no operation). Eine Fehlerbehandlung erfolgt nicht (was soll auch für ein Fehler auftreten?), daher kein EXCEPTION-Teil.

Komplexeres Beispiel

Ein komplexeres Beispiel sehen wir hier, ohne es kommentieren zu wollen:

Beispiel:

DECLARE    
  TEMP_COST NUMBER(10,2); 
BEGIN    
  SELECT COST INTO TEMP_COST FROM JD11.BOOK WHERE ISBN = 21;
IF TEMP_COST > 0 THEN
  UPDATE JD11.BOOK SET COST = (TEMP_COST*1.175) WHERE ISBN = 21;
ELSE
  UPDATE JD11.BOOK SET COST = 21.32 WHERE ISBN = 21;    
END IF;     
COMMIT; 
EXCEPTION    
  WHEN NO_DATA_FOUND THEN       
       INSERT INTO JD11.ERRORS (CODE, MESSAGE) VALUES(99, 'ISBN 21 NOT FOUND'); 
END;

Grundregeln

Einige Regeln zur Arbeit mit Blöcken und deren Struktur wollen wir bereits hier definieren:

Jede PL/SQL-Einheit muß eine Block-Struktur haben. Minimal sind also die Schlüsselworte BEGIN und END um die ausführbaren Anweisungen zu stellen.

SELECT Befehle innerhalb vom PL/SQL Blöcken sind embedded SQL (eine ANSI Kategorie) und müssen als solche exakt eine Zeile zurückliefern. SELECT Befehle, die mehr als eine Zeile oder keine Zeile liefern, lösen einen Fehler aus. Wenn Sie mit einer Reihe von Datensätzen arbeiten müssen, plaziert man die Menge von Datensätzen in einen Cursor. Das Schlüsselwort INTO bei SELECT Statements in PL/SQL (außer bei der Arbeit mit Cursors) ist Pflicht, denn der gelieferte Wert muß in einer Variablen gespeichert werden.

Wenn innerhalb eines PL/SQL Blocks Variablen oder Objekte genutzt werden, müssen diese im DECLARE Teil bekannt gemacht werden (hier gibt es Ausnahmen)

Wenn ein EXCEPTION Teil existiert, wird der jeweilige Exception-Handler nur aufgerufen, wenn der entsprechende Fehler im ausführbaren Teil auftritt. Wenn der Fehlerbehandlungsteil bearbeitet wurde, wird die Bearbeitung des gesamten Blocks abgeschlossen. Ein Fehler im Fehlerbehandlungsteil führt zum „Hochreichen“ des Fehlers an den Aufrufer des Blocks.

PL/SQL Blöcke können geschachtelt (und damit sehr mächtig) werden. Die Schachtelung kann überall da erfolgen, wo eine ausführbare Anweisung stehen kann (also auch im Exception-Teil, nicht jedoch im Declare-Teil).

Variablen und deren Deklaration

Variablen sind die Namen, die wir PL/SQL Elementen geben, wie Tabellen, Cursorn oder Records.

  • Variablen können bis zu 30stellige Bezeichner haben
  • Sie müssen mit einem Buchstaben beginnen
  • Sie sind eine Kombination von Buchstaben, Zahlen, ,$,# und _
  • Eine Konstante ist eine spezielle Variable, sie hat einen Datentyp und ihr Wert wird zum Zeitpunkt der Erzeugung gesetzt.

Wenn Sie einen Variablennamen wählen, sollte dieser so gut wie möglich die Bedeutung der Variablen im Algorithmus beschreiben. Versuchen Sie stets, lesbare Namen zu wählen. PL/SQL erlaubt die Nutzung des _ im Namen, so dass es recht einfach ist, lesbare Namen zu erzeugen.

Jede Konstante und Variable, die in Ihrem Programm definiert wird, hat einen Datentyp. Der Datentyp bestimmt das Speicherformat und Einschränkungen, wie die Variable genutzt werden kann und welche Werte sie enthalten kann.

Ein skalarer Datentyp ist atomar, das heißt, der Datentyp besteht aus keinen weiteren Datentypen. PL/SQL erlaubt mehrere sogenannte zusammengesetzten Datentypen, dazu gehören Tabellen und Records. Dazu aber später mehr.

Skalare Datentypen gehören zu einer der folgenden vier Familien: Number, Character, Boolean und Date. Wir wollen an dieser Stelle die Datentypen nicht weiter beschreiben, die ORACLE Dokumentation gibt einen gründlichen Überblick. Wir beschreiben die Datentypen, wenn sie im folgenden genutzt werden.

Haben Variablen einen Initialwert?

Dies ist eine häufig gestellte Frage und wir können Sie eindeutig beantworten: Ja, unabhängig vom Datentyp ist es NULL. Damit taucht die Frage auf, was denn NULL ist?

  • NULL ist unbekannt (undefiniert).

Jetzt ist es raus! Behalten Sie nur drei Dinge im Hinterkopf über NULL-Werte:

  • NULL ist niemals gleich einem anderen Wert (auch nicht einem anderen NULL)
  • NULL ist niemals ungleich einem anderen Wert (auch nicht einem anderen NULL)
  • Jede mathematische Operation mit einem NULL-Wert führt zu NULL.

Diese Feststellung ist deshalb so wichtig, weil bei der Deklaration von Variablen, – unabhängig davon, welchen Typ sie hat -, die Variable mit NULL initialisiert wird. Das ist ein wichtiger Unterschied zu Programmiersprachen, wo z.B. die numerische Null der Initialwert ist.

Deklaration

Bevor eine Variable referenziert, sprich genutzt werden kann, muß sie definiert werden. Wenn eine Variable deklariert wird, allokiert PL/SQL den notwendigen Speicher für das Ablegen des Variablenwerts. Die Syntax zur Variablendeklaration ist

variable_name datatype [optional assignment];

Beispiel

age_of_person number := 31;

Die vorgenommene Wertzuweisung ist optional. Wird kein Initialwert zugewiesen, ist der Initialwert unabhängig vom Datentyp NULL.

Unsere Deklaration kann unbeschränkt (unbounded) oder beschränkt (bounded) sein. Das bedeutet, dass wir die Mächtigkeit, bzw. Größe der möglichen Werte einschränken. Unsere NUMBER Variable aus obigem Beispiel unterstützt bis zu 38 Stellen. Wenn wir die Variable beschränken mit number(2), allokiert PL/SQL nur soviel Platz, um zweistellige Zahlen abzulegen.

Beispiel

age_of_person number(2) := 31; 
age_of_person2 number(2) default 31; -- wir nehmen an, dass niemand aelter als 99 wird

Auch komplexe Ausdrücke sind möglich:

zu_spaet_ueberwiesen boolean := sysdate > zahlungsdatum + 5;

Konstanten

Wollen Sie Konstanten deklarieren, geschieht das wie folgt:

pi constant number := 3.141; 
-- pi darf im ausführbaren Teil kein anderer Wert  
-- zugewiesen werden

Denken Sie daran, dass Sie Konstanten im ausführbaren Teil keine Werte mehr zuweisen dürfen, daher muss eine Wertzuweisung im Deklarationsteil erfolgen. Die Wertzuweisung erfolgt entweder über := oder das Schlüsselwort DEFAULT

Subtypen

Es ist weiterhin möglich, neue Datentypen auf Basis vorhandener Typen zu definieren und Variablen dieses Typen zu erzeugen:

subtype zweistellig is number(2); 
mein_alter zweistellig; 
subtype wuerfel is binary_integer range 1 .. 6; 
mein_wuerfel wuerfel;

Da Sie sicherlich häufig mit Variablen arbeiten, um Daten aus Tabellen zu nutzen, ist die verankerte Variablendeklaration eleganter.

Verankerte Deklaration

Verankerte (anchored) Deklaration bedeutet, daß wir die %TYPE-Deklaration nutzen. PL/SQL verankert den Datentyp der Variablen am Typ einer anderen Struktur, normalerweise der Spalte einer Tabelle. Der Vorteil der Verankerung ist, dass PL/SQL Code nicht modifiziert werden muß, wenn der referenzierte Datentyp sich verändert.

Beispiel:

age_of_person number := 31; 
age_of_person2 age_of_person%TYPE; 
-- Wir beziehen uns auf eine zuvor deklarierte Variable 
-- der ihr zugewiesene Wert wird nicht übernommen

Genauso können Sie sich auf den Datentyp einer Spalte einer Tabelle beziehen.

Beispiel:

age_of_person mytable.mycolumn%TYPE; 
-- wir beziehen uns auf den Datentyp der Spalte mycolumn von der 
-- Tabelle mytable, die sich in unserem Schema(!) befindet

Tips zum Umgang mit Variablen

  • Benutzen Sie stets selbsterklärende Namen und versuchen Sie, Namenskonventionen für die verschiedenen Arten von Variablen zu etablieren.
  • Versuchen Sie nicht, Variablen wiederzuverwenden. Lesen Sie dies als, NIEMALS Variablen wieder verwenden! Die Wiederbenutzung von Variablen kann mehr Probleme bereiten als man denkt. Versuchen Sie mal ein Programm zu debuggen, in dem immer wieder die gleiche Zählervariable genutzt wird, aber eben nicht nur zum Zählen…
  • Versuchen Sie wann immer möglich, Konstanten zu verwenden anstelle von hart kodierten Werten. Nachträgliche Änderungen müssen somit nur im Deklarationsteil erfolgen.
  • Entfernen Sie ungenutzte Variablen aus dem Code. Einige Programme entwickeln sich über eine lange Zeit. Wenn Sie Variablen nicht mehr nutzen, entfernen Sie diese aus dem Code. Es macht es einfacher, den Code zu verstehen.
  • Wenn Sie eine Variable nutzen, um den Wert einer Tabellenspalte aufzunehmen, nutzen sie die verankerte Deklaration für die Variable mit %TYPE.

Beachten Sie auch folgende Besonderheit

Beispiel:

DECLARE    
  age_of_person NUMBER(10);    
  age_of_person VARCHAR2(10); 
BEGIN      
  NULL; 
END;

Dieser Block terminiert erfolgreich, der Compiler meldet keinen Fehler. Die Besonderheit ist, dass erst bei der Verwendung der Variablen im ausführbaren Teil des Programms ein Fehler gemeldet wird.

Dieser Artikel beschreibt lediglich die Deklaration von skalaren Variablen. Es gibt aber auch komplexe Datentypen, denen wir uns später zuwenden.

Steuerung des Programmfluß

Wie in anderen Programmiersprachen auch gibt es im wesentlichen zwei Konstrukte für die Steuerung des Programmfluss.

  • Konditionale Konstrukte (Bedingungen)
  • Schleifenkonstrukte
    Natürlich existieren neben diesen auch das ungeliebte GOTO und eine Reihe von Modularisierungstechniken, die die Wartbarkeit des Codes erhöhen. Ausserdem existieren in PL/SQL Programmen Besonderheiten in der Arbeit mit Exceptions. All dies gehört allerdings nicht klassisch in die Programmflußsteuerung und wird daher hier noch nicht behandelt.

IF-THEN

Das Format des IF THEN Statements ist wie folgt:

IF <condition> THEN 
  executable statements; 
END IF;

Die Bedingung wird ausgewertet. Wenn die Bedingung wahr ist, werden die ausführbaren Anweisungen ausgeführt. Wenn die Bedingung falsch oder NULL ist, werden die ausführbaren Anweisungen übersprungen.

IF-ELSIFELSE

Das ELSIF ist kein typografischer Fehler. Das Format des IF-ELSIF Statement ist wie folgt:

IF <condition1> THEN
  <statements1> 
ELSIF <condition2> THEN
  <statements2> 
ELSIF <condition3> THEN   
  <statements3> 
ELSE   
  <statementsN> 
END IF;

Das IF-ELSIFELSE Statement kann begriffen werden als viele IF-THEN Statements, von denen nur eine wahr sein kann. Tatsächlich werden die folgenden Bedingungen nicht mehr ausgewertet, sobald die erste wahr ist. Ist keine der Bedingungen wahr, wird der ELSE-Teil ausgeführt.

Verschachtelte IF Statements

Es ist möglich, mehrere IF Statements zu verschachteln. Als Faustregel gilt, wenn mehr als drei Verschachtelungstiefen vorliegen, sollte man seinen Code kritisch betrachten. Grundsätzlich ist die Verschachtelungstiefe unendlich, praktisch hängt sie von den Serverressourcen ab.

Das GOTO Statement

Eigentlich wollen wir gar nicht wissen, dass es das GOTO Statement gibt, denn durch seine Verwendung lässt sich herrlich verwirrender Programmcode erzeugen. Nun, obwohl sich beweisen lässt, dass man in der Programmierung ohne die verpönte Sprunganweisung auskommt, sei sie hier dennoch erwähnt. Als Sprungziel nutzt man sogenannte Labels. Labels spielen neben einem Sprungziel auch eine Rolle für die Sichtbarkeit von Variablen in verschachtelten Blöcken.

Beispiel:

DECLARE  
  a number :=0; 
BEGIN 
  <<label1>>  
  a := a + 1;  
  if a < 10 then
     goto label1;  
  end if; 
END;

Neben konditionalen Konstrukten gibt es noch Schleifenkonstrukte

Schleifenkonstrukte

Die Fähigkeit, Schleifen zu formulieren ist einer der Hauptunterschiede zwischen SQL und PL/SQL, so dass wir diesen Unterschied sehr genau betrachten wollen. Schleifenkonstrukte sind recht einfach und leicht zu verstehen.

  • Einfache Schleifen
  • Numerische Schleifen
  • WHILE-Schleifen

Warum gibt es drei verschiedene Konstrukte. Für Flexibilität. Nun, ich nutze trotzdem meist nur eine Form.

In der Literatur werden Sie noch von einer weiteren Schleifenform lesen, der CURSORFORLOOP. Diese Schleife ist ein sehr mächtiges Konzept, auf das wir aber erst eingehen im Bereich der Datenbank-Interaktion.

Die einfache Schleife

Die einfache Schleife wird auch als Endlos-Schleife bezeichnet. Warum das der Fall ist, sehen wir gleich. Die Syntax für die einfache Schleife ist wie folgt:

LOOP   
  executable statements;   
  EXIT WHEN (condition); 
END LOOP;

Versuchen wir ein Beispiel: Geben Sie in SQL*Plus folgendes ein:

set serveroutput on 
DECLARE i 
  NUMBER := 1; 
BEGIN
   dbms_output.enable(100000);   
  LOOP     
  dbms_output.put_line(i);     
  i := i + 1;     
  EXIT WHEN i > 10;   
  END LOOP; 
END;

Wenn Sie diesen Code ausführen, sehen Sie die Zahlen 1 bis 10 auf dem Bildschirm. Der Schleifencode wird solange ausgeführt bis die EXIT WHEN Bedingung wahr wird. Der Aufruf von DBMS_OUTPUT erzeugt Bildschirmausgaben, darum kümmern wir uns später. Diese Schleifenform heißt auch rumpfgesteuerte Schleife, denn sie wird mindestens einmal ausgeführt. Ob weitere Durchläufe erfolgen, entscheidet sich innerhalb der Schleife (also im Rumpf) mit Hilfe der EXIT WHEN Bedingung. Fehlt diese oder wird niemals wahr, haben Sie eine Endlosschleife produziert, die niemals terminiert.

Ein klassischer Fehler

DECLARE    
  i NUMBER; 
BEGIN   
  dbms_output.enable(100000);   
  LOOP     
    dbms_output.put_line(i);     
    i := i + 1;     
    EXIT WHEN i > 10;   
  END LOOP; 
END;

Nun, sieht ja fast aus wie das vorige Beispiel, ist aber eine Endlosschleife. Wo liegt das Problem? Wir haben versäumt, die Variable i mit einem Wert zu initialisieren. Der Variableninhalt von i ist im ausführbaren Teil also NULL, unbekannt. Selbst wenn wir i um eins erhöhen: unbekannt erhöht um eins ist? Unbekannt! Die EXIT WHEN Bedingung wird daher niemals wahr, also terminiert die Schleife nicht.

Die numerische Schleife

Numerische Schleifen führen den Schleifenrumpf eine feste Anzahl Male aus, die Häufigkeit wird mit dem Schleifenindex festgelegt. Die Syntax für numerische Schleifen ist:

FOR loop_index IN lower_range .. upper_range LOOP
   executable statements 
END LOOP;

Beispiel

set serveroutput on 
BEGIN   
  dbms_output.enable(10000);   
  FOR i IN 1 .. 10   LOOP     
    dbms_output.put_line(i);   
  END LOOP; 
END;

Der Code wird zehnmal ausgeführt. Es demonstriert also eine andere Form der einfachen Schleife. Die numerische Schleife wird genutzt, wenn Sie die Anzahl Schleifendurchläufe kennen und Sie die Schleife nicht bereits vorher abbrechen wollen. Erwähnt sei folgendes: Die Syntax min .. max ist sehr selten, es handelt sich nicht um einen Schreibfehler! Der Schleifenindex i muß nicht deklariert werden! Innerhalb des Schleifenrumpfs darf keine Wertzuweisung an i erfolgen. Schleifen zählen nur ganzzahlig und stets mit dem Inkrement (oder Dekrement) 1.

Es ist also auch möglich, rückwärts zu zählen:

set serveroutput on 
BEGIN   
  dbms_output.enable(10000);   
  FOR i IN REVERSE 1 .. 10   LOOP     
    dbms_output.put_line(i);   
  END LOOP; 
END;

Diese Schleife zählt von zehn bis eins rückwärts. Wichtig ist, dass Sie nicht die Ober- und Untergrenze des Intervalls einfach umdrehen, sondern das Schlüsselwort REVERSE nutzen. Im Gegenteil, wenn die Untergrenze (links) größer als die Obergrenze (rechts) ist, wird die Schleife gar nicht ausgeführt. Es handelt sich hier also nicht um eine rumpfgesteuerte, sondern eine sogenannte kopfgesteuerte Schleife: Ob ein Schleifendurchlauf erfolgt oder nicht, wird vor Schleifeneintritt geprüft. Der Grund für die Nutzung von Schleifen liegt nun weniger in der Anzeige von Zahlen, sondern vielmehr, um bspw. Datensätze zu bearbeiten, die aus der Datenbank “gefetcht” werden. Hierfür können wir entweder die einfache Schleife oder die sogenannte Cursor-Schleife nutzen (den Cursor werden wir später behandeln).

Hier ein Beispiel mit der einfachen Schleife

DECLARE 
  CURSOR c_students IS   SELECT * FROM students;   
  student_rec c_students%rowtype; 
BEGIN   
  OPEN c_students;   
  LOOP     
    FETCH c_students INTO student_rec;     
    EXIT WHEN c_students%notfound;   
  END LOOP;   
  CLOSE c_students; 
END; 
/

Hier geschehen mehrere spannende Sachen: Im DECLARE Teil wird eine zusammengesetzte Variable deklariert, ein sogenannter Record, dessen Aufbau sich aus dem Rückgabewert des Cursors ableitet. Für den Datentypen verwendet man hierbei die verankerte Methode und bezieht sich mit %ROWTYPE auf den Aufbau z.B. einer Tabelle oder aber eines Cursors. In der LOOP Schleife wird nun der gesamte Datensatz aus dem Cursor nicht in einzelne Variablen gestellt, sondern in den Record, der ja den korrekten Aufbau hat. Dazu später mehr. Nach dem FETCH überprüfen wir, ob noch ein Datensatz im Cursor zur Verfügung steht. Wenn nicht, wurden also alle verarbeitet, wir verlassen die Schleife und schließen den Cursor.

Die WHILE-Schleife

Die WHILE-Schleife wird solange ausgeführt wie eine Testbedingung wahr ist.

set serveroutput on 
DECLARE   
  i NUMBER := 0; 
BEGIN   
  dbms_output.enable(1000);   
  WHILE i < 10   LOOP     
     i := i + 1;     
    dbms_output.put_line(i);   
  END LOOP; 
END;

Die WHILE Schleife wird genutzt, wenn nicht bekannt ist, wie häufig die Schleife ausgeführt werden muß und die Durchführung der Schleife unter Umständen gar nicht nötig ist. Denn ist die WHILE-Bedingung beim Schleifeneintritt bereits unwahr, wird die Schleife gar nicht ausgeführt.

Die FORCURSORLOOP

Hier ein anderes Beispiel für die Verarbeitung von Datensätzen. Wir führen jetzt die sogenannte FORCURSORLOOP ein:

DECLARE   
  CURSOR c_students IS   SELECT * FROM students; 
BEGIN   
  FOR c_students_rec IN c_students LOOP
    NULL;   
  END LOOP; 
END; 
/

Nun, das ist die Schleifenvariante mit dem wenigsten Tippaufwand. Was passiert. Im FORLOOP Konstrukt erfolgt bereits das FETCH aus dem Cursor in den Record. Wir brauchen kein Ende-Kriterium, die Schleife terminiert, wenn keine Datensätze mehr im Cursor zur Verfügung stehen. Besonderheit: Der Cursor muß weder geöffnet noch geschlossen werden, das geschieht automatisch. Weitere Besonderheit: Der Record c_students_rec muß nicht deklariert werden. Auch dies geschieht automatisch. Warum hat die erste Variante dennoch ihre Existenzberechtigung, wenn es doch auch einfacher geht? Die einfache Schleife ist dann zu bevorzugen, wenn der Schleifenabbruch bereits erfolgen kann, ohne alle Datensätze verarbeitet haben zu müssen. Das hier vorgestellte Konstrukt verarbeitet erst alle Datensätze, dann terminiert die FORCURSORLOOP.

Der Exception-Teil eines PL/SQL Blocks

PL/SQL hat ein mächtiges Konzept zur Behandlung von Fehlern, bzw. Ausnahmen in Programmen. Es ist tatsächlich möglich, stabile Programme zu realisieren, die “abschusssicher” sind und Fehler in der Datenbank oder die Nutzung der Datenbank sichern.Die Ausnahmebehandlung ist ereignisbasierend. Alles, was getan werden muß, ist eine Ausnahmebedingung zu formulieren und schon wird, wann immer diese Bedingung zutrifft, der entsprechende Ausnahmebehandlungsteil, den Sie ebenfalls selbst formulieren, aufgerufen.

Ein PL/SQL Block besteht bekanntlich aus mehreren Teilen: dem Deklarationsteil, dem ausführbaren Teil und dem Fehlerbehandlungsteil (exception section).

Wenn ein Fehler im ausführbaren Teil des Blockes auftritt, wird die Programmkontrolle an den Exception Teil übergeben. PL/SQL durchsucht den Exception Teil nach einer Behandlung für den aufgetretenen Fehler.

Ein Fehler gilt als behandelt, wenn im Exception Teil ein sogenannter Exception Handler existiert. Das bedeutet, wenn es eine WHEN Klausel gibt, die der aufgetretenen Ausnahme (=Fehler) entspricht. Wenn dies zutrifft, werden die ausführbaren Anweisungen des Exception Handlers ausgeführt. Wenn es keine Behandlung gibt, aber eine WHEN OTHERS Klausel existiert, werden die hier formulierten Anweisungen ausgeführt. Wenn keine der obigen Bedingungen zutrifft, gilt die Ausnahme als unbehandelt und der Fehlercode mitsamt dem Fehlertext wird “hochgereicht”, im einfachsten Fall also dem Benutzer präsentiert.

Typen von Exceptions

Es gibt vier Typen von Exceptions.

  • Benannte System Exceptions. Dies sind Ausnahmen, die von ORACLE bereits benannt wurden und ausgelöst werden als Ergebnis eines Fehlers in PL/SQL Programmen oder innerhalb der Datenbank.
  • Benannte, durch den Programmierer definierte Exceptions. Diese Fehler werden ausgelöst durch Ihre eigene Programmierlogik (bspw. Wollen Sie einen Fehler auslösen, wenn das Gehalt eines Mitarbeiters kleiner null wird). Diese Ausnahmen können bei der Deklaration benannt werden und im ausführbaren Teil ausgelöst werden (RAISE).
  • Unbenannte System Exceptions. Diese Ausnahmen werden ebenfalls aufgrund eines Fehlers innerhalb von PL/SQL oder innerhalb der Datenbank ausgelöst, sind aber durch ORACLE nicht benannt. Daher ist ohne weitere Vorbereitung eine Reaktion Ihres Programms auf diese Fehler nicht möglich. Sie können diese ORACLE-Fehler mit Hilfe des Pragmas EXCEPTION_INIT an eine Ausnahme in Ihrem PL/SQL Programm binden und dann darauf reagieren.
  • Unbenannte, durch den Programmierer definierte Ausnahmen. Diese Ausnahmen werden durch den Entwickler definiert und ausgelöst. Sie werden durch die Prozedur RAISE_APPLICATION_ERROR ausgelöst und „hochgereicht“. Eine Reaktion innerhalb des gleichen Programms ist bei dieser Fehlerklasse nicht möglich.

Benannte System Exceptions

Beispiele für benannte Systemfehler sind z.B. NO_DATA_FOUND, DUP_VAL_ON_INDEX oder ZERO_DIVIDE-Ausnahmen. (die benannten Exceptions findet man am besten in der ORACLE Dokumentation. Sie sind allerdings nur ein ganz kleiner Teil der möglichen Fehler, die die Datenbank zurückliefern kann)

Beispiel:

DECLARE
    gehalt number;
BEGIN
-- wenn das folgende Select keinen Wert liefert, wird NO_DATA_FOUND
-- ausgelöst. Darauf reagiert der Exception-Teil mit einer
-- Ausgabe auf dem Bildschirm
 SELECT sal into gehalt from emp where empno=99;
EXCEPTION
  WHEN no_data_found THEN
    dbms_output.put_line('Diesen Mitarbeiter gibt es nicht');
END;

Benannte, durch den Programmierer definierte Exceptions

Hier haben wir ein Beispiel für eine durch den Programmierer benannte Exception. Ich habe eine Exception erzeugt mit Namen no_sales. Im ausführbaren Teil löse ich die Ausnahme explizit aus. Die Ausführung wird sofort im Exception Teil fortgesetzt.

DECLARE
  no_sales EXCEPTION;
BEGIN
  RAISE no_sales;
EXCEPTION
  WHEN no_sales THEN
    NULL;
  WHEN OTHERS THEN
-- der WHEN OTHERS Teil reagiert auf alle nicht behandelten Fehler
-- er muss am Ende der WHEN-Liste stehen!
    NULL;
END;

Die Ausnahme wird im Deklarationsteil definiert. Im ausführbaren Teil steht nur die Anweisung zum Auslösen der Ausnahme. PL/SQL durchsucht nun den Exception Teil nach dem Exception Handler der Ausnahme und führt die Anweisungen für den Handler aus (die NULL-Anweisung). Damit gilt der Fehler, oder die Ausnahme als behandelt.

Unbenannte System Exceptions

Wenn ein interner Fehler auftritt (bspw. Ein Extent kann nicht angelegt werden oder eine Berechtigung zur Manipulation von Tabellen fehlt), liefert ORACLE den entspr. Fehler, der anhand seiner Fehlernummer identifiziert wird. PL/SQL stellt hier keine vordefinierten Fehler zur Verfügung. Wir können aber einen eigenen Handler für solche Fehler schreiben. Dazu bindet man den ORACLE-Fehler an eine im Programm deklarierte Ausnahme. Das geschieht mit dem Pragma EXCEPTION_INIT.

DECLARE
  value_found EXCEPTION;
  PRAGMA exception_init(value_found, -00001);
BEGIN
  RAISE value_found;
EXCEPTION
  WHEN value_found THEN
    NULL;
END;

Das Beispiel ist nicht sinnvoll, zeigt jedoch das Binden der Fehler: ORACLEs? Fehler -00001 ist der Fehler “duplicate values found”, der bspw. beim Verletzen eines Unique Constraints auftritt. Wann immer dieser Fehler im ausführbaren Teil auftritt, wird der Exception Handler angesprungen. Das Beispiel zeigt, dass Sie aber nach wie vor solche Fehler auch selbst mit RAISE auslösen können. Sinnvoll ist das Beispiel deshalb nicht, weil es für den Fehler -00001 bereits eine von ORACLE vordefinierte Ausnahme gibt: DUP_VAL_ON_INDEX.

Verhalten und Propagation

Wenn eine Ausnahme in einem Block deklariert wird, ist sie lokal innerhalb des Blocks, aber global für alle Blöcke, die durch den Block umschlossen werden. Alle lokal deklarierten Ausnhamen haben Vorrang gegenüber den System Exceptions. Das bedeutet, wenn Sie eine Ausnahme no_data_found erzeugen, ersetzt diese Ausnahme das Standard-Verhalten der System Exception gleichen Namens. Es ist also keine gute Idee, eigene Ausnahmen genauso zu nennen, wie vordefinierte Exceptions.

Wenn eine Ausnahme ausgelöst wurde, sucht PL/SQL den entsprechenden Handler im aktuellen Block. Wird der Fehler dort nicht behandelt, propagiert PL/SQL den Fehler in den übergeordneten Block. Wird der Fehler auch hier nicht behandelt, wird weiterpropagiert. Gibt es keine weiteren Blöcke, in die “hinaufgereicht” werden kann, erhält der Benutzer einen unbehandelten Ausnahme-Fehler.

Exceptions auslösen

Ausnahmen werden automatisch ausgelöst, wenn ein Datenbankfehler oder PL/SQL-Fehler auftritt. Sie können aber auch explizit vom Entwickler ausgelöst werden mit Hilfe des RAISE Statements. Gleichermaßen kann der Entwickler die Prozedur RAISE_APPLICATION_ERROR nutzen, um Fehler an die Client-Applikation zu melden.

Exceptions behandeln

Wurde eine Ausnahme ausgelöst, bricht die normale Programmausführung ab und der Exception Teil erhält die Programmkontrolle. Aus dem Exception Teil ist es nicht möglich, in den ausführbaren Teil zurückzukehren. Das scheint eine enorme Einschränkung zu sein, lässt sich aber über geschachtelte Blöcke umgehen.

Wenn Sie die WHEN OTHERS Klausel im Exception Teil nutzen, muß dies der letzte Handler im Exception Teil sein. Denn PL/SQL scannt den Exception Teil von oben nach unten nach dem passenden Handler durch. Da WHEN OTHERS auf jede Form von Ausnahme reagiert, würden dahinterstehende Handler niemals angesprungen werden.

Unbehandelte Exceptions

Wenn eine Ausnahme ausgelöst wird und es keinen Handler im Exception Teil gibt, wird dies als unbehandelte Ausnahme bezeichnet und propagiert. Eine Anwendung mit gutem Design wird einen WHEN OTHERS Teil haben, um auch unerwartete Ausnahmen abzufangen.

Es gibt zwei Standards, die es erlauben, zu prüfen, welcher Fehler aufgetreten ist: SQLCODE und SQLERRM.

SQLCODE und SQLERRM

Die Funktion SQLCODE wird genutzt, um die Fehlernummer der letzten aufgetretenen Ausnahme zu ermitteln. Die Funktion SQLERRM erhält als Argument die Fehlernummer und liefert den dazugehörenden Fehlertext.

DECLARE
   gehalt number;
BEGIN
   select sal into gehalt from emp where empno=4711;
EXCEPTION
  WHEN OTHERS THEN
    dbms_output.put_line('Fehler: '||sqlcode);
    dbms_output.put_line('Fehlertext:'||sqlerrm(sqlcode));
END;

RAISE_APPLICATION_ERROR

Die RAISE_APPLICATION_ERROR Prozedur ist der einzige Weg, Fehler an die Client-Applikation zu senden. Sie wird häufig genutzt, um mit Hilfe von Triggern Datenbankfehler an den Client zu melden. Die Prozedur wird mit zwei Argumenten aufgerufen. Das erste Argument ist die Fehlernummer (Nummern zwischen -20001 und -21000 stehen zur freien Verfügung), das zweite Argument ein beliebiger Fehlertext. Unmittelbar nach Aufruf der Prozedur bricht das Programm ab.

Beispiel

BEGIN
   raise_application_error (-20001,'Abbruch mit  Fehler');
END; 

Übersteuerte Ausnahmen

Systemausnahmen müssen eigentlich nicht deklariert werden, da sie ja bereits deklariert sind, sobald PL/SQL ausgeführt wird. Wenn man jedoch – warum auch immer – eine eigene Ausnahme mit dem gleichen Namen wie eine Systemausnahme deklarieren will, ist dies möglich. Die benutzerdefinierte Ausnahme übersteuert die Systemausnahme.

declare
  dup_val_on_index exception;
begin 
  ...

Diese Ausnahme übersteuert die Systemausnahme. Sie wird nicht mehr ausgelöst, wenn ein doppelter Wert in eine Spalte mit einem UNIQUE INDEX eingefügt wird, was ja das normale Verhalten wäre.

Ausnahmen qualifizieren

Wenn Sie also aus irgendwelchen sadistischen Gründen eine benutzerdefinierte Ausnahme gleich einer Systemausnahme benennen, übersteuert diese die Systemausnahme. Trotzdem ist es möglich, zwischen beiden Ausnahmen zu unterscheiden. Sie müssen dazu lediglich genauer qualifiziert werden.

declare
  dup_val_on_index exception;
begin
 null;
exception
  when dup_val_on_index then
    dbms_output.put_line('This is our exception');
  when standard.dup_val_on_index then
    dbms_output.put_line('The named system exception');
end; 

Das obere Beispiel unterscheidet also zwischen der benutzerdefinierten und der Systemausnahme und erlaubt es PL/SQL, den richtigen Fehlerbehandlungsteil zu finden.

Aber das Übersteuern von Systemausnahmen ist generell eine schlechte Idee, also denken wir gar nicht erst an mögliche Konsequenzen und, schlimmer noch, an das aufwendige Debuggen.

Arbeiten mit Records

PL/SQL unterstützt drei verschiedene Typen von Records: tabellenbasierte, cursorbasierte und selbstdefinierte Records. Alle diese Typen werden verschieden benutzt, haben aber die gleiche interne Struktur. Jeder Record besteht aus einem oder mehreren Feldern. Das Feld eines selbstdefinierten Records kann wiederum selbst ein Record sein.

  • Tabellenbasierte Records beruhen auf der Spaltenstruktur einer zugrundeliegenden Tabelle und jedes Feld des Records heißt genauso wie die Spalte der Tabelle
  • Cursorbasierte Records beruhen auf dem SELECT Statement eines Cursors. Erinnern Sie sich, dass das SELECT Statement eine “virtuelle” Tabelle erzeugt. Jedes Feld des Records beruht damit auf einer Spalte oder einem Ausdruck des SELECT Kommandos in der Cursordeklaration.
  • Selbstdefinierte Records werden vom Programmierer bereitgestellt, jedes Feld wird einzeln deklariert unter Angabe des Namens und Datentyps.

Vorteile von Records

Einfachheit ist der wohl wichtigste Grund. Es ist sehr viel einfacher, einen Records zu referenzieren als alle einzelnen Spalten einer Tabelle oder alle Spalten eines CURSORSELECT Statements.

Tabellenbasierte Records

Ein tabellenbasierter Record beruht in seinem Aufbau auf der Struktur einer Tabelle. Jedes Feld des Records entspricht einer Spalte der Tabelle, auf die wir uns bei der Deklaration des Records beziehen. Die Deklaration von tabellenbasierten Records erfolgt über das %ROWTYPE Attribut. Das Format der Deklaration ist also

record_name table_name%ROWTYPE; 

Erinnern Sie sich an die verankerte Deklaration mit Hilfe des TYPE beziehen wir uns auf den (skalaren) Datentypen einer Spalte. Über %ROWTYPE beziehen wir uns auf den Aufbau der gesamten Tabelle.

Cursorbasierte Records

Ein cursorbasierter Record beruht auf dem Aufbau des SELECT-Statements. Jedes Feld im Record korrespondiert mit einem Spaltennamen oder Ausdruck aus dem Cursor. Cursorbasierte Records machen es sehr einfach, eine Datenstruktur bereitzustellen, die die Spaltenwerte des Cursors aufnehmen kann. Wir beziehen uns bei der Deklaration wieder mit %ROWTYPE auf eine vorhandene Struktur. Diesmal nicht auf eine Tabelle, sondern auf einen Cursor.

cursor c1 is select * from emp;
record_name c1%ROWTYPE; 

Referenzieren von Records

Um das Feld eines Records anzusprechen, wird das Feld wie folgt referenziert:

--record_name.attribute_name
-- also z.B.
mitarbeiter.name := 'Hansi';

Der Attributname muß stets komplett ausgeschrieben werden. Soll der gesamte Record referenziert werden, reicht es, den Namen des Records anzugeben.

Selbstdefinierte Records

Es ist möglich, Recordstrukturen zu erzeugen, die weder etwas mit Tabellen noch mit Cursors zu tun haben. Ich beschreibe sie hier nur der Vollständigkeit halber, selber benutze ich sie so gut wie nie, denn meist erreicht man mit erstgenannten Records schneller das Ziel.

Die Syntax, um eigene Records zu deklarieren, ist folgende:

TYPE record_typ IS RECORD
  (field_name1 datatype1,
   field_name2 datatype2,
   ..
   ..
   field_nameN datatypeN); 
my_record_var record_typ;

Auch hier ist es möglich, den einzelnen Feldern einen Initialwert zuzuordnen.

Verschachtelte records

Weiterhin ist er möglich, Recordstrukturen zu bauen, die ihrerseits wieder auf Records basieren. An dieser Stelle sei lediglich erwähnt, dass es möglich ist, ohne weiter darauf einzugehen. Verschachtelte Records werden in Verbindung mit PL/SQL Tabellen wieder interessant.

Records zuweisen und vergleichen

Die Zuweisung von Recordvariablen an andere Recordvariablen gleichen Typs ist möglich, sie müssen aber den GLEICHEN Typ haben. Selbst wenn Sie zwei Typen deklarieren, die völlig identisch sind, wird ein Recordvergleich scheitern. Ein Recordvergleich scheitert in jedem Fall.

...
-- das funktioniert
 myrecord1 := myrecord2;
-- der Vergleich bringt TYPE MISMATCH
 if myrecord1 = myrecord2 then
...

Damit haben wir den ersten komplexen Datentyp kennengelernt. Nun existieren noch die PL/SQL Tabellen …

PL/SQL-Tabellen (associative arrays)

PL/SQL Tabellen sind erst ab PL/SQL Version 2 verfügbar und werden in der PGA der ORACLE Instanz abgelegt. Sie existieren also nur als eine serverseitige Speicherstruktur und können daher clientseitig (z.B. Im Developer 2000) nicht manipuliert werden.

PL/SQL Tabellen werden seit ORACLE 9i als associative array bezeichnet, ich nenne sie hier jedoch weiterhin PL/SQL-Tabellen.
Bitte beachten Sie hier bereits einen Unterschied zwischen den Begrifflichkeiten PL/SQL Tabelle, nested table und varray. Die letzteren sind ebenfalls Datentypen in PL/SQL, werden hier aber noch nicht behandelt.

Charakteristik von PL/SQL Tabellen

PL/SQL Tabellen sind merkwürdig. Auf der einen Seite sind sie wie Arrays, auf der anderen Seite nicht. Einerseits verhalten sie sich wie Tabellen, andererseits wieder nicht. Also, was sind sie nun?

Nun, lassen Sie uns sowohl PL/SQL Tabellen wie auch Arrays und SQL Tabellen beschreiben, um die Unterschiede herauszuarbeiten und den Einsatz von PL/SQL Tabellen kennenzulernen.

  • PL/SQL Tabellen sind eindimensional. Das bedeutet, sie können nur aus einer Spalte bestehen. Arrays wie auch Tabellen können aus mehr als einer Dimension bestehen. Es sei hier aber erwähnt, dass der Datentyp der einen Spalte frei wählbar ist und damit auch ein Record sein kann, der wiederum aus mehreren Feldern besteht. Damit ließe sich die Beschränkung der Eindimensionalität aufheben. Dies ist erst seit PL/SQL Version 2.3 möglich.
  • PL/SQL Tabellen sind in Ihrer Größe unbeschränkt. Anders als Arrays, deren Größe zum Erzeugungszeitpunkt angegeben werden muß. PL/SQL Tabellen wachsen dynamisch, je mehr Zeilen der Tabelle hinzugefügt werden.
  • Sie sind lückenhaft. Das soll heißen, daß eine Zeile in der Tabelle nur existiert, wenn ihr auch ein Wert zugewiesen wurde. Neue Zeilen müssen also nicht sequentiell hinzugefügt werden, sondern ein Überspringen einzelner „Zeilen“ ist möglich. Anders als bei SQL Tabellen greift man auf die einzelnen Zeilen der Tabelle mit einem Index zu. Wir können also in Zeile 10, 30 und in Zeile 1029 einen Wert ablegen. Alle anderen Zeilen bleiben unzugeordnet (ihr Wert ist damit aber nicht NULL, sondern tatsächlich „unassigned“). Dieses Verhalten ist bei Arrays ein anderes: Wenn ein Array deklariert wird, ist wirklich jeder Zelle Speicher zugeordnet.
  • PL/SQL Tabellen haben einen Indizierungsmodus. Oft wird BINARY_INTEGER genutzt, es gibt aber auch andere Indizierungsmodi. Der Index fungiert als der Primärschlüssel der Tabelle. Da der Index ein Integer ist, kann man also keinen Wert in Zeile 2.3 einfügen, sehr wohl aber in Zeile -5!

DML und PL/SQL-Tabellen

PL/SQL Tabellen verhalten sich anders als SQL Tabellen:

  • Es gibt keine Transaktionsintegrität, Manipulationen an PL/SQL Tabellen können also nicht mit COMMIT oder ROLLBACK rückgängig gemacht werden.
  • Aus einer PL/SQL Tabelle kann man nicht selektieren, sondern muß ihre Inhalte in einem Schleifenkonstrukt bearbeiten.
  • Man kann kein INSERT oder UPDATE in einer PL/SQL Tabelle machen. Ein DELETE ist seit Version 2.3 möglich
  • Da es sich hier um PL/SQL Datenstrukturen handelt, existieren diese nur für die Dauer der Programmausführung. Sie sind flüchtig wie Variablen.