Datenbanksysteme Datenbanksysteme I I

Datenbanksysteme Datenbanksysteme I I

„ Dipl.-Inf. Michael Wilhelm

„ Hochschule Harz

„ FB Automatisierung und Informatik

„ mwilhelm@hs-harz.de

„ Raum 2.202

„ Tel. 03943 / 659 338

Inhalt

1. Grundlegende Begriffe der Datenbanktechnologie 2. Datenbankentwurf / Datenmodelle

3. ER-Modell / ER-Diagramm 4. SQL-Sprache

5. Normalisierung

6. SQL-Erweiterungen

7. PL-SQL

Datenbanksprache PL/SQL

Definition: Program-Language SQL

„ Einleitung

„ Blockübersicht

„ Variablendeklaration

„ IF / THEN / CASE

„ Schleifen

„ SQL-Anweisungen

„ Datenbankcursor

PL/SQL Block

PL/SQL Engine

Oracle Server

Procedural Statement

Executor PL/SQL

SQL

SQL Statement Executor PL/SQL

Block

PL/SQL Umgebung

Anwendung Anwendung

Vorteile von PL/SQL

„ Integration

•

Stored Proc.

•

Trigger

•

Forms

•

Shared Libraries

Anwendung

Anwendung Andere DBMS Andere DBMS

Anwendung

Anwendung Oracle mit

PL/SQL Oracle mit

PL/SQL

SQLSQL SQLSQL

SQLSQL SQLSQL

SQL SQL

IF...THEN IF...THEN

SQL SQL ELSE ELSE

SQL SQL END IF;

END IF;

SQL SQL

Vorteile von PL/SQL

„ Verbesserung der Performance

•

Eine Anweisung (Netz)

Anonymer Block Anonymer Anonymer

Block Block

Anwendungs- Trigger Anwendungs Anwendungs- -

Trigger Trigger

Stored Procedure/

Function Stored Stored Procedure Procedure/ /

Function Function

Datenbank- Trigger Datenbank Datenbank- -

Trigger Trigger

Anwendungs- Prozedur/

Funktion Anwendungs Anwendungs- -

Prozedur/

Prozedur/

Funktion Funktion

Package Package Package

DECLARE DECLARE BEGIN

BEGIN

EXCEPTION EXCEPTION END; END;

PL/SQL-Programmkonstrukte

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

Blockübersicht

Eigenschaften von PL/SQL

„ (+) Portabel

„ (+) Verfügbarkeit von Variablen

„ (+) Programmierung mit prozeduralen Kontrollstrukturen

„ (+) Behandlung von Laufzeitfehlern

„ (-) Portierbarkeit zwischen Datenbanken

„ (-) Verbot von gespeicherten Prozeduren

SQL SQL

SQL PL/SQL PL/SQL PL/SQL Anwendungen

Personal Finanzen Fertigung ...

Anwendungen Anwendungen

Personal Personal Finanzen Finanzen Fertigung Fertigung ... ...

Datentabellen Datentabellen Data Dictionary Data Dictionary Oracle

Oracle Datenbank Datenbank

Oracle Discoverer Oracle

Oracle Discoverer Discoverer

Oracle Designer Oracle Designer Oracle Designer

Oracle Developer Oracle

Oracle Developer Developer

SQL* Plus SQL* Plus SQL* Plus Web-basierte

Anwendungen Web- Web -basierte basierte Anwendungen Anwendungen

Multi-tier Anwendungen

Multi

Multi- -tier tier Anwendungen Anwendungen

Die vollständige Oracle-Lösung

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.

„ Vorteile von PL/SQL

• Integration

• Verbesserte Performance

• Portabilität

• Modularität der Programmentwicklung

Blockbeginn mit DECLARE Variablentypen:

„ Variablendefinitionen (Skalar, Zusammengesetzt, Referenz)

„ Cursor

„ benutzerdefinierte Exception

Variablendeklaration

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

Variablendeklaration

DECLARE

v_variable1 VARCHAR2(5);

v_variable2 INTEGER;

BEGIN

SELECT spaltenname1, spaltenname2 INTO v_variable1, v_variable2 FROM tabellen_name;

EXCEPTION

WHEN exception_name THEN ...

END;

Variablendeklaration: Beispiel Oracle

identifier [CONSTANT] datentyp [NOT NULL]

[:= | DEFAULT ausdruck];

identifier [CONSTANT] datentyp [NOT NULL]

[:= | DEFAULT ausdruck];

Declare

v_hiredate DATE;

v_deptno NUMBER(2) NOT NULL := 10;

v_location VARCHAR2(13) := 'Atlanta';

c_comm CONSTANT NUMBER := 1400;

Declare

v_hiredate DATE;

v_deptno NUMBER(2) NOT NULL := 10;

v_location VARCHAR2(13) := 'Atlanta';

c_comm CONSTANT NUMBER := 1400;

Variablendeklaration: Beispiel

Syntax:

Beispiele:

v_job VARCHAR2(9);

v_count BINARY_INTEGER := 0;

v_total_sal NUMBER(9,2) := 0;

v_job VARCHAR2(9);

v_count BINARY_INTEGER := 0;

v_total_sal NUMBER(9,2) := 0;

Anonym Prozedur Funktion

[DECLARE]

BEGIN

--Anweisungen [EXCEPTION]

END;

[DECLARE]

[DECLARE]

BEGIN BEGIN

--Anweisungen -- Anweisungen [EXCEPTION]

[EXCEPTION]

END; END;

PROCEDURE name IS

BEGIN

--Anweisungen [EXCEPTION]

END;

PROCEDURE

PROCEDURE name name IS IS

BEGIN BEGIN

-- -- Anweisungen Anweisungen [EXCEPTION]

[EXCEPTION]

END; END;

FUNCTION name RETURN datentyp IS

BEGIN

--Anweisungen RETURN wert;

[EXCEPTION]

END;

FUNCTION

FUNCTION name name RETURN

RETURN datentyp datentyp IS IS

BEGIN BEGIN

--Anweisungen -- Anweisungen RETURN wert;

RETURN wert;

[EXCEPTION]

[EXCEPTION]

END; END;

Blocktypen

Trigger

Handhabung von Variablen in PL/SQL

„ Variablendeklaration und -initialisierung im Deklarationsteil

„ Zuweisung neuer Variablenwerte im Ausführungsteil

„ Werteübergabe an PL/SQL-Blöcke durch Parameter

„ Betrachtung der Resultate durch Bind- und

Hostvariablen (Oracle-spezifisch)

Deklaration von PL/SQL- Variablen

Richtlinien

„ Beachten der Namenskonventionen (Java, C)

„ Initialisierung von NOT NULL- und CONSTANT- Variablen

„ Initialisierung mit Zuweisungsoperator (:= / =) oder mit Schlüsselwort DEFAULT

„ Empfehlung:

Deklaration einer Variable je Zeile

„ Deklariert eine Variable entsprechend:

z

Einer Datenbankspalte

z

Einer vorher deklarierten Variablen

„ Präfix vor %TYPE:

z

Tabellen- und Spaltenname

z

Name der vorher deklarierten Variable

Das Attribut %TYPE (nur Oracle)

...

v_ename emp.ename%TYPE;

v_balance NUMBER(7,2);

...

v_ename emp.ename%TYPE;

v_balance NUMBER(7,2);

Beispiele:

„ Anweisungen

„ Kommentare

z

/* */

z

--

„ Zeichen- und Datumsliterale in einfachen Anführungszeichen

„ In prozeduralen Anweisungen verfügbar:

• Single Row numerisch

• Single Row Zeichenketten

• Datentypkonvertierung

• Datumsfunktionen

„ In prozeduralen Anweisungen nicht verfügbar:

• DECODE

• Gruppenfunktionen

Block-Abschnitt

Eine Bind-Variable wird in PL/SQL mit einem Doppelpunkt (:) als Namens-Präfix referenziert.

Beispiel :

Bind-Variablen (Oracle)

DECLARE

v_sal emp.sal%TYPE;

BEGIN

SELECT sal INTO v_sal

FROM emp

WHERE empno = 7369;

:salary := v_sal;

END;

DECLARE

v_sal emp.sal%TYPE;

BEGIN

SELECT sal INTO v_sal

FROM emp

WHERE empno = 7369;

:salary := v_sal;

END;

Übersichtlichkeit durch Einrückung jeder Code-Ebene.

Beispiel:

Einrücken von Code

BEGIN

IF x=0 THEN y:=1;

END IF;

END;

BEGIN

IF x=0 THEN y:=1;

END IF;

END;

DECLARE

v_detpno NUMBER(2);

v_location VARCHAR2(13);

BEGIN

SELECT deptno, location INTO v_deptno,

v_location FROM dept

WHERE dname = 'SALES';

...

END;

DECLARE

v_detpno NUMBER(2);

v_location VARCHAR2(13);

BEGIN

SELECT deptno, location INTO v_deptno,

v_location FROM dept

WHERE dname = 'SALES';

...

END;

Definition von Funktionen

Definition:

CREATE OR REPLACE FUNCTION Name( parameter in Typ1) RETURN Typ2 is

BEGIN // Code

RETURN Rückgabewert END;

Aufruf:

select Name(first_name), last_name

Funktionen: 1. Aufgabe

Erstellen Sie eine Funktion in Oracle, F_Upper, die den Parameter in Großbuchstaben umwandeln.

CREATE OR REPLACE FUNCTION F_Upper( name in char) RETURN varchar is

BEGIN

RETURN upper(name);

END;

SELECT F_Upper(first_name), first_name

FROM employees;

Aufruf:

SELECT emp_no, first_name, ( select B

from F_UPPER(e.first_name) )

from employee e;

Funktionen: 1. Aufgabe

Erstellen Sie eine Funktion mit der IBOConsole, F_Upper, die den

Parameter in Großbuchstaben umwandeln.

Funktionen: 2. Aufgabe

Erstellen Sie eine Funktion, F_UpperNamen, die als Parameter den Vornamen und den Nachnamen erhält. Als Ergebnis gibt diese

Funktion den Namen in Großbuchstaben und den Vornamen getrennt durch Komma aus (MÜLLER, Peter)

Create or Replace Function F_UpperNamen( vname in char, nname in char) RETURN varchar is

BEGIN

RETURN (upper(nname) || ', ' || vname);

END;

SELECT F_Upper_trim(first_name, last_name), first_name

FROM employees;

Funktionen: 3. Aufgabe

Erstellen Sie eine Funktion, F_Employee, die einen Datensatz in eine „Visitenkarte“ umwandelt. Parameter: First_name, lastname, Email, Phone_nummer

Create or Replace Function F_Visitenkarte( Vname in Char, Nname in Char, Email in Char, Tel in char)

RETURN varchar is BEGIN

RETURN Nname || ", " || Vname || chr(10) ||

'Tel: ' || Tel || chr(10) || 'Email: ' || Email

END;

Funktionen: 4. Aufgabe

Erstellen Sie eine Funktion, die einen Parameter um eins erhöht.

Mit IboConsole !

COMMIT WORK;

SET AUTODDL OFF;

SET TERM ^ ;

CREATE PROCEDURE INC ( A INTEGER )

RETURNS ( B INTEGER )

AS BEGIN

B = A+1;

suspend;

END ^

SET TERM ; ^ COMMIT WORK;

SET AUTODDL ON;

COMMIT WORK;

SET AUTODDL OFF;

SET TERM ^ ;

CREATE PROCEDURE INC2 ( A INTEGER

)

RETURNS ( B INTEGER )

AS

BEGIN b=a+1;

suspend;

END ^

SET TERM ; ^

Funktionen: 4. Aufgabe

Erstellen Sie eine Funktion, die einen Parameter um eins erhöht.

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

Funktionen: 5. Aufgabe

Erstellen Sie eine Funktion, die einen Parameter um zwei erhöht.

Mit IboConsole und Dialogbasiert ! 1) Aufruf IBOConsole

2) Einloggen

3) Connect Employee.gdb

4) Anklicken Eintrag „Stored Procedures“

5) Rechte Maus, in der rechten Liste, Auswahl „Create“

Definition mit der IBOConsole

Definition mit der IBOConsole: Lösung