Prozeduren

Eine Prozedur – gespeicherte Prozedur, engl. StoredProcedure (SP) – ist vorgesehen für Arbeitsabläufe, die „immer wiederkehrende“ Arbeiten direkt innerhalb der Datenbank ausführen sollen.

(Einführung in SQL: Prozeduren)

Bitte haben Sie Nachsicht: Wegen der vielen Varianten bei den DBMS beschränkt sich dieses Kapitel bei den Hinweisen und Beispielen auf Firebird. Zusammen mit den speziellen Hinweisen zur SQL-Programmierung sollten sie problemlos an andere DBMS-Regeln angepasst werden können. Da es sich um ein Wikibook handelt, dürfen Sie das Buch gerne um weitere Hinweise und andere Beispiele ergänzen.

Ein Überblick

Zur Verwendung von Prozeduren gibt es zwei gegensätzliche Standpunkte, die mit den Begriffen Fat Server und Fat Client zusammengefasst werden können:

Beim Fat Server wird so viel Funktionalität wie möglich in die (zentral gespeicherte) Datenbank gelegt.
Beim Fat Client ist die Datenbank nur für die Speicherung der Daten vorgesehen; sämtliche Verarbeitung erfolgt auf dem Arbeitsplatzrechner.

Der große Vorteil eines Fat Server liegt darin, dass Daten direkt in der Datenbank verarbeitet werden können, sofern keine Ansicht der Daten auf dem Arbeitsplatz benötigt wird. Es ist natürlich überflüssig, die Daten zuerst von der Datenbank zum Arbeitsplatz zu kopieren, dann automatisch zu verarbeiten und schließlich das Arbeitsergebnis (ohne manuelle Überprüfung) auf die Datenbank zurückzukopieren. Einfacher ist es, alles in der Datenbank ausführen zu lassen.

Der große Vorteil eines Fat Client liegt darin, dass das Client-Programm meistens schneller und einfacher geändert werden kann als die Arbeitsabläufe innerhalb der Datenbank.

In der Praxis ist eine Mischung beider Verfahren am sinnvollsten. Zur Steuerung der Arbeitsabläufe in der Datenbank werden die gespeicherten Prozeduren verwendet; Beispiele dafür werden in diesem Abschnitt behandelt.

Prozedur definieren

Prozedur erstellen

Die Syntax für die Definition einer Prozedur sieht so aus:

CREATE OR ALTER PROCEDURE <routine-name>
      ( [ <parameterliste> ] )
AS
BEGIN
      [ <variablenliste> ]
      <routine body>
END

Notwendig sind folgende Angaben:

neben dem Befehlsnamen der Name der Prozedur
das Schlüsselwort AS als Zeichen für den Inhalt
die Schlüsselwörter BEGIN und END als Begrenzer für den Inhalt

Hinzu kommen die folgenden Angaben:

eine Liste von Parametern, sofern Werte übergeben oder abgefragt werden
- Bei den meisten DBMS werden Eingabe- und Ausgabe-Parameter durch Schlüsselwörter wie IN und OUT unterschieden, sie stehen dabei innerhalb derselben Liste.
- Bei Firebird enthält die Parameterliste nur die Eingabe-Parameter; die Ausgabe-Parameter stehen hinter RETURNS in einer eigenen Liste mit Klammern.
eine Liste von Variablen, die innerhalb der Prozedur verwendet werden; diese Liste steht je nach DBMS zwischen AS und BEGIN oder innerhalb des Rumpfes (also zwischen BEGIN und END)
die Befehle, die innerhalb der Prozedur auszuführen sind

Bei den Befehlen innerhalb der Prozedur handelt es sich um „normale“ SQL-Befehle sowie um Bestandteile der SQL-Programmiersprache. Zum Semikolon, das den Abschluss des CREATE-Befehls darstellen sollte, aber innerhalb des Inhalts bereits für jede einzelne Anweisung benutzt wird, beachten Sie bitte auch die Hinweise zur SQL-Programmierung unter Anweisungen begrenzen.

Prozedur ausführen

Es gibt mehrere Verfahren, wie eine Prozedur vom Nutzer, aus einem Anwendungsprogramm oder aus einer anderen Prozedur oder einem Trigger heraus benutzt werden kann.

Der direkte Weg geht bei Firebird über Execute Procedure und führt eine Prozedur aus:

EXECUTE PROCEDURE <routine-name> [ <eingabe-parameter> ] 
      [ RETURNING_VALUES <ausgabe-parameter> ] ;

Dem Namen der Prozedur folgen (soweit erforderlich) die Eingabe-Parameter; mehrere werden mit Komma getrennt. Sofern die Prozedur Werte zurückliefert, werden sie in eine oder mehrere Variablen eingetragen, die dem Begriff RETURNING_VALUES folgen.

Bei den anderen DBMS gehören zu EXECUTE (MySQL: CALL) nicht nur die Eingabe-, sondern auch die Ausgabe-Parameter. Beispiele stehen im Kapitel zur SQL-Programmierung bei den Erläuterungen des jeweiligen DBMS.

Eine Prozedur kann bei manchen DBMS auch mit Select Procedure wie eine Abfrage verwendet werden. Dies ist vor allem dann erforderlich, wenn eine Reihe von Werten oder Datensätzen zurückgeliefert werden.

SELECT * FROM <routine-name> [ <eingabe-parameter> ] ;

Die Ausgabe-Parameter stehen dann in der Liste der Spalten, also in der Ergebnismenge des SELECT-Befehls.

Man könnte auch an Execute Block denken, aber dieser Hinweis passt hier überhaupt nicht. Ein Block ist eine Menge von Anweisungen, die einmalig benutzt werden, aber eben nicht in der Datenbank gespeichert werden. Ein solcher Block ersetzt eine gespeicherte Prozedur, wenn das Speichern überflüssig ist, und wird bei der SQL-Programmierung unter Routinen ohne feste Speicherung beschrieben.

Prozedur ändern

Eine Änderung einer Prozedur ist nur möglich durch eine vollständige Neudefinition mit allen Bestandteilen. Deshalb wird es heutzutage fast immer durch CREATE OR ALTER zusammengefasst. Wenn dies nicht möglich ist, dann muss ein einzelner ALTER-Befehl benutzt werden, der alle Bestandteile der obigen Erläuterungen enthält.

Prozedur löschen

Das geht wie üblich mit dem DROP-Befehl:

DROP PROCEDURE <routine-name>;

Beispiele

	Hinweis Wir empfehlen, dieses Kapitel sowie das Kapitel Programmierung in zwei Fenstern Ihres Browsers gleichzeitig zu öffnen und diese nebeneinander zu setzen. Dann können Sie ein umfangreiches Beispiel sowie die passenden Einzelheiten je nach SQL-Dialekt gleichzeitig sehen.

Um die Beispiele für ein anderes DBMS zu übertragen, sind vor allem folgende Punkte zu ändern:

Die Ausgabe-Parameter folgen nicht nach RETURNS, sondern gehören in die Parameterliste.
Die Deklaration der Variablen ist anzupassen, ebenso die Kennzeichnung mit Doppelpunkt.
Schleifen und ähnliche Maßnahmen sind umzuschreiben.
Dies gilt auch dafür, wie Werte – z. B. eines SELECT – übernommen werden.

Ersatz für eine View mit Parametern

Im Kapitel zu VIEWs wollten wir eine Ansicht mit variabler Selektion erstellen, aber variable Bedingungen waren nicht möglich. Mit einer Prozedur geht es.

Aufgabe

Mache aus der View Mitarbeiter_in_Abteilung eine Prozedur Mitarbeiter_aus_Abteilung, die die Abteilungsnummer als Parameter erhält.

Firebird-Version

CREATE OR ALTER PROCEDURE Mitarbeiter_aus_Abteilung 
        ( Abt INTEGER)
RETURNS ( Personalnummer VARCHAR(10),
          Name           VARCHAR(30),
          Vorname        VARCHAR(30),
          Geburtsdatum   DATE )
AS
BEGIN
  FOR SELECT Personalnummer, Name, Vorname, Geburtsdatum
        FROM Mitarbeiter
       WHERE Abteilung_ID = :Abt
    ORDER BY Ist_Leiter, Name, Vorname
        INTO :Personalnummer, :Name, :Vorname, :Geburtsdatum
  DO SUSPEND;
END

Als Prozedur enthält die Abfrage folgende Bestandteile:

Als Eingabe-Parameter wird die gewünschte Abteilungsnummer erwartet und in der Variablen Abt gespeichert.
Als Ausgabe-Parameter werden die gewünschten Spalten der Tabelle Mitarbeiter aufgeführt.
Weitere Variable werden nicht benötigt.

Zwischen BEGIN und END steht der eigentliche Arbeitsablauf mit Anweisungen gemäß SQL-Programmierung.

Es handelt sich dabei vor allem um einen SELECT-Befehl mit der gewünschten WHERE-Klausel, die bei der VIEW nicht möglich war.
Diese Abfrage wird in eine FOR-DO-Schleife eingebunden.
Dabei werden die Spalten eines jeden ausgewählten Datensatzes mit INTO in die Ausgabe-Variablen übertragen und per SUSPEND zurückgegeben.

Damit haben wir die gewünschte variable Abfrage. Bei der Verwendung ist es für den Anwender gleichgültig, ob er sie wie eine View oder wie eine Prozedur aufzurufen hat:

Aufgabe

Hole alle Mitarbeiter einer bestimmten Abteilung.

Aufruf als View

SELECT * FROM Mitarbeiter_in_Abteilung
 WHERE Abt = 5;

Aufruf als Prozedur

SELECT * FROM Mitarbeiter_aus_Abteilung (5);

INSERT in mehrere Tabellen

Wenn in der Beispieldatenbank ein neuer Versicherungsvertrag eingegeben werden soll, benötigt man in der Regel drei INSERT-Befehle:

für einen neuen Eintrag in der Tabelle Fahrzeug
für einen neuen Eintrag in der Tabelle Versicherungsnehmer
für einen neuen Eintrag in der Tabelle Versicherungsvertrag mit Verweisen auf die beiden anderen Einträge

Bei den ersten beiden Einträgen ist durch SELECT die neue ID abzufragen und beim dritten INSERT zu verwenden. Warum sollte man sich die Arbeit in dieser Weise erschweren? Soll doch die Datenbank einen einzigen INSERT-Befehl entgegennehmen und die Parameter selbständig auf die beteiligten Tabellen verteilen.

Aufgabe

Speichere einen neuen Versicherungsvertrag mit allen Einzelheiten.

Firebird-Version

create or alter PROCEDURE Insert_Versicherungsvertrag
   ( /* Bestandteile des Vertrags */
     Vertragsnummer VARCHAR(20),
     Abschlussdatum DATE,
     Art            CHAR(2),
     Praemiensatz   INTEGER,
     Basispraemie   DECIMAL(9,0),
     Mitarbeiter_ID INTEGER,
     /* Angaben zum Versicherungsnehmer */
     Name                          VARCHAR(30),
     Vorname                       VARCHAR(30),
     Geschlecht                    CHAR(1),
     Geburtsdatum                  DATE,
     Fuehrerschein                 DATE,
     Ort                           VARCHAR(30),
     PLZ                           CHAR(5),
     Strasse                       VARCHAR(30),
     Hausnummer                    VARCHAR(10),
     Eigener_Kunde                 CHAR(1),
     Versicherungsgesellschaft_ID  INTEGER,
     /* Angaben zum Fahrzeug */
     Kennzeichen    VARCHAR(10),
     Farbe          VARCHAR(30),
     Fahrzeugtyp_ID INTEGER
   )
   RETURNS( NewID INTEGER )
AS
   DECLARE VARIABLE NewFahrzeugID   integer;
   DECLARE VARIABLE NewVersnehmerID integer;
BEGIN
  NewFahrzeugID = NEXT VALUE FOR Fahrzeug_ID;
  INSERT into Fahrzeug
       values ( :NewFahrzeugID, :Kennzeichen, :Farbe, :Fahrzeugtyp_ID );
  NewVersnehmerID = NEXT VALUE FOR Versicherungsnehmer_ID;
  insert into Versicherungsnehmer
       values ( :NewVersnehmerID, :Name, :Vorname, :Geburtsdatum, :Fuehrerschein,
                :Ort, :PLZ, :Strasse, :Hausnummer,
                :Eigener_Kunde, :Versicherungsgesellschaft_ID, :Geschlecht );
  NewID = NEXT VALUE FOR Versicherungsvertrag_ID;
  insert into Versicherungsvertrag
       values ( :NewID, :Vertragsnummer, :Abschlussdatum, :Art, :Mitarbeiter_ID,
                :NewFahrzeugID, :NewVersnehmerID, :Praemiensatz, :Abschlussdatum, :Basispraemie );
  SUSPEND;
END

Als Eingabe-Parameter werden alle Werte benötigt, die der Sachbearbeiter für die einzelnen Tabellen eingeben muss (siehe die durch Kommentare getrennten Abschnitte).

Als Ausgabe-Parameter wollen wir die ID für den neuen Vertrag erhalten.

Als Variable werden die Verweise auf die zugeordneten Tabellen Fahrzeug und Versicherungsnehmer vorgesehen.

Der Arbeitsablauf ist ganz einfach strukturiert:

Hole die nächste ID für die Tabelle Fahrzeug und speichere den nächsten Eintrag in dieser Tabelle.
Hole die nächste ID für die Tabelle Versicherungsnehmer und speichere den nächsten Eintrag in dieser Tabelle.
Hole die nächste ID für die Tabelle Versicherungsvertrag und speichere den nächsten Eintrag in dieser Tabelle. Benutze dafür die beiden anderen IDs.

Bei einem DBMS mit automatischem Zähler wird statt der „nächsten ID“ die gerade neu vergebene ID abgefragt. Möglichkeiten dafür enthält der Abschnitt Die letzte ID abfragen des Kapitels „Tipps und Tricks“.

Damit wird ein neuer Vertrag mit einem einzigen Befehl erstellt:

Aufgabe

Speichere einen neuen Vertrag mit allen Angaben.

Firebird-Version

EXECUTE PROCEDURE Insert_Versicherungsvertrag
   ( 'HS-38', '03.11.2009', 'VK', 125, 870, 11, 'Graefing', 'Charlotte',
     'W', '09.11.1989', '26.02.2008', 'Hattingen', '45529',
     'Laakerweg', '17 b', 'J', NULL, 'BO-MC 413', 'gelb', 8 );

Ausgabe

------ Procedure executing results: ------
NEWID = 29     /* die ID des neuen Vertrags */

Automatisches UPDATE gemäß Bedingungen

Bei einer Versicherungsgesellschaft muss regelmäßig der Prämiensatz eines Vertrags neu berechnet werden: Bei verschuldeten Schadensfällen wird er (abhängig von der Höhe des Schadens) erhöht, bei Schadensfreiheit verringert. Dies ist eine Aufgabe, die die Datenbank selbständig erledigen kann und soll. Das ist ein komplexer Arbeitsablauf mit mehreren Prüfungen; vor allem die unterschiedliche Zuordnung neuer Werte wird hier stark vereinfacht.

Aufgabe

Berechne für alle (eigenen) Verträge, ob eine neue Prämienrechnung ansteht. Dabei ist zu prüfen, ob wegen neuer Schadensfälle der Prämiensatz zu erhöhen oder wegen Schadensfreiheit zu verringern ist.

Die einzelnen Schritte werden anhand des folgenden Codes erläutert.

Firebird-Version

create or alter PROCEDURE Update_Praemiensatz
                ( Aktualisierung DATE default CURRENT_DATE )
        RETURNS ( Erledigt INTEGER )
AS
  DECLARE VARIABLE current_id INTEGER;
  DECLARE VARIABLE current_fz INTEGER;
  DECLARE VARIABLE current_aenderung DATE;
  DECLARE VARIABLE vergleich_aenderung DATE;
  DECLARE VARIABLE current_satz INTEGER;
  DECLARE VARIABLE current_value DECIMAL(16,2);
BEGIN
  Erledigt = 0;
  SUSPEND;

  /* Vorarbeit: Anfänger werden auf Praemiensatz 200 gesetzt; das kann aber
     nur für noch nicht behandelte Verträge gelten und muss deshalb noch
     vor der nächsten IS NULL-Prüfung erledigt werden. */
  UPDATE Versicherungsvertrag vv
     SET Praemiensatz = 200
   WHERE Praemienaenderung is null
     and Versicherungsnehmer_ID 
         /* bestimme die Liste der Anfänger, aber nur für eigene Kunden */
         in ( SELECT ID 
                from Versicherungsnehmer vn
                     /* wenn der Führerschein beim Vertragsabschluss weniger als 2 Jahre alt ist */
               where vn.Eigener_kunde = 'J'
                 and ( ( DATEADD( YEAR,  2, vn.Fuehrerschein ) > vv.Abschlussdatum )
                     /* wenn der VersNehmer beim Führerschein-Erwerb noch nicht 21 Jahre alt ist */
                    or ( DATEADD( YEAR, 21, vn.Geburtsdatum  ) > vn.Fuehrerschein  ) ) );

  /* Schritt 1: zu bearbeiten sind alle Verträge für eigene Kunden, deren letzte
     Prämienänderung „zu lange“ zurückliegt */
  for SELECT vv.ID, Fahrzeug_ID,
             CASE WHEN Praemienaenderung is null THEN Abschlussdatum
                  ELSE Praemienaenderung
             END, 
             Praemiensatz
        from Versicherungsvertrag vv
             join Versicherungsnehmer vn on vn.Id = vv.Versicherungsnehmer_id
       where vn.Eigener_Kunde = 'J'
         and ( Praemienaenderung is null or Praemienaenderung <= :Aktualisierung)
        into :current_id, :current_fz, :current_aenderung, :current_satz
  DO BEGIN
    /* Schritt 2: wegen der Übersicht das mögliche Schlussdatum vorher bestimmen */
    vergleich_aenderung = DATEADD( YEAR, 1, current_aenderung );
    vergleich_aenderung = DATEADD( DAY, -1, vergleich_aenderung );

    /* Schritt 3: weitere Bearbeitung, sofern die Aktualisierung über das
       Vergleichsdatum hinausgeht */
    if (Aktualisierung >= vergleich_aenderung) THEN
    BEGIN
       /* Schritt 4: suche zu diesem Vertrag, d.h. diesem Fahrzeug alle Schadensfälle in dieser Zeit
          und summiere die Schadenssumme nach Schuldanteil */
       select SUM( sf.Schadenshoehe * zu.Schuldanteil / 100 )
         from Zuordnung_SF_FZ zu
              join Schadensfall sf on zu.Schadensfall_id = sf.Id
        where zu.Fahrzeug_ID = :current_fz
          and sf.Datum between :current_aenderung and :vergleich_aenderung
         into :current_value;

      /* Schritt 5: abhängig von (anteiliger) Schadenssumme und bisherigem Prämiensatz
         wird der neue Prämiensatz bestimmt und das Datum weitergesetzt */
      update Versicherungsvertrag
         set Praemiensatz =
             CASE
             WHEN :current_value is null THEN CASE
                                              WHEN :current_satz >      100
                                                   THEN :current_satz - 20
                                              WHEN :current_satz BETWEEN 40 AND 100
                                                   THEN :current_satz - 10
                                              ELSE 30
                                              END
             WHEN :current_value =     0 THEN :current_satz - 10
             WHEN :current_value <   500 THEN :current_satz
             WHEN :current_value <  1000 THEN :current_satz + 10
             WHEN :current_value >= 1000 THEN :current_satz + 30
             END,
             Praemienaenderung = DATEADD( YEAR, 1, :current_aenderung )
       where ID = :current_id;
       Erledigt = Erledigt + 1;
    END
  END

  SUSPEND;
END

Als Eingabe-Parameter wird nur das Datum der derzeitigen Aktualisierung benötigt. Ein Vertrag wird dann geprüft, wenn der Zeitraum der nächsten Prämienrechnung – vom Datum der letzten Prämienänderung aus ein Jahr – das Datum der Aktualisierung enthält. Mit diesem Verfahren werden immer nur „neue“ Schadensfälle und die jeweils anstehenden Prämienrechnungen berücksichtigt.

Als Ausgabe-Parameter nehmen wir nur die Anzahl der berechneten Verträge, also der geänderten Erledigt-Vermerke.

Als Variable benötigen wir Zwischenwerte für alle Angaben, die im Arbeitsablauf benutzt werden.

Der Arbeitsablauf umfasst die folgenden Punkte:

Der Rückgabewert wird mit dem Anfangswert belegt und angezeigt zum Zeichen dafür, dass die Prozedur arbeitet.
Als Vorarbeit wird ein „Anfänger“, der als Eigener_Kunde gespeichert ist, auf einen Prämiensatz von 200 gesetzt. Als Anfänger gilt, wenn der Führerschein beim Abschluss des Vertrags noch nicht zwei Jahre alt ist oder wenn der Führerschein vor dem 21. Geburtstag erworben wurde.

Bei der Prüfung auf „zwei Jahre“ handelt es sich nicht um einen sachlichen Fehler: Dieser Arbeitsablauf wird innerhalb des ersten Versicherungsjahres ausgeführt; später ist die Hauptbedingung „Praemienaenderung IS NULL“ niemals mehr gegeben. In der Praxis müssen solche Bedingungen wegen möglicher Missverständnisse vorher ganz genau formuliert werden.

Schritt 1 wählt die Verträge aus, die „im Moment“ zu prüfen und ggf. zu bearbeiten sind: alle eigenen Kunden, deren Prämiensatz noch nie geprüft wurde (dann wird das Abschlussdatum, also das Datum der ersten Rechnung, zur Berechnung verwendet) oder deren letzte Prämienänderung vor dem Datum der Aktualisierung liegt. Durch die FOR-SELECT-Schleife wird jeder dieser Verträge innerhalb von DO BEGIN...END einzeln bearbeitet; die dazu benötigten Werte werden in den Variablen zwischengespeichert.
Schritt 2 berechnet das Schlussdatum der letzten Prämienrechnung. Wir arbeiten hier nur mit Jahresrechnungen; da bei BETWEEN beide Grenzwerte einbezogen werden, müssen wir das Schlussdatum um einen Tag verringern.
Schritt 3 erledigt die letzte Vorprüfung:
- Wenn das Datum der Aktualisierung vor dem Schlussdatum liegt, soll noch keine neue Rechnung erfolgen.
- Aber wenn das Datum der Aktualisierung das Schlussdatum der letzten Prämienrechnung überschreitet, ist eine neue Rechnung und damit eine Überprüfung des Prämiensatzes fällig.
Schritt 4 berechnet die Summe aller Schadensfälle:
- Berücksichtigt wird die Summe der Schadenshöhe je Vorfall, also aus der Tabelle Schadensfall.
- Dieser Wert wird bei der Summierung anteilig nach dem Schuldanteil aus der Tabelle Zuordnung_SF_FZ berücksichtigt.
- Das Ergebnis bei current_value lautet NULL, wenn das Fahrzeug nicht in einen Schadensfall verwickelt wurde, es lautet 0 bei fehlender Teilschuld und größer als 0 bei Mit- oder Vollschuld.
Schritt 5 berechnet den Prämiensatz neu:
- Ohne Schadensfall wird er um 10 Punkte verringert, wenn er bisher kleiner/gleich 100 beträgt, und um 20 Punkte, wenn er bisher größer ist.
- Das Minimum von 30 kann nicht unterschritten werden.
- Mit Schadensfall ohne Teilschuld wird der Prämiensatz um 10 Punkte verringert.
- Bei einem sehr geringen Schaden bleibt der Prämiensatz unverändert.
- Bei einem kleineren Schaden wird er um 10 Punkte, sonst um 30 Punkte erhöht.
Gleichzeitig wird das Datum der Prämienänderung um 1 Jahr weitergesetzt.

Mit dieser Prozedur können die neuen Prämiensätze berechnet und für die nächste Prämienrechnung vorbereitet werden:

Aufgabe

Berechne die Prämiensätze für alle (eigenen) Verträge, bei denen im 3. Quartal 2009 eine neue Prämienrechnung ansteht.

EXECUTE PROCEDURE Update_Praemiensatz('30.09.2009');

Ausgabe

------ Procedure executing results: ------
Erledigt = 7

Testdaten in einer Tabelle erstellen

Das übliche Vorgehen, wenn in einer Tabelle viele Testdaten gespeichert werden sollen, werden wir im Kapitel Testdaten erzeugen verwenden:

In Zusatztabellen werden geeignete Feldinhalte gesammelt: eine Tabelle mit möglichen Vornamen, eine mit Nachnamen usw.
Wie im Kapitel Einfache Tabellenverknüpfung beschrieben, werden als „kartesisches Produkt“ alle Zeilen und Spalten aller Zusatztabellen miteinander kombiniert.
Oder die Zeilen und Spalten der Zusatztabellen werden per Zufallsfunktion miteinander verknüpft.

Bei wenigen Spalten der Zieltabelle kann auf Zusatztabellen verzichtet werden; dieses Verfahren nutzen wir im folgenden Beispiel. (Das Verfahren ist theoretisch auch für komplexere Tabellen möglich, wird dann aber schnell unübersichtlich; schon die folgenden CASE-Konstruktionen deuten solche Probleme an.)

Aufgabe

Erzeuge eine bestimmte Anzahl von Datensätzen in der Tabelle Fahrzeug; die Anzahl der neuen Einträge soll als Parameter vorgegeben werden.

Firebird-Version

CREATE OR ALTER PROCEDURE Insert_Into_Fahrzeug 
        ( Anzahl INTEGER = 0)
RETURNS ( Maxid  INTEGER)
AS
  DECLARE VARIABLE Temp INTEGER = 0;
  DECLARE VARIABLE Listekz  CHAR( 66) = 'E  E  E  DU DU BOTRE RE OB OB GE GE HERHAMBO BO DO DO UN UN EN EN ';
  DECLARE VARIABLE Listekza CHAR( 26) = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ';
  DECLARE VARIABLE Listefrb CHAR(100)
    = 'elfenbein schwarz   gelb      orange    ocker     blau      silbern   grau      braun     weiss     ';
  DECLARE VARIABLE Kz    VARCHAR(  3);
  DECLARE VARIABLE Name  VARCHAR( 30);
  DECLARE VARIABLE Rand1 INTEGER = 0;
  DECLARE VARIABLE Rand2 INTEGER = 0;
  DECLARE VARIABLE Rand3 INTEGER = 0;
  DECLARE VARIABLE Rand4 INTEGER = 0;
  DECLARE VARIABLE Rand5 INTEGER = 0;
BEGIN
  Maxid = 0;
  WHILE (Temp < Anzahl) DO
  BEGIN
    /* neue Zufallszahlen */
    Rand1 = FLOOR(1 + (RAND() * 21));    /* für Kfz-Kz. eine Zufallszahl 1 bis 22 */
    Rand2 = FLOOR(0 + (RAND() * 26));    /* ebenso      eine Zufallszahl 0 bis 26 */
    Rand3 = FLOOR(1 + (RAND() * 25));    /* ebenso      eine Zufallszahl 1 bis 26 */
    Rand4 = FLOOR(1 + (RAND() * 9));     /* für Farbe   eine Zufallszahl 1 bis 10 */
    Rand5 = FLOOR(1 + (RAND() * 22));    /* für Fz.Typ  eine Zufallszahl 1 bis 23 */
    /* hole per Zufall Rand1 eines der Kürzel aus Listekz */
    Kz = TRIM(SUBSTRING(:Listekz from (:Rand1*3 - 2) for 3));
    /* mache daraus ein vollständiges Kfz-Kennzeichen */
    Name = Kz

Dies definiert die Prozedur mit folgenden Bestandteilen:

Mit Anzahl als einzigem Eingabe-Parameter wird festgelegt, wie viele Datensätze erzeugt werden sollen. Dazu gehören der Name und Typ der Variablen sowie ein Vorgabewert.
Mit Maxid als einzigem Ausgabe-Parameter wird angegeben, dass ein INTEGER-Wert mit diesem Namen erwartet wird. Der Anfangswert wird im ersten Befehl des „Rumpfes“ der Prozedur festgelegt.
Zwischen AS und BEGIN stehen die verwendeten Variablen mit ihren Anfangswerten.
- Listekz ist eine Liste von 22 möglichen Kfz-Kennzeichen, Listekza enthält das Alphabet für die Zufallssuche nach dem zweiten Teil des Kennzeichens und Listefrb eine Liste von 10 Farben (zu je 10 Zeichen). Diese Listen ersetzen die temporären Tabellen.
- Temp wird als Zähler für die neuen Datensätze verwendet, Kz und Name als Zwischenspeicher bei der Bildung des neuen Kennzeichens.
- Rand1 usw. sind Variablen für Zufallszahlen. Darauf könnte auch verzichtet werden, weil die Formeln für die Zufallszahlen auch direkt in der String-Verknüpfung und im VALUES-Teil verwendet werden könnten; aber die Trennung macht es übersichtlicher.

Zwischen BEGIN und END steht der eigentliche Arbeitsablauf mit Anweisungen gemäß SQL-Programmierung. Innerhalb der WHILE-Schleife zwischen DO BEGIN und END wird jeweils ein neuer Datensatz erzeugt und gespeichert:

Zuerst werden neue Zufallszahlen geholt.
Durch Rand1 wird eine der Zahlen 1, 4, 7 usw. berechnet und gemäß diesem Wert eines der Kennzeichen aus Listekz geholt.
Anschließend wird daraus ein vollständiges Kfz-Kennzeichen:
- Wenn Rand2 ungleich 0 ist, folgt ein Buchstabe nach der Zufallszahl Rand2 aus dem Alphabet, sonst nichts.
- Danach folgt ein weiterer Buchstabe nach der Zufallszahl Rand3 aus dem Alphabet.
- Danach folgt eine (Zufalls-) Zahl: bei einstelligem Kennzeichen eine vierstellige Zahl, sonst eine dreistellige.
Schließlich wird ein neuer Datensatz eingetragen mit dem eben erzeugten Kennzeichen sowie:
- einer Farbe, die mit der Zufallszahl Rand4 aus Listefrb geholt wird
- einer Fahrzeugtyp-ID, die nach der Zufallszahl Rand5 einem der vorhandenen Werte in der Tabelle Fahrzeugtyp entspricht

Zusätzlich wird die Variable Temp weitergezählt; nach dem Ende der Arbeit wird der größte Wert von ID bestimmt, in die Variable Maxid eingetragen und per SUSPEND als Rückgabewert übernommen.

Ausgeführt wird diese Prozedur durch einen einfachen Befehl.

Aufgabe

Erzeuge 10 neue Datensätze in der Tabelle Fahrzeug.

EXECUTE PROCEDURE Insert_into_Fahrzeug (10);

Ausgabe

------ Procedure executing results: ------
MAXID = 145

Zusammenfassung

In diesem Kapitel lernten wir Prozeduren für Arbeitsabläufe kennen, die „nur“ innerhalb der Datenbank ausgeführt werden:

Eine Prozedur kann mit oder ohne Argumenten und mit oder ohne Rückgabewerte ausgeführt werden.
Sie dient zur automatischen Erledigung von Aufgaben, die „von außen“ angestoßen werden, aber keine zusätzlichen Maßnahmen des Anwenders oder der Anwendung benötigen.
Bei einem solchen Arbeitsablauf werden viele Bestandteile einer Programmiersprache benutzt.

Übungen

Unter „Skizzieren“ (Übung 3, 5, 6) ist wie im vorigen Kapitel gemeint: Eingabe- und Ausgabeparameter sowie Variablen mit sinnvollen Namen und Datentypen benennen, Arbeitsablauf möglichst genau mit Pseudo-Code oder normaler Sprache beschreiben.

Tipp: Die Parameter ergeben sich in der Regel aus der Aufgabenstellung. Aus der Überlegung zum Arbeitsablauf folgen die Variablen.

Übung 1

Prozeduren verwenden

Zur Lösung

In welchen der folgenden Situationen ist eine Prozedur sinnvoll, in welchen nicht? Gehen Sie davon aus, dass alle Informationen in der Datenbank gespeichert sind.

Erstelle neue Rechnungen nach den aktuellen Prämiensätzen.
Berechne die Weihnachtsgratifikationen der Mitarbeiter nach den erfolgten Abschlüssen.
Ein Versicherungsvertrag wird gekündigt.

Übung 2

Definition einer Prozedur kontrollieren

Zur Lösung

Nennen Sie in der folgenden Definition Punkte, die unabhängig vom SQL-Dialekt falsch sind. (Je nach DBMS sind noch andere Punkte falsch, danach wird aber nicht gefragt.) Die Prozedur soll folgende Aufgabe erledigen:

Mit einem Aufruf sollen bis zu 5 Abteilungen neu gespeichert werden. Die Angaben sollen wie folgt in jeweils einem String zusammengefasst werden:

'AbCd-Name der Abteilung-Ort der Abteilung'
zuerst 4 Zeichen für das Kürzel, Bindestrich, der Name der Abteilung, Bindestrich, der Ort der Abteilung

In einer Schleife werden die 5 Zeichenketten verarbeitet, nämlich aufgeteilt und als Neuaufnahme in der Tabelle Abteilung gespeichert. Als Rückgabewert soll die letzte neu vergebene ID dienen.

create Insert_Abteilungen as PROCEDURE
   /* Eingabe: mehrere neue Abteilungen einzutragen in der Schreibweise */
      'AbCd-Name der Abteilung-Ort der Abteilung' */
   INPUTS Inhalt1, Inhalt2, Inhalt3, Inhalt4, Inhalt5 VARCHAR(70)
  OUTPUTS lastid INTEGER
variables x1, pos INTEGER,
          temp VARCHAR(70),
          krz CHAR(4),
          name, ort VARCHAR(30)
as BEGIN
  x1 = 0;
  WHILE (x1 < 5) 
  DO BEGIN
    x1 = x1 + 1;
    temp = CASE x1
                WHEN 1 THEN Inhalt1
                WHEN 2 THEN Inhalt2
                WHEN 3 THEN Inhalt3
                WHEN 4 THEN Inhalt4
                WHEN 5 THEN Inhalt5
           END
    /* vorzeitiger Abbruch, falls NULL übergeben wird */
    IF (temp IS NULL)
    THEN break;
    /* bestimme durch '-', wo der Name aufhört und der Ort anfängt */
    pos  = POSITION( '-', temp, 6 );
    krz  = SUBSTRING( temp from 1 for 4 );
    name = SUBSTRING( temp from 5 for (pos-5) );
    ort  = SUBSTRING( temp from (pos+1) );
    /* neuen Datensatz speichern */
    insert into Abteilung
              ( Kuerzel, Bezeichnung, Ort )
       values ( krz, name, ort );
    lastid = SELECT ID from Abteilung WHERE Kuerzel = krz;
  END
END

Übung 3

Prozedur Insert_Fahrzeugtyp erstellen

Zur Lösung

Skizzieren Sie eine Prozedur Insert_Fahrzeugtyp zum Speichern von Fahrzeugtyp und Hersteller in einem Schritt: Es ist zu prüfen, ob der Hersteller schon gespeichert ist; wenn ja, ist diese ID zu verwenden, andernfalls ist ein neuer Eintrag zu erstellen und dessen ID zu übernehmen. Mit dieser Hersteller-ID ist der Fahrzeugtyp zu registrieren.

Übung 4

Prozedur Insert_Fahrzeugtyp erstellen

Zur Lösung

Erstellen Sie die Prozedur Insert_Fahrzeugtyp aus der vorigen Übung.

Übung 5

Prozedur Insert_Schadensfall erstellen

Zur Lösung

Skizzieren Sie eine Prozedur Insert_Schadensfall zum Speichern eines Schadensfalls; dabei soll nur das Fahrzeug des Versicherungsnehmers beteiligt sein.

Übung 6

Prozedur Update_Schadensfall erstellen

Zur Lösung

Skizzieren Sie eine Prozedur Update_Schadensfall zum Ändern eines Schadensfalls; dabei soll jeweils ein weiteres beteiligtes Fahrzeug registriert werden. (Hinweise: Sie müssen auch berücksichtigen, wie sich die Schadenshöhe neu verteilt. Sie können davon ausgehen, dass der Versicherungsnehmer schon gespeichert ist – egal, ob es sich um einen eigenen Kunden handelt oder nicht.) Beschreiben Sie zusätzlich, welche Punkte beim Arbeitsablauf und damit bei den Eingabe-Parametern noch geklärt werden müssen.

Lösungen

Lösung zu Übung 1

Prozeduren verwenden

Zur Übung

sinnvoll, weil es nach den gespeicherten Informationen automatisch erledigt werden kann
nicht sinnvoll, weil zwar die Angaben automatisch zusammengestellt werden können, aber die Gratifikation eine individuelle Entscheidung der Geschäftsleitung ist
sinnvoll, weil mehrere zusammenhängende Maßnahmen auszuführen sind

Lösung zu Übung 2

Definition einer Prozedur kontrollieren

Zur Übung

Zeile 1: Der Befehl lautet: CREATE PROCEDURE <name>.
Zeile 2/3: Der Kommentar ist falsch abgeschlossen.
Zeile 4 ff.: Die gemeinsame Deklaration von Parametern oder Variablen ist nicht zulässig.
Zeile 4: Die Eingabe-Parameter müssen in Klammern stehen.
Zeile 5: Die Ausgabe-Parameter werden bei keinem DBMS durch eine OUTPUTS-Klausel angegeben.
Zeile 6 ff.: Die Variablen folgen erst hinter AS und werden anders deklariert.
Zeile 21: Es fehlt das Semikolon am Ende der Zuweisung.
Zeile 28: Die Längenangabe im SUBSTRING ist nicht korrekt.
Zeile 31 ff.: Die Eindeutigkeit der Namen von Variablen und Spalten wird teilweise nicht beachtet.

Lösung zu Übung 3

Prozedur Insert_Fahrzeugtyp erstellen

Zur Übung

Eingabe-Parameter: TypBezeichnung Varchar(30), HerstellerName Varchar(30), HerstellerLand Varchar(30)
Ausgabe-Parameter: TypID Integer, HerstellerID Integer
Variable: werden nicht benötigt
Arbeitsablauf:
1. Suche in der Tabelle Fahrzeughersteller den gegebenen HerstellerName und registriere die gefundene ID im Parameter HerstellerID.
2. Wenn dieser Parameter jetzt NULL ist, fehlt der Eintrag noch. Also ist er neu aufzunehmen unter Verwendung der Angaben aus HerstellerName und HerstellerLand; das liefert den Wert von HerstellerID.
3. Damit kann der neue Datensatz in der Tabelle Fahrzeugtyp registriert werden; die neue ID dieses Datensatzes wird als Wert TypID zurückgegeben.

Lösung zu Übung 4

Prozedur Insert_Fahrzeugtyp erstellen

Zur Übung

Diese Variante der Lösung benutzt die Firebird-Syntax, vor allem hinsichtlich der Trennung von Eingabe- und Ausgabeparametern.

CREATE OR ALTER PROCEDURE Insert_Fahrzeugtyp
   ( TypBezeichnung VARCHAR(30),
     HerstellerName VARCHAR(30),
     HerstellerLand VARCHAR(30)
   )
   RETURNS( TypID INTEGER, HerstellerID INTEGER )
AS
BEGIN
  /* ist der Hersteller schon registriert? */
  select ID from Fahrzeughersteller
   where Name = :HerstellerName
    into :HerstellerID;

  /* nein, dann als Hersteller neu aufnehmen */
  IF (HerstellerID is null) THEN
  BEGIN
    HerstellerID = NEXT VALUE FOR Fahrzeughersteller_ID;
    insert into Fahrzeughersteller
         values ( :HerstellerID, :HerstellerName, :HerstellerLand );
  END

  /* anschließend den Typ registrieren */
  TypID = NEXT VALUE FOR Fahrzeugtyp_ID;
  INSERT INTO Fahrzeugtyp
       VALUES ( :TypID, :TypBezeichnung, :HerstellerID );
END

Lösung zu Übung 5

Prozedur Insert_Schadensfall erstellen

Zur Übung

Eingabe-Parameter: die Angaben zum Schadensfall (Datum Date, Ort varchar(200), Beschreibung varchar(1000), Schadenshoehe number, Verletzte char(1), Mitarbeiter_ID Integer), Fahrzeugbeteiligung (ID Integer oder Kennzeichen Varchar(10), Schuldanteil Integer)
Ausgabe-Parameter: neue ID des Schadensfalls
Arbeitsablauf:
1. Insert into Schadensfall: Registriere den Schadensfall, notiere die neue ID
2. Select from Fahrzeug: Suche ggf. zum Kennzeichen die Fahrzeug-ID
3. Insert into Zuordnung_SF_FZ: Registriere die Zuordnung zwischen Schadensfall und Fahrzeug
Variable werden voraussichtlich nicht benötigt.

Lösung zu Übung 6

Prozedur Change_Schadensfall erstellen

Zur Übung

Eingabe-Parameter: Schadensfall-ID Integer, Fahrzeugbeteiligung (Kennzeichen Varchar(10), anteilige Schadenshöhe Number)
Ausgabe-Parameter: eigentlich nicht erforderlich, aber als „Erfolgsmeldung“ die ID der neuen Zuordnung
Arbeitsablauf:
1. Select from Fahrzeug: Suche zum Kennzeichen die Fahrzeug-ID.
2. Insert into Zuordnung_SF_FZ: Registriere die Zuordnung zwischen Schadensfall und dem neuen Fahrzeug.
3. Übernimm die ID dieser neuen Zuordnung als Ausgabe-Parameter.
4. Update Zuordnung_SF_FZ: Ändere im ersten Eintrag zu diesem Schadensfall die anteilige Schadenshöhe unter Berücksichtigung des neu registrierten beteiligten Fahrzeugs. (Der benötigte „erste Eintrag“ kann durch eine passende WHERE-Klausel direkt geändert werden; ersatzweise kann er auch durch einen eigenen SELECT-Befehl bestimmt werden – dann wird für die ID eine Variable benötigt.)
Variable werden voraussichtlich nicht benötigt.
Unklarheiten: Bei diesem Arbeitsablauf wird davon ausgegangen, dass zu jedem später registrierten Fahrzeug ein Teil des ursprünglichen Schadens gehört; es wird nicht berücksichtigt, dass sich die Schadensverteilung insgesamt ändern kann. Völlig offen ist, wie sich der Verschuldensanteil beim ersten Eintrag und bei jedem weiteren Eintrag ändern kann. In beiden Punkten ist lediglich klar, dass alle Einzelwerte zu summieren sind und den Maximalwert (100 beim Schuldanteil bzw. den Gesamtschaden) nicht überschreiten dürfen.

Siehe auch

Teile dieses Kapitels beziehen sich auf Erläuterungen in den folgenden Kapiteln:

SQL-Programmierung
Views: Eine View mit variabler Selektion
Testdaten erzeugen
Einfache Tabellenverknüpfung – alle Kombinationen aller Datensätze
Funktionen (2) – u. a. RAND für Zufallszahlen
Tipps und Tricks

Bei Wikipedia gibt es grundlegende Hinweise:

Fat Client als ein Prinzip der Datenverarbeitung

Inhaltsverzeichnis: Erweiterungen

Zurück zu: Eigene Funktionen

Weiter zu: Trigger