Quelltext der Seite PL/SQL

{{Infobox Programmiersprache
| Name = 
| Logo = 
| Beschreibung = Prozedurale Erweiterung von [[SQL]]
| Paradigma = prozedural
| Erscheinungsjahr = 1991
| Designer = 
| Entwickler = [[Oracle]]
| AktuelleVersion = 11.1
| AktuelleVersionFreigabeDatum = 2009
| AktuelleVorabVersion = 
| AktuelleVorabVersionFreigabeDatum = 
| Typisierung = [[Starke Typisierung|stark]], [[Statische Typisierung|statisch]], [[Explizite Typisierung|explizit]]
| Implementierung = [[Oracle (Datenbanksystem)|Oracle-Datenbank]]
| Dialekte = 
| Standardisierungen = 
| Beeinflusst_von = [[Ada (Programmiersprache)|Ada]], [[SQL]]
| Beeinflusste = 
| Betriebssystem = 
| Lizenz = proprietär
| Website = [http://technet.oracle.com/ Oracle Technology Network]
}}

'''PL/SQL''' ('''P'''rocedural '''L'''anguage/'''S'''tructured '''Q'''uery '''L'''anguage) ist eine [[proprietär]]e [[Programmiersprache]] der Firma [[Oracle]].

PL/SQL verbindet die [[Abfragesprache]] [[SQL]] mit einer [[Prozedurale Programmierung|prozeduralen]] Programmiersprache. Die Syntax ist stark an die Programmiersprache [[Ada (Programmiersprache)|Ada]] angelehnt.

Unterstützt werden [[Variable (Programmierung)|Variablen]], [[Bedingte Anweisung und Verzweigung|Bedingungen]], [[Schleife (Programmierung)|Schleifen]] und [[Ausnahmebehandlung]]en. Ab Version 8 des [[Oracle (Datenbanksystem)|Oracle-RDBMS]] halten auch [[Objektorientierung|objektorientierte]] Merkmale Einzug.

PL/SQL ist für das Arbeiten mit [[Oracle (Datenbanksystem)|Oracle-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.&nbsp;B. bei [[Open Database Connectivity|ODBC]], [[Java Database Connectivity|JDBC]] u.&nbsp;ä.), sondern fügen sich nahtlos in den Programmcode ein. Die Syntax kann damit bereits zum Zeitpunkt der Kompilierung überprüft werden.

Die [[Gespeicherte Prozedur|prozedurale Erweiterung]] der SQL-Abfragesprache wird inzwischen auch von vielen anderen Datenbankherstellern implementiert. Daher wurde diese prozedurale SQL-Erweiterung inzwischen vom [[American National Standards Institute|ANSI-Gremium]] standardisiert.

== Verwendung ==
* Man kann PL/SQL-Code wie SQL-Befehle über ein Datenbank-[[Front-End und Back-End|Frontend]] absetzen, der dann direkt abgearbeitet wird.
* Man kann einzelne Unterprogramme ([[Stored Procedure]]s) oder Bibliotheken mehrerer Unterprogramme (Stored Packages) als dauerhafte [[Datenbankobjekt]]e 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 [[Datenbanktrigger]]n
* Programmierung in diversen Tools ([[Oracle Forms|Oracle-Forms]], Oracle-Reports)

PL/SQL-Programme können die Performance verbessern, wenn der Aufruf von einem Applikationsserver ausgeführt wird, der über eine langsame Netzwerkverbindung mit dem Datenbankserver verbunden ist. So muss in diesem Fall nur zu Beginn und am Ende der Ausführung eine Nachricht über das Netzwerk transportiert werden. Es gibt aber auch andere Möglichkeiten, bei einem langsamen Netzwerk die Performance einer Applikation zu verbessern. So kann z.&nbsp;B. die Oracle-Datenbank auch Java-Programme ausführen, die die Datenbank-Manipulationen vornehmen.

== Grundlegender Aufbau ==

'''PL/SQL'''-Programme bestehen aus Blöcken:
<syntaxhighlight lang="sql">
  declare
      -- Deklarationsblock
  begin
     -- Ausführungsteil
  exception
     -- Ausnahmeverarbeitung
  end;

  /* So kommentiert man
  mehrzeilig */
  --So kommentiert man einzeilig
</syntaxhighlight>

== Variablendefinitionen ==

Variablen werden im (optionalen) Deklarationsabschnitt definiert und optional initialisiert.
<syntaxhighlight lang="plpgsql">
  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' and rownum=1;
  end;
</syntaxhighlight>

<code>:=</code> ist der Zuweisungsoperator, mit dem man einer Variable einen Wert zuweist.

=== Zahlenvariablen ===
<syntaxhighlight lang="plpgsql">
  variablenname number(P[,S]) := Wert;
</syntaxhighlight>

Um eine Zahlenvariable zu definieren, schreibt man zum Beispiel den Variablennamen, gefolgt vom Variablentyp <code>NUMBER</code>.

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

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

Auswahl weiterer Datentypen für Zahlenvariablen:
<syntaxhighlight lang="psql">
  dec, decimal, double precision, integer, int, numeric, real, smallint, binary_integer, pls_integer
</syntaxhighlight>

=== Textvariablen ===
<syntaxhighlight lang="plpgsql">
  variablenname varchar2(L) := 'Text';
</syntaxhighlight>

Um eine Textvariable zu definieren, schreibt man den Variablennamen gefolgt vom Variablentyp <code>VARCHAR2</code>.

Der angehängte Längenconstraint (Integer in Klammern) gibt die maximale Länge der Variablen in Bytes an (sofern die Length Semantic nicht auf CHAR gestellt ist).

Auswahl weiterer Datentypen für Textvariablen:
<syntaxhighlight lang="plpgsql">
CHAR, NCHAR, NVARCHAR2, CLOB, NCLOB
</syntaxhighlight>
Die Datentypen LONG, LONG RAW und VARCHAR sind deprecated.

=== Boolean ===
<syntaxhighlight lang="plpgsql">
  variablenname boolean := true;
</syntaxhighlight>

Kann <code>TRUE</code>, <code>FALSE</code> oder <code>NULL</code> sein.

=== Datum ===
<syntaxhighlight lang="plpgsql">
   datum_1   date;
   datum_2   date := to_date('31.12.2016', 'DD.MM.YYYY');
   datum_3   date := to_date('31.12.2016 23:59:59', 'DD.MM.YYYY HH24:MI:SS');
   datum_4   date := date '2016-12-31 ';
</syntaxhighlight>

Variablen vom Typ  <code>DATE</code> enthalten Datum und Uhrzeit sekundengenau. Um eine Datumsvariable zu definieren schreibt man den Variablennamen gefolgt vom Variablentyp <code>DATE</code>.
Die Funktion <code>TO_DATE</code> wandelt den Text zwischen den ersten Hochkommas in ein Datum mit dem angegebenen Format zwischen den zweiten Hochkommas um, wobei die Angabe einer Uhrzeit optional und der Default 00:00:00 Uhr ist. Ein Datum ohne Uhrzeitanteil kann ohne Konvertierung von Zeichenkette zu Datum mit einem Datumsliteral definiert werden; man schreibt dann das Schlüsselwort  <code>date</code>, gefolgt von Datum im Format 'JJJJ-MM-TT'.
Um ein Datum in einen Text zu konvertieren, gibt es die Funktion <code>TO_CHAR(Datum)</code>.
Neben dem Datentyp <code>date</code> gibt es noch den Datentyp <code>timestamp</code>, der eine größere Präzision hat. Hierzu gibt es weitere Subtypen: <code>timestamp with time zone</code> speichert die Zeitzone mit den Zeitinformationen, <code>timestamp with local timezone</code> konvertiert jeweils von der Zeitzone der Session in die lokale Zeitzone des Datenbankservers.

=== Datentyp über Spalte festlegen ===
<syntaxhighlight lang="plpgsql">
  Variablenname Tabellenname.Spaltenname%type;
</syntaxhighlight>

Definiert eine Variable des Typs der angegebenen Spalte.

=== Datentyp über Tabelle festlegen ===
<syntaxhighlight lang="plpgsql">
  Variablenname Tabellenname%rowtype;
</syntaxhighlight>

Definiert eine Variable für einen Datensatz/Row des Typs der angegebenen Tabelle.

Beispiel:
<syntaxhighlight lang="plpgsql">
    CURSOR cursor_name IS
        SELECT *
        FROM tabelle;

    variable tabelle%rowtype;

    ...

    FOR i IN cursor_name LOOP
        variable := i;
        andere_variable := variable.SPALTENNAME;
    END LOOP;
</syntaxhighlight>

=== Gültigkeitsbereich von Variablen ===
PL/SQL erlaubt es, Blöcke zu verschachteln. Variablen, die in äußeren Blöcken deklariert werden, sind in allen inneren Blöcken gültig. Variablen, die in inneren Blöcken deklariert werden, sind nicht in äußeren Blöcken gültig.

Je nachdem, wo Variablen deklariert werden, kann man zwischen zwei Arten unterscheiden.
*Lokale Variablen – Diese werden in einem inneren Block deklariert. Von außen kann nicht darauf zugegriffen werden.
*Globale Variablen – Diese werden in einem äußeren Block deklariert, und es kann von außen und innen darauf zugegriffen werden.

In folgendem Beispiel werden zwei Variablen deklariert, und die Summe derer wird in einem inneren Block einer dritten Variable zugewiesen. Auf die Variablen <code>var_num1</code> und <code>var_num2</code> kann von überall des Blocks aus zugegriffen werden. Auf die Variable <code>var_result</code> dagegen, welche im inneren Block deklariert wurde, kann von außerhalb nicht zugegriffen werden.
<syntaxhighlight lang="plpgsql">
DECLARE
  var_num1 NUMBER;
  var_num2 NUMBER;
BEGIN
  var_num1 := 100;
  var_num2 := 200;
  DECLARE
    var_result NUMBER;
  BEGIN
    var_result := var_num1 + var_num2;
  END;
  /* Auf var_result kann hier nicht zugegriffen werden */
END;
</syntaxhighlight>

=== Konstanten ===
Eine Konstante ist ein Wert in einem PL/SQL Block, der sich während des Programmablaufes nicht verändert.

<syntaxhighlight lang="plpgsql">
DECLARE
  konstantenname CONSTANT NUMBER(3) := 10;
</syntaxhighlight>
Die Wertzuweisung einer Konstante muss direkt bei der Deklaration erfolgen.

== Benutzerdefinierte Datentypen ==

Benutzerdefinierte Datentypen werden definiert mit Hilfe von:
<syntaxhighlight lang="psql">
  type datentyp is record(feld1 typ1 [:=xyz], feld2 typ2 [:=xyz], ..., feldn typn [:=xyz]);
</syntaxhighlight>

'''Beispiel:'''
<syntaxhighlight lang="plpgsql">
  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;
</syntaxhighlight>

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

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

Mittels Punktnotation kann auf die Attribute zurückgegriffen werden
<syntaxhighlight lang="plpgsql">
  v_adresse.hausname := 'Nollenburgerweg 115';
</syntaxhighlight>

== Schleifen ==

Schleifen wiederholen die in ihrem Rumpf enthaltenen Anweisungen.

=== Loop-Schleife (Basisschleife) ===
<syntaxhighlight lang="plpgsql">
  loop
    ...
    exit when BEDINGUNG;
  end loop;
</syntaxhighlight>

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

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

=== While-Schleife ===
<syntaxhighlight lang="plpgsql">
  while Bedingung loop
    ...
  end loop;
</syntaxhighlight>

Die <code>while</code>-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 ===
<syntaxhighlight lang="plpgsql">
  for v_counter in 1..10 loop
    ...
  end loop;
</syntaxhighlight>

Die <code>for</code>-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 <code>REVERSE</code> nach dem <code>IN</code> 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.

<syntaxhighlight lang="plpgsql">
  begin
     for i in reverse 1 .. 5 loop
      dbms_output.put_line(to_char(i));
     end loop;
  end;
</syntaxhighlight>

<syntaxhighlight lang="text">
  5
  4
  3
  2
  1
</syntaxhighlight>

'''Hinweis:''' Wenn Sie beim Arbeiten mit SQL*Plus die erwarteten Zahlen 5 bis 1 nicht sehen, müssen Sie vorher die Ausgabe einschalten.
:
<syntaxhighlight lang="plpgsql">
  set serveroutput on
</syntaxhighlight>

=== Cursor-For-Schleife ===
<syntaxhighlight lang="plpgsql">
  for Record-Index in (Select Mitarbeiter_Nummer from Personaltabelle)
  loop
    ...
  end loop;
</syntaxhighlight>

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

Alternativ dazu kann das <code>SELECT</code>-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).
<syntaxhighlight lang="plpgsql">
  cursor cursor_mitarbeiter is

  Select Mitarbeiter_Nummer from Personaltabelle;
  for Record-Index in cursor_mitarbeiter

  loop
    ...
  end loop;
</syntaxhighlight>

Der Zugriff auf die Mitarbeiter-Nummer innerhalb der <code>FOR</code>-Schleife erfolgt mit dem Verbindungsoperator <code>.</code>:
<syntaxhighlight lang="plpgsql">
  Record-Index.Mitarbeiter_Nummer
</syntaxhighlight>

== Bedingungen ==

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

=== IF-THEN-ELSE ===
<syntaxhighlight lang="plpgsql">
  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;
</syntaxhighlight>

<code>if</code> prüft, ob eine Bedingung erfüllt ist. Ist die Bedingung erfüllt, wird der Code zwischen <code>if</code> und <code>end if</code> ausgeführt, ansonsten wird er übersprungen. Optional kann mit <code>elsif</code> 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 <code>else</code> angeben, dessen Code ausgeführt wird, wenn keine der vorangegangenen Bedingungen erfüllt waren.

Bedingungsstrukturen kann man zudem mittels des <code>CASE</code>-Statements ausdrücken.
=== CASE-WHEN ===
<syntaxhighlight lang="plpgsql">
  DECLARE
    v_land welt.land%TYPE;
  BEGIN
    DBMS_OUTPUT.enable(20000);
    SELECT land INTO v_land FROM welt
      WHERE ROWNUM = 1;
    CASE WHEN v_land = 39 THEN DBMS_OUTPUT.put_line('Land ist 39');
         WHEN v_land = 49 THEN DBMS_OUTPUT.put_line('Land ist 49');
         WHEN v_land = 59 THEN DBMS_OUTPUT.put_line('Land ist 59');
                          ELSE DBMS_OUTPUT.put_line('Land unbekannt');
    END CASE;
  END;
</syntaxhighlight>

Vereinfachte Form für reine Wertelisten:
<syntaxhighlight lang="plpgsql">
  DECLARE
    v_land welt.land%TYPE;
  BEGIN
    DBMS_OUTPUT.enable(20000);
    SELECT land INTO v_land FROM welt
      WHERE ROWNUM = 1;
    CASE v_land
       WHEN 39 THEN DBMS_OUTPUT.put_line('Land ist 39');
       WHEN 49 THEN DBMS_OUTPUT.put_line('Land ist 49');
       WHEN 59 THEN DBMS_OUTPUT.put_line('Land ist 59');
               ELSE DBMS_OUTPUT.put_line('Land unbekannt');
    END CASE;
  END;
</syntaxhighlight>

== Exception-Handling ==
Es gibt zwei Arten von Exceptions:
* vordefinierte Exceptions und
* benutzerdefinierte Exceptions,
deren Handling analog ist.

=== Vordefinierte Exceptions ===
Exceptions werden automatisch von PL/SQL ausgelöst, wenn Fehler bei der Arbeit mit Datenbankobjekten (Tabellen, Views, Packages u.&nbsp;ä.) oder Programmierfehler (bspw. Division durch Null) bei der Abarbeitung des Programms auftreten. Es gibt in PL/SQL 20 Exceptions mit vordefinierten Namen.
Jede Exception hat einen Fehlercode, der aus den Buchstaben '''ORA-''' und 5 Ziffern besteht.
Typische Exceptions dieser Art sind

'''no_data_found (ORA-01403)''' – 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
<syntaxhighlight lang="plpgsql">
  <exception_name> exception;
  pragma exception_init(<exception_name>, -<exception_number>);
</syntaxhighlight>

definiert und durch das Kommando
<syntaxhighlight lang="plpgsql">
  raise <exception_name>;
</syntaxhighlight>

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:
<syntaxhighlight lang="plpgsql">
  begin
     -- Programmcode der eine Exception auslösen kann
     Ausführungsteil
     ...
  exception
  when <exception_name_1> then
     -- Exceptionbehandlung für die Exception <exception_name_1>
     Ausführungsteil
  ...
  when <exception_name_n> then
     -- Exceptionbehandlung für die Exception <exception_name_n>
     Ausführungsteil
  when others then
    -- Exceptionbehandlung für alle restlichen, nicht erwarteten Exceptions
     Ausführungsteil
  end;
</syntaxhighlight>

Der <code>when others</code>-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 <code>raise <exception_name>;</code> enden.

== Vergleich mit T-SQL ==
PL/SQL ist trotz oberflächlicher Ähnlichkeiten grundsätzlich anders als [[Transact-SQL]]. Bei der Übernahme von Quellcode handelt es sich daher in der Regel um eine nicht ganz einfache Arbeit. Nicht nur auf Grund der Unterschiede in den Feature-Sets der beiden Sprachen,<ref>[http://vyaskn.tripod.com/oracle_sql_server_differences_equivalents.htm vyaskn.tripod.com].</ref> sondern auch wegen der sehr deutlichen Unterschiede, wie Oracle und SQL Server mit der Steuerung der Parallelität und dem [[Lock]]ing umgehen. Es gibt jedoch Software-Tools, die die Übernahme erleichtern können, zum Beispiel Oracle Translation Scratch Editor<ref>Scratchlauncher [http://www.oracle.com/technetwork/database/migration/scratchlauncher-098387.html ''Oracle Scratchlauncher.'']</ref> und SwisSQL.<ref>[http://www.swissql.com/ swissql.com].</ref>

== Vergleichbare Möglichkeiten der Programmierung mit anderen Datenbank-Management-Systemen ==
* [[PL/pgSQL]] in der [[Open Source|Open-Source]]-[[Datenbank]] [[PostgreSQL]]
* [[SQL-PL]] für [[Adabas]]
* [[Db2#SQL PL|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

== Weblinks ==
{{Wikibooks|PL/SQL}}
* [http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/toc.htm PL/SQL Dokumentation von Oracle] (englisch)
* [http://www.orafaq.com/wiki/PL/SQL Oracle FAQ’s: PL/SQL]

== Einzelnachweise ==
<references />
{{Normdaten|TYP=s|GND=4457669-9}}

{{SORTIERUNG:Plsql}}
[[Kategorie:SQL]]
[[Kategorie:Oracle]]
[[Kategorie:Programmiersprache]]