Datenbanksysteme I
Werbung
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. PL-SQL FB Automatisierung und Informatik: Datenbanksysteme I 2 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 3 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 4 Vorteile von PL/SQL Integration • • • • Stored Proc. Trigger Forms Shared Libraries Anwendung Shared Library FB Automatisierung und Informatik: Datenbanksysteme I Oracle Server 5 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 6 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 7 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 8 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 9 Die vollständige Oracle-Lösung Web -basierte Multi -tier Web-basierte Multi-tier Anwendungen Anwendungen Anwendungen Anwendungen SQL SQL Anwendungen Anwendungen Personal Personal Finanzen Finanzen Fertigung Fertigung ... ... PL/SQL PL/SQL Oracle Datenbank Oracle Oracle Developer Developer Oracle Oracle Discoverer Discoverer Oracle Oracle Designer Designer SQL* SQL* Plus Plus Data Dictionary Datentabellen FB Automatisierung und Informatik: Datenbanksysteme I 10 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 FB Automatisierung und Informatik: Datenbanksysteme I 11 Variablendeklaration Blockbeginn mit DECLARE Variablentypen: Variablendefinitionen (Skalar, Zusammengesetzt, Referenz) Cursor benutzerdefinierte Exception FB Automatisierung und Informatik: Datenbanksysteme I 12 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 FB Automatisierung und Informatik: Datenbanksysteme I 13 Variablendeklaration: Beispiel Oracle DECLARE v_variable1 v_variable2 VARCHAR2(5); INTEGER; BEGIN SELECT spaltenname1, spaltenname2 INTO v_variable1, v_variable2 FROM tabellen_name; EXCEPTION WHEN exception_name THEN ... END; FB Automatisierung und Informatik: Datenbanksysteme I 14 Variablendeklaration: Beispiel Syntax: identifier identifier [CONSTANT] [CONSTANT] datentyp datentyp [NOT [NOT NULL] NULL] [:= [:= || DEFAULT DEFAULT ausdruck]; ausdruck]; Beispiele: Declare Declare v_hiredate v_hiredate v_deptno v_deptno v_location v_location c_comm c_comm v_job v_job v_count v_count v_total_sal v_total_sal v_orderdate v_orderdate c_tax_rate c_tax_rate v_valid v_valid DATE; DATE; NUMBER(2) NUMBER(2) NOT NOT NULL NULL := := 10; 10; VARCHAR2(13) VARCHAR2(13) := := 'Atlanta'; 'Atlanta'; CONSTANT CONSTANT NUMBER NUMBER := := 1400; 1400; VARCHAR2(9); VARCHAR2(9); BINARY_INTEGER BINARY_INTEGER := := 0; 0; NUMBER(9,2) NUMBER(9,2) := := 0; 0; DATE DATE := := SYSDATE SYSDATE ++ 7; 7; CONSTANT CONSTANT NUMBER(3,2) NUMBER(3,2) := := 8.25; 8.25; BOOLEAN BOOLEAN NOT NOT NULL NULL := := TRUE; TRUE; FB Automatisierung und Informatik: Datenbanksysteme I 15 Blocktypen Anonym Prozedur Funktion [DECLARE] [DECLARE] PROCEDURE PROCEDURE name name IS IS BEGIN BEGIN -Anweisungen --Anweisungen BEGIN BEGIN -Anweisungen --Anweisungen [EXCEPTION] [EXCEPTION] [EXCEPTION] [EXCEPTION] FUNCTION FUNCTION name name RETURN RETURN datentyp datentyp IS IS BEGIN BEGIN -Anweisungen --Anweisungen RETURN RETURN wert; wert; [EXCEPTION] [EXCEPTION] END; END; END; END; END; END; Trigger FB Automatisierung und Informatik: Datenbanksysteme I 16 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) FB Automatisierung und Informatik: Datenbanksysteme I 18 Deklaration von PL/SQL- Variablen Richtlinien Beachten der Namenskonventionen (Java, C) Initialisierung von NOT NULL- und CONSTANTVariablen Initialisierung mit Zuweisungsoperator (:= / =) oder mit Schlüsselwort DEFAULT Empfehlung: Deklaration einer Variable je Zeile FB Automatisierung und Informatik: Datenbanksysteme I 19 Das Attribut %TYPE (nur Oracle) Deklariert eine Variable entsprechend: Einer Datenbankspalte Einer vorher deklarierten Variablen z z Präfix vor %TYPE: Tabellen- und Spaltenname Name der vorher deklarierten Variable z z Beispiele: ... ... v_ename v_ename v_balance v_balance v_min_balance v_min_balance ... ... emp.ename%TYPE; emp.ename%TYPE; NUMBER(7,2); NUMBER(7,2); v_balance%TYPE v_balance%TYPE := := 10; 10; FB Automatisierung und Informatik: Datenbanksysteme I 20 Block-Abschnitt Anweisungen Kommentare /* */ -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 z z FB Automatisierung und Informatik: Datenbanksysteme I 21 Bind-Variablen (Oracle) Eine Bind-Variable wird in PL/SQL mit einem Doppelpunkt (:) als Namens-Präfix referenziert. Beispiel : DECLARE DECLARE v_sal v_sal BEGIN BEGIN SELECT SELECT INTO INTO FROM FROM WHERE WHERE :salary :salary END; END; emp.sal%TYPE; emp.sal%TYPE; sal sal v_sal v_sal emp emp empno empno == 7369; 7369; := := v_sal; v_sal; FB Automatisierung und Informatik: Datenbanksysteme I 22 Einrücken von Code Übersichtlichkeit durch Einrückung jeder Code-Ebene. Beispiel: BEGIN BEGIN IF IF x=0 x=0 THEN THEN y:=1; y:=1; END END IF; IF; END; END; DECLARE DECLARE v_detpno NUMBER(2); v_detpno NUMBER(2); v_location VARCHAR2(13); v_location VARCHAR2(13); BEGIN BEGIN SELECT SELECT deptno, deptno, location location INTO v_deptno, INTO v_deptno, v_location v_location FROM dept FROM dept WHERE dname WHERE dname == 'SALES'; 'SALES'; ... ... END; END; FB Automatisierung und Informatik: Datenbanksysteme I 23 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 from employees; FB Automatisierung und Informatik: Datenbanksysteme I 24 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; FB Automatisierung und Informatik: Datenbanksysteme I 25 Funktionen: 1. Aufgabe Erstellen Sie eine Funktion mit der IBOConsole, F_Upper, die den Parameter in Großbuchstaben umwandeln. Aufruf: SELECT emp_no, first_name, ( select B from F_UPPER(e.first_name) ) from employee e; FB Automatisierung und Informatik: Datenbanksysteme I 27 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; FB Automatisierung und Informatik: Datenbanksysteme I 28 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; SELECT F_Visitenkarte(first_name, last_name,Email, Phone_nummer) FROM employees; FB Automatisierung und Informatik: Datenbanksysteme I 29 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 ( RETURNS ( AS BEGIN B = A+1; suspend; END ^ A INTEGER B INTEGER ) ) SET TERM ; ^ COMMIT WORK; SET AUTODDL ON; FB Automatisierung und Informatik: Datenbanksysteme I 30 COMMIT WORK; SET AUTODDL OFF; SET TERM ^ ; CREATE PROCEDURE INC2 ( A INTEGER ) RETURNS ( B INTEGER ) AS BEGIN b=a+1; suspend; END ^ SET TERM ; ^ COMMIT WORK; SET AUTODDL ON; FB Automatisierung und Informatik: Datenbanksysteme I 31 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; FB Automatisierung und Informatik: Datenbanksysteme I 32 Funktionen: 5. 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 33 Definition mit der IBOConsole FB Automatisierung und Informatik: Datenbanksysteme I 34 Definition mit der IBOConsole: Lösung FB Automatisierung und Informatik: Datenbanksysteme I 35
