Post on 05-Apr-2015
transcript
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;