ORACLE PLSQL Grundlagen der Datenbankentwicklung » Syntax kurz erklärt

Oracle PLSQL Syntax ist insofern einmalig, als dass PLSQL (PL/SQL) die Flexibilität von SQL mit der Funktionalität der 3GL Programmierung kombiniert. Die prozeduralen Elemente sind nahtlos in Oracle SQL integriert, sodass eine strukturierte und leistungsfähige Programmiersprache entstanden ist. PL/SQL steht für Procedural Language SQL.



CTPM » Business • Health • Travel • Move - Stefan Müller CTPM » Business • Health • Travel • Move - XING CV - Stefan Müller

Autor: Stefan Müller CEO & Senior Consultant

CTPM » Business • Health • Travel • Move

Köln • Berlin • Lindlar im Bergischen Land


 
 Event Katalog 📅 

Inhalt

Inhalt

1. PL/SQL-Grundlagen
 1.1 TECHNOLOGIE
  1.1.1 Warum PL/SQL ?
  1.1.2 Das Client / Server – Modell
 1.2 SYNTAX
  1.2.1 Der PL/SQL Block
  1.2.2 Variablendeklaration
  1.2.3 SELECT
  1.2.4 INSERT
  1.2.5 UPDATE
  1.2.6 DELETE
  1.2.7 CURSOR
   1.2.7.1 Explizite Cursor
   1.2.7.2 Cursor-Attribute
   1.2.7.3 Parametrisierte Cursor
   1.2.7.4 Select for Update – Cursor
   1.2.7.5 Implizite Cursor
   1.2.7.6 Cursor Variablen
  1.2.8 Schleifen
   1.2.8.1 Einfache- oder Endlosschleife
   1.2.8.2 WHILE-Schleife
   1.2.8.3 numerische For-Schleife
   1.2.8.4 FOR-Schleife mit Cursor
   1.2.8.5 FOR-Schleife mit SELECT
  1.2.9 GOTOs und Marken
  1.2.10 IF… THEN… ELSE…
  1.2.11 NULL-Anweisung
  1.2.12 Integrierte SQL-Funktionen
 1.3 EINBINDUNG IN ORACLE TOOLS
 1.4 FEHLERBEHANDLUNG
 1.5 BENUTZERDEFINIERTE DATENTYPEN
2. PL/SQL-Datenbankprogrammierung
 2.1 DDL-ANWEISUNGEN AUSFÜHREN
 2.2 ENTWICKLUNG VON PROZEDUREN- UND FUNKTIONEN
  2.2.1 Eine Prozedur erstellen
  2.2.2 Eine Funktion erstellen
  2.2.3 Prozeduren und Funktionen löschen
  2.2.4 Lokale Unterprogramme
  2.2.5 Abhängigkeiten zwischen den Unterprogrammen
  2.2.6 Die Berechtigung zu EXECUTE
 2.3 ENTWICKLUNG VON PAKETEN
  2.3.1 Die Paketspezifikation
  2.3.2 Der Paketrumpf
  2.3.3 Das Überladen von Unterprogrammen
 2.4 TRIGGER
  2.4.1 Trigger auf Zeilenebene (create trigger … for each row)
  2.4.2 Trigger auf Anweisungsebene (create trigger …)
  2.4.3 Trigger auf Datenbankebene
  2.4.4 BEFORE- und AFTER-Trigger
  2.4.5 INSTEAD OF-Trigger (ab Oracle8)
  2.4.6 WHEN-Klausel
  2.4.7 Trigger für DDL-Ereignisse (ab Oracle8i)
  2.4.8 Trigger löschen
 2.5 ANALYSE UND FEHLERSUCHE
  2.5.1 Daten in eine Testtabelle einfügen
  2.5.2 DBMS_OUTPUT
 2.6 VERWENDUNG VON STANDARD-PACKAGES ( BUILT-IN PACKAGES)
 2.7 ÜBERBLICK ÜBER LARGE-OBJECTS (LOB)


1. PL/SQL-Grundlagen


TECHNOLOGIE

 1.1 TECHNOLOGIE

PL/SQL ist eine prozedurale Sprache, die speziell dafür entwickelt wurde, SQL-Anweisungen in ihrer Syntax zu berücksichtigen. PL/SQL-Programmeinheiten werden vom Oracle-Datenbankserver kompiliert und in der Datenbank gespeichert. Zur Laufzeit laufen sowohl PL/SQL als auch SQL innerhalb desselben Serverprozesses, was zu einer optimalen Effizienz führt. PL/SQL erbt automatisch die Robustheit, Sicherheit und Portabilität der Oracle-Datenbank.


1.1.1 Warum PL/SQL ?

Oracle ist eine relationale Datenbank. Die Sprache, mit der auf relationale Datenbanken zugegriffen werden kann, ist die Structured Query Language (SQL – sprich: sequel). SQL ist eine flexible und effiziente Sprache, die über Funktionen verfügt, mit denen relationale Daten verändert oder untersucht werden können.

DELETE FROM emp WHERE job = ‚CLERK‘;

SQL ist eine Programmiersprache der vierten Generation, eine sogenannte 4GL-Sprache. Dies heißt, dass die Programmiersprache beschreibt, was getan werden soll, nicht wie es getan werden soll. In der oben dargestellten DELETE-Anweisung beispielsweise wissen wir nicht, wie die Datenbank tatsächlich die Mitarbeiter ermittelt, die als Beruf ‘CLERK’ haben. Vermutlich wird der Server alle Mitarbeiter in einer bestimmten Reihenfolge untersuchen, um die zu löschenden Einträge zu finden. Das genaue Verfahren bleibt jedoch verborgen.

PL/SQL steht für Procedural Language/SQL. Wie der Name sagt, erweitert PL/SQL die SQL, um Elemente aus anderen prozeduralen Sprachen, wie z.B.:

  • Variablen und Typen (sowohl vordefinierte als auch benutzerdefinierte)
  • Steuerstrukturen wie IF-THEN-ELSE-Anweisungen und Schleifen
  • Proceduren und Funktionen
  • Objekttypen und Methoden (ab PL/SQL Version 8 und höher)

Die prozeduralen Elemente sind nahtlos in Oracle SQL integriert, so dass eine strukturierte und leistungsfähige Programmiersprache entstanden ist. Angenommen, wir wollen beispielsweise den Beruf eines Mitarbeiters ändern. Wenn der Mitarbeiter noch nicht existiert, muß zunächst ein neuer Datensatz erstellt werden. Hierzu kann folgendes SQL-Programm verwendet werden:

DECLARE

   /* Beschreibung der Variablen, die in diesen SQL-Statements gebraucht werden */

   v_Job VARCHAR2(10) := ‚Trainer‘;

   v_Name VARCHAR2(10) := ‚Mueller‘;

BEGIN

   /* Update der Mitarbeiter-Tabelle */

  UPDATE emp

        SET job = v_Job

  WHERE ename = v_Name

      AND job = ‚CLERK‘;

/* Prüfen ob der Satz gefunden wurde. Wenn nicht wird der Satz angelegt. */

  IF SQL%NOTFOUND THEN

      INSERT INTO emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)

      VALUES (7955, v_Name, v_Job, 7698, sysdate, 3500, 0, 30);

   END IF;

END;

/

Dieses Beispiel enthält zwei verschiedene SQL-Anweisungen (UPDATE und INSERT) sowie mehrere Variablendeklarationen und die bedingte IF-Anweisung.

Die PL/SQL ist insofern einmalig, als dass sie die Flexibilität von SQL mit der Funktionalität und Anpassungsfähigkeit einer 3GL kombiniert.

Die notwendigen prozeduralen Elemente und der Datenbankzugriff sind damit in einer Programmiersprache vereint. Das Ergebnis ist eine robuste, leistungsfähige Programmiersprache, mit der auch komplexe Anwendungen erstellt werden können.


1.1.2 Das Client / Server – Modell

Viele Datenbankanwendungen werden mit Hilfe des Client-Server-Modells erstellt. Die Anwendung läuft dabei selbst auf dem Client-Rechner ab und schickt dabei Anfragen nach Informationen an einen Datenbankserver. Die Anfragen werden in SQL ausgeführt. Gewöhnlich bedeutet dies, dass für eine SQL-Anweisung eine Reihe von Netzaktivitäten anfallen. Dies wird in dem Schaubild auf der linken Seite dargestellt. Vergleicht man dies mit der Situation im rechten Schaubild, stellt man fest, dass mehrere SQL-Anweisungen in einem PL/SQL-Block gebündelt und dem Server als eine Einheit geschickt werden können. Hierdurch sinkt die Netzbelastung, und die Anwendung wird beschleunigt.

oracle sql vs plsql - die Unterschiede in einer Grafik

Auch wenn Client und Server auf demselben Rechner ablaufen, wird die Leistung verbessert. In diesem Fall gibt es zwar kein Netzwerk, aber die Zusammenfassung von SQL-Anweisungen führt immer noch zu einem einfacheren Programm, das mit weniger Datenbankaufrufen auskommt.

Weil PL/SQL sowohl in der Datenbank (für gespeicherte Prozeduren und Datenbanktrigger) als auch im Applikations-Code (beispielsweise für die Logik in einem Formular) verwendet wird, können Sie Ihr Wissen, sowohl für clientseitige als auch serverseitige Entwicklung anwenden. Sie können sogar Programme von einer Komponente der Konfiguration zu einer anderen verschieben (solange es keine Konflikte zwischen den verschiedenen PL/SQL-Versionen gibt). Beispielsweise könnten Sie sich überlegen, dass eine Funktion, die ursprünglich für eine einzige Bildschirmmaske gedacht war, von allen Modulen in einer Applikation verwendet werden könnte. Dazu verschieben Sie diese Funktion einfach von der clientseitigen Formularkomponente in die serverseitige Datenbankumgebung. Sie müssen weder den PL/SQL-Code noch irgendwelche Programme, die diesen Code verwenden, ändern.

Natürlich ist PL/SQL als eine zugrundeliegende Technologie der Oracle-Produktfamilie nur ein Element, mit dem man beim Programmieren konfrontiert wird. Das Entwickeln einer Oracle-basierten Anwendung ist immer eine Kombination aus Technologie und Techniken.

PLSQL - Client Server Grafik



SYNTAX

 1.2 SYNTAX

Die Grundlage der „Grammatik“ einer jeden Programmiersprache/-Code ist die Syntax.


1.2.1 Der PL/SQL Block

Die grundlegende Einheit jedes PL/SQL-Programms ist der Block. Alle PL/SQL-Programme setzen sich aus Blöcken zusammen, die sowohl der Reihe nach auftreten (einer nach dem anderen), als auch ineinander verschachtelt sein können.

Jeder Block besteht aus drei verschiedenen Abschnitten – dem Deklarationsabschnitt (Beschreibungsabschnitt), dem Ausführungs-abschnitt und dem Abschnitt für Exceptions (Fehlerbehandlungs-abschnitt). Lediglich der Ausführungsabschnitt ist zwingend erforderlich, die beiden anderen sind optional. Das folgende Beispiel zeigt einen anonymen Block mit allen drei Abschnitten:

DECLARE

       /* Hier befindet sich der Deklarations-

         abschnitt */

BEGIN

       /* Hier befindet sich der Ausführungs-

         abschnitt */

EXCEPTION

       /* Hier befindet sich der EXCEPTION-

         abschnitt */

END;

Es gibt verschiedene Arten von Blöcken:

  • Anonyme Blöcke werden im allgemeinen dynamisch erzeugt und nur ein einziges Mal ausgeführt.

set serveroutput on

 

DECLARE

 v_ename varchar2(30);

BEGIN

 select ename

   into v_ename

   from emp

  where ename = ‚Mueller‘;

 dbms_output.put_line (v_ename);

END;

/ 

   

  • Benannte Blöcke sind anonyme Blöcke, die über ein Label verfügen, das dem Block einen Namen gibt. Auch sie werden im allgemeinen dynamisch erzeugt und nur ein einziges Mal ausgeführt.

set serveroutput on

<<demo>>

DECLARE

 v_ename varchar2(30);

BEGIN

 select ename

   into v_ename

   from emp

  where ename = ‚Mueller‘;

 dbms_output.put_line (v_ename);

END demo;

/ 

  

  • Unterprogramme sind Prozeduren, Pakete und Funktionen, die in einer Datenbank gespeichert sind. Diese Blöcke verändern sich im allgemeinen nicht mehr, nachdem sie einmal erstellt wurden, und sie werden in der Regel viele Male ausgeführt. Unterprogramme werden durch direkten Aufruf einer Prozedur, eines Pakets oder einer Funktion ausgeführt.

set serveroutput on

CREATE OR REPLACE PROCEDURE lese_ename AS

            v_ename varchar2(30);

BEGIN

   SELECT ename

     INTO v_ename

     FROM emp

    WHERE ename = ‚Mueller‘;

           dbms_output.put_line (v_ename);

END lese_ename;

/

execute lese_ename

  • Trigger sind benannte Blöcke, die ebenfalls in der Datenbank gespeichert sind. Auch sie verändern sich gewöhnlich nicht mehr, nachdem die erstellt wurden und werden ebenfalls viele Male ausgeführt. Trigger werden immer dann ausgelöst, wenn das zu dem Trigger gehörende Ereignis auftritt. Dieses Trigger-Ereignis ist eine Anweisung in einer Datenmanipulationssprache (DML), die auf eine Tabelle in der Datenbank angewendet wird. DML-Anweisungen enthalten INSERT-, UPDATE– und DELETE-Befehle.

 

CREATE OR REPLACE TRIGGER only_positiv_sal

    BEFOR INSERT OR UPDATE OF sal

    ON emp

    FOR EACH ROW

BEGIN

    IF :new.sal < 0 THEN

        RAISE_APPLICATION_ERROR

    (-20100,’Bitte positiven Wert eingeben‘);

    END IF;

END only_positiv_sal;


1.2.2 Variablendeklaration

Die Kommunikation mit der Datenbank findet über die Variablen innerhalb des PL/SQL-Blocks statt. Variablen sind Stellen im Speicher, die Datenwerte aufnehmen können. Wenn das Programm ausgeführt wird, kann sich der Inhalt der Variablen verändern. Die Informationen aus der Datenbank können Variablen zugewiesen werden. Diese Variablen werden im Deklarationsabschnitt eines Blockes definiert. Jede Variable besitzt einen bestimmten Typ, der festlegt, welche Art von Informationen in der Variablen gespeichert sein können.

variable_name typ [constant] [not null] [:=value];

Wobei variable_name der Name der Variablen ist, typ ihr Datentyp und value ein Initialisierungswert der Variablen.

Beispiele:

falsch ist:

DECLARE

v_temp number not null;

richtig ist:

DECLARE

v_temp number not null := 0;

falsch ist:

DECLARE

v_temp1, v_temp2 varchar2(20);

richtig ist:

DECLARE

v_temp1 varchar2(20);
v_temp2 varchar2(20);

Wenn die Option CONSTANT in der Variablendeklaration erscheint, muß die Variable initialisiert werden. Ihr Wert kann innerhalb des Blocks nicht mehr geändert werden. Eine konstante Variable kann daher gelesen, aber nicht mehr überschrieben werden.

Beispiel:

falsch ist:

DECLARE
v_temp constant number(5);

richtig ist:

DECLARE
v_temp constant number(5) := 10000;

Statt := kann auch DEFAULT verwendet werden.

Beispiel:

richtig ist:

DECLARE

v_temp number(5) := 10000;

auch richtig ist:

DECLARE

v_temp number(5) default 10000;

In PL/SQL ist der Inhalt einer nicht initialisierten Variablen definiert. Ihr wird der Wert NULL zugewiesen. NULL bedeutet soviel wie „fehlender oder unbekannter Wert“.


Praxisbeispiele
  • %TYPE und %ROWTYPE – ORACLE PL/SQL » Syntax kurz erklärt
  • REFCURSOR mit WHERE IN – ORACLE PL/SQL » Syntax kurz erklärt
  • ORACLE MULTISET + object type + nested table » Syntax kurz erklärt

  • 1.2.3 SELECT

    Eine SELECT-Anweisung ruft Daten aus einer Datenbank ab und übergibt diese an PL/SQL-Variablen. Die allgemeine Form einer SELECT-Anweisung lässt sich folgendermaßen darstellen:

    SELECT feld
       INTO variable
      FROM tabelle
    WHERE where_clausel;

    Die hier dargestellte Form der SELECT-Anweisung sollte nicht mehr als eine Zeile zurückliefern. Die WHERE-Klausel wird mit jeder Zeile der Tabelle verglichen. Wenn mehr als eine Zeile diese Kriterien erfüllt, liefert PL/SQL die Fehlermeldung

    ORA-01422: Exakter Abruf gibt mehr als die angeforderte
    Zeilenzahl zurück

    In diesem Fall sollte ein Cursor verwendet werden (oder ‚for rec in (select…)‘) siehe Kapitel Cursor bzw. FOR-Schleifen.


    1.2.4 INSERT

    Eine INSERT-Anweisung fügt Daten in eine Datenbank ein. Die Syntax einer INSERT-Anweisung sieht wie folgt aus.

    INSERT INTO tabelle
    (Feld, Feld1, …)
    VALUES (wert1, wert2,…)

    oder

    INSERT INTO tabelle
    (Feld, Feld1, …)
    SELECT wert1, wert2, …
    FROM tabelle
    WHERE wert3 = 100;

    Beachten Sie, dass die INSERT-Anweisung keine direkte WHERE-Klausel enthält (auch wenn in einer untergeordneten Abfrage eine auftreten kann).


    Praxisbeispiele
  • Generator für INSERT SQL » dynamisch & automatisch » Syntax kurz erklärt

  • 1.2.5 UPDATE

    Eine UPDATE-Anweisung ändert bestehende Daten in einer Datenbank ab. Die Syntax für eine UPDATE-Anweisung lautet:

    UPDATE tabelle
    SET Feld1 = wert1
    WHERE Feld1 = 100;

    oder

    UPDATE tabelle1
    SET (Feld1,Feld2,…) = (SELECT wert1, wert2, …
    FROM tabelle2
    WHERE wert3 = 100)
    WHERE Feld1 = 100;

    Beispiel:

    BEGIN
    UPDATE emp
    SET (ename, sal) = (SELECT upper(ename), sal + 100
    FROM emp
    WHERE ename = ‚Mueller‘)
    WHERE ename = ‚Mueller‘;
    END;
    /


    1.2.6 DELETE

    Mit der DELETE-Anweisung werden Zeilen aus einer Datenbanktabelle entfernt. Die WHERE-Klausel in der Anweisung gibt an, welche Zeilen entfernt werden sollen. Die Syntax der DELETE-Anweisung lautet:

    DELETE from tabelle
    WHERE Feld1 = 100;


    1.2.7 CURSOR

    Um eine SQL-Anweisung zu verarbeiten, reserviert Oracle einen Speicherbereich, der auch als Kontextbereich bezeichnet wird. Der Kontextbereich enthält Informationen, die für die Verarbeitung benötigt werden, wie z.B. die Anzahl der Zeilen, die von der Anweisung bearbeitet werden sollen, einen Zeiger auf die analysierte Darstellung der Anweisung sowie im Fall einer Abfrage die aktive Gruppe d.h. die Gruppe von Zeilen, die von der Abfrage zurückgeliefert wird.

    Ein Cursor ist ein Zeiger auf den Kontextbereich. Mit Hilfe des Cursors kann ein PL/SQL-Programm den Kontextbereich während der Verarbeitung einer Anweisung steuern.

    1.2.7.1 Explizite Cursor

    Für die Verarbeitung eines expliziten Cursors sind folgende vier Schritte nötig:

    1. Die Deklaration des Cursors.
    2. Das Öffnen des Cursors für eine Abfrage.
    3. Die Übergabe der Ergebnisse an PL/SQL-Variablen.
    4. Das Schließen des Cursors

    Die Deklaration des Cursors ist der einzige Schritt, der im Deklarationsabschnitt des Blocks stattfindet – die übrigen drei Schritte werden im Ausführungs- bzw. im Exception-Abschnitt durchgeführt.

    1. Die Deklaration des Cursors

    Bei der Deklaration eines Cursors wird der Name des Cursors definiert und mit einer SELECT-Anweisung verknüpft. Die Syntax hierfür lautet:

    CURSOR cursor_name IS SELECT_statement;

    Die SELECT-Anweisung enhält keine INTO-Kausel. Diese ist Teil der
    FETCH-Anweisung (siehe 3. Die Übergabe der Ergebnisse an PL/SQL-Variablen).

    PL/SQL-Variablen, die in der WHERE-Klausel des CURSOR-SELECT-Statements verwendet werden, werden oberhalb des CURSORs im DECLARE-Bereich definiert.
    Um sicherzustellen, dass alle Variablen, die in einer CURSOR-Deklaration verwendet werden, auch definiert sind, sollten alle CURSOR am Ende des Deklarationsabschnitts deklariert werden.

    2. Das Öffnen des Cursors für eine Abfrage

    Die Syntax für das Öffnen des Cursors lautet

    OPEN cursor_name;

    Beim Öffnen des Cursors geschieht folgendes:

    • Die Werte der Bindevariablen (WHERE-Klausel) werden untersucht.
    • Anhand der Werte der Bindevariablen wird die aktive Gruppe ermittelt
    • Der Zeiger auf die aktive Gruppe wird auf die erste Zeile gesetzt

    Die Bindevariablen (z.B. v_ename) werden nur beim Öffnen eines Cursors überprüft, d.h. ändern sich nach dem OPEN die Inhalte der Bindevariablen, ändert sich nicht die aktive Gruppe der Abfrage. Diese Eigenschaft wird als Lesekonsistenz bezeichnet und sorgt für die Integrität der Daten.
    Um die aktive Gruppe entsprechend der geänderten Bindevariablen anzupassen, müsste der Cursor einmal geschlossen und wieder geöffnet werden.

    Beispiel:

    set serveroutput on
    ____________________________________________

    DECLARE
       v_ename emp.ename%type;
       CURSOR c_record IS
       SELECT *
         FROM emp
       WHERE ename = v_ename;
       v_record c_record%rowtype;
    BEGIN
       v_ename := ‚MUELLER‘;
       OPEN c_record;
       FETCH c_record INTO v_record;
       dbms_output.put_line (v_record.ename);
       CLOSE c_record;
    END;
    /

    Es dürfen mehrer unterschiedliche CURSOR parallel geöffnet werden.

    3. Die Übergabe der Ergebnisse an PL/SQL-Variablen

    Es gibt zwei Formen der FETCH-Anweisung:

    FETCH cursor_name INTO variable1, variable2, …;

    oder

    FETCH cursor_name INTO record;

    Beispiel:

    set serveroutput on
    ____________________________________________

    DECLARE
      CURSOR c_ename IS
      SELECT ename
       FROM emp
     WHERE ename = ‚MUELLER‘;

     v_ename varchar2(30);

    BEGIN
      OPEN c_ename;
      FETCH c_ename INTO v_ename;
      dbms_output.put_line (v_ename);
      CLOSE c_ename;
    END;
    /

    oder

    set serveroutput on
    ____________________________________________

    DECLARE
      CURSOR c_ename IS
      SELECT ename
        FROM emp
    WHERE ename = ‚MUELLER‘;

    v_ename emp.ename%type;

    BEGIN
      OPEN c_ename;
      FETCH c_ename INTO v_ename;
      dbms_output.put_line (v_ename);
      CLOSE c_ename;
    END;
    /

    oder

    set serveroutput on
    ____________________________________________

    DECLARE
      CURSOR c_record IS
      SELECT *
       FROM emp
    WHERE ename = ‚MUELLER‘;

    v_record c_record%rowtype;

    BEGIN
      OPEN c_record;
      FETCH c_record INTO v_record;
      dbms_output.put_line (v_record.ename);
      CLOSE c_record;
    END;
    /

    In PL/SQL ist es üblich, einen Datensatz mit denselben Typen wie eine Datenbankzeile zu deklarieren. Um dies zu ermöglichen, stellt PL/SQL den Operator %ROWTYPE zur Verfügung. Ähnlich wie %TYPE liefert %ROWTYPE einen Typ entsprechend der Tabellendefinition. Eine Deklaration wie

    DECLARE
       v_record emp%rowtype;

    definiert beispielsweise einen Datensatz, dessen Felder den Spalten der Tabelle emp entsprechen. Genauer gesagt wird v_record folgende Struktur haben:

    (EMPNO NUMBER(4),
    ENAME VARCHAR2(10),
    JOB VARCHAR2(9),
    MGR NUMBER(4),
    HIREDATE DATE,
    SAL NUMBER(7,2),
    COMM NUMBER(7,2),
    DEPTNO NUMBER(2))

    So wie bei %TYPE werden auch hier Beschränkungen der Spalte durch NOT NULL nicht übernommen. Dagegen werden die Längen der Spalten vom Typ VARCHAR2 und CHAR sowie die Genauigkeit und Nachkommastellen der Spalten vom Typ NUMBER übernommen.
    Sollte sich die Tabellendefinition ändern, werden diese Änderungen durch %ROWYTPE auch im Programm ausgeführt. Wie %TYPE wird der Typ von %ROWTYPE jedes Mal ermittelt, wenn ein anonymer Block von der PL/SQL-Engine verarbeitet bzw. wenn ein gespeichertes Objekt (Funktion bzw. Prozedur) kompiliert wird.

    1.2.7.2 Cursor-Attribute

    Es gibt in PL/SQL vier Attribute (Status), die auf Cursor angewendet werden können. Cursor-Attribute werden in einem PL/SQL-Block an den Cursor-Namen angehängt, ähnlich wie %TYPE oder %ROWTYPE. Anstatt jedoch einen Typ zurückzuliefern, geben Cursor-Attribute einen Wert zurück, der in Ausdrücken verwendet werden kann. Die Attribute heißen:

    %FOUND
    ist ein boolesches Attribut. Es liefert den Wert TRUE, wenn die vorangegangene FETCH-Anweisung eine Zeile zurückgegeben hat, ansonsten den Wert FALSE.

    %NOTFOUND
    entspricht dem Gegenteil von %FOUND.

    %ISOPEN
    ist ein boolesches Attribut. Es wird dazu benutzt um festzustellen, ob der verknüpfte Cursor geöffnet ist oder nicht. Wenn dies der Fall ist, liefert %ISOPEN den Wert TRUE, andernfalls den Wert FALSE.

    %ROWCOUNT
    ist ein numeriches Attribut. Es liefert die Anzahl von Zeilen, die vom Cursor bisher ausgelesen wurden.

    Syntax:

    if cursor_name%FOUND then…
    if cursor_name%NOTFOUND then…
    if cursor_name%ISOPEN then…
    if cursor_name%ROWCOUNT = 10 then…

    1.2.7.3 Parametrisierte Cursor

    Ein parametrisierter Cursor kann Argumente (‚MUELLER‘) übernehmen.

    Beispiel: (Im Vergleich hierzu bitte das Beispiel unter ‚2. Das Öffnen des Cursors für eine Abfrage‘ beachten)

    set serveroutput on
    ____________________________________________

    DECLARE
        CURSOR c_record (p_ename emp.ename%type) IS
            SELECT *
              FROM emp
           WHERE ename = p_ename;
       v_record c_record%rowtype;
    BEGIN
       OPEN c_record (‚MUELLER‘);
       FETCH c_record INTO v_record;
       dbms_output.put_line (v_record.ename);
       CLOSE c_record;
    END;
    /

    1.2.7.4 Select for Update – Cursor

    Nicht selten sollen die Zeilen, die von dem Cursor abgerufen wurden, verändert werden. PL/SQL verfügt über eine Syntax, um diese Aufgabe durchzuführen. Diese besteht aus zwei Teilen – der FOR UPDATE – Klausel innerhalb der CURSOR-Deklaration sowie der WHERE CURRENT OF – Klausel in einer UPDATE- oder DELETE-Anweisung.

    Die FOR UPDATE – Klausel
    ist Teil einer SELECT-Anweisung. Sie kann als letzte Klausel der Anweisung nach der ORDER BY-Klausel (falls diese vorhanden ist) stehen.

    SELECT… FROM… FOR UPDATE [OF column_reference] [NOWAIT]

    column_reference entspricht einer oder mehreren Tabellenspalten .
    Mit der Angabe von ‚OF column_reference‘ werden nur die angegebenen Spalten gesperrt. Ohne diese Angabe wird die ganze Zeile (der Datensatz) gesperrt.

    NOWAIT verhindert, dass der SELECT-Befehl, im Fall, das ein anderer Benutzer einen Zeile gesperrt hat, solange wartet, bis er alle Datensätze der aktiven Gruppe sperren kann.
    Stattdessen wird eine Fehlermeldung generiert.

    Die WHERE CURRENT OF – Klausel
    ist Teil einer UPDATE- oder DELETE-Anweisung. Diese kann nur benutzt werden, wenn der CURSOR mit FOR UPDATE deklariert wurde.

    Beispiel:

    set serveroutput on

    ____________________________________________

    DECLARE
       CURSOR c_record (p_ename emp.ename%type) IS
        SELECT *
          FROM emp
       WHERE ename = p_ename
       FOR UPDATE OF ename;
       v_record c_record%rowtype;
    BEGIN
       OPEN c_record (‚MUELLER‘);
       FETCH c_record INTO v_record;
       UPDATE emp
            SET ename = ‚mueller‘
       WHERE CURRENT OF c_record;
      CLOSE c_record;

      COMMIT;

      OPEN c_record (‚mueller‘);
      FETCH c_record INTO v_record;
      dbms_output.put_line (v_record.ename);
      CLOSE c_record;

    END;
    /

    Die COMMIT – Anweisung hebt alle Satzsperrungen auf. Hiernach können nur dann wieder Daten ausgelesen werden, wenn der CURSOR neu geöffnet wird.

    1.2.7.5 Implizite Cursor

    (Der implizite Cursor wird intern bei einer ganz normalen z.B. SELECT-Anweisung verwaltet)

    Der implizite Cursor wird verwendet, um INSERT-, UPDATE-, DELETE- und einzeilige SELECT…INTO-Anweisungen zu verarbeiten. Da der SQL-Cursor von der PL/SQL-Engine geöffnet und geschlossen wird, haben die Befehle OPEN, FETCH und CLOSE hier keine Bedeutung.

    Allerdings können die Cursor-Attribute auf SQL-Cursor angewendet werden.

    Syntax:

    if SQL%FOUND then…
    if SQL%NOTFOUND then…
    if SQL%ISOPEN then…
    if SQL%ROWCOUNT = 10 then…

    1.2.7.6 Cursor Variablen

    Eine Cursor-Variable kann zur Laufzeit mit verschiedenen SELECT-Anweisungen verknüpft werden. Cursor-Variablen verhalten sich wie PL/SQL-Variablen, die zur Laufzeit unterschiedliche Werte annehmen können.

    Definition einer Cursor-Variablen:

    Eingeschränkte Cursor-Variable:

    TYPE type_name IS REF CURSOR RETURN return_type;

    Diese Form des Cursors kann nur ein Datensatzformat aufnehmen, d.h. es können verschiedene Tabelle gelesen werden, aber die Satzbeschreibung muß identisch sein.

    Nicht eingeschränkte Cursor-Variable:

    TYPE type_name IS REF CURSOR;

    Diese Form des Cursors kann mehrere Datensatzformate aufnehmen, d.h. es können verschiedene Tabellen mit unterschiedlicher Satzbeschreibung gelesen werden.

    Beispiel Eingeschränkte Cursor-Variable:

    set serveroutput on

    ____________________________________________

    DECLARE
       TYPE t_emp IS REF CURSOR RETURN emp%rowtype;
       c_empCursor t_emp;
       v_empRecord emp%rowtype;
    BEGIN
       OPEN c_empCursor FOR SELECT *
                                             FROM emp
                                          WHERE ename = ‚mueller‘;
    FETCH c_empCursor INTO v_empRecord;
    dbms_output.put_line (v_empRecord.ename);
    CLOSE c_empCursor;
    END;
    /

    Beispiel NICHT eingeschränkte Cursor-Variable:

    set serveroutput on

    ____________________________________________

    DECLARE
       TYPE t_empdept IS REF CURSOR;
       c_empdept t_empdept;
       v_empRecord emp%rowtype;
       v_deptRecord dept%rowtype;
    BEGIN

    — Hier wird die Tabelle ‚emp‘ ausgelesen

      OPEN c_empdept FOR SELECT *
                                         FROM emp 
                                       WHERE ename = ‚mueller‘;
       FETCH c_empdept INTO v_empRecord;
       dbms_output.put_line (v_empRecord.ename);
       CLOSE c_empdept;

    — Hier wird die Tabelle ‚dept‘ ausgelesen

      OPEN c_empdept FOR SELECT *
                                         FROM dept
                                      WHERE deptno = (SELECT deptno
                                                                    FROM emp
                                                                 WHERE ename = ‚mueller‘);
       FETCH c_empdept INTO v_deptRecord;
       dbms_output.put_line (v_deptRecord.dname);
       CLOSE c_empdept;
    END;
    /


    1.2.8 Schleifen

    PL/SQL verfügt über eine Möglichkeit, Anweisungen mit Hilfe von Schleifen mehrmals hintereinander auszuführen. Schleifen können in fünf Kategorien unterteilt werden.

    1.2.8.1 Einfache- oder Endlosschleife

    Syntax:

    LOOP
       sequence_of_statements;
    END LOOP;

    In diesem Fall wird die Anweisungsfolge sequence_of_statements unendlich oft ausgeführt, da die Schleife keine Abbruchbedingung enthält. Eine Abbruchbedingung kann mit Hilfe der Anweisung EXIT eingefügt werde.

    Syntax:

    EXIT [when condition];

    Beispiel:

    set serveroutput on

    ____________________________________________

    DECLARE
      v_ename    emp.ename%type;
      v_counter  number(1) := 1;
    BEGIN
      LOOP
         SELECT ename
           INTO v_ename
           FROM emp
          WHERE ename = ‚mueller‘;
         dbms_output.put_line (v_ename);
         v_counter := v_counter + 1;
         EXIT WHEN v_counter > 5;
      END LOOP;
    END;
    /

    1.2.8.2 WHILE-Schleife

    Syntax:

    WHILE condition LOOP
       sequence_of_statements;
    END LOOP;

    Die Bedingung condition wird vor jedem Durchlauf der Schleife ausgewertet. Ergibt ihr Wert TRUE, dann wird die Anweisungsfolge sequence_of_statements ausgeführt. Liefert condition den Wert FALSE oder NULL, wird die Schleife beendet und die Ausführung des Programms wird hinter der Anweisung END LOOP fortgesetzt.

    Beispiel:

    set serveroutput on

    ____________________________________________

    DECLARE
      v_ename    emp.ename%type;
      v_counter  number(1) := 1;
    BEGIN
      WHILE v_counter <= 5 LOOP
           SELECT ename
             INTO v_ename
            FROM emp
           WHERE ename = ‚mueller‘;
          dbms_output.put_line (v_ename);
          v_counter := v_counter + 1;
      END LOOP;
    END;
    /

    Die Anweisung EXIT oder EXIT WHEN kann auch innerhalb einer WHILE-Schleife verwendet werden, um die Schleife falls erforderlich vorzeitig abzubrechen.

    1.2.8.3 numerische For-Schleife

    Syntax:

    FOR loop_counter IN [REVERSE] low_bound ..   
                                  high_bound   LOOP
       sequence_of_statements;
    END LOOP;

    Loop_counter entspricht der Indexvariablen (Typ => BINARY_INTEGER), die nicht gesondert deklariert werden muß. Low_bound und High_bound markieren die Anzahl der Durchläufe (von .. bis).

    Bei jedem Durchlauf der Schleife wird die Indexvariable Loop_counter um eins erhöht, bis die Schleife beendet ist.

    Beispiel:

    set serveroutput on

    ____________________________________________

    DECLARE
      v_ename    emp.ename%type;
    BEGIN
      FOR v_counter IN 1 .. 5 LOOP
       SELECT ename
         INTO v_ename
         FROM emp
        WHERE ename = ‚mueller‘;
        dbms_output.put_line (v_counter||‘ ‚||v_ename);
      END LOOP;
    END;
    /

    Beispiel mit REVERSE:

    DECLARE
      v_ename    emp.ename%type;
    BEGIN
      FOR v_counter IN REVERSE 1 .. 5 LOOP
         SELECT ename
           INTO v_ename
          FROM emp
         WHERE ename = ‚mueller‘;
        dbms_output.put_line (v_counter||‘ ‚||v_ename);
      END LOOP;
    END;
    /

    Low_Bound und High_Bound müssen keine numerischen Werte sein. Es kann sich hier auch um numerische Variablen handeln.

    1.2.8.4 FOR-Schleife mit Cursor

    Beispiel:

    set serveroutput on

    ____________________________________________

    DECLARE
      CURSOR c_record is
             select *
               from emp
              where ename in (‚mueller‘,’FORD‘);
    BEGIN
      FOR v_record IN c_record LOOP
          dbms_output.put_line (v_record.ename);
      END LOOP;
    END;
    /

    Diese Schleife läuft solange, solange sie Sätze in der aktuellen Gruppe des CURSORs findet.

    1.2.8.5 FOR-Schleife mit SELECT

    Beispiel:

    set serveroutput on

    ____________________________________________

    DECLARE
    BEGIN
      FOR v_record IN (SELECT *
                         FROM emp
                        WHERE ename IN
                              (‚mueller‘,’FORD‘))
      LOOP
          dbms_output.put_line (v_record.ename);
      END LOOP;
    END;
    /

    Diese Schleife läuft solange, sie Sätze in der aktuellen Gruppe des SELECT… findet.


    1.2.9 GOTOs und Marken

    PL/SQL enthält auch die GOTO-Anweisung. Die Syntax lautet:

    GOTO label_name;

    wobei label_name eine so genannte Marke ist, die innerhalb des PL/SQL-Blocks definiert ist.

    Syntax für Marke:

    <<label_name>>
    .
    .
    .

    HINWEIS:
    Der Umgang mit GOTO erfordert eine gewisse Sorgfalt. Unnötige GOTO-Anweisungen führen dazu, dass ein Programm, das grundlos von einer Stelle zu einer anderen springt sehr schwierig zu warten und zu verstehen ist. Man sagt hierzu auch Spaghetti-Code.

    Wird eine GOTO-Anweisung ausgeführt, springt das Programm sofort zu der Anweisung unter der Marke, die angegeben wurde.
       
    Beispiel:

    set serveroutput on

    ____________________________________________

    DECLARE
      v_ename    emp.ename%type;
      v_counter  number(1) := 1;
    BEGIN
      LOOP
         SELECT ename
           INTO v_ename
           FROM emp
          WHERE ename = ‚mueller‘;
          dbms_output.put_line (v_ename);
          v_counter := v_counter + 1;
         IF v_counter > 5 THEN
            GOTO ende_loop;
         END IF;
      END LOOP;

    <<ende_loop>>
        dbms_output.put_line (‚E N D E‘);
    END;
    /

    Folgende Einschränkung für die GOTO-Anweisung sind anzumerken:

    Das Springen in einen inneren Block ist nicht erlaubt:

    Beispiel:

    BEGIN
      GOTO innen;
        BEGIN
           <<innen>>
           .
           .
           .
        END;
    END;

    Das Springen in eine Schleife ist nicht erlaubt:

    Beispiel:

    BEGIN
      GOTO schleife;
        LOOP
           <<schleife>>
           .
           .
           .
        END LOOP;
    END;

    Das Springen in eine IF-Anweisung ist nicht erlaubt:

    Beispiel:

    BEGIN
      GOTO if_anweisung;
        IF
           <<if_anweisung>>
           .
           .
           .
        END IF;
    END;

    Das Springen von einer IF-Klausel in eine andere ist nicht erlaubt:

    Beispiel:

    BEGIN
    .
    .
    .
        IF
           GOTO if_anweisung;
        ELSE
           <<if_anweisung>>
           .
           .
           .
        END IF;
    END;

    Das Zurückspringen von einer EXCEPTION in den aktuellen Block ist nicht erlaubt:

    Beispiel:

    BEGIN
        SELECT…
        .
        .
        .
    <<block>>
    .
    .
    .   
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
         GOTO block;
    END;

    Auch Schleifen selbst können mit Marken versehen werden. So kann die Marke bei der EXIT-Anweisung verwendet werden, um anzuzeigen, welche Schleife beendet werden soll.

    Beispiel:

    BEGIN
      <<aussen>>
      FOR v_aussen IN 1..5 LOOP
      .
      .
      .
          <<innen>>
          FOR v_innen IN 2..10 LOOP
          .
          .
          .
            IF v_aussen > 4 THEN
               EXIT aussen;   –Beendet beide Schleifen
            END IF;
          END LOOP innen;
      END LOOP aussen;
    END;


    1.2.10 IF… THEN… ELSE…

    Die IF…THEN…ELSE… – Anweisung wird auch als bedingte Kontrollanweisung bezeichnet und die Syntax dieser Anweisung lautet:

    IF boolesche-anweisung1 THEN
       befehle_sequence1;
    [ELSIF boolesche-anweisung1 THEN
       befehle_sequence2;]

    [ELSE
       befehle_sequence3;]
    END IF;

    Wobei boolesche-anweisung1 ein beliebiger Ausdruck ist, der einen booleschen Wert liefert. Die Klauseln ELSIF und ELSE sind optional, wobei die Anzahl der ELSIF-Klauseln beliebig sein kann.

    Beispiel:

    set serveroutput on

    ____________________________________________

    DECLARE
      CURSOR c_record is
             select *
               from emp
              where ename in (‚mueller‘,’FORD‘,’ADAMS‘);
    BEGIN
      FOR v_record IN c_record LOOP
          IF    v_record.ename = ‚mueller‘ THEN
                dbms_output.put_line (‚Guten Tage Herr‘
                                        ||initcap(v_record.ename));
          ELSIF v_record.ename = ‚FORD‘ THEN
                dbms_output.put_line (‚Hello Mister‘
                                        ||initcap(v_record.ename));
          ELSE
                dbms_output.put_line (initcap(v_record.ename)
                                        ||‘-Family‘);
          END IF;
      END LOOP;
    END;
    /

    Jede Folge von Anweisungen wird nur dann ausgeführt, wenn der zugehörige boolesche Ausdruck den Wert TRUE ergibt.


    Praxisbeispiele
  • MEMBER OF vs. IN ( SQL ) – ORACLE PL/SQL » Syntax kurz erklärt
  • CASE expressions in the ORDER BY – ORACLE » Syntax kurz erklärt

  • 1.2.11 NULL-Anweisung

    Es gibt Fälle, da ist es wünschenswert, anzuzeigen, dass keine Anweisung ausgeführt werden soll. Um das zu erreichen, wird die NULL-Anweisung verwendet. Die NULL-Anweisung dient dann lediglich als Platzhalter, es wird keine Aktion ausgeführt.

    Beispiel:

    DECLARE
      v_ename  varchar2(10) default ‚MUELLER‘;
    BEGIN
        IF v_ename = ‚mueller‘ THEN
           .
           .
           .
        ELSE
           NULL;
        END IF;
    END;


    1.2.12 Integrierte SQL-Funktionen

    Man unterscheidet 4-Arten von integrierten SQL-Funktionen

    •    Zeichenfunktionen

    –    ASCII
    –    CHR
    –    CONCAT   oder   ||
    –    INITCAP
    –    INSTR
    –    LENGTH
    –    LOWER
    –    LPAD
    –    LTRIM
    –    REPLACE
    –    RPAD
    –    RTRIM
    –    SOUNDEX
    –    SUBSTR
    –    TRANSLATE
    –    UPPER

    Beispiel:

    set serveroutput on

    ____________________________________________

    DECLARE
     v_name   varchar2(20);
    BEGIN
     v_name := UPPER(‚mueller‘);
     dbms_output.put_line(v_name);
    END;
    /

    •    Datumsfunktionen

    –    ADD_MONTH
    –    LAST_DAY
    –    MONTH_BETWEEN
    –    NEW_TIME
    –    NEXT_DAY
    –    ROUND
    –    SYSDATE
    –    TRUNC

    Beispiel:

    set serveroutput on

    ____________________________________________

    DECLARE
     v_date  date;
    BEGIN
     v_date := SYSDATE;
     dbms_output.put_line(v_date);
    END;
    /

    •    Numerische, LOB- und diverse Funktionen

    –    ABS
    –    ACOS
    –    ASIN
    –    ATAN
    –    ATAN2
    –    CEIL
    –    COS
    –    COSH
    –    EXP (n)
    –    FLOOR
    –    LM (n)
    –    LOG (a,b)
    –    MOD (a,b)
    –    POWER (a,b)
    –    ROUND (a,[b])
    –    SIGN (a)
    –    SIN
    –    SINH
    –    SQRT
    –    TAN
    –    TANH
    –    TRUNC (a,[b])

    Beispiel:

    set serveroutput on

    ____________________________________________

    DECLARE
     v_number  number(8,2);
    BEGIN
     v_number := ROUND(85.235,2);
     dbms_output.put_line(v_number);
    END;
    /

    •    LOB-Funktion

    –    BFILENAME
    –    EMTY_BLOB
    –    EMTY_CLOB

    Das Paket DBMS_LOB beinhaltet viele weitere Funktionen und Prozeduren zur Verarbeitung von LOB-Daten.

                             
    •    Diverse-Funktion

    –    DUMP
    –    GREATEST
    –    LEAST
    –    NVL
    –    SQLCODE
    –    SQLERRM
    –    UID
    –    USER
    –    USERENV
    –    VSIZE

    Beispiel:

    set serveroutput on

    ____________________________________________

    DECLARE
     v_size   number(1);
    BEGIN
     SELECT VSIZE(ename)
       INTO v_size
       FROM emp
      WHERE ename = ‚mueller‘;
     dbms_output.put_line(v_size);
    END;
    /

     

    •    Konvertierungsfunktionen

    –    CHARTOROWID
    –    CONVERT
    –    HEXTORAW
    –    RAWTOHEX
    –    ROWIDTOCHAR
    –    TO_CHAR
    –    TO_DATE
    –    TO_NUMBER

    Beispiel:

    set serveroutput on

    ____________________________________________

    DECLARE
     v_date  varchar2(23);
    BEGIN
     v_date := TO_CHAR(SYSDATE,’DD.MM.RRRR –   
                                          HH24:MI:SS‘);
     dbms_output.put_line(v_date);
    END;
    /


    Oracle Tools

     1.3 EINBINDUNG IN ORACLE TOOLS

    Entwickler verwenden Oracle Tools, um anspruchsvolle Anwendungen zu erstellen, die eine größere Benutzerfreundlichkeit, Sichtbarkeit und Verwaltbarkeit der Daten bieten.


    Oracle Developer

    Die Programme Oracle Forms und Oracle Reports sind u.a. Bestandteil der Entwicklungsumgebung.
    Wenn Sie einmal die grafischen Objekte (z.B. Button) mit diesen Werkzeugen erzeugt haben, dann weisen Sie diesen Objekten PL/SQL-Code zu. Hierzu werden verschiedene Arten von Triggern (Die Trigger in einer z.B. FORMS-Anwendung sind PL/SQL-Blöcke, die auf verschieden Ereignisse warten, um dann zur Ausführung zu gelangen. Ein Ereignis könnten z.B. sein, dass jemand die Taste F7 drückt (KEY-F7)) genutzt (z.B. WHEN_BUTTON_PRESSED)

    Beispiel (FORMS):

    Oracle Developer Forms Fenster mit Liste und Button

    Oracle Developer Forms - Entwickleransicht - Triggerprogrammierung

    Zu den Werkzeugen von Oracle, die eine eigene PL/SQL-Engine auf dem Client haben, gehören Developer/2000 (u.a. FORMS & REPORT), Designer 2000 und Discoverer/2000.

    Oracle Developer Forms - interne PL/SQL Engine

    Bei einer clientseitigen PL/SQL-Engine werden die SQL-Anweisungen über das Netzwerk geschickt, während die prozeduralen Anweisungen auf dem Client verarbeitet werden. Dies scheint zu einer höheren Belastung des Netzwerkes zu führen und somit die Anwendung langsamer ablaufen zu lassen.

    Aber der Großteil in einer z.B. Forms-Anwendung anfallenden Tätigkeiten bestehen jedoch naturgemäß aus prozeduralen Anweisungen. Während der Benutzer zwischen den Feldern und Blöcken des Formulares hin- und her wechselt oder eine Taste drückt, werden automatisch Trigger ausgelöst, und die Formularfelder werden mit unterschiedlichen Werten gefüllt. Dies alles sind prozedurale Vorgänge, und dementsprechend sollten sie auf dem Client durchgeführt werden, um eine hohe Leistung zu erzielen.


    EXCEPTIONS

     1.4 FEHLERBEHANDLUNG

    Jeder Oracle-Fehler hat eine Nummer, Ausnahmen müssen jedoch nach Namen behandelt werden. Daher gibt PL / SQL einige allgemeine Oracle-Fehler als Ausnahmen vor.


    Oracle Exception

    Jedes gut geschriebene Programm muß dazu in der Lage sein intelligent mit Fehlern umzugehen, und diese nach Möglichkeit auch zu beheben. PL/SQL implementiert die Fehlerbehandlung in Form von EXCEPTIONs und EXCEPTION-HANDLERn. Exeptions können Oracle-Fehler oder eigene benutzerdefinierte Fehlern zugeordnet werden.

    Die Syntax für die Fehlerbehandlung lautet:

    PL/SQL Fehlerbehandlung - Syntaxdarstellung

    Vordefinierte Exceptions

    Oracle verfügt über mehrere vordefinierte Exceptions, die den häufigsten Oracle-Fehlern entsprechen.

    PL/SQL Fehlerbehandlung - Oracle Fehlercode

    PL/SQL Fehlerbehandlung - Oracle Fehlercode

    Benutzerdefinierte Exceptions

    Bei einer benutzerdefinierten Exception handelt es sich um einen Fehler, der durch das Programm definiert ist. Der dadurch angezeigte Fehler ist nicht notwendigerweise ein Oracle-Fehler – es kann sich beispielsweise auch um einen Fehler bei den Daten handeln. Andererseits beziehen sich die vordefinierten Exceptions auf übliche SQL-Fehler.

    Benutzerdefinierte Exceptions werden im Deklarationsabschnitt eines PL/SQL-Blocks deklariert. Exceptions verfügen wie Variablen über einen Typ (EXCEPTION) und einen Geltungsbereich.

    DECLARE
    fehlertext EXCEPTION;

    Wie wird eine Exception ausgelöst

    Wenn der zu einer Exception gehörende Fehler auftritt, wird die Exception ausgelöst. Benutzerdefinierte Exceptions werden explizit mittels der Anweisung RAISE aktiviert, wohingegen vordefinierte Exceptions implizit beim Auftreten des entsprechenden Oracle-Fehler ausgelöst werden. Bei Bedarf können vordefinierte Exceptions auch explizit mittels der Anweisung RAISE hervorgerufen werden.

    Wenn eine Exception ausgelöst wird, geht die Programmsteuerung sofort auf den Exception-Abschnitt des Blocks über. Sobald der Exception-Handler die Kontrolle erlangt hat, führt kein Weg zurück in den Blockabschnitt mit dem ausführbaren Code.

    Der Exception-Handler OTHERS

    Der OTHERS-Handler wird für alle ausgelösten Exceptions ausgeführt. Er sollte immer der letzte Handler im Block sein. Es gehört zum guten Programmierstil, einen OTHERS-Handler auf der obersten Ebene Ihres Programms (dem äußersten Block) vorzusehen, um sicherzustellen, dass kein Fehler unentdeckt bleibt.

    SQLCODE und SQLERRM

    Innerhalb eines OTHERS-Handlers erweist es sich häufig als nützlich zu wissen, welcher Oracle-Fehler die Exception ausgelöst hat. Ein Grund könnte sein, aufzeichnen zu wollen, welcher Fehler aufgetreten ist, statt nur den Umstand, dass ein Fehler aufgetreten ist.

    Oder Sie möchten abhängig davon, welcher Fehler ausgelöst worden ist, unterschiedliche Dinge tun. PL/SQL stellt diese Informationen über die beiden vordefinierten Funktionen SQLCODE und SQLERRM bereit. SQLCODE liefert den aktuellen Fehlercode zurück und SQLERRM den Text der aktuellen Fehlermeldung.

    Das Pragma EXCEPTION_INIT

    Sie können einen internen Oracle-Fehlercode mit einem Namen (Exception-Namen) verbinden. Damit haben Sie die Möglichkeit, genau diesen Fehler abzufangen, statt auf einen OTHERS-Handler zurückgreifen zu müssen.
    Mittels SQLCODE kann der internen Oracle-Fehlercode ermittelt werden.

    Beispiele:

    Aus dieser Version

    set serverout on

    ____________________________________________

    BEGIN
    DELETE FROM dept;
    EXCEPTION
    WHEN OTHERS THEN
    dbms_output.put_line
    (SQLCODE);
    END;
    /

    wird mit PRAGMA EXCEPTION_INIT diese Version

    set serverout on

    ___________________________________________

    DECLARE
    wir_haben_noch_Daten EXCEPTION;
    PRAGMA EXCEPTION_INIT (wir_haben_noch_Daten, -2292);
    BEGIN
    DELETE FROM dept;
    EXCEPTION
    WHEN wir_haben_noch_Daten THEN
    dbms_output.put_line
    (‚Löschen Sie bitte zunächst Daten in emp.‘);
    END;
    /

    RAISE_APPLICATION_ERROR verwenden

    Sie können die vordefinierte Funktion RAISE_APPLICATION_ERROR verwenden, um Ihre eigenen Fehlermeldungen zu erzeugen, die anschaulicher sein können als benannte Exceptions. Benutzerdefinierte Fehler werden genauso aus dem Block an die aufrufende Umgebung übergeben wie ORACLE-Fehler.

    Die Syntax hierfür lautet:

    RAISE_APPLICATION_ERROR (error_nummer, error_meldung);

    Dabei steht error_nummer für die Fehlernummer, die ein Parameter zwischen –20000 und –20999 ist. Bei error_meldung handelt es sich um den mit diesem Fehler verknüpften Text. Der Parameter error_meldung darf höchstens 512 Zeichen lang sein.

    Beispiele:

    BEGIN
    IF 10 > 9 THEN
    RAISE_APPLICATION_ERROR
    (-20001, ’10 ist größer als 9 !‘);
    END IF;
    END;
    /

    Verhalten der Ausnahme

    Exceptions im ausführbaren Bereich

    BEISPIEL 1

    PL/SQL Fehlerbehandlung - Syntaxdarstellung

    Dieses Beispiel führt die Anwendung der Regel1 vor. Exception A wird im untergeordneten Block ausgelöst und verarbeitet. Die Programmsteuerung geht anschließend wieder auf den äußeren Block über.

    BEISPIEL 2

    PL/SQL Fehlerbehandlung - Syntaxdarstellung

    In diesem Beispiel wird die Regel 2 auf den untergeordneten Block angewendet. Die Exception wird an den umgebenden Block weitergereicht, wo dann die Regel 1 zur Anwendung gelangt. Anschließend wird der umgebende Block erfolgreich beendet.

    BEISPIEL 3

    PL/SQL Fehlerbehandlung - Syntaxdarstellung

    Die Regel 2 wird wiederum auf den untergeordneten Block angewendet. Die Exception wird an den umgebenden Block weitergereicht, in dem ebenfalls kein Handler für sie vorhanden ist. Regel 2 gelangt erneut zur Anwendung, und der umgebende Block wird mit einer nicht abgefangenen Exception ohne Erfolg beendet.

    Exceptions im Deklarationsabschnitt

    BEISPIEL 4

    PL/SQL Fehlerbehandlung - Syntaxdarstellung

    In diesem Beispiel wird die Exception VALUE_ERROR durch folgende Deklaration ausgelöst:

    v_number NUMBER(3) := ‚ABC‘;

    Diese Exception wird sofort an den umgebenden Block weitergereicht. Selbst wenn ein OTHERS-Exception-Handler vorhanden sein sollte, wird er nicht ausgeführt. Wenn dieser Block von einem äußeren Block umgeben wäre, würde der äußere Block diese Exception abfangen können (siehe hierzu Beispiel 5).

    BEISPIEL 5

    PL/SQL Fehlerbehandlung - Syntaxdarstellung

    Exceptions im Exception-Abschnitt

    BEISPIEL 6

    PL/SQL Fehlerbehandlung - Syntaxdarstellung

    In diesem Beispiel wird die Exception A ausgelöst und anschließend verarbeitet. In dem Exception-Handler für A wird aber die Exception B ausgelöst. Diese Exception wird sofort an den äußeren Block weitergereicht, wodurch der Handler für die Exception B übergangen wird. Ähnlich wie in Beispiel 5 hätte ein umgebender äußerer Block die Exception B abfangen können, wenn er vorhanden gewesen wäre.(Letzterer Fall wird in Beispiel 7 dargestellt).

    BEISPIEL 7

    PL/SQL Fehlerbehandlung - Syntaxdarstellung


    Datentypen

     1.5 BENUTZERDEFINIERTE DATENTYPEN

    Die objektorientierte Programmierung eignet sich besonders für den Aufbau wiederverwendbarer Komponenten und komplexer Anwendungen. In PL / SQL basiert die objektorientierte Programmierung auf Objekttypen. Mit ihnen können Sie reale Objekte modellieren, Schnittstellen und Implementierungsdetails trennen und objektorientierte Daten persistent in der Datenbank speichern.


    TYPE ... IS RECORD

    Mit benutzerdefinierte Datentypen erhalten Sie die Möglichkeit sich Ihren eigenen Typ (wie z.B. NUMBER, VARCHAR2) zu erstellen, mit dem Sie dann Ihre Felder beschreiben können.

    Folgendes Beispiel veranschaulicht die Syntax und Arbeitsweise eines benutzerdefinierten Datensatzes:

    Beispiel:

    set serveroutput on

    ____________________________________________

    DECLARE
      TYPE typ_emp IS RECORD
           (ename       emp.ename%type,
            job            emp.job%type,       
            sal            emp.sal%type,
            dname      dept.dname%type);
      emp_rec  typ_emp;
    BEGIN
       SELECT ename,
              job,
              sal,
              dname
         INTO emp_rec
         FROM emp,
              dept
        WHERE emp.deptno = dept.deptno
          AND ename = ‚mueller‘;
       dbms_output.put_line (emp_rec.ename||
                             ‚   ‚||
                             emp_rec.job||
                             ‚   ‚||
                             emp_rec.sal||
                             ‚   ‚||
                             emp_rec.dname);
    END;
    /     

     

    Im Deklarationsabschnitt wird  zunächst ein neuer Datentyp definiert. Im obigen Beispiel mit dem Namen typ_emp. Dieser Typ wird direkt im Anschluß der Variablen emp_rec zugeordnet.
    Somit erhalten wir die Möglichkeit, im SELECT-Befehl mehrere Felder in diese eine Variablen zu lesen (INTO emp_rec).
    Das Voranstellen des Variablen-Namen vor den Feldname (getrennt durch einen Punkt) ermöglicht es uns, diese Felder wieder einzeln anzusprechen (z.B. emp_rec.ename).


    Praxisbeispiele
  • %TYPE und %ROWTYPE – ORACLE PL/SQL » Syntax kurz erklärt
  • REFCURSOR mit WHERE IN – ORACLE PL/SQL » Syntax kurz erklärt
  • ORACLE MULTISET + object type + nested table » Syntax kurz erklärt


  • 2. PL/SQL-Datenbankprogrammierung


    DDL-ANWEISUNGEN AUSFÜHREN

     2.1 DDL-ANWEISUNGEN AUSFÜHREN

    Datendefinitionssprachen (DDL) werden verwendet, um die Datenbankstruktur zu definieren. Alle CREATE-, DROP- und ALTER-Befehle sind Beispiele für DDL-SQL-Anweisungen.


    DDL

    Unter den DDL-Befehlen  (DATA DEFINITION LANGUAGE) versteht man Befehle wie z.B. DROP TABLE…, CREATE TABLE…, GRANT…, etc.

    Im folgenden Beispiel ist zu erkennen, wie das Ausführen einer DDL-Anweisung funktioniert:

    Beispiel:

    set serveroutput on

    ____________________________________________

    DECLARE

      v_cursor            number;

      v_create_string     varchar2(1000);

      v_drop_string       varchar2(1000);

    BEGIN

    — Der Cursor für die DDL-Anweisung wird eröffnet

      v_cursor := DBMS_SQL.OPEN_CURSOR;

     

     

    — String mit dem DDL-Befehl ‚CREATE TABLE…‘ füllen

      v_create_string  :=

            ‚CREATE TABLE tmp_table (tmp_id    number(15),

                                     tmp_nr    number(5))‘;

     

    — Der DDL-Befehl aus ‚v_create_string‘ wird ausgeführt

      DBMS_SQL.PARSE(v_cursor, v_create_string, DBMS_SQL.V7);

     

    — String mit dem DDL-Befehl ‚DROP TABLE…‘ füllen

      v_drop_string    := ‚DROP TABLE tmp_table‘;

     

    — Der DDL-Befehl aus ‚v_drop_string‘ wird ausgeführt

      DBMS_SQL.PARSE(v_cursor, v_drop_string, DBMS_SQL.V7);

     

    — Der Cursor für die DDL-Anweisung wird geschlossen

      DBMS_SQL.CLOSE_CURSOR(v_cursor);

    EXCEPTION

      WHEN OTHERS THEN

         /* Der Cursor für die DDL-Anweisung wird im Falle einer

            EXCEPTION geschlossen */

           DBMS_SQL.CLOSE_CURSOR(v_cursor);

      RAISE;

    END;

     

    Zunächst werden im DECLARE-Abschnitt die benötigten Variablen definiert (Es ist nicht, wie in diesem Beispiel dargestellt, erforderlich, die  Befehlsfolge in einer Variablen zu hinterlegen. Sie können die Befehlsfolge auch direkt in den Befehl DBMS_SQL.PARSE… eintragen).

    Im Anschluß wird die Cursor-ID ausgelesen. Sie wird benötigt um die jeweilige Session eindeutig zu bestimmen. Dies erfolgt mit der Anweisung …DBMS_SQL.OPEN_CURSOR.

    Sind Variablen angelegt worden, werden diese nun gefüllt.

    Der Befehl

       

           DBMS_SQL.PARSE(v_cursor, v_drop_string, DBMS_SQL.V7);

     

    macht nun die eigentliche Arbeit. In v_cursor steht die zuvor gelesenen Cursor-ID, in v_drop_string steht der auszuführende Befehl und DBMS_SQL.V7 ist eine Standardvorgabe.

    Das oben aufgeführte Beispiel legt die Tabelle ‚tmp_table‘ an und löscht diese direkt im Anschluß wieder. Hierzu werden die DDL-Befehle ‚CREATE TABLE…‘ und ‚DROP TABLE…‘ zum Einsatz gebracht.


    PROZEDUREN- UND FUNKTIONEN

     2.2 ENTWICKLUNG VON PROZEDUREN- UND FUNKTIONEN

    Die Haupttypen von Programmeinheiten, die Sie mit PL / SQL erstellen und in der Datenbank speichern können, sind eigenständige Prozeduren und Funktionen. Nach der Speicherung in der Datenbank können diese PL / SQL-Komponenten, die zusammen als gespeicherte Prozeduren bezeichnet werden, als Bausteine ​​für verschiedene Anwendungen verwendet werden.




    2.2.1 Eine Prozedur erstellen

    Die Syntax um eine Prozedur zu erstellen sieht wie folgt aus:

    CREATE [OR REPLACE] PROCEDURE procedure_name
    [(argument [{IN | OUT | IN OUT }]] type,

    (argument [{IN | OUT | IN OUT }]] type)] {IS | AS}
    procedure_body

    Dabei steht procedure_name für die Bezeichnung der zu erstellenden Prozedur, argument für die Bezeichnung eines Prozedurparameters, type für den Datentyp dieses Parameters und procedure_body für einen PL/SQL-Block, der den eigentlichen Programmcode der Prozedur ausmacht.
    Um den Code einer Prozedur zu ändern, muß die Prozedur gelöscht und neu erstellt werden. Da dies während der Entwicklungsphase einer Prozedur ein üblicher Vorgang ist, ermöglichen es die Schlüsselworte OR REPLACE, diese Operation in einem einzigen Vorgang durchzuführen.
    Die Schlüsselworte IS und AS sind gleichbedeutend.

    Parameter für PLSQL Prozeduren und Funktionen

    Wird für einen Formalparameter kein Modus angegeben, so wird standardmäßig von IN ausgegangen.

    AKTUALPARAMETER werden die Werte genannt, die beim Aufruf der Prozedur übergeben werden, und sie erhalten Ergebnisse, wenn die Prozedur beendet ist.

    FORMALPARAMETER sind die Platzhalter für die Werte der Aktualparameter. Beim Prozeduraufruf werden dem Formalparametern die Werte der Aktualparameter zugewiesen. Wenn die Prozedur beendet ist, werden den Aktualparameter die Werte der Formalparameter zugewiesen.

    Einschränkungen der Formalparameter

    Wenn eine Prozedur aufgerufen wird, werden ihr die Werte der Aktualparameter übergeben. Auf diese wird sich innerhalb der Prozedur mittels der Formalparameter bezogen. Es werden nicht nur die Werte übergeben, sondern als Bestandteil des Parameterübergabe-mechanismus auch die Einschränkungen der Variablen. In einer Prozedurdeklaration ist es nicht zulässig, CHAR- und VARCHAR2-Parameter mit einer Längenangabe oder NUMBER-Parameter mit einer Stellenanzahl und/oder einer Angabe der Nachkommastellen zu versehen.

    Beispiele:

    falsch ist:

    CREATE OR REPLACE PROCEDURE test (
        p_Parameter1 IN OUT VARCHAR2(10),
        p_Parameter2 IN OUT NUMBER(3,2)) AS
    BEGIN
        p_Parameter1 := ‚abcdefghij‘;
        p_Parameter2 := 12.30;
    END test;

    richtig ist:

    CREATE OR REPLACE PROCEDURE test (
        p_Parameter1 IN OUT VARCHAR2,
        p_Parameter2 IN OUT NUMBER) AS
    BEGIN
        p_Parameter1 := ‚abcdefghij‘;
        p_Parameter2 := 12.30;
    END test;

    Worin besteht nun die Einschränkung von p_Parameter1 und p_Parameter2 ? Die Einschränkungen beruhen auf den Aktualparameter. Wenn wir test

    DECLARE
        v_Variable1 VARCHAR2(10);
        v_Variable2 NUMBER(3,2);
    BEGIN
        test (v_Variable1, v_Variable2);
    END;

    aufrufen, verfügt p_Parameter1 über eine maximale Länge von 10 Zeichen (die auf dem Aktualparameter v_Variable1 beruht) und p_Parameter2 über 3 Stellen und 2 Nachkommastellen (die auf dem Aktualparameter v_Variable2 beruhen).

     

    Die Verwendung von %TYPE mit Prozedurparametern

    Die einzige Möglichkeit, Formalparameter mit Einschränkung zu versehen, besteht in der Verwendung von %TYPE. Wenn ein Formalparameter mittels %TYPE deklariert und der zugrundeliegende Typ eingeschränkt wurde, dann gelten die Einschränkungen für den Formalparameter statt die des Aktualparameters. Wenn wir test folgendermaßen deklarieren,

     

    CREATE OR REPLACE PROCEDURE test (
        p_Parameter1 IN OUT VARCHAR2,
        p_Parameter2 IN OUT emp.deptno%TYPE) AS
    BEGIN
        p_Parameter2 := 12345;
    END test;

    dann wird p_Parameter2 auf 2 Stellen beschränkt, da dies die Stellenanzahl der Spalte deptno ist. Selbst wenn wir test mit einem Aktualparameter aufrufen, der über eine ausreichend hohe Stellenanzahl verfügt gelangt die formale Stellenanzahl zur Anwendung.

    Positionelle und namentliche Notation

    Bei allen bisher in diesem Kapitel vorgestellten Beispielen waren die Aktualparameter durch die Position mit den Formalparametern verbunden. Wie im folgendem Beispiel nochmals dargestellt.

    Zuerst die Prozedurdeklaration

    CREATE OR REPLACE PROCEDURE CallMe (
        p_ParameterA IN VARCHAR2,
        p_ParameterB IN NUMBER,
        p_ParameterC IN BOOLEAN,
        p_ParameterD IN DATE) AS
    BEGIN
        NULL;
    END CallMe;

    mit dem zugehörigen aufrufenden Block

    DECLARE
        v_Variable1 VARCHAR2(10);
        v_Variable2 NUMBER(7,6);
        v_Variable3 BOOLEN;
        v_Variable4 DATE;
    BEGIN
       CallMe ( v_Variable1,
        v_Variable2,
        v_Variable3,
        v_Variable4);
    END;

    Hier werden die Aktualparameter den Formelparametern entsprechend der Position zugeordnet: v_Variable1 wird mit p_ParameterA verbunden, v_Variable2 mit p_ParameterB usw. Dieser Vorgang wird positionelle Notation genannt. Die positionelle Notation ist die am häufigsten verwendete.

    Alternativ hierzu können wir die Prozedur aber auch mittels der namentlichen Notation aufrufen. Hierzu folgendes Beispiel
    In den folgenden Beispielen ist die Prozedurdeklaration wie oben.

    mit dem zugehörigen aufrufenden Block

    DECLARE
        v_Variable1 VARCHAR2(10);
        v_Variable2 NUMBER(7,6);
        v_Variable3 BOOLEN;
        v_Variable4 DATE;
    BEGIN
        CallMe ( p_ParameterA => v_Variable1,
        p_ParameterB => v_Variable2,
        p_ParameterC => v_Variable3,
        p_ParameterD => v_Variable4);
    END;

    Die Reihenfolge ist durch die Zuordnung der Formal- und der Aktualparameter nicht maßgeblich. Somit kann der Aufruf von CallMe auch folgendermaßen aussehen:

    DECLARE
        v_Variable1 VARCHAR2(10);
        v_Variable2 NUMBER(7,6);
        v_Variable3 BOOLEN;
        v_Variable4 DATE;
    BEGIN
        CallMe ( p_ParameterD => v_Variable4,
         p_ParameterC => v_Variable3,
         p_ParameterB => v_Variable2,
         p_ParameterA => v_Variable1);
    END;

    Die positionelle und namentliche Notation kann in einem Aufruf bei Bedarf auch in einer Mischform zum Einsatz kommen. Die ersten Argumente müssen dabei anhand der Position und die übrigen können anhand des Namens angegeben werden. Folgendes Beispiel veranschaulicht diese Methode:

    DECLARE
        v_Variable1 VARCHAR2(10);
        v_Variable2 NUMBER(7,6);
        v_Variable3 BOOLEN;
        v_Variable4 DATE;
    BEGIN
        CallMe (v_Variable1,
        v_Variable2,
        p_ParameterC => v_Variable3,
        p_ParameterD => v_Variable4);
    END;

     

    Standardwerte für Parameter

    Ähnlich wie Variablendeklarationen können auch Formalparameter einer Prozedur oder Funktion über Standardwerte verfügen. Wenn es einen Standardwert zu einem Parameter gibt, braucht dieser nicht von der aufrufenden Umgebung übergeben zu werden. Wenn er übergeben wird, so wird der Wert des Aktualparameters anstelle des Standardwerts verwendet.

    CREATE OR REPLACE PROCEDURE CallMe (
        p_ParameterA IN VARCHAR2 DEFAULT ‚test‘,
        p_ParameterB IN NUMBER,
        p_ParameterC IN BOOLEAN,
        p_ParameterD IN DATE) AS
    BEGIN
       NULL;
    END CallMe;

     

    oder

    CREATE OR REPLACE PROCEDURE CallMe (
        p_parameterA IN VARCHAR2 := ‚test‘,
        p_ParameterB IN NUMBER, 
        p_ParameterC IN BOOLEAN,
        p_ParameterD IN DATE) AS
    BEGIN
        NULL;
    END CallMe;

    Beispiel:

    Prozedurbeschreibung:

    CREATE OR REPLACE PROCEDURE gebe_name (
        p_Empno IN emp.empno%TYPE := 7955,
        p_Ename OUT emp.ename%TYPE,
        p_Job OUT emp.job%TYPE,
        p_Sal OUT emp.sal%TYPE) AS
    BEGIN
       SELECT ename,
        job,
        sal
       INTO p_Ename,
        p_job,
        p_sal
      FROM emp
      WHERE empno = p_Empno;
    END;
    /

    Aufruf Beispiel 1 (Default lt. Prozedur nutzen):

    set serverout on

    ____________________________________________

    DECLARE
        v_Empno emp.empno%TYPE;
        v_Ename emp.ename%TYPE;
        v_Job emp.job%TYPE;
        v_Sal emp.sal%TYPE;
    BEGIN
        gebe_name (p_Ename => v_Ename,
        p_Job => v_Job,
        p_Sal => v_Sal);
        dbms_output.put_line (‚Name: ‚||
        v_Ename ||
        ‚ Job: ‚||
        v_Job ||
        ‚ Sal: ‚|| 
        v_Sal);
    END;
    /

    Aufruf Beispiel 2 (Default der Prozedur überschreiben):

    set serverout on

    ___________________________________________

    DECLARE
        v_Empno emp.empno%TYPE := 7902;
        v_Ename emp.ename%TYPE;
        v_Job emp.job%TYPE;
        v_Sal emp.sal%TYPE;
    BEGIN
        gebe_name (v_Empno,
        v_Ename,
        v_Job,
        v_Sal);
        dbms_output.put_line (‚Name: ‚||
        v_Ename ||
        ‚ Job: ‚||
        v_Job ||
        ‚ Sal: ‚||
        v_Sal);
    END;
    /


    2.2.2 Eine Funktion erstellen

    Eine Funktion ähnelt einer Prozedur sehr stark. Beide nehmen Argumente auf, die in einem beliebigen Modus vorliegen können. Beide stellen unterschiedliche Formen von PL/SQL-Blöcken mit Abschnitten für die Deklaration, den ausführbaren Code und die Exceptionbehandlung dar. Beide können in der Datenbank gespeichert werden oder innerhalb eines Blocks deklariert werden. (Nicht in der Datenbank gespeicherte Funktionen bzw. Prozeduren werden im Kapitel lokale Unterprogramme behandelt). Ein Prozeduraufruf ist allerdings selbst eine PL/SQL-Anweisung, wohingegen ein Funktionsaufruf als Bestandteil eines Ausdrucks erfolgt.

    Die Syntax, um eine Funktion zu erstellen, sieht wie folgt aus:

    CREATE [OR REPLACE] FUNCTION function_name

     [(argument [{IN | OUT | IN OUT }]] type,

      …

     (argument [{IN | OUT | IN OUT }]] type)]

    RETURN return_type {IS | AS}

    function_body

     

    Dabei steht function_name für die Bezeichnung der zu erstellenden Prozedur, argument  und type entsprechen denselben Angaben wie bei Prozeduren, return_type ist der Datentyp des von der Funktion zurückgelieferten Wertes und function_body steht für einen PL/SQL-Block, der den eigentlichen Programmcode der Funktion enthält.

    Die Argumentenauflistung ist wie schon bei den Prozeduren optional. Wenn keine Argumente vorhanden sind, kommen weder in der Funktionsdeklaration noch beim Funktionsaufruf Klammern vor. Der Rückgabetyp der Funktion ist allerdings notwendig, da der Funktionsaufruf immer ein Bestandteil eines Ausdrucks ist. Der Typ der Funktion legt den Typ des Ausdrucks fest, der den Funktionsaufruf enthält.

    Die RETURN-Anweisung

    Innerhalb des Funktionsrumpfs wird die RETURN-Anweisung verwendet, um die Programmsteuerung mit einem Wert wieder an die aufrufende Umgebung zurückkehren zu lassen. Die allgemeine Syntax der RETURN-Anweisung sieht wie folgt aus:

    RETURN expression;

     

    Dabei steht expression für den zu übergebenden Wert. Der Wert von expression wird zu dem Datentyp konvertiert, der in der RETURN-Klausel der Funktionsdefinition angegeben wurde, sofern er nicht bereits von diesem Typ ist. Wenn die RETURN-Anweisung ausgeführt wird, kehrt die Programmsteuerung sofort zur aufrufenden Umgebung zurück.

    In einer Funktion können zwar mehrere RETURN-Anweisungen vorkommen, jedoch wird nur eine von Ihnen ausgeführt. Eine Funktion ist fehlerhaft, wenn sie keine RETURN-Anweisungen ausführt.

    Der Einsatz von Funktionen

    Funktionen haben viele Eigenschaften mit Prozeduren gemeinsam:

    • Funktionen können mittels OUT-Parameter mehr als einen Wert zurückliefern.
    • Der Funktionscode besteht aus Abschnitten für Deklaration, ausführbaren Code und die Exceptionbehandlung.
    • Funktionen können mit Standardwerten umgehen.
    • Funktionen können mittels der positionellen und namentlichen Notation aufgerufen werden

    Wann ist dann eine Funktion angebracht und wann eine Prozedur? Im allgemeinen hängt das davon ab, wie viel Werte das Unterprogramm zurückliefern soll und wie diese Werte weiterbenutzt werden sollen. Eine Daumenregel besagt, dass bei mehr als einem Rückgabewert eine Prozedur zum Einsatz gelangen sollte. Wenn es nur einen Rückgabewert gibt, kann eine Funktion verwendet werden. Auch wenn OUT-Parameter für Funktionen zugelassen sind, handelt es sich bei deren Verwendung um schlechten Stil.


    2.2.3 Prozeduren und Funktionen löschen

    Ähnlich wie bei Tabellen können Prozeduren und Funktionen gelöscht werden. Dabei wird die Prozedur bzw. Funktion aus dem Data Dictionary (Inhaltsverzeichnis für alle Objekte der Datenbank) entfernt. Die Syntax lautet:

    DROP PROCEDURE procedure_name;

     

    bzw.

     

    DROP FUNCTION function_name;

     

    Beispiel:

        Funktionsbeschreibung:

     

    CREATE OR REPLACE FUNCTION gebe_alles (

      p_Empno       IN  emp.empno%TYPE := 7955)

    RETURN VARCHAR2 IS

      v_Ename       emp.ename%TYPE;

      v_Job         emp.job%TYPE;

      v_Sal         emp.sal%TYPE;

    BEGIN

      SELECT ename,

             job,

             sal

        INTO v_Ename,

             v_job,

             v_sal

        FROM emp

       WHERE empno = p_Empno;

      RETURN (‚Name: ‚||

              v_Ename ||

              ‚ Job: ‚||

              v_Job   ||

              ‚ Sal: ‚||

              v_Sal);

    END gebe_alles;

    /

     

    Aufruf Beispiel 1 (Default lt. Funktion nutzen):

     set serverout on

    ____________________________________________

     

    BEGIN

      dbms_output.put_line (gebe_alles);

    END;

    /

      

    Aufruf Beispiel 2 (Default der Funktion überschreiben):

     

    set serverout on

    ____________________________________________

     

    BEGIN

      dbms_output.put_line (gebe_alles(7902));

    END;

    /


    2.2.4 Lokale Unterprogramme

    Im folgenden Beispiel wird ein im Deklarationsabschnitt eines PL/SQL-Blocks deklariertes lokales Unterprogramm vorgeführt:

    Beispiel:

        Funktion:

    set serverout on

    ____________________________________________

     

    DECLARE

        FUNCTION gebe_alles_lokal (

              p_Empno       IN  emp.empno%TYPE := 7902)

        RETURN VARCHAR2 IS

            v_Ename       emp.ename%TYPE;

            v_Job         emp.job%TYPE;

            v_Sal         emp.sal%TYPE;

        BEGIN

            SELECT ename,

                   job,

                   sal

              INTO v_Ename,

                   v_job,

                   v_sal

              FROM emp

             WHERE empno = p_Empno;

           —

            RETURN (‚Name: ‚||

                    v_Ename ||

                    ‚ Job: ‚||

                    v_Job   ||

                    ‚ Sal: ‚||

                    v_Sal);

        END;

    BEGIN

      dbms_output.put_line (gebe_alles_lokal);

    END;

    /

    Prozedur:

     

    set serverout on

    ____________________________________________

     

    DECLARE

        PROCEDURE gebe_alles_lokal (

            p_Empno   IN  emp.empno%TYPE := 7902) AS

            v_Ename   emp.ename%TYPE;

            v_Job     emp.job%TYPE;

            v_Sal     emp.sal%TYPE;

        BEGIN

            SELECT ename,

                   job,

                   sal

              INTO v_Ename,

                   v_job,

                   v_sal

              FROM emp

             WHERE empno = p_Empno;

           —

            dbms_output.put_line (‚Name: ‚||

                                  v_Ename ||

                                  ‚ Job: ‚||

                                  v_Job   ||

                                  ‚ Sal: ‚||

                                  v_Sal);

        END;

    BEGIN

      gebe_alles_lokal;

    END;

    /

     

    Der Funktionsname ist nur in demjenigen Block sichtbar, in dem er deklariert wurde, und der Geltungsbereich erstreckt sich von der Deklaration bis zum Blockende.

    Die Deklaration von lokalen Unterprogrammen muß am Ende eines Deklarationsbereichs erfolgen.


    2.2.5 Abhängigkeiten zwischen den Unterprogrammen

    Wenn eine Prozedur oder Funktion kompiliert wird, werden alle Oracle –Objekte im Data Dictionary (Inhaltsverzeichnis für alle Objekte der Datenbank) aufgezeichnet, auf die verwiesen wird. Die Prozedur ist abhängig von diesen Objekten. Ein Unterprogramm wird im Data Dictionary als unzulässig  markiert, wenn es zu Kompilierungsfehlern führt. Ein gespeichertes Unterprogramm kann weiterhin unzulässig werden, wenn eine DDL-Operation mit einem der von ihm abhängigen Objekte durchgeführt wird, d.h. wenn z.B. mit einem ALTER TABLE emp ADD (wert   number(2)); die Tabelle emp um die Spalte wert  erweitert wird, werden alle abhängigen Prozeduren bzw. Funktionen mit dem Status INVALID (fehlerhaft) versehen. Somit müssen  diese Prozeduren bzw. Funktionen neu kompiliert werden.


    2.2.6 Die Berechtigung zu EXECUTE

    Für gespeicherte Unterprogramme (Prozeduren und Funktionen) und Pakete (werden weiter hinten beschrieben) ist die EXECUTE-Berechtigung entscheidend. Da sie sonst von Benutzern, die keine EXECUTE-Berechtigung erteilt bekommen haben, nicht gestartet werden können.


    PAKETE

     2.3 ENTWICKLUNG VON PAKETEN

    Pakete (Packages) sind neben den Prozeduren und Funktionen die dritte Art von benannten PL/SQL-Blöcken. Für PL/SQL sind sie eine sehr nützliche Einrichtung, und sie stellen einen Mechanismus zur Erweiterung der Sprache zur Verfügung. In diesem Kapitel werden zunächst die Syntax der Paketerstellung unter die Lupe genommen, bevor wir dann einige der Vorteile beim Einsatz von Paketen erläutern.

    Bei einem Paket handelt es sich um ein PL/SQL-Konstrukt, das es ermöglicht, miteinander in Verbindung stehende Objekte zusammen zu speichern. Ein Paket besteht aus zwei voneinander getrennten Teilen: der Spezifikation und dem Rumpf. Beide werden getrennt voneinander im Data Dictionary gespeichert. Anders als Prozeduren und Funktionen, die entweder lokal in einem Block enthalten oder in der Datenbank gespeichert sein können, kann ein Paket nur gespeichert werden, aber nicht lokal eingebettet sein. Neben der Möglichkeit, miteinander in Verbindung stehende Objekte zusammenzufassen, erweisen sich Pakte als nützlich, weil für sie in Hinsicht auf Abhängigkeit (z.B. eine Prozedur ruft eine andere Prozedur auf. Ist davon eine INVALID sind beide INVALID.) weniger Einschränkungen gelten.




    2.3.1 Die Paketspezifikation

    Die Paketspezifikation (auch Paketkopf genannt) enthält Informationen über den Inhalt des Paketes. Jedoch enthält sie keinen Programmcode für irgendwelche Prozeduren.

    Beispiel:

     

    CREATE OR REPLACE PACKAGE test_pak AS

        — PROZEDUR

        PROCEDURE gebe_alles_paket_p (

            p_Empno       IN  emp.empno%TYPE := 7902);

     

        — FUNKTION

        FUNCTION gebe_alles_paket_f (

              p_Empno       IN  emp.empno%TYPE := 7902)

        RETURN VARCHAR2;

     

        — EXCEPTION

        e_KeinMitarbeiter EXCEPTION;

    END test_pak;

    /

     

    Die allgemeine Syntax lautet:

    CREATE OR REPLACE PACKAGE package_name {IS | AS}

        procedure_specification |

        function_specification |

        variable_specification |

        type_specification |

        exception_specification |

        cursor_specification

    END package_name;


    2.3.2 Der Paketrumpf

    Der Paketrumpf ist ein getrennt vom Paketkopf im Data Dictionary vorliegendes Objekt. Er kann erst dann erfolgreich kompiliert werden, wenn der Paketkopf bereits erfolgreich kompiliert worden ist. Der Rumpf enthält den Programmcode für die im Paketkopf vorwärtsdeklarierten Unterprogramme.

     

    Beispiel:

     

    CREATE OR REPLACE PACKAGE BODY test_pak AS

        — PROZEDUR

        PROCEDURE gebe_alles_paket_p (

            p_Empno    IN  emp.empno%TYPE := 7902) AS

            v_Ename    emp.ename%TYPE;

            v_Job      emp.job%TYPE;

            v_Sal      emp.sal%TYPE;

        BEGIN

            SELECT ename,

                   job,

                   sal

              INTO v_Ename,

                   v_job,

                   v_sal

              FROM emp

             WHERE empno = p_Empno;

           —

            dbms_output.put_line (‚Name: ‚||

                                  v_Ename ||

                                  ‚ Job: ‚||

                                  v_Job   ||

                                  ‚ Sal: ‚||

                                  v_Sal);

        END gebe_alles_paket_p;

     

     

     

        — FUNKTION

        FUNCTION gebe_alles_paket_f (

              p_Empno  IN  emp.empno%TYPE := 7902)

        RETURN VARCHAR2 IS

            v_Ename    emp.ename%TYPE;

            v_Job      emp.job%TYPE;

            v_Sal      emp.sal%TYPE;

        BEGIN

            SELECT ename,

                   job,

                   sal

              INTO v_Ename,

                   v_job,

                   v_sal

              FROM emp

             WHERE empno = p_Empno;

           —

            RETURN ‚Name: ‚||

                   v_Ename ||

                   ‚ Job: ‚||

                   v_Job   ||

                   ‚ Sal: ‚||

                   v_Sal;

         END gebe_alles_paket_f;

    END test_pak;

    /

     

    Aufruf Beispiel 1 (Funktions):

      

    set serverout on

    ____________________________________________

     

    BEGIN

       dbms_output.put_line  

                   (test_pak.gebe_alles_paket_f(7955));

    END;

    /

     

    Aufruf Beispiel 1 (Prozedur):

     

    set serverout on

    ____________________________________________

     

    exec test_pak.gebe_alles_paket_p(7955);


    2.3.3 Das Überladen von Unterprogrammen

    In einem Paket können Prozeduren und Funktionen überladen werden. Das bedeutet, dass es mehr als eine Prozedur bzw. Funktion mit demselben Namen, aber mit unterschiedlichen Parametern geben kann. Dabei handelt es sich um eine sehr nützliche Eigenschaft, da sie es ermöglicht, ein und denselben Vorgang auf Objekte verschiedenen Typs anzuwenden. Nehmen wir beispielweise an, wir wollen einen Mitarbeiter entweder durch Angabe der empno oder durch Angabe von ename lesen.

     

    Beispiel:

       Kopf:

    CREATE OR REPLACE PACKAGE test_pak2 AS

        — FUNKTION

        FUNCTION gebe_alles_paket_f (

           p_Empno   IN  emp.empno%TYPE := 7902)

        RETURN VARCHAR2;

     

        — FUNKTION

        FUNCTION gebe_alles_paket_f (

           p_Ename   IN  emp.ename%TYPE := ‚mueller‘)

        RETURN VARCHAR2;

    END test_pak2;

    /

     

     

        Rumpf:

     

    CREATE OR REPLACE PACKAGE BODY test_pak2 AS

        — FUNKTION

        FUNCTION gebe_alles_paket_f (

              p_Empno  IN  emp.empno%TYPE := 7902)

        RETURN VARCHAR2 IS

            v_Ename    emp.ename%TYPE;

            v_Job      emp.job%TYPE;

            v_Sal      emp.sal%TYPE;

        BEGIN

            SELECT ename,

                   job,

                   sal

              INTO v_Ename,

                   v_job,

                   v_sal

              FROM emp

             WHERE empno = p_Empno;

           —

            RETURN ‚Name: ‚||

                   v_Ename ||

                   ‚ Job: ‚||

                   v_Job   ||

                   ‚ Sal: ‚||

                   v_Sal   ||

                   ‚ nach Empno‘;

         END gebe_alles_paket_f;

     

        — FUNKTION

        FUNCTION gebe_alles_paket_f (

              p_Ename  IN  emp.ename%TYPE := ‚mueller‘)

        RETURN VARCHAR2 IS

            v_Ename    emp.ename%TYPE;

            v_Job      emp.job%TYPE;

            v_Sal      emp.sal%TYPE;

        BEGIN

            SELECT ename,

                   job,

                   sal

              INTO v_Ename,

                   v_job,

                   v_sal

              FROM emp

             WHERE ename = p_Ename;

           —

            RETURN ‚Name: ‚||

                   v_Ename ||

                   ‚ Job: ‚||

                   v_Job   ||

                   ‚ Sal: ‚||

                   v_Sal   ||

                   ‚ nach Ename‘;

         END gebe_alles_paket_f;

    END test_pak2;

    /

     

     Aufruf (1 x mit empno (7955) und 1 x mit ename (FORD)):

      

    BEGIN

       dbms_output.put_line  

             (test_pak2.gebe_alles_paket_f(7955));

       dbms_output.put_line

             (test_pak2.gebe_alles_paket_f(‚FORD‚));

    END;

    /


    TRIGGER Zündung

     TRIGGER

    Die Oracle-Datenbank löst einen Trigger aus, sobald sich eine DML-Anweisung auf die Tabelle auswirkt.



    2.4 TRIGGER

    Trigger ähneln Prozeduren bzw. Funktionen in der Weise, dass es sich bei Ihnen um benannte PL/SQL-Blöcke mit Abschnitten für die Deklaration, den ausführbaren Code und die Exceptionbehandlung handelt. Wie Pakete müssen auch Trigger in der Datenbank gespeichert sein und können nicht lokal in einem Block vorliegen. Allerdings wird eine Prozedur explizit mittels eines Prozeduraufrufs aus einem anderen Block heraus ausgeführt, wobei auch Argumente übergeben werden können. Ein Trigger dagegen wird implizit bei jedem Auftreten eines auslösenden Ereignisses ausgeführt, und ein Trigger kann keine Argumente enthalten. Der Vorgang des Ausführens eines Triggers wird Auslösen oder auch Feuern des Triggers genannt. Bei dem auslösenden Ereignis handelt es sich um eine DML-Operation (INSERT, UPDATE oder DELETE) mit einer Datenbanktabelle.

    Trigger können auf vielfältige Weise eingesetzt werden, wie z.B.:

    • zur Überwachung komplexer Integritätsregeln, die sich nicht durch die bei der Tabellenerstellung deklarierten Integritätsregeln realisieren lassen.
    • zum Registrieren von Informationen in einer Tabelle, indem die durchgeführten Änderungen und die ändernde Person aufgezeichnet werden.
    • um andere Programme automatisch darauf hinzuweisen, dass aufgrund von Änderungen an einer Tabelle bestimmte Handlungen durchgeführt werden müssen.

    Beispiel:

        Tabelle trigger_tab erstellen:

     

    CREATE TABLE trigger_tab

                   (nummer         NUMBER(15),

                    bezeichnung    VARCHAR2(2000),

                    status         NUMBER(1)

                   );

      

        Tabelle trigger_tab_log erstellen:

    CREATE TABLE trigger_tab_log

                   (aktion          CHAR(1),

                    datum_zeit      DATE,

                    mitarbeiter     VARCHAR2(10),

                    alt_nummer      NUMBER(15),

                    alt_bezeichnung VARCHAR2(2000),

                    alt_status      NUMBER(1),

                    neu_nummer      NUMBER(15),

                    neu_bezeichnung VARCHAR2(2000),

                    neu_status      NUMBER(1)

                    );

      

        Trigger t_trigger_tab erstellen:

     

    CREATE OR REPLACE TRIGGER t_trigger_tab

      AFTER INSERT OR DELETE OR UPDATE ON trigger_tab

      FOR EACH ROW

    DECLARE

      v_aktion         char(1);

    BEGIN

      IF    INSERTING THEN

            v_aktion := ‚I‘;            

      ELSIF UPDATING  THEN

            v_aktion := ‚U‘;

      ELSE

            v_aktion := ‚D‘;

      END IF;

     

      INSERT INTO trigger_tab_log

             (aktion,

              datum_zeit,

              mitarbeiter,

              alt_nummer,

              alt_bezeichnung,

              alt_status,

              neu_nummer,

              neu_bezeichnung,

              neu_status)

       VALUES(v_aktion,

              SYSDATE,

              USER,

              :old.nummer,

              :old.bezeichnung,

              :old.status,

              :new.nummer,

              :new.bezeichnung,

              :new.status);

    END t_trigger_tab;

    /

     

    Mit CREATE OR REPLACE TRIGGER wird veranlasst, dass ein Trigger mit dem Namen t_trigger_tab angelegt wird.  Die AFTER INSERT OR DELETE OR UPDATE ON Anweisung teilt mit, dass  immer nach dem Einfügen (INSERT), Löschen (DELETE) oder Ändern (UPDATE) eines Datensatzs (FOR EACH ROW) dieser Trigger ausgelöst werden soll. Hier besteht ebenso die Möglichkeit, anzugeben, dass der Trigger vorher (BEFORE) oder anstatt (INSTEAD OF (ab Oracle8)) der eigentlichen Aktion feuern soll. INSERTING, UPDATING oder DELETING ermöglichen es, im Trigger den Anlaß dieser Aktion zu erfragen. Die Zusätze :old. und :new. beinhalten zum einen den Wert vor der Aktion (:old.), sowie den zu ersetzenden Wert (:new.).

     

    Reaktion des Trigger testen:

        2 x einen INSERT durchführen (Testdaten anlegen):

     

    INSERT INTO trigger_tab VALUES (1,’Test 1′, 1);

    INSERT INTO trigger_tab VALUES (2,’Test 2′, 1);

     

        Veränderungen in der LOG-Tabelle (trigger_tab_log ) ansehen:

     

    set linesize 300

    col alt_bezeichnung format a20;

    col neu_bezeichnung format a20;

    select * from trigger_tab_log;

      

        1 x ein UPDATE durchführen (Testdaten fortschreiben):

     

    UPDATE trigger_tab

       SET bezeichnung = ‚Test 1.1‘

     WHERE nummer = 1;

      

        Veränderungen in der LOG-Tabelle (trigger_tab_log ) ansehen:

    set linesize 300

    col alt_bezeichnung format a20;

    col neu_bezeichnung format a20;

    select * from trigger_tab_log;

     

        1 x ein DELETE durchführen (Testdaten fortschreiben):

    DELETE trigger_tab

     WHERE nummer = 1;

      

        Veränderungen in der LOG-Tabelle (trigger_tab_log ) ansehen:

     

    set linesize 300

    col alt_bezeichnung format a20;

    col neu_bezeichnung format a20;

    select * from trigger_tab_log;


    2.4.1 Trigger auf Zeilenebene (create trigger ... for each row)

    Die Trigger auf Zeilenebene werden für jede Zeile in einer Transaktion ausgeführt. Fügt eine Transaktion beispielsweise 500 Zeilen in eine Tabelle ein, wird ein Trigger auf Zeilenebene 500 Mal ausgeführt.


    2.4.2 Trigger auf Anweisungsebene (create trigger ...)

    Die Trigger auf Anweisungsebene werden bei jeder Transaktion einmal ausgeführt. Fügt eine Transaktion beispielsweise 500 Zeilen ein eine Tabelle ein, wird ein Trigger auf Anweisungsebene nur ein einziges Mal ausgeführt.


    2.4.3 Trigger auf Datenbankebene

    Sie können Trigger anlegen, die bei Datenbankereignissen aktiviert werden. Dazu gehören Fehler, An- und Abmeldungen, Starten und Herunterfahren der Datenbank. Diese Trigger dienen der Automatisierung der Datenbankverwaltung.

     

    Die Syntax lautet:

    CREATE TRIGGER test_trigger

     AFTER startup ON DATABASE …


    2.4.4 BEFORE- und AFTER-Trigger

    Da die Trigger auf Grund von Ereignissen auftreten, kann man sie so einrichten, dass sie entweder unmittelbar vor oder nach diesem Ereignissen ausgeführt werden.

     

    Die Syntax lautet:

    CREATE TRIGGER test_trigger

     BEFORE

     

     oder

    CREATE TRIGGER test_trigger

     AFTER


    2.4.5 INSTEAD OF-Trigger (ab Oracle8)

    Mit einem INSTEAD OF-Trigger teilen Sie Oracle8 mit, was an Stelle der Aktionen zu tun ist, die einen Trigger aktiviert haben. So können Sie beispielsweise mit einem INSTEAD OF-Trigger auf einen VIEW INSERTs in eine Tabelle umleiten oder zur Aktualisierung von Tabellen nutzen, die Bestandteil der View sind.

    Die Syntax lautet:

    CREATE TRIGGER test_trigger

     INSTEAD OF


    2.4.6 WHEN-Klausel

    Die WHEN-Klausel ist nur bei zeilenbezogenen Triggern zugelassen. Der Triggerrumpf wird nur für diejenigen Zeilen ausgeführt, welche  die in der WHEN-Klausel angegebene Bedingung erfüllen.

    Die Syntax lautet:

    CREATE OR REPLACE TRIGGER t_trigger_tab

      AFTER INSERT OR DELETE OR UPDATE ON trigger_tab

      FOR EACH ROW

     WHEN (NEW.nummer <> OLD.nummer) …


    2.4.7 Trigger für DDL-Ereignisse (ab Oracle8i)

    Seit Oracle8i lassen sich Trigger anlegen, die beim Auftreten eines DDL-Ereignisses ausgeführt werden. Mithilfe der Trigger auf DDL-Ereignisse lässt sich der Trigger-Code von create, alter und drop-Befehlen auf Cluster, Funktionen, Indizies, Pakete, Prozeduren, Rollen Sequenzen, Synonyme, Tabellen, Tablespaces, Trigger, Typen, Benutzer oder Views ausführen.

     

    Die Syntax lautet:

     

    CREATE TRIGGER test_trigger

     AFTER DROP ON scott.SCHEMA …

     

        oder

     

    CREATE TRIGGER test_trigger

     AFTER CREATE ON scott.SCHEMA …


    2.4.8 Trigger löschen

    Trigger lassen sich über den Befehl drop trigger löschen.

     

    Die Syntax lautet:

     

    DROP TRIGGER test_trigger;


    Analyse

     2.5 ANALYSE UND FEHLERSUCHE

    „In der Oracle PL / SQL habe ich diese Codierung, es gibt mir Compilerfehler. Ich weiß nicht warum, sieht so aus als hätte ich alles …“

    Die  wenigsten Programme funktionieren von Anfang an korrekt. Außerdem ändern sich häufig die Anforderungen an ein Programm während seiner Entwicklung, und es muß umgeschrieben werden. In beiden Fällen muß das Programm gründlich getestet werden, um sicherzustellen, dass es richtig funktioniert, und sich so verhält wie erwartet.



    2.5.1 Daten in eine Testtabelle einfügen

    Die einfachste Testmethode ist das Einfügen der Werte von lokalen Variablen in eine temporäre Tabelle, während das Programm läuft. Wenn das Programm beendet ist , können Sie die Daten der Tabelle abfragen, und die Werte der Variablen stehen Ihnen zur Verfügung.

     

    Zunächst erstellen Sie sich ein DEBUG-Paket:

     

        Erstellen der Tabelle debug_tabelle

     

    drop table debug_tabelle;

    CREATE TABLE debug_tabelle (zeile    number,

                                meldung  varchar2(2000));

     

      

        Erstellen des Paket-Kopf

     

    CREATE OR REPLACE PACKAGE debug AS

       PROCEDURE debug(p_Beschreibung IN VARCHAR2,

                       p_Wert         IN VARCHAR2);

     

       PROCEDURE reset;

    END debug;

    /

     

         Erstellen des Paket-Rumpf

     

    CREATE OR REPLACE PACKAGE BODY debug AS

      v_counter   NUMBER;

     

       PROCEDURE debug(p_Beschreibung IN VARCHAR2,

                       p_Wert         IN VARCHAR2) IS

          BEGIN

             INSERT INTO debug_tabelle

                        (zeile,

                         meldung)

                  VALUES(v_counter,

                         p_Beschreibung||‘ : ‚||p_Wert);

             COMMIT;

             v_counter := v_counter + 1;

          END debug;

     

        PROCEDURE reset IS

          BEGIN

            v_counter := 1;

            DELETE FROM debug_tabelle;

          END reset;

    BEGIN /* Paket Initialisierung Code */

       reset;

    END debug;

    /

      

        Beispiel für debug.reset & debug.debug

     

    DECLARE

      CURSOR c_emp IS

        SELECT empno,

               job,

               ename

          FROM emp;

      v_empno      emp.empno%TYPE;

      v_job        emp.job%TYPE;

      v_ename      emp.ename%TYPE;

    BEGIN

      debug.reset;

      debug.debug (‚Vor dem FOR…LOOP…‘,“);

     

      FOR rec IN c_emp

      LOOP

        debug.debug

              (‚emp.empno / emp.job / emp.ename‘,

               rec.empno||‘ / ‚||rec.job||‘ / ‚||rec.ename);  

      END LOOP;

      debug.debug (‚Nach dem FOR…LOOP…‘,“);

    END;

    /

      

        Auslesen der debug_tabelle mit einem SQL-Befehl

     

    set linesize 300

    col meldung format a100

     

    SELECT zeile,

           meldung

      FROM debug_tabelle

     ORDER BY zeile;


    2.5.2 DBMS_OUTPUT

    Die Version von debug, die wir im vorigen Abschnitt kennengelernt haben, ermöglicht es eine begrenzte Ein- und Ausgabe. PL/SQL enthält keinerlei vordefinierte Eingabe-/Ausgabe-Funktionen. Das war vom Entwurf her so beabsichtigt, denn um Daten in der Datenbank zu bearbeiten, ist es nicht erforderlich, die Werte von Variablen und Strukturen ausdrucken zu können. Allerdings ist es ein sehr nützliches Werkzeug zur Fehlersuche. Schließlich wurde die Ausgabe in PL/SQL 2.0 hinzugefügt, und zwar in dem vordefinierten Paket DBMS_OUTPUT.

     

    Die Syntax lautet:

               DBMS_OUTPUT.PUT_LINE (varchar2);

       oder

               DBMS_OUTPUT.PUT_LINE (number);

         oder

               DBMS_OUTPUT.PUT_LINE (date);

      

        Beispiel für DBMS_OUTPUT

     

    set serverout on

    ____________________________________________

     

    DECLARE

      CURSOR c_emp IS

        SELECT empno,

               job,

               ename

          FROM emp;

      v_empno      emp.empno%TYPE;

      v_job        emp.job%TYPE;

      v_ename      emp.ename%TYPE;

    BEGIN

      DBMS_OUTPUT.PUT_LINE (‚Vor dem FOR…LOOP…‘);

      FOR rec IN c_emp

      LOOP

        DBMS_OUTPUT.PUT_LINE

              (‚emp.empno / emp.job / emp.ename’||‘ : ‚||

               rec.empno||‘ / ‚||rec.job||‘ / ‚||rec.ename);  

      END LOOP;

      DBMS_OUTPUT.PUT_LINE (‚Nach dem FOR…LOOP…‘);

    END;

    /


    BUILT-IN's

     2.6 VERWENDUNG VON STANDARD-PACKAGES ( BUILT-IN PACKAGES)

    Oracle stellt mit dem Oracle-Server viele PL / SQL-Pakete bereit, um die Datenbankfunktionalität zu erweitern und PL / SQL-Zugriff auf SQL-Funktionen bereitzustellen. Sie können die bereitgestellten Pakete beim Erstellen Ihrer Anwendungen oder beim Erstellen eigener gespeicherter Prozeduren verwenden.

    STANDARD-PACKAGES

    Wie im Abschnitt 2.5.2 DBMS_OUTPUT bereits dargestellt, erweitern

    Standard-Pakete den Befehlsvorrat von PL/SQL.

    Das zunächst wichtigste Paket wurde Ihnen bereit im Abschnitt 2.5.2 DBMS_OUTPUT dargestellt.


    Praxisbeispiele
  • ORACLE External Table – Dynamic Access Generator » Syntax kurz erklärt
  • DBMS_XSLPROCESSOR.CLOB2FILE vs. UTL_FILE von ORACLE » Syntax kurz erklärt

  • LOB's

     2.7 ÜBERBLICK ÜBER LARGE-OBJECTS (LOB)

    Oracle hat viele neue Multimediadatentypen oder LOB-Datatypen eingeführt, die die Handhabung von Bild-, Video-, Audio- oder großen Zeichendatensätzen erleichtern.

    LARGE-OBJECTS

    Ein LOB ist einfach ein Datenbankfeld, welches eine große Datenmenge enthält, zum Beispiel Grafikdateien oder ein langes Textdokument. Welcher Datentyp von Oracle eignet sich am besten für die Speicherung dieser Art von Information?

    Oracle8 bietet hierfür eine Lösung an. Es wurde eine neue Datenfamilie eingeführt, nämlich die Familie LOB. Es gibt vier verschiedene Arten von LOBs, die für unterschiedliche Datenarten gedacht sind: CLOB, NCLOB, BLOB und BFILE. Da die Daten in der Datenbank gespeichert werden, werden CLOBs, NCLOBs und BLOBs auch allgemein als interne LOBs bezeichnet. BFILEs bezeichnet man als externe LOBs.

    LOB Varianten in Oracle

    • Die maximale Größe eines LOBs beträgt 4 GByte im Unterschied zu den auf 2 GByte beschränkten LONG– und LONG-RAW –Daten unter ORACLE 7
    • LOBs können entweder mit der Aufrufschnittstelle (unter SQL) von Oracle8 oder mit PL/SQL und dem DBMS_LOB-Paket bearbeitet werden. Beide Schnittstellen gewähren direkten Schreib- oder Lesezugriff auf ein LOB (BFILE dürfen nur gelesen werden).
    • Die Einschränkung für LONG– und LONG-RAW-Daten gelten nicht für LOB So ist z.B., anders als bei LONG– und LONG-RAW-Spalten, die Anzahl der LOB-Spalten nur durch die maximale Anzahl der Spalten einer Tabelle beschränkt. Außerdem können die LOB-Daten mit Triggern bearbeitet werden.
    • Objekte können LOB-Attribute haben und Methoden können LOBs als Argumente übernehmen (Methoden sind fertige Routinen, ähnlich den Paketen, die für die Kommunikation zwischen den Objekten zuständig sind (Objekte beinhalten Variablen, Spalten, Methoden (UPDATE-, INSERT-, DELETE-Routinen))). Ein Objekt kann jedoch kein NCLOB-Attribut haben, auch wenn eine Methode ein NCLOB-Argument übernehmen kann.
    • LOBs können als Bindevariablen (z.B. ‚SELECT … WHERE A = bindevariable‘ ) verwendet werden.

    Aufgrund der großen Unterschiede in den Versionen (z.B. fileopen-Befehl in Oracle 8.0.4 und open-Befehl in 8.1.7) wird hier kein weiteres Beispiel dargestellt und das Thema somit nur kurz angedeutet.


     

     Event Katalog 📅 
     Anzeige 

    Oracle und PL/SQL sind eingetragene Warenzeichen der Oracle Corporation.


    Datenbank & Internet

    IT-Seminare, Kurse, Workshops oder Stammtisch


    Ihre Fragen und Anmerkungen sind jederzeit willkommen.

    (Link)

    Schreibe einen Kommentar

    %d Bloggern gefällt das: