BDA - MIAGE - Part2 - 2020
BDA - MIAGE - Part2 - 2020
BDA - MIAGE - Part2 - 2020
Avancées
M. ZOUHRI
INSERT
INSERT INTO
INTO table
table [(column
[(column [,
[, column...])]
column...])]
VALUES
VALUES (value
(value [,
[, value...]);
value...]);
1 row created.
Copie de Lignes d'une autre Table
◦ Ecrivez votre ordre INSERT en spécifiant une sous-
interrogation.
SQL> INSERT INTO managers(id, name, salary, hiredate)
2 SELECT empno, ename, sal, hiredate
3 FROM emp
4 WHERE job = 'MANAGER';
3 rows created.
UPDATE
UPDATE table
table
SET
SET column
column == value
value [,
[, column
column == value]
value]
[WHERE
[WHERE condition];
condition];
em
art
ép
d
UPDATE
UPDATE emp
emp de
o
** ér
ERROR
ERROR at
at line
line 1:u
1:m
n
ORA-02291:
L e
ORA-02291: integrity
integrity constraint
constraint (USR.EMP_DEPTNO_FK)
(USR.EMP_DEPTNO_FK)
violated
violated -- parent
parent key
key not
not found
found
L‘ordre DELETE
DELETE
DELETE [FROM]
[FROM] table
table
[WHERE
[WHERE condition];
condition];
Suppression de Lignes d'une Table
◦ La clause WHERE permet de supprimer une ou
plusieurs lignes spécifiques.
SQL>
SQL> DELETE
DELETE FROM
FROM department
department
22 WHERE
WHERE dname
dname == 'DEVELOPMENT';
'DEVELOPMENT';
11 row
row deleted.
deleted.
SQL>
SQL> DELETE
DELETE FROM
FROM department;
department;
44 rows
rows deleted.
deleted.
Suppression de Lignes :
Erreur de Contrainte d'Intégrité
ui
e q
g n clé
l i e
e
n mm
u o
SQL>
SQL> DELETE
DELETE FROM
FROM dept
dept er e c
22 WHERE deptno
deptno == 10; ri m i sé
WHERE 10;
p p ti l .
su e u bl e
as air ta
z p im tre
DELETE
DELETE FROM
FROM dept
dept
v e p r au
**
ou clé ne
ERROR
ERROR at
at line
line 1:1:e p ne s u
s n u constraint an
ORA-02292:
ORA-02292: integrity
integrity
u n t d
constraint (USR.EMP_DEPTNO_FK)
(USR.EMP_DEPTNO_FK)
violated --Vo
violated child
childt i e record
r
recorde foundfound
o n g è
c ran
ét
Transactions de Base de Données
ROLLBACK to Savepoint B
ROLLBACK to Savepoint A
ROLLBACK
Traitement Implicite des Transactions
SQL> UPDATE...
SQL> SAVEPOINT update_done;
Savepoint created.
SQL> INSERT...
SQL> ROLLBACK TO update_done;
Rollback complete.
Rollback au Niveau Ordre
◦ Si un seul ordre du LMD dans la transaction
échoue, seul cet ordre est annulé.
◦ Oracle met en œuvre un savepoint implicite.
◦ Toutes les autres modifications sont conservées.
◦ L'utilisateur doit terminer explicitement les
transactions en exécutant un ordre COMMIT ou
ROLLBACK.