77

Verändern von DatenVerändern von Daten

9-2

ZieleZiele

• Beschreibe jeden DML Befehl

• Einfügen von Zeilen in eine Tabelle

• Ändern von Zeilen in einer Tabelle

• Löschen von Zeilen aus einer Tabelle

• Transaktionskontrolle

• Beschreibe jeden DML Befehl

• Einfügen von Zeilen in eine Tabelle

• Ändern von Zeilen in einer Tabelle

• Löschen von Zeilen aus einer Tabelle

• Transaktionskontrolle

9-3

Data Manipulation LanguageData Manipulation Language

• Ein DML Statement wird ausgeführt bei:

– Hinzufügen neuer Zeilen zu einer Tabelle

– Modifikation von existierenden Zeilen in einer Tabelle

– Löschen von Zeilen aus einer Tabelle

• Eine Transaktion besteht aus einer Folge von DML-Statements, die eine logische Einheit (Unit of Work) bilden.

• Ein DML Statement wird ausgeführt bei:

– Hinzufügen neuer Zeilen zu einer Tabelle

– Modifikation von existierenden Zeilen in einer Tabelle

– Löschen von Zeilen aus einer Tabelle

• Eine Transaktion besteht aus einer Folge von DML-Statements, die eine logische Einheit (Unit of Work) bilden.

9-4

Hinzufügen einer neuen Zeile zu einer Tabelle (Insert)

Hinzufügen einer neuen Zeile zu einer Tabelle (Insert)

DEPT DEPT DEPTNO DNAME LOC ------ ---------- -------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON

Neue ZeileNeue Zeile

50 DEVELOPMENT DETROIT

DEPT DEPT DEPTNO DNAME LOC ------ ---------- -------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON

“…“…Einfügen neue Zeile Einfügen neue Zeile in die DEPT Tabelle…”in die DEPT Tabelle…”

50 DEVELOPMENT DETROIT

9-5

Der INSERT BefehlDer INSERT Befehl

• Hinzufügen neuer Zeilen zu einer Tabelle mit Hilfe des INSERT Statement.

• Nur eine Zeile wird gleichzeitig eingefügt bei dieser Syntax.

• Hinzufügen neuer Zeilen zu einer Tabelle mit Hilfe des INSERT Statement.

• Nur eine Zeile wird gleichzeitig eingefügt bei dieser Syntax.

INSERT INTO table [(column [, column...])]VALUES (value [, value...]);

INSERT INTO table [(column [, column...])]VALUES (value [, value...]);

9-6

Einfügen neuer ZeilenEinfügen neuer Zeilen

• Einfügen einer neuen Zeile, die für jede Spalte Werte enthält.

• Gebe die Werte standardmäßig in der Reihenfolge der Spalten der Tabelle an.

• Optional: Angabe der Spalten in der INSERT-Klausel.

• Zeichen- und Datumsfelder in einfache Hochkommas einschließen.

• Einfügen einer neuen Zeile, die für jede Spalte Werte enthält.

• Gebe die Werte standardmäßig in der Reihenfolge der Spalten der Tabelle an.

• Optional: Angabe der Spalten in der INSERT-Klausel.

• Zeichen- und Datumsfelder in einfache Hochkommas einschließen.

SQL> INSERT INTO dept (deptno, dname, loc) 2 VALUES (50, 'DEVELOPMENT', 'DETROIT');1 row created.1 row created.

9-7

Einfügen von Zeilen mit NullwertenEinfügen von Zeilen mit Nullwerten

• Implizite Methode: Spalte aus der Spaltenliste weglassen. • Implizite Methode: Spalte aus der

Spaltenliste weglassen.

SQL> INSERT INTO dept (deptno, dname ) 2 VALUES (60, 'MIS');1 row created.1 row created.

• Explizite Methode: Schlüsselwort NULL angeben.• Explizite Methode: Schlüsselwort NULL

angeben.

SQL> INSERT INTO dept 2 VALUES (70, 'FINANCE', NULL);1 row created.1 row created.

9-8

Einfügen spezieller WerteEinfügen spezieller Werte

SYSDATE Funktion ermittelt aktuelles SYSDATE Funktion ermittelt aktuelles Datum und Uhrzeit.Datum und Uhrzeit.SYSDATE Funktion ermittelt aktuelles SYSDATE Funktion ermittelt aktuelles Datum und Uhrzeit.Datum und Uhrzeit.

SQL> INSERT INTO emp (empno, ename, job, 2 mgr, hiredate, sal, comm, 3 deptno) 4 VALUES (7196, 'GREEN', 'SALESMAN', 5 7782, SYSDATE, 2000, NULL, 6 10);1 row created.1 row created.

9-9

Kopieren von Datensätzen aus einer anderen Tabelle

Kopieren von Datensätzen aus einer anderen Tabelle

• INSERT Statement mit Subquery verbinden.

• VALUES-Klausel nicht verwenden.

• Anzahl der Spalten in der INSERT Klausel muß mit der in der Subquery übereinstimmen.

• INSERT Statement mit Subquery verbinden.

• VALUES-Klausel nicht verwenden.

• Anzahl der Spalten in der INSERT Klausel muß mit der in der Subquery übereinstimmen.

SQL> INSERT INTO managers(id, name, salary, hiredate) 2 SELECT empno, ename, sal, hiredate 3 FROM emp 4 WHERE job = 'MANAGER';3 rows created.3 rows created.

9-10

Ändern von Daten in einer TabelleÄndern von Daten in einer TabelleEMPEMP

“…“…ändere eine Zeile ändere eine Zeile in EMP Tabelle…”in EMP Tabelle…”

EMPEMP

EMPNO ENAME JOB ... DEPTNO

7839 KING PRESIDENT 10 7698 BLAKE MANAGER 30 7782 CLARK MANAGER 10 7566 JONES MANAGER 20 ...

20

EMPNO ENAME JOB ... DEPTNO

7839 KING PRESIDENT 10 7698 BLAKE MANAGER 30 7782 CLARK MANAGER 10 7566 JONES MANAGER 20 ...

9-11

Der UPDATE BefehlDer UPDATE Befehl

• Modifiziere existierende Zeilen mit dem UPDATE Statement.

• Ändere mehr als eine Zeile wenn erforderlich.

• Modifiziere existierende Zeilen mit dem UPDATE Statement.

• Ändere mehr als eine Zeile wenn erforderlich.

UPDATE tableSET column = value [, column = value, ...][WHERE condition];

UPDATE tableSET column = value [, column = value, ...][WHERE condition];

9-12

Ändern von Zeilen in einer TabelleÄndern von Zeilen in einer Tabelle

• Eine oder mehrere Zeilen werden modi-fiziert, wenn eine WHERE Klausel besteht.

• Alle Zeilen der Tabelle werden modifiziert wenn WHERE Klausel weggelassen wird.

• Eine oder mehrere Zeilen werden modi-fiziert, wenn eine WHERE Klausel besteht.

• Alle Zeilen der Tabelle werden modifiziert wenn WHERE Klausel weggelassen wird.

SQL> UPDATE emp 2 SET deptno = 20 3 WHERE empno = 7782;1 row updated.1 row updated.

SQL> UPDATE employee 2 SET deptno = 20;14 rows updated.14 rows updated.

SQL> UPDATE employee 2 SET deptno = 20;14 rows updated.14 rows updated.

9-13

Ändern mit Hilfe einer Multiple-Column Subquery

Ändern mit Hilfe einer Multiple-Column Subquery

SQL> UPDATE emp 2 SET (job, deptno) = 3 (SELECT job, deptno 4 FROM emp 5 WHERE empno = 7499) 6 WHERE empno = 7698;1 row updated.1 row updated.

Ändere Job und Abteilung (Deptno) des Ändere Job und Abteilung (Deptno) des Angestellten 7698, so daß diese mit dem Angestellten 7698, so daß diese mit dem Angestellten 7499 übereinstimmen.Angestellten 7499 übereinstimmen.

Ändere Job und Abteilung (Deptno) des Ändere Job und Abteilung (Deptno) des Angestellten 7698, so daß diese mit dem Angestellten 7698, so daß diese mit dem Angestellten 7499 übereinstimmen.Angestellten 7499 übereinstimmen.

9-14

Ändern von Zeilen basierend auf einer anderen Tabelle

Ändern von Zeilen basierend auf einer anderen Tabelle

Verwende Subqueries in UPDATE Befehlen, Verwende Subqueries in UPDATE Befehlen, um Zeilen in einer Tabelle zu ändern, um Zeilen in einer Tabelle zu ändern, basierend auf Werten einer anderen Tabelle. basierend auf Werten einer anderen Tabelle.

Verwende Subqueries in UPDATE Befehlen, Verwende Subqueries in UPDATE Befehlen, um Zeilen in einer Tabelle zu ändern, um Zeilen in einer Tabelle zu ändern, basierend auf Werten einer anderen Tabelle. basierend auf Werten einer anderen Tabelle.

SQL> UPDATE employee 2 SET deptno = (SELECT deptno 3 FROM emp 4 WHERE empno = 7788) 5 WHERE job = (SELECT job 6 FROM emp 7 WHERE empno = 7788);2 rows updated.2 rows updated.

SQL> UPDATE employee 2 SET deptno = (SELECT deptno 3 FROM emp 4 WHERE empno = 7788) 5 WHERE job = (SELECT job 6 FROM emp 7 WHERE empno = 7788);2 rows updated.2 rows updated.

9-15

UPDATE emp *ERROR at line 1:ORA-02291: integrity constraint (USR.EMP_DEPTNO_FK) violated - parent key not found

UPDATE emp *ERROR at line 1:ORA-02291: integrity constraint (USR.EMP_DEPTNO_FK) violated - parent key not found

SQL> UPDATE emp 2 SET deptno = 55 3 WHERE deptno = 10;

SQL> UPDATE emp 2 SET deptno = 55 3 WHERE deptno = 10;

Ändern von Zeilen: Integrity Constraint Error

Ändern von Zeilen: Integrity Constraint Error

Department n

umber 55 does not e

xist

Department n

umber 55 does not e

xist

Department n

umber 55 does not e

xist

Department n

umber 55 does not e

xist

9-16

“…“…lösche einer Zeile lösche einer Zeile aus der DEPT Tabelle …”aus der DEPT Tabelle …”

Löschen einer Zeile aus einer Tabelle

Löschen einer Zeile aus einer Tabelle DEPT DEPT

DEPTNO DNAME LOC ------ ---------- -------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON 50 DEVELOPMENT DETROIT 60 MIS ...

DEPT DEPT DEPTNO DNAME LOC ------ ---------- -------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON 60 MIS ...

9-17

Der DELETE BefehlDer DELETE Befehl

Existierende Zeilen können mit Hilfe des Existierende Zeilen können mit Hilfe des DELETE Befehls gelöscht werden.DELETE Befehls gelöscht werden.Existierende Zeilen können mit Hilfe des Existierende Zeilen können mit Hilfe des DELETE Befehls gelöscht werden.DELETE Befehls gelöscht werden.

DELETE [FROM] table[WHERE condition];

DELETE [FROM] table[WHERE condition];

9-18

• Einzelne Zeilen werden gelöscht, wenn WHERE Klausel angegeben wird.

• Alle Zeilen einer Tabelle werden gelöscht, wenn WHERE Klausel weggelassen wird.

• Einzelne Zeilen werden gelöscht, wenn WHERE Klausel angegeben wird.

• Alle Zeilen einer Tabelle werden gelöscht, wenn WHERE Klausel weggelassen wird.

Löschen von Zeilen aus einer Tabelle

Löschen von Zeilen aus einer Tabelle

SQL> DELETE FROM department 2 WHERE dname = 'DEVELOPMENT'; 1 row deleted.1 row deleted.

SQL> DELETE FROM department 2 WHERE dname = 'DEVELOPMENT'; 1 row deleted.1 row deleted.

SQL> DELETE FROM department;4 rows deleted.4 rows deleted.

SQL> DELETE FROM department;4 rows deleted.4 rows deleted.

9-19

Löschen von Zeilen basierend auf einer anderen Tabelle

Löschen von Zeilen basierend auf einer anderen Tabelle

Nutze Subqueries in DELETE Befehlen, Nutze Subqueries in DELETE Befehlen, um Zeilen aus einer Tabelle zu löschen, um Zeilen aus einer Tabelle zu löschen, basierend auf Werten aus einer anderen basierend auf Werten aus einer anderen Tabelle.Tabelle.

Nutze Subqueries in DELETE Befehlen, Nutze Subqueries in DELETE Befehlen, um Zeilen aus einer Tabelle zu löschen, um Zeilen aus einer Tabelle zu löschen, basierend auf Werten aus einer anderen basierend auf Werten aus einer anderen Tabelle.Tabelle.

SQL> DELETE FROM employee 2 WHERE deptno = 3 (SELECT deptno 4 FROM dept 5 WHERE dname ='SALES');6 rows deleted.6 rows deleted.

9-20

Löschen von Zeilen: Integrity Constraint Error

Löschen von Zeilen: Integrity Constraint Error

SQL> DELETE FROM dept 2 WHERE deptno = 10;

SQL> DELETE FROM dept 2 WHERE deptno = 10;

DELETE FROM dept *ERROR at line 1:ORA-02292: integrity constraint (USR.EMP_DEPTNO_FK) violated - child record found

DELETE FROM dept *ERROR at line 1:ORA-02292: integrity constraint (USR.EMP_DEPTNO_FK) violated - child record found

You cannot delete a row

You cannot delete a row

that contains a primary key

that contains a primary key

that is used as a foreign key

that is used as a foreign key

in another table.

in another table.You cannot delete a row

You cannot delete a row

that contains a primary key

that contains a primary key

that is used as a foreign key

that is used as a foreign key

in another table.

in another table.

9-21

Datenbank-TransaktionenDatenbank-Transaktionen

Besteht aus einem der folgenden Befehle: Besteht aus einem der folgenden Befehle:

• DML Befehle, die eine konsistente Datenänderung bilden

• Ein DDL Befehl

• Ein DCL Befehl

Besteht aus einem der folgenden Befehle: Besteht aus einem der folgenden Befehle:

• DML Befehle, die eine konsistente Datenänderung bilden

• Ein DDL Befehl

• Ein DCL Befehl

9-22

Datenbank-TransaktionenDatenbank-Transaktionen

• Beginn bei der Ausführung des ersten ausführbaren SQL-Befehls.

• Endet mit einem der folgenden Ereignisse:

– COMMIT oder ROLLBACK abgesetzt

– DDL oder DCL Befehl (automatisches Commit)

– Benutzer verläßt System

– Systemzusammenbruch

• Beginn bei der Ausführung des ersten ausführbaren SQL-Befehls.

• Endet mit einem der folgenden Ereignisse:

– COMMIT oder ROLLBACK abgesetzt

– DDL oder DCL Befehl (automatisches Commit)

– Benutzer verläßt System

– Systemzusammenbruch

9-23

Vorteile von COMMIT und ROLLBACK Befehlen

Vorteile von COMMIT und ROLLBACK Befehlen

• Sichern Datenkonsistenz

• Offene Änderungen werden dauerhaft gemacht oder zurückgesetzt

• Gruppieren logisch zusammen-hängende Operationen

• Sichern Datenkonsistenz

• Offene Änderungen werden dauerhaft gemacht oder zurückgesetzt

• Gruppieren logisch zusammen-hängende Operationen

9-24

DELETEDELETE

Kontrolle von TransaktionenKontrolle von Transaktionen

TransaktionTransaktionTransaktionTransaktion

Savepoint ASavepoint A

ROLLBACK to Savepoint BROLLBACK to Savepoint B

DELETEDELETE

Savepoint BSavepoint BCOMMITCOMMIT

INSERTINSERTUPDATEUPDATE

ROLLBACK to Savepoint AROLLBACK to Savepoint A

INSERTINSERTUPDATEUPDATEINSERTINSERT

ROLLBACKROLLBACK

INSERTINSERT

9-25

Zustand der Daten vor dem COMMIT oder ROLLBACK

Zustand der Daten vor dem COMMIT oder ROLLBACK

• Der vorherige Zustand kann wieder-hergestellt werden.

• Die Ergebnisse der DML-Operationen inner-halb der Transaktion können mittels SELECT ausgewertet werden.

• Andere Benutzer können nicht die Ergebnisse der DML-Befehle des aktuellen Benutzers sehen.

• Die betroffenen Zeilen sind gesperrt (locked); somit können keine anderen Benutzer Daten innerhalb dieser Sätze ändern.

• Der vorherige Zustand kann wieder-hergestellt werden.

• Die Ergebnisse der DML-Operationen inner-halb der Transaktion können mittels SELECT ausgewertet werden.

• Andere Benutzer können nicht die Ergebnisse der DML-Befehle des aktuellen Benutzers sehen.

• Die betroffenen Zeilen sind gesperrt (locked); somit können keine anderen Benutzer Daten innerhalb dieser Sätze ändern.

9-26

Zustand der Daten nach COMMITZustand der Daten nach COMMIT

• Datenänderungen werden dauerhaft in der Datenbank.

• Der vorherige Zustand der Daten geht dauerhaft verloren.

• Alle Benutzer können die Ergebnisse sehen.

• Sperren auf den betroffenen Sätzen werden freigegeben; diese werden somit verfügbar für andere Benutzer.

• Datenänderungen werden dauerhaft in der Datenbank.

• Der vorherige Zustand der Daten geht dauerhaft verloren.

• Alle Benutzer können die Ergebnisse sehen.

• Sperren auf den betroffenen Sätzen werden freigegeben; diese werden somit verfügbar für andere Benutzer.

9-27

Ausführung eines Commits auf Daten

Ausführung eines Commits auf Daten

SQL> UPDATE emp 2 SET deptno = 10 3 WHERE empno = 7782;1 row updated.1 row updated.

SQL> UPDATE emp 2 SET deptno = 10 3 WHERE empno = 7782;1 row updated.1 row updated.

• Ausführung der Änderung.• Ausführung der Änderung.

• Commit der Änderung (Bestätigung).• Commit der Änderung (Bestätigung).SQL> COMMIT;Commit complete.Commit complete.

9-28

Zustand der Daten nach ROLLBACKZustand der Daten nach ROLLBACK

Verwerfen aller offenen Änderung durch Verwerfen aller offenen Änderung durch Nutzung des ROLLBACK Befehls. Nutzung des ROLLBACK Befehls.

• Datenänderungen rückgängig machen.

• Vorherigen Zustand rekonstruieren.

• Sperren auf betroffenen Sätzen freigeben.

Verwerfen aller offenen Änderung durch Verwerfen aller offenen Änderung durch Nutzung des ROLLBACK Befehls. Nutzung des ROLLBACK Befehls.

• Datenänderungen rückgängig machen.

• Vorherigen Zustand rekonstruieren.

• Sperren auf betroffenen Sätzen freigeben.

SQL> DELETE FROM employee;14 rows deleted.14 rows deleted.SQL> ROLLBACK;Rollback complete.Rollback complete.

9-29

LesekonsistenzLesekonsistenz

• Lesekonsistenz garantiert eine kon-sistente Sicht auf die Daten zu jeder Zeit.

• Änderungen eines Benutzer stehen nicht in Konflikt mit Änderungen durch andere Benutzer.

• Lesekonsistenz sichert auf den gleichen Daten:

– Leser warten nicht auf Schreiber

– Schreiben warten nicht auf Leser

• Lesekonsistenz garantiert eine kon-sistente Sicht auf die Daten zu jeder Zeit.

• Änderungen eines Benutzer stehen nicht in Konflikt mit Änderungen durch andere Benutzer.

• Lesekonsistenz sichert auf den gleichen Daten:

– Leser warten nicht auf Schreiber

– Schreiben warten nicht auf Leser

9-30

Implementierung der LesekonsistenzImplementierung der Lesekonsistenz

UPDATE empUPDATE empSET sal = 2000SET sal = 2000WHERE ename = WHERE ename = 'SCOTT';'SCOTT';

Daten-Daten-blöckeblöcke

RollbackRollbackSegmenteSegmente

veränderteveränderteund und unveränderte unveränderte DatenDaten

““alte” Daten alte” Daten vor der vor der ÄnderungÄnderung

Benutzer ABenutzer A

Benutzer BBenutzer B

ReadReadconsistentconsistentimageimage

SELECT *SELECT *FROMFROM emp;emp;

9-31

Sperren (Locking)Sperren (Locking)

Oracle sperrt:Oracle sperrt:

• Verhinderung von gegenseitigen Behinderungen gleichen Daten durch konkurrierende Transaktionen

• Erfordert keine Benutzeraktion

• Sperren werden während der Dauer der Transaktion gehalten

• Zwei Basismodi: – Exclusive– Share

Oracle sperrt:Oracle sperrt:

• Verhinderung von gegenseitigen Behinderungen gleichen Daten durch konkurrierende Transaktionen

• Erfordert keine Benutzeraktion

• Sperren werden während der Dauer der Transaktion gehalten

• Zwei Basismodi: – Exclusive– Share

9-32

ZusammenfassungZusammenfassung

Description

Fügt eine neue Zeile in die Tabelle ein

Modifiziert existierende Zeilen in der Tabelle

Löscht existierende Zeilen aus der Tabelle

Macht alle offenen Änderungen dauerhaft

Erlaubt ein Rollback bis zu Sicherungspunkt.

Verwerfen aller offenen Datenänderungen

Statement

INSERT

UPDATE

DELETE

COMMIT

SAVEPOINT

ROLLBACK

9-33

ÜbungenÜbungen

• Einfügen neuer Datensätze in Tabellen• Ändern und Löschen von Datensätzen in der Tabelle• Transaktionskontrolle

• Einfügen neuer Datensätze in Tabellen• Ändern und Löschen von Datensätzen in der Tabelle• Transaktionskontrolle