Anlegen von Packages

Packages bestehen aus Prozeduren, Funktionen, Cursors, Variablen, Konstanten oder Ausnahmen. Was ist der Vorteil, diese in einer Package zu sammeln anstelle sie einzeln zu nutzen?

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…

Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert.