PL SQL

PL/SQL
Paradigmen:	prozedural
Erscheinungsjahr:	1991
Entwickler:	Oracle
Aktuelle Version:	10.2  (2005)
Typisierung:	stark, statisch, explizit
Einflüsse:	COBOL, PL/I, Ada
Betriebssystem:	plattformunabhängig
Lizenz:	proprietär
Oracle Technology Network

PL/SQL (Procedural Language/SQL) ist eine proprietäre Programmiersprache der Firma Oracle.

PL/SQL verbindet die Abfragesprache SQL mit einer prozeduralen Programmiersprache. Die Syntax wurde sehr stark an die Programmiersprache Ada angelehnt.

Unterstützt werden Variablen, Bedingungen, Schleifen und Ausnahmebehandlungen. Ab der Version 8 der Oracle-RDBMS halten auch objektorientierte Merkmale Einzug.

PL/SQL ist besonders für das Arbeiten mit Datenbanken ausgelegt. Insbesondere kann man im Quelltext SQL-Befehle nach dem Oracle-Standard einfügen. Dabei werden die SQL-Anweisungen nicht als Zeichenketten erzeugt und an eine Datenbankschnittstelle übergeben (wie z. B. bei ODBC, JDBC u.ä.), sondern fügen sich nahtlos in den Programmcode ein. Die Korrektheit der SQL-Statements kann somit schon durch Kompilieren verifiziert werden, zumindest wenn diese statisch, also nicht erst zur Laufzeit erzeugt sind. Dies gilt jedoch nur für DML-Befehle. DDL und DCL müssen mit dbms_sql (einem PL/SQL-Package) oder mit der Syntax "execute immediate <befehl>" ausgeführt werden.

Verwendung

• Man kann PL/SQL-Code wie SQL-Befehle über ein Datenbank-Frontend absetzen, der dann direkt abgearbeitet wird.
• Man kann einzelne Unterprogramme (Stored Procedures) oder Bibliotheken mehrerer Unterprogramme (Stored Packages) als dauerhafte Datenbankobjekte auf dem Datenbankserver speichern und damit die Funktionalität der Datenbank erweitern; jeder Benutzer der Datenbank kann diese Unterprogramme aufrufen und nutzen. Die Berechtigungen können für jedes einzelne PL/SQL-Paket an einzelne Benutzer oder Benutzergruppen (sogenannte 'Rollen') vergeben werden.
• Programmierung von Datenbanktriggern
• Programmierung in diversen Tools (Oracle-Forms, Oracle-Reports)

Wenn PL/SQL-Programme direkt auf der Datenbank ausgeführt werden, führt dies zu einem erheblichen Geschwindigkeitsvorteil. Konkret kann Kommunikation zwischen Prozessen und eventuell Netzwerkverkehr – falls Datenbank und Applikationsserver auf unterschiedlicher Hardware ausgeführt werden – vermieden werden.

Grundlegender Aufbau

PL/SQL Programme bestehen aus Blöcken:

declare

-- Deklarationsblock

begin

-- Ausführungsteil

exception

-- Ausnahmeverarbeitung

end;

/* So kommentiert man

mehrzeilig */

--So kommentiert man einzeilig

Variablendefinitionen

Variablen werden im (optionalen) Deklarationsabschnitt definiert und optional initialisiert.

declare

Zahl1 number(2);

Zahl2 number(2) := 17;

Text varchar(20) := 'Das ist ein Text';

begin

select hausnummer into Zahl1 from Adressverzeichnis where name='Meier';

end;

:= ist der Zuweisungsoperator, mit dem man einer Variable einen Wert zuweist.

Zahlenvariablen

variablenname number(P[,S]) := Wert;

Um eine Zahlenvariable zu definieren, schreibt man den Variablennamen gefolgt vom Variablentyp "number".

Hinter diesem schreibt man in runden Klammern die Genauigkeit P sowie optional ein Komma und die Anzahl an Nachkommastellen S.

Genauigkeit entspricht in diesem Fall der Anzahl an Stellen, welche die Variable enthalten kann, und nicht dem Wertebereich.

Auswahl weiterer Datentypen für Zahlenvariablen:

dec, decimal, double precision, integer, int, numeric, real, smallint, binary_integer, pls_integer

Textvariablen

variablenname varchar(L) := 'Text';

Um eine Textvariable zu definieren, schreibt man den Variablennamen gefolgt vom Variablentyp "varchar".

Hinter diesem schreibt man die Anzahl der Zeichen, die in der Variable gespeichert werden können, in Klammern.

Auswahl weiterer Datentypen für Textvariablen:

varchar, varchar2, char, long, raw, long raw, nchar, nchar2, clob

Boolean

variablenname boolean:= true;

Kann TRUE, FALSE oder NULL sein.

Datum

variablenname date :=to_date( '01.01.2005' , 'DD.MM.YYYY');

Um eine Datumsvariable zu definieren schreibt man den Variablennamen gefolgt vom Variablentyp "date".

Damit man einen Text in ein Datum konvertieren kann, stellt Oracle die Funktion to_date zur Verfügung.

Diese Funktion wandelt den Text zwischen den ersten Hochkommas in ein Datum mit dem angegebenen Format zwischen den zweiten Hochkommas um.

Beispiel: "to_date('31.12.2004','dd.mm.yyyy')"

Um ein Datum in einen Text zu konvertieren, gibt es die Funktion to_char(Datum).

Datentyp über Spalte festlegen

Variablenname Tabellenname.Spaltenname%type;

Definiert eine Variable des Typs der angegebenen Spalte.

Datentyp über Tabelle festlegen

Variablenname Tabellenname%rowtype;

Definiert eine Variable des Typs der angegebenen Tabelle.

Beispiel :

   CURSOR cursor_name IS
       SELECT *
       FROM tabelle;
   
   variable tabelle%rowtype;
   
   ...
   
   FOR i IN cursor_name LOOP
   
       variable := i;
       andere_variable := variable.SPALTENNAME;
   
   END LOOP;

Benutzerdefinierte Datentypen

Benutzerdefinierte Datentypen werden mit Hilfe von "type datentyp is record(feld1 typ1 :=xyz, feld2 typ2 :=xyz, ..., feldn typn :=xyz);" definiert.

declare

type t_adresse is record(

hausname adresse.hausname%type,

strasse adresse.strasse%type,

hausnummer adresse.hausnummer%type,

postleitzahl adresse.postleitzahl%type);

v_adresse t_adresse;

begin

select hausname,strasse, hausnummer, postleitzahl into v_adresse from adresse where rownum = 1;

end;

Das Beispielprogramm definiert einen eigenen Datentyp mit Namen t_adresse welcher die Felder hausname, strasse, hausnummer und postleitzahl enthält.

Mit diesem Datentyp wird eine Variable v_adresse definiert, welche mit einem Datensatz aus der Tabelle adresse gefüllt wird.

Mittels Punktnotation kann auf die Attribute zurückgegriffen werden "v_adresse.hausname := 'Nollenburgerweg 115';"

Schleifen

Schleifen wiederholen die in ihrem Rumpf enthaltenen Anweisungen.

Loop-Schleife (Basisschleife)

loop

...

exit when BEDINGUNG;

end loop;

Die loop-Schleife wiederholt die in ihrem Körper enthaltenen Anweisungen. Sie kann durch ein "exit when" gefolgt von einer Abbruchbedingung beendet werden.

Beachte: Auch wenn die Bedingung für das "exit" erfüllt ist, werden die Anweisungen, die im Schleifenkörper vor der exit-Anweisung stehen, mindestens einmal ausgeführt.

While-Schleife

while Bedingung loop

...

end loop;

Die while-Schleife wiederholt die in ihrem Körper enthaltenen Anweisungen, so lange die Bedingung in ihrem Kopf erfüllt ist.

Beachte: Sollte die Bedingung im Kopf nicht erfüllt sein, werden die Anweisungen im Schleifenkörper nie ausgeführt.

For-Schleife

for v_counter in 1..10 loop

...

end loop;

Die for-Schleife zählt eine Indexvariable von einem festgelegten Startwert bis zu einem festgelegten Endwert. Der kleinere Wert steht immer links, der größere immer rechts. Gibt man das Schlüsselwort REVERSE nach dem IN an, so wird vom größeren zum kleineren Wert heruntergezählt.

Beachte: Auch hierbei muss der kleinere Wert links und der größere Wert rechts stehen.

begin
   for i in reverse 1 .. 5 loop
      dbms_output.put_line(to_char(i));
   end loop;
end;

5
4
3
2
1

Hinweis: Wenn Sie beim Arbeiten mit SQL*Plus die erwarteten Zahlen 5 bis 1 nicht sehen, müssen Sie vorher die Ausgabe einschalten

set serveroutput on

Cursor-For-Schleife

for Record-Index in (Select Mitarbeiter_Nummer from Personaltabelle) loop

...

end loop;

Die Cursor for-Schleife öffnet automatisch den Cursor, liest die Datensätze ein und schließt den Cursor wieder.

Alternativ dazu kann das SELECT-Statement des Cursors auch vorher definiert werden, um es mehrfach zu verwenden bzw. um die Darstellung übersichtlicher zu gestalten (besonders bei längeren/komplexeren Abfragen von Vorteil).

cursor cursor_mitarbeiter is Select Mitarbeiter_Nummer from Personaltabelle;

for Record-Index in cursor_mitarbeiter loop

...

end loop;

Der Zugriff auf die Mitarbeiter-Nummer innerhalb der FOR-Schleife erfolgt mit dem Verbindungsoperator ".":

Record-Index.Mitarbeiter_Nummer

Bedingungen

Mit Hilfe von Bedingungen kann man auf verschiedene Situationen unterschiedlich reagieren.

If-Then-Else

declare

v_land welt.land%type;

begin

dbms_output.enable(20000);

select land into v_land from welt

where rownum = 1;

if v_land = 39 then

dbms_output.put_line('Land ist 39');

elsif v_land = 49 then

dbms_output.put_line('Land ist 49');

else

dbms_output.put_line('Land unbekannt');

end if;

end;

'if' prüft, ob eine Bedingung erfüllt ist. Ist die Bedingung erfüllt, wird der Code zwischen 'if' und 'end if' ausgeführt, ansonsten wird er übersprungen. Optional kann mit 'elsif' eine weitere Bedingung mit zugehörigem Code angegeben werden, der ausgeführt wird, falls diese Bedingung erfüllt ist. Zu guter Letzt kann man ein allgemeines 'else' angeben, dessen Code ausgeführt wird, wenn keine der vorangegangenen Bedingungen erfüllt waren.

Simple CASE

 CASE <variable>
   WHEN <Ausdruck1> THEN <ausführen>;
   WHEN <Ausdruck2> THEN <ausführen>;
   .
   WHEN <Ausdruckn> THEN <ausführen>;
   ELSE <ausführen>;
 END CASE;
 /

CASE vergleicht eine Variable mit einem Ausdruck und führt bei der ersten Übereinstimmung einen bestimmten Befehl aus und endet dann.

Searched CASE

 CASE 
   WHEN <variable> < <Wert> THEN <ausführen>;
   WHEN <variable> between <Wert> and <Wert> THEN <ausführen>;
   .
   WHEN <Vergleich> THEN <ausführen>;
   ELSE <ausführen>;
 END CASE;
 /

In der WHEN-Klausel wird ein (ansonsten beliebiger) Ausdruck ausgewertet, wobei das Ergebnis ein boolescher Wert (also TRUE oder FALSE) sein muss.

Das CASE-Statement führt den ersten THEN-Befehl aus, dessen WHEN-Ausdruck TRUE war und endet dann. Ist dies in keiner WHEN-Klausel der Fall, so wird der ELSE-Befehl (sofern vorhanden) durchgeführt.

CASE bei Oracle

Exception-Handling

Es gibt zwei Arten von Exceptions:

• vordefinierte Exceptions und
• benutzerdefinierte Exceptions,

deren Handling analog ist.

Vordefinierte Exceptions

Diese Art von Exceptions werden automatisch von PL/SQL ausgelöst, wenn Fehler bei der Arbeit mit Datenbankobjekten (Tabellen, Views, Packages u.ä.) oder Programmierfehler (bspw. Division durch Null) bei der Abarbeitung des Programms auftreten. Die Exceptions haben vordefinierte Namen, die in PL/SQL-Programmen verwendet werden können. Jede Exception hat einen Fehlercode, der aus den Buchstaben ORA- und 5 Ziffern besteht. Typische Exceptions dieser Art sind

no_data_found (ORA-00100) − Eine SELECT-Anfrage liefert eine leere Datenmenge

too_many_rows (ORA-01422) − Eine SELECT-Anfrage liefert eine Datenmenge mit mehr als einem Satz, an einer Stelle, an der nur ein einzelner Satz als Ergebnis der Anfrage erwartet wurde.

Benutzerdefinierte Exceptions

Für die Behandlung logischer Programmfehler können eigene Exceptions in der Form

 <exception_name> exception;
 pragma exception_init(<exception_name>, -<exception_number>);

definiert und durch das Kommando

 raise <exception_name>;

ausgelöst werden.

Exceptionblock

Der Exceptionblock dient dazu, alle Exceptions des dazugehörigen Ausführungsteiles abzufangen. Es besteht damit die Möglichkeit, nach einer Exception eine individuelle Fehlerbehandlung anzuschließen. Zum einen kann der Fehler nach dem Abfangen behandelt werden, danach kann im Programm weitergearbeitet werden; zum anderen kann die Exception an den umschließenden Block weitergereicht werden. Falls die Exception bis zum äußersten Block propagiert wird und dort keine entsprechende Fehlerbehandlung definiert wurde, führt dies zu einem unkontrollierten Abbruch des Programms.

Der generelle Aufbau eines Exceptionblocks ist folgender:

  exception
  when <exception_name_1> then
     -- Execptionhandlung für die Exception <exception_name_1>
     Ausführungsteil 
  ...
  when <exception_name_n> then
     -- Execptionhandlung für die Exception <exception_name_n>
     Ausführungsteil
  when others then
    -- Execptionhandlung für alle restlichen, nicht erwarteten Exceptions
     Ausführungsteil 
  end;

Der when others-Zweig ist optional; er fängt alle bis dahin im Exceptionblock nicht behandelten Exceptions ab. Fehlt dieser Zweig, werden unbehandelte Exceptions implizit an den umschließenden Block weitergereicht. Soll eine Exception explizit weitergereicht werden, muss der Ausführungsteil mit der Programmzeile raise <exception_name>; enden.

Vergleichbare Möglichkeiten der Programmierung bei anderen Datenbanken

• PL/pgSQL in der Open-Source-Datenbank PostgreSQL
• SQL-PL für Adabas
• SQL PL für DB2
• Transact SQL (TSQL) ist die entsprechende Programmiersprache für RDBMS von Sybase und Microsoft
• Stored Procedures in MySQL ab Version 5

Siehe auch

• Application Server
• Embedded SQL

Weblinks

• Oracle PL/SQL Users Guide
• Tutorial für PL/SQL
• Umfangreiches PL/SQL Tutorial
• Oracle FAQ’s: PL/SQL
• Ask Tom
• Performanceoptimierung von PL/SQL-Routinen: native Kompilierung
• Stored Procedures in MySQL ab Version 5