Konzept und Umsetzung einer automatischen Messwertarchivierung

GhK
Universität–G esam tho c hsc hule Kassel
Z UB - Z entrum fü rUm w eltg erec htes B auen
Diplo m arbeitII
Ko nz eptund Um setz u ng
einerauto m atisc hen
M essw ertarc hivierung m it
O RAC LE
UniversitätG esam tho c hsc hule Kassel
F ac hg ebiete Bauphy sik / TG A, G o ttsc halkstr.28 , 34109 Kassel
Verfasser
Ax elSeibel
12.02.2001
1
Universität Gesamthochschule Kassel
Diplomarbeit II
Konzept und Umsetzung einer automatischen
Messwertarchivierung mit ORACLE
von
Dipl.-Ing. Axel Seibel
Mat.-Nr.: 668206
Betreuer:
Univ.-Prof. Dr.-Ing. Jürgen Schmid (Fachbereich 16 Elektrotechnik)
Univ.-Prof. Dr.-Ing. Gerhard Hausladen (Fachbereich 12 Architektur)
Dipl.-Ing. Jens Oppermann (Fachbereich 12 Architektur)
INHALT
1
EINLEITUNG .................................................................................. 5
1.1 Allgemein .............................................................................................. 5
1.2 Abweichung zur Aufgabenstellung und weitere Informationen ........... 5
2
ABKÜRZUNGEN............................................................................. 6
2.1 Allgemeine............................................................................................. 6
2.2 Abkürzungen für die Datenbanktabellen .............................................. 6
3
KONZEPT DER DATENBANK FÜR DAS ZENTRUM FÜR
UMWELTGERECHTES BAUEN (ZUB)............................................... 7
3.1 Grundlegendes ...................................................................................... 7
3.1.1 Datenerfassung der Gebäudeleittechnik und der Klimadaten...................... 7
3.1.2 Informationsschwerpunkte der ZUB Leittechnik ......................................... 8
3.1.3 Redundanzen der Informationsschwerpunkte .............................................. 9
3.1.4 Bereitstellung der Messdaten, Schnittstelle zur Datenbank ........................10
3.1.5 Nutzen der Redundanzen der Messpunkte ..................................................10
3.1.6 Bezeichnungen für die Spaltennamen wählen.............................................11
3.1.7 Anforderungen an die Leittechnik und die ZUB Datenbank........................12
3.1.8 Begriffe im weiteren Verlauf ......................................................................13
3.2 Aufbau und Übersicht......................................................................... 14
3.2.1 Aufbau der benötigten Datentabellen.........................................................14
3.2.2 Aufbau der benötigten Fehlertabellen........................................................15
3.2.3 Struktur der Datenbank .............................................................................16
3.2.4 Die Umgebungstabellen ZUORDNUNG und ANHANG .............................18
3.2.5 Weitere wichtige Umgebungstabellen ........................................................20
3.2.5.1 Die Zeitreferenztabelle ZEITTAB............................................................. 20
3.2.5.2 Tabellen FEHLER_CODE, FEHL_AUTO_ANZEIGE_TAB und
FEHL_TAB............................................................................................................. 21
3.3 Datenfluss und Verarbeitung .............................................................. 24
3.3.1 Automatisierter täglicher Ablauf................................................................26
3.3.1.1
Ein Beispiel: Täglicher Datenfluss ............................................................ 26
2
3.3.1.2
3.3.1.3
Verbessern ungenauer Datumseinträge aus ZUB_DATEN_TMP_TAB ..... 28
Automatische Fehlerkontrolle ................................................................... 30
3.3.2 Manueller Ablauf mit Blick auf die Tabelle FEHL_TAB ............................31
4
REALISIERUNG ........................................................................... 34
4.1 Anlegen der Datenbank....................................................................... 34
4.1.1 Verzeichnisse.............................................................................................34
4.1.2 Vorbereitung .............................................................................................35
4.1.3 Die Datenbank neu anlegen (Ablaufstrg_DB_neu_anlegen.sql).................35
4.2 Datenbanktabellen anlegen/ändern
(Ablaufstrg_DB_Tabellen_anlegen.sql) ...................................................... 38
4.2.1 Übersicht...................................................................................................38
4.2.2 Zuordnung prüfen (Funktion_Zuordnung_pruefen.sql) ..............................39
4.2.3 Anlegen / Ändern der Datentabellen (DB_Tabellen_anlegen.sql) ..............42
4.3 Ändern von ZUORDNUNG ................................................................ 44
4.3.1 Hinzufügen von Zeilen ...............................................................................44
4.3.2 Löschen von Zeilen....................................................................................45
4.3.3 Ändern von Zeilen .....................................................................................46
4.4 Ausführen der Datenbank................................................................... 46
4.4.1 Tägliche Ablaufsteuerung (Ablaufstrg_taeglich.sql) ..................................47
4.4.2 Füllen der Datentabellen zur Auswertung (Ablaufstrg_manuell.sql) ..........49
5
BEARBEITUNG DER DB UNTER ORACLE FORMS.............. 52
5.1 Fehlertabellen bearbeiten ................................................................... 52
5.2 ZUORDNUNG bearbeiten .................................................................. 56
5.3 Messbereich ändern ............................................................................ 59
5.4 Fehlertabelle bearbeiten...................................................................... 60
5.5 Die Symbolleiste .................................................................................. 62
6
ZUSAMMENFASSUNG UND AUSBLICK ................................. 64
6.1 Zusammenfassung .............................................................................. 64
6.2 Ausblick............................................................................................... 66
3
7
LITERATUR .................................................................................. 68
8
ABBILDUNGSVERZEICHNIS .................................................... 69
9
ANHANG ........................................................................................ 70
9.1 PL/SQL Skripte ................................................................................... 70
9.2 CD-ROM ............................................................................................139
9.2.1 Diese Ausarbeitung .................................................................................139
9.2.2 PL/SQL Skripte, Eingabemaske unter ORACLE Forms............................139
4
Einleitung
1 Einleitung
1.1 Allgemein
Diese Arbeit befasst sich mit der Archivierung und Aufbereitung von Messdaten unter
dem relationalen Datenbankprogramm Oracle. Die Messdaten werden im „Zentrum für
umweltbewusstes Bauen“ (ZUB) erfasst und stehen der Datenbank zur Verfügung. Alle
erfassten Messdaten werden über die Laufzeit des Messprojektes in Tabellen archiviert.
Die Datenbank muss so übersichtlich sein, dass die Messdaten möglichst leicht ausgewertet werden können. Die Auswertung ist nicht Teil dieser Arbeit.
Das ZUB ist ein Institut für umweltbewusstes Bauen. Das ZUB-Gebäude selbst soll als
Forschungsobjekt dienen. Daher wird eine Leittechnik mit vielen Messpunkten installiert, welche die Klimatisierung und Steuerung des Gebäudes weitgehend übernehmen
soll. Unter dem Begriff Forschungsobjekt ist u.a. zu verstehen, dass die installierten
Messpunkte im Verlauf des Betriebes ausgewertet werden, damit Verbesserungen der
Steuerung und Klimatisierung erzielt werden können, sowie auf bautechnische Verbesserungen zurückgeschlossen werden kann. Die geeignete Anwendung zur Sicherung
und Aufbereitung der Messdaten ist eine Datenbank. Die Auswertung der Daten erfolgt
dann durch den Anwender, indem aus den aufbereiteten Daten weitere Größen berechnet werden und die Inhalte der Tabellen aus der Datenbank (also die Messdaten) so
ausgeben werden, dass diese ein Programm zur Auswertung verarbeiten kann.
1.2 Abweichung zur Aufgabenstellung und weitere Informationen
Die täglichen Messdaten des ZUB werden nicht in ASCII-Dateien zur Verfügung
gestellt, sondern direkt in eine Oracle Tabelle geschrieben.
Das ZUB ist während der Bearbeitung der Aufgabenstellung nicht fertig gestellt worden. Die Leittechnik wurde daher nicht in Betrieb genommen und wichtige Konzepte
fehlen. Die Datenbank musste mit den zur Verfügung stehenden Informationen
programmiert werden.
Die Dokumentation versteht sich als Anwenderdokumentation. Auf die Datenbanksprache SQL (Structured Query Language) und den Aufbau von Oracle selbst wird
nicht eingegangen. Die Literatur (Kap. 7) bietet einen besseren Überblick als eine kurze
Zusammenfassung in dieser Arbeit.
5
Abkürzungen
2 Abkürzungen
2.1 Allgemeine
CAN
Controller Area Network
DB
Datenbank
DDC
Direct Digital Control
ISP
Informationsschwerpunkt
PL/SQL
Procedural Language/SQL
ODBC
Open Database Connection
SQL
Structured Query Language
ZUB
Zentrum für umweltbewusstes Bauen
2.2 Abkürzungen für die Datenbanktabellen
Die Bezeichnungen werden bei Zusammensetzung von hinten nach vorne gelesen.
AUTO
Automatisch
FEHL
Fehlermarkierung
INV
Invers
JAHR
Jahresmittelwerte
MESS
Messbereich
MONAT
Monatsmittelwerte
ROH
Rohdaten
ROHKORR
korrigierte Rohdaten
TAB
Tabelle
TAG
Tagesmittelwerte
TMP
Temporär
Beispiel: <Kennzeichner>_FEHL_AUTO_TAB → <Kennzeichner> Tabelle mit
automatischer Fehlermarkierung
6
Konzept der Datenbank für das Zentrum für umweltgerechtes Bauen (ZUB)
3 Konzept der Datenbank für das Zentrum für
umweltgerechtes Bauen (ZUB)
Die Datenbank für das ZUB begrenzt sich auf die Sicherung und die erste Aufbereitung
der Messdaten zur Auswertung. Unter der ersten Aufbereitung ist die Anordnung der
Messdaten gemeint und nicht die Berechnung weiterer Größen.
Zum jetzigen Zeitpunkt sind noch keine Konzepte für eine Auswertung bekannt; daher
kann noch keine Aussage über die Weiterverarbeitung getroffen werden. Auch ein
Konzept für Anordnung der Messdaten existiert nur vorläufig (Kap. 3.1.2). Die Datenbank muss so flexibel sein, dass eben die Anordnung der Messpunkte unter Angabe
einiger Informationen automatisch gewährleistet wird. Im Verlaufe des Kapitels wird
sich klären, was damit gemeint ist.
3.1 Grundlegendes
3.1.1 Datenerfassung der Gebäudeleittechnik und der Klimadaten
Messdaten zur Erfassung in der Datenbank werden an zwei Stellen aufgenommen. Einmal die des ZUB selbst und zum anderen Klimadaten, die von einer in unmittelbarer
Nachbarschaft stehenden Wetterstation der Universität Kassel aufgenommen werden.
Die Messpunkte des ZUB werden über einen CAN-Bus (Controller Area Network) dem
Steuerrechner, der DDC (Direct Digital Control) übermittelt.
Die Klimadaten sind über das Netzwerk der Universität Kassel oder ein eigenes Netzwerk verfügbar1. Für die Haustechnikregelung oder eine Auswertung sind die Daten
unerlässlich und müssen dem Steuerrechner zur Verfügung stehen. Abbildung 1 zeigt
die Möglichkeit über den Anschluss an das Universitätsnetzwerk.
1
Diese Angaben sind ohne Gewähr, da zum Zeitpunkt dieser Arbeit die Schnittstelle noch nicht geklärt
ist.
7
Konzept der Datenbank für das Zentrum für umweltgerechtes Bauen (ZUB)
Wetterstation
ZUB
Universitätsnetzwerk
Technische
Gebäudeausrüstung
Abbildung 1: Datenverkehr über das Universitätsnetzwerk
3.1.2 Informationsschwerpunkte der ZUB Leittechnik
Im ZUB werden die Messpunkte in sogenannten Informationsschwerpunkten (ISP) der
"Gebäudeautomation Informationsliste" dokumentiert [8]. Es gibt bisher 24 ISPs:
ISP
Bezeichnung
00-02
03, 06, 21
04
05
07-19
Raum 0.01 –2.08
Strom
Klimadaten
Sonderräume
Standardräume
20
22
Messräume
Zentrales
Lüftungsgerät
Kanalnetz
Heizung /
Kühlung
23
24
ca. Anzahl Bemerkung
Messpunkte
pro ISP
5
Erdgeschoss, Etage 1, Etage 2
4
13
95
13
Standardräume enthalten zusätzliche
Messwerte von ISP 00 bis ISP 02
122
44
31
54
8
Konzept der Datenbank für das Zentrum für umweltgerechtes Bauen (ZUB)
Die Tabelle stellt diese Informationsschwerpunkte gebündelt dar. Die Berechnung der
Gesamtanzahl der Messpunkte ergibt sich aus der Anzahl Messpunkte pro ISP mal
Anzahl der ISP. Die Anzahl der Messpunkte beläuft sich auf ca. 700. Je nach Bedarf
kann sich die Anzahl noch ändern. Messpunkte innerhalb der ISP sind beispielsweise:
-
Temperatur
-
Strahlung
-
Luftfeuchtigkeit
-
Ventilzustände (Binär)
-
Kontakte (Binär)
-
Durchfluss
-
Volumina
-
Etc.
Für den Aufbau der Datenbank bietet sich an, Redundanzen der Informationsschwerpunkte zu nutzen, wie sie schon in obiger Tabelle dargestellt wurden.
3.1.3 Redundanzen der Informationsschwerpunkte
Wird jeder ISP in einer autonomen Tabelle gesichert, entstehen 24 Tabellen (Gesamtzahl der ISPs), die nach Ort und Funktionen eingeteilt sind. Es ist jedoch für eine
Auswertung sinnvoller verschiedene ISP´s mit gleicher Funktion zusammenzufassen.
Die Redundanzen in den ISPs werden erstens zur Reduzierung der Anzahl der Tabellen
insgesamt, zweitens zur Reduzierung der Spaltenzahl und drittens zur Verbesserung der
Übersichtlichkeit genutzt. Die Auswertbarkeit wird verbessert.
Es sind Redundanzen bei den Standardräumen ISP 00 – 02 zu erkennen. Nur der Ort
unterscheidet sich, nicht ihre Funktion. Standardräume sind auf alle Etagen des ZUB
verteilt. Spezielle Messpunkte dieser Räume werden zusätzlich in ISP 07 - 19 zusammengefasst. Also werden ISP 00–02 mit ISP 07 - 19 verbunden. Der Hintergrund der
Aufteilung ist, dass nicht in allen Standardräumen die gleichen Daten erfasst werden
bzw. zusätzliche Daten aufgenommen werden müssen. wenn z.B. ein Raum mehr
Fenster oder Türen wie ein anderer hat. Gleiches gilt u.a. für den ISP Strom.
9
Konzept der Datenbank für das Zentrum für umweltgerechtes Bauen (ZUB)
3.1.4 Bereitstellung der Messdaten, Schnittstelle zur Datenbank
Die Messpunkte des ZUB werden von der DDC in eine oder mehrere dBase Tabellen
geschrieben (dBase ist ein relationales Datenbanksystem) Die Spaltennamen dieser
Tabelle(n) entsprechen den Hardwareadressen der Leittechnik, über die die Messwerte
erfasst werden (die Adressen sind bisher noch nicht festgelegt). Zusätzlich enthält
die/jede Tabelle eine Spalte für das Datum und die Uhrzeit. Handelt es sich um mehrere
Tabellen, werden diese in einer Tabelle zusammengefasst, die alle Spaltennamen für die
Messpunkte plus eine Spalte für das Datum und eine für die Uhrzeit enthält. Diese
Tabelle wird über einen ODBC-Treiber in das Oracle Format konvertiert und soll
mindestens die Messdaten der letzten 14 Wochen enthalten (Abbildung 2). Sie wird
täglich aktualisiert und in die ZUB-DB (ZUB Datenbank) kopiert.
Diese Tabelle heißt ZUB_DATEN_TMP_TAB2 und ist der Ausgangspunkt für die
weitere Verarbeitung.
Rohdaten
(dBase)
Täglichen Messdaten
erfasst von der Leittechnik;
eine oder mehrere Tabellen.
Konvertierung von dBase
nach ORACLE
Oracle
Tabelle
ODBC
Treiber
ZUB_DATEN_TMP_TAB
(Oracle)
Abbildung 2: Schnittstelle zwischen Gebäudeleittechnik und ZUB-DB
3.1.5 Nutzen der Redundanzen der Messpunkte
Redundanzen der ISPs sind in Kap. 3.1.3 beschrieben. Möglichkeiten die Messwerte
abzulegen sind u.a.:
1) Alle Daten ungeordnet in eine Tabelle ablegen.
2
Gelesen: Tabelle mit temporären Messdaten des ZUB
10
Konzept der Datenbank für das Zentrum für umweltgerechtes Bauen (ZUB)
2) Alle Daten ungeordnet in mehrere Tabellen ablegen.
3) Aufteilung der Daten nach Etagen.
4) Aufteilung der Daten nach Räumen und Funktionen.
Punkt 1 ist nicht zu empfehlen. Die Anzahl der zulässigen Spalten von Oracle ist 1000,
aber durch die Tabellengröße werden Abfragen auf die Tabelle sehr zeitintensiv. Desweiteren ist die Identifizierung mit der Hardwareadresse als Spaltenname umständlich.
Das macht die Auswertung unübersichtlich und schwer wartbar, da selbst kleine
Änderungen in der Tabelle einen großen Aufwand bei der Anpassung des Auswertprogrammes nach sich ziehen.
Punkt 2 entspricht in den Nachteilen Punkt1, außer dass die Zugriffszeit verbessert
wird.
Punkt 3 scheint sinnvoll. Redundanzen der ISPs werden aber auch hier nicht genutzt.
Am Besten ist es - nach Punkt 4 - die Geschosse nach Räumen und Funktionen zusammenzufassen. Es gibt beispielsweise eine bestimmte Anzahl an Standardräumen, in
denen die gleichen Messungen durchgeführt werden. Um die Räume mit gleichen
Messpunkten zusammenzuführen, wird immer eine Tabelle benutzt, die Spalten für das
Datum, die Messpunkte und die Raumnummern enthält. Das reduziert erstens die
Gesamtzahl der Datentabellen und zweitens die Spaltenzahl. So stehen die Daten nicht
mehr nebeneinander sondern untereinander (Abbildung 7).
3.1.6 Bezeichnungen für die Spaltennamen wählen
Wie in Kap. 3.1.4 beschrieben, enthält die Tabelle ZUB_DATEN_TMP_TAB Spaltennamen, die den Hardwareadressen der Leitstation entsprechen. Diese Bezeichnungen
sind hinsichtlich der späteren Auswertung der Messdaten und der Wartbarkeit der
Datenbank nicht sinnvoll und der gewollten Nutzung der Redundanzen hinderlich. Die
Spalten mit der Kennung der Hardwareadresse enthalten implizit den Standort des
Sensors und seine Beschaffenheit (z.B. Temperatur oder Türkontakt). Ort und Beschaffenheit müssen entkoppelt werden. Die neue Kennung (Spaltenname) soll nur noch die
Beschaffenheit des Sensors enthalten; der Ort des Sensors steht in einer zusätzlichen
Spalte mit seiner Raumnummer (Kap. 3.1.5). Diese Aufteilung fördert auch die
Übersichtlichkeit und Wartbarkeit. Es ist einfacher, z.B. die Temperatur (Sensor) als
11
Konzept der Datenbank für das Zentrum für umweltgerechtes Bauen (ZUB)
Bezeichnung mit der Raumnummer 0_02 (R_Nr) zu wählen, als H00100 (die
Raumnummer muss über die Liste der ISPs ermittelt werden).
In Kapitel 3.2.4 befindet sich die genaue Beschreibung.
3.1.7 Anforderungen an die Leittechnik und die ZUB Datenbank
Hier werden die wichtigsten Anforderungen der Leittechnik zum Einen und zum
Anderen die Anforderungen an die Datenbank aufgelistet, damit ein reibungsloser
Ablauf weitgehend garantiert werden kann.
Anforderungen an die Leittechnik:
-
Jeder Messwert wird im 6 Minuten Takt erfasst.
-
Der abgelegte 6 Minuten Messwert ist ein Mittelwert aus 1 minütigen Messungen (nur bei analogen Werten).
-
Binärwerte werden über die Messdauer von 6 Minuten integriert und als Analogwerte abgelegt3.
-
Die Zeitangabe beginnt für jeden Messwert jeden Tag um 00:00 Uhr und endet
um 23:54 Uhr.
-
Die Daten werden mindestens 1* pro Tag in ZUB_DATEN_TMP_TAB kopiert;
bestenfalls am Tagesende (nach Erfassung des letzten Tageswertes mit 23:54
Uhr)
-
Die ständig aktualisierte Tabelle ZUB_DATEN_TMP_TAB enthält den Messwertsatz in einem Zeitraum von mindestens 14 Wochen.
-
Die Daten von mindestens 14 Wochen müssen gesichert sein.
-
Die Klimadaten werden mit in der Tabelle ZUB_DATEN_TMP_TAB abgelegt.
-
Die Zeiten der von der Leittechnik erfassten Messwerte und der Klimadaten sind
zu synchronisieren.
3
Man integriert den Binärwert, um den prozentualen Zustand innerhalb der 6 Minuten zu erfassen. Man
weiß nicht, wann und wie oft geschaltet wurde, aber es können genauere Aussagen über die Auswirkungen z.B. auf das Raumklima oder den Energieverbrauch getroffen werden.
12
Konzept der Datenbank für das Zentrum für umweltgerechtes Bauen (ZUB)
ZUB Datenbank:
-
Datum, Zeit und Raumnummer müssen immer vorhanden und eindeutig sein.
-
Bei Ausfall des Universitäts-Servers müssen die gesicherten Daten der Leittechnik nachträglich archivierbar sein.
-
Bei teilweisem Ausfall der Sensorik müssen die verbleibenden Daten archiviert
werden und die fehlenden automatisch in einem Fehlerprotokoll festgehalten
werden.
-
Bei totalem Ausfall der Sensorik muss dies automatisch in einem Fehlerprotokoll festgehalten werden.
-
Überschreitungen des Messbereichs müssen automatisch in einem Fehlerprotokoll festgehalten werden.
-
Es müssen beliebige Messpunkte bzw. ihre Messwerte nachträglich als fehlerhaft markiert werden können.
-
Die Rohdaten enthalten alle Messwerte, die einmal von der Leittechnik in die
Tabelle ZUB_DATEN_TMP_TAB geschrieben wurden.
-
Die korrigierten Rohdaten enthalten alle Daten die nicht manuell als fehlerhaft
markiert wurden.
3.1.8 Begriffe im weiteren Verlauf
Bei relationalen Datenbankmodellen - auf dem Oracle basiert – verwendet man einige
Begrifflichkeiten, die im Verlauf der Arbeit genutzt werden (Abbildung 3). Das Wort
Relation wird im sog. Relationsmodell für Tabelle verwendet. Der Relationsname ist
der Tabellenname. Ein Attribut einer Relation ist die Tabellenspalte (Spaltenname). Und
ein Tupel einer Relation entspricht einer Zeile in einer Tabelle, also Werte von gespeicherten Datensätzen. Ein Attributwert ist ein einzelner gespeicherter Wert innerhalb des
Attributes des Relation [3, 4].
13
Konzept der Datenbank für das Zentrum für umweltgerechtes Bauen (ZUB)
Abbildung 3: Relationsmodell
3.2 Aufbau und Übersicht
3.2.1 Aufbau der benötigten Datentabellen
Für eine Messwerterfassung sind - an sich - nur die "rohen" (unbearbeiteten) Daten
verlangt. Die Datensicherung macht allerdings nur Sinn, wenn diese Daten ausgewertet
werden. Für eine Auswertung müssen die "rohen" Daten der Datenbank aufbereitet
werden. Das geschieht durch eine automatische Fehlererkennung, die sicher nicht alle
Fehler findet, und durch eine ergänzende manuelle Fehlererkennung, bei der der Administrator die "rohen" Daten sichtet – auch die automatisch erkannten Fehler noch einmal
überprüft.
Speziell in dieser Anwendung werden die Rohdaten in 6 Minuten Intervallen gesichert,
das heißt, es werden täglich 240 Messwerte pro Tag mal ca. 700 Messpunkte gesichert.
Vor der Auswertung müssen Mittelwerte gebildet werden, damit Auswertprogramme
die Daten über Tage, Wochen, Monate und Jahre darstellen können. Die Tabellen mit
Mittelwerten werden in der Datenbank mit angelegt und gefüllt (wöchentliche Mittelwerte werden in dieser DB nicht berechnet). Diese Tabellen besitzen die gleichen
Spalten wie die der Rohdaten – ihre Attributwerte sind Mittelwerte. Es handelt sich um
Abbildungen der Tabelle mit den rohen Daten.
14
Konzept der Datenbank für das Zentrum für umweltgerechtes Bauen (ZUB)
Muttertabelle
vererbt ihre Spalten
<Tab_Name>
<Tab_Name>_ROH_TAB
Gelesen: Tabelle <Tab_Name>
mit rohen Daten (unbearbeiteten)
Abbildungen von
<Tab_Name>
Fehlerkorrektur
<Tab_Name>_ROHKORR_TAB
<Tab_Name>_TAG_TAB
<Tab_Name>_MONAT_TAB
<Tab_Name>_JAHR_TAB
Abbildung 4: Datentabellen und ihre Abbildungen
Abbildung 4 erschließt nicht nur die Verwendung von Abbildungen einer Tabelle,
sondern auch die Bezeichnungen, die in dieser Datenbank standardisiert wurden. Der
<Tab_Name> ist der eigentliche Tabellenname (z.B. MESSR für Messraum, STDR für
Standardraum etc.). In der Datenbank werden Abbildungen der Tabelle <Tab_Name>
erstellt, die später Messdaten sichern. Die Abbildungen enthalten den <Tab_Name> der
Muttertabelle und werden mit einem Index bzw. Anhang versehen, der verdeutlicht,
welche Daten in diesen Tabellen abgelegt werden.
3.2.2 Aufbau der benötigten Fehlertabellen
Wie schon in Kap. 3.2.1 beschrieben, werden die Fehlertabellen zur Datenaufbereitung
verwendet. Es gibt zwei verschiedene Arten von Fehlertabellen. Einmal die Fehlertabellen zur automatischen Fehlererkennung. Diese besitzen eine Kodierung, die den
15
Konzept der Datenbank für das Zentrum für umweltgerechtes Bauen (ZUB)
gefundenen Fehler beschreibt. Die anderen sind die inversen Fehlertabellen. Diese
enthalten nur die Werte NULL ("Nichts") für einen fehlerhaften Wert und eine 1 für
einen korrekten Wert. Sie werden später mit den Rohdaten multipliziert. Mehr dazu
befindet sich in den Kapiteln 4.2.3 "Anlegen / Ändern der Datentabellen
(DB_Tabellen_anlegen.sql)" und im Kapitel 5 "Bearbeitung der DB unter Oracle
Forms".
Die inversen Fehlertabellen <Tab_Name>_FEHL_INV_TAB sind keine Abbilder von
<Tab_Name>, wie die Datentabellen. Die Spaltennamen sind zwar identisch zu den
Datentabellen, aber sie besitzen ein anderes Datenformat. Sie benötigen keine Kommastellen oder ähnliches.
Die automatischen Fehlertabellen <Tab_Name>_FEHL_AUTO_TAB haben dasselbe
Spaltenformat wie die Inversen, sind aber ein Sonderfall. Sie enthalten zusätzlich zwei
weitere Spalten. Die Spalten sind Check_Flag und Bearbeiter. Erste zeigt an, ob der
Fehler vom Administrator gesichtet wurde. Die Zweite enthält den Namen des Administrators.
3.2.3 Struktur der Datenbank
Die Datenbank teilt sich in Messdatentabellen, Fehlertabellen und sogenannte Umgebungstabellen auf. Erstere enthalten entweder in irgendeiner Form Messdaten oder als
Fehler markierte Messdaten als Abbildung der Messdaten. Letztere enthalten Informationen über die Relationen und Attribute der Datenbank oder unterstützen die
Verarbeitung der Messdaten.
16
ZEITTAB
Skript:
Zeittab
FEHLER_CODE
fü llt
inträ g
We rte a n
W_
AD
Ta
e rs
tell
to
ü be
im m
t We rt
e
fü llt
Skript:
Ablaufstrg_manuell
rn
de
A NHANG
DATENTABELLEN:
<Tab_Name>_ROH_TAB und
<Tab_Name>_FEHL_A UTO_TAB
ä nd ert
e in
lt H
Skript:
Ablaufstrg_DB_Tabellen_anlegen
ho
R
en
th
ä
ZUORDNUNG
rä
nd
t
lt
Anhängsel:
_TMP_TAB
_FEHL_AUTO_TAB
_FEHL_INV_TAB
_ROH_TAB
_ROHKORR_TAB
_TAG_TAB
_MONAT_TAB
_JAHR_TAB
FEHL_TAB
Skript:
Fuelle_Fehl_inv_tab
DATENTABELLEN:
<Tab_Name>_FEHL_INV_TA B,
<Tab_Name>_ROHKORR_TAB,
<Tab_Name>TAG_TA B,
<Tab_Name>_MONAT_TAB,
<Tab_Name>_JA HR_TAB
er
thä
füllt
E
Be sch re ib t
ü be
Skript:
Ablaufstrg_taeglich
rgib t
ZUB_DATEN_TMP_TAB
p rü ft
t
lt
holt
Anhä
ngsel au
s
Funktion
zuordnung_pruefen
t Fehler in
finde
ho
l
t
We
rte aus
: Zugewiesener interner Name im ZUB
(Spaltennamen aus ZUB_DATEN_TMP_TAB)
R_Nr
: Raumnummer im ZUB
(laut Bauplan + weiteren Index)
Sensor
: Spaltenname in der Datenbank
Format
: Analog- oder Binärwert (A oder B)
Tab_Name : Tabellenname in der Datenbank
(z.B.: Klimadaten)
Minwert : Minimaler Sensorwert für den Messbereich
Maxwert : Maximaler Sensorwert für den Messbereich
en
Alle Datumseinträge, die laut
Spezifikation vorkommen dürfen.
aktuali
siert
prüft
sor aus
erstellt
oder ändert
f
prü
, Sen
aus
äl t
enth
b
e
am
_n
holt E
inträg
e
Hw_Adr
Konzept der Datenbank für das Zentrum für umweltgerechtes Bauen (ZUB)
Abbildung 5: Mind-Map der Datenbank
17
Konzept der Datenbank für das Zentrum für umweltgerechtes Bauen (ZUB)
In Abbildung 5 ist das Mind-Mapping der Datenbank zu sehen. Im Mittelpunkt der
Datenbank stehen die Tabellen ZUORDNUNG und ANHANG. Diese Tabelle enthalten
unter anderem alle Informationen über die Relationen und Attribute der Datentabellen,
in denen die Messwerte als Tupel gespeichert werden.
Diese Abbildung wird im Folgenden genau beschrieben. Dabei soll gleichzeitig anhand
eines Beispieles die Funktion und der Ablauf dargestellt werden.
3.2.4 Die Umgebungstabellen ZUORDNUNG und ANHANG
Diese Tabellen bilden das "Herz" der Datenbank. Der Sinn dieser Tabellen besteht
darin, dass alle Datentabellen automatisch anlegt werden können (Kap. 3.2.1) und die
Beziehungen der Tabellen untereinander festgelegt sind. Man erinnere sich, dass die
Datenbank so flexibel sein muss, dass sie einerseits jederzeit erweiterbar und änderbar
sein muss, ohne dass die Hardwareadressen der Messpunkte bisher bekannt sind.
Die Relation ZUORDNUNG enthält folgende Attribute:
Hw_Adr
Attribut der Relation ZUB_DATEN_TMP_TAB
R_Nr
Raumnummer im Gebäude
Sensor
Beschaffenheit des Sensors
Format
Analog- oder Binärwert
Tab_Name
Tabellenname für die Messdaten
Minwert
Minimaler Wert des Messwertes der Spalte Sensor in der Tabelle
<Tab_name>_ROH_TAB (dazu später mehr)
Maxwert
siehe MINWERT
Als Beispiel ist die Tabelle ZUORDNUNG mit folgenden Werten gefüllt.
Hw_Adr
H00100
H00101
H00102
H00103
H00104
H00105
R_Nr
0_01
0_01
0_01
0_01
0_01
0_01
Sensor
T_I
VENTIL_A
VENTIL_B
VENTIL_C
T_OBERFL0
T_OBERFL1
Format
A
B
B
B
A
A
Tab_Name
STDR
STDR
STDR
STDR
STDR
STDR
Minwert Maxwert
-10
50
0
1
0
1
0
1
-10
50
-10
50
18
Konzept der Datenbank für das Zentrum für umweltgerechtes Bauen (ZUB)
H00106
H00107
H00108
H00109
H00110
H00111
H00112
H00113
H00114
1_02
1_02
1_02
1_02
1_02
1_02
1_02
1_02
1_02
T_I
VENTIL_A
VENTIL_B
VENTIL_C
T_OBERFL0
T_OBERFL1
T_OBERFL0
T_OBERFL1
T_OBERFL2
A
B
B
B
A
A
A
A
A
STDR
STDR
STDR
STDR
STDR
STDR
MESSR
MESSR
MESSR
-10
0
0
0
-10
-10
-10
-10
-10
50
1
1
1
50
50
50
50
50
Ein Tupel von ZUORDNUNG enthält also gleich eine Vielzahl von Informationen. Z.B.
der Attributwert H00100 der Hw_Adr verrät in der Tabelle ZUB_DATEN_TMP_TAB
den Spaltennamen. Weiter ist angeben, dass sich der Sensor im Erdgeschoss im Raum
0_02 (<Etage>_<Raumnummer>) befindet und es sich um einen Temperatursensor
handelt, welcher analog (Format A) die Innentemperatur misst (T für Temperatur, I für
innen) und dieser in einem Standardraum (STDR) installiert ist. Gültige Werte liefert
der Sensor nur im Bereich –10 bis 50 Grad Celsius (falls er für Celsius kalibriert ist).
Die Tabelle ANHANG enthält:
ANHANG
Index bzw. Anhängsel für <Tab_Name> zur Erstellung aller Datentabellen (Kap. 3.2.1).
Anhang
_TMP_TAB
_FEHL_AUTO_TAB
_FEHL_INV_TAB
_ROH_TAB
_ROHKORR_TAB
_TAG_TAB
_MONAT_TAB
_JAHR_TAB
Die Indexe geben Aufschluss über die abgelegten Daten. TAB steht immer für Tabelle.
TMP
temporäre Daten
19
Konzept der Datenbank für das Zentrum für umweltgerechtes Bauen (ZUB)
FEHL_AUTO automatisch erkannte Fehler
FEHL_INV
inverse Fehlertabelle (mehr dazu ab Kap. 3.2.5.2)
ROH
Rohdaten
ROHKORR
korrigierte Rohdaten
TAG
Tagesmittelwerte
MONAT
Monatsmittelwerte
JAHR
Jahresmittelwerte
Die Tabelle ANHANG verknüpft mit der Tabelle ZUORDNUNG gibt Aufschluss
welche Tabellen sich in der Datenbank befinden und über die Daten in den Datentabellen.
Wenn man wieder mit der Hw_Adr H00100 beginnt, steht erstens Ort und Beschaffenheit des Sensors fest und zweitens, dass sich z.B. in <Tab_Name>_TAG_TAB - hier
STDR_TAG_TAB - die Tagesmittelwerte des Innentemperatursensors im Standardraum
0_02 befinden. Diese Werte sind nicht fehlerhaft markiert worden. Fehlerhafte Werte
stehen nur in den Roh- (<Tab_Name>_ROH_TAB) und temporären Daten
(<Tab_Name>_TMP_TAB).
Zusammenfassend kann gesagt werden, dass die Tabelle ZUORDNUNG alle Hintergrundinformationen enthält und die Tabelle ANHANG die Tabellennamen in der
Tabelle ZUORDNUNG ergänzt und dadurch alle Tabellen mit ihren Attributen bekannt
sind.
3.2.5 Weitere wichtige Umgebungstabellen
3.2.5.1 Die Zeitreferenztabelle ZEITTAB
Für den Betrieb der Datenbank werden noch einige Tabellen benötigt. Vor allem werden Tabellen benötigt, die Informationen über Fehler speichern oder für die Fehlererkennung wichtig sind. Hier werden die Wichtigsten beschrieben.
Die Tabelle ZEITTAB besitzt das Attribut Ref_Zeit (Referenzzeit) und enthält Datumseinträge im 6 Min. Intervall, die für die Messwerte gültig sind4. Das ermöglicht erstens
4
Die Deklaration DATE in Oracle beinhaltet Datum und Uhrzeit im 24 Std.-Format.
20
Konzept der Datenbank für das Zentrum für umweltgerechtes Bauen (ZUB)
die Suche nach fehlerhaften Datumseinträgen – das sind Abweichungen vom zulässigen
6 Min. Intervall. Zweitens wird die Suche nach fehlenden Datumeinträgen ermöglicht –
z.B. wenn die Leittechnik ausfällt oder die Übertragung gestört ist.
3.2.5.2 Tabellen FEHLER_CODE, FEHL_AUTO_ANZEIGE_TAB und FEHL_TAB
Die Tabelle FEHLER_CODE enthält die Attribute Nr (Nummer) und Fehlerart. In die
automatischen Fehlertabellen wird die Nummer eingetragen, um erstens einen numerischen Wert vorliegen zu haben, der einen schnellen Zugriff erlaubt, zweitens um
Speicherplatz zu sparen. Tritt ein Fehler 1000 mal auf, lohnt es nicht 1000 mal "Messwert nicht vorhanden" in eine Tabelle zu schreiben. Desweiteren sind die Fehler fest
definiert und es treten keine Redundanzen auf. Nach Installation der Datenbank sind
anfangs drei Einträge in dieser Tabelle, die für die automatische Fehlererkennung
verwendet werden.
Nr. Fehlerart
1
Datum nicht vorhanden.
2
Wert ist nicht vorhanden (ev. Sensorausfall).
3
Wert liegt nicht im Messbereich.
Während des Betriebs sollte die Fehlercodetabelle vom Benutzer weiter gefüllt werden,
vor allem wenn er weitere Fehler findet (manuelle Fehlerkontrolle). Ein anderer Benutzer muss wissen, was z.B. die Fehlernummern 4 oder 5 bedeuten.
Die Tabelle FEHL_AUTO_ANZEIGE_TAB fasst die automatisch gefundenen Fehler
aus den Tabellen <Tab_Name>_FEHL_AUTO_TAB zusammen. Der Benutzer erhält
schnell einen Überblick über gefundene Fehler. Er kann diese in den automatischen
Fehlertabellen kontrollieren, ohne alle Datensätze zu durchsuchen (Kap. 3.3.1.3). Das
PL/SQL Skript "Fehl_Auto_Anzeige.sql" durchsucht die Fehlertabellen und schreibt die
Ergebnisse in FEHL_AUTO_ANZEIGE_TAB (das Skript wird bei Bedarf manuell
(Kap. 5.5) gestartet). Die Fehlertabellen enthalten zusätzlich eine Spalte für das
Check_Flag plus eine für den Bearbeiter. Die Fehlereinträge bei denen das Check_Flag
21
Konzept der Datenbank für das Zentrum für umweltgerechtes Bauen (ZUB)
auf 1 gesetzt wurde und die einen Bearbeiter haben, werden beim Füllen von
FEHL_AUTO_ANZEIGE_TAB nicht berücksichtigt.
Beispiel FEHL_AUTO_ANZ_TAB:
Tabelle
Sensor
R_Nr
Anzahl
Anfang
MESSR
ALLE
ALLE
100
STDR
ALLE
ALLE
240
STDR
T_OBERFL0 0_01
98
STDR
T_OBERFL1 0_01
29
STDR
T_I
1_02
29
STDR
T_I
0_01
30
STDR
T_I
0_01
19
STDR
T_I
0_01
30
29.11.2000
00:06
29.11.2000
00:06
09.12.2000
00:06
09.12.2000
07:06
09.12.2000
07:06
29.11.2000
03:00
30.11.2000
00:06
10.12.2000
00:00
Ende
Fehler Check Bearbeiter
_Nr
_Flag
29.11.2000 1
02:54
29.11.2000 1
02:54
09.12.2000 2
09:54
09.12.2000 2
09:54
09.12.2000 2
09:54
29.11.2000 3
05:54
30.11.2000 3
01:54
10.12.2000 3
02:54
Hier ist die Tabelle schon gefüllt. Fehler wurden in den automatischen Fehlertabellen
von STDR und MESSR gefunden.
Beispiel: In der Fehlertabelle MESSR_FEHL_AUTO_TAB sind ALLE Sensoren in
ALLEN Räumen (R_Nr) ausgefallen bzw. der Fehler 1 aus FEHLER_CODE (Datum
nicht vorhanden) ist 100 mal aufgetreten. Dabei zeigt 100 an, dass das Datum insgesamt
für die Messwerte 100 mal fehlt; die Fehleranzahl berechnet sich pro Messwert. Hat das
Tupel 5 Datenspalten (plus 2 Spalten für Datum und R_Nr), wird bei einem fehlendem
Datum die Anzahl 5 ausgegeben. Das Attribut Check_Flag zeigt an, ob der Fehler
gesichtet wurde. Dieses wird vom Administrator gesetzt (Kap. 5).
Wichtig ist, dass Anfangszeitpunkt und Endzeitpunkt des Fehlers in der Tabelle stehen.
Es ist einstellbar, wie genau FEHL_AUTO_ANZEIGE_TAB gefüllt werden soll. Träte
theoretisch ein Fehler im 12 Min. Takt auf, so würde dieser Eintrag pro Tag 120 mal in
FEHL_AUTO_ANZEIGE_TAB geschrieben. Es reicht die Fehlerart mit frühestem und
letztem Auftreten einmal in die Tabelle zu schreiben. Standardmäßig wurde das Fehler-
22
Konzept der Datenbank für das Zentrum für umweltgerechtes Bauen (ZUB)
intervall auf 1 Stunde eingestellt5. Das heißt, erst wenn ein Fehler eine Stunde lang
nicht auftritt und dann wieder, erst dann wird er als Fehler neu eingetragen. Es werden
so in der Anzeigetabelle auch korrekte Messwerte als fehlerhaft abgebildet, aber sie
wird übersichtlicher und eine manuelle Überprüfung ist ohnehin nötig.
Bestätigt die Kontrolle, dass es sich tatsächlich um einen Fehler handelt, überträgt der
Administrator den Datensatz in die Tabelle FEHL_TAB. In FEHL_TAB stehen alle als
fehlerhaft erkannten Datensätze, also auch solche, die bei Sichtung der Daten vom
Administrator gefunden wurden. Ist FEHL_TAB vollständig, werden aus ihr die
Tabellen <Tab_Name>_FEHL_INV_TAB gefüllt, die eine 1 für einen korrekten und
eine NULL für einen fehlerhaften Wert beinhalten (NULL ist ungleich 0. Eine NULL
bedeutet soviel wie "leer" oder "nichts"). Multipliziert man die Rohdatentabellen mit
einer solchen Tabelle – beide haben ja die gleichen Spalten – erhält man die korrigierten
Rohdaten.
Beipiel: FEHL_TAB:
LFD
_NR
1
Tab_Name
Sensor
R_Nr
STDR
T_I
0_01
Fehler
_Nr
3
2
STDR
T_I
0_01
3
3
STDR
T_I
0_01
3
4
STDR
T_I
1_02
2
5
STDR
T_OBERFL1
0_01
2
6
STDR
T_OBERFL0
0_01
2
7
STDR
ALLE
ALLE
1
8
MESSR
ALLE
ALLE
1
Anfang
Ende
Bearbeiter Bem.
10.12.2000
00:00
30.11.2000
00:06
29.11.2000
03:00
09.12.2000
07:06
09.12.2000
07:06
09.12.2000
00:06
29.11.2000
00:06
29.11.2000
00:06
10.12.2000
02:54
30.11.2000
01:54
29.11.2000
05:54
09.12.2000
09:54
09.12.2000
09:54
09.12.2000
09:54
29.11.2000
02:54
29.11.2000
02:54
Name
Name
Name
Name
Name
Name
Name
Name
Die Tabellen FEHL_AUTO_ANZEIGE_TAB und FEHL_TAB werden in den nächsten
Kapiteln noch einmal aufgegriffen.
5
PROZEDUR_FORMS_FUELL_FEHL_AUTO_ANZEIGE.SQL Variable: fehlerintervall
Die Prozedur muss nach Änderung neu angelegt werden!
23
Konzept der Datenbank für das Zentrum für umweltgerechtes Bauen (ZUB)
3.3 Datenfluss und Verarbeitung
Die Architektur des Mind-Map in Abbildung 5 wird hier in der Abfolge des Datenflusses dargestellt (Abbildung 6).
Wie in den vorherigen Kapiteln beschrieben, liegen die Messdaten in der Tabelle
ZUB_DATEN_TMP_TAB mit den Attributen Hw_Adr aus ZUORDNUNG. Die Werte
aus ZUB_DATEN_TMP_TAB werden täglich aktualisiert und in die Tabellen
<Tab_Name>_TMP_TAB aufgeteilt (Abbildung 6). Vorher werden fehlerhafte bzw.
ungenaue Datumseinträge verbessert, um die 6 Minute Intervalle genau einzuhalten
(Kap. 3.3.1.2). Ist beispielsweise eine Messwertserie um 1 Minute "verrutscht", werden
diese Datumseinträge auf das 6 Minuten Intervall aus ZEITTAB (Kap. 3.2.5.1) angepasst.
<Tab_Name>_TMP_TAB enthält die Attribute Sensor und R_Nr aus ZUORDNUNG
plus eine Spalte für das Datum. Die Hw_Adr erhalten hier ihre neue Bezeichnung (Kap.
3.2.4). Die temporären Daten werden 1:1 in die Tabellen <Tab_Name>_ROH_TAB
(Rohdaten) aufgenommen und eventuelle automatisch gefundenen Fehler in die Tabellen <Tab_Name>_FEHL_AUTO_TAB aufgenommen 6.
6
Das geschieht, indem das Skript "Ablaufstrg_taeglich.sql" (tägliche Ablaufsteuerung) gestartet wird.
24
Konzept der Datenbank für das Zentrum für umweltgerechtes Bauen (ZUB)
Ablaufstrg_taeglich.sql
(Automatisierter Teil)
ZUB_DATEN_TMP_TAB
(Oracle)
Spalten mit Sauternamen
(entsprechen HW_ADR in ZUORDNUNG)
TÄGLICH
Datumseinträge
verbessern
<Tab_Name>_TMP_TAB
Mit korrigierten
Datumseinträgen
Wert vorhanden?
Datum
fehlt?
Messbereich?
<Tab_Name>_ROH_TAB
Tabellen (alle Werte)
Spaltennamen entsprechen
Sensor aus ZUORDNUNG
Eintragen in
<Tab_Name>_FEHL_AUTO_TAB
manuell gefüllt
Ablaufstrg_manuell.sql
(Bei Bedarf starten))
FEHL_TAB
Füllen der inverser
Fehlertabellen
(Fuelle_Fehl_inv_Tab.sql):
Korrekte Werte == 1
Fehlerhafte Werte == NULL
MANUELL
<Tab_Name>_FEHL_INV_TAB
Eintragen in
<Tab_Name>_ROHKORR_TAB
korrigierte
Tageswerte
bzw. Rohdaten
Tagesmittelwerte
Monatsmittelwerte
Jahresmittelwerte
Abbildung 6: Täglicher und manueller Ablauf
Sollen die Datensätze zur Auswertung freigeben werden, muss die Tabelle FEHL_TAB
gefüllt sein (Kap. 3.2.5.2). Aus ihr werden die inversen Fehlertabellen gefüllt und mit
den Rohdatentabellen multipliziert. Man erhält die korrigierten Rohdatentabellen
<Tab_Name>_ROHKORR_TAB (durch Starten von "Fuelle_Fehl_inv_Tab.sql" (Füllen
der inversen Fehlertabellen) werden die inversen Fehlertabellen gefüllt. Danach wird
25
Konzept der Datenbank für das Zentrum für umweltgerechtes Bauen (ZUB)
durch den Aufruf des Skriptes "Ablaufstrg_manuell.sql" (manuelle Ablaufsteuerung)
die inversen Fehlertabellen mit den Rohdatentabellen multipliziert). Jetzt folgen die
Mittelwertbildungen für die Tages, Monats- und Jahresmittelwerte. Hier endet die
Verarbeitung in der Datenbank. Die Auswertung beginnt.
3.3.1 Automatisierter täglicher Ablauf
Dieses Kapitel beginnt mit einem Beispiel, um den täglichen Datenfluss anschaulich zu
machen. Grundlegend sind hierfür die vorherigen Kapitel und die Abbildung 5 und
Abbildung 6. Dort sind zum einen die Verbindungen zwischen den Tabellen dargestellt,
zum anderen der Datenfluss im Ablaufdiagramm.
3.3.1.1 Ein Beispiel: Täglicher Datenfluss
Die Tabellen in diesem Beispiel wurden in den vorherigen Kapiteln beschrieben.
ZUORDNUNG enthält hier u.a. die Einträge aus Abbildung 7 (siehe auch das Beispiel
in Kap. 3.2.4). Aus den Einträgen werden die Spalten H00100 und H00112 in
ZUB_DATEN_TMP_TAB, die Spalten T_I in STDR_TMP_TAB, STDR_ROH_TAB,
STDR_FEHL_AUTO_TAB und die Spalte T_OBERFL0 in MESSR_TMP_TAB,
MESSR_ROH_TAB, MESS_FEHL_AUTO_TAB angelegt. Das sind die Datentabellen,
die für den täglichen Ablauf wichtig sind (Kap. 3.2 "Aufbau und Übersicht").
Gesetzt den Fall, dass die täglichen Messwerte in ZUB_DATEN_TMP_TAB aufgelaufen sind, werden diese auf die temporären Tabellen STDR_TMP_TAB und
MESSR_TMP_TAB verteilt. Die Verbindung, welche Spalte aus ZUB_DATEN_TMP_TAB welcher aus den Datentabellen zugeordnet ist, findet sich in der Tabelle ZUORDNUNG. Die Werte aus H00100 werden in die Tabelle STDR_TMP_TAB
in Spalte T_I mit der Raumnummer 0_01 eingetragen. Gäbe es – nicht in der Abbildung
– einen Eintrag in ZUORDNUNG, der auch auf die Spalte T_I verweist, aber eine
andere Hw_Adr und Raumnummer besitzt, würden diese Daten in die gleiche Spalte
eingetragen, jedoch mit einer anderen Raumnummer. Die Tabelle wird mit dieser
Vorgehensweise "länger", aber es werden Spalten wie z.B. T_I1, 2 oder 3 gespart, die
die Tabelle "breiter" und unübersichtlicher machen würden. Selbiges gilt für MESSR.
26
Konzept der Datenbank für das Zentrum für umweltgerechtes Bauen (ZUB)
ZUORDNUNG
Hw_Adr
H00100
Verweis
auf H00100
H00112
R_Nr Sensor
0_01 T_I
Format
A
1_02 T_Oberfl0 A
Tab_Name
STDR
Minwert
10
MESSR
20
Maxwert
50
45
ZUB_DATEN_TMP_TAB
Datum
Uhrzeit
01.01.2001 00:00
H00100
21,3
03.03.2001
21
06:06
H00112
44,4
Verweis
auf T_I
45,1
Daten mit Raumnummer
und neuer Bezeichnung
eintragen
STDR_TMP_TAB
Datum
01.01.2001 00:00
R_Nr
0_01
T_I
21,3
03.03.2001 06:06
0_01
21
STDR_ROH_TAB
Datum
R_Nr
T_I
01.01.2001 00:00
0_01
21,3
03.03.2001 06:06
0_01
21
STDR_FEHL_AUTO_TAB
Datum R_Nr
T_I
MESSR_TMP_TAB
Datum
R_Nr
01.01.2001 00:00 1_02
T_OBERFL0
44,4
03.03.2001 06:06
45,1
1_02
Check_flag Bearbeiter
Maximalwert
überschritten
Eintrag Fehler 3
in Fehlertabelle
MESSR_ROH_TAB
Datum
R_Nr
T_OBERFL0
01.01.2001 00:00
1_02
44,4
03.03.2001 06:06
1_02
45,1
MESSR_FEHL_AUTO_TAB
Datum
R_Nr
03.03.2001 06:06
1_02
T_OBERFL0
3
Check_flag Bearbeiter
NULL
NULL
Abbildung 7: Beispiel für den täglichen Datenfluss
Nachdem die temporären Daten in den Datentabellen (TMP) kopiert wurden, werden
diese 1:1 in den Tabellen STDR_ROH_TAB und MESSR_ROH_TAB gesichert.
Die temporären Daten werden jetzt auf mögliche Fehler geprüft. Das übernimmt die
"Automatische Fehlerkontrolle" in Kap. 3.3.1.3. Für MESSR_FEHL_AUTO_TAB ist
27
Konzept der Datenbank für das Zentrum für umweltgerechtes Bauen (ZUB)
das in Abbildung 7 dargestellt. Der Messbereich ist in ZUORDNUNG eingetragen und
wird im Falle einer Abweichung in die entsprechende Fehlertabelle mit einer "3" in der
Spalte mit Datum und Raumnummer eingetragen (siehe Tabelle FEHLER_CODE in
Kap. 3.2.5.2).
3.3.1.2 Verbessern ungenauer Datumseinträge aus ZUB_DATEN_TMP_TAB
Nachdem die Messdaten aktualisiert wurden, werden die Datumseinträge kontrolliert.
Das Datum aus ZUB_DATEN_TMP_TAB liegt noch nicht im Oracle Format vor. Da
ZUB_DATEN_TMP_TAB aus dBase-Tabellen konvertiert wurde, enthält diese Tabelle
das Datumformat aus dBase. Dort sind Datum und Uhrzeit jeweils in einer gesonderten
Spalte vorhanden. Oracle dagegen sichert unter der Deklaration DATE das Datum und
die Uhrzeit in einer einzigen Variablen.
Der Ablauf dieser Funktion ist in Abbildung 8 ersichtlich. Die Grundlage ist der
Vergleich zwischen den Einträgen aus ZEITTAB und der Datentabelle. Realisiert ist
diese Funktion, indem das Anfangs- und Enddatum aus ZUB_DATEN_TMP_TAB
gelesen wird und dieser Abschnitt aus ZEITTAB selektiert wird. Danach kann man
direkt vergleichen, ob es Abweichungen in den Datumseinträgen gibt (das übernimmt
die Funktion "Prozedur_Datum_verbessern.sql").
Falls mehrere Datumseinträge hintereinander ungenau sind, wird untersucht, welcher
Wert logisch der nähere ist. Dabei kann ein ungenaues Datum mehr als die Hälfte eines
Intervalls abweichen (> 3 Minuten) und es wird trotzdem richtig zugeordnet. Liegt
beispielsweise ein Wert 1 Minute vor der Referenzzeit und einer 1 Minute danach,
werden beide Werte übernommen. Bei einer Datumkorrektur, die die Werte immer auf
das naheliegende Datum setzt, ginge ein Wert von beiden verloren, da sie auf die
gleiche Referenzzeit verwiesen.
28
Konzept der Datenbank für das Zentrum für umweltgerechtes Bauen (ZUB)
ZUB_DATEN_TMP_TAB
Tabelle mit den aktuellen Messdaten
Datum,
Uhrzeit
(CHAR)
Anfangs- und Enddatum bestimmen
Konvertieren in
Oracle Format
+
Datum
(Oracle Format)
Gibt es Datumseinträge, die
Datum
in ZUB_DATEN_TMP_TAB
gefunden? sind, aber nicht in ZEITTAB?
Diese Datums sind falsch.
JA
NEIN
ENDE
Werte aus ZEITTAB
lesen
Nächstes Falsche Datumseinauslesen träge einzeln auslesen
NEIN
ENDE
Gefunden?
JA
Referenzdatums
auslesen
Es werden aus ZEITTAB die richtigen
Datumseinträge vor und nach dem
falschen Datum gelesen.
Referenzdatum vor
falschem Datum
NEIN
Schon
vorhanden?
JA
Ist das erste richtige Datum aus ZEITTAB
in ZUB_DATEN_TMP_TAB vorhanden?
Wenn nein, nehme das richtige Datum davor;
wenn ja, nehme das richtige Datum danach;
ersetze das Datum in ZUB_DATEN_TMP_TAB.
Referenzdatum nach
falschem Datum
Schon
vorhanden?
JA
NEIN
ZUB_DATEN_TMP_TAB
aktualisieren
Das falsche Datum in
ZUB_DATEN_TMP_TAB
wird durch das Refenenzdatum ersetzt.
Abbildung 8: Verbessern ungenauer Datumseinträge in ZUB_DATEN_TMP_TAB
29
Konzept der Datenbank für das Zentrum für umweltgerechtes Bauen (ZUB)
Der Nachteil eines solchen Vorgehens kommt zum Vorschein, wenn erst Datumseinträge vollständig fehlen und dann ein ungenaues Datum auftritt. Weicht das
ungenaue Datum 1 Minute von der Referenzzeit ab, würde es in so einem Fall einer
Referenzzeit früher zugeordnet und auf dem Wert 5 Minuten früher gesetzt. Dieser
Fehler ist allerdings unwahrscheinlich.
3.3.1.3 Automatische Fehlerkontrolle
In Abbildung 9 ist wieder der gesamte tägliche Datenfluss dargestellt, nur diesmal mit
besonderem Blick auf die automatische Fehlerkontrolle (Skript "Fehler_eintragen.sql").
Zuerst werden in ZUB_DATEN_TMP_TAB die ungenauen Datumseinträge verbessert
(siehe vorherigen Abschnitt) und in die temporären Datentabellen und Rohdatentabellen
übernommen. Bis hierhin wurde alles schon beschrieben. Die Tabellen mit temporären
Daten <Tab_Name>_TMP_TAB werden jetzt automatisch untersucht. Gefundene
Fehler werden in die Tabellen <Tab_Name>_FEHL_AUTO_TAB eingetragen.
Zurst wird geprüft, ob alle Datumseinträge vorhanden sind (Fehler "1" in FEHLER_CODE Kap. 3.2.5.1). Das geschieht ähnlich wie bei dem Verbessern der ungenauen Datumseinträge. Fehlt das Datum oder die Uhrzeit in ZUB_DATEN_TMP_TAB,
fehlt es für alle Räume und alle Sensoren, da hier alle Messdaten gesammelt sind. In
diesem Fall werden alle Messdatenfehlerspalten der automatischen Fehlertabellen mit
einer 1 markiert.
Die zweite Prüfung ist, ob es eine Sensorausfall oder ähnliches gegeben hat. Das heißt,
dass Datum und die Raumnummer vorhanden sind, aber ein oder mehrere Messwerte
fehlen. Dieser Fehler wird mit einer 2 quittiert.
Die letzte Prüfung ist die des Messbereiches. Hier wird geprüft, ob die Werte in dem
Bereich liegen, der in ZUORDNUNG (Kap. 3.2.4) in Minwert und Maxwert angegeben
ist. Liegt der Messwert nicht in diesem Bereich, wird der Fehler 3 eingetragen.
30
Konzept der Datenbank für das Zentrum für umweltgerechtes Bauen (ZUB)
Prozedur:
datum_verbessern()
ZUORDNUNG
Siehe Abbildung:
Prozedur: datum_verbessern
Tabellenname (Tab_Name),
Spaltenname (Sensor)
Raumnummer (R_Nr),
Sautername (Hw_Adr)
Minimalwert (Minwert)
Maximalwert (Maxwert)
Neuestes Datum
bei bestimmter R_Nr
<Tab_Name>_ROH_TAB
<Tab_Name> _TMP_TAB
Eintragen in
Rohdatentabelle
Prüfen, ob alle Datums vorhanden sind.
Fehler “1” eintragen in
<Tab_Name>_FEHL_AUTO_TAB
NEIN
Restliche Werte
Fehler “2” eitragen in
<Tab_Name>_FEHL_AUTO_TAB
NEIN
Restliche Werte
Fehler “3” eintragen in
<Tab_Name>_FEHL_AUTO_TAB
NEIN
Restliche Werte
Datum
vorhanden?
JA
ZEITTAB
Zeittabelle mit Refernzdatums
Prüfen, ob alle Werte vorhanden sind
Werte
vorhanden?
JA
Messbereich?
Zwischen
Min- und Maxwert?
JA
ENDE
Abbildung 9: Automatische Fehlerkontrolle
3.3.2 Manueller Ablauf mit Blick auf die Tabelle FEHL_TAB
Die manuelle Ablaufsteuerung wird gestartet, wenn die inversen Fehlertabellen aus
Tabelle FEHL_TAB (Kap. 3.2.5.2) gefüllt sind (Skript "Fuelle_Fehl_inv_tab.sql").
Hier soll auf das Füllen der inversen Fehlertabellen <Tab_Name>_FEHL_INV_TAB
eingegangen werden; der Rest – sprich die Mittelwertbildung - wird im folgenden
Kapitel beschrieben.
Realisiert wird dieser Prozess, indem erst einmal alle Datumseinträge und Raumnummern aus <Tab_Name>_ROH_TAB übernommen werden und in alle übrigen Spalten
31
Konzept der Datenbank für das Zentrum für umweltgerechtes Bauen (ZUB)
des Tupels eine 1 eingetragen wird7. Die fehlerhaften Werte werden danach auf NULL
aktualisiert.
Hier sei noch einmal die Information in der Tabelle FEHL_TAB aufgegriffen. Am
einfachsten ist die Beschreibung anhand eines Beispieles.
LFD
_Nr
1
Tab_Name
Sensor
R_Nr
STDR
T_I
0_01
Fehler
_Nr
3
Anfang
Ende
Bearbeiter Bem.
10.12.2000 10.12.2000 Name
00:00
02:54
Die Lfd_Nr, die Fehlernummer (Fehler_Nr), der Bearbeiter und die Bemerkung (Bem.)
interessieren nur den Administrator.
Tab_Name, Sensor, R_Nr, Anfang und Ende sind die relevanten Informationen zum
Füllen der inversen Fehlertabellen.
STDR (Standardraum), T_I (Innentemperatur), Raum 0_01 hat irgendeinen Fehler
zwischen dem 10.12.2000 ab 00:00 Uhr bis zum 10.12.2000 ab 02:54 Uhr (s.o.). Diese
Werte werden mit einer NULL überschrieben; alle anderen dieses Wertes bleiben auf 1
(korrekt).
Das Enddatum kann auch freigelassen werden (NULL sein). Es wird dann so behandelt,
als wäre es der 01.01.2050 00:00 Uhr. Dieses Datum wurde frei gewählt und kann noch
geändert werden. Ein solcher Fall tritt ein, wenn der Fehler bekannt ist, aber nicht der
Zeitpunkt seiner Behebung.
FEHL_TAB kann als Eintrag"ALLE" in Sensor und R_Nr enthalten oder diese können
genau spezifiziert sein. Das bedeutet, es sind vier verschiedene Prozesse zu beachten.
7
Mit der Option DEFAULT 1 auf eine Spalte beim Anlegen der Tabelle, wird diese automatisch mit
einer 1 gefüllt, wenn ein beliebiger Wert – außer NULL - in eine andere Spalte des Tupels eingetragen
wurde.
32
Konzept der Datenbank für das Zentrum für umweltgerechtes Bauen (ZUB)
Möglichkeiten
Tabelle
Sensor
R_Nr
1
<Tab_Name>
Angegeben
Angegeben
2
<Tab_Name>
ALLE
Angegeben
z.B. (STDR)
3
<Tab_Name>
(z.B. 0_01)
Angegeben
ALLE
(z.B. T_I)
4
<Tab_Name>
ALLE
ALLE
Möglichkeit 1 wurde oben beschrieben. Wir gehen weiter davon aus, dass die inversen
Fehlertabellen alle Raumnummern und Datumeinträge enthalten.
Möglichkeit 2 zeigt an, dass "ALLE" Sensoren des Raumes z.B. 0_01 fehlerhaft sind.
Das heißt, die Zeilen zwischen Anfang- und Enddatum werden für die Raumnummer in
<Tab_Name>_FEHL_INV_TAB (z.B. STDR_FEHL_INV_TAB) entfernt.
Möglichkeit 3 sagt, dass in allen Räumen der Sensor z.B. T_I falsch ist. Folglich wird
der Inhalt der Spalte T_I in den inversen Fehlertabellen - zwischen dem Anfangs- und
Enddatum in STDR_FEHL_INV_TAB - auf NULL gesetzt.
Möglichkeit 4 löscht alle Datumseinträge zwischen dem Anfangs- und dem Enddatum
in STDR_FEHL_INV_TAB. Das geschieht, wenn ein Datum oder eine Uhrzeit in
ZUB_DATEN_TMP_TAB fehlen.
33
Realisierung
4 Realisierung
Dieses Kapitel beschreibt die Realisierung der Datenbank mit ihren Skripten und
Meldungen. Besonderes Augenmerk wird auf die Konsistenz aller Tabellen gelegt.
Es soll möglichst wenig auf die Programmierung in SQL, PL/SQL und DBMS_SQL
eingegangen werden.
DBMS_SQL ist dynamisches PL/SQL, das heißt, Anweisungen werden zur Laufzeit
geparst und dann ausgeführt. Diese Art der Programmierung musste oft angewendet
werden, da die Tabellen erst während der Laufzeit angelegt bzw. geändert werden, so
dass die Einträge aus ZUORDNUNG weitmöglichst konsistent sind (siehe Kap. 4.3).
Die Literatur behandelt dieses oft unzureichend. Am ausführlichsten ist DBMS_SQL in
"Documentation Library for Oracle8 Release 8.0.5" [1] beschrieben. Es ist dort unter "> Application Development -> Oracle8 Application Developer's Guide Release 8.0 ->
14 Using Dynamic SQL" zu finden.
4.1 Anlegen der Datenbank
4.1.1 Verzeichnisse
Die Skripte der Datenbank sind auf mehere Verzeichnisse verteilt.
Hauptverzeichnis D:\ZUB_DB
Unterverzeichnisse
...\SQL
Skripte für das Anlegen des täglichen und manuellen Ablaufs
der Datenbank.
...\Forms
Enthält die Eingabemaske unter Oracle Forms und die Skripte
zur Ansicht unter der Form´s Maske.
Enthält auch das Skript "Fuelle_Fehl_Inv_Tab.sql", welches
aus der Maske gestartet wird. Dazu mehr in Kap. 5.
...\Meldungen Hier werden alle Meldungen gesammelt, die von verschiedenen
Skripten ausgeben werden. Sie werden in den folgenden Kapiteln
beschrieben.
...\Test
Skripte für den Test der Datenbank. Beim Betrieb der DB sind
34
Realisierung
diese nicht relevant.
...\DB_Indx
Enthält drei physikalische Dateien ("Tablespace"), in denen
die Benutzerdaten, Indexe und die Messdaten abgelegt sind.
Soll die Datenbank in einem anderen Verzeichnis angelegt werden, müssen die SQL
Skripte und die Kodierung unter Forms durchsucht werden und das Hauptverzeichnis
dort geändert werden. Die Unterverzeichnisse sollten gleich bleiben. Die folgenden
Abschnitte müssen dann so gelesen werden, als wäre das Hauptverzeichnis ein anderes.
!!! Das Ändern der Verzeichnisse funktioniert nur vor dem Anlegen der Datenbank. Die
Messdaten und Indexe gehen sonst verloren !!!
4.1.2 Vorbereitung
Um die Umgebungstabellen und alle Datentabellen anzulegen, sind nach Installation
von Oracle zwei zusätzliche TABLESPACE [2, 4] unter dem Verzeichnis d:\ZUB_DB\
Indx_DB mit den Namen "ZUB_DATEN" und "INDX" anzulegen. "INDX" enthält
Indexe auf Tabellen sowie die Constraints [1 - 6] der Tabellen. Ein gut angelegter Index
auf eine Tabelle verbessert die Performance. "ZUB_DATEN" enthält die Messdaten.
Der TABLESPACE "USR" wird in das Verzeichnis verschoben. Er enthält die Benutzerinformationen. Alle drei Änderungen werden am besten mit dem "Enterprise
Manager" von Oracle [1] vorgenommen.
4.1.3 Die Datenbank neu anlegen (Ablaufstrg_DB_neu_anlegen.sql)
Die Skripte mit dem Präfix "Ablaufstrg_" steuern den Ablauf der Verarbeitung und
rufen ihrerseits Skripte auf. Beim Anlegen der Datenbank muss sehr vorsichtig vorgegangen werden. Hier wird die Grundlage für die Funktionstüchtigkeit gelegt. Ist die
Datenbank falsch angelegt und sind Messdaten abgespeichert, ist eine Änderung bzw.
Korrektur sehr schwierig und zeitintensiv. Wichtig für das Anlegen ist das Skript
"Zuordnung.sql" (Abbildung 10). Hier sind Einträge, aus denen ZUB_DATEN_TMP
geändert wird und alle Datentabellen angelegt werden. "Zuordnung.sql" enthält die
Einträge für die Tabelle ZUORDNUNG, die in "Umgebungstabellen.sql" angelegt wird.
"Umgebungstabellen.sql" löscht alle Umgebungstabellen und legt sie neu an. In
"InitUmgebungstabellen.sql" werden einige Tabellen initialisiert. Die wichtigsten sind
35
Realisierung
ANHANG (Kap. 3.2.4) und FEHLER_CODE. Ist ZUORDNUNG gefüllt, werden alle
notwendigen Funktionen und Prozeduren für den Betrieb angelegt [1, 2, 4].
DB_Tabellen_loeschen.sql
Umgebungstabellen.sql
InitUmgebungstabellen.sql
Zuordnung.sql
Datenbank_neu_anlegen.txt
Dateien mit Informationen
im Verzeichnis “Meldungen”
Datenbanktabellen.txt
Funktionen und
Prozeduren anlegen
DB_Tabellen_anlegen.sql
Fehler?
NEIN
Ende
JA
Löschen aller Datentabellen
<Tab_Name> und die Tabellen
<Tab_Name> plus <Anhang>
Löschen und neu anlegen
aller Umgebungstabellen,
inkl. ZUORDNUNG und
ZUB_DATEN_TMP_TAB
Initialisieren der Umgebungstabellen
FEHLER_CODE
ANHANG
ZUORDNUNG_GEAENDERT
Vorbereite Tabelleneinträge für
ZUORDNUNG.
Anlegen aller Funktionen und Prozeduren, die während des Ablaufs der
Datenbankbenötigt werden.
Anlegen der Datentabellen
<Tab_Name> und <Tab_Name>
plus Anhang bei erfolgreicher
Prüfung von ZUORDNUNG..
Wenn ein Fehler beim Anlegen der
Tabellen aufgetreten ist, muss der
Fehler in ZUORDNUNG korrigiert
und das Skript erneut gestartet
werden.
Das Skript Zuordnung.sql darf man
nicht gesondert ausführen. U nter
Umständen können die Datenbanktabellen dann nicht mehr
automatisch gelöscht werden.
Abbildung 10: Anlegen der Datenbank
Der Leser wird sich fragen, warum die Datentabellen zuerst gelöscht werden, obwohl
sie ja noch nicht existieren ("DB_Tabellen_loeschen.sql")? Das liegt an dem Skript
"DB_Tabellen_anlegen.sql", dem ein eigenes Kapitel gewidmet ist. Dort wird versucht
die Datenbanktabellen anzulegen. ZUORDNUNG muss fehlerfrei sein, bevor die
Tabellen angelegt werden. ZUORDNUNG kann nur auf Syntax und Plausibilität geprüft
werden, nicht aber auf einen versehentlichen Eintrag wie STRD anstelle von STDR im
der Spalte Tab_Name. Diese Ungenauigkeit hat zur Folge, dass die Tabellen STRD und
ihre Abbildungen angelegt werden. Der Administrator schaut sich im Verzeichnis
36
Realisierung
...\Meldungen die Dateien "Datenbank_neu_anlegen.txt" und "Datenbanktabellen.txt"
an und prüft diese auf Fehlermeldungen. "Datenbanktabellen.txt" enthält alle Fehler aus
der Prüfung von ZUORDNUNG, bzw. alle angelegten Datentabellen aus ZUORDNUNG. Findet der Administrator einen Fehler oder eine versehentlich angelegte
Tabelle, ändert er "Zuordnung.sql" (ohne es auszuführen) und startet das Skript
"Ablaufstrg_DB_neu_anlegen.sql" erneut. Alle Tabellen werden gelöscht und neu
angelegt. Dieser Vorgang wiederholt sich bis der Administrator sicher ist, dass alle
Tabelle korrekt angelegt wurden.
ACHTUNG:
Am Ende sollten die Dateien "DB_Tabellen_loeschen.sql", "Zuordnung.sql", "Umgebungstabellen.sql" und "InitUmgebungstabellen.sql" gesichert und aus dem Verzeichnis
entfernt werden, damit nicht versehentlich eines dieser Skripte gestartet wird und es zu
einem Datenverlust kommt!
37
Realisierung
4.2 Datenbanktabellen anlegen/ändern
(Ablaufstrg_DB_Tabellen_anlegen.sql)
Meldungen über Fehler oder Erfolg sind in der Datei "Datenbanktabellen.txt" im
Verzeichnis ...\Meldungen zu finden.
4.2.1 Übersicht
Das Skript "DB_Tabelle_anlegen.sql" ist ein wichtiger Teil der Datenbank. Es ist für
die Konsistenz der Datenbanktabellen, wie sie in ZUORDNUNG stehen, verantwortlich. Der Ablauf ist in Abbildung 11 dargestellt.
Einerseits werden die Datentabellen angelegt bzw. geändert, andererseits wird die
Tabelle ZUB_DATEN_TMP_TAB geändert. ZUB_DATEN_TMP_TAB wurde in den
Umgebungstabellen mit den Attributen Datum und Uhrzeit angelegt (Kap. 4.1.3). Die
zukünftigen Attribute von ZUB_TMP_DATEN müssen den Attributwerten des Attributes Hw_Adr aus ZUORDNUNG entsprechen, damit alle Daten übernommen werden.
Genauso verhält es sich mit den Datentabellen. Tab_Name, Sensor, Format aus
ZUORDNUNG und der Inhalt der Tabelle ANHANG sind notwendig, diese Tabellen
konsistent zu ZUB_DATEN_TMP_TAB zu gestalten (Kap. 4.2.3).
Bevor das geschieht, muss ZUORDNUNG vorgeprüft werden, damit Obiges gewährleistet ist (Kap. 4.2.2).
38
Realisierung
ZUORDNUNG
zuordnung_pruefen( Aufruf mit FLAG ‘ANLEGEN’
‘ANLEGEN’)
siehe Funktion zuordnung_pruefen()
ZUB_DATEN_TMP_TAB
aus ZUORDNUNG
ändern
NEIN
Fehler
gemeldet?
JA
Keine Tabellen
anlegen oder ändern.
1) Fehlerausgabe
2) ENDE
zuordnung_pruefen(
‘ALLES’)
Fehler
gemeldet?
JA
NEIN
1) Fehlerausgabe
Keine Tabellen
2) ENDE
anlegen oder ändern.
Hier dürften nach dem Konzept keine Fehler mehr auftauchen;
Prüfung gilt der Datensicherheit und -konsistenz
Detailierte Dargestellung unter Abbildung 13: Nächster
Tabellenname
Ändern und Anlegen der Datentabellen
(DB_Tabellen_Anlegen.sql)
ZUORDNUNG
Ende
NEIN
Gefunden?
JA
Tabellen anlegen
mit allen Spalten
Spalten einfügen
NEIN
NEIN
Tabelle
vorhanden?
JA
Tabellennamen
Daten- und
Fehlertabellen
SpaltenSpalte namen
vorhanden?
JA
Abbildung 11: Übersicht Ablaufsteuerung Tabellen anlegen/ändern
4.2.2 Zuordnung prüfen (Funktion_Zuordnung_pruefen.sql)
Die Funktion "zuordnung_pruefen(<option>)" wird aufgerufen, bevor irgendwelche
Tabellen geändert oder angelegt werden. Sie besteht aus fünf Teilen (Abbildung 12).
39
Realisierung
ZUORDNUNG
NEIN
Hw_Adr
mehrfach?
JA
Fehlerausgabe
Fehler Bit 1
Wurde ein Spalte aus
ZUB_DATEN_TMP_TAB mehrfach eingegeben?
NEIN
R_Nr,
Sensor?
JA
Fehlerausgabe
Fehler Bit 2
Sind gleichzeitig Raumnummern
und Sensoren gleich?
NEIN
Spalten
in?
Fehlerausgabe
Fehler Bit 3
JA
Sind alle Hw_Adr aus
ZUORDNUNG in ZUB_DATEN_TMP_TAB?
JA
FLAG =
‘ANLEGEN’?
Wird bei Aufruf der Funktion
‘ANLEGEN’übergeben, wird diese
Prüfung übersprungen.
NEIN
JA
Spalten
Fehlerausgabe
in?
Fehler Bit 4
Sind alle Spalten aus
ZUB_DATEN_TMP_TAB in ZUORDNUNG?
NEIN
NEIN
Syntax
ok?
JA
Fehlerausgabe
Fehler Bit 5
Sind andere Zeichen wie 0-9, A-Z oder
ein _ in ZUORDNUNG?
RETURN 0
NEIN
Fehler?
JA
Sind oben Fehler aufgetreten?
ENDE
RETURN
Fehler-Bits
Fehler addieren
und ausgeben.
Bei Fehlern werden keine DB
Tabellen angelegt oder verändert.
Abbildung 12: ZUORDNUNG auf mögliche Fehler prüfen
40
Realisierung
Schritt 1
ist die Prüfung, ob der Attributwert aus Hw_Adr mehrmals vorkommt. Ist das
der Fall, müsste diese Spalte mehrfach in ZUB_DATEN_TMP_TAB angelegt
werden. Da das nicht sein kann, wird ein Fehler gesetzt.
Schritt 2
prüft in ZUORDNUNG die Attributwerte aus R_Nr und Sensor. Kommt ein
Attributwert aus Sensor doppelt vor, ist das nicht unbedingt falsch. Z.B. werden
in den Standardräumen die Innentemperaturen mit den gleichen Sensoren
gemessen. Ein Fehler ist es nur, wenn sich dieser Sensor mehrmals im gleichen
Raum befindet. Dann müsste die Spalte mit dem gleichen Namen mehrfach
angelegt werden, was nicht möglich ist.
Schritt 3
ist eine Gegenprüfung von ZUB_DATEN_TMP_TAB und ZUORDNUNG. Es
wird abgefragt, welche Attribute ZUB_DATEN_TMP_TAB besitzt und welche
Attributwerte unter Hw_Adr in ZUORDNUNG stehen. Diese werden verglichen.
Hier wird nur geprüft, ob sich andere Spalten in ZUB_DATEN_TMP_TAB
befinden, als sich Verweise in ZUORDNUNG befinden. In ZUORDNUNG
können dabei mehr Verweise stehen als Spalten in ZUB_DATEN_TMP_TAB.
Das heißt, es wird eine Untermenge abgefragt.
Schritt 4
ist invers zu Schritt 3. Hier können mehr Spalten in ZUB_DATEN_TMP_TAB
sein, als in ZUORDNUNG angegeben. Sollten es weniger sein, wird ein Fehler
gemeldet. Diese Prüfung muss übersprungen werden, wenn ZUB_DATEN_TMP_TAB geändert werden soll. Das ist logisch, denn wenn ein neuer
Eintrag in ZUORDNUNG steht, kann diese Spalte schließlich noch nicht in
ZUB_DATEN_TMP_TAB stehen.
Schritt 3 und Schritt 4 ergänzen sich, sodass beide Tabellen konsistent sein
müssen.
Schritt 5
ist die Abschlussprüfung. Hier werden alle Spalten aus ZUORDNUNG geprüft
die keine numerischen Werte enthalten (alle außer Minwert, Maxwert). In
41
Realisierung
ZUORDNUNG sind nur Zeichen von 0-9,A-Z und der Unterstrich "_" zulässig.
Kommen andere Zeichen vor, wird ein Fehler gemeldet.
Wird eine anderer Wert als "0" von dieser Funktion zurückgegeben, werden keine
Tabellen angelegt oder verändert.
"Zuordnung_pruefen(<option>)" wird bei jedem Ändern oder Anlegen von Tabellen
zweimal aufgerufen. Bei der ersten Prüfung, wird die <option> 'ANLEGEN' übergeben.
Schritt 4 wird übersprungen und - falls keine anderen Fehler auftreten – die Tabelle
ZUB_DATEN_TMP_TAB geändert. Danach werden alle Prüfungen noch einmal
ausgeführt. Im fehlerfreien Fall werden dann die Datentabellen angelegt oder verändert.
Hier sei noch einmal darauf hingewiesen, dass diese Prüfungen nicht vor versehentlichen Eingaben schützen. Sind die Tabellen erst einmal geändert, können diese
Änderungen nicht mehr bzw. nur unter großem Aufwand zurückgenommen werden.
4.2.3 Anlegen / Ändern der Datentabellen (DB_Tabellen_anlegen.sql)
Hier wird der schraffierte Bereich aus Abbildung 11 genauer betrachtet.
Zuerst werden die Tabellennamen <Tab_Name> aus ZUORDNUNG selektiert. Gibt es
diesen Tabellennamen noch nicht, müssen alle erforderlichen Datentabellen angelegt
werden (Abbildung 13 links). Als ersten Schritt werden die Tabellen <Tab_Name>,
<Tab_Name>_FEHL_AUTO_TAB, <Tab_Name>_FEHL_INV_TAB mit den Spalten
Datum und Raumnummer R_Nr angelegt. Zusätzlich werden in den Tabellen
<Tab_Name>_FEHL_AUTO_TAB Spalten Check_Flag und Bearbeiter eingefügt
(Verwendung unter Kap. 5.1). Die Fehlertabellen werden gleichzeitig mit <Tab_Name>
angelegt, da das Format der in ZUORDNUNG referenzierten Spalte (Sensor) nicht dem
der übrigen Messdatentabellen entspricht. Das Format dieser Spalten ist NUMBER(2)8.
8
NUMBER(2) bedeutet Zahlen von -99-99 ohne Komma. Es handelt sich also um ein spezielles Integer-
Format.
42
Realisierung
DB_TAB_ANLEGEN
(Datenbanktabellen anlegen)
Nächster
Tabellenname (<Tab_Name>),
Spaltenname (Sensor)
Spalten:
Tabelle mit
Datum, R_Nr <Tab_Name> anlegen
NEIN
Tabellen anlegen
<T ab_Name>_FEHL_AUTO_TAB
<T ab_Name>_FEHL_INV_TAB
Sensor aus
Spaltenname ZUORDNUNG
entspicht Sensor
Alle
bearbeitet?
ZUORDNUNG
Tabelle
vorhanden?
Spalten:
Datum, R_Nr,
Check_Flag,
Bearbeiter
Alle übrigen Spalten mit
Format NUMBER(2)
(siehe unten))
Spalten:
Datum, R_Nr
Tabellen kopieren von <Tab_Name>
<Tab_Name> _TMP_TAB
<Tab_Name> _ROH_TAB
<Tab_Name> _ROHKORR_TAB
<Tab_Name> _TAG_TAB
<Tab_Name> _MONAT_TAB
<Tab_Name> _JAHR_TAB
JA
NEIN
Spalte Sensor anlegen in
<T ab_Name> _FEHL_AUTO_TAB
<T ab_Name>_FEHL_INV_TAB
(Format NUMBER(2))
NEIN
Binärwert
Format
NUMBER(1)
Analogwert?
NEIN
JA
Analogwert
Format
NUMBER(5,2)
nächste
Spalte Sensor
auswählen
Spalte
vorhanden?
Spalte Sensor anlegen in
<Tab_Name> _FEHL_AUTO_TAB
<T ab_Name>_FEHL_INV_TAB
(Format NUMBER(2))
NEIN
Binärwert
Spalte Sensor mit gewählten
Format anlegen in <T ab_Name>
JA
Format
NUMBER(1)
Analogwert?
JA
JA
Analogwert
Format
NUMBER(5,2)
Spalte Sensor einfügen in
<Tab_Name>_TMP_TAB
<Tab_Name>_ROH_TAB
<Tab_Name>_ROHKORR_TAB
<Tab_Name>_TAG_TAB
<Tab_Name>_MONAT_TAB
<Tab_Name>_JAHR_TAB
Alle NEIN
bearbeitet?
JA
Abbildung 13: Ändern und Anlegen der Datentabellen (DB_Tabellen_Anlegen.sql)
Jetzt werden die Attributwerte Sensor aus ZUORDNUNG in die Tabelle <Tab_Name>
eingefügt. Weiter ist in ZUORDNUNG unter dem Attribut Format angegeben, ob es
43
Realisierung
sich um einen Analog- oder Binärwert handelt. Ist es ein Analogwert, wird die Spalte
mit dem Format NUMBER(5,2) angelegt. Das heißt, die Zahl kann insgesamt fünf
Zeichen lang sein – ohne Berücksichtigung des Vorzeichens -, wobei 2 Zeichen
Nachkommastellen sind. Das entspricht einem Wertebereich von –999.99 bis 999.99.
Handelt es sich um einen Binärwert, ist das Format NUMBER(1) mit einem Wertebereich9 von –9 bis 9. Sind alle Spalten eingetragen, werden die übrigen Datentabellen
als Abbild von <Tab_Name> erstellt und der nächste Tabellenname ausgewählt. Die
Prozedur wiederholt sich, wenn der nächste gefundene Name wieder eine noch nicht
angelegte Tabelle ist.
Ist die Tabelle allerdings schon vorhanden, werden die Spaltennamen (Attributwert von
Sensor) auf Vorhandensein abgefragt. Wird ein Spaltenname gefunden, der sich noch
nicht in <Tab_Name> befindet, werden diese Spalten, wie vorher beschrieben, in die
Datentabellen eingefügt.
Bemerkung:
Eine Schwäche dieser Vorgehensweise ist, dass nur die Tabelle <Tab_Name> abgefragt
wird. Der Fall eines Systemabsturzes, nachdem die Spalte in <Tab_Name> eingetragen
wurde, aber noch nicht oder nur teilweise in die Datentabellen, würde ein automatisches
Nachtragen durch erneuten Start des Skriptes unmöglich machen. Die Spalten müssten
dann von Hand nachgetragen werden.
4.3 Ändern von ZUORDNUNG
Die Änderung von ZUORDNUNG ist, wie in den vorherigen Kapiteln ausführlich
beschrieben, eine sehr sensible Angelegenheit. Hier soll kurz noch einmal zusammengefasst werden, was beim Ändern beachtet werden muss.
4.3.1 Hinzufügen von Zeilen
Das Hinzufügen von Zeilen in ZUORDNUNG, nach dem Anlegen der Datenbank (Kap.
4.1.3), sollte nur unter der Eingabemaske in Forms (Kap. 5) geschehen. Dort werden die
9
Für Binärwerte werden eigentlich nur die 0 und die 1 benötigt. Die Binärwerte werden aber eventuell im
Messintervall von 6 min. integriert. Das würde realisiert, um z.B. Prozentual die Öffnungszeiten eines
Fensterkontaktes in diesem Zeitraum zu erfassen.
44
Realisierung
notwendigen Sicherheitsprüfungen durchgeführt. Allerdings sollte der Administrator
sicher sein, dass seine Angaben stimmen; sind die Änderungen einmal akzeptiert,
können sie ohne weiteres nicht mehr rückgängig gemacht werden.
4.3.2 Löschen von Zeilen
Das Löschen von Zeilen aus ZUORDNUNG ist prinzipiell verboten, da es dann Spalten
in Datentabellen gibt, auf die nicht mehr verwiesen wird. Weiter könnten keine Tabellen
mehr geändert werden, da die Fehlerprüfung in ZUB_TMP_DATEN immer eine Spalte
findet, die nicht in ZUORDNUNG steht (Fehler Bit 3 -> Schritt 3 Kap. 4.2.2).
Sollte ein Sensor im Gebäude selbst demontiert und nie wieder gebraucht werden,
liefert er natürlich keine Messwerte mehr. Die automatische Fehlerkontrolle würde
diesen fehlenden Messwert immer mit Fehler 2 quittieren und in den Tabellen
<Tab_Name>_FEHL_AUTO_TAB eintragen. Die automatische Fehlerkontrolle benötigt relativ viel Rechenzeit. Daher sollte es möglich sein, eine Zeile aus ZUORDNUNG
zu entfernen. Es gibt für einen solchen Fall keine Eingabemaske oder Hilfestellung.
Diese Änderungen müssen von Hand durchgeführt werden. Folgende Schritte müssen
durchgeführt werden.
1. Löschen
der
Zeile
aus
ZUORDNUNG.
Diese Zeile darf nie wieder verwendet werden. Der Attributwert von Sensor und
gleichzeitig R_Nr darf für diesen <Tab_Name> nie wieder verwendet werden,
da sonst Daten unter die gleiche Spalte mit gleicher Raumnummer geschrieben
werden, die vorher einem anderen Sensor zugeordnet waren. Es entsteht eine
Inkonsistenz. Diese Änderungen müssen dokumentiert werden und ab diesem
Zeitpunkt muss jede Änderung in ZUORDNUNG mit dieser Dokumentation
verglichen werden.
2. Löschen
der
Tabelle
ZUB_DATEN_TMP_TAB.
Diese Tabelle kann gelöscht werden, da sie täglich neu gefüllt wird. Allerdings
muss die Firma benachrichtigt werden, welche die Leittechnik im ZUB betreut,
damit das Füllen der Tabelle weiter funktioniert.
3. Anlegen der Tabelle ZUB_DATEN_TMP_TAB mit Datum, Uhrzeit als
Attributen, wie es in den Umgebungstabellen schon einmal geschehen ist (Kap.
4.1.3).
45
Realisierung
4. Starten des Skriptes " Ablaufstrg_DB_Tabellen_anlegen.sql" (Kap. 4.2).
Hier werden die Spalten in ZUB_DATEN_TMP_TAB eingefügt.
4.3.3 Ändern von Zeilen
Das Ändern von Zeilen in ZUORDNUNG ist nur soweit erlaubt, als dass man den
Messbereich ändert. Dieser wird von der automatischen Fehlerprüfung verwendet. Die
Änderung des Messbereichs geschieht unter der Eingabemaske (Kap. 5). Wird der
Messbereich geändert, werden alle Daten, für die der Messbereich geändert wurde,
erneut geprüft und zwar vom ersten Tag an.
Führt man die Änderung des Messbereichs nicht unter der Eingabemaske durch, werden
nur Daten ab der Änderung geprüft.
Ändert man die Hw_Adr und ist diese noch nicht vorhanden, wird eine neue Spalte in
ZUB_TMP_DATEN angelegt und die Daten in die Spalte R_Nr und Sensor eingetragen, auf die vorher eine andere Hw_Adr verwiesen hat. Die Datenintegrität ist
verletzt. Genauso verhält es sich mit Änderungen des Sensors und der Raumnummer bei
gleichbleibender Hw_Adr.
Es kann vorkommen, dass beim Anlegen der Datenbank Fehler gemacht wurden, indem
Verweise durcheinander gebracht wurden. Es gibt eine Option bei der Forms-Eingabemaske Änderungen vorzunehmen (Kap. 5). Diese sollten aber in den ersten 14 Wochen
vorgenommen werden. Solange sind in ZUB_DATEN_TMP_TAB die ersten Messdaten vorhanden; es entstehen keine Datenverluste. Man sollte nach dem Ändern die
Datentabellen leeren ("...\Test\DB_Tabellen_leeren.sql") oder besser die Datentabellen
löschen
("DB_Tabellen_loeschen.sql")
und
neu
anlegen
("Ablaufstrg_DB_Tabellen_anlegen.sql").
4.4 Ausführen der Datenbank
Hier soll das Augenmerk auf die Datenintegrität gelegt werden, da bisher hauptsächlich
über das Anlegen und Ändern von Tabellen gesprochen wurde. Die Frage lautet hier,
wie das Eintragen der Daten realisiert wird.
46
Realisierung
4.4.1 Tägliche Ablaufsteuerung (Ablaufstrg_taeglich.sql)
Die tägliche Ablaufsteuerung wird einmal am Tag, nachdem die Daten in ZUB_DATEN_TMP_TAB aktualisiert wurden, ausgeführt. Abbildung 6 (Kap. 3.3.1) zeigt, was
bei dieser Verarbeitung geschieht. Unter Abbildung 14 ist das skripttechnisch dargestellt. Das Verbessern fehlerhafter Datumseinträge und die automatische Fehlerkontrolle
wurden schon ausführlich in Kap. 3.3.1 behandelt.
Zeittab.sql
ASCII-Datei im Verzeichnis
“Meldungen” mit Informationen
aus den Skripten und Prozeduren
Ablaufstrg_taeglich.txt
datum_verbessern
Gespoolte Datei
Rohdaten_eintragen.sql aus
Spool_Rohdaten_eintragen.sql
Fehler_eintragen.sql
Aktualisiert ZEITTAB mit
den Referenzzeiten
Prozedur zum Verbessern der
ungenauen Datumseinträge
in ZUB_DATEN_TMP_TAB
Übernahme der Messdaten aus
ZUB_DATEN_TMP_TAB in die
Messdatentabellen
<Tab_Name>_TMP_TAB und
<Tab_Name>_ROH_TAB
Eintragen der Fehler in die automatischen Fehlertabellen
<Tab_Name>_FEHL_AUTO_TAB
Ende
Abbildung 14: Täglicher Ablauf (Ablaufstrg_taeglich.sql)
Für die Sicherung der Messdaten in die Rohdatentabellen <Tab_Name>_ROH_TAB ist
ZUORDNUNG grundlegend. Ist die Integrität von ZUORDNUNG gewährleistet, sind
alle Spalten aus ZUB_DATEN_TMP_TAB bekannt, die in eine Spalte von
<Tab_Name>_TMP_TAB mit einer bestimmten Raumnummer R_Nr eingetragen
werden.
Man könnte ein PL/SQL Skript mit DBMS_SQL schreiben, welches diese Aufgabe
übernimmt. Das wäre negativ für die Laufzeit. Wesentlich schneller geht es, die Daten
in die temporären- und Rohdatentabellen mit einer reinen SQL-Datei aufzunehmen.
Programmtechnisch wird das gelöst, indem die Datei "gespoolt" wird. Oracle bietet das
Spooling normalerweise an, um Meldungen in einer ASCII Datei zu speichern oder um
Tabellen dort als ASCII –Code abzulegen, damit die Daten von Auswertprogrammen
47
Realisierung
(wie z.B. Matlab/Simulink) gelesen werden können (einige Programme greifen direkt
über ODBC auf die Tabellen zu). Hier wird das Spoolen genutzt werden, um die
Integrität der Datenbank zu sichern und den Ablauf zu automatisieren. Es muss die
Änderung von ZUORDNUNG reichen, um die Daten richtig abzulegen. Es ist sehr
fehlerträchtig, wenn Änderungen an vielen Stellen vorgenommen werden müssen.
Das Spoolen dieser Datei findet jedes Mal statt, wenn ZUORDNUNG geändert und das
Skript "Ablaufstrg_DB_Tabellen_anlegen.sql" gestartet wurde. Dieser Prozess funktioniert nur, wenn eben genanntes Skript erfolgreich ausgeführt wurde. Das ist immer in
der Datei '...\Meldungen\Datenbanktabellen.txt" erkenntlich.
Um das Beispiel aus Kap. 3.2.4 "Die Tabellen ZUORDNUNG und ANHANG" aufzunehmen, wird hier das Ergebnis des Spoolens aus den Tabellen für MESSR (Messraum)
gezeigt. STDR (Standardraum) wurde hier gelöscht, ist aber im Original vorhanden.
- Datei erstellt am 14.12.2000 15:07
----------------------------------------------------------------------------------------- Datei: Rohdaten_eintragen.sql
-- Erstellt: 14.12.2000 15:07
-- Aus Datei SPOOL_Rohdaten_eintragen.sql
-- Beschreibung:
-- Kopieren der Daten aus ZUB_DATEN_TMP_TAB in die Datenbank TMP Tabellen
-----------------------------------------------------------------------------------------******************************************************************--- Leeren der temporären Tabellen
TRUNCATE TABLE MESSR_TMP_TAB;
COMMIT;
--******************************************************************--- Löschen ungültiger Einträge von ZUB_DATEN_TMP_TAB
DELETE FROM ZUB_DATEN_TMP_TAB WHERE Datum IS NULL OR Uhrzeit IS NULL;
COMMIT;
--******************************************************************--- Kopieren der Werte in die temporären Tabellen,
-- bei denen das Datum neuer ist als in den Rohdatentabellen.
----------------------------------------------INSERT INTO MESSR_TMP_TAB (Datum, R_Nr,
T_OBERFL0, T_OBERFL1, T_OBERFL2)
SELECT to_date(Datum||Uhrzeit,'dd.mm.yyyy hh24:mi'), '1_02',
H00112, H00113, H00114
FROM ZUB_DATEN_TMP_TAB
WHERE to_date(Datum||Uhrzeit,'dd.mm.yyyy hh24:mi') >
(SELECT nvl(max(Datum), to_date('01.01.1111','dd.mm.yyyy hh24:mi')) FROM
MESSR_ROH_TAB
WHERE R_Nr ='1_02');
COMMIT;
-----------------------------------------------
48
Realisierung
--******************************************************************--- Kopieren der Temporären Daten in die Rohdatentabellen
INSERT INTO MESSR_ROH_TAB SELECT * FROM MESSR_TMP_TAB;
COMMIT;
--******************************************************************--10
Als erstes werden ungültige Einträge aus ZUB_DATEN_TMP_TAB gelöscht. Ungültig
sind alle Daten, die kein Datum oder keine Uhrzeit enthalten. Das sollte eigentlich nicht
auftreten, wird hier jedoch zur Sicherheit ausgeführt. Der nächste Schritt ist das Leeren
der Tabellen <Tab_Name>_TMP_TAB, da die Messdaten nicht mehr aktuell sind.
Danach werden die Werte aus ZUB_DATEN_TMP_TAB in die temporären Dateien
verteilt und gesichert. Die Raumnummer wird immer berücksichtigt. Es werden nur
Daten gesichert, die neuer sind, als das letzte gültige Datum mit der entsprechenden
Raumnummer in <Tab_Name>_ROH_TAB. Befindet sich kein gültiges Datum für
diese Raumnummer in den Rohdatentabellen, werden alle Daten aus ZUB_DATEN_TMP_TAB gesichert. Sind die Daten in den Tabellen mit temporären Messdaten
eingetragen, werden sie 1:1 in die Rohdaten aufgenommen. Die folgende automatische
Fehlerkontrolle durchsucht die temporären Tabellen.
4.4.2 Füllen der Datentabellen zur Auswertung (Ablaufstrg_manuell.sql)
Das Skript " Ablaufstrg_manuell.sql" wird erst korrekt ausgeführt, nachdem
"Fuelle_Fehl_inv_Tab.sql" ausgeführt wurde. Genaueres zu beiden findet sich in Kap.
3.3.2 "Manueller Ablauf mit Blick auf die Tabelle FEHL_TAB" und in Kap. 5.5.
Hier gilt für die Datenintegrität dasselbe wie im vorherigen Kapitel. Beim Starten von
"Ablaufstrg_manuell.sql" werden zwei Dateien gespoolt. Eine zum Füllen der korrigierten Rohdatentabellen und eine andere zur Berechnung der Mittelwerte (Abbildung 15)
10
Bei der Zeichenfolge – handelt es sich um ein Kommentar.
49
Realisierung
Fuelle_Fehl_inv_Tab.txt
Fuelle_Fehl_inv_Tab.sql
Spool_Rohkorr.sql
ASCII-Datei im Verzeichnis
“Meldungen” mit Informationen
aus den Skripten und Prozeduren
Spool_TagMonatJahr.sql
Rohkorr.sql
Ablaufstrg_manuell.txt
TagMonatJahr.sql
Füllen der inversen Fehlertabellen.
Wird extra unter der Eingabemaske
in Forms gestartet.
Erzeugen der SQL Datei Rohkorr.sql
zur Berechnung der korrigierten Rohdaten und zum Eintrag in
<Tab_Name>_ROHKORR_TAB.
Erzeugen der SQL Datei
TagMonatJahr.sql zur Berechnung der
Tages-, Monats- und Jahresmittelwerte
Füllen von
<Tab_Name>_ROHKORR_TAB
durch multiplizieren von
<Tab_Name>_ROH_TAB mit
<Tab_Name>_FEHL_INV_TAB
Füllen von
<Tab_Name>_TAG_TAB,
<Tab_Name>_MONAT_TAB und
<Tab_Name>_JAHR_TAB
Ende
Abbildung 15: Manueller Ablauf (Ablaufstrg_manuell.sql)
Zuerst werden alle Tabellen, die Messdaten – auch Mittelwerte – enthalten, geleert. Nur
die inversen Fehlertabellen bleiben gefüllt. Wie oben bereits erwähnt, werden die korrigierten Rohdaten gefüllt, indem die Rohdatentabellen mit den inversen Fehlertabellen
multipliziert werden. Das sei wieder am Beispiel des Messraumes MESSR gezeigt:
--------------------------------------------------------------------------------- Datei: ROHKORR.SQL
-- Erstellt: 22.12.2000 17:02
-- Aus Datei SPOOL_ROHKORR.SQL
-- Beschreibung:
-- Füllen der Tabellen ..._ROHKORR_TAB:
-- ..._ROHKORR_TAB wird durch Multiplikation der Tabellen ..._ROH_TAB und
-- ..._FEHL_INV_TAB gefüllt.
---------------------------------------------------------------------------------******************************************************************--- Leeren der Tabellen ..._ROHKORR_TAB
TRUNCATE TABLE MESSR_ROHKORR_TAB;
COMMIT;
--******************************************************************--- Füllen der Tabellen ..._ROHKORR_TAB
----------------------------------------------INSERT INTO MESSR_ROHKORR_TAB (Datum, R_Nr,
T_OBERFL0, T_OBERFL1, T_OBERFL2)
SELECT r.Datum ,r.R_Nr ,
50
Realisierung
f.T_OBERFL0 * r.T_OBERFL0, f.T_OBERFL1 * r.T_OBERFL1, f.T_OBERFL2 *
r.T_OBERFL2
FROM MESSR_ROH_TAB r, MESSR_FEHL_INV_TAB f
WHERE r.Datum = f.Datum
AND r.R_Nr = f.R_Nr;
COMMIT;
-----------------------------------------------
Danach folgt die Mittelwertbildung aus den korrigierten Rohdaten für die Tageswerttabellen. Aus diesen werden die Mittelwerte für die Monatstabellen und aus diesen
wiederum die Jahrestabellen gefüllt. Hier endet das Skript.
51
Bearbeitung der DB unter Oracle Forms
5 Bearbeitung der DB unter Oracle Forms
Die Administration und Ausführung der Datenbank erfolgt hauptsächlich – bis auf die
tägliche Ablaufsteuerung – unter Oracle Forms. Forms ist eine objektorientiertes
Designprogramm- ähnlich wie Visual C++ von Microsoft oder Delphi – mit dem
Schwerpunkt Datenbankzugriffe und wird mit dem Paket Developer 2000 geliefert.
Die Administrationsoberfläche (Abbildung 16) besteht aus einer Symbolleiste (links)
und aus einem vierseitigen Register (rechts):
1. Fehler-Tabellen eintragen
2. ZUORDNUNG bearbeiten
3. Messbereich ändern
4. Fehlertabelle bearbeiten
In diesem Kapitel wird beschrieben, welche Änderungen und Einträge welche Auswirkungen haben und wie diese am besten vorgenommen werden. Als erstes werden die
Registerseiten behandelt, da die Einträge sich auf die Handhabung der Symbolleiste
auswirken.
Die Registerseiten hängen in ihren Auswirkungen untereinander zusammen. Die Änderungen werden auf den Registerseiten bis zur endgültigen Übernahme zwischengespeichert. Solange Änderungen geplant sind –aber noch nicht übernommen-, werden die
übrigen Registerseiten – bis auf "Fehlertabelle bearbeiten" – inaktiv geschaltet. Das
Bearbeiten dieser ist erst nach dem Verwerfen oder Übernehmen möglich.
Änderungen werden zumeist nicht gleich ausgeführt, da sie sensible Auswirkungen auf
die Datenbank haben.
5.1 Fehlertabellen bearbeiten
In Abbildung 16 ist die Tabelle FEHL_TAB , u.a. mit den Einträgen aus FEHL_AUTO_ANZEIGE_TAB gefüllt, dargestellt (Kap. 3.2.5.2). Ausgehend von dem dortigen
Beispiel sieht man oben die "Zusammenfassung der automatischen Fehlererkennung".
Hier handelt es sich um die Ansicht der Tabelle FEHL_AUTO_ANZEIGE_TAB, die
aus den automatischen Fehlertabellen <Tab_Name>_FEHL_AUTO_TAB gefüllt
wurde. Dabei werden nur Einträge berücksichtigt, die noch nicht mit dem Eintrag
52
Bearbeitung der DB unter Oracle Forms
Check_Flag und Bearbeiter als geprüft markiert wurden. Die Spalten Tab_Name
(entspricht <Tab_Name>), Sensor, R_Nr , Fehlerkennung (Fehlernummer aus FEHLER_CODE), Anfangsdatum, Enddatum und Anzahl können nicht bearbeitet werden.
Sie wurden automatisch erkannt.
Checked und Bearbeiter müssen dagegen bearbeitet werden. Nach Anklicken des
Kontrollkästchens Checked muss ein Bearbeiter eingegeben werden (das Programm
lässt keine andere Aktion zu).
Ganz unten ist die Tabelle FEHL_TAB zu erkennen, in der alle gefundenen Fehler von
Hand eingetragen werden (Kap. 3.2.5.2). Die Eingabemaske befindet sich darüber.
Abbildung 16: Register: Fehlertabelle bearbeiten
Zurück zur "Zusammenfassung der automatischen Fehlererkennung". Checked wird
markiert, wenn der Administrator diesen Fehler in den automatischen Fehlertabellen
<Tab_Name>_FEHL_AUTO_TAB gesichtet und mit den Rohdaten verglichen hat.
53
Bearbeitung der DB unter Oracle Forms
Erkennt er den Fehler als realen Fehler an, übernimmt er ihn in FEHL_TAB und
markiert ihn. Sieht der Administrator, dass es sich nicht um einen Fehler handelt, wird
Checked gesetzt, aber der Wert nicht in FEHL_TAB übernommen.
Beim Drücken von "Neu Füllen" werden alle automatischen Fehlertabellen erneut
durchsucht und die Einträge gefunden, die automatisch fehlerhaft markiert wurden und
als ungeprüft gelten (Check_Flag ist NULL). Alle Änderungen, die nicht mit "Ok"
übernommen wurden, gehen dabei verloren, da FEHL_AUTO_ANZEIGE_TAB geleert
und wieder neu gefüllt wird (Skript "...\FORMS\fehl_auto_anzeige.sql").
Beim Drücken von "Ok" beginnt die Aktualisierung der automatischen Fehlertabellen.
Alle Fehler mit dem Haken Checked werden in den automatischen Fehlertabellen mit
einer 1 in der Spalte Check_Flag und dem eingetragenen Bearbeiter in der Spalte Bearbeiter aktualisiert (Skript "...\FORMS\fehl_auto_markierung.sql"). Danach wird die
Tabelle FEHL_AUTO_ANZEIGE_TAB neu gefüllt (Skript "...\FORMS\ fehl_auto_anzeige.sql"). Dieser Prozess kann bei vielen Fehlern einige Zeit dauern. Erst sollten
einige Fehler überprüft und übernommen werden, bevor einer der beiden Knöpfe
bedient wird.
Ein Problem der automatischen Fehlertabellen besteht, wenn bei verschiedenen Sensoren Fehler zur selben Zeit in den selben Räumen auftreten. Es gibt schließlich nur eine
Spalte für Checked und den Bearbeiter. Würde man nur einen Fehler überprüfen und
diesen in den automatischen Fehlertabellen markieren, würde der andere – der zur
selben Zeit im selben Raum auftritt - nicht mehr nach dem erneuten Füllen von
FEHL_AUTO_ANZEIGE_TAB erscheinen, da Checked für diesen Raum und dieses
Datum gesetzt ist . Es wird eine Funktion aufgerufen, die diese möglichen Überschneidungen in FEHL_AUTO_ANZEIGE_TAB findet, bevor die automatischen Fehlertabellen markiert werden. Sie prüft,ob ein anderer oder mehrere, die zur selben Zeit im
selben Raum auftreten, nur teilweise in FEHL_AUTO_ANZEIGE_TAB markiert
wurden. In diesem Fall wird das Fenster "Mögliche zusammenhängende Fehler"
geöffnet (Abbildung 17). Es handelt sich hierbei um eine reine Ansicht. Die Fehler
müssen weiterhin im Register markiert werden. Auf dem aktuellen Fenster gibt es
einige Knöpfe. Diese sind in der Abbildung beschrieben. Normalerweise sollte "Weitere
markieren" bedient und die Liste abgearbeitet werden. Nach Abarbeiten der Liste und
erneutem Drücken von "Ok" auf der Registerseite, wird die Markierung durchgeführt.
54
Bearbeitung der DB unter Oracle Forms
Abbildung 17: Fenster: Mögliche Überschneidungen
Die Eingabemaske für FEHL_TAB (Abbildung 16 Mitte) wird einerseits mit den
Fehlern aus FEHL_AUTO_ANZEIGE_TAB gefüllt, andererseits mit gefundenen
Fehlern beim Sichten der Rohdaten.
Eine Erleichterung beim Füllen der Maske, bietet ein Mausklick auf Tab_Name bei
"Zusammenfassung der automatischen Fehlererkennung". Die Maske wird mit der
angeklickten Zeile gefüllt.
Jeder Eintrag enthält eine laufende Nummer (Lfd. Nr.), die automatisch angezeigt und
nicht geändert werden kann. Sie ist immer um eins größer, als die maximale Nummer.
Wird eine laufende Nummer durch "Löschen", die kleiner ist als die Maximale, entfernt,
wird sie nicht mehr neu eingefügt. Das soll dem Administrator sagen, dass es mal einen
Eintrag gab, der jetzt nicht mehr gültig ist. Die Tabelle FEHL_TAB sollte vor einer
Änderung immer ausgedruckt und archiviert werden, damit man den Verlauf verfolgen
kann (mehr dazu in Kap. 5.5 Knopf "Spool").
Bevor die Maske in FEHL_TAB durch Drücken von "Übernehmen" eingefügt wird,
wird die Maske auf Syntax geprüft. Die Angaben der Tabelle, des Sensors und der
55
Bearbeitung der DB unter Oracle Forms
Raumnummer sind T-Listen bzw. Pull-Down-Menues. Sie enthalten Namen von Tabellen, Sensoren und Räumen, die in ZUORDNUNG vorkommen. Spalte und R_Nr. enthalten zusätzlich die Auswahl "ALLE". Es kann vorkommen, dass alle Sensoren in
einem Raum fehlerhaft sind oder dass ein bestimmter Sensor in allen Räumen falsch
misst oder dass alle Sensoren in allen Räumen fehlerbehaftet sind. Letzteres kommt bei
einem Ausfall der Leittechnik vor.
Syntaktisch kann bei diesen T-Listen kein Fehler entstehen. Die Fehlerkennung ist die
Nummer aus der Tabelle FEHLER_CODE, die dem Fehler zugeordnet ist (Kap. 3.2.5.2
und Kap. 5.4). Sie darf zwischen 1-99 liegen. Das Anfangs- und Enddatum muss im
Oracle Format 'dd.mm.yyyy hh24:mi' vorliegen (z.B. 24.12.2001 23:55). Das Anfangsdatum darf nicht leer sein. Das Enddatum darf leer sein, wenn ein Fehler aufgetreten ist,
aber der Zeitpunkt der Reparatur ungewiss ist (Kap. 3.3.2). Der Bearbeiter muss immer
bekannt sein und muss daher eingegeben werden. Eine Bemerkung ist wichtig, aber
nicht notwendig.
Weitere Knöpfe, die für die Registerseite von Bedeutung sind, sind "Spool" und
"FEHL_TAB verarbeiten" auf der Symbolleiste (Kap. 5.5)
5.2 ZUORDNUNG bearbeiten
In Abbildung 18 ist die Eingabemaske zur Bearbeitung von ZUORDNUNG zu sehen.
Oben befindet sich die Eingabemaske selbst. Darunter sind die Änderungen angezeigt,
die durch Drücken von "Hinzufügen/Bearbeiten" eingetragen wurden.
Die Richtlinien zum Löschen, Hinzufügen und Ändern von ZUORDNUNG sind in
Kapitel 4.3 "Ändern von ZUORDNUNG" festgehalten und sollten unbedingt beachtet
werden.
Die Eingabemaske wird auf syntaktische Mängel geprüft, bevor sie in die "Änderungen
von ZUORDNUNG" aufgenommen wird. Zum einen dürfen in die Felder Hw_Adr,
R_Nr, Sensor, Tab_Name nur die Zeichen 0-9, A-Z und ein Unterstrich "_" eingetragen
werden. Kleingeschriebenes wird automatisch durch Großbuchstaben ersetzt. Das
erleichtert bei einer Auswertung die Selektion, da der Administrator weiß, es kommen
nur Großbuchstaben vor. Die Felder Minwert und Maxwert dürfen nur Zahlen im
Wertebereich von –999.99 bis 999.99 enthalten.
56
Bearbeitung der DB unter Oracle Forms
Abbildung 18: Register: ZUORDNUNG bearbeiten
Zur Sicherheit wird - falls die Hw_Adr schon in ZUORDNUNG steht - ein Fenster
angezeigt (Abbildung 19). Hier wird dem Administrator verdeutlicht, dass es schon
einen Verweis aus ZUB_DATEN_TMP_TAB auf die Datentabellen gibt und er diesen
durch einen neuen ersetzt, wenn er "Zeile ersetzen" drückt. "Schließen" zeigt wieder die
gewohnte Registerseite an, ohne dass Änderungen übernommen werden.
57
Bearbeitung der DB unter Oracle Forms
Abbildung 19: Zeilen in ZUORDNUNG ersetzen
Die "Änderungen in ZUORDNUNG" in Abbildung 18 können komplett verworfen oder
übernommen werden.
Falls neue Datentabellen angelegt werden müssen, erscheint ein neues Fenster, damit
der Administrator auch hier noch einmal prüfen kann, ob er wirklich neue Datentabellen
anlegen will oder ob er sich verschrieben hat (Abbildung 20). Die übrigen Punkte, die in
den vorhandenen Datentabellen hinzugefügt oder geändert werden, werden in jedem
Falle nach dem Bedienen von "Übernehmen" in ZUORDNUNG eingetragen.
Abbildung 20: Fenster: Erstellen einer Tabelle
58
Bearbeitung der DB unter Oracle Forms
Die Grundlage dieser Registerseite bildet das Skript "Ablaufstrg_DB_Tabellen_anlegen.sql", welches ausführlich in Kap. 4.2.3 "" beschrieben wurde. Nachdem die Änderungen ausgeführt wurden, wird automatisch der Standardeditor Notepad gestartet und
die Textdatei ...\Meldungen\Datenbanktabellen.txt angezeigt. Der Administrator sieht
direkt, ob die Änderungen erfolgreich durchgeführt wurden.
5.3 Messbereich ändern
Die Änderung des Messbereiches wirkt sich auf <Tab_Name>_FEHL_AUTO_TAB
aus. In Abbildung 21 ist die Tabelle ZUORDNUNG abgebildet. In dieser können nur
die Felder Minwert und Maxwert des Messbereiches bearbeitet werden. Nach der
Änderung des Messbereiches werden die geänderten Werte in das Feld unterhalb der
Tabellenansicht angezeigt.
Wieder besteht die Option des Verwerfens oder Übernehmens. Im ersten Fall wird die
Tabelle in ihren ursprünglichen Zustand zurückgesetzt, im zweiten Fall wird der Messbereich der geänderten Werte neu geprüft.
Alle Werte in <Tab_Name>_FEHL_AUTO_TAB, die in der referierten Spalte "Sensor"
stehen und den Fehler 3 aus FEHLER_CODE enthalten, werden auf NULL zurückgesetzt. Jetzt wird die entsprechende Spalte in <Tab_Name>_ROH_TAB auf den neuen
Messbereich überprüft und die Werte in die automatische Fehlertabelle eingetragen.
Wieviele Änderungen vorgenommen wurden und welche Befehle ausgeführt wurden, ist
ganz unten in der Tabelle zu erkennen.
59
Bearbeitung der DB unter Oracle Forms
Abbildung 21: Register: Messbereich ändern
Die Messbereichsänderungen wirken sich auf die Tabelle FEHL_AUTO_ANZEIGE_TAB aus, da mit dem Löschen des alten Messbereiches auch die Prüfflags
Checked und der Bearbeiter aus den automatischen Fehlertabellen gelöscht werden
müssen (nur bei Fehler 3 in FEHLER_CODE)11. Im Register "Fehlertabellen eintragen"
sollte daher der Knopf "Neu füllen" angewendet werden.
5.4 Fehlertabelle bearbeiten
Abbildung 22 zeigt die Tabelle FEHLER_CODE an. Standardfehler der automatischen
Fehlererkennung sind in den Nummern 1-3 festgelegt. Diese können nicht geändert
werden. Der Administrator soll diese Tabelle nutzen, um gesichtete Fehler aus den
11
Programmiertechnisch in DBMS_SQL nur aufwendig zu lösen. Es müssten mehrere FULL TABLE
SCANS durchgeführt werden, die sehr rechenzeitintensiv sind.
60
Bearbeitung der DB unter Oracle Forms
Datentabellen, die mit der Fehlernummer in FEHL_TAB eingetragen werden, zu
beschreiben.
Abbildung 22: Register: FEHLER_CODE bearbeiten
"Fehler Bearbeiten" öffnet die Eingabemaske in Abbildung 23. Die Beschreibung kann
jederzeit eingetragen oder geändert werden; allerdings sollten einmal festgelegte
Beschreibungen nicht mehr geändert werden, damit FEHL_TAB konsistent bleibt.
Abbildung 23: Fenster: Fehler eintragen
61
Bearbeitung der DB unter Oracle Forms
5.5 Die Symbolleiste
Die Symbolleiste aus Abbildung 24 startet verschiedene Skripte.
Abbildung 24: Symbolleiste
Sie enthält folgende Knöpfe:
1. "ZUB
DB
Ausführen"
Startet das Skript "Ablaufstrg_manuell.sql" (Kap. 3.3.2). Zeigt automatisch die
Ergebnisse
in
"...\Meldungen\Ablaufsteuerung_manuell.txt"
an.
Sollten die inversen Fehlertabellen noch nicht gefüllt sein, werden keine Daten
übernommen. Falls neue Messdaten seit dem letzten Füllen der Fehlertabellen
62
Bearbeitung der DB unter Oracle Forms
aufgelaufen sind, muss "FEHL_TAB verarbeiten" (s.u.) erneut ausgeführt werden, da sich sonst nicht alle Datumseinträge und Raumnummern in den inversen
Fehlertabellen befinden.
2. "FEHL_TAB
verarbeiten"
Füllen der inversen Fehlertabellen <Tab_Name>_FEHL_INV_TAB aus der
Tabelle FEHL_TAB. Dieser Vorgang ist unabhängig von der manuellen Ablaufsteuerung, damit die inversen Fehlertabellen nicht immer wieder neu gefüllt
werden müssen, falls keine neuen Messdaten angefallen sind oder diese im
Moment nicht interessieren. Genaueres hierzu befindet sich in den Kapiteln
3.3.2 "Manueller Ablauf mit Blick auf die Tabelle FEHL_TAB"
3. "Spool"
Spoolen wurde im Kap. 4.4.1 beschrieben. Das aufgerufenen Skript "...\Forms\
spool_fehler_tabs.sql" schreibt den Inhalt der Tabellen FEHL_TAB, FEHL_AUTO_ANZEIGE_TAB, MOEGLICHE_UEBERSCHNEIDUNG_TAB in die
Datei "...\Meldungen\Fehler_Tabs.txt". Vor der Bearbeitung der Fehlertabellen
sollte die Funktion ausgeführt werden. Die Textdatei wird automatisch angezeigt
und sollte ausgedruckt und archiviert werden, damit der Änderungsstand immer
bekannt ist.
4. "ZUB
täglicher
Ablauf"
Starten der Skriptes "Ablaufstrg_taeglich.sql" (Kap. 3.3.1und Kap. 4.4.1). Die
tägliche Ablaufsteuerung wird – wie beschrieben – jeden Tag gestartet. Dieser
Knopf führt die gleiche Funktion aus. Die Ergebnisse werden in "...\Meldungen\
Ablaufsteuerung_taeglich.txt " automatisch angezeigt. Bevor dieser Knopf
bedient wird, sollte der Administrator die Textdatei öffnen und Einsicht nehmen,
ob wichtige Meldungen in ihr sind, bevor sie nach Bedienen des Knopfes neu
angelegt wird.
5. EXIT
Verlassen der Anwendung ohne Änderungen zu speichern oder zu übernehmen.
Es wird vor dem Verlassen auf etwaige Änderungen geprüft, die noch nicht
übernommen oder verworfen wurden.
63
Zusammenfassung und Ausblick
6 Zusammenfassung und Ausblick
6.1 Zusammenfassung
In dieser Arbeit wurde eine Datenbank zur Messwertarchivierung beschrieben. Die
Messwerte werden im "Zentrum für umweltbewusstes Bauen" (ZUB) aufgenommen,
der Datenbank als Rohdaten übergeben und weiter verarbeitet. Die von der Leittechnik
erfassten Messwerte werden großteils für die Gebäudesteuerung benutzt. Das Konzept
der Datenbank beruht auf der Tatsache, dass die archivierten Daten später ausgewertet
werden. Die Auswertung soll zu Verbesserungen bei der Steuerung der Haustechnik
führen.
Die ZUB-Leittechnik wird in Informationsschwerpunkte – wie z.B. Standardräume oder
Messräume – eingeteilt. Die Informationsschwerpunkte geben an, welche Werte gemessen werden (Kap. 3.1.1 - 3.1.3).
Alle Messwerte werden von der Leittechnik täglich in ZUB_DATEN_TMP_TAB
geschrieben. Sie enthält die Messwerte der letzten 14 Wochen. Als Spaltennamen
werden die von der Leittechnik zugewiesenen Hardwareadressen benutzt (Kap. 3.1.4).
Alle Messdaten werden in den sogenannten Rohdatentabellen gesammelt (Kap. 3.2.1).
Für eine Auswertung müssen die rohen Daten der Datenbank aufbereitet werden. Das
geschieht durch eine automatische Fehlererkennung und durch eine ergänzende manuelle Fehlererkennung, bei der der Administrator die Messdaten sichtet (Kap. 3.2.2).
Daraus ergeben sich die notwendigen Datentabellen. Es sind die Rohdatentabellen,
Fehlertabellen, korrigierten Rohdatentabellen und Mittelwerttabellen. Letztere sind
wiederum in Tages-, Monats- und Jahresmittelwerttabellen aufgeteilt (Kap. 3.2).
Hinsichtlich einer späteren Auswertung sind Hardwareadressen als Spaltennamen in der
Tabelle ZUB_DATEN_TMP_TAB ungeeignet (s.o.) (Kap. 3.1.6). Für eine Auswertung
und die Übersichtlichkeit ist es besser, die physikalische Eigenschaft des Messsensors
als Spaltennamen zu wählen und den Ort der Messung zu kennen. Dazu werden die
Daten in den Datentabellen neu angeordnet. Die Datentabellen enthalten also die physikalische Eigenschaft des Sensors als Spaltennamen und zusätzlich eine Spalte mit der
Raumnummer (R_Nr) im ZUB als Bestimmung des Ortes. Der Anwender weiß sofort,
wann, was und wo gemessen wurde.
64
Zusammenfassung und Ausblick
Es muss sicher gestellt sein, dass bei dem neuen Anordnen die Daten den richtigen
Spalten zugeordnet werden. Die Verbindung zwischen ZUB_DATEN_TMP_TAB und
den Datentabellen mit dem Ort (Raumnummer) und der physikalischen Eigenschaft ist
die Tabelle ZUORDNUNG (Kap. 3.2.4).
Die Verknüpfung in ZUORDNUNG genügt nur dann der Anforderung nach Datenkonsistenz, wenn die Datentabellen automatisch angelegt und geändert werden können
(Kap. 4.2). Um alle Datentabellen automatisch zu erstellen, benötigt man Tab_Name in
ZUORDNUNG und Anhang aus der Tabelle ANHANG. ANHANG enthält Einträge
wie _ROH_TAB, _ROHKORR_TAB oder _JAHR_TAB. Setzt man Tab_Name mit den
Einträgen aus ANHANG zusammen, erhält man die vollständigen Namen der Datentabellen und kennt ihren Inhalt. Die Spaltennamen in den Tabellen sind identisch. Es
handelt sich um Abbildungen. Eine Ausnahme bilden die automatischen Fehlertabellen
und die inversen Fehlertabellen (Kap. 3.2.4).
Wie der Name "automatische Fehlertabelle" sagt, wird diese automatisch gefüllt. Es gibt
standardmäßig drei verschiedene Fehler, die in die Tabellen eingetragen werden. Erstens wird kontrolliert, ob alle Datumseinträge vorhanden, zweitens ob alle Messwerte
vorhanden sind und drittens, ob der Messwert im Messbereich liegt (Kap. 3.3.1.3).
Dadurch wird eine erste Plausibilisierung der Messdaten vorgenommen. Damit der
Administrator schnell eine Übersicht über die gefundenen Fehler bekommt, werden
diese in der Tabelle FEHL_AUTO_ANZEIGE_TAB zusammengefasst (Kap. 3.2.5.2,
5.1). Mit den Einträgen kann er nun die Rohdaten sichten und festlegen, ob es sich um
einen tatsächlichen Fehler handelt oder ob die automatische Fehlersuche falsch eingestellt ist (z.B. Messbereich nicht richtig eingegeben).
Handelt es sich um einen realen Fehler, so übernimmt der Administrator den Eintrag
aus FEHL_AUTO_ANZEIGE_TAB in FEHL_TAB. FEHL_TAB enthält alle bestätigten Fehler aus der automatischen Fehlersuche, sowie Fehler, die beim Sichten der
Rohdaten gefunden worden. Aus FEHL_TAB werden die sogenannten inversen Fehlertabellen gefüllt (Kap. 3.2.5.2 , 3.3.2 , 5.1).
Die inversen Fehlertabellen beinhalten alle Datumseinträge und Raumnummern der
Rohdaten und je nach fehlerbehaftetem oder korrektem Wert eine NULL (nichts) oder
eine 1. Durch Multiplikation der Rohdaten mit den inversen Fehlertabellen bleiben nur
als korrekt empfundene Werte übrig. Man erhält korrigierte Rohdaten, aus denen
65
Zusammenfassung und Ausblick
Mittelwerte gebildet werden. Erst wenn die Rohdaten korrigiert wurden, können sie
plausibel ausgewertet werden (Kap. 3.3.2).
Die obigen Ausführungen geben auch den gesamten Ablauf der Datenverarbeitung vor.
Dieser wird in einen täglichen Ablauf (Kap. 3.3.1, 4.4.1) und einen manuellen Ablauf
(Kap. 3.3.2, 4.4.2) unterteilt.
Täglich müssen die in ZUB_DATEN_TMP_TAB angefallenen Daten in den Rohdatentabellen gesammelt werden. Die automatische Fehlerprüfung wird auch täglich
durchgeführt. Das hat den Vorteil, dass der Administrator keine Zeit verliert, wenn er
automatisch gefundene Fehler in den Rohdaten prüft.
Die manuelle Ablaufsteuerung wird erst gestartet, wenn die inversen Fehlertabellen aus
FEHL_TAB gefüllt wurden. Daraus werden dann die korrigierte Rohdaten berechnet
und aus den korrigierten Rohdaten die Mittelwerte der Tage, Monate und Jahre.
Um die Handhabung der Datenbank zu vereinfachen, wurde unter Oracle Forms eine
Maske programmiert (Kap. 5). In dieser kann Maske können die Tabellen FEHL_AUTO_ANZEIGE_TAB und die Tabelle FEHL_TAB gefüllt werden (Kap. 5.1), ZUORDNUNG kann bearbeitet werden (Kap. 4.3, 5.2), der Messbereich der Sensoren kann
geändert werden (Kap. 5.3) und die Tabelle FEHLER_CODE (Kap. 5.4) kann erweitert
oder geändert werden. Weiter können die inversen Fehlertabellen aus FEHL_TAB
gefüllt werden und die manuelle Ablaufsteuerung gestartet werden (Kap. 5.5).
6.2 Ausblick
Das ZUB Gebäude wurde während dieser Arbeit nicht fertig gestellt. Die Leittechnik ist
nicht installiert und somit sind keine Daten angefallen. Das voraussichtliche Datenaufkommen ist sehr hoch. Die beschriebenen Funktionen wurden mit wesentlich weniger
Daten getestet. Ob dieser Test ausreichend war, kann nicht gesagt werden und wird sich
in den ersten Tagen nach Inbetriebnahme zeigen.
Ein Nachteil der Bauverzögerung ist, dass Informationen relativ langsam flossen und
Konzepte nicht fertiggestellt sind. Jedoch bieten wenige Informationen viel Platz für
Ideen und bergen Freiräume. Unter diesen Bedingungen wurde eine flexible Datenbank
erstellt, die mit kleinen Änderungen auf andere Projekte übertragbar ist. Prinzipiell
66
Zusammenfassung und Ausblick
handelt es sich um eine Verteilung und nach Bedarf eine Umbenennung von Daten aus
einer Tabelle in mehrere.
Bei der automatischen Fehlerprüfung sind Erweiterungen denkbar. Sinnvoll wäre eine
Funktion die Abweichungen der Messwerte von ihrem Mittelwert findet. In welchem
Zeitraum der Mittelwert berechnet wird und mit den Messdaten verglichen wird, muss
durch Überlegungen und Tests herausgefunden werden. Z.B. liegen Außentemperaturen
tagsüber und nachts so weit auseinander, dass die Prüfung der Abweichung vom Tagesmittelwert nicht sinnvoll erscheint. Eine weitere Fehlerprüfung wäre für Differenziale
denkbar. Man setzt hier eine maximale Abweichung vom vorherigen Messwert fest.
Z.B. ändert sich die Außentemperatur innerhalb eines 6-Minuten Intervalls wahrscheinlich nicht um 10°C.
67
Literatur
7 Literatur
[1]
Documentation Library for Oracle8 release 8.0.5
Browserbasierte Dokumentation des Programmpaketes Oracle 8.0.5
[2]
Froese, Jürgen: Effiziente Systementwicklung mit Oracle 7.1
2. Aufl., Addsison-Wesley (Deutschland) GmbH, 1995
[3]
Kemper, A.; Eickler A.: Datenbanksysteme
Wien: Oldenbourg München, 1996
[4]
Christiansen, Andreas; Höding, Michael; Rautenstrauch, Claus; Saake,
Gunter:
Oracle8 effizient einsetzen
Bonn; Reading, Massachusetts [u.a.]: Addison-Wesley-Longman, 1998
[5]
Finkenzeller, Hermann; Kracke, Ulrich; Unterstein:
Systematischer Einsatz von SQL-ORACLE
Bonn; Reading, Mass.;NewYork [u.a]: Addison-Wesley. 1989
[6]
Burleson, Donald K.: Oracle Datenbank-Anwendungen
Bonn; Albany [u.a]: Internat. Thomson Publ., 1997
[7]
Urman, Scott ORACLE (PL/SQL Programming)
New York, Berkley [u.a]: Oracle Press: Osborne McGraw-Hill 1996
[8]
SAUTER-CUMULUS GmbH, Niederlassung Frankfurt:
Informationsliste Automationseinrichtungen
Internes Papier zur Beschreibung der Messpunkte im ZUB
68
Abbildungsverzeichnis
8 Abbildungsverzeichnis
Abbildung 1: Datenverkehr über das Universitätsnetzwerk ........................................... 8
Abbildung 2: Schnittstelle zwischen Gebäudeleittechnik und ZUB-DB....................... 10
Abbildung 3: Relationsmodell..................................................................................... 14
Abbildung 4: Datentabellen und ihre Abbildungen...................................................... 15
Abbildung 5: Mind-Map der Datenbank...................................................................... 17
Abbildung 6: Täglicher und manueller Ablauf ............................................................ 25
Abbildung 7: Beispiel für den täglichen Datenfluss..................................................... 27
Abbildung 8: Verbessern ungenauer Datumseinträge in ZUB_DATEN_TMP_TAB ... 29
Abbildung 9: Automatische Fehlerkontrolle................................................................ 31
Abbildung 10: Anlegen der Datenbank ....................................................................... 36
Abbildung 11: Übersicht Ablaufsteuerung Tabellen anlegen/ändern ........................... 39
Abbildung 12: ZUORDNUNG auf mögliche Fehler prüfen......................................... 40
Abbildung 13: Ändern und Anlegen der Datentabellen (DB_Tabellen_Anlegen.sql)... 43
Abbildung 14: Täglicher Ablauf (Ablaufstrg_taeglich.sql) .......................................... 47
Abbildung 15: Manueller Ablauf (Ablaufstrg_manuell.sql)......................................... 50
Abbildung 16: Register: Fehlertabelle bearbeiten ........................................................ 53
Abbildung 17: Fenster: Mögliche Überschneidungen .................................................. 55
Abbildung 18: Register: ZUORDNUNG bearbeiten.................................................... 57
Abbildung 19: Zeilen in ZUORDNUNG ersetzen ....................................................... 58
Abbildung 20: Fenster: Erstellen einer Tabelle............................................................ 58
Abbildung 21: Register: Messbereich ändern .............................................................. 60
Abbildung 22: Register: FEHLER_CODE bearbeiten ................................................. 61
Abbildung 23: Fenster: Fehler eintragen ..................................................................... 61
Abbildung 24: Symbolleiste........................................................................................ 62
69
Anhang
9 Anhang
9.1 PL/SQL Skripte
Skriptname
Seite
Skript 1: ABLAUFSTRG_DB_NEU_ANLEGEN.SQL .............................................. 72
Skript 2: ABLAUFSTRG_DB_TABELLEN_ANLEGEN.SQL .................................. 73
Skript 3: ABLAUFSTRG_MANUELL.SQL............................................................... 74
Skript 4: ABLAUFSTRG_TAEGLICH.SQL .............................................................. 75
Skript 5: DATENBANKTABELLEN_ANLEGEN.SQL............................................. 77
Skript 6: DATENBANKTABELLEN_LEEREN.SQL ................................................ 82
Skript 7: DATENBANKTABELLEN_LOESCHEN.SQL........................................... 83
Skript 8: FEHL_AUTO_ANZEIGE.SQL.................................................................... 85
Skript 9: FEHL_AUTO_MARKIERUNG.SQL .......................................................... 87
Skript 10: FEHLER_EINTRAGEN.SQL .................................................................... 89
Skript 11: FUELLE_FEHL_INV_TAB.SQL .............................................................. 94
Skript 12: FUNKTION_DATUM_R_NR_IN_TAB.SQL............................................ 96
Skript 13: FUNKTION_FINDEANZAHL.SQL .......................................................... 97
Skript 14: FUNKTION_SONDERZEICHEN.SQL ..................................................... 97
Skript 15: FUNKTION_SPALTE_VORHANDEN.SQL............................................. 98
Skript 16: FUNKTION_TAB_VORHANDEN.SQL ................................................... 99
Skript 17: FUNTKION_ZUORDNUNG_PRUEFEN.SQL........................................ 100
Skript 18: INITUMGEBUNGSTABELLEN.SQL..................................................... 105
Skript 19: PROZEDUR_DATUM_VERBESSERN.SQL.......................................... 106
Skript 20: PROZEDUR_DB_SPALTEN_ANLEGEN.SQL ...................................... 109
Skript 21: PROZEDUR_DBMS_EXEC.SQL ........................................................... 111
70
Anhang
Skript 22: FROMS_COMMIT_ONLY.SQL ............................................................. 111
Skript 23: PROZEDUR_FORMS_FUELL_FEHL_AUTO_ANZEIGE.SQL............. 112
Skript 24: PROZEDUR_FORMS_MESSBEREICH_FEHL_UPDATE.SQL ............ 115
Skript 25: PROZEDUR_ZUB_DATEN_TMP_TAB_ANLEGEN.SQL .................... 117
Skript 26: SPOOL_FEHLER_TABS.SQL ................................................................ 119
Skript 27: SPOOL_KOPIEREN_NACH_ZUB_DATEN_TMP_TAB.SQL............... 121
Skript 28: SPOOL_ROHDATEN_EINTRAGEN.SQL ............................................. 123
Skript 29: SPOOL_ROHKORR.SQL........................................................................ 126
Skript 30: SPOOL_TAGMONATJAHR.SQL ........................................................... 128
Skript 31: UMGEBUNGSTABELLEN.SQL ............................................................ 131
Skript 32: ZEITTAB.SQL......................................................................................... 136
Skript 33: ZUORDNUNG.SQL ................................................................................ 138
71
Anhang
Skript 1: ABLAUFSTRG_DB_NEU_ANLEGEN.SQL
-- ACHTUNG:
-- DIE DATENBANKTABELLEN WERDEN KOMPLETT GELÖSCHT!!!
-- ACHTUNG
-- NUR STARTEN BEIM ERSTEN ANLEGEN DER DATENBANK!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
----------------------------------------------------------------------------------------- Datei: Ablaufstrg_DB_neu_anlegen.sql
-- Erstellt: 08.11.2000
-- Beschreibung:
-- Anlegen der Datenbank mit Prozeduren.
-- Die Tabellen ZUB_DATEN_TMP_TAB und die Datenbanktabellen mit den Messwerten werden
-- erstellt, wenn ZUORDNUNG fehlerfrei ist. Es kann vorkommen das Sensoren (sensor),
-- die im gleichen Raum (tab_name) sind und auch noch die gleiche Raumnummer (r_nr)
-- besitzen, automatisch geändert werden. Prüfen Sie die Textdatei
-- "Datenbank_neu_anlegen" auf Fehlermeldungen.Falls Sensoren geändert wurden und Sie
-- nicht damit einverstanden sind, ändern Sie ZUORDNUNG und führen Sie dieses Skript
-- erneut aus!
-- Änderungen:
----------------------------------------------------------------------------------------SET SERVEROUTPUT ON
SET termout off;
SET echo off;
SET heading off;
--SET feedback off;
spool d:\zub_db\sql\meldungen\Datenbank_neu_anlegen.txt
BEGIN
DBMS_OUTPUT.PUT_LINE('-- Datenbank angelegt am '||to_char(SYSDATE,'dd.mm.yyyy
hh24:mi'));
END;
/
-- LÖSCHEN ALLER DATENBANKTABELLEN !!!
@@DB_Tabellen_loeschen.sql
-- INITIALISIERUNG DER DATENBANK
-Umgebungstabellen löschen und neu anlegen.
@@Umgebungstabellen.sql--!!!
-Umgebungstabellen initialisieren.
@@InitUmgebungstabellen.sql--!!!
---**********************************************************************
-- Tabelle ZUORDNUNG zur genauen Angabe der
-- Sensoren (Name bei Sauther,Sensorart, Raumnummer im Zub , Ort
-- (Art des Raumes), Minimaler Wert, Maximaler Wert).
-- Neue Sensoren hier editieren, alte herausnehmen. So erreicht man immer den
-- neuesten Stand bei einer Neuinitialisierung der Datenbank.
-- *********************************************************************
@@zuordnung.sql
/* PROZEDUREN ANLEGEN */
@@prozedur_dbms_exec.sql
@@funktion_findeAnzahl.sql
@@funktion_tab_vorhanden.sql
@@funktion_zuordnung_pruefen.sql
@@funktion_spalte_in_tabelle.sql
@@prozedur_zub_daten_tmp_tab_anlegen.sql
@@funktion_datum_r_nr_in_tab.sql
@@prozedur_db_spalte_anlegen.sql
@@prozedur_datum_verbessern.sql
@@funktion_sonderzeichen.sql
@@prozedur_forms_commit_only.sql
@@prozedur_forms_fuell_fehl_auto_anzeige.sql
@@prozedur_forms_messbereich_fehl_update.sql
-- ZUORDNUNG wird geprüft. Ist die Fehlerprüfung erfolgreich, werden die Tabellen
erstellt.
@@ablaufstrg_DB_Tabellen_anlegen.sql
72
Anhang
COMMIT;
EXIT;
Skript 2: ABLAUFSTRG_DB_TABELLEN_ANLEGEN.SQL
----------------------------------------------------------------------------------------- Datei: ABLAUFSTRG_DB_TABELLEN_ANLEGEN.SQL
-- Erstellt: 10.11.2000
-- Beschreibung:
-- Ablaufsteuerung für das Anlegen oder Löschen eines Messpunktes-- Die Tabelle ZUORDNUNG muss auf dem aktuellen Stand sein.
---- Bem.:
-Die Tabelle ZUB_DATEN_TMP_TAB kann im Notfall gelöscht werden, falls falsche
-Spalten vorhanden sind.
-Es muss aber sicher gestellt sein, dass ZUORDNUNG auf dem aktuellen Stand ist.
-Sie muss aber von Hand neu angelegt werden (siehe Umgebungstabellen.sql).
-- Änderungen:
-----------------------------------------------------------------------------------------SET SERVEROUTPUT ON
SET termout off;
SET echo off;
SET heading off;
SET feedback off;
spool d:\zub_db\sql\meldungen\Datenbanktabellen.txt
-- INITIALISIERUNG DER DATENBANK
DECLARE
fehler_flag
NUMBER(2) := NULL;
BEGIN
DBMS_OUTPUT.PUT_LINE('-- Letzte Änderung der Tabellen '||to_char(SYSDATE,'dd.mm.yyyy
hh24:mi'));
-- Prüfen, ob die Werte in ZUORDNUNG mit denen aus ZUB_DATEN_TMP_TAB übereinstimmen.
-- Automatisches Anlegen der Datenbanktabellen aus den Tabellen 'ZUORDNUNG' und
'ANHANG'.
-- Die Datenbanktabellen werden erst angelegt, wenn die Tabelle ZUB_DATEN_TMP_TAB
-- vollständig ist.
-- Es wird hier der erste Versuch unternommen, die Messpunkte aus ZUORDNUNG zu
übernehmen
-- Mit der Option ANLEGEN entfällt die Prüfung von ZUB_DATEN_TMP_TAB
-- ZUB_DATEN_TMP_TAB kann nicht stimmen, bevor Sie nicht geändert wurde.
-- Keine Fehler in ZUORDNUNG. Die Tabelle ZUB_DATEN_TMP_TAB wird geändert.
fehler_flag := zuordnung_pruefen('ANLEGEN');
IF fehler_flag = 0 THEN
zub_daten_tmp_tab_anlegen;
COMMIT;
-- ZUORDNUNG wird vollständig geprüft.
-- Es wird der Fehler gefunden, falls nicht alle Spalten in ZUB_DATEN_TMP_TAB
-- eingetragen wurden. Andere Fehler werden erneut gefunden. Die Funktion
-- zuordnung_pruefen schreibt das fehler_flag in die Tabelle ZUORNUNG_GEPRUEFT,
welches
-- vor dem Anlegen der Datenbanktabellen gelesen wird. Ist es ungleich 0 wird
-- keine Datentabelle angelegt (s.u. DB_Tabellen_anlegen.sql).
DBMS_OUTPUT.PUT_LINE('-------------------------------------------------------');
DBMS_OUTPUT.PUT_LINE('-------------------------------------------------------');
DBMS_OUTPUT.PUT_LINE('-- ZUORDNUNG wird erneut geprüft');
fehler_flag := zuordnung_pruefen('VOLL');
END IF;
END;
/
-- Anlegen von Tabellen und Spalten.
@@DB_Tabellen_anlegen;
-- Neues Anlegen der von Rohdaten_eintragen_kopieren.sql
-- Damit wird erreicht, dass immer nur Daten kopiert werden aus ZUB_DATEN_TMP_TAB,
73
Anhang
-- die noch nicht in ..._ROH_TAB vorhanden sind!
@@spool_Rohdaten_eintragen.sql
EXIT;
Skript 3: ABLAUFSTRG_MANUELL.SQL
----------------------------------------------------------------------------------------- Datei: ABLAUFSTRG_MANUELL.SQL
-- Erstellt: 20.11.2000
-- Beschreibung:
-- Ablaufsteuerung:
-- Es müssen die Tabellen ...FEHL_INV_TAB aus der Tabelle FEHL_TAB gefüllt werden.
-- Die Tabelle FEHL_TAB wird ausschliesslich in FORMS50 bearbeitet und die Verarbeitung
-- gestartet.
-- Die Tabellen ..._ROH_TAB werden mit den Tabellen ...FEHL_INV_TAB multipliziert
-- und in die Tabellen ..._ROHKORR_TAB eingetragen. Aus dieser werden die
-- Tagesmittelwerte
-- in die Tabellen ..._TAG_TAB eingetragen, aus denen wiederum ..._MONAT_TAB und zuletzt
-- aus ..._MONAT_TAB ..._JAHR_TAB erstellt.
-- ACHTUNG:
-- *** Im Skript "spool_TagMonatJahr.sql" kann eingestellt werden, wieviele Messwerte
-pro Tag aus ..._ROHKORR_TAB benötigt werden, damit ein Tageswert in
-..._TAG_TAB eingetragen wird.
-- Bem.:
-- 1) Die oben angegebene Zeichenkette ... bedeutet, dass es sich hierbei um
-die Datenbanktabellen aus ZUORDNUNG handelt, die in der Spalte
-'TAB_NAME' definiert wurden. Diese 'TAB_NAME'n werden mit
-Anhängseln aus der Tabelle ANHANG versehen
-(z.B. _ROH_TAB oder _FEHL_AUTO_TAB).
-- 2) Es werden Werte kopiert die in der Tabelle ZUORDNUNG vorhanden sind.
-Um neue Messpunkte anzulegen oder alte zu löschen, muss erst ZUORDNUNG
-aktualisiert werden und dann das Skript 'ablaufstrg_DB_Tabellen_anlegen.sql'
-gestartet werden.
-- Änderungen:
---------------------------------------------------------------------------------------SET SERVEROUTPUT ON
SET termout off;
SET echo off;
SET heading off;
SET FEEDBACK ON
-- ***Im Skript spool_TagMonatJahr.sql kann eingestellt werden, wieviele Messwerte pro
Tag aus ..._ROHKORR_TAB
--
benötigt werden, damit ein Tageswert in ..._TAG_TAB eingetragen wird.
@@spool_rohkorr.sql
@@spool_TagMonatJahr.sql
-- Eintragen von Werten in die Tabellen ..._FEHL_INV_TAB
SET SERVEROUTPUT ON
SET termout on;
SET echo on;
SET heading on;
SET FEEDBACK ON
spool d:\zub_db\sql\meldungen\Ablaufsteuerung_manuell.txt
BEGIN
DBMS_OUTPUT.PUT_LINE('-- Manuelle Ablaufsteuerung zuletzt gestartet am
'||to_char(SYSDATE,'dd.mm.yyyy hh24:mi'));
END;
/
-- Füllen der Tabellen ..._FEHL_INV_TAB
-- DIESES SKRIPT WIRD AUS FORMS MANUELL GESTARTET
--@@fuelle_fehl_inv_tab.sql
74
Anhang
-- Füllen der ..._ROHKORR_TAB, ..._TAG_TAB, ..._MON_TAB, ...JAHR_TAB Tabellen
-!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
-Beachten Sie die Beschreibung (s.o. ***) von spool_TagMonatJahr.sql und stellen !!!
-Sie die minimale Anzahl der benötigten Werte ein.
!!!
@@rohkorr.sql
@@TagMonatJahr.sql
--!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
COMMIT;
EXIT;
Skript 4: ABLAUFSTRG_TAEGLICH.SQL
----------------------------------------------------------------------------------------- Datei: ABLAUFSTRG_TAEGLICH.SQL
-- Erstellt: 20.11.2000
-- Beschreibung:
-- Ablaufsteuerung: Aufruf der verschiedenen Verarbeitungsstufen
-- zur Messwerterfassung für das ZUB
-- Die Daten werden aus der Tabelle ZUB_DATEN_TMP_TAB, die vom ZUB täglich mit den
-- Messwerten gefüllt wird in die Rohdatentabellen (..._ROH_TAB) eingefügt.
-- Die Tabelle ZUB_DATEN_TMP_TAB entählt die Hausadressen der Messtechnik als
Spaltennamen;
-- die ..._ROH_TAB Tabellen enthalten dagegen, die von der TGA zugewiesenen
-- Spaltennamen aus der Spalte 'SENSOR' aus der Tabelle ZUORDNUNG.
-- Desweiteren werden Mess- und Datumsfehler (s.u.) in die Tabellen ..._FEHL_AUTO_TAB
-- eingetragen.
-- Bem.:
-- 1) Die oben angegebene Zeichenkette ... bedeutet, dass es sich hierbei um
-die Datenbanktabellen aus ZUORDNUNG handelt, die in der Spalte
-'TAB_NAME' definiert wurden. Diese 'TAB_NAME'n werden mit
-Anhängseln aus der Tabelle ANHANG versehen
-(z.B. _ROH_TAB oder _FEHL_AUTO_TAB).
-- 2) Es werden Werte kopiert die in der Tabelle ZUORDNUNG vorhanden sind.
-Um neue Messpunkte anzulegen oder alte zu löschen, muss erst ZUORDNUNG
-aktualisiert werden und dann das Skript 'ablaufstrg_DB_Tabellen_anlegen.sql'
-gestartet werden.
-- Änderungen:
-----------------------------------------------------------------------------------------SET SERVEROUTPUT ON
SET NEWPAGE 0
SET SPACE 0
SET LINESIZE 150
SET PAGESIZE 0
SET ECHO OFF
SET FEEDBACK OFF
SET HEADING OFF
SET TERMOUT off;
-- TEST TEST TEST
start d:\zub_db\sql\test\spool_kopieren_nach_zub_daten_tmp_tab.sql
-- ENDE TEST
spool d:\zub_db\sql\meldungen\Ablaufsteuerung_taeglich.txt
-- TEST TEST TEST
start d:\zub_db\sql\test\kopieren_nach_zub_daten_tmp_tab.sql
-- COMMIT;
-- ENDE TEST
BEGIN
DBMS_OUTPUT.PUT_LINE('Tägliche Ablaufsteuerung zuletzt gestartet am:
'||to_char(SYSDATE,'dd.mm.yyyy hh24:mi'));
END;
/
75
Anhang
-- Anlegen und Aktualisieren der Zeittabelle zur Referenzierung der Datumseinträge.
@@zeittab;
-- Verbessern falscher Datums in ZUB_DATEN_TMP_TAB
BEGIN
DBMS_OUTPUT.PUT_LINE('------------------------------------------------------------------');
DBMS_OUTPUT.PUT_LINE('-- Verbessern falscher Datums');
datum_verbessern;
DBMS_OUTPUT.PUT_LINE('------------------------------------------------------------------');
END;
/
COMMIT;
-- Dies ist eine Spool-Datei die aus spool_Rohdaten_eintragen.sql erstellt wurde.
-- Hier werden die Daten in die _TMP und _ROH_TAB Tabellen aufgenommen.
@@Rohdaten_eintragen.sql
-- Eintrag in Fehlertabellen der Datenbank (Prüfung, ob alle Daten vorhanden sind).
-- 1) Fehlende Datums
-- 2) Nicht vorhandene Messpunktwerte
-- 3) Eintrag von Abweichungen des Messbereichs.
@@fehler_eintragen.sql
SPOOL OFF;
COMMIT;
EXIT;
76
Anhang
Skript 5: DATENBANKTABELLEN_ANLEGEN.SQL
----------------------------------------------------------------------------------------- Datei: DATENBANKTABELLEN_ANLEGEN.SQL
-- Erstellt: 05.10.2000
-- Beschreibung:
-- Automatisches Anlegen der Oracle Tabellen, aus der Tabelle Zuordnung.
-- Es werden alle Datenpunkte aus der Tabelle Zuordnung überprüft; die
-- Tabellen geändert oder eine neue Tabelle erstellt.
---Änderungen:
------------------------------------------------------------------------------------------SET SERVEROUTPUT ON
DECLARE
-- Formate von verschiedenen Spalten
fehler_format VARCHAR2(20) := ' NUMBER(2)';
-- Die Spalten der Fehlertabellen werden
mit diesem Format gefüllt.
raum_format
VARCHAR2(20) := ' VARCHAR2(5)';
-- Die Raumnummern werden so angelegt.
-- Beim Anlegen der Tabellen werden verschiedenste Optionen angegeben
-- Date Tablespace mit den Messdaten des ZUB
-- Tabellen, die nur Spalten, aber keine Werte enthalten
db_zub_daten_storage VARCHAR2(150) := ' TABLESPACE ZUB_DATEN STORAGE (INITIAL 1M NEXT
10K MINEXTENTS 1 MAXEXTENTS 50)';
-- Temporäre Tabellen
db_zub_daten_tmp_storage
VARCHAR2(150) := ' TABLESPACE ZUB_DATEN STORAGE (INITIAL
20M NEXT 1M MINEXTENTS 1 MAXEXTENTS 50) PARALLEL (DEGREE 5) ';
-- Rohdaten und korrigierte Rohdaten sind sehr grosse Tabellen
db_zub_daten_roh_storage
VARCHAR2(150) := ' TABLESPACE ZUB_DATEN STORAGE (INITIAL
20M NEXT 20M MINEXTENTS 1 MAXEXTENTS 200) PARALLEL (DEGREE 5) ';
-- Tageswerte (Mittelwerte)
db_zub_daten_tag_storage
VARCHAR2(150) := ' TABLESPACE ZUB_DATEN STORAGE (INITIAL
10M NEXT 10M MINEXTENTS 1 MAXEXTENTS 50) PARALLEL (DEGREE 5) ';
-- Monats und Jahresmittelwerte
db_zub_daten_mon_jahr_storage VARCHAR2(150) := ' TABLESPACE ZUB_DATEN STORAGE (INITIAL 5M
NEXT 5M MINEXTENTS 1 MAXEXTENTS 50) PARALLEL (DEGREE 5) ';
-- Fehlertabellen
db_zub_daten_fehl_storage
VARCHAR2(150) := ' TABLESPACE ZUB_DATEN STORAGE (INITIAL
10M NEXT 10M MINEXTENTS 1 MAXEXTENTS 200) PARALLEL (DEGREE 5) ';
-- INDX Tablespace (Beschreibung wie oben)
db_indx_storage
VARCHAR2(150) := ' USING INDEX
1M NEXT 10K MINEXTENTS 1 MAXEXTENTS 50) ENABLE ';
db_indx_tmp_storage
VARCHAR2(150) := ' USING INDEX
5M NEXT 2M MINEXTENTS 1 MAXEXTENTS 50) ENABLE ';
db_indx_roh_storage
VARCHAR2(150) := ' USING INDEX
10M NEXT 10M MINEXTENTS 1 MAXEXTENTS 200) ENABLE ';
db_indx_tag_storage
VARCHAR2(150) := ' USING INDEX
5M NEXT 5M MINEXTENTS 1 MAXEXTENTS 200) ENABLE ';
db_indx_mon_jahr_storage VARCHAR2(150) := ' USING INDEX
5M NEXT 5M MINEXTENTS 1 MAXEXTENTS 200) ENABLE ';
db_indx_fehl_storage
VARCHAR2(150) := ' USING INDEX
5M NEXT 5M MINEXTENTS 1 MAXEXTENTS 200) ENABLE ';
TABLESPACE INDX STORAGE (INITIAL
TABLESPACE INDX STORAGE (INITIAL
TABLESPACE INDX STORAGE (INITIAL
TABLESPACE INDX STORAGE (INITIAL
TABLESPACE INDX STORAGE (INITIAL
TABLESPACE INDX STORAGE (INITIAL
-- Variablen und CURSOR für die Verarbeitung.
c_dbms_tab_name
INTEGER;
c_dbms_spalte
INTEGER;
c_dbms_copy
INTEGER;
dummy
INTEGER;
tmp_name
Zuordnung.tab_name%TYPE; -- Temporärer Speicher für
aktuellen Tabellennamen (DBMS_SQL Funktion)
77
Anhang
anhaengsel
der Tabelle Anhang
spalte_vorh
für 'spalte'
spalte_sensor
flag_fehler_zuordnung
n_exception_flag
anhang.anhang%TYPE :=' ';
-- Variable zur Selektion
CURSOR c_namen
IS SELECT DISTINCT upper(tab_name) tab_name from zuordnung;
user_tab_columns.column_name%TYPE;
-- Zweite Variable
VARCHAR2(30);
NUMBER(2);
NUMBER(6);
CURSOR c_spalten
IS SELECT upper(z.sensor) sensor, upper(hw_adr) hw_adr,
upper(format) format FROM zuordnung z WHERE upper(z.tab_name)=upper(tmp_name);
CURSOR c_anhang
IS SELECT upper(anhang) anhang FROM anhang;
CURSOR c_spalte_vorh IS
SELECT user_tab.column_name FROM user_tab_columns user_tab
WHERE upper(tmp_name) = upper(user_tab.table_name)
AND upper(user_tab.column_name) = upper(spalte_sensor);
BEGIN
DBMS_OUTPUT.ENABLE(1000000);
DBMS_OUTPUT.PUT_LINE('--*****************************************************--');
DBMS_OUTPUT.PUT_LINE('-DB_Tabellen_anlegen.sql');
SELECT flag INTO flag_fehler_zuordnung FROM zuordnung_geprueft;
IF flag_fehler_zuordnung != 0 THEN
DBMS_OUTPUT.PUT_LINE('-- FEHLER in ZUORDNUNG!!!');
DBMS_OUTPUT.PUT_LINE('-Es werden keine Datenbanktabellen
angelegt oder verändert.');
DBMS_OUTPUT.PUT_LINE('-Verbessern Sie den/die oben genannten
Fehler und starten Sie das Skript erneut.');
-- Das gewünschten flag Fehler heraussuchen. Der flag_fehler_zuordnung ist nach
Bits geordnet (siehe prouzedur_zuordnung_pruefen.sql)
ELSE
-- ZUORDNUNG ist mit ZUB_DATEN_TMP_TAB abgeglichen. Die Datenbanktabellen werden
angelegt oder geändert.
c_dbms_tab_name
:= DBMS_SQL.OPEN_CURSOR;
c_dbms_spalte
:= DBMS_SQL.OPEN_CURSOR;
c_dbms_copy
:= DBMS_SQL.OPEN_CURSOR;
-- Alle Tabellen mit Spalten aus Zuordnung anlegen.
FOR n IN c_namen LOOP
n_exception_flag := 1000;
-- kopieren des Tabellennamens in eine temporäre Variable (sonst
funktioniert CURSOR c_spalten nicht)
tmp_name := upper(n.tab_name);
-- Anlegen der Tabellennamen, wie sie in der Tabelle Zuordnung stehen.
-- Prüfen, ob die Standardtabellen vorhanden sind.
IF tab_vorhanden(tmp_name) = FALSE THEN
-- Tabelle nicht vorhanden. Es werden die Tabellen mit allen
Spalten aus ZUORDNUNG für die Datenbank erstellt.
-- Die Tabelle tmp_name ist nur für das Anlegen der anderen
Tabellen zuständig. Sie bleibt leer.
n_exception_flag := 2000;
DBMS_SQL.PARSE(c_dbms_tab_name,'CREATE TABLE ' || tmp_name || '
(datum DATE, r_nr '||raum_format||') '||db_zub_daten_storage,DBMS_SQL.NATIVE);
dummy := DBMS_SQL.EXECUTE(c_dbms_tab_name);
COMMIT;
DBMS_SQL.PARSE(c_dbms_tab_name,'ALTER TABLE '|| tmp_name ||' ADD
CONSTRAINT pk'||n.tab_name||' PRIMARY KEY (datum, r_nr)
'||db_indx_storage,DBMS_SQL.NATIVE);
dummy := DBMS_SQL.EXECUTE(c_dbms_tab_name);
COMMIT;
-- Anlegen der Fehlertabellen
DBMS_SQL.PARSE(c_dbms_tab_name,'CREATE TABLE ' || tmp_name ||
'_FEHL_AUTO_TAB (datum DATE, r_nr '||raum_format||',check_flag NUMBER(1) DEFAULT NULL,
Bearbeiter VARCHAR2(30)) '||db_zub_daten_fehl_storage,DBMS_SQL.NATIVE);
dummy := DBMS_SQL.EXECUTE(c_dbms_tab_name);
COMMIT;
DBMS_SQL.PARSE(c_dbms_tab_name,'ALTER TABLE '|| tmp_name
||'_FEHL_AUTO_TAB ADD CONSTRAINT pk'||n.tab_name||'_FEHL_AUTO_TAB PRIMARY KEY (datum,
r_nr) '||db_indx_fehl_storage,DBMS_SQL.NATIVE);
dummy := DBMS_SQL.EXECUTE(c_dbms_tab_name);
COMMIT;
-- Fehlertabelle extra erstellen, da Sie später ein anderes NUMBER
Format erhält.
78
Anhang
DBMS_SQL.PARSE(c_dbms_tab_name,'CREATE TABLE ' || tmp_name ||
'_FEHL_INV_TAB (datum DATE, r_nr '||raum_format||')
'||db_zub_daten_fehl_storage,DBMS_SQL.NATIVE);
dummy := DBMS_SQL.EXECUTE(c_dbms_tab_name);
COMMIT;
DBMS_SQL.PARSE(c_dbms_tab_name,'ALTER TABLE '|| tmp_name
||'_FEHL_INV_TAB ADD CONSTRAINT pk'||n.tab_name||'_FEHL_INV_TAB PRIMARY KEY (datum,
r_nr) '|| db_indx_fehl_storage,DBMS_SQL.NATIVE);
dummy := DBMS_SQL.EXECUTE(c_dbms_tab_name);
COMMIT;
DBMS_OUTPUT.PUT_LINE('--');
DBMS_OUTPUT.PUT_LINE('--');
DBMS_OUTPUT.PUT_LINE('-Tabelle ' || tmp_name || '
erstellt.');
FOR spalte IN c_spalten LOOP
n_exception_flag := 3000;
-- Einfügen der Spalten
IF
spalte_in_tabelle('ZUB_DATEN_TMP_TAB',UPPER(spalte.hw_adr)) =TRUE AND
spalte_in_tabelle(tmp_name,spalte.sensor) = FALSE THEN
-- Die Spalte ist in ZUB_DATEN_TMP_TAB vorhanden, aber
nicht in der DB Tabellen.
db_spalte_anlegen(tmp_name,spalte.sensor,spalte.format);
-- Fehlertabellen mit Spalten werden automatisch im
Format Number(2) angelegt.
db_spalte_anlegen(tmp_name||'_FEHL_AUTO_TAB',spalte.sensor,spalte.format);
db_spalte_anlegen(tmp_name||'_FEHL_INV_TAB',spalte.sensor,spalte.format);
DBMS_OUTPUT.PUT_LINE('-Spalte ' ||
spalte.sensor || ' eingefügt.');
COMMIT;
ELSIF
spalte_in_tabelle('ZUB_DATEN_TMP_TAB',UPPER(spalte.hw_adr)) =FALSE THEN
DBMS_OUTPUT.PUT_LINE('-- Fehler oder Warnung:
Tabelle neu angelegt und der Versuch Spalten einzufügen.');
DBMS_OUTPUT.PUT_LINE('-Spalte: ' ||
spalte.sensor || ' für Tabelle '|| tmp_name||' in ZUORDNUNG nicht als
'||spalte.hw_adr||' in ZUB_DATEN_TMP_TAB gefunden.');
DBMS_OUTPUT.PUT_LINE('-Spalte wird
nicht angelegt.');
DBMS_OUTPUT.PUT_LINE('-Prüfen Sie, ob
nicht eine Primary Key Verletzung in ZUORDNUNG auftrat.');
END IF;
END LOOP;
-- Kopieren der Tabelle 'tmp_name' in die Datenbanktabellen mit
den Anhängseln aus der Tabelle 'ANHANG'
FOR a IN c_anhang
LOOP
n_exception_flag := 4000;
-- Tabellen aus ZUORDNUNG mit Anhang aus Tabelle ANHANG
erstellen.
IF a.anhang !='_FEHL_AUTO_TAB' AND a.anhang
!='_FEHL_INV_TAB' THEN -- 1:1 kopieren aller Tabellen bis auf die Fehlertabellen (diese
werden oben angelegt).
-- Unterscheiden, welche Tabelle gerade angelegt
wird.
IF a.anhang = '_ROH_TAB' OR a.anhang =
'_ROHKORR_TAB' THEN
n_exception_flag := 4010;
DBMS_SQL.PARSE(c_dbms_copy,'CREATE TABLE '
|| tmp_name || a.anhang || ' '||db_zub_daten_roh_storage||' AS (SELECT * FROM ' ||
n.tab_name || ')',DBMS_SQL.NATIVE);
dummy := DBMS_SQL.EXECUTE(c_dbms_copy);
COMMIT;
-- PRIMARY KEY hinzufügen
DBMS_SQL.PARSE(c_dbms_copy,'ALTER TABLE ' ||
tmp_name || a.anhang || ' ADD PRIMARY KEY (datum, r_nr)
'||db_indx_roh_storage,DBMS_SQL.NATIVE);
dummy := DBMS_SQL.EXECUTE(c_dbms_copy);
ELSIF
a.anhang = '_TAG_TAB' THEN
n_exception_flag := 4020;
DBMS_SQL.PARSE(c_dbms_copy,'CREATE TABLE '
|| tmp_name || a.anhang || ' '||db_zub_daten_tag_storage||' AS (SELECT * FROM ' ||
n.tab_name || ')',DBMS_SQL.NATIVE);
dummy := DBMS_SQL.EXECUTE(c_dbms_copy);
79
Anhang
COMMIT;
-- PRIMARY KEY hinzufügen
DBMS_SQL.PARSE(c_dbms_copy,'ALTER TABLE ' ||
tmp_name || a.anhang || ' ADD PRIMARY KEY (datum, r_nr)
'||db_indx_tag_storage,DBMS_SQL.NATIVE);
dummy := DBMS_SQL.EXECUTE(c_dbms_copy);
ELSIF
a.anhang = '_MONAT_TAB' OR a.anhang =
'_JAHR_TAB' THEN
n_exception_flag := 4030;
DBMS_SQL.PARSE(c_dbms_copy,'CREATE TABLE '
|| tmp_name || a.anhang || ' '||db_zub_daten_mon_jahr_storage||' AS (SELECT * FROM ' ||
n.tab_name || ')',DBMS_SQL.NATIVE);
dummy := DBMS_SQL.EXECUTE(c_dbms_copy);
COMMIT;
-- PRIMARY KEY hinzufügen
DBMS_SQL.PARSE(c_dbms_copy,'ALTER TABLE ' ||
tmp_name || a.anhang || ' ADD PRIMARY KEY (datum, r_nr)
'||db_indx_mon_jahr_storage,DBMS_SQL.NATIVE);
dummy := DBMS_SQL.EXECUTE(c_dbms_copy);
ELSE
n_exception_flag := 4040;
-- _TMP_TAB
DBMS_SQL.PARSE(c_dbms_copy,'CREATE TABLE '
|| tmp_name || a.anhang || ' '||db_zub_daten_tmp_storage||' AS (SELECT * FROM ' ||
n.tab_name || ')',DBMS_SQL.NATIVE);
dummy := DBMS_SQL.EXECUTE(c_dbms_copy);
COMMIT;
-- PRIMARY KEY hinzufügen
DBMS_SQL.PARSE(c_dbms_copy,'ALTER TABLE ' ||
tmp_name || a.anhang || ' ADD PRIMARY KEY (datum, r_nr)
'||db_indx_tmp_storage,DBMS_SQL.NATIVE);
dummy := DBMS_SQL.EXECUTE(c_dbms_copy);
END IF;
n_exception_flag := 4050;
COMMIT;
END IF;
-- Die Fehlertabellen wurden oben schon angelegt, aber für
eine Ausgabe ist das übersichtlicher.
DBMS_OUTPUT.PUT_LINE('--');
DBMS_OUTPUT.PUT_LINE('-Tabelle ' || tmp_name
||a.anhang ||' als Abbildung von '||tmp_name||' erstellt.');
END LOOP;
ELSE
-- Tabelle ist vorhanden! Gibt es schon diese Spalte?
n_exception_flag := 5000;
FOR spalte IN c_spalten LOOP
-- Spaltenname aus Tabelle tmp_name selektieren.
-DBMS_OUTPUT.PUT_LINE('-- Spalte: ' || spalte || ' Tab_name:
' || tmp_name);
IF
spalte_in_tabelle('ZUB_DATEN_TMP_TAB',UPPER(spalte.hw_adr)) =TRUE AND
spalte_in_tabelle(tmp_name,spalte.sensor) = FALSE THEN
-- Die Spalte ist in ZUB_DATEN_TMP_TAB vorhanden,
aber nicht in der DB Tabellen.
spalte_sensor := spalte.sensor;
OPEN c_spalte_vorh;
FETCH c_spalte_vorh INTO spalte_vorh; -Selektieren des Spaltennamens aus der DB Tabelle.
IF c_spalte_vorh%NOTFOUND THEN
spalte_vorh := NULL;
END IF;
CLOSE c_spalte_vorh;
-- Gibt es Spaltennamen?
IF spalte_vorh IS NULL THEN
--DBMS_OUTPUT.PUT_LINE('-- Neue Spalte: ' ||
spalte.sensor || ' in Tabelle: ' || tmp_name);
anhaengsel := ''; -- anhängsel initialiseren
für LOOP
OPEN c_anhang;
-- Eintrag in die Tabelle 'tmp_name' und in
die Tabellen 'tmp_name' mit ihren Anhängseln aus der Tabelle 'ANHANG'.
LOOP
80
Anhang
db_spalte_anlegen(tmp_name||anhaengsel,spalte.sensor,spalte.format);
FETCH c_anhang INTO anhaengsel;
EXIT WHEN c_anhang%NOTFOUND;
END LOOP;
DBMS_OUTPUT.PUT_LINE('-- Neue Spalte: ' ||
spalte.sensor || ' in DB Tabellen mit: ' || tmp_name ||' eingefügt.');
COMMIT;
CLOSE c_anhang;
END IF;
ELSIF
spalte_in_tabelle('ZUB_DATEN_TMP_TAB',UPPER(spalte.hw_adr)) = FALSE THEN
DBMS_OUTPUT.PUT_LINE('-- Fehler oder Warnung: Beim
Einfügen einer neuen Spalte in bestehende Tabelle.');
DBMS_OUTPUT.PUT_LINE('-Spalte: ' ||
spalte.sensor || ' für Tabelle '|| tmp_name||' in ZUORDNUNG nicht als
'||spalte.hw_adr||' in ZUB_DATEN_TMP_TAB gefunden.');
DBMS_OUTPUT.PUT_LINE('-Spalte wird
nicht angelegt.');
DBMS_OUTPUT.PUT_LINE('-Prüfen Sie, ob
nicht eine Primary Key Verletzung in ZUORDNUNG auftrat.');
END IF;
END LOOP;
COMMIT;
END IF;
DBMS_OUTPUT.PUT_LINE('--');
DBMS_OUTPUT.PUT_LINE('--');
END LOOP;
DBMS_OUTPUT.PUT_LINE('-------------------------------------------------------');
DBMS_SQL.CLOSE_CURSOR(c_dbms_tab_name);
DBMS_SQL.CLOSE_CURSOR(c_dbms_spalte);
DBMS_SQL.CLOSE_CURSOR(c_dbms_copy);
END IF;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('-- Fehler: Exception OTHERS in DB_Tabellen_anlegen.sql .');
DBMS_OUTPUT.PUT_LINE('-Flag: '||n_exception_flag||'.');
IF c_spalte_vorh%ISOPEN THEN
CLOSE c_spalte_vorh;
END IF;
IF c_namen%ISOPEN THEN
CLOSE c_namen;
END IF;
IF c_spalten%ISOPEN THEN
CLOSE c_spalten;
END IF;
IF c_anhang%ISOPEN THEN
CLOSE c_anhang;
END IF;
IF DBMS_SQL.IS_OPEN(c_dbms_tab_name) THEN
DBMS_SQL.CLOSE_CURSOR(c_dbms_tab_name);
END IF;
IF DBMS_SQL.IS_OPEN(c_dbms_spalte) THEN
DBMS_SQL.CLOSE_CURSOR(c_dbms_spalte);
END IF;
IF DBMS_SQL.IS_OPEN(c_dbms_copy) THEN
DBMS_SQL.CLOSE_CURSOR(c_dbms_copy);
END IF;
RAISE;
END;
/
COMMIT;
--select table_name from user_tables;
--desc Klima;
--desc StdR;
81
Anhang
Skript 6: DATENBANKTABELLEN_LEEREN.SQL
----------------------------------------------------------------------------------------- ACHTUNG: Dieses Skript darf nur bei Inbetriebnahme der Datenbank gestartet werden.
----------------------------------------------------------------------------------------- Datei: DATENBANKTABELLEN_LEEREN.SQL
-- Erstellt: 05.10.2000
-- Beschreibung:
-- Leeren aller Datenbanktabellen mit den Anhängseln aus der Tabelle 'Anhang'
-- und der Datenbanktabelle ohne Anhängsel.
---Änderungen:
----------------------------------------------------------------------------------------SET SERVEROUTPUT ON
-- Löschen der Tabellen und neu anlegen im unteren Teil
-- kann hier auch automatisiert werden.
DECLARE
c_dbms_del
INTEGER;
dummy
INTEGER;
name
Zuordnung.tab_name%TYPE;
tmp_name
Zuordnung.tab_name%TYPE;
CURSOR c_namen IS SELECT DISTINCT upper(tab_name) from zuordnung;
CURSOR c_anhang IS SELECT upper(anhang) FROM anhang;
anhaengsel
anhang.anhang%TYPE;
BEGIN
c_dbms_del
:= dbms_sql.open_cursor;
OPEN c_namen;
-- Alle Tabellen mit Spalten aus Zuordnung anlegen.
LOOP
FETCH c_namen INTO name;
EXIT WHEN c_namen%NOTFOUND;
-- Anlegen der Tabellennamen, wie sie in der Tabelle Zuordnung stehen.
-- Prüfen, ob die Standardtabellen vorhanden sind.
tmp_name := upper(name);
IF tab_vorhanden(tmp_name) = TRUE THEN
DBMS_SQL.PARSE(c_dbms_del,'TRUNCATE TABLE ' || tmp_name,DBMS_SQL.NATIVE);
dummy := DBMS_SQL.EXECUTE(c_dbms_del);
DBMS_OUTPUT.PUT_LINE('Tabelle ' || tmp_name || ' geleert.');
END IF;
OPEN c_anhang;
LOOP
FETCH c_anhang INTO anhaengsel;
EXIT WHEN c_anhang%NOTFOUND;
name := upper(tmp_name || anhaengsel);
IF tab_vorhanden(name) = TRUE THEN
DBMS_SQL.PARSE(c_dbms_del,'TRUNCATE TABLE ' ||
name,DBMS_SQL.NATIVE);
dummy := DBMS_SQL.EXECUTE(c_dbms_del);
DBMS_OUTPUT.PUT_LINE('Tabelle ' || name || ' geleert.');
ELSE
DBMS_OUTPUT.PUT_LINE('Tabelle ' || name || ' nicht vorhanden.');
END IF;
COMMIT;
END LOOP;
CLOSE c_anhang;
END LOOP;
DBMS_SQL.CLOSE_CURSOR(c_dbms_del);
CLOSE c_namen;
EXCEPTION
WHEN OTHERS THEN
IF c_namen%ISOPEN THEN
CLOSE c_anhang;
END IF;
IF c_namen%ISOPEN THEN
CLOSE c_namen;
END IF;
IF DBMS_SQL.IS_OPEN(c_dbms_del) THEN
DBMS_SQL.CLOSE_CURSOR(c_dbms_del);
END IF;
RAISE;
END;
82
Anhang
/
COMMIT;
Skript 7: DATENBANKTABELLEN_LOESCHEN.SQL
----------------------------------------------------------------------------------------- ACHTUNG: Dieses Skript darf nur bei Inbetriebnahme der Datenbank gestartet werden.
----------------------------------------------------------------------------------------- Datei: DATENBANKTABELLEN_LOESCHEN.SQL
-- Erstellt: 05.10.2000
-- Beschreibung:
-- Löschen aller Datenbanktabellen mit den Anhängseln aus der Tabelle 'Anhang'
-- und der Datenbanktabelle ohne Anhängsel.
---Änderungen:
-----------------------------------------------------------------------------------------SET SERVEROUTPUT ON
-- Löschen der Tabellen und neu anlegen im unteren Teil
-- kann hier auch automatisiert werden.
DECLARE
c_dbms_del
INTEGER;
dummy
INTEGER;
name
Zuordnung.tab_name%TYPE;
tmp_name
Zuordnung.tab_name%TYPE;
CURSOR c_namen IS SELECT DISTINCT upper(tab_name) from zuordnung;
CURSOR c_anhang IS SELECT upper(anhang) FROM anhang;
anhaengsel
anhang.anhang%TYPE;
zaehl
NUMBER(6) := 0;
-------------------------------------------------------------------------
BEGIN
DBMS_OUTPUT.PUT_LINE('Datenbanktabellen löschen!');
DBMS_OUTPUT.ENABLE(1000000);
c_dbms_del
:= dbms_sql.open_cursor;
-- Alle Tabellen mit Spalten aus Zuordnung anlegen.
OPEN c_namen;
LOOP
FETCH c_namen INTO name;
EXIT WHEN c_namen%NOTFOUND;
zaehl := zaehl + 1;
-- Anlegen der Tabellennamen, wie sie in der Tabelle Zuordnung stehen.
-- Prüfen, ob die Standardtabellen vorhanden sind.
tmp_name := upper(name);
IF tab_vorhanden(tmp_name) = TRUE THEN
DBMS_SQL.PARSE(c_dbms_del,'DROP TABLE ' || tmp_name,DBMS_SQL.NATIVE);
dummy := DBMS_SQL.EXECUTE(c_dbms_del);
DBMS_OUTPUT.PUT_LINE('Tabelle ' || tmp_name || ' gelöscht.');
END IF;
OPEN c_anhang;
LOOP
FETCH c_anhang INTO anhaengsel;
EXIT WHEN c_anhang%NOTFOUND;
name := upper(tmp_name || anhaengsel);
IF tab_vorhanden(name) = TRUE THEN
DBMS_SQL.PARSE(c_dbms_del,'DROP TABLE ' || name,DBMS_SQL.NATIVE);
dummy := DBMS_SQL.EXECUTE(c_dbms_del);
DBMS_OUTPUT.PUT_LINE('Tabelle ' || name || ' gelöscht.');
ELSE
DBMS_OUTPUT.PUT_LINE('Tabelle ' || name || ' nicht vorhanden.');
END IF;
83
Anhang
COMMIT;
END LOOP;
CLOSE c_anhang;
END LOOP;
DBMS_OUTPUT.PUT_LINE('Es wurden ' || zaehl || ' Tabellen gelöscht.');
DBMS_SQL.CLOSE_CURSOR(c_dbms_del);
CLOSE c_namen;
EXCEPTION
WHEN OTHERS THEN
IF c_namen%ISOPEN THEN
CLOSE c_anhang;
END IF;
IF c_namen%ISOPEN THEN
CLOSE c_namen;
END IF;
IF DBMS_SQL.IS_OPEN(c_dbms_del) THEN
DBMS_SQL.CLOSE_CURSOR(c_dbms_del);
END IF;
RAISE;
END;
/
COMMIT;
84
Anhang
Skript 8: FEHL_AUTO_ANZEIGE.SQL
----------------------------------------------------------------------------------------- Datei: FEHL_AUTO_ANZEIGE.SQL
-- Erstellt: 07.12.2000
-- Beschreibung:
-- Füllt die Tabelle FEHL_AUTO_ANZEIGE_TAB für FORMS50 mit Werten
-- Sehen Sie in die Beschreibung von der PROZEDUR_FUELL_FEHL_AUTO_ANZEIGE.SQL
-- Änderungen:
-----------------------------------------------------------------------------------------SET SERVEROUTPUT ON
SET NEWPAGE 1
SET LINESIZE 200
SET SPACE 1
--SET ECHO OFF
--SET FEEDBACK OFF
SET HEADING OFF
SET TERMOUT off;
SPOOL d:\zub_db\sql\meldungen\fehl_auto_anzeige.txt
DECLARE
CURSOR c_tabs IS
SELECT DISTINCT upper(tab_name) tab_name, upper(sensor) sensor, upper(r_nr) r_nr FROM
ZUORDNUNG
ORDER BY tab_name, r_nr, sensor;
n_exception_flag NUMBER(6);
ausgabe VARCHAR2(100);
fehl_nr NUMBER(2);
dummy INTEGER;
c_dbms INTEGER;
BEGIN
DBMS_OUTPUT.ENABLE(1000000);
DBMS_OUTPUT.PUT_LINE('-- Datei erstellt am '||to_char(SYSDATE,'dd.mm.yyyy hh24:mi'));
DBMS_OUTPUT.PUT_LINE('Füllen der Tabelle FEHL_AUTO_ANZEIGE_TAB.');
DBMS_OUTPUT.PUT_LINE('.');
c_dbms := DBMS_SQL.OPEN_CURSOR;
n_exception_flag := 0;
-- Leeren der Tabelle
ausgabe := 'TRUNCATE TABLE FEHL_AUTO_ANZEIGE_TAB';
DBMS_SQL.PARSE(c_dbms,ausgabe,DBMS_SQL.NATIVE);
dummy := DBMS_SQL.EXECUTE(c_dbms);
DBMS_SQL.CLOSE_CURSOR(c_dbms);
n_exception_flag := 100;
-- Füllen der Tabelle
FOR c IN c_tabs LOOP
n_exception_flag := 1000;
-- Aufruf:
Tabellenname, Sensor,
Raumnummer, Fehlerkennung
fuell_fehl_auto_anzeige(c.tab_name,
c.sensor,
c.r_nr,
1);
n_exception_flag := 2000;
fuell_fehl_auto_anzeige(c.tab_name, c.sensor, c.r_nr, 2);
n_exception_flag := 3000;
fuell_fehl_auto_anzeige(c.tab_name, c.sensor, c.r_nr, 3);
END LOOP;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('-- Fehler: Exception OTHERS in FEHL_AUTO_ANZEIGE_SQL.sql
.');
DBMS_OUTPUT.PUT_LINE('-Flag: '||n_exception_flag||'.');
IF c_tabs%ISOPEN THEN
CLOSE c_tabs;
END IF;
IF DBMS_SQL.IS_OPEN(c_dbms) THEN
DBMS_SQL.CLOSE_CURSOR(c_dbms);
85
Anhang
END IF;
RAISE;
END;
/
select count(*) eintraege from FEHL_AUTO_ANZEIGE_TAB;
select tab_name , sensor , r_nr , anz_fehler , to_char(anf_dat,'dd.mm.yyyy hh24:mi')
anfangsdatum , to_char(end_dat,'dd.mm.yyyy hh24:mi') enddatum , fehler_nr from
FEHL_AUTO_ANZEIGE_TAB
order by tab_name,fehler_nr, anf_dat, r_nr, sensor;
SPOOL OFF;
EXIT;
86
Anhang
Skript 9: FEHL_AUTO_MARKIERUNG.SQL
----------------------------------------------------------------------------------------- Datei: FEHL_AUTO_MARKIERUNG.SQL
-- Erstellt: 09.12.2000
-- Beschreibung:
-- Schreibt das Check_flag und den Bearbeiter in die jeweiligen Fehlertabellen.
-- Dazu wird die Tabelle FEHL_AUTO_ANZEIGE_TAB genutzt, die von der SQL Datei
-- FEHL_AUTO_ANZEIGE.SQL gefüllt wird.
-- Änderungen:
-----------------------------------------------------------------------------------------SET SERVEROUTPUT ON
SET NEWPAGE 0
SET SPACE 2
SET LINESIZE 200
SET PAGESIZE 0
SET ECHO OFF
SET FEEDBACK OFF
SET HEADING OFF
SET TERMOUT off;
SPOOL d:\zub_db\sql\meldungen\fehl_auto_markierung.txt
DECLARE
n_exception_flag NUMBER(6);
ausgabe VARCHAR2(255);
fehl_nr NUMBER(2);
dummy INTEGER;
c_dbms INTEGER;
CURSOR c_flag IS
SELECT * FROM fehl_auto_anzeige_tab
WHERE check_flag IS NOT NULL;
BEGIN
DBMS_OUTPUT.ENABLE(1000000);
DBMS_OUTPUT.PUT_LINE('-- Datei erstellt am '||to_char(SYSDATE,'dd.mm.yyyy hh24:mi'));
DBMS_OUTPUT.PUT_LINE('Markieren der Tabellen. FEHL_AUTO_TAB.');
c_dbms := DBMS_SQL.OPEN_CURSOR;
n_exception_flag := 0;
FOR c IN c_flag LOOP
IF c.r_nr != 'ALLE' THEN
n_exception_flag := 1000;
ausgabe := 'UPDATE '||c.tab_name||'_FEHL_AUTO_TAB SET check_flag = 1,
Bearbeiter = '||CHR(39)||c.Bearbeiter||CHR(39)||' WHERE datum BETWEEN :anf_dat AND
:end_dat AND '||c.sensor||' = '||c.fehler_nr||' AND upper(r_nr) =
'||CHR(39)||UPPER(c.r_nr)||CHR(39);
ELSE
-- Es wird das Flag für alle Raumnummern und Sensoren gesetzt.
-- Dies kommt von einen fehlenden Datum in ZUB_DATEN_TMP_TAB
n_exception_flag := 1500;
ausgabe := 'UPDATE '||c.tab_name||'_FEHL_AUTO_TAB SET check_flag = 1,
Bearbeiter = '||CHR(39)||c.Bearbeiter||CHR(39)||' WHERE datum BETWEEN :anf_dat AND
:end_dat';
END IF;
n_exception_flag := 2000;
DBMS_OUTPUT.PUT_LINE(ausgabe);
DBMS_OUTPUT.PUT_LINE('
:anf_dat =
'||to_char(c.anf_dat,'dd.mm.yyyy hh24:mi')||' :end_dat =
'||to_char(c.end_dat,'dd.mm.yyyy hh24:mi'));
DBMS_SQL.PARSE(c_dbms,ausgabe,DBMS_SQL.NATIVE);
DBMS_SQL.BIND_VARIABLE(c_dbms,':anf_dat', c.anf_dat);
DBMS_SQL.BIND_VARIABLE(c_dbms,':end_dat',c.end_dat);
dummy := DBMS_SQL.EXECUTE(c_dbms);
END LOOP;
DBMS_SQL.CLOSE_CURSOR(c_dbms);
-- Füllen der Tabelle
87
Anhang
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('-- Fehler: Exception OTHERS in FEHL_AUTO_MARKIERUNG.SQL
.');
DBMS_OUTPUT.PUT_LINE('-Flag: '||n_exception_flag||'.');
IF DBMS_SQL.IS_OPEN(c_dbms) THEN
DBMS_SQL.CLOSE_CURSOR(c_dbms);
END IF;
RAISE;
END;
/
select * from FEHL_AUTO_ANZEIGE_TAB
WHERE check_flag = 1
order by tab_name,fehler_nr, anf_dat, r_nr, sensor;
SPOOL OFF;
EXIT;
88
Anhang
Skript 10: FEHLER_EINTRAGEN.SQL
----------------------------------------------------------------------------------------- Datei: FEHLER_EINTRAGEN.SQL
-- Erstellt: 13.11.2000
-- Beschreibung:
-- Dieses Skript prüft Fehler in ZUB_DATEN_TMP_TAB und trägt die Fehler in die einzelnen
-- Datenbanktabellen ein.
-- FEHLER 1: Sind alle Datums vorhanden?
-- FEHLER 2: Sind die Werte vorhanden?
-- FEHLER 3: Sind die Werte im Messbereich?
-- Die fehlerhaften Werte werden bei Bedarf in FORMS in die Tabellen ..._FEHL_AUTO_TAB
-- mit obiger Fehlernummer eingetragen.
-----
Bem.:
Es werden nur neuere Datums durchsucht, als die in den Rohdaten stehen.
Dazu wird das neueste Datum von einem Sensor mit seiner Raumnummer
aus ..._ROH_TAB selektiert und dass neueste Datum von ZUB_DATEN_TMP_TAB
-- Änderungen:
-----------------------------------------------------------------------------------------SET SERVEROUTPUT ON
--SET termout off;
--SET echo off;
--SET heading off;
--SET feedback off;
SET LINESIZE 300
--SPOOL d:\zub_db\sql\meldungen\fehler_eintragen.txt
-- Die Datei wird in Ablaufsteuerung Täglich eingetragen.
DECLARE
-- CURSOR FÜR DEN EINTRAG IN DIE ROHDATEN-, TMP-, UND FEHLERTABELLEN
d_anfang
DATE; -- ältestes Datum in ..._TMP_TAB
d_ende
DATE; -- neuestes Datum in ..._TMP_TAB
--d_fehlt
DATE; -- ungültiges Datum
d_mess
DATE; -- ausserhalb des messbereichs
n_exception_flag
NUMBER(10) ;
hk
CHAR(1); -- Abgespeichertes Hochkomma in Tabelle.
ausgabe
VARCHAR2(255);
ausgabe1
VARCHAR2(100);
ausgabe2
VARCHAR2(100);
ausgabe3
VARCHAR2(100);
-- Tabellennamen und Spaltennamen, Minimal- und Maximalwerte
CURSOR c_zuordnung IS
SELECT UPPER(hw_adr) hw_adr, UPPER(r_nr) r_nr ,UPPER(sensor) sensor, UPPER(tab_name)
tab_name, minwert, maxwert FROM zuordnung -- TEST (schnellerer Verlauf) -> WHERE hw_adr
='H00100';
ORDER BY tab_name, r_nr;
--ORDER BY tab_name, r_nr;
-- Cursor gibt die fehlenden Datums in ZUB_DATEN_TMP_TAB (falsche Datums wurden schon
korrigiert)
CURSOR c_datum_fehlt IS
SELECT ref_zeit FROM zeittab
WHERE ref_zeit BETWEEN d_anfang AND d_ende
MINUS
SELECT to_date(datum||uhrzeit,'dd.mm.yyyy hh24:mi') from zub_daten_tmp_tab;
-- CURSOR findet Werte ausserhalb des Messbereichs, die nicht NULL sind.
-- Es muss in diesem Fall ein DBMS_SQL Cursor verwendet werden, da der Spaltenname noch
nicht selektiert ist.
--CURSOR c_mess IS
--SELECT to_date(datum||uhrzeit,'dd.mm.yyyy hh24:mi') FROM zub_daten_tmp_tab
--WHERE spaltenname NOT BETWEEN minimalwert AND maximalwert
--AND spaltenname IS NOT NULL;
-- CURSOR findet nicht vorhandene Werte, die ein Datum haben, aber NULL sind.
--CURSOR c_null IS
--SELECT to_date(datum||uhrzeit,'dd.mm.yyyy hh24:mi') FROM zub_daten_tmp_tab
--WHERE spaltenname IS NULL;
89
Anhang
-- DBMS_SQL
dummy
c_dbms
c_dbms1
c_dbms_mess
INTEGER;
INTEGER;
INTEGER;
INTEGER;
------------------------------------------------------------------------------------------------------------------------------------------------------ BEGINN DES SKRIPTS
BEGIN
DBMS_OUTPUT.ENABLE(1000000);
c_dbms := DBMS_SQL.OPEN_CURSOR;
c_dbms1 := DBMS_SQL.OPEN_CURSOR;
c_dbms_mess := DBMS_SQL.OPEN_CURSOR;
DBMS_OUTPUT.PUT_LINE('---------------------------------------------');
DBMS_OUTPUT.PUT_LINE('-- SQL DATEI: FEHLER_EINTRAGEN');
-- Das Hochkomma ' in hk speichern!
SELECT strich INTO hk FROM hochkomma;
-- neuestes Datum aus ZUB_DATEN_TMP_TAB d_ende ablegen.
--SELECT max(to_date(datum||uhrzeit,'dd.mm.yyyy hh24:mi') ) INTO d_ende FROM
zub_daten_tmp_tab;
--DBMS_OUTPUT.PUT_LINE('-- Ende: ' || to_char(d_ende,'dd.mm hh24:mi'));
<<hauptschleife>>
FOR z IN c_zuordnung LOOP
-- Selektieren des jüngsten und ältesten Datums bei einem Sensor mit bestimmter
R_Nr in den Tabellen ..._TMP_TAB
ausgabe := 'SELECT min(datum), max(datum) FROM '||z.tab_name||'_TMP_TAB WHERE
r_nr = :z_r_nr1 AND '||z.sensor||' IS NOT NULL';
DBMS_SQL.PARSE(c_dbms,ausgabe,DBMS_SQL.NATIVE);
DBMS_SQL.DEFINE_COLUMN(c_dbms, 1, d_anfang);
DBMS_SQL.DEFINE_COLUMN(c_dbms, 2, d_ende);
DBMS_SQL.BIND_VARIABLE(c_dbms, ':z_r_nr1',z.r_nr);
dummy := DBMS_SQL.EXECUTE(c_dbms);
IF DBMS_SQL.FETCH_ROWS(c_dbms) > 0 THEN
DBMS_SQL.COLUMN_VALUE(c_dbms, 1, d_anfang);
--DBMS_OUTPUT.PUT_LINE('Maximales Datum :' ||
to_char(d_anfang,'dd.mm.yyyy hh24:mi'));
END IF;
IF d_anfang IS NULL THEN
-- Datum auf Minimales Datum von ..._ZUB_DATEN_TMP_TAB setzen oder wenn
kein Datum vorhanden ist, alle Datums auffüllen.
SELECT min(to_date(datum||uhrzeit,'dd.mm.yyyy hh24:mi') ),
max(to_date(datum||uhrzeit,'dd.mm.yyyy hh24:mi') ) INTO d_anfang, d_ende FROM
zub_daten_tmp_tab;
END IF;
IF d_anfang != d_ende THEN
-- Nur ausführen, wenn ein Datum gefunden wurde.
--------------------------------------------------------------------------- EINTRAG DER FEHLENDEN DATUMS IN DIE FEHLERTABELLEN (FEHLER 1)
-- DBMS_SQL.CLOSE_CURSOR(c_dbms);
-- c_dbms := DBMS_SQL.OPEN_CURSOR;
n_exception_flag :=3000;
ausgabe := NULL;
<<fehlereintrag>>
FOR fehlt IN c_datum_fehlt LOOP
-d_fehlt := fehlt.ref_zeit;
IF datum_r_nr_in_tab(fehlt.ref_zeit, z.r_nr,
z.tab_name||'_FEHL_AUTO_TAB') = TRUE THEN
ausgabe := 'UPDATE ' || z.tab_name ||'_FEHL_AUTO_TAB SET
'|| z.sensor ||' = 1 WHERE datum = :fehl_ref_zeit AND r_nr = '||hk||z.r_nr||hk;
ELSE
ausgabe := 'INSERT INTO ' || z.tab_name ||'_FEHL_AUTO_TAB
(datum, r_nr,' || z.sensor ||') VALUES( :fehl_ref_zeit, '||hk||z.r_nr||hk||' , 1)';
END IF;
-- n_exception_flag :=3010;
DBMS_SQL.PARSE(c_dbms,ausgabe,DBMS_SQL.NATIVE);
DBMS_SQL.BIND_VARIABLE(c_dbms, ':fehl_ref_zeit',fehlt.ref_zeit);
-DBMS_SQL.BIND_VARIABLE(c_dbms, ':z_r_nr',z.r_nr);
dummy := DBMS_SQL.EXECUTE(c_dbms);
-- Anzeige der fehlenden Datums
90
Anhang
-- DBMS_OUTPUT.PUT_LINE('-- Fehlendes Datum ' ||
to_char(fehlt.ref_zeit,'dd.mm.yyyy hh24:mi') || ' in '|| z.tab_name ||'_FEHL_AUTO_TAB
eingetragen');
END LOOP fehlereintrag;
COMMIT;
IF ausgabe IS NOT NULL THEN
ausgabe1 := ('-- Fehler 1: Es sind fehlende Datums gefunden
worden.');
ELSE
ausgabe1 := NULL;
END IF;
-- ENDE DATUM FEHLT
--------------------------------------------------------------------------- PRÜFEN OB ALLE WERTE VORHANDEN SIND (FEHLER 2)
-- Cursor zur Suche von NULL Werten.
n_exception_flag :=4000;
ausgabe := 'SELECT to_date(datum||uhrzeit,'||hk||'dd.mm.yyyy
hh24:mi'||hk||') FROM ZUB_DATEN_TMP_TAB WHERE to_date(datum||uhrzeit,'||hk||'dd.mm.yyyy
hh24:mi'||hk||') > :d_anfang AND '||z.hw_adr||' IS NULL';
-- DBMS_OUTPUT.PUT_LINE(ausgabe);
DBMS_SQL.PARSE(c_dbms_mess,ausgabe,DBMS_SQL.NATIVE);
DBMS_SQL.DEFINE_COLUMN(c_dbms_mess, 1, d_mess);
DBMS_SQL.BIND_VARIABLE(c_dbms_mess, ':d_anfang',d_anfang);
--n_exception_flag :=4130;
dummy := DBMS_SQL.EXECUTE(c_dbms_mess);
-- Setzen der Default Ausgabe.
ausgabe := NULL;
-- '-- Keine fehlenden Messwerte';
<<fehlendeWerte>>
LOOP
n_exception_flag :=4200;
IF DBMS_SQL.FETCH_ROWS(c_dbms_mess) > 0 THEN
-- Datums heraussuchen.
DBMS_SQL.COLUMN_VALUE(c_dbms_mess, 1, d_mess);
-- Eintragen in die Fehlertabelle
IF datum_r_nr_in_tab(d_mess, z.r_nr,
z.tab_name||'_FEHL_AUTO_TAB') = TRUE THEN
ausgabe := 'UPDATE ' || z.tab_name
||'_FEHL_AUTO_TAB fehl SET '|| z.sensor ||' =2 WHERE datum = :v_d_mess AND r_nr =
:z_r_nr3';
ELSE
ausgabe := 'INSERT INTO ' || z.tab_name
||'_FEHL_AUTO_TAB(datum, r_nr ,'|| z.sensor ||') VALUES (:v_d_mess ,:z_r_nr3, 2)';
END IF;
DBMS_SQL.PARSE(c_dbms,ausgabe,DBMS_SQL.NATIVE);
DBMS_SQL.BIND_VARIABLE(c_dbms, ':v_d_mess',d_mess);
DBMS_SQL.BIND_VARIABLE(c_dbms, ':z_r_nr3',z.r_nr);
dummy := DBMS_SQL.EXECUTE(c_dbms);
COMMIT;
ELSE
EXIT fehlendeWerte;
END IF;
END LOOP fehlendeWerte;
IF ausgabe IS NOT NULL THEN -- Es wurden fehlende Werte gefunden.
ausgabe2 := '-- Fehler 2: Mindestens ein Messwert ist nicht
vorhanden';
ELSE
ausgabe2 := NULL;
END IF;
COMMIT;
-- ENDE WERTE VORHANDEN
--------------------------------------------------------------------------- PRÜFEN DES MESSBEREICHS EINTRAG IN DIE FEHLERTABELLEN (FEHLER 3)
n_exception_flag :=5000;
ausgabe := 'SELECT to_date(datum||uhrzeit,'||hk||'dd.mm.yyyy
hh24:mi'||hk||') FROM ZUB_DATEN_TMP_TAB WHERE '|| z.hw_adr ||' NOT BETWEEN
'||z.minwert||' AND '||z.maxwert||' AND to_date(datum||uhrzeit,'||hk||'dd.mm.yyyy
hh24:mi'||hk||') > :d_anfang AND '||z.hw_adr||' IS NOT NULL';
--DBMS_OUTPUT.PUT_LINE(ausgabe);
-- n_exception_flag :=5100;
DBMS_SQL.PARSE(c_dbms_mess,ausgabe,DBMS_SQL.NATIVE);
DBMS_SQL.DEFINE_COLUMN(c_dbms_mess, 1, d_mess);
DBMS_SQL.BIND_VARIABLE(c_dbms_mess, ':d_anfang',d_anfang);
dummy := DBMS_SQL.EXECUTE(c_dbms_mess);
91
Anhang
-- Setzen der Default Ausgabe.
ausgabe := NULL;
-- '-- Keine Messwerte ausserhalb des Messbereichs: Minimum '|| z.minwert
||' und Maximum '|| z.maxwert;
<<messbereich>>
LOOP
-- n_exception_flag :=5200;
IF DBMS_SQL.FETCH_ROWS(c_dbms_mess) > 0 THEN
-- Datums heraussuchen.
DBMS_SQL.COLUMN_VALUE(c_dbms_mess, 1, d_mess);
-- Eintragen in die Fehlertabelle
IF datum_r_nr_in_tab(d_mess, z.r_nr,
z.tab_name||'_FEHL_AUTO_TAB') = TRUE THEN
ausgabe := 'UPDATE ' || z.tab_name
||'_FEHL_AUTO_TAB fehl SET '|| z.sensor ||' =3 WHERE datum = :v_d_mess AND r_nr =
:z_r_nr4';
ELSE
ausgabe := 'INSERT INTO ' || z.tab_name
||'_FEHL_AUTO_TAB(datum, r_nr ,'|| z.sensor ||') VALUES (:v_d_mess ,:z_r_nr4, 3)';
END IF;
DBMS_SQL.PARSE(c_dbms,ausgabe,DBMS_SQL.NATIVE);
DBMS_SQL.BIND_VARIABLE(c_dbms, ':v_d_mess',d_mess);
DBMS_SQL.BIND_VARIABLE(c_dbms, ':z_r_nr4',z.r_nr);
dummy := DBMS_SQL.EXECUTE(c_dbms);
-- Löschen der Werte aus ZUB_DATEN_TMP_TAB, die nicht im
Messbereich liegen.
-- Damit kann dann ZUB_DATEN_TMP_TAB in die ..._ROHKORR_TAB
kopiert werden.
--ausgabe := 'UPDATE zub_daten_tmp_tab SET '||z.hw_adr||' =
NULL WHERE to_date(datum||uhrzeit,'||hk||'dd.mm.yyyy hh24:mi'||hk||') = :v_d_mess';
--DBMS_SQL.PARSE(c_dbms1,ausgabe,DBMS_SQL.NATIVE);
--DBMS_SQL.BIND_VARIABLE(c_dbms1, ':v_d_mess',d_mess);
--dummy := DBMS_SQL.EXECUTE(c_dbms1);
--COMMIT;
ELSE
EXIT messbereich;
END IF;
END LOOP messbereich;
IF ausgabe IS NOT NULL THEN -- Es wurden Abweichungen im Messbereich
eingetragen
ausgabe3 := '-- Fehler 3: Mindestens ein Messwert ausserhalb des
Messbereichs';
ELSE
ausgabe3 := NULL;
END IF;
COMMIT;
-- ENDE MESSBEREICH
----------------------------------------------------------------------------------------- Fehlerausgabe
IF ausgabe1 IS NOT NULL OR ausgabe2 IS NOT NULL OR ausgabe3 IS NOT NULL
THEN
DBMS_OUTPUT.PUT_LINE('...........................................................
...................');
DBMS_OUTPUT.PUT_LINE('-- Tabelle: ' || z.tab_name || ' Raumnr.:
'|| z.r_nr || ' Sensor: ' || z.sensor ||' Minimum: '|| z.minwert ||' Maximum: '||
z.maxwert ||' Sauter: '||z.hw_adr);
DBMS_OUTPUT.PUT_LINE('-- Prüfen ab:
'||to_char(d_anfang,'dd.mm.yyyy hh24:mi'));
DBMS_OUTPUT.PUT_LINE(ausgabe1);
DBMS_OUTPUT.PUT_LINE(ausgabe2);
DBMS_OUTPUT.PUT_LINE(ausgabe3);
--ELSE
-DBMS_OUTPUT.PUT_LINE('..................................................................
............');
--DBMS_OUTPUT.PUT_LINE('-- Tabelle: ' || z.tab_name || ' Raumnr.:
'|| z.r_nr || ' Sensor: ' || z.sensor ||' Minimum: '|| z.minwert ||' Maximum: '||
z.maxwert ||' Sauter: '||z.hw_adr);
--DBMS_OUTPUT.PUT_LINE('-- Prüfen ab:
'||to_char(d_anfang,'dd.mm.yyyy hh24:mi'));
--DBMS_OUTPUT.PUT_LINE('-- Es wurden keine Fehler gefunden');
END IF;
ELSE
-- Exception Aulösen
92
Anhang
n_exception_flag :=5000;
DBMS_OUTPUT.PUT_LINE('-- Tabelle: ' || z.tab_name || ' Raumnr.: '||
z.r_nr || ' Sensor: ' || z.sensor ||' Minimum: '|| z.minwert ||' Maximum: '|| z.maxwert
||' Sauter: '||z.hw_adr);
DBMS_OUTPUT.PUT_LINE('-- Es sind keine neueren Datums in ..._TMP_TAB als
in '||z.tab_name||'_ROH_TAB gefunden worden.');
DBMS_OUTPUT.PUT_LINE('-- Keine Fehlerprüfung durchgeführt.');
END IF;
END LOOP hauptschleife;
n_exception_flag :=10000;
-- Schliessen aller CURSOR
IF DBMS_SQL.IS_OPEN(c_dbms_mess) THEN
DBMS_SQL.CLOSE_CURSOR(c_dbms_mess);
END IF;
IF DBMS_SQL.IS_OPEN(c_dbms) THEN
DBMS_SQL.CLOSE_CURSOR(c_dbms);
END IF;
IF DBMS_SQL.IS_OPEN(c_dbms1) THEN
DBMS_SQL.CLOSE_CURSOR(c_dbms1);
END IF;
IF c_datum_fehlt%ISOPEN THEN
CLOSE c_datum_fehlt;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND in fehler_eintragen.sql ,
FLAG: '
|| n_exception_flag);
DBMS_OUTPUT.PUT_LINE('Wahrscheinlich ist ZUB_DATEN_TMP_TAB leer');
IF DBMS_SQL.IS_OPEN(c_dbms_mess) THEN
DBMS_SQL.CLOSE_CURSOR(c_dbms_mess);
END IF;
IF DBMS_SQL.IS_OPEN(c_dbms) THEN
DBMS_SQL.CLOSE_CURSOR(c_dbms);
END IF;
IF DBMS_SQL.IS_OPEN(c_dbms1) THEN
DBMS_SQL.CLOSE_CURSOR(c_dbms1);
END IF;
IF c_zuordnung%ISOPEN THEN
CLOSE c_zuordnung;
END IF;
IF c_datum_fehlt%ISOPEN THEN
CLOSE c_datum_fehlt;
END IF;
RAISE;
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS in fehler_eintragen.sql ,
FLAG: ' ||
n_exception_flag);
IF DBMS_SQL.IS_OPEN(c_dbms_mess) THEN
DBMS_SQL.CLOSE_CURSOR(c_dbms_mess);
END IF;
IF DBMS_SQL.IS_OPEN(c_dbms) THEN
DBMS_SQL.CLOSE_CURSOR(c_dbms);
END IF;
IF DBMS_SQL.IS_OPEN(c_dbms1) THEN
DBMS_SQL.CLOSE_CURSOR(c_dbms1);
END IF;
IF c_zuordnung%ISOPEN THEN
CLOSE c_zuordnung;
END IF;
IF c_datum_fehlt%ISOPEN THEN
CLOSE c_datum_fehlt;
END IF;
RAISE;
END;
/
COMMIT;
93
Anhang
Skript 11: FUELLE_FEHL_INV_TAB.SQL
----------------------------------------------------------------------------------------- Datei: FUELLE_FEHL_INV_TAB.SQL
-- Erstellt: 11.12.2000
-------
Beschreibung:
Als erstes werden die Tabellen ..._FEHL_INV_TAB geleert und mit den
Datums und Raumnummern aus ...ROH_TAB gefüllt. (Die Datenspalten aus
..._FEHL_INV_TAB wurden mit DEFAULT 1 angelegt.)
Als zweites werden die Werte aus FEHL_TAB gelesen und die Werte auf NULL
zurückgesetzt die dort referenziert wurden.
-- Änderungen:
-----------------------------------------------------------------------------------------SET SERVEROUTPUT ON
SET LINESIZE 255
SPOOL d:\zub_db\sql\meldungen\fuelle_fehl_inv_tab.txt
-- Leeren der Tabllen ...FEHL_INV_TAB
-- Füllen der Tabellen ...FEHL_INV_TAB mit den Datums und R_Nr aus ...ROH_TAB
DECLARE
c_dbms INTEGER;
dummy INTEGER;
ausgabe VARCHAR2(255);
CURSOR c_zu IS
SELECT DISTINCT UPPER(tab_name) tab_name from zuordnung
ORDER BY tab_name;
BEGIN
c_dbms := DBMS_SQL.OPEN_CURSOR;
DBMS_OUTPUT.ENABLE(1000000);
DBMS_OUTPUT.PUT_LINE('-- Füllen der Tabellen ..._FEHL_INV_TAB');
FOR z IN c_zu LOOP
-- Leeren
ausgabe := 'TRUNCATE TABLE '||z.tab_name||'_FEHL_INV_TAB';
DBMS_OUTPUT.PUT_LINE(ausgabe);
DBMS_SQL.PARSE(c_dbms,ausgabe,DBMS_SQL.NATIVE);
dummy := DBMS_SQL.EXECUTE(c_dbms);
COMMIT;
-- Füllen
ausgabe := 'INSERT INTO '||z.tab_name||'_FEHL_INV_TAB(datum,r_nr) SELECT
datum,r_nr FROM '||z.tab_name||'_ROH_TAB';
DBMS_OUTPUT.PUT_LINE(ausgabe);
DBMS_SQL.PARSE(c_dbms,ausgabe,DBMS_SQL.NATIVE);
dummy := DBMS_SQL.EXECUTE(c_dbms);
COMMIT;
END LOOP;
DBMS_SQL.CLOSE_CURSOR(c_dbms);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS in fuelle_fehl_inv_tab.sql - Leeren und
Füllen der Tabellen ...FEHL_INV_TAB');
IF DBMS_SQL.IS_OPEN(c_dbms) THEN
DBMS_SQL.CLOSE_CURSOR(c_dbms);
END IF;
RAISE;
END;
/
-- UPDATEN der Tabellen ...FEHL_INV_TAB mit den eingetragenen Fehlern aus FEHL_TAB
DECLARE
c_dbms INTEGER;
dummy INTEGER;
ausgabe VARCHAR2(255);
CURSOR c_fehl_tab IS
SELECT * FROM FEHL_TAB
ORDER BY lfd_nr ASC;
94
Anhang
BEGIN
DBMS_OUTPUT.PUT_LINE('---------------------------------------------------------');
DBMS_OUTPUT.PUT_LINE('---------------------------------------------------------');
DBMS_OUTPUT.PUT_LINE('Updaten der Tabellen ..._FEHL_INV_TAB');
c_dbms := DBMS_SQL.OPEN_CURSOR;
FOR f IN c_fehl_tab LOOP
IF f.end_dat IS NULL THEN
f.end_dat := to_date('01.01.2100 00:00','dd.mm.yyyy hh24:mi');
END IF;
IF UPPER(f.sensor) ='ALLE' THEN
IF UPPER(f.r_nr)= 'ALLE' THEN
-- Raumnummer und Sensor -> 'ALLE'
ausgabe := 'DELETE FROM '||f.tab_name||'_FEHL_INV_TAB WHERE datum
BETWEEN :anf_dat AND :end_dat';
ELSE
-- Sensor -> ALLE
ausgabe := 'DELETE FROM '||f.tab_name||'_FEHL_INV_TAB WHERE
UPPER(r_nr) = '||CHR(39)||UPPER(f.r_nr)||CHR(39)|| ' AND datum BETWEEN :anf_dat AND
:end_dat';
END IF;
ELSE
IF UPPER(f.r_nr)= 'ALLE' THEN
-- Raumnummer -> 'ALLE'
ausgabe := 'UPDATE '||f.tab_name||'_FEHL_INV_TAB SET
'||f.sensor||'=NULL WHERE datum BETWEEN :anf_dat AND :end_dat';
ELSE
-- Spalte und Raumnummer definiert.
ausgabe := 'UPDATE '||f.tab_name||'_FEHL_INV_TAB SET
'||f.sensor||'=NULL WHERE UPPER(r_nr) = '||CHR(39)||UPPER(f.r_nr)||CHR(39)|| ' AND datum
BETWEEN :anf_dat AND :end_dat';
END IF;
END IF;
DBMS_OUTPUT.PUT_LINE('Lfd_Nr: '|| f.lfd_nr);
DBMS_OUTPUT.PUT_LINE(ausgabe);
DBMS_OUTPUT.PUT_LINE(' :Anf_Dat = '||to_char(f.anf_dat,'dd.mm.yyyy hh24:mi') ||'
:End_Dat = '||to_char(f.end_dat,'dd.mm.yyyy hh24:mi'));
DBMS_SQL.PARSE(c_dbms,ausgabe,DBMS_SQL.NATIVE);
DBMS_SQL.BIND_VARIABLE(c_dbms,':anf_dat',f.anf_dat);
DBMS_SQL.BIND_VARIABLE(c_dbms,':end_dat',f.end_dat);
dummy := DBMS_SQL.EXECUTE(c_dbms);
COMMIT;
END LOOP;
DBMS_SQL.CLOSE_CURSOR(c_dbms);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS in fuelle_fehl_inv_tab.sql - Updaten der
Tabellen ...FEHL_INV_TAB');
IF DBMS_SQL.IS_OPEN(c_dbms) THEN
DBMS_SQL.CLOSE_CURSOR(c_dbms);
END IF;
RAISE;
END;
/
SPOOL OFF
EXIT;
95
Anhang
Skript 12: FUNKTION_DATUM_R_NR_IN_TAB.SQL
----------------------------------------------------------------------------------------- Datei: FUNKTION_DATUM_R_NR_IN_TAB.SQL
-- Erstellt: 13.11.2000
-- Beschreibung:
-- Prüft, ob die das Datum mit der Raumnummer schon in einer Datenbanktabelle vorhanden
ist.
-- Liefert TRUE, falls es vorhanden ist; ansonsten FALSE
-- Änderungen:
-----------------------------------------------------------------------------------------CREATE OR REPLACE FUNCTION datum_r_nr_in_tab (dat DATE, raum IN VARCHAR2, tabelle IN
VARCHAR2) RETURN BOOLEAN IS
tab_datum
DATE;
--tmp_dat
DATE;
ist_da
NUMBER;
c_vorhanden
NUMBER;
hk
hochkomma.strich%TYPE;
raum_gross
VARCHAR2(30);
n_exception_flag
NUMBER(6) := 0;
--tabelle_gross
VARCHAR2(30);
BEGIN
SELECT strich INTO hk FROM hochkomma;
--tabelle := upper(tabelle);
--tmp_dat := to_date(to_char(dat,'dd.mm.yyyy hh24:mi'),'dd.mm.yyyy hh24:mi');
raum_gross := upper(raum);
c_vorhanden := DBMS_SQL.OPEN_CURSOR;
IF raum_gross ='ALLE' THEN
-- Diese Option wird für FORMS genutzt.
DBMS_SQL.PARSE(c_vorhanden,'SELECT datum FROM '||tabelle||' WHERE datum = :d_dat
', DBMS_SQL.NATIVE);
ELSE
DBMS_SQL.PARSE(c_vorhanden,'SELECT datum FROM '||tabelle||' WHERE datum = :d_dat
AND upper(r_nr)='||hk||raum_gross||hk, DBMS_SQL.NATIVE);
END IF;
DBMS_SQL.DEFINE_COLUMN(c_vorhanden, 1, tab_datum);
DBMS_SQL.BIND_VARIABLE(c_vorhanden, ':d_dat',dat);
--DBMS_SQL.BIND_VARIABLE(c_vorhanden, ':d_dat',tmp_dat);
--nvl(r_nr,'||hk||'9999'||hk||')
ist_da := DBMS_SQL.EXECUTE(c_vorhanden);
ist_da := DBMS_SQL.FETCH_ROWS(c_vorhanden);
-- DBMS_OUTPUT.PUT_LINE(ist_da);
DBMS_SQL.CLOSE_CURSOR(c_vorhanden);
IF ist_da != 0 THEN
RETURN TRUE;
ELSE
RETURN FALSE;
END IF;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS in funktion_datum_r_nr_in_tab.sql ,
n_exception_flag);
IF DBMS_SQL.IS_OPEN(c_vorhanden) THEN
DBMS_SQL.CLOSE_CURSOR(c_vorhanden);
END IF;
RAISE;
END;
/
COMMIT;
FLAG: ' ||
96
Anhang
Skript 13: FUNKTION_FINDEANZAHL.SQL
----------------------------------------------------------------------------------------- Datei: FUNKTION_FINDEANZAHL.SQL
-- Erstellt: 07.11.2000
-- Beschreibung:
-- Findet die Anzahl der übergebenen 'chars' im Wort 'Wort'
----Änderungen:
------------------------------------------------------------------------------------------SET SERVEROUTPUT ON
CREATE OR REPLACE FUNCTION findeAnzahl (Wort IN VARCHAR2, chars IN VARCHAR2) RETURN
INTEGER IS
laengeWort
NUMBER(3);
tmp_laenge
NUMBER(3);
BEGIN
laengeWort := LENGTH(Wort);
tmp_laenge := LENGTH(REPLACE(Wort,chars,''));
RETURN laengeWort - tmp_laenge;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Fehler: Die Übergabeparameter für die Funktion find_ stimmen
nicht.');
RAISE;
END;
/
Skript 14: FUNKTION_SONDERZEICHEN.SQL
----------------------------------------------------------------------------------------- Datei: FUNKTION_SONDERZEICHEN.SQL
-- Erstellt: 07.12.2000
-- Beschreibung:
-- Wenn nur die Zeichen A-Z, 0-9 und ein _ im übergebenen String vorkommen,
-- wird TRUE zurückgegeben, ansonsten FALSE
---Änderungen:
------------------------------------------------------------------------------------------CREATE OR REPLACE FUNCTION sonderzeichen(str IN VARCHAR2) RETURN BOOLEAN IS
num NUMBER(2);
teilstr VARCHAR2(1);
BEGIN
num := LENGTH(str);
IF num >= 1 THEN
FOR i IN 1..num LOOP
teilstr := SUBSTR(str,i,1);
IF (ASCII(UPPER(teilstr))<65 OR ASCII(UPPER(teilstr))>90) AND
(ASCII(teilstr)<48 OR ASCII(teilstr)>57) AND ASCII(teilstr) != 95 THEN
-- str enthält andere Werte als von A-Z, 0-9 oder _
RETURN FALSE;
END IF;
END LOOP;
RETURN TRUE;
ELSE
RETURN FALSE;
END IF;
EXCEPTION
97
Anhang
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Unbehandelte Exception in Funktion sonderzeichen.');
RAISE;
END;
/
Skript 15: FUNKTION_SPALTE_VORHANDEN.SQL
----------------------------------------------------------------------------------------- Datei: FUNKTION_SPALTE_VORHANDEN.SQL
-- Erstellt: 04.11.2000
-- Beschreibung:
-- Prüft, ob die Spalte in einer Tabelle existiert und gibt Wert 'TRUE' zurück ansonsten
-- 'FALSE'
-- Änderungen:
-----------------------------------------------------------------------------------------CREATE OR REPLACE FUNCTION spalte_in_tabelle (tabelle IN VARCHAR2, spalte IN VARCHAR2)
RETURN BOOLEAN IS
ist_da
NUMBER;
c_vorhanden
NUMBER;
spalte_gross
VARCHAR2(30);
tabelle_gross VARCHAR2(30);
BEGIN
spalte_gross := upper(spalte);
tabelle_gross := upper(tabelle);
c_vorhanden := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(c_vorhanden,'SELECT column_name FROM user_tab_columns WHERE table_name =
:v_tmp_tab AND column_name= :v_spalte', DBMS_SQL.NATIVE);
DBMS_SQL.BIND_VARIABLE(c_vorhanden,':v_spalte',spalte_gross);
DBMS_SQL.BIND_VARIABLE(c_vorhanden,':v_tmp_tab',tabelle_gross);
ist_da := DBMS_SQL.EXECUTE(c_vorhanden);
ist_da := DBMS_SQL.FETCH_ROWS(c_vorhanden);
-- DBMS_OUTPUT.PUT_LINE(ist_da);
DBMS_SQL.CLOSE_CURSOR(c_vorhanden);
IF ist_da != 0 THEN
RETURN TRUE;
ELSE
RETURN FALSE;
END IF;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('EXCEPTION in Funktion
IF DBMS_SQL.IS_OPEN(c_vorhanden) THEN
DBMS_SQL.CLOSE_CURSOR(c_vorhanden);
END IF;
RAISE;
END;
/
COMMIT;
spalte_in_tabelle');
98
Anhang
Skript 16: FUNKTION_TAB_VORHANDEN.SQL
----------------------------------------------------------------------------------------- Datei: FUNKTION_TAB_VORHANDEN.SQL
-- Erstellt: 04.10.2000
-- Beschreibung:
-- Prüft, ob die Tabelle existiert und gibt Wert 'TRUE' zurück - ansonsten 'FALSE'
-- Änderungen:
-----------------------------------------------------------------------------------------CREATE OR REPLACE FUNCTION tab_vorhanden (tab IN VARCHAR2) RETURN BOOLEAN IS
ist_da
NUMBER;
c_vorhanden
NUMBER;
tab_gross
VARCHAR2(30);
BEGIN
tab_gross := upper(tab);
c_vorhanden := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(c_vorhanden,'SELECT table_name FROM user_tab_columns WHERE table_name =
:tabn', DBMS_SQL.NATIVE);
DBMS_SQL.BIND_VARIABLE(c_vorhanden,':tabn',tab_gross);
ist_da := DBMS_SQL.EXECUTE(c_vorhanden);
ist_da := DBMS_SQL.FETCH_ROWS(c_vorhanden);
DBMS_SQL.CLOSE_CURSOR(c_vorhanden);
IF ist_da != 0 THEN
RETURN TRUE;
ELSE
RETURN FALSE;
END IF;
EXCEPTION
WHEN OTHERS THEN
IF DBMS_SQL.IS_OPEN(c_vorhanden) THEN
DBMS_SQL.CLOSE_CURSOR(c_vorhanden);
END IF;
RAISE;
END;
/
COMMIT;
99
Anhang
Skript 17: FUNTKION_ZUORDNUNG_PRUEFEN.SQL
----------------------------------------------------------------------------------------- Datei: FUNKTION_ZUORDNUNG_PRUEFEN.SQL
-- Erstellt: 06.11.2000
-- Beschreibung:
-- Diese Funktion prüft die Tabellen ZUORDNUNG und ZUB_DATEN_TMP_TAB auf konsistenz.
-- Wenn der Funktion "ANLEGEN" übergeben wird. wird Punkt 4 (s.u.) nicht geprüft.
-- 1) Prüfung der Tabelle Zuordnung, ob HW_ADR mehrfach vorkommen.
-- 2) Prüfung, ob Sensoren (Spalte SENSOR) in ZUORDNUNG konsistent sind -also keine Sensoren und gleichzeitig Raumnummern bei einer Tabelle Tab_Name
-doppelt in ZUORDNUNG vorkommen!
-- 3) Prüfung, ob alle Spalten aus ZUB_DATEN_TMP_TAB in ZUORDNUNG vorhanden sind.
-- 4) Prüfung, ob alle Spalten aus ZUORDNUNG in ZUB_DATEN_TMP_TAB vorhanden sind.
-(Bei Aufruf der Funktion mit dem Parameter "ANLEGEN" wird dieser Punkt nicht
-geprüft).
-- 5) Prüfen, nur Zeichen 0-9,A-Z und _ in ZUORDNUNG vorkommen.
-- Returniert wird das sogenannte "flag_fehler_zuordnung". Dieser
"flag_fehler_zuordnung"
-- wird auch in die Tabelle ZUORDNUNG_GEPRUEFT eingetragen.
-- Es werden hier 4 Fehler behandelt die in verschiedenen kombinationen auftreten
können.
-- Dazu werden der flag_fehler_zuordnung mit 4 Bit codiert. Es werden die Zahlen 0-15
-- returniert.
-- Bit 1: Punkt 1) HW_ADR kommen mehrmals vor in ZUORDNUNG (keine weitere Bearbeitung
-möglich)
-- Bit 2: Punkt 2) Raumnummer (R_Nr) und Sensorname (SENSOR) kommen merhmals vor in
-ZUORDNUNG (Fehler)
-- Bit 3: Punkt 3) Die Spalte XXX aus ZUB_DATEN_TMP_TAB existiert nicht in ZUORDNUNG
-(Warnung oder Fehler)
-- Bit 4: Punkt 4) Die Spalte XXX aus ZUORDNUNG existiert nicht in ZUB_DATEN_TMP_TAB
-(Fehler)
-- Bit 5: Punkt 5) Syntax
--Änderungen:
------------------------------------------------------------------------------------------SET SERVEROUTPUT ON
--DECLARE
CREATE OR REPLACE FUNCTION zuordnung_pruefen (flag_anlegen IN VARCHAR2) RETURN INTEGER
IS
ausgabe
VARCHAR2(300) := NULL;
ausgabe1
VARCHAR2(300) := NULL;
ausgabe2
VARCHAR2(1000) := NULL;
---------------------------------------------------------------------- CURSOR zum Vergleichen von ZUB_DATEN_TMP_TAB und ZUORDNUNG
CURSOR c1 IS
SELECT column_name FROM user_tab_columns WHERE table_name = 'ZUB_DATEN_TMP_TAB'
AND column_name NOT IN ('DATUM', 'UHRZEIT')
MINUS
SELECT hw_adr FROM ZUORDNUNG;
CURSOR c1_inv IS
SELECT hw_adr FROM ZUORDNUNG
MINUS
SELECT column_name FROM user_tab_columns WHERE table_name = 'ZUB_DATEN_TMP_TAB'
AND column_name NOT IN ('DATUM', 'UHRZEIT');
----------------------------------------------------------------------------------------- Variable und Cursor zum internen Prüfen von ZUORDNUNG
-- 1) Sind HW_ADR doppelt? -> Keine weitere Bearbeitung mehr möglich, da die Werte nicht
-eindeutig sind!
-- 2) Sind Raumnummern und gleichzeitig Sensornamen identisch? -> Raumnummern werden
-automatisch mit Indizies versehen.
n_exception_flag
NUMBER(6);
spalte_vorh
user_tab_columns.column_name%TYPE; -- Zweite Variable für
'spalte'
tmp_tab_name
zuordnung.tab_name%TYPE; -- Temporärer Speicher für aktuellen
-- Tabellennamen (DBMS_SQL Funktion)
flag_fehler_zuordnung
NUMBER(2) := 0;
100
Anhang
tmp_flag_fehler_zuordnung
i
erster_lauf
syntax_ok
BOOLEAN;
NUMBER(2) := 0;
NUMBER(2) := 0;
BOOLEAN
:= TRUE;
CURSOR c_tab_name
IS
SELECT DISTINCT UPPER(tab_name) tab_name from zuordnung
ORDER BY UPPER(tab_name);
-- Prüfen, ob <Tab_Name> mehrfach die gleichen Raumnummern und Sensoren enthält
CURSOR c_finde_doppelte_eintraege IS
SELECT UPPER(z.r_nr) r_nr, UPPER(z.sensor) sensor, UPPER(z.hw_adr) hw_adr FROM zuordnung
z
WHERE 1<(SELECT count(z1.r_nr) FROM zuordnung z1
WHERE UPPER(z.r_nr) = UPPER(z1.r_nr)
AND UPPER(z.sensor) = UPPER(z1.sensor)
AND UPPER(z1.tab_name) = UPPER(tmp_tab_name))
AND UPPER(z.tab_name) = UPPER(tmp_tab_name)
ORDER BY UPPER(z.r_nr), UPPER(z.sensor) ASC;
-- Prüfen, ob Zuordnung mehrfach die gleichen HW_ADR enthält
CURSOR c_hw_adr_doppelt IS
SELECT UPPER(z.r_nr) r_nr, UPPER(z.sensor) sensor, UPPER(z.hw_adr) hw_adr,
UPPER(z.tab_name) tab_name FROM zuordnung z
WHERE 1<(SELECT count(z1.hw_adr) FROM zuordnung z1
WHERE UPPER(z.hw_adr) = UPPER(z1.hw_adr));
-- Auf Sonderzeichen Prüfen
CURSOR c_sonder IS
SELECT * FROM ZUORDNUNG
ORDER BY tab_name, sensor, r_nr;
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------BEGIN
DBMS_OUTPUT.ENABLE(1000000);
UPDATE zuordnung_geprueft SET flag = 0;
in der Tabelle
-- Das Fehlerflag für die Prüfung von ZUORDNUNG
-ZUORDNUNG_GEPRUEFT zurücksetzen.
COMMIT;
-- Prüfen von ZUORDNUNG
-------------------------------------------------------------------------------------- Punkt1) PRÜFEN DOPPELTER HW_ADR
-- Gibt es HW_ADR die in Zuordnung mehrmals auftreten? Wenn ja, werden
-- keine Tabellen angelegt oder verändert!!!!
n_exception_flag := 1000;
erster_lauf := TRUE;
FOR doppelt IN c_hw_adr_doppelt LOOP
IF erster_lauf = TRUE THEN
erster_lauf := FALSE;
flag_fehler_zuordnung :=1;
DBMS_OUTPUT.PUT_LINE('----------------------------------------------------------------');
DBMS_OUTPUT.PUT_LINE('-- Pruefen der HW_ADR');
DBMS_OUTPUT.PUT_LINE('-- Fehler: Folgende HW_ADR in Zuordnung kommen
mehrmals vor:');
DBMS_OUTPUT.PUT_LINE('-Korrigieren Sie den Fehler mit dem
ISP Listen von Sauter.');
DBMS_OUTPUT.PUT_LINE('-Ansonsten besteht die hohe
Wahrscheinlichkeit eines Fehlers');
DBMS_OUTPUT.PUT_LINE('-in der weiteren Verarbeitung!!!');
-DBMS_OUTPUT.PUT_LINE('-Es werden keine Tabellen angelegt
oder verändert!!!');
END IF;
DBMS_OUTPUT.PUT_LINE('-- Doppelt: '|| doppelt.hw_adr ||' Tabelle: '||
doppelt.tab_name||' Raumnummer: '|| doppelt.r_nr ||' sensor: '|| doppelt.sensor);
END LOOP;
erster_lauf := TRUE;
----------------------------------------------------------------------------------------- Punkt 2) PRÜFEN GLEICHER RAUMNUMMER UND SENSOR
-- Werden gleiche gefunden wird der Sensor indiziert.
n_exception_flag := 2000;
<<tabellenname>>
FOR tab IN c_tab_name LOOP -- Tabellennamen selektieren
101
Anhang
i := 0; -- Indx wieder auf 0 setzen.
tmp_tab_name := tab.tab_name;
ausgabe := NULL;
ausgabe1 := NULL;
ausgabe2 := NULL;
DBMS_OUTPUT.PUT_LINE('-----------------------------------------------------------------------------------------');
DBMS_OUTPUT.PUT_LINE('-- Pruefen und indizieren gleicher Raumnummer (R_NR) und
Sensor (SENSOR) für Tabellenname '||tmp_tab_name|| ' in ZUORDNUNG.');
<<doppelteWerte>>
FOR doppelt IN c_finde_doppelte_eintraege LOOP -- doppelte Einträge finden.
i := i +1;
IF i <= 12 THEN -- Ausgabe wird unterteilt, falls zuviele gleiche HW_ADR
auftreten.
ausgabe := ausgabe ||' '||doppelt.hw_adr; -- Sammeln der HW_ADR
zur Ausgabe.
ELSIF i > 12 AND i <=25 THEN
ausgabe1 := ausgabe1 ||' '||doppelt.hw_adr;
ELSE
ausgabe2 := ausgabe2 ||' '||doppelt.hw_adr;
END IF;
DBMS_OUTPUT.PUT_LINE('-- Doppelte Raumnummer '|| doppelt.r_nr ||' und
Sensor '||doppelt.sensor||' bei HW_ADR: '|| doppelt.hw_adr);
COMMIT;
tmp_flag_fehler_zuordnung := 2;
END LOOP doppelteWerte;
IF i != 0 THEN -- Alle HW_ADR ausgeben, in denen Raumnummer und Sensor gleich
sind.
DBMS_OUTPUT.PUT_LINE('-Doppelt in: '|| ausgabe);
DBMS_OUTPUT.PUT_LINE('-'|| ausgabe1);
DBMS_OUTPUT.PUT_LINE('-'|| ausgabe2);
END IF;
END LOOP tabellenname;
IF tmp_flag_fehler_zuordnung = 2 THEN
DBMS_OUTPUT.PUT_LINE('-----------------');
DBMS_OUTPUT.PUT_LINE('--');
DBMS_OUTPUT.PUT_LINE('-- FEHLER: ');
DBMS_OUTPUT.PUT_LINE('-- Es wurden mehrfach vorkommende Raumnummern und Sensoren
gefunden.');
DBMS_OUTPUT.PUT_LINE('-- Ändern Sie ZUORDNUNG und führen Sie das Skript erneut
aus.');
DBMS_OUTPUT.PUT_LINE('-- Führen Sie die Änderungen in Ihrer Dokumentation nach.
');
DBMS_OUTPUT.PUT_LINE('----------------------------------------------------------------');
END IF;
erster_lauf := TRUE;
flag_fehler_zuordnung := flag_fehler_zuordnung + tmp_flag_fehler_zuordnung; -- Das
flag_fehler_zuordnung ist jetzt 2 oder 3 (1+2)
tmp_flag_fehler_zuordnung := 0;
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Punkt 3) Ableich der Tabellen ZUB_DATEN_TMP_TAB und ZUORDNUNG.
n_exception_flag := 3000;
FOR spalte1 IN c1 LOOP
IF erster_lauf =TRUE THEN
DBMS_OUTPUT.PUT_LINE('----------------------------------------------------------------');
DBMS_OUTPUT.PUT_LINE('-- Abgleichen von ZUB_DATEN_TMP_TAB mit
ZUORDNUNG.');
DBMS_OUTPUT.PUT_LINE('--');
erster_lauf := FALSE;
END IF;
tmp_flag_fehler_zuordnung := 4;
ausgabe := spalte1.column_name;
DBMS_OUTPUT.PUT_LINE('--');
DBMS_OUTPUT.PUT_LINE('-- Fehler: Die Spalte '|| ausgabe ||' aus ZUB_DATEN_TMP_TAB
existiert nicht in ZUORDNUNG.');
END LOOP;
IF tmp_flag_fehler_zuordnung = 4 THEN
DBMS_OUTPUT.PUT_LINE('--');
102
Anhang
DBMS_OUTPUT.PUT_LINE('-Prüfen Sie ZUORDNUNG, ob beim Anlegen eine
PRIMARY KEY Verletzung');
DBMS_OUTPUT.PUT_LINE('-auftrat oder die Spalte schlicht vergessen
wurde oder ob die Daten');
DBMS_OUTPUT.PUT_LINE('-nicht aufgenommen werden sollen.');
DBMS_OUTPUT.PUT_LINE('-Im letzteren Fall tragen Sie die Daten
trotzdem in ZUORDNUNG ein und verwenden');
DBMS_OUTPUT.PUT_LINE('-Sie eine neue Tabelle, damit keine Daten
verloren gehen.');
END IF;
erster_lauf := TRUE;
flag_fehler_zuordnung := flag_fehler_zuordnung + tmp_flag_fehler_zuordnung; -flag_fehler_zuordnung ist jetzt 4,5,6,7
------------------------------------- Punkt 4)
n_exception_flag := 4000;
tmp_flag_fehler_zuordnung := 0;
IF UPPER(REPLACE(flag_anlegen,' ','')) != 'ANLEGEN' THEN
-- In diesem Fall diese
Fehlerprüfung überspringen.
FOR spalte IN c1_inv LOOP
IF erster_lauf =TRUE THEN
DBMS_OUTPUT.PUT_LINE('-...........................................');
DBMS_OUTPUT.PUT_LINE('----------------------------------------------------------------');
DBMS_OUTPUT.PUT_LINE('-- Abgleichen von ZUORDNUNG mit
ZUB_DATEN_TMP_TAB.');
DBMS_OUTPUT.PUT_LINE('--');
erster_lauf := FALSE;
END IF;
tmp_flag_fehler_zuordnung := 8;
ausgabe := spalte.hw_adr;
DBMS_OUTPUT.PUT_LINE('--');
DBMS_OUTPUT.PUT_LINE('-- Fehler: Die Spalte '|| ausgabe ||' aus ZUORDNUNG
existiert nicht in ZUB_DATEN_TMP_TAB (Sauter Daten).');
END LOOP;
erster_lauf := TRUE;
IF tmp_flag_fehler_zuordnung = 8 THEN
DBMS_OUTPUT.PUT_LINE('--');
DBMS_OUTPUT.PUT_LINE('-Prüfen Sie nach den ISP
(Informationsschwerpunkt (Tabellen)), ob dieser Datenpunkt ');
DBMS_OUTPUT.PUT_LINE('-zur Messswerterfassung vorgesehen ist,
wenn ja: ');
DBMS_OUTPUT.PUT_LINE('-Wenden Sie sich an die verantwortliche
Firma für die Datenmessungen im ZUB, ');
DBMS_OUTPUT.PUT_LINE('-ob dieser Messpunkt zur Aufnahme
vorgesehen ist');
DBMS_OUTPUT.PUT_LINE('-wenn nein, löschen Sie den Messpunkt in
ZUORDNUNG!!!.');
DBMS_OUTPUT.PUT_LINE('-Der Messpunkt wird in der zukünfitgen
Verarbeitung ignoriert !!!!!!!!!.');
DBMS_OUTPUT.PUT_LINE('----------------------------------------------------------------');
END IF;
END IF;
flag_fehler_zuordnung := flag_fehler_zuordnung + tmp_flag_fehler_zuordnung;
tmp_flag_fehler_zuordnung := 0;
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Punkt 5) Prüfen auf Sonderzeichen ZUORDNUNG.
i := 0;
DBMS_OUTPUT.PUT_LINE('----------------------------------------------------------------');
DBMS_OUTPUT.PUT_LINE('-- Prüfen der Syntax von ZUORDNUNG .');
DBMS_OUTPUT.PUT_LINE('--');
FOR sonder IN c_sonder LOOP
syntax_ok :=
sonderzeichen(sonder.hw_adr||sonder.tab_name||sonder.sensor||sonder.r_nr||sonder.format)
;
IF syntax_ok = FALSE THEN
i := i +1;
103
Anhang
DBMS_OUTPUT.PUT_LINE('-- Sonderzeichen in Hw_Adr '||sonder.hw_adr||'
Tab_Name
'||sonder.tab_name||'
Sensor '||sonder.sensor||'
R_Nr '||sonder.r_nr||'
Format
'||sonder.format||' gefunden.');
END IF;
END LOOP;
IF i > 0 THEN
tmp_flag_fehler_zuordnung := 32;
DBMS_OUTPUT.PUT_LINE('-- FEHLER: Es sind mindestens '||i||' Sonderzeichen in
ZUORDNUNG gefunden worden.');
DBMS_OUTPUT.PUT_LINE('-- Ändern Sie ZUORDNUNG und führen Sie das Skript erneut
aus.');
ELSE
DBMS_OUTPUT.PUT_LINE('-- Keine Syntaxfehler gefunden.');
END IF;
flag_fehler_zuordnung := flag_fehler_zuordnung + tmp_flag_fehler_zuordnung;
tmp_flag_fehler_zuordnung := 0;
UPDATE zuordnung_geprueft SET flag = flag_fehler_zuordnung;
COMMIT;
DBMS_OUTPUT.PUT_LINE('----------------------------------------------------------------');
DBMS_OUTPUT.PUT_LINE('-- Die Fehleranzeige ist: ' ||flag_fehler_zuordnung);
DBMS_OUTPUT.PUT_LINE('-- ENDE von zuordnung_pruefen.');
DBMS_OUTPUT.PUT_LINE('----------------------------------------------------------------');
RETURN flag_fehler_zuordnung;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('-- Fehler: Exception OTHERS in
funktion_zuordnung_pruefen.sql. FLAG: '||n_exception_flag);
IF c_tab_name%ISOPEN THEN
CLOSE c_tab_name;
END IF;
IF c_finde_doppelte_eintraege%ISOPEN THEN
CLOSE c_finde_doppelte_eintraege;
END IF;
IF c_hw_adr_doppelt%ISOPEN THEN
CLOSE c_hw_adr_doppelt;
END IF;
IF c1%ISOPEN THEN
CLOSE c1;
END IF;
IF c1_inv%ISOPEN THEN
CLOSE c1_inv;
END IF;
RAISE;
END;
/
COMMIT;
--select table_name from user_tables;
--desc Klima;
--desc StdR;
104
Anhang
Skript 18: INITUMGEBUNGSTABELLEN.SQL
----------------------------------------------------------------------------------------- Datei: INITUMGEBUNGSTABELLEN.SQL
-- Erstellt: 04.09.2000
-- Beschreibung:
-- Initialisieren der ZUB Datenbank für die erste Inbetriebnahme
-- - Füllen der Tabelle ANHANG, die die Anhängsel für die Datentabellen enthält.
-- - Anfangswert für ZEITTAB festlegen.
-- - Standardfehler für die automatische Fehlererkennung festlegen.
-- - Ein ' - das ist CHR(39) in ASCII - in die Tabelle HOCHKOMMA eintragen.
-- - Das Prüfflag für ZUORDNUNG auf 0 (entspricht -> keine Fehler) setzen.
-- Änderungen:
------------------------------------------------------------------------------------------- Tabelle Anhang initialisieren.
-- !!! DIESE TABELLE DARF NICHT GEÄNDERT WERDEN !!!
INSERT
INSERT
INSERT
INSERT
INSERT
INSERT
INSERT
INSERT
INTO
INTO
INTO
INTO
INTO
INTO
INTO
INTO
Anhang
Anhang
Anhang
Anhang
Anhang
Anhang
Anhang
Anhang
VALUES
VALUES
VALUES
VALUES
VALUES
VALUES
VALUES
VALUES
('_TMP_TAB');
('_FEHL_AUTO_TAB');
('_FEHL_INV_TAB');
('_ROH_TAB');
('_ROHKORR_TAB');
('_TAG_TAB');
('_MONAT_TAB');
('_JAHR_TAB');
COMMIT;
-- Jahres und Monatsmittelwert werden eingetragen.
--TRUNCATE TABLE Zeittab_fuellen;
INSERT INTO Zeittab_fuellen VALUES (TRUNC(SYSDATE,'YYYY'));
COMMIT;
-- Fehlercode festlegen.
INSERT INTO FEHLER_CODE VALUES (1,'Datum nicht vorhanden.');
INSERT INTO FEHLER_CODE VALUES (2,'Wert ist nicht vorhanden (ev. Sensorausfall).');
INSERT INTO FEHLER_CODE VALUES (3,'Wert liegt nicht im Messbereich.');
COMMIT;
-- Hochkomma eintragen (Es wird ein ' eingetragen, wenn ASCII Format vorhanden ist
-- Ist die Codepage 500 wird das EBCDIC Format verwendet und es muss CHR(125) eingesetzt
werden.
TRUNCATE TABLE HOCHKOMMA;
INSERT INTO HOCHKOMMA VALUES (CHR(39));
-- Hier wird ein Flag eingetragen, welche Fehler die Tabelle ZUORDNUNG enthält.
-- Bei einer 0 ist ZUORDNUNG o.k.
TRUNCATE TABLE ZUORDNUNG_GEPRUEFT;
INSERT INTO ZUORDNUNG_GEPRUEFT VALUES(0);
105
Anhang
Skript 19: PROZEDUR_DATUM_VERBESSERN.SQL
----------------------------------------------------------------------------------------- Datei: PROZEDUR_DATUM_VERBESSERN.SQL
-- Erstellt: 06.11.2000
-- Beschreibung:
-- Verbesserung falscher Datums ZUB_DATEN_TMP_TAB. Ein falsches Datum ist eine
Abweichung
-- von den Datumsintervallen in der Tabelle ZEITTAB. Das falsche Datum in
ZUB_DATEN_TMP_TAB
-- wird automatisch verbessert und entsrpechend weiter verarbeitet vom aufrufenden
Skript.
-- Vorteil:
Die Funktion wählt automatisch das erste Datum aus, das im Intervall
+/-6min.nicht existiert. D.h.
-die Abweichung eines Datums kann auch grösser sein als 3 min.
-(die halbe Intervallzeit).
-- Schwäche:
Fehlt ein Datum (z.B.: 00:06) und das nächste ist falsch (z.B.:00:11),
-dann setzt die Funktion das Datum auf 00:06 und nicht - was eventuell
-richtig ist auf 00:12.
-- Bem.:
-- Änderungen:
-----------------------------------------------------------------------------------------SET SERVEROUTPUT ON;
CREATE OR REPLACE PROCEDURE datum_verbessern IS
v2_dummy
VARCHAR2(10);
CURSOR c_datum IS SELECT to_date(datum||uhrzeit,'dd.mm.yyyy hh24:mi') from
zub_daten_tmp_tab;
-- DBMS_SQL
dummy
INTEGER;
c_dbms_hw_adr
INTEGER;
-- VARIABLE FÜR DIE FEHLENDEN TABELLEN
strout
VARCHAR2(200);
ch_sensor
zuordnung.sensor%TYPE;
ch_tab_name
zuordnung.tab_name%TYPE;
ch_r_nr zuordnung.r_nr%TYPE;
-- CURSOR UND VARIABLE FÜR DIE VERBESSERUNG FALSCHER DATUMS
d_anfang
DATE;
d_ende
DATE;
d_falsch
DATE;
d_nahes_datum DATE;
d_erstes_ungueltiges_datum
DATE;
datumsfehler_flag
NUMBER(1) :=0;
-- CUROSR findet die falschen Datums mit falschen Zeiten
CURSOR c_datum_falsch IS
SELECT to_date(datum||uhrzeit,'dd.mm.yyyy hh24:mi') from zub_daten_tmp_tab
MINUS
SELECT ref_zeit FROM zeittab
WHERE ref_zeit BETWEEN d_anfang AND d_ende;
-- Cursor findet gültige Datumwerte im Bereich d_falsch +/- 6 Minuten.
-- d_falsch wird im Cursor c_datum falsch selektiert.
-- Das Datum aus ZUB_DATEN_TMP_TAB wird auf das erste ungültige Datum gesetzt.
CURSOR c_finde_nahes_datum IS
SELECT ref_zeit FROM zeittab
WHERE ref_zeit BETWEEN (d_falsch - 6/(24*60)) AND (d_falsch + 6/(24*60))
ORDER BY ref_zeit ASC;
-- Wenn ein nahes Datum vorhanden ist und dieses Datum NICHT in TMP_DATNE existiert,
-- wird dieses Datum übernommen, andernfalls wird das nächste überprüft.
CURSOR c_erstes_ungueltiges_datum IS
SELECT to_date(datum||uhrzeit,'dd.mm.yyyy hh24:mi') FROM zub_daten_tmp_tab
WHERE to_date(datum||uhrzeit,'dd.mm.yyyy hh24:mi') = d_nahes_datum;
----------------------------------------------------------------------------------------
106
Anhang
BEGIN
-- VERBESSERN DES DATUMS IN ZUB_DATEN_TMP_TAB
c_dbms_hw_adr := DBMS_SQL.OPEN_CURSOR;
-- NULL Datums löschen.
DELETE FROM zub_daten_tmp_tab WHERE datum IS NULL OR uhrzeit IS NULL;
COMMIT;
-- Ältestes und jüngstes Datum speichern.
SELECT min(to_date(datum||uhrzeit,'dd.mm.yyyy hh24:mi')),
max(to_date(datum||uhrzeit,'dd.mm.yyyy hh24:mi')) INTO d_anfang, d_ende FROM
zub_daten_tmp_tab;
-- Falsche Datums korrigieren
OPEN c_datum_falsch;
<<falschesDatum>>
LOOP
-- d_falsch enthält die falschen Datums oder
-- wenn alle Datums richtig sind, wird die Schleife unmittelbar verlassen.
FETCH c_datum_falsch INTO d_falsch;
EXIT falschesDatum WHEN c_datum_falsch%NOTFOUND;
-DBMS_OUTPUT.PUT_LINE('-- Tabelle: ZUB_DATEN_TMP_TAB; Falsches Datum: ' ||
to_char(d_falsch,'dd.mm hh24:mi'));
OPEN c_finde_nahes_datum;
<<ungueltigesDatum>>
LOOP
-- Suchen des ersten ungültigen Datums
FETCH c_finde_nahes_datum INTO d_nahes_datum;
IF c_finde_nahes_datum%NOTFOUND THEN
DBMS_OUTPUT.PUT_LINE('-- Kein nahes Datum gefunden. Falsches
Datum: ' || to_char(d_falsch,'dd.mm hh24:mi'));
EXIT ungueltigesDatum WHEN c_finde_nahes_datum%NOTFOUND;
ELSE -- tmp_hw_adr nach dem ersten ungültigen Wert durchsuchen.
OPEN c_erstes_ungueltiges_datum;
FETCH c_erstes_ungueltiges_datum INTO d_erstes_ungueltiges_datum;
IF c_erstes_ungueltiges_datum%NOTFOUND THEN
UPDATE zub_daten_tmp_tab SET datum =
to_char(d_nahes_datum,'dd.mm.yyyy') ,uhrzeit = to_char(d_nahes_datum,'hh24:mi') WHERE
to_date(datum||uhrzeit,'dd.mm.yyyy hh24:mi') = d_falsch;
-- ZUM direkten lesen des falschen Datums
DBMS_OUTPUT.PUT_LINE('-- Tabelle ZUB_DATEN_TMP_TAB;
Falsches Datum: ' || to_char(d_falsch,'dd.mm hh24:mi') ||' Datum geupdated ' ||
to_char(d_nahes_datum,'dd.mm hh24:mi'));
-- Fehlerflag setzen
datumsfehler_flag := 1;
COMMIT;
EXIT ungueltigesDatum WHEN
c_erstes_ungueltiges_datum%NOTFOUND;
END IF;
END IF;
CLOSE c_erstes_ungueltiges_datum;
COMMIT;
END LOOP ungueltigesDatum;
IF c_finde_nahes_datum%ISOPEN THEN
CLOSE c_finde_nahes_datum;
END IF;
IF c_erstes_ungueltiges_datum%ISOPEN THEN
CLOSE c_erstes_ungueltiges_datum;
END IF;
-DBMS_OUTPUT.PUT_LINE('-- Datum zwischen ' || to_char(d_falsch 6/(24*60),'dd.mm.yy hh24:mi') ||' UND '|| to_char(d_falsch + 6/(24*60),'dd.mm.yy
hh24:mi'));
DBMS_OUTPUT.PUT_LINE('-- Falsches Datum: ' || to_char(d_falsch,'dd.mm hh24:mi')
|| ' Nahes Datum: ' || to_char(d_nahes_datum,'dd.mm hh24:mi'));
d_nahes_datum := NULL;
COMMIT;
END LOOP falschesDatum;
IF datumsfehler_flag = 1 THEN
DBMS_OUTPUT.PUT_LINE('-- Warnung: In der Tabelle TMP_DATUM sind falsche Datums
verbessert worden.');
END IF;
IF c_finde_nahes_datum%ISOPEN THEN
CLOSE c_finde_nahes_datum;
END IF;
CLOSE c_datum_falsch;
COMMIT;
IF c_datum%ISOPEN THEN
CLOSE c_datum;
107
Anhang
END IF;
IF DBMS_SQL.IS_OPEN(c_dbms_hw_adr) THEN
DBMS_SQL.CLOSE_CURSOR(c_dbms_hw_adr);
END IF;
---------------------------------------------------------------------------------------EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('-- Exception OTHERS: Es sind keine Daten in
ZUB_DATEN_TMP_TAB.');
DBMS_OUTPUT.PUT_LINE('-- Diese Prozedur wird in Ablaufstrg_taeglich.sql
verwendet.');
IF c_datum%ISOPEN THEN
CLOSE c_datum;
END IF;
IF DBMS_SQL.IS_OPEN(c_dbms_hw_adr) THEN
DBMS_SQL.CLOSE_CURSOR(c_dbms_hw_adr);
END IF;
RAISE;
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('-- Exception OTHERS: Es sind keine Daten in den kopierten
dBase Tabellen (kopie_tmp_hw_adr.sql).');
DBMS_OUTPUT.PUT_LINE('-- Diese Prozedur wird in rohdaten_messbereich.sql
verwendet.');
IF c_datum%ISOPEN THEN
CLOSE c_datum;
END IF;
IF DBMS_SQL.IS_OPEN(c_dbms_hw_adr) THEN
DBMS_SQL.CLOSE_CURSOR(c_dbms_hw_adr);
END IF;
RAISE;
END;
/
COMMIT;
108
Anhang
Skript 20: PROZEDUR_DB_SPALTEN_ANLEGEN.SQL
----------------------------------------------------------------------------------------- Datei: PROZEDUR_DB_SPALTEN_ANLEGEN.SQL
-- Erstellt: 24.11.2000
-----
Beschreibung:
Prodzedur fügt eine Spalte in eine Datentabelle ein.
Dabei wird unterschieden, ob es sich um eine Fehlertabelle
oder um eine Messdatentabelle handelt.
--Änderungen:
------------------------------------------------------------------------------------------CREATE OR REPLACE PROCEDURE db_spalte_anlegen(tab_name IN VARCHAR2, sensor IN VARCHAR2,
format IN VARCHAR2) AS
tmp_name
zuordnung.tab_name%TYPE;
b
BOOLEAN;
c_dbms
INTEGER;
dummy
INTEGER;
n_exception_flag NUMBER(6);
binaer
VARCHAR2(20) := ' NUMBER(1)';
analog
VARCHAR2(20) := ' NUMBER(5,2)';
fehler_format
VARCHAR2(20) := ' NUMBER(2)';
num_format
VARCHAR2(30);
BEGIN
n_exception_flag := 0;
c_dbms := DBMS_SQL.OPEN_CURSOR;
-- Übernehmen des Formates!
IF SUBSTR(UPPER(format),1,2) = 'B' THEN
-- Binärwert
num_format :=binaer;
ELSIF SUBSTR(UPPER(format),1,2) = 'A' THEN
-- Analogwert
num_format := analog;
ELSE
num_format := 'UNGUELTIG';
END IF;
n_exception_flag := 100;
/*
DBMS_OUTPUT.PUT_LINE(tab_name||' '||sensor||' '||format);
b := spalte_in_tabelle(tab_name,sensor);
IF b = TRUE THEN
DBMS_OUTPUT.PUT_LINE('Fehler Spalte in Tabelle : JA');
ELSE
DBMS_OUTPUT.PUT_LINE('I.O. Spalte in Tabelle : NEIN');
END IF;
b := tab_vorhanden(tab_name) ;
IF b = FALSE THEN
DBMS_OUTPUT.PUT_LINE('Fehler Tabelle vorhanden: NEIN');
ELSE
DBMS_OUTPUT.PUT_LINE('I.O. Tabelle vorhanden: JA');
END IF;
DBMS_OUTPUT.PUT_LINE('Format: '||num_format);
n_exception_flag := 200;
*/
IF num_format = 'UNGUELTIG' OR spalte_in_tabelle(tab_name,sensor) = TRUE OR
tab_vorhanden(tab_name) = FALSE THEN
-- FEHLER
n_exception_flag := 300;
raise_application_error(-20101, 'Fehler: Entweder Format ungültig, Spalte ist
schon in Tabelle oder Tabelle nicht vorhanden.');
ELSE
n_exception_flag := 1000;
IF INSTR(tab_name,'_FEHL') = 0 THEN
109
Anhang
n_exception_flag := 1100;
-- Einfügen der Spalte 'spalte' in die Tabelle 'tmp_name'
DBMS_SQL.PARSE(c_dbms,'ALTER TABLE ' || tab_name || ' ADD (' || sensor ||
num_format || ')',DBMS_SQL.NATIVE);
dummy := DBMS_SQL.EXECUTE(c_dbms);
ELSIF INSTR(tab_name,'_FEHL_AUTO_TAB') != 0 THEN
n_exception_flag := 1200;
DBMS_SQL.PARSE(c_dbms,'ALTER TABLE ' || tab_name || ' ADD (' ||
sensor || fehler_format || ')',DBMS_SQL.NATIVE);
dummy := DBMS_SQL.EXECUTE(c_dbms);
ELSIF INSTR(tab_name,'_FEHL_INV_TAB') != 0 THEN
n_exception_flag := 1300;
-- Fehlertabellen mit Splaten im Format Number(2) anlegen
n_exception_flag := 1100;
DBMS_SQL.PARSE(c_dbms,'ALTER TABLE ' || tab_name || ' ADD (' ||
sensor || fehler_format || ' DEFAULT 1)',DBMS_SQL.NATIVE);
dummy := DBMS_SQL.EXECUTE(c_dbms);
ELSE
n_exception_flag := 1400;
raise_application_error(-20102, 'Fehler: Die Tabelle '||tab_name||' und/oder
Spalte '||sensor||' ist ungültig.');
END IF;
END IF;
n_exception_flag := 2000;
COMMIT;
DBMS_SQL.CLOSE_CURSOR(c_dbms);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('-- Fehler: Exception OTHERS in prozedur_DB_Spalte_anlegen.sql .');
DBMS_OUTPUT.PUT_LINE('-Flag: '||n_exception_flag||'.');
IF DBMS_SQL.IS_OPEN(c_dbms) THEN
DBMS_SQL.CLOSE_CURSOR(c_dbms);
END IF;
END;
/
COMMIT;
110
Anhang
Skript 21: PROZEDUR_DBMS_EXEC.SQL
----------------------------------------------------------------------------------------- Datei: PROZEDUR_DBMS_EXEC.SQL
-- Erstellt: 05.10.2000
-- Beschreibung:
-- Prodzedur führt eine DBMS_SQL Anweisung aus. Der STRING wird geparst und
-- ausgeführt.
--Änderungen:
------------------------------------------------------------------------------------------CREATE OR REPLACE PROCEDURE dbms_exec(STRING IN varchar2) AS
cursor_name
INTEGER;
dummy
INTEGER;
BEGIN
cursor_name := DBMS_SQL.OPEN_CURSOR;
--DDL statements are executed by the parse call, which
--performs the implied commit
DBMS_SQL.PARSE(cursor_name, string, DBMS_SQL.NATIVE);
dummy := DBMS_SQL.EXECUTE(cursor_name);
COMMIT;
DBMS_SQL.CLOSE_CURSOR(cursor_name);
END;
/
COMMIT;
Skript 22: FROMS_COMMIT_ONLY.SQL
----------------------------------------------------------------------------------------- Datei: FROMS_COMMIT_ONLY.SQL
-- Erstellt: 01.12.2000
-- Beschreibung:
-- Forms behandelt ein COMMIT wie den Befehl COMMIT_FORM. Das kann, wenn man eine
Tabelle
-- z.B. in einem Trigger ändern will, zu Fehlern bzw. Fehleranzeigen führen.
-- Das COMMIT in einer Prozedur wird innerhalb der Datenbank wie ein COMMIT der
Datenbank
-- behandelt, aber Forms führt kein COMMIT_FORMS durch.
--Änderungen:
------------------------------------------------------------------------------------------CREATE OR REPLACE PROCEDURE forms_commit_only AS
BEGIN
COMMIT;
END;
/
COMMIT;
111
Anhang
Skript 23: PROZEDUR_FORMS_FUELL_FEHL_AUTO_ANZEIGE.SQL
--------------------------------------------------------------------------------------------------
Datei: PROZEDUR_FORMS_FUELL_FEHL_AUTO_ANZEIGE.SQL
Erstellt: 08.12.2000
Beschreibung:
Diese Funktion wird von dem Skript d:\zub_db\sql\forms\fehl_auto_anzeige.sql genutzt.
Füllt die Tabelle FEHL_AUTO_ANZEIGE_TAB für FORMS50 mit Werten aus ..._FEHL_ATUO_TAB,
die noch nicht als Checked gekennzeichnet wurden. Die Werte werden mit Anfangs- und
Endatum zusammengefasst. Bei FEHLER 1 (Datum nicht vorhanden) wird der Fehler auf
"ALLE" Sensoren und "ALLE" Raumnummern ausgeweitet, da bei einem fehlenden Datum in
ZUB_DATEN_TMP_TAB auch alle Daten fehlen. Der Fehler wird für jede Spaltenwert
einzeln pro Tabelle gezählt. D.h. z.B. enthält die Tabelle 4 Spalten wird bei
einem fehlenden Datum die Anzahl der Fehler auf 4 gesetzt.
-- Änderungen:
-----------------------------------------------------------------------------------------CREATE OR REPLACE PROCEDURE fuell_fehl_auto_anzeige(tab_name_ IN VARCHAR2, sensor_ IN
VARCHAR2, r_nr_ IN VARCHAR2, fehler_nr_ IN INTEGER) AS
-- Eingabe des Fehlerintervalls in Tagen. Erst wenn der Fehler die Dauer des Intervalls
-- nicht mehr auftritt, wird er erneut als Fehlerhaft markiert.
fehlerintervall
NUMBER(8,4) := 1/24;
n_exception_flag
NUMBER(6);
zaehler
NUMBER(8);
dat
DATE;
anfang_dat
DATE;
ende_dat
DATE;
ausgabe
VARCHAR2(255);
anzahl
NUMBER;
tmp_sensor
fehl_auto_anzeige_tab.sensor%TYPE;
tmp_r_nr
fehl_auto_anzeige_tab.r_nr%TYPE;
dummy INTEGER;
c_dbms INTEGER;
CURSOR c_vorh IS
SELECT anz_fehler FROM fehl_auto_anzeige_tab
WHERE UPPER(tab_name) = UPPER(tab_name_)
AND UPPER(sensor) = tmp_sensor
AND UPPER(r_nr) =tmp_r_nr
AND anf_dat = anfang_dat
AND end_dat = ende_dat
AND fehler_nr = fehler_nr_;
BEGIN
c_dbms := DBMS_SQL.OPEN_CURSOR;
n_exception_flag := 0;
tmp_sensor := UPPER(sensor_);
tmp_r_nr
:= UPPER(r_nr_);
-- FEHLER fehl_nr eintragen.
zaehler := 0;
ausgabe := 'SELECT datum FROM '||tab_name_||'_FEHL_AUTO_TAB WHERE '||sensor_||' =
'||fehler_nr_||' AND upper(r_nr) ='||CHR(39)||upper(r_nr_)||CHR(39)||' AND check_flag IS
NULL ORDER BY datum';
--DBMS_OUTPUT.PUT_LINE(ausgabe);
DBMS_SQL.PARSE(c_dbms,ausgabe,DBMS_SQL.NATIVE);
DBMS_SQL.DEFINE_COLUMN(c_dbms,1,dat);
dummy := DBMS_SQL.EXECUTE(c_dbms);
n_exception_flag := 100;
<<fehler1>>
LOOP
IF DBMS_SQL.FETCH_ROWS(c_dbms) > 0 THEN
n_exception_flag := 1000;
DBMS_SQL.COLUMN_VALUE(c_dbms,1,dat);
IF zaehler = 0 THEN
-- Nur bei der ersten Schleife
ende_dat := dat;
anfang_dat := dat;
END IF;
112
Anhang
IF
dat > ende_dat + fehlerintervall THEN
-- Wenn der Fehler nicht mehr als 1 Std. auftritt
-- Werte eintragen.
--DBMS_OUTPUT.PUT_LINE('Schleife :'||zaehler||' Fehler
'||fehler_nr_||' Tabelle: '||tab_name_||' Sensor: '||sensor_||' Raum '||r_nr_ );
--DBMS_OUTPUT.PUT_LINE('
dat
'||to_char(dat,'dd.mm.yyyy hh24:mi')||' anfang '||to_char(anfang_dat,'dd.mm.yyyy
hh24:mi')||' ende '||to_char(ende_dat,'dd.mm.yyyy hh24:mi'));
IF fehler_nr_ = 1 THEN
tmp_sensor := 'ALLE';
tmp_r_nr
:= 'ALLE';
END IF;
OPEN c_vorh;
FETCH c_vorh INTO anzahl;
IF c_vorh%NOTFOUND THEN
INSERT INTO FEHL_AUTO_ANZEIGE_TAB( tab_name
, sensor
, r_nr , anz_fehler , anf_dat
, end_dat , fehler_nr)
VALUES
(tab_name_
, tmp_sensor, tmp_r_nr, zaehler
, anfang_dat , ende_dat , fehler_nr_);
COMMIT;
ELSE
-- Nur Fehler 1 (Datum nicht vorhanden wird gefunden!)
zaehler := zaehler + anzahl;
UPDATE FEHL_AUTO_ANZEIGE_TAB SET anz_fehler = zaehler
WHERE tab_name = tab_name_
AND sensor = tmp_sensor
AND r_nr = tmp_r_nr
AND anf_dat = anfang_dat
AND end_dat = ende_dat
AND fehler_nr = fehler_nr_;
END IF;
anfang_dat := dat;
-- Zähler zurücksetzten
zaehler := 0;
CLOSE c_vorh;
END IF;
ende_dat := dat;
zaehler := zaehler +1;
ELSE
n_exception_flag := 2000;
-- Keine Eintträge mehr gefunden.
-- Wurde irgenwann gezählt?
IF zaehler > 1 THEN
IF fehler_nr_ = 1 THEN
tmp_sensor := 'ALLE';
tmp_r_nr
:= 'ALLE';
END IF;
OPEN c_vorh;
FETCH c_vorh INTO anzahl;
--DBMS_OUTPUT.PUT_LINE('Ende :'||zaehler||' Fehler
'||fehler_nr_||' Tabelle: '||tab_name_||' Sensor: '||sensor_||' Raum '||r_nr_ );
--DBMS_OUTPUT.PUT_LINE('
dat '||to_char(dat,'dd.mm.yyyy
hh24:mi')||' anfang '||to_char(anfang_dat,'dd.mm.yyyy hh24:mi')||' ende
'||to_char(ende_dat,'dd.mm.yyyy hh24:mi'));
IF c_vorh%NOTFOUND THEN
INSERT INTO FEHL_AUTO_ANZEIGE_TAB( tab_name
, sensor
, r_nr , anz_fehler , anf_dat
, end_dat , fehler_nr)
VALUES
(tab_name_
, tmp_sensor, tmp_r_nr, zaehler
, anfang_dat , ende_dat , fehler_nr_);
COMMIT;
ELSE
-- Nur Fehler 1 (Datum nicht vorhanden wird gefunden!)
zaehler := zaehler + anzahl;
UPDATE FEHL_AUTO_ANZEIGE_TAB SET anz_fehler = zaehler
WHERE tab_name = tab_name_
AND sensor = tmp_sensor
AND r_nr = tmp_r_nr
AND anf_dat = anfang_dat
AND end_dat = ende_dat
AND fehler_nr = fehler_nr_;
END IF;
END IF;
EXIT fehler1;
END IF;
113
Anhang
END LOOP fehler1;
DBMS_SQL.CLOSE_CURSOR(c_dbms);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('-- Fehler: Exception OTHERS in Prozedur
FUELL_FEHL_AUTO_ANZEIGE.sql .');
DBMS_OUTPUT.PUT_LINE('-Flag: '||n_exception_flag||'.');
IF DBMS_SQL.IS_OPEN(c_dbms) THEN
DBMS_SQL.CLOSE_CURSOR(c_dbms);
END IF;
RAISE;
END;
/
COMMIT;
114
Anhang
Skript 24: PROZEDUR_FORMS_MESSBEREICH_FEHL_UPDATE.SQL
------------------------------------------------------------------------------------------------
Datei: PROZEDUR_FORMS_MESSBEREICH_FEHL_UPDATE.SQL
Erstellt: 10.12.2000
Beschreibung:
Diese Datei ist FORMS zugeordnet. Sie updatet die geänderten Messbereiche.
Bei einer Änderung des Messbereiches werden u. U. das "check_flag" und der
"Bearbeiter" gelöscht. Wenn mehrere Fehler in dieser aktualisierten Zeile vorhanden
sind, müssen diese Fehler wieder neu geprüft werden, obwohl Sie schon in der
Bearbeitungstabelle FEHL_INV stehen.
-------
Bem.:
Diese Funktion war ursprünglich in Forms selber geschrieben. Dabei wurde die
Bibliothek EXEC_SQL verwendet. Sie enthält allerdings einen BUG. Bei einer
Selektion des Datums und anschliessendem Parsen wird das Datum lediglich im
hh:mi - also 12 Std. Format - ausgegeben und nicht im hh24:mi Format. Das hat
zu einem Fehler beim Updaten geführt.
-- Änderungen:
-----------------------------------------------------------------------------------------CREATE OR REPLACE PROCEDURE forms_messbereich_fehl_update(HW_Adresse IN VARCHAR2) IS
--CREATE OR REPLACE PROCEDURE forms_messbereich_fehl_update(HW_Adresse IN VARCHAR2, num
IN INTEGER) IS
lfd_nr NUMBER(6);
i
NUMBER(4) := 0;
z
zuordnung%ROWTYPE;
c_1 INTEGER;
c_2 INTEGER;
dummy INTEGER;
ausgabe VARCHAR2(255);
d_datum DATE;
ch_datum VARCHAR2(50);
n_exception_flag NUMBER(4);
BEGIN
-- Initialisierung
SELECT nvl(max(nr),0) INTO lfd_nr FROM MESSBEREICH_GEAENDERT_TAB;
n_exception_flag := 0;
i := 0;
c_1 := DBMS_SQL.OPEN_CURSOR;
c_2 := DBMS_SQL.OPEN_CURSOR;
SELECT * INTO z FROM zuordnung WHERE HW_ADR = HW_Adresse;
-- Löschen der bisherigen Messbereichsabweichungen.
ausgabe := 'UPDATE '||z.tab_name||'_FEHL_AUTO_TAB SET '||z.sensor||' = NULL,
check_flag=NULL, Bearbeiter = NULL WHERE '||z.sensor||' = 3 AND upper(r_nr) =
'||CHR(39)||upper(z.r_nr)||CHR(39);
INSERT INTO MESSBEREICH_GEAENDERT_TAB VALUES(lfd_nr+3, ausgabe);
COMMIT;
DBMS_SQL.PARSE(c_1,ausgabe,DBMS_SQL.NATIVE);
dummy := DBMS_SQL.EXECUTE(c_1);
COMMIT;
-- Abfragen der neuen Messbereichsfehler
n_exception_flag := 200;
ausgabe := 'SELECT datum FROM '|| z.tab_name ||'_ROH_TAB WHERE '|| z.sensor ||' NOT
BETWEEN '||z.minwert||' AND '||z.maxwert||' AND '||z.sensor||' IS NOT NULL AND
upper(r_nr) ='||CHR(39)||upper(z.r_nr)||CHR(39);
INSERT INTO MESSBEREICH_GEAENDERT_TAB VALUES(lfd_nr+2, ausgabe);
COMMIT;
DBMS_SQL.PARSE(c_1,ausgabe,DBMS_SQL.NATIVE);
DBMS_SQL.DEFINE_COLUMN(c_1, 1, d_datum);
dummy := DBMS_SQL.EXECUTE(c_1);
n_exception_flag := 300;
<<mess_update>>
LOOP
115
Anhang
IF DBMS_SQL.FETCH_ROWS(c_1) > 0 THEN
i := i + 1;
n_exception_flag := 500;
-- Datums heraussuchen.
DBMS_SQL.COLUMN_VALUE(c_1, 1, d_datum);
ch_datum := to_char(d_datum,'dd.mm.yyyy hh24:mi');
IF datum_r_nr_in_tab(d_datum, z.r_nr, z.tab_name||'_FEHL_AUTO_TAB')= TRUE
THEN
-- Datum, Raumnummer vorhanden -> UPDATE
ausgabe := 'UPDATE '||z.tab_name||'_FEHL_AUTO_TAB SET
'||z.sensor||' = 3, check_flag = NULL, Bearbeiter = NULL WHERE upper(r_nr) =
'||CHR(39)||upper(z.r_nr)||CHR(39)||' AND '||z.sensor||' IS NULL AND datum =
to_date('||CHR(39)||ch_datum||CHR(39)||','||CHR(39)||'dd.mm.yyyy hh24:mi'||CHR(39)||')';
ELSE
-- Datum, Raumnummer nicht vorhanden -> INSERT
ausgabe := 'INSERT INTO '||z.tab_name||'_FEHL_AUTO_TAB
(datum,r_nr,'||z.sensor||') VALUES
(to_date('||CHR(39)||ch_datum||CHR(39)||','||CHR(39)||'dd.mm.yyyy
hh24:mi'||CHR(39)||'),'||CHR(39)||upper(z.r_nr)||CHR(39)||', 3)';
END IF;
DBMS_SQL.PARSE(c_2,ausgabe,DBMS_SQL.NATIVE);
dummy := DBMS_SQL.EXECUTE(c_2);
COMMIT;
ELSE
INSERT INTO MESSBEREICH_GEAENDERT_TAB VALUES(lfd_nr+4, ' Es wurden
'||i||' Fehler markiert.');
COMMIT;
INSERT INTO MESSBEREICH_GEAENDERT_TAB VALUES(lfd_nr+1, 'Letzter Befehl:
'||ausgabe);
COMMIT;
EXIT mess_update;
END IF;
n_exception_flag := 1000;
COMMIT;
END LOOP mess_update;
n_exception_flag := 2000;
DBMS_SQL.CLOSE_CURSOR(c_1);
DBMS_SQL.CLOSE_CURSOR(c_2);
EXCEPTION
WHEN NO_DATA_FOUND THEN
IF DBMS_SQL.IS_OPEN(c_1) THEN
DBMS_SQL.CLOSE_CURSOR(c_1);
END IF;
IF DBMS_SQL.IS_OPEN(c_2) THEN
DBMS_SQL.CLOSE_CURSOR(c_2);
END IF;
DBMS_OUTPUT.PUT_LINE('Keine Daten gefunden. Prozedur UPDATE_FEHLERTABS. Flag:
'||n_exception_flag);
RAISE;
WHEN OTHERS THEN
IF DBMS_SQL.IS_OPEN(c_1) THEN
DBMS_SQL.CLOSE_CURSOR(c_1);
END IF;
IF DBMS_SQL.IS_OPEN(c_2) THEN
DBMS_SQL.CLOSE_CURSOR(c_2);
END IF;
DBMS_OUTPUT.PUT_LINE('Unbehandelte Exception in Prozedur UPDATE_FEHLERTABS. Flag:
'||n_exception_flag);
RAISE;
END;
/
COMMIT;
116
Anhang
Skript 25: PROZEDUR_ZUB_DATEN_TMP_TAB_ANLEGEN.SQL
----------------------------------------------------------------------------------------- Datei: PROZEDUR_ZUB_DATEN_TMP_TAB_ANLEGEN.SQL
-- Erstellt: 13.11.2000
-- Beschreibung:
-- Automatisches Einfügen von Spalten in die Tabelle ZUB_DATEN_TMP_TAB
-- mit den Einträgen Hw_Adr aus der Tabelle ZUORDNUNG. Die Fehlerprüfung von ZUORDNUNG
-- muss korrekt sein.
---- Bem.:
-- Die Tabelle ZUB_DATEN_TMP_TAB wurde in Umgebungstabellen.sql angelegt mit den
-- Spalten datum und uhrzeit als VARCHAR2
--Änderungen:
------------------------------------------------------------------------------------------SET SERVEROUTPUT ON
CREATE OR REPLACE PROCEDURE zub_daten_tmp_tab_anlegen IS
binaer
VARCHAR2(20) := ' NUMBER(1)';
analog
VARCHAR2(20) := ' NUMBER(5,2)';
analog_binaer
zuordnung.format%TYPE;-- entspricht der Spalte FORMAT in
ZUORDNUNG
c_dbms
INTEGER;
dummy
INTEGER;
hw_adr_neu
Zuordnung.hw_adr%TYPE;
CURSOR c_hw_adr_neu
IS
SELECT upper(hw_adr) hw_adr FROM zuordnung z
MINUS
SELECT user_tab.column_name FROM user_tab_columns user_tab
WHERE user_tab.table_name = 'ZUB_DATEN_TMP_TAB';
CURSOR c_format
IS SELECT
WHERE hw_adr_neu = hw_adr;
upper(format) format FROM zuordnung z
BEGIN
DBMS_OUTPUT.ENABLE(1000000);
DBMS_OUTPUT.PUT_LINE('--*****************************************************--');
DBMS_OUTPUT.PUT_LINE('-Prozedur: ZUB_DATEN_TMP_TAB_ANLEGEN');
DBMS_OUTPUT.PUT_LINE('-prozedur_zub_daten_tmp_tab_anlegen.sql');
c_dbms := DBMS_SQL.OPEN_CURSOR;
-- Anlegen der Tabellennamen, wie sie in der Tabelle Zuordnung stehen.
FOR c IN c_hw_adr_neu LOOP
-- Diese Spalten sind in ZUB_DATEN_TMP_TAB nicht vorhanden.
hw_adr_neu := c.hw_adr;
OPEN c_format;
FETCH c_format INTO analog_binaer; -- Selektieren aus ZUORDNUNG, ob es sich um
einen Binär- oder Analogwert handelt.
IF c_format%NOTFOUND THEN
analog_binaer := NULL;
END IF;
IF SUBSTR(UPPER(analog_binaer),1,2) = 'B' THEN
-- Binärwert
DBMS_SQL.PARSE(c_dbms,'ALTER TABLE ZUB_DATEN_TMP_TAB ADD (' || hw_adr_neu
|| binaer || ')',DBMS_SQL.NATIVE);
dummy := DBMS_SQL.EXECUTE(c_dbms);
DBMS_OUTPUT.PUT_LINE('-- Spalte '|| hw_adr_neu ||' im Format '|| binaer
||' in ZUB_DATEN_TMP_TAB eingefügt.');
ELSIF SUBSTR(UPPER(analog_binaer),1,2) = 'A' THEN
-- Analogwert
DBMS_SQL.PARSE(c_dbms,'ALTER TABLE ZUB_DATEN_TMP_TAB ADD (' || hw_adr_neu
|| analog || ')',DBMS_SQL.NATIVE);
dummy := DBMS_SQL.EXECUTE(c_dbms);
DBMS_OUTPUT.PUT_LINE('-- Spalte '|| hw_adr_neu ||' im Format '|| analog
||' in ZUB_DATEN_TMP_TAB eingefügt.');
ELSE
DBMS_OUTPUT.PUT_LINE('-- FEHLER: Die Spalte "Format" in ZUORDNUNG muss
ein A für Analogwert oder B für Binärwert enthalten.');
117
Anhang
DBMS_OUTPUT.PUT_LINE('-starten Sie das Skript erneut.');
DBMS_OUTPUT.PUT_LINE('-nicht geändert.');
END IF;
CLOSE c_format;
-- Eintrag in die Tabelle
COMMIT;
END LOOP;
DBMS_SQL.CLOSE_CURSOR(c_dbms);
Verbessern Sie den Fehler und
Die Tabelle ZUB_DATEN_TMP_TAB wird
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('-- Fehler: Exception OTHERS in
prozedur_zub_daten_tmp_tab_anlegen.sql.');
IF c_hw_adr_neu%ISOPEN THEN
CLOSE c_hw_adr_neu;
END IF;
IF c_format%ISOPEN THEN
CLOSE c_format;
END IF;
IF DBMS_SQL.IS_OPEN(c_dbms) THEN
DBMS_SQL.CLOSE_CURSOR(c_dbms);
END IF;
RAISE;
END;
/
COMMIT;
--select table_name from user_tables;
--desc Klima;
--desc StdR;
118
Anhang
Skript 26: SPOOL_FEHLER_TABS.SQL
----------------------------------------------------------------------------------------- Datei: SPOOL_FEHLER_TABS.SQL
-- Erstellt: 07.12.2000
-- Beschreibung:
-- Spoolt die Tabelle FEHL_AUTO_ANZ_TAB und die Tabelle FEHL_TAB
-- in unten genanntes TXT-File.
-- Änderungen:
-----------------------------------------------------------------------------------------SET SERVEROUTPUT ON;
SET Space 0
SET LINESIZE 500
--SET termout off;
--SET echo off;
--SET heading off;
SET Space 4
SET Feedback off
SPOOL d:\zub_db\sql\meldungen\fehler_tabs.txt
DECLARE
anz NUMBER(6);
BEGIN
DBMS_OUTPUT.PUT_LINE('-- Datei erstellt am '||to_char(SYSDATE,'dd.mm.yyyy hh24:mi'));
DBMS_OUTPUT.PUT_LINE('********** FEHL_AUTO_ANZEIGE_TAB **********');
SELECT count(*) INTO anz FROM fehl_auto_anzeige_tab;
IF anz = 0 THEN
DBMS_OUTPUT.PUT_LINE('FEHL_AUTO_ANZEIGE_TAB ist leer!');
DBMS_OUTPUT.PUT_LINE('.');
DBMS_OUTPUT.PUT_LINE('.');
DBMS_OUTPUT.PUT_LINE('------------------------------------------------------');
END IF;
END;
/
select
RPAD(TAB_NAME,10,' ') Tabelle,
RPAD(SENSOR,10,' ') Sensor,
R_NR,
RPAD(ANZ_FEHLER,5,' ') Anzeige_Fehler,
to_char(ANF_DAT,'dd.mm.yyyy hh24:mi'),
to_char(END_DAT,'dd.mm.yyyy hh24:mi'),
RPAD(FEHLER_NR,9,' ') Fehler_Nr,
CHECK_FLAG,
LPAD(BEARBEITER,10,' ') Bearbeiter
FROM fehl_auto_anzeige_tab
order by tab_name, fehler_nr, anf_dat, r_nr, sensor;
DECLARE
anz NUMBER(6);
BEGIN
DBMS_OUTPUT.PUT_LINE('.');
DBMS_OUTPUT.PUT_LINE('.');
DBMS_OUTPUT.PUT_LINE('******************************************************************
***********');
DBMS_OUTPUT.PUT_LINE('******************************************************************
***********');
DBMS_OUTPUT.PUT_LINE('********** FEHL_TAB **********');
SELECT count(*) INTO anz FROM fehl_tab;
IF anz = 0 THEN
DBMS_OUTPUT.PUT_LINE('FEHL_TAB ist leer!');
DBMS_OUTPUT.PUT_LINE('.');
DBMS_OUTPUT.PUT_LINE('.');
DBMS_OUTPUT.PUT_LINE('------------------------------------------------------');
END IF;
END;
/
119
Anhang
SELECT
Lfd_Nr,
RPAD(TAB_NAME,10,' ') Tabelle,
RPAD(SENSOR,10,' ') Sensor,
R_NR,
RPAD(FEHLER_NR,9,' ') Fehler_Nr,
to_char(ANF_DAT,'dd.mm.yyyy hh24:mi') Anfang,
to_char(END_DAT,'dd.mm.yyyy hh24:mi') Ende,
RPAD(BEARBEITER,10,' ') Bearbeiter,
Bemerkung
FROM fehl_tab
order by lfd_nr;
DECLARE
anz NUMBER(6);
BEGIN
DBMS_OUTPUT.PUT_LINE('.');
DBMS_OUTPUT.PUT_LINE('.');
DBMS_OUTPUT.PUT_LINE('******************************************************************
***********');
DBMS_OUTPUT.PUT_LINE('******************************************************************
***********');
DBMS_OUTPUT.PUT_LINE('********** MOEGLICHE_UEBERSCHNEIDUNG_TAB **********');
SELECT count(*) INTO anz FROM moegliche_ueberschneidung_tab;
IF anz = 0 THEN
DBMS_OUTPUT.PUT_LINE('MOEGLICHE_UEBERSCHNEIDUNG_TAB ist leer!');
DBMS_OUTPUT.PUT_LINE('.');
DBMS_OUTPUT.PUT_LINE('.');
DBMS_OUTPUT.PUT_LINE('------------------------------------------------------');
END IF;
END;
/
SELECT
RPAD(NR,4,' ') Nr,
RPAD(TAB_NAME,10,' ') Tabelle,
RPAD(SENSOR,10,' ') Sensor,
R_NR,
RPAD(ANZ_FEHLER,10,' ') Anz_Fehler,
to_char(ANF_DAT,'dd.mm.yyyy hh24:mi') Anfang,
to_char(END_DAT,'dd.mm.yyyy hh24:mi') Ende,
RPAD(FEHLER_NR,9,' ') Fehler_Nr,
CHECK_FLAG,
RPAD(BEARBEITER,10,' ') Bearbeiter
FROM moegliche_ueberschneidung_tab
order by nr ASC;
SPOOL OFF
EXIT;
120
Anhang
Skript 27: SPOOL_KOPIEREN_NACH_ZUB_DATEN_TMP_TAB.SQL
----------------------------------------------------------------------------------------- Datei: SPOOL_KOPIEREN_NACH_ZUB_DATEN_TMP_TAB.SQL
-- Erstellt: 06.11.2000
-- Beschreibung:
-- Wird nur zum Testen der Datenbank benötigt. Dieses Skript kopiert alle Werte aus
Daten
-- nach ZUB_DATEN_TMP_TAB.
--
--Änderungen:
--------------------------------------------------------------------------------------------SET SERVEROUTPUT ON;
--SET termout off;
--SET echo off;
--SET heading off;
--SET feedback off;
--SET LINESIZE 255
SPOOL d:\zub_db\sql\test\kopieren_nach_zub_daten_tmp_tab.sql
DECLARE
hk
CHAR(1);
tmp_tab_name
zuordnung.tab_name%TYPE;
tmp_r_nr
zuordnung.r_nr%TYPE := NULL;
letzterSensor
zuordnung.sensor%TYPE := NULL;
strout
VARCHAR(50) := NULL;
flag
NUMBER(1) := 0;
--spalte
user_tab_columns.column_name%TYPE;
letzte_spalte user_tab_columns.column_name%TYPE;
r_nr_alt
zuordnung.r_nr%TYPE := NULL;
-- Nur Tabellennamen auslesen
-- Tabellennamen und Raumnummern auslesen.
CURSOR c_spalte IS SELECT UPPER(column_name) spaltenname FROM user_tab_columns WHERE
table_name = 'ZUB_DATEN_TMP_TAB'
AND UPPER(column_name) NOT IN ('DATUM', 'UHRZEIT')
ORDER BY column_name ASC;
-- Spaltennamen auslesen.
--CURSOR c_spalte IS
--SELECT user_tab.column_name spalte FROM user_tab_columns user_tab
--WHERE upper(tmp_tab_name) = upper(user_tab.table_name)
--ORDER BY user_tab.column_name ASC;
c_dbms INTEGER;
dummy
INTEGER;
------------------------------------------------------------------------------------------BEGIN
DBMS_OUTPUT.ENABLE(1000000);
SELECT strich INTO hk FROM hochkomma;
DBMS_OUTPUT.PUT_LINE('---------------------------------------------------------------------------------------');
DBMS_OUTPUT.PUT_LINE('-- DIES IST EIN TESTSKRIPT!!!!!!!!!!!!!!!!');
DBMS_OUTPUT.PUT_LINE('-- Datei: KOPIEREN_NACH_ZUB_DATEN_TMP_TAB.SQL');
DBMS_OUTPUT.PUT_LINE('-- Erstellt: '||to_char(SYSDATE,'dd.mm.yyyy hh24:mi'));
DBMS_OUTPUT.PUT_LINE('-- Aus Datei KOPIEREN_NACH_ZUB_DATEN_TMP_TAB');
DBMS_OUTPUT.PUT_LINE('-- Beschreibung: ');
DBMS_OUTPUT.PUT_LINE('-- Kopieren der Daten aus DATEN nach ZUB_DATEN_TMP_TAB.');
DBMS_OUTPUT.PUT_LINE('---------------------------------------------------------------------------------------');
DBMS_OUTPUT.PUT_LINE('-******************************************************************--');
DBMS_OUTPUT.PUT_LINE('-- Leeren der temprären Tabellen');
DBMS_OUTPUT.PUT_LINE('TRUNCATE TABLE ZUB_DATEN_TMP_TAB;');
121
Anhang
DBMS_OUTPUT.PUT_LINE('COMMIT;');
DBMS_OUTPUT.PUT_LINE('-******************************************************************--');
DBMS_OUTPUT.PUT_LINE('-- Kopieren der Werte in die temporären Tabellen, ');
DBMS_OUTPUT.PUT_LINE('-- bei denen das Datum neuer ist als in den Rohdatentabellen.');
DBMS_OUTPUT.PUT_LINE('-----------------------------------------------');
DBMS_OUTPUT.PUT_LINE('INSERT INTO ZUB_DATEN_TMP_TAB (datum, uhrzeit, ');
SELECT MAX(UPPER(column_name)) INTO letzterSensor FROM user_tab_columns
WHERE upper(table_name)='ZUB_DATEN_TMP_TAB'
AND UPPER(column_name) NOT IN ('DATUM', 'UHRZEIT')
ORDER BY column_name ;
FOR n IN c_spalte LOOP
IF UPPER(n.spaltenname) = UPPER(letzterSensor) THEN
strout := n.spaltenname || ')';
ELSE
strout := n.spaltenname || ', ';
END IF;
DBMS_OUTPUT.PUT_LINE(strout);
END LOOP;
DBMS_OUTPUT.PUT_LINE('SELECT datum, uhrzeit, ');
FOR z IN c_spalte LOOP
IF UPPER(z.spaltenname) = UPPER(letzterSensor) THEN
strout := z.spaltenname;
ELSE
strout := z.spaltenname || ', ';
END IF;
DBMS_OUTPUT.PUT_LINE(strout);
END LOOP;
DBMS_OUTPUT.PUT_LINE('FROM DATEN;');
DBMS_OUTPUT.PUT_LINE('COMMIT;');
DBMS_OUTPUT.PUT_LINE('-----------------------------------------------');
--DBMS_OUTPUT.PUT_LINE('-******************************************************************--');
--DBMS_OUTPUT.PUT_LINE('-- Löschen ungültiger Einträge von ZUB_DATEN_TMP_TAB');
--DBMS_OUTPUT.PUT_LINE('DELETE FROM ZUB_DATEN_TMP_TAB WHERE datum IS NULL OR uhrzeit IS
NULL;');
--DBMS_OUTPUT.PUT_LINE('-******************************************************************--');
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('-- Fehler: Exception OTHERS in
SPOOL_KOPIEREN_NACH_ZUB_DATEN_TMP_TAB.SQL.');
IF c_spalte%ISOPEN THEN
CLOSE c_spalte;
END IF;
END;
/
COMMIT;
SPOOL OFF;
--EXIT;
122
Anhang
Skript 28: SPOOL_ROHDATEN_EINTRAGEN.SQL
----------------------------------------------------------------------------------------- Datei: SPOOL_Rohdaten_eintragen.SQL
-- Erstellt: 06.11.2000
-- Beschreibung:
-- Spoolt die Datei Rohdaten_eintragen.sql zum Kopieren der Tabelle ZUB_DATEN_TMP_TAB
-- in die einzelnen ...TMP_TAB Tabellen der Datenbank.
--
--Änderungen:
------------------------------------------------------------------------------------------SET SERVEROUTPUT ON;
SET termout off;
SET echo off;
SET heading off;
SET feedback off;
SET LINESIZE 200
SPOOL d:\zub_db\sql\Rohdaten_eintragen.sql
DECLARE
hk
tmp_tab_name
tmp_R_Nr
letzterSensor
ausgabe
R_Nr_alt
CHAR(1);
zuordnung.tab_name%TYPE;
zuordnung.R_Nr%TYPE := NULL;
zuordnung.sensor%TYPE := NULL;
VARCHAR2(250) := NULL;
zuordnung.R_Nr%TYPE := NULL;
-- Nur Tabellennamen auslesen
CURSOR c_tab_name IS SELECT DISTINCT upper(tab_name) tab_name from zuordnung
ORDER BY tab_name;
-- Tabellennamen und Raumnummern auslesen.
CURSOR c_namen IS SELECT DISTINCT upper(tab_name) tab_name, upper(R_Nr) R_Nr from
zuordnung
ORDER BY tab_name, R_Nr;
-- Restliche Zeile aus Zuordnung selektieren.
CURSOR c_zeile IS SELECT upper(hw_adr) hw_adr, upper(R_Nr) R_Nr, upper(sensor) sensor
FROM zuordnung
WHERE upper(tmp_tab_name) = upper(tab_name)
AND upper(R_Nr) = upper(tmp_R_Nr)
ORDER BY hw_adr ASC;
-----------------------------------------------------------------------------------------BEGIN
DBMS_OUTPUT.ENABLE(1000000);
SELECT strich INTO hk FROM hochkomma;
DBMS_OUTPUT.PUT_LINE('-- Datei erstellt am '||to_char(SYSDATE,'dd.mm.yyyy hh24:mi'));
DBMS_OUTPUT.PUT_LINE('---------------------------------------------------------------------------------------');
DBMS_OUTPUT.PUT_LINE('-- Datei: Rohdaten_eintragen.sql');
DBMS_OUTPUT.PUT_LINE('-- Erstellt: '||to_char(SYSDATE,'dd.mm.yyyy hh24:mi'));
DBMS_OUTPUT.PUT_LINE('-- Aus Datei SPOOL_Rohdaten_eintragen');
DBMS_OUTPUT.PUT_LINE('-- Beschreibung: ');
DBMS_OUTPUT.PUT_LINE('-- Kopieren der Daten aus ZUB_DATEN_TMP_TAB in die Datenbank TMP
Tabellen ');
DBMS_OUTPUT.PUT_LINE('---------------------------------------------------------------------------------------');
DBMS_OUTPUT.PUT_LINE('-******************************************************************--');
DBMS_OUTPUT.PUT_LINE('-- Leeren der temporären Tabellen');
FOR n IN c_tab_name LOOP
DBMS_OUTPUT.PUT_LINE('TRUNCATE TABLE '||n.tab_name||'_TMP_TAB;');
DBMS_OUTPUT.PUT_LINE('COMMIT;');
END LOOP;
123
Anhang
DBMS_OUTPUT.PUT_LINE('-******************************************************************--');
DBMS_OUTPUT.PUT_LINE('-- Löschen ungültiger Einträge von ZUB_DATEN_TMP_TAB');
DBMS_OUTPUT.PUT_LINE('DELETE FROM ZUB_DATEN_TMP_TAB WHERE Datum IS NULL OR Uhrzeit IS
NULL;');
DBMS_OUTPUT.PUT_LINE('COMMIT;');
DBMS_OUTPUT.PUT_LINE('-******************************************************************--');
DBMS_OUTPUT.PUT_LINE('-- Kopieren der Werte in die temporären Tabellen, ');
DBMS_OUTPUT.PUT_LINE('-- bei denen das Datum neuer ist als in den Rohdatentabellen.');
FOR n IN c_namen LOOP
ausgabe := NULL;
tmp_tab_name := n.tab_name; -- Tabellennamen und
tmp_R_Nr := n.R_Nr;
-- Raumnummer aus Zuordnung selektieren.
R_Nr_alt := n.R_Nr;
-- erste Raumnummer!
DBMS_OUTPUT.PUT_LINE('-----------------------------------------------');
DBMS_OUTPUT.PUT_LINE('INSERT INTO '|| tmp_tab_name ||'_TMP_TAB (Datum, R_Nr, ');
FOR z IN c_zeile LOOP -- Restliche Zeilen aus ZUORDNUNG in der 'tmp_tab_name'
steht.
IF spalte_in_tabelle('ZUB_DATEN_TMP_TAB',z.hw_adr) = TRUE THEN
SELECT MAX(hw_adr) INTO letzterSensor FROM zuordnung WHERE
upper(tmp_tab_name) = upper(tab_name) AND upper(R_Nr) = upper(tmp_R_Nr) ORDER BY hw_adr;
IF z.R_Nr = R_Nr_alt THEN
IF UPPER(z.hw_adr) = UPPER(letzterSensor) THEN
ausgabe := ausgabe || z.sensor || ')';
DBMS_OUTPUT.PUT_LINE(ausgabe);
ELSE
ausgabe := ausgabe || z.sensor || ', ';
IF LENGTH(ausgabe) > 150 THEN
DBMS_OUTPUT.PUT_LINE(ausgabe);
ausgabe := NULL;
END IF;
END IF;
R_Nr_alt := z.R_Nr;
END IF;
END IF;
END LOOP;
ausgabe := NULL;
DBMS_OUTPUT.PUT_LINE('SELECT to_date(Datum||Uhrzeit,'||hk||'dd.mm.yyyy
hh24:mi'||hk||'), '||hk||tmp_R_Nr||hk||', ');
FOR z IN c_zeile LOOP -- Restliche Zeilen aus ZUORDNUNG in der 'tmp_tab_name'
steht.
IF spalte_in_tabelle('ZUB_DATEN_TMP_TAB',z.hw_adr) = TRUE THEN
-SELECT MAX(sensor) INTO letzterSensor FROM zuordnung WHERE
upper(tmp_tab_name) = upper(tab_name) AND upper(R_Nr) = upper(tmp_R_Nr);
IF z.R_Nr = R_Nr_alt THEN
IF UPPER(z.hw_adr) = UPPER(letzterSensor) THEN
ausgabe := ausgabe||z.hw_adr;
DBMS_OUTPUT.PUT_LINE(ausgabe);
ELSE
ausgabe := ausgabe|| z.hw_adr || ', ';
IF LENGTH(ausgabe) > 150 THEN
DBMS_OUTPUT.PUT_LINE(ausgabe);
ausgabe := NULL;
END IF;
END IF;
R_Nr_alt := z.R_Nr;
END IF;
END IF;
END LOOP;
DBMS_OUTPUT.PUT_LINE('FROM ZUB_DATEN_TMP_TAB');
DBMS_OUTPUT.PUT_LINE('WHERE to_date(Datum||Uhrzeit,'||hk||'dd.mm.yyyy
hh24:mi'||hk||') > (SELECT
nvl(max(Datum),to_date('||hk||'01.01.1111'||hk||','||hk||'dd.mm.yyyy hh24:mi'||hk||'))')
;
DBMS_OUTPUT.PUT_LINE('
FROM
'||tmp_tab_name||'_ROH_TAB') ;
DBMS_OUTPUT.PUT_LINE('
WHERE
R_Nr ='||hk|| tmp_R_Nr||hk||');') ;
DBMS_OUTPUT.PUT_LINE('COMMIT;');
DBMS_OUTPUT.PUT_LINE('-----------------------------------------------');
END LOOP;
124
Anhang
DBMS_OUTPUT.PUT_LINE('-******************************************************************--');
DBMS_OUTPUT.PUT_LINE('-- Kopieren der Temporären Daten in die Rohdatentabellen');
FOR n IN c_tab_name LOOP
DBMS_OUTPUT.PUT_LINE('INSERT INTO '||n.tab_name||'_ROH_TAB SELECT * FROM
'||n.tab_name||'_TMP_TAB;');
DBMS_OUTPUT.PUT_LINE('COMMIT;');
END LOOP;
DBMS_OUTPUT.PUT_LINE('-******************************************************************--');
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('-- Fehler: Exception OTHERS in spool_Rohdaten_eintragen.sql.');
IF c_zeile%ISOPEN THEN
CLOSE c_zeile;
END IF;
IF c_namen%ISOPEN THEN
CLOSE c_namen;
END IF;
IF c_tab_name%ISOPEN THEN
CLOSE c_tab_name;
END IF;
END;
/
COMMIT;
SET FEEDBACK ON;
SPOOL OFF;
125
Anhang
Skript 29: SPOOL_ROHKORR.SQL
-----------------------------------------------------------------------------------------------
Datei: SPOOL_ROHKORR.SQL
Erstellt: 21.11.2000
Beschreibung:
Aus den Tabellen ..._FEHL_INV_TAB und den Tabellen ..._ROH_TAB
werden die Tabellen ..._ROHKORR_TAB gefüllt.
Dieses Skript spoolt die reine SQL Datei ROHKORR.sql, die obige
Angaben ausführt.
-- Bem.:
-- Änderungen:
-----------------------------------------------------------------------------------------SET SERVEROUTPUT ON;
SET termout off;
SET echo off;
SET heading off;
SET feedback off;
SET LINESIZE 200
SPOOL d:\zub_db\sql\ROHKORR.sql
DECLARE
-- Minimale Anzahl der Werte in ..._ROHKORR_TAB pro Tag, damit ein Mittelwert übernommen
wird.
min_anz_rohkorrwerte_pro_tag NUMBER(5) := 230;
hk
CHAR(1);
tmp_tab_name
zuordnung.tab_name%TYPE;
letzterSensor
zuordnung.sensor%TYPE := NULL;
ausgabe
VARCHAR2(255) := NULL;
-- Nur Tabellennamen auslesen
CURSOR c_tab_name IS SELECT DISTINCT upper(tab_name) tab_name from zuordnung
ORDER BY tab_name;
-- Sensoren aus Zuordnung selektieren.
CURSOR c_sensor IS SELECT DISTINCT upper(sensor) sensor FROM zuordnung
WHERE upper(tmp_tab_name) = upper(tab_name)
ORDER BY sensor ASC;
------------------------------------------------------------------------------------------BEGIN
DBMS_OUTPUT.ENABLE(1000000);
SELECT strich INTO hk FROM hochkomma; -- das Zeichen ' in hk speichern.
DBMS_OUTPUT.PUT_LINE('-- Datei erstellt am '||to_char(SYSDATE,'dd.mm.yyyy hh24:mi'));
DBMS_OUTPUT.PUT_LINE('-------------------------------------------------------------------------------');
DBMS_OUTPUT.PUT_LINE('-- Datei: ROHKORR.SQL');
DBMS_OUTPUT.PUT_LINE('-- Erstellt: '||to_char(SYSDATE,'dd.mm.yyyy hh24:mi'));
DBMS_OUTPUT.PUT_LINE('-- Aus Datei SPOOL_ROHKORR.SQL');
DBMS_OUTPUT.PUT_LINE('-- Beschreibung: ');
DBMS_OUTPUT.PUT_LINE('-- Füllen der Tabellen ..._ROHKORR_TAB:');
DBMS_OUTPUT.PUT_LINE('-- ..._ROHKORR_TAB wird durch Multiplikation der Tabellen
..._ROH_TAB und ');
DBMS_OUTPUT.PUT_LINE('-- ..._FEHL_INV_TAB gefüllt. ');
DBMS_OUTPUT.PUT_LINE('-------------------------------------------------------------------------------');
DBMS_OUTPUT.PUT_LINE('-******************************************************************--');
DBMS_OUTPUT.PUT_LINE('-- Leeren der Tabellen ..._ROHKORR_TAB');
FOR n IN c_tab_name LOOP
DBMS_OUTPUT.PUT_LINE('TRUNCATE TABLE '||n.tab_name||'_ROHKORR_TAB;');
DBMS_OUTPUT.PUT_LINE('COMMIT;');
END LOOP;
---------------------------------------------------------------------------------------DBMS_OUTPUT.PUT_LINE('-******************************************************************--');
126
Anhang
DBMS_OUTPUT.PUT_LINE('-- Füllen der Tabellen ..._ROHKORR_TAB');
FOR n IN c_tab_name LOOP
ausgabe := NULL;
tmp_tab_name := n.tab_name;
DBMS_OUTPUT.PUT_LINE('-----------------------------------------------');
DBMS_OUTPUT.PUT_LINE('INSERT INTO '|| tmp_tab_name ||'_ROHKORR_TAB (Datum, R_Nr,
');
FOR z IN c_sensor LOOP -- Restliche Zeilen aus ZUORDNUNG in der 'tmp_tab_name'
steht.
IF spalte_in_tabelle(tmp_tab_name||'_ROHKORR_TAB',z.sensor) = TRUE THEN
SELECT MAX(sensor) INTO letzterSensor FROM zuordnung
WHERE upper(tmp_tab_name) = upper(tab_name) ORDER BY sensor;
IF UPPER(z.sensor) = UPPER(letzterSensor) THEN
ausgabe := ausgabe|| z.sensor || ')';
DBMS_OUTPUT.PUT_LINE(ausgabe);
ELSE
ausgabe := ausgabe|| z.sensor || ', ';
IF LENGTH(ausgabe) > 150 THEN
DBMS_OUTPUT.PUT_LINE(ausgabe);
ausgabe := NULL;
END IF;
END IF;
END IF;
END LOOP;
ausgabe := NULL;
DBMS_OUTPUT.PUT_LINE('SELECT r.Datum ,r.R_Nr , ');
FOR z IN c_sensor LOOP -- Restliche Zeilen aus ZUORDNUNG in der 'tmp_tab_name'
steht.
IF spalte_in_tabelle(tmp_tab_name||'_ROHKORR_TAB',z.sensor) = TRUE THEN
IF UPPER(z.sensor) = UPPER(letzterSensor) THEN
ausgabe := ausgabe||'f.'||z.sensor||' * r.'||z.sensor;
DBMS_OUTPUT.PUT_LINE(ausgabe);
ELSE
ausgabe :=ausgabe|| 'f.'||z.sensor||' * r.'||z.sensor||',
';
IF LENGTH(ausgabe) > 150 THEN
DBMS_OUTPUT.PUT_LINE(ausgabe);
ausgabe := NULL;
END IF;
END IF;
END IF;
END LOOP;
DBMS_OUTPUT.PUT_LINE('FROM '||tmp_tab_name||'_ROH_TAB r,
'||tmp_tab_name||'_FEHL_INV_TAB f');
DBMS_OUTPUT.PUT_LINE('WHERE r.Datum = f.Datum');
DBMS_OUTPUT.PUT_LINE('AND r.R_Nr = f.R_Nr;');
DBMS_OUTPUT.PUT_LINE('COMMIT;');
DBMS_OUTPUT.PUT_LINE('-----------------------------------------------');
END LOOP;
END;
/
SET FEEDBACK ON;
SPOOL OFF;
127
Anhang
Skript 30: SPOOL_TAGMONATJAHR.SQL
----------------------------------------------------------------------------------------- Datei: SPOOL_TAGMONATJAHR.SQL
-- Erstellt: 21.11.2000
-- Beschreibung:
-- Aus der Tabelle ..._ROHKORR_TAB werden die Tabellen ..._TAG_TAB, ..._MONAT_TAB,
-- und JAHR_TAB gefüllt.
-- Diese Datei spoolt das reine SQL Skript TagMonatJahr.sql, welches zum Füllen
-- der Datentabellen benötigt wird.
-----
**************************************
Der Anwender kann unten eintragen, wieviele Messwerte pro Tag, Monat, Jahr vorhanden
sein müssen, damit die Werte übernommen werden.
**************************************
-- Bem.:
-- Änderungen:
-----------------------------------------------------------------------------------------SET SERVEROUTPUT ON;
SET termout off;
SET echo off;
SET heading off;
SET feedback off;
SET LINESIZE 80
SPOOL d:\zub_db\sql\TagMonatJahr.sql
DECLARE
-- Minimale Anzahl der Werte in ..._ROHKORR_TAB pro Tag, damit ein Mittelwert übernommen
wird.
min_anz_rohkorrwerte_pro_tag NUMBER(5) := 230;
hk
CHAR(1);
tmp_tab_name
zuordnung.tab_name%TYPE;
letzterSensor
zuordnung.sensor%TYPE := NULL;
ausgabe
VARCHAR2(50) := NULL;
-- Nur Tabellennamen auslesen
CURSOR c_tab_name IS SELECT DISTINCT upper(tab_name) tab_name from zuordnung
ORDER BY tab_name;
-- Sensoren aus Zuordnung selektieren.
CURSOR c_sensor IS SELECT DISTINCT upper(sensor) sensor FROM zuordnung
WHERE upper(tmp_tab_name) = upper(tab_name)
ORDER BY sensor ASC;
------------------------------------------------------------------------------------------BEGIN
DBMS_OUTPUT.ENABLE(1000000);
SELECT strich INTO hk FROM hochkomma; -- das Zeichen ' in hk speichern.
DBMS_OUTPUT.PUT_LINE('-- Datei erstellt am '||to_char(SYSDATE,'dd.mm.yyyy hh24:mi'));
DBMS_OUTPUT.PUT_LINE('---------------------------------------------------------------------------------------');
DBMS_OUTPUT.PUT_LINE('-- Datei: TAGMONATJAHR.SQL');
DBMS_OUTPUT.PUT_LINE('-- Erstellt: '||to_char(SYSDATE,'dd.mm.yyyy hh24:mi'));
DBMS_OUTPUT.PUT_LINE('-- Aus Datei SPOOL_TAGMONATJAHR.SQL');
DBMS_OUTPUT.PUT_LINE('-- Beschreibung: ');
DBMS_OUTPUT.PUT_LINE('-- Füllen der Tabellen ..._TAG_TAB, ..._MONAT_TAB, ..._JAHR_TAB
');
DBMS_OUTPUT.PUT_LINE('---------------------------------------------------------------------------------------');
DBMS_OUTPUT.PUT_LINE('-******************************************************************--');
DBMS_OUTPUT.PUT_LINE('-- Leeren der Tabellen ..._TAG_TAB, ..._MONAT_TAB, ..._JAHR_TAB');
FOR n IN c_tab_name LOOP
DBMS_OUTPUT.PUT_LINE('TRUNCATE TABLE '||n.tab_name||'_TAG_TAB;');
DBMS_OUTPUT.PUT_LINE('COMMIT;');
END LOOP;
128
Anhang
FOR n IN c_tab_name LOOP
DBMS_OUTPUT.PUT_LINE('TRUNCATE TABLE '||n.tab_name||'_MONAT_TAB;');
DBMS_OUTPUT.PUT_LINE('COMMIT;');
END LOOP;
FOR n IN c_tab_name LOOP
DBMS_OUTPUT.PUT_LINE('TRUNCATE TABLE '||n.tab_name||'_JAHR_TAB;');
DBMS_OUTPUT.PUT_LINE('COMMIT;');
END LOOP;
DBMS_OUTPUT.PUT_LINE('-******************************************************************--');
DBMS_OUTPUT.PUT_LINE('-Füllen der Tabellen ..._TAG_TAB');
DBMS_OUTPUT.PUT_LINE('-Es müssen mindestens '||min_anz_rohkorrwerte_pro_tag||' von
240 Tagesdaten vorhanden sein.');
FOR n IN c_tab_name LOOP
tmp_tab_name := n.tab_name;
DBMS_OUTPUT.PUT_LINE('-----------------------------------------------');
DBMS_OUTPUT.PUT_LINE('INSERT INTO '|| tmp_tab_name ||'_TAG_TAB (datum, r_nr, ');
FOR z IN c_sensor LOOP -- Restliche Zeilen aus ZUORDNUNG in der 'tmp_tab_name'
steht.
IF spalte_in_tabelle(tmp_tab_name||'_TAG_TAB',z.sensor) = TRUE THEN
SELECT MAX(sensor) INTO letzterSensor FROM zuordnung
WHERE upper(tmp_tab_name) = upper(tab_name) ORDER BY sensor;
IF UPPER(z.sensor) = UPPER(letzterSensor) THEN
ausgabe := z.sensor || ')';
ELSE
ausgabe := z.sensor || ', ';
END IF;
DBMS_OUTPUT.PUT_LINE(ausgabe);
END IF;
END LOOP;
DBMS_OUTPUT.PUT_LINE('SELECT TRUNC(r.datum,'||hk||'DD'||hk||') ,r.r_nr , ');
FOR z IN c_sensor LOOP -- Restliche Zeilen aus ZUORDNUNG in der 'tmp_tab_name'
steht.
IF spalte_in_tabelle(tmp_tab_name||'_TAG_TAB',z.sensor) = TRUE THEN
IF UPPER(z.sensor) = UPPER(letzterSensor) THEN
ausgabe := 'avg(r.'||z.sensor||')';
ELSE
ausgabe := 'avg(r.'||z.sensor||'),';
END IF;
DBMS_OUTPUT.PUT_LINE(ausgabe);
END IF;
END LOOP;
DBMS_OUTPUT.PUT_LINE('FROM '||tmp_tab_name||'_ROHKORR_TAB r');
DBMS_OUTPUT.PUT_LINE('GROUP BY TRUNC(r.datum,'||hk||'DD'||hk||') ,r.r_nr');
DBMS_OUTPUT.PUT_LINE('HAVING count(TRUNC(r.datum,'||hk||'DD'||hk||')) >=
'||min_anz_rohkorrwerte_pro_tag||';');
DBMS_OUTPUT.PUT_LINE('COMMIT;');
DBMS_OUTPUT.PUT_LINE('-----------------------------------------------');
END LOOP;
---------------------------------------------------------------------------------------DBMS_OUTPUT.PUT_LINE('-******************************************************************--');
DBMS_OUTPUT.PUT_LINE('-Füllen der Tabellen ..._MONAT_TAB');
DBMS_OUTPUT.PUT_LINE('-- Es müssen mindestens '||min_anz_rohkorrwerte_pro_tag||'
Tageswerte pro Tag vorhanden sein.');
FOR n IN c_tab_name LOOP
tmp_tab_name := n.tab_name;
DBMS_OUTPUT.PUT_LINE('-----------------------------------------------');
DBMS_OUTPUT.PUT_LINE('INSERT INTO '|| tmp_tab_name ||'_MONAT_TAB (datum, r_nr,
');
FOR z IN c_sensor LOOP -- Restliche Zeilen aus ZUORDNUNG in der 'tmp_tab_name'
steht.
IF spalte_in_tabelle(tmp_tab_name||'_MONAT_TAB',z.sensor) = TRUE THEN
SELECT MAX(sensor) INTO letzterSensor FROM zuordnung
WHERE upper(tmp_tab_name) = upper(tab_name) ORDER BY sensor;
IF UPPER(z.sensor) = UPPER(letzterSensor) THEN
ausgabe := z.sensor || ')';
ELSE
ausgabe := z.sensor || ', ';
END IF;
DBMS_OUTPUT.PUT_LINE(ausgabe);
129
Anhang
END IF;
END LOOP;
DBMS_OUTPUT.PUT_LINE('SELECT TRUNC(r.datum,'||hk||'MM'||hk||') ,r.r_nr , ');
FOR z IN c_sensor LOOP -- Restliche Zeilen aus ZUORDNUNG in der 'tmp_tab_name'
steht.
IF spalte_in_tabelle(tmp_tab_name||'_MONAT_TAB',z.sensor) = TRUE THEN
IF UPPER(z.sensor) = UPPER(letzterSensor) THEN
ausgabe := 'avg(r.'||z.sensor||')';
ELSE
ausgabe := 'avg(r.'||z.sensor||'),';
END IF;
DBMS_OUTPUT.PUT_LINE(ausgabe);
END IF;
END LOOP;
DBMS_OUTPUT.PUT_LINE('FROM '||tmp_tab_name||'_TAG_TAB r');
DBMS_OUTPUT.PUT_LINE('GROUP BY TRUNC(r.datum,'||hk||'MM'||hk||') ,r.r_nr;');
DBMS_OUTPUT.PUT_LINE('COMMIT;');
DBMS_OUTPUT.PUT_LINE('-----------------------------------------------');
END LOOP;
---------------------------------------------------------------------------------------DBMS_OUTPUT.PUT_LINE('-******************************************************************--');
DBMS_OUTPUT.PUT_LINE('-Füllen der Tabellen ..._JAHR_TAB');
DBMS_OUTPUT.PUT_LINE('-- Es müssen mindestens '||min_anz_rohkorrwerte_pro_tag||'
Monatswerte pro Jahr vorhanden sein.');
FOR n IN c_tab_name LOOP
tmp_tab_name := n.tab_name;
DBMS_OUTPUT.PUT_LINE('-----------------------------------------------');
DBMS_OUTPUT.PUT_LINE('INSERT INTO '|| tmp_tab_name ||'_JAHR_TAB (datum, r_nr, ');
FOR z IN c_sensor LOOP -- Restliche Zeilen aus ZUORDNUNG in der 'tmp_tab_name'
steht.
IF spalte_in_tabelle(tmp_tab_name||'_JAHR_TAB',z.sensor) = TRUE THEN
SELECT MAX(sensor) INTO letzterSensor FROM zuordnung
WHERE upper(tmp_tab_name) = upper(tab_name) ORDER BY sensor;
IF UPPER(z.sensor) = UPPER(letzterSensor) THEN
ausgabe := z.sensor || ')';
ELSE
ausgabe := z.sensor || ', ';
END IF;
DBMS_OUTPUT.PUT_LINE(ausgabe);
END IF;
END LOOP;
DBMS_OUTPUT.PUT_LINE('SELECT TRUNC(r.datum,'||hk||'YY'||hk||') ,r.r_nr , ');
FOR z IN c_sensor LOOP -- Restliche Zeilen aus ZUORDNUNG in der 'tmp_tab_name'
steht.
IF spalte_in_tabelle(tmp_tab_name||'_JAHR_TAB',z.sensor) = TRUE THEN
IF UPPER(z.sensor) = UPPER(letzterSensor) THEN
ausgabe := 'avg(r.'||z.sensor||')';
ELSE
ausgabe := 'avg(r.'||z.sensor||'),';
END IF;
DBMS_OUTPUT.PUT_LINE(ausgabe);
END IF;
END LOOP;
DBMS_OUTPUT.PUT_LINE('FROM '||tmp_tab_name||'_MONAT_TAB r');
DBMS_OUTPUT.PUT_LINE('GROUP BY TRUNC(r.datum,'||hk||'YY'||hk||') ,r.r_nr;');
DBMS_OUTPUT.PUT_LINE('COMMIT;');
DBMS_OUTPUT.PUT_LINE('-----------------------------------------------');
END LOOP;
---------------------------------------------------------------------------------------EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('-- Fehler: Exception OTHERS in spool_FEHL_INV_TAB.sql.');
IF c_sensor%ISOPEN THEN
CLOSE c_sensor;
END IF;
IF c_tab_name%ISOPEN THEN
CLOSE c_tab_name;
END IF;
END;
/
COMMIT;
SET FEEDBACK ON;
SPOOL OFF;
130
Anhang
Skript 31: UMGEBUNGSTABELLEN.SQL
----------------------------------------------------------------------------------------- Datei: UMGEBUNGSTABELLEN.SQL
-- Erstellt: 06.10.2000
-- Beschreibung:
-- Anlegen verschiedener Tabellen für die ZUB-Datenbank.
-- Die Tabellen werden im TABLESPACE d:\zub_db\db_indx\Messdaten.ora.
-- die Indexe und Constraints unter d:\zub_db\db_indx\Indxzub.ora angelegt
-- Änderungen:
------------------------------------------------------------------------------------------- Beschreibung siehe Skript ZUORDNUNG.SQL
DROP TABLE ZUORDNUNG;
COMMIT;
CREATE TABLE ZUORDNUNG (
HW_ADR VARCHAR2(10),
R_NR
VARCHAR2(5),
SENSOR VARCHAR2(20),
FORMAT VARCHAR2(1) NOT NULL,
TAB_NAME
VARCHAR2(30),
MINWERT NUMBER(6,2) NOT NULL,
MAXWERT NUMBER(6,2) NOT NULL)
TABLESPACE ZUB_DATEN
STORAGE (
INITIAL 200K
NEXT
50K
MINEXTENTS 1
MAXEXTENTS 300
)
PARALLEL (DEGREE 5)
/
COMMIT;
ALTER TABLE ZUORDNUNG ADD CONSTRAINT pkZuordnung PRIMARY KEY (HW_ADR, R_NR, SENSOR,
TAB_NAME)
USING INDEX TABLESPACE INDX
STORAGE (
INITIAL 100K
NEXT
50K
MINEXTENTS 1
MAXEXTENTS 300
)
ENABLE
/
COMMIT;
-- wird von der Funktion zuordnung_pruefen gefüllt mit einem Fehlerflag.
-- Ist es ungleich 0, werden keine Datentabellen angelegt oder verändert.
-- Die Tabelle wird von db_tabellen_anlegen.sql abgefragt.
DROP TABLE ZUORDNUNG_GEPRUEFT;
COMMIT;
CREATE TABLE ZUORDNUNG_GEPRUEFT (
FLAG NUMBER(2)
)
TABLESPACE ZUB_DATEN
/
COMMIT;
-- Hier werden die Anhaengsel (Suffix) der verschiedenen Datenbanktabellen
-- festgelegt.
DROP TABLE Anhang;
COMMIT;
CREATE TABLE Anhang (
anhang VARCHAR2(30),
CONSTRAINT pkAnhang PRIMARY KEY (Anhang) USING INDEX TABLESPACE INDX ENABLE
)
TABLESPACE ZUB_DATEN
/
131
Anhang
COMMIT;
-- Diese Tabelle gibt an, wann ZEITTAB mit weiteren Datumswerten gefüllt wird.
DROP TABLE Zeittab_fuellen;
COMMIT;
CREATE TABLE Zeittab_fuellen(
Jahr
DATE NOT NULL
)
TABLESPACE ZUB_DATEN
/
COMMIT;
-- Hier wird eine Datumstabelle mit 6 min. Intervallen als Referenzdatums angelegt.
-- Siehe Beschreibung zeitab.sql
DROP TABLE zeittab;
COMMIT;
CREATE TABLE zeittab (
ref_zeit
DATE,
CONSTRAINT pkZeittab PRIMARY KEY (ref_zeit) USING INDEX TABLESPACE INDX
STORAGE (
INITIAL 10M
NEXT
5M
MINEXTENTS 1
MAXEXTENTS 300
)
ENABLE )
TABLESPACE ZUB_DATEN
STORAGE (
INITIAL 10M
NEXT
5M
MINEXTENTS 1
MAXEXTENTS 300
)
PARALLEL (DEGREE 5)
/
COMMIT;
-- Die Fehler aus den Tabellen ..._FEHL_AUTO_ANZEIGE_TAB werden mit einem
-- Fehlercode versehen, der hier angelegt ist.
DROP TABLE Fehler_Code;
COMMIT;
CREATE TABLE Fehler_Code(
Nr
NUMBER(2),
Fehlerart
VARCHAR2(300),
CONSTRAINT pkFehler_Code PRIMARY KEY (Nr,Fehlerart) USING INDEX TABLESPACE INDX ENABLE
)
TABLESPACE ZUB_DATEN
/
COMMIT;
-- Es wird nur ein Hochkomma ' eingetragen.
-- Dieses wird für DBMS_SQL verwendet, um das Parsen einiger Befehle zu ermöglichen.
DROP TABLE Hochkomma;
COMMIT;
CREATE TABLE Hochkomma (
strich CHAR(1) NOT NULL
)
TABLESPACE ZUB_DATEN
/
COMMIT;
-- ZUB_DATEN_TMP_TAB anlegen, damit Prozeduren und Funktionen angelegt werden können.
-- In dieser Tabelle befinden sich die aktuellen Messdaten von der Leittechnik des
-- ZUB.
-- Das Skript prozedur_zub_daten_tmp_tab_anlegen.sql fügt die notwendigen Spalten an,
-- die in ZUORDNUNG referenziert sind.
DROP TABLE ZUB_DATEN_TMP_TAB;
CREATE TABLE ZUB_DATEN_TMP_TAB (
datum VARCHAR2(30),
uhrzeit VARCHAR2(30),
CONSTRAINT pkZUB_DATEN_TMP_TAB PRIMARY KEY (datum, uhrzeit) USING INDEX TABLESPACE INDX
ENABLE
)
TABLESPACE ZUB_DATEN
PARALLEL (DEGREE 5)
/
COMMIT;
132
Anhang
-- Tabelle wird in Forms gefüllt. Hieraus werden die Inversen Fehlertabellen erstellt.
DROP TABLE FEHL_TAB;
COMMIT;
CREATE TABLE FEHL_TAB (
lfd_nr
NUMBER(6),
tab_name
VARCHAR2(30) NOT NULL,
sensor
VARCHAR2(30) NOT NULL,
r_nr
VARCHAR2(5) NOT NULL,
fehler_nr
NUMBER(2)
NOT NULL,
anf_dat
DATE
NOT NULL,
end_dat
DATE,
Bearbeiter
VARCHAR2(30) NOT NULL,
Bemerkung
VARCHAR2(200))
TABLESPACE ZUB_DATEN
STORAGE (INITIAL 10M
NEXT
10M
MINEXTENTS 1
MAXEXTENTS 100)
PARALLEL (DEGREE 5)
/
COMMIT;
ALTER TABLE FEHL_TAB ADD CONSTRAINT pk_FEHL_TAB PRIMARY KEY (lfd_nr)
USING INDEX TABLESPACE INDX
STORAGE (INITIAL 5M
NEXT
5M
MINEXTENTS 1
MAXEXTENTS 200)
ENABLE
/
COMMIT;
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Tabelle für FORMS, um Änderungen zu verwerfen.
-- Erst wenn Änderungen in Forms übernommen wurden,
-- wird diese Tabelle aktualisiert.
DROP TABLE ZUORDNUNG_ALT;
COMMIT;
CREATE TABLE ZUORDNUNG_ALT
TABLESPACE ZUB_DATEN
STORAGE (
INITIAL 200K
NEXT
50K
MINEXTENTS 1
MAXEXTENTS 300
)
PARALLEL (DEGREE 5)
AS SELECT * FROM ZUORDNUNG;
COMMIT;
ALTER TABLE ZUORDNUNG_ALT ADD PRIMARY KEY (HW_ADR, R_NR, SENSOR, TAB_NAME)
USING INDEX TABLESPACE INDX
STORAGE (INITIAL
100K
NEXT
50K
MINEXTENTS
1
MAXEXTENTS
300)
ENABLE
/
COMMIT;
-- Tabellen für FORMS
DROP TABLE ZUORDNUNG_GEAENDERT;
COMMIT;
CREATE TABLE ZUORDNUNG_GEAENDERT
TABLESPACE ZUB_DATEN
STORAGE (
INITIAL 200K
NEXT
50K
MINEXTENTS 1
MAXEXTENTS 300
)
PARALLEL (DEGREE 5)
AS SELECT * FROM ZUORDNUNG;
COMMIT;
ALTER TABLE ZUORDNUNG_GEAENDERT ADD PRIMARY KEY (HW_ADR, R_NR, SENSOR, TAB_NAME)
USING INDEX TABLESPACE INDX
STORAGE (INITIAL
100K
NEXT
50K
133
Anhang
MINEXTENTS
MAXEXTENTS
1
300)
ENABLE
/
COMMIT;
-- Diese Tabelle wird 1:1 mit den Werten aus ZUORDNUNG gefüllt. In Forms
-- kann man hier den Messbereich (Minwert und Maxwert) bearbeiten.
-- Erst beim Übernehmen des neuen Messbereichs werden die Werte in ZUORDNUNG
-- eingetragen.
DROP TABLE ZUORDNUNG_MESS;
COMMIT;
CREATE TABLE ZUORDNUNG_MESS
TABLESPACE ZUB_DATEN
STORAGE (
INITIAL 200K
NEXT
50K
MINEXTENTS 1
MAXEXTENTS 300
)
PARALLEL (DEGREE 5)
AS SELECT * FROM ZUORDNUNG;
COMMIT;
ALTER TABLE ZUORDNUNG_MESS ADD PRIMARY KEY (HW_ADR, R_NR, SENSOR, TAB_NAME)
USING INDEX TABLESPACE INDX
STORAGE (INITIAL
100K
NEXT
50K
MINEXTENTS
1
MAXEXTENTS
300)
ENABLE
/
COMMIT;
-- Die Befehle, die ausgeführt wurden um den Messbereich zu ändern,
-- werden in diese Tabelle eingetragen und in Forms angezeigt.
DROP TABLE MESSBEREICH_GEAENDERT_TAB;
COMMIT;
CREATE TABLE MESSBEREICH_GEAENDERT_TAB (
nr
NUMBER(6),
Anweisung
VARCHAR2(300)
)
TABLESPACE ZUB_DATEN
STORAGE (INITIAL 500K
NEXT
500K
MINEXTENTS 1
MAXEXTENTS 50)
PARALLEL (DEGREE 5)
/
COMMIT;
ALTER TABLE MESSBEREICH_GEAENDERT_TAB ADD CONSTRAINT pk_MESS_GEAENDERT PRIMARY KEY (nr)
USING INDEX TABLESPACE INDX
STORAGE (INITIAL 100K
NEXT
100K
MINEXTENTS 1
MAXEXTENTS 50)
ENABLE
/
COMMIT;
-- Diese Tabelle wird gefüllt durch das Skript d:\zub_db\forms\fehl_auto_anzeige.sql
-- Fehler die noch nicht geprüft gelten (Check_Flag ist NULL) werden hier eingetragen
-- und angezeigt.
-- Die angezeigten Fehler, werden nach Sichtung in die Tabelle FEHL_TAB übernommen.
DROP TABLE FEHL_AUTO_ANZEIGE_TAB;
COMMIT;
CREATE TABLE FEHL_AUTO_ANZEIGE_TAB (
tab_name
VARCHAR2(30),
sensor VARCHAR2(30),
r_nr
VARCHAR2(5),
anz_fehler
NUMBER(6)
NOT NULL,
anf_dat
DATE
NOT NULL,
end_dat
DATE
NOT NULL,
fehler_nr
NUMBER(2)
NOT NULL,
check_flag
NUMBER(1)
DEFAULT NULL,
Bearbeiter
VARCHAR2(30)
DEFAULT NULL)
TABLESPACE ZUB_DATEN
STORAGE (INITIAL 10M
134
Anhang
NEXT
10M
MINEXTENTS 1
MAXEXTENTS 50)
PARALLEL (DEGREE 5)
/
COMMIT;
ALTER TABLE FEHL_AUTO_ANZEIGE_TAB ADD CONSTRAINT pk_FEHL_AUTO_ANZEIGE_TAB PRIMARY KEY
(tab_name, sensor, r_nr, anf_dat, fehler_nr)
USING INDEX TABLESPACE INDX
STORAGE (INITIAL 5M
NEXT
5M
MINEXTENTS 1
MAXEXTENTS 50)
ENABLE
/
COMMIT;
-- Bevor die Werte Checked aus FEHL_AUTO_ANZEIGE in die Tabellen
<Tab_Name>_FEHL_AUTO_TAB
-- eingetragen werden, wird geprüft, ob Fehler die noch nicht markiert wurden eventuell
-- zusätzlich betroffen sind. Dann wird die Tabelle mit den gefundenen Überschneidungen
gefüllt
-- und angezeigt.
DROP TABLE MOEGLICHE_UEBERSCHNEIDUNG_TAB;
COMMIT;
CREATE TABLE MOEGLICHE_UEBERSCHNEIDUNG_TAB
TABLESPACE ZUB_DATEN
STORAGE (INITIAL 50K
NEXT
50K
MINEXTENTS 1
MAXEXTENTS 50)
PARALLEL (DEGREE 5)
AS SELECT * FROM FEHL_AUTO_ANZEIGE_TAB
/
COMMIT;
TRUNCATE TABLE MOEGLICHE_UEBERSCHNEIDUNG_TAB;
COMMIT;
ALTER TABLE MOEGLICHE_UEBERSCHNEIDUNG_TAB ADD nr NUMBER(4) NOT NULL;
COMMIT;
ALTER TABLE MOEGLICHE_UEBERSCHNEIDUNG_TAB ADD CONSTRAINT pk_UEBERSCHNEIDUNG PRIMARY KEY
(nr)
USING INDEX TABLESPACE INDX
STORAGE (INITIAL 10K
NEXT
10K
MINEXTENTS 1
MAXEXTENTS 50)
ENABLE
/
COMMIT;
135
Anhang
Skript 32: ZEITTAB.SQL
----------------------------------------------------------------------------------------- Datei: ZEITTAB.SQL
-- Erstellt: 07.11.2000
-- Beschreibung:
-- Erstellen einer Zeittabelle für das laufende Jahr..
-- Sie enthält die zulässigen 6 min. Intervalle als Referenzzeit.
-- Mit ihr werden ungenaue Datums verbessert und fehlende Datums abgefragt.
-- Bei anlegen einer neuen Tabelle, wird dieses Jahr ab heute vor 8 Wochen eingetragen.
-- Ansonsten startet das Skript immer beim ersten Aufruf der Datenbank in einem neuen
Jahr.
-- Bem:
-- Änderungen:
-----------------------------------------------------------------------------------------SET SERVEROUTPUT ON
-- Tabelle mit 6 min Zeitintervallen anlegen.
DECLARE
i_voll
NUMBER(6) := 0; -- Test, ob überhaupt Werte in den Tabellen stehen.
sechs_min
NUMBER := 6/(24*60);
d_anfang
DATE;
d_lauf
DATE;
d_ende DATE;
d_fuellen
DATE;
---------------------------------------------------------------------------------------------------------BEGIN
DBMS_OUTPUT.ENABLE(1000000);
--SAVEPOINT datum_raumnummer_einfuegen;
DBMS_OUTPUT.PUT_LINE('-----------------------------------------------');
DBMS_OUTPUT.PUT_LINE('-- SQL DATEI: ZEITTAB.SQL.');
SELECT jahr INTO d_fuellen FROM Zeittab_fuellen;
-- DBMS_OUTPUT.PUT_LINE('zeittab, jahr: ' || d_fuellen);
-- Zählen der Einträge in Zeittab
SELECT COUNT(ref_zeit) INTO i_voll FROM zeittab;
-- DBMS_OUTPUT.PUT_LINE('Die Zeittabelle enthält '|| i_voll || ' Datumswerte.');
-- Ein neues Jahr hat begonnen oder die Zeittabelle ist leer !
IF (d_fuellen < TRUNC(SYSDATE,'YYYY')) OR (i_voll = 0) THEN
-- Anfang ist 00:00 des heutigen Tages
IF i_voll = 0 THEN -- Datenbank noch nicht in Betrieb. Anfang ist heute.
SELECT TRUNC(SYSDATE,'DD')-8*7 INTO d_anfang FROM DUAL;
-DBMS_OUTPUT.PUT_LINE('-- Die Zeittabelle ist leer. Beginn der
Datumeinträge beginnt heute vor 8 Wochen.');
ELSE -- Es gibt schon Einträge, also Beginn ist Anfang des Jahres
SELECT TRUNC(SYSDATE,'YYYY') INTO d_anfang FROM DUAL;
-DBMS_OUTPUT.PUT_LINE('-- Die Zeittabelle ist gefüllt. Es wird ein
komplettes Jahr eingetragen.');
END IF;
d_lauf := d_anfang;
-- Ende ist 31.12 23:54
SELECT TRUNC(TRUNC(SYSDATE,'YYYY') + 370,'YYYY')-sechs_min INTO d_ende FROM DUAL;
-DBMS_OUTPUT.PUT_LINE('Anfang: '|| to_char(d_anfang,'dd.mm.yy hh24:mi') ||' Ende:
'|| to_char(d_ende,'dd.mm.yy hh24:mi'));
-TESTFUNKTION
-d_anfang := to_date('20.11.2000 00:00','dd.mm.yyyy hh24:mi');
-d_lauf := d_anfang;
-d_ende := to_date('02.02.2001 00:12','dd.mm.yyyy hh24:mi');
SET TRANSACTION USE ROLLBACK SEGMENT RB_BIG;
<<innen>>
LOOP
EXIT WHEN d_lauf > d_ende;
136
Anhang
INSERT INTO zeittab (ref_zeit) VALUES (d_lauf);
d_lauf := d_lauf + sechs_min;
END LOOP innen;
COMMIT;
DBMS_OUTPUT.PUT_LINE('-- Zeittabelle wurde aktualisiert von '||
to_char(d_anfang,'dd.mm.yyyy hh24:mi') ||' bis '||to_char(d_ende,'dd.mm.yyyy
hh24:mi')||'.');
DBMS_OUTPUT.PUT_LINE('-----------------------------------------------');
ELSE
SELECT max(ref_zeit) INTO d_fuellen FROM zeittab;
DBMS_OUTPUT.PUT_LINE('-- Zeittabelle ist aktuell bis '||
to_char(d_fuellen,'dd.mm.yyyy hh24:mi'));
DBMS_OUTPUT.PUT_LINE('-----------------------------------------------');
END IF;
-- Updaten der Jahreswertberechnung.
UPDATE Zeittab_fuellen SET jahr = TRUNC(SYSDATE,'YYYY');
COMMIT;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('EXCEPTION: OTHERS in Zeittab.sql');
RAISE;
END;
/
COMMIT;
137
Anhang
Skript 33: ZUORDNUNG.SQL
----------------------------------------------------------------------------------------- Datei: ZUORDNUNG.SQL
-- Erstellt: 06.11.2000
-- Beschreibung:
-- Die Tabelle ZURDNUNG ist die Basis der gesamten Datenbank.
-- Sie enthält alle Verknüpfungen zwischen den Messdaten, die vom ZUB verfügbar sind
-- und den Datenbanktabellen, in denen die Messdaten geordnet werden.
-- Sie enthält:
-- Hw_Adr
Hardwareadresse: Bezeichnet die Spaltennamen aus
-der Tabelle ZUB_DATEN_TMP_TAB (dort werden die Rohdaten abgelegt)
-- R_Nr
Die Raumnummer in der der Messaufnehmer installiert ist
-- Sensor
Die Art des Sensors (z.B. T_OBERFL (Oberflächentemperatursensor)).
-Bezeichnet die Spaltennamen in den Datenbanktabellen.
-- Format
Beschreibung ob es ein Binärwert oder Analogwert ist (nur 'A' oder
'B')
-- Tab_Name Tabellenname für die Datenbank (wird später angelegt)
-In der Tabelle <Tab_Name> werden jeweils die Spalten <Sensor>
angelegt.
-Die Tabellennamen entsprechen den Informationsschwerpunkten aus
der
-entsprechenden Liste.
-- Minwert
Minimaler erlaubter Wert des Messbereiches, sonst wird der Messwert
als
-fehlerhaft markiert.
-- Maxwert
Maximaler erlaubter Wert des Messbereiches, s.o.
-- Die Verknüpfung besteht hauptsächlich aus den Spalten Hw_Adr, Tab_Name und Sensor.
-- <Hw_Adr> bezeichnet die Spaltennamen aus der täglichen Messdatentabelle
-- ZUB_DATEN_TMP_TAB.
-- Aus der Beziehung <Tab_Name> und <Sensor> kann die <Hw_Adr> eindeutig einer Tabelle
aus
-- der Datenbank mit der Spalte <Sensor> zugeordnet werden.
-- Bemerkung: <BeipielWert> wird gelesen als "Inhalt der Spalte BeispielWert".
-- Änderungen:
-----------------------------------------------------------------------------------------TRUNCATE TABLE ZUORDNUNG;
-- Initialisierung der Tabelle ZUORDNUNG
-- Hier sollten neue Sensoren eingetragen werden und alte gelöscht werden,
-- um bei einer neuen Initialisierung der Datenbank den aktuellen Stand
-- zur Verfügung zu haben.
INSERT INTO ZUORDNUNG VALUES ('H00100', '0_01',
'T_I',
10,50);
INSERT INTO ZUORDNUNG VALUES ('H00101', '0_01',
'VENTIL_A',
INSERT INTO ZUORDNUNG VALUES ('H00102', '0_01',
'VENTIL_B',
INSERT INTO ZUORDNUNG VALUES ('H00103', '0_01',
'VENTIL_C',
INSERT INTO ZUORDNUNG VALUES ('H00104', '0_01',
'T_OBERFL0',
10,50);
INSERT INTO ZUORDNUNG VALUES ('H00105', '0_01',
'T_OBERFL1',
10,50);
INSERT INTO ZUORDNUNG VALUES ('H00106', '1_02',
'T_I',
10,50);
INSERT INTO ZUORDNUNG VALUES ('H00107', '1_02',
'VENTIL_A',
INSERT INTO ZUORDNUNG VALUES ('H00108', '1_02',
'VENTIL_B',
INSERT INTO ZUORDNUNG VALUES ('H00109', '1_02',
'VENTIL_C',
INSERT INTO ZUORDNUNG VALUES ('H00110', '1_02',
'T_OBERFL0',
10,50);
INSERT INTO ZUORDNUNG VALUES ('H00111', '1_02',
'T_OBERFL1',
10,50);
INSERT INTO ZUORDNUNG VALUES ('H00112', '1_02',
'T_OBERFL0',
10,50);
INSERT INTO ZUORDNUNG VALUES ('H00113', '1_02',
'T_OBERFL1',
10,50);
INSERT INTO ZUORDNUNG VALUES ('H00114', '1_02',
'T_OBERFL2',
10,50);
select count(*) AnzahlWerteinZuordnung from zuordnung;
select * from zuordnung order by tab_name, sensor, r_nr, hw_adr;
'A',
'STDR',-
'B',
'B',
'B',
'A',
'STDR',0,1);
'STDR',0,1);
'STDR',0,1);
'STDR',-
'A',
'STDR',-
'A',
'STDR',-
'B',
'B',
'B',
'A',
'STDR',0,1);
'STDR',0,1);
'STDR',0,1);
'STDR',-
'A',
'STDR',-
'A',
'MESSR',-
'A',
'MESSR',-
'A',
'MESSR',-
138
Anhang
9.2 CD-ROM
9.2.1 Diese Ausarbeitung
9.2.2 PL/SQL Skripte, Eingabemaske unter ORACLE Forms
139