ORACLE und PL/SQL - sabin.at · SQL> ALTER SESSION SET plsql_debug = TRUE; PL/SQL Einführung 1...

1.2.066 / 4053 1-1

1 PL/SQL Einführung

1.1 Einleitung........................................................................................ 1-4 1.2 Funktionsweise von PL/SQL .......................................................... 1-6 1.3 Initialisierungsparameter in ORACLE 10g...................................... 1-8 1.4 PL/SQL-Blockstruktur ................................................................... 1-14 1.5 Arten von PL/SQL-Programmkonstrukten .................................... 1-16 1.6 Wichtige Rechte in PL/SQL.......................................................... 1-18 1.7 Programmierstil ............................................................................ 1-20 1.8 Schachtelung von PL/SQL-Blöcken ............................................. 1-20 1.9 Labels........................................................................................... 1-22


1-2 1.2.066 / 4053


Um auf eine ORACLE Datenbank zuzugreifen, wird ausschließlich die Sprache SQL benutzt. Ein SQL-Befehl wird vom ORACLE-Server ge-parst, 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 ste-henden 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 Datenbanksys-tems 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 in-

nerhalb 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 SQL-Statements wird das Netzwerk nur durch eine Anfrage und eine ent-sprechende Rückmeldung belastet.

2. PL/SQL sollte innerhalb aller Oracle-Programme (PRO-C, PRO-COBOL, Forms,...) und innerhalb des Datenbankservers (Trigger, Funktionen,...) zur Verfügung stehen.

Prekompiliertes Programm

SQLIF ...

THEN

ELSESQL

SQL

SQLIF ...

THEN

ELSESQL

SQL

SQLIF ...

THEN

ELSESQL

SQL

PL/SQL Engine

SQL-Prozessor

ProceduralStatementExecutor

PL/SQL Einführung 1

1.2.066 / 4053 1-3

www.unilog.integrata.dewww.unilog-integrata.de Folie 24053 / 1.2.036

1Einleitung


1-4 1.2.066 / 4053

1.1 Einleitung

Kom

mun

ikat

ions

netz

Client Datenbank-Server

Kom

mun

ikat

ions

netz

Client Datenbank-Server

Anwendungsprogramm Oracle-RDBMS

select count (x)

insert into

delete from ...

PL/SQL-Block Declare ... Beginn Select Insert Delete End;

Applikation Oracle-RDBMS

mit PL/SQL


1.2.066 / 4053 1-5


1Einleitung


1-6 1.2.066 / 4053

1.2 Funktionsweise von PL/SQL

PL/SQL ist eine mächtige, prozedurale Spracherweiterung von SQL, die von ORACLE (angelehnt an die Programmiersprache ADA) entwi-ckelt wurde. ORACLE wählte ADA als Vorlage für PL/SQL, da diese hochentwickelte Programmiersprache alle derzeit gebräuchlichen Pro-grammierkonzepte (Verwendung von abstrakten Datentypen, modula-res 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, Up-date, Delete), Cursor Operationen und Transaction Processing (Com-mit, Rollback und Savepoint). PL/SQL erlaubt dagegen keine unmittel-baren DDL-Statements (Create, Drop, Alter, Truncate, Rename) und DCL-Statements (Grant, Revoke). Im Gegensatz zu Precompilern, die eine Einbettung von SQL-Anweisungen in eine konventionelle, prozedurale Sprache ermöglichen, ist PL/SQL eine Kombination von SQL und Sprachkonstrukten her-kö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 in-nerhalb des Datenbanksystems führt alle prozeduralen Befehle aus, während alle SQL-Anweisungen vom SQL-Prozessor bearbeitet wer-den. Speziell in Client/Server-Umgebungen macht sich der Einsatz von PL/SQL positiv bemerkbar.


1.2.066 / 4053 1-7


1Einleitung


1Funktionsweise von PL/SQL

SQL-Befehl wird von ORACLE geparst, optimiert und ausgeführtSQL hat als nicht-prozedurale Sprache keine Kontrollstrukturen

PL/SQL kann seit Version 6 komplexe datenbankorientierte Operationen im DB-Server speichern und ausführenPL/SQL ist angelehnt an die Programmiersprache ADAPL/SQL unterstützt alle DML-StatementsPL/SQL unterstützt CursoroperationenPL/SQL unterstützt COMMIT, ROLLBACK und SAVEPOINT


1-8 1.2.066 / 4053

1.3 Initialisierungsparameter in ORACLE 10g

In ORACLE 10g sind einige neue Initialisierungsparameter hinzuge-kommen, 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 über-setzt.

– 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 Server-maschine notwendig. Unter Windows werden unterstützt: • MinGW • Visual C++ Unter Linux: • GCC


1.2.066 / 4053 1-9


1Neue Initialisierungsparameter in ORACLE

10g

plsql_code_typeINTERPRETEDNATIVE

plsql_debugTRUEFALSE

plsql_optimize_level012


1-10 1.2.066 / 4053

• plsql_debug:

– Standardwert: FALSE

– Beschreibung: Legt fest, ob PL/SQL-Pakete mit Debuginformationen übersetzt werden sollen. Hat keinerlei Einfluss auf bereits übersetzte Pake-te.

– 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.2.066 / 4053 1-11

• 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 unwesent-lich schneller laufen als unter 9i, aber das Potential von 10g wird kaum ausgeschöpft.

– 1

Wendet übliche Optimierungsverfahren an, wie z.B. die Elimi-nation von unnötigen Berechnungen. Jedoch wird die Ablauf-struktur des Codes nicht optimiert.

– 2

Verwendet alle ORACLE zur Verfügung stehenden Optimier-verfahren an. Diese Einstellung ist natürlich am langsamsten und produziert den schnellsten Code. Allerdings ist der Ge-schwindigkeitsgewinn gegenüber 1 nicht besonders groß.

– Mögliche Anpassungsmethoden: – ALTER SYSTEM

– ALTER SESSION

– Beispiel SQL> ALTER SESSION

SET plsql_optimize_level = 1;


1-12 1.2.066 / 4053

• 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.2.066 / 4053 1-13


1Neue Initialisierungsparameter in ORACLE

10g (f)

plsql_warnings = vclause {,vclause}Default: DISABLE:ALLVclause ::=[ ENABLE | DISABLE | ERROR ]:

[ ALL | SEVERE | INFORMATIONAL| PERFORMANCE | integer| (integer {,integer}) ]


1-14 1.2.066 / 4053

1.4 PL/SQL-Blockstruktur

PL/SQL ist eine blockorientierte Sprache. Jeder Block besteht im Wesentli-chen aus folgenden drei Teilen: • dem Deklarationsteil (optional) Hier werden Variablen und Konstanten deklariert, explizite Cursor für men-genorientierte 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/SQL-Anweisungen 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. Ausnah-mesituation verzweigt, um die aufgetretene Fehlersituation definiert bearbei-ten zu können. Hier finden sich sogenannte 'Exception Handler', das sind PL/SQL Programmteile, die für unterschiedliche Fehlersituationen entspre-chende 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 /) been-det den Eingabemodus; ein anonymer Block wird dadurch auch gleich ausge-führt, benannte PL/SQL-Programme werden dadurch kompiliert. Befindet sich das Programm im Eingabepuffer, kann es ebenfalls mit RUN kompiliert wer-den. Um mit PL/SQL arbeiten zu können, muss das Skript CATPROC.SQL ausge-fü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/SQL-Programm ü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 so-mit 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.2.066 / 4053 1-15


1PL/SQL-Blockstruktur

Aufbau eines PL/SQL Blocks

DECLARE -- optionalVariablen, Konstanten, Cursor,benutzerdefinierte Exceptions

BEGIN -- erforderlichSQL-AnweisungenPL/SQL-Anweisungen

EXCEPTION -- optionalAnweisungen, die ausgeführt werden, wenn Fehler auftreten

END; -- erforderlich/

Ein Punkt (".") beendet die Eingabe eines PL/SQL-Blocks in SQL*Plus oder SQL-WorksheetEin "/" oder das Kommando "RUN" beenden ebenfalls die Eingabe und kompilieren das Programm zusätzlich


1-16 1.2.066 / 4053

1.5 Arten von PL/SQL-Programmkonstrukten

Die wichtigsten PL/SQL-Programmkonstrukte sind:

Anonymer Block: Unbenannter PL/SQL-Block, in eine Anwen-dung eingebettet oder interaktiv gestartet (/ bzw. run)

Stored Procedure/ Stored Function: 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 zu-sammenfaßt.

Daneben gibt es noch:

Objekt-Typen: In der Datenbank gespeicherte, benutzerdefi-nierte 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.2.066 / 4053 1-17


1PL/SQL-Blockstruktur

PL/SQL-Anweisungen verarbeitet die PL/SQL-Engine

SQL-Anweisungen verarbeitet der SQL-Statement-Executor

PL/SQL-Programmkonstrukte:Anonymer BlockStored Procedure / Stored FunctionDatenbanktriggerPackageObjekt-TypAnwendungsprozedur / AnwendungsfunktionAnwendungstrigger


1-18 1.2.066 / 4053

1.6 Wichtige Rechte in PL/SQL

CREATE PROCEDURE erlaubt einem User, innerhalb des eigenen Schemas Prozeduren, Funktionen und Packages anzulegen. Der Besit-zer 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 na-mentlich zu nennenden PL/SQL-Programms (Prozedur, Funktion, Pa-ckage) EXECUTE ANY PROCEDURE erlaubt die Ausführung jedes PL/SQL-Programms 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.2.066 / 4053 1-19


1Rechte in PL/SQL

Rechte in PL/SQL

CREATE ANY PROCEDUREALTER ANY PROCEDUREDROP ANY PROCEDUREEXECUTE ANY PROCEDURECREATE PROCEDUREEXECUTE proc/func/pack name

Rechte, die über Rollen zugewiesen wurden, sind nicht wirksam


1-20 1.2.066 / 4053

1.7 Programmierstil

Solange die Syntax eingehalten wird, ist die äußere Form nicht ent-scheidend. 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 eini-gen 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 er-stellt werden, wodurch der jeweilige Umfang des Blockes problembe-zogen beschränkt bleibt. Die Möglichkeit zur Schachtelung von PL/SQL-Blöcken erlaubt es, Feh-ler die auftreten, innerhalb einer Schleife eines einschließenden Blo-ckes erneut durchlaufen zu lassen, nachdem der Fehler behoben wur-de.


1.2.066 / 4053 1-21


1Schachtelung von PL/SQL-Blöcken

ProgrammierstilEinrücken von Codenur eine Anweisung pro ZeileKommentare: -- einzeilige Kommentare/* mehrzeilige

Kommentare */

Schachtelung von PL/SQL-Blöckenfeine Gliederungübersichtlicher Programmaufbauproblembezogene Blöckegezielte Fehlerbehandlung


1-22 1.2.066 / 4053

1.9 Labels

Mit Labels kann man einen bestimmten Punkt in einem Programm kennzeichnen. Labels müssen innerhalb ihres Wirkungsbereichs ein-deutig 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.2.066 / 4053 1-23


1Labels

Labelsvor einem Blockvor einer Schleifevor einer bedingten Anweisung

Beispiel:DECLARE...

BEGIN... <<Block_a>>BEGIN... <<Block_b>>

DECLARE... -- hier ist kein

-- Label erlaubtBEGIN

... <<Anweisung_a>>Anweisung;

END;END; -- hier ist kein

-- Label erlaubtEND;


1-24 1.2.066 / 4053

ORACLE und PL/SQL - sabin.at · SQL> ALTER SESSION SET plsql_debug = TRUE; PL/SQL Einführung 1...

Documents

Transcript of ORACLE und PL/SQL - sabin.at · SQL> ALTER SESSION SET plsql_debug = TRUE; PL/SQL Einführung 1...