PL/SQL

- Kurze Einführung -

15. April 2004 Übung Data Warehousing: PL/SQL

2

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

3

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

4

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

5

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

6

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

7

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

8

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

9

Zuweisungen & SQLDECLARE 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

10

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

11

Cursor ..

• Problem bei erwähnten Zuweisungen von Tabellenwerten:

funktioniert nur bei single-row-select

• Abhilfe: die sogenannten Cursor• arbeiten sämtliche Tupel aus dem

Ergebnis nacheinander ab

15. April 2004 Übung Data Warehousing: PL/SQL

12

.. 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

13

.. CursorLOOP

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

14

Prozeduren & FunktionenCREATE 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

15

Prozeduren & FunktionenCREATE 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

16

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

17

Fehlermeldungen

• Fehlermeldungen sind i.d.R. leider wenig aussagekräftig

• genauere Beschreibung übershow errors procedure <name>;

• Anzeige des letzten Fehlers:SHO ERR;

• Positionsangaben oft ungenau!

15. April 2004 Übung Data Warehousing: PL/SQL

18

BeispielDECLARE qty_on_hand NUMBER(5);BEGIN

SELECT quantity INTO qty_on_hand FROM inventoryWHERE product = 'TENNIS RACKET'‚FOR UPDATE OF quantity;

IF qty_on_hand > 0 THENUPDATE inventory SET quantity = quantity – 1

WHERE product = 'TENNIS RACKET';INSERT INTO purchase_record

VALUES ('Tennis racket purchased', SYSDATE);ELSE

INSERT INTO purchase_recordVALUES ('Out of tennis rackets', SYSDATE);

END IF;COMMIT;

END;