PL/SQL – Überblick

Christian Silberbauer
1
PL/SQL – Überblick
1. SQL-Anweisungen
CREATE TABLE <Tabellenname> (<Feld> <Typ>, <Feld2> <Typ2>, ...)
CREATE TABLE <Tabellenname> AS SELECT * FROM <Tabellenname>
DROP TABLE <Tabellenname>
INSERT INTO <Tabellenname> [(<Spaltenliste>)] VALUES (<Werteliste>)
UPDATE <Tabellenname> SET <Spaltenname> = <Wert> WHERE <Bedingung>
DELETE FROM <Tabellenname> [WHERE <Bedingung>]
SELECT DISTINCT <Spaltenname> FROM <Tabellenname>
SELECT <Spaltenname> INTO <Variablenname> FROM <Tabellenname>
CONSTRAINT <Constraint-Name> {PRIMARY KEY | UNIQUE | NOT NULL |
REFERENCES <Fremd-Tabelle> [(<Fremdfeld1>, <Fremdfeld2>)]}
2. SQL-Funktionen
AVG
MAX
MIN
SUM
COUNT
Durchschnitt
Maximalwert
Minimalwert
Summe
Anzahl
TO_DATE
TO_CHAR
TO_NUMBER
3. Datentypen
Zahlen
Zeichen
Datum
NUMBER
NUMBER(n)
NUMBER(m,n)
CHAR(n)
VARCHAR2(n)
DATE
Gleitkommazahl
Ganze Zahl
Festkommazahl
Zeichenkette (genau n Bytes)
Zeichenkette (max. n Bytes)
Datumsfeld (inkl. Uhrzeit)
4. PL/SQL - Befehle / Funktionen
CHECK (<Bedingung>)
MOD (z,d)
DBMS_OUTPUT.ENABLE
DBMS_OUTPUT.PUT_LINE(<String>)
DBMS_OUTPUT.PUT(<String>)
DBMS_OUTPUT.NEW_LINE
SET SERVEROUTPUT ON
COMMIT
SHOW ERRORS
Access: * und ? - Oracle: % und _
SQLCODE
SQLERRM
Prüffunktion für Feldinhalte in Tabellen
Modulodivision
Textzeile ausgeben (mit Zeilenvorschub)
Wert in Zeilenpuffer geben
Expliziter Zeilenvorschub
Ermöglicht es Texte am Bildschirm auszugeben
SQL-Befehl abschließen
Fehlerliste beim compilieren gespeicherter Prozeduren
Wildcards in Strings
Aktuelle Fehlernummer durch Oracle-Exception
Aktuelle Fehlermeldung durch Oracle-Exception
5. Kontrollstrukturen
IF - Anweisung
LOOP - Anweisung
WHILE - LOOP - Anweisung FOR - LOOP - Anweisung
IF <Bedingung> THEN
<Befehl>;
ELSIF <Bedingung>
<Befehl>;
ELSE
<Befehl>;
LOOP
<Befehl>;
EXIT WHEN <Bedingung>;
END LOOP;
WHILE <Bedingung> LOOP FOR <Zähler> IN
<Befehl>;
<Anfangswert>..<Endwert>
END LOOP;
LOOP
<Befehl>;
END LOOP;
Christian Silberbauer
2
6. Programmstrukturen
Standard - PL/SQL - Skript
Gespeicherte Prozeduren
DECLARE
<Deklarationsteil>
CREATE OR REPLACE PROCEDURE <Procedure-Name>
(<Deklarationsteil>) IS
<Deklarationsteil>
PROCEDURE <Procedure-Name> (<Deklarationsteil>) IS BEGIN
<Deklarationsteil>
<Ausführungsteil>
BEGIN
END;
<Ausführungsteil>
Aufruf der Prozedur: EXECUTE <Procedure-Name>
END;
FUNCTION <Function-Name> (<Deklartionsteil>)
RETURN <Datentyp> IS
<Deklarationsteil>
BEGIN
<Ausführungsteil>
RETURN <Rückgabewert>;
END;
BEGIN
<Ausführungsteil>
<Procedure-Name>;
<Variablenname>:=<Function-Name>(<Werteliste>);
<Ausführungsteil>
END;
Gespeicherte Funktionen
CREATE OR REPLACE FUNCTION <Function-Name>
(<Deklarationsteil>) RETURN <Datentyp> IS
<Deklarationsteil>
BEGIN
<Ausführungsteil>
END;
Aufruf der Prozedur in anderen Skripten oder in SQLAnweisungen.
7. Deklarationen
Variable definieren
<Variablenname> <Datentyp>;
Konstante definieren
<Variablenname> CONSTANT <Datentyp>;
Übernahme des Datentypen aus einer Tabelle
<Variablenname> <Tabellenname>.<Spaltenname>%TYPE;
Variable definieren, in der ein ganzer Datensatz gespeichert
wird
<Variablenname> <Tabellenname>%ROWTYPE;
Zugriff auf ein Element: <Variablenname>.<Spaltenname>
8. Exceptions
Syntax
Definition anwenderspezifischer Exceptions
EXCEPTION
WHEN <Exception-Name1> THEN
<Ausführungsteil>
WHEN <Exception-Name2> THEN
<Ausführungsteil>
WHEN OTHERS THEN
<Ausführungsteil>
Exception auslösen: RAISE <Exception-Name>
Exception abfangen: WHEN <Exception-Name> THEN ...
Fehlernummer und Fehlermeldung an Anwendungen weitergeben
WHEN <Exception-Name> THEN
RAISE_APPLICATION_ERROR(-20000,'Falsche Zahl!');
vordefinierte Exceptions
INVALID_NUMBER
NO_DATA_FOUND
TOO_MANY_ROWS
VALUE_ERROR
z.B. Konversion einer Zeichenkette in einen numerischen Wert schlug fehl (SQL-Anweisung)
z.B. durch ein SELECT wurden keine Sätze zurückgeliefert
Ein SELECT mit INTO liefert mehr als einen Datensatz zurück
Berechnungs- oder Konversionsfehler im Skript selbst (nicht durch SQL-Anweisung)
Christian Silberbauer
3
9. Cursor
Explizite Cursor
Initialisierung
Aktuellen Satz lesen
Cursor schließen
CURSOR <Cursor-Name> IS
SELECT <Spaltenname1> <Spaltenname2> FROM <Tabellenname>;
OPEN <Cursor-Name>;
FETCH <Cursor-Name> INTO <Variablenname1>, <Variablenname2>;
CLOSE <Cursor-Name>;
Implizite Cursor
Werden automatisch definiert und können über den Namen SQL angesprochen werden
Deklaration/Definition
Parametrisierte Cursor
Definition
CURSOR <Cursor-Name> (Variablenname Datentyp) IS
<SELECT-Anweisung unter Verwendung der Parameter>;
Besonderheit
Parameter werden durch den OPEN - Befehl übergeben
Cursor - Attribute
Cursor - For - Schleife
Definition
Bedeutung
Besonderheiten
%FOUND
%NOTFOUND
%ISOPEN
%ROWCOUNT
Wahr, wenn ein Fetch-Befehl auf einen gültigen Datensatz zeigt
Wahr, wenn ein Fetch-Befehl "hinter" einen Cursor greift
Wahr, wenn der Cursor geöffnet ist
Angaben der Zeilen, die mit OPEN in den Speicherbereich geladen sind
(Diese erhöhen sich also ständig!)
FOR <Satz-Name> IN <Cursor-Name> LOOP
<Befehl>;
END LOOP;
Alle Sätze eines Cursors werden angesprochen. Auf den OPEN - und den CLOSE - Befehl kann
verzichtet werden.
Eine Spalte wird über <Cursor-Name>.<Spaltenname> angesprochen
Zugriff auf alle Sätze im Speicherbereich über CURRENT OF
Bsp.: DELETE FROM <Tabellenname> WHERE CURRENT OF <Cursor-Name>
Cursor für Änderungen CURSOR <Cursor-Name> IS <SQL-Anweisung> FOR UPDATE;
10. Trigger
Syntax
Zugriff auf Spalten
Trigger-Ergeignisse
Trigger löschen
Trigger deaktivieren
Trigger aktivieren
CREATE OR REPLACE TRIGGER <Trigger-Name>
{BEFORE | AFTER} INSERT OR DELETE OR UPDATE ON <Tabellen-Name>
[FOR EACH ROW] [WHEN <Bedingung>]
BEGIN
<Ausführungsteil>
END;
Alter Wert := :old.<Spaltenname>;
Neuer Wert := :new.<Spaltenname>;
INSERTING, DELETING, UPDATING
DROP TRIGGER <Trigger-Name>
ALTER TRIGGER <Trigger-Name> disable;
ALTER TRIGGER <Trigger-Name> enable;
11. Wichtige Infos über Tabellen und Views
Alle Objekte
Tabellen
Tabellenaufbau
Constraints
Primärschlüssel
SELECT DISTINCT object_type FROM obj;
SELECT object_name FROM obj where object_type = 'TABLE';
DESC <Tabellenname>
SELECT * FROM user_constraints WHERE table_name = '<Tabellenname>';
bzw. SELECT constraint_name, constraint_type, search_condition FROM user_constraints
WHERE table_name = '<Tabellenname>';
SELECT * FROM user_cons_columns WHERE constraint_name = '<Primärschlüssel-Name>';