PL/SQL (Procedural Language/Structured Query Language) ist eine proprietäre Programmiersprache der Firma Oracle.
PL/SQL verbindet die Abfragesprache SQL mit einer prozeduralen Programmiersprache. Die Syntax ist stark an die Programmiersprache Ada angelehnt.
Unterstützt werden Variablen, Bedingungen, Schleifen und Ausnahmebehandlungen. Ab Version 8 des Oracle-RDBMS halten auch objektorientierte Merkmale Einzug.
PL/SQL ist für das Arbeiten mit 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. B. bei ODBC, JDBC u. ä.), sondern fügen sich nahtlos in den Programmcode ein. Die Syntax kann damit bereits zum Zeitpunkt der Kompilierung überprüft werden.
Die prozedurale Erweiterung der SQL-Abfragesprache wird inzwischen auch von vielen anderen Datenbankherstellern implementiert. Daher wurde diese prozedurale SQL-Erweiterung inzwischen vom ANSI-Gremium standardisiert.
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)
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. B. die Oracle-Datenbank auch Java-Programme ausführen, die die Datenbank-Manipulationen vornehmen.
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' and rownum=1; 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 zum Beispiel 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 varchar2(L) := 'Text';
Um eine Textvariable zu definieren, schreibt man den Variablennamen gefolgt vom Variablentyp VARCHAR2.
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: CHAR, NCHAR, NVARCHAR2, CLOB, NCLOB Die Datentypen LONG, LONG RAW und VARCHAR sind deprecated.
Boolean variablenname boolean := true;
Kann TRUE, FALSE oder NULL sein.
Datum 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 ';
Variablen vom Typ DATE enthalten Datum und Uhrzeit sekundengenau. Um eine Datumsvariable zu definieren, schreibt man den Variablennamen gefolgt vom Variablentyp DATE. Die Funktion TO_DATE 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 date, gefolgt von Datum im Format 'JJJJ-MM-TT'. Um ein Datum in einen Text zu konvertieren, gibt es die Funktion TO_CHAR(Datum). Neben dem Datentyp date gibt es noch den Datentyp timestamp, der eine größere Präzision hat. Hierzu gibt es weitere Subtypen: timestamp with time zone speichert die Zeitzone mit den Zeitinformationen, timestamp with local timezone konvertiert jeweils von der Zeitzone der Session in die lokale Zeitzone des Datenbankservers.
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 für einen Datensatz/Row 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;
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 var_num1 und var_num2 kann von überall des Blocks aus zugegriffen werden. Auf die Variable var_result dagegen, welche im inneren Block deklariert wurde, kann von außerhalb nicht zugegriffen werden. 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;
Konstanten Eine Konstante ist ein Wert in einem PL/SQL Block, der sich während des Programmablaufes nicht verändert.
DECLARE konstantenname CONSTANT NUMBER(3) := 10; Die Wertzuweisung einer Konstante muss direkt bei der Deklaration erfolgen.
Benutzerdefinierte Datentypen
Benutzerdefinierte Datentypen werden definiert mit Hilfe von: type datentyp is record(feld1 typ1 [:=xyz], feld2 typ2 [:=xyz], ..., feldn typn [:=xyz]);
Beispiel: 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
Ab Oracle 21c gibt es sehr viele Erweiterungen zur FOR-Schleife, die die Syntax enorm ausweiten. Die bisher (aus Ada übernommene) absichtlich stark eingeschränkte FOR-Schleife kann damit umgangen werden.
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.
Bedingungsstrukturen kann man zudem mittels des CASE-Statements ausdrücken. CASE-WHEN 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;
Vereinfachte Form für reine Wertelisten: 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');