Gestione datiDML Statement

DML – Data Manipultion Language

Un’operazione DML viene eseguita quando:

• aggiungendo nuove righe ad una tabella

• modificando le righe esistenti di una tabella

• cancellando righe da una tabella

Una transazione consiste in un insieme di

statement DML che formano un’unità logica di

lavoro.

INSERT

Sintassi per inserire righe in una tabella

INSERT INTO tabella [(colonna,

[,colonna…])]

VALUES (value [,value]);

Con questa sintassi verrà inserita solo una riga

per volta

INSERT

• inserire valori per ciascuna colonna della

tabella

• la lista di valori deve comparire nell’ordine delle colonne nella tabella

• non è obbligatorio inserire la lista delle colonne nella clausola insert, per quanto sia consigliabile per chiarezza

• usare gli apici per inserire valori quali caratteri e date

INSERIMENTO DI VALORI NULLI

• Metodo implicito: omettere la colonna nela

lista INSERT INTO departments (department_id, department_name)

VALUES (30, ‘Nuovo dipartimento’);

• Metodo espicito : specificare NULL nella clausola VALUES

INSERT INTO departments

VALUES (30, ‘Nuovo dipartimento’, NULL, NULL);

ERRORI DI INSERIMENTO

• tentativo di inserire un valore nullo per una colonna definita NOT NULL

• duplicazione di valori che violano il constraint di unicità

• foreign key constraint violato

• violato constraint CHECK (assicurano l’integrità di una colonna limitando i valori accettati da una colonna)

• tipo di dato non coerente con la definizione della colonna

• valore troppo grande per la colonna

INSERT

È possibile utilizzare funzioni speciali per inserire valori nella tabella.

INSERT INTO employees (employee_id, first_name, last_name,

email, phone_number, hire_date,

jod_id, salary, commission_pct,

manager_id, department_id)

VALUES (113, ‘Luis’, ‘Popp’,

‘LPOPP’,’515.124.4567’, SYSDATE,

‘AC_ACCOUNT’, 6900, NULL,

205, 100);

INSERT

Esempio di inserimento campo data

INSERT INTO employees

VALUES (113, ‘Den’, ‘Raphealy’,

‘DRAPHELY’,’515.127.4561’,

TO_DATE(‘FEB 3, 1999’,’MON DD, YYYY’),

‘AC_ACCOUNT’, 11000, NULL, 205, 100);

Normalmente la data viene inserita nel formatoDD-MON-YY. Se deve essere inserita in un formato differente occorre usare la funzione TO_DATE per specificarne il formato.

INSERIMENTO DI PIÙ RIGHE

• scrivere uno statement INSERT con una subquery

• non usare la clausola VALUES

• scrivere la giusta corrispondenza tra i campi della tabella e quelli della subquery e verificare i data type

INSERT INTO sales_reps (id, name, salary, commission_pct)

SELECT employee_id, last_name, salary, commission_pct

FROM employees

WHERE job_id LIKE ‘%REP%’;

INSERT CON LE SUBQUERY

INSERT INTO (SELECT employee_id, last_name, email, hire_date, job_id, salary, department_id FROM employees WHERE department_id = 50)VALUES (99999, ‘Taylor’, ‘DTAYLOR’, TO_DATE(’07-JUN-99’,’DD-MON-YY’), ‘ST_CLERK’,500,50);

La select list della subquery deve avere lo stesso numero di colonne della lista valori nella clausola VALUES.

COPIA DI TABELLE

• mantenendo valide le regole della slide precedente per creare una copia della tabella usare SELECT *

INSERT INTO copy_emp

SELECT *

FROM employees;

MODIFICA DEI DATI IN UNA TABELLA

• usare lo statement UPDATE per modificare le righe in una tabella

UPDATE tableSET colonna = valore [, colonna = valore, …][WHERE condizione];

• se richiesto è possibile modificare più righe contemporaneamente (con lo stesso statement)

• utilizzare la primary key per isentificare una singola riga. Utilizzare le altre colonne può causare la modifica involontaria di più righe

MODIFICA DEI DATI IN UNA TABELLA

• specificare la riga o le righe che devono essere modificare con la clausola WHERE

UPDATE employeesSET department_id = 70WHERE employee_id = 113;

• omettendo la clausola WHERE verranno modificate tutte le righe della tabella

UPDATE copy_empSET department_id = 110;

MODIFICA COLONNE CON UNA SUBQUERY

• modificare job_id e salario dell’impiegato 114 con i dati dell’impiegato 205

UPDATE employeesSET job_id = (SELECT job_id FROM employees WHERE employee_id = 205), salary = (SELECT salary FROM employees WHERE employee_id = 205)WHERE employee_id = 114;

UPDATE

• è possibile modificare il campo di una tabella assegnado un valore proveniente da un’altra tabella

UPDATE copy_empSET department_id = (SELECT department_id FROM employees WHERE employee_id = 100)WHERE job_id = (SELECT job_id FROM employees WHERE employee_id = 200);

ERRORI: INTEGRITY CONSTRAINT

UPDATE employeesSET department_id = 55WHERE department_id = 110;

UPDATE employees *ERRORE at line 1:ORA-02291: integrity constraint (HR.EMP_DEPT_PK) violated -Parent key not found

si è cercato di modificare un record con un valore che viola il vincolo di integrità. Nella tabella DEPARTMENTS (tabella parent) non esiste il record 55

DELETE

È possibile cancellare righe da una tabella usando lo statement DELETE

DELETE [FROM] tabella[WHERE condizione;

NOTA: se nessuna riga viene cancellata viene restituito il messaggio “0 rows deleted”

DELETE

• specificare le righe da cancellare con la clausola WHERE

DELETE FROM departmentsWHERE department_name = ‘Finance’;

• tutte le righe della tabella verranno cancellate se si omette la clausola WHERE

DELETE FROM copy_emp;

DELETE

È possibile cancellare righe da una tabella usando i valori di un’altra tabella

DELETE FROM employeesWHERE department_id = (SELECT department_id FROM departments WHERE department_name LIKE ‘%Public%’);

ERRORI: INTEGRITY CONSTRAINT

DELETE FROM departmentsWHERE department_id = 60;

DELETE FROM departments *ERRORE at line 1:ORA-02292: integrity constraint (HR.EMP_DEPT_PK) violated -Child record found

si è cercato di modificare un record con un valore che viola il vincolo di integrità. Cercando di cancellare il dipartimento 60 ritorna un errore perché il numero di dipartimento è usato dalla foreign key della tabella employees

WITH CHECK OPTION

• una subquery è usata per identificare una tabella e le colonne di uno statement DML

• WITH CHECK OPTION indica che non è permesso fare modifiche (INSERT; UPDATE, DELETE) alle righe che non sono nella subquery

INSERT INTO (SELECT employee_id, last_name, email, hire_date, job_id, salary FROM employees WHERE department_id = 50 WITH CHECK OPTION)VALUES (99999, ‘Smith’, ‘JSMITH’, TO_DATE(’07-JUN-99’,’DD-MON-YY’), ‘ST_CLERK’,5000);

La subquery identifica le righe che sono del dipartimento 50, ma il campo department_id non è nella select list, quindi il valore non è fornito per esso nella VALUES list. Inserendo questa riga risulterebbe il department_id nullo ma non è così nella subquery

MERGE

• è possibile effettuare una insert o un’update in base a certe condizioni usando lo statement MERGE

MERGE INTO tabella alias_tabella INTO indica la tabella in cui verrà fatto l’insert o l’update

USING (tabella|vista|subquery) alias indica la sorgente dati per l’inserimento o l’update

ON (condizione di join) WHEN MATCHED THEN UPDATE SET col1 = col_val1, col2 = col2_val WHEN NOT MATCHED THEN INSERT (lista_colonne) VALUES (lista_valori);

MERGE

Esempio

MERGE INTO copy_emp cUSING employees eON (c.employee_id = e.employee_id) WHEN MATCHED THEN UPDATE SET c.first_name = e.first_name; c.last_name = e.last_name … c.department_id = e.department_id WHEN NOT MATCHED THEN INSERT VALUES(e.employee_id, e.first_name, e.last_name, e.email, e.phone_number, e.hire_date, e.job_id, e.salary, e.commission_pct, e.manager_id, e.department_id);

Inserisce o modifica i dati nella tabella copy_emp utilizzando i record della tabella employees