Einführung in PL/SQL -...

888Einführung in PL/SQL

Das Speichern und Auslesen von Informationen ist nur ein Teil jeder „echten“ Appli-kation. Auch die einfachsten Programme müssen Aktionen durchführen, die sich mitSQL gar nicht oder nur sehr schwer umsetzen ließen. Denken Sie nur daran, wie auf-wändig die Berechnungen sind, die jedes Jahr vom Finanzamt vorgenommen werdenmüssen, um die korrekten Steuern zu berechnen! OK, vielleicht möchten Sie sich lie-ber 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 er-möglichen, aufwändigere Berechnungen mit den Daten durchzuführen. Das ist zumTeil historisch bedingt: SQL entstand als Datenbank-Abfragesprache (StructuredQuery Language) und wurde weiterentwickelt und optimiert für genau diesen Zweck:Datenbanken abzufragen. Verschiedene Datenbank-Hersteller haben sich auf be-stimmte SQL-Standards geeinigt, nicht aber darauf, wie den Benutzern weitergehendeSQL-orientierte Programmierumgebungen zur Verfügung gestellt werden sollten. Da-her präsentiert jeder Datenbank-Hersteller seine proprietäre oder semi-proprietäreLösung. Oracle bezeichnet seine Lösung als PL/SQL. Dies steht für „ProgrammingLanguage for SQL“.

In diesem Kapitel werden Sie in die Grundlagen von PL/SQL eingeführt. Sie werdenden Unterschied zwischen SQL, SQL*Plus und PL/SQL kennen lernen. Weiterhinwerden Sie erste einfache PL/SQL-Prozeduren schreiben sowie Funktionen, diegrundlegende PL/SQL-Konstrukte wie Variablen, Schleifen und Cursor verwenden.Dann werden Sie den wichtigen Bereich der Fehlerbehandlung kennen lernen, damitdem Benutzer verständliche Mitteilungen gemacht werden können.

Wenn Sie erst in diesem Kapitel mit dem Lesen des Buchs begonnen und noch keineder Ü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 Kapitelnachvollziehen können. Dazu dienen die folgenden SQL-Befehle:

DROP TABLE plsql101_purchase;DROP TABLE plsql101_product;DROP TABLE plsql101_person;DROP TABLE plsql101_old_item;DROP TABLE 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_indexON plsql101_person(last_name, first_name);

ALTER TABLE plsql101_personADD 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 NULLAND

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_purchaseADD PRIMARY KEY (product_name,

salesperson,purchase_date)

;

ALTER TABLE plsql101_purchase ADD CONSTRAINT reasonable_date CHECK(purchase_date IS NOT NULLANDTO_CHAR(purchase_date, ’YYYY-MM-DD’) >= ’2000-06-30’)

;


ALTER TABLE plsql101_purchaseADD CONSTRAINT plsql101_purchase_fk_product FOREIGN KEY(product_name) REFERENCES plsql101_product;

ALTER TABLE plsql101_purchaseADD CONSTRAINT plsql101_purchase_fk_person FOREIGN KEY(salesperson) REFERENCES plsql101_person;

CREATE INDEX plsql101_purchase_productON plsql101_purchase(product_name);

CREATE INDEX plsql101_purchase_salespersonON 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_productSET product_price = product_price * .9WHERE product_name NOT IN (

SELECT DISTINCT product_nameFROM 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’);


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 Artund Weise zu verarbeiten. Jede Nacht werden Sie die Zusammenfassung der täglichenGeschäftsvorgänge in eine Übersichtstabelle transferieren wollen – PL/SQL-Paketekönnen Ihnen dabei helfen. Möchten Sie wissen, ob Sie zusätzliche Lieferungen anfor-dern müssen, um große Bestellungen bedienen zu können – PL/SQL bietet Trigger, dieSie benachrichtigen, sobald eine Bestellung eingegeben wird, die bestimmte, vorgege-bene Grenzen überschreitet. Sie können gespeicherte PL/SQL-Prozeduren nutzen, umdie Leistung Ihrer Mitarbeiter zu berechnen und den Bonus festzulegen. Eine nettekleine PL/SQL-Funktion kann die Steuersätze für einen Angestellten ermitteln.

PL/SQL stellt Ihnen alle Datenmanipulationsmöglichkeiten, Cursor-Kontrollen undTransaktionskontrollbefehle von SQL zur Verfügung, wie auch sämtliche SQL-Funk-tionen und -Operatoren. Damit behalten Sie bei der Manipulation von Daten in Ora-


cle die volle Flexibilität und Kontrolle. Außerdem unterstützt PL/SQL alle SQL-Datentypen. Dadurch verringert sich die Notwendigkeit, Daten zu konvertieren, diezwischen Ihrer Applikation und der Datenbank ausgetauscht werden. PL/SQL stelltauch „Dynamic SQL“ zur Verfügung, eine fortgeschrittene Programmiertechnik, mitder Ihre Applikationen flexibler und anpassbarer werden. Ihre Programme könnenSQL-Befehle zur Datendefinition, Datenkontrolle und Transaktionskontrolle „on thefly“ zur Laufzeit erstellen und ausführen lassen.

Bevor wir mehr über einige dieser Fähigkeiten erfahren, werde ich Ihnen erläutern, wiePL/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 ei-nem gut ausgebildeten Ober erwartet. Sie schauen sich die Speisekarte an und gebeneine Bestellung auf. Der Ober notiert Ihre Bestellung und bringt sie in die Küche. DieKü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 Per-sonen mit unterschiedlichen Aufgaben: Sie holen die Vorräte aus dem Lager, bereitenbestimmte Gänge vor (zum Beispiel nur Suppen oder Salate) und so weiter. IhrerMenü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 Me-nü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 „PizzaHawaii“ mit – während andere Bestellungen individuell zusammengestellt sind undeine genaue Liste der Zutaten erfordern.

Nun ändern wir das Szenario ein bisschen. Denken Sie an eine Oracle-Datenbank alsKüche des Restaurants, in der SQL*Plus als Ober unsere Bestellungen – Skripte, Befeh-le oder Programme – an die Küche oder Datenbank weiterleitet. Innerhalb der Küchegibt es zwei Köche: SQL und PL/SQL. Wie ein Ober weiß SQL*Plus, welche Bestellun-gen es selbst abwickeln kann und welche es an wen weiterleiten muss. Genau wie einOber Ihnen ein Glas Wasser bringen kann, ohne den Chefkoch zu belästigen, kannSQL*Plus die Zeilenlänge auf Ihrem Bildschirm anpassen, ohne auf die Datenbank zu-greifen 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 Bestellungenmüssen die Köche jedes Mal ein bisschen nachdenken. Wie auch der Koch ein Rezeptfü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-Elementewerden Trigger, gespeicherte Funktionen und Pakete genannt. Sie werden bald mehrüber sie lernen.


Wie ich schon weiter oben erwähnte, benötigen einige Bestellungen mehr als einenKoch, um sie zu bearbeiten. Die meisten der interessanteren und nützlicheren Daten-bank-Applikationen, die Sie erstellen, werden SQL und PL/SQL zusammen einsetzen,wobei Informationen zwischen beiden Bereichen ausgetauscht werden, um ein Skriptoder ein Programm auszuführen. In einem Restaurant wird die Bestellung nach derZubereitung von einem Ober an Ihren Tisch gebracht. In ähnlicher Weise werden dieErgebnisse von SQL- und PL/SQL-Programmen an SQL*Plus (oder ein eigenes Front-end) zurückgeliefert, um sie dem Benutzer anzuzeigen.

8.1.3 Gespeicherte Prozeduren, Funktionenund Trigger

PL/SQL-Prozeduren, Funktionen und Trigger helfen Ihnen dabei, komplexe Ge-schäftslogiken einfach und in modularer Form (das heißt, Stück für Stück, wobei dieeinzelnen Stücke von anderen Elementen wiederverwendet werden können) zu erstel-len. Beim Abspeichern auf dem Oracle-Server bietet das zwei direkte Vorteile: Sie kön-nen immer wieder mit vorhersagbaren Ergebnissen genutzt werden und sie lassen sichdeutlich schneller ausführen, da Server-Operationen wenig oder gar keinen Netzwerk-verkehr erzeugen.

Gespeicherte ProzedurenEine gespeicherte Prozedur ist ein festgelegtes Set von Aktionen, die mit der Program-miersprache PL/SQL geschrieben wurden. Wenn eine Prozedur aufgerufen wird, führtsie die enthaltenen Aktionen aus. Die Prozedur ist in der Datenbank abgespeichert, da-her 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, Funkti-onen oder Triggern aufgerufen werden. Eine gespeicherte Prozedur kann auch direktvom SQL*Plus-Prompt aus gestartet werden. Wenn Sie die nächsten Seiten lesen, wer-den Sie lernen, wie jede dieser Methoden zum Aufruf einer gespeicherten Prozedurverwendet wird.

Eine Prozedur besteht aus zwei Teilen: der Spezifikation und dem Rumpf. Die Proze-dur-Spezifikation enthält den Namen der Prozedur und eine Beschreibung der Ein-und Ausgabeparameter. Die Ein- und Ausgabeparameter werden formale Parameteroder formale Argumente der Prozedur genannt. Wenn ein Aufruf einer Prozedur Kom-mandozeilen-Parameter oder andere Eingaben mit einschließt, werden diese Wertekonkrete Parameter oder konkrete Argumente genannt.

Betrachten wir nun ein paar Beispiele für Prozedur-Spezifikationen. (Denken Sie dar-an, dass die Spezifikation keinerlei Code enthält; sie gibt der Prozedur nur einen Na-men und legt die Ein- und Ausgabeparameter fest, die die Prozedur verwenden kann.)


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. In-nerhalb der Prozedur wird der Wert als PERCENT_INCREASE angesprochen. Beach-ten 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 ei-nem Parameternamen zeigt an, dass die Prozedur einen Eingabewert aus den Para-metern nutzen kann, wenn sie aufgerufen wird. Das Wort „OUT“ nach einemParameternamen zeigt an, dass die Prozedur diesen Parameter nutzen kann, umWerte 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ückgabewertdienen.

Dem Parameter INCREASE_PERCENT wird in diesem Beispiel ein Standardwert von7 zugewiesen, indem „:= 7“ nach dem Datentyp angefügt wird. Wenn nun die Proze-dur ohne die Angabe einer prozentualen Erhöhung aufgerufen wird, wird sie die Ge-hälter um sieben Prozent erhöhen und die Steuern basierend auf den neuen Wertenberechnen.

Hinweis:Datentypen in einer Prozedur können keine Größenangabenenthalten. Sie können zum Beispiel festlegen, dass ein Parametervom 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 Ab-schnitt dieses Kapitels einiges lernen werden.

Gespeicherte FunktionenEine PL/SQL-Funktion ist einer PL/SQL-Prozedur sehr ähnlich: Sie besitz eine Funk-tionsspezifikation und einen Funktionsrumpf. Der entscheidende Unterschied zwi-schen 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.


Stellen Sie sich zum Beispiel eine Funktion vor, die zum Berechnen der prozentualenDifferenz zwischen zwei Zahlen gedacht ist. Ohne den Code, der die Berechnungdurchfü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_1und 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));

TriggerEin Trigger ist eine PL/SQL-Prozedur, die automatisch ausgeführt wird, sobald ein be-stimmtes, vom Trigger definiertes Ereignis – das auslösende Ereignis – eintritt. Sie kön-nen Trigger schreiben, die ausgelöst werden, sobald ein INSERT-, UPDATE- oderDELETE-Befehl auf eine Tabelle ausgeführt wird; wenn DDL-Befehle genutzt werden;wenn sich ein Benutzer an- oder abmeldet, oder wenn die Datenbank startet, einenFehler 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 vonOracle 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 Pro-zedur.

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 ge-speicherte Prozeduren innerhalb Ihrer Oracle-Datenbank abgelegt. Ein SQL-Skriptenthält eine Reihe von SQL-Befehlen, die nacheinander ausgeführt werden, wenn Siedas Skript aufrufen. Eine gespeicherte Prozedur dagegen kann Befehle zur Ablaufkon-trolle enthalten, die es ihr ermöglichen, einen bestimmten Teil des Codes immer wie-der abzuarbeiten, zu einem anderen Code-Abschnitt zu verzweigen, wenn bestimmteSituationen auftreten, und auf Fehler in der von Ihnen vorgegebenen Art und Weisezu reagieren.


8.2 Struktur eines PL/SQL-BlocksIn 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-Blockvertraut gemacht haben, werden Sie im nächsten Abschnitt Beispiele für kompletteProzeduren, Funktionen und Trigger gezeigt bekommen.

Ein PL/SQL-Block besteht aus vier Abschnitten: dem Kopf, einem optionalen Deklara-tionsabschnitt, dem Ausführungsabschnitt und einem optionalen Ausnahmeabschnitt.

Ein anonymer Block ist ein PL/SQL-Block ohne Kopf oder Namensabschnitt, daherauch der Begriff „anonymer Block“. Anonyme Blöcke können unter SQL*Plus ausge-führt werden und in PL/SQL-Funktionen, Prozeduren und Triggern Verwendung fin-den. Denken Sie daran, dass PL/SQL-Prozeduren, Funktionen und Trigger selbst alleaus PL/SQL-Blöcken bestehen. Das bedeutet, dass Sie einen PL/SQL-Block innerhalbeines anderen PL/SQL-Blocks haben können. Sie werden mehr darüber weiter untenin diesem Abschnitt lernen.

Vielleicht ist der beste Weg, einen PL/SQL-Block zu verstehen, ein Beispiel auszufüh-ren. Geben Sie zunächst den folgenden Befehl ein, damit Informationen von Program-men in SQL*Plus ausgegeben werden.

set serveroutput on

Nun geben Sie den folgenden Code ein, um einen anonymen Block zu erstellen. Ver-gleichen Sie Ihre Ergebnisse mit Abbildung 8-1.

DECLAREnum_a NUMBER := 6;num_b NUMBER;

BEGINnum_b := 0;num_a := num_a / num_b;num_b := 7;dbms_output.put_line(’ Wert von num_b ’ || num_b);

EXCEPTIONWHEN ZERO_DIVIDE

THENdbms_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. Er-innern Sie sich daran, dass Prozeduren, Funktionen, Trigger und anonyme Blöcke ausPL/SQL-Blöcken bestehen. Tatsächlich hat jeder von ihnen einen PL/SQL-Block, derden Rumpf darstellt. Dieser Rumpf-Block kann nun wiederum mehrere PL/SQL-Blö-cke enthalten. Der Kopf dieses obersten Blocks einer Funktion, Prozedur oder einesTriggers ist die Spezifikation für diese Funktion, Prozedur oder diesen Trigger. Bei an-onymen Blöcken enthält der Kopf nur das Schlüsselwort DECLARE. Bezeichnete Blö-cke enthalten im Kopf den Namen, der durch << und >> umschlossen wird, gefolgtvom Schlüsselwort DECLARE, wie im Folgenden gezeigt:

<<just_a_label>>DECLARE

Blockbezeichner erhöhen die Lesbarkeit des Codes. In einer Prozedur, die verschach-telte Blöcke verwendet (Blöcke innerhalb anderer Blöcke) können Sie sich auf ein Ele-ment in einem bestimmten Block beziehen, indem Sie vor den Namen des Elementsden Namen des Blocks setzen (zum Beispiel block_label.item_label).


8.2.2 Deklarationsabschnitt

Der Deklarationsabschnitt ist optional. Wenn er genutzt wird, beginnt er direkt nachdem Kopf und endet mit dem Schlüsselwort BEGIN. Der Deklarationsabschnitt ent-hält die Deklarationen für PL/SQL-Variablen, Konstanten, Cursor, Exceptions, Funk-tionen und Prozeduren, die von den Ausführungs- und Ausnahmeabschnittenverwendet werden. Alle Deklarationen von Variablen und Konstanten müssen ange-geben werden, bevor die erste Funktions- oder Prozedurdeklaration aufgeführt ist. Siewerden mehr über PL/SQL-Variablen und Konstanten im folgenden Abschnitt lernen.Eine Deklaration teilt PL/SQL mit, dass es eine Variable, Konstante, Funktion, Proze-dur 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/SQLdazu auf, zwei Variablen vom Typ NUMBER anzulegen, die die Namen Num_a undNum_b erhalten sollen. Er weist der Variablen Num_a zudem noch den Standardwert6 zu.

Wenn ein PL/SQL-Block abgearbeitet wurde, hören alle Deklarationen aus dessen De-klarationsabschnitt auf zu existieren. Elemente aus dem Deklarationsabschnitt einesBlocks können nur innerhalb des gleichen Blocks verwendet werden. Daher gibt esnach dem Durchlauf des Beispiel-Blocks in SQL*Plus auch keinerlei Möglichkeitmehr, Num_a an eine andere PL/SQL-Prozedur weiterzugeben. Num_a und Num_bverschwinden direkt nach der Beendigung des Blocks aus dem Blickfeld. Wenn Sie al-lerdings eine PL/SQL-Funktion oder -Prozedur innerhalb des Ausführungs- oder Aus-nahmeabschnitts des Blocks aufrufen, können Sie ihnen Num_a oder Num_b alskonkrete Parameter mitgeben.

Langer Rede kurzer Sinn: Alles, was im Deklarationsabschnitt angelegt wird, ist dasPrivateigentum des Blocks – es kann nur durch ihn genutzt werden und ist auch nurfür ihn sichtbar. Alles aus dem Deklarationsabschnitt des Blocks existiert nur solangewie der Block selbst. Technisch gesprochen: Num_a und Num_b besitzen den Gel-tungsbereich des Blocks, in dem sie deklariert wurden. Der Geltungsbereich des Blocksbeginnt mit dem Anfang des Deklarationsabschnitts und endet mit dem Ende seinesAusnahmeabschnitts.

8.2.3 Ausführungsabschnitt

Der Ausführungsabschnitt beginnt mit dem Schlüsselwort BEGIN und endet auf zweiverschiedene Arten. Wenn es einen Ausnahmeabschnitt gibt, endet der Ausführungs-abschnitt mit dem Schlüsselwort EXCEPTION. Wenn es keinen Ausnahmeabschnittgibt, endet er mit dem Schlüsselwort END, optional gefolgt vom Namen der Funktionoder Prozedur, und einem Semikolon. Der Ausführungsabschnitt enthält einen odermehrere PL/SQL-Befehle, die ausgeführt werden, wenn der Block abläuft. Die Struk-tur 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-Zuweisungsbefeh-le. 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 einemGleichheitszeichen, ist der Zuweisungsoperator. Er teilt PL/SQL mit, alles rechts vonihm 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. Beach-ten Sie, dass sich nach erfolgreicher Ausführung dieses Befehls der Wert von Num_ageä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üh-rungsabschnitt ein Fehler auftaucht, der es unmöglich macht, mit der Ausführungfortzufahren. Diese Fehlerbedingungen werden Exceptions (oder Ausnahmen) ge-nannt. Der Benutzer der Prozedur sollte über das Auftreten und den Grund einer Ex-ception informiert werden. Vielleicht wollen Sie dem Benutzer eine sinnvolleFehlermeldung präsentieren, oder korrigierend eingreifen und versuchen, die Proze-dur an der entsprechenden Stelle fortzuführen. Eventuell möchten Sie ja auch alle Än-derungen 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. Ex-ceptions sind für gut geschriebene Applikationen so wichtig, dass ich am Ende des Ka-pitels einen speziellen Abschnitt über sie angefügt habe, in dem Sie mehr darübererfahren können. Als Einführung sei hier die Struktur des Ausnahmeabschnitts aufge-fü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


Der Ausnahmeabschnitt beginnt mit dem Schlüsselwort EXCEPTION und endet amEnde 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ältdrei lustig aussehende Zeilen, die dafür sorgen, dass auf Ihrem SQL*Plus-BildschirmText ausgegeben wird. Zum Verständnis ihrer Arbeitsweise sind allerdings weitere In-formationen notwendig, die in Kapitel 9 gegeben werden. Das Paket DBMS_OUTPUTund die Prozedur PUT_LINE sind Teil der Oracle-Datenbank; zusammen sorgen siedafü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 Ab-schnitts werden ignoriert. Daher wird im Beispielblock die Zuweisung des Werts 7 andie Variable Num_b nicht durchgeführt. Sie können dies kontrollieren, indem Sie sichden 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 Exceptionund das Ausführen entsprechender Schritte, um PL/SQL darüber zu informieren, wel-cher Teil des Ausnahmeabschnitts verwendet werden soll, bezeichnet man als das Aus-lösen einer Exception (raising an exception). Im Beispielcode wird die Exception vonPL/SQL selbst ausgelöst, da es einen Versuch feststellt, durch Null zu teilen. PL/SQL hateinen vorgegebenen Namen für diese Exception – ZERO_DIVIDE. Häufig muss derFehler aber von Ihrem eigenen Code entdeckt werden, da PL/SQL dies nicht erkennt.

8.3 Eine einfache PL/SQL-Prozedur erstellenWir haben nun alle Zutaten, um zu versuchen, eine komplette PL/SQL-Prozedur zuschreiben. Sie kennen PL/SQL-Blöcke und haben gelernt, wie die Spezifikation einerProzedur aussehen muss. Geben Sie einmal den folgenden Code ein.

CREATE PROCEDURE my_first_proc ISgreetings VARCHAR2(20);

BEGINgreetings := ’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, undder Rumpf aus allem bis zum letzten Semikolon. Für Funktionen nutzen Sie statt desSchlü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 sol-len. Sie können die gleiche Prozedur oder Funktion erneut erstellen, indem Sie den Be-fehl CREATE in CREATE OR REPLACE abwandeln. Damit wird die alte Definitionzerstört und duch die neue ersetzt. Wenn keine alte Definition vorhanden ist, wirdeinfach 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 onEXECUTE my_first_proc;

SERVEROUTPUT ON ermöglicht Ihnen, die ausgegebenen Zeilen zu betrachten. DerBefehl EXECUTE führt dann die Prozedur aus. Sie können die Prozedur auch aus ei-nem anonymen Block aufrufen, wie im Folgenden gezeigt wird. Vergleichen Sie IhreErgebnisse mit denen in Abbildung 8-2.

BEGINmy_first_proc;

END;/

Abbildung 8-2: Eine einfache „Hallo Welt“-PL/SQL-Prozedur


8.3.1 Prozeduren und Funktionen aufrufen

Eine Prozedur oder Funktion kann formale Parameter mit Standardwerten enthaltenoder auch nicht. Tatsächlich muss sie gar keine formalen Parameter enthalten. Demjeweiligen Fall entsprechend ist die Aufrufkonvention unterschiedlich. Die folgendenPunkte sind aber unabhängig von den Parametern gültig.

n Die Datentypen der konkreten Parameter müssen passen oder sollten wenigstensvon PL/SQL in die entsprechenden formalen Parameter konvertierbar sein.

n Konkrete Parameter müssen für alle formalen Parameter angegeben werden, diekeine Standardwerte haben.

Wenn Sie eine Funktion ohne Parameter aufrufen, können Sie den Namen der Funk-tion mit oder ohne Klammern angeben:

procedure_name();

oder

procedure_name;

Die gleiche Syntax wird beim Verwenden von Funktionen genutzt, nur dass innerhalbeines Ausdrucks kein Semikolon angegeben werden darf.

Wenn eine Prozedur formale Parameter mit Standardwerten besitzt und diese sich alleam Ende der Liste mit den formalen Parametern in der Prozedurspezifikation befin-den, kann die Prozedur einfach ohne die Angabe der Werte für die letzten formalenParameter aufgerufen werden, für die Standardwerte existieren. Dies geht aber nurdann, wenn für alle vorigen Parameter auch konkrete Werte mitgeliefert werden. DerAufruf 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 Standardwertesein.

Wenn die formalen Parameter mit gegebenen Standardwerten nicht die letzten Para-meter in der Spezifikation sind, oder wenn Sie vermeiden wollen, dass PL/SQL selbstanhand der Reihenfolge herausfinden muss, welcher konkrete Wert welchem forma-len Parameter zugeordnet werden soll, können Sie mit der folgenden Syntax PL/SQLexakt mitteilen, welcher konkrete Parameter für welchen formalen Parameter genutztwerden 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 be-zeichnet. Die vorige Notation dagegen heißt positionsabhängige Notation, da die Para-meter ü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 anderenAusdrücken auftauchen und haben dann kein Semikolon am Ende. Sie werden Bei-spiele für die benannte Notation im nächsten Abschnitt sehen. Es ist möglich, die bei-den Notationen zu kombinieren, dann muss aber die positionsabhängige Liste imAufruf vor der benannten Liste angegeben werden.

8.4 PL/SQL-Variablen und KonstantenSie haben einige Beispiel für PL/SQL-Variablen im vorigen Abschnitt kennen gelernt.Nun wollen wir sie uns genauer anschauen. Variablen sind prinzipiell Schachteln mitNamensschildchen. Sie können Informationen oder Daten verschiedenen Typs ent-halten. Abhängig von der Art der Daten, die sie aufbewahren können, haben sie unter-schiedliche Datentypen, und um sie auseinanderhalten zu können, haben sie Namen.Genauso, wie Öl in Flaschen und Mehl in Papiertüten aufbewahrt wird, speichertPL/SQL Zahlen in Variablen des Datentyps NUMBER und Text in Variablen der Da-tentypen CHAR oder VARCHAR2. Gehen wir noch einen Schritt weiter: Stellen Siesich 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 Ih-rer ist? Genau! Ihr Lunchpaket ist beschriftet mit Ihrem Namen. Variablen haben auchNamen, um Verwechslungen zu vermeiden. Wenn Ihr Lunch nur aus Bananen be-stand, 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 Variablenwährend der Ausführung von PL/SQL-Anweisungen ändern.


8.4.1 PL/SQL-Variablen deklarieren

Die Syntax für das Deklarieren einer Variable in PL/SQL lautet wie eine der beiden fol-genden 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-Bezeichnermuss folgende Bedingungen erfüllen:

n Er kann bis zu 30 Zeichen lang sein und darf keinerlei Leerzeichen oder Tabulato-ren 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 Bedeu-tung für PL/SQL oder SQL hat. Ein Variablenname kann zum Beispiel nicht „BE-GIN“ heißen. BEGIN teilt nämlich PL/SQL mit, dass hier der Anfang einesPL/SQL-Blocks ist.

data_type ist jeder gültige SQL- oder PL/SQL-Datentyp. Im nächsten Abschnitt erhal-ten 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 angegebenwerden.

Wenn eine Variable angelegt wird, kann sie gleich mit einem Standardwert ausgestat-tet werden. Dies ist eine einfache Möglichkeit, Variablen Werte zuzuweisen.

Sie kennen bereits SQL-Datentypen – NUMBER, VARCHAR2 und DATE. PL/SQLverfügt wie SQL über diese Datentypen, hat darüber hinaus aber noch weitere Daten-typen, die SQL nicht bietet. Eine komplette Liste finden Sie in den PL/SQL-Referenzenvon 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 derLebenszeit der Konstanten auch nicht geändert werden kann. Konstanten sind sehrnü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 dieDaten, die einer PL/SQL-Prozedur mitgeteilt werden, sich von der Prozedur nicht än-dern lassen, können Sie diese Daten zu Konstanten machen. Wenn die Prozedur dannversucht, 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 ei-nes 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 INOUT- oder OUT-Parameter an eine PL/SQL-Prozedur übergeben werden. Nachdemdie Prozedur abgearbeitet wurde, kann sich der Wert der Variablen geändert haben.Das folgende Beispiel zeigt die benannte Notation beim Aufruf einer Prozedur. Schau-en Sie sich für die Ausgabe Abbildung 8-3 auf Seite 298 an.

CREATE PROCEDURE hike_prices (old_price NUMBER,percent_hike NUMBER := 5,new_price OUT NUMBER)

ISBEGIN

new_price := old_price + old_price * percent_hike / 100;END hike_prices;/

Die folgende Prozedur zeigt, wie die Variablen ihren Wert ändern:

DECLAREprice_to_hike NUMBER(6,2) := 20;hiked_price NUMBER(6,2) := 0;

BEGINdbms_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;/


Abbildung 8-3: PL/SQL-Variablen Werte zuweisen, indem sie als konkrete Parameter genutztwerden

Der dritte Weg, um Variablen Werte zuzuweisen oder sie zu ändern, wird detaillierterim nächsten Kapitel besprochen. Hier nur ein kleines Beispiel, zu dem Abbildung 8-4auf Seite 299 das Ergebnis zeigt.

DECLAREproduct_quant NUMBER;BEGIN

SELECT quantity_on_handINTO product_quantFROM plsql101_productWHERE 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“ ent-spricht.

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 werdenverwendet, um die Ergebnisse von Berechnungen zu speichern, um Werte aus Funk-tionsaufrufen zurückzuliefern, und als konkrete Parameter für Funktionen und Pro-zeduren und so weiter. Variablen sollen Ihre Applikation ordentlicher und lesbarermachen, damit den Wartungsaufwand reduzieren und ein effizienteres Programmschaffen.

Angenommen, Sie wollen einige Berechnungen mit der aktuellen Anzahl der „SmallWidgets“ durchführen – sie mit der Anzahl von vor drei Monaten oder der Anzahl der„Medium Widgets“ vergleichen. Wenn Sie eine Variable zum Speichern des Wertsnutzen, vermeiden Sie die Verzögerungen, die entstehen, wenn Sie den Wert wiederund wieder aus der Tabelle auslesen.

Wenn Sie Variablen sinnvoll benennen, wird Ihr Code leichter lesbar und verständli-cher. Dieselben Prinzipien gelten auch, wenn Sie Variablen zum Speichern von Ergeb-nissen aus komplexen Berechnungen verwenden, anstatt die Berechnung im Code anmehreren Stellen direkt auszuführen.

8.5 Kontrollstrukturen in PL/SQLHäufig wollen Sie etwas tun, wenn eine Bedingung wahr ist, und etwas anderes, wennsie falsch ist. Wenn zum Beispiel eine Bestellung über einem gewissen Betrag liegt, sol-len vielleicht fünf Prozent abgezogen werden, und sogar zehn Prozent, wenn sie übereinem anderen Betrag liegt. Diese Art von Logik mag innerhalb Ihrer Applikation er-forderlich sein, wenn die abschließende Rechnung für Ihre Kunden ausgedruckt wird.

Einführung in PL/SQL -...

Documents

Transcript of Einführung in PL/SQL -...