Base du SQL

Structured Query Langage

 plan
1. Syntaxe générale
2. Requêtes (LMD)
3. MAJ (LMD)
4. Langage de Définition des données
(DDL)
5. Introduction à PL/SQL de Oracle
6. Administration
1- Syntaxe générale
L’anglais est le langage du SQL?

SQL implémente les deux grands sous-ensemble

dû au modèle relationnel:
• Définition (LDD)
• Manipulation(LMD)
• Contrôle (DCL)
1- Syntaxe générale
Le langage de manipulation:
• Déclaratif et non procédurale(description des
étapes pas à pas).
• Composition du langage.
– Algèbre relationnelle+ Fonction-Agrégats+ Tri
1- Syntaxe générale
• Langage de Manipulation des Données (LMD)
Le langage de manipulation de données (Data
Manipulation Language : DML) permet de modifier les
données grâce à l'insertion (INSERT), la mise à
jour (UPDATE) , la suppression (DELETE) de n-uplets et
l’Interrogation (SELECT.
• Langage de définition de données (LDD)
Le LDD ou (Data Definition Language: DDL) se charge
de la définition du schéma d'une base de données.
1- Syntaxe générale
1- Syntaxe générale
Comment remplir les clauses ?
SELECT : Quel résultat souhaite voir
l’utilisateur (schéma du résultat) ?
FROM : Dans quelles relations sont les
attributs dont j’ai besoin ?
WHERE : optionnel
• des conditions sur les valeurs
d’attributs exprimées dans ma
requête ?
• plusieurs relations dans ma clause
FROM ? Si oui, quelles sont les
conditions de jointure(s) ?
1- Syntaxe générale
Base de données exemple: vins
Vins(num, cru, annee, degre)
Recoltes(nvin, nprod, quantite)
Producteurs(num, nom, prenom, region)
Clients(num, nom, prenom, ville)
Commandes(ncde, date, ncli, nvin, qte)
Livraisons(ncde, no_ordre, qteLivree)
2- Requêtes
Restriction et projection
2- Requêtes
Restriction et projection
2- Requêtes
Restriction et projection
Intervalle
2- Requêtes
LIKE

"Donner les vins dont le cru commence par p ou P"

SELECT *
FROM Vins
WHERE cru LIKE ‘p%’ or cru LIKE ‘P%’;
2- Requêtes
Tri
SELECT cru
FROM Vins
WHERE annee=1995 AND degre = 12
ORDER by cru [ASC | DESC];
2- Requêtes
Jointure
"Donner les noms des producteurs de
Pommard"
Exercice
2- Requêtes
Sous-requêtes
2- Requêtes
Sous-requêtes
Une sous-requêtes est une requête à l’intérieur
d’une requête. Les sous-requêtes peuvent être
dans le WHERE, FROM, ou SELECT.
• Lorsque la sous-requête renvoie une unique
valeur, la requête principale peut utiliser cette
valeur avec les opérateurs de comparaison
classiques
2- Requêtes
Sous-requêtes
Select *
From vins
Where degre >= (Select Max(degre)
From vins
where cru = ‘Pommard’
);
2- Requêtes
Sous-requêtes
Lorsque la sous-requête renvoie une colonne de
valeurs, la principale peut utiliser un opérateur
de comparaison classique mais accompagné soit
de all soit de ANY.
2- Requêtes
Sous-requêtes
Une sous requête peut renvoyer plusieurs
colonnes de valeurs, auquel cas elle peut être
utilisée( avec un alias de table) dans la clause
from de la requête principale.
2- Requêtes
Sous-requêtes
Select *
From
(Select num, cru, dgree
From vins
Where annee=2000 ) vins2000
Where cru = ‘mouton cadet’ ;
2- Requêtes
Jointure
"Donner les noms des producteurs de
Pommard"
SELECT nom

FROM vins V,
producteurs P,
recoltes R

WHERE cru = ‘Pommard’

And v.num = nvim
And P.num = nprod;
2- Requêtes
Jointure
Autre formes de jointure(syntaxe SQL2)
• Syntaxe plus proche de l’algèbre relationnelle
(directement exprimée dans le FROM). Supportée dans
plusieurs SGBD (>= Oracle 9, MySQL, SQLServer, …)

SELECT nom
FROM Vins V JOIN Recoltes R ON
(V.num = R.nvin) JOIN Producteurs P
ON (R.nprod = P.num)
WHERE cru = ‘Pommard’ ;
2- Requêtes
Jointure
Autres formes de jointure(procédurale)

SELECT nom
FROM Producteurs
WHERE num IN (
SELECT nprod
FROM Recoltes
WHERE nvin IN (
SELECT num
FROM Vins
WHERE cru = ‘Pommard’)) ;
 Exercice
Soient les tables suivantes :
Avions (no_av, nom_av, cap, loc)
Pilotes (no_pil, nom_pil, ville)
Vols (no_vol, no_av, no_pil, V_d, V_a, H_d, H_a)
Opérations :
1. Afficher les avions dont les localités se trouvent dans
une des villes des pilotes.
2. Afficher les avions qui ont effectué des vols dont la
ville de départ (V_d) se trouve parmi les localités des
pilotes.
3. Afficher les pilotes qui ont effectué des vols dans les
mêmes villes (V_d ou V_a) que les villes des avions.
2- Requêtes
Jointure
Auto-jointure : Jointure d’une relation avec elle-
même
« Donner les couples de producteurs produisant le
même vin »
SELECT P1.num, P2.num
FROM Producteurs P1, Producteurs P2,
Recoltes R1, Recoltes R2
WHERE P1.num = R1.nprod AND P2.num =
R2.nprod AND R1.nvin = R2.nvin
AND P1.num > P2.num ;
2- Requêtes
Opérateurs ensemblistes
Union (norme SQL1)
• Élimination automatique des doublons
SELECT nom FROM Producteurs
UNION
SELECT nom FROM Clients ;
2- Requêtes
Opérateurs ensemblistes
SELECT nom FROM Producteurs
INTERSECT
SELECT nom FROM Clients ;

Différence
SELECT nom FROM Clients
EXCEPT (ou MINUS)
SELECT nom FROM Producteurs
2- Requêtes
Fonctions –agrégats
Exemple
2- Requêtes
Fonctions
Fonctions prédéfinies : COUNT, SUM, MIN, MAX, AVG
Principe :
• S'applique à l'ensemble des valeurs d'un attribut d'une
relation
• Produit une valeur unique
• Pour une requête sans partionnement :
– uniquement dans le SELECT, jamais dans le WHERE
– Ne pas mélanger dans le SELECT les fonctions et les attributs simples!
2- Requêtes
Fonctions
Exemple 1 (avg)
« Donner la moyenne des degrés de tous les vins »
SELECT avg(degree)
FROM vins;
Exemple 2 (sum)
"Donner la quantité totale commandée par le client de nom
Some"
SELECT SUM(qte)
FROM Commandes, Clients
WHERE Clients.nom= ‘Some'
AND Clients.num=Commandes.ncli ;
2- Requêtes
Fonctions
Exemple 3 (count)
Donner le "Nombre de crus différents«

SELECT COUNT(DISTINCT cru)

FROM Vins ;

Exemple 4
Donner le "Nombre de vins«

SELECT COUNT (*) ou COUNT (num)

FROM vins
2- Requêtes
Fonctions
Exemple
Quel sont les " Vins dont le degré est supérieur à la moyenne des
degrés des vins"

SELECT *
FROM Vins
WHERE degre > (
SELECT AVG(degre)
FROM Vins)
;
2- Requêtes
2- Requêtes
Partionnement
2- Requêtes
Partionnement
Principe : « je veux des stats par ??? »
• Partitionnement horizontal d'une relation, selon les
valeurs d'un attribut ou d'un groupe d'attributs qui est
spécifié dans la clause GROUP BY
• Fonctions (sum, max,…)sur les groupes
• Restrictions sur les groupes
– Application possible d'un critère de restriction sur
les groupes
obtenus
– Clause HAVING
2- Requêtes
Partionnement
" Donner, pour chaque cru, la moyenne des degrés des vins de ce
cru …"
SELECT CRU, avg(degre)
FROM Vins
GROUP BY CRU
… avec un tri par degré décroissant"
SELECT cru, AVG(degre)
FROM Vins
GROUP BY cru
ORDER BY AVG(degree) DESC;
2- Requêtes
Partionnement
" Donner, pour chaque cru, la moyenne des
degrés des vins de ce cru, uniquement si ce
cru concerne plus de 3 vins"

SELECT cru, AVG(degre)

FROM Vins
GROUP BY cru
HAVING count(*) >= 3

Dans le HAVING, les conditions sont exprimés

sur des fonctions et pas sur des attributs.
2- Requêtes
Partionnement
Etape de Calcul du partionnement
1 Trier la relation selon les attributs de
groupement
2. Créer une sous-relation pour chaque
paquet ayant même valeur sur
l'ensemble des attributs de groupement,
ici « cru »
3. Calculer les fonctions (clause SELECT
ou HAVING ou ORDER BY) sur
chaque partition (dans notre exemple la
valeur de cru et la moyenne des degrés
sur la partition)
4. Appliquer la restriction du HAVING
5. Unifier les résultats
2- Requêtes
Partionnement
Exemple de requête erronée
SELECT cru, num, AVG(degre)
FROM Vins
GROUP BY cru ;

Résultats
2- Requêtes
Partionnement
Syntaxe
SELECT exp1, exp2, ... expn,
aggregate_function(aggregate_expression)
FROM tables
WHERE conditions
GROUP BY exp1, exp2, ... expn;
HAVING having_condition;
2- Requêtes
Prédicat
ALL
Teste si la valeur d’un attribut satisfait un critère de
comparaison avec tous les résultats d’une sous-requête
Exemple:
Quels sont les clients qui ont passé la plus grosse commande
SELECT Cl.num, Cl.nom
FROM Clients Cl, Commandes C
WHERE Cl.num = C.ncli
AND C.qte >= ALL(
SELECT qte
FROM Commandes) ;
2- Requêtes
Prédicat
ANY
Teste si la valeur d’un attribut satisfait un critère de comparaison
avec au moins un résultat d’une sous-requête

Exemple:

SELECT Cl.num, Cl.nom

FROM Clients Cl, Commandes C
WHERE Cl.num = C.ncli
AND C.qte > ANY(
SELECT qte
FROM Commandes) ;
3 - Mise à jour (LMD)
Le LMD permet d’ajouter/modifier/supprimer
(mise à jour) les données dans la base de
données.

On peut remplir automatiquement la base de

données, sous la forme d'instructions écrites
dans un fichier.
3 - Mise à jour (LMD)
Insertion de données
Une insertion de n-uplets a la syntaxe suivante :

INSERT INTO <table> [(<liste

d'attributs>)] VALUES (<liste de
valeurs>) | <requete>

On peut préciser la liste des attributs à renseigner :

les autres valeurs sont alors fixées soit à la valeur
par défaut de l'attribut, soit à NULL.
 3 - Mise à jour (LMD)
Insertion de données
Exemple.
• INSERT INTO VINS VALUES (100,
‘JURANÇON’,1987,12);

• INSERT INTO VINS(NUM, CRU, ANNEE, DEGREE)

VALUES (100, ‘JURANÇON’,1987,12);

• INSERT INTO VINS(NUM, CRU) VALUES

(200,’GAMMAY’);
 3 - Mise à jour (LMD)
Insertion de données
La clause VALUES ajoute un n-uplet à la fois alors
que l'utilisation d'une requête à la place de
VALUES permet d'insérer plusieurs n-uplets en
une seule opération.
INSERT INTO <table> [(<liste
d'attributs>)] <requete>
 3 - Mise à jour (LMD)
Insertion de données
CREATE TABLE BORDEAUX(num NUMBER, annee
DATE, degre NUMBER(4,2)) ;

INSERT INTO BORDEAUX

SELECT num, annee, degre
FROM Vins
WHERE cru = 'Bordeaux' ;
3 - Mise à jour (LMD)
Modification
L'instruction UPDATE modifie les valeurs des n-
uplets d'une table :
UPDATE <table> SET <liste
d'affectations> [WHERE
<condition>].
L'utilisation d'une liste de valeurs permet de
modifier la valeur de plusieurs attributs. La
clause WHERE définit les n-uplets qui seront mis
à jour.
3 - Mise à jour (LMD)
Mise à jour
Exemple
« Le producteur 150 habite maintenant dans le
sahel »
UPDATE Producteurs
SET region = ‘Sahel'
WHERE num = 150 ;

Producteurs(num, nom, prenom, region)

3 - Mise à jour (LMD)
Mise à jour
Exemple
« Les degrés des Gamays augmentent de 10 % »
UPDATE Vins
SET degre = degre * 1.1
WHERE cru = 'Gamay' ;
3 - Mise à jour (LMD)
Mise à jour
Exercice
Les clients de nom « Diallo » augmente les
quantités de leurs commandes de 10 unités.
Ecrire la commande qui permet de faire cette
Maj.
3 - Mise à jour (LMD)
Suppression
L'instruction DELETE supprime des n-uplets
d'une table :

DELETE FROM <table> [WHERE <condition>].

La clause WHERE précise les n-uplets à
supprimer. Sans elle, tout le contenu de la table
est supprimé.
3 - Mise à jour (LMD)
Suppression
Exemple
• Supprimer tous les tuples de Vins
DELETE FROM Vins ;

• Supprimer le vin de numéro 150

DELETE FROM Vins WHERE num = 150
;
3 - Mise à jour (LMD)
Suppression
Exemple:
• "Supprimer les vins de degré inférieur à 9 ou
supérieur à 12 »
• "Supprimer les commandes passées par
Mohamed et Hamidou"
4 - Définition de données (LDD)
Le langage de définition de données est la partie
de SQL qui permet de décrire les tables et autres
objets du SGBD.
Il propose trois opérations :
• la création (CREATE),
• la suppression (DROP) et
• la modification (ALTER).
4 - Définition de données (LDD)
Création de table
La création d'une table a pour syntaxe :
CREATE TABLE nom_table (
colonne1 type [contrainte],
colonne2 type [contrainte],
…
colonneN type [contrainte]
);
Il permet de créer et de définir une table
4 - Définition de données (LDD)

Création de table
Chaque définition de colonne comporte le
nom de la colonne, son type et
éventuellement une spécification de
contraintes d'intégrité (not null).
4 - Définition de données (LDD)
Création de table
Les types de données sont :
Chaînes de caractères
• Char : un seul caractère;
• Char(n) : chaîne de longueur fixe n ( 1 ≤ n ≤
2000)
• Varchar2(n) : chaîne de longueur variable n ie
ajustée au contenu (1 ≤ n ≤ 4000).
4 - Définition de données (LDD)
Création de table
Nombre
• number(p) : entier à p chiffres (1 ≤ p ≤ 38)
• number(p,s) : nombre réel à virgule fixe de p
chiffres dont s chiffres décalés après la virgule
(1 ≤ p ≤ 38, -84 ≤ s ≤ 127):
• number : nombre réel à virgule flottante avec
environ 16 chiffres significatifs.
4 - Définition de données (LDD)
Création de table
Date et heure
Date : entier décrivant le nombre de secondes
écoulées depuis le 01/01/-4712 0:00:00.
Et va jusuq’au 31/12/9999.
Sysdate: fonction qui retourne la date et l’heure
courante du serveur oracle
 4 - Définition de données (LDD)
Création de table
Format de la date:
to_date : fonction permet de convertir date en
format (yyyymmdd h:m:s) chaîne
compréhensible par l’homme.
TO_DATE('2003/07/09', 'yyyy/mm/dd')
4 - Définition de données (LDD)
Création de table
Exemple
CREATE TABLE etudiant (
Etud_id number(10) NOT NULL,
Etud_name varchar2(50) NOT NULL,
city varchar2(50)
);
4 - Définition de données (LDD)
Modification d’une table
Une table qui existe déjà peut être modifiée par
l’intruction ALTER TABLE accompagné d’une clause
ADD ou MODIFY
Syntaxe:
ALTER TABLE nomtable MODIF(col1
type1, col2 typ2,…)
Col1, col2,… sont les noms des colonnes que l’on veut
modifier. Elle doivent déjà exister dans la table.
Type1, typ2 sont les nouveau types que l’on veut
attribuer au colonnes.
4 - Définition de données (LDD)
Modification d’une table
Syntaxe:
ALTER TABLE nomtable ADD (col1
type1, col2 typ2, …)
Cette instruction permet d’ajouter une ou
plusieurs colonnes à une table existante.
L’attribut ‘not null’ n’est possible que si la table
est vide sinon la nouvelle colonne sera nulle
dans ces lignes existante
4 - Définition de données (LDD)
Modification d’une table
On peut supprimer une colonne d’une table.
Synthaxe:
ALTER TABLE nomtable DROP COLUMN
nomcolonne;

Renommer une colonne

ALTER TABLE table_name RENAME COLUMN
old_name to new_name; Cette instruction permet
de renommer une colonne d’une table
4 - Définition de données (LDD)
Suppression de table
On peut supprimer une table avec l’instruction
DROP TABLE nomtable ;
C’est toute table (schéma et données) qui est
détruite. L’espace occupé par la table est libéré.
4 - Définition de données (LDD)
Suppression de table
• Select table_name from
user_tables
Cette instruction permet à l’utilisateur d’Oracle
de retrouver toutes ses tables.
• Desc[ribe] nomtable
Voir les colonnes d’une table.
4 - Définition de données (LDD)
Vues ou View
On peut enregistrer une requête SELECT en tant
que vue. Les utilisateurs peuvent consulter ou
modifier la base à travers les vues: C’est-à-dire
manipuler la table résultat du select comme si
c’était une table réelle.
Une vue est une table virtuelle qui n’existe pas
physiquement car seule le schéma est enregistré
et pas les données.
4 - Définition de données (LDD)
Vues ou View
Syntaxe de création d’une vue:
CREATE VIEW nomVue AS
SELECT colonne
from tables
where conditions;

DROP VIEW nomVue;

Supprime la vue nomVue
 4 - Définition de données (LDD)
Types de Clé
• La clé primaire d'une table est un ensemble
d'attributs (souvent un seul attribut). Une
combinaison de valeurs de ces attributs détermine
au plus un n-uplet de la table.
• Une table peut avoir plusieurs clés. On dira d'une clé
d'une table T qu'elle est une clé primaire si elle est
utilisée dans d'autres tables pour identifier des n-
uplets de T. (Les autres clés seront dites clés
candidates.)
Le langage de définition de données (LDD)

Types de clé
• On appelle clé étrangère un ensemble d'attributs
d'une table Tb faisant référence à la clé primaire de
la table Ta.
 4 - Définition de données (LDD)
Contrainte d’intégrité
• Le langage DDL permet de spécifier qu'un ensemble
d'attributs est clé primaire (PRIMARY KEY), qu'une
séquence d'attributs est clé étrangère (FOREIGN
KEY/REFERENCES).
• Il permet aussi de contraindre le domaine d'un attribut
(CHECK) ou de vérifier qu'un ensemble d'attributs ne
contient pas de valeur manquante (NOT NULL).
• il permet, de préciser qu'un ensemble d'attributs ne
contienne pas de doublons (UNIQUE) ; à la différence de
PRIMARY KEY, il est possible de spécifier plusieurs
UNIQUE sur la même table.
 4 - Définition de données (LDD)
Contrainte d’intégrité (clé)
Exemple:
CREATE TABLE coordonnees(
salle VARCHAR(30) PRIMARY KEY,
adresse VARCHAR(255) UNIQUE NOT
NULL,
telephone VARCHAR(10)
);
L'attribut salle est la clé primaire donc annoté par
PRIMARY KEY. Les contraintes UNIQUE et NOT NULL sur
adresse précisent que cet attribut est une clé
candidate.
 4 - Définition de données (LDD)
Primary keys
Une clé primaire peut être définie lors de la
création de la table (CREATE) ou en modification
(ALTER TABLE)
Une clé primaire peut contenir 32 colonnes
(Oracle)
 4 - Définition de données (LDD)
Primary keys
Syntaxe:
CREATE TABLE table_name (
column1 datatype null/not null,
column2 datatype null/not null,
...
CONSTRAINT constraint_name PRIMARY KEY
(column1, column2, ... column_n)
);
 4 - Définition de données (LDD)
Primary keys
Exemple
CREATE TABLE supplier (
supplier_id numeric(10) not null,
supplier_name varchar2(50) not null,
contact_name varchar2(50),
CONSTRAINT pk_supplier PRIMARY KEY
(supplier_id)
);
 4 - Définition de données (LDD)
Primary keys
Exemple avec de clé p. sur 2 colonnes
CREATE TABLE supplier (
supplier_id numeric(10) not null,
supplier_name varchar2(50) not null,
contact_name varchar2(50),
CONSTRAINT pk_supplier PRIMARY KEY
(supplier_id, supplier_name)
);
 4 - Définition de données (LDD)
Primary keys
Pour créer la clé primaire après création de la table
Syntaxe:
ALTER TABLE table_name
ADD CONSTRAINT constraint_name PRIMARY KEY
(column1, column2, ... column_n);
Exemple :
ALTER TABLE supplier ADD CONSTRAINT
supplier_pk PRIMARY KEY (supplier_id);
 4 - Définition de données (LDD)
Primary keys
Suppression . on utilise ALTER TABLE
Syntaxe :
ALTER TABLE table_name DROP CONSTRAINT
constraint_name;
Exemple:
ALTER TABLE supplier DROP CONSTRAINT pk_supplier;
 4 - Définition de données (LDD)
Primary keys
Désactiver/activer
On peut désactiver une clé primaire sans la supprimer
avec le mot DISABLE
Syntaxe:
ALTER TABLE table_name DISABLE CONSTRAINT
constraint_name;
Exemple :
ALTER TABLE supplier
DISABLE CONSTRAINT pk_supplier;
 4 - Définition de données (LDD)
Primary keys
Désactiver/activer
On peut activer une clé primaire désactivée avec le mot
ENABLE
Syntaxe:
ALTER TABLE table_name ENABLE CONSTRAINT
constraint_name;
Exemple :
ALTER TABLE supplier
ENABLE CONSTRAINT pk_supplier;
 4 - Définition de données (LDD)
FOREIGN KEYS
Une clé etrangère d’une table (fille) référence une clé
primaire d’une table (parent)
Syntaxe:
CREATE TABLE table_name (
column1 datatype null/not null,
column2 datatype null/not null,
...
CONSTRAINT fk_column FOREIGN KEY
(column1, column2, ... column_n)
REFERENCES parent_table (column1,
column2, ... column_n) );
FOREIGN KEYS
CREATE TABLE SUPPLIER (
supplier_id number(10) not null,
supplier_name varchar2(50) not null,
contact_name varchar2(50),
CONSTRAINT supplier_pk PRIMARY KEY (supplier_id));

CREATE TABLE PRODUCTS (

product_id number(10) not null,
supplier_id number(10) not null,
CONSTRAINT fk_supplier FOREIGN KEY (supplier_id)
REFERENCES SUPPLIER (supplier_id));
 4 - Définition de données (LDD)
FOREIGN KEYS
La clé étrangère peut être définie après création de
la table
Syntaxe
ALTER TABLE table_name ADD
CONSTRAINT constraint_name FOREIGN
KEY (column1, column2, ... column_n)
REFERENCES parent_table (column1,
column2, ... column_n);
 4 - Définition de données (LDD)
FOREIGN KEYS
Exemple:
ALTER TABLE products ADD CONSTRAINT
fk_supplier FOREIGN KEY (supplier_id,
supplier_name) REFERENCES supplier(supplier_id,
supplier_name);
 4 - Définition de données (LDD)
FOREIGN KEYS
Clé étrangère avec option DELETE CASCADE
Par défaut la suppression d’une ligne sur la table
parent n’est pas possible car référencé par une
autre table.
Une clé étrangère avec l’option CASCADE DELETE
signifie que si une ligne de la table parent est
supprimée alors la ligne correspondante de la table
fille est automatiquement supprimée. On appelle
suppression en cascade.
 4 - Définition de données (LDD)
FOREIGN KEYS
Clé étrangère avec option DELETE CASCADE
Syntaxe:
CREATE TABLE table_name (
column1 datatype null/not null, column2 datatype
null/not null,
...
CONSTRAINT fk_column FOREIGN KEY (column1,
column2, ... column_n) REFERENCES parent_table
(column1, column2, ... column_n) ON DELETE
CASCADE );
Ou
ALTER TABLE table_name ADD CONSTRAINT
constraint_name FOREIGN KEY (column1, column2,
... column_n) REFERENCES parent_table (column1,
column2, ... column_n) ON DELETE CASCADE;
 4 - Définition de données (LDD)

FOREIGN KEYS
Clé étrangère avec l’option ON DELETE SET NULL
Une clé étrangère avec l’option ON DELETE SET
NULL signifie que la suppression d’une ligne de la
table parent (référencée) entraine que le champ
correspondant dans la table fille sera mis à NULL la
le ligne de la table fille n’est pas supprimer.
FOREIGN KEYS
Clé étrangère avec l’option ON DELETE SET NULL
CREATE TABLE table_name (
column1 datatype null/not null,
column2 datatype null/not null,
...
CONSTRAINT fk_column FOREIGN KEY (column1,
column2, ... column_n) REFERENCES parent_table
(column1, column2, ... column_n) ON DELETE SET
NULL
);
 4 - Définition de données (LDD)
Contrainte d’intégrité (check)
La contrainte check permet de s’assurer que les
données saisies dans une colonne sont dans une
plage de valeurs définie à l’avance.
Syntaxe
CREATE TABLE table_name (
column1 datatype null/not null,
column2 datatype null/not null,
...
CONSTRAINT constraint_name
CHECK(column_name condition)
[DISABLE]
);
Disable est optionnel, crée une contrainte désactivée
4 - Définition de données (LDD)
Contrainte d’intégrité (check)
Exemple
ALTER TABLE vins ADD CONSTRAINT
ck_degre CHECK (degree >= 0);
-ajouter un colonne et sa contrainte
ALTER TABLE clients ADD (genre
CHAR CONSTRAINT ck_genre CHECK
(genre in (‘M’, ‘F’));
Contrainte d’intégrité (check)
• Pour supprimer une contrainte.
ALTER TABLE table_name DROP CONSTRAINT
constraint_name;
ALTER TABLE vins DROP CONSTRAINT
ck_degre;
• Pour activer/desactiver une contraine
ALTER TABLE table_name ENABLE CONSTRAINT
constraint_name;
ALTER TABLE table_name DISABLE CONSTRAINT
constraint_name;
 4 - Définition de données (LDD)

Contrainte d’intégrité (check)

Exemple
• Pour activer/desactiver une contraine
ALTER TABLE vins ENABLE CONSTRAINT
ck_degree;
ALTER TABLE vins DISABLE CONSTRAINT
ck_degree;
5 – Contrôle des données
Gestion des privilèges
On peut accorder différents types et différents
niveaux de privilèges aux utilisateurs sur les
tables (objets).
Ces privilèges peuvent être toute combinaison
de : SELECT, INSERT, UPDATE, DELETE,
REFERENCES, ALTER, INDEX, or ALL.
5 – Contrôle des données
Gestion des privilèges
La syntaxe pour accorder des privilèges est:

GRANT privileges ON objet TO

utilisateur;

Objet: le nom de l’objet de la base de données sur

lequel on veut accorder des privilèges.

Utilisateur: le nom de l’utilisateur qui bénéficiera

des privilèges.
5 – Contrôle des données
Grant
Exemple
GRANT SELECT, INSERT, UPDATE,
DELETE ON vins TO Issa;
• Les privileges select, insert, update et delete
sur la table vins sont accordés à l’utilisateur
Issa.
GRANT ALL ON clients TO ISSA
• Tous les privilèges sont accordés à ISSA
5 – Contrôle des données
Grant
Exemple
GRANT ALL ON clients FROM public;
Le mot-clé public permet d’attribuer des
privilèges à tous les utilisateurs.
5 – Contrôle des données
Revoke
De façon manière inverse, on peut révoquer
tout ou une partie des privilèges accordées à un
utilisateur avec le mot-clé revoke.
REVOKE privileges ON objet FROM
utilisateur;
5 – Contrôle des données
Revoke
Exemple:
REVOKE DELETE ON clients FROM Issa;
REVOKE ALL ON clients FROM public;
 SPQlus
1- Connexion au compte utilisateur
 Connect utilisateu
 Password: xxxxxx

2- Afficher les définitions des colonnes d’une

table
 describe nom_table;
Bien vouloir préparer sa session de travail en
exécutant les commandes suivantes :
SET PAGES[IZE] 50000 (taille d’une page)
SET LIN[ESIZE] 32767 (taille d’une ligne)
HO[ST] MD c:\tpadmin (vérifier que le
répertoire c:\tpadmin a été créé)
SPO[OL] c:\tpadmin\tp1.txt
A la fin de la séance, exécuter la commande SPO
OFF et récupérer le fichier tp1.txt
 SPQlus
3- Afficher toutes les tables:
Select TABLE_NAME from USER_TABLES;

Exécuter un fichier sql (contenant des

instructions sql) avec @
– @nom_fichier.sql (répertoire courant)
– @chemin_fichier nom_fichier.sql (autre
répertoire)
 SPQlus
4- Exécuter un fichier sql (contenant des
instructions sql) avec START
Start nom_fichier.sql (répertoire courant)
Start chemin_fichier nom_fichier.sql (autre
répertoire)
sql> start C:\Users\USER\Desktop\myscript.sql
 SQLPlus
5- Enregistrer les commandes sql exécutées
dans slqplus dans un fichier
save filename [create|append]
Append: ajoute dans le filename,
Create : crée un nouveau filename
 Programmation Oracle
• Procédure stocké

• PL/SQL (Procedural langage/SQL ) est une

extension procédural de SQL en langage de
programmation
• Les manipulation des données et réquêtes SQL
sont incluses dans du code procédural
 4 Programmation Oracle
• Avantages de PL/SQL
 4 Programmation Oracle
Structure
Comme dans tout langage procédural, le code
est formé de blocs. Chaque bloc comprend une
section de déclaration de variables, et un
ensemble d’instruction.
 4 Programmation Oracle
Structure
DECLARE
/*déclaration de variables: optionnelle */
BEGIN
/*instructions sql et pl/sql à exécuter */

EXCEPTION
/*Actions à faire en cas d’erreur: optionnel*/
END;
 4 Programmation Oracle
Variable
Une variable se déclare comme suit:
nom type := [initialisation];
L’initialisation est optionnelle. Les types utilisés
sont les mêmes que dans les tables.
 4 Programmation Oracle
Variable
Exemple
DECLARE
c varchar2 (15) := ' Hello World ! ' ;
BEGIN
DBMS_OUTPUT.PUT_LINE( c ) ;
END;
/
Un script écrit en PL/SQL se termine obligatoirement
par un /, sinon SQL+ ne l'interprète pas.
 4 Programmation Oracle
Affichage
Pour afficher le contenu d'une variable, les
procédures DBMS_OUTPUT.PUT() et
DBMS_OUTPUT.PUT_LINE() prennent en
argument une valeur à afficher ou une variable.
Dans SQL+, on peut activer ou désactiver
l’affichage avec la commande
SET SERVEROUTPUT ON (active)
SET SERVEROUTPUT OFF (désactive)
 4 Programmation Oracle
Traitement conditionnel
IF /*condition * /
THEN
/* instructions1* /
ELSE
/*instructions2 */
END IF ;
 4 Programmation Oracle
Traitements répétitifs ou itération
LOOP
/*Instructions*/
END LOOP;
On peut quitter la boucle à condition
LOOP
/*instructions*/
EXIT WHEN /*condition*/ ;
END LOOP;
 4 Programmation Oracle
Traitements répétitifs ou itération
La boucle FOR existe aussi en PL/SQL
FOR /*variable*/ IN /* debut*/ .. /*fin*/
LOOP
/*instructions*/
END LOOP;
 4 Programmation Oracle
Traitements répétitifs ou itération
La boucle while aussi
WHILE /*Condition*/
LOOP
/*instructions*/
END LOOP;
 4 Programmation Oracle
Tableau
Les types tableau doivent être définis explicitement par
une déclaration de la forme:
TYPE /*nomype*/ IS VARRAY (/*taille*/ ) OF /* type
Elements*/ ;
• Type: nom du type tableau;
• Taille : le nombre maximal d’éléments
• TypeElements : type des éléments qui vont être
stockés dans le tableau.
Type numTab IS VARRAY (10) of NUMBER;
 4 Programmation Oracle
Tableau
DECLARE
TYPE numTab IS VARRAY (10) OF NUMBER;
t numTab ;
BEGIN
t = numTab();
/*instructions* /
END;
/
Le type d’un tableau peut être utilisé au même titre
que number ou varchar2.
 4 Programmation Oracle
Interaction avec BD
Les scripts PL/SQL permet d’interagir avec
une base de données.
 4 Programmation Oracle
Interaction avec BD
INTO
On place dans une variable le résultat
d’une réquête en utilisant INTO.
SELECT champ1, ..., champn INTO v1,
..., vn
FROM . . .

Affect e aux variable v1, … vn les valeurs retournées par

la requête.
 4 Programmation Oracle
Interaction avec BD
INTO
DECLARE
nomclt varchar2(30);
numcmd number(6) := 345 ;
BEGIN
SELECT nom INTO nomclt
FROM commandes clients
WHERE ncde = numcmd and ncli=num;
DBMSOUTPUT.PUT_LINE( 'L a commande ' ||
numcmd || ‘est passée par le client ’ || nomclt) ;
END;
/
 4 Programmation Oracle
Interaction avec BD
%type
il est possible de référer directement au
type d’une colonne avec le %type :
Syntaxe
nomTable.nomColonne%type
Exemple
nomclt clients.nom%type ;
numcmd commandes.ncde%type := 345 ;
 4 Programmation Oracle
Interaction avec BD
%rowtype
Il est même possible de déclarer une structure
pour représenter une ligne d'une table, le type
porte alors le nom suivant :
nomTable%rowtype.
 4 Programmation Oracle
Transaction
Le système des transactions est l’un des
mécanismes les plus puissants des SGBD
récents.
Une transaction est un ensemble d'opérations
« atomiques », c'est-a-dire indivisible.
 4 Programmation Oracle
Transaction
On considère qu'un ensemble d'opérations est
indivisible si une exécution partielle de ces
instructions poserait des problèmes d'intégrité
dans la base de données.
Par exemple, dans le cas d'une base de donnees
de gestion de comptes en banque, un virement
d'un compte a un autre se fait en deux temps.
Débiter et créditer.
 4 Programmation Oracle
Transaction
Les instructions COMMIT et ROLLBACK permet
d’assurer l’exécution d’un ensemble d’opérations
/*Instruction*/
IF /*Erreur*/ THEN ROLLBACK annule toutes les
modifications faites depuis le
ROLLBACK; début de la transaction (depuis le
ELSE précèdent
COMMIT; COMMIT),
END; COMMIT les enregistre
définitivement dans la base de
données.
 4 Programmation Oracle
Transaction
La variable d'environnement AUTOCOMMIT,
permet d'activer la gestion des transactions.
AUTOCOMMIT ON: chaque instruction a des
répercussions immédiates dans la base.
AUTOCOMMIT OFF: les modifications ne sont
effectives qu'une fois qu'un COMMIT a été
exécute.
 4 Programmation Oracle
Un sous-programme PL/SQL (subprogram)
est un bloc PL/SQL nommée qui peut être
appelée plusieurs fois. Si le sous programme
prend des paramètres, leurs valeurs peuvent
être différentes à chaque invocation.
Un subprogram est une procédure ou une
fonction.
 4 Programmation Oracle
Procedure
On définit une procédure de la façon suivante:
CREATE [OR REPLACE] PROCEDURE proc_name
[ (parameter [,parameter]) ] IS
/*Declaration de variable!*/
BEGIN
/*Instruction*/
END;
 4 Programmation Oracle
Procedure
Exemple
CREATE OR REPLACE PROCEDURE compteARebours (n
NUMBER) IS
BEGIN
IF n >= 0 THEN
DBMS_OUTPUT.PUT_LINE(n ) ;
compteARebours (n - 1 ) ;
END IF ;
END;
 4 Programmation Oracle
Procedure
On appelle de la procédure avec son nom.
Dans sql+, on utilise le mot-clé call.
SQL> call compteARebours(20)

Supprimer
DROP PROCEDURE procedure_name;
 4 Programmation Oracle
Fonction
On définit une fonction de la façon suivante:
CREATE [OR REPLACE] FUNCTION nom_funct [
(parameter [,parameter]) ]
RETURN type_retourne IS |
/*Déclaration de variable*/
BEGIN
/*instructions*/
END [function_name];
 4 Programmation Oracle
Fonction
Exemple
CREATE OR REPLACE FUNCTION module ( a NUMBER, b
NUMBER) RETURN NUMBER IS
BEGIN
IF a < b THEN
RETURN a ;
ELSE
RETURN module ( a - b , b ) ;
END IF;
END;
 4 Programmation Oracle
Fonction
On appelle les fonctions en PL/SQL de la même
manière que les procédures.
En SQL+, on passe par une pseudo-table nommée
DUAL de la façon suivante:
SELECT module (21,12) FROM DUAL;
 4 Programmation Oracle
Triggers ou déclencheurs
Principe
Un trigger est comme une procédure stockée dans
la base, déclenché lors de l’occurrence d’un
événement particulier.
La plupart du temps les triggers sont déclenchés
par la modification du contenu d’une table.
 4 Programmation Oracle
Package
Principe
Un package est un module de programmes incluant
procédures et / ou fonctions fonctionnellement
dépendantes.
Un package est composé de 2 parties :
• la spécification (introduite par ‘CREATE PACKAGE’) liste les
entêtes de procédures et fonctions contenues dans le
package,
• le corps du package (introduit par ‘CREATE PACKAGE
BODY’) qui contient le code effectif des procédures et
fonctions déclarées précédemment.