École Nationale des Sciences Informatiques

SGBD
II2
KHCHERIF Raoudha
[Link]@[Link]
Bureau : 208
 Plan du cours
I. Installation d’oracle
II. TP’s sur Le Langage de définition de
Données
III. TP’s sur Le Langage de manipulation de
données
IV. Le Langage PL/SQL, TP’s

KHCHERIF Raoudha SGBD 2

 Oracle 10g
• Creation d’utilisateur
– Se connecter en tant que SYS/ORACLE
– Sys et System sont deux utilisateurs privilégiés d’oracle
ils ont le rôle DBA ( Administrateur)
– SQL>Create user II2 identified by motpass;
– SQL>Grant connect to ii2;
– SQL>Grant resource to ii2;
– SQL>Connect ii2/motpass
– SQL> Show user;
– NB: Ne pas travailler avec le compte SYS et SYSTEM
KHCHERIF Raoudha SGBD 3
 I. Architecture d’un SGBD
Définitions:
BD: Collections de données cohérentes et
structurées
Système de Gestion de Bases de Données
(SGBD) : Logiciel(s) assurant structuration,
stockage, maintenance, mise à jour et
consultation des données d’une BD
KHCHERIF Raoudha SGBD 4
I.1 Objectifs des SGBD
• Indépendance physique : un remaniement de
l’organisation physique des données n’entraîne pas
de modification dans les programmes
d’application (traitements)

• Indépendance logique : un remaniement de

l’organisation logique des fichiers (ex. nouvelle
rubrique) n’entraîne pas de modification dans les
programmes d’application non concernés
KHCHERIF Raoudha SGBD 5
I.1 Objectifs des SGBD
• Manipulation facile des données : un utilisateur
non-informaticien doit pouvoir manipuler
simplement les données (interrogation et mise à
jour)
• Administration facile des données : un SGBD
doit fournir des outils pour décrire les données,
permettre le suivi de ces structures et autoriser
leur évolution (tâche de l’administrateur BD)

KHCHERIF Raoudha SGBD 6

I.1 Objectifs des SGBD
• Efficacité des accès aux données : garantie d’un
bon débit (nombre de transactions exécutées par
seconde) et d’un bon temps de réponse (temps
d’attente moyen pour une transaction)

• Redondance contrôlée des données : diminution

du volume de stockage, pas de mise à jour
multiple ni d’incohérence

KHCHERIF Raoudha SGBD 7

I.1 Objectifs des SGBD
• Cohérence des données : ex. L’âge d’une
personne doit être un nombre entier positif. Le
SGBD doit veiller à ce que les applications
respectent cette règle (contrainte d’intégrité).
• Partage des données : utilisation simultanée les
données par différentes applications
• Sécurité des données : les données doivent être
protégées contre les accès non-autorisés ou en cas
de panne
KHCHERIF Raoudha SGBD 8
I.2 Fonctions des SGBD
• Description des données : (LDD)
• Recherche des données
• Mise à jour des données
• Transformation des données
} LMD

• Contrôle de l’intégrité des données (respect des

contraintes d’intégrité)
• Gestion de transactions (atomicité des transactions)
et sécurité (mots de passe, etc.)
KHCHERIF Raoudha SGBD 9
 I.3 Architecture fonctionnelle type d’un SGBD

• Architecture Ansi/Sparc:
– Niveau externe: C’est le niveau d’expression des
besoins des utilisateurs. Il formalise les préoccupations
de chaque utilisateur et sera en final confronté au
schéma conceptuel global.
– Niveau conceptuel: Décrit la structure de la base de
données globalement à tous les utilisateurs (limite la
redondance) . Ce schéma décrit la structure de la base
indépendamment de son implantation
KHCHERIF Raoudha SGBD 10
I.3 Architecture fonctionnelle type d’un SGBD

– Niveau interne : c’est le niveau d’implantation

informatique de la solution conceptuelle:

• Niveau interne logique: intègre une technologie

particulière, comme par exemple la technologie des
bases de données relationnelles.

• Niveau interne physique: c’est le niveau

d’implantation physique où l’on tient compte d’un
environnement logiciel et matériel donné.

KHCHERIF Raoudha SGBD 11

 Architecture Ansi/Sparc

KHCHERIF Raoudha SGBD 12

 Exemple: institut de formation
permanente
• Schéma logique
– Etudiant: nétudiant, nom, prénom, date_naissance
– Enseignant: nom, prénom, statut, compte_bancaire
– Cours: nomC, cycle, nom_enseignant
– Inscription: nétudiant, nom_cours, note1, note2

KHCHERIF Raoudha SGBD 13

 Exemple (suite)
• Schémas externes.
• Schéma externe du professeur de base de données:

– Etudiant_BD: nom, prénom, note1, note2, note_finale tel

que Etudiant_BD résulte de la combinaison de Etudiant et
Inscription du SL, tels qu'il existe une Inscription de cet
étudiant pour le cours BD (nétudiant dans Etudiant =
nétudiant dans Inscription et nom_cours dans Inscription
= BD), et tel que note_finale = (note1 + note2)/2

KHCHERIF Raoudha SGBD 14

 Exemple (suite)
• Schéma externe du service de gestion du personnel
enseignant :
– Professeur: nom, prénom, ncompte_bancaire,
nombre_de_cours, liste(nom_cours) tel que Professeur
résulte de la combinaison de Enseignant et Cours du SL,
tels que liste(nom_cours) est la liste de nomC qui se
trouvent dans Cours tel que nom_enseignant dans Cours =
nom dans Enseignant, et tel que nombre_de_cours =
Cardinalité (liste(nom_cours))

KHCHERIF Raoudha SGBD 15

 Exemple (suite)
• Schéma interne (SI)
– Etudiant: fichier FEtud, contenu: nom, prénom, date de
naissance, nétudiant indexé sur nétudiant, index
secondaire sur nom+prénom
– Enseignant + cours: fichier FEnsCours, contenu: nom,
prénom, statut, ncompte_bancaire, liste(nomC, cycle)
tel que nom_enseignant dans Cours = nom dans
Enseignant indexé sur nom, deux index secondaires,
l'un sur nomC, l'autre sur cycle
– Inscription: fichier FInscrits, contenu: nétudiant,
nom_cours, note1, note2 indexé sur nétudiant, index
secondaire sur nom_cours
KHCHERIF Raoudha SGBD 16
KHCHERIF Raoudha SGBD 17
 Stockage
• Stockage des données dans les supports
physiques
• gestion des structures de mémorisation
(fichiers)
• gestion des structures d ’accès (gestion des
index, des clés,…)

KHCHERIF Raoudha SGBD 18

 Contrôle
• Optimisation globale des requêtes
• Gestion des conflits d ’accès simultanés par
plusieurs utilisateurs
• Contrôle général de la cohérence de l ’ensemble
• Coordination et suivi des processus en cours
• Garantie du bon déroulement des actions
entreprises
KHCHERIF Raoudha SGBD 19
 Dialogue

• Analyse des demandes de l ’utilisateur

• Contrôle des droits d ’accès de l ’utilisateur

• Présentation des résultats.

KHCHERIF Raoudha SGBD 20

II. Le langage de Définition des Données
(LDD SQL)
• SQL = Structured Query Language, issu de
SEQUEL (Structured English as a Query
Language)
• SQL permet la définition, la manipulation et le
contrôle d’une base de données relationnelle. Il se
base sur l’algèbre relationnelle.
• SQL est un standard ANSI depuis 1986.
• Nous adoptons dans ce chapitre la syntaxe de
SQL*Plus (Oracle).

KHCHERIF Raoudha SGBD 21

II.2 Créations de Table:
CREATE TABLE
• Forme simple
CREATE TABLE Client
(noClient INTEGER,
nomClient VARCHAR(15),
noTéléphone VARCHAR(15)
)

• Transmise à l ’interprète du LDD

– vérification
– création de la table
• schéma stocké dans dictionnaire de données
• allocation des structures physiques

KHCHERIF Raoudha SGBD 22

 La commande DESCRIBE
• la commande DESCRIBE permet de retrouver le
format d’une table
SQL> describe s_dept;
Name Null? Type
------------------------------- -------- ----
ID NOT NULL NUMBER(7)
NAME NOT NULL VARCHAR2(25)
REGION_ID NUMBER(7)

KHCHERIF Raoudha SGBD 23

 Types de données SQL
• CHAR(n):
• chaîne de caractères de longueur fixe n
• longueur par défaut 1
• longueur minimum 1,longueur maximum 255
• constantes:
• ‘DUPONT’
• ‘Dupont, Marc’
• ‘Titre “Coucou”’
• ‘1234’
• ‘NULL’

KHCHERIF Raoudha SGBD 24

 Type de données: VARCHAR2(n)

• chaîne de caractères de longueur variable et

de taille maximum n
– Longueur maximale : 32767 octets
• uniquement sur Oracle sinon CHAR
VARYING(n)
• constantes identiques a CHAR(n)

KHCHERIF Raoudha SGBD 25

Type de données: NUMBER(p)
• nombre entier positif, négatif ou nul de p
chiffres
• constantes:
– 0
– +0
– +1234
– -123123
– 1234

KHCHERIF Raoudha SGBD 26

KHCHERIF Raoudha SGBD 27
 Type de données:
NUMBER(p,s)
• nombre réel en virgule fixe de p chiffres au total
dont s après la virgule
• constantes de type NUMBER(4,1):
– 0,0
– 123,5
– -123,7
– -3,0
• d'après la configuration, il faut utiliser ‘.’ ou ‘,’
• certains SQL utilisent NUMERIC(p,s)

KHCHERIF Raoudha SGBD 28

 Type de données: LONG
• chaîne de caractères de taille variable
jusqu'à 2 Gbytes
• limité à un seul attribut par table

KHCHERIF Raoudha SGBD 29

 Type de données: DATE
• date (siècle, année, mois, jour, heure, minute, seconde)
• constantes
– ‘07-OCT-97’
– ‘30-FEB-97’ pas correct !
– ‘01-FEB-98’ ou ‘01-FEV-98’
– SYSDATE == date et heure actuelle
• l'arithmétique fonctionne sur les dates:
– ‘10-OCT-99’ - ‘09-OCT-99’ == 1
– ‘07-OCT-96’ + 10 == ‘17-OCT-96’
– Par défaut DD-MON-YY (18-DEC-02)
– Fonction TO_DATE
Exemple :
start_date := to_date(’29-SEP-2003’,’DD-MON-YYYY’);
start_date := to_date(’29-SEP-2003:13:01’,’DD-MON-YYYY:HH24:MI’);

KHCHERIF Raoudha SGBD 30

 La valeur NULL
• la valeur NULL est spéciale
– NULL appartient à tous les domaines: CHAR, LONG,
NUMBER, …
– signifie que la ligne n’a pas de valeur pour cet attribut
• ne pas confondre avec
– ‘‘
– 0
– 0.0

KHCHERIF Raoudha SGBD 31

 Contrainte NOT NULL
• lors de la création d’une table on peut spécifier
que certains attributs ne peuvent prendre la valeur
NULL

CREATE TABLE s_dept

(id NUMBER(7) NOT NULL,
name VARCHAR2(25) ,
region_id NUMBER(7) ) ;

KHCHERIF Raoudha SGBD 32

 Définition des clés primaires et
étrangères
• Clé primaire :
CONSTRAINT nom_contrainte PRIMARY KEY
(attribut_clé [, attribut_clé2, …])

• Clé étrangère :
CONSTRAINT nom_contrainte FOREIGN KEY
(attribut_clé_ét) REFERENCES table(attribut)

KHCHERIF Raoudha SGBD 33

 Définition des clés (exemple)
CREATE TABLE Client ( NumCli NUMBER(3),
Nom CHAR(30),
DateNaiss DATE,
Salaire NUMBER(8,2),
NumEmp NUMBER(3),
CONSTRAINT cle_pri PRIMARY KEY (NumCli),
CONSTRAINT cle_etr FOREIGN KEY (NumEmp)
REFERENCES EMPLOYEUR(NumEmp));
KHCHERIF Raoudha SGBD 34
 Définition des contraintes
d’intégrités
• Contrainte de domaine :
CONSTRAINT nom_cont CHECK (condition)
• Exemple:
CREATE TABLE Client ( NumCli NUMBER(3),
Nom CHAR(30),
DateNaiss DATE,
Salaire NUMBER(8,2),
NumEmp NUMBER(3),
CONSTRAINT cle_pri PRIMARY KEY (NumCli),
CONSTRAINT cle_etr FOREIGN KEY (NumEmp) REFERENCES
EMPLOYEUR(NumEmp),
CONSTRAINT date_ok CHECK (DateNaiss <SYSDATE));
KHCHERIF Raoudha SGBD 35
 Définitions des index
• Création d’index (accélération des accès)
CREATE [UNIQUE] INDEX nom_index ON
nom_table (attribut [ASC|DESC], …);
• UNIQUE pas de double
• ASC/DESC ordre croissant ou décroissant
CREATE UNIQUE INDEX Icli ON Client (NumCli);
• Destructions : DROP TABLE nom_table;
DROP INDEX nom_index;
KHCHERIF Raoudha SGBD 36
 Modification du schéma d’une
table
• Ajout d’attributs
ALTER TABLE nom_table ADD (attribut TYPE, …);
ex. ALTER TABLE Client ADD (tel NUMBER(8));
• Modifications d’attributs
ALTER TABLE nom_table MODIFY (attribut TYPE, …);
ex. ALTER TABLE Client MODIFY (tel NUMBER(10));
• Suppression de contraintes
ALTER TABLE nom_table DROP CONSTRAINT
nom_contrainte;
KHCHERIF Raoudha SGBD 37
Recommandations pour les CI
• Étant donnée que le nom d'une contrainte d'intégrité doit être unique
dans toute la BD.
• Il est conseillé d'utiliser un format pour les noms de contraintes et ceci
dans le but :
– de garantir leur unicité
– et de connaître leur type simplement en lisant leur nom
• Un exemple de format : prefixe_nom_table_nomcolonne

Préfixe Signification
PK clé primaire (primary key)
FK clé étrangère (foreign key)
NN Not NULL
U Unique
CK contrainte de domaine (CHECK)

SGBD 38
 Exemples
• Quelques contraintes sur des colonnes :
CREATE TABLE EMP (
MATRICULE NUMBER(5) CONSTRAINT PK_EMP
PRIMARY KEY,
NOM VARCHAR2(10) NOT NULL CONSTRAINT
CK_EMP_NOM CHECK (NOM = UPPER(NOM)),
CIN NUMBER(7) CONSTRAINT U_EMP_CIN UNIQUE,
DEP NUMBER(5) CONSTRAINT FK_EMP_DEPT
REFERENCES DEPT(DEPT)
CONSTRAINT CK_EMP_DEPT CHECK (DEPT IN (10,
20, 30, 35, 40));

SGBD 39
 Exemples
• Des contraintes de colonne peuvent être mises au niveau de la table :
CREATE TABLE EMP (
MATRICULE NUMBER(5),
NOM VARCHAR2(10) NOT NULL ,
CIN NUMBER(7),
DEPT NUMBER(5),
SAL NUMBER(15,3),
CONSTRAINT PK_EMP PRIMARY KEY (MATRICULE),
CONSTRAINT U_EMP_NOM UNIQUE(NOM),
CONSTRAINT U_EMP_CIN UNIQUE(CIN),
CONSTRAINT FK_EMP_DEPT FOREIGN KEY (DEPT) REFERENCES
DEPT(DEPT),
CONSTRAINT CK_EMP_DEPT CHECK (DEPT IN (10, 20, 30, 35,
40)));

SGBD 40
 Exemples
• Certaines contraintes portent sur plusieurs colonnes et ne peuvent être
indiquées que comme contraintes de colonne:
CREATE TABLE PARTICIPATION (
MATRICULE NUMBER(5) CONSTRAINT
FK_PARTICIPATION_MATR REFERENCES EMP,
CODEP VARCHAR2(5) CONSTRAINT FK_PARTICIPATION_CODEP
REFERENCES PROJET,
.........
CONSTRAINT PK_PATRICIPATION PRIMARY KEY(MATRICULE,
CODEP));

SGBD 41
Différer une contrainte d’intégrité
• Les contraintes d'intégrité sont vérifiées à chaque exécution d’une
requête SQL,
• Il est parfois utiles de différer la vérification d'une ou plusieurs
contraintes à la fin d'un ensemble de requêtes regroupées en une
transaction,
• Exemple :
– EMP (matricule, nom, fonction, matr_sup, date
d'embauche, sal comm, dep )
– On veut ajouter plusieurs employés dans une même transaction (chargement
de la table à partir d’un fichier de données)
• La table emp contient une contrainte FK liée au fait que matr_sup du supérieur
d'un employé doit correspondre à un matricule d'un des employés de emp.
• => violation de la contrainte étrangère
• Sol : différer la vérification de la C.I. FK sur matr-sup à la fin de la transaction

SGBD 42
Différer une contrainte d’intégrité
• Comment procéder ? : en deux étapes
1. lors de la déclaration de la contrainte (au moment de la création de la table),
on indique qu'on pourra différer sa vérification :
CONSTRAINT nom_contrainte definition-contrainte [NOT]
DEFERRABLE [INITIALLY DEFERRED | IMMEDIATE]
• La valeur par défaut est NOT DEFERRABLE.
avec
• INITIALLY DEFERED : diffère tout de suite la contrainte.
• IMMEDIATE (valeur par défaut) la contrainte n'est différée qu'à la suite de
l'application de la deuxième phase

SGBD 43
Différer une contrainte d’intégrité
2. On diffère la vérification de contraintes juste pour la transaction en cours,
avec la commande :
set CONSTRAINTS {liste de contraintes | ALL} DEFERRED
• Si elle est INITIALLY DEFERRED, on peut ne plus la différer pour le
temps d'une transaction en lançant la commande :
set CONSTRAINTS {liste de contraintes | ALL} IMMEDIATE

SGBD 44
 Différer une contrainte d’intégrité
• Exemple
EMP (matricule, nom, fonction, matr_sup, date d'embauche,sal
comm, dep)
DEPT (dept,nomd, lieu)
CREATE TABLE EMP (
MATRICULE NUMBER(5) CONSTRAINT PK_EMP PRIMARY KEY,
MATR_SUP NUMBER(5) CONSTRAINT FK_EMP_MATR_SUP REFERENCES EMP
DEFERRABLE,
…
);
SET CONSTRAINTS FK_EMP_MATR_SUP DEFERRED;
insert into emp values
(7499, 'salah', 'commercial', 7698, '20/2/1991',
1280.000, 240.000, 30);
. . .
insert into emp values
(7698, 'Mohamed', 'directeur', NULL, '19/3/1988',
1500.00, NULL, 30);
--pour marquer la fin de la transaction
COMMIT;

SGBD 45
 III. Le Langage de Manipulation
de données (LMD SQL)
III.1 Syntaxe générale d’une requête:
SELECT [ALL|DISTINCT] attribut(s) FROM table(s)
[WHERE condition] [GROUP BY attribut(s) [HAVING
condition]]
[ORDER BY attribut(s) [ASC|DESC]];
• Tous les tuples d’une table
ex. SELECT * FROM Client;
• Tri du résultat
ex. Par ordre alphabétique inverse de nom
SELECT * FROM Client ORDER BY Nom DESC;
KHCHERIF Raoudha SGBD 46
 BD exemple
• client ( numcli , nom, prenom, adresse, codepost,
ville, tel);
• produit ( numprod, designation, prixunit, qtestock );
• commande ( numcom, numcli, idvendeur, datec,
quantite, numprod);
• vendeur ( idvendeur, nomvendeur, qualité, salaire,
commission));

KHCHERIF Raoudha SGBD 47

 Interrogation des données

• Calculs ex. Calcul de prix TTC

SELECT PrixUni+PrixUni*0.206 prixttc FROM
Produit;
• Projection
ex. Noms et Prénoms des clients, uniquement
SELECT Nom, Prenom FROM Client;
• Restriction
ex. Clients qui habitent à Tunis
SELECT
KHCHERIF * FROM ClientSGBD
Raoudha WHERE Ville = ‘Tunis’; 48
 Interrogation des données
• ex. Commandes en quantité au moins égale à 3
SELECT * FROM Commande WHERE Quantite >= 3;
• ex. Produits dont le prix est compris entre 50 et 100 DT
SELECT * FROM Produit
WHERE PrixUni BETWEEN 50 AND 100;
• ex. Commandes en quantité indéterminée
SELECT * FROM Commande WHERE Quantite IS
NULL;

KHCHERIF Raoudha SGBD 49

 Interrogation des données
• ex. Clients habitant une ville dont le nom se
termine par Menzel
SELECT * FROM Client
WHERE Ville LIKE ‘%Menzel’;
• ‘Menzel%’  commence par Menzel
• ‘%Men%’  contient le mot Men
– le joker ‘_’ peut être remplacé par n’importe quel caractère
– le joker ‘%’ peut être remplacé par 0, 1, 2, … caractères
KHCHERIF Raoudha SGBD 50
 Interrogation des données
• ex. Prénoms des clients dont le nom est Mohamed,
Ahmed ou Mahmoud
SELECT Prenom FROM Client
WHERE Nom IN (‘Ahmed’, ‘Mahmoud’, ‘Mohamed’);
• spécifie une liste de constantes dont une doit égaler le premier terme pour
que la condition soit vraie
• pour des NUMBER, CHAR, VARCHAR, DATE

• NB : Possibilité d’utiliser la négation pour tous ces

prédicats : NOT BETWEEN, NOT NULL, NOT LIKE, NOT
IN.
KHCHERIF Raoudha SGBD 51
 Fonctions d’agrégat
• Elles opèrent sur un ensemble de valeurs, fournissent une
valeur unique
– AVG() : moyenne des valeurs
– SUM() : somme des valeurs
– MIN(), MAX() : valeur minimum, valeur maximum
– COUNT() : nombre de valeurs
• ex. Moyenne des prix des produits
SELECT AVG(PrixUni) FROM Produit;
KHCHERIF Raoudha SGBD 52
 Opérateur DISTINCT
• ex. Nombre total de commandes
SELECT COUNT(*) FROM Commande;
SELECT COUNT(NumCli) FROM Commande;
• ex. Nombre de clients ayant passé commande
SELECT COUNT( DISTINCT NumCli)
FROM Commande;

KHCHERIF Raoudha SGBD 53

 Exemple
• Table COMMANDE (simplifiée)
NumCli Date Quantite
1 22/09/99 1
3 22/09/99 5
3 22/09/99 2

➢ COUNT(NumCli) Résultat = 3
➢ COUNT(DISTINCT NumCli)  Résultat = 2
KHCHERIF Raoudha SGBD 54
 Jointure
• Consiste en un produit cartésien ou certaines lignes seulement
sont sélectionnées via la clause WHERE
• ex. Liste des commandes avec le nom des clients
SELECT Nom, Date, Quantite FROM Client, Commande WHERE
[Link] = [Link];
• ex. Idem avec le numéro de client en plus
SELECT [Link], Nom, Date, Quantite
FROM Client C1, Commande C2 WHERE [Link] =
[Link] ORDER BY Nom;
• NB : Utilisation d’alias (C1 et C2) pour alléger
l’écriture + tri par nom.
KHCHERIF Raoudha SGBD 55
 Jointure exprimée avec le
prédicat IN
• ex. Nom des clients qui ont commandé le 23/09

SELECT Nom FROM Client WHERE NumCli

IN (SELECT NumCli FROM Commande
WHERE DateC = ‘23-09-2000’ );

NB : Il est possible d’imbriquer des requêtes.

KHCHERIF Raoudha SGBD 56

 Prédicats EXISTS / NOT
EXISTS
• ex. Clients qui ont passé au moins une
commande [n ’ont passé aucune commande]

SELECT * FROM Client C1

WHERE [NOT] EXISTS ( SELECT * FROM
Commande C2 WHERE [Link] =
[Link] );

KHCHERIF Raoudha SGBD 57

 Prédicats ALL / ANY
• ex. Numéros des clients qui ont commandé au
moins un produit en quantité supérieure à chacune
[à au moins une] des quantités commandées par le
client n° 1.

SELECT DISTINCT NumCli FROM Commande

WHERE Quantite > ALL [ANY] (
SELECT Quantite FROM Commande
WHERE NumCli = 1 );
KHCHERIF Raoudha SGBD 58
 Groupement
• permet de créer des groupes de lignes pour appliquer
des fonctions d’agrégat sur les groupes
– il est possible de créer des groupes sur plusieurs attributs
• ex. Quantité totale commandée par chaque client
SELECT NumCli, SUM(Quantite) FROM Commande
GROUP BY NumCli;
• ex. Nombre de produits différents commandés...
SELECT NumCli, COUNT(DISTINCT NumProd)
FROM Commande GROUP BY NumCli;
KHCHERIF Raoudha SGBD 59
 Groupement
• ex. Quantité moyenne commandée pour les
produits faisant l’objet de plus de 3 commandes

SELECT NumProd, AVG(Quantite)

FROM Commande GROUP BY NumProd HAVING
COUNT(*)>3;

Attention : La clause HAVING ne s’utilise qu’avec

GROUP BY.
KHCHERIF Raoudha SGBD 60
 Opérations ensemblistes
• INTERSECT, MINUS, UNION
– les deux tableaux opérandes doivent avoir une description identique:
• nombre de colonnes
• domaines des valeurs de colonnes
– structure générale
• co-requête OPERATEUR co-requête
• ou chaque co-requête est une instruction SELECT
ex. Numéro des produits qui soit ont un prix inférieur à 100 DT, soit
ont été commandés par le client n° 2
SELECT NumProd FROM Produit WHERE PrixUni<100 UNION
SELECT NumProd FROM Commande WHERE NumCLi=2;

KHCHERIF Raoudha SGBD 61

 Les sous-requêtes
• mécanisme très puissant mais d’un emploi
subtil !
• = une requête SELECT insérée dans la
clause WHERE d’une autre requête
SELECT
• bien sur, une sous-requête peut contenir une
sous-sous-requête !

KHCHERIF Raoudha SGBD 62

 Les sous-requêtes
• peuvent retourner
– une seule valeur
– une liste de valeurs
• une sous-requête constitue le second
membre d’une comparaison dans la clause
WHERE

KHCHERIF Raoudha SGBD 63

 Les sous-requêtes retournant
une valeur
SELECT last_name, salary FROM s_emp
WHERE salary >= (SELECT AVG(salary) FROM
s_emp);

LAST_NAME SALARY sous-requête

------------------------- ------------------
Velasquez 2500
Ngao 1450
Nagayama 1400
Quick-To-See 1450
...
KHCHERIF Raoudha SGBD 64
 Les sous-requêtes retournant
une liste de valeurs
SELECT last_name, salary FROM s_emp
WHERE salary >= ALL (SELECT salary FROM
s_emp WHERE dept_id = 10) ;
LAST_NAME SALARY
------------------------- -------------------
Velasquez 2500
Ngao 1450
Quick-To-See 1450
Ropeburn 1550
Giljum 1490
KHCHERIF Raoudha SGBD 65
 Contraintes des sous-requêtes

• clause ORDER BY y est interdite

• chaque sous-requête doit être entourée de
parenthèses
• clause SELECT d’une sous-requête ne peut
contenir qu’un seul attribut
• les attributs définis dans la requête principale
peuvent être utilises dans la sous-requête
• les attributs définis dans la sous-requête ne
peuvent pas être utilises dans la requête principale

KHCHERIF Raoudha SGBD 66

 Opérateurs de comparaison et les
sous-requêtes
• dans le cas de sous-requêtes retournant une seule
valeur, les opérateurs classiques (<, >, …) peuvent
être appliques
• dans le cas de sous-requêtes retournant une liste de
valeurs, il faut utiliser les quantificateurs
– ANY: l’expression est vraie si une des valeurs de la
sous-requête vérifie la comparaison
– ALL: l’expression est vraie si toutes les valeurs de la
sous-requête vérifient la comparaison
• Note: IN équivaut à = ANY
KHCHERIF Raoudha SGBD 67
 Les sous-requêtes multiples
• la clause WHERE d’une requête principale
peut contenir plusieurs sous-requêtes reliées
par les connecteurs AND et OR

KHCHERIF Raoudha SGBD 68

 Mise à jour des données
• Ajout d’un tuple
INSERT INTO nom_table VALUES (val_att1, val_att2,
…);
ex. INSERT INTO Produit VALUES (400, ‘Nouveau
produit’, 78.90);
• Mise à jour d’un attribut
UPDATE nom_table SET attribut=valeur
[WHERE condition];
ex. UPDATE Client SET Nom=‘Dudule’ WHERE
NumCli = 3;
KHCHERIF Raoudha SGBD 69
 Mise à jour des données
• Suppression de tuples
DELETE FROM nom_table [WHERE condition];

ex. DELETE FROM Produit;

ex. DELETE FROM Client WHERE Ville = ‘Tunis’;

KHCHERIF Raoudha SGBD 70

 Les vues
• Vue : table virtuelle calculée à partir d’autres
tables grâce à une requête

CREATE [OR REPLACE]

[FORCE | NOFORCE] VIEW nom_de_vue
[(alias_colonne1, alias_colonne2, ...)]
AS subquery
WITH CHECK OPTION [CONSTRAINT
constraint] ;
KHCHERIF Raoudha SGBD 71
• Mots clés et paramètres
• OR REPLACE : permet de supprimer puis de recréer
la vue si elle existe
• FORCE : ignore les erreurs et crée la vue
• WITH CHECK OPTION : permet d'assurer la
cohérence des informations modifiées afin de laisser
dans la vue les lignes affectées par une modification
• CONSTRAINT constraint : permet juste de nommer
la contrainte de la vue

KHCHERIF Raoudha SGBD 72

• Syntaxe (suite)
Remarques :
- la modification d’une table de base affecte la vue
- le corps d’une vue ne peut contenir de clause ORDER
BY ou FOR UPDATE
- on ne peut effectuer des insertions, des mises à jours et
des suppressions dans une vue contenant une jointure,
des opérateurs ensemblistes, des fonctions de groupe, les
clauses GROUP BY, ou l'opérateur DISTINCT.
- tables systèmes: all_views, dba_views, user_views
KHCHERIF Raoudha SGBD 73
 Les vues
• Intérêt des vues
– Simplification de l’accès aux données en masquant les
opérations de jointure
ex. CREATE VIEW Prod_com AS SELECT
[Link], Dési, PrixUni, Date, Quantite FROM
Produit P, Commande C WHERE
[Link]=[Link];
SELECT NumProd, Dési FROM Prod_com
WHERE Quantite>10;

KHCHERIF Raoudha SGBD 74

 Les vues
• Intérêt des vues
– Sauvegarde indirecte de requêtes complexes
– Présentation de mêmes données sous différentes formes
adaptées aux différents usagers particuliers
– Support de l’indépendance logique
ex. Si la table Produit est remaniée, la vue
Prod_com doit être refaite, mais les requêtes qui
utilisent cette vue n’ont pas à être remaniées.

KHCHERIF Raoudha SGBD 75

 Les vues
• Intérêt des vues
– Renforcement de la sécurité des données par
masquage des lignes et des colonnes sensibles
aux usagers non habilités
• Problèmes de mise à jour, restrictions
– La mise à jour de données via une vue pose des
problèmes et la plupart des systèmes impose
d’importantes restrictions.

KHCHERIF Raoudha SGBD 76

Les transactions
 Les Transactions
Exemple
Une transaction peut transférer une somme
d'argent entre deux comptes d'un client
d'une banque. Elle comporte deux ordres :
un débit sur un compte et un crédit sur un
autre compte. Si un problème empêche le
crédit, le débit doit être annulé.

SGBD
KHCHERIF Raoudha 78
 Les Transactions
Propriétés : il est facile de se souvenir des propriétés
essentielles des transactions : elles sont ACID".
• Atomicité : un tout indivisible ;
• Cohérence : une transaction doit laisser la base dans un état
cohérent ; elle ne doit pas contredire une contrainte d'intégrité
ou mettre les données dans un état anormal,
• Isolation : les modifications effectuées par une transaction ne
doivent être visibles par les autres transactions que lorsque la
transaction est validée ;
• Durabilité : le SGBD doit garantir que les modifications d'une
transaction validée seront conservées, même en ca de panne.
SGBD
KHCHERIF Raoudha 79
 Les Transactions
Propriétés
• "AID" est du ressort du système transactionnel du SGBD.
• "C" est du ressort de l'utilisateur (ou du programmeur) mais il
est aidé
– par "I", car ainsi il n'a pas à considérer les
interactions avec les autres transactions,
– par la vérification automatique des contraintes
d'intégrité par le SGBD.
• Le "I" est effectué par le système de contrôle de la concurrence
et "AD" sont supportés par les procédures de reprise après
panne.
SGBD
KHCHERIF Raoudha 80
 Les Transactions
• Une transaction se termine :
– soit par une validation qui applique les
modifications de façon permanente,
– soit par une annulation qui remet la base dans
son état initial (avant l’exécution de la
transaction).
• En cas de fin anormale d'une tâche utilisateur :
il y a automatiquement annulation des transactions
non terminées afin d’assurer l'intégrité de la base.
SGBD
KHCHERIF Raoudha 81
 Les Transactions dans SQL
• Une transaction commence :
– au début d'une session de travail :
• Exemple : lors d’une ouverture de session sous sql*plus

– ou juste après la fin de la transaction

précédente.
• Elle se termine soit par :
– Un ordre explicite de validation: commit
• Les modifications deviennent alors définitives,et visibles par toutes les
autres transactions.

– Un ordre explicite d’annulation: Rollback

• Toutes les modifications depuis le début de la transaction sont alors
SGBD
KHCHERIF annulées.
Raoudha 82

• La BD est restituée à son état avant l’exécution de transaction annulée.

 •
Atomicité d’une transaction
L’ordre commit valide toutes les modifications effectuées sur la BD lors de la
transaction.
• L’ordre Rollback annule toutes les modifications de la transaction.
• Les instructions SQL sont atomiques : quand une instruction provoque une erreur
(par exemple si une contrainte d'intégrité n'est pas vérifiée), toutes les modifications
déjà effectuées par cette instruction sont annulées.
– Exemple: modification du N° du depart dans la table emp en ajoutant 20 à l’ancien numéro.
sql> update emp a
set (deptno) =(select distinct deptno + 20
from emp b where [Link]=[Link]);
ERREUR à la ligne 1 :
ORA-02291: violation de contrainte (SCOTT.FK_DEPTNO)
d'intégrité – touche parent introuvable
Toutes les modifications effectuées par cet ordre sql sont annulées.
– Mais cette erreur ne provoque pas nécessairement de rollback automatique de la
transaction (c’est le cas si elle est exécutée par sql*Plus).

SGBD
KHCHERIF Raoudha 83
 Exemple
• Soit la table compte_client d’une BD d’une banque : compte_client(NCompte,nomClient,solde)
• On désire effectuer un virement de 200 dinars du compte 1111 vers le compte 2222.
• L’opération consiste à effectuer les 2 opérations suivantes :
– Débiter le solde de 1111 de 200 dinars
– Créditer le solde de 2222 de 200 dinars
• Ces deux opérations doivent former un tout indivisible c’est-à-dire
– Soit elles sont exécutées toutes les deux,
– Soit aucune n’est exécutée.

 …
Elles doivent donc être exécutées dans la même transaction

commit;-- fin de la transaction précédente

-- nouvelle transaction
update compte_client set
Solde = solde – 200
where ncompe=1111;
update compte_client set
Solde = solde + 200
Même
where ncompe=2222;
commit;-- validation de la transaction transaction
…

SGBD
KHCHERIF Raoudha 84
 Les transactions dans SQL
• Les points de sauvegarde:savepoint
– Il peut arriver qu'il ne soit pas nécessaire d'annuler toutes les
modifications effectuées. Pour cela, il est possible d’utiliser des
points de sauvegarde SAVEPOINT. Ces points permettront de
faire des ROLLBACKS "réduits".

SGBD
KHCHERIF Raoudha 85
 Exemple
SQL> select sal,comm from emp where empno=7499;
SAL COMM
----- ----------
1760 300
SQL> update emp set sal=nvl(sal,0) + nvl(sal,0)*0.1;
14 ligne(s) mise(s) à jour.
SQL> select sal,comm from emp where empno=7499;
SAL COMM
---------- ----------
1936 300
SQL> savepoint A;
Point de sauvegarde (SAVEPOINT) créé.
SQL> update emp set comm=comm + comm*0.1 Where comm is not null;
4 ligne(s) mise(s) à jour.
SQL> select sal,comm from emp where empno=7499;
SAL COMM
-------- ----------
1936 330
SQL> Rollback to A;
Annulation (ROLLBACK) effectuée.
SQL> commit;
Validation effectuée.
SQL> select sal,comm from emp where empno=7499;
SAL COMM
---------- ----------
1936 300

SGBD
KHCHERIF Raoudha 86
 [Link] Langage PL/SQL
IV.1.Généralités
➢PL/SQL :
➢ Langage procédural
➢Extension de SQL
➢Déclaration de variables et de constantes
➢Définition de sous-programmes
➢Gestion des erreurs à l’exécution (exceptions)
➢Manipulation
KHCHERIF Raoudha
de données
SGBD
avec SQL 87
 II – LE LANGAGE PL/SQL

STRUCTURE D’UN PROGRAMME PL/SQL

Déclaration de variables, types, curseurs, exceptions,.. (Optionnel)

Corps du programme : Code SQL (Obligatoire)
Gestion des erreurs (Optionnel)

BLOC PL/SQL :

DECLARE (Déclaration de variables et curseurs)

…..
BEGIN
…..
….
EXCEPTION (Traitement des cas d’erreur signalés par le SGBD)
…..
END ;
KHCHERIF Raoudha SGBD 88
 PORTEE DES OBJETS

Un bloc qui déclare un objet peut y accéder ainsi que tous les sous blocs
(sous bloc : bloc imbriqué dans un autre).
Par contre tout objet déclaré dans un sous bloc n’est pas accessible au
niveau du bloc supérieur.

UTILISATION EN CLIENT SERVEUR

Requêtes SQL exécutées séquentiellement :

Envoi d’un message du client vers le serveur pour chaque
requête et retour du serveur vers le client

Bloc PL/SQL :
Envoi d’un message pour tout le bloc et un seul retour vers le
client

KHCHERIF Raoudha SGBD 89

IV.2 Bloc PL/SQL
DECLARE
--Déclaration constantes/variables
BEGIN
--Commandes/instructions
EXCEPTION
--Traitement des erreurs à l’exécution.
END;
KHCHERIF Raoudha SGBD 90
 DECLARATION DES VARIABLES

Identificateur [CONSTANT] typededonnées [NOT NULL] [ :=| DEFAULT

expression] ;

Types de données :
Caractères (CHAR, VARCHAR, VARCHAR2, LONG, CLOB,..)
Numériques (NUMBER)
Date/heure (DATE, INTERVAL,….)
Données binaires (BLOB, RAW, …)
Adresse d’enregistrements (ROWID)
Variable du type d’une colonne (%TYPE)

Nom_variable Nom_table.Nom_colonne%TYPE

Variable du type enregistrement d’une table (%ROWTYPE)

Nom_variable Nom_table %ROWTYPE

Variable de type RECORD :

TYPE Nom_variable IS RECORD (……..) ;

KHCHERIF Raoudha SGBD 91
IV.3 Déclaration
• Partie déclarative d’un bloc PL/SQL ou d’un sous-
programme
• Types usuels : INTEGER, REAL, STRING, DATE,
BOOLEAN + types SQL
• Variables
– ex.
Date_naissance DATE;
compteur INTEGER:=0; -- Valeur par défaut
compteur INTEGER DEFAULT 0; -- idem
 id CHAR(5) NOT NULL:=‘AP001’;
KHCHERIF Raoudha SGBD 92
IV.3. Déclarations
• Constantes
– ex. euro CONSTANT REAL:=6.55957;
• Type d’une autre variable
– ex. credit REAL;
– debit credit%TYPE;
• Type d’un tuple d’une table
– ex. un_client client%ROWTYPE;
• Tableaux : 1) Déclaration d’un type tableau
2) Déclaration d’une variable de ce type
KHCHERIF Raoudha SGBD 93
IV.3. Déclarations
• Tableaux
– ex. TYPE Tab_entiers TABLE OF INTEGER
INDEX BY BINARY_INTEGER;
– TYPE Tab_cli TABLE OF [Link]%TYPE INDEX
BY BINARY_INTEGER;
• un_entier Tab_entiers;
• un_client Tab_cli;

KHCHERIF Raoudha SGBD 94

IV.3. Déclarations
• Enregistrements personnalisés
1) Définition d’un type enregistrement
– ex. TYPE Enr_four IS RECORD (numero
[Link]%TYPE, raison_sociale
CHAR(20));
2) Définition d’une variable de ce type
– ex. un_fournisseur Enr_four;

KHCHERIF Raoudha SGBD 95

V.4. Instructions de base
• Affectation
– ex. numero:=0;
– numero:=numero+1;
– SELECT numcli INTO numero FROM client
WHERE numcli=numero+1;
• Utilisation des tableaux
– ex. i:=1;
– un_entier(i):=i*2;
– NB : i doit être de type BINARY_INTEGER.
KHCHERIF Raoudha SGBD 96
V.4. Instructions de base
• Utilisation des enregistrements
personnalisés
– ex. un_fournisseur.numero:=4589;
– un_fournisseur.raison_sociale:=‘COGIP’;
– SELECT numfour, raisonsoc INTO
un_fournisseur FROM fournisseur WHERE
numfour=4589;

KHCHERIF Raoudha SGBD 97

V.5. Structures de contrôle
• Instruction sélective
– IF-THEN, IF-THEN-ELSE ou IF-THEN-
ELSEIF
IF condition1 THEN
-- Instructions
ELSEIF condition2 THEN
-- Instructions
ELSE
-- Instructions
END IF;
KHCHERIF Raoudha SGBD 98
 Test conditionnel
CASE variable
WHEN expr1 THEN instructions1;
WHEN expr2 THEN instructions2;
……
WHEN exprn THEN instructionsn;
[ELSE instructionsm;]
END CASE;

CASE variable
WHEN condition1 THEN instructions1;
WHEN condition2 THEN instructions2;
……
WHEN condition_n THEN instructionsn;
[ELSE instructionsm;]
END CASE;
KHCHERIF Raoudha SGBD 99
 Test conditionnel
• CASE
– CASE sélecteur
WHEN expression1 THEN résultat1
WHEN expression2 THEN résultat2
ELSE résultat3
END;

KHCHERIF Raoudha SGBD 100

 Test conditionnel
Exemple :
val := CASE city
WHEN ‘TORONTO’ THEN ‘RAPTORS’
WHEN ‘LOS ANGELES’ THEN ‘LAKERS’
ELSE ‘NO TEAM’
END;

KHCHERIF Raoudha SGBD 101

V.5. Structures de contrôle
• Instructions itératives
FOR compteur IN [REVERSE] min..max LOOP
-- Instructions
END LOOP;
WHILE condition LOOP
-- Instructions
END LOOP;
LOOP
-- Instructions
END LOOP;
KHCHERIF Raoudha SGBD 102
V.5. Structures de contrôle
• Branchements
-- Saut inconditionnel
GOTO étiquette;
-- Sortie de boucle
EXIT WHEN condition;
NB : À utiliser avec énormément de modération !

KHCHERIF Raoudha SGBD 103

V.6. Curseurs
• Curseur : structure de données permettant de
stocker le résultat d’une requête qui retourne
plusieurs tuples
• Déclaration : CURSOR nom_curs IS requête;
– ex. CURSOR calcul IS SELECT numprod,
prixuni*1.206 prixttc FROM produit;

NB : Un tuple du curseur sera de type

calcul%ROWTYPE.
KHCHERIF Raoudha SGBD 104
 V.6. Curseurs
• Ouverture d’un curseur : OPEN nom_curs;
• Gestion automatique d’un curseur
ex. FOR tuple IN calcul LOOP
var1:=[Link];
var2:=[Link];
END LOOP;
• Gestion « manuelle »
[Link]
FETCH calcul INTO tuple;
EXIT WHEN calcul%NOTFOUND;
…
END LOOP
KHCHERIF Raoudha SGBD 105
 V.6. Curseurs
• Attributs des curseurs
– "%NOTFOUND : FALSE si FETCH retourne un
résultat
– "%FOUND : opposé logique de %NOTFOUND
– "%ROWCOUNT : Nombre de lignes lues
– "%ISOPEN : TRUE si le curseur est ouvert
• Fermeture d’un curseur : CLOSE nom_curs;

KHCHERIF Raoudha SGBD 106

KHCHERIF Raoudha SGBD 107
 Exemple

KHCHERIF Raoudha SGBD 108

 Ligne courante d’un curseur
• La ligne courante d’un curseur est déplacée à
chaque appel de l’instruction fetch
• On est parfois amené à modifier la ligne courante
pendant le parcours du curseur
• Pour cela on peut utiliser la clause « where current
of » pour désigner cette ligne courante dans un
ordre LMD (insert, update, delete)
• Il est nécessaire d’avoir déclaré le curseur avec la
clause FOR UPDATE OF pour que le bloc
compile

KHCHERIF Raoudha SGBD 109

 Exemple
• DECLARE
CURSOR c IS
select matr, nome, sal from emp
where dept = 10
FOR UPDATE OF [Link];
………………………………………..
if salaire is not null then
total := total + salaire;
else -- on met 0 à la place de null pour le salaire
update emp set sal = 0 where current of c;
• end if;

KHCHERIF Raoudha SGBD 110

KHCHERIF Raoudha SGBD 111
 Select … for update
• Select classique : non verrouillé
• Problème avec les curseurs
• Solution : Select … for update
– Résultat : verrouiller toutes les lignes jusqu’au
prochain commit

KHCHERIF Raoudha SGBD 112

V.7. Exceptions
• À chaque erreur à l’exécution, une
exception est levée. Ces exceptions sont
gérées par des routines séparées.
• Fonctions PL/SQL pour la gestion
d’erreurs
– "SQLCODE : Code de la dernière exception
levée
– "SQLERRM : Message d’erreur associé
KHCHERIF Raoudha SGBD 113
V.7. Exceptions
• Exceptions personnalisées
– Déclaration : nom_exc EXCEPTION;
– Lever l’exception : IF condition THEN RAISE
nom_exc; END IF;
• Traitement des exceptions
– WHEN nom_exc THEN -- Instruction
– ex. WHEN probleme THEN
RAISE_APPLICATION_ERROR(-20501,’Erreur !’);

KHCHERIF Raoudha SGBD 114

 Les exceptions et erreurs
• NO_DATA_FOUND
– Quand Select into ne retourne aucune ligne
• TOO_MANY_ROWS
– Quand Select into retourne plusieurs lignes
• OTHERS
– Toutes erreurs non interceptées
• RAISE_APPLICATION_ERROR
– Pour retourner une message d’erreur
– Numéro d’erreur entre -20000 et -20999
• LOGIN_DENIED Connexion incorrecte
• NOT_LOGGED_ON Pas de connexion
• ROWTYPE_MISMATCH Erreur de type
• STORAGE_ERROR Dépassement de capacité mémoire
• ZERO_DIVIDE Division par zéro

KHCHERIF Raoudha SGBD 115

V.8. Exemple de programme
PL/SQL
-- Calcul du prix TTC des produits
-- et recopie dans la table PRODTTC
DECLARE
nbp NUMBER(3);
aucun_produit EXCEPTION;
CURSOR calcul IS SELECT numprod, prixuni*1.206
prixttc FROM produit;
tuple calcul%ROWTYPE;
BEGIN
-- Comptage des produits
SELECT COUNT(*) INTO nbp FROM produit;
-- Test « il existe des produits » ou pas ?
IF nbp = 0 THEN RAISE aucun_produit;
END IF;
KHCHERIF Raoudha SGBD 116
V.8. Exemple de programme
PL/SQL
-- Recopie des valeurs dans la table prodttc
FOR tuple IN calcul LOOP
INSERT INTO prodttc VALUES ([Link], [Link]);
END LOOP;
-- Validation de la transaction
COMMIT;
EXCEPTION
WHEN aucun_produit THEN
RAISE_APPLICATION_ERROR(-20501, ‘Erreur : table client
vide’);
END;

KHCHERIF Raoudha SGBD 117

 Affichage
• Activer le retour écran
– set serveroutput on size 10000
• Affichage
– dbms_output.put_line(chaîne);
– Utilise || pour faire une concaténation

KHCHERIF Raoudha SGBD 118

 Exemple n°1
DECLARE
i number(2);
BEGIN
FOR i IN 1..5 LOOP
dbms_output.put_line(‘Nombre : ’ || i );
END LOOP;
END;
KHCHERIF Raoudha SGBD 119
 Exemple n°2
DECLARE
compteur number(3);
i number(3);
BEGIN
select count(*) into compteur from clients;
FOR i IN 1..compteur LOOP
dbms_output.put_line('Nombre : ' || i );
END LOOP;
KHCHERIF Raoudha SGBD 120
END;
 PL/SQL

Procédures et fonctions
 FONCTIONS ET PROCEDURES STOCKEES (CATALOGUEES)

Programmes PL/SQL compilés et stockés dans le dictionnaire de données

Les procédures stockées peuvent être appelées dans :

Un bloc PL/SQL
Une fonction ou une procédure
Dans un programme en langage évolué (C, Fortran, ..)

Sécurité : droit d’accès sur les procédures

GRANT EXECUTE ON Nom_procédure TO Nom_utilisateur ;

Intégrité : Utilisation de COMMIT-ROLLBACK lors de

traitements dépendants exécutés dans le même bloc ;

Performance : réduction du nombre d’appels à la base

Productivité : simplicité de maintenance

KHCHERIF Raoudha SGBD 122
 CREATION DE PROCEDURES OU FONCTIONS :

CREATE [OR REPLACE] PROCEDURE Nom_procédure

[(paramètre1 IN | OUT | IN OUT type
[,paramètre2 IN | OUT | IN OUT type]
…..
[,paramètren IN | OUT | IN OUT type])]
IS
Déclaration de variables locales ;
BEGIN
Corps_de_la_procédure
END;

CREATE [OR REPLACE] FUNCTION Nom_fonction

[(paramètre1 IN | OUT | IN OUT type
[,paramètre2 IN | OUT | IN OUT type]
…..
[,paramètren IN | OUT | IN OUT type])]
RETURN typeSQL
IS
Déclaration de variables locales ;
BEGIN
KHCHERIF Raoudha Corps_de_la_procédure
SGBD 123
END;
 APPEL D’UNE PROCEDURE OU D’UNE FONCTION :

EXECUTE Nom_Procédure(……) ;

SELECT …., Nom_Fonction(….),…..

Variable := Nom_Fonction(…….) ;

KHCHERIF Raoudha SGBD 124

 Les procédures
create or replace procedure list_nom_clients
IS
BEGIN
DECLARE
nom varchar2(30);
CURSOR get_nom_clients IS
select nom,adresse from clients;
BEGIN
FOR toto IN get_nom_clients LOOP
dbms_output.put_line('Employé : ' ||
UPPER([Link]) ||' Ville : '|| [Link]);
END LOOP;
KHCHERIF Raoudha SGBD 125
END;
 Les procédures
create or replace procedure list_nom_clients
(ville IN varchar2,
result OUT number)
IS
BEGIN
DECLARE
CURSOR get_nb_clients IS
select count(*) from clients where adresse=ville;
BEGIN
open get_nb_clients;
fetch get_nb_clients INTO result;
end;
KHCHERIF Raoudha SGBD 126
end;
 Récupération des résultats
• Déclarer une variable
SQL> variable nb number;
• Exécuter la procedure
SQL> execute list_nom_clients('paris',:nb)
• Visualisation du résultat
SQL> print
• Description des paramètres
SQL>
KHCHERIF desc nom_procedure
Raoudha SGBD 127
 Les fonctions
create or replace function nombre_clients
return number
IS
BEGIN
DECLARE
i number;
BEGIN

select count(*) INTO i from clients;

return i;
end;
end;
• Exécution: select nombre_clients() from dual

KHCHERIF Raoudha SGBD 128

 Procédures et fonctions
• Suppression de procédures ou fonctions
– DROP PROCEDURE nom_procedure
– DROP FUNCTION nom_fonction
• Table système contenant les procédures et
fonctions : user_source

KHCHERIF Raoudha SGBD 129

 Exercices
• Réalisez une procédure list_tables qui
donne le nom de toutes vos tables
• Réalisez une procédure UPDATENOM qui
remplit correctement la colonne
NOM_PIECE de clients par rapport à la
table fournisseurs
• Réalisez une procédure UPDATEPRIX qui
met à jour tous les prix de la table clients
KHCHERIF Raoudha SGBD 130
• Réalisez une procédure list_tables qui
donne les noms de toutes vos tables;

• Réalisez une procédure structure_tables qui

donne les nom de toutes vos tables ainsi que
leurs structures

KHCHERIF Raoudha SGBD 131

 Procédure LIST_TABLES
create or replace procedure list_tables
IS
BEGIN
DECLARE
CURSOR get_nom IS
select table_name from user_tables;
BEGIN
FOR nom IN get_nom
LOOP
dbms_output.put_line('Nom de la table : ' ||
nom.table_name);
END LOOP;
KHCHERIF Raoudha SGBD 132
END;
 Procédure UPDATENOM
CREATE OR REPLACE PROCEDURE updatenom
IS
BEGIN
DECLARE
nompiece varchar2(30);
cursor toto IS
SELECT distinct [Link], fournisseurs.nom_piece
FROM fournisseurs;
BEGIN
FOR nompiece IN toto LOOP
UPDATE clients
SET clients.nom_piece=nompiece.nom_piece
WHERE [Link]=[Link];
END LOOP;
END;
END;
KHCHERIF Raoudha SGBD 133
 Procédure UPDATEPRIX
CREATE OR REPLACE PROCEDURE updateprix
IS
BEGIN
DECLARE
prixunit number(5);
CURSOR toto IS
SELECT [Link], [Link], fournisseurs.prix_piece_unite
FROM fournisseurs,clients
WHERE [Link]=[Link];
BEGIN
FOR prixunit IN toto LOOP
UPDATE clients
SET [Link]=[Link]*prixunit.prix_piece_unite
WHERE [Link]=[Link] and [Link]=[Link];
END LOOP;
END;
KHCHERIF Raoudha SGBD 134
END;
PL/SQL

Triggers
V.9. Triggers
• Trigger (déclencheur) : routine déclenchée
automatiquement par des événements liés à des
actions sur la base

• Les triggers complètent les contraintes d’intégrité

en permettant des contrôles et des traitements plus
complexes.

KHCHERIF Raoudha SGBD 136

[Link]
Types de Insertion Suppression Mise à jour
triggers
Avant 1 2 3
Après 4 5 6
D’une table de la base

KHCHERIF Raoudha SGBD 137

 Types de Triggers
• Il existe deux types de triggers différents : les triggers de
table (STATEMENT) et les triggers de ligne (ROW).
Quelle est la différence ?
– Les triggers de table sont exécutés une seule fois lorsque des
modifications surviennent sur une table (même si ces modifications
concernent plusieurs lignes de la table). Ils sont utiles si des
opérations de groupe doivent être réalisées (comme le calcul d’une
moyenne, d’une somme totale, d’un compteur, …). Pour des
raisons de performance, il est préférable d’employer ces triggers
plutôt que les triggers lignes.
– Les triggers lignes sont exécutés « séparément » pour chaque
ligne modifiée dans la table. Ils sont très utiles s’il faut mesurer
une évolution pour certaines valeurs, effectuer des opérations pour
chaque ligne en question.

KHCHERIF Raoudha SGBD 138

[Link]
• Création en SQL*Plus :
– CREATE [OR REPLACE] TRIGGER nom_trig
BEFORE|AFTER INSERT|DELETE|UPDATE ON
nom_table [FOR EACH ROW]
-- Bloc PL/SQL contenant le
-- traitement à effectuer
• Variables spécifiques
– :OLD.nom_attribut : valeur de l’attribut avant mise à
jour
– :NEW.nom_attribut : valeur de l’attribut après mise à
jour

KHCHERIF Raoudha SGBD 139

 Accès aux valeurs modifiées
• Utilisation de new et old
• Si nous ajoutons un client dont le nom est
toto, alors nous récupérons ce nom grâce à
la variable :[Link]
• Dans le cas de suppression ou modification,
les anciennes valeurs sont dans la variable
:[Link]

KHCHERIF Raoudha SGBD 140

V.9. Triggers
-- Exemple : test de clé primaire sur la table client
CREATE OR REPLACE TRIGGER trig_clep BEFORE INSERT ON
client FOR EACH ROW
DECLARE
n INTEGER;
cle_existe EXCEPTION;
cle_nulle EXCEPTION;
BEGIN
-- Existence de la clé primaire
SELECT COUNT(numcli) INTO n FROM client WHERE
numcli=:[Link];
IF n>0 THEN
RAISE cle_existe;
END IF
KHCHERIF Raoudha SGBD 141
V.9. Triggers
-- Valeur nulle
IF :[Link] IS NULL THEN
RAISE cle_nulle;
END IF;
EXCEPTION
WHEN cle_existe THEN
RAISE_APPLICATION_ERROR(-20501,‘Clé primaire déjà utilisée !’);
WHEN cle_nulle THEN RAISE_APPLICATION_ERROR(-20502,‘La
clé primaire doit avoir une valeur!’);
END;

KHCHERIF Raoudha SGBD 142

 Activation ou desactivation du
trigger

• Alter trigger nom_trig enable|disable ;

KHCHERIF Raoudha SGBD 143

 Les Packages?
• Regroupement des types, éléments et sous
programmes PL/SQL logiquement liés.
• Se composent de deux parties
– Une spécification
– Un corps
• Permettent à Oracle de lire immédiatement
plusieurs objets en mémoire

KHCHERIF Raoudha SGBD 144

 Package
Spécification Déclaration de la procédure A

Définition de la procédure A
Corps de package
Variable locale

KHCHERIF Raoudha SGBD 145

• Spécification :
• CREATE [OR REPLACE] PACKAGE Nom_programme AS
Variables;
PROCEDURE ….;
FUNCTION ….RETURN …;
…..
Exceptions;
END [Nom_programme] ;
• Implémentation :
• CREATE [OR REPLACE] PACKAGE BODY Nom_programme
» AS
PROCEDURE ….
BEGIN
…..
END;
FUNCTION ….RETURN …;
BEGIN
….
END ;
• END [Nom_programme] ;

KHCHERIF Raoudha SGBD 146

 Exemple
• CREATE PACKAGE bank AS
PROCEDURE transac;
PROCEDURE detourne(value NUMBER);
• END bank;

• CREATE PACKAGE BODY bank AS

--commentaires
PROCEDURE transac IS
BEGIN
...
END transac;
-- commentaires
PROCEDURE detourne(value NUMBER) IS
BEGIN
...
END detourne;
END bank;
EXECUTE [Link](1 000 000 000 $);

KHCHERIF Raoudha SGBD 147