Was ist PL/SQL?

3. Stored Procedures und PL/SQL
PL/SQL
Prozedurale SQL-Erweiterungen
Prozedurale SQL-Erweiterungen unterscheiden sich typischerweise durch die folgenden Punkte von dem Ansatz der Einbettung:
• Neue dedizierte Sprache mit direkter Integration der SQL-Anweisungen
☞ bekannteste Variante: PL/SQL von Oracle
• Ausführung der Programme immer im Datenbank-Server
• Zugriff auf Typinformation der Datenbank
• Integration des Cursor-Konzeptes in Kontrollstrukturen
Objektrelationale Datenbanksysteme — Hochschule Bonn-Rhein-Sieg, SS 2010
57
3. Stored Procedures und PL/SQL
PL/SQL
Was ist PL/SQL?
• Procedural Language Extensions to SQL
• proprietäre Programmiersprache der Firma Oracle
• Abfragesprache (DML) mit zusätzlichen prozeduralen und objektorientierten Elementen
• Überprüfung von Syntax und Semantik (bzgl. Datenbank) zur Compile-Zeit
• Syntax angelehnt an ADA
• effiziente Ausführung, compiliert
Objektrelationale Datenbanksysteme — Hochschule Bonn-Rhein-Sieg, SS 2010
58
3. Stored Procedures und PL/SQL
PL/SQL
Verwendung von PL/SQL
In:
•
•
•
•
anonymen PL/SQL-Blöcken (Scripts),
Stored Procedures und User Defined Functions,
Triggern und
einer Reihe von Datenbank-Werkzeugen (Oracle APEX, Forms, etc.).
Objektrelationale Datenbanksysteme — Hochschule Bonn-Rhein-Sieg, SS 2010
59
3. Stored Procedures und PL/SQL
PL/SQL
Kurze Geschichte zu PL/SQL
• PL/SQL 1.0 in Oracle 6 (1991), noch sehr eingeschränkt
• PL/SQL 2.0 in Oracle 7, schon deutlich ausgebaut, Stored Procedures, Packages
• PL/SQL 2.1 mit Oracle 7.1, dynamisches SQL, Unterstützung von DDL, User Defined Functions
• PL/SQL 2.2 mit Oracle 7.2, Verschlüsseln von Quelltext, Jobverarbeitung in der
Datenbank
• PL/SQL 2.3 mit Oracle 7.3, Dateiein- und -ausgabe, PL/SQL-Tabellen
• PL/SQL 8.0 mit Oracle 8 (1999), Large Objects (LOBs), Kollektionstypen, Queueing
Objektrelationale Datenbanksysteme — Hochschule Bonn-Rhein-Sieg, SS 2010
60
3. Stored Procedures und PL/SQL
PL/SQL
PL/SQL-Block
Aufbau eines PL/SQL-Skripts:
declare
Variablendeklarationen
...
begin
SQL- und PL/SQL-Anweisungen
...
exception
Exception-Handler
...
end;
Datentypen: Datentypen:
Datentpen,
Cursor,
Felder
SQLStrukturen,
Kontrollstrukturen: SQLAnweisungen,
Zuweisungen,
loop, for, while, Exceptions
if,
Exceptions: vordefinierte und selbstdefinierte Exceptions
Deklarationsteil und Exception-Handler sind optional.
Beliebige Schachtelung von Blöcken möglich.
PL/SQL ist nicht case-sensitiv.
Objektrelationale Datenbanksysteme — Hochschule Bonn-Rhein-Sieg, SS 2010
61
3. Stored Procedures und PL/SQL
PL/SQL
Verwendung von PL/SQL-Blöcken
• als anonyme Blöcke ad-hoc in Sql*Plus
• für die Definition einer Stored Procedure oder einer User Defined Function innerhalb und außerhalb eines Pakets
dann CREATE PROCEDURE ... statt declare
• für die Definition von Methoden für selbstdefinierte Typen innerhalb und außerhalb
eines Pakets
Objektrelationale Datenbanksysteme — Hochschule Bonn-Rhein-Sieg, SS 2010
62
3. Stored Procedures und PL/SQL
PL/SQL
Kommentare
• Zeilenkommentar
-Bis zum Ende der Zeile
• Blockkommentar
/* ...*/
Objektrelationale Datenbanksysteme — Hochschule Bonn-Rhein-Sieg, SS 2010
63
3. Stored Procedures und PL/SQL
PL/SQL
Bildschirmausgaben
• Hierzu nutzt man das vordefinierte Paket dbms output.
• Beispiel:
dbms_output.put_line(’Hello World!’);
•
•
•
•
Ausgabe von Hello World! plus Zeilentrenner.
Die Prozedur put line ist überladen, es können auch andere Datentypen eingesetzt werden: number, date, ...
put ermöglicht eine Ausgabe ohne Zeilentrenner.
Da der PL/SQL-Block im Server ausgeführt wird, werden die Ausgaben in einem
Puffer gesammelt und vom Client erst nach Beendigung des Skripts vollständig
angezeigt.
Der Puffer hat eine Standardgröße von 20.000 Bytes, Anpassung mit
dbms_output.enable(1024*1024);
Setzt den Puffer auf 1 MB.
Objektrelationale Datenbanksysteme — Hochschule Bonn-Rhein-Sieg, SS 2010
64
3. Stored Procedures und PL/SQL
PL/SQL
Zwei wichtige Hinweise zur PL/SQL-Nutzung
• Zur Aktivierung der Ausgabe im Server muss eine SQL-Option gesetzt werden,
z.B. in Sql*Plus
set serveroutput on
• In Sql*Plus wird die Ausführung von PL/SQL-Anweisungen durch das Zeichen /
angestoßen.
begin
dbms_output.put_line(’Hello World!’);
end;
/
Objektrelationale Datenbanksysteme — Hochschule Bonn-Rhein-Sieg, SS 2010
65
3. Stored Procedures und PL/SQL
PL/SQL
Datentypen und Variablendeklarationen
• Es stehen alle Datentypen von SQL zur Verfügung, ☞ kein Type Mismatch
• number, char, varchar2, date, boolean
• Variablendeklaration zwischen declare und begin:
declare
n number;
s varchar2(30);
begin
...
• Variablen können den SQL-Wert NULL annehmen, Deklaration als NOT NULL
möglich, dann Initialisierung erforderlich:
n number not null := 0;
s varchar2(30) := ’hello’;
Objektrelationale Datenbanksysteme — Hochschule Bonn-Rhein-Sieg, SS 2010
66
3. Stored Procedures und PL/SQL
PL/SQL
• Konstantendeklaration, hier ist ebenfalls Initialisierung erforderlich:
eins constant number := 1;
Keine Zuweisung an die Variable möglich.
• weiterer (einfacher) PL/SQL-Datentyp: PLS INTEGER
entspricht int in Java, effizientere Repräsentation als number
Objektrelationale Datenbanksysteme — Hochschule Bonn-Rhein-Sieg, SS 2010
67
3. Stored Procedures und PL/SQL
PL/SQL
Typ-Referenzierung in Variablendeklarationen
• Statt eines konkreten Typs kann Bezug genommen werden auf einen Typ eines
Attributs einer Relation.
name
Customer.name%type;
• Damit hat die Variable name den Typ des Attributs name der Relation Customer.
• Für Stored Procedures ensteht dadurch eine Abhängigkeit, die im Data Dictionary
verzeichnet wird.
• Eine Änderung des Attributstyps würde dazu führen, dass eine referenzierende
Stored Procedure invalid wird.
• Mit einem einfachen recompile-Befehl würde die Stored Procedure wieder valid.
Objektrelationale Datenbanksysteme — Hochschule Bonn-Rhein-Sieg, SS 2010
68
3. Stored Procedures und PL/SQL
PL/SQL
Row-Types
• Es ist ebenfalls ein Bezug auf das Schema einer Relation möglich.
cust Customer%rowtype;
• Damit hat die Variable cust einen strukturierten Typ (Record), der der Definition
der Tabelle Customer entspricht.
• Der Zugriff auf die Komponenten erfolgt mit Hilfe der Dot-Notation:
cust.name := ’Meier, Peter’;
• Dies kann z.B. direkt in SQL-Anweisungen genutzt werden:
declare
cname Customer.name%type;
cust
Customer%rowtype;
begin
Objektrelationale Datenbanksysteme — Hochschule Bonn-Rhein-Sieg, SS 2010
69
3. Stored Procedures und PL/SQL
PL/SQL
select name into cname from customer where id = 4711;
select * into cust from customer where id = 32168;
end;
Objektrelationale Datenbanksysteme — Hochschule Bonn-Rhein-Sieg, SS 2010
70
3. Stored Procedures und PL/SQL
PL/SQL
Operatoren
Vergleich:
•
•
•
•
•
•
•
gleich: =
ungleich: <> oder !=
ansonsten die üblichen Vergleichsoperatoren
IS NULL und IS NOT NULL
LIKE und NOT LIKE
BETWEEN
IN
Logische Operatoren: AND, OR und NOT
Die üblichen arithmetischen Operatoren
Konkatenation von Strings: ||
Objektrelationale Datenbanksysteme — Hochschule Bonn-Rhein-Sieg, SS 2010
71
3. Stored Procedures und PL/SQL
PL/SQL
Kontrollstrukturen
• Bedingte Anweisung
if Bedingung then
...
end if;
• Verzweigung
if Bedingung then
...
else
...
end if;
• Unbedingte Schleife
loop
Objektrelationale Datenbanksysteme — Hochschule Bonn-Rhein-Sieg, SS 2010
72
3. Stored Procedures und PL/SQL
PL/SQL
...
end loop;
• Sprung aus unbedingten Schleifen
exit when Bedingung ;
• While-Schleife
while Bedingung loop
...
end loop;
• Zählschleife
for variable in unten..oben loop
...
end loop;
Die Zählvariable muss nicht deklariert sein.
Von oben nach unten mittels in reverse statt in.
Objektrelationale Datenbanksysteme — Hochschule Bonn-Rhein-Sieg, SS 2010
73
3. Stored Procedures und PL/SQL
PL/SQL
Parameterloser Cursor
• Deklaration eines benannten Cursors im Deklarationsteil, open, fetch und close im
Ausführungsteil.
• declare
v_summe
number := 0;
v_pos_preis
number;
cursor c_auftrag_pos is select anzahl*preis from auftrag_pos;
begin
OPEN c_auftrag_pos;
loop
FETCH c_auftrag_pos into v_pos_preis;
exit when c_auftrag_pos%NOTFOUND;
v_summe := v_summe + v_pos_preis;
end loop;
CLOSE c_auftrag_pos;
end;
Objektrelationale Datenbanksysteme — Hochschule Bonn-Rhein-Sieg, SS 2010
74
3. Stored Procedures und PL/SQL
PL/SQL
Cursor mit Parameter (1)
declare
v_summe
number := 0;
v_pos_preis
number;
cursor c_auftrag_pos (p_auftrag_nr in auftrag_pos.auftrag_nr%TYPE) is
select anzahl*preis
from auftrag_pos
where auftrag_nr = p_auftrag_nr;
begin
OPEN c_auftrag_pos (4711);
loop
FETCH c_auftrag_pos into v_pos_preis;
exit when c_auftrag_pos%NOTFOUND;
v_summe := v_summe + v_pos_preis;
end loop;
CLOSE c_auftrag_pos;
end;
Objektrelationale Datenbanksysteme — Hochschule Bonn-Rhein-Sieg, SS 2010
75
3. Stored Procedures und PL/SQL
PL/SQL
Cursor mit Parameter (2)
declare
v_summe
number := 0;
v_auftrag_pos
auftrag_pos%ROWTYPE;
cursor c_auftrag_pos (p_auftrag_nr in auftrag_pos.auftrag_nr%TYPE) is
select *
from auftrag_pos
where auftrag_nr = p_auftrag_nr;
begin
OPEN c_auftrag_pos (4711);
loop
FETCH c_auftrag_pos into v_auftrag_pos;
exit when c_auftrag_pos%NOTFOUND;
v_summe := v_summe + v_auftrag_pos.anzahl * v_auftrag_pos.preis;
end loop;
CLOSE c_auftrag_pos;
end;
Objektrelationale Datenbanksysteme — Hochschule Bonn-Rhein-Sieg, SS 2010
76
3. Stored Procedures und PL/SQL
PL/SQL
Cursor-for-Schleife (1)
declare
cursor cursor name is select ...
begin
...
FOR variable name IN cursor name LOOP
...
END LOOP;
...
end;
Objektrelationale Datenbanksysteme — Hochschule Bonn-Rhein-Sieg, SS 2010
77
3. Stored Procedures und PL/SQL
PL/SQL
Cursor-for-Schleife (2)
• Der Cursor wird unmittelbar vor Ausführung der for-Schleife geöffnet und nach dem
Verlassen automatisch geschlossen.
• Pro Schleifentupel wird ein Tupel verarbeitet (kein explizites Fetch notwendig).
• Innerhalb der Schleife steht die Schleifenvariable variable name für den Zugriff auf
das aktuelle Tupel zur Verfügung.
• Die Schleifenvariable hat den Typ des Cursors.
• Der Status eines Cursors kann abgefragt werden.
Objektrelationale Datenbanksysteme — Hochschule Bonn-Rhein-Sieg, SS 2010
78
3. Stored Procedures und PL/SQL
PL/SQL
Cursor-for-Schleife (3)
declare
v_summe
number :=
cursor c_auftrag_pos
select *
from auftrag_pos
where auftrag_nr
begin
for v_auftrag_pos in
v_summe := v_summe
0;
(p_auftrag_nr in auftrag_pos.auftrag_nr%TYPE) is
= p_auftrag_nr;
c_auftrag_pos(4711) loop
+ v_auftrag_pos.anzahl *
v_auftrag_pos.preis;
end loop;
end;
Objektrelationale Datenbanksysteme — Hochschule Bonn-Rhein-Sieg, SS 2010
79
3. Stored Procedures und PL/SQL
PL/SQL
Dynamische Cursor-for-Schleife
Statt Cursor-Deklaration direkte Formulierung der SQL-Anfrage im Schleifenkopf!
declare
v_summe number := 0;
begin
for v_auftrag_pos in (select * from auftrag_pos) loop
v_summe := v_summe + v_auftrag_pos.anzahl *
v_auftrag_pos.preis;
end loop;
end;
Objektrelationale Datenbanksysteme — Hochschule Bonn-Rhein-Sieg, SS 2010
80
3. Stored Procedures und PL/SQL
PL/SQL
Cursor-Attribute
• %FOUND
War der letzte Fetch erfolgreich?
• %NOTFOUND
War der letzte Fetch nicht erfolgreich?
• %ROWCOUNT
Liefert die Anzahl der mit Fetch gelesenen Tupel.
• %ISOPEN
Ist der Cursor geöffnet?
Objektrelationale Datenbanksysteme — Hochschule Bonn-Rhein-Sieg, SS 2010
81