Einführung in PL/SQL

8
88
Einführung in PL/SQL
Das Speichern und Auslesen von Informationen ist nur ein Teil jeder „echten“ Applikation. Auch die einfachsten Programme müssen Aktionen durchführen, die sich mit
SQL gar nicht oder nur sehr schwer umsetzen ließen. Denken Sie nur daran, wie aufwändig die Berechnungen sind, die jedes Jahr vom Finanzamt vorgenommen werden
müssen, um die korrekten Steuern zu berechnen! OK, vielleicht möchten Sie sich lieber ein anderes Beispiel vorstellen. Auf jeden Fall ist SQL alleine nicht sehr nützlich.
8.1
Was ist PL/SQL?
Sie werden sich fragen, warum SQL nicht über Fähigkeiten verfügt, die es Ihnen ermöglichen, aufwändigere Berechnungen mit den Daten durchzuführen. Das ist zum
Teil historisch bedingt: SQL entstand als Datenbank-Abfragesprache (Structured
Query Language) und wurde weiterentwickelt und optimiert für genau diesen Zweck:
Datenbanken abzufragen. Verschiedene Datenbank-Hersteller haben sich auf bestimmte SQL-Standards geeinigt, nicht aber darauf, wie den Benutzern weitergehende
SQL-orientierte Programmierumgebungen zur Verfügung gestellt werden sollten. Daher präsentiert jeder Datenbank-Hersteller seine proprietäre oder semi-proprietäre
Lösung. Oracle bezeichnet seine Lösung als PL/SQL. Dies steht für „Programming
Language for SQL“.
In diesem Kapitel werden Sie in die Grundlagen von PL/SQL eingeführt. Sie werden
den Unterschied zwischen SQL, SQL*Plus und PL/SQL kennen lernen. Weiterhin
werden Sie erste einfache PL/SQL-Prozeduren schreiben sowie Funktionen, die
grundlegende PL/SQL-Konstrukte wie Variablen, Schleifen und Cursor verwenden.
Dann werden Sie den wichtigen Bereich der Fehlerbehandlung kennen lernen, damit
dem Benutzer verständliche Mitteilungen gemacht werden können.
Wenn Sie erst in diesem Kapitel mit dem Lesen des Buchs begonnen und noch keine
der Übungen aus den vorigen Kapiteln nachgespielt haben, sollten Sie die Beispielta-
280
8 Einführung in PL/SQL
bellen aus den vorigen Kapiteln erstellen, bevor Sie die Übungen aus diesem Kapitel
nachvollziehen können. Dazu dienen die folgenden SQL-Befehle:
DROP
DROP
DROP
DROP
DROP
TABLE
TABLE
TABLE
TABLE
TABLE
plsql101_purchase;
plsql101_product;
plsql101_person;
plsql101_old_item;
plsql101_purchase_archive;
CREATE TABLE plsql101_person (
person_code VARCHAR2(3) PRIMARY KEY,
first_name VARCHAR2(15),
last_name
VARCHAR2(20),
hire_date
DATE
)
;
CREATE INDEX plsql101_person_name_index
ON plsql101_person(last_name, first_name);
ALTER TABLE plsql101_person
ADD CONSTRAINT plsql101_person_unique UNIQUE (
first_name,
last_name,
hire_date
)
;
INSERT INTO plsql101_person VALUES
(’CA’, ’Charlotte’, ’Atlas’, ’01.02.02’);
INSERT INTO plsql101_person VALUES
(’GA’, ’Gerhard’, ’Anderson’, ’15.02.02’);
INSERT INTO plsql101_person VALUES
(’BB’, ’Bernd’, ’Barkenhagen’, ’28.02.02’);
INSERT INTO plsql101_person VALUES
(’LB’, ’Lars’, ’Baxter’, ’01.03.02’);
INSERT INTO plsql101_person VALUES (
’LN’, ’Lara’, ’Normann’, ’01.06.03’);
CREATE TABLE plsql101_product (
product_name
VARCHAR2(25) PRIMARY KEY,
product_price
NUMBER(4,2),
quantity_on_hand NUMBER(5,0),
last_stock_date DATE
)
;
ALTER TABLE plsql101_product ADD CONSTRAINT positive_quantity CHECK(
quantity_on_hand IS NOT NULL
AND
8.1 Was ist PL/SQL?
;
281
quantity_on_hand >= 0
)
INSERT INTO plsql101_product VALUES
(’Small Widget’, 99, 1, ’15.01.03’);
INSERT INTO plsql101_product VALUES
(’Medium Wodget’, 75, 1000, ’15.01.02’);
INSERT INTO plsql101_product VALUES
(’Chrome Phoobar’, 50, 100, ’15.01.03’);
INSERT INTO plsql101_product VALUES
(’Round Chrome Snaphoo’, 25, 10000, NULL);
INSERT INTO plsql101_product VALUES
(’Extra Huge Mega Phoobar +’,9.95,1234,’15.01.04’);
INSERT INTO plsql101_product VALUES (’Square Zinculator’,
45, 1, TO_DATE(’31. Dezember 2002, 23:30’,
’dd. Month YYYY, HH24:MI’)
)
;
INSERT INTO plsql101_product VALUES (
’Anodized Framifier’, 49, 5, NULL);
INSERT INTO plsql101_product VALUES (
’Red Snaphoo’, 1.95, 10, ’31.12.00’);
INSERT INTO plsql101_product VALUES (
’Blue Snaphoo’, 1.95, 10, ’30.12.01’)
;
CREATE TABLE plsql101_purchase (
product_name VARCHAR2(25),
salesperson
VARCHAR2(3),
purchase_date DATE,
quantity
NUMBER(4,2)
)
;
ALTER TABLE plsql101_purchase
ADD PRIMARY KEY (product_name,
salesperson,
purchase_date
)
;
ALTER TABLE plsql101_purchase ADD CONSTRAINT reasonable_date CHECK(
purchase_date IS NOT NULL
AND
TO_CHAR(purchase_date, ’YYYY-MM-DD’) >= ’2000-06-30’
)
;
282
8 Einführung in PL/SQL
ALTER TABLE plsql101_purchase
ADD CONSTRAINT plsql101_purchase_fk_product FOREIGN KEY
(product_name) REFERENCES plsql101_product;
ALTER TABLE plsql101_purchase
ADD CONSTRAINT plsql101_purchase_fk_person FOREIGN KEY
(salesperson) REFERENCES plsql101_person;
CREATE INDEX plsql101_purchase_product
ON plsql101_purchase(product_name);
CREATE INDEX plsql101_purchase_salesperson
ON plsql101_purchase(salesperson);
INSERT INTO plsql101_purchase VALUES
(’Small Widget’, ’CA’, ’14.07.03’, 1);
INSERT INTO plsql101_purchase VALUES
(’Medium Wodget’, ’BB’, ’14.07.03’, 75);
INSERT INTO plsql101_purchase VALUES
(’Chrome Phoobar’, ’GA’, ’14.07.03’, 2);
INSERT INTO plsql101_purchase VALUES
(’Small Widget’, ’GA’, ’15.07.03’, 8);
INSERT INTO plsql101_purchase VALUES
(’Medium Wodget’, ’LB’, ’15.07.03’, 20);
INSERT INTO plsql101_purchase VALUES
(’Round Chrome Snaphoo’, ’CA’, ’16.07.03’, 5);
INSERT INTO plsql101_purchase VALUES (
’Small Widget’, ’CA’, ’17.07.03’, 1)
;
UPDATE plsql101_product
SET
product_price = product_price * .9
WHERE product_name NOT IN (
SELECT DISTINCT product_name
FROM
plsql101_purchase
)
;
CREATE TABLE plsql101_old_item (
item_id
CHAR(20),
item_desc CHAR(25)
)
;
INSERT INTO plsql101_old_item VALUES
(’LA-101’, ’Can, Small’);
INSERT INTO plsql101_old_item VALUES
(’LA-102’, ’Can, Large’);
INSERT INTO plsql101_old_item VALUES
(’LA-103’, ’Bottle, Small’);
283
8.1 Was ist PL/SQL?
INSERT INTO plsql101_old_item VALUES
(’LA-104’, ’Bottle, Large’);
INSERT INTO plsql101_old_item VALUES
(’NY-101’, ’Box, Small’);
INSERT INTO plsql101_old_item VALUES
(’NY-102’, ’Box, Large’);
INSERT INTO plsql101_old_item VALUES
(’NY-103’, ’Shipping Carton, Small’);
INSERT INTO plsql101_old_item VALUES
(’NY-104’, ’Shipping Carton, Large’);
CREATE TABLE plsql101_purchase_archive (
product_name VARCHAR2(25),
salesperson
VARCHAR2(3),
purchase_date DATE,
quantity
NUMBER(4,2)
)
;
INSERT INTO plsql101_purchase_archive VALUES
(’Round Snaphoo’, ’BB’, ’21.06.01’, 10);
INSERT INTO plsql101_purchase_archive VALUES
(’Large Harflinger’, ’GA’, ’22.06.01’, 50);
INSERT INTO plsql101_purchase_archive VALUES
(’Medium Wodget’, ’LB’, ’23.06.01’, 20);
INSERT INTO plsql101_purchase_archive VALUES
(’Small Widget’, ’ZZ’, ’24.06.02’, 80);
INSERT INTO plsql101_purchase_archive VALUES
(’Chrome Phoobar’, ’CA’, ’25.06.02’, 2);
INSERT INTO plsql101_purchase_archive VALUES
(’Small Widget’, ’JT’, ’26.06.02’, 50);
8.1.1
Beschreibung von PL/SQL
PL/SQL bietet Möglichkeiten, die es Ihnen erlauben, Informationen auf komplexe Art
und Weise zu verarbeiten. Jede Nacht werden Sie die Zusammenfassung der täglichen
Geschäftsvorgänge in eine Übersichtstabelle transferieren wollen – PL/SQL-Pakete
können Ihnen dabei helfen. Möchten Sie wissen, ob Sie zusätzliche Lieferungen anfordern müssen, um große Bestellungen bedienen zu können – PL/SQL bietet Trigger, die
Sie benachrichtigen, sobald eine Bestellung eingegeben wird, die bestimmte, vorgegebene Grenzen überschreitet. Sie können gespeicherte PL/SQL-Prozeduren nutzen, um
die Leistung Ihrer Mitarbeiter zu berechnen und den Bonus festzulegen. Eine nette
kleine PL/SQL-Funktion kann die Steuersätze für einen Angestellten ermitteln.
PL/SQL stellt Ihnen alle Datenmanipulationsmöglichkeiten, Cursor-Kontrollen und
Transaktionskontrollbefehle von SQL zur Verfügung, wie auch sämtliche SQL-Funktionen und -Operatoren. Damit behalten Sie bei der Manipulation von Daten in Ora-
284
8 Einführung in PL/SQL
cle die volle Flexibilität und Kontrolle. Außerdem unterstützt PL/SQL alle SQLDatentypen. Dadurch verringert sich die Notwendigkeit, Daten zu konvertieren, die
zwischen Ihrer Applikation und der Datenbank ausgetauscht werden. PL/SQL stellt
auch „Dynamic SQL“ zur Verfügung, eine fortgeschrittene Programmiertechnik, mit
der Ihre Applikationen flexibler und anpassbarer werden. Ihre Programme können
SQL-Befehle zur Datendefinition, Datenkontrolle und Transaktionskontrolle „on the
fly“ zur Laufzeit erstellen und ausführen lassen.
Bevor wir mehr über einige dieser Fähigkeiten erfahren, werde ich Ihnen erläutern, wie
PL/SQL, SQL und SQL*Plus miteinander verbunden sind.
8.1.2
Who’s Who in SQL, PL/SQL und SQL*Plus
Stellen Sie sich ein Restaurant vor. Sie gehen hinein und werden (hoffentlich) von einem gut ausgebildeten Ober erwartet. Sie schauen sich die Speisekarte an und geben
eine Bestellung auf. Der Ober notiert Ihre Bestellung und bringt sie in die Küche. Die
Küche ist groß – es gibt viele Köche und Hilfsköche. Sie können viele Speisen sehen –
gekocht, vorgegart und ungekocht – die in der Küche abgestellt sind. Es gibt auch Personen mit unterschiedlichen Aufgaben: Sie holen die Vorräte aus dem Lager, bereiten
bestimmte Gänge vor (zum Beispiel nur Suppen oder Salate) und so weiter. Ihrer
Menüfolge entsprechend gibt der Ober die Bestellung an verschiedene Köche weiter.
Einfache Gänge werden von nur einem Koch zubereitet, während aufwändigere Menüs die Hilfe eines Assistenten bei der Zubereitung erfordern oder sogar mehrere Köche. Zusätzlich gibt es „Standardbestellungen“ – ein Ober teilt einem Koch nur „Pizza
Hawaii“ mit – während andere Bestellungen individuell zusammengestellt sind und
eine genaue Liste der Zutaten erfordern.
Nun ändern wir das Szenario ein bisschen. Denken Sie an eine Oracle-Datenbank als
Küche des Restaurants, in der SQL*Plus als Ober unsere Bestellungen – Skripte, Befehle oder Programme – an die Küche oder Datenbank weiterleitet. Innerhalb der Küche
gibt es zwei Köche: SQL und PL/SQL. Wie ein Ober weiß SQL*Plus, welche Bestellungen es selbst abwickeln kann und welche es an wen weiterleiten muss. Genau wie ein
Ober Ihnen ein Glas Wasser bringen kann, ohne den Chefkoch zu belästigen, kann
SQL*Plus die Zeilenlänge auf Ihrem Bildschirm anpassen, ohne auf die Datenbank zugreifen zu müssen.
Die Befehle oder Programme, die Sie am SQL*Plus-Prompt eingeben und ausführen,
sind so etwas wie Ihre individuell zusammengestellte Pizza. Bei solchen Bestellungen
müssen die Köche jedes Mal ein bisschen nachdenken. Wie auch der Koch ein Rezept
für eine Käse-Pizza in seinem Gehirn gespeichert hat, können Sie PL/SQL „Rezepte“
für Ihre Lieblingsgerichte speichern lassen. Diese gespeicherten PL/SQL-Elemente
werden Trigger, gespeicherte Funktionen und Pakete genannt. Sie werden bald mehr
über sie lernen.
285
8.1 Was ist PL/SQL?
Wie ich schon weiter oben erwähnte, benötigen einige Bestellungen mehr als einen
Koch, um sie zu bearbeiten. Die meisten der interessanteren und nützlicheren Datenbank-Applikationen, die Sie erstellen, werden SQL und PL/SQL zusammen einsetzen,
wobei Informationen zwischen beiden Bereichen ausgetauscht werden, um ein Skript
oder ein Programm auszuführen. In einem Restaurant wird die Bestellung nach der
Zubereitung von einem Ober an Ihren Tisch gebracht. In ähnlicher Weise werden die
Ergebnisse von SQL- und PL/SQL-Programmen an SQL*Plus (oder ein eigenes Frontend) zurückgeliefert, um sie dem Benutzer anzuzeigen.
8.1.3
Gespeicherte Prozeduren, Funktionen
und Trigger
PL/SQL-Prozeduren, Funktionen und Trigger helfen Ihnen dabei, komplexe Geschäftslogiken einfach und in modularer Form (das heißt, Stück für Stück, wobei die
einzelnen Stücke von anderen Elementen wiederverwendet werden können) zu erstellen. Beim Abspeichern auf dem Oracle-Server bietet das zwei direkte Vorteile: Sie können immer wieder mit vorhersagbaren Ergebnissen genutzt werden und sie lassen sich
deutlich schneller ausführen, da Server-Operationen wenig oder gar keinen Netzwerkverkehr erzeugen.
Gespeicherte Prozeduren
Eine gespeicherte Prozedur ist ein festgelegtes Set von Aktionen, die mit der Programmiersprache PL/SQL geschrieben wurden. Wenn eine Prozedur aufgerufen wird, führt
sie die enthaltenen Aktionen aus. Die Prozedur ist in der Datenbank abgespeichert, daher heißt sie „gespeicherte Prozedur“.
Eine gespeicherte Prozedur kann SQL-Befehle ausführen und Daten in Tabellen verändern. Sie kann dazu auch von anderen gespeicherten PL/SQL-Prozeduren, Funktionen oder Triggern aufgerufen werden. Eine gespeicherte Prozedur kann auch direkt
vom SQL*Plus-Prompt aus gestartet werden. Wenn Sie die nächsten Seiten lesen, werden Sie lernen, wie jede dieser Methoden zum Aufruf einer gespeicherten Prozedur
verwendet wird.
Eine Prozedur besteht aus zwei Teilen: der Spezifikation und dem Rumpf. Die Prozedur-Spezifikation enthält den Namen der Prozedur und eine Beschreibung der Einund Ausgabeparameter. Die Ein- und Ausgabeparameter werden formale Parameter
oder formale Argumente der Prozedur genannt. Wenn ein Aufruf einer Prozedur Kommandozeilen-Parameter oder andere Eingaben mit einschließt, werden diese Werte
konkrete Parameter oder konkrete Argumente genannt.
Betrachten wir nun ein paar Beispiele für Prozedur-Spezifikationen. (Denken Sie daran, dass die Spezifikation keinerlei Code enthält; sie gibt der Prozedur nur einen Namen und legt die Ein- und Ausgabeparameter fest, die die Prozedur verwenden kann.)
286
8 Einführung in PL/SQL
run_ytd_reports
Diese einfache Spezifikation enthält nur den Prozedurnamen. Sie hat keine Parameter.
increase_prices (percent_increase NUMBER)
Ein Wert kann an diese Prozedur weitergegeben werden, wenn sie aufgerufen wird. Innerhalb der Prozedur wird der Wert als PERCENT_INCREASE angesprochen. Beachten Sie, dass der Datentyp festgelegt wurde: NUMBER.
increase_salary_find_tax (increase_percent IN
NUMBER := 7,
sal
IN OUT NUMBER,
tax
OUT NUMBER
)
Hier haben wir eine Prozedur mit drei formalen Parametern. Das Wort „IN“ nach einem Parameternamen zeigt an, dass die Prozedur einen Eingabewert aus den Parametern nutzen kann, wenn sie aufgerufen wird. Das Wort „OUT“ nach einem
Parameternamen zeigt an, dass die Prozedur diesen Parameter nutzen kann, um
Werte an den Aufrufenden zurückzuliefern. Ein Parameter mit den Schlüsselwörtern
„IN OUT“ kann einen Wert in die Prozedur einbringen, aber auch als Rückgabewert
dienen.
Dem Parameter INCREASE_PERCENT wird in diesem Beispiel ein Standardwert von
7 zugewiesen, indem „:= 7“ nach dem Datentyp angefügt wird. Wenn nun die Prozedur ohne die Angabe einer prozentualen Erhöhung aufgerufen wird, wird sie die Gehälter um sieben Prozent erhöhen und die Steuern basierend auf den neuen Werten
berechnen.
Hinweis:
Datentypen in einer Prozedur können keine Größenangaben
enthalten. Sie können zum Beispiel festlegen, dass ein Parameter
vom Typ NUMBER ist, aber nicht vom Typ NUMBER(10, 2).
Der Prozedurrumpf ist ein Block mit PL/SQL-Code, über den Sie im nächsten Abschnitt dieses Kapitels einiges lernen werden.
Gespeicherte Funktionen
Eine PL/SQL-Funktion ist einer PL/SQL-Prozedur sehr ähnlich: Sie besitz eine Funktionsspezifikation und einen Funktionsrumpf. Der entscheidende Unterschied zwischen einer Prozedur und einer Funktion ist der, dass eine Funktion dafür gedacht ist,
einen Wert zurückzuliefern, der in einem größeren SQL-Befehl genutzt werden kann.
287
8.1 Was ist PL/SQL?
Stellen Sie sich zum Beispiel eine Funktion vor, die zum Berechnen der prozentualen
Differenz zwischen zwei Zahlen gedacht ist. Ohne den Code, der die Berechnung
durchführt, würde die Spezifikation so aussehen:
calc_percent(value_1 NUMBER,
value_2 NUMBER) return NUMBER
Diese Funktion akzeptiert zwei Zahlen als Eingabewerte, auf die intern mit VALUE_1
und VALUE_2 verwiesen wird. Nachdem der Rumpf der Funktion geschrieben wurde,
können Sie sie in einem SQL-Befehl wie folgt verwenden:
INSERT INTO employee VALUES (3000, CALC_PERCENT(300, 3000));
Trigger
Ein Trigger ist eine PL/SQL-Prozedur, die automatisch ausgeführt wird, sobald ein bestimmtes, vom Trigger definiertes Ereignis – das auslösende Ereignis – eintritt. Sie können Trigger schreiben, die ausgelöst werden, sobald ein INSERT-, UPDATE- oder
DELETE-Befehl auf eine Tabelle ausgeführt wird; wenn DDL-Befehle genutzt werden;
wenn sich ein Benutzer an- oder abmeldet, oder wenn die Datenbank startet, einen
Fehler meldet oder herunterfährt.
Trigger unterscheiden sich von PL/SQL-Prozeduren in drei Punkten:
n Sie können einen Trigger nicht aus Ihrem Code aufrufen. Trigger werden von
Oracle automatisch als Reaktion auf ein vorgegebenes Ereignis aufgerufen.
n Trigger enthalten keine Parameterliste.
n Die Spezifikation eines Triggers enthält andere Informationen als die für eine Prozedur.
Sie werden mehr über Trigger und ihre Verwendung im nächsten Kapitel lernen.
8.1.4
Gespeicherte Prozeduren und SQL-Skripte
Während SQL-Skripte auf der Festplatte Ihres Rechners gespeichert sind, werden gespeicherte Prozeduren innerhalb Ihrer Oracle-Datenbank abgelegt. Ein SQL-Skript
enthält eine Reihe von SQL-Befehlen, die nacheinander ausgeführt werden, wenn Sie
das Skript aufrufen. Eine gespeicherte Prozedur dagegen kann Befehle zur Ablaufkontrolle enthalten, die es ihr ermöglichen, einen bestimmten Teil des Codes immer wieder abzuarbeiten, zu einem anderen Code-Abschnitt zu verzweigen, wenn bestimmte
Situationen auftreten, und auf Fehler in der von Ihnen vorgegebenen Art und Weise
zu reagieren.
288
8 Einführung in PL/SQL
8.2
Struktur eines PL/SQL-Blocks
In diesem Abschnitt werden Sie einen einfachen PL/SQL-Block kennen lernen. Alles,
was in PL/SQL läuft, besteht aus Blöcken. Nachdem Sie sich mit dem PL/SQL-Block
vertraut gemacht haben, werden Sie im nächsten Abschnitt Beispiele für komplette
Prozeduren, Funktionen und Trigger gezeigt bekommen.
Ein PL/SQL-Block besteht aus vier Abschnitten: dem Kopf, einem optionalen Deklarationsabschnitt, dem Ausführungsabschnitt und einem optionalen Ausnahmeabschnitt.
Ein anonymer Block ist ein PL/SQL-Block ohne Kopf oder Namensabschnitt, daher
auch der Begriff „anonymer Block“. Anonyme Blöcke können unter SQL*Plus ausgeführt werden und in PL/SQL-Funktionen, Prozeduren und Triggern Verwendung finden. Denken Sie daran, dass PL/SQL-Prozeduren, Funktionen und Trigger selbst alle
aus PL/SQL-Blöcken bestehen. Das bedeutet, dass Sie einen PL/SQL-Block innerhalb
eines anderen PL/SQL-Blocks haben können. Sie werden mehr darüber weiter unten
in diesem Abschnitt lernen.
Vielleicht ist der beste Weg, einen PL/SQL-Block zu verstehen, ein Beispiel auszuführen. Geben Sie zunächst den folgenden Befehl ein, damit Informationen von Programmen in SQL*Plus ausgegeben werden.
set serveroutput on
Nun geben Sie den folgenden Code ein, um einen anonymen Block zu erstellen. Vergleichen Sie Ihre Ergebnisse mit Abbildung 8-1.
DECLARE
BEGIN
num_a NUMBER := 6;
num_b NUMBER;
num_b := 0;
num_a := num_a / num_b;
num_b := 7;
dbms_output.put_line(’ Wert von num_b ’ || num_b);
EXCEPTION
WHEN ZERO_DIVIDE
THEN
dbms_output.put_line(’Versuch, durch Null zu teilen’);
dbms_output.put_line(’ Wert von num_a ’ || num_a);
dbms_output.put_line(’ Wert von num_b ’ || num_b);
END;
/
8.2 Struktur eines PL/SQL-Blocks
289
Abbildung 8-1: Beispiel für einen anonymen PL/SQL-Block
8.2.1
Kopf
Der Kopf eines Blocks sieht abhängig davon, wozu er gehört, unterschiedlich aus. Erinnern Sie sich daran, dass Prozeduren, Funktionen, Trigger und anonyme Blöcke aus
PL/SQL-Blöcken bestehen. Tatsächlich hat jeder von ihnen einen PL/SQL-Block, der
den Rumpf darstellt. Dieser Rumpf-Block kann nun wiederum mehrere PL/SQL-Blöcke enthalten. Der Kopf dieses obersten Blocks einer Funktion, Prozedur oder eines
Triggers ist die Spezifikation für diese Funktion, Prozedur oder diesen Trigger. Bei anonymen Blöcken enthält der Kopf nur das Schlüsselwort DECLARE. Bezeichnete Blöcke enthalten im Kopf den Namen, der durch << und >> umschlossen wird, gefolgt
vom Schlüsselwort DECLARE, wie im Folgenden gezeigt:
<<just_a_label>>
DECLARE
Blockbezeichner erhöhen die Lesbarkeit des Codes. In einer Prozedur, die verschachtelte Blöcke verwendet (Blöcke innerhalb anderer Blöcke) können Sie sich auf ein Element in einem bestimmten Block beziehen, indem Sie vor den Namen des Elements
den Namen des Blocks setzen (zum Beispiel block_label.item_label).
290
8 Einführung in PL/SQL
8.2.2
Deklarationsabschnitt
Der Deklarationsabschnitt ist optional. Wenn er genutzt wird, beginnt er direkt nach
dem Kopf und endet mit dem Schlüsselwort BEGIN. Der Deklarationsabschnitt enthält die Deklarationen für PL/SQL-Variablen, Konstanten, Cursor, Exceptions, Funktionen und Prozeduren, die von den Ausführungs- und Ausnahmeabschnitten
verwendet werden. Alle Deklarationen von Variablen und Konstanten müssen angegeben werden, bevor die erste Funktions- oder Prozedurdeklaration aufgeführt ist. Sie
werden mehr über PL/SQL-Variablen und Konstanten im folgenden Abschnitt lernen.
Eine Deklaration teilt PL/SQL mit, dass es eine Variable, Konstante, Funktion, Prozedur oder einen Cursor so erstellen soll, wie es in der Deklaration festgelegt wurde.
Der Deklarationsabschnitt im Beispiel von Abbildung 8-1 auf Seite 289 fordert PL/SQL
dazu auf, zwei Variablen vom Typ NUMBER anzulegen, die die Namen Num_a und
Num_b erhalten sollen. Er weist der Variablen Num_a zudem noch den Standardwert
6 zu.
Wenn ein PL/SQL-Block abgearbeitet wurde, hören alle Deklarationen aus dessen Deklarationsabschnitt auf zu existieren. Elemente aus dem Deklarationsabschnitt eines
Blocks können nur innerhalb des gleichen Blocks verwendet werden. Daher gibt es
nach dem Durchlauf des Beispiel-Blocks in SQL*Plus auch keinerlei Möglichkeit
mehr, Num_a an eine andere PL/SQL-Prozedur weiterzugeben. Num_a und Num_b
verschwinden direkt nach der Beendigung des Blocks aus dem Blickfeld. Wenn Sie allerdings eine PL/SQL-Funktion oder -Prozedur innerhalb des Ausführungs- oder Ausnahmeabschnitts des Blocks aufrufen, können Sie ihnen Num_a oder Num_b als
konkrete Parameter mitgeben.
Langer Rede kurzer Sinn: Alles, was im Deklarationsabschnitt angelegt wird, ist das
Privateigentum des Blocks – es kann nur durch ihn genutzt werden und ist auch nur
für ihn sichtbar. Alles aus dem Deklarationsabschnitt des Blocks existiert nur solange
wie der Block selbst. Technisch gesprochen: Num_a und Num_b besitzen den Geltungsbereich des Blocks, in dem sie deklariert wurden. Der Geltungsbereich des Blocks
beginnt mit dem Anfang des Deklarationsabschnitts und endet mit dem Ende seines
Ausnahmeabschnitts.
8.2.3
Ausführungsabschnitt
Der Ausführungsabschnitt beginnt mit dem Schlüsselwort BEGIN und endet auf zwei
verschiedene Arten. Wenn es einen Ausnahmeabschnitt gibt, endet der Ausführungsabschnitt mit dem Schlüsselwort EXCEPTION. Wenn es keinen Ausnahmeabschnitt
gibt, endet er mit dem Schlüsselwort END, optional gefolgt vom Namen der Funktion
oder Prozedur, und einem Semikolon. Der Ausführungsabschnitt enthält einen oder
mehrere PL/SQL-Befehle, die ausgeführt werden, wenn der Block abläuft. Die Struktur des Ausführungsabschnitts sieht wie folgt aus:
8.2 Struktur eines PL/SQL-Blocks
291
BEGIN
einer oder mehrere PL/SQL-Befehle
[Ausnahmeabschnitt]
END [Name der Funktion oder Prozedur];
Der Ausführungsabschnitt des Beispielblocks enthält drei PL/SQL-Zuweisungsbefehle. Der Zuweisungsbefehl ist der am häufigsten verwendete Befehl im PL/SQL-Code.
Der erste Befehl weist Num_b den Wert 0 zu. Der Doppelpunkt, gefolgt von einem
Gleichheitszeichen, ist der Zuweisungsoperator. Er teilt PL/SQL mit, alles rechts von
ihm zu berechnen und das Ergebnis dem zuzuweisen, was sich links von ihm befindet.
Der zweite Befehl weist Num_a den Wert von Num_a geteilt durch Num_b zu. Beachten Sie, dass sich nach erfolgreicher Ausführung dieses Befehls der Wert von Num_a
geändert hat.
Der dritte Befehl weist Num_b den Wert 7 zu.
8.2.4
Ausnahmeabschnitt
Es ist möglich, dass während der Ausführung von PL/SQL-Anweisungen im Ausführungsabschnitt ein Fehler auftaucht, der es unmöglich macht, mit der Ausführung
fortzufahren. Diese Fehlerbedingungen werden Exceptions (oder Ausnahmen) genannt. Der Benutzer der Prozedur sollte über das Auftreten und den Grund einer Exception informiert werden. Vielleicht wollen Sie dem Benutzer eine sinnvolle
Fehlermeldung präsentieren, oder korrigierend eingreifen und versuchen, die Prozedur an der entsprechenden Stelle fortzuführen. Eventuell möchten Sie ja auch alle Änderungen an der Datenbank bis zum Auftreten des Fehlers rückgängig machen.
Für all diese Situationen stellt PL/SQL die Exception-Behandlung zur Verfügung. Exceptions sind für gut geschriebene Applikationen so wichtig, dass ich am Ende des Kapitels einen speziellen Abschnitt über sie angefügt habe, in dem Sie mehr darüber
erfahren können. Als Einführung sei hier die Struktur des Ausnahmeabschnitts aufgeführt:
EXCEPTION
WHEN exception_name
THEN
Aktionen, die beim Auftreten dieser Exception ausgeführt werden sollen
WHEN exception_name
THEN
Aktionen, die beim Auftreten dieser Exception ausgeführt werden sollen
292
8 Einführung in PL/SQL
Der Ausnahmeabschnitt beginnt mit dem Schlüsselwort EXCEPTION und endet am
Ende des Blocks. Für jede Exception gibt es eine Anweisung WHEN exception_name,
die angibt, was getan werden soll, wenn diese Exception auftritt. Unser Beispiel enthält
drei lustig aussehende Zeilen, die dafür sorgen, dass auf Ihrem SQL*Plus-Bildschirm
Text ausgegeben wird. Zum Verständnis ihrer Arbeitsweise sind allerdings weitere Informationen notwendig, die in Kapitel 9 gegeben werden. Das Paket DBMS_OUTPUT
und die Prozedur PUT_LINE sind Teil der Oracle-Datenbank; zusammen sorgen sie
dafür, dass auf Ihrem SQL*Plus-Bildschirm Zeile für Zeile Text ausgegeben wird.
Alle Befehle zwischen der Anweisung, die den Fehler auslöste, und dem Ende des Abschnitts werden ignoriert. Daher wird im Beispielblock die Zuweisung des Werts 7 an
die Variable Num_b nicht durchgeführt. Sie können dies kontrollieren, indem Sie sich
den Wert von Num_b anschauen, der vom Beispielcode ausgegeben wird.
Wenn eine Exception durch eine Anweisung im Ausnahmeabschnitt behandelt wird,
bezeichnen wir diese Aktion als Exception-Behandlung.
Das Erkennen eines aufgetretenen Fehlers, das Zuordnen der passendsten Exception
und das Ausführen entsprechender Schritte, um PL/SQL darüber zu informieren, welcher Teil des Ausnahmeabschnitts verwendet werden soll, bezeichnet man als das Auslösen einer Exception (raising an exception). Im Beispielcode wird die Exception von
PL/SQL selbst ausgelöst, da es einen Versuch feststellt, durch Null zu teilen. PL/SQL hat
einen vorgegebenen Namen für diese Exception – ZERO_DIVIDE. Häufig muss der
Fehler aber von Ihrem eigenen Code entdeckt werden, da PL/SQL dies nicht erkennt.
8.3
Eine einfache PL/SQL-Prozedur erstellen
Wir haben nun alle Zutaten, um zu versuchen, eine komplette PL/SQL-Prozedur zu
schreiben. Sie kennen PL/SQL-Blöcke und haben gelernt, wie die Spezifikation einer
Prozedur aussehen muss. Geben Sie einmal den folgenden Code ein.
CREATE PROCEDURE my_first_proc IS
greetings VARCHAR2(20);
BEGIN
greetings := ’Hallo Welt’;
dbms_output.put_line(greetings);
END my_first_proc;
/
Die Syntax für das Erstellen einer gespeicherten Prozedur lautet:
CREATE PROCEDURE procedure_specification IS procedure_body
In unserem Beispiel besteht die Spezifikation der Prozedur nur aus dem Namen, und
der Rumpf aus allem bis zum letzten Semikolon. Für Funktionen nutzen Sie statt des
Schlüsselworts PROCEDURE das Wort FUNCTION.
8.3 Eine einfache PL/SQL-Prozedur erstellen
293
CREATE FUNCTION function_specification IS function_body
Der Schrägstrich (/) teilt SQL*Plus mit, dass die Befehle nun abgearbeitet werden sollen. Sie können die gleiche Prozedur oder Funktion erneut erstellen, indem Sie den Befehl CREATE in CREATE OR REPLACE abwandeln. Damit wird die alte Definition
zerstört und duch die neue ersetzt. Wenn keine alte Definition vorhanden ist, wird
einfach eine neue angelegt.
CREATE OR REPLACE PROCEDURE procedure_specification IS procedure_body
Betrachten wir nun, wie diese Prozedur aus SQL*Plus heraus aufgerufen werden kann:
set serveroutput on
EXECUTE my_first_proc;
SERVEROUTPUT ON ermöglicht Ihnen, die ausgegebenen Zeilen zu betrachten. Der
Befehl EXECUTE führt dann die Prozedur aus. Sie können die Prozedur auch aus einem anonymen Block aufrufen, wie im Folgenden gezeigt wird. Vergleichen Sie Ihre
Ergebnisse mit denen in Abbildung 8-2.
BEGIN
END;
/
my_first_proc;
Abbildung 8-2: Eine einfache „Hallo Welt“-PL/SQL-Prozedur
294
8 Einführung in PL/SQL
8.3.1
Prozeduren und Funktionen aufrufen
Eine Prozedur oder Funktion kann formale Parameter mit Standardwerten enthalten
oder auch nicht. Tatsächlich muss sie gar keine formalen Parameter enthalten. Dem
jeweiligen Fall entsprechend ist die Aufrufkonvention unterschiedlich. Die folgenden
Punkte sind aber unabhängig von den Parametern gültig.
n Die Datentypen der konkreten Parameter müssen passen oder sollten wenigstens
von PL/SQL in die entsprechenden formalen Parameter konvertierbar sein.
n Konkrete Parameter müssen für alle formalen Parameter angegeben werden, die
keine Standardwerte haben.
Wenn Sie eine Funktion ohne Parameter aufrufen, können Sie den Namen der Funktion mit oder ohne Klammern angeben:
procedure_name();
oder
procedure_name;
Die gleiche Syntax wird beim Verwenden von Funktionen genutzt, nur dass innerhalb
eines Ausdrucks kein Semikolon angegeben werden darf.
Wenn eine Prozedur formale Parameter mit Standardwerten besitzt und diese sich alle
am Ende der Liste mit den formalen Parametern in der Prozedurspezifikation befinden, kann die Prozedur einfach ohne die Angabe der Werte für die letzten formalen
Parameter aufgerufen werden, für die Standardwerte existieren. Dies geht aber nur
dann, wenn für alle vorigen Parameter auch konkrete Werte mitgeliefert werden. Der
Aufruf sieht dann so aus:
procedure_name(actual_param1,
actual_param2,
...
actual_paramN);
N kann kleiner oder gleich der Anzahl der formalen Parameter dieser Prozedur sein,
muss aber größer oder gleich der Anzahl der formalen Parameter ohne Standardwerte
sein.
Wenn die formalen Parameter mit gegebenen Standardwerten nicht die letzten Parameter in der Spezifikation sind, oder wenn Sie vermeiden wollen, dass PL/SQL selbst
anhand der Reihenfolge herausfinden muss, welcher konkrete Wert welchem formalen Parameter zugeordnet werden soll, können Sie mit der folgenden Syntax PL/SQL
exakt mitteilen, welcher konkrete Parameter für welchen formalen Parameter genutzt
werden soll:
8.4 PL/SQL-Variablen und Konstanten
295
procedure_name(formal_param1 => actual_param1,
formal_param2 => actual_param2,
...
)
;
Dies wird als benannte Notation beim Aufruf von Funktionen und Prozeduren bezeichnet. Die vorige Notation dagegen heißt positionsabhängige Notation, da die Parameter über ihre Position in der Liste zugeordnet werden.
Die gleiche Art des Aufrufs ist auch für Funktionen gültig. Sie können aber in anderen
Ausdrücken auftauchen und haben dann kein Semikolon am Ende. Sie werden Beispiele für die benannte Notation im nächsten Abschnitt sehen. Es ist möglich, die beiden Notationen zu kombinieren, dann muss aber die positionsabhängige Liste im
Aufruf vor der benannten Liste angegeben werden.
8.4
PL/SQL-Variablen und Konstanten
Sie haben einige Beispiel für PL/SQL-Variablen im vorigen Abschnitt kennen gelernt.
Nun wollen wir sie uns genauer anschauen. Variablen sind prinzipiell Schachteln mit
Namensschildchen. Sie können Informationen oder Daten verschiedenen Typs enthalten. Abhängig von der Art der Daten, die sie aufbewahren können, haben sie unterschiedliche Datentypen, und um sie auseinanderhalten zu können, haben sie Namen.
Genauso, wie Öl in Flaschen und Mehl in Papiertüten aufbewahrt wird, speichert
PL/SQL Zahlen in Variablen des Datentyps NUMBER und Text in Variablen der Datentypen CHAR oder VARCHAR2. Gehen wir noch einen Schritt weiter: Stellen Sie
sich einen Kühlschrank in der Kantine Ihrer Firma vor. Er ist voller brauner Papiertüten mit Ihrem Lunch und dem Ihrer Kollegen. Wie finden Sie nun heraus, welches Ihrer ist? Genau! Ihr Lunchpaket ist beschriftet mit Ihrem Namen. Variablen haben auch
Namen, um Verwechslungen zu vermeiden. Wenn Ihr Lunch nur aus Bananen bestand, geben Sie nach ihrem Verzehr vielleicht die Schalen wieder in die braune Tüte.
Der Inhalt der Tüte hat sich also geändert. Genauso kann sich der Inhalt von Variablen
während der Ausführung von PL/SQL-Anweisungen ändern.
296
8 Einführung in PL/SQL
8.4.1
PL/SQL-Variablen deklarieren
Die Syntax für das Deklarieren einer Variable in PL/SQL lautet wie eine der beiden folgenden Zeilen:
variable_name data_type [ [NOT NULL] := default_value_expression];
variable_name data_type [ [NOT NULL] DEFAULT default_value_expression];
variable_name ist jeder gültige PL/SQL-Bezeichner. Ein gültiger PL/SQL-Bezeichner
muss folgende Bedingungen erfüllen:
n Er kann bis zu 30 Zeichen lang sein und darf keinerlei Leerzeichen oder Tabulatoren beinhalten.
n Er besteht aus Buchstaben, den Ziffern 0 bis 9, dem Unterstrich (_), Dollar ($)
oder dem Nummernzeichen (#).
n Er beginnt mit einem Buchstaben.
n Er darf kein reserviertes Wort in PL/SQL oder SQL sein, das eine spezielle Bedeutung für PL/SQL oder SQL hat. Ein Variablenname kann zum Beispiel nicht „BEGIN“ heißen. BEGIN teilt nämlich PL/SQL mit, dass hier der Anfang eines
PL/SQL-Blocks ist.
data_type ist jeder gültige SQL- oder PL/SQL-Datentyp. Im nächsten Abschnitt erhalten Sie weitere Informationen über Datentypen.
Die Verwendung von NOT NULL erfordert, dass die Variable immer einen Wert hat.
Wenn dies angegeben wurde, muss auch zwingend ein Standardwert mit angegeben
werden.
Wenn eine Variable angelegt wird, kann sie gleich mit einem Standardwert ausgestattet werden. Dies ist eine einfache Möglichkeit, Variablen Werte zuzuweisen.
Sie kennen bereits SQL-Datentypen – NUMBER, VARCHAR2 und DATE. PL/SQL
verfügt wie SQL über diese Datentypen, hat darüber hinaus aber noch weitere Datentypen, die SQL nicht bietet. Eine komplette Liste finden Sie in den PL/SQL-Referenzen
von Oracle.
8.4.2
PL/SQL-Konstanten deklarieren
Die Syntax für das Deklarieren einer Konstante lautet:
variable_name data_type CONSTANT := constant_value_expression;
Anders als Variablen muss Konstanten ein Wert mitgegeben werden, der während der
Lebenszeit der Konstanten auch nicht geändert werden kann. Konstanten sind sehr
nützlich, wenn Sie die Entwicklung großer und komplexer Applikationen sicherer und
8.4 PL/SQL-Variablen und Konstanten
297
„disziplinierter“ machen wollen. Wenn Sie zum Beispiel sicherstellen wollen, dass die
Daten, die einer PL/SQL-Prozedur mitgeteilt werden, sich von der Prozedur nicht ändern lassen, können Sie diese Daten zu Konstanten machen. Wenn die Prozedur dann
versucht, sie zu ändern, löst PL/SQL eine Exception aus.
8.4.3
Variablen Werte zuweisen
Es gibt drei verschiedene Wege, den Wert einer Variable zu ändern. Das Zuweisen eines gültigen Ausdrucks mit dem Zuweisungsoperator von PL/SQL ist der erste Weg.
Sie haben schon einige Beispiele dafür gesehen. Die Syntax lautet:
variable_name := expression;
Als zweite Möglichkeit kann eine Variable als konkreter Parameter für formale IN
OUT- oder OUT-Parameter an eine PL/SQL-Prozedur übergeben werden. Nachdem
die Prozedur abgearbeitet wurde, kann sich der Wert der Variablen geändert haben.
Das folgende Beispiel zeigt die benannte Notation beim Aufruf einer Prozedur. Schauen Sie sich für die Ausgabe Abbildung 8-3 auf Seite 298 an.
CREATE
IS
BEGIN
PROCEDURE hike_prices (old_price NUMBER,
percent_hike NUMBER := 5,
new_price OUT NUMBER)
new_price := old_price + old_price * percent_hike / 100;
END hike_prices;
/
Die folgende Prozedur zeigt, wie die Variablen ihren Wert ändern:
DECLARE
BEGIN
price_to_hike NUMBER(6,2) := 20;
hiked_price NUMBER(6,2) := 0;
dbms_output.put_line(’Preis vor hike_prices ’ || price_to_hike);
dbms_output.put_line(’hiked_price vor hike_prices ’ ||
hiked_price);
hike_prices (old_price => price_to_hike,
new_price => hiked_price);
dbms_output.put_line(’Preis nach hike_prices ’ || price_to_hike);
dbms_output.put_line(’hiked_price nach hike_prices ’ ||
hiked_price);
END;
/
298
8 Einführung in PL/SQL
Abbildung 8-3: PL/SQL-Variablen Werte zuweisen, indem sie als konkrete Parameter genutzt
werden
Der dritte Weg, um Variablen Werte zuzuweisen oder sie zu ändern, wird detaillierter
im nächsten Kapitel besprochen. Hier nur ein kleines Beispiel, zu dem Abbildung 8-4
auf Seite 299 das Ergebnis zeigt.
DECLARE
product_quant
NUMBER;
BEGIN
SELECT quantity_on_hand
INTO
product_quant
FROM
plsql101_product
WHERE
product_name = ’Small Widget’;
dbms_output.put_line (’Small Widget ’ || product_quant);
END;
/
product_quant wird der Wert zugewiesen, der der Anzahl der „Small Widgets“ entspricht.
8.5 Kontrollstrukturen in PL/SQL
299
Abbildung 8-4: Einer PL/SQL-Variablen einen Wert durch SQL zuweisen
8.4.4
Variablen verwenden
Variablen sind die grundlegenden Elemente von PL/SQL-Programmen. Sie werden
verwendet, um die Ergebnisse von Berechnungen zu speichern, um Werte aus Funktionsaufrufen zurückzuliefern, und als konkrete Parameter für Funktionen und Prozeduren und so weiter. Variablen sollen Ihre Applikation ordentlicher und lesbarer
machen, damit den Wartungsaufwand reduzieren und ein effizienteres Programm
schaffen.
Angenommen, Sie wollen einige Berechnungen mit der aktuellen Anzahl der „Small
Widgets“ durchführen – sie mit der Anzahl von vor drei Monaten oder der Anzahl der
„Medium Widgets“ vergleichen. Wenn Sie eine Variable zum Speichern des Werts
nutzen, vermeiden Sie die Verzögerungen, die entstehen, wenn Sie den Wert wieder
und wieder aus der Tabelle auslesen.
Wenn Sie Variablen sinnvoll benennen, wird Ihr Code leichter lesbar und verständlicher. Dieselben Prinzipien gelten auch, wenn Sie Variablen zum Speichern von Ergebnissen aus komplexen Berechnungen verwenden, anstatt die Berechnung im Code an
mehreren Stellen direkt auszuführen.
8.5
Kontrollstrukturen in PL/SQL
Häufig wollen Sie etwas tun, wenn eine Bedingung wahr ist, und etwas anderes, wenn
sie falsch ist. Wenn zum Beispiel eine Bestellung über einem gewissen Betrag liegt, sollen vielleicht fünf Prozent abgezogen werden, und sogar zehn Prozent, wenn sie über
einem anderen Betrag liegt. Diese Art von Logik mag innerhalb Ihrer Applikation erforderlich sein, wenn die abschließende Rechnung für Ihre Kunden ausgedruckt wird.