Inhalt
Vorteile
Persistenz
Packages erlauben nicht nur die Gruppierung von logisch zusammengehöriger Funkionalität, sondern bieten durch ihre Persistenz, die sie innerhalb einer Session haben, weitere Möglichkeiten: Sie haben also eine Persistenz, die sich nicht nur auf die Erhaltung von Variablenwerten, sondern auch auf Cursor etc. auswirken.
Erhöhte Sicherheit
Es ist denkbar, eine Package bereitzustellen, die Kundendatensätze erzeugt, verändert und löscht. Sie können Nutzern das Recht einräumen, diese Package zu nutzen, ohne dass sie das Recht erhalten, direkt auf die relevanten Tabellen zuzugreifen. Dieser Grund allein zählt nicht hinreichend, da es selbstverständlich auch möglich ist, Usern Rechte für einzelne Prozeduren zu geben.
Mehr noch, hier kann sich auch ein Nachteil verbergen, da Sie sich die Möglichkeit nehmen, einem Nutzer nur das Einfügen, einem anderen nur das Ändern zu erlauben. Ausführungsrechte vergeben Sie stets für die Package, also alle darin enthaltenen Prozeduren und Funktionen.
Performanceverbesserung
Wenn ein Objekt einer Package genutzt wird, wird stets die gesamte Package in den Speicher geladen. Das heißt, wann immer eine andere Funktion der gleichen Package genutzt wird, ist diese bereits im RAM und muß nicht von der Platte gelesen werden.
Globale Daten
Packages haben die Fähigkeit, sich Zustände über die Programmausführung hinaus “zu merken”. Dies macht z.B. Variablen zu globalen Daten innerhalb Ihrer Sitzung. Diese globalen Daten sind nur in Ihrer Sitzung bekannt und in keiner anderen, selbst dann, wenn Sie mit dem gleichen Namen mehrfach eingewählt sind (es stellt also keine Übergabemöglichkeit sessionübergreifend dar. Dazu gibt es Packages, die von ORACLE bereitgestellt werden).
Sogar ein Cursor, der in einer Package geöffnet wird, steht in der Sitzung, und damit auch anderen Prozeduren oder Packages zur Verfügung.
Diese Gründe machen es so wichtig, sich mit Packages auseinanderzusetzen.
Aufbau einer Package
Eine Package besteht aus zwei Bereichen, einer Spezifikation und einem Rumpf (Body). Die Spezifikation enthält die Definition aller Komponenten der Package, die von aussen zugreifbar sein sollen. Man nennt sie auch Public Components. In unserer Übung werden wir die Spezifikation einer Package namens “Banking” entwickeln. Wir machen die Schnittstelle bekannt, die von aussen genutzt werden kann. Unsere Package soll folgende Aufgaben erfüllen:
- Bankkonten anlegen
- Geld einzahlen
- Geld abheben
- Kontostand anzeigen
- Bankkonten schließen
Eine Package besteht aus zwei Teilen: Einer Spezifikation und einem sog. Package Body.
Die Spezifikation enthält alle Elemente, die von aussen refenziert werden können, der Body kann zusätzliche Funktionen enthalten, die nur innerhalb des Bodies nutzbar sind.
Demzufolge ist eine Spezifikation ohne Body denkbar (z.B. für die Bekanntgabe von Konstanten, die keinen ausführbaren Teil haben), ein Body ohne Spezifikation ist unmöglich (was soll denn der Body tun, wenn er von aussen nicht zugreifbar ist?).
Spezifikation
Fangen wir mit der Spezifikation an:
create package banking is
procedure new_acct(acno NUMBER, name IN VARCHAR);
procedure acct_dep(acno IN NUMBER, amount IN NUMBER);
procedure acc_wdr(acno IN NUMBER, amount IN NUMBER);
procedure acc_bal(acno IN NUMBER, bal OUT NUMBER);
function acc_drwn(acno IN NUMBER) RETURN BOOLEAN;
end banking;
/
Auch die Schnittstelle von Packages kann mit dem DESCRIBE-Kommando eingesehen werden:
SQL>desc banking;
PROCEDURE ACCT_DEP
Argument Name Type In/Out Default?
------------- ---- ------ --------
ACNO NUMBER IN
AMOUNT NUMBER IN
PROCEDURE ACC_BAL
Argument Name Type In/Out Default?
------------- ---- ------ --------
ACNO NUMBER IN
BAL NUMBER OUT
FUNCTION ACC_DRWN RETURNS BOOLEAN
Argument Name Type In/Out Default?
------------- ---- ------ --------
ACNO NUMBER IN
PROCEDURE ACC_WDR
Argument Name Type In/Out Default?
------------- ---- ------ --------
ACNO NUMBER IN
AMOUNT NUMBER IN
PROCEDURE NEW_ACCT
Argument Name Type In/Out Default?
------------- ---- ------ --------
ACNO NUMBER IN
NAME VARCHAR2 IN
Wie bei allen PL/SQL-Objekten kann auch der Source-Code von Packages aus der View user_source ermittelt werden:
SQL>select text from user_source where name = 'BANKING';
TEXT
----------------------------------------------
package banking is
procedure new_acct(acno IN number, name IN VARCHAR);
procedure acct_dep(acno IN NUMBER, amount IN NUMBER);
procedure acc_wdr(acno IN NUMBER, amount IN NUMBER);
procedure acc_bal(acno IN NUMBER, bal OUT NUMBER);
function acc_drwn(acno IN NUMBER) RETURN BOOLEAN;
end banking;
Implementierung
Nach der Spezifikation geht es nun an die Implementierung.
Jetzt wollen wir den Package Body erzeugen. Der Body enthält den eigentlichen ausführbaren Code. Die Syntax für die Implementierung ist die gleiche, als wären die Prozeduren und Funktionen nicht Teil einer Package.
create or replace package body banking is
procedure new_acct ( acno IN NUMBER, name IN varchar) is
begin
null;
end;
procedure acct_dep(acno IN NUMBER, amount IN NUMBER) is
begin
null;
end;
procedure acc_wdr(acno IN NUMBER, amount IN NUMBER) is
begin
null;
end;
procedure acc_bal(acno IN NUMBER, bal OUT NUMBER) is
begin
null;
end;
function acc_drwn(acno IN NUMBER) RETURN BOOLEAN is begin
null;
end;
end banking;
/
Beispiel
Beachten Sie, dass unsere Prozeduren und Funktionen noch keine Zeile Code enthalten, lediglich NULL. Wir wollen also stückweise unsere Package mit Funktionalität ausstatten. Der Package Body muss die gleichen Prozeduren und Funktionen enthalten (inklusive der gleichen Argumente) wie die, die in der Package Spezifikation bekannt gemacht sind, sonst erhalten Sie einen ORACLE-Fehler.
Bevor wir weitermachen, brauchen wir unsere Kontentabelle.
create table accounts
(acno number(10),
name varchar2(20),
balance number(10,2));
Jetzt können wir beginnen, die Prozedur new_acct zu implementieren, um neue Kundenkonten zu öffnen.
create or replace package body banking is
procedure new_acct ( acno IN number, name IN varchar) is
begin
insert into accounts
(acno, name, balance)
values
(acno, name,0);
end;
procedure acct_dep(acno IN NUMBER, amount IN NUMBER) is
begin
null;
end;
procedure acc_wdr(acno IN NUMBER, amount IN NUMBER) is
begin
null;
end;
procedure acc_bal(acno IN NUMBER, bal OUT NUMBER) is
begin
null;
end;
function acc_drwn(acno IN NUMBER) RETURN BOOLEAN is
begin
null;
end;
end banking;
/
Nun eröffnen wir für Herrn Simpson ein Konto:
begin
banking.new_acct(123,'SIMPSON');
end;
/
SQL> select * from accounts;
ACNO NAME BALANCE
---------- -------------------- ----------
123 SIMPSON 0
Das neue Konto ist eröffnet! Alternativ können wir am SQL-Prompt auch eingeben:
SQL>execute banking.new_accnt(123,'SIMPSON');
Beachten Sie, dass wir diesmal gezwungen sind, die Prozedur durch das Voranstellen des Package-Namens weiter zu qualifizieren. Prozedurnamen können ja in verschiedenen Packages gleich heissen.
Nun ergänzen wir die Funktionalität der restlichen Komponenten:
create or replace package body banking is
procedure new_acct ( acno IN number,
name IN varchar) is
begin
insert into accounts
(acno, name, balance)
values
(acno, name,0);
end;
procedure acct_dep(acno IN NUMBER,
amount IN NUMBER) is
begin
update accounts
set balance = balance + amount
where acno = acno;
end;
procedure acc_wdr(acno IN NUMBER,
amount IN NUMBER) is
begin
update accounts
set balance = balance - amount
where acno = acno;
end;
procedure acc_bal(acno IN NUMBER,
bal OUT NUMBER) is
begin
declare cursor c_balance(i_acno IN accounts.acno%type) is
select balance
from accounts
where acno = i_acno;
acc_bal accounts.balance%type;
begin
if c_balance%isopen then
close c_balance;
end if;
open c_balance(acno);
fetch c_balance into acc_bal;
close c_balance;
bal = acc_bal;
end;
end;
function acc_drwn(acno IN NUMBER) RETURN BOOLEAN is
begin
declare cursor c_balance(i_acno IN accounts.acno%type) is
select balance
from accounts
where acno = i_acno;
bal accounts.balance%type;
begin
if c_balance%isopen then
close c_balance;
end if;
open c_balance(acno);
fetch c_balance into bal;
close c_balance;
if bal < 0 then
return true;
else
return false;
end if;
end;
end;
end banking;
/
Test
Lassen Sie uns testen:
SQL>execute banking.acct_dep(123,100);
PL/SQL procedure successfully completed.
SQL> select * from accounts;
ACNO NAME BALANCE
---------- -------------------- ----------
123 SIMPSON 100
Homer hat 100 Dollar eingezahlt! Jetzt hebt er wieder was ab:
SQL>execute banking.acc_wdr(123,50);
SQL> select * from accounts;
ACNO NAME BALANCE
---------- -------------------- ----------
123 SIMPSON 50
Mit Hilfe der implementierten Funktion ermitteln wir nun den aktuellen Kontostand: Beachten Sie, das wieder SERVEROUTPUT auf ON geschaltet sein muss.
SQL> set serveroutput on
SQL> begin
2 declare
3 bal accounts.balance%type;
4 begin
5 banking.acc_bal(123,bal);
6 dbms_output.put_line('Homers balance is '||bal);
7 end;
8 end;
9 /
Homers balance is 50
PL/SQL procedure successfully completed.
Jetzt prüfen wir, ob Mr. Simpson noch einen positiven Kontostand hat:
set serveroutput on
SQL> begin
2 declare
3 overdrawn boolean;
4 begin
5 if banking.acc_drwn(123) then
6 dbms_output.put_line('Homer is overdrawn');
7 else
8 dbms_output.put_line('Homer has sufficent funds');
9 end if;
10 end;
11 end;
12 /
Homer has sufficient funds
PL/SQL procedure successfully completed.
Berechtigungen
Bis jetzt haben wir die ganze Zeit im selben Schema gearbeitet. Wir aber bereits erwähnt, ist es möglich, einem User lediglich Zugriff auf die Package, nicht aber auf die durch sie modifizierten Tabellen zu geben. Damit kapseln wir die Zugriffsmöglichkeiten durch den Einsatz der Package. Wir erzeugen zwei Public Synonyms für das Package und die Tabelle. Dann geben wir dem Nutzer FRED das Recht, die Package auszuführen.
SQL>create public synonym accounts for accounts;
SQL>create public synonym banking for banking;
SQL>grant execute on banking to fred;
Wenn Nutzer Fred auf die Tabelle zugreifen will, erhält er einen Fehler:
SQL> select * from accounts;
select * from accounts
*
ERROR at line 1:
ORA-01031: insufficient privileges
Über die Package ist der Zugriff auf die Tabelle aber möglich.
SQL> execute banking.acct_dep(123,500);
PL/SQL procedure successfully completed.
Fred kann auch über seine eigenen PL/SQL Blöcke zugreifen:
SQL> set serveroutput on
SQL> begin
2 declare
3 bal accounts.balance%type;
4 begin
5 banking.acc_bal(123,bal);
6 dbms_output.put_line('Homers balance is '||bal);
7 end;
8 end;
9 /
Homers balance is 550
PL/SQL procedure successfully completed.
Sie sehen also, dass man mit Packages modularisierten Code erzeugen kann und diesen bspw. nutzen kann, um Zugriffsmöglichkeiten auf Daten einzuschränken. Angemessen ist hier sicherlich, wenn unser Package auch prüft, wer wessen Kontostand abfragen darf…
Das Verhalten eines Stored Objects bei Zugriffen durch Nicht-Eigentümer kann gesteuert werden über den Zusatz AUTHID CURRENT_USER||DEFINER Dazu ein getrennter Artikel…