Informazioni sulle istruzioni e le transazioni DML

Le istruzioni DML (Data Manipulation Language) aggiungono, modificano ed eliminano i dati delle tabelle di database. Una transazione è una sequenza di uno o più istruzioni SQL che Oracle AI Database tratta come un'unità: vengono eseguite tutte le istruzioni oppure non ne viene eseguita nessuna.

Informazioni sulle istruzioni di controllo delle transazioni

Una transazione è una sequenza di uno o più istruzioni SQL che Oracle AI Database tratta come un'unità: vengono eseguite tutte le istruzioni oppure non ne viene eseguita nessuna. È necessario utilizzare le transazioni per modellare business process che richiedono l'esecuzione di più operazioni come un'unica unità.

Ad esempio, quando un responsabile lascia l'azienda, è necessario inserire una riga nella tabella JOB_HISTORY per indicare quando è via, nonché aggiornare il valore di MANAGER_ID nella tabella EMPLOYEES per ogni dipendente che fa riferimenti a tale responsabile. Per modellare questo processo in un'applicazione, è necessario raggruppare le istruzioni INSERT e UPDATE in una singola transazione.

Le istruzioni di controllo delle transazioni di base sono:

SAVEPOINT, che indica un SAVEPOINT in una transazione, ovvero un punto al quale è possibile effettuare il rollback più tardi. I savepoint sono facoltativi e una transazione può avere più savepoint.
COMMIT, che termina la transazione corrente, ne rende permanenti le modifiche, ne elimina i relativi savepoint e ne rilascia i lock.
ROLLBACK, che esegue il ROLLBACK (annulla) dell'intera transazione corrente o solo delle modifiche apportate dopo il savepoint specificato.

Nell'ambiente SQL*Plus è possibile immettere un'istruzione di controllo delle transazioni dopo il prompt SQL>.

Nell'ambiente SQL Developer è possibile immettere un'istruzione di controllo della transazione nel Foglio di lavoro. In SQL Developer sono inoltre disponibili le icone Commit delle modifiche e Rollback delle modifiche, descritte in "Esecuzione del commit di transazioni" e "Esecuzione del rollback di transazioni".

Attenzione:

Se non si esegue un commit esplicito di una transazione in modo esplicito e il programma termina in maniera anomala, il database esegue automaticamente il rollback dell'ultima transazione di che non ha eseguito il Commit.

Oracle consiglia di terminare le transazioni in modo esplicito nei programmi applicativi, eseguendone il commit o il rollback.

Vedere anche:

Oracle AI Database Concepts per ulteriori informazioni sulla gestione delle transazioni
Oracle AI Database SQL Language Reference per ulteriori informazioni sulle istruzioni di controllo delle transazioni

Esecuzione del commit delle transazioni

Il commit di una transazione ne rende permanenti le modifiche, ne cancella i savepoint e ne rilascia i lock.

Per eseguire un COMMIT esplicito di una transazione, utilizzare l'istruzione COMMIT o (nell'ambiente SQL Developer) l'icona COMMIT delle modifiche.

Nota: Oracle AI Database esegue un'istruzione COMMIT implicita prima e dopo qualsiasi istruzione D DLL (Data Definition Language). Per informazioni sulle istruzioni D DLL, vedere "Informazioni sulle istruzioni D DLL (Data Definition Language)".

Prima di eseguire il commit di una transazione:

Le modifiche apportate non sono visibili agli altri utenti dell'istanza del database;
le modifiche non sono finali (è possibile annullarle con un'istruzione ROLLBACK).

Dopo avere eseguito il commit di una transazione:

le modifiche apportate sono visibili agli altri utenti e alle relative istruzioni che vengono eseguite in seguito al commit della propria transazione;
le modifiche sono finali (non è possibile annullarle con un'istruzione ROLLBACK).

Esempio 3-1 aggiunge una riga alla tabella REGIONS (una transazione molto semplice), verifica i risultati e quindi esegue il commit della transazione.

Esempio 3-1 Esecuzione del commit di una transazione

Prima della transazione:

SELECT * FROM REGIONS
ORDER BY REGION_ID;

Risultato:

 REGION_ID REGION_NAME
---------- -------------------------
         1 Europe
         2 Americas
         3 Asia
         4 Middle East and Africa

4 rows selected.

Transazione (aggiunta di una riga alla tabella):

INSERT INTO regions (region_id, region_name) VALUES (5, 'Africa');

Risultato:

1 row created.

Verifica dell'aggiunta della riga:

SELECT * FROM REGIONS
ORDER BY REGION_ID;

Risultato:

 REGION_ID REGION_NAME
---------- -------------------------
         1 Europe
         2 Americas
         3 Asia
         4 Middle East and Africa
         5 Africa

5 rows selected.

Esecuzione del commit della transazione:

COMMIT;

Risultato:

Commit complete.

Vedere anche: Oracle AI Database SQL Language Reference per informazioni sull'istruzione COMMIT

Esecuzione del rollback delle transazioni

Il rollback di una transazione ne annulla le modifiche. È possibile eseguire il rollback dell'intera transazione corrente oppure eseguirne il rollback solo a un savepoint specificato.

Per eseguire il ROLLBACK della transazione corrente solo a un SAVEPOINT specificato è necessario utilizzare l'istruzione ROLLBACK con la clausola TO SAVEPOINT.

Per eseguire il ROLLBACK dell'intera transazione corrente, utilizzare l'istruzione ROLLBACK senza la clausola TO SAVEPOINT oppure (nell'ambiente SQL Developer) l'icona ROLLBACK delle modifiche.

Rollback dell'intera transazione corrente:

termina la transazione;
ne annulla tutte le modifiche;
ne cancella tutti i savepoint;
rilascia gli eventuali lock della transazione.

Il rollback della transazione corrente al savepoint specificato:

non termina la transazione;
annulla solo le modifiche apportate dopo il savepoint specificato;
cancella solo i savepoint impostati dopo quello specificato (escludendo quest'ultimo);
rilascia tutti i lock di tabella e di riga acquisiti dopo il savepoint specificato.

Le altre transazioni che hanno richiesto l'accesso alle righe bloccate dopo il savepoint specificato devono continuare ad attendere finché non viene eseguito il commit o il rollback della transazione. Le altre transazioni che non hanno richiesto le righe possono richiederle e accedervi immediatamente.

Per vedere l'effetto di un rollback in SQL Developer potrebbe essere necessario fare clic sull'icona Aggiorna.

Come risultato dell'Esempio 3-1, la tabella REGIONS contiene un'area denominata 'Medio Oriente e Africa' e un'area denominata 'Africa'. L'Esempio 3-2 corregge questo problema (una transazione molto semplice) e verifica la modifica, ma quindi esegue il rollback della transazione e verifica il rollback.

Esempio 3-2 Rollback di un'intera transazione

Prima della transazione:

SELECT * FROM REGIONS
ORDER BY REGION_ID;

Risultato:

 REGION_ID REGION_NAME
---------- -------------------------
         1 Europe
         2 Americas
         3 Asia
         4 Middle East and Africa
         5 Africa

5 rows selected.

Transazione (modifica della tabella):

UPDATE REGIONS
SET REGION_NAME = 'Middle East'
WHERE REGION_NAME = 'Middle East and Africa';

Risultato:

1 row updated.

Verifica della modifica:

SELECT * FROM REGIONS
ORDER BY REGION_ID;

Risultato:

 REGION_ID REGION_NAME
---------- -------------------------
         1 Europe
         2 Americas
         3 Asia
         4 Middle East
         5 Africa

5 rows selected.

Esecuzione del rollback della transazione:

ROLLBACK;

Risultato:

Rollback complete.

Verifica del rollback:

SELECT * FROM REGIONS
ORDER BY REGION_ID;

Risultato:

 REGION_ID REGION_NAME
---------- -------------------------
         1 Europe
         2 Americas
         3 Asia
         4 Middle East and Africa
         5 Africa

5 rows selected.

Vedere anche: Oracle AI Database SQL Language Reference per informazioni sull'istruzione ROLLBACK

Impostazione dei savepoint nelle transazioni

L'istruzione SAVEPOINT indica un SAVEPOINT in una transazione, ossia in un punto al quale è possibile effettuare il rollback a seguito. I savepoint sono facoltativi e una transazione può avere più savepoint. L'Esempio 3-3 esegue una transazione che include diverse istruzioni DML e diversi savepoint, quindi esegue il rollback della transazione a un savepoint, annullando solo le modifiche apportate dopo il savepoint.

Esempio 3-3 Esecuzione del rollback di una transazione a un savepoint

Verifica della tabella REGION prima del transazione:

SELECT * FROM REGIONS
ORDER BY REGION_ID;

Risultato:

 REGION_ID REGION_NAME
---------- -------------------------
         1 Europe
         2 Americas
         3 Asia
         4 Middle East and Africa
         5 Africa

5 rows selected.

Verifica dei paesi nell'area 4 prima della transazione:

SELECT COUNTRY_NAME, COUNTRY_ID, REGION_ID
FROM COUNTRIES
WHERE REGION_ID = 4
ORDER BY COUNTRY_NAME;

Risultato:

COUNTRY_NAME                             CO  REGION_ID
---------------------------------------- -- ----------
Egypt                                    EG          4
Israel                                   IL          4
Kuwait                                   KW          4
Nigeria                                  NG          4
Zambia                                   ZM          4
Zimbabwe                                 ZW          4

6 rows selected.

Verifica dei paesi nell'area 5 prima della transazione:

SELECT COUNTRY_NAME, COUNTRY_ID, REGION_ID
FROM COUNTRIES
WHERE REGION_ID = 5
ORDER BY COUNTRY_NAME;

Risultato:

no rows selected

Transazione, con diversi savepoint:

UPDATE REGIONS
SET REGION_NAME = 'Middle East'
WHERE REGION_NAME = 'Middle East and Africa';

UPDATE COUNTRIES
  SET REGION_ID = 5
  WHERE COUNTRY_ID = 'ZM';
SAVEPOINT zambia;

UPDATE COUNTRIES
  SET REGION_ID = 5
  WHERE COUNTRY_ID = 'NG';
SAVEPOINT nigeria;

UPDATE COUNTRIES
  SET REGION_ID = 5
  WHERE COUNTRY_ID = 'ZW';
SAVEPOINT zimbabwe;

UPDATE COUNTRIES
  SET REGION_ID = 5
  WHERE COUNTRY_ID = 'EG';
SAVEPOINT egypt;

Verifica della tabella REGION dopo le transazioni:

SELECT * FROM REGIONS
ORDER BY REGION_ID;

Risultato:

 REGION_ID REGION_NAME
---------- -------------------------
         1 Europe
         2 Americas
         3 Asia
         4 Middle East
         5 Africa

5 rows selected.

Verifica dei paesi nell'area 4 dopo la transazione:

SELECT COUNTRY_NAME, COUNTRY_ID, REGION_ID
FROM COUNTRIES
WHERE REGION_ID = 4
ORDER BY COUNTRY_NAME;

Risultato:

COUNTRY_NAME                             CO  REGION_ID
---------------------------------------- -- ----------
Israel                                   IL          4
Kuwait                                   KW          4

2 rows selected.

Verifica dei paesi nell'area 5 dopo la transazione:

SELECT COUNTRY_NAME, COUNTRY_ID, REGION_ID
FROM COUNTRIES
WHERE REGION_ID = 5
ORDER BY COUNTRY_NAME;

Risultato:

COUNTRY_NAME                             CO  REGION_ID
---------------------------------------- -- ----------
Egypt                                    EG          5
Nigeria                                  NG          5
Zambia                                   ZM          5
Zimbabwe                                 ZW          5

4 rows selected.

ROLLBACK TO SAVEPOINT nigeria;

Verifica della tabella REGION dopo la rollback:

SELECT * FROM REGIONS
ORDER BY REGION_ID;

Risultato:

 REGION_ID REGION_NAME
---------- -------------------------
         1 Europe
         2 Americas
         3 Asia
         4 Middle East
         5 Africa

5 rows selected.

Verifica dei paesi nell'area 4 dopo il rollback:

SELECT COUNTRY_NAME, COUNTRY_ID, REGION_ID
FROM COUNTRIES
WHERE REGION_ID = 4
ORDER BY COUNTRY_NAME;

Risultato:

COUNTRY_NAME                             CO  REGION_ID
---------------------------------------- -- ----------
Egypt                                    EG          4
Israel                                   IL          4
Kuwait                                   KW          4
Zimbabwe                                 ZW          4

4 rows selected.

Verifica dei paesi nell'area 5 dopo il rollback:

SELECT COUNTRY_NAME, COUNTRY_ID, REGION_ID
FROM COUNTRIES
WHERE REGION_ID = 5
ORDER BY COUNTRY_NAME;

Risultato:

COUNTRY_NAME                             CO  REGION_ID
---------------------------------------- -- ----------
Nigeria                                  NG          5
Zambia                                   ZM          5

2 rows selected.

Vedere anche: Oracle AI Database SQL Language Reference per informazioni sull'istruzione SAVEPOINT