Utilizzo della manipolazione dei dati

Oracle fornisce comandi Data Manipulation Language per esercitare le operazioni sui dati nel database. Le operazioni sui dati possono popolare le tabelle del database con l'applicazione oi dati aziendali, modificando i dati e rimuovendo i dati dal database, quando richiesto. Oltre alle operazioni sui dati, sono presenti una serie di comandi utilizzati per controllare queste operazioni, raggruppati in Transaction Control Language.

Esistono tre tipi di istruzioni DML coinvolte in una transazione SQL logica, ovvero Inserisci, Aggiorna, Elimina e Unisci. Una transazione è la raccolta logica di azioni DML all'interno di una sessione di database.

Istruzione INSERT

Il comando INSERT viene utilizzato per memorizzare i dati nelle tabelle. Il comando INSERT viene spesso utilizzato nei linguaggi di programmazione di livello superiore come Visual Basic.NET o C ++ come comando SQL incorporato; tuttavia, questo comando può essere eseguito anche al prompt SQL * PLUS in modalità comando. Esistono due diverse forme del comando INSERT. Il primo modulo viene utilizzato se una nuova riga avrà un valore inserito in ogni colonna della riga. La seconda forma del comando INSERT viene utilizzata per inserire righe in cui alcuni dati della colonna sono sconosciuti o di default da un'altra logica aziendale. Questa forma del comando INSERT richiede di specificare i nomi delle colonne per le quali vengono memorizzati i dati.

Sintassi:

La sintassi seguente può essere seguita se i valori per tutte le colonne della tabella sono definiti e noti.

INSERT INTO table
VALUES (column1 value, column2 value, 
...);

La sintassi seguente può essere utilizzata se solo poche colonne della tabella devono essere popolate con un valore. Il resto delle colonne può dedurre i propri valori come NULL o da una logica aziendale diversa.

INSERT INTO table (column1 name, column2 name, . . .)
VALUES (column1 value, column2 value, . . .);

L'istruzione INSERT riportata di seguito crea un nuovo record dipendente nella tabella EMPLOYEES. Notare che inserisce i valori per le colonne primarie EMPLOYEE_ID, FIRST_NAME, SALARY e DEPARTMENT_ID.

INSERT INTO employees (EMPLOYEE_ID, FIRST_NAME, SALARY, DEPARTMENT_ID)
VALUES (130, 'KEMP', 3800, 10);

In caso contrario, i dati completi dei dipendenti possono essere inseriti nella tabella EMPLOYEES senza specificare l'elenco delle colonne utilizzando l'istruzione INSERT sottostante, a condizione che i valori siano noti in anticipo e devono essere conformi al tipo di dati e alla posizione delle colonne nella tabella.

INSERT INTO employees
VALUES (130, 'KEMP','GARNER', '[email protected]', '48309290',TO_DATE ('01-JAN-2012'), 'SALES', 3800, 0, 110, 10);

I valori da inserire devono essere compatibili con il tipo di dati della colonna. Letterali, valori fissi e valori speciali come funzioni, SYSDATE, CURRENT_DATE, SEQ.CURRVAL (NEXTVAL) o USER possono essere utilizzati come valori di colonna. I valori specificati devono seguire le regole generiche. I valori letterali stringa e i valori di data devono essere racchiusi tra virgolette. Il valore della data può essere fornito nel formato GG-MON-RR o G-MON-AAAA, ma è preferibile YYYY poiché specifica chiaramente il secolo e non dipende dalla logica di calcolo del secolo RR interno.

Istruzione INSERT-AS-SELECT (IAS)

I dati possono essere inseriti nella tabella di destinazione dalla tabella di origine utilizzando l'operazione INSERT..AS..SELECT (IAS). È un'operazione di lettura del percorso diretto. È un modo semplice per creare una copia dei dati da una tabella a un'altra o creare una copia di backup della tabella in cui sono online le operazioni della tabella di origine.

Ad esempio, i dati possono essere copiati dalla tabella EMPLOYEES alla tabella EMP_HISTORY.

INSERT INTO EMP_HISTORY
SELECT EMPLOYEE_ID, EMPLOYEE_NAME, SALARY, DEPARTMENT_ID
FROM employees;

Istruzione UPDATE

Il comando UPDATE modifica i dati archiviati in una colonna e può aggiornare una o più righe alla volta a seconda del set di risultati filtrato in base alle condizioni specificate nella clausola WHERE. Tieni presente che l'aggiornamento delle colonne è diverso dall'alterazione delle colonne. In precedenza in questo capitolo si è studiato il comando ALTER. Il comando ALTER modifica la struttura della tabella, ma lascia inalterati i dati della tabella. Il comando UPDATE modifica i dati nella tabella, non la struttura della tabella.

Sintassi:

UPDATE table
SET column = value [, column = value ...]
[WHERE condition]

Dalla sintassi,

L'espressione SET column = può essere qualsiasi combinazione di caratteri, formule o funzioni che aggiorneranno i dati nel nome di colonna specificato. La clausola WHERE è facoltativa, ma se è inclusa, specifica quali righe verranno aggiornate. essere aggiornato alla volta con un comando UPDATE.

La dichiarazione UPDATE di seguito aggiorna lo stipendio del dipendente JOHN a 5000.

UPDATE employees
SET salary = 5000
WHERE UPPER (first_name) = 'JOHN';

Sebbene i predicati WHERE siano facoltativi, devono essere aggiunti logicamente in modo da modificare solo la riga richiesta nella tabella. La dichiarazione UPDATE di seguito aggiorna gli stipendi di tutti i dipendenti nella tabella.

UPDATE employees
SET salary = 5000;

È inoltre possibile aggiornare più colonne specificando più colonne nella clausola SET separate da una virgola. Ad esempio, se sia lo stipendio che il ruolo lavorativo devono essere modificati rispettivamente in 5000 e SALES per GIOVANNI, l'istruzione UPDATE avrà il seguente aspetto,

UPDATE employees
SET	SALARY = 5000,
	JOB_ID = 'SALES'
WHERE UPPER (first_name) = 'JOHN';

1 row updated.

Un altro modo per aggiornare più colonne della stessa riga mostra l'utilizzo della sottoquery.

UPDATE employees
SET (SALARY, JOB_ID) = (SELECT 5000, 'SALES' FROM DUAL)
WHERE UPPER (ENAME) = 'JOHN'

Istruzione DELETE

Il comando DELETE è una delle più semplici istruzioni SQL. Rimuove una o più righe da una tabella. Non sono consentite operazioni di cancellazione di più tabelle in SQL. La sintassi del comando DELETE è la seguente.

DELETE FROM table_name
    [WHERE condition];

Il comando DELETE elimina tutte le righe nella tabella che soddisfano la condizione nella clausola WHERE facoltativa. Poiché la clausola WHERE è facoltativa, è possibile eliminare facilmente tutte le righe da una tabella omettendo una clausola WHERE poiché la clausola WHERE limita l'ambito dell'operazione DELETE.

L'istruzione DELETE di seguito rimuoverà i dettagli di EDWIN dalla tabella EMP.

DELETE employees
WHERE UPPER (ENAME) = 'EDWIN'

1 row deleted.

Nota: DELETE [TABLE NAME] e DELETE FROM [TABLE NAME] hanno lo stesso significato.

La condizione WHERE nelle istruzioni di eliminazione condizionale può utilizzare la sottoquery come mostrato di seguito.

DELETE FROM employees
WHERE DEPARTMENT_ID IN (SELECT DEPARTMENT_ID
				    FROM LOCATIONS
				    WHERE LOCATION_CODE = 'SFO')

TRONCARE

Truncate è un comando DDL che viene utilizzato per eliminare tutti i record da una tabella ma mantenendo la struttura della tabella. Non supporta la condizione WHERE per rimuovere i record selezionati.

Sintassi:

TRUNCATE [table name]

È Auto Commit, cioè esegue il commit della transazione attiva corrente nella sessione. Il troncamento della tabella non elimina gli indici dipendenti, i trigger o i vincoli di tabella. Se una tabella A è padre di un vincolo di riferimento di una tabella B nel database, la tabella A non può essere troncata.

Transazione

Una transazione è un'unità logica del lavoro svolto nel database. Può contenere -

• Comandi DML multipli che terminano con un comando TCL, ad esempio COMMIT o ROLLBACK

• Un comando DDL

• Un comando DCL

L'inizio di una transazione è contrassegnato dal primo comando DML. Termina con un comando TCL, DDL o DCL. Un comando TCL, ad esempio COMMIT o ROLLBACK, viene emesso esplicitamente per terminare una transazione attiva. In virtù del loro comportamento di base, se uno qualsiasi dei comandi DDL o DCL viene eseguito in una sessione di database, eseguire il commit della transazione attiva in corso nella sessione. Se l'istanza del database si arresta in modo anomalo, la transazione viene interrotta.

COMMIT, ROLLBACK e SAVEPOINT sono il linguaggio di controllo delle transazioni. COMMIT applica le modifiche ai dati in modo permanente nel database mentre ROLLBACK esegue l'operazione anti-commit. SAVEPOINT controlla la serie di una transazione impostando marcatori in diverse fasi della transazione. L'utente può ripristinare la transazione corrente al punto di salvataggio desiderato, impostato in precedenza.

COMMIT- Commit termina la transazione attiva corrente applicando le modifiche ai dati in modo permanente nelle tabelle del database. COMMIT è un comando TCL che termina esplicitamente la transazione. Tuttavia, il comando DDL e DCL esegue il commit implicito della transazione.

SAVEPOINT- Savepoint viene utilizzato per contrassegnare un punto specifico nella transazione corrente nella sessione. Poiché è un indicatore logico nella transazione, i punti di salvataggio non possono essere interrogati nei dizionari dei dati.

ROLLBACK- Il comando ROLLBACK viene utilizzato per terminare l'intera transazione annullando le modifiche ai dati. Se la transazione contiene punti di salvataggio contrassegnati, ROLLBACK TO SAVEPOINT [nome] può essere utilizzato per eseguire il rollback della transazione solo fino al punto di salvataggio specificato. Di conseguenza, tutte le modifiche ai dati fino al punto di salvataggio specificato verranno eliminate.

Dimostrazione

Considera la tabella DIPENDENTI che viene popolata con i dettagli dei dipendenti appena assunti durante il primo trimestre di ogni anno. Il personale impiegatizio aggiunge ad ogni dettaglio dei dipendenti un punto di salvataggio, in modo da annullare in qualsiasi momento i dati difettosi durante l'attività di alimentazione dei dati. Nota che mantiene i nomi dei punti di salvataggio uguali a quelli dei dipendenti.

INSERT INTO employees (employee_id, first_name, hire_date, job_id, salary, department_id)
VALUES (105, 'Allen',TO_DATE ('15-JAN-2013','SALES',10000,10);

SAVEPOINT Allen;

INSERT INTO employees (employee_id, first_name, hire_date, job_id, salary, department_id)
VALUES (106, 'Kate',TO_DATE ('15-JAN-2013','PROD',10000,20);

SAVEPOINT Kate;

INSERT INTO employees (employee_id, first_name, hire_date, job_id, salary, department_id)
VALUES (107, 'McMan',TO_DATE ('15-JAN-2013','ADMIN',12000,30);

SAVEPOINT McMan;

Supponiamo che l'operatore di alimentazione dei dati si renda conto di aver inserito erroneamente lo stipendio di "Kate" e "McMan". Ripristina la transazione attiva al punto di salvataggio Kate e inserisce nuovamente i dettagli del dipendente per Kate e McMan.

ROLLBACK TO SAVEPOINT Kate;

INSERT INTO employees (employee_id, first_name, hire_date, job_id, salary, department_id)
VALUES (106, 'Kate',TO_DATE ('15-JAN-2013','PROD',12500,20);

SAVEPOINT Kate;

INSERT INTO employees (employee_id, first_name, hire_date, job_id, salary, department_id)
VALUES (107, 'McMan',TO_DATE ('15-JAN-2013','ADMIN',13200,30);

SAVEPOINT McMan;

Una volta terminato l'inserimento dei dati, può eseguire il commit dell'intera transazione emettendo COMMIT nella sessione corrente.

Leggi coerenza

Oracle mantiene la coerenza tra gli utenti in ogni sessione in termini di accesso ai dati e azioni di lettura / scrittura.

Quando si verifica un DML su una tabella, i valori dei dati originali modificati dall'azione vengono registrati nei record di annullamento del database. Finché la transazione non viene salvata nel database, qualsiasi utente in un'altra sessione che in seguito interroga i dati modificati visualizza i valori dei dati originali. Oracle utilizza le informazioni correnti nell'area globale del sistema e le informazioni nei record di annullamento per costruire una vista coerente in lettura dei dati di una tabella per una query. Solo quando viene eseguito il commit di una transazione, le modifiche della transazione diventano permanenti. La transazione è la chiave della strategia Oracle per fornire coerenza di lettura.

Il punto di inizio per visualizzazioni coerenti con la lettura viene generato per conto dei lettori

Controlla quando i dati modificati possono essere visti da altre transazioni del database per la lettura o l'aggiornamento