Programmentwicklung mit PL/SQL

Erstellen einer gespeicherten Prozedur oder Funktion
Abrufen einer gespeicherten Prozedur
Vorwärtsdeklaration für Prozeduren und Funktionen
Verwenden gespeicherter Funktionen in einer SQL-Anweisung
Speichern von Rückgabewerten in einer Tabelle
Aufrufen einer gespeicherten Prozedur oder Funktion
Pakete
Zusätzliche PL/SQL-Datentypen
Festlegen von Standardwerten für Variablen

Erstellen einer gespeicherten Prozedur oder Funktion

CREATE [OR REPLACE] PROCEDURE procedure-name
[(argument1 ... [, argumentN) ] IS
[local-variable-declarations]
BEGIN
executable-section
[exception-section]
END [procedure-name];
Als Beispiel enthält das Listing eine gespeicherte Prozedur, die ein einzelnes Argument hat, das von der delete-Anweisung verwendet wird, um zu bestimmen, welche Klasse aus der Tabelle Course entfernt wird.
SQL> create or replace procedure Delete_Specified_Course
2 (Description_Phrase varchar2) is
3
3 begin
4
4 delete from Course
5 where
6 upper(Description) like Description_Phrase;
7
7 end;
8 /
Procedure created.
Die Syntax für das Erzeugen einer gespeicherten Funktion ist sehr ähnlich wie die Syntax für das Erzeugen einer gespeicherten Prozedur. Natürlich muß eine gespeicherte Funktion einen Wert zurückgeben.
CREATE [OR REPLACE] FUNCTION function-name
[(argument1 ... [, argumentN) ]
RETURN function-datatype IS
[local-variable-declarations]
BEGIN
executable-section
[exception-section]
RETURN function-value
END [function-name];
Erstellen einer gespeicherten Funktion mit einem einzigen Argument:
create or replace function student_GPA (arg_student_ID IN varchar2)
return number is

GPA number;

begin

select avg(decode(grade, 'A+', 4.25, 'A', 4, 'A-', 3.75,
'B+', 3.25, 'B', 3, 'B-', 2.75,
'C+', 2.25, 'C', 2, 'C-', 1.75,
'D+', 1.25, 'D', 1, 'D-', 0.75, 'F', 0))
into GPA
from student_schedule
where
student_id = arg_student_id;

return GPA;

end;

Das Paket namens dbms_output bietet eine Menge von Prozeduren und Funktionen, die nützlich sind, um Werte von einem PL/SQL-Block aus anzuzeigen. Um das Paket mit SQL*Plus zu verwenden, geben Sie die folgende Anweisung ein, bevor die erste Prozedur oder Funktion aus dbms-output aufgerufen wird:
set serveroutput on
Ermitteln von Fehlermeldungen bei Erstellen gespeicherter Prozeduren

Wenn Oracle beim Erzeugen eines gespeicherten PL/SQL-Programms Fehler entdeckt, gibt es eine undefinierbare Meldung aus, die angibt, daß Fehler aufgetreten sind - ohne irgendwelche zusätzlichen Details anzugeben. Als Beispiel zeigt das Listing, was geschieht, wenn Sie eine gespeicherte Prozedur mit einem Syntaxfehler erzeugen wollen. Um die Fehler zu sehen, die bei dem Versuch entstehen, diesen PL/SQL-Code zu kompilieren, können Sie den SQL*Plus-Befehl show errors verwenden, dieser zeigt die speziellen PL/SQL-Übersetzungsfehler.

SQL> create or replace procedure show_inserts IS
2
2 max_records constant int := 100;
3 i int := 1;
4
4 begin
5
5 dbms_output.enable;
6
6 for i in 1..max_records loop
7
7 if (mod(i,10) = 0) then
8 insert into test_table
9 (record_number, current_date)
10 values
11 (i, SYSDATE)
12 dbms_output.put_line('The value of i is ' || to_char(i));
13
13 else
14 null;
15
15 end if;
16
16 end loop;
17
17 end;
18 /
Warning: Procedure created with compilation errors.
SQL> show errors
Errors for PROCEDURE SHOW_INSERTS:
LINE/COL ERROR
-------- -------------------------------
12/5 PLS-00103: Encountered the symbol "DBMS_OUTPUT" when
expecting one of the following:
;
; was inserted before "DBMS_OUTPUT" to continue.
Abrufen einer gespeicherten Prozedur

Das Data Dictionary von Oracle ist eine Gruppe von Tabellen, die Informationen über die Oracle-Datenbank selbst enthalten. Weil die Tabellen dieses Data Dictionary eine etwas kryptische Struktur haben, definiert Oracle eine Menge von Datensichten, die einen kohärenteren Blick auf das Data Dictionary bieten. Eine dieser Datensichten hat den Namen USER_SOURCE und enthält die folgenden vier Spalten:
NAME Enthält den Namen der Prozedur, der Funktion, des Pakets oder Paketrumpfs
TYPE Gibt an, ob der Code zu einer Prozedur, einer Funktion, einem Paket oder einem Paketrumpf gehört
TEXT Enthält eine Zeile des Codes
LINE Enthält die Zeilennummer des Codes in TEXT

Stellen Sie sich als Beispiel vor, daß Sie eine gespeicherte Prozedur namens drop_class erstellen. Wenn Sie den Code von drop_class sehen wollen, fragen Sie die Datensicht user_source des Data Dictionary ab, wie dies im Listing gezeigt wird.

SQL> select line, text
2 from user_source
3 where
4 name = 'DROP_CLASS'
5 order by line;

LINE TEXT
---- ---------------------------------------------------------------
1 procedure drop_class (arg_student_ID IN varchar2,
2 arg_class_ID IN varchar2,
3 status OUT number) is
4
5 counter number;
6
7 begin
8
9 status := 0;
10
11 - Verify that this class really is part of the student's schedule.
12
13 select count(*) into counter
14 from student_schedule
15 where
16 student_id = arg_student_id and
17 class_id = arg_class_id;
18
19 if counter = 1 then
20 delete from student_schedule
21 where
22 student_id = arg_student_id and
23 class_id = arg_class_id;
24 status := -1;
25 end if;
26
27 end;

27 rows selected.

Ermitteln einer Liste von Prozeduren, Funktionen, Paketen und Paketrümpfen

Sie können USER_OBJECTS abfragen, um eine Liste gespeicherter Prozeduren, Funktionen, Pakete und Paketrümpfe zu erhalten, die zu dem Oracle-Account gehört, mit dem Sie gerade verbunden sind. Wenn Sie alle diese Objekte, unabhängig von ihrem Eigentümer, sehen wollen, müssen Sie DBA_OBJECTS anstelle von USER_OBJECTS abfragen. Die Spalte OBJECT_TYPE von DBA_OBJECTS gibt den Typ des Objekts an: Tabelle, Datensicht, Prozedur usw.

Bestimmung der im Besitz des aktuellen Benutzers befindlichen Objekttypen.

SQL> select distinct object_type
2 from user_objects;

OBJECT_TYPE
----------------
FUNCTION
INDEX
PACKAGE
PACKAGE BODY
PROCEDURE
TABLE
VIEW

7 rows selected.

Vorwärtsdeklaration für Prozeduren und Funktionen

In PL/SQL muß jeder Bezeichner deklariert werden - Konstanten, Variablen, Cursor oder Funktionen -, der an irgendeiner Stelle eines PL/SQL-Unterprogramms verwendet wird. Diese Anforderung kann ein Problem verursachen, wenn zwei Unterprogramme aufeinander verweisen, wie es im folgenden Listing gezeigt wird.

SQL> set serveroutput on
SQL>
SQL> declare
2
2 function Medicare_Patient (Patient_ID IN varchar2)
3 return number is
4
4 status number;
5 Pat_ID varchar2(6);
6
6 begin
7
7 if Insurable_Patient (Pat_ID) = 2 then
8 status := 1;
9 end if;
10
10 return status;
11
11 end Medicare_Patient;
12
12
12 function Insurable_Patient (Patient_ID IN varchar2)
13 return number is
14
14 status number;
15 Pat_ID varchar2(6);
16
16 begin
17
17 if Medicare_Patient (Pat_ID) = 2 then
18 status := 1;
19 end if;
20
20 return status;
21
21 end Insurable_Patient;
22
22 - Executable portion of anonymous block.
23
23 begin
24
24 dbms_output.enable;
25
25 end;
26 /
declare
*
ERROR at line 1:
ORA-06550: line 7, column 4:
PLS-00313: 'INSURABLE_PATIENT' not declared in this scope
ORA-06550: line 7, column 1:
PL/SQL: Statement ignored
Wie Sie im Listing sehen können, erkennt PL/SQL den Aufruf von Insurable_Patient in der Funktion Medicare_Patient nicht an, weil die Deklaration von Insurable_Patient nach der Deklaration von Medicare_Patient erfolgt (Zeile 2). Um dieses Dilemma zu umgehen, nehmen Sie eine Vorwärtsdeklaration des Unterprogramms in den Deklarationsteil auf. Eine Vorwärtsdeklaration ist eine Deklaration eines Unterprogramms, seiner Argumente und seines Rückgabewerts. Listing 10.7 zeigt, wie man für das vorhergehende Beispiel eine Vorwärtsdeklaration für Insurable_Patient (Zeile 2) angibt.
SQL> set serveroutput on
SQL>
SQL> declare
2
2 function Insurable_Patient (Patient_ID IN varchar2) return number;
3
3 function Medicare_Patient (Patient_ID IN varchar2)
4 return number is
5
5 status number;
6 Pat_ID varchar2(6);
7
7 begin
8
8 if Insurable_Patient (Pat_ID) = 2 then
9 status := 1;
10 end if;
11
11 return status;
12
12 end Medicare_Patient;
13
13
13 function Insurable_Patient (Patient_ID IN varchar2)
14 return number is
15
15 status number;
16 Pat_ID varchar2(6);
17
17 begin
18
18 if Medicare_Patient (Pat_ID) = 2 then
19 status := 1;
20 end if;
21
21 return status;
22
22 end Insurable_Patient;
23
23 - Executable portion of anonymous block.
24
24 begin
25
25 dbms_output.enable;
26
26 end;
27 /
PL/SQL procedure successfully completed.


Verwenden gespeicherter Funktionen in einer SQL-Anweisung

Mit der Version 7.1 der Oracle-RDBMS wurde es möglich, eine gespeicherte Funktion in einer SQL-Anweisung aufzurufen. Dieses Merkmal ist sehr leistungsfähig, weil es die Funktionalität einer einzelnen SQL-Anweisung so ausdehnt, daß sie die Logik einer gespeicherten Funktion einschließt. Lassen Sie uns ein elementares Beispiel dafür betrachten, wie man diese Funktionalität erhält.

Weil es in Oracle keine integrierte Funktion für die Umwandlung einer Temperatur von Fahrenheit nach Grad Celsius gibt, erstellen Sie eine gespeicherte Funktion, die diese Umwandlung durchführt. Nachdem die gespeicherte Prozedur erfolgreich erstellt wurde, können Sie sie in einer select-Anweisung verwenden.

Verwendung einer gespeicherten Funktion in einer select-Anweisung.

SQL> create or replace function DegF_to_DegC (Deg_F IN number)
2 return number is
3
3 Deg_C number;
4
4 begin
5
5 Deg_C := (5.0/9.0)*(Deg_F - 32);
6
6 return Deg_C;
7
7 end DegF_to_DegC;
8 /
Function created.

SQL> select body_temp, degf_to_degc(body_temp)
2 from patient;
BODY_TEMP DEGF_TO_DEGC(BODY_TEMP)
--------- -----------------------
99.2 37.333333
100.2 37.888889
103.8 39.888889


Speichern von Rückgabewerten in einer Tabelle

Obwohl PL/SQL keine integrierte Unterstützung für die Kommunikation mit dem Benutzer besitzt, können Sie dennoch PL/SQL verwenden, um einem Benutzer oder einem anderen Programm Ergebnisse zu liefern, indem Sie

Sie haben bereits ein Beispiel dafür gesehen, wie man mit PL/SQL eine Zwischentabelle schreiben kann. Wenn gespeicherte Prozeduren und Funktionen in PL/SQL kompiliert werden, schreibt der PL/SQL-Kern selbst Fehlermeldungen in eine Tabelle des Data Dictionary, die vom Benutzer abgefragt werden kann. Wenn Sie über SQL*Plus Ausgaben zur Verfügung stellen wollen, ist die Verwendung von dbms_output eine gute Strategie. Wenn Sie viele Werte an einen Benutzer oder ein Programm übergeben wollen, ist eine Tabelle sinnvoller.

Aufrufen einer gespeicherten Prozedur oder Funktion

Die Methode für den Aufruf einer gespeicherten Prozedur hängt vom Umfeld ab.
Für SQL*Plus verwenden Sie den Befehl execute (die folgende Syntax gilt für eine gespeicherte Prozedur ohne Argumente) auf folgende Weise:

execute show_inserts;
Pakete

Ein Paket ist eine Gruppe verwandter Prozeduren und Funktionen in PL/SQL. Wie ein Paket der Programmiersprache Ada besteht ein PL/SQL-Paket aus einer Paketspezifikation und einem Paketrumpf. Sie können Pakete erstellen, die anwendungsabhängig sind - beispielsweise könnte ein Paket namens patient_data Prozeduren und Funktionen enthalten, die mit der Behandlung und Beschaffung von Informationen über die Patienten eines Krankenhauses zu tun haben. Außerdem könnte ein Paket Prozeduren und Funktionen enthalten, die häufig vorkommende Aufgaben wie die Umwandlung einer Ortsangabe von einem Koordinatensystem in ein anderes ausführen.

Deklarieren eines Pakets

CREATE [OR REPLACE] PACKAGE package-name IS
declaration-section
END package-name;
Deklaration einer Paketspezifikation.
create or replace package Flugle is

function register_for_class (arg_student_ID IN varchar2,
arg_class_ID IN varchar2)
return number;

function schedule_conflict (arg_student_ID IN varchar2,
arg_class_ID IN varchar2)
return number;

procedure drop_class (arg_student_ID IN varchar2,
arg_class_ID IN varchar2,
status OUT number);

procedure assign_instructor (arg_class_ID IN varchar2,
arg_instructor_ID IN varchar2,
status OUT number);

procedure assign_grade (arg_student_ID IN varchar2,
arg_class_ID IN varchar2,
arg_grade IN varchar2,
status OUT number);

function student_GPA (arg_student_ID IN varchar2)
return number;

END;

Wenn Sie Paketspezifikationen oder Paketrümpfe mit einem Skript erzeugen, verwenden Sie die or replace-Klausel. Oracle bietet auch die Anweisungen drop package und drop package body, aber die or replace-Klausel erspart Ihnen den Ärger, sich zu merken, ob Sie ein Paket gelöscht haben, bevor Sie versuchen, es zu erzeugen.

Deklarieren eines Paketrumpfs

Ein Paketrumpf enthält die öffentlichen und privaten Elemente eines Pakets. Er versteckt die Details, wie Cursors, Prozeduren und Funktionen konkret implementiert sind - Details, die vor Entwicklern verborgen werden sollten.

CREATE PACKAGE BODY package-name IS
declaration-section
procedure-bodies;
function-bodies;
initialization-section
END package-name;
*******************************************************
create or replace package flugle is

- Declare some exceptions.

schedule_conflict_exists exception;
already_registered exception;
not_registered exception;

- Declare values for status.

conflicting_classes number := -2;
unsuccessful number := -1;

normal number := 0;


- *******************************************************
- Function register_for_class
-
function register_for_class (arg_student_ID IN varchar2,
arg_class_ID IN varchar2)
return number is

status number;
counter number;

begin

- Determine if the student isn't already registered for this class.

select count(*) into counter
from student_schedule
where
student_id = arg_student_id and
class_id = arg_class_id;

if counter > 0 then
-
- The student is already registered for this class.
-
raise already_registered;
else
-
- The student isn't registered for this class.
- Determine if there is a schedule conflict.
-
if schedule_conflict(arg_student_id, arg_class_id) = 0 then
insert into student_schedule
(student_id, class_id)
values
(arg_student_id, arg_class_id);
else
raise schedule_conflict_exists;
end if;
end if;

status := normal;

return status;

exception
when schedule_conflict_exists then
raise_application_error (-20001, 'Schedule conflict exists');
when already_registered then
raise_application_error (-20002, 'Student is already registered for class');
when others then
null;
end;

- *******************************************************
- Function schedule_conflict
-
function schedule_conflict (arg_student_ID IN varchar2,
arg_class_ID IN varchar2)
return number is

- Declare a cursor to look for other classes with the same schedule
- as this one.

cursor get_other_classes is
select SS.Class_ID
from Student_Schedule SS, Class C
where
SS.Class_ID = C.Class_ID and
(C.Semester, C.School_Year, C.Schedule_ID) =
(select Semester, School_Year, Schedule_ID
from Class
where
Class_ID = arg_class_ID);

Conflicting_Class_ID Class.Class_ID%type;
status number;

begin

- Need to look at the other classes in the student's schedule
- for the same semester and school year.

for get_other_classes_Rec in get_other_classes loop

fetch get_other_classes into Conflicting_Class_ID;
exit when get_other_classes%notfound;

end loop;

close get_other_classes;

if get_other_classes%rowcount > 0 then
status := conflicting_classes;
else
status := normal;
end if;

return status;

end;

- *******************************************************
- Procedure drop_class
-
procedure drop_class (arg_student_ID IN varchar2,
arg_class_ID IN varchar2,
status OUT number) is

counter number;

begin

- Verify that this class really is part of the student's schedule.

select count(*) into counter
from student_schedule
where
student_id = arg_student_id and
class_id = arg_class_id;

if counter = 1 then
delete from student_schedule
where
student_id = arg_student_id and
class_id = arg_class_id;
end if;

end;

- *******************************************************
- Procedure assign_instructor
-
procedure assign_instructor (arg_class_ID IN varchar2,
arg_instructor_ID IN varchar2,
status OUT number) is

counter number;

begin

- Determine if this instructor is associated with the department
- that offers this class.

select count(*)
into counter
from Instructor I, Class C
where
C.Class_ID = arg_class_ID and
I.Instructor_ID = arg_instructor_ID and
C.Department_ID = I.Department_ID;

if counter = 0 then
status := unsuccessful;
else
-
- Assign this instructor to this class.
-
update class
set
Instructor_ID = arg_instructor_ID
where
Class_ID = arg_class_ID;

status := normal;

end if;

end;

- *******************************************************
- Procedure assign_grade
-
procedure assign_grade (arg_student_ID IN varchar2,
arg_class_ID IN varchar2,
arg_grade IN varchar2,
status OUT number) is
counter number;

begin

- Determine if the student is registered for this class.

select count(*) into counter
from student_schedule
where
student_id = arg_student_id and
class_id = arg_class_id;

if counter = 0 then
-
- The student is not taking this class.
-
raise not_registered;
else
-
- Assign the grade for this class.
-
update student_schedule
set
grade = arg_grade
where
student_id = arg_student_id and
class_id = arg_class_id;
end if;

exception
when not_registered then
raise_application_error (-21003, 'Student not registered for class');
when others then
null;

end;

- *******************************************************
- Function student_GPA
-
function student_GPA (arg_student_ID IN varchar2)
return number is

GPA number;

begin

-
- Calculate the average grade point for this student based on all
- classes for which a grade has been assigned.

select avg(decode(grade, 'A+', 4.25, 'A', 4, 'A-', 3.75,
'B+', 3.25, 'B', 3, 'B-', 2.75,
'C+', 2.25, 'C', 2, 'C-', 1.75,
'D+', 1.25, 'D', 1, 'D-', 0.75, 'F', 0))
into GPA
from student_schedule
where
student_id = arg_student_id;

return GPA;

end;

end;

Wenn der Flugle-Paketrumpf erstellt ist, können Sie die Funktionen aus dem Paket aufrufen. Werfen Sie einen Blick auf das Listing. Die update-Anweisung wird verwendet, um den Lehrer für Class 104200 auf null zu setzen. Die nachfolgende select-Anweisung beweist, daß dieser Klasse kein Lehrer zugewiesen ist. Als nächstes wird ein anonymer Block verwendet, in dem Assign_Instructor aufgerufen wird, um Instructor E491 der Klasse 104200 zuzuweisen. Der Status ist 0, dies zeigt an, daß die Prozedur erfolgreich war. Schließlich zeigt die letzte select-Anweisung an, daß der Lehrer in der Tat der Klasse zugewiesen wurde.
SQL> set serveroutput on
SQL>
SQL> update class
2 set
3 Instructor_ID = null
4 where
5 class_ID = '104200';

1 row updated.

SQL> select Instructor_ID
2 from Class
3 where
4 Class_ID = '104200';

INSTRUCTOR_ID
----------------

SQL> declare
2
2 status number;
3
3 begin
4
4 dbms_output.enable;
5
5
5 Flugle.Assign_Instructor ('104200', 'E491', status);
6
6 dbms_output.put_line('Status: ' || to_char(status));
7
7 end;
8 /
Status: 0

PL/SQL procedure successfully completed.

SQL> select Instructor_ID
2 from Class
3 where
4 Class_ID = '104200';

INSTRUCTOR_ID
-------------
E491

Entwerfen eines Pakets für Datenbank-Trigger

Die Prozeduren und Funktionen eines Pakets können aus SQL*Plus-Skripten, aus PL/SQL-Unterprogrammen, aus Skripten von Client-Anwendungen (wie Oracle Forms oder Power Builder) aufgerufen werden - sowie aus Datenbank-Triggern. Ein Datenbank-Trigger kann jedoch keine gespeicherte Prozedur, Funktion und kein Unterprogramm eines Pakets aufrufen, das eine commit-, rollback- oder savepoint-Anweisung enthält. Wenn Sie die Flexibilität benötigen, daß die Unterprogramme eines Pakets von einem Datenbank-Trigger aufgerufen werden können, stellen Sie deshalb sicher, daß Sie keine der Prozeduren und Funktionen im Paket Transaktionen festschreiben oder rückgängig machen. Lesen Sie Tag 11, »Weitere Programmiertechniken für PL/SQL«, um weitere Informationen über Datenbank-Trigger zu erhalten.

Zusätzliche PL/SQL-Datentypen

 PL/SQL bietet  die folgenden zusätzlichen Datentypen, die in gewöhnlichen SQL-Anweisungen nicht zur Verfügung stehen:

Der Datentyp boolean

Einer der zusätzlichen Datentypen, die PL/SQL unterstützt, ist boolean. Das Listing zeigt, wie Sie eine boolean-Variable deklarieren. Sie können eine boolean-Variable auch mit true oder false initialisieren.

SQL> set serveroutput on
SQL>
SQL> declare
2 Payment_Is_Late boolean := TRUE;
3
3 begin
4
4 dbms_output.enable;
5
5 if Payment_Is_Late then
6 dbms_output.put_line('The payment is late!');
7 end if;
8
8 end;
9 /
The payment is late!
PL/SQL procedure successfully completed.
In Zeile 2 wird die boolean-Variable Payment_Is_Late mit true initialisiert. In Zeile 5 wird Payment_Is_Late ausgewertet; weil Zeile 5 true ergibt, wird Zeile 6 ausgeführt.
Bis Sie ihr einen Wert zuweisen, hat eine boolean-Variable den Wert Null. Im Listing wird der boolean-Variablen Payment_Is_Late der boolean-Ausdruck Day_of_Month > 5 zugewiesen.
SQL> set serveroutput on
SQL>
SQL> declare
2 Payment_Is_Late boolean;
3 Day_of_Month integer;
4
4 begin
5
5 dbms_output.enable;
6
6 select to_number(to_char(sysdate,'DD'))
7 into Day_of_Month
8 from dual;
9
9 Payment_Is_Late := Day_of_Month > 3;
10
10 if Payment_Is_Late then
11 dbms_output.put_line('The payment is late!');
12 end if;
13
13 end;
14 /
The payment is late!
PL/SQL procedure successfully completed.


Der Datentyp binary integer

Der Datentyp binary integer speichert vorzeichenbehaftete ganze Zahlen im Bereich von -2147483647 bis 2147483647. PL/SQL bietet auch zwei andere Datentypen, die Untertypen von binary_integer sind.

Zahlen, die niemals einen Nachkommateil haben, wie etwa Schleifenzähler, können Sie mit den Datentypen natural oder positive deklarieren.
Wenn Sie reelle Zahl einer als binary_integer, natural oder positive deklarierten Zahl zuweisen, wird die Zahl abgeschnitten:
SQL> declare
2 Counter natural;
3
3 begin
4
4 dbms_output.enable;
5
5 Counter := 103.2;
6
6 dbms_output.put_line('Counter: ' || to_char(Counter,'999.999'));
7
7 end;
8 /
Counter: 103.000
%type verwenden

PL/SQL bietet zwei Notationen für Verweise auf Tabellen und Datentypen von Spalten in Oracle.

Diese beiden Datentypen helfen Ihnen, PL/SQL-Code mit den im Data Dictionary von Oracle existierenden Tabellen- und Spaltendefinitionen zu integrieren.
Das Schöne bei der Verwendung von %type ist, daß der Wartungsaufwand für PL/SQL-Code verringert wird. Sie können beispielsweise die Breite einer Spalte ändern, ohne daß Sie die Deklarationen der auf dieser Spalte basierenden Variablen ändern müssen.

%rowtype verwenden

Sie können die Bezeichnung %rowtype verwenden, um eine Variable - in Wirklichkeit einen Datensatz - zu deklarieren, dessen Struktur identisch mit der Struktur einer bestimmten Tabelle ist.
Beispielsweise wird ein Datensatz namens Instructor_Rec als Instructor%rowtype deklariert. Das Ergebnis ist, daß die Felder von Instructor_Rec dieselben Namen und Datentypen wie die Spalten der Tabelle Instructor haben.

SQLWKS> declare
2>
3> Instructor_Rec Instructor%ROWTYPE;
4>
5> begin
6>
7> dbms_output.enable;
8>
9> select *
10> into Instructor_Rec
11> from Instructor
12> where
13> Instructor_ID = 'P331';
14>
15> dbms_output.put_line('Instructor ID: ' || Instructor_Rec.Instructor_ID);
16> dbms_output.put_line('Last Name: ' || Instructor_Rec.Last_Name);
17> dbms_output.put_line('First Name: ' || Instructor_Rec.First_Name);
18>
19> end;
20> /
Statement processed.
Instructor ID: P331
Last Name: POULSON
First Name: RANIER
Wie Sie sehen können (in Zeile 15 bis 17), werden die Felder des %rowtype-Datensatzes über
variable-name.field-name
angesprochen.
Obwohl Sie in einer select-Anweisung auf einen Datensatz verweisen können, der mit %rowtype deklariert wurde, können Sie nicht mit einer insert-Anweisung auf den gesamten Datensatz verweisen. PL/SQL weist beispielsweise die folgende insert-Anweisung zurück:
SQL> declare
2 Patient_Rec Patient%rowtype;
3
3 begin
4
4 Patient_Rec.Patient_ID := 'HHH111';
5 Patient_Rec.Body_Temp_Deg_F := 102.7;
6
6 insert into Patient
7 (Patient_ID, Body_Temp_Deg_F)
8 values
9 Patient_Rec;
10
end;
/
Patient_Rec;
*
ERROR at line 9:
ORA-06550: line 9, column 1:
PLS-00103: Encountered the symbol "PATIENT_REC"
when expecting one of the follow
an aggregate
Resuming parse at line 9, column 12.
Statt dessen müssen Sie jede Komponente des Datensatzes Patient_Rec angeben, die den in der insert-Anweisung angegebenen Spalten entspricht.
Sie können auch eine Variable einer anderen Variablen zuweisen, wenn beide mit der Bezeichnung %rowtype für dieselbe Tabelle deklariert wurden.
Zuweisungen an PL/SQL-Variablen, die auf %rowtype basieren.

SQL> declare
2
2 New_Patient Patient%ROWTYPE;
3 ER_Patient Patient%ROWTYPE;
4
4 begin
5
5 dbms_output.enable;
6
6 select *
7 into New_Patient
8 from Patient
9 where
10 Patient_ID = 'ZZ0123';
11
11 ER_Patient := New_Patient;
12
12 dbms_output.put_line('ER_Patient.Body_Temp_Deg_F: ' ||
13 to_char(ER_Patient.Body_Temp_Deg_F));
K 14
14 end;
15 /
ER_Patient.Body_Temp_Deg_F: 98.6

Sie können jedoch eine %rowtype-Variable nicht einer anderen %rowtype-Variablen zuweisen, wenn beide nicht auf dieselbe Tabelle einer Datenbank verweisen, auch wenn die Tabellen identisch sind.
SQL> create table Identical_Patient as
2 select * from Patient;
Table created.
SQL> set serveroutput on
SQL>
SQL> declare
2
2 New_Patient Patient%ROWTYPE;
3 ER_Patient Identical_Patient%ROWTYPE;
4
4 begin
5
5 dbms_output.enable;
6
6 select *
7 into New_Patient
8 from Patient
9 where
10 Patient_ID = 'ZZ0123';
11
11 ER_Patient := New_Patient;
12
12 dbms_output.put_line('ER_Patient.Body_Temp_Deg_F: ' ||
13 to_char(ER_Patient.Body_Temp_Deg_F));
14
14 end;
15 /
declare
*
ERROR at line 1:
ORA-06550: line 11, column 15:
PLS-00382: expression is of wrong type
ORA-06550: line 11, column 1:
PL/SQL: Statement ignored


Weitere komplexe Datentypen: table und record

PL/SQL unterstützt zwei zusätzliche zusammengesetzte Datentypen. Tabellen und Datensätze. Jedes dieser Objekte wird zuerst als Datentyp deklariert, dann wird die eigentliche PL/SQL-Tabelle oder der eigentliche PL/SQL-Datensatz auf der Basis des angegebenen Datentyps deklariert.
Sie können sich eine PL/SQL-Tabelle als Array vorstellen: Sie besteht aus einem einzigen Feld. Sie deklarieren auch keine obere Grenze für die Anzahl der Elemente, die eine PL/SQL-Tabelle enthalten kann; ihre Größe ist dynamisch.
Eine PL/SQL-Tabelle ist eine Sammlung von Elementen desselben Typs, die durch eine Indexnummer geordnet ist.
Es ist ungünstig, daß sich Oracle entschlossen hat, den Namen Tabelle für eine Struktur zu verwenden, die angemessener als Array beschrieben werden kann. Eine PL/SQL-Tabelle besteht im Gegensatz zu einer Datenbanktabelle aus einer einzigen Spalte. Wie bei einem Array wird auf die Werte einer PL/SQL-Tabelle über einen Index zugegriffen. Denken Sie daran, daß eine PL/SQL-Tabelle und eine Datenbanktabelle zwei verschiedene Dinge mit sehr speziellen Eigenschaften und Verwendungszwecken sind.

Ein benutzerdefinierter Datensatz bietet mehr Flexibilität als die Bezeichnung %rowtype. Sie sollten die Verwendung eines benutzerdefinierten Datensatzes in Betracht ziehen, wenn eine der folgenden Bedingungen erfüllt ist:

Wenn Sie einen benutzerdefinierten Datensatz deklarieren, der mit einer Datenbanktabelle verknüpft ist, sollten Sie die Bezeichnung %type für jedes Feld verwenden, das eine Spalte der Datenbanktabelle spiegelt. Das reduziert den Aufwand für die Wartung des PL/SQL-Codes, wenn die unvermeidlichen Änderungen an der Datenbank vorgenommen werden.

Deklarieren von PL/SQL-Tabellen

TYPE type-name IS TABLE OF
table-name.column-name%TYPE
INDEX BY BINARY_INTEGER;
Nachdem Sie den PL/SQL-Tabellentyp deklariert haben, können Sie Variablen auf der Basis dieses Typs deklarieren. Beispielsweise wird im Listing, das einen anonymen PL/SQL-Block enthält, Class_ID_Tab als Tabelle der Spalte Class_ID in der Tabelle Class deklariert. Ein Cursor for loop wählt jede Class_ID aus der Tabelle Class aus und weist sie einem Element von Class_ID_Tab zu.

Verwendung einer PL/SQL-Tabelle.

SQLWKS> set serveroutput on

Server Output ON
SQLWKS> declare
2>
3> type Class_ID_Type is table of Class.Class_ID%TYPE
4> index by binary_integer;
5>
6> Class_ID_Tab Class_ID_Type;
7> i binary_integer := 0;
8> final_count binary_integer;
9>
10> begin
11>
12> dbms_output.enable;
13>
14> for Class_ID_Rec in (select Class_ID from Class) loop
15>
16> i := i + 1;
17> Class_ID_Tab(i) := Class_ID_Rec.Class_ID;
18>
19> end loop;
20>
21> final_count := i;
22>
23> for i in 1..final_count loop
24>
25> dbms_output.put_line('Class_ID_Tab(' || to_char(i) || ') = ' ||
26> Class_ID_Tab(i));
27> end loop;
28>
29> end;
30> /
Statement processed.
Class_ID_Tab(1) = 104200
Class_ID_Tab(2) = 104500
Class_ID_Tab(3) = 109100
Class_ID_Tab(4) = 120200
Class_ID_Tab(5) = 110300
Class_ID_Tab(6) = 108300
Class_ID_Tab(7) = 108400
Class_ID_Tab(8) = 108600
Class_ID_Tab(9) = 103400
Class_ID_Tab(10) = 103600

Sie können eine PL/SQL-Tabelle als Argument an eine Prozedur oder Funktion übergeben. Zusammen mit der PL/SQL-Tabelle werden Sie wahrscheinlich auch eine binary_integer-Variable übergeben, die die Anzahl der Elemente in der PL/SQL-Tabelle angibt. Das Listing zeigt ein Beispiel für eine Prozedur, die eine PL/SQL-Tabelle zurückgibt, die diejenigen Course_IDs enthält, für welche die zusätzlichen Gebühren mehr als $ 50 betragen.
SQLWKS> declare
2>
3> type Course_ID_Type is table of Course.Course_ID%TYPE
4> index by binary_integer;
5> Course_ID_Tab Course_ID_Type;
6>
7> i binary_integer := 0;
8> Total_Number binary_integer;
9>
10> procedure Get_Course_IDs (Num_Rows out binary_integer,
11> Course_ID_Table out Course_ID_Type) is
12>
13> i binary_integer := 0;
14>
15> begin
16>
17> for Course_ID_Rec in (select Course_ID from Course
18> where Additional_Fees > 50) loop
19>
20> i := i + 1;
21> Course_ID_Table(i) := Course_ID_Rec.Course_ID;
22>
23> end loop;
24>
25> Num_Rows := i;
26>
27> end Get_Course_IDs;
28>
29> - Main block.
30>
31> begin
32>
33> dbms_output.enable;
34>
35> Get_Course_IDs (Total_Number, Course_ID_Tab);
36>
37> for i in 1..Total_Number loop
38> exit when Course_ID_Tab(i) = NULL;
39> dbms_output.put_line('Course_ID_Tab(' || to_char(i) ||
40> ') = ' || Course_ID_Tab(i));
41> end loop;
42>
43> end;
44> /

Statement processed.
Course_ID_Tab(1) = 101
Course_ID_Tab(2) = 189
Course_ID_Tab(3) = 101
Course_ID_Tab(4) = 178
Course_ID_Tab(5) = 177
Course_ID_Tab(6) = 174
Course_ID_Tab(7) = 181
Course_ID_Tab(8) = 501

Die erste Zeile im Listing beginnt mit dem Deklarationsteil eines anonymen Blocks. Der Typ Course_ID_Type wird in Zeile 3 vereinbart. Die Deklaration der Prozedur Get_Course_IDs beginnt in Zeile 10. In Zeile 13 wird der Index der PL/SQL-Tabelle Course_ID_Table auf 0 initialisiert. Der Ausführungsteil des anonymen Blocks beginnt in Zeile 31. In Zeile 35 wird im Ausführungsteil des anonymen Blocks die Prozedur Get_Course_IDs aufgerufen.
PL/SQL schränkt den Bereich des Index einer PL/SQL-Tabelle nicht ein; Sie könnten bei -100, 0 oder 1 anfangen, je nachdem, wie es gerade nötig ist.

Deklarieren von benutzerdefinierten Datensätzen

Der Prozeß der Verwendung benutzerdefinierter Datensätze ist sehr ähnlich wie bei einer PL/SQL-Tabelle: Sie definieren einen Datentyp für den Datensatz und deklarieren dann Variablen auf der Basis des neuen Typs.

TYPE type-name IS RECORD
(field-name field-datatype [NOT NULL] [initial-value],
...
(field-name field-datatype [NOT NULL] [initial-value];
Ein Vorteil eines benutzerdefinierten Datensatzes ist es, daß Sie Felder für die Speicherung abgeleiteter Daten in einem Datensatz definieren können, der nicht in der zugehörigen Datenbanktabelle gespeichert wird.
Das Listing zeigt die Deklaration eines benutzerdefinierten Datensatztyps. In diesem Fall vereinbaren wir einen Datensatztyp namens Patient_Rec_Type, der sich aus drei Feldern zusammensetzt: Patient_ID, Body_Temp und Bed_Number. Die ersten zwei Felder sind in der Tabelle Patient vorhanden; Body_Temp hat jedoch in der Tabelle einen anderen Namen - Body_Temp_Deg_F. Das dritte Feld, Bed_Number, existiert in der Tabelle Patient nicht.
SQL> declare
2
2 type Patient_Rec_Type is record
3 (Patient_ID Patient.Patient_ID%TYPE,
4 Body_Temp Patient.Body_Temp_Deg_F%TYPE,
5 Bed_Number varchar2(4));
6
6 Patient_Rec Patient_Rec_Type;
7
7 begin
8
8 dbms_output.enable;
9
9 Patient_Rec.Patient_ID := 'ZZ0123';
10 Patient_Rec.Body_Temp := 98.6;
11 Patient_Rec.Bed_Number := 'A123';
12
12 dbms_output.put_line('Patient ID: ' || Patient_Rec.Patient_ID);
13 dbms_output.put_line('Body_Temp: ' || to_char(Patient_Rec.Body_Temp));
14 dbms_output.put_line('Bed Number: ' || Patient_Rec.Bed_Number);
15
15 insert into Patient
16 (Patient_ID, Body_Temp_Deg_F)
17 values
18 (Patient_Rec.Patient_ID, Patient_Rec.Body_Temp);
19
19 end;
20 /

Patient ID: ZZ0123
Body_Temp: 98.6
Bed Number: A123


Festlegen von Standardwerten für Variablen

Als Standard werden beim Eintritt in eine Prozedur, eine Funktion oder einen anonymen Block alle Variablen mit null initialisiert. Sie können eine Variable im Deklarationsteil von PL/SQL auf zwei Weisen initialisieren:

variable-name data-type := initial-value;
oder
variable-namedata-type DEFAULT initial-value;
Hier ist ein anonymer Block, der beide Methoden der Initialisierung einer PL/SQL-Variablen zeigt:
SQL> declare
2
2 i natural := 33;
3 mystring varchar2(30) default 'JACKSON';
4
4 begin
5
5 dbms_output.enable
6
6 end;
7 /
PL/SQL procedure sucessfully completed.
Ein Grund, einen Standardwert für eine Variable anzugeben, ist, daß der Code oft besser zu verstehen und zu warten ist. Indem Sie Standardwerte angeben, machen Sie weniger Annahmen darüber, wie der Code sich verhalten wird.
 
home
... weiter

Ein Imprint des Markt&Technik Buch- und Software-Verlag GmbH.
Elektronische Fassung des Titels: Oracle in 21 Tagen, ISBN: 3-8272-2017-3