ORACLE und PL/SQL

1
PL/SQL Einführung
1.1
1.2
1.3
1.4
1.5
1.6
1.7
1.8
1.9
Einleitung........................................................................................ 1-4
Funktionsweise von PL/SQL .......................................................... 1-6
Initialisierungsparameter in ORACLE 10g...................................... 1-8
PL/SQL-Blockstruktur ................................................................... 1-14
Arten von PL/SQL-Programmkonstrukten .................................... 1-16
Wichtige Rechte in PL/SQL.......................................................... 1-18
Programmierstil ............................................................................ 1-20
Schachtelung von PL/SQL-Blöcken ............................................. 1-20
Labels........................................................................................... 1-22
1.2.066 / 4053
1-1
1
1
PL/SQL Einführung
PL/SQL Einführung
Um auf eine ORACLE Datenbank zuzugreifen, wird ausschließlich die
Sprache SQL benutzt. Ein SQL-Befehl wird vom ORACLE-Server geparst, optimiert und ausgeführt. Je nach Ergebnis der SQL-Operation
kann dann innerhalb des Hauptprogrammes (3GL-Programm oder
4GL-Programm) reagiert werden, und mit Hilfe der zur Verfügung stehenden Kontrollkonstrukte können die weiteren Aktionen innerhalb des
Programms veranlaßt werden. SQL hat selbst keine Kontrollkonstrukte.
Jedoch mit der Einführung der Version 6 des ORACLE Datenbanksystems wurde die Sprache PL/SQL (Procedural Language / Structured
Query Language) als prozedurale Erweiterung von SQL eingeführt.
Dieses hat zwei entscheidende Vorteile:
1. Komplexe, datenbankorientierte Operationen werden vollständig innerhalb des Datenbankservers durchgeführt. Es wird ein ganzer
PL/SQL-Block auf einmal zum Datenbankserver geschickt und dort
verarbeitet. Nach kompletter Abarbeitung wird die entsprechende
Rückmeldung gegeben. Anders als bei der Abarbeitung von SQLStatements wird das Netzwerk nur durch eine Anfrage und eine entsprechende Rückmeldung belastet.
2. PL/SQL sollte innerhalb aller Oracle-Programme (PRO-C, PROCOBOL, Forms,...) und innerhalb des Datenbankservers (Trigger,
Funktionen,...) zur Verfügung stehen.
Prekompiliertes Programm
PL/SQL Engine
SQL
IF ...
THEN
SQL
ELSE
SQL
SQL
IF ...
THEN
SQL
ELSE
SQL
SQL
IF ...
THEN
SQL
ELSE
SQL
Procedural
Statement
Executor
SQL-Prozessor
1-2
1.2.066 / 4053
PL/SQL Einführung
1
Einleitung
www.unilog.integrata.de
www.unilog-integrata.de
1.2.066 / 4053
4053 / 1.2.036
1
Folie 2
1-3
1
1.1
PL/SQL Einführung
Einleitung
Client
Datenbank-Server
Anwendungsprogramm
insert into
delete from ...
Kommunikationsnetz
select count (x)
Oracle-RDBMS
Datenbank-Server
Client
Oracle-RDBMS
Applikation
1-4
Kommunikationsnetz
PL/SQL-Block
Declare
...
Beginn
Select
Insert
Delete
End;
mit PL/SQL
1.2.066 / 4053
PL/SQL Einführung
1
Einleitung
www.unilog.integrata.de
www.unilog-integrata.de
1.2.066 / 4053
4053 / 1.2.036
1
Folie 3
1-5
1
1.2
PL/SQL Einführung
Funktionsweise von PL/SQL
PL/SQL ist eine mächtige, prozedurale Spracherweiterung von SQL,
die von ORACLE (angelehnt an die Programmiersprache ADA) entwickelt wurde. ORACLE wählte ADA als Vorlage für PL/SQL, da diese
hochentwickelte Programmiersprache alle derzeit gebräuchlichen Programmierkonzepte (Verwendung von abstrakten Datentypen, modulares Programmieren, ausgefeilte Fehlerbehandlung) unterstützt, die für
die Entwicklung von komplexen Anwendungen erforderlich sind.
PL/SQL unterstützt die Verwendung aller DML-Statements (Insert, Update, Delete), Cursor Operationen und Transaction Processing (Commit, Rollback und Savepoint). PL/SQL erlaubt dagegen keine unmittelbaren DDL-Statements (Create, Drop, Alter, Truncate, Rename) und
DCL-Statements (Grant, Revoke).
Im Gegensatz zu Precompilern, die eine Einbettung von SQLAnweisungen in eine konventionelle, prozedurale Sprache ermöglichen,
ist PL/SQL eine Kombination von SQL und Sprachkonstrukten herkömmlicher Programmiersprachen. Es finden sich Konzepte wie
Ö Variablenvereinbarungen
Ö Variablenzuweisungen
Ö Verzweigungen
Ö Schleifen
Ö Fehlerbehandlung
PL/SQL ist eine Technologie, die aus der sogenannten PL/SQL Engine
besteht. Diese Engine ist Bestandteil vom ORACLE Datenbanksystem
und hat die Aufgabe, PL/SQL-Blöcke auszuführen. Ein PL/SQL-Block
besteht aus Vereinbarungsteil (optional), ausführbaren und bedingten
Anweisungen sowie einem Fehlerbehandlungsteil (optional). Der Vorteil
für eine Anwendung besteht darin, dass ein PL/SQL-Block als Einheit
zum Datenbanksystem geschickt werden kann. Die PL/SQL Engine innerhalb des Datenbanksystems führt alle prozeduralen Befehle aus,
während alle SQL-Anweisungen vom SQL-Prozessor bearbeitet werden. Speziell in Client/Server-Umgebungen macht sich der Einsatz von
PL/SQL positiv bemerkbar.
1-6
1.2.066 / 4053
PL/SQL Einführung
1
Einleitung
www.unilog.integrata.de
www.unilog-integrata.de
4053 / 1.2.036
1
Folie 4
Funktionsweise von PL/SQL
1
ƒ SQL-Befehl wird von ORACLE geparst, optimiert und ausgeführt
ƒ SQL hat als nicht-prozedurale Sprache keine Kontrollstrukturen
ƒ PL/SQL kann seit Version 6 komplexe datenbankorientierte Operationen im DB-Server
speichern und ausführen
ƒ PL/SQL ist angelehnt an die Programmiersprache ADA
ƒ PL/SQL unterstützt alle DML-Statements
ƒ PL/SQL unterstützt Cursoroperationen
ƒ PL/SQL unterstützt COMMIT, ROLLBACK und SAVEPOINT
www.unilog.integrata.de
www.unilog-integrata.de
1.2.066 / 4053
4053 / 1.2.036
Folie 5
1-7
1
1.3
PL/SQL Einführung
Initialisierungsparameter in ORACLE 10g
In ORACLE 10g sind einige neue Initialisierungsparameter hinzugekommen, mit denen sich das Verhalten von PL/SQL beeinflussen lässt.
• plsql_code_type:
– Standardwert:
INTERPRETED
– Beschreibung:
Kompilierungsmodus für PL/SQL-Pakete.
– Mögliche Werte (String):
– INTERPRETED
Pakete werden in PL/SQL-Bytecode übersetzt und von der
PL/SQL-Interpreter Engine ausgeführt.
– NATIVE
Pakete werden soweit möglich in nativen Maschinencode übersetzt.
– Mögliche Anpassungsmethoden:
– ALTER SYSTEM
– ALTER SESSION
– Beispiel
SQL> ALTER SESSION
SET plsql_code_type = 'NATIVE';
Hinweis: Für die Einstellung ‘NATIVE’ ist ein C++ Compiler auf der Servermaschine notwendig.
Unter Windows werden unterstützt:
• MinGW
• Visual C++
Unter Linux:
• GCC
1-8
1.2.066 / 4053
PL/SQL Einführung
1
Neue Initialisierungsparameter in ORACLE
10g
1
ƒ plsql_code_type
ƒ INTERPRETED
ƒ NATIVE
ƒ plsql_debug
ƒ TRUE
ƒ FALSE
ƒ plsql_optimize_level
ƒ 0
ƒ 1
ƒ 2
www.unilog.integrata.de
www.unilog-integrata.de
1.2.066 / 4053
4053 / 1.2.036
Folie 6
1-9
1
PL/SQL Einführung
• plsql_debug:
– Standardwert:
FALSE
– Beschreibung:
Legt fest, ob PL/SQL-Pakete mit Debuginformationen übersetzt
werden sollen. Hat keinerlei Einfluss auf bereits übersetzte Pakete.
– Mögliche Werte (Boolean):
– TRUE
Pakete werden mit Debuginformationen übersetzt. Dies hat zur
Folge, dass automatisch INTERPRETED übersetzt wird!
– FALSE
Pakete werden ohne Debuginformationen übersetzt.
– Mögliche Anpassungsmethoden:
– ALTER SYSTEM
– ALTER SESSION
– Beispiel
SQL> ALTER SESSION SET plsql_debug = TRUE;
1-10
1.2.066 / 4053
PL/SQL Einführung
1
• plsql_optimize_level
– Standwert:
2
– Beschreibung:
Optimierungsgrad für PL/SQL-Compiler.
– Mögliche Werte (Integer):
– 0
Abgesehen von einfachsten Optimierungen finden praktisch
keine Verbesserungen statt. Generell wird der Code unwesentlich schneller laufen als unter 9i, aber das Potential von 10g
wird kaum ausgeschöpft.
– 1
Wendet übliche Optimierungsverfahren an, wie z.B. die Elimination von unnötigen Berechnungen. Jedoch wird die Ablaufstruktur des Codes nicht optimiert.
– 2
Verwendet alle ORACLE zur Verfügung stehenden Optimierverfahren an. Diese Einstellung ist natürlich am langsamsten
und produziert den schnellsten Code. Allerdings ist der Geschwindigkeitsgewinn gegenüber 1 nicht besonders groß.
– Mögliche Anpassungsmethoden:
– ALTER SYSTEM
– ALTER SESSION
– Beispiel
SQL> ALTER SESSION
SET plsql_optimize_level = 1;
1.2.066 / 4053
1-11
1
PL/SQL Einführung
• plsql_warnings:
– Standardwert:
DISABLE:ALL
– Beschreibung:
Warnungseinstellung für PL/SQL.
– Mögliche Werte (String):
Syntax:
plsql_warnings = 'vclause' {, 'vclause'}…
wobei:
vclause ::=
[ ENABLE | DISABLE | ERROR ]:
[
ALL
| SEVERE
| INFORMATIONAL
| PERFORMANCE
| integer
| (integer {,integer})
]
ENABLE
aktiviert Warnungen.
DISABLE
deaktiviert Warnungen.
ERROR
behandelt Warnungen als Fehler.
ALL
steht für alle Warnungen.
SEVERE
steht für schwerwiegende Warnungen.
INFORMATIONAL steht für informierende Warnungen.
PERFORMANCE
steht für Performancewarnungen.
integer
steht für eine beliebige Fehlernummer.
– Mögliche Anpassungsmethoden:
– ALTER SYSTEM … DEFERRED
– ALTER SESSION
– Beispiel
SQL> ALTER SESSION SET
plsql_warnings = 'ENABLE:SEVERE',
'DISABLE:(5001,5002)';
1-12
1.2.066 / 4053
PL/SQL Einführung
1
Neue Initialisierungsparameter in ORACLE
10g (f)
1
ƒ plsql_warnings = vclause {,vclause}
ƒ Default: DISABLE:ALL
ƒ Vclause ::=
[ ENABLE | DISABLE | ERROR ]:
[ ALL | SEVERE | INFORMATIONAL
| PERFORMANCE | integer
| (integer {,integer}) ]
www.unilog.integrata.de
www.unilog-integrata.de
1.2.066 / 4053
4053 / 1.2.036
Folie 7
1-13
1
1.4
PL/SQL Einführung
PL/SQL-Blockstruktur
PL/SQL ist eine blockorientierte Sprache. Jeder Block besteht im Wesentlichen aus folgenden drei Teilen:
• dem Deklarationsteil (optional)
Hier werden Variablen und Konstanten deklariert, explizite Cursor für mengenorientierte SELECT Operationen definiert und benutzerdefinierte Fehler
bzw. Ausnahmen angegeben. Der Deklarationsteil wird eingeleitet durch das
Schlüsselwort 'DECLARE'.
• dem Ausführungsteil (erforderlich)
Dieser Teil beinhaltet das eigentliche Programm. Hier finden sich PL/SQLAnweisungen wie Schleifen, if-then-else Konstrukte und Zuweisungen sowie
SQL-Befehle. Der Ausführungsteil wird eingeleitet durch das Schlüsselwort
'BEGIN' und beendet durch 'END'.
• dem Fehlerbehandlungsteil (optional)
In diesen Teil des PL/SQL-Blocks wird stets bei einer Fehler- bzw. Ausnahmesituation verzweigt, um die aufgetretene Fehlersituation definiert bearbeiten zu können. Hier finden sich sogenannte 'Exception Handler', das sind
PL/SQL Programmteile, die für unterschiedliche Fehlersituationen entsprechende Programmaktionen durchführen.
Nach einer definierten Fehlerbehandlung wird der PL/SQL-Block verlassen.
Der Fehlerbehandlungsteil eines PL/SQL-Blocks wird eingeleitet durch das
Schlüsselwort EXCEPTION.
Ein PL/SQL-Programm kann interaktiv (wie SQL-Anweisungen) innerhalb von
SQL*Plus bzw. SQL-Worksheet eingegeben werden. Sobald SQL*Plus oder
SQL-Worksheet das Schlüsselwort DECLARE oder BEGIN erkennt, wird in den
Eingabemodus für PL/SQL-Programme umgeschaltet. (Die Eingabe eines ';'
bleibt ohne Funktion.) Dieser Eingabemodus kann durch die Eingabe eines
Punktes wieder verlassen werden. Auch die Eingabe von RUN (oder /) beendet den Eingabemodus; ein anonymer Block wird dadurch auch gleich ausgeführt, benannte PL/SQL-Programme werden dadurch kompiliert. Befindet sich
das Programm im Eingabepuffer, kann es ebenfalls mit RUN kompiliert werden.
Um mit PL/SQL arbeiten zu können, muss das Skript CATPROC.SQL ausgeführt worden sein. Normalerweise geschieht dies bei der Installation.
Innerhalb von PL/SQL können beliebig viele SQL-Kommandos verwendet
werden. Die Verarbeitung der SQL-Kommandos geschieht wie gewohnt im
Datenbankkern, die Verarbeitung der Nicht-SQL-Kompo-nenten übernimmt
eine sogenannte PL/SQL-Engine. Die PL/SQL-Engine muss das PL/SQLProgramm übersetzen und ausführen.
Dazu muss sie die enthaltenen SQL-Anweisungen identifizieren und an den
SQL-Statement-Executor übergeben, der das Ergebnis einer Anweisung an
die PL/SQL-Engine zurückliefert. Die zurückgelieferten Daten werden gemäß
dem PL/SQL-Programm weiterverarbeitet.
Der SQL-Statement-Executor ist bei der Datenbank angesiedelt und läuft somit auf dem Datenbankserver ab. Die PL/SQL-Engine kann sich dagegen
auch beim Client befinden, zum Beispiel integriert in einem Werkzeug wie
ORACLE*Forms.
1-14
1.2.066 / 4053
PL/SQL Einführung
1
PL/SQL-Blockstruktur
1
ƒ Aufbau eines PL/SQL Blocks
DECLARE
-- optional
Variablen, Konstanten, Cursor,
benutzerdefinierte Exceptions
BEGIN
SQL-Anweisungen
PL/SQL-Anweisungen
-- erforderlich
EXCEPTION
-- optional
END;
-- erforderlich
Anweisungen, die ausgeführt werden, wenn Fehler auftreten
/
ƒ Ein Punkt (".") beendet die Eingabe eines PL/SQL-Blocks in SQL*Plus oder SQL-Worksheet
ƒ Ein "/" oder das Kommando "RUN" beenden ebenfalls die Eingabe und kompilieren das Programm
zusätzlich
www.unilog.integrata.de
www.unilog-integrata.de
1.2.066 / 4053
4053 / 1.2.036
Folie 8
1-15
1
1.5
PL/SQL Einführung
Arten von PL/SQL-Programmkonstrukten
Die wichtigsten PL/SQL-Programmkonstrukte sind:
Anonymer Block:
Stored Procedure/
Stored Function:
Unbenannter PL/SQL-Block, in eine Anwendung eingebettet oder interaktiv gestartet
(/ bzw. run)
Benannter, in der Datenbank gespeicherter
PL/SQL-Block, an den Parameter übergeben
werden können und der (wiederholt) namentlich
aufgerufen wird (exec[ute] name)
Datenbanktrigger:
In der Datenbank gespeicherter PL/SQL-Block,
der beim Eintreten von bestimmten Ereignissen
ausgeführt wird. Ein expliziter Aufruf ist nicht
möglich.
Package:
Benannte PL/SQL-Einheit, die zusammengehörige Prozeduren, Funktionen und Variablen zusammenfaßt.
Daneben gibt es noch:
Objekt-Typen:
In der Datenbank gespeicherte, benutzerdefinierte Datentypen
Anwendungsprozedur
Anwendungsfunktion: Bestandteile einer Developer-Anwendung
(Forms, Reports) und in dieser gespeichert.
Anwendungstrigger: Bestandteile
einer
Developer-Anwendung
(Forms, Reports) und in dieser gespeichert.
1-16
1.2.066 / 4053
PL/SQL Einführung
1
PL/SQL-Blockstruktur
1
ƒ PL/SQL-Anweisungen verarbeitet die
PL/SQL-Engine
ƒ SQL-Anweisungen verarbeitet der
SQL-Statement-Executor
ƒ PL/SQL-Programmkonstrukte:
ƒ Anonymer Block
ƒ Stored Procedure / Stored Function
ƒ Datenbanktrigger
ƒ Package
ƒ Objekt-Typ
ƒ Anwendungsprozedur / Anwendungsfunktion
ƒ Anwendungstrigger
www.unilog.integrata.de
www.unilog-integrata.de
1.2.066 / 4053
4053 / 1.2.036
Folie 9
1-17
1
1.6
PL/SQL Einführung
Wichtige Rechte in PL/SQL
CREATE PROCEDURE erlaubt einem User, innerhalb des eigenen
Schemas Prozeduren, Funktionen und Packages anzulegen. Der Besitzer eines PL/SQL-Programms darf dieses jederzeit löschen.
CREATE / ALTER / DROP ANY PROCEDURE erlauben, Prozeduren,
Funktionen und Packages in jedem beliebigen Schema anzulegen, zu
kompilieren oder zu löschen.
EXECUTE erlaubt die Ausführung eines in der GRANT-Anweisung namentlich zu nennenden PL/SQL-Programms (Prozedur, Funktion, Package)
EXECUTE ANY PROCEDURE erlaubt die Ausführung jedes PL/SQLProgramms in jedem Schema.
GRANT ANY OBJECT PRIVILEGE erlaubt die Vergabe von beliebigen
Objektrechten an einen Benutzer.
Rechte, die ein Benutzer über eine Rolle erhalten hat, sind innerhalb
von PL/SQL nicht wirksam. Vergeben Sie deshalb Rechte immer direkt
an den Entwickler!
1-18
1.2.066 / 4053
PL/SQL Einführung
1
Rechte in PL/SQL
1
ƒ Rechte in PL/SQL
ƒ
ƒ
ƒ
ƒ
ƒ
ƒ
CREATE ANY PROCEDURE
ALTER ANY PROCEDURE
DROP ANY PROCEDURE
EXECUTE ANY PROCEDURE
CREATE PROCEDURE
EXECUTE proc/func/pack name
ƒ Rechte, die über Rollen zugewiesen wurden, sind nicht wirksam
www.unilog.integrata.de
www.unilog-integrata.de
1.2.066 / 4053
4053 / 1.2.036
Folie 10
1-19
1
1.7
PL/SQL Einführung
Programmierstil
Solange die Syntax eingehalten wird, ist die äußere Form nicht entscheidend. Mehrere Anweisungen in einer Zeile sind ebenso erlaubt
wie der Zeilenumbruch innerhalb einer Anweisung.
Zur besseren Lesbarkeit und Wartung wird jedoch empfohlen, auf jeder
Block-, Schleifen- und Bedingungsebene den Code einzurücken. Das
erleichtert die Zuordnung von Anfang und Ende eines Blockes, einer
Schleife oder einer Bedingung. In jeder Zeile sollte außerdem nicht
mehr als eine Anweisung stehen.
Auch Kommentare tragen zur besseren Lesbarkeit bei. Es gibt in
PL/SQL drei Kennzeichen für Kommentare, die im Gegensatz zu einigen anderen Programmiersprachen nicht unbedingt am Zeilenanfang
stehen müssen:
--
leitet einen einzeiligen Kommentar ein
/*
leitet einen mehrzeiligen Kommentar ein
*/
beendet einen mehrzeiligen Kommentar
Verschachtelte Kommentare, z.B. in der Form
/*
/*
*/
*/
sind nicht zulässig.
1.8
Schachtelung von PL/SQL-Blöcken
PL/SQL bietet die Möglichkeit, PL/SQL-Blöcke zu schachteln, wodurch
ein Programm fein gegliedert und übersichtlich aufgebaut werden kann.
Für jeden Block kann ein eigener Deklarations- und Ausnahmeteil erstellt werden, wodurch der jeweilige Umfang des Blockes problembezogen beschränkt bleibt.
Die Möglichkeit zur Schachtelung von PL/SQL-Blöcken erlaubt es, Fehler die auftreten, innerhalb einer Schleife eines einschließenden Blockes erneut durchlaufen zu lassen, nachdem der Fehler behoben wurde.
1-20
1.2.066 / 4053
PL/SQL Einführung
1
Schachtelung von PL/SQL-Blöcken
1
ƒ Programmierstil
ƒ Einrücken von Code
ƒ nur eine Anweisung pro Zeile
ƒ Kommentare:
-- einzeilige Kommentare
/* mehrzeilige
Kommentare */
ƒ Schachtelung von PL/SQL-Blöcken
ƒ feine Gliederung
ƒ übersichtlicher Programmaufbau
ƒ problembezogene Blöcke
ƒ gezielte Fehlerbehandlung
www.unilog.integrata.de
www.unilog-integrata.de
1.2.066 / 4053
4053 / 1.2.036
Folie 11
1-21
1
1.9
PL/SQL Einführung
Labels
Mit Labels kann man einen bestimmten Punkt in einem Programm
kennzeichnen. Labels müssen innerhalb ihres Wirkungsbereichs eindeutig sein. Ein Label darf nur stehen
• vor einem Block
• vor einer Schleife
• vor einer ausführbaren Anweisung
Syntax:
<<labelname>>
Beispiel:
DECLARE
.....
BEGIN
.....
<<Block_a>>
BEGIN
......
<<Block_b>>
DECLARE
.....
– hier ist kein Label erlaubt
BEGIN
Anweisung;
END;
END;
– hier ist kein Label erlaubt
END;
1-22
1.2.066 / 4053
PL/SQL Einführung
1
Labels
1
ƒ Labels
ƒ vor einem Block
ƒ vor einer Schleife
ƒ vor einer bedingten Anweisung
ƒ Beispiel:
DECLARE
...
BEGIN
... <<Block_a>>
BEGIN
... <<Block_b>>
DECLARE
...
-- hier ist kein
-- Label erlaubt
BEGIN
... <<Anweisung_a>>
Anweisung;
END;
END;
-- hier ist kein
-- Label erlaubt
END;
www.unilog.integrata.de
www.unilog-integrata.de
1.2.066 / 4053
4053 / 1.2.066
Folie 12
1-23
1
1-24
PL/SQL Einführung
1.2.066 / 4053