PLSQL - IMN/HTWK

PL/SQL – Die prozedurale
Erweiterungssprache Zu SQL
von
Andreas Schulz
[email protected]
[email protected]
Übersicht
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
Was ist PL/SQL?
Aufbau eines PL/SQL-Programms
Datentypen
Bezeichner
Kontrollstrukturen
Fehlerbehandlung
Unterprogramme
Collections, Records und Objekte
Packages
Cursor
2
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.
3







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)
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
5
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
6
2. Aufbau eines PL/SQLProgramms I



Blockstrukturiert
Blöcke: Prozeduren, Funktionen, anonyme
Blöcke
Drei Teile:
[DECLARE
-- Deklarationsteil]
BEGIN
-- Ausführungsteil
[EXCEPTION
-- Ausnahmebehandlungsteil]
END;
7
Aufbau eines PL/SQLProgramms II



Unterblöcke im Ausführungs- und
Ausnahmebehandlungsteil
Lokale Unterprogramme im
Deklarationsteil definierbar
Nur vom Block aufrufbar, in dem
definiert
8
3. Datentypen


SQL-Datentypen wie CHAR, DATE oder
NUMBER
PL/SQL-Datentypen wie BOOLEAN oder
BINARY_INTEGER
9
Überblick über PL/SQLDatentypen
Skalare Datentypen
BINARY_INTEGER
DEC
DECIMAL
DOUBLE PRECISION
FLOAT
INT
INTEGER
NATURAL
NATURALN
NUMBER
NUMERIC
PLS_INTEGER
POSITIVE
POSITIVEN
REAL
SIGNTYPE
SMALLINT
Zusammengesetzte Datentypen
CHAR
CHARACTER
LONG
LONG RAW
NCHAR
NVARCHAR2
RAW
ROWID
STRING
UROWID
VARCHAR
VARCHAR2
BOOLEAN
DATE
RECORD
TABLE
VARRAY
Referenzen
REF CURSOR
REF object_type
LOB Datentypen
BFILE
BLOB
CLOB
NCLOB
10
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
11

BINARY_INTEGER vs. PLS_INTEGER:




SIGNTYPE ist Tristate-Logik
CHAR-Datentyp (einfaches Zeichen):

Einfache Hochkommas
Als Zahlen in Berechnungen (A := 9 * ‘8‘;)

Implizite Umwandlung


Operationen mit BINARY_INTEGER langsamer
Überlaufexception bei PLS_INTEGER
CHAR-Datentyp (String):


Apostrophe zum Quotieren
‘Don‘‘t leave without saving your
work!‘
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;
13
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;
14
Erweiterte Funktionalitäten

DEFAULT:



NOT NULL:


acct_id INTEGER(4) NOT NULL := 9999;
%TYPE:




blood_type CHAR DEFAULT ‘0‘;
blood_type CHAR := ‘0‘;
credit REAL(7, 2);
debit credit%TYPE;
Datentyp einer Variable oder Tabellenspalte
%ROWTYPE
15


Vorsicht bei Variablen, die gleichen Namen
haben, wie Spaltennamen von Tabellen
Beispiel:
DECLARE
ename VARCHAR2(10) := ‘KING‘;
BEGIN
DELETE FROM emp WHERE ename = ename;

Besser:
<<main>>
DECLARE
ename VARCHAR2(10) := ‚KING‘;
BEGIN
DELETE
FROM
emp
WHERE
ename
main.ename;
=
Wertzuweisungen
Wertzuweisungszeichen: :=
1.




tax := price * taxe_rate;
bonus := current_salary * 0.10;
amount := TO_NUMBER(SUBSTR(‘750
dollars‘, 1, 3);
valid := FALSE;
Datenbankwerte in Variable selecten
2.

SELECT sal * 0.10 INTO Bonus FROM
emp WHERE empno = emp_id;
17
4. Bezeichner

max. 30 Zeichen
Reservierte Wörter (z. B. BEGIN und
END)

vordefinierte Bezeichner

z. B.: Exception INVALID_NUMBER
18
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 ...
19
5. Kontrollstrukturen



Conditional Control (IF-THEN-ELSE)
Iterative Control (LOOP)
Sequential Control (GOTO)
20
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
21
Iterative Controls II



CURSOR-FOR-LOOP
WHILE-LOOP
EXIT WHEN
LOOP
. . .
total := total + salary;
EXIT WHEN total > 25000;
END LOOP;
22
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;
23
6. Fehlerbehandlung






bei Fehler Exception ausgelöst
Reaktion in Ausnahmebehandlungsblock
Funktionen SQLCODE und SQLERRM
Interne Ausnahmefehler:
ZERO_DIVIDE
Nutzerdefinierte Ausnahmefehler:
RAISE
EXCEPTION_INIT
24
DECLARE
out_of_stock
EXCEPTION;
number_on_hand NUMBER(4);
BEGIN.
...
IF number_on_hand < 1 THEN
RAISE out_of_stock;
END IF;
EXCEPTION
WHEN out_of_stock THEN
-- Fehlerbehandlung
WHEN OTHERS THEN
...
END;
DECLARE
out_of_balance EXCEPTION;
BEGIN
...
BEGIN ---------- Unterblock beginnt
...
IF ... THEN
RAISE out_of_balance;
END IF;
EXCEPTION
WHEN out_of_balance THEN
-- Fehlerbehandlung
RAISE;
-- Wiederauslösung
END; ------------ Unterblock endet
EXCEPTION
WHEN out_of_balance THEN
-- andere Fehlerbehandlung für Exc.
...
7. Unterprogramme



Prozeduren und Funktionen
Unterprogrammblöcke mit Einteilungen
Funktionen mind. 1 RETURNAnweisungen (PROGRAM_ERROR)
27
Syntax
{FUNCTION|PROCEDURE} name [(parameter[,
parameter, ...])] RETURN datatype IS
[local declarations]
BEGIN
executable statements
[EXCEPTION
exception handlers]
END [name];
28
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
29
Positionelle vs. Benannte
Notation
DECLARE
acct INTEGER;
amt REAL;
PROCEDURE credit_acct (acct_no INTEGER,
amount REAL) IS ...
BEGIN
credit_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
30
Ein- und Ausgabeparameter





IN (Standard), OUT und IN OUT
IN-Parameter wie Konstante
Keine Referenz bei OUT und IN OUT
Referenz durch NOCOPY
Beispiel:
DECLARE
TYPE Staff IS VARRAY(200) OF
Employee;
PROCEDURE reorganize (my_staff IN OUT
NOCOPY Staff) IS ...
31
Stored Subprograms



Werden in den Datenbanken
gespeichert
CREATE PROCEDURE
CREATE FUNCTION
32
Aufruf von UP anderer
Programmiersprachen I
import java.sql.*;
import oracle.jdbc.driver.*;
public class Adjuster {
public static void raiseSalary (int
empNo, float percent) throws
SQLException {
Connection conn = new
OracleDriver().defaultConnection();
33
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) { …
34
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)';
35
8. Collections, Records und
Objekte



Felder, Listen, Bäume, etc.
In PL/SQL Datentypen TABLE und
VARRAY
Ermöglichen indizierte Tabellen und
variable Felder
36
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]
37
Collections II

Indizierte Tabellen:



Varrays




Deklaration: TYPE type_name IS TABLE OF
element_type [NOT NULL] INDEX BY
BINARY_INTEGER;
Weniger Datentypen möglich
Begrenzte maximale Größe
Bei Deklaration NULL
Konstruktor
Rückgabewerte bei Funktionen
38
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
39
Records






Analog Records anderer PS
Punktnotation
%ROWTYPE
Einlesen aus Tabelle
Keine Speicherung in Datenbanken
DECLARE
TYPE TimeRec IS RECORD (hours
SMALLINT, minutes SMALLINT);
40
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)
);
41
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
42
Vorteile


Modularität und Datenkapselung
Bessere Performance: Laden des
gesamten Packages
43
Vordefinierte Packages

STANDARD:








Definiert PL/SQL-Umgebung
Funktionen zur Berechnung
DBMS_Standard
DBMS_ALERT
DBMS_OUTPUT
DBMS_PIPE
UTL_FILE
UTL_HTTP
44
10. Cursor



Temporäre Speicherung einer SELECTAnweisung
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;
45
Attribute I

%FOUND:






Wenn Cursor geöffnet
Vor erstem FETCH NULL
FALSE, wenn letzter FETCH
fehlgeschlagen
%NOTFOUND
%ISOPEN
%ROWCOUNT: Anzahl Zeilen bei FETCH
46
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;
47