Datenbanksysteme I

Datenbanksysteme I
„
Dipl.-Inf. Michael Wilhelm
„
Hochschule Harz
„
FB Automatisierung und Informatik
„
[email protected]
„
Raum 2.202
„
Tel. 03943 / 659 338
FB Automatisierung und Informatik: Datenbanksysteme I
1
Inhalt
1. Grundlegende Begriffe der Datenbanktechnologie
2. Datenbankentwurf / Datenmodelle
3. ER-Modell / ER-Diagramm
4. SQL-Sprache
5. Normalisierung
6. SQL-Erweiterungen
7. Trigger / PL-SQL / Generator
FB Automatisierung und Informatik: Datenbanksysteme I
2
Automatisches Hochzählen
Häufig benötigt man einen ganzzahligen Schlüssel für ein Feld.
Nun kann man mit folgendem Code diesen bestimmen
(Psudocode und PL-SQL):
MaxNr =
SELECT max(MatrNr)
FROM Student
INSERT INTO Student (MatrNr, Name)
VALUES (MaxNr +1, "meier”);
Diese Variante funktioniert leider nicht sicher in einem
Netzwerk! Darum existieren Funktionen, die garantieren, dass
ein Schlüssel immer nur einmalig vergeben wird.
FB Automatisierung und Informatik: Datenbanksysteme I
3
Trigger und Generator
„
Trigger sind SQL-Befehle die bei einer Änderung
der Datenbank aufgerufen werden
„
Sechs Trigger
before
insert
after
insert
before
update
after
update
before
delete
before
delete
„
„
„
„
„
„
FB Automatisierung und Informatik: Datenbanksysteme I
4
Generator
„
Ein Generator benutzt einen internen Zähler
„
Bei der Benutzung wird dieser erhöht
„
Dann wird einem Wert im Datensatz dieser Wert,
unabhängig von "insert" bzw. "delete"
zugewiesen
FB Automatisierung und Informatik: Datenbanksysteme I
5
Generator für Oracle1.fdb
Beispiel:
■
Anlegen eines Mitarbeiters
■
SELECT MAX(empno)
FROM emp
■
■
■
7934
INSERT INTO EMP (empno, eName, deptno)
VALUES ( 7935, 'Bates',10);
FB Automatisierung und Informatik: Datenbanksysteme I
6
Generator für Oracle1.fdb
Beispiel:
■
Anlegen eines Studenten mittels eines Generators
■
CREATE GENERATOR GEN_NUMBER;
SET GENERATOR GEN_NUMBER TO 9999;
drop generator gen_number;
■
■
■
■
■
■
INSERT INTO EMP (empno, Ename, deptno)
VALUES ( GEN_ID(GEN_NUMBER,1), 'Bates', 10);
INSERT INTO EMP (empno, Ename, deptno)
VALUES ( GEN_ID(GEN_NUMBER,1), 'Norman', 10);
FB Automatisierung und Informatik: Datenbanksysteme I
7
Automatisches Einfügen für Oracle1.fdb
■
Anlegen eines Mitarbeiters mittels Generators
■
In Baumelement existiert nun der Generator
„GEN_MATRNR.
■
Nun die Verknüpfung mit dem Trigger:
■
Aufruf der Tabelle emp
Anklicken des Register Trigger:
■
FB Automatisierung und Informatik: Datenbanksysteme I
8
FB Automatisierung und Informatik: Datenbanksysteme I
9
Automatisches Einfügen für Oracle1.fdb
■
Rechte Maustaste, Eintrag „new“
■
Quellcode:
AS
BEGIN
new.empno = gen_id(gen_number, 1);
END
■
■
■
■
FB Automatisierung und Informatik: Datenbanksysteme I
10
FB Automatisierung und Informatik: Datenbanksysteme I
11
Datenbanksprache PL/SQL
Definition: Program-Language SQL
„
„
„
„
„
„
„
„
Einleitung
Blockübersicht
Variablendeklaration
IF / THEN / CASE
Schleifen
SQL-Anweisungen
Datenbankcursor
Exception
FB Automatisierung und Informatik: Datenbanksysteme I
12
PL/SQL Umgebung
PL/SQL Engine
PL/SQL
Block
PL/SQL
Block
PL/SQL
SQL
Procedural
Statement
Executor
SQL Statement Executor
Oracle Server
FB Automatisierung und Informatik: Datenbanksysteme I
13
Vorteile von PL/SQL
„
Integration
•
•
•
•
Stored Proc.
Trigger
Forms
Shared Libraries
Anwendung
Shared
Library
FB Automatisierung und Informatik: Datenbanksysteme I
Oracle Server
14
Vorteile von PL/SQL
„
Verbesserung der Performance
•
Eine Anweisung (Netz)
SQL
Anwendung
Anwendung
SQL
SQL
Andere
Andere DBMS
DBMS
SQL
Anwendung
Anwendung
SQL
IF...THEN
SQL
ELSE
SQL
END IF;
SQL
FB Automatisierung und Informatik: Datenbanksysteme I
Oracle
Oracle mit
mit
PL/SQL
PL/SQL
15
PL/SQL-Programmkonstrukte
Anonymer
Anonymer
Block
Block
Anwendungs
AnwendungsTrigger
Trigger
DECLARE
BEGIN
EXCEPTION
Datenbank
DatenbankTrigger
Trigger
Stored
Stored
Procedure
Procedure//
Function
Function
Anwendungs
AnwendungsProzedur/
Prozedur/
Funktion
Funktion
END;
Package
Package
Objekt
-Typ
Objekt-Typ
FB Automatisierung und Informatik: Datenbanksysteme I
16
Blockübersicht
„
Variablendeklaration
DECLARE
v_salary NUMBER(9,2) ;
„
// VARCHAR(122);
Block: Begin / End
BEGIN
v_salary := sal*12;
END
„
Block: EXCEPTION
EXCEPTION
WHEN exception1 [OR exception2 . . .] THEN
anweisung1;
anweisung2;
WHEN exception1 [OR exception2 . . .] THEN
anweisung2;
END;
FB Automatisierung und Informatik: Datenbanksysteme I
17
Eigenschaften von PL/SQL
„
(+) Portabel
„
(+) Verfügbarkeit von Variablen
„
(+) Programmierung mit prozeduralen Kontrollstrukturen
„
(+) Behandlung von Laufzeitfehlern
„
(-) Portierbarkeit zwischen Datenbanken
„
(-) Verbot von gespeicherten Prozeduren
FB Automatisierung und Informatik: Datenbanksysteme I
18
Zusammenfassung
„
„
„
„
PL/SQL ist eine Erweiterung zu SQL.
Blöcke mit PL/SQL-Code werden an eine PL/SQL-Engine
übergeben und von dieser verarbeitet.
Trigger
• Insert (before/after)
• Delete (before/after)
• Update (before/after)
Vorteile von PL/SQL
•
•
•
•
Integration
Verbesserte Performance
Portabilität
Modularität der Programmentwicklung
FB Automatisierung und Informatik: Datenbanksysteme I
19
Variablendeklaration: Firebird
Variablentypen:
„ Char(n)
„ VARCHAR2(n)
„ NUMBER(n), NUMBER(n.m)
„ DECIMAL(n), DECIMAL(n, m)
„ INTEGER, SMALLINT, LONG, FLOAT
„ BINARY_INTEGER
„ DATE
„
„
„
RAW, LONG RAW
CLOB, BLOB
BFILE
FB Automatisierung und Informatik: Datenbanksysteme I
20
Funktionen: 1. Aufgabe
FB Automatisierung und Informatik: Datenbanksysteme I
22
Funktionen: 2. Aufgabe
Erstellen Sie eine Funktion, die einen Parameter um eins erhöht.
Mit der IBOConsole !
1)
2)
3)
4)
5)
6)
7)
Datenbanktabelle öffnen, z. B. employee.fdb
Eintrag: "stored procedure"
rechte Maustaste, create
Name: INC
Eingangsparameter A Integer
Ausgangsparameter B Integer
Code:
begin
B = A+1;
suspend;
end
FB Automatisierung und Informatik: Datenbanksysteme I
23
Funktionen:
Aufruf der Funktion inc: mit der IBOConsole !
SELECT emp_no, (
select B
from inc(21)
)
from employee e;
SELECT emp_no, (
select B
from inc(e.emp_no)
)
from employee e;
FB Automatisierung und Informatik: Datenbanksysteme I
24
Funktionen: 3. Aufgabe
Erstellen Sie eine Funktion, die einen Parameter um zwei erhöht.
Mit IBOConsole und Dialogbasiert !
1)
2)
3)
4)
5)
Aufruf IBOConsole
Einloggen
Connect Employee.gdb
Anklicken Eintrag „Stored Procedures“
Rechte Maus, in der rechten Liste, Auswahl „Create“
FB Automatisierung und Informatik: Datenbanksysteme I
25
Definition mit der IBOConsole
FB Automatisierung und Informatik: Datenbanksysteme I
26
Definition mit der IBOConsole: Lösung
FB Automatisierung und Informatik: Datenbanksysteme I
27
Eintragen mittels SQL-Befehl
COMMIT WORK;
SET AUTODDL OFF;
SET TERM ^ ;
CREATE PROCEDURE INC2 ( A INTEGER )
RETURNS (
B INTEGER
)
AS
DECLARE VARIABLE x INTEGER;
BEGIN
x=A+2;
B=x;
suspend;
END ^
SET TERM ; ^
COMMIT WORK;
SET AUTODDL ON;
FB Automatisierung und Informatik: Datenbanksysteme I
28
Trigger und Oracle1.fdb
„
„
„
„
„
„
„
„
Aufbau einer Methode, die Gehaltsänderungen
automatisch dokumentiert
Sechs Trigger
before
insert
after
insert
before
update
after
update
before
delete
before
delete
FB Automatisierung und Informatik: Datenbanksysteme I
29
Tabelle Sales_History
CREATE TABLE SAL_HISTORY
(
EMPNO
INTEGER NOT NULL,
CHANGE_DATE TIMESTAMP NOT NULL,
UPDATER_ID
VARCHAR(20) NOT NULL,
OLD_SAL
NUMERIC(7,2) DEFAULT 0 NOT NULL,
PERCENT_CHANGE
DOUBLE PRECISION NOT NULL,
NEW_SALARY
COMPUTED BY
(old_sal + old_sal * percent_change / 100),
PRIMARY KEY (EMPNO, CHANGE_DATE, UPDATER_ID)
);
FB Automatisierung und Informatik: Datenbanksysteme I
30
Trigger-Code
AS
BEGIN
IF (old.SAL <> new.SAL) THEN
INSERT INTO sal_history
(empno, change_date, updater_id, old_sal, percent_change)
VALUES (
old.empno,
'NOW',
user,
old.sal,
(new.sal - old.sal) * 100 / old.sal);
END
FB Automatisierung und Informatik: Datenbanksysteme I
31
FB Automatisierung und Informatik: Datenbanksysteme I
32
Trigger-Code
■
■
■
■
■
■
■
■
■
UPDATE EMP
SET SAL=1700
WHERE ( EMPNO = 7844);
EMPNO
CHANGE_DATE
UPDATER_ID
OLD_SAL
PERCENT_CHANGE
NEW_SALARY
FB Automatisierung und Informatik: Datenbanksysteme I
7844
02.07.2008 20:40:52
SYSDBA
1500
13
1700
33