PL/SQL – Die prozedurale
Erweiterungssprache Zu SQL
von
Andreas Schulz
aschulz@imn.htwk-leipzig.de
2
Übersicht
1. Was ist PL/SQL?
2. Aufbau eines PL/SQL-Programms
3. Datentypen
4. Bezeichner
5. Kontrollstrukturen
6. Fehlerbehandlung
7. Unterprogramme
8. Collections, Records und Objekte
9. Packages
10. Cursor
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)
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
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
3. Datentypen
SQL-Datentypen wie CHAR , DATE oder NUMBER
PL/SQL-Datentypen wie BOOLEAN oder
BINARY_INTEGER
10
Überblick über PL/SQL- Datentypen
BINARY_INTEGER DEC
DECIMAL
DOUBLE PRECISION FLOAT
INT
INTEGER NATURAL NATURALN NUMBER NUMERIC PLS_INTEGER POSITIVE POSITIVEN REAL
SIGNTYPE SMALLINT
CHAR
CHARACTER LONG
LONG RAW NCHAR
NVARCHAR2 RAW
ROWID STRING UROWID VARCHAR VARCHAR2
BOOLEAN DATE
Skalare Datentypen Zusammengesetzte Datentypen RECORD
TABLE VARRAY
Referenzen REF CURSOR REF object_type
LOB Datentypen BFILE
BLOB CLOB NCLOB
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!‘
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;
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:
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
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
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 )
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;
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
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
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.
...
END;
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]
BEGIN
executable statements [EXCEPTION
exception handlers]
END [name];
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 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
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 ...
32
Stored Subprograms
Werden in den Datenbanken gespeichert
CREATE PROCEDURE
CREATE FUNCTION
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();
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) { …
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
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
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);
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
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
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
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;
