PL/SQL ist eine blockstrukturierte Sprache
Variablendeklaration mit PL/SQL
Einige bekannte Kontrollstrukturen
SQL-Anweisungen in einem PL/SQL-Programm verwenden
PL/SQL-Unterprogramme
Verwenden von Unterblöcken
Deklarieren einer Prozedur
Deklarieren einer Funktion

Bis jetzt habe ich Ihnen SQL als eine Sprache ohne prozedurale Fähigkeiten präsentiert. Oracle bietet jedoch durch die Sprache PL/SQL Erweiterungen zu SQL. PL/SQL ist die Basis für die folgenden logischen Elemente von Anwendungen:

• SQL*Plus-Skript: Ein SQL*Plus-Skript kann PL/SQL-Unterprogrammblöcke enthalten.
• Gespeicherte Prozeduren oder Funktionen: Eine gespeicherte Prozedur oder Funktion ist ein PL/SQL-Unterprogramm, das durch eine Client-Anwendung, einen Datenbanktrigger oder einen Anwendungstrigger eines Oracle-Werkzeugs aufgerufen werden kann.
• Datenbanktrigger: Ein Datenbanktrigger ist ein PL/SQL-Unterprogramm, das eine Aktion aufgrund der Ausführung einer DML-Anweisung - wie insert, update oder delete - an einer Datenbanktabelle durchführt.
• Package: Eine Menge von PL/SQL-Prozeduren, -Funktionen, -Cursors und anderen PL/SQL-Variablen, die in einem Paket gebündelt werden.
• Anwendungstrigger: Entwicklungswerkzeuge für Oracle-Anwendungen wie Oracle Forms und Oracle Reports sind mit einer PL/SQL-Engine ausgestattet, so daß Entwickler mit PL/SQL Anwendungstrigger konstruieren können.

PL/SQL ist eine blockstrukturierte Sprache mit einer ähnlichen Syntax wie die Programmiersprache C. Zusätzlich zu eingebetteten SQL-Anweisungen bietet PL/SQL Standardkonstrukte für das Programmieren wie Deklarationen von Prozeduren und Funktionen, Kontrollanweisungen wie if-then-else und loop und deklarierte Variablen. Ein PL/SQL-Programm besteht aus Prozeduren, Funktionen oder anonymen Blöcken. Ein anonymer Block ist ein unbenannter PL/SQL-Block, der keine Argumente besitzt und keine Werte zurückgibt. Anonyme Blöcke kommen häufig in Skripten vor, die in einer SQL*Plus-Sitzung ausgeführt werden.

Abbildung 19.1 zeigt die oberste Ebene der Struktur eines SQL-Blocks. Dieser enthält einen optionalen Abschnitt mit Deklarationen, einen ausführbaren Abschnitt und einen optionalen Abschnitt für die Behandlung von Exceptions und Fehlern von PL/SQL und SQL
 

Abbildung 19.1:  Struktur höchster Ebene eines PL/SQL-Blocks.

Der Deklarationsteil

Der Deklarationsteil eines PL/SQL-Blocks ist optional. Sie müssen jedoch alle Variablen und Konstanten deklarieren, die in den PL/SQL-Anweisungen verwendet werden. Um einen Deklarationsteil in einen PL/SQL-Block aufzunehmen, beginnen Sie den PL/SQL-Block mit dem Wort declare. Jede Deklaration einer Variablen oder Konstanten besteht aus ihrem Namen, dem Datentyp und einem optionalen Standardwert. Wie bei allen PL/SQL-Anweisungen wird die Deklaration jeder Variablen und Konstanten mit einem Semikolon abgeschlossen. Listing 9.2 enthält einige Beispiele für Deklarationen von Variablen und Konstanten.

Fax_Number VARCHAR2(10);
Current_Used_Value NUMBER(6,2) := 100.00;
Max_Current_Used_Value REAL := 9999.99;
State VARCHAR2(2) := 'CA';

Der Ausführungsteil eines PL/SQL-Blocks folgt auf das Schlüsselwort begin. Jede PL/SQL-Anweisung wird durch ein Semikolon beendet. Diese Anweisungen können wie folgt aufgeteilt werden:

• Zuweisungsanweisungen
• Anweisungen, die den Programmablauf steuern
• SQL-Anweisungen
• Cursoranweisungen

Eine Exception ist eine Fehlerbedingung, die während der Ausführung eines PL/SQL-Programms auftritt. Eine Exception kann vordefiniert sein - wenn beispielsweise eine insert-Anweisung versucht, eine doppelte Zeile in eine Tabelle einzufügen, wird eine dup_val_on_index-Exception ausgelöst. Sie können auch Ihre eigenen, für die Anwendung spezifischen Exceptions definieren. Der Exception-Teil definiert die Exception-Handler, die sowohl für vordefinierte, als auch für benutzerdefinierte Exceptions aufgerufen werden. Jeder Exception-Handler besteht aus einer oder mehreren PL/SQL-Anweisungen.

Variablendeklaration mit PL/SQL

Eine weitere Fähigkeit von PL/SQL - jedoch nicht von SQL - sind zusätzliche Datentypen. Zusätzlich zu den normalen Datentypen von Oracle-SQL können Sie mit PL/SQL Variablen der folgenden Datentypen deklarieren:

boolean	Eine boolsche Variable, die folgende Werte aufweisen kann: true, false oder null.
binary_integer	Dieser Typ wird für vorzeichenbehaftete ganze Zahlen im Bereich von -2147483647 bis 2147483647 verwendet.
natural	Eine Untermenge von binary_integer. Dieser Datentyp umfaßt die ganzen Zahlen von 0 bis 2147483647.
positive	Eine weitere Untermenge von binary_integer, dieser Datentyp umfaßt die ganzen Zahlen von 1 bis 2147483647.
%type	Diese Bezeichnung ermöglicht es, den Datentyp einer Variablen als äquivalent zum Datentyp der angegebenen Spalte zu deklarieren. Das Ergebnis ist PL/SQL-Code, der sich leichter warten läßt.
%rowtype	Mit diesem Datentyp können Sie einen zusammengesetzten Datentyp deklarieren, der äquivalent zu einer Zeile der angegebenen Tabelle ist. Die zusammengesetzte Variable wird aus den Spaltennamen und den Datentypen der referenzierten Tabelle zusammengesetzt.

Zusätzlich bietet PL/SQL zwei zusammengesetzte Datentypen: table und record. Sie werden an Tag 10 mehr darüber lernen.

Deklaration einer Variablen mit Hilfe von %type

Die Syntax für die Deklaration einer Variablen mit %type ist

variable-name table-name.column-name%TYPE

Tech_Name Depot_Estimate.Technician%TYPE;

Deklaration einer Variablen mit Hilfe von %rowtype

Die Syntax für die Deklaration einer Variablen mit %rowtype ist

variable-name table-name%ROWTYPE

Depot_Est_Row Depot_Estimate%ROWTYPE;

Depot_Est_Row.Technician := 'RICHARDSON';

Einige bekannte Kontrollstrukturen

• if-then-elsif
• loop
• exit
• while loop
• for loop
• goto
• null

Die Syntax der if-then-elsif-Anweisung von PL/SQL unterscheidet sich etwas von der vergleichbaren Anweisung in der Programmiersprache C:

IF condition THEN
statement; ... statement;
[ELSIF condition THEN
statement; ... statement;]
...
[ELSIF condition THEN
statement; ... statement;]
[ELSE condition THEN
statement; ... statement;]
END IF;

if MOD(i,5) = 0 then
rec_number := 5;
elsif MOD(i,7) = 0 then
rec_number := 7;
else
rec_number := i;
end if;

LOOP
statement; ... statement;
END LOOP

Die exit-Anweisung hat zwei Formen:

• exit ohne andere Klauseln - ein unbedingtes Exit
• exit [label_name] when condition

SQL> declare
2
2 i positive := 1;
3 max_loops constant positive := 100;
4
4 begin
5
5 loop
6
6 i := i + 1;
7 exit when i > max_loops;
8
8 end loop;
9
9 end;
10 /
PL/SQL procedure successfully completed.

WHILE condition LOOP
statement; ... statement;
END LOOP;

WHILE I < 100 LOOP
I := I + 1;
insert into temp_table (rec_number) values (I);
END LOOP;

FOR loop-variable IN [REVERSE] lower-bound..upper-bound LOOP
statement; ... statement;
END LOOP;

for i in 1..max_loops loop
j := j + j;
dbms_output.put_line('j: ' || to_char(j));
end loop;

In PL/SQL wird ein Label folgendermaßen definiert:

<<my_label>>

SQL> declare
2
2 i positive := 1;
3 max_loops constant positive := 100;
4
4 begin
5
5 i := 1;
6
6 loop
7
7 i := i + 1;
8 if i > max_loops then
9 goto more_processing;
10 end if;
11
11 end loop;
12
12 <<more_processing>>
13 i := 1;
14
14 end;
15 /
PL/SQL procedure successfully completed.

In bestimmten Situationen sollten Sie PL/SQL anzeigen, daß keine Aktion durchgeführt werden soll. In einem Exception-Handler kann es beispielsweise vorkommen, daß Sie nichts tun wollen, wenn eine bestimmte Exception auftritt. Um Klarheit zu schaffen, verwenden Sie die null-Anweisung in einem if-then-elsif, um anzuzeigen, daß für eine bestimmte elsif-Klausel keine Aktion durchgeführt werden soll.

 Verwendung der null-Anweisung:

if (mod(i,10) = 0) then
i := i + 1;
else
NULL;
end if;

PL/SQL bietet Ihnen zwei Wege, Ihre Codes zu dokumentieren. Zum einen können Sie in jeder Zeile einen Kommentar angeben, indem Sie -- , gefolgt vom Kommentar, angeben, wie hier gezeigt wird:

Depot_Est_Row.Technician := Last_Tech_Name;
-- Assign the name of the last technician involved

j + 1;
/* The next section inserts a row into the Utility_Audit table
to record the name of the current Oracle user and the
current date and time (SYSDATE).
*/
insert into Utility_Audit
...

Sie können SQL-Anweisungen in einem anonymen Block, in einer Prozedur oder in Funktionen, wie Sie in SQL*Plus angewandt werden, mit nur wenigen Abweichungen verwenden. Wie andere PL/SQL-Anweisungen wird jede SQL-Anweisung mit einem Semikolon beendet. Sie können jedoch in PL/SQL in einer SQL-Anweisung auf deklarierte Variablen zugreifen.

 Zugriff auf Variablen in einer SQL-Anweisung.

DECLARE
max_records CONSTANT int := 100;
i int := 1;
BEGIN
FOR i IN 1..max_records LOOP
if (mod(i,10) = 0) then
INSERT INTO test_table
(record_number, current_date)
VALUES
(i, SYSDATE);
else
NULL;
end if;
END LOOP;
COMMIT;
END;
/

PL/SQL und die select-Anweisung

In einem PL/SQL-Unterprogramm verwendet die select-Anweisung eine andere Klausel - into - um die PL/SQL-Variablen zu identifizieren, in die Spaltenwerte eingetragen werden sollen. Stellen Sie die into-Klausel zwischen die Auswahlliste und die from-Klausel.

Verwendung einer select-Anweisung in einem PL/SQL-Block.

SQL> set serveroutput on
SQL>
SQL> declare
2
2 Average_Body_Temp Patient.Body_Temp_Deg_F%type;
3
3 begin
4
4 dbms_output.enable;
5
5 select avg(Body_Temp_Deg_F)
6 into Average_Body_Temp
7 from Patient;
8
8 dbms_output.put_line('Average body temp in Deg. F: ' || to_char(Average_Body_Temp,'999.99'));
9
9 end;
10 /
Average body temp in Deg. F: 99.80
PL/SQL procedure successfully completed.

PL/SQL unterstützt auch die Verwendung von Unterprogrammen - benannte Prozeduren und Funktionen. Eine PL/SQL-Prozedur führt diverse Aktionen durch und kann optionale Parameter haben. Eine PL/SQL-Funktion gibt einen Wert mit einem bestimmten Datentyp zurück und kann ebenfalls optionale Parameter haben.

Verwenden von Unterblöcken

PL/SQL ermöglicht es Ihnen, in einen Block Unterblöcke einzuschließen:

SQL> declare
2
2 max_i constant int := 100;
3 i int := 1;
4 rec_number int;
5
5 begin
6
6 for i in 1..max_i loop
7
7 if mod(i,5) = 0 then
8 rec_number := 5;
9 elsif mod(i,7) = 0 then
10 rec_number := 7;
11 else
12 rec_number := i;
13 end if;
14
14 insert into test_table
15 (record_number, current_date)
16 values
17 (rec_number, sysdate);
18
18 - Here is a sub-block:
19
19 declare
20 max_j constant int := 20;
21 j int := 1;
22
22 begin
23
23 for j in 1..max_j loop
24
24 rec_number := rec_number * j;
25
25 insert into test_table
26 (record_number, current_date)
27 values
28 (rec_number, sysdate);
29
29 end loop;
30
30 end;
31
31 commit;
32 end loop;
33
33 end;
34 /
PL/SQL procedure successfully completed.
SQL> select count(*) from test_table;
COUNT(*)
--------
2100

Obwohl PL/SQL die Möglichkeit unterstützt, Blöcke ineinander einzubetten, ist diese Praxis aus zwei Gründen nicht wünschenswert. Zum einen reduziert sie die Lesbarkeit - und damit die Wartbarkeit - Ihres Codes. Zum anderen können eingebettete Blöcke nicht von anderen PL/SQL-Unterprogrammen verwendet werden. Sie sollten darauf achten, Prozeduren und Funktionen so zu entwerfen, daß die Wiederverwendbarkeit des Codes und seine Wartbarkeit erhöht werden.

Deklarieren einer Prozedur

Zusätzlich zu anonymen Blöcken können Sie in PL/SQL auch Prozeduren und Funktionen deklarieren:

PROCEDURE procedure-name [(argument1 ... [, argumentN) ] IS
[local-variable-declarations]
BEGIN
executable-section
[exception-section]
END [procedure-name];

• procedure-name ist der Name der Prozedur, er unterliegt den Einschränkungen von Oracle-Datenbanken bezüglich der Namensgebung von Objekten.
• argument1 bis argumentN sind optionale Deklarationen von Argumenten, diese sind folgendermaßen aufgebaut:
• argument-name [IN | OUT] datatype [ {:= | DEFAULT} value]
• local-variable-declarations sind optionale Deklarationen von Variablen, Konstanten und anderen Prozeduren, die lokal für procedure-name sind.
• executable-section sind die PL/SQL-Anweisungen, aus denen sich die Prozedur zusammensetzt.
• exception-section ist der optionale Abschnitt zur Behandlung von Exceptions in der Prozedur.

Um diese Syntax zu verdeutlichen, enthält das Listing ein Beispiel für einen anonymen Block, der eine Prozedur namens Record_Patient_Temp_Dec deklariert. Diese Prozedur hat zwei Argumente: die ID des Patienten und die Körpertemperatur des Patienten, gemessen in Grad Celsius (siehe Zeile 4). In Zeile 3 wird die Variable High_Fever auf 42 initialisiert. In Zeile 17 wird die Prozedur mit zwei Argumenten aufgerufen: New_Patient_ID (mit dem Wert GG9999) und High_Fever. Die select-Anweisung, die auf den anonymen Block folgt, demonstriert, daß die Prozedur ihre Aufgabe erfüllt hat - sie hat 42 Grad Celsius in 107.6 Grad Fahrenheit umgewandelt.

SQL> declare
2
2 New_Patient_ID Patient.Patient_ID%type;
3 High_Fever constant real := 42.0;
4
4 procedure Record_Patient_Temp_Deg_C (Patient_ID varchar2,
5 Body_Temp_Deg_C real) is
6
6 Temp_Deg_F real;
7
7 begin
8
8 Temp_Deg_F := (9.0/5.0)*Body_Temp_Deg_C + 32.0;
9
9 insert into Patient
10 (Patient_ID, Body_Temp_Deg_F)
11 values
12 (Patient_ID, Temp_Deg_F);
13
13 commit;
14 end;
15
15 begin
16
16 New_Patient_ID := 'GG9999';
17
17 Record_Patient_Temp_Deg_C (New_Patient_ID, High_Fever);
18
18 end;
19 /
PL/SQL procedure successfully completed.

SQL> select Patient_ID, Body_Temp_Deg_F
2 from Patient
3 where
4 Patient_ID = 'GG9999';
PATIEN BODY_TEMP_DEG_F
------ ---------------
GG9999 107.6

SQL> declare
2
2 procedure Delete_Patients is
3
3 Temp_Deg_F real;
4
4 begin
5
5 delete from Patient
6 where
7 Patient_ID = 'GG3333';
8
8 commit;
9
9 end;
10
10 begin
11
11 Temp_Deg_F := 100.0;
12
12 end;
13 /
Temp_Deg_F := 100.0;
*
ERROR at line 11:
ORA-06550: line 11, column 1:
PLS-00201: identifier 'TEMP_DEG_F' must be declared
ORA-06550: line 11, column 1:
PL/SQL: Statement ignored

Deklarieren einer Funktion

Die Deklaration einer Funktion in PL/SQL ähnelt der Deklaration einer Prozedur - außer, daß die Funktion einen Wert mit einem vordefinierten Datentyp zurückgibt:

FUNCTION function-name [(argument1 ... [, argumentN) ]
RETURN function-datatype IS
[local-variable-declarations]
BEGIN
executable-section
[exception-section]
END [function-name];

• function-name ist der Name der Funktion, er unterliegt den Einschränkungen von Oracle-Datenbanken bezüglich der Namensgebung von Objekten.
• argument1 bis argumentN sind optionale Deklarationen von Argumenten, diese sind folgendermaßen aufgebaut:
• argument-name [IN | OUT] datatype [ {:= | DEFAULT} value]
• local-variable-declarations sind optionale Deklarationen von Variablen, Konstanten und anderen Prozeduren, die lokal für function-name sind.
• executable-section sind die PL/SQL-Anweisungen, aus denen sich die Funktion zusammensetzt.
• exception-section ist der optionale Abschnitt zur Behandlung von Exceptions in der Funktion.

SQL> declare
2
2 Course_ID Course.Course_ID%type;
3
3 function Max_Additional_Fees (Dept_ID IN varchar2)
4 return varchar2 is
5
5 Additional_Fees Course.Additional_Fees%type;
6 Units Course.Units%type;
7 Course_ID Course.Course_ID%type;
8
8 begin
9
9 select Course_ID
10 into Course_ID
11 from Course
12 where
13 Department_ID = Dept_ID and
14 Additional_Fees =
15 (select max(Additional_Fees)
16 from Course
17 where
18 Department_ID = Dept_ID);
19
19 return Course_ID;
20
20 end;
21
21 - Beginning of executable section of anonymous block.
22
22 begin
23
23 dbms_output.enable;
24
24 Course_ID := Max_Additional_Fees ('ECON');
25
25 dbms_output.put_line('Course_ID: ' || Course_ID);
26
26 end;
27 /
Course_ID: 189

PL/SQL procedure successfully completed.

SQL> select Course_ID, Additional_Fees
2 from Course
3 where
4 Department_ID = 'ECON'
5 order by Course_ID;

COURS ADDITIONAL_FEES
----- ---------------
101 25
189 750
199 0

 Verwendung unterschiedlicher Arten von Argumenten - in, out und in out.

SQL> declare
2
2 This_Arg1 number;
3 This_Arg2 number;
4 This_Arg3 number;
5
5 procedure Different_Arguments
6 (arg1 IN number,
7 arg2 OUT number,
8 arg3 IN OUT number) is
9
9 begin
10
10 arg2 := arg1;
11 arg3 := arg3 + 1;
12
12 end;
13
13 - Beginning of executable section of anonymous block.
14
14 begin
15
15 This_Arg1 := 3.14159;
16
16 Different_Arguments (This_Arg1, This_Arg2, This_Arg3);
17
17 end;
18 /
PL/SQL procedure successfully completed.
 

home	... weiter