Le Langage

SQL

Introduction
 Historique du Langage SQL

• E. F. CODD : premiers articles dans les années 70

• IBM crée le langage SEQUEL (Structured English
Query Language) ancêtre du langage SQL
• Ce langage devient SQL (Structured Query
Language, prononcer sikuel)
• En 1979, Relational Software Inc. (devenu depuis
Oracle) met en place la première version
commerciale de SQL
• Principaux SGBDR : Oracle, DB2, Informix, SQL-
Server, Ingres, MySQL, Interbase, ….

Mars - Avril 2014 2

 Norme et Standard du langage SQL

• Base du succès de SQL

• Fin des SGBD constructeurs
• ANSI (American National Standards Institute) et
de l’ISO (International Standards Organization)
qui est affilié à l’IEC (International
Electrotechnical Commission)
• L’ANSI et l’ISO/IEC ont accepté SQL comme le
langage standardisé. La dernière norme publiée
par l’ANSI et l’ISO est SQL92 (SQL2)
• On attend la norme SQL3 ….

Mars - Avril 2014 3

 Caractéristiques de la norme SQL

• Oracle et IBM participent activement au sein du

groupe ANSI
• SQL92 défini quatre niveaux : Entry, Transitional,
Intermediate, et Full
• Un SGBDR doit supporter au moins une
implémentation de SQL de type Entry
• Oracle9i est totalement compatible Entry et a
beaucoup de caractéristiques de type
Transitional, Intermediate, et Full

Mars - Avril 2014 4

 Les sous-langages de SQL

• LDD : Langage de Définition des Données

– Création, Modification et Suppression des objets
– Objets : tables, index, cluster, privilèges, ….
• LMD : Langage de Manipulation des Données
– Ajout, Modification et Suppression des données
– Notion de Transaction
• LID : Langage d’Interrogation des Données
– Sélection (recherche) de l’information
– Mise en œuvre du langage relationnel
• LCD : langage de Contrôle des Données
– Notion de sous-schéma ou schéma externe
– Notion de rôles et de privilèges

Mars - Avril 2014 5

 SQL avancé

• Langage de bloc pour augmenter la puissance de SQL :

– Fonctions itératives et alternatives
– PL/SQL avec Oracle, Transact-SQL avec SQL-Server
• Notion de Déclencheur ou Trigger
– MAJ automatique de colonnes dérivées
– Contraintes complexes
• Notion de Procédure Stockée
– Programme SQL stocké (compilé) dans la base
• SQL encapsulé : SQL embarqué dans un langage externe
– Géré par le SGBD : PRO*C, PRO*ADA, …
– Extérieur au SGBD : VB, C#, …

Mars - Avril 2014 6

 Apprendre SQL avec Oracle

• SGBD le plus répandu dans le monde (gros,

moyens et petits systèmes)
• SGBD le plus normalisé
• Produit téléchargeable sur oracle.com à des fins
d’apprentissage
• Interface SQL*Plus pour dialoguer avec le
langage SQL

Mars - Avril 2014 7

 Offre complète d’Oracle :
Produits proposés

• Noyau Oracle Serveur

– DBMS : gestionnaire de bases de données
– Création d’une ou plusieurs instances
– Licence serveur minimale
– Toutes plates-formes acceptées
– Driver SQL*Net serveur
– PL/SQL : langage de bloc propriétaire
• SQL*Plus
– Interface minimale pour exécuter des requêtes SQL
SQL> SELECT * FROM emp ;
– Envoi de requêtes et retour des résultats sur écran
– Appel de blocs, procédures, fonctions...

Mars - Avril 2014 8

 Offre complète d’Oracle (suite)

• Enterprise Manager
– Interface graphique pour administrer la base de données distante
(DBA)
– Administration système Oracle
– Ajout, modification et suppression de tous les objets de la base
– Surveillance des activités
• SQL*Net
– Driver propriétaire de communication client et serveur
– Nécessaire en Client - Serveur et les BD réparties
• Oracle Application Server
– Pilier de NCA (Network Computing Architecture)
– Serveur Web Transactionnel
– Cartouches PL/SQL, Java...
– Intègre le standard CORBA et IIOP

Mars - Avril 2014 9

 Offre complète d’Oracle (suite)

• Oracle WareHouse
– Serveur OLAP
– Analyse décisionnelle
• Oracle Database Designer
– Atelier de génie logiciel
– Construction du système d’information (données et
programme)
– Reverse engineering
• Oracle Developer 2000
– Outil graphique de développement propriétaire
– Intègre le produit SQL*Forms

Mars - Avril 2014 10

 Offre complète d’Oracle (fin)

• Oracle Inter-Office
– Outil de Workflow 
– Gestion des flux de documents électronique
– Concurrents : Lotus Notes et Exchange
• Oracle Portal
– Portail d’entreprise
– Point d’entrée unique de travail
• Certains produits sont aujourd’hui proposés en
standard avec Oracle 9i

Mars - Avril 2014 11

 SQL*Plus

sqlplus user@connect_string SAVE START

Password : *******
SQL> SELECT …….;
SQL> SAVE req
SQL> START req
req.sql
SQL> EXIT
Mars - Avril 2014 12
Le Langage de Définition
de Données

LDD
 Les ordres et les objets

• Ordre CREATE
– Création de la structure de l’objet  DD
• Ordre DROP
– Suppression des données et de la structure
• Ordre ALTER
– Modification de la structure (contenant)
• Syntaxe <Ordre> <Objet> < nom_objet>
• Objet TABLE
• Objet INDEX
• Objet CLUSTER
• Objet SEQUENCE

Mars - Avril 2014 14

 Objet Table et Contraintes
CREATE : Syntaxe

create table nom_table

(colonne1 type1(longueur1),
colonne2 type2(longueur2),
…………………….
constraint nom_contrainte1
type_contrainte1,
…………………….
);

PRIMARY KEY

3 Types de Contraintes FOREIGN KEY

CHECK (NOT NULL, UNIQUE)

Mars - Avril 2014 15

 Objet Table et Contraintes
Les types de données

VARCHAR(size) Données caractères de longueur variable

CHAR(size) Données caractères de longueur fixe
NUMBER(p,s) Numérique de longueur variable
DATE Valeurs de date et d'heure
LONG Données caractères de longueur variable (2 Go)
CLOB Données caractères (4 Go)
RAW Binaire
BLOB Binaire, jusqu'à 4 giga-octets
BFILE Binaire, stocké dans un fichier externe, (4 Go)

Mars - Avril 2014 16

 Objet Table et Contraintes
CREATE : Exemples

-- Table ‘Mère’

CREATE TABLE service

(IdService CHAR(3),
NomService VARCHAR(30),
CONSTRAINT pk_service
PRIMARY KEY(IdService)
);

Mars - Avril 2014 17

 Objet Table et Contraintes
CREATE : Exemples (suite)

-- Table ‘Fille’
CREATE TABLE employe
(IdEmploye NUMBER(5),
NomEmploye VARCHAR(30),
Indice NUMBER(3),
DateEmbauche DATE DEFAULT SYSDATE,
IdService CHAR(3)
CONSTRAINT nn_emp_ser NOT NULL,
CONSTRAINT pk_employe
PRIMARY KEY(IdEmploye),
CONSTRAINT fk_emp_ser FOREIGN KEY(IdService)
REFERENCES service(IdService),
CONSTRAINT ck_emp_indice CHECK
(indice BETWEEN 100 AND 900)
);

Mars - Avril 2014 18

 Objet Table : DROP

DROP TABLE nom_table;

Suppression complète de la table : définition et données

DROP TABLE nom_table CASCADE CONSTRAINTS;

Suppression aussi des contraintes de référence filles

Mars - Avril 2014 19

 Modification de la structure
ALTER TABLE

Ajout de colonnes

ALTER TABLE nom_table

ADD (colonne1 type1, colonne2 type2);

Modification de colonnes

ALTER TABLE nom_table

MODIFY (colonne1 type1, colonne2 type2);

Suppression de colonnes
ALTER TABLE nom_table
DROP COLUMN (colonne1, colonne2);

Mars - Avril 2014 20

 ALTER TABLE
Exemples de modifications

ALTER TABLE client

ADD ChiffreAffaire NUMBER (10,2);

ALTER TABLE client MODIFY nom VARCHAR(60);

ALTER TABLE etudiant

MODIFY idDiplome CONSTRAINT nn_etu_dip NOT NULL;

ALTER TABLE client

DROP COLUMN ChiffreAffaire ;

Mars - Avril 2014 21

 Contraintes

constraint nomcontrainte
{ unique | primary key (col1[,col2]...)
| foreign key (col1[,col2]...)
references [schema].table (col1[,col2]...)
[ON DELETE CASCADE]
| check (condition) }

Attention : suppression de tous les fils !

Mars - Avril 2014 22

 Modification des contraintes
Ajout et Suppression

Ajout de contraintes

ALTER TABLE nom_table

ADD CONSTRAINT nom_contrainte
type_contrainte;

Comme à la création d’une table

Suppression de contraintes

ALTER TABLE nom_table

DROP CONSTRAINT nom_contrainte;

Mars - Avril 2014 23

 Modification des contraintes
Exemples

ALTER TABLE client

ADD CONSTRAINT fk_client_cat
FOREIGN KEY(idCat)
REFERENCES categorie(idCat);

ALTER TABLE client

DROP CONSTRAINT fk_client_cat;

Mars - Avril 2014 24

 Activation et désactivation
de contraintes

Désactivation de contraintes

ALTER TABLE nom_table

DISABLE CONSTRAINT nom_contrainte;

ALTER TABLE nom_table

DISABLE CONSTRAINT PRIMARY KEY;

Les contraintes existent toujours dans le dictionnaire de données

mais ne sont pas actives

Chargement de données volumineuses extérieures à la base

Mars - Avril 2014 25

 Activation d’une contrainte
désactivée

Activation de contraintes

ALTER TABLE nom_table

ENABLE CONSTRAINT nom_contrainte ;

ALTER TABLE nom_table

ENABLE CONSTRAINT PRIMARY KEY;

Mars - Avril 2014 26

 Ajout ou activation de contraintes :
Récupération des lignes en erreur

Création d’une table Rejets

CREATE TABLE rejets

(ligne rowid, Adresse ligne
proprietaire varchar(30),
nom_table varchar(30),
contrainte varchar(30));

Activation de contraintes

ALTER TABLE nom_table

ENABLE CONSTRAINT nom_contrainte
EXCEPTIONS INTO rejets;

Mars - Avril 2014 27

 Vérification de Contraintes différées

• Une contrainte peut-elle être différée ?

– NOT DEFERRABLE (par défaut)
– DEFERRABLE
• Comportement par défaut de la contrainte :
– INITIALLY IMMEDIATE (par défaut)
– INITIALLY DEFERRED
• Utiliser la clause SET CONSTRAINTS ou
ALTER SESSION SET CONSTRAINTS pour
modifier le comportement d'une contrainte

Mars - Avril 2014 28

 Vérification de Contraintes différées
Exemples
CREATE TABLE emps ...
dept_id NUMBER(6)
CONSTRAINT fk_emp_dept REFERENCES depts
DEFERRABLE INITIALLY IMMEDIATE);

SQL> INSERT INTO emps VALUES (1, ‘Laurent', 2)

*
ERROR at line 1:
ORA-02291: integrity constraint (MICHEL.FK_EMP_DEPT_ID)
violated - parent key not found ;

SET CONSTRAINTS ALL DEFERRED;

SQL> INSERT INTO emps VALUES (1, 'Laurent', 2);

1 row created.

SQL> COMMIT;
COMMIT
*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-02291: integrity constraint (MICHEL.FK_EMP_DEPT_ID)
violated - parent key not found
Mars - Avril 2014 29
 Les fichiers Index :
organisation en B-Arbre

Entrée d'index

Racine

Branche

En-tête d'entrée d'index

Longueur de la colonne
de la clé
Feuille
Valeur de la colonne de
la clé
ROWID
Mars - Avril 2014 30
 Création et suppression d’Index

Création d’un index Unique

CREATE UNIQUE INDEX nom_index

ON nom_table(colonne[,colonne2 …]);

Création d’un index non Unique

CREATE INDEX nom_index

ON nom_table(colonne[,colonne2 …]);

Suppression d’un index

DROP INDEX nom_index;

Mars - Avril 2014 31

 Apport des Index

• Respect de la 4NF : clé primaire  index unique

• Amélioration des accès (sélection) sur les
colonnes recherchées
• Optimisation des jointures (équi-jointure entre
une clé primaire et sa clé étrangère)
• Attention aux clés primaires composées
• Table USER_INDEXES du dictionnaire

Mars - Avril 2014 32

 Cluster : Jointure physique

no_dept nom_dept resp_dept Cluster Key

----- -------- ------- (no_dept)
10 Info Jean
DEPT 20 Ventes Xavier 10 Info Jean
30 Achats Paul 101 Sylvie Bloc1
102 Michel

20 Ventes Xavier
No_emp nom_emp no_dept
100 Pierre Bloc2
104 Corinne
------ ------- -------
100 Pierre 20
30 Achats Paul
EMP 101 Sylvie 10 Bloc3
102 Michel 10
104 Corinne 20

Tables DEPT et EMP Tables DEPT et EMP

non-clusterisées clusterisées
Mars - Avril 2014 33
 Création de Cluster (1)

1.Création du cluster

CREATE CLUSTER personnel

(no_dept NUMBER(3)) Taille du
bloc logique
SIZE 200 TABLESPACE ts1
STORAGE(INITIAL 5M NEXT 5M PCTINCREASE 0);

2.Création de l’index de cluster

CREATE INDEX idx_personnel

ON CLUSTER personnel
TABLESPACE tsx1
STORAGE(INITIAL 1M NEXT 1M PCTINCREASE 0);
Mars - Avril 2014 34
 Création de Cluster (2)

3- Création des tables dans le cluster

CREATE TABLE dept

(no_dept NUMBER(3)
CONSTRAINT pk_dept PRIMARY KEY,
nom_dept VARCHAR(30),resp_dept VARCHAR(30))
CLUSTER personnel(no_dept);

CREATE TABLE emp

(no_emp NUMBER(3) CONSTRAINT pk_emp PRIMARY KEY,
nom_emp VARCHAR(30),
no_dept NUMBER(3) REFERENCES dept(no_dept))
CLUSTER personnel(no_dept);
Mars - Avril 2014 35
 Administration des Clusters

Modification des paramètres de stockage et

de consommation d'espace d'un bloc

ALTER CLUSTER personnel

SIZE 300K STORAGE (NEXT 2M);
Suppression des Clusters

DROP CLUSTER personnel DROP TABLE emp;

INCLUDING TABLES; DROP TABLE dept;
DROP CLUSTER personnel;
OU

Mars - Avril 2014 36

 Objet Séquence

• Permet d’obtenir des valeurs incrémentales

• Équivalent des colonnes AUTO_INCREMENT de
MySql ou IDENTITY de SqlServer
• N’est pas associée à une colonne particulière
• Verrouillage automatique en cas de concurrence
d’accès
• Valeur suivante : <nom_séquence>.NEXTVAL
• Valeur courante : <nom_séquence>.CURRVAL

Mars - Avril 2014 37

 Objet Séquence
création et utilisation

CREATE SEQUENCE nom_séquence

START WITH valeur_départ
INCREMENT BY incrément;

INSERT INTO t1 VALUES

(nom_séquence.NEXTVAL, …..);
INSERT INTO t2 VALUES
(……….., nom_séquence.CURRVAL);

DROP SEQUENCE nom_séquence;

Mars - Avril 2014 38

 Objet Séquence
Exemple de mise en oeuvre

SQL> CREATE TABLE client (idClient NUMBER PRIMARY KEY,

2 nomClient VARCHAR(20));
Table créée.
SQL> CREATE TABLE compte (idCompte NUMBER PRIMARY KEY,
2 nomCompte VARCHAR(30), idClient REFERENCES client);
Table créée.
SQL> CREATE SEQUENCE seq_client START WITH 1 INCREMENT BY 1;
Séquence créée.
SQL> CREATE SEQUENCE seq_compte START WITH 1 INCREMENT BY 1;
Séquence créée.
SQL> INSERT INTO client VALUES(seq_client.NEXTVAL,'Michel');
1 ligne créée.
SQL> SELECT seq_client.CURRVAL FROM dual;
CURRVAL
----------
1
Mars - Avril 2014 39
 Objet Séquence
Exemple de mise en œuvre (suite)

SQL> INSERT INTO compte VALUES(seq_compte.NEXTVAL,'Compte

Courant Michel',seq_client.CURRVAL);
1 ligne créée.
SQL> INSERT INTO compte VALUES(seq_compte.NEXTVAL,'Compte
Epargne Michel',seq_client.CURRVAL);
1 ligne créée.
SQL> SELECT * FROM client;
IDCLIENT NOMCLIENT
---------- --------------------
1 Michel
SQL> SELECT * FROM compte;
IDCOMPTE NOMCOMPTE IDCLIENT
---------- ------------------------------ ----------
1 Compte Courant Michel 1
2 Compte Epargne Michel 1

Mars - Avril 2014 40

Le Langage de Manipulation
de Données

LMD
 Les ordres SQL de manipulation

• INSERT
– Insertion (ajout) de ligne(s) dans une table
– Utiliser SQL*LOAD pour des chargements externes
• UPDATE
– Mise à jour (modification) de une ou plusieurs colonnes
de une ou plusieurs lignes
• DELETE
– Suppression de une ou plusieurs lignes
• COMMIT / ROLLBACK
– Fin d’une transaction

Mars - Avril 2014 42

 INSERT

INSERT INTO nom_table [(liste des colonnes)]

VALUES (liste des valeurs);

Exemples :

INSERT INTO service (idSer, nomSer)

VALUES (50,’Réseaux et Systèmes’);

INSERT INTO service

VALUES (60,’Analyse et Conception’);

INSERT INTO service

INSERT INTO service
(idSer)
VALUES (60,NULL);
VALUES (60);

Mars - Avril 2014 43

 INSERT (suite)

Insert avec le contenu de une ou plusieurs tables

INSERT INTO etudiant_deug

SELECT * FROM etudiant
WHERE cycle = 1;

INSERT INTO etudiant_deug (nomd, prenomd, cycled)

SELECT nom,prenom,1 FROM etudiant
WHERE cycle = 1;

Mars - Avril 2014 44

 UPDATE

UPDATE nom_table
SET colonne1 = valeur1
[,colonne2 = valeur2 ….]
[ WHERE prédicat];
Exemples :

UPDATE employe
SET nom = ‘Michel’, adresse = ‘Toulouse’
WHERE idEmp = 100;

UPDATE employe
SET salaire = salaire * 1.1
WHERE idSer = ‘info’;

UPDATE synchronisés : voir LID plus loin

Mars - Avril 2014 45
 DELETE

DELETE FROM nom_table

[WHERE prédicat];

Exemples :

DELETE FROM employe

WHERE idEmp = 100;

DELETE FROM employe;

Mars - Avril 2014 46

 COMMIT / ROLLBACK

• Notion de transaction : ensemble fini d’actions

(update, delete et insert)
• Commit : point de confirmation dans la base
• Rollback (‘retour arrière’) : les actions sont
‘défaites’ jusqu’au dernier point de confirmation
• Le Commit peut être automatique (pas conseillé)

Voir la fonction d’Intégrité

Mars - Avril 2014 47

Le Langage d’Interrogation
de Données
LID
Mono-table
Mise en forme des résultats
 Sélection et Affichage
Mono Table

• Ordre SELECT
• Sélection : restriction sur les lignes
• Projection : fonctions de groupage et restrictions
sur les groupages
• Ordres de Tris
• Fonctions Chaînes et Calculs
• Mise en page des résultats

Mars - Avril 2014 49

 Ordre SELECT

SELECT {DISTINCT|*|col1[,col2,….]
[AS nom_col]}
FROM nom_de_table

WHERE <prédicat sur les lignes>

GROUP BY col1 [,col2,….]
HAVING <prédicat sur les groupages>
ORDER BY {col1 {ASC|DESC}
[,col2 …]| n°col }

Mars - Avril 2014 50

 Exemples de SELECT MONO (1)

SELECT * FROM emp

Toutes les colonnes
WHERE
idService IN (10,40,60)
AND (salaire BETWEEN 1000 AND 2000
OR Prédicat de restriction
indice > 750 )
AND UPPER(adresse) LIKE '%TOULOUSE%';

SELECT nome AS "Nom Employé",

sal AS "Salaire Mensuel",
Sal*12 AS "Salaire Annuel" FROM emp
WHERE idService NOT IN (10,40,60)

Mars - Avril 2014 51

 Exemples de SELECT MONO (2)
colonne virtuelle ROWNUM

SELECT * FROM emp

WHERE ROWNUM < 50; Colonne Virtuelle

SELECT ROWNUM,e.* FROM emp e

WHERE ROWNUM < 50;

Mars - Avril 2014 52

 Exemples de SELECT MONO (3)
tri du résultat

SELECT idService,nome,indice FROM emp

ORDER BY idService , indice DESC, nome;

SELECT idService,nome,indice FROM emp

ORDER BY 1 , 3 DESC, 2;

SELECT * FROM (
SELECT idService,nome,indice FROM emp
ORDER BY 1 , 3 DESC, 2)
WHERE ROWNUM < 15; Affichage des 15 premiers triés

Mars - Avril 2014 53

 Exemples de SELECT MONO (4)
clause DISTINCT

SELECT DISTINCT idSer FROM emp;

idEmp nomEmp idSer salaire

100 Michel 20 2000
200 Sylvie 10 3000 idSer
-----
(Projection)
300 Bernard 20 1000 10
20
400 Claude 10 2000
500 Thomas 10 1000

Mars - Avril 2014 54

 Clause de groupage : GROUP BY
fonctions de groupage

SELECT idSer, AVG(salaire), COUNT(*)

FROM emp
GROUP BY idSer;

idEmp nomEmp idSer salaire

100 Michel 20 2000
200 Sylvie 10 3000
300 Bernard 20 1000 idSer AVG(salaire) COUNT(*)
----- ------------ --------
400 Claude 10 2000 10 2000 3
20 1500 2
500 Thomas 10 1000

Mars - Avril 2014 55

 Restriction sur les groupages :
HAVING

SELECT idSer, AVG(salaire), COUNT(*)

FROM emp
GROUP BY idSer
HAVING COUNT(*) >2;
restriction
idEmp nomEmp idSer salaire du groupage 20

100 Michel 20 2000

200 Sylvie 10 3000
300 Bernard 20 1000 idSer AVG(salaire) COUNT(*)
----- ------------ --------
400 Claude 10 2000 10 2000 3

500 Thomas 10 1000

Mars - Avril 2014 56

 Mécanisme du GROUP BY et DISTINCT

• Si la colonne est indexée : la table n’est pas

utilisée  optimisation
• Si la colonne n’est pas indexée : tri sur la colonne
et regroupement  pas d’optimisation
• Utilisation d’un espace de travail si le volume est
important (tablespace temporaire)
• Les résultats sont triés de façon ascendante

Mars - Avril 2014 57

 Fonctions de groupages (agrégats)

AVG Moyenne
COUNT Nombre de lignes
GROUPING Composer des regroupements
(datawarehouse)
MAX Maximum
MIN Minimum
STDDEV Écart type
SUM Somme
VARIANCE Variance
 

Mars - Avril 2014 58

 Fonctions numériques (1)

ABS Valeur absolue

ACOS Arc cosinus (de –1 à 1)
ADD_MONTHS Ajoute des mois à une date
ATAN Arc tangente
CEIL Plus grand entier  n,
CEIL(15.7) donne 16
COS Cosinus
COSH Cosinus hyperbolique
EXP Retourne e à la puissance n
(e = 2.71828183)

Mars - Avril 2014 59

 Fonctions numériques (2)

FLOOR Tronque la partie fractionnaire

CEIL Arrondi à l’entier le plus
proche
MOD(m,n) Division entière de m par n
POWER(m,n) m puissance n
ROUND(m,n) Arrondi à une ou plusieurs
décimales
SIGN(n) Retourne le signe d’un nombre
SQRT Racine carré
SIN Sinus

Mars - Avril 2014 60

 Fonctions chaînes de caractères(1)

CHR Retourne le caractère ASCII

équivalent
CONCAT Concatène

INITCAP Premières lettres de chaque

mot en majuscule
LOWER Tout en minuscules
LPAD(c1,n,c2) Cadrage à gauche de c2 dans c1
et affichage de n caractères
LTRIM(c1,c2) Enlève c2 à c1 par la gauche
RTRIM(c1,c2) Enlève c2 à c1 par la droite
TRIM(c1,c2) Enlève c2 à c1 des 2 côtés
Mars - Avril 2014 61
 Fonctions chaînes de caractères(2)

REPLACE(c1,c2,c3) Remplace c2 par c3 dans c1

LPAD(c1,n,c2) Cadrage à gauche de c2 dans c1
SOUNDEX Compare des chaînes
phonétiquement (english only…)
SUBSTR(c,d,l) Extraction d’une sous-chaîne
dans c à partir de d et d’une
longueur de l
UPPER Tout en majuscules
T0_CHAR(entier) Transforme en chaîne
TO_NUMBER(chaîne) Transforme en entier

Mars - Avril 2014 62

 Fonctions pour les dates

ADD_MONTHS(d,n) Ajoute n mois à la date d

LAST_DAY(d) Retourne le dernier jour
du mois
MONTHS_BETWEEN(d1,d2) Retourne le nombre de mois
entre 2 dates
NEXT_DAY(d,chaine) Retourne le prochain jour
ouvrable
ROUND(FonctionDate) Arrondie une date
SYSDATE Date courante
TRUNC(FonctionDate) Tronque une date
TO_DATE(chaîne,format) Exemple :’DD-MM-YYYY’

Mars - Avril 2014 63

 Mise en forme des résultats

• Principe
– Amélioration de la présentation du résultat d’une requête SQL
• Formatage de colonnes
– Instruction COLUMN pour afficher des entêtes
• Formatage de pages
– Instruction BREAK pour gérer les ruptures
– Instruction COMPUTE avec les fonctions SUM, MIN, MAX, AVG,
STD, VAR, COUNT, NUM pour des calculs
– Directive SET avec les paramètres NEWPAGE, PAGESIZE,
LINESIZE pour la mise en page des états de sortie
– Instructions TTITLE, BTITLE pour l’affichage des titres des états
de sortie
• Ordres SQL*PLUS

Mars - Avril 2014 64

 Affichage avec COLUMN

COLUMN nom_colonne [HEADING texte] -

[JUSTIFY {LEFT | RIGHT | CENTER}] -
[NOPRINT | PRINT] [FORMAT An]

COLUMN nome FORMAT A(30) -

HEADING 'Nom des employés‘ -
JUSTIFY CENTER

COLUMN adresse NOPRINT

CLEAR COLUMN

Mars - Avril 2014 65

 Rupture de séquences : BREAK

BREAK ON nom_colonne SKIP n

Rupture sur la colonne

Nul pour les valeurs dupliquées
Saut de n lignes à chaque rupture

CLEAR BREAK

Mars - Avril 2014 66

 Exemple de BREAK

BREAK ON specialite SKIP 2 SPECIALITE Nom

SELECT specialite, ------------------ ----------
nom_chercheur bd michel
AS "Nom" FROM chercheur
ORDER BY 1,2;
CLEAR BREAK oo christian
claude
gilles

rx daniel
françois

si jacques
jean
Mars - Avril 2014 67
 Opérations de calculs

• Utilisation conjointe de BREAK et COMPUTE pour

programmer des calculs en fonction de ruptures
• Syntaxe
– COMPUTE fonction LABEL ‘label’ OF colonne ON
{colonne | ROW | REPORT}
• fonction : SUM, MIN, MAX, AVG, STD, VAR,
COUNT, NUM
– ROW : le calcul est exécuté pour chaque ligne
– REPORT : le calcul est exécuté à la fin du rapport
– CLEAR COMPUTE efface les COMPUTES

Mars - Avril 2014 68

 Exemple de COMPUTE

BREAK ON "Numéro Client "

compute sum label "Solde Client" -
of SOLDE_COMPTE on "Numéro Client"  
select num_client as "Numéro Client" ,
libelle_compte,solde_compte from comptes
order by 1;
CLEAR BREAK
CLEAR COMPUTE

Mars - Avril 2014 69

 Résultat de la requête

Numéro Client LIBELLE_COMPTE SOLDE_COMPTE

------------- ------------------------------ ------------
1 compte courant Tuffery 1200
compte épargne Tuffery 5800
************* ------------
Solde Client 7000
2 compte courant Nonne -250
livret a Nonne 440
************* ------------
Solde Client 190
3 compte courant Crampes 2000
livret a Crampes 500
compte épargne Crampes 750
************* ------------
Solde Client 3250

Mars - Avril 2014 70

 État de sortie

Entêtes de page 
TTITLE option {texte | variable}
option : COL n, SKIP n, TAB n,
LEFT, CENTER, RIGHT, BOLD FORMAT…
 
Variables 
SQL.LNO numéro de la ligne courante
SQL.PNO numéro de la ligne courante
SQL.RELEASE version d’Oracle
SQL.USER nom du user
 
Pieds de page
BTITLE option {texte | variable}
Mars - Avril 2014 71
 Exemples d’état de sortie

SET NEWPAGE 1
SET PAGESIZE 30
SET LINESIZE 80
COL JOUR NOPRINT NEW_VALUE AUJOURDHUI
TTITLE LEFT 'PAGE' SQL.PNO SKIP LEFT AUJOURDHUI -
SKIP CENTER ‘Comptes' SKIP CENTER 'par client' SKIP2
BTITLE CENTER 'Fin du rapport'
BREAK ON "Numéro Client"
COMPUTE SUM LABEL "Solde Compte" -
OF solde_compte ON "Numéro Client"  
SELECT TO_CHAR(sysdate,('DD/MM/YYYY')) AS jour,
num_client AS "Numéro Client" ,
libelle_compte,solde_compte FROM comptes
ORDER BY 1;
TTITLE OFF
BTITLE OFF
CLEAR BREAKS
CLEAR COMPUTES
CLEAR COLUMNS
Mars - Avril 2014 72
 Exemples d’état de sortie (suite)

PAGE 1
14/10/2003 Comptes
par client

Numéro Client LIBELLE_COMPTE SOLDE_COMPTE

------------- ------------------------------ ------------
1 compte courant Tuffery 1200
compte épargne Tuffery 5800
************* ------------
Solde Compte 7000
2 compte courant Soutou -250
livret a Soutou 440
************* ------------
Solde Compte 190
3 compte courant Teste 2000
livret a Teste 500
compte épargne Teste 750
************* ------------
Solde Compte 3250
Fin du rapport
Mars - Avril 2014 73
 Ordres d’entrée – sortie
Variables paramétrées

Appuyer sur Entrée pour continuer

PAUSE 'Texte affiché avec arrêt'

PROMPT 'Texte affiché sans arrêt'

ACCEPT variable [NUMBER | CHAR] -

[PROMPT [texte] | NOPROMPT] [HIDE]
SQL> ACCEPT nequipelue PROMPT 'Entrer le n°équipe : ' HIDE
Entrer le n°équipe : **
SELECT * FROM CHERCHEUR WHERE n_equipe='&nequipelue';
N_CHERCHEUR NOM_CHERCH SPECIALITE UNIVERSITE N_EQUIPE
----------- ---------- ---------- ---------- --------
c1 michel bd 2 e1
c3 claude oo 3 e1
c7 christian oo 2 e1
Mars - Avril 2014 74
 Variables d’environnement :
ordres SET variable valeur

TERMOUT { ON | OFF } Affichage

ECHO { ON | OFF } affichage des commandes
FEEDBACK n écho du nombre de lignes
traitées
SHOWMODE { ON | OFF } affichage des paramètres
HEADING { ON | OFF } affichage des titres
PAUSE { ON | OFF | arrêt en fin de page
texte}
SQLN { ON | OFF } numéro de ligne du buffer
VERIFY { ON | OFF } affichage des substitutions

Show all  variables courantes

Mars - Avril 2014 75

 Gestion des fichiers SPOOL

SQL> SPOOL fichier

SQL> -- ordres sql
SQL> trace écran
SQL>
SQL> SPOOL OFF

fichier.lst

Mars - Avril 2014 76

Le Langage d’Interrogation
de Données
LID
Multi-table
Jointures
 Les différentes méthodes de Jointure

• Opérateurs ensemblistes
– UNION, INTERSECT et MINUS
• Jointure en forme procédurale déconnectée
– SELECT imbriqués déconnectés
• Jointure en forme procédurale synchronisée
– SELECT imbriqués synchronisés
• Jointure en forme non procédurale ou relationnelle
– Pas d’imbrication : 1 seul bloc SELECT-FROM-WHERE
– Jointure complète ou externe (gauche ou droite)
• Requêtes hiérarchiques
– Parcours d’arbre : association réflexive 1-N

Mars - Avril 2014 78

 L’opérateur ensembliste UNION

SELECT liste de colonnes FROM table1

UNION
SELECT liste de colonnes FROM table2;

SELECT idPro FROM produits_fabriques

UNION
SELECT codePro FROM produits_achetes;

Même nombre de colonnes

Mêmes types de colonnes
Tri (ORDER BY) sur le n° de colonne
Le DISTINCT est automatique
Mars - Avril 2014 79
 L’opérateur ensembliste INTERSECT

SELECT liste de colonnes FROM table1

INTERSECT
SELECT liste de colonnes FROM table2;

SELECT idPro FROM produits_fabriques

INTERSECT
SELECT codePro FROM produits_achetes;

Même nombre de colonnes

Mêmes types de colonnes
Tri (ORDER BY) sur le n° de colonne
Le DISTINCT est automatique
Mars - Avril 2014 80
 L’opérateur ensembliste MINUS

SELECT liste de colonnes FROM table1

MINUS
SELECT liste de colonnes FROM table2;

SELECT idService FROM service

MINUS
SELECT idService FROM employe;

Attention à l’ordre des SELECT

Mars - Avril 2014 81

 Composition d’opérateurs ensemblistes

SELECT liste de colonnes FROM table1

INTERSECT
(SELECT liste de colonnes FROM table2
MINUS
SELECT liste de colonnes FROM table3);

Attention à l’ordre d’exécution  parenthèses

Mars - Avril 2014 82

 Jointure forme procédurale déconnectée
imbrication de SELECT

SELECT liste de colonnes(*) FROM table1

WHERE col1 [NOT] [IN] [<,>,!=,=,….][ANY|ALL]
(SELECT col2 FROM table2
[WHERE prédicat ]);

•Col1 et col2 sont les colonnes de jointure

•[NOT] IN : si le sous-select peut ramener
plusieurs lignes
• Ne sélectionne que des colonnes de la
première table (*)
• Exécute d’abord le sous-select puis ‘remonte’
en respectant le graphe relationnel
Mars - Avril 2014 83
 Jointure forme procédurale déconnectée
Exemple 1 : ouvrages empruntés

SELECT titre,auteur FROM ouvrage

WHERE idOuvrage IN
(SELECT idOuvrage FROM emprunt
WHERE dateEmprunt = SYSDATE
AND idEtudiant IN
(SELECT idEtudiant FROM etudiant
WHERE ufr='Informatique'));

•Du select le plus imbriqué vers le select le plus

haut
• Opérateur ‘IN’ obligatoire

Mars - Avril 2014 84

 Jointure forme procédurale déconnectée
Exemple 2 : employés les mieux payés

SELECT nom,fonction FROM emp

WHERE salaire =
(SELECT MAX(salaire) FROM emp);

•Le select imbriqué ne ramène qu’une valeur

•Signe ‘ =’ autorisé
•Cette forme est obligatoire pour obtenir ce résultat

Mars - Avril 2014 85

 Jointure forme procédurale déconnectée
Exemple 3 : employé responsable

SELECT nom,indice,salaire FROM emp

WHERE idEmp =
(SELECT idResp FROM emp
WHERE idEmp=&idlu);

•Signe ‘=‘ autorisé

•Jointure réflexive

Mars - Avril 2014 86

 Jointure forme procédurale déconnectée
Avantages et inconvénients

• Parfois obligatoire (exemple 2)

• Mise en œuvre naturelle du graphe de requêtes
• Affichage final : uniquement les colonnes de la première
table
• Écriture lourde si le niveau d’imbrication est important :
chemin d’accès à la charge du développeur
• Jointures
• Équi-jointures avec = ou IN
• Autres jointures (>,<, ….)
• ANY : au moins un élément (=ANY  IN)
• ALL : tous les éléments (!=ALL  NOT IN)

Mars - Avril 2014 87

 Jointure forme procédurale déconnectée
Exemple 4 : ALL

SELECT nom,indice,salaire FROM emp

WHERE salaire > ALL
(SELECT salaire FROM emp
WHERE idService = 10);

•Employés gagnant plus que tous les employés

du service 10 ?
•Requête plus ‘naturelle’ avec la fonction MAX …

Mars - Avril 2014 88

Jointure en forme procédurale synchronisée
Présentation

• Contrairement à la forme précédente, le sous-

select est ‘synchronisé’ avec l’ordre du dessus
(select ou update)
• Les deux ordres (dessus et dessous) fonctionnent
comme deux boucles imbriquées
• Pour 1 pas de la boucle supérieure  n pas de la
boucle inférieure

t1 SELECT ….. FROM table t1

WHERE …..
t2
(SELECT ……. FROM table t2
WHERE t1.col1 = t2.col2);

Mars - Avril 2014 89

Jointure en forme procédurale synchronisée
Exemple 1

• Employés ne travaillant pas dans le même

service que leur responsable ?
EMPLOYE
idEmp nomEmp idSer idResp SELECT e.nomEmp FROM emp e
WHERE e.idSer !=
100 Jean 50 200 (SELECT r.idSer FROM emp r
200 Sylvie 25 WHERE r.idEmp=e.idResp)
AND e.idResp IS NOT NULL;
300 Xavier 25 200
nomEmp
400 Claude 30 200
Jean
500 Thomas 25 200
Claude
e
r
Mars - Avril 2014 90
Jointure en forme procédurale synchronisée
Exemple 2 : UPDATE automatique

• Mise à jour automatique du nombre d’employés

(nbEmp) de la table Service ?

SERVICE
idSer nomSer nbEmp
UPDATE service s
25 Info 3 SET s.nbEmp =
(SELECT COUNT(e.idEmp) FROM emp e
30 Stat 1 WHERE e.idSer=s.idSer);

50 Maths 1

60 Bd 0 s
e

Mars - Avril 2014 91

Jointure en forme procédurale synchronisée
Test d’existence : [NOT] EXISTS

• Le prédicat ‘EXISTS’ est ‘vrai’ si le sous-select ramène au

moins une ligne
• NOT EXISTS  ensemble vide
• Permet de mettre en œuvre l’opérateur relationnel de
DIVISION
• Principe :

SELECT colonnes résultats FROM table1 t1

WHERE [NOT] EXISTS
(SELECT {col | valeur} FROM table2 t2
WHERE t1.col1=t2.col2);

Mars - Avril 2014 92

 Jointure en forme procédurale synchronisée
Exemple 3 : Test d’existence

• Les employés (nomEmp) travaillant dans un service

contenant au moins un ‘programmeur’ ?

EMPLOYE SELECT e1.nomEmp FROM emp e1

idEmp nomEmp idSer fonction WHERE EXISTS
concepteur
(SELECT 'trouvé' FROM emp e2
100 Jean 50 WHERE e1.idSer=e2.idSer
200 Sylvie 25 analyste AND
e2.fonction='programmeur');
300 Xavier 25 concepteur

400 Claude 30 analyste

nomEmp
500 Thomas 25 programmeur Sylvie
Xavier
Thomas
Mars - Avril 2014 93
Jointure en forme procédurale synchronisée
Mise en œuvre de la DIVISION

• DIVISION : un ensemble A est entièrement compris dans un

autre (B)
• Un ensemble (A) est entièrement compris dans un autre (B)
si :

– A–B= ou NOT EXISTS

• Égalité parfaite de 2 ensembles (A et B) si :

– A–B=
et
B–A=

Mars - Avril 2014 94

Jointure en forme procédurale synchronisée
Exemple 4 : DIVISION

• Étudiants (idEtu) inscrits aux mêmes UV que

l’étudiant ‘100’ ?
COURS SELECT c1.idEtu FROM cours c1
idEtu UV WHERE NOT EXISTS
(SELECT c100.UV FROM cours c100
100 UV1 WHERE c100.idEtu=100
200 UV1 MINUS
100 UV3 SELECT c2.UV FROM cours c2
WHERE c2.idEtu=c1.idEtu)
300 UV1 AND c1.idEtu <> 100;
200 UV4
idEtu
300 UV2
300
300 UV3

Mars - Avril 2014 95

 Jointure en forme Relationnelle

• Pas d’imbrication, colonnes résultats de toutes

les tables intervenant
• Le chemin d’accès (graphe) est à la charge du
SGBD
• Un seul bloc SELECT – FROM – WHERE
• Présentation d’une EQUI-JOINTURE :

SELECT colonnes résultats (des 3 tables)

FROM table1 t1, table2 t2, table3 t3
WHERE t1.col1=t2.col2 AND t2.col2=t3.col3;

N tables  N-1 Jointures

Mars - Avril 2014 96

 Jointure en forme Relationnelle
Exemple 1 : ouvrages empruntés

• Reprise du 1er exemple de la forme procédurale

BREAK ON nom
SELECT et.nom,o.titre,o.auteur
FROM ouvrage o, emprunt ep, etudiant et
WHERE et.ufr='Informatique' AND
et.idEtudiant=ep.idEtudiant AND
ep.dateEmprunt = SYSDATE AND
ep.idOuvrage=o.idOuvrage
ORDER BY 1,2;
CLEAR BREAK

Colonnes de tables différentes : impossible en procédural

Mars - Avril 2014 97

 Jointure en forme Relationnelle
Exemple 2 : employé responsable

• Nom de l’employé responsable d’un employé ?

SET HEADING OFF

ACCEPT idlu PROMPT 'Entrer le n°
employé:'
SELECT er.nom||'Responsable de'||ee.nom
FROM emp er, emp ee
WHERE er.idEmp = ee.idResp
AND ee.idEmp = &idlu;
SET HEADING ON

Mars - Avril 2014 98

 Jointure en forme Relationnelle
Exemple 3 : autre jointure

• Autre jointure que l’ EQUI-JOINTURE

• Employés gagnant plus que ‘Michel’ ?

COLUMN n HEADING 'Nom employé'

COLUMN s1 HEADING 'Son salaire'
COLUMN s2 HEADING 'Salaire de Michel'
SELECT e.nom as n,e.salaire as s1,
m.salaire as s2
FROM emp e, emp m
WHERE m.nom='Michel'
AND e.salaire > m.salaire;
CLEAR COLUMNS
Mars - Avril 2014 99
 Jointure en forme Relationnelle
Jointure Externe ou Complète
• Lignes de l’EQUI-JOINTURE plus les lignes n’ayant pas de
correspondantes (+)
• Équivalent du LEFT JOIN de certains SGBD, OUTER JOIN pour Oracle

SELECT s.nomSer AS 'Nom du Service',

e.nomEmp AS 'Employés'
FROM emp e, service s
WHERE s.idSer = e.idSer(+);
Nom du Service Employés
Info Michel Aucun employé
Info jean (valeur nulle)
Stat xavier
Bd
Mars - Avril 2014 100
 Jointure Externe ou Complète
Exemple d’utilisation

• Services n’ayant pas d’employés ?

SELECT s.nomSer AS 'Nom du Service'

FROM emp e, service s
WHERE s.idSer = e.idSer(+)
AND e.idEmp IS NULL;

Mars - Avril 2014 101

 Requêtes hiérarchiques

• Extraction de données provenant d’un parcours

d’arbre
• Association de type Hiérarchique (mère-fille)
réflexif à plusieurs niveaux
• Syntaxe :
SELECT [LEVEL], colonne, expression...
FROM table
[WHERE condition(s)]
[START WITH condition(s)]
[CONNECT BY PRIOR condition(s)];

Mars - Avril 2014 102

 Requêtes hiérarchiques
Syntaxe d’une requête

• Les clauses CONNECT BY et START WITH identifient les requêtes

hiérarchiques
• La pseudo-colonne LEVEL indique le numéro de niveau (1 pour
le nœud racine, 2 représente un enfant de la racine...)
• FROM table : sélection à partir d'une seule table
• START WITH spécifie les lignes racine de la hiérarchie ou point
de départ (clause obligatoire)
• CONNECT BY indique les colonnes où il existe une relation entre
des lignes parent et enfant
• PRIOR indique la direction du parcours de l'arbre, permet
également d’éliminer certaines branches de l’arborescence
• L’ordre SELECT ne peut pas contenir de jointure ou être basé
sur une vue issue d’une jointure
• Remarque : Il manque un ordre de fin de parcours d’arbre

Mars - Avril 2014 103

 Requêtes hiérarchiques
exemple : table scott.emp

SELECT empno AS "Employé",

ename AS "Nom",job AS "Travail",
mgr AS "Responsable" FROM emp;
Employé Nom Travail Responsable
---------- ---------- --------- -----------
7369 SMITH CLERK 7902
7499 ALLEN SALESMAN 7698
7521 WARD SALESMAN 7698
7566 JONES MANAGER 7839
7654 MARTIN SALESMAN 7698
7698 BLAKE MANAGER 7839
7782 CLARK MANAGER 7839
7788 SCOTT ANALYST 7566
7839 KING PRESIDENT
7844 TURNER SALESMAN 7698
7876 ADAMS CLERK 7788
7900 JAMES CLERK 7698
7902 FORD ANALYST 7566
7934 MILLER CLERK 7782

14 ligne(s) sélectionnée(s).
Mars - Avril 2014 104
 Requêtes hiérarchiques
mise en oeuvre

• Parcours de l’arbre (CONNECT BY PRIOR)

– Bas vers le haut : clé père = clé fils
– Haut vers le bas : clé fils = clé père
• Point de départ (START WITH)
– Prédicat simple (START WITH ename = ‘BLAKE’)
– Prédicat contenant une sous-interrogation :
(START WITH empno =
(SELECT empno FROM emp
WHERE ename = ’Michel’))

Mars - Avril 2014 105

 Requêtes hiérarchiques
exemple (1)

SELECT empno,ename,job,mgr
FROM emp
START WITH empno=7654 Départ
CONNECT BY PRIOR mgr = empno;

Du bas vers le haut

EMPNO ENAME JOB MGR

---------- ---------- --------- ----------
7654 MARTIN SALESMAN 7698
7698 BLAKE MANAGER 7839
7839 KING PRESIDENT

Mars - Avril 2014 106

 Utilisation de la fonction LPAD

SELECT LPAD('Texte à droite',30,'*-')

AS "Ex. avec 30 car. affichés" FROM dual;

Ex. avec 30 car. affichés

-------------------------------
*-*-*-*-*-*-*-*-Texte à droite

SELECT LPAD(' ',3*2) || 'Indentation

de 6' AS "ex. de LPAD" FROM DUAL;

ex. de LPAD
----------------------
Indentation de 6

Mars - Avril 2014 107

 Requêtes hiérarchiques
exemple final

COLUMN organisation FORMAT A15 HEADING "Organigramme"

SELECT LPAD(' ', 3 * LEVEL-3) || ename AS organisation,
LEVEL, empno FROM emp
CONNECT BY PRIOR empno = mgr
START WITH mgr is NULL;

Organigramme LEVEL EMPNO

--------------- ---------- ----------
KING 1 7839
JONES 2 7566
SCOTT 3 7788
ADAMS 4 7876
FORD 3 7902
SMITH 4 7369
BLAKE 2 7698
ALLEN 3 7499
WARD 3 7521
MARTIN 3 7654
TURNER 3 7844
JAMES 3 7900
CLARK 2 7782
MILLER 3 7934
Mars - Avril 2014 108
 7 Façons de résoudre une requête ……

• Étudiants (nom) ayant emprunté un ouvrage

particulier (&ouvlu) ?
SELECT e.nom
FROM etudiant e
WHERE e.idEtu IN
1 (SELECT o.idEtu FROM emprunt ep
WHERE ep.idOuv = '&ouvlu');
SELECT e.nom
FROM etudiant e
2 WHERE e.idEtu = ANY
(SELECT o.idEtu FROM emprunt ep
WHERE ep.idOuv = '&ouvlu');
Mars - Avril 2014 109
 7 Façons de résoudre une requête (suite)

SELECT e.nom
FROM etudiant e
WHERE EXISTS
3 (SELECT 'trouvé' FROM emprunt ep
WHERE ep.idOuv = '&ouvlu‘
AND e.idEtu = ep.idEtu);

SELECT e.nom
FROM etudiant e, emprunt ep
4 WHERE ep.idOuv = '&ouvlu'
AND e.idEtu = ep.idEtu;

Mars - Avril 2014 110

 7 Façons de résoudre une requête (suite)

SELECT e.nom
FROM etudiant e
WHERE 0 <
5 (SELECT COUNT(*) FROM emprunt ep
WHERE ep.idOuv = '&ouvlu'
AND e.idEtu = ep.idEtu);

SELECT e.nom
FROM etudiant e
6 WHERE '&ouvlu' IN
(SELECT ep.idOuv FROM emprunt ep
WHERE e.idEtu = ep.idEtu);

Mars - Avril 2014 111

 7 Façons de résoudre une requête (fin)

SELECT e.nom
FROM etudiant e
WHERE '&ouvlu' = ANY
7
(SELECT ep.idOuv FROM emprunt ep
WHERE e.idEtu = ep.idEtu);

Je préfère
la 4 !!

Mars - Avril 2014 112

Optimisation des Requêtes

Plan d’exécution
EXPLAIN PLAN
 Optimisation des requêtes :
Visualisation du graphe

• Oracle conserve la trace du chemin d’accès d’une

requête dans une table appelée « plan_table »
• Tous les graphes des requêtes du LID et LMD
sont conservées :
– SELECT, UPDATE , INSERT et DELETE
• Chaque opération sur un objet est notée avec :
– L’ordre d’exécution
– Le parent (arbre relationnel ou algébrique)
– L’objet consulté (table, index, cluster, view, …)
– Le type d’opération

Mars - Avril 2014 114

 Structure de PLAN_TABLE

STATEMENT_ID VARCHAR2(30) id choisi

TIMESTAMP DATE date d’exécution
REMARKS VARCHAR2(80)
OPERATION VARCHAR2(30) opération (plus loin)
OPTIONS VARCHAR2(30) option de l’opération
OBJECT_NODE VARCHAR2(128) pour le réparti
OBJECT_OWNER VARCHAR2(30) propriétaire
OBJECT_NAME VARCHAR2(30) nom objet (table, index, ….
OBJECT_INSTANCE NUMBER(38)
OBJECT_TYPE VARCHAR2(30) (unique, …)
OPTIMIZER VARCHAR2(255)
SEARCH_COLUMNS NUMBER
ID NUMBER(38) n° nœud courant
PARENT_ID NUMBER(38) n° nœud parent
POSITION NUMBER(38) 1 ou 2 (gauche ou droite)
COST NUMBER(38)
CARDINALITY NUMBER(38)
BYTES NUMBER(38)
OTHER_TAG VARCHAR2(255)
PARTITION_START VARCHAR2(255)
PARTITION_STOP VARCHAR2(255)
PARTITION_ID NUMBER(38)
OTHER LONG

Mars - Avril 2014 115

 Opérations / Options (1)

Operation Option  Description 

AND- Operation accepting multiple sets of rowids, returning the intersection of the
EQUAL sets, eliminating duplicates. Used for the single-column indexes access path. 

TO ROWIDS converts bitmap representations to actual rowids that can be used to

CONVER- access the table.
SION FROM ROWIDS converts the rowids to a bitmap representation
COUNT returns the number of rowids if the actual values are not needed  
SINGLE VALUE looks up the bitmap for a single key value in the index.
INDEX RANGE SCAN retrieves bitmaps for a key value range.
FULL SCAN performs a full scan of a bitmap index if there is no start or stop key. 

MERGE Merges several bitmaps resulting from a range scan into one bitmap

Subtracts bits of one bitmap from another. Row source is used for negated
MINUS predicates. Can be used only if there are nonnegated predicates yielding a
bitmap from which the subtraction can take place

OR Computes the bitwise OR of two bitmaps

CONNECT
Retrieves rows in hierarchical order for a query containing a CONNECT BY clause
BY
Mars - Avril 2014 116
 Opérations / Options (2)

Operation Option  Description 

CONCATENA Operation accepting multiple sets of rows returning the union-all of
TION the sets

COUNT Operation counting the number of rows selected from a table

Operation accepting a set of rows, eliminates some of them, and

FILTER
returns the rest

FIRST ROW Retrieval on only the first row selected by a query

Operation retrieving and locking the rows selected by a query

FOR UPDATE
containing a FOR UPDATE clause

HASH JOIN Operation joining two sets of rows and returning the result

SEMI Hash semi-join

Mars - Avril 2014 117

 Opérations / Options (3)

Operation Option  Description 

INDEX UNIQUE SCAN Retrieval of a single rowid from an index

Retrieval of one or more rowids from an index. Indexed values

RANGE SCAN
are scanned in ascending order
RANGE SCAN Retrieval of one or more rowids from an index. Indexed values
DESCENDING are scanned in descending order
INLIST Iterates over the operation below it for each value in the IN-
ITERATOR list predicate
INTERSECT Operation accepting two sets of rows and returning the
ION intersection of the sets, eliminating duplicates
Operation accepting two sets of rows, each sorted by a
MERGE
specific value, combining each row from one set with the
JOIN
matching rows from the other, and returning the result

OUTER Merge join operation to perform an outer join statement

Mars - Avril 2014 118

 Opérations / Options (4)

Operation Option  Description 

Operation accepting two sets of rows and returning rows
MINUS appearing in the first set but not in the second, eliminating
duplicates
NESTED Operation accepting two sets of rows, an outer set and an
LOOPS inner set. Oracle compares each row of the outer set with
(These are join each row of the inner set, returning rows that satisfy a
operations)  condition. 
Retrieval of a single row that is the result of applying a group
SORT AGGREGATE
function to a group of selected rows

UNIQUE Operation sorting a set of rows to eliminate duplicates

Operation sorting a set of rows into groups for a query with a

GROUP BY
GROUP BY clause

JOIN Operation sorting a set of rows before a merge-join

Operation sorting a set of rows for a query with an ORDER BY

ORDER BY
clause

Mars - Avril 2014 119

 Opérations / Options (5)

Operation Option  Description 

TABLE
FULL Retrieval of all rows from a table
ACCESS
Retrieval of rows from a table based on a value of an indexed
CLUSTER
cluster key

HASH Retrieval of rows from table based on hash cluster key value

BY ROWID Retrieval of a row from a table based on its rowid

BY INDEX If the table is nonpartitioned and rows are located using

ROWID index(es)
Operation accepting two sets of rows and returns the union of
UNION
the sets, eliminating duplicates
Operation performing a view's query and then returning the
VIEW
resulting rows to another operation

Mars - Avril 2014 120

 Visualisation du graphe
Mise en œuvre (1)

• Calcul du plan d’exécution  remplissage dans

PLAN_TABLE
explain plan
set statement_id='r1'
for
select * from comptes
where num_client IN
(select num_client from clients
where nom_client = 'Tuffery');
column operation format a18
column options format a15
column object_name heading OBJET format a13
column id format 99
column parent_id format 99
column position format 99
select operation,options,object_name,id,parent_id,position
from plan_table
where statement_id='r1'
order by id;
Mars - Avril 2014 121
 Visualisation du graphe
Mise en œuvre (2)

• Contenu de PLAN_TABLE
OPERATION OPTIONS OBJET ID PARENT_ID POSITION
------------------ --------------- ---------- --- --------- --------
SELECT STATEMENT 0
NESTED LOOPS 1 0 1
TABLE ACCESS FULL COMPTES 2 1 1
TABLE ACCESS BY INDEX ROWID CLIENTS 3 1 2
INDEX UNIQUE SCAN PK_CLIENT 4 3 1

• Graphe correspondant
TABLE ACCESS

INDEX UNIQUE SCAN BY INDEX ROWID

4 3 NESTED LOOPS SELECT STATEMENT

PK_CLIENT CLIENTS
1 0
TABLE ACCESS FULL

2
COMPTES
Mars - Avril 2014 122
 Visualisation du graphe
autres exemples (1)

select * from clients

where num_client NOT IN
(select num_client from comptes);

OPERATION OPTIONS OBJET ID PARENT_ID POSITION

------------------ -------- -------- --- --------- --------
SELECT STATEMENT 0
FILTER 1 0 1
TABLE ACCESS FULL CLIENTS 2 1 1
TABLE ACCESS FULL COMPTES 3 1 2

Mars - Avril 2014 123

 Visualisation du graphe
autres exemples (2)
select * from clients
where num_client IN
(select num_client from clients
minus
select num_client from comptes);

OPERATION OPTIONS OBJET ID PARENT_ID POSITION

------------------ --------------- --------- --- --------- --------
SELECT STATEMENT 0
NESTED LOOPS 1 0 1
VIEW VW_NSO_1 2 1 1
MINUS 3 2 1
SORT UNIQUE 4 3 1
TABLE ACCESS FULL CLIENTS 5 4 1
SORT UNIQUE 6 3 2
TABLE ACCESS FULL COMPTES 7 6 1
TABLE ACCESS BY INDEX ROWID CLIENTS 8 1 2
INDEX UNIQUE SCAN PK_CLIENT 9 8 1
Mars - Avril 2014 124
 Visualisation du graphe
indentation de plan table
• Exemple précédent : indentation
select lpad(' ',2*level)||operation||
options||' ('||object_name||')'
"plan de la requête"
from plan_table where statement_id='r3'
connect by prior id=parent_id
start with id=0;

plan de la requête
------------------------------------------
SELECT STATEMENT ()
NESTED LOOPS ()
VIEW (VW_NSO_1)
MINUS ()
SORTUNIQUE ()
TABLE ACCESSFULL (CLIENTS)
SORTUNIQUE ()
TABLE ACCESSFULL (COMPTES)
TABLE ACCESSBY INDEX ROWID (CLIENTS)
INDEXUNIQUE SCAN (PK_CLIENT)

Mars - Avril 2014 125

Le Langage de Contrôle
de Données

LCD
 Contrôle des Données

• Notion de Sous–Schéma
– Restriction de la vision
– Restriction des actions
• Privilèges
– Systèmes
– Objets
• Rôles
– Regroupement de privilèges
• Contraintes évènementielles : Trigger (plus loin)
– Contrôles avant une modification
– Mises à jour automatique

Mars - Avril 2014 127

 Restreindre les accès à une BD

Tout le monde ne peut pas VOIR

et FAIRE n’importe quoi

de la vision VIEW

RESTRICTION

des actions GRANT

Mars - Avril 2014 128

 Restriction des accès
Sous-schéma ou schéma externe

DBA
Utilisateur2

BD
Utilisateur1

Mars - Avril 2014 129

 L’objet VUE

• Une VUE est une table virtuelle : aucune

implémentation physique de ses données
• La définition de la vue est enregistrée dans le DD
• A chaque appel d’une vue : le SGBD réactive sa
construction à partir du DD
• Vue mono-table : crée à partir d’une table
– Modifications possibles  modifications dans la table
– Jointure en forme procédurale autorisée
• Vue multi-table
– Crée par une jointure en forme relationnelle
– Aucune modification autorisée

Mars - Avril 2014 130

 Utilisation d’une VUE

• Simplification de requêtes pour des non

spécialistes
• Création de résultats intermédiaires pour des
requêtes complexes
• Présentation différente de la base de données :
schéma externe
• Mise en place de la confidentialité (VOIR)
• Une vue mono-table pourra être mise à jour avec
contraintes

Mars - Avril 2014 131

 Création et suppression
d’une VUE

• Création d’une vue

CREATE [( OR REPLACE )] VIEW nom_vue
[( liste des colonnes de la vue )]
AS Sélection de lignes et
SELECT ………… colonnes
[WITH CHECK OPTION [CONSTRAINT nom_cont]];
• Suppression d’une vue
DOP VIEW nom_vue;

• Pas de modification d’une vue

Mars - Avril 2014 132

 Exemples de création de VUES (1)

• Vue mono-table avec restriction horizontale

CREATE VIEW enseignant_info AS
SELECT * FROM enseignant 1 Table
WHERE idDip IN
(SELECT idDip FROM diplome
WHERE UPPER(nomDiplome) LIKE '%INFO%');

• Vue mono-table avec restriction verticale

CREATE VIEW etudiant_scol AS

SELECT idEtu,nomEtu,adrEtu,idDip FROM etudiant;

Mars - Avril 2014 133

 Exemples de création de VUES (2)

• Vue mono-table avec restriction mixte

CREATE VIEW etudiant_info

(numEtudiant,nomEtudiant,adrEtudiant,dip) AS
SELECT idEtu,nomEtu,adrEtu,idDip
FROM etudiant
Nouveaux noms
WHERE idDip IN
(SELECT idDip FROM diplome
WHERE UPPER(nomDiplome) LIKE '%INFO%');

Mars - Avril 2014 134

 Exemples de création de VUES (3)

• Vue mono-table avec colonnes virtuelles

CREATE VIEW employe_salaire

(ne,nome,mensuel,annuel,journalier) AS
SELECT idEmp,nomEmp,sal,sal*12,sal/22
FROM employe;
• Pas de modification sur les colonnes virtuelles
• Modifications autorisées sur les colonnes de
base  mise à jour instantanée !

Mars - Avril 2014 135

 Exemples de création de VUES (4)

• Vue mono-table avec groupage

CREATE VIEW emp_service
(ns,nombreEmp,moyenneSal) AS
SELECT idService,COUNT(*),AVG(sal)FROM employe
GROUP BY idService;

• Utilisation de la vue  reconstruction

SELECT * FROM emp_service WHERE nombreEmp>5;

SELECT idService AS ns,COUNT(*) AS nombreEmp,

AVG(sal) AS moyenneSal FROM employe
GROUP BY ns HAVING COUNT(*) > 5;
Mars - Avril 2014 136
 Vues multi-tables

• Simplification de requêtes
• Pas de modifications possibles dans ce type de
vue ( voir trigger ‘instead of’)
• Tables temporaires ‘virtuelles’ de travail
• Transformation de la présentation des données
Schéma externe
CREATE VIEW emp_ser(nom_service, nom_employe)
AS SELECT s.noms,e.nome FROM emp e,service s
WHERE e.idSer=s.idSer;

2 Tables

Mars - Avril 2014 137

 Exemples de vues multi-tables

• Reconstitution des clients (UNION)

CREATE VIEW clients(idCli,nom,….,secteur) AS
SELECT ct.*,’T’ FROM clients_toulouse ct
UNION
SELECT cb.*,’B’ FROM clients_bordeaux cb
UNION
SELECT cm.*,’M’ FROM clients_montpellier cm;
• Reconstitution des étudiants (JOINTURE)
CREATE VIEW etudiants(idEtu,nom,adresse,
nomstage,entrstage) AS
SELECT e.id,e.nom,e.adr,s.nomS,s.entrS
FROM etudiant e,stage s WHERE e.id=s.id;
Mars - Avril 2014 138
 Vues avec Contraintes
WITH CHECK OPTION

• Principe : le prédicat de sélection de lignes se

transforme en contrainte
• Mise en place de contraintes spécifiques :

Table T
Vue VT

Contraintes
Contraintes
génériques
spécifiques

Mars - Avril 2014 139

 Exemple de vue avec Contrainte

• Les employés informaticiens ont des contraintes avantageuses …

CREATE VIEW emp_info AS

SELECT * FROM emp
WHERE idSer IN
(SELECT idSer FROM service
WHERE UPPER(nomSer) LIKE '%INFO%')
AND sal > 3500
AND prime BETWEEN 500 AND 1000
WITH
• On CHECK
ne pourra OPTION
pas insérer CONSTRAINT cko_emp_info;
un employé informaticien qui ne
correspond pas aux contraintes du prédicat

Mars - Avril 2014 140

 Restriction des Actions :
Les privilèges

• Privilèges Système et objet

• Contrôler l’accès à la base de données
• Sécurité système : couvre l'accès à la base de
données et son utilisation au niveau du système
(nom de l'utilisateur et mot de passe, espace
disque alloué aux utilisateurs et opérations
système autorisées par l'utilisateur)
• Sécurité données : couvre l'accès aux objets de
la base de données et leur utilisation, ainsi que
les actions exécutées sur ces objets par les
utilisateurs

Mars - Avril 2014 141

 Privilèges système

• Plus de 100 privilèges système :

• Création d’utilisateurs (CREATE USER)
• Suppression de table (DROP ANY TABLE)
• Création d’espace disque (CREATE TABLESPACE)
• Sauvegarde des tables (BACKUP ANY TABLE)
• ……..
• Les privilèges peuvent être regroupés dans des
rôles (voir plus loin)

Mars - Avril 2014 142

Exemples de privilèges systèmes

Privilèges ALTER CREATE DROP

PROCEDURE  
ANY PROCEDURE  
TABLE 
ANY TABLE   
SESSION  
TABLESPACE   
USER   
VIEW 
………

Mars - Avril 2014 143

 Délégation et suppression
de privilèges

• Délégation : GRANT
GRANT {privilège_système | rôle}
[,{privilège2 | rôle2}...]
TO {utilisateur1 | rôle | PUBLIC}
[,{utilisateur2 ...}]
[WITH ADMIN OPTION] ;

• Suppression : REVOKE
REVOKE {privilège_système | rôle}
[,{privilège2 | rôle2}...]
FROM {utilisateur1 | rôle | PUBLIC}
[,{utilisateur2 ...}] ;
Mars - Avril 2014 144
 Exemple de délégation et de suppression
de privilèges Système

GRANT CREATE SESSION, CREATE TABLE 

DROP ANY TABLE TO michel;

Attention : suppression d’autres tables

REVOKE DROP ANY TABLE FROM michel;

On supprime que ce privilège

Mars - Avril 2014 145

 Privilèges Objet

• Contrôle les actions sur les objets

– Objets : tables, vues, séquences, ….
– Actions : update, insert, execute, ….
• Le propriétaire (‘owner’) peut donner ces
privilèges sur ses propres objets
• Les privilèges peuvent être donnés avec l’option
de délégation

Mars - Avril 2014 146

 Privilèges Objet
Délégation et suppression
• Délégation : GRANT
GRANT privilège1 [,privilège2 ...]
[(colonne [,colonne2.])] ON schéma.objet
TO {utilisateur1 | rôle | PUBLIC}
[,{utilisateur2 ...}]
[WITH GRANT OPTION] ;
• Suppression : REVOKE
REVOKE privilège1 [,privilège2 ...]
[(colonne [,colonne2...])]
ON schéma.objet
FROM {utilisateur1 | rôle | PUBLIC}
[,{utilisateur2 ...}]
[CASCADE CONSTRAINTS] ;
Mars - Avril 2014 147
 Privilèges Objet
Exemples

GRANT INSERT,UPDATE (adr,tel) ON etud_info

TO Martine, Nicole ;

GRANT DELETE, UPDATE , INSERT ON etud_info

TO Michel WITH GRANT OPTION;

REVOKE UPDATE (tel) ON etud_info

FROM Nicole ;

Mars - Avril 2014 148

 Les privilèges Objet :
objets et actions possibles

Procédure
Objets
Table Vue Séquence Fonction Snapshot
Actions Package

ALTER 
DELETE  
EXECUTE 
INDEX 
INSERT  
REFERENCES 
SELECT    
UPDATE  
Mars - Avril 2014 149
 Les Rôles

• Regroupement de privilèges pour des familles

d’utilisateur
• Facilitent la gestion des autorisations des
privilèges objet en évitant les ordres GRANT
• Un rôle par défaut est donné à un utilisateur
• Un utilisateur peut posséder plusieurs rôles mais
n’est connecté qu’avec un seul à la fois
• On peut donner un mot de passe pour certains
rôles

Mars - Avril 2014 150

Les Rôles : évitent le produit cartésien

Privilège1

ROLE1
Privilège2

Privilège3
ROLE2

Privilège4

ROLE3 Privilège5

Privilège6

Mars - Avril 2014 151

 Manipulation des rôles :
Ordres

• Création / Modification d’un rôle

{CREATE|ALTER} ROLE nom_rôle {NOT IDENTIFIED |
IDENTIFIED {BY mot_de_passe | EXTERNALLY}};

• Remplissage et attribution d’un rôle

GRANT {privilège1 | rôle1} TO nom_rôle;
GRANT {privilège2 | rôle2} TO nom_rôle;
GRANT ROLE nom_role TO user;
• Rôle par défaut ou activation
SET ROLE nom_rôle [IDENTIFIED BY mot_de_passe];
• Suppression / Révocation d’un rôle
DROP ROLE nom_rôle; REVOKE ROLE nom_rôle FROM user;

Mars - Avril 2014 152

 Manipulation des rôles :
Exemples

CREATE ROLE secretariat_info ;

GRANT SELECT,UPDATE (adr,tel)

ON ens_info TO secretariat_info;
GRANT SELECT,INSERT,UPDATE
ON etud_info TO secretariat_info;
GRANT SELECT,INSERT
ON cours_info TO secretariat_info;

GRANT secretariat_info TO
laurent, thomas, corinne ;

Mars - Avril 2014 153

 Rôles prédéfinis

• DBA
– Tous les privilèges système et objet
• RESOURCE
– Création de tous les objets ‘classiques’
– Propriétaire des données (‘owner’)
• CONNECT
– Connexion à la base
– Attente de privilèges objet
• EXP_FULL_DATABASE
– Exportation de tous les objets
• IMP_FULL_DATABASE
– Importation d’objets

Mars - Avril 2014 154

 Dictionnaire de données

• ROLE_SYS_PRIVS
– privilèges systèmes accordés aux rôles
• ROLE_TAB_PRIVS
– privilèges objets accordés aux rôles
• USER_ROLE_PRIVS
– rôles accessibles par l’utilisateur

Mars - Avril 2014 155

Le Langage de Contrôle
de Données

TRIGGERS
 Généralités sur les Triggers

• Programme évènementiel SI évènement

– Bloc événement
ALORS action
(UPDATE, DELETE, INSERT) SINON
– Bloc action rien
(bloc PL/SQL) FINSI
• Trois fonctions assurées
– Mise en place de contraintes complexes
– Mise à jour de colonnes dérivées
– Génération d’évènements
• Associé à une table
– Suppression de la table  suppression des triggers

Mars - Avril 2014 157

 12 types de Triggers

• "Row" Trigger ou "Statement" Trigger

– Row : le trigger est exécuté pour chaque ligne touchée
2
– Statement : le trigger est exécuté une fois
• Exécution avant ou après l’événement
– "before " : le bloc action est levé avant que l’événement soit
exécuté
– "after " : le bloc action est levé après l’exécution du bloc 2
événement
• Trois évènements possibles
– UPDATE : certaines colonnes
– INSERT 3
– DELETE

Mars - Avril 2014 158

 Syntaxe de création

CREATE [OR REPLACE] TRIGGER <nom_trigger>

{BEFORE|AFTER}
{INSERT|DELETE|UPDATE [OF colonnes]}
ON <nom_table>
[FOR EACH ROW] row trigger si présent
[DECLARE]
-- déclaration de variables, exceptions
-- curseurs
BEGIN
-- bloc action
-- ordres SQL et PL/SQL
END;
/
Mars - Avril 2014 159
 Anciennes et nouvelles valeurs

• Pour les row trigger (triggers lignes ) : accès aux

valeurs des colonnes pour chaque ligne modifiée
• Deux variables : :NEW.colonne et :OLD.colonne

Ancienne valeur Nouvelle valeur

:OLD.colonne :NEW.colonne
INSERT NULL Nouvelle valeur

DELETE Ancienne valeur NULL

UPDATE Ancienne valeur Nouvelle valeur

Mars - Avril 2014 160

 Trigger de contraintes :
‘lever’ une erreur

• Test de contraintes : erreur ou pas

• Ordre : RAISE_APPLICATION_ERROR

RAISE_APPLICATION_ERROR(n°erreur,’texte erreur’)

• N°erreur : [-20000 , -20999]  SQLCODE

• Texte erreur : message envoyé  SQLERRM

Mars - Avril 2014 161

 Exemples de row trigger :
prise de commande

(1) produits
idProd NomProd QtStock Seuil
détail_commandes
IdCom idProd QtCom p1 Produit 1 20 15
P2 Produit 2 50 12
1001 p1 10
INSERT (2) ……
(3)
• (1) Contrôle réapprovisionnement
– QtStock > QtCom ? idProd NomProd QtStock Seuil
• (2) Mise à jour
– QtStock=QtStock-QtCom
• (3) génération évènements
– Ajout dans réappro si
– Seuil >= QtStock

Mars - Avril 2014 162

 Prise de commande :
(1) contrôle quantité en stock ?

CREATE TRIGGER t_b_i_detail_commandes

BEFORE INSERT ON détail_commandes
FOR EACH ROW
DECLARE
v_qtstock NUMBER;
BEGIN
SELECT qtstock INTO v_qtstock FROM produits
WHERE idprod = :NEW.idprod;
IF v_qtstock < :NEW.qtcom THEN
RAISE_APPLICATION_ERROR(-20001,’stock insuffisant’);
END IF;
END;
/

Mars - Avril 2014 163

 Prise de commande :
(2) Mise à jour quantité en stock

CREATE TRIGGER t_a_i_detail_commandes

AFTER INSERT ON detail_commandes
FOR EACH ROW
BEGIN
UPDATE produits p
SET p.qtstock = p.qtstock - :NEW.qtcom
WHERE idprod = :NEW.idprod;
END;
/

Mars - Avril 2014 164

 Prise de commande :
(3) génération d’un réapprovisionnement

CREATE TRIGGER t_a_u_produits

AFTER UPDATE OF qtstock ON produits
FOR EACH ROW
BEGIN
IF :NEW.qtstock <= :NEW.seuil THEN
INSERT INTO reapprovisionnement VALUES
(:NEW.idprod,:NEW.nomprod,:NEW.qtstock,
:NEW.seuil);
END IF;
END;
/

Mars - Avril 2014 165

 Les prédicats dans un trigger

•CREATE
On peutTRIGGER
regrouper tous les triggers d’un même
<nom_trigger>
type : BEFORE ou AFTER
{BEFORE|AFTER}
•INSERT OR DELETE
On précise OR UPDATE
l’ordre dans le BEGIN[OF colonnes]}
ON <nom_table>
[FOR EACH ROW]
[DECLARE]
-- déclaration de variables, exceptions
BEGIN
IF UPDATING(’colonne’) THEN …… END IF;
IF DELETING THEN …… END IF;
IF INSERTING THEN …… END IF;
END;
/
Mars - Avril 2014 166
 Limitation des Trigger

• Impossible d’accéder sur la table sur laquelle

porte le trigger
• Attention aux effets de bords :
– exemple : trigger x1 de la table T1 fait un insert dans la
table T2 qui possède un trigger x2 qui modifie T3 qui
possède un trigger x3 qui modifie T1
T1 T2 T3
X1 X2

X3

Mars - Avril 2014 167

 Les triggers d’état
ou ‘Statement trigger’

• Raisonnement sur la globalité de la table et non

sur un enregistrement particulier
• TRIGGER BEFORE : 1 action avant un ordre
UPDATE de plusieurs lignes
• TRIGGER AFTER : 1 action après un ordre
UPDATE touchant plusieurs lignes

Mars - Avril 2014 168

 Exemple de Statement Trigger

• Interdiction d’emprunter un ouvrage pendant le

week-end

CREATE TRIGGER controle_date_emp

BEFORE UPDATE OR INSERT OR DELETE ON emprunt
BEGIN
IF TO_CHAR(SYSDATE,’DY’)
IN (’SAT’,’SUN’) THEN 
RAISE_APPLICATION_ERROR
(-20102,’Désole les emprunts sont
interdits le week-end...’) ;
END IF;
END;
/

Mars - Avril 2014 169

 Les Triggers ‘INSTEAD OF’

• Trigger faisant le travail ‘à la place de’ …..

• Posé sur une vue multi-table pour autoriser les
modifications sur ces objets virtuels
• Utilisé dans les bases de données réparties pour
permettre les modifications sur le objets virtuels
fragmentés (cours BD Réparties)
• Permet d’assurer un niveau d’abstraction élevé
pour les utilisateurs ou développeurs clients : les
vraies mises à jour sont faites à leur insu ….

Mars - Avril 2014 170

 Exemple de trigger ‘instead of’

• Vue étudiant résultant de 4 tables

S
ETUDIANT ETUDIANT_LICENCE
T
A
ETUDIANT_MASTERE
G
E
ETUDIANT_DOCTORAT

Mars - Avril 2014 171

 Exemple de trigger ‘instead of’
Construction de la vue ETUDIANT

Colonne virtuelle
CREATE VIEW etudiant
(ine,nom,adresse,cycle,nomstage,adstage)
AS SELECT el.ine,el.nom,el.adr,’L’,s.noms,s.ads
FROM etudiant_licence el, stage s
WHERE el.ine=s.ine
UNION
SELECT em.ine,em.nom,em.adr,’M’,s.noms,s.ads
FROM etudiant_mastere em, stage s
WHERE em.ine=s.ine
UNION
SELECT ed.ine,ed.nom,ed.adr,’D’,s.noms,s.ads
FROM etudiant_doctorat ed, stage s
WHERE ed.ine=s.ine;

Mars - Avril 2014 172

 Exemple de trigger ‘instead of’
utilisation de la vue : INSERT

INSERT INTO etudiant VALUES

(100,’Michel’,’Toulouse,’M’,’Oracle’,’CICT’);

ETUDIANT

100 Michel Toulouse 100 Oracle CICT

Mars - Avril 2014 173

 Exemple de trigger ‘instead of’
trigger pour INSERT

CREATE TRIGGER insert_etudiant

INSTEAD OF INSERT ON etudiant FOR EACH ROW
BEGIN
IF :NEW.cycle=’L’ THEN
INSERT INTO etudiant_licence VALUES
(:NEW.ine,:NEW.nom,:NEW.adresse);
INSERT INTO stage VALUES
(:NEW.ine,:NEW.nomstage,:NEW.adstage);
ELSIF :NEW.cycle=’M’ THEN
....... Idem pour M et D ........
ELSE RAISE_APPLICATION_ERROR
(-20455,’Entrer M, L ou D’);
END IF;
END;
/
Mars - Avril 2014 174
 Manipulation des Triggers

• Suppression d’un trigger

DROP TRIGGER <nomtrigger> ;

• Désactivation d’un trigger

ALTER TRIGGER <nomtrigger> DISABLE;

• Réactivation d’un trigger

ALTER TRIGGER <nomtrigger> ENABLE;

• Tous les triggers d’une table

ALTER TABLE <nomtable>
{ENABLE|DISABLE} ALL TRIGGERS;
Mars - Avril 2014 175
 Dictionnaire des Données

• USER_TRIGGERS
TRIGGER_NAME VARCHAR2(30)
TRIGGER_TYPE VARCHAR2(16)
TRIGGERING_EVENT VARCHAR2(227)
TABLE_OWNER VARCHAR2(30)
BASE_OBJECT_TYPE VARCHAR2(16)
TABLE_NAME VARCHAR2(30)
COLUMN_NAME VARCHAR2(4000)
REFERENCING_NAMES VARCHAR2(128)
WHEN_CLAUSE VARCHAR2(4000)
STATUS VARCHAR2(8)
DESCRIPTION VARCHAR2(4000)
ACTION_TYPE VARCHAR2(11)
TRIGGER_BODY LONG
Mars - Avril 2014 176
 Dictionnaire des Données (suite)

• USER_TRIGGER_COLS

TRIGGER_OWNER VARCHAR2(30)
TRIGGER_NAME VARCHAR2(30)
TABLE_OWNER VARCHAR2(30)
TABLE_NAME VARCHAR2(30)
COLUMN_NAME VARCHAR2(4000)
COLUMN_LIST VARCHAR2(3)
COLUMN_USAGE VARCHAR2(17)

Mars - Avril 2014 177

Le Langage de BLOC

PL/SQL
 Le Langage de Bloc
PL/SQL # SQL

• SQL : langage ensembliste

– Ensemble de requêtes distinctes
– Langage de 4ème génération : on décrit le résultat sans dire
comment il faut accéder aux données
– Obtention de certains résultats : encapsulation dans un
langage hôte de 3ème génération
• PL/SQL
– ‘Procédural Language’ : sur-couche procédurale à SQL,
boucles, contrôles, affectations, exceptions, ….
– Chaque programme est un bloc (BEGIN – END)
– Programmation adaptée pour :
• Transactions
• Une architecture Client - Serveur

Mars - Avril 2014 179

 Requêtes SQL

• Chaque requête ‘client’ est transmise au serveur de

données pour être exécutée avec retour de résultats

CLIENT SERVEUR
INSERT INTO … Exécute INSERT
Résultat
DELETE FROM … Exécute DELETE
Résultat
UPDATE … Exécute UPDATE
Résultat
SELECT …
Exécute SELECT
……. Résultat

Mars - Avril 2014 180

 Bloc PL/SQL

• Le bloc de requêtes est envoyé sur le serveur.

Celui-ci exécute le bloc et renvoie 1 résultat final.

CLIENT SERVEUR

BEGIN
Exécution
INSERT …
SI …. ALORS du bloc
SELECT … PL/SQL
FSI
END;
Résultat

Mars - Avril 2014 181

 Format d’un bloc PL/SQL

• Section DECLARE : déclaration de

– Variables locales simples
DECLARE
– Variables tableaux
– cursors --déclarations

• Section BEGIN BEGIN

– Section des ordres exécutables --exécutions
– Ordres SQL EXCEPTION
– Ordres PL
--erreurs
• Section EXCEPTION
END;
– Réception en cas d’erreur
– Exceptions SQL ou utilisateur /

Mars - Avril 2014 182

 Variables simples

• Variables de type SQL

nbr NUMBER(2) ;
nom VARCHAR(30) ;
minimum CONSTANT INTEGER := 5 ;
salaire NUMBER(8,2) ;
debut NUMBER NOT NULL ;

• Variables de type booléen (TRUE, FALSE, NULL)

fin BOOLEAN ;
reponse BOOLEAN DEFAULT TRUE ;
ok BOOLEAN := TRUE; 

Mars - Avril 2014 183

 Variables faisant référence
au dictionnaire de données

• Référence à une colonne (table, vue)

vsalaire  employe.salaire%TYPE;
vnom etudiant.nom%TYPE;
Vcomm vsalaire%TYPE;
• Référence à une ligne (table, vue)
vemploye  employe%ROWTYPE;
vetudiant etudiant%ROWTYPE;

– Variable de type ‘struct’

– Contenu d’une variable : variable.colonne
vemploye.adresse
Mars - Avril 2014 184
 Tableaux dynamiques

• Déclaration d’un type tableau

TYPE <nom du type du tableau>
IS TABLE OF <type de l’élément>
INDEX BY BINARY_INTEGER;

• Affectation (héritage) de ce type à une variable

<nom élément> <nom du type du tableau>;

• Utilisation dans la section BEGIN : un élément du

tableau :

<nom élément> (rang )

dans le tableau

Mars - Avril 2014 185

 Tableaux dynamiques
variables simples

• Déclaration d’un tableau avec des éléments numériques

TYPE type_note_tab
IS TABLE OF NUMBER(4,2) i:=1;
INDEX BY BINARY_INTEGER; tab_notes(i) := 12.5;
tab_notes type_note_tab;
i NUMBER;

• Déclaration d’un tableau avec des éléments caractères

TYPE type_nom_tab
IS TABLE OF VARCHAR(30) i:=1;
INDEX BY BINARY_INTEGER; tab_noms(i):= 'toto';
tab_noms type_nom_tab;
i NUMBER;

Mars - Avril 2014 186

 Tableaux dynamiques
variables simples avec héritage

• Tableau avec éléments hérités

TYPE type_note_tab
IS TABLE OF partiel.note%TYPE i:=1;
INDEX BY BINARY_INTEGER; tab_notes(i) := 12.5;
tab_notes type_note_tab;
i NUMBER;

TYPE type_nom_tab
IS TABLE OF etudiant.nom%TYPE i:=1;
INDEX BY BINARY_INTEGER; tab_noms(i):= 'toto';
tab_noms type_nom_tab;
i NUMBER;

Mars - Avril 2014 187

 Tableaux dynamiques
avec des éléments de type RECORD

• Type RECORD : plusieurs variables dans un élément

TYPE type_emp_record
(idEmp NUMBER,
nomEmp VARCHAR(30),
adrEmp VARCHAR(80)); i:=1;
tab_emps(i).idEmp:= 100;
tab_emps(i).nomEmp:= 'toto';
tab_emps(i).adrEmp:= 'tlse';

TYPE type_emp_tab
IS TABLE OF type_emp_record
INDEX BY BINARY_INTEGER;
tab_emps type_emp_tab;
i NUMBER;
Mars - Avril 2014 188
 Tableaux dynamiques
avec des éléments de type ROW

• Type ROW : chaque élément est une variable ‘struct’

TYPE type_emp_tab
IS TABLE OF employe%ROWTYPE
INDEX BY BINARY_INTEGER;
tab_emps type_emp_tab;
i NUMBER;

i:=1;
tab_emps(i).idE:= 100;
tab_emps(i).nom:= 'toto';
tab_emps(i).adresse:= 'tlse';

Mars - Avril 2014 189

 Variables paramétrées
lues sous SQLPLUS : &

• Variables lues par un ACCEPT …. PROMPT

+ ACCEPT plu PROMPT 'Entrer la valeur : '

DECLARE
-- déclarations
BEGIN
PL -- travail avec le contenu de plu :
-- &plu si numérique
-- '&plu' si caractère
END;
/
+ -- Ordre SQL .....
Mars - Avril 2014 190
 Variables en sortie
sous SQLPLUS : :

• Variable déclarée sous sqlplus , utilisée dans le

bloc PL puis affichée sous sqlplus

+ VARIABLE i NUMBER
SQL> print i
BEGIN
:i := 15;
PL END; I
/ ----------
+ PRINT i
15

Mars - Avril 2014 191

 Instructions PL

• Affectation (:=)
– A := B;
• Structure alternative ou conditionnelle
– Opérateurs SQL : >,<,….,OR,AND,….,BETWEEN,LIKE,IN
– IF …. THEN ….. ELSE ……END IF;

IF condition THEN
instructions;
ELSE
instructions;
IF condition THEN instructions;
ELSIF condition THEN instructions;
ELSE instructions;
END IF;

Mars - Avril 2014 192

 Structure alternative : CASE (1)

• Choix selon la valeur d’une variable

CASE variable

WHEN valeur1 THEN action1;

WHEN valeur2 THEN action2;
………
ELSE action;

END CASE;

Mars - Avril 2014 193

 Structure alternative : CASE (2)

• Plusieurs choix possibles

CASE

WHEN expression1 THEN action1;

WHEN expression2 THEN action2;
………
ELSE action;

END CASE;

Mars - Avril 2014 194

 Structure itérative

LOOP
• LOOP instructions;
EXIT WHEN (condition);
END LOOP;

FOR (indice IN [REVERSE] borne1..borne2) LOOP

• FOR instructions;
END LOOP;

WHILE (condition) LOOP

• WHILE instructions;
END LOOP;

Mars - Avril 2014 195

 Affichage de résultats intermédiaires
Package DBMS_OUTPUT

• Messages enregistrés dans une mémoire tampon côté

serveur
• La mémoire tampon est affichée sur le poste client à la fin
Serveur ORACLE
Client SQLPLUS BEGIN
DBMS_OUTPUT.PUT_LINE('Message1');
DBMS_OUTPUT.PUT_LINE('Message2');
Message1 DBMS_OUTPUT.PUT_LINE('Message3');
Message2 END;
Message3
à la
fin
Message1
Message2
Message3
SQL>SET SERVEROUT ON
Mémoire tampon
Mars - Avril 2014 196
 Le package DBMS_OUTPUT

• Écriture dans le buffer avec saut de ligne

– DBMS_OUTPUT.PUT_LINE(<chaîne caractères>);
• Écriture dans le buffer sans saut de ligne
– DBMS_OUTPUT.PUT(<chaîne caractères>);
• Écriture dans le buffer d’un saut de ligne
– DBMS_OUTPUT.NEW_LINE;

DBMS_OUTPUT.PUT_LINE('Affichage des n premiers ');

DBMS_OUTPUT.PUT_LINE('caractères en ligne ');
FOR i IN 1..n LOOP
DBMS_OUTPUT.PUT(tab_cars(i));
END LOOP;
DBMS_OUTPUT.NEW_LINE;

Mars - Avril 2014 197

 Sélection mono – ligne
SELECT …. INTO
• Toute valeur de colonne est rangée dans une
variable avec INTO
SELECT nom,adresse,tel INTO vnom,vadresse,vtel
FROM etudiant WHERE ine=&nolu;
SELECT nom,adresse,libDip INTO vnom,vadresse,vdip
FROM etudiant e, diplôme d WHERE ine=&nolu
AND e.idDip=d.idDip;

• Variable ROWTYPE
SELECT * INTO vretud FROM etudiant WHERE ine=&nolu;
…………
DBMS_OUTPUT.PUT_LINE('Nom étudiant : '||vretud.nom);
…………
Mars - Avril 2014 198
 Sélection multi – ligne : les CURSEURS
Principe des curseurs

• Obligatoire pour sélectionner plusieurs lignes

• Zone mémoire (SGA : Share Global Area)
partagée pour stocker les résultats
• Le curseur contient en permanence l’@ de la
ligne courante
• Curseur implicite
– SELECT t.* FROM table t WHERE ……
– t est un curseur utilisé par SQL
• Curseur explicite
– DECLARE CURSOR 

Mars - Avril 2014 199

 Démarche générale des curseurs

• Déclaration du curseur : DECLARE

– Ordre SQL sans exécution
• Ouverture du curseur : OPEN
– SQL ‘monte‘ les lignes sélectionnées en SGA
– Verrouillage préventif possible (voir + loin)
• Sélection d’une ligne : FETCH
– Chaque FETCH ramène une ligne dans le programme
client
– Tant que ligne en SGA : FETCH
• Fermeture du curseur : CLOSE
– Récupération de l’espace mémoire en SGA

Mars - Avril 2014 200

 Traitement d’un curseur

Programme PL/SQL
SGA
FETCH

variables

DECLARE
CURSOR c1 IS SELECT ……;
BEGIN
OPEN c1;
FETCH c1 INTO ………;
WHILE (c1%FOUND) LOOP
………
……… OPEN
FETCH c1 INTO ………;
END LOOP;
CLOSE c1;
END; BD

Mars - Avril 2014 201

 Gestion ‘classique’ d’un curseur

DECLARE
CURSOR c1 IS SELECT nom,moyenne FROM etudiant ORDER BY 1;
vnom etudiant.nom%TYPE;
vmoyenne etudiant.moyenne%TYPE;
e1 ,e2 NUMBER;
BEGIN
OPEN c1;
FETCH c1 INTO vnom,vmoyenne;
WHILE c1%FOUND LOOP
IF vmoyenne < 10 THEN e1:=e1+1;
INSERT INTO liste_refus VALUES(vnom);
ELSE e2:=e2+1;
INSERT INTO liste_refus VALUES(vnom);
END IF;
FETCH c1 INTO vnom,vmoyenne;
END LOOP;
CLOSE c1;
DBMS_OUTPUT.PUT_LINE(TO_CHAR(e2)||'Reçus ');
DBMS_OUTPUT.PUT_LINE(TO_CHAR(e1)||'Collés ');
COMMIT;
END;
Mars - Avril 2014 202
 Les variables système des Curseurs

• Curseur%FOUND
– Variable booléenne
– Curseur toujours ‘ouvert’ (encore des lignes)
• Curseur%NOTFOUND
– Opposé au précédent
– Curseur ‘fermé’ (plus de lignes)
• Curseur%COUNT
– Variable number
– Nombre de lignes déjà retournées
• Curseur%ISOPEN
– Booléen : curseur ouvert ?

Mars - Avril 2014 203

 Gestion ‘automatique’ des curseurs

DECLARE
CURSOR c1 IS SELECT nom,moyenne FROM etudiant ORDER BY 1;
-- PAS DE DECLARATION DE VARIABLE DE RECEPTION
e1 ,e2 NUMBER;
BEGIN
--PAS D’OUVERTURE DE CURSEUR
--PAS DE FETCH Variable STRUCT de réception
FOR c1_ligne IN c1 LOOP
IF c1_ligne.moyenne < 10 THEN e1:=e1+1;
INSERT INTO liste_refus VALUES(c1_ligne.nom);
ELSE e2:=e2+1;
INSERT INTO liste_refus VALUES(c1_ligne.nom);
END IF;
END LOOP;
--PAS DE CLOSE
DBMS_OUTPUT.PUT_LINE(TO_CHAR(e2)||'Reçus ');
DBMS_OUTPUT.PUT_LINE(TO_CHAR(e1)||'Collés ');
COMMIT;
END;
Mars - Avril 2014 204
 Curseurs et Tableaux
exemple final

DECLARE
CURSOR c1 IS SELECT nom,moyenne FROM etudiant
WHERE moyenne>=10 ORDER BY 2 DESC;
TYPE type_nom_tab IS TABLE OF etudiant.nom%TYPE
INDEX BY BINARY_INTEGER;
tab_noms type_nom_tab;
i,j NUMBER;
BEGIN /* Remplissage tableau */
i:=1;
FOR c1_ligne IN c1 LOOP
tab_noms(i):= c1.ligne.nom;
i:=i+1;
END LOOP; /* Affichage du tableau */
FOR j IN 1..i-1 LOOP
DBMS_OUTPUT.PUT_LINE('Rang : '||TO_CHAR(j)||
'Etudiant : '||tab_nom(j));
END LOOP;
END;
Mars - Avril 2014 205
 Gestion des Exceptions
Principe
• Toute erreur (SQL ou applicative) entraîne
automatiquement un débranchement vers le
paragraphe EXCEPTION :
Débranchement involontaire (erreur SQL)
BEGIN
instruction1; ou volontaire (erreur applicative)

instruction2;
……
instructionn;
EXCEPTION
WHEN exception1 THEN
………
WHEN exception2 THEN
………
WHEN OTHERS THEN
………
END;
Mars - Avril 2014 206
 Deux types d’exceptions

• Exceptions SQL
– Déjà définies (pas de déclaration)
• DUP_VAL_ON_INDEX
• NO_DATA_FOUND
• OTHERS
– Non définies
• Déclaration obligatoire avec le n° erreur (sqlcode)

nomerreur EXCEPTION;
PRAGMA EXCEPTION_INIT(nomerreur,n°erreur);

• Exceptions applicatives
– Déclaration sans n° erreur
nomerreur EXCEPTION;

Mars - Avril 2014 207

 Exemple de gestion d’exception (1)

DECLARE
tropemprunt EXCEPTION;
i NUMBER;
BEGIN
i:=1;
SELECT ……
i:=2;
SELECT ……
IF ……… THEN RAISE tropemprunt; ………
EXCEPTION
WHEN NO_DATA_FOUND THEN
IF i=1 THEN ……
ELSE
END IF;
WHEN tropemprunt THEN
………
WHEN OTHERS THEN
………
END;
Mars - Avril 2014 208
 Exemple de gestion d’exception (2)

DECLARE
enfant_sans_parent EXCEPTION;
PRAGMA EXCEPTION_INIT(enfant_sans_parent,-2291);
BEGIN
INSERT INTO fils VALUES ( ……. );

EXCEPTION
WHEN enfant_sans_parent THEN
………
WHEN OTHERS THEN
………
END;

Mars - Avril 2014 209

Procédures Stockées

Fonctions

Paquetages
 Procédures Stockées : Principe (1)

• Programme (PL/SQL) stocké dans la base

• Le programme client exécute ce programme en
lui passant des paramètres (par valeur)
• Si le code est bon , le SGBD conserve le
programme source (USER_SOURCE) et le
programme compilé
• Le programme compilé est optimisé en tenant
compte des objets accélérateurs (INDEX,
CLUSTER, PARTITION, …)

Mars - Avril 2014 211

 Procédures Stockées : Principe (2)

CLIENT SERVEUR

PROCEDURE P(v1,v2) AS
EXECUTE P(p1, p2); P
BEGIN
Ordre SQL et PL/SQL
………..
END P;
Retour résultats

Mars - Avril 2014 212

 Optimisation des procédures
liens avec les objets
Références croisées

Procédure P1 Table1

Procédure P2
Index1

Procédure P3

Table2
Procédure P4

Procédure P5 index2

Mars - Avril 2014 213

 Optimisation des procédures

• Recompilation automatique d’une procédure si

un objet est modifé
• Recompilation manuelle possible

ALTER PROCEDURE <nom_procédure> COMPILE;

Mars - Avril 2014 214

 Avantages des procédures stockées

• Vitesse : programme compilé et optimisé

– Une requête SQL normale est interprétée et optimisée à chaque
exécution
• Intégrité : encapsulation des données
– Vers le modèle Objet
– Droit d’exécution et plus de manipulation
– Les règles de gestion sont données sur le serveur en un seul
exemplaire
• Performance : moins de transfert réseau
– Plus de transfert de bloc de programme
– Une procédure pour plusieurs utilisateurs
• Abstraction : augmentation du niveau d’abstraction des
développeurs Client
• Performance :
– Extensibilité, Modularité, Réutilisation, Maintenance

Mars - Avril 2014 215

 Déclaration d’une procédure stockée

CREATE [OR REPLACE] PROCEDURE <nom_procédure>

[(variable1 type1, ..., variablen typen [OUT])] AS
...
-- déclarations des variables et
-- curseurs utilisées dans le corps de la procédure
BEGIN
....
-- instructions SQL ou PL/SQL
EXCEPTION
....
END;
/

Mars - Avril 2014 216

 Exemple 1 de procédure stockée
inscription d’un étudiant

CREATE PROCEDURE inscription (pnom etudiant.nom%TYPE,

... ,pdip diplome.idDip%TYPE)
AS
CURSOR uv_ins IS SELECT c.iduv AS uv FROM composition c
WHERE c.idDip=pdip;
BEGIN
DBMS_OUTPUT.PUT_LINE('Début inscription de ||pnom');
INSERT INTO etudiant VALUES(seqEtu.NEXTVAL,pnom,…,pdip);
FOR uv_l IN uv_ins LOOP
INSERT INTO inscrire VALUES(seqEtu.CURRVAL,uv_l.uv);
END LOOP;
DBMS_OUTPUT.PUT_LINE('Transaction réussie');
COMMIT;
EXCEPTION
....
END;
/
Mars - Avril 2014 217
 Exemple 1 : appel de la procédure

• A partir de sqlplus

ACCEPT vnom PROMPT 'Entrer le nom : '

…………
EXECUTE inscription('&vnom',……, '&vdip');

• A partir de PL/SQL
inscription(nom,……, dip);
• A partir de pro*c
EXEC SQL EXECUTE
BEGIN
inscription(:nom, ……… ,:dip);
END;
END-EXEC;
Mars - Avril 2014 218
 Exemple 2 : avec retour de valeurs
suppression d’un étudiant

CREATE PROCEDURE suppression (pidEtu NUMBER,

retour OUT NUMBER) AS
inscriptions EXCEPTION;
PRAGMA EXCEPTION_INIT(inscriptions,-2292);
vnom etudiant.nom%TYPE;
BEGIN
SELECT nom INTO vnom FROM etudiant WHERE idEtu=pidEtu;
DELETE FROM etudiant WHERE idEtu=pidEtu;
DBMS_OUTPUT.PUT_LINE('Etudiant '||vnom||' supprimé');
COMMIT;
retour:=0;

../..
Mars - Avril 2014 219
 Exemple 2 : avec retour de valeurs
suppression d’un étudiant (suite)

EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Etudiant'||TO_CHAR(pidEtu)||'inconnu);
retour:=1;
WHEN inscriptions THEN
DBMS_OUTPUT.PUT_LINE('Encore des inscriptions');
retour:=2;
…………
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM);
retour:=9;
END;
/

Mars - Avril 2014 220

 Exemple 2 : appel avec retour

VARIABLE ret NUMBER

ACCEPT vnom PROMPT 'Entrer le nom : '
…………
EXECUTE inscription('&vnom',……, '&vdip',:ret);
PRINT ret

Mars - Avril 2014 221

 Les Fonctions stockées

• Comme une procédure mais qui ne retourne

qu’un seul résultat
• Même structure d’ensemble qu’une procédure
• Utilisation du mot clé RETURN pour retourner le
résultat
• Appel possible à partir de :
– Une requête SQL normale
– Un programme PL/SQL
– Une procédure stockée ou une autre fonction stockée
– Un programme externe comme pro*c

Mars - Avril 2014 222

 Déclaration d’une fonction stockée

CREATE [OR REPLACE] FUNCTION nom_fonction

[(paramètre1 type1, ……… , paramètren typen)]
RETURN type_résultat IS
-- déclarations de variables,curseurs et exceptions
BEGIN
-- instructions PL et SQL

RETURN(variable);
END;
/

1 ou plusieurs RETURN

Mars - Avril 2014 223

 Exemple 1 de fonction stockée

CREATE OR REPLACE FUNCTION moy_points_marques

(eqj joueur.ideq%TYPE)
RETURN NUMBER IS
moyenne_points_marques NUMBER(4,2);
BEGIN
SELECT AVG(totalpoints) INTO moyenne_points_marques
FROM joueur WHERE ideq=eqj;
RETURN(moyenne_points_marques);
END;
/

Mars - Avril 2014 224

 Utilisation d’une fonction

• A partir d’une requête SQL

SELECT moy_points_marques('e1') FROM dual;

SELECT nomjoueur FROM joueur WHERE

totalpoints > moy_points_marques('e1');

• A partir d’une procédure ou fonction

BEGIN
………
IF moy_points_marques(equipe) > 20 THEN ………

END;

Mars - Avril 2014 225

 Exemple 2 de fonction stockée

CREATE OR REPLACE FUNCTION bon_client

(pidclient NUMBER, pchiffre NUMBER)
RETURN BOOLEAN IS
total_chiffre NUMBER;
BEGIN
SELECT SUM(qte*prix_unit) INTO total_chiffre
FROM commande WHERE idclient=pidclient;
IF total_chiffre > pchiffre THEN
RETURN(TRUE);
ELSE RETURN(FALSE);
END IF;
END;

BEGIN
………
IF bon_client(client,10000) THEN ………
…………
Mars - Avril 2014 226
 Les Paquetages

• Ensemble de programmes ayant un lien logique

entre eux
• Exemple : package étudiant qui peut regrouper
tous les programmes écrits sur les étudiants
• Début de l’approche objet avec les méthodes
associées à une classe (MEMBER en Objet-Relationnel)

P1 P3

ETUDIANT

P2

Mars - Avril 2014 227

 Structure d’un paquetage

• Partie ‘visible’ ou spécification

– Interface accessible au programme appelant
– Ne contient que les déclarations des procédures ou
fonctions publiques
– Variables globales et session
– Curseurs globaux
• Partie ‘cachée’ ou body
– Corps des procédures ou des fonctions citées dans la
partie spécification
– Nouvelles procédures ou fonctions privées accessibles
uniquement par des procédures ou fonctions du
paquetage

Mars - Avril 2014 228

 Déclaration d’un paquetage
partie spécification

-- Partie Spécification

CREATE [OR REPLACE] PACKAGE nom_package AS

Procédure1(liste des paramètres);
…………
Fonction1(liste des paramètres);
…………
Variable_globale1 type1;
…………
CURSOR Curseur_global1 IS …………
…………
END nom_package;
/

Mars - Avril 2014 229

 Déclaration d’un paquetage
partie body

-- Partie body
CREATE [OR REPLACE] PACKAGE BODY nom_package AS
Procédure1(liste des paramètres)IS
…………
BEGIN
…………
END Procédure1;
Fonction1(liste des paramètres) RETURN type IS
…………
BEGIN
…………
RETURN(……);
END Fonction2;
END nom_package;
/

Mars - Avril 2014 230

 Exemple : package ‘étudiant’ (1)

CREATE PACKAGE etudiant AS

-- Procédure publique inscription
PROCEDURE inscription (pnom etudiant.nom%TYPE,
... ,pdip diplome.idDip%TYPE);
-- Procédure publique suppression
PROCEDURE suppression(pidetu NUMBER);
END nom_package;
/
CREATE PACKAGE BODY etudiant AS
inscription (pnom etudiant.nom%TYPE,
... ,pdip diplome.idDip%TYPE) IS
CURSOR uv_ins IS SELECT c.iduv AS uv FROM
composition c
WHERE c.idDip=pdip;
BEGIN

Mars - Avril 2014 231

 Exemple : package ‘étudiant’ (2)

INSERT INTO etudiant VALUES(seqEtu.NEXTVAL,pnom,…,pdip);

FOR uv_l IN uv_ins LOOP
INSERT INTO inscrire VALUES(seqEtu.CURRVAL,uv_l.uv);
END LOOP;
DBMS_OUTPUT.PUT_LINE('Transaction réussie');
COMMIT;
EXCEPTION ....
END inscription;
-- fonction privée inscrit_uv
FUNCTION inscrit_uv(pidetu NUMBER) RETURN BOOLEAN IS
nbre_ins NUMBER;
BEGIN
SELECT COUNT(*) INTO nbre_ins FROM inscrire WHERE
Idetu=pidetu;
IF nbre_ins>0 THEN RETURN(TRUE) ELSE RETURN(FALSE)
END IF;
END inscrit_uv;
Mars - Avril 2014 232
 Exemple : package ‘étudiant’ (3)

PROCEDURE suppression (pidetu NUMBER) AS

BEGIN
IF inscrit_uv(pidetu) THEN
DBMS_OUTPUT.PUT_LINE('Cet étudiant est inscrit à des UV');
DBMS_OUTPUT.PUT_LINE('Impossible de le supprimer');
ELSE
DELETE FROM etudiant WHERE idetu=pidetu;
DBMS_OUTPUT.PUT_LINE('Etudiant supprimé');
COMMIT;
END IF;
END suppression;
END etudiant

Mars - Avril 2014 233

 Appel d’un programme d’un package

• A partir des SQL

ACCEPT vnom PROMPT 'Entrer le nom : '
…………
EXECUTE etudiant.inscription('&vnom',……, '&vdip');

• A partir d’un autre package

etudiant.inscription(nom,……, dip);

• Uniquement les programmes PUBLICS

Mars - Avril 2014 234