Oracle PL/SQL Tutorial: Datenbank-Programmierung

Inhalt Oracle PL/SQL

PL/SQL Block

Bildschirmausgaben

PL/SQL Datentypen

Deklarationen

Operatoren

Funktionen

Ablaufsteuerung

SQL in PL/SQL

Fehlerbehandlung

Tables

Packages

Oracle PL/SQL-Bücher

Prinzip

Stored Procedures, Functions und Packages sind Prozeduren und Funktionen, die in der Datenbank gespeichert und verwaltet werden. Sie werden in übersetzter Form abgespeichert. Die Verwaltung in der Datenbank erfolgt analog zu anderen Datenbank-Objekten.

Weitere Details und eine Beschreibung der vordefinierten Packages sind in dem Buch Oracle PL/SQL Programmierung zu finden.

Erzeugen und Speichern in der Datenbank

create [ or replace ] procedure prozedur_name

create [ or replace ] function funktions_name

create [ or replace ] package package_name

create [ or replace ] package body package_name

Ausgabe der Kompilierungsfehler

show errors - letzter Kompilierungsbefehl

show errors [FUNCTION | PROCEDURE | PACKAGE | PACKAGE BODY] name

select name, line, position, text
  from user_errors
  where name = 'PROZEDUR_TEST'
  order by line, position;

Aufruf von Stored Procedures und Functions 

Beispiel für einen direkten Aufruf einer Prozedur in SQL*Plus:

execute prozedur_test (100, 200);

Beispiel für einen direkten Aufruf einer Funktion in SQL*Plus:

variable v_ergebnis number;

execute :v_ergebnis := funktion_test (20);

oder

declare
  v_ergebnis number;
begin
  v_ergebnis := funktion_test (20);
end;

Anzeige von Stored Procedures, Functions oder Packages 

Anzeige einer Stored Procedure, Function oder Package

desc [FUNCTION | PROCEDURE | PACKAGE | PACKAGE BODY]

select text
  from user_source
  where name = ' PROZEDUR_TEST'
  order by line;

Anzeige aller Stored Procedures, Functions und Packages

select object_name, status
  from user_objects
  where object_type = 'PROCEDURE'
  order by object_name;

select object_name, status
  from user_objects
  where object_type = 'FUNCTION'
  order by object_name;

select object_name, status
  from user_objects
  where object_type like 'PACKAGE%'
   order by object_name;

Status INVALID 

Hat eine Prozedur, Funktion oder Package den Status "invalid", so ist das Objekt zwar in der Datenbank abgelegt, es ist aber nicht übersetzt. Der nächste Aufruf führt automatisch zum erneuten Übersetzen. Es ist möglich, dass das Programm Fehler enthält oder ein abhängiges Objekt geändert wurde.

Manuelles erneutes Übersetzen:

alter procedure prozedur_name compile;

alter function funktions_name compile;

alter package package_name compile;

alter package body package_name compile;

Bestimmen des Status:

select * from user_objects where status = 'INVALID';

Verwendung von Stored Functions in SQL 

Eine Stored Function kann prinzipiell in SQL-Befehlen verwendet werden. Es gibt jedoch eine Reihe von Einschränkungen, die von den Seiteneffekten der Funktion abhängig sind. Funktionen aus Packages müssen zur Verwendung in SQL immer eine PRAGMA RESTRICT_REFERENCES-Anweisung enthalten.

Einschränkungen 

• Die Funktion darf keine UPDATE, INSERT oder DELETE-Befehle enthalten
• Funktionen, die Package-Variablen verwenden, können nicht remote oder parallel ausgeführt werden
• Nur Funktionen, die über SELECT-Befehle aufgerufen werden, können Package-Variablen schreiben
• Die Parameter und der Rückgabewert müssen Datenbanktypen besitzen
• OUT und IN OUT-Parameter sind nicht erlaubt

Packages 

Eine Package ist eine Zusammenfassung von PL/SQL-Objekten. Sie ist unterteilt in Header und Body. Beide Teile sind getrennte Datenbank-Objekte. Der Header enthält den öffentlichen Teil (Spezifikation), der von außen sichtbar ist. Der Body ist der private Teil (Programm). In ihm werden die im Header deklarierten Prozeduren und Funktionen implementiert.

Vorteile von Packages

• Modularität
• Information Hiding
• Persistente Variablen und Cursor

Nachteil:

Ein erneutes Übersetzen einer Package kann zu Problemen führen. Enthält die Package globale Variablen oder Cursor, so verlieren diese Variablen ihren Wert. Das führt entweder zu einem Laufzeitfehler für alle Sessions, die diese Package verwenden oder zu einer erneuten Initialisierung. Ggf. müssen die Anwender die aktuelle Session beenden und neu starten.

Package Header

CREATE [ OR REPLACE ] PACKAGE package_name AS

öffentliche Deklarationen von:

• Typen
• Variablen
• Konstanten
• Cursors
• Exceptions
• Funktionen
• Prozeduren

END package_name;

Die öffentlich deklarierten Objekte können mit der Punkt-Notation direkt angesprochen werden. Die Variablen und Cursor, die im Header deklariert sind, sind persistent für jede Session, d.h. sie sind während der gesamten Dauer einer Session gültig.

Package Body

CREATE [ OR REPLACE ] PACKAGE BODY package_name AS

private Deklarationen von:

• Typen
• Variablen
• Konstanten
• Cursors
• Exceptions

Implementierung der öffentlichen und privaten Prozeduren und Funktionen

[BEGIN]

  Initialisierungsblock

END package_name;

Die privat deklarierten Objekte sind ebenfalls persistent für jede Session. Sie sind jedoch von außen nur über Prozeduren und Funktionen indirekt verwendbar. Der Initialisierungsblock wird einmalig beim ersten Zugriff ausgeführt. Die Köpfe der öffentlich deklarierten Prozeduren und Funktionen müssen im Body und Header identisch sein.

Overloading 

Es ist möglich, Prozeduren und Funktionen mit dem selben Namen, aber unterschiedlichen Parametern zu deklarieren. Anhand der Aufruf-Parameter wird dann automatisch die richtige Prozedur/Funktion bestimmt.

Beispiel:

create package ovl as
  procedure ausgabe (p in varchar2);
  procedure ausgabe (p in boolean);
end;

create package body ovl as

  procedure ausgabe (p in varchar2) is
  begin
    dbms_output.put_line ('Ausgabe => ' || p);
  end;

  procedure ausgabe (p in boolean) is
  begin
    if p then
      dbms_output.put_line ('Ausgabe => TRUE');
    else
      dbms_output.put_line ('Ausgabe => FALSE');
    end if;
  end;

end;

Aufrufe:

execute ovl.ausgabe ('Hallo');

execute ovl.ausgabe (8 > 9);

Beispiel einer Package

create or replace package perf as

  procedure start_timer;

  procedure stop_timer;

  function get_time return number; -- Zeit in Sekunden

end perf;

create or replace package body perf as

  SEK_JE_TAG constant number := 86400;

  v_start_time date := NULL;

  v_stop_time date := NULL;

procedure start_timer is
begin
  v_start_time := sysdate;
  v_stop_time := NULL;
end;

procedure stop_timer is
begin
  v_stop_time := sysdate;
end;

function get_time return number is
  v_diff_time number;
begin
  if v_start_time is NULL then
    return NULL;
  end if;
  if v_stop_time is NULL then
    v_diff_time := sysdate - v_start_time;
  else
    v_diff_time := v_stop_time - v_start_time;
  end if;
  return v_diff_time * SEK_JE_TAG;
end;

end perf;

Verwendung von Package-Funktionen in SQL 

Zur Verwendung einer Package-Funktion in SQL ist ein Pragma RESTRICT_REFERENCES zu deklarieren. Damit erfolgt die Definition der Einschränkungen der Funktion manuell (im Gegensatz zu Stored Functions). Die Pragma-Anweisung steht im Package Header nach der Deklaration der entsprechenden Funktion.

PRAGMA RESTRICT_REFERENCES ( funktions_name,
  WNDS [, WNPS] [, RNDS] [, RNPS])

WNDS    writes no database state

WNPS    writes no package state

RNDS    reads no database state

RNPS    reads no package state

Beispiel:

create or replace package perf as

  procedure start_timer;
  procedure stop_timer;
  function get_time return number; -- Zeit in Sekunden
  pragma restrict_references (get_time, WNDS, WNPS, RNDS);

end perf;