PL/SQL

PL/SQL
- Kurze Einführung -
Was fehlt noch?
• Konzept einer PL/SQL-Sprache in
DB ausführlicher
• PL/SQL und Ablauf
• Konkrete Beispiele
15. April 2004
Übung Data Warehousing:
PL/SQL
2
PL/SQL
• .. ist eine Oracle-eigene,
prozedurale Programmiersprache
• Sämtliche bekannten Konzepte
können verwendet werden:
Funktionen/ Prozeduren,
Abfragen/bedingte Ausführung,
Schleifen, etc.
15. April 2004
Übung Data Warehousing:
PL/SQL
3
Konzept
• Tight integration
– DDL / DML: einfach & effizient
• Stored procedures
– Verwendung in SQL Statements
– dynamisches SQL
• Security
– Kein direkter Zugriff durch Clients
– User benutzen stored procedures
15. April 2004
Übung Data Warehousing:
PL/SQL
4
Programmdateien
• Programme können in Dateien
abgelegt werden
• Ausführung eines Programmes in
SQL*Plus:
„@meinprogramm{.sql}“
• Programmdateien müssen mit
einer Leerzeile enden
15. April 2004
Übung Data Warehousing:
PL/SQL
5
PL/SQL-Programme
• .. bestehen aus Blöcken, welche
ineinander verschachtelt sein können:
DECLARE
.. /* Variablen-&Typdeklarationen */
BEGIN
.. /* Programmcode, Funktionen .. */
EXCEPTION
.. /* Exception-Handling */
END;
15. April 2004
Übung Data Warehousing:
PL/SQL
6
SQL in PL/SQL
• Erlaubte SQL-Statements:
– select, insert, delete, update +
Transaktionen
• Nicht (direkt) erlaubt
– create, drop, alter
• SELECT hat eine eigene, abgewandelte
Form:
select <attribut> into <variable>
from <tabelle> where <bedingung>;
15. April 2004
Übung Data Warehousing:
PL/SQL
7
Variablen und Datentypen
• Alle von Oracle unterstützen Typen
+ NUMBER, BOOLEAN
• Ermittlung zur Compilezeit möglich
DECLARE
name
VARCHAR(20);
gruppe
NUMBER;
punktezahl Gruppen.punkte%TYPE;
15. April 2004
Übung Data Warehousing:
PL/SQL
8
Zuweisungen & SQL
DECLARE
gruppe
Gruppen.name%TYPE;
punktezahl
Gruppen.punkte%TYPE;
BEGIN
SELECT name,punkte INTO gruppe,
punktezahl FROM Gruppen
WHERE punkte <= 10 FOR UPDATE;
punktezahl := punktezahl + 1;
INSERT INTO Gruppen VALUES (gruppe,
punktezahl);
END;
15. April 2004
Übung Data Warehousing:
PL/SQL
9
Kontrollflüsse
• LOOP EXIT WHEN <B> ... END LOOP;
• WHILE <B> LOOP ... END LOOP;
• FOR <V> IN <C1>..<C2> LOOP
... END LOOP;
• IF <B> THEN ... ELSIF <B2> THEN ...
ELSE ... END IF;
15. April 2004
Übung Data Warehousing:
PL/SQL
10
Cursor ..
• Problem bei erwähnten
Zuweisungen von Tabellenwerten:
funktioniert nur bei single-rowselect
• Abhilfe: die sogenannten Cursor
• arbeiten sämtliche Tupel aus dem
Ergebnis nacheinander ab
15. April 2004
Übung Data Warehousing:
PL/SQL
11
.. Cursor ..
DECLARE
CURSOR GruppenCursor IS
SELECT name,punkte
FROM Gruppen WHERE punkte <= 10
FOR UPDATE;
BEGIN
OPEN Gruppencursor;
<do something>
CLOSE GruppenCursor;
END;
15. April 2004
Übung Data Warehousing:
PL/SQL
12
.. Cursor
LOOP
FETCH GruppenCursor INTO gruppe,punktezahl;
EXIT WHEN GruppenCursor%NOTFOUND;
punktezahl := punktezahl * 2;
DELETE FROM Gruppen WHERE CURRENT
OF GruppenCursor;
INSERT INTO Gruppen VALUES
(gruppe,punktezahl);
END LOOP;
15. April 2004
Übung Data Warehousing:
PL/SQL
13
Prozeduren & Funktionen
CREATE PROCEDURE GruppeEinfuegen(
gruppe
Gruppen.name%TYPE,
punktezahl Gruppen.punkte%TYPE) AS
BEGIN
/* .. */
END GruppeEinfuegen;
/* Hauptprogramm */
BEGIN
/* .. */
GruppeEinfuegen('group1',0);
/* .. */
END;
15. April 2004
Übung Data Warehousing:
PL/SQL
14
Prozeduren & Funktionen
CREATE FUNCTION Quadriere(zahl NUMBER)
RETURN NUMBER AS
DECLARE q NUMBER;
BEGIN
q := zahl * zahl;
RETURN zahl;
END Quadriere;
/* Hauptprogramm */
DECLARE r NUMBER;
BEGIN
r := Quadriere(7);
END;
15. April 2004
Übung Data Warehousing:
PL/SQL
15
Prozeduren & Funktionen
• Anzeige aller Procedures/Functions:
select object_type, object_name
from user_objects
where object_type = 'PROCEDURE'
or object_type = 'FUNCTION';
• Löschen einer Procedure/Function:
drop function <funktions_name>;
• Überschreiben:
CREATE OR REPLACE PROCEDURE .. ;
15. April 2004
Übung Data Warehousing:
PL/SQL
16
Fehlermeldungen
• Fehlermeldungen sind i.d.R. leider
wenig aussagekräftig
• genauere Beschreibung über
show errors procedure <name>;
• Anzeige des letzten Fehlers:
SHO ERR;
• Positionsangaben oft ungenau!
15. April 2004
Übung Data Warehousing:
PL/SQL
17
Beispiel
DECLARE qty_on_hand NUMBER(5);
BEGIN
SELECT quantity INTO qty_on_hand FROM inventory
WHERE product = 'TENNIS RACKET'‚
FOR UPDATE OF quantity;
IF qty_on_hand > 0 THEN
UPDATE inventory SET quantity = quantity – 1
WHERE product = 'TENNIS RACKET';
INSERT INTO purchase_record
VALUES ('Tennis racket purchased', SYSDATE);
ELSE
INSERT INTO purchase_record
VALUES ('Out of tennis rackets', SYSDATE);
END IF;
COMMIT;
END;
15. April 2004
Übung Data Warehousing:
PL/SQL
18