3 Der Ausführungsteil eines PL/SQL

3
Der Ausführungsteil eines PL/SQL-Blocks
3.1
3.2
3.3
3.4
3.5
3.6
3.7
3.8
3.9
Aufbau des Ausführungsteils .......................................................... 3-4
Zuweisungen .................................................................................. 3-6
Funktionen innerhalb von PL/SQL.................................................. 3-8
3.3.1 .... Erlaubte SQL-Funktionen .................................................. 3-8
3.3.2 .... Nicht erlaubte SQL-Funktionen........................................ 3-10
Reguläre Ausdrücke ..................................................................... 3-12
3.4.1 .... Allgemein ......................................................................... 3-12
3.4.2 .... Einsatz in ORACLE 10g................................................... 3-14
3.4.3 .... REGEXP_Funktionen ...................................................... 3-14
3.4.4 .... Die Metazeichen der Regulären Ausdrücke..................... 3-18
Operatoren in PL/SQL .................................................................. 3-20
Der SELECT-Befehl in PL/SQL ..................................................... 3-22
DML-Befehle in PL/SQL ............................................................... 3-25
Erweiterungen der DML-Befehle in ORACLE 10g ........................ 3-28
3.8.1 ....INSERT Statement .......................................................... 3-28
3.8.2 ....UPDATE Statement ......................................................... 3-28
3.8.3 ....DELETE Statement.......................................................... 3-28
Die GOTO-Anweisung.................................................................... 3-30
1.2.066 / 4053
3-1
3
Der Ausführungsteil eines PL/SQL-Blocks
3.10 Bedingte Kompilierung ................................................................. 3-32
3-2
1.2.066 / 4053
Der Ausführungsteil eines PL/SQL-Blocks
1.2.066 / 4053
3
3-3
3
Der Ausführungsteil eines PL/SQL-Blocks
3
Der Ausführungsteil eines PL/SQL-Blocks
3.1
Aufbau des Ausführungsteils
Dieser Teil beinhaltet das eigentliche Programm. Hier finden sich Kontrollkonstrukte wie Schleifen, bedingte Anweisungen, Zuweisungen und
SQL-Befehle. Der Block wird eingeleitet durch das Schlüsselwort
‘BEGIN’ und beendet durch ‘END;’.
Im Ausführungsteil dürfen PL/SQL-Anweisungen, Funktionen sowie alle
SQL-Befehle mit Ausnahme von DDL-Befehlen (Data Definition Language: CREATE, ALTER, DROP, RENAME, TRUNCATE) und DCL-Befehlen
(Data Control Language: GRANT, REVOKE) verwendet werden.
DDL-Befehle sind allerdings leicht einzubauen durch Verwendung von
EXECUTE IMMEDIATE sql_statement;
Weiterhin nicht erlaubt sind Befehle aus dem SYSDBA Bereich wie:
STARTUP
SHUTDOWN
RECOVER DATABASE
3-4
1.2.066 / 4053
Der Ausführungsteil eines PL/SQL-Blocks
Allgemeine Eigenschaften des
Ausführungsteils
3
3
ƒ PL/SQL-Befehle:
ƒ Bedingungen
ƒ Schleifen
ƒ RAISE (Auslösen einer Exception)
ƒ EXIT WHEN
ƒ NULL
ƒ Zuweisungen
ƒ SQL-Befehle:
ƒ INSERT
ƒ UPDATE
ƒ DELETE
ƒ SELECT ... INTO
ƒ EXECUTE IMMEDIATE ddl_sql_statement
ƒ Transaktionssteuerungsbefehle:
ƒ COMMIT
ƒ SAVEPOINT
ƒ ROLLBACK
ƒ SET TRANSACTION READ ONLY
ƒ LOCK TABLE
www.unilog.integrata.de
www.unilog-integrata.de
1.2.066 / 4053
4053 / 1.2.036
Folie 2
3-5
3
3.2
Der Ausführungsteil eines PL/SQL-Blocks
Zuweisungen
Variablen können im Ausführungsteil (und im Fehlerbehandlungsteil)
beliebig oft Werte zugewiesen werden, wobei die Variable stets den zuletzt zugewiesenen Wert beinhaltet. Die Zuweisung geschieht in der
Regel mit dem Zuweisungsoperator ‘:=‘ in der Form:
variablename := ausdruck;
Der Ausdruck kann ein einzelner Wert sein, eine Funktion, eine andere
Variable oder auch eine arithmetische Operation.
Beispiele:
var_a := 17;
var_b := INITCAP('asterix');
var_c := var_a + 11;
var_d := sysdate;
var_e := pack.var;
Weitere Möglichkeiten der Zuweisung sind der SELECT … INTO-Befehl
und der FETCH INTO-Befehl bei Cursoren, die später behandelt werden.
3-6
1.2.066 / 4053
Der Ausführungsteil eines PL/SQL-Blocks
3
Zuweisungen
3
ƒ möglich im Deklarationsteil (als Default-Wert)
ƒ beliebig oft erlaubt im Ausführungs- und Exception-Teil
ƒ durch den Zuweisungsoperator:
variable := ausdruck;
ƒ durch SELECT INTO
ƒ durch FETCH INTO (bei Cursor)
ƒ Beispiele:
var_a := 17;
var_b := INITCAP('asterix');
var_c := var_a + 11;
www.unilog.integrata.de
www.unilog-integrata.de
1.2.066 / 4053
4053 / 1.2.036
Folie 3
3-7
3
3.3
Der Ausführungsteil eines PL/SQL-Blocks
Funktionen innerhalb von PL/SQL
Eine Funktion kann durch ihren Funktionsnamen und der Angabe von
zu übergebenden Parametern aufgerufen werden. Jede Funktion liefert
ein Ergebnis als Rückgabewert wieder. Dieser Rückgabewert wird üblicherweise einer Variablen zugeordnet, wie im zweiten vorherigen Beispiel.
3.3.1
Erlaubte SQL-Funktionen
ORACLE bietet im SQL-Bereich eine Vielzahl von Funktionen, die fast
alle auch in PL/SQL-Anweisungen zur Verfügung stehen:
– Zeichenkettenfunktionen (Single Row), z.B.: CONCAT
SUBSTR, INSTR
LOWER, UPPER, INITCAP
LPAD, RPAD
LTRIM, RTRIM, TRIM
REPLACE, REGEXP_REPLACE (10g)...
– Numerische Funktionen (Single Row), z. B.: MOD
ROUND, TRUNC
POWER, LOG, ...
– Datumsfunktionen, z. B.: ADD_MONTHS, MONTHS_BETWEEN
NEXT_DAY, LAST_DAY
SYSDATE
TRUNC...
– Konvertierungsfunktionen, z. B.: TO_CHAR (für NUMBER und DATE)
TO_NUMBER
TO_DATE
TO_LOB (LONG zu CLOB)
NVL
ISNULL (seit 9i)
Diese SQL-Funktionen sind auch in PL/SQL-Anweisungen uneingeschränkt verfügbar.
3-8
1.2.066 / 4053
Der Ausführungsteil eines PL/SQL-Blocks
Erlaubte SQL-Funktionen
ƒ
ƒ
ƒ
ƒ
3
3
Zeichenkettenfunktionen (Single Row)
Numerische Funktionen (Single Row)
Datumsfunktionen
Konvertierungsfunktionen
ƒ Beispiele:
v_rest
v_name
v_friday
v_date
v_str
www.unilog.integrata.de
www.unilog-integrata.de
1.2.066 / 4053
:=
:=
:=
:=
MOD(v_xyz, 5);
LOWER(v_ename);
NEXT_DAY(SYSDATE, 'FRIDAY');
TO_DATE('13.10.1962',
'DD.MM.YYYY');
:= TO_CHAR(v_date,
'DD.Month YYYY');
4053 / 1.2.066
Folie 4
3-9
3
3.3.2
Der Ausführungsteil eines PL/SQL-Blocks
Nicht erlaubte SQL-Funktionen
• Gruppenfunktionen (SUM, AVG, MIN, MAX..)
• Analytische Funktionen (PARTITION, DIMENSION,OVER, …)
• DECODE
Diese Funktionen sind in PL/SQL-Anweisungen nicht zulässig, in SQLAnweisungen innerhalb eines PL/SQL-Blocks dagegen sind sie erlaubt.
nicht zulässig:
v_sum := SUM (sal_table);
zulässig:
SELECT SUM(sal) INTO v_sum FROM emp;
3-10
1.2.066 / 4053
Der Ausführungsteil eines PL/SQL-Blocks
Nicht erlaubte SQL-Funktionen
3
3
ƒ Nicht erlaubte SQL-Funktionen:
ƒ Gruppenfunktionen
ƒ DECODE
ƒ Beispiel:
-- nicht erlaubt!!!
v_sum := SUM(sal_Table);
-- erlaubt
SELECT SUM(sal)
INTO v_sum
FROM emp;
www.unilog.integrata.de
www.unilog-integrata.de
1.2.066 / 4053
4053 / 1.2.036
Folie 5
3-11
3
Der Ausführungsteil eines PL/SQL-Blocks
3.4
Reguläre Ausdrücke
3.4.1
Allgemein
Reguläre Ausdrücke (engl.: regular expressions) sind ein leistungsstarkes Werkzeug zur Verarbeitung von Texten und Daten in der ORACLE
Version 10g.
Mit regulären Ausdrücken können sehr komplexe und subtile Textprobleme elegant beschrieben und gelöst werden. Reguläre Ausdrücke sind
in der UNIX Welt (egrep, vi, awk, emacs, …) seit langem verbreitet.
Bsp.:
Suche alle Zeilen aus der Datei protokoll.log, welche mit dem Wort
"Fehler" oder Wort "Error" anfangen:
% egrep '^(Fehler|Error)' protokoll.log
Die Programmiersprachen Java, Visual Basic, VBScript, JavaScript, C,
C++, C#, (.NET), elist, perl, Python, Tcl, Ruby, PHP, etc. können mit
regulären Ausdrücken umgehen.
Ein produktiver Einsatz ist für Ungeübte jedoch kein einfaches Unterfangen, da sie ein sehr mächtiges und komplexes Werkzeug darstellen.
Mehr über reguläre Ausdrücke im Allgemeinen erfährt man im Buch von
J.E.F. Friedl: "Reguläre Ausdrücke".
Reguläre Ausdrücke im Zusammenhang mit Oracle sind in einer zusammengefassten Form im Buch "Oracle Regular Expressions" zu finden.
3-12
1.2.066 / 4053
Der Ausführungsteil eines PL/SQL-Blocks
3
Reguläre Ausdrücke
3
ƒ Reguläre Ausdrücke
(über REGEXP Funktionen)
ƒ Anwendung auf:
ƒ CHAR
ƒ VARCHAR2
ƒ CLOB
ƒ NCHAR
ƒ NVARCHAR2
ƒ NCLOB
www.unilog.integrata.de
www.unilog-integrata.de
1.2.066 / 4053
4053 / 1.2.036
Folie 6
3-13
3
3.4.2
Der Ausführungsteil eines PL/SQL-Blocks
Einsatz in ORACLE 10g
Mögliche Einsatzgebiete in ORACLE sind:
– das Suchen eines bestimmten Musters in einer Zeichenkette
– das Ersetzen eines bestimmten Musters durch ein anderes in einer
Zeichenkette
– das Extrahieren bestimmter Muster aus einer Zeichenkette
– das Prüfen des Auftretens eines bestimmten Musters in einer Zeichenkette
Reguläre Ausdrücke können bei ORACLE auf folgende Datentypen angewandt werden:
– CHAR,
– VARCHAR2,
– CLOB,
– NCHAR,
– NVARCHAR,
– NCLOB
3.4.3
REGEXP_Funktionen
Für reguläre Ausdrücke wurden vier neue Funktionen entwickelt.
3.4.3.1 REGEXP_INSTR
– REGEXP_INSTR stellt ähnlich wie der INSTR-Operator fest, an welcher Stelle das gesuchte Muster auftritt.
Beispiel: Angestellte mit mehr als einem 'A' im Namen suchen:
SELECT * FROM emp
WHERE
REGEXP_INSTR (ename, '[A]+', 1, 2) > 0;
JOB
EMPNO ENAME
7876 ADAMS CLERK
3-14
MGR HIREDATE
7788 23.05.87
SAL COMM DEPTNO
1400
20
1.2.066 / 4053
Der Ausführungsteil eines PL/SQL-Blocks
3
3.4.3.2 REGEXP_SUBSTR
– REGEXP_SUBSTR ist ähnlich wie der SUBSTR-Operator. Gibt den
Teilstring aus, welcher auf das gesuchte Muster passt.
SELECT ename,
REGEXP_SUSTR (ename,'[^A]+') Test
FROM emp;
ENAME
SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
ADAMS
JAMES
FORD
MILLER
1.2.066 / 4053
TEST
SMITH
LLEN
W
JONES
M
BL
CL
SCOTT
KING
TURNER
D
J
FORD
MILLER
3-15
3
Der Ausführungsteil eines PL/SQL-Blocks
3.4.3.3 REGEXP_LIKE
– REGEXP_LIKE prüft ähnlich wie der LIKE-Operator, ob die Muster
gleich sind.
Beispiel: Alle die mit 'J' anfangen und ein 'N' im Namen haben:
SELECT ename
FROM emp
WHERE REGEXP_LIKE (ename,'^J.*N');
ENAME
JONES
3.4.3.4 REGEXP_REPLACE
– REGEXP_REPLACE ist ähnlich wie der REPLACE-Operator. Sucht und
ersetzt die beschriebenen Muster.
Beispiel: Ersetzen von ‚L’s durch das benachbarte Nachfolgezeichen,
sofern dieses kein weiteres ‚L’ ist.
SELECT ename,
REGEXP_REPLACE (ename, 'L([^L])', '\1\1') AS Test
FROM emp;
ENAME
SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
ADAMS
JAMES
FORD
MILLER
3-16
TEST
SMITH
ALEEN
WARD
JONES
MARTIN
BAAKE
CAARK
SCOTT
KING
TURNER
ADAMS
JAMES
FORD
MILEER
1.2.066 / 4053
Der Ausführungsteil eines PL/SQL-Blocks
3
REGEXP
3
ƒ REGEXP Funktionen:
ƒ REGEXP_INSTR
ƒ REGEXP_SUBSTR
ƒ REGEXP_LIKE
ƒ REGEXP_REPLACE
ƒ Bsp.:
SELECT ename,
REGEXP_REPLACE (ename, 'L([^L])', '\1\1') AS Test
FROM emp;
www.unilog.integrata.de
www.unilog-integrata.de
1.2.066 / 4053
4053 / 1.2.036
Folie 7
3-17
3
3.4.4
Der Ausführungsteil eines PL/SQL-Blocks
Die Metazeichen der Regulären Ausdrücke
Die Implementierung in ORACLE 10g basiert auf dem POSIX ERE (Extended Regular Expression) Standard.
Des Weiteren sind auch diverse Zeichenklassen ab Oracle 10g abprüfbar. Oracle hat ab Version 10.2 eigene Kürzel für einige Zeichenklassen eingeführt.
Zeichen
10.2 Bedeutung
[:alnum:]
\w
[^[:alnum:]] \W
Alles außer alphanumerischen Zeichen
[:alpha:]
Alle alphabetischen Zeichen (A-Z)
[:blank:]
Alle Leerzeichen
[:cntrl:]
Alle nichtdruckbaren Sonderzeichen
[:digit:]
\d
Alle Zahlen
[^[:digit:]]
\D
Alles außer Zahlen
[:graph:]
Alle [:punct:], [:upper:], [:lower:], und [:digit:] Zeichen.
[:lower:]
Alle Kleinbuchstaben
[:print:]
Alle druckbaren Zeichen
[:punct:]
Alle Punktations Zeichen
[:space:]
3-18
Alle alphanumerischen Zeichen (A-Z, 0-9)
\s
Alle Space Zeichen (nichtdruckbar)
[^[:space:]] \S
Alle außer Space Zeichen
[:upper:]
Alle Großbuchstaben
[:xdigit:]
Alle Hexadezimalzeichen
1.2.066 / 4053
Der Ausführungsteil eines PL/SQL-Blocks
Metazeichen von Regulären Ausdrücken
\
Der Backslash kann abhängig vom Kontext vier verschiedene Bedeutungen haben. Er kann:
für sich selbst stehen, das nächste Zeichen zitieren, einen Operator einbringen oder nichts tun.
*
Passt bei beliebig vielen Vorkommen (auch bei keinen)
+
Passt bei mindestens einem Vorkommen
?
Passt bei keinem oder genau einem Vorkommen
|
Trennzeichen für alternative Muster
^
Passt auf den Zeilenanfang
$
Passt auf das Zeilenende
.
Passt auf jedes unterstützte Zeichen ausgenommen von NULL
[]
Klammerausdruck enthält eine Liste von Ausdrücken, von denen ein beliebiger passen darf.
Eine Ausschlussliste beginnt mit einem Circumflex (^).
()
Gruppierung, wird als einzelner Teilausdruck behandelt.
{m}
Passt bei genau m Auftreten.
{m,}
Passt bei mindestens m Auftreten.
{m,n}
Passt bei mindestens m Auftreten, jedoch nicht mehr als n.
\n
Eine Backreference (n ist eine Ziffer zwischen 1 und 9) passt auf den n-ten Teilausdruck vor dem \n
[..]
Steht für ein (evtl. zusammengesetztes) Textzeichen, z.b. [.ch.] im Spanischen.
[: :]
Steht für eine Zeichenklasse (z.B. [:alpha:]). Passt auf jedes Zeichen dieser Klasse.
[==]
3
3
Steht für eine Äquivalenzklasse. So passt z.B. [=o=] auf o, ô, ö, ò, usw.
www.unilog.integrata.de
www.unilog-integrata.de
4053 / 1.2.066
Folie 8
Weitere mögliche reguläre Ausdrücke
Zeichen 10.2
[:alnum:] \w
[^[:alnum:]]\W
[:alpha:]
[:blank:]
[:cntrl:]
[:digit:] \d
[^[:digit:]] \D
[:graph:]
[:lower:]
[:print:]
[:punct:]
[:space:] \s
[^[:space:]] \S
[:upper:]
[:xdigit:]
www.unilog.integrata.de
www.unilog-integrata.de
1.2.066 / 4053
3
Bedeutung
Alle alphanummerischen Zeichen (A-Z, 0-9)
Alles außer alphanummerischen Zeichen
Alle alphabetischen Zeichen (A-Z)
Alle Leerzeichen
Alle nichtdruckbaren Sonderzeichen
Alle Zahlen
Alles außer Zahlen
Alle [:punct:], [:upper:], [:lower:], und [:digit:] Zeichen.
Alle Kleinbuchstaben
Alle druckbaren Zeichen
Alle Punktations Zeichen
Alle Space Zeichen (nichtdruckbar)
Alle außer Space Zeichen
Alle Großbuchstaben
Alle Hexadezimalzeichen
4053 / 1.2.066
Folie 9
3-19
3
3.5
Der Ausführungsteil eines PL/SQL-Blocks
Operatoren in PL/SQL
PL/SQL unterstützt das logische Vergleichen von Variablen und Konstanten in SQL sowie in PL/SQL-Befehlen. Es sind dieselben arithmetischen und logischen Operatoren und Verknüpfungen zulässig wie unter
SQL. Zusätzlich gibt es den Exponentialoperator (**). Mit Klammern
kann man die Priorität der Operationen verändern. Auch Wildcards (‘_‘
für ein Zeichen und ‘%‘ für 0 –n Zeichen) sind erlaubt.
Priorität der Operatoren:
Operator
Operation
Exponentialoperator, logische Verneinung
**, NOT
+, -
Vorzeichen
*, /
Multiplikation, Division
Addition, Subtraktion, Konkatenierung
+, -, ||
=, !=, <, >, <=, >=, IS NULL, Vergleich
LIKE, BETWEEN, IN
AND
logisches UND
OR
logisches ODER
Logiktabellen:
NOT
TRUE
FALSE
NULL
FALSE
TRUE
NULL
TRUE
FALSE
NULL
TRUE
TRUE
FALSE
NULL
FALSE
FALSE
FALSE
FALSE
NULL
NULL
FALSE
NULL
OR
TRUE
FALSE
NULL
TRUE
TRUE
TRUE
TRUE
FALSE
TRUE
FALSE
NULL
NULL
TRUE
NULL
NULL
AND
3-20
1.2.066 / 4053
Der Ausführungsteil eines PL/SQL-Blocks
3
Operatoren in PL/SQL
Operator
3
Operation
**, NOT
Exponentialoperator, logische Verneinung
+, -
Vorzeichen
*, /
Multiplikation, Division
+, -, ||
Addition, Subtraktion, Konkatenierung
=, !=, <, >, <=, >=, IS NULL, LIKE, BETWEEN, Vergleich
IN
AND
logisches UND
OR
NOT
logisches ODER
TRUE
FALSE
NULL
FALSE
TRUE
NULL
TRUE
FALSE
NULL
TRUE
TRUE
FALSE
NULL
FALSE
FALSE
FALSE
FALSE
NULL
NULL
FALSE
NULL
AND
TRUE
FALSE
NULL
TRUE
OR
TRUE
TRUE
TRUE
FALSE
TRUE
FALSE
NULL
NULL
TRUE
NULL
NULL
www.unilog.integrata.de
www.unilog-integrata.de
4053 / 1.2.066
Folie 10
Operatoren in PL/SQL
3
ƒ AND: beide Bedingungen müssen TRUE sein, damit das Ergebnis TRUE wird
ƒ OR: Mindestens eine der Bedingungen muss TRUE sein, damit das Ergebnis
TRUE wird
ƒ NOT und logische Verknüpfungen
ƒ Achtung: NOT NULL ergibt NULL!
ƒ Beispiele:
5 < 7 AND 'ADAM' != 'EVE' TRUE
5 < 7 OR 'EVE' < 'ADAM‘
TRUE
a < b OR 'EVE' < 'ADAM'
NULL
(a und b
unbekannt)
a < b AND 'EVE' < 'ADAM'
www.unilog.integrata.de
www.unilog-integrata.de
1.2.066 / 4053
4053 / 1.2.036
FALSE
Folie 11
3-21
3
3.6
Der Ausführungsteil eines PL/SQL-Blocks
Der SELECT-Befehl in PL/SQL
Innerhalb von PL/SQL ist die normale SELECT-Syntax nicht zulässig.
Stattdessen müssen mit Hilfe eines SELECT-Befehls Werte aus einer
Tabelle in Variablen eingelesen werden.
Syntax:
SELECT
INTO
FROM
[WHERE
spalte1 {, spalte2}
variable1 {, variable2}
tabelle
Bedingungen];
Dabei ist zu beachten:
• Anzahl und Datentyp der Variablen müssen mit der Anzahl und dem
Datentyp der selektierten Spalten übereinstimmen.
• Die WHERE-Bedingung ist so zu gestalten, dass genau ein Datensatz
zurückgeliefert wird. Wird gar kein Datensatz oder mehr als einer zurückgeliefert, so wird ein Fehler ausgelöst (NO_DATA_FOUND bzw.
TOO_MANY_ROWS).
Tipp: Gruppenfunktionen (ohne GROUP BY) liefern immer genau einen
Wert zurück
Mehr als einen Wert mit einem einzelnen SELECT-Befehl einlesen kann
man mit der Klausel BULK COLLECT :
Syntax:
SELECT spalte1 [, spalte2,...]
BULK COLLECT INTO variable1 [, variable2,...]
FROM
tabelle
[WHERE Bedingung];
Auch hier müssen Anzahl und Datentyp von Spaltenliste und Variablen
zusammenpassen. Die verwendeten Variablen müssen allerdings in der
Lage sein, mehr als einen Wert aufzunehmen; nur Collections sind zulässig (z.B. PL/SQL-Tables).
Einzelheiten zur Variablendeklaration im Beispiel (PL/SQL-TABLE) finden Sie in Kapitel 5.
3-22
1.2.066 / 4053
Der Ausführungsteil eines PL/SQL-Blocks
3
Beispiele:
SELECT ename INTO v_name
FROM emp where empno = 7788;
SELECT ename INTO v_name
FROM emp where mgr IS NULL;
SELECT MAX(mgr) INTO v_mgr FROM emp;
SELECT ename, mgr, hiredate
INTO v_name, v_mgr, v_date
FROM emp where empno = 7788;
DECLARE
TYPE namensliste IS TABLE OF emp.ename%TYPE;
n_list namensliste;
BEGIN
SELECT ename BULK COLLECT INTO n_list
FROM emp;
.....
END;
1.2.066 / 4053
3-23
3
Der Ausführungsteil eines PL/SQL-Blocks
SELECT-Befehl in PL/SQL
ƒ Syntax:
SELECT
INTO
FROM
[WHERE
ƒ
ƒ
ƒ
ƒ
spalte1 {spalte2}
variable1 {,variable2}
tabelle
bedingungen];
Anzahl und Datentyp der Spalten und Variablen müssen übereinstimmen
Es darf nur genau eine Zeile zurückgegeben werden
Tipp: Gruppenfunktionen verwenden
BULK COLLECT möglich
www.unilog.integrata.de
www.unilog-integrata.de
3-24
3
4053 / 1.2.066
Folie 12
1.2.066 / 4053
Der Ausführungsteil eines PL/SQL-Blocks
3.7
3
DML-Befehle in PL/SQL
DML-Befehle (INSERT, UPDATE, DELETE) sind in PL/SQL uneingeschränkt erlaubt. Innerhalb von DML-Befehlen dürfen PL/SQL-Variablen
anstatt fester Werte verwendet werden, falls der Datentyp stimmt. Auch
Befehle zur Transaktionssteuerung (COMMIT, ROLLBACK) sind zulässig
(Ausnahme: Trigger).
Ein DML-Befehl, der keine Zeile oder mehr als eine Zeile betrifft, löst im
Gegensatz zu SELECT keinen Fehler aus. Der Erfolg eines DMLBefehls kann jedoch mit Hilfe von Cursor-Attributen überprüft werden.
Ein Cursor ist ein privater Speicherbereich innerhalb der SGA, in dem
ein SQL-Befehl geparst und ausgeführt wird. Gleichzeitig ist er auch ein
Zeiger auf diesen Speicherbereich. Man unterscheidet explizite und implizite Cursor. Explizite Cursor werden ähnlich wie Variablen deklariert.
Jedem SQL-Befehl wird automatisch ein impliziter SQL-Cursor zugeordnet, dessen Attribute unmittelbar nach Ausführung des Befehls abgefragt werden können.
SQL-Cursor-Attribute:
SQL%ROWCOUNT:
Anzahl der Zeilen, die vom letzten SQL-Befehl
bearbeitet wurden (Datentyp INTEGER)
SQL%FOUND:
gibt TRUE zurück, wenn der letzte SQL-Befehl
mindestens eine Zeile bearbeitet hat (Datentyp
BOOLEAN)
SQL%NOTFOUND:
gibt TRUE zurück, wenn der letzte SQL-Befehl
keine Zeile bearbeitet hat (Datentyp BOOLEAN)
SQL%ISOPEN:
gibt immer FALSE zurück, weil ein impliziter Cursor unmittelbar nach Ausführung des SQLBefehls geschlossen wird
Im nächsten Beispiel wird die Anzahl der geänderten bzw. gelöschten
Zeilen mit Hilfe von DBMS_OUTPUT.PUT_LINE am Bildschirm ausgegeben. Damit diese vordefinierte Prozedur unter SQL*Plus wirksam
wird, muss vorher eingegeben werden:
SQL> SET SERVEROUTPUT ON
1.2.066 / 4053
3-25
3
Der Ausführungsteil eines PL/SQL-Blocks
Beispiel:
UPDATE emp SET sal = sal * 1.1
WHERE job = 'MANAGER';
v_count := SQL%ROWCOUNT;
if SQL%FOUND THEN
-- falls Zeile
-- geändert, dann lösche:
DELETE FROM emp WHERE mgr = 7902;
v_count2 := SQL%ROWCOUNT;
end if;
COMMIT;
DBMS_OUTPUT.PUT_LINE(v_count || ' Zeilen geändert');
DBMS_OUTPUT.PUT_LINE(v_count2 || ' Zeilen gelöscht');
3-26
1.2.066 / 4053
Der Ausführungsteil eines PL/SQL-Blocks
DML-Befehle in PL/SQL
3
3
ƒ uneingeschränkt zulässig
ƒ Transaktionskontrolle zulässig (Ausnahme Trigger)
ƒ durch Cursor-Attribute kontrollierbar
ƒ SQL-Cursor-Attribute:
ƒ SQL%ROWCOUNT
ƒ SQL%FOUND
ƒ SQL%NOTFOUND
ƒ SQL%ISOPEN
ƒ Beispiel:
UPDATE emp SET sal = sal * 1.1 WHERE job = 'MANAGER';
v_count := SQL%ROWCOUNT;
IF SQL%FOUND THEN
-- falls Zeile geändert, dann lösche:
DELETE FROM emp WHERE mgr = 7902;
v_count2 := SQL%ROWCOUNT;
END IF;
COMMIT;
DBMS_OUTPUT.PUT_LINE(v_count ||' Zeilen geändert');
DBMS_OUTPUT.PUT_LINE(v_count2 ||' Zeilen gelöscht');
www.unilog.integrata.de
www.unilog-integrata.de
1.2.066 / 4053
4053 / 1.2.036
Folie 13
3-27
3
3.8
Der Ausführungsteil eines PL/SQL-Blocks
Erweiterungen der DML-Befehle in ORACLE 10g
Es ist bei INSERT, UPDATE und DELETE Befehlen nun möglich, innerhalb des Statements einen Einzelwert (Single Aggregate) einer Variable
zuzuweisen und diesen später aus der Variable auszulesen.
SQL> SET SERVEROUTPUT ON;
SQL> VARIABLE sum_verdienst NUMBER;
3.8.1
INSERT Statement
SQL> INSERT INTO emp (empno, ename, sal)
VALUES (4711, 'Mato Bejic', 5000)
RETURNING (SELECT sum(sal) FROM emp) INTO
:sum_verdienst;
SQL> exec dbms_output.put_line
(TO_CHAR(:sum_verdienst));
3.8.2
UPDATE Statement
SQL> UPDATE emp
SET sal = sal + 100
RETURNING SUM(sal) INTO :sum_verdienst;
SQL> exec dbms_output.put_line
(TO_CHAR(:sum_verdienst));
3.8.3
DELETE Statement
SQL> DELETE FROM emp
WHERE empno = 4711
RETURNING SUM(sal) INTO :sum_verdienst;
SQL> exec dbms_output.put_line
(TO_CHAR(:sum_verdienst));
3-28
1.2.066 / 4053
Der Ausführungsteil eines PL/SQL-Blocks
Änderungen der DML Befehle in ORACLE 10g
3
3
ƒ Single Aggregate können einer Variable zugewiesen werden
ƒ Möglich bei:
ƒ INSERT
ƒ UPDATE
ƒ DELETE
ƒ Beispiel:
UPDATE emp
SET sal = sal + 100
RETURNING SUM(sal) INTO :sum_verdienst;
exec dbms_output.put_line
(TO_CHAR(:sum_verdienst));
www.unilog.integrata.de
www.unilog-integrata.de
1.2.066 / 4053
4053 / 1.2.036
Folie 14
3-29
3
3.9
Der Ausführungsteil eines PL/SQL-Blocks
Die GOTO-Anweisung
Mit GOTO wird bedingungslos zu einem Label gesprungen, um von dort
aus den Code weiter abzuarbeiten. Dieses Label wird auch als
Sprungmarke bezeichnet.
Nicht zulässig sind allerdings:
• Sprung in ein IF-Konstrukt hinein (s. Kapitel 4)
• Sprung von einem Zweig eines IF-Konstrukts in einen anderen
• Sprung in eine Schleife hinein (s. Kapitel 4)
• Sprung in einen untergeordneten Block hinein
• Sprung aus dem Fehlerbehandlungsteil zurück in den Ausführungsteil
Sprünge aus einem IF-Konstrukt heraus, aus einer Schleife heraus,
und aus einem untergeordneten Block in den übergeordneten Block
sind erlaubt.
Anmerkung: GOTO-Anweisungen sollten möglichst vermieden werden,
da sie den Code unübersichtlich machen.
Syntax:
GOTO label;
3-30
1.2.066 / 4053
Der Ausführungsteil eines PL/SQL-Blocks
3
GOTO-Anweisung
3
ƒ Durch GOTO springt das Programm an die angegebene Stelle
ƒ Nicht gesprungen werden darf in:
ƒ Schleifen,
ƒ IF-Konstrukten und
ƒ untergeordneten Blöcken.
ƒ Beispiel:
DECLARE
v_name CHAR(10);
valid BOOLEAN;
BEGIN
<<get_name>>
SELECT ename INTO v_name FROM emp
WHERE empno = &g_no;
BEGIN
.....
IF valid THEN
....
ELSE
GOTO get_name;
END IF;
END;
END;
www.unilog.integrata.de
www.unilog-integrata.de
1.2.066 / 4053
4053 / 1.2.036
Folie 15
3-31
3
3.10
Der Ausführungsteil eines PL/SQL-Blocks
Bedingte Kompilierung
Ab Version 10.2 sind nun endlich auch Preprozessoranweisungen möglich. Abhängig von der Version kann z.B. dann nur ein bestimmter Codeabschnitt kompiliert werden.
Beispiel:
Hier wird abhängig von der Version nur ein bestimmter Codeabschnitt
kompiliert.
$IF DBMS_DB_VERSION.VER_LE_10 $THEN
Code für Version 10 oder älter$ELSIF
DBMS_DB_VERSION.VER_LE_11 $THEN
Versions 11 code
$ELSE
Version 12 oder größer Code
$END
In diesem Beispiel wird eine boolsche Variable für die Kompilierung auf
einen Wert gesetzt. Diese Variable entscheidet dann welche Teil der
Funktion kompiliert wird.
CREATE OR REPLACE FUNCTION f1
RETURN VARCHAR2 AS
BEGIN
$If $$ppv $Then
RETURN 'PPV was TRUE';
$Else
RETURN 'PPV was FALSE';
$END /* Kein Semicolon */
END;
SQL> ALTER SESSION SET plsql_ccflags = 'PPV:TRUE';
SQL> ALTER FUNCTION F1 COMPILE;
SQL> select f1 from dual;
MYFUNC
------------------------------------PPVAL was TRUE
3-32
1.2.066 / 4053
Der Ausführungsteil eines PL/SQL-Blocks
Bedingte Kompilierung
3
3
ƒ Lange haben die Programmier auf Preprozessor- anweisungen gewartet.
ƒ Damit kann ein bedingte Kompilierung erzeugt werden
ƒ Beispiele:
ƒ Programmteile für verschiedene Versionen
ƒ Debugginginfos mit in den Code kompileren
ƒ Deluxe Version (vergleichbar Enterprise/Standard Edition) kompilieren
ƒ Syntax:
ƒ $IF <bed> $THEN
<Code>
ƒ $ELSIF <bed> $THEN
<Code>
ƒ $ELSE
<Code>
ƒ $END
www.unilog.integrata.de
www.unilog-integrata.de
1.2.066 / 4053
4053 / 1.2.036
Folie 16
3-33
3
3-34
Der Ausführungsteil eines PL/SQL-Blocks
1.2.066 / 4053