Entwurfsarbeit  Datenbankmanagement I und II / Kommunikationsinformatik  

Thema  : Prototypenverwaltung  

2003  Stephan Feltel Matr.-Nr:  7302

Inhaltsverzeichnis   

1.  Erläuterung der Aufgabenstellung.  3

2.  Spezifikation der Aufgabenstellung.  3

3.  Auswahl der Entitäten mit Attributen und Datentypen.  8

4.  Auswahl beispielhafter Beziehungen  9

5.  ER- Modell  9

6.  Nachweis der durchgeführten Normalisierung des ER- Modells  11

7.  SQL- Script zum vollständigen Generieren der Tabellen entsprechend  

normalisiertem  ER- Modell  14

8.  SQL-Script zum Füllen der einzelnen Tabellen mit je 10 Datensätzen.  15

9.  SQL-Scripte für Datenabfrage  17

10.  Script zum Erzeugen eines Paketes mit 8 Funktionen und 5 Prozeduren.  19

11.  Script zum Erzeugen von 2 Datenbanktriggern  24

Seite  2 von  25

Datenbankentwurf  doc  

1. Erläuterung der Aufgabenstellung

Die vorliegende Entwurfsarbeit bildet einen Ausschnitt einer komplexen Teile-Verwaltung ab. Es sollen hier ausgewählte Funktionen und Mechanismen einer Lagerverwaltung gezeigt werden. Um ein Lager und ein voll funktionierendes System aufzubauen genügen meine gezeigten Ansätze jedoch nicht aus. Die komplexen Zusammenhänge zwischen einer Disposition 1 und der daraus generierten Bestellung werden in meiner Arbeit auch nicht berücksichtig. Es besteht jedoch die Möglichkeit eine „manuelle“ Bestellung auszulösen.

Eine detaillierte Spezifikation der zu lösenden Aufgaben meiner Arbeit folgt im nächste Abschnitt.

2. Spezifikation der Aufgabenstellung

Im folgenden Abschnitt werde ich beispielhaft erläutern worauf ich in meiner Arbeit Wert lege, welche Funktionalitäten sie beinhaltet und wieso ich gerade diese gewählt habe.

Für ein Unternehmen ist es heutzutage immer wichtiger Aufträge zu bekommen und diese dann so schnell wie möglich, mit bester Qualität zu bearbeiten. Damit eine reibungslose Produktion gewährleistet werden kann, ist es fast unerlässlich sein Unternehmen in einem sog. ERP- System 2 abzubilden. In meiner Arbeit greife ich daraus folgende Module bzw. Funktionen heraus:

Um die Grundfunktionalitäten darstellen zu können, reichen diese 6 Entitäten 4 aus.

2.1. Welche Daten werden benutzt

Um einen besseren Einblick in die vorliegende Arbeit zu bekommen stelle ich die einzelnen Entitäten hier kurz vor:

2.2.1. Teil:

Unter dieser Entität werden Teile zusammengefasst, welche die gleichen Eigenschaften besitzen. Es wird also nicht jedes Teil explizit angelegt sondern einer bestimmten Teilegruppe zugeordnet. Es wäre z.B. nicht sinnvoll für eine Lagerverwaltung 50 Schaftfräser mit einem Durchmesser von 5mm einzeln anzulegen, sondern eher eine Teilegruppe mit der Anzahl gleicher Teile zu pflegen. Um einen Bezug zu einer gesamten Baugruppe zu schaffen wird zu jedem „Teil“ eine baugruppen_id eingetragen, die ihrerseits nur eine teil_id referenziert und somit

¹ Disposition: Unter Disposition versteht man die Anforderung, Artikel zu beschaffen. Von wesentlicher Bedeutung ist dabei die Ermittlung der Menge der zu beschaffenden Artikel und die Auswahl des Lieferanten.

² ERP-System: Enterprise- Ressource- Planing: Mit solchen Softwaresystemen, sog. Warenwirtschaftsprogramme können

kennzeichnet zu welcher Baugruppe ein Teil gehört. Am Beispiel lässt sich der Zusammenhang besser erkennen. Nehmen wir an ein Teil (Abdeckhaube) hat die teil_id = 10 und eine baugruppen_id = 20. Dann würde das Teil (Fräser) mit teil_id = 30 und baugruppen_id = 10 zum Teil (oder Baugruppe) Abdeckhaube (teil_id = 10) gehören. Wir haben hier also die konkrete Zuordnung von Teilen zu einer bestimmten Baugruppe erreicht. Auf die weiteren Attribute und ihre Funktion gehe ich hier nicht ein, da sie sich erstens selbst erklären und ich zweitens im folgenden noch nähere Angaben dazu mache.

2.2.2. Lieferant

Zur Lieferantentabelle 5 gibt es eigentlich nicht allzu viel zu sagen. Hier wird die Lieferzeit hinterlegt und eine Verknüpfung auf eine Adresse, worauf ich im Weiteren noch genauer eingehen werde.

2.2.3. Kunde

Die Tabelle Kunde ist ähnlich der Lieferantentabelle aufgebaut. Ein Verweis zu einer Adresse und das Lieferziel 6 werden hier eingetragen. Wie auch in der Tabelle Lieferant habe ich mich entschlossen hier nur einige, zu Demonstrationszwecken ausgewählte Attribute zu benutzen. In der Praxis würden hier sicherlich noch mehr Informationen benötigt werden, auf die ich hier aber verzichte. Ich denke um die Funktionalitäten ausreichend zu gestalten reichen die von mir gewählten Attribute aus.

2.2.4. Adresse

Die Tabelle Adresse erfüllt eine Wichtige Rolle in diesem Datenbankentwurf. Ihre Attribute beschreiben wieder ausgewählte Merkmale einer Adresse. Hier werden u.A. Name, Wohnanschrift, Telefonnummer etc. hinterlegt. Um einen Bezug zu bekommen zu Kunden oder Lieferanten werden in den genannten Tabellen Fremdschlüssel eingebaut. Da ein Kunde „Klaus Meier“ auch für andere Produkte Lieferant „Klaus Meier“ sein kann ist das eine gute Möglichkeit diesen Zusammenhang zu schaffen. Des weiteren ist es so auch möglich z.B. eine Lieferadresse abweichend von der hinterlegten Kundenadresse zu referenzieren und eine Bestellung somit an eine Adresse X zu senden.

2.2.5. Bestellung

Eine Bestellung ist unerlässlich um seinen Lagerbestand wieder aufzufüllen. Deshalb habe ich auch in meiner Arbeit wert auf diese Funktionalität gelegt. Eine Bestellung beinhaltet Informationen zum Bestelldatum einer Adresse (in diesem Fall immer die Lieferadresse) und eines möglichen Rabattes einer Bestellung. Was genau aber bestellt wir, werde ich in einer weiteren Tabelle zusammenfassen.

2.2.6. Bestell_Position

Diese Tabelle nennt sich zwar Bestell_Position, könnte aber durchaus auch für Lieferungen, Materialscheine oder Stücklistenpositionen genutzt werden. Sie beinhaltet allgemeine Informationen zu einer Position auf genannten Dokumenten. Im Fall einer Bestellung werden hier also Bestell_Positionen charakterisiert. Damit alle Positionen auch zu einer Bestellung zugeordnet werden können, wird zu jeder

Bestell_Position eine bestellung_id eingetragen. Somit kann man alle Positionen genau zu einer Bestellung zuordnen.

2.2.7. Allgemeine Attribute

In allen Tabellen wird das Anlagedatum des jeweiligen Datensatzes gespeichert. Dieses Attribut werde ich zwar nicht benutzen, es hat aber durchaus eine Berechtigung.

2.2.8. Kurzbeschreibung der Implementierten Funktionen und Prozeduren F1 get_kunde_info:

In dieser Funktion wird der Einzelpreis aus der Tabelle TEIL gesucht, der zu dem Teil gehört, dessen teil_id der Funktion als Eingangsparameter übergeben wurde. Dieser Einzelpreis kann dann einer Variablen zugeordnet werden. Bsp.: v_preis := get_kunde_info(10);

F2 get_kuerzeste_lieferzeit:

Diese Funktion liefert die kürzeste Lieferzeit, aus der Lieferantentabelle. Bsp.: v_lieferzeit := get_kuerzeste_lieferzeit;

F3 get_mwst:

Diese Funktion gibt von einem als Eingangsparameter mitgegebenen Wert den Mehrwertsteueranteil zurück. Bsp.: v_mwst := get_mwst(1000);

F4 get_bestellsumme:

Als Eingangsparameter für diese Funktion dient eine bestellung_id. Daraufhin werden die einzelnen Positionen „durchforstet“ und die Preise der Jeweiligen Teile mit deren Bestellanzahl multipliziert. Diese Positionssummen werden aufaddiert und ergeben somit die Bestellsumme. Mit welchen Maßnahmen dies realisiert ist, kann unter dem Gliederungspunkt 10 F:4 nachgelesen werden. Bsp.: v_summe := get_bestellsumme(10);

F5 get_rabatt:

Diese Funktion benutzt die eben beschrieben get_bestellsumme- Funktion um daraus abhängig von einer bestimmten Höhe des Preises einen Rabatt festzulegen. Eingangsparameter hier ist wiederum eine bestellung_id. Bsp.: v_rabatt := get_rabatt(10);

F6 get_gesamtsumme_brutto:

Um auch die Bruttosumme einfach berechnen zu lassen, habe ich diese Funktion geschrieben. Als Eingangsparameter benötigt „sie“ eine Nettosumme (z.B. aus Funktion get_bestellsumme), einen Mwst-Wert und einen Rabatt (in %). Daraus wird dann wie folgt die Endsumme berechnet. Von der Nettosumme + Mwst wird der %tuale Rabatt abgezogen.

Bsp.: v_summe_brutto := get_gesamtsumme_brutto(1050,168,1);

F7 anzahl_kunden:

Diese Funktion zählt die Anzahl der Datensätze 7 in der Tabelle KUNDE und gibt diese dann zurück. Bsp.: v_anzahl_kunden :=anzahl_kunden;

F8 anzahl_adressen:

Diese Funktion zählt die Anzahl der Datensätze in der Tabelle ADRESSE und gibt diese dann zurück. Bsp.: v_anzahl_adressen :=anzahl_adressen;

P1 adresse_anlegen:

Mit Hilfe dieser Prozedur kann man einen Adressendatensatz in die Tabelle ADRESSE einfügen. Die Prozedur benötigt einige Eingangsparameter. Die „Firma, Vorname, Nachname, Strasse, Hausnummer, Telefon, Email“. Wenn man zur Laufzeit alle Daten eingeben möchte, sollte man die Prozedur wie folgt aufrufen. Bsp.: adresse_anlegen('&v_firma', '&v_vorname', '&v_nachname', '&v_strasse', '&v_hausnummer', '&v_telefon', '&v_email');

P2 kunde_anlegen:

Diese Prozedur legt einen Kunden an. Da sich hinter einem KUNDE auch immer eine ADRESSE verbirgt, wird bevor der Kunde an sich gespeichert wird erst einmal eine Adresse mit der vorher beschriebenen Prozedur (adresse_anlegen) angelegt. Diese Adresse hat dann die höchste adresse_id. Dieses „Wissen“ nutze ich um dem Kunden auch die angelegte Adresse zuzuordnen. Diese Funktion könnte man auch so für Lieferanten implementieren. Allerdings müsste dann als Eingangsparameter nicht das lieferziel, sonder die lieferzeit übergeben werden. Bsp.: execute kunde_anlegen(’&v_lieferzeit’);

P3 lagerstatistik:

Diese Prozedur gibt eine Lagerstatistik aus. Es werden alle TEILE mit ihrem jeweiligen Warenwert ausgegeben. Somit bekommt man einen Überblick wie viel Teile jeder „TEIL“eart vorhanden sind und welchen Warenwert sie haben. Des weiteren wird noch die Gesamtsumme des Lagerbstandes ausgegeben. Diese Statistik könnte man auch als Inventurliste verwenden. Bsp.: execute lagerstatistik;

⁷ Datensatz: Ein Zeile in einer Tabelle bezeichnet man als Datensatz.

P4 print_bestellung:

Als einziger Eingangsparameter dieser Funktion muss ein bestellung_id „mitgegeben“ werden. Es werden dann eine Reihe Informationen aus den Tabellen: TEIL, BESTELLUNG, BESTELL_POSITION und ADRESSE. Diese Informationen werden dann, Positionsweise zusammengefasst, ausgegeben. Für die einzelnen Zwischensumme, Mwst, Rabatt etc. werden teilweise o.g. Funktionen und Prozeduren verwendet. Bsp.: execute print_bestellung;

P5 print_teilinfo:

Diese Prozedur gibt eine kurze Teileinfo aus. Die Bezeichnung und der Lieferant werden angezeigt. Bsp.: execute print_teilinfo(10);

Des weiteren gehörten neben o.a.g. Funktionen und Prozeduren noch zahlreiche SQL- Abfragen und Scripte, auf die ich hier nicht näher eingehe. Sie sind unter dem Gliederungspunkt 9 nachzulesen.

Unter dem nächsten Gliederungspunkt finden Sie eine Übersicht über die von mir verwendeten Entitäten, deren Attributen und die zugehörigen Datentypen.

3. Auswahl der Entitäten mit Attributen und Datentypen

4. Auswahl beispielhafter Beziehungen

Um das unter 5. gezeigte ER- Modell besser zu verstehen werde ich einige Beziehungen der Entitäten hier näher beschreiben.

1 zu 1 Beziehung:

Eine solche Beziehung bedeutet folgendes. In unserem konkreten Beispiel besitzt ein KUNDE genau eine ADRESSE. Eine Adresse wiederum gehört immer zu einem KUNDE oder zu einem LIEFERANT, oder in Sonderfällen auch zu einem KUNDE und zu einem LIEFERANT. Dies ist allerdings in der Praxis keine Ausnahme, sonder kommt einfach dadurch zu Stande, dass eine Person (oder Firma) sowohl KUNDE als auch LIEFERANT von „meiner“ Firma sein kann. Durch die 1 zu 1 Beziehung ist jedoch ausgeschlossen, dass z.B. ein KUNDE mehrere ADRESSEn hat, oder dass mehrere KUNDEn die selbe ADRESSE haben.

1 zu m Beziehung:

Eine solche Beziehung bedeutet, das in unserem Beispiel eine BESTELLUNG aus beliebig vielen BESTELL_POSITIONen bestehen kann, eine BESTELL_POSITION jedoch genau zu einer BESTELLUNG zugeordnet ist.

m zu m Beziehung:

Bei diesem Beispiel sehen wir eine m zu m Beziehung. Sie charakterisiert folgende Eigenschaft. Ein TEIL kann von mehreren KUNDEn gekauft werden. Ein KUNDE kann sich aber auch für mehrere Produkte (TEILe) interessieren und somit KUNDE dafür sein.

Die Beziehung TEIL - LIEFERANT ist eine m zu 1 Beziehung. Das bedeutet, dass ein LIEFERANT mehrere TEILE liefern kann, ein TEIL aber immer nur einen einzigen LIEFERANT besitzt. In größeren ERP- Systemen wird dies meist durch einen Hauptlieferant abgedeckt.

5. ER- Modell

Alle Symbole wurden (Punkt 4.) bzw. werden im Folgenden erläutert.

6. Nachweis der durchgeführten Normalisierung des ER- Modells

6.1. Die erste Normalform (1NF)

Um die 1NF zu erfüllen muss der Wertebereich der Attribute einer Relation elementar sein. Es dürfen sich keine weiteren Mengen von Werten oder weiteren Relationen in ihm (oder in einer Zelle einer Tabelle) befinden.

Typische einfache Datentypen 8 wären zum Beispiel ganze Zahlen, Dezimalzahlen, alphanumerische Zeichen, Zeichenketten oder Datentypen für logische Werte. Gegen diese Konvention verstößt folgende Tabelle:

Die Werte des Attributes Temperatur sind nicht elementar bzw. atomar. Um diese Vorrausetzungen zu erfüllen wäre es notwendig eine weitere Spalte in die Tabelle in die Tabelle zu integrieren. (siehe folgende Tabelle)

Die Tabellen / Relationen des vorliegende Datenbankentwurfes befinden sich in der 1NF, da jedes Element elementar ist und keine Element weggelassen werden kann, ohne dass ein Datenverlust auftritt.

6.2. Die zweite Normalform (2NF)

Die zweite Normalform vermeidet partielle funktionale Abhängigkeiten (diese bewirken Redundanzen). Eine partielle funktionale Abhängigkeit besteht, wenn Attribute (die nicht zum Schlüssel (#) gehören) funktional schon von einem Teil des Schlüssels abhängen. Die zweite Normalform kann durch Elimination der abhängigen Attribute und Auslagerung in eine eigene Relation(Tabelle) erreicht werden. Wenn ein Attribut nicht vollständig vom Primärschlüssel seiner Entität abhängig ist, ist dieses Attribut falsch platziert und muss dort entfernt werden. Dieses Attribut wird normalisiert, indem es entweder in die Entität verschoben wird, in die es tatsächlich gehört oder eine neue Entität für dieses Attribut geschaffen wird. Im folgenden Beispiel wird es sicher besser nachzuvollziehen sein.

⁸ einfacher Datentyp: Ein einfacher Datentyp ist durch einen Wertebereich charakterisiert, dessen Werte elementar sind, in

Diese Relation wäre nicht in der 2NF, da zum Beispiel das Attribut Name nicht voll funktional vom Schlüssel abhängt. Beim Löschen eines Datensatzes, z.B. des Ansprechpartners Schmitt, würde es auch zum Datenverlust des Lieferanten Meier kommen. Hierbei spricht man von sog. „Löschanomalien“. Um diesen Effekt zu umgehen und die Relation auch vor Redundanz zu schützen müssen wir eine neue Relation schaffen. Wir splitten also die Relation AuL in zwei separate auf.

Alle Relationen der „Prototypenverwaltung“ erfüllen die Vorraussetzungen für die 2NF. Da die Anforderungen an die erste Normalform erfüllt sind und jedes Attribut der jeweiligen Relation funktional vom Schlüssel abhängt liegt auch die zweite Normalform vor.

6.3. Die dritte Normalform (3NF)

Eine Relation befindet sich in der dritten Normalform, wenn sie die Anforderungen der zweiten Normalform erfüllt und kein Attribut (außer dem PrimaryKey) transitiv vom Schlüssel(PK) abhängt.

Am Beispiel ist gut zu erkennen, warum trotz funktionaler Abhängigkeit aller Attribute vom Primärschlüssel eine Überführung in die dritte Normalform benötigt wird. Noch immer können hier Anomalien auftreten, da zum Beispiel das Bestelldatum auftritt. Dadurch hat die BESTELLUNG_ID mehrfach den gleichen Wert. Wobei sich die BESTELL_POSITION_ID erhöht. Es ist also eine weitere Prüfung der Attribute notwendig. Um diese Relation in die dritte Normalform zu wandeln muss also diese transitive Abhängigkeit beseitigt werden. Das wird durch eine Auslagerung der Attribute Bestelldatum und BESTELLUNG_ID in eine separate Entität. Um aber noch die Zuordnung der Bestellpositionen zu einer Bestellung zu erhalten bleibt das Attribut BESTELLUNG_ID in der Relation BP als Referenz (ForeignKey) erhalten.

Tabelle (Relation) BP

Tabelle (Relation) BS

Alle Tabellen der Entwurfsübung erfüllen die Anforderungen der 2NF und haben auch keine transitiven Abhängigkeiten. Dadurch ist auch die 3NF nachgewiesen und erfüllt.

7. SQL- Script zum vollständigen Generieren der Tabellen entsprechend normalisiertem ER- Modell

-- Anlegen der Tabelle Teil -------------------------------------------------------------------------------------------------------------------------------------create table TEIL(

teil_id bezeichnung lieferant_id kunde_id baugruppe_id einzelpreis anzahl anlagedatum

constraint teil_pk primary key (teil_id) ,

constraint teil_fk foreign key (baugruppe_id) references TEIL (teil_id) ) /

-- Anlegen der Tabelle Lieferant ------------------------------------------------------------------------------------------------------------------------------create table LIEFERANT(

lieferant_id adresse_id lieferzeit anlagedatum

constraint lieferant_pk primary key (lieferant_id), constraint lieferant_un unique (adresse_id)

) /

-- Anlegen der Tabelle Kunde ---------------------------------------------------------------------------------------------------------------------------------create table KUNDE(

kunde_id adresse_id lieferziel anlagedatum

constraint kunde_pk primary key (kunde_id), constraint kunde_un unique (adresse_id) ) /

-- Anlegen der Tabelle Adresse ------------------------------------------------------------------------------------------------------------------------------create table ADRESSE(

adresse_id firma vorname nachname strasse hausnummer telefon email anlagedatum

constraint adresse_pk primary key (adresse_id) ) /

-- Anlegen der Tabelle Bestellung----------------------------------------------------------------------------------------------------------------------------create table BESTELLUNG(

bestellung_id adresse_id bestelldatum rabatt anlagedatum

constraint bestellung_pk primary key (bestellung_id) ) /

-- Anlegen der Tabelle Bestell_Position---------------------------------------------------------------------------------------------------------------------------create table BESTELL_POSITION(

constraint bestell_position_pk primary key (bestell_position_id) ) /

-- foreign keys für tabelle TEIL---------------------------------------------------------------------------------------------------------------------------------alter table TEIL

add (constraint teil_lieferant_fk foreign key (lieferant_id) references LIEFERANT (lieferant_id)); alter table TEIL

add (constraint teil_kunde_fk foreign key (kunde_id) references KUNDE (kunde_id));

-- foreign keys für tabelle LIEFERANT----------------------------------------------------------------------------------------------------------------------alter table LIEFERANT

add (constraint lieferant_adresse_fk foreign key (adresse_id) references ADRESSE (adresse_id));

-- foreign keys für tabelle KUNDE----------------------------------------------------------------------------------------------------------------------------alter table KUNDE

add (constraint kunde_adresse_fk foreign key (adresse_id) references ADRESSE (adresse_id));

-- foreign keys für tabelle BESTLLUNG---------------------------------------------------------------------------------------------------------------------alter table BESTELLUNG

add (constraint bestellung_adresse_fk foreign key (adresse_id) references ADRESSE (adresse_id));

-- foreign keys für tabelle BESTLL_POSITION------------------------------------------------------------------------------------------------------------alter table BESTELL_POSITION

add (constraint bestell_position_teil_fk foreign key (teil_id) references TEIL (teil_id)); alter table BESTELL_POSITION

add (constraint bestell_position_bestellung_fk foreign key (teil_id) references TEIL (teil_id));

create sequence teil_s

create sequence bestellung_s

create sequence bestell_position_s increment by 10 start with 10; create sequence adresse_s

create sequence kunde_s create sequence lieferant_s 8. SQL-Script zum Füllen der einzelnen Tabellen mit je 10 Datensätzen

-- script zum füllen der Tabellen --------------------------------------------------------------------------------------------------------------------------------Tabelle ADRESSE ---------------------------------------------------------------------------------------------------------------------------------------------insert into ADRESSE

values(adresse_s.NEXTVAL,'Stift und Zahn','Ulli','Stift','Am Wendehammer',8,'012346644332','mail@stift_und_zahn.de',sysdate); insert into ADRESSE

values(adresse_s.NEXTVAL,'Reich und schön','Magda','Reich','Unterm Deich',18,'011199987666','email@reich.de',sysdate); insert into ADRESSE

values(adresse_s.NEXTVAL,'Billiger und Söhne ','Werner','Billiger','Spaarstrasse',1,'03943121212','',sysdate); insert into ADRESSE

values(adresse_s.NEXTVAL,'Teurer gehts kaum','Kaum','Teurer','Hiltonweg',899,'019001900190','',sysdate); insert into ADRESSE

values(adresse_s.NEXTVAL,'Insolventia GbR','Katrin','Insolventia','Am Bach',7,'01234567890','email@123.de',sysdate); insert into ADRESSE

values(adresse_s.NEXTVAL,'Berg ab','Tal','Berg','Talsole',128,'03934202020','info@berg_ab.de',sysdate); insert into ADRESSE values(adresse_s.NEXTVAL,'Wucher und

KoKG','Sven','Wucher','Schlossstrasse',89,'09787654321','email@emailprovider.de.de',sysdate); insert into ADRESSE

values(adresse_s.NEXTVAL,'','No','Name','Irgendwo',7,'01222222222','mail@mail.de',sysdate); insert into ADRESSE values(adresse_s.NEXTVAL,'Kugelservice

Meyer','Mensch','Meyer','Schlossstrasse',28,'08008080808','123@allerlei.de',sysdate); insert into ADRESSE

values(adresse_s.NEXTVAL,'Freibadtechnik Wasserkopf','Karl-Heinz','Badewanne','Badweg',1,'0192837465','',sysdate);

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------Tabelle KUNDE --------------------------------------------------------------------------------------------------------------------------------------------------

insert into KUNDE

values(kunde_s.NEXTVAL,10,15,sysdate); insert into KUNDE

insert into KUNDE values(kunde_s.NEXTVAL,30,30,sysdate); insert into KUNDE values(kunde_s.NEXTVAL,40,30,sysdate); insert into KUNDE values(kunde_s.NEXTVAL,50,30,sysdate); insert into KUNDE values(kunde_s.NEXTVAL,60,25,sysdate); insert into KUNDE values(kunde_s.NEXTVAL,70,100,sysdate); insert into KUNDE values(kunde_s.NEXTVAL,80,30,sysdate); insert into KUNDE values(kunde_s.NEXTVAL,90,30,sysdate); insert into KUNDE values(kunde_s.NEXTVAL,100,30,sysdate);

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------Tabelle LIEFERANT --------------------------------------------------------------------------------------------------------------------------------------------insert into LIEFERANT

values(lieferant_s.NEXTVAL,10,15,sysdate); insert into LIEFERANT values(lieferant_s.NEXTVAL,20,15,sysdate); insert into LIEFERANT values(lieferant_s.NEXTVAL,30,55,sysdate); insert into LIEFERANT values(lieferant_s.NEXTVAL,50,15,sysdate); insert into LIEFERANT values(lieferant_s.NEXTVAL,70,15,sysdate); insert into LIEFERANT values(lieferant_s.NEXTVAL,80,25,sysdate); insert into LIEFERANT values(lieferant_s.NEXTVAL,90,15,sysdate); insert into LIEFERANT values(lieferant_s.NEXTVAL,60,15,sysdate); insert into LIEFERANT values(lieferant_s.NEXTVAL,40,35,sysdate); insert into LIEFERANT values(lieferant_s.NEXTVAL,70,15,sysdate);

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------Tabelle TEIL ------------------------------------------------------------------------------------------------------------------------------------------------------insert into TEIL

values (teil_s.nextval,'Motor',10,20,NULL,1500,0,sysdate); insert into TEIL

values (teil_s.nextval,'Zylinderkopf',20,10,10,50,0,sysdate); insert into TEIL

values (teil_s.nextval,'Nockenwelle',10,10,10,15.90,0,sysdate); insert into TEIL

values (teil_s.nextval,'Turbolader',30,20,NULL,5,0,sysdate); insert into TEIL

values (teil_s.nextval,'Abdeckhaube',40,20,40,12,0,sysdate); insert into TEIL

values (teil_s.nextval,'Grundplatte',40,10,40,20,0,sysdate); insert into TEIL

values (teil_s.nextval,'Spiralbohrer',50,30,60,7.45,12,sysdate); insert into TEIL

values (teil_s.nextval,'Fräser',60,30,30,23,25,sysdate); insert into TEIL

values (teil_s.nextval,'Bolzen',70,40,20,523,70,sysdate); insert into TEIL

values (teil_s.nextval,'Kerzenhülse',80,10,20,2500,2,sysdate);

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------Tabelle BESTELLPOSITION ---------------------------------------------------------------------------------------------------------------------------------insert into BESTELL_POSITION

values(bestell_position_s.NEXTVAL,10,10,5,sysdate); insert into BESTELL_POSITION values(bestell_position_s.NEXTVAL,20,10,53,sysdate); insert into BESTELL_POSITION values(bestell_position_s.NEXTVAL,30,10,15,sysdate); insert into BESTELL_POSITION values(bestell_position_s.NEXTVAL,40,40,1,sysdate); insert into BESTELL_POSITION values(bestell_position_s.NEXTVAL,50,40,2,sysdate);

values(bestell_position_s.NEXTVAL,60,30,50,sysdate); insert into BESTELL_POSITION values(bestell_position_s.NEXTVAL,70,30,100,sysdate); insert into BESTELL_POSITION values(bestell_position_s.NEXTVAL,80,60,25,sysdate); insert into BESTELL_POSITION values(bestell_position_s.NEXTVAL,90,70,115,sysdate); insert into BESTELL_POSITION values(bestell_position_s.NEXTVAL,100,100,2,sysdate);

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------Tabelle BESTELLUNG ----------------------------------------------------------------------------------------------------------------------------------------insert into BESTELLUNG

values(bestellung_s.NEXTVAL,10,sysdate+2,0,sysdate); insert into BESTELLUNG

values(bestellung_s.NEXTVAL,20,sysdate,0,sysdate); insert into BESTELLUNG

values(bestellung_s.NEXTVAL,30,sysdate+1,0,sysdate); insert into BESTELLUNG

values(bestellung_s.NEXTVAL,40,sysdate+20,0,sysdate); insert into BESTELLUNG

values(bestellung_s.NEXTVAL,50,sysdate,0,sysdate); insert into BESTELLUNG

values(bestellung_s.NEXTVAL,60,sysdate,0,sysdate); insert into BESTELLUNG

values(bestellung_s.NEXTVAL,50,sysdate+2,0,sysdate); insert into BESTELLUNG

values(bestellung_s.NEXTVAL,20,sysdate+4,0,sysdate); insert into BESTELLUNG

values(bestellung_s.NEXTVAL,10,sysdate+8,0,sysdate); insert into BESTELLUNG

values(bestellung_s.NEXTVAL,10,sysdate+12,0,sysdate);

9. SQL-Scripte für Datenabfrage

-- sql-abfragen

-- S O R T I E R U N G E N -------------------------------------------------------------------------------------------------------------------------------------- sortieren nach einzelpreis, das "billigste" zu erst -------------------------------------------------------------------------------------------------------

select t.bezeichnung, t.einzelpreis, t.anlagedatum,

a.vorname ||' '|| a.nachname "KUNDE" from TEIL t, KUNDE k, ADRESSE a where t.kunde_id=k.kunde_id and k.adresse_id=a.adresse_id order by t.einzelpreis;

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Adresse sortiert ausgegeben nach Nachnamen -------------------------------------------------------------------------------------------------------

select firma, vorname, nachname, strasse||' '||hausnummer "Strasse/Nummer"

, telefon, email from ADRESSE order by nachname;

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------- G R U P P E N F U N K T I O N E N ----------------------------------------------------------------------------------------------------------------------- Teile sortiert nach Preis, geordnet je nach Baugruppe -----------------------------------------------------------------------------------------------

select t.bezeichnung,max(t.einzelpreis)

from TEIL t

group by t.bezeichnung,t.baugruppe_id;

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Bestellungen geordnet ja nach Anzahl der Positionen -----------------------------------------------------------------------------------------------

select bs.bestellung_id, count(bp.bestellung_id) "Anzahl Positionen"

from BESTELLUNG bs, BESTELL_POSITION bp, TEIL t

and t.teil_id = bp.teil_id group by bs.bestellung_id;

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------- U N T E R A B F R A G E N ---------------------------------------------------------------------------------------------------------------------------------- Bestellung raussuchen, die an Adresse mit id 30 gehen soll ---------------------------------------------------------------------------------------

select bs.bestellung_id,bs.bestelldatum

from BESTELLUNG bs

where bs.bestellung_id=( select bestellung_id

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Zeige Teilinfos zu Teilen die an 2. Stelle ein R haben ------------------------------------------------------------------------------------------------

select bezeichnung, einzelpreis, anlagedatum

from TEIL where teil_id in ( select teil_id from TEIL where bezeichnung like '_r%');

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------- H I E R A R C H I S C H E A B F R A G E N ----------------------------------------------------------------------------------------------------------- Aufstellung welches Teil zu welcher Baugrupe gehört -----------------------------------------------------------------------------------------------select teil_id, bezeichnung ||' gehört zur Baugruppe: '|| PRIOR bezeichnung "BAUGRUPPEN"

from TEIL

CONNECT BY PRIOR teil_id = baugruppe_id START WITH teil_id = 10;

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Struktur der Baugruppen -------------------------------------------------------------------------------------------------------------------------------------column "Baugruppen-Struktur" FORMAT A30 select lpad(' ', 3 * LEVEL-3) || bezeichnung "Baugruppen-Struktur", LEVEL "EBENE" from teil

connect by prior teil_id = baugruppe_id start with baugruppe_id is null;

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------- A R I T H M E T I S C H E F U N K T I O N E N ------------------------------------------------------------------------------------------------------- Berechnung der Preise für bestellte Teile ----------------------------------------------------------------------------------------------------------------select t.bezeichnung, bp.anzahl, t.einzelpreis "Einzelpreis (pro Stück)",

(bp.anzahl * t.einzelpreis) "Gesamtpreis (pro Position)" from TEIL t, BESTELL_POSITION bp where t.teil_id = bp.teil_id;

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Auflistung der Bestellungen mit Bestelldatum und jeweiligem Liefertermin ---------------------------------------------------------------------

select bs.bestelldatum, l.lieferzeit,

(bs.bestelldatum + l.lieferzeit) "vorraussichtliches Lieferdatum", a.vorname ||' '|| a.nachname "Lieferant" from BESTELLUNG bs, LIEFERANT l, ADRESSE a, TEIL t, BESTELL_POSITION bp where l.lieferant_id = t.lieferant_id and t.teil_id = bp.teil_id and

bp.bestellung_id=bs.bestellung_id and l.adresse_id = a.adresse_id;

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Z E I C H E N K E T T E N F U N K T I O N E N -------------------------------------------------------------------------------------------------------- Ausgewählte Adressdaten darstellen ---------------------------------------------------------------------------------------------------------------------select upper(firma) ||' / '||

upper(nachname) ||', '||

initcap(strasse)||' '|| hausnummer||' / '|| email "ausgewählte ADRESSDATEN" from ADRESSE;

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Teile-Bezeichnug analysiert ---------------------------------------------------------------------------------------------------------------------------------select bezeichnung,

length(bezeichnung), instr(bezeichnung,'o') from TEIL

order by instr(bezeichnung,'o'),length(bezeichnung);

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------- K O N V E R T I E R U N G S F U N K T I O N E N ---------------------------------------------------------------------------------------------------- Anlagedatum ausgewertet -----------------------------------------------------------------------------------------------------------------------------------select a.vorname||' '||a.nachname ||' ist Kunde seit dem: '||

to_char(a.anlagedatum,'fmDD. MONTH YYYY')"KUNDENINFOS" from ADRESSE a, KUNDE k

where a.adresse_id = k.adresse_id;

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Adressinfos auswerten und gegebenen Falls die Firma ersetzen ---------------------------------------------------------------------------------

select nvl(firma,'K E I N E F I R M A '),

vorname nachname from ADRESSE;

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------- D A T U M S F U N K T I O N E N -------------------------------------------------------------------------------------------------------------------------- Datumsinformation zum Liefertermin ----------------------------------------------------------------------------------------------------------------------

column lieferdatum format a85;

select bs.bestellung_id, bs.bestelldatum,

'Das Teil: '||t.bezeichnung || ' könnte in ca: '||(l.lieferzeit*24)||' Stunden da sein.' ||' Also am: '||to_char((l.lieferzeit+bs.bestelldatum),'fmDD.MONTH YYYY') "Lieferdatum" from BESTELLUNG bs, LIEFERANT l, ADRESSE a, TEIL t, BESTELL_POSITION bp where l.lieferant_id = t.lieferant_id and t.teil_id = bp.teil_id and

bp.bestellung_id=bs.bestellung_id and l.adresse_id = a.adresse_id;

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Round Funktion für Datum angewendet ------------------------------------------------------------------------------------------------------------------

select adresse_id, telefon, email,

to_char(round(anlagedatum,'MONTH'),'fmDD. MONTH YYYY') "Statistischer Startwert" from ADRESSE;

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 10.Script zum Erzeugen eines Paketes mit 8 Funktionen und 5 Prozeduren

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Packagedefinition ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------CREATE OR REPLACE PACKAGE prototypen_package IS FUNCTION get_kunde_info (v_teil_id in TEIL.teil_id%TYPE) RETURN number;

FUNCTION get_mwst (netto_preis in number) RETURN number;

FUNCTION get_bestellsumme (v_bestellung_id in BESTELLUNG.bestellung_id%TYPE) RETURN number; FUNCTION get_rabatt (v_bestellung_id in BESTELLUNG.bestellung_id%TYPE) RETURN number; FUNCTION get_gesamtsumme_brutto (v_gesamtsumme_netto in number,

FUNCTION anzahl_kunden RETURN number;

FUNCTION anzahl_adressen RETURN number;

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------PROCEDURE adresse_anlegen(

PROCEDURE kunde_anlegen (v_lieferziel IN KUNDE.lieferziel%TYPE);

PROCEDURE lagerstatistik;

PROCEDURE print_bestellung (v_bestellung_id IN BESTELLUNG.bestellung_id%TYPE); PROCEDURE print_teilinfo (v_teil_id in TEIL.teil_id%TYPE); END prototypen_package;

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------- FUNKTIONEN UND PROZEDUREN im Packagebody --------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------CREATE OR REPLACE PACKAGE BODY prototypen_package IS

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------- F1: Funktion die den Einzelpreis eines Teils zurück gibt -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------CREATE OR REPLACE FUNCTION get_kunde_info (v_teil_id in TEIL.teil_id%TYPE) RETURN number IS v_einzelpreis number(5); BEGIN select einzelpreis into v_einzelpreis from TEIL where teil_id = v_teil_id; RETURN(v_einzelpreis); END get_kunde_info; --/

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------- F2: Diese Funktion liefert die kürzeste Lieferzeit unabhängig vom Teil -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------CREATE OR REPLACE FUNCTION get_kuerzeste_lieferzeit RETURN number IS v_lieferzeit number(5); BEGIN select min(lieferzeit) into v_lieferzeit from LIEFERANT; RETURN(v_lieferzeit); END get_kuerzeste_lieferzeit; --/

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------- F3: Diese Funktion berechnet die MWST eines Nettobetrages -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------CREATE OR REPLACE FUNCTION get_mwst (netto_preis in number) RETURN number IS BEGIN RETURN (netto_preis*0.16); END get_mwst; --/

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------- F4: Hier wird die NettoBestellSumme berechnet ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------CREATE OR REPLACE FUNCTION get_bestellsumme (v_bestellung_id in BESTELLUNG.bestellung_id%TYPE)

IS

v_summe TEIL.einzelpreis%TYPE; CURSOR teil_summe_cursor IS select (t.einzelpreis * bp.anzahl)sum from BESTELLUNG bs,

where l.lieferant_id = t.lieferant_id

BEGIN

v_summe:=0;

FOR teil_summe_record IN teil_summe_cursor LOOP v_summe:=v_summe + teil_summe_record.sum; END LOOP; RETURN(v_summe); END get_bestellsumme; --/

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------- F5: Diese Funktion berechnet einen Rabatt abhängig von der Summe -----------------------------------------------------------------------

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------CREATE OR REPLACE FUNCTION get_rabatt (v_bestellung_id in BESTELLUNG.bestellung_id%TYPE) RETURN number IS

v_summe TEIL.einzelpreis%TYPE; v_rabatt BESTELLUNG.rabatt%TYPE; BEGIN

v_summe:=get_bestellsumme(v_bestellung_id); if v_summe > 10000 then v_rabatt:=5; elsif v_summe > 5000 then v_rabatt:=3; elsif v_summe > 1000 then v_rabatt:=1; else v_rabatt:=0; end if; RETURN(v_rabatt); END get_rabatt; --/

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------- F6: Hier wird nun die entgültige Bruttosumme berechnet ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------CREATE OR REPLACE FUNCTION get_gesamtsumme_brutto( v_gesamtsumme_netto in number, v_mwst

v_rabatt ) RETURN number IS v_gesamtsumme_brutto number(7,2); BEGIN

v_gesamtsumme_brutto := (v_gesamtsumme_netto + v_mwst)-(((v_gesamtsumme_netto + v_mwst)*v_rabatt)/100); RETURN(v_gesamtsumme_brutto); END get_gesamtsumme_brutto; --/

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------- F7: Diese Funktion gibt die Anzahl der Kunden zurück ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------CREATE OR REPLACE FUNCTION anzahl_kunden RETURN NUMBER IS v_anzahl NUMBER(2); BEGIN SELECT COUNT(kunde_id)

FROM KUNDE; RETURN (v_anzahl); END anzahl_kunden; --/

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------- F8: Diese Funktion gibt die Anzahl Adressen zurück ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------CREATE OR REPLACE FUNCTION anzahl_adressen RETURN NUMBER IS v_anzahl NUMBER(2); BEGIN SELECT COUNT(adresse_id) INTO v_anzahl FROM ADRESSE; RETURN (v_anzahl); END anzahl_adressen; --/

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------- P1: mit dieser Procedur kann man Adressen anlegen ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------CREATE OR REPLACE PROCEDURE adresse_anlegen(

v_firma v_vorname v_nachname v_strasse v_hausnummer v_telefon v_email )

IS

v_maxadresse_id v_adressen_anzahl

BEGIN

SELECT max(adresse_id) INTO v_maxadresse_id FROM ADRESSE; v_maxadresse_id := v_maxadresse_id+10;

INSERT INTO ADRESSE (adresse_id,firma,vorname,nachname,strasse,hausnummer,telefon,email,anlagedatum) VALUES (v_maxadresse_id,v_firma,v_vorname,v_nachname,v_strasse,v_hausnummer,v_telefon,v_email,SYSDATE); v_adressen_anzahl:=anzahl_adressen();

DBMS_OUTPUT.PUT_LINE('Anzahl Adressen im Datenbestand:'||v_adressen_anzahl); END adresse_anlegen; --/

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------- P2: Diese Prozedur nutzt P1 und legt danach zusätzliche Kundendaten an -----------------------------------------------------------------

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------CREATE OR REPLACE PROCEDURE kunde_anlegen( v_lieferziel IN KUNDE.lieferziel%TYPE ) IS

v_maxadresse_id v_adressen_anzahl BEGIN

adresse_anlegen('&v_firma','&v_vorname','&v_nachname','&v_strasse','&v_hausnummer','&v_telefon','&v_email'); SELECT max(adresse_id) INTO v_maxadresse_id FROM ADRESSE;

INSERT INTO KUNDE (kunde_id,adresse_id,lieferziel,anlagedatum) VALUES (kunde_s.NEXTVAL,v_maxadresse_id,v_lieferziel,SYSDATE); END kunde_anlegen; --/

--execute kunde_anlegen('&v_lieferzeit');

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------- P3: Diese Prozedur liefert eine detaliierte Lagerstatistik (Warenwerte) -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------CREATE OR REPLACE PROCEDURE lagerstatistik IS v_gesamtsumme NUMBER(7,2); cursor lagerstatistik_cursor IS select t.bezeichnung, t.einzelpreis, t.anzahl,

(a.vorname||' '||a.nachname) as kunde, a.firma from TEIL t, ADRESSE a, KUNDE k where t.kunde_id = k.kunde_id and a.adresse_id = k.adresse_id order by warenwert; BEGIN v_gesamtsumme:=0;

DBMS_OUTPUT.PUT_LINE('--------------- L A G E R B E S T A N D S S T A T I S T I K --------------'); DBMS_OUTPUT.PUT_LINE('--------------------------------------------------------------------------'); FOR lagerstatistik_record IN lagerstatistik_cursor LOOP DBMS_OUTPUT.PUT_LINE(lagerstatistik_record.bezeichnung); DBMS_OUTPUT.PUT_LINE('- - - - - - - - - - - -');

DBMS_OUTPUT.PUT_LINE('Bestand: '||lagerstatistik_record.anzahl||' Stück'); DBMS_OUTPUT.PUT_LINE('Warenwert: '||lagerstatistik_record.warenwert|| ' EURO');

DBMS_OUTPUT.PUT_LINE('KUNDE: '||upper(lagerstatistik_record.Kunde)||' von der Firma: '||lagerstatistik_record.firma); DBMS_OUTPUT.PUT_LINE('***********************'); v_gesamtsumme:=v_gesamtsumme + lagerstatistik_record.warenwert; END LOOP;

DBMS_OUTPUT.PUT_LINE('-----------------------'); DBMS_OUTPUT.PUT_LINE('-----------------------'); DBMS_OUTPUT.PUT_LINE('WARENWERT GESAMT: '||v_gesamtsumme|| ' EURO');

DBMS_OUTPUT.PUT_LINE('--------------------------------------------------------------------------'); END lagerstatistik; --/ --execute lagerstatistik;

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------- P4: Diese Prozedur listet alle Positionen einer Bestellung auf sowie Summen --------------------------------------------------------------

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------CREATE OR REPLACE PROCEDURE print_bestellung( v_bestellung_id IN BESTELLUNG.bestellung_id%TYPE) IS

v_rabatt v_mwst v_gesamtsumme v_gesamtsumme_brutto v_lieferadresse varchar(255); cursor bestellung_cursor IS select t.bezeichnung, t.einzelpreis, bs.bestellung_id, bs.bestelldatum,

(a.firma||' / '||a.vorname||' '||a.nachname||' / '||a.strasse||' '||a.hausnummer)as lieferadresse, bp.bestell_position_id, bp.anzahl,

(t.einzelpreis * bp.anzahl) as positionssumme from TEIL t, BESTELLUNG bs, BESTELL_POSITION bp, ADRESSE a where bp.teil_id = t.teil_id and

bp.bestellung_id = bs.bestellung_id and a.adresse_id = bs.adresse_id and

bs.bestellung_id=v_bestellung_id; BEGIN v_gesamtsumme := 0; v_rabatt:= get_rabatt(v_bestellung_id);

DBMS_OUTPUT.PUT_LINE('----------BESTELLUNG Nr: '||v_bestellung_id); DBMS_OUTPUT.PUT_LINE('-------------------------------------------'); DBMS_OUTPUT.PUT_LINE('Positionen'); DBMS_OUTPUT.PUT_LINE('----------'); FOR bestellung_record IN bestellung_cursor LOOP

DBMS_OUTPUT.PUT_LINE('Position: '||bestellung_record.bestell_position_id); DBMS_OUTPUT.PUT_LINE(' '||bestellung_record.bezeichnung); DBMS_OUTPUT.PUT_LINE(' Anzahl: '||bestellung_record.anzahl||' Stück'); DBMS_OUTPUT.PUT_LINE(' Einzelpreis: '||bestellung_record.einzelpreis); DBMS_OUTPUT.PUT_LINE(' Positionssumme: '||bestellung_record.positionssumme); DBMS_OUTPUT.PUT_LINE('-------------------------------'); v_gesamtsumme:=v_gesamtsumme + bestellung_record.positionssumme;

END LOOP; v_mwst:= get_mwst(v_gesamtsumme);

v_gesamtsumme_brutto:= get_gesamtsumme_brutto(v_gesamtsumme,v_mwst,v_rabatt); DBMS_OUTPUT.PUT_LINE('-------------------------------'); DBMS_OUTPUT.PUT_LINE('----Gesamtsumme Netto: '||v_gesamtsumme||' EURO'); DBMS_OUTPUT.PUT_LINE('----Rabatt: '||v_rabatt||' %'); DBMS_OUTPUT.PUT_LINE('----MWST: '||v_mwst||' EURO');

DBMS_OUTPUT.PUT_LINE('-------zu zahlender Betrag Brutto: '||v_gesamtsumme_brutto||' EURO'); DBMS_OUTPUT.PUT_LINE('-------------------------------'); DBMS_OUTPUT.PUT_LINE('Geliefert wir an: '||v_lieferadresse); EXCEPTION WHEN NO_DATA_FOUND THEN

RAISE_APPLICATION_ERROR(-20221, 'Diese Bestellung gibt es nicht!'); END print_bestellung; --/

--execute print_bestellung(10);

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------- P5: Diese Prozedur gibt ausgewählte Teileinfos aus ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------CREATE OR REPLACE PROCEDURE print_teilinfo( v_teil_id in TEIL.teil_id%TYPE) IS

cursor print_teilinfo_cursor IS select t.teil_id, t.bezeichnung,

(a.vorname||' '||a.nachname)as lieferant from TEIL t, ADRESSE a, LIEFERANT l where t.lieferant_id=l.lieferant_id and l.adresse_id=a.adresse_id and v_teil_id=t.teil_id; BEGIN

FOR print_teil_info_record in print_teilinfo_cursor LOOP DBMS_OUTPUT.PUT_LINE('---------- TEILEINFO ----------');

DBMS_OUTPUT.PUT_LINE(print_teil_info_record.bezeichnung||' Lieferant: '||print_teil_info_record.lieferant); END LOOP; EXCEPTION WHEN NO_DATA_FOUND THEN

RAISE_APPLICATION_ERROR(-20222, 'Dieses Teil gibt es nicht!'); END print_teilinfo; --/ END prototypen_package; /

11. Script zum Erzeugen von 2 Datenbanktriggern

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Trigger 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------CREATE OR REPLACE TRIGGER delete_adresse BEFORE delete ON ADRESSE DECLARE v_adresse_anzahl number(5); BEGIN

v_adresse_anzahl:=anzahl_adressen(); if (v_adresse_anzahl < 1)then RAISE_APPLICATION_ERROR

(-20211, 'Es sind keine Adressen vorhanden, die sie löschen können'); end if; END; /

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Trigger 2 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------CREATE OR REPLACE TRIGGER delete_kunde BEFORE delete ON KUNDE DECLARE v_adresse_anzahl number(5); BEGIN

v_adresse_anzahl:=anzahl_adressen(); if (v_adresse_anzahl < 1)then

(-20212, 'Es sind keine Kunden vorhanden, die sie löschen können'); end if; END; /

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- VielenDank für Ihre Aufmerksamkeit

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------