Buchgenerator (deaktivieren)

Einführung in SQL: Prozeduren

Aus Wikibooks

Wechseln zu: Navigation, Suche


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

Attention green.svg

 Hinweis an Autoren betr. SQL-Dialekte
Die Beispiele sollten auch für MS-SQL, MySql, Oracle verfasst werden. Je nach Bedarf und Interesse kann eines der vorhandenen Beispiele umgeschrieben oder wie unter Programmierung mit Vorlage:Navigationsleiste zum Ausklappen hinzugefügt werden. Ich Juetho habe auch nichts dagegen, wenn jemand mir einen Code-Vorschlag auf meine Diskussionsseite schreibt oder per Email schickt mit der Bitte, dass ich es einbauen möge. – Nach Erledigung durch einen Fachmann kann der Hinweis auf einen einzelnen Dialekt hier entfernt werden; nach Erledigung für alle DBMS kann der Hinweis insgesamt gelöscht werden.


Inhaltsverzeichnis

[Bearbeiten] Ein Überblick

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

  • Beim Fat Server wird so viel Funktionalität wie möglich in die 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 schließlich überflüssig, die Daten zuerst von der Datenbank zum Arbeitsplatz zu kopieren, dann automatisch zu verarbeiten und das Arbeitsergebnis (ohne manuelle Überprüfung) schließlich 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.


[Bearbeiten] Prozedur definieren

[Bearbeiten] Prozedur erstellen

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

CREATE OR ALTER PROCEDURE <routine-name>
      ( [ <input-parameterliste> ] )
      [ RETURNS ( <output-parameterliste> ) ]
AS
      [ <variablenliste> ]
BEGIN
      <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 Eingabe-Parametern, sofern Werte übergeben werden sollen
  • eine Liste von Ausgabe-Parametern, sofern Werte abgefragt werden sollen
  • eine Liste von Variablen, die innerhalb der Prozedur verwendet werden
  • die Befehle, die innerhalb der Prozedur ausgeführt werden sollen

Bei den Befehlen innerhalb der Prozedur handelt es sich zum Teil um "normale" SQL-Befehle und vor allem 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 unter Anweisungen begrenzen.


[Bearbeiten] 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 ü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.

Eine Prozedur kann 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 überhaupt nicht hierher. 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 unter Programmierung: Routinen ohne feste Speicherung beschrieben.


[Bearbeiten] Prozedur ändern

Eine Änderung einer Prozedur ist nur möglich durch eine vollständige Neudefinition mit allen Bestandteilen. Deshalb wird das 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.

[Bearbeiten] Prozedur löschen

Das geht wie üblich mit dem DROP-Befehl:

DROP PROCEDURE <routine-name>;


[Bearbeiten] Beispiele

Attention green.svg

 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.

[Bearbeiten] Ersatz für eine View mit Parametern

Unter Views: Eine View mit variabler Selektion konnten variable Bedingungen nicht in eine View eingebunden werden; stattdessen wurde auf die Möglichkeit einer Prozedur hingewiesen.

Aufgabe

Mache aus der View Mitarbeiter_in_Abteilung eine Prozedur Mitarbeiter_aus_Abteilung, die die Abteilungsnummer als Parameter entgegennimmt.

Firebird-Version
Crystal Clear app terminal.png SQL-Quelltext:

  1. CREATE OR ALTER PROCEDURE Mitarbeiter_aus_Abteilung 

  2.         ( Abt INTEGER)

  3. RETURNS ( Personalnummer VARCHAR(10),

  4.           Name           VARCHAR(30),

  5.           Vorname        VARCHAR(30),

  6.           Geburtsdatum   DATE )

  7. AS

  8. BEGIN

  9.   FOR SELECT Personalnummer, Name, Vorname, Geburtsdatum

  10.         FROM Mitarbeiter

  11.        WHERE Abteilung_ID = :Abt

  12.     ORDER BY Ist_Leiter, Name, Vorname

  13.         INTO :Personalnummer, :Name, :Vorname, :Geburtsdatum

  14.   DO SUSPEND;

  15. 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 ab Zeile 8 steht der eigentliche Arbeitsablauf mit Anweisungen gemäß Programmieren mit SQL.

  • 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 als View oder als Prozedur aufzurufen hat:

Aufgabe

Hole alle Mitarbeiter einer bestimmten Abteilung.

Aufruf als View
Crystal Clear app terminal.png SQL-Quelltext:

SELECT * FROM Mitarbeiter_in_Abteilung
 WHERE Abt = 5;

Aufruf als Prozedur
Crystal Clear app terminal.png SQL-Quelltext:

SELECT * FROM Mitarbeiter_aus_Abteilung (5);


[Bearbeiten] Testdaten in einer Tabelle erzeugen

Das übliche Vorgehen, wenn in einer Tabelle viele Testdaten gespeichert werden sollen, geht so:

  • In Zusatztabellen werden geeignete Feldinhalte gesammelt: eine Tabelle mit möglichen Vornamen, eine mit Nachnamen usw.
  • Wie unter Einfache Tabellenverknüpfung beschrieben werden 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 in diesem Beispiel. (Das Verfahren ist theoretisch auch für andere Tabellen möglich, wird dann aber schnell unübersichtlich.)

Aufgabe

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

Firebird-Version
Crystal Clear app terminal.png SQL-Quelltext:

  1. CREATE OR ALTER PROCEDURE Insert_Into_Fahrzeug 

  2.         ( Anzahl INTEGER = 0)

  3. RETURNS ( Maxid  INTEGER)

  4. AS

  5.   DECLARE VARIABLE Temp INTEGER = 0;

  6.   DECLARE VARIABLE Listekz  CHAR(120) = 'K  K  K  E  E  E  MH MH DU DU WESBOTRE RE OB OB GE GE HERHAMBO BO  DO DO UN UN D  D  D  ME ME EN EN W  W  W  HA HA KR NE ';

  7.   DECLARE VARIABLE Listekza CHAR( 26) = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ';

  8.   DECLARE VARIABLE Listefrb CHAR(100) = 'elfenbein schwarz   gelb      orange    ocker     blau      silbern   grau      braun     weiss     ';

  9.   DECLARE VARIABLE Kz    VARCHAR( 3);

  10.   DECLARE VARIABLE Name  VARCHAR(30);

  11.   DECLARE VARIABLE Rand1 INTEGER = 0;

  12.   DECLARE VARIABLE Rand2 INTEGER = 0;

  13.   DECLARE VARIABLE Rand3 INTEGER = 0;

  14.   DECLARE VARIABLE Rand4 INTEGER = 0;

  15.   DECLARE VARIABLE Rand5 INTEGER = 0;

  16. BEGIN

  17.   Maxid = 0;

  18.   WHILE (Temp < Anzahl) DO

  19.   BEGIN

  20.     Rand1 = FLOOR(1 + (RAND() * 39));    /* eine Zufallszahl zwischen 1 und 40 (je einschl.) */

  21.     Rand2 = FLOOR(0 + (RAND() * 26));    /* eine Zufallszahl zwischen 0 und 26 */

  22.     Rand3 = FLOOR(1 + (RAND() * 25));    /* eine Zufallszahl zwischen 1 und 26 */

  23.     Rand4 = FLOOR(1 + (RAND() * 9));     /* eine Zufallszahl zwischen 1 und 10 */

  24.     Rand5 = FLOOR(1 + (RAND() * 22));    /* eine Zufallszahl zwischen 1 und 23 */

  25.     Kz = TRIM(SUBSTRING(:Listekz FROM (:Rand1*3 - 2) FOR 3));

  26.     Name = Kz || '-' || CASE :Rand2

  27.                               WHEN 0 THEN ''

  28.                               ELSE SUBSTRING(:Listekza FROM :Rand2 FOR 1)

  29.                          END

  30.                       || SUBSTRING(:Listekza FROM :Rand3 FOR 1)

  31.                       || ' ' || CAST( (CASE CHAR_LENGTH(Kz)

  32.                                        WHEN 1 THEN FLOOR(1 + (RAND() * 9998))

  33.                                        ELSE        FLOOR(1 + (RAND() *  998))

  34.                                        END) 

  35.                                   AS INT);

  36.     INSERT INTO Fahrzeug (Kennzeichen, Farbe, Fahrzeugtyp_ID)

  37.          VALUES ( :Name,

  38.                   TRIM(SUBSTRING(:Listefrb FROM (:Rand4*10-9) FOR 10)),

  39.                   :Rand5 );

  40.     Temp = Temp + 1;

  41.   END

  42.   SELECT MAX(ID) FROM fahrzeug INTO :Maxid;

  43.   SUSPEND;

  44. END


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 "Körpers" der Prozedur festgelegt.
  • Zwischen AS und BEGIN stehen die verwendeten Variablen mit ihren Anfangswerten.
    • Listekz ist eine Liste von 40 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 Zwischenspeicherung bei der Bildung des neuen Kennzeichens.
    • Rand1 usw. sind Variablen für Zufallszahlen. Darauf könnte auch verzichtet werden, weil die Formeln gemäß Funktionen (2) auch direkt in der String-Verknüpfung und dem VALUES-Teil verwendet werden könnten; aber die Trennung macht es übersichtlicher.

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

  • Zeile 20 bis 24 liefern neue Zufallszahlen.
  • In Zeile 25 wird durch Rand1 eine der Zahlen 1, 4, 7 usw. berechnet und gemäß diesem Wert eines der Kennzeichen aus Listekz geholt.
  • In Zeile 26 ff. 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.
  • In Zeile 36 ff. 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.

Crystal Clear app terminal.png SQL-Quelltext:

EXECUTE PROCEDURE Insert_into_Fahrzeug (10);

Crystal Clear app kscreensaver.png SQL-Ausgabe:  

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


[Bearbeiten] 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

Man benötigt also drei INSERT-Befehle; bei den ersten beiden ist durch SELECT die eingetragene ID abzufragen und beim dritten INSERT zu verwenden. Warum sollte man sich die Arbeit in dieser Weise erschweren? Soll doch die Datenbank einen INSERT-Befehl entgegennehmen und die Parameter selbständig auf die beteiligten Tabellen verteilen.

Aufgabe

Speichere einen neuen Versicherungsvertrag mit allen Einzelheiten.

Firebird-Version
Crystal Clear app terminal.png SQL-Quelltext:

  1. CREATE OR ALTER procedure Insert_Versicherungsvertrag

  2.    ( /* Bestandteile des Vertrags */

  3.      Vertragsnummer VARCHAR(20),

  4.      Abschlussdatum DATE,

  5.      Art            CHAR(2),

  6.      Praemiensatz   INTEGER,

  7.      Basispraemie   DECIMAL(9,0),

  8.      Mitarbeiter_ID INTEGER,

  9.      /* Angaben zum Versicherungsnehmer */

  10.      Name                          VARCHAR(30),

  11.      Vorname                       VARCHAR(30),

  12.      Geschlecht                    CHAR(1),

  13.      Geburtsdatum                  DATE,

  14.      Fuehrerschein                 DATE,

  15.      Ort                           VARCHAR(30),

  16.      PLZ                           CHAR(5),

  17.      Strasse                       VARCHAR(30),

  18.      Hausnummer                    VARCHAR(10),

  19.      Eigener_Kunde                 CHAR(1),

  20.      Versicherungsgesellschaft_ID  INTEGER,

  21.      /* Angaben zum Fahrzeug */

  22.      Kennzeichen    VARCHAR(10),

  23.      Farbe          VARCHAR(30),

  24.      Fahrzeugtyp_ID INTEGER

  25.    )

  26.    RETURNS( NewID INTEGER )

  27. AS

  28.    DECLARE VARIABLE NewFahrzeugID   integer;

  29.    DECLARE VARIABLE NewVersnehmerID integer;

  30. BEGIN

  31.   NewFahrzeugID = NEXT VALUE FOR Fahrzeug_ID;

  32.   INSERT INTO Fahrzeug

  33.        VALUES ( :NewFahrzeugID, :Kennzeichen, :Farbe, :Fahrzeugtyp_ID );

  34.   NewVersnehmerID = NEXT VALUE FOR Versicherungsnehmer_ID;

  35.   INSERT INTO Versicherungsnehmer

  36.        VALUES ( :NewVersnehmerID, :Name, :Vorname, :Geburtsdatum, :Fuehrerschein,

  37.                 :Ort, :PLZ, :Strasse, :Hausnummer,

  38.                 :Eigener_Kunde, :Versicherungsgesellschaft_ID, :Geschlecht );

  39.   NewID = NEXT VALUE FOR Versicherungsvertrag_ID;

  40.   INSERT INTO Versicherungsvertrag

  41.        VALUES ( :NewID, :Vertragsnummer, :Abschlussdatum, :Art, :Mitarbeiter_ID,

  42.                 :NewFahrzeugID, :NewVersnehmerID, :Praemiensatz, :Abschlussdatum, :Basispraemie );

  43.   SUSPEND;

  44. END


Als Eingabe-Parameter werden alle Werte benötigt, die der Mitarbeiter für die einzelnen Tabellen eingeben muss:

  • in den Zeilen 3 bis 8 die Angaben für Versicherungsvertrag
  • in den Zeilen 10 bis 20 die Angaben für Versicherungsnehmer
  • in den Zeilen 22 bis 24 die Angaben für Fahrzeug

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 IDs der beiden anderen Tabellen.

Damit wird ein neuer Vertrag mit einem einzigen Befehl erstellt:

Aufgabe

Speichere einen neuen Vertrag mit allen Angaben.

Firebird-Version
Crystal Clear app terminal.png SQL-Quelltext:

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 );

Crystal Clear app kscreensaver.png SQL-Ausgabe:  

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


[Bearbeiten] 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
Crystal Clear app terminal.png SQL-Quelltext:

  1. CREATE OR ALTER PROCEDURE Update_Praemiensatz

  2.                 ( Aktualisierung DATE DEFAULT CURRENT_DATE )

  3.         RETURNS ( Erledigt INTEGER )

  4. AS

  5.   DECLARE VARIABLE current_id INTEGER;

  6.   DECLARE VARIABLE current_fz INTEGER;

  7.   DECLARE VARIABLE current_aenderung DATE;

  8.   DECLARE VARIABLE vergleich_aenderung DATE;

  9.   DECLARE VARIABLE current_satz INTEGER;

  10.   DECLARE VARIABLE current_value DECIMAL(16,2);

  11. BEGIN

  12.   Erledigt = 0;

  13.   SUSPEND;

  14.  

  15.   /* Vorarbeit: Anfänger werden auf Praemiensatz 200 gesetzt; das kann aber

  16.      nur für noch nicht behandelte Verträge gelten und muss deshalb noch

  17.      vor der nächsten IS NULL-Prüfung erledigt werden. */

  18.   UPDATE Versicherungsvertrag vv

  19.      SET Praemiensatz = 200

  20.    WHERE Praemienaenderung IS NULL

  21.      AND Versicherungsnehmer_ID 

  22.          /* bestimme die Liste der Anfänger, aber nur für eigene Kunden */

  23.          IN ( SELECT ID 

  24.                 FROM Versicherungsnehmer vn

  25.                      /* wenn der Führerschein beim Vertragsabschluss weniger als 2 Jahre alt ist */

  26.                WHERE vn.Eigener_kunde = 'J'

  27.                  AND ( ( DATEADD( YEAR,  2, vn.Fuehrerschein ) > vv.Abschlussdatum )

  28.                      /* wenn der VersNehmer beim Führerschein noch nicht 21 Jahre alt ist */

  29.                     OR ( DATEADD( YEAR, 21, vn.Geburtsdatum  ) > vn.Fuehrerschein  ) ) );

  30.  

  31.   /* zu bearbeiten sind alle Verträge für eigene Kunden, deren letzte

  32.      Prämienänderung "zu lange" zurückliegt */

  33.   FOR SELECT vv.ID, Fahrzeug_ID,

  34.              CASE

  35.              WHEN Praemienaenderung IS NULL THEN Abschlussdatum

  36.              ELSE Praemienaenderung

  37.              END, Praemiensatz

  38.         FROM Versicherungsvertrag vv

  39.              JOIN Versicherungsnehmer vn ON vn.Id = vv.Versicherungsnehmer_id

  40.        WHERE vn.Eigener_Kunde = 'J'

  41.          AND ( Praemienaenderung IS NULL OR Praemienaenderung <= :Aktualisierung)

  42.         INTO :current_id, :current_fz, :current_aenderung, :current_satz

  43.   DO BEGIN

  44.     /* wegen der Übersicht das mögliche Schlussdatum vorher bestimmen */

  45.     vergleich_aenderung = DATEADD( YEAR, 1, current_aenderung );

  46.     vergleich_aenderung = DATEADD( DAY, -1, vergleich_aenderung );

  47.  

  48.     /* weitere Bearbeitung, sofern die Aktualisierung über das

  49.        Vergleichsdatum hinausgeht */

  50.     IF (Aktualisierung >= vergleich_aenderung) THEN

  51.     BEGIN

  52.  

  53.        /* suche zu diesem Vertrag, d.h. diesem Fahrzeug alle Schadensfälle in dieser Zeit

  54.           und summiere die Schadenssumme nach Schuldanteil */

  55.        SELECT SUM( sf.Schadenshoehe * zu.Schuldanteil / 100 )

  56.          FROM Zuordnung_SF_FZ zu

  57.               JOIN Schadensfall sf ON zu.Schadensfall_id = sf.Id

  58.         WHERE zu.Fahrzeug_ID = :current_fz

  59.           AND sf.Datum BETWEEN :current_aenderung AND :vergleich_aenderung

  60.          INTO :current_value;

  61.  

  62.       /* abhängig von (anteiliger) Schadenssumme und bisherigem Prämiensatz

  63.          wird der neue Prämiensatz bestimmt und das Datum weitergesetzt */

  64.       UPDATE Versicherungsvertrag

  65.          SET Praemiensatz =

  66.              CASE

  67.              WHEN :current_value IS NULL THEN CASE

  68.                                               WHEN :current_satz >      100

  69.                                                    THEN :current_satz - 20

  70.                                               WHEN :current_satz BETWEEN 40 AND 100

  71.                                                    THEN :current_satz - 10

  72.                                               ELSE 30

  73.                                               END

  74.              WHEN :current_value =     0 THEN :current_satz - 10

  75.              WHEN :current_value <   500 THEN :current_satz

  76.              WHEN :current_value <  1000 THEN :current_satz + 10

  77.              WHEN :current_value >= 1000 THEN :current_satz + 30

  78.              END,

  79.              Praemienaenderung = DATEADD( YEAR, 1, :current_aenderung )

  80.        WHERE ID = :current_id;

  81.        Erledigt = Erledigt + 1;

  82.     END

  83.   END

  84.  

  85.   SUSPEND;

  86. 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. Dieses Verfahren stellt sicher, dass immer nur "neue" Schadensfälle und nur die jeweils anstehenden Prämienrechnungen berücksichtigt werden.

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:

  • In Zeile 13 wird der Rückgabewert mit dem Anfangswert belegt und angezeigt zum Zeichen dafür, dass die Prozedur arbeitet.
  • In Zeile 19 bis 30 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.
Dabei handelt es sich um einen sachlichen Fehler, der erst jetzt beim Schreiben und nicht beim Programmieren aufgefallen ist: Danach wäre auch ein neuer Vertrag nach 20 Jahren Fahrpraxis immer noch ein Anfänger-Vertrag. In der Praxis müssen solche Bedingungen vorher genau formuliert werden.
  • In Zeile 34 bis 42 werden die Verträge ausgewählt, 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 in Zeile 44 ff. einzeln bearbeitet; die dazu benötigten Werte werden in Zeile 43 in den Variablen zwischengespeichert.
  • In Zeile 46/47 wird das Schlussdatum der letzten Prämienrechnung berechnet. Wir arbeiten hier nur mit Jahresrechnungen; da bei BETWEEN beide Grenzwerte einbezogen werden, müssen wir das Schlussdatum um einen Tag verringern.
  • In Zeile 51 kommt 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.
  • In Zeile 56 bis 61 wird die Summe aller Schadensfälle berechnet:
    • 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.
  • In Zeile 65 ff. wird der Prämiensatz neu berechnet:
    • 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.

Crystal Clear app terminal.png SQL-Quelltext:

EXECUTE PROCEDURE Update_Praemiensatz('30.09.2009');

Crystal Clear app kscreensaver.png SQL-Ausgabe:  

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


[Bearbeiten] 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.


[Bearbeiten] Übungen


Von „http://de.wikibooks.org/wiki/Einf%C3%BChrung_in_SQL:_Prozeduren“
Persönliche Werkzeuge