PL/SQL – Die prozedurale Erweiterungssprache Zu SQL

vonAndreas Schulz

aschulz@imn.htwk-leipzig.deAndreas.Schulz@lpzm.siemens.de

2

Übersicht1. Was ist PL/SQL?2. Aufbau eines PL/SQL-Programms3. Datentypen4. Bezeichner5. Kontrollstrukturen6. Fehlerbehandlung7. Unterprogramme8. Collections, Records und Objekte9. Packages10. Cursor

3

1. Was ist PL/SQL? 4GL (Fourth-Generation Language) Plattformen abhängig von Oracle Moderne Features wie Datenkapselung,

Überladen, Ausnahmebehandlung, etc. SQL-Statements benutzen, um Daten zu

manipulieren Konstanten und Variablen deklarieren Prozeduren und Funktionen definieren

u. v. m.

Manipulation der Daten über Unterprogramme mit definierten Privilegien

Zugriff auf Tabelle nicht notwendig Einbettung in andere

Programmiersprachen wie C++, Java, ... Gespeichertes Unterprogramm In kompilierter Form Aufruf durch Datenbank-Trigger, andere

UP, Oracle-Präcompiler-Applikation Gemeinsamer Speicherbereich (1 Kopie

für mehrere User)

5

Limitierungen Optimiert für high-speed

Transaktionsprozesse Anzahl von Token (Bezeichner,

Schlüsselwörter, Operatoren, ...) begrenzt Spezifikationen: 32 KB Körper: 64 KB Komplexe SQL-Anweisungen Unterprogramme zerlegen Hostvariablen der Hochsprache

verwenden

6

Grundlagen Viele Regeln anderer Programmiersprachen Hier: Abweichungen Nicht case-sensitiv Statement-Indikator: ; Potenz-Operator: ** Stringverkettung: || @-Operator für Datenbanken Kommentare:

/* */: Mehrzeilenkommentare -- bist zum Rest der Zeile

7

2. Aufbau eines PL/SQL-Programms I Blockstrukturiert Blöcke: Prozeduren, Funktionen, anonyme

Blöcke Drei Teile:

[DECLARE-- Deklarationsteil]BEGIN-- Ausführungsteil[EXCEPTION-- Ausnahmebehandlungsteil]END;

8

Aufbau eines PL/SQL-Programms II Unterblöcke im Ausführungs- und

Ausnahmebehandlungsteil Lokale Unterprogramme im

Deklarationsteil definierbar Nur vom Block aufrufbar, in dem

definiert

9

3. Datentypen SQL-Datentypen wie CHAR, DATE

oder NUMBER PL/SQL-Datentypen wie BOOLEAN

oder BINARY_INTEGER

10

Überblick über PL/SQL-Datentypen

BINARY_INTEGERDECDECIMALDOUBLE PRECISIONFLOATINTINTEGERNATURALNATURALNNUMBERNUMERICPLS_INTEGERPOSITIVEPOSITIVENREALSIGNTYPESMALLINT

CHARCHARACTERLONGLONG RAWNCHARNVARCHAR2RAWROWIDSTRINGUROWIDVARCHARVARCHAR2

BOOLEAN

DATE

Skalare Datentypen Zusammengesetzte Datentypen

RECORDTABLEVARRAY

Referenzen

REF CURSORREF object_type

LOB Datentypen

BFILEBLOBCLOBNCLOB

11

Ausgewählte Datentypen Wahrheitswerte:

Dreiwertige Logik TRUE, FALSE, NULL (fehlender, unbekannter

oder nicht anwendbarer Wert) Nicht in Spalte einer Tabelle gelesen oder

geschrieben Datum:

Auch Tageszeit in Sekunden seit Mitternacht 1. Januar 4712 v. Chr. Bis 31. Dezember 9999

a. D. Berechnungen geben meist Anzahl Tage zurück

BINARY_INTEGER vs. PLS_INTEGER: Operationen mit BINARY_INTEGER langsamer Überlaufexception bei PLS_INTEGER

SIGNTYPE ist Tristate-Logik CHAR-Datentyp (einfaches Zeichen):

Einfache Hochkommas Als Zahlen in Berechnungen (A := 9 * ‘8‘;) Implizite Umwandlung

CHAR-Datentyp (String): Apostrophe zum Quotieren ‘Don‘‘t leave without saving your work!‘

13

Nutzerdefinierte Unterdatentypen SUBTYPE CHARACTER IS CHAR Eigene Unterdatentypen Wertebereich für Datentyp Kein neuer Datentyp Z. B.:

SUBTYPE BirthDate IS DATE NOT NULL;SUBTYPE Counter IS NATURAL;TYPE NameList IS TABLE OF VARCHAR2(10);SUBTYPE DutyRoster IS NameList;

14

Deklaration von ... Variablen:

birthday DATE; emp_count SMALLINT; i, j, k SMALLINT; -- nicht zulässig birthday DATE; äquivalent zu birthday DATE := NULL;

Konstanten: credit_limit CONSTANT REAL := 5000.00;

15

Erweiterte Funktionalitäten DEFAULT:

blood_type CHAR DEFAULT ‘0‘; blood_type CHAR := ‘0‘;

NOT NULL: acct_id INTEGER(4) NOT NULL := 9999;

%TYPE: credit REAL(7, 2); debit credit%TYPE; Datentyp einer Variable oder Tabellenspalte

%ROWTYPE

Vorsicht bei Variablen, die gleichen Namen haben, wie Spaltennamen von Tabellen

Beispiel:DECLAREename VARCHAR2(10) := ‘KING‘;

BEGINDELETE FROM emp WHERE ename = ename;

Besser:<<main>>DECLAREename VARCHAR2(10) := ‚KING‘;

BEGINDELETE FROM emp WHERE ename = main.ename;

17

Wertzuweisungen

1. Wertzuweisungszeichen: := tax := price * taxe_rate; bonus := current_salary * 0.10; amount := TO_NUMBER(SUBSTR(‘750

dollars‘, 1, 3); valid := FALSE;

2. Datenbankwerte in Variable selecten SELECT sal * 0.10 INTO Bonus FROM

emp WHERE empno = emp_id;

18

4. Bezeichner max. 30 Zeichen Reservierte Wörter (z. B. BEGIN

und END) vordefinierte Bezeichner

z. B.: Exception INVALID_NUMBER

19

Quotierte Bezeichner doppelte Hochkommas Verbotene Zeichen in Bezeichnern:

“*** header info ***“ “on/off“

Reservierte Bezeichner ansprechen SELECT acct, type, bal INTO ... SELECT acct, “TYPE“, bal INTO ... SELECT acct, “type“, bal INTO ...

20

5. Kontrollstrukturen Conditional Control (IF-THEN-ELSE)

Iterative Control (LOOP) Sequential Control (GOTO)

21

Iterative Controls I LOOP .. END LOOP FOR-LOOP:

FOR counter IN [REVERSE] lower_bound .. higher_bound LOOP

-- Sequence_of_statements

END LOOP; Keine Wertzuweisung für Zählvariable Explizite Deklarierung nicht nötig Keine Schrittweitenangabe

22

Iterative Controls II CURSOR-FOR-LOOP WHILE-LOOP EXIT WHEN

LOOP. . .total := total + salary;EXIT WHEN total > 25000;

END LOOP;

23

Sequential Control Verzweigung zu Sprungmarke Labels nur vor ausführbaren Befehl Verzweigungen in Unterblöcken

IF valid THEN..GOTO update_row; -- illegaler Sprung

ELSE..<<update_row>>UPDATE emp SET ..

END IF;

24

6. Fehlerbehandlung bei Fehler Exception ausgelöst Reaktion in

Ausnahmebehandlungsblock Funktionen SQLCODE und SQLERRM Interne Ausnahmefehler: ZERO_DIVIDE

Nutzerdefinierte Ausnahmefehler: RAISE

EXCEPTION_INIT

DECLAREout_of_stock EXCEPTION;number_on_hand NUMBER(4);

BEGIN....IF number_on_hand < 1 THEN

RAISE out_of_stock;END IF;

EXCEPTIONWHEN out_of_stock THEN

-- FehlerbehandlungWHEN OTHERS THEN

...END;

DECLAREout_of_balance EXCEPTION;

BEGIN...BEGIN ---------- Unterblock beginnt

...IF ... THEN

RAISE out_of_balance;END IF;

EXCEPTIONWHEN out_of_balance THEN

-- FehlerbehandlungRAISE; -- Wiederauslösung

END; ------------ Unterblock endetEXCEPTION

WHEN out_of_balance THEN-- andere Fehlerbehandlung für Exc.

...END;

27

7. Unterprogramme Prozeduren und Funktionen Unterprogrammblöcke mit

Einteilungen Funktionen mind. 1 RETURN-

Anweisungen (PROGRAM_ERROR)

28

Syntax{FUNCTION|PROCEDURE} name

[(parameter[, parameter, ...])] RETURN datatype IS[local declarations]

BEGINexecutable statements

[EXCEPTIONexception handlers]

END [name];

29

Parameter-DEFAULT-Werte Beispiel:

PROCEDURE create_dept (new_dname CHAR DEFAULT 'TEMP', new_loc CHAR DEFAULT 'TEMP') IS …

Aufruf: create_dept; create_dept('MARKETING'); create_dept('MARKETING', 'NEW YORK'); create_dept('NEW YORK'); create_dept(, 'NEW YORK'); -- illegal

30

Positionelle vs. Benannte NotationDECLAREacct INTEGER;amt REAL;PROCEDURE credit_acct (acct_no INTEGER, amount REAL) IS ...

BEGINcredit_acct(acct, amt); credit_acct(amount=>amt,acct_no=>acct); credit_acct(acct_no=>acct,amount=>amt); credit_acct(acct, amount => amt);credit_acct(acct_no => acct, amt);-- letztes Beispiel illegal

31

Ein- und Ausgabeparameter IN (Standard), OUT und IN OUT IN-Parameter wie Konstante Keine Referenz bei OUT und IN OUT Referenz durch NOCOPY Beispiel:

DECLARETYPE Staff IS VARRAY(200) OF Employee;PROCEDURE reorganize (my_staff IN OUT NOCOPY Staff) IS ...

32

Stored Subprograms Werden in den Datenbanken

gespeichert CREATE PROCEDURE CREATE FUNCTION

33

Aufruf von UP anderer Programmiersprachen Iimport java.sql.*;

import oracle.jdbc.driver.*;

public class Adjuster {

public static void raiseSalary (int empNo, float percent) throws

SQLException {

Connection conn = new OracleDriver().defaultConnection();

34

Aufruf von UP anderer Programmiersprachen II

String sql = "UPDATE emp SET sal = sal * ? WHERE empno = ?";

try {PreparedStatement pstmt =

conn.prepareStatement(sql);pstmt.setFloat(1, (1 + percent

/ 100));pstmt.setInt(2, empNo);pstmt.executeUpdate();pstmt.close();

} catch (SQLException e) { …

35

Aufruf von UP anderer Programmiersprachen III

Deklaration der Prozedur:CREATE PROCEDURE

raise_salary (empno NUMBER, pct NUMBER) AS LANGUAGE JAVA NAME

'Adjuster.raiseSalary(int, float)';

36

8. Collections, Records und Objekte Felder, Listen, Bäume, etc. In PL/SQL Datentypen TABLE und VARRAY

Ermöglichen indizierte Tabellen und variable Felder

37

Collections I Geordnete Gruppen von Elementen

eines Datentyps TABLE Nested Tables:

Elemente out-of-line in anderen Tabellen Größe dynamisch Elemente können herausgelöscht werden Deklaration: TYPE type_name IS TABLE OF element_type [NOT NULL]

38

Collections II Indizierte Tabellen:

Deklaration: TYPE type_name IS TABLE OF element_type [NOT NULL] INDEX BY BINARY_INTEGER;

Weniger Datentypen möglich Varrays

Begrenzte maximale Größe Bei Deklaration NULL Konstruktor

Rückgabewerte bei Funktionen

39

Collections III Können nicht verglichen werden Spezielle Methoden:

EXISTS: if courses.EXISTS(i) THEN ... COUNT: tatsächliche Anzahl der Elemente LIMIT: maximalste Anzahl oder NULL FIRST und LAST PRIOR und NEXT EXTEND TRIM DELETE

40

Records Analog Records anderer PS Punktnotation %ROWTYPE Einlesen aus Tabelle Keine Speicherung in Datenbanken DECLARE

TYPE TimeRec IS RECORD (hours SMALLINT, minutes SMALLINT);

41

Objektdatentypen Attribute Methoden Beispiel:

CREATE TYPE Bank_Account AS OBJECT (Acct_number INTEGER(5),balance REAL,MEMBER PROCEDURE open (amount IN REAL),MEMBER PROCEDURE close (num IN INTEGER, amount OUT REAL)

);

42

9. Packages Objektschema, um logisch

zusammengehörige Daten und Unterprogramme zu gruppieren

2 Teile: Spezifikation: Schnittstelle zu

Applikationen Körper:

Vollständige Definition von Cursor und Ups Private Deklarationen optional

43

Vorteile Modularität und Datenkapselung Bessere Performance: Laden des

gesamten Packages

44

Vordefinierte Packages STANDARD:

Definiert PL/SQL-Umgebung Funktionen zur Berechnung

DBMS_Standard DBMS_ALERT DBMS_OUTPUT DBMS_PIPE UTL_FILE UTL_HTTP

45

10. Cursor Temporäre Speicherung einer SELECT-

Anweisung Keine, eine oder mehrere Zeilen 3 Kommandos:

Öffnen: OPEN Bearbeiten: FETCH Schließen: CLOSE

DECLARE CURSOR c1 IS SELECT empno, ename, job FROM emp WHERE deptno = 20;

46

Attribute I %FOUND:

Wenn Cursor geöffnet Vor erstem FETCH NULL FALSE, wenn letzter FETCH

fehlgeschlagen %NOTFOUND %ISOPEN %ROWCOUNT: Anzahl Zeilen bei FETCH

47

Attribute II Auch bei INSERT, UPDATE, DELETE

und SELECT INTO Beispiel:

DELETE FROM emp WHERE ...IF SQL%ROWCOUNT > 10 THEN-- more than 10 rows were deleted...

END IF;