SQL Oracle
S.EUSCHI
TP n° 2
3 eme LMD SI
2017/2018
1
Changement dynamique de la description d’une
table
La commande alter table add ou modify
Ajouter une nouvelle colonne à une table existante,
ajouter une contrainte ou modifier une colonne
existante (type de données, longueur, contrainte
d’intégrité colonne,…)
Créer une table PROJ
create table PROJ
( PROJNO number(3) not null constraint PK_PROJ
primary key ,
PNAME varchar2(5),
BUDGET number(7,2)
)
2
Ajouter ces lignes dans PROJ
Insert into PROJ values(101,’ALPHA’,96000);
Insert into PROJ values(102,’BETA’,82000);
Insert into PROJ values(103,’GAMMA’,15000);
Commit; -- Valider la transaction
Select * from PROJ;
Ajouter une colonne PROJNO à la table EMP
alter table EMP add PROJNO number(3);
alter table EMP add constraint FK_PROJNO foreign key
(PROJNO) references PROJ (PROJNO) enable;
Affecter les employés aux projets
-- Les employés du dept 20 ont le projet 101
update EMP set PROJNO=101 where DEPTNO=20;
-- les autres employés au projet 102
update EMP set PROJNO=102 where DEPTNO!=20; 3
Afficher les employés avec leurs noms de projet
Select ENAME,JOB,DEPTNO,PNAME from EMP,PROJ
where EMP.PROJNO=PROJ.PROJNO;
Changer la valeur budget du projet 103 de 15000
à 105000
Update PROJ set BUDGET=105000 where
PROJNO=103;
Erreur : largeur de colonne insuffisante
Augmenter la taille de la colonne BUDGET
Alter table PROJ modify BUDGET number(8,2);
Update PROJ set BUDGET=105000 where
PROJNO=103;
1 row updated
Select * from PROJ;
4
Les vues (les requêtes storées)
Une vue est une fenêtre sur la table de la base de
données
Une vue est une table virtuelle, la table a une existence
physique mais la vue est une structure logique basée
sur une ou pls tables.
Les vues permettent de :
- Simplifier l’accès aux données (éviter d’interroger des
tables complexes)
- Sécuriser les données (limiter les accès aux données)
5
Exemple1 : créer une vue basée sur la table EMP donnant
le n°, nom et fonction des employés du département 10
:
create or replace view EMP10 as select EMPNO,
ENAME,JOB from EMP where DEPTNO=10;
select * from EMP10;
Exemple 2 : créer une vue jointure basée sur les tables
EMP et PROJ affichant le n°, nom employé et nom de
son projet : :
create or replace view PERSONNEL as select EMPNO,
ENAME,PNAME from EMP,PROJ where
EMP.PROJNO=PROJ.PROJNO;
select * from PERSONNEL;
6
Indépendance de données
Structure permettant des relations plusieurs à
plusieurs
Exemple : Un employé travaille dans pls projets, un
projet peut avoir plusieurs employés
EMP 0,n PROJ
0, n
#EMPNO Est affecté #PROJNO
Workhrs
Créer une table PE
create table PE
( EMPNO number(4) not null ,
PROJNO number(3) not null ,
WORKHRS number(4), 7
constraint PK_PE primary key(EMPNO,PROJNO));
Affecter les employés au projets:
insert into PE values(7369,101,0);
insert into PE values(7369,102,0);
insert into PE values(7499,101,0);
insert into PE values(7521,101,0);
insert into PE values(7566,101,0);
insert into PE values(7698,102,0);
….
Commit; -- valider les insertions
La colonne PROJNO de EMP est inutile
après la création de PE
update EMP set PROJNO = null;
Supprimer la vue PERSONNEL
drop view PERSONNEL;
8
Créer la nouvelle vue PERSONNEL avec la
table PE:
Create or replace view PERSONNEL as
select ENAME,JOB,PNAME from emp,
proj, pe where EMP.EMPNO=PE.EMPNO
and PE.PROJNO=PROJ.PROJNO;
Si on a un programme qui utilise la vue PERSONNEL,
il continue à s’exécuter normalement
Select * from PERSONNEL order by ENAME;
9
Partage de données et sécurité
SQL comporte des commandes LDD permettant:
• d’attribuer les privilèges d’accès sur des tables ou
des vues à d’autres utilisateurs (GRANT)
• De restituer des privilèges déjà attribués aux
utilisateurs (REVOKE)
Exemple1 : autoriser l’utilisateur SCOTT à
interroger la vue PERSONNEL
grant SELECT on PERSONNEL to SCOTT;
connect SCOTT/TIGER
select * from CoursBDA.PERSONNEL;
10
Exemple2 : autoriser tous les utilisateurs
d’interroger la table EMP à l’exception des
colonnes SAL et COMM
Créer une vue basée sur la table EMP :
create or replace view EMPS as select
EMPNO,ENAME,JOB,MGR,HIREDATE,DEPTNO
from EMP;
• Autoriser l’interrogation de cette vue par tous les
utilisateurs:
grant SELECT on EMPS to public;
La commande REVOKE
On peut restituer un privilège qu’on a déjà attribué à
un utilisateur
revoke SELECT on PERSONNEL from SCOTT;
11
Les privilèges objets
Privilèges Privilèges Privilèges Privilèges
table view procedure sequence
Select Select Execute alter
Insert Insert select
Update Update
Delete Delete
Alter
Index
12
