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];

• argument1 bis argumentN sind optionale Deklarationen von Argumenten, sie bestehen aus
argument-name[IN | OUT] datatype [ {:= | DEFAULT} value]
• local-variable-declarations sind optionale Deklarationen von Variablen, Konstanten und anderen Prozeduren und Funktionen, die für procedure-name lokal sind.

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.

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];

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;

set serveroutput on

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.

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.

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.

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

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

• Informationen in eine Zwischentabelle schreiben, die ein Benutzer oder ein Programm abfragen kann;
• die Prozeduren und Funktionen des von Oracle zur Verfügung gestellten Pakets dbms_output verwenden.

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;

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;

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;

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;

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

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:

• boolean
• binary_integer, natural und positive
• %type
• %rowtype
• die PL/SQL-Tabelle (oder den Array)
• den benutzerdefinierten Datensatz

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.

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.

• natural kann ganze Zahlen im Bereich von 0 bis 2147483647 speichern.
• positive kann ganze Zahlen im Bereich von 1 bis 2147483647 speichern.

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

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

• Verwenden Sie %type, um eine Variable mit demselben Datentyp wie eine bestimmte Spalte (oder einen vorher deklarierte Variable) zu deklarieren.
• Verwenden Sie %rowtype, um eine zusammengesetzte Variable zu deklarieren, deren Struktur diejenige einer bestimmten Tabelle oder eines bestimmten Cursor widerspiegelt.

%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

variable-name.field-name

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.

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

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:

• Sie benötigen nur eine Teilmenge der Spalten einer Tabelle.
• Sie wollen auch verknüpfte oder abgeleitete Informationen im Datensatz speichern.

Deklarieren von PL/SQL-Tabellen

TYPE type-name IS TABLE OF
table-name.column-name%TYPE
INDEX BY BINARY_INTEGER;

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

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

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];

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;

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.