Categorias: Todos

por Kiss Máté 7 anos atrás

336

DML utasítások és tranzakciókezelés

A DML (Data Manipulation Language) utasítások fontos szerepet játszanak az adatbázisok kezelésében, mivel lehetővé teszik a meglévő táblák adatainak módosítását. Ezek az utasítások többféle műveletet foglalnak magukba, mint például új sorok beszúrása, meglévő sorok módosítása vagy törlése.

DML utasítások és tranzakciókezelés

DML utasítások és tranzakciókezelés

Forrás: Előadás diáik

Tranzakciók

A tranzakció SQL műveletek (DML műveletek) egy olyan sorozata, amely vagy teljes egészében érvényre jut, vagy annak minden módosítása visszavonásra kerül.

A tranzakciókkal logikai csoportokba rendezzük a módosításainkat.

A fejlesztő (programozó) felelőssége, hogy pontosan a megfelelő egységekbe (tranzakciókba) csoportosítsa a DML műveleteket.



Olvasási konzisztencia
SAVEPOINT utasítás

Egyes adatbáziskezelőknél a tranzakció során elhelyezhetőek olyan pontok, amelyekre opcionálisan vissza lehet térni, ez a potenciális visszatérési pont a SAVEPOINT.

Ezekre a visszatérési pontokra a ROLLBACK TO SAVEPOINT utasítással térhetünk vissza.


Példa:

UPDATE...

SAVEPOINT update_done;

Savepoint created


INSERT...

ROLLBACK TO update_done;

Rollback complete

COMMIT és ROLLBACK utasítások

Ha egy tranzakciót véglegesíteni akarunk, azt a COMMIT utasítással tesszük.

Ha viszont menet közben meggondoltuk magunkat, és vissza akarjuk vonni az éppen folyamatban lévő tranzakciónkat, azt a ROLLBACK utasítással tesszük.


Példa:

SELECT COUNT(*) FROM newworker;

COUNT(*)

----------

53

INSERT INTO newworker SELECT * FROM newworker;

53 rows inserted


SELECT COUNT(*) FROM newworker;

COUNT(*)

----------

106

DELETE FROM newworker WHERE position_id LIKE 'SALES%';

28 rows deleted


SELECT COUNT(*) FROM newworker;

COUNT(*)

----------

78

ROLLBACK;

rollback complete


SELECT COUNT(*) FROM newworker;

COUNT(*)

----------

53



Kétfázisú jóváhagyás

Two phase commit

Utasítás szintű ROLLBACK
Adatok értéke a ROLLBACK után


Példa:

DELETE FROM copy_emp;

22 rows deleted

ROLLBACK ;

Rollback complete


Adatok értéke a COMMIT után
Adatok értéke a COMMIT vagy ROLLBACK előtt
ACID szabályok

Az adatbáziskezelők azon funkcióit, melyek a többfelhasználós környezetben biztosítják minden felhasználó számára a konzisztens adatbázis képet az ACID rövidítéssel jellemzik (Atomicity, Cosistency, Isolation, Durability - Atomikusság, Konzisztencia, Elszigeteltség, Állandóság).

Konzisztencia

Konzisztencia (Consistency):

A tranzakció befejezése után az adatbázisnak konzisztens állapotba kell kerülnie.

Elszigeteltség

Elszigeteltség (Isolation):

A párhuzamosan futó tranzakcióknak egymástól függetlenül kell működniük. Minden felhasználónak úgy kell tünnie mintha csak ő használná az adatbázist. Hatékonysági okokból nem lehet a függetlenséget úgy biztosítani, hogy egyidőben csak egy tranzakció futhat az adatbázison. Helyette sor illetve tábla zárolásokat alkalmaznak az adatbáziskezelők.

Atomikusság

Atomikusság (Atomicity):

A tranzakcióba bevont DML utasításokat egy egységként kell kezelnie az adatbáziskezelőnek, például, ha a tranzakció végrehajtása valamilyen hardver, szoftver hiba miatt megszakad, akkor az adatbáziskezelőnek automatikusan vissza kell vonnia az addig végrahatott műveleteket, hogy a tranzakció kezdése előtti konzisztens állapot álljon elő.

Állandóság

Állandóság (Durability):

A lezárt tranzakciók eredménye nem veszhet el. Hatékonysági okokból az adatbáziskezelők nem írják rögtön diszkre a módosított adatokat, hanem memóriában tárolják. Például egy hardver hiba a memória tartalma és így a tranzakció eredményének elvesztésével járhatna. Az ilyen adatvesztéseket az adatbáziskezelők tranzakció log használatával oldják meg.

DML Utasítások (Data Manipulation Language)

A DML utasítások a meglévő táblák (általánosabban "egyes séma-objektumok") adatait módosítják.

DML utasítás lehet, ha:

A DML utasítások sorozatát tranzakciónak nevezzük, amely az adatmódosításaink egy logikai egysége. Ezeket a módosításokat egységenként vagy érvényesítjük a végén, vagy mindenestül visszavonjuk.

A DELETE utasítás

A táblák meglévő sorainak törlése.


Példa:

DELETE [FROM] table

[WHERE condition];


Példa:

DELETE FROM divisions

WHERE division_name = 'Contracting';

1 row deleted


Példa:

DELETE FROM divisions

WHERE division_name = 'Recruiting';




Allekérdezés a DELETE-ben

Egy DELETE segítségével törölhetők ki olyan sorok is, amelyek megtalálhatóak egy másik táblában, vagy akár fordítva, azok, amelyek nem találhatóak a másikban sem.


Példa:

DELETE FROM newworker

WHERE worker_id NOT IN

(SELECT NVL(manager_id,-1)

FROM  workers);

39 row deleted

Az INSERT utasítás és a szintaxisa

Sorok beszúrása a táblába. Az INSERT utasítással sorokat adunk hozzá a táblához, a nézet alaptáblájához, vagy esetleg egyéb nem szabványos elemhez.


Példa:

INSERT INTO table [(column [, column...])]

VALUES (value [, value...]);


Ezzel a szintaxissal egy utasítással csupán egy sort szúrhatunk be.


Megadjuk az oszlopokat, amelyek értéket kapnak:


Példa:

INSERT INTO divisions (division_id,division_name,

manager_id,city,country_id,parent_id)

VALUES (2,'Headquarters',

100,'San Francisco','US',NULL);

1 rows inserted

NULL értékek beszúrása

Implicit módszer: azokat az oszlopokat kihagyjuk az oszloplistából, amelyekbe NULL kell, hogy kerüljön:


Példa:

INSERT INTO divisions

(division_id,division_name,manager_id,city) VALUES (2,'Headquarters',100,'San Francisco');

1 row created


Explicit módszer: NULL kulcsszót használunk a VALUES klauzulában:


Példa:

INSERT INTO   divisions

VALUES (100, 'Finance', NULL, NULL, NULL, NULL);

1 row created



INSERT SELECT utasítás

Írhatunk INSERT utasítást egy allekérdezéssel:

Példa:

INSERT INTO preferred_customers

SELECT * FROM  customers

WHERE credit_rating in ('GOOD','EXCELLENT');

105 row created


Ilyenkor nem szerepel a VALUES klauzula.


Az INSERT klauzula oszlopainak a száma megegyezik az allekérdezés oszlopainak a számával:

Példa:

INSERT INTO preferred_customers

(customer_id ,customer_name,city)

SELECT customer_id ,customer_name,city FROM  customers

WHERE credit_rating in ('GOOD','EXCELLENT');

105 row created

Az UPDATE utasítás és a szintaxisa

A meglévő sorok értékeinek a módosítása.

Az UPDATE utasítás a tábla soraiban, vagy a nézet alaptáblája, esetleg egy materializált nézet soraiban lévő attribútumok értékeit módosítja:


Példa:

UPDATE table

SET column = value [, column = value, ...]

[WHERE condition];


Példa:

UPDATE workers

SET   division_id = 70

WHERE worker_id = 100;

1 row updated


Példa:

UPDATE workers

SET  last_name = UPPER(last_name);

53 rows updated


Egyszerre tetszőleges számú sor is módosítható allekérdezéssel:

Példa:

UPDATE  workers

SET (position_id, salary) = (SELECT position_id, salary

FROM   workers  WHERE  worker_id = 205)

WHERE   worker_id = 206;

1 rows updated













Korrelált UPDATE utasítás

A korrelált allekérdezés egy olyan SELECT utasítás, amely be van ágyazva a másik utasításba (itt pl. az UPDATE-be), és amely hivatkozik az őt körülvevő SQL utasítás oszlopára vagy oszlopaira.

Amikor elképzeljük a működését, akkor úgy képzeljük, hogy a külső UPDATE utasítás által érintett minden sorra külön lefut a belső SELECT.


Példa:

UPDATE customers c

SET comments = (SELECT TO_CHAR(SUM(total_amount))

FROM orders o

WHERE o.customer_id = c.customer_id);

135 rows updated


A példában a CUSTOMERS tábla COMMENTS mezőjébe írtuk be azt, hogy az illető ügyfél összesen mekkora értékben rendelt terméket.