PL/SQL

II1 h el a .b ou kef @e ns i- u ma. t n

2024/ 2025
1
Plan

• Blocs PL/SQL : structure

• Variables
• Traitements conditionnels
• Traitements répétitifs
• Curseurs
• Exceptions
• Procédures et fonctions
• Triggers

2
Structure du bloc PL/SQL (1/11)

Le langage Oracle PL SQL est :

• un l angage procé dural

• une e xte nsion procé durale du langage SQL

- Il permet de grouper des traitements et de les soumettre au noyau

en un bloc unique de traitement.
- Le langage SQL est non procédural alors que le PL SQL est un
langage procédural. Le PL SQL sert à programmer des procédures, des
fonctions, des triggers, des packages.

- Des variables permettent l’échange d’information entre les requêtes

SQL et le reste du programme
3
Structure du bloc PL/SQL (2/11)

Supposons que nous voulons accorder un bonus à chaque

employé en fonction des heures effectuées sur un projet.
Le problème se rait s im plif ié si vous disp osiez d'ins tructi ons
conditionnelle s.
 Le la ngage PL/SQL e st conçu pour faire face à ces besoins

4
Structure du bloc PL/SQL (3/11)

Le langage PL/SQL :

• Of fre une stru cture d e bloc p our le s uni tés d e code e xécuta bles. La
ma intenance d u code e st facili tée avec une structure bie n dé f inie .

• Fournit de s structures procé dura les telle s que :

– Va r i a b l e s , c o n s t a n t e s e t t yp e s

– S t ru c t u r e s de contrôle, telles que les instructions conditionnelles et les

boucles

– P r o g ra m m e s r é u t i l i s a b l e s é c r i t s u n e f o i s e t e x é c u t é s p l u s i e u r s f o i s

5
Structure du bloc PL/SQL (4/11)

Moteur PL/SQL
Programme
procédural d'exécution des
Bloc
instructions
PL/SQL procédurales
SQL

Programme d'exécution
des instructions SQL

Serveur de base de données Oracle

6
Structure du bloc PL/SQL (5/11)

• Un bloc PL/SQL contient des instructions procédurales et

des instructions SQL.
• Si on soumet un bloc PL/SQL au ser veur, le moteur PL/SQL
commence par analyser le bloc : Il identifie les
instructions procédurales et les instructions SQL.
• Il transmet les instructions procédurales au programme
d'exécution des instructions procédurales et transmet les
instructions SQL au programme d'exécution des instructions
SQL.
• Par conséquent, toutes les instructions procédurales sont
exécutées localement et seules les instructions SQL sont
exécutées dans la base de données.
7
Structure du bloc PL/SQL (6/11)

La structure de base d’un programme PL/SQL est

celle de bloc (possiblement imbriqué)
La délimitation des blocs est faite avec les mots réservés:

DECLARE (facultatif )
Va r i a b l e s , c u r s e u r s , exc ep t i o n s d éf i ni e s p a r l ' u t i l i s a t eu r
BEGIN (obligatoire)
- Instructions SQL
- Instructions PL/SQL
EXCEPTION (facultatif )
A c t i o n s à e f fe c t u er l o r s q ue d e s er r eu r s s e p r o d ui s e n t
END; (obligatoire)
8
Structure du bloc PL/SQL (7/11)

• Le corps du programme (entre le BEGIN et le END)

contient des instructions PL/SQL (assignements, boucles, appel
de procédure) ainsi que des instructions SQL.
• Il s’a git de la seule par tie qui so it obligatoire. Les deux autres
zones, dites zone de déclaration et zone de gestion des
exceptions sont facultatives.
• Les seuls ordres SQL que l’on peut trouver dans un bloc PL/SQL
sont: SELECT, INSERT, UPDATE, DELETE.
• Les autres types d’ instru ctions (par exemple CREATE, DROP,
ALTER) ne peuvent se trouver qu’a l’extérieur d’un tel bloc .
Chaque instruction se termine par un “;”.
• Le PL/SQL ne se soucie pas de la casse. On peut inclure
des commentaires par - - (en début de chaque ligne commentée)
ou par /*.... */ (pour délimiter des blocs de commentaires).

9
Structure du bloc PL/SQL (8/11)

Ainsi :

• La pa r tie de dé claration e st optionnell e, ma is chaq ue objet utilisé

doit être décla ré : Variables, Constante s, Types, Curse urs, . ..

• La pa r tie de s comm ande s e xécutabl es est toujours pré sente . Elle est
constitué e d e :

– Ordres SQL

– M a n i p u l a t i o n s d e s v a r i a b l e s , c o n s t a n t e s , e t d e s t r u c t u r e s d e p r o g ra m m a t i o n
( i t é ra t i o n s , s é l e c t i o n s , e t c .) .

• La pa r tie de s e xceptions e st optionnelle

– E l l e g è r e l e s e xc e p t i o n s e t l e s r e p r i s e s d ' e r r e u r s .
10
Structure du bloc PL/SQL (9/11)

Premiers pas en pl /sql

Un bloc pl/sql doit
Programm e1 se terminer par un /
Begin

End; Un bloc pl/sql doit

Programm e2
contenir au moins
une instruction
Begin

End;

11
Structure du bloc PL/SQL (10/11)

Programm e 3

Declare
Le bloc d’exécution
M o t va r c h a r 2 ( 2 0 ) ;
Begin/End est
End; obligatoire
/

12
Structure du bloc PL/SQL (11/11)

Programm e 4 : Aff ichage

Permet l’activation de
S e t s e r ve r o u t p u t o n l’affichage sur ecran
Declare

C h a i n e var c h a r 2 ( 10 ) : = ‘ B o n j o u r ’ ;

Begin Toute variable doit avoir été

déclarée avant de pouvoir être
D b m s _o u t p u t . p u t _l i n e ( c h a i n e ) ; utilisée dans la section
exécutable.
End;

13
Les variables (1/17)

La section déclarative
Syn ta xe :

nom variable [CONSTANT] type [ [NOT NULL] := expression ] ;

– nom variable représente le nom de la variable composé de lettres, chiffres, $, # ou _
L e n o m d e l a v a r i a b l e n e p e u t p a s e x c é d e r 3 0 c a r a c t è r e s , d o i t c o m m e n c e r p a r u n e l e t t r e , n’e s t p a s
sensible a la casse et doit être déclarée avant d’être utilisée.

– C O N STA N T i n d i q u e q u e l a v a l e u r n e p o u r r a p a s ê t r e m o d i f i é e d a n s l e c o d e d u b l o c P L / S Q L

– N OT N U L L i n d i q u e q u e l a v a r i a b l e n e p e u t p a s ê t r e N U L L , e t d a n s c e c a s e x p r e s s i o n d o i t ê t r e
indiqué.

– TYPE représente le type de la variable ()

14
Les variables (2/17)

( )
P lu s i eurs t yp es d e va ria b l es s on t m a n ip u lés p a r u n p ro gra m m e P L /S QL :
Va r i a b l e s P L/ S Q L :

– Ty p e s s c a l a i r e s r e c e va n t u n e s e u l e va l e u r :

I N T E G E R , R E A L , ST R I N G , D AT E , B O O L E A N + % T Y P E + t y p e s S Q L ( To u s l e s
types SQL sont utilisables en PL/SQL)

– Ty p e s c o m p o s i t e s ( % R O W T Y P E , R E C O R D )

Va r i a b l e s n o n P L/ S Q L :

– d é f i n i e s s o u s S Q L * P l u s ( d e s u b s t i t u t i o n e t g l o b a l e s ) , Va r i a b l e s h ô t e s ( d é c l a r é e s
d a n s d e s p r o g ra m m e s p r é c o m p i l é s ) .

15
Les variables (3/17)

Exe mples

– age integer;

– n o m va r ch a r 2 (3 0 ) ;

– da t e N a i s s a n ce d a t e ;

– ok boolean := true;

Rq: Dé clarations multiple s in terdites :

Exe mple: i , j integer;

16
Les variables (4/17)

Exemple
declare
date_naissance DATE := ‘03/02/2008’;
compteur integer default 0;
id char(5) not null ;
begin
d b m s _o u t p u t . p u t _l i n e ( d at e _n a i s s a n c e | | ' ' | | c o m p t e u r | | ' ' | | i d ) ;

end;
/
17
Les variables (5/17)

Le type %Type
– référ ence à un typ e exista nt qui est soit une co lonne d'une ta ble (o u
d’une vue) soit un type déf i ni précéd emm ent

nom_variable nom_table.nom _colonne%TYPE ;

nom_variable nom_variable_ref%TYPE ;

Exemple1 Exemple2
Declare Declare

i d P r o j e t p r o j e t . n u m p r o j % Ty p e ; D a t e 1 D AT E ;

D a t e 2 D a t e 1% Ty p e ; 18
Les variables (6/17)

Le type %RowType
– Ce t y p e e s t c o m p o s é d ’ u n e n s e m b l e d e c o l o n n e s d ’ u n e n r e g i s t r e m e n t .
L’e n r e g i s t r e m e n t p e u t c o n t e n i r t o u t e s l e s c o l o n n e s d ’ u n e t a b l e o u s e u l e m e n t c e r t a i n e s .

– Il fait référence à une ligne d'une table

– Exemple
Declare

Employe pe rs onne%Row ty pe;

– Utilité:
– diminue les changements à apporter au code en cas de modification des
types des colonnes de la table.
– Il est aussi possible d’insérer dans une table ou de modifier une table en 19
utilisant une variable de type %ROWTYPE.
Les variables (7/17)

Exe rcice

– C r é e r l e t yp e u n s e r v i c e c o m p o s é d ’ u n e n r e g i s t r e m e n t d e l a t a b l e s e r vi c e e t
d o n t t o u s l e s c o m p o s a n t s s o n t d e m ê m e t y p e q u e l a t a b l e s e r v i c e av e c l a v a l e u r
d u n u m é r o a c c o r d é a u s e r v i c e é t a n t c h o i s i e p a r d é f a u t e t l e s a u t r e s va l e u r s
quelconques.

– I n s é r e r l e s e r v i c e c r é e a u n i ve a u d e l a t a b l e s e r v i c e p u i s l e v i s u a l i s e r.

20
Les variables (8/17)

Le type Record : permet de déclarer des structures de données personnalisées.

TYPE nomRecord IS RECORD

( n o m C h a m p 1 t y p e D o n n é e s [ [ N OT N U L L ] { : = | D E FA U LT } e x p r e s s i o n ]

[,nomChamp2 typeDonnées… ]… );

– Exemple
Déclaration du RECORD employe contenant trois champs ;
DECLARE
initialisation du champ postemp par défaut à ingénieur.
T YP E e m p l oy e I S R E C O R D

(numemp NUMBER(4),

N o m e m p VA R C H A R 2( 1 6) ,

p o s t e m p VA R C H A R 2 ( 1 2 ) : = ‘ i n g é n i e u r ’ ) ;

E m p 1 e m p l o ye ; Déclaration d’une variable de type employe

21
Les variables (9/17)

Remarque il est possible qu’un champ d’un RECORD soit

lui-même un RECORD, ou soit déclaré avec les directives
%TYPE ou %ROWTYPE.

Exemple
– Déclarer une structure « point » composée d’une abscisse
et d’une ordonnée. Afficher l’abscisse et l’ordonnée d’un point
de votre choix.

22
Les variables (10/17)

Af fect a t io n

I l e x i s t e p l u s i e u r s f a ç o n s d e d o n n e r u n e va l e u r à u n e va r i a b l e :

– :=

– Par la directiv e default

– Par la directiv e INTO de la requête SELECT

Exempl es :

– age := 25;

– Aff icher en utilisant la variable nompersonne la personne ayant le

numéro 7501

23
Les variables (11/17)

Le type Varray

TYPE nomTypeTableau I S VAR RAY (t aill e) OF type Ele ments ;

N omTa blea u no mTyp eTabl ea u;

24
Les variables (12/17)

Le ty p e Ta bl e

T Y P E n o m Ty p eTa b l e a u I S TA B L E O F

{ t y p e S c a l a i r e | v a r i a b l e % T Y P E | t a b l e . c o l o n n e % T Y P E } [ N OT N U L L ]

| table.%ROWTYPE [INDEX BY BINARY_INTEGER];

n o m Ta b l e a u n o m Ty p eTa b l e a u ;

– Perme t de déf inir et manipule r de s tableaux dynamique s (car dé f inis sans dime nsion
initiale).

– Un tabl eau e st compos é d’une clé pri mai re (de type BINARY_INTEGER) et d’une col onne
(de type scalaire, TYPE, ROWTYPE ou RECORD) pour stocker chaque élément.

– La plage de valeurs du type BINARY_INTEGER est comprise entre

-2 147 483 647 et 2 147 483 647, ce qui signif ie que la valeur de la clé primaire peut être
n é g a t i v e . L' i n d e x a t i o n n e d o i t p a s n é c e s s a i r e m e n t c o m m e n c e r à 1 . 25
Les variables (13/17)

Fo nct ion s pour les tabl ea u x

– E X I S TS ( x ) Re t o u r n e T R U E s i l e x è m e é l é me n t d u t a b l e a u e x i s t e .

– C O U N T Re t o u r n e l e n o m b r e d ’ é l é m e n t s d u t a b l e a u .

– F I R S T / L A S T Re t o u r n e l e p r e m i e r / d e r n i e r i n d i c e d u t a b l e a u ( N U L L s i t a b l e a u
vide).

– P R I O R ( x) / N E X T ( x ) Re t o u r n e l ’ é l é me n t av a n t / a p r è s l e x è m e é l é me n t d u
tableau.

– D E L ET E ; D E L ET E ( x ) ; D E L E T E ( x , y) S u p p r i m e u n o u p l u s i e u r s é l é m e n t s d u
tableau.

26
Les variables (14/17)

Exercice
– Déclarer un tableau dynamique de chaine de caractères
dont les indices -3, -2, 0 contiennent des noms de
personnes ayant les numeros 7369,7566,7901.
– Afficher le premier indice, le dernier indice ainsi que le
nombre d’éléments du tableau.
– Afficher le contenu du tableau correspondant à l’indice 0.

27
Les variables (15/17)

Variables non pl /sql

Variable s de sub stitution
– I l e s t p o s s i b l e d e p a s s e r e n p a ra m è t r e s d ’e n t r é e d ’ u n b l o c P L/ S Q L d e s v a r i a b l e s
définies sous SQL*Plus.

– Ce s v a r i a b l e s s o n t d i t e s d e s u b s t i t u t i o n .

– O n a c c è d e a u x v a l e u r s d ’ u n e t e l l e v a r i a b l e d a n s l e c o d e P L/ S Q L e n f a i s a n t
p r é f i xe r l e n o m d e l a v a r i a b l e d u s y m b o l e « & » ( av e c o u s a n s g u i l l e m e t s s i m p l e s
s u i va n t q u’ i l s’a g i t d ’ u n n o mb r e o u p a s ) .

– Sy n t a xe

AC C E P T va r i a b l e [N U M B E R | C H A R | DATE B I N A RY _ F LO AT |
B I N A RY _ D O U B L E ] [P R O M P T t e x t | N O P R O M P T
28
Les variables (16/17)

Exe mple

E c r i r e u n p r o g ra m me q u i p e r m e t d e d e m a n d e r l a s a i s i e d ’ u n n u m é r o p u i s d e l ’a f f i c h e r.

29
Les variables (17/17)

Variables de session
– Il est possible de définir des variables de session (globales) définies sous
S Q L * P l u s a u n i v e a u d ’ u n b l o c P L/ S Q L .

– L a d i r e c t i v e S Q L *P l u s à u t i l i s e r e n d e h o r s d u b l o c P L/ S Q L e s t VA R I A B L E .

– D a n s l e c o d e P L/ S Q L , i l f a u t f a i r e p r é f i xe r l e n o m d e l a v a r i a b l e d e s e s s i o n d u
s ym b o l e « : ».

– L’a f f i c h a g e d e l a va r i a b l e s o u s S Q L *P l u s e s t r é a l i s é p a r l a d i r e c t i v e P R I N T.

Exe mple
– Déclarer une variable globale var1

– A f f e c t e r a c e t t e va r i a b l e l a s o m m e d ’ u n n o m b r e n u m + 5

– Afficher cette variable 30

Traitement conditionnel (1/5)

31
Traitement conditionnel (2/5)

Syntaxe de la structure IF :

I F < c on d i ti o n > TH EN c om m an des;

[ ELS IF < co n di t i on > T HE N co m ma nd es ; ]

[ ELS E co m ma n d es ; ]

EN D I F;

Exemple

Ecrire un programme qui permet d’af f icher qu’une personne, selon son
âge, est un enfant (avant 15 ans) ou un adulte (après 15 ans). 32
Traitement conditionnel (3/5)

Syntaxe de la structure CASE

CASE [variable]

WHEN expr1 THEN instructions1;

WHEN expr2 THEN instructions2;

....

[ELSE instruction N;]

END CASE ;

33
Traitement conditionnel (4/5)

Exemple

Donner en fonction de la note obtenue d’un étudiant la mention :

– Tr è s b i e n s i l a n o t e e s t > 1 6

– B i e n s i l a n o t e e s t e n t r e 14 e t 1 6

– Assez bien si la note est entre 12 et 14

– Pa s s a b l e s i l a n o t e e s t e n t r e 10 e t 1 2

34
Traitement conditionnel (5/5)

Exercice

Ecrire un programm e pe rm etta nt de dire pour le s pe rsonne s:

- 7501 da ns quel numé ro de se r vice elle est a f fe ctée

- 7901 combie n de p ersonnes travaillent ave c e lle da ns ce se r vice.

- 7902 la moyenne de s sa laires de son ser vice .

35
Exercice IF

Ecrire un programme pl/sql permettant d’extraire le salaire moyen du service

20. Si la différence entre le salaire minimal de ce service et le salaire moyen
est supérieure à 200, multiplier ce salaire par 2 (sans modification dans la
base de données).

Afficher pour la personne concernée son nom et prénom et son salaire avant
et après modification.

36
Traitement Répétitif (1/6)

Les boucles permettent d'exécuter plusieurs fois une instruction

ou une séquence d'instructions.

Il existe trois types de boucle :

Boucle de b ase LOOP for

loop
Boucle WHI LE

Boucle FOR

while

37
Traitement Répétitif (2/6)

La boucle loop
C ’e s t u n e b o u c l e p o t e n t i e l l e m e n t i n f i n i e .

Au moins une des instructions du corps de la boucle doit être une instruction de sortie.

D è s q u e l a c o n d i t i o n d e v i e n t v r a i e ( s i e l l e l e d e v i e n t . . .) , o n s o r t d e l a b o u c l e .

Syntaxe
LOOP
commandes;
. . .
EXIT [WHEN condition];
38
END LOOP;
Traitement Répétitif (3/6)

Exe mple

A p a r t i r d u n u mé r o d u de r n i er s e r v i c e i n s é ré a u n i v e a u d e la t a b l e s e r v i c e ,
u t i l i s e r u n e b o u c l e p o u r i n s é r e r à l ’a i d e d ’ u n c o m p t e u r ( d e v a l e u r é g a l e à 1) 3
n o u ve a u x e n r e g i s t r e m e n t s d e v o t r e c h o i x .

39
Traitement Répétitif (4/6)

La boucle While
Elle permet la sortie selon une condition prédéfinie.

E l l e e s t u t i l i s é e p o u r r é p é t e r d e s i n s t r u c t i o n s t an t q u e l a c o n d i t i o n c h o i s i e r e nv o i e T R U E .

S i l a c o n d i t i o n r e nv o i e l a va l e u r N UL L , l a b o u c l e e s t i g n o r é e e t l ' e x é c u t i o n d u p r o g ra m m e
r e p r e n d à l ' i n s t r u c t i o n s u i va n t l a f i n d e l a b o u c l e .

Syntaxe
W HI LE <c on ditio n> LOOP

comma n des;

EN D LOOP;

Exe mple
40
– Fa ire le m ême exemple que l e précédent en utilisa nt la bo ucle While .
Traitement Répétitif (5/6)

La boucle For
• C e t y p e d e b o u c l e p e r m e t d e r é p é t e r u n n o m b r e d é f i n i d e f o i s u n m ê m e t ra i t e m e n t . E l l e
e s t u t i l i s é e p o u r s i m p l i f i e r l e c o n t r ô l e d u n o m b r e d ' i t é ra t i o n s .
• L a d é c l ara t i o n d u c o m p t e u r e s t i m p l i c i t e .
• L a s y n t a x e 'l ow er _ bo un d . . up p er _b o un d ' e s t o b l i g a t o i r e .

Syntaxe
FOR <co mpte ur> I N [R EVERSE] <limite_in f> .. <limi te _s up> l oop

comma n des;
EN D LOOP;

Exemple
– Supprimer l es d ernières lignes aj outées suite aux deux exemples précédents. 41
Traitement Répétitif (6/6)

Règles à respecter pour la boucle FOR

• Le c om p t eur ne d oi t êt re réf éren c é q u 'à l' in t é rieu r d e la b ou c le ; i l n 'e s t pa s
d éf in i e n d eh ors .

• Le c om p t eur n e d oit p a s êt re u t i li s é en t a n t q u e c ib le d ' u ne a f f ec t at ion.

• A u c u n e li m it e d e b ouc l e n e d oit êt re NULL.

Autres règles pour les boucles

• U t il ise z l a b ou cl e LOO P lo rs q u e s es i ns t ruc t ion s d oivent s 'ex éc u ter au m oi ns u n e
f ois .

• U t il ise z l a b ou cl e WHI LE s i l a c on d i t ion d oi t êt re éva lu ée a u d éb u t d e c ha q ue

it éra t i on .

• U t il ise z u n e b ou c le FOR s i l e nom b re d 'it éra t i on s e s t c on nu . 42

Les curseurs (1/22)

• Dans un bloc PL/SQL, le langage PL/SQL prend en charge les

instructions LMD (pour extraire et modifier des données de la table de
base de données ) ainsi que les commandes de gestion des
transactions (commit, rollback).

• L’utilisation du résultat de la requête se fait à travers la commande

INTO

43
Les curseurs (2/22)

• Il faut définir autant de variables dans la clause INTO que de

colonnes de base de données dans la clause SELECT en
s’assurant qu'elles correspondent de manière appropriée et
que les types de données sont compatibles.

• Les fonctions de groupe, telles que SUM, COUNT, … dans une

instruction SQL peuvent être utilisées puisqu’elles
s'appliquent à des ensembles de lignes dans une table

44
Les curseurs (3/22)

Soit la requête suivante • Les instructions de type

Declare SELECT ... INTO ... manquent
Nompers [Link]%type;
de souplesse, elles ne
fonctionnent que sur des
Salairepers [Link]%type;
requêtes retourant une et une
Po s t e p e r s p e r s o n n e . p o s t e p % t y p e ;
seule valeur.
Begin
• Ne serait-il donc pas
SELECT nomp, postep, salairep into
interessant de pouvoir placer
nompers, postepers, salairepers
dans des variables le résultat
from personne where numservp = 20; d'une requête retournant
end; plusieurs lignes ?
/
45
Les curseurs (4/22)

• Etant donné que les requêtes renvoient très souvent un

nombre impor tant et non prévisible de lignes.

• On introduit, donc une notion de “curseur” pour récupérer (et

exploiter) les résultats de requêtes.

• Un curseur est une zone mémoire de taille fixe, utilisée par le

moteur SQL pour analyser et interpréter un ordre SQL

• Il contient le résultat d'une requête (0, 1 ou plusieurs lignes).

46
Les curseurs (5/22)

Il existe deux types de curseurs :

Curse urs im pli cites : cré és et gé ré s en in terne pa r le s e r veur Ora cle

a f in de traite r le s instructions SQL

Curse urs explicite s : dé claré s explicite ment par le progra mmeur

47
 Les curseurs (6/22)

Les curseurs explicites

Un curseur e xplicite, contrairem ent a u curseur implicite est géré pa r

l'utilisa teur pour traite r un ordre Se lect q ui ramè ne plusie urs l ignes

7369 SMITH CLERK

7566 JONES MANAGER

CURSEUR 7788 SCOTT ANALYST
7876 ADAMS CLERK
7902 FORD ANALYST Ligne courante

48
Les curseurs (7/22)

Etap es d’utilisation des curse urs Non

Oui
DECLARE OPEN FETCH VIDE? CLOSE

D é c l a r er l e c u r s eu r d an s l a s e c t i o n d é c l ara t i v e d ' u n b l o c P L / S Q L e n l e n o m m an t e t e n
d é f i n i s s a n t l a s t r u c t u r e d e l ' i n t e r r o g a t i o n à y as s o c i e r.

O u v r i r l e c u r s eu r : L' i n s t r u c t i o n O PE N e x é c u t e l ' i n t e r r o g a t i o n e t a t t ac h e t o u t e s l e s
va r i a b l e s r é f é r e n c é e s . L e s l i g n e s i d e n t i f i é e s p ar l ' i n t e r r o g a t i o n c o n s t i t u e n t l ' e n s e m b l e a c t i f
e t p e u ve n t d é s o r m ai s ê t r e e x t ra i t e s ( F ETC H ) .

P r o c é d e r à l ' ex t r a c t i o n ( F E TC H ) d e s d o n n é e s à p ar t i r d u c u r s e u r. D a n s l e d i ag ra m m e d e
f l u x p r é s e n t é d a n s l a d i a p o s i t i v e c i - d e s s u s , ap r è s c h a q u e e x t ra c t i o n ( f e t c h ) , vo u s t e s t e z
l ' e x i s t e n c e d e l a l i g n e d a n s l e c u r s e u r. S ' i l n ' y a p l u s d e l i g n e s à t ra i t e r, v o u s d e ve z f e r m e r
l e c u r s e u r.

Fer m er l e c u r s eu r . L' i n s t r u c t i o n C LO SE l i b è r e l ' e n s e m b l e a c t i f d e l i g n e s . Il e s t d é s o r m ai s 49

p o s s i b l e d e r o u v r i r l e c u r s e u r p o u r é t ab l i r u n n o u v e l e n s e m b l e a c t i f.
Les curseurs (8/22)

1 Ouverture du curseur

Pointeur de
curseur

2 Extraction (fetch)
d'une ligne
Pointeur de
curseur

Pointeur de
3 Fermeture du curseur curseur 50
Les curseurs (9/22)

Syn ta xe
– D écla ra tion du cur seur
CURSOR nomcurseur IS requête ;

– Exemple
DECLARE
CURSOR curseur_pers IS
SELECT nump, nomp
FROM personne
WHERE numservp=20;

numero [Link]%type;
nom [Link]%type; 51
Les curseurs (10/22)

- Ouver ture du curseur

Begin

O P E N c u r s e u r _p e r s ;

- Extra ction d es lignes

LO O P

F ETC H c u r s e u r _p e r s I N TO n u m e r o , n o m ;

E X I T WH E N c u r s e u r _p e r s % N OT F O U N D ;

D B M S _ O U T P U T. P U T _ L I N E ( ‘ l e n u m e r o e s t : ’ | | n u m e r o

||' le nom est'||nom);

END LOOP;

– Fermeture du cur seur

52
CLOSE c urseur_pers;
Les curseurs (11/22)

Exe mple

Sé lectionne r l’e nse mble de s e mployé s dont le sala ire ne dé passe pas
400 dina rs e t les augme nter de 5 dinars.

53
Les curseurs (12/22)

Curseurs et boucle FOR

• Il e xiste une boucle FOR se charge ant de l'ouve r ture , de la le cture

de s ligne s du curse ur e t de sa fe rme ture .

• Elle simplif ie le traite me nt des curse urs e xplicites.

• D es opé rations d'ouver ture , d' extraction (fe tch), de sor tie e t de
fermeture ont li eu de maniè re implicite.

• L'e nreg istrem ent e st d éclaré implicite ment.

54
Les curseurs (12/22)

Curseurs et boucle FOR

Syntaxe
FO R no m _en r eg is t r e men t IN n o m _c u r s eu r LOO P
in s t r u c t i o n 1;
in s t r u c t i o n 2;
. . .
E ND LO OP ;

– Rq! Le no m de l’enregistr ement est déclaré implicitem ent

E xe m p le

Sélectionner l’ensemble des employés dont le sala ire dépa sse 1000 dinar s et les
55
a ff icher.
Les curseurs (13/22)

Attributs d’un curseur explicite

Attribut Type Description

%ISOPEN Booléen Prend la valeur TRUE si le curseur est ouvert

%NOTFOUND Booléen Prend la valeur TRUE si la dernière extraction

(fetch) ne renvoie pas de ligne

%FOUND Booléen Prend la valeur TRUE si la dernière extraction

renvoie une ligne ; complément de
%NOTFOUND
%ROWCOUNT Nombre Prend la valeur correspondant au nombre
total de lignes renvoyées jusqu'à présent 56
Les curseurs (14/22)

At t rib ut %IS OPE N

– Les lignes ne peuvent être extraites que si le curseur est ouver t.

– L'attribut de curseur %ISOPEN ser t à déterminer si le curseur est

ouver t.

– %ISOPEN renvoie l'état du curseur à TRUE s'il est ouvert et FALSE

s'il est fermé.
Exempl e
– On peut tester si un curseur est ouvert avant de commencer a extraire les lignes

IF NOT emp_cursor%ISOPEN THEN

OPEN emp_cursor;
END IF;
LOOP
FETCH emp_cursor... 57
Les curseurs (15/22)

At t rib ut %R OWCOU NT

– Sert à :

– E x t ra i r e u n n o m b r e e x a c t d e l i g n e s

– E x t ra i r e ( f e t c h ) l e s l i g n e s a v e c u n e b o u c l e e t d é t e r m i n e r d a n s q u e l s c a s l a s o r t i e
de la boucle doit s'effectuer

E xe m p l e

– E c r i r e u n p r o g ra m m e p l / s q l p e r m e t t a n t d ’e x t r a i r e e t d ’a f f i c h e r l e s 4 p r e m i e r s
employés appartenants au service 10 s’ils existent sinon afficher uniquement ceux qui
existent.

– Rq! Il faut faire attention à la condition de sortie de la boucle.

58
Les curseurs (16/22)

Utilisation de sous interrogation dans la boucle FOR du curseur

On peut ne pas déclarer un curseur dans la boucle for et utiliser à la place
directement une sous interrogation
Syntaxe
F O R n o m_ e n r e g i s t r e m e n t I N ( R e q u ê t e ) LO O P
instruction1;
instruction2;
. . .
E N D LO O P ;

Exemple
– E xt ra ir e et a f f i c he r le s p er s o n n es t rav a il la nt a u s er vi c e 20.
59
Les curseurs (17/22)

Curseurs avec paramètres

Pe rm et t e nt de t ra n s m et tre d es p ara m è t res au c u rs eu r a u m om en t d e s on ou ver t u re
et d e l' ex éc u t ion d e l' in t e rrog a t ion.

Ou v ri r u n c u rs e u r ex p li c it e à p l us i eu rs rep ris es , e n renvoya nt u n en s em b l e ac t i f

d if f éren t à c ha qu e f oi s .

Syntaxe
CURSOR nom_curseur [(nom_parametre type, ...)]
IS select_statement;

OPEN nom_curseur (val_parametre,.....) ;

instructions;
CLOSE nom curseur; 60
Les curseurs (18/22)

Exe mple

– E xt ra i r e e t a f f i c h e r e n f o n c t i o n d u n u m é r o d e s e r vi c e

– l e n u m é r o d u s e r v i c e e t l e n o m b r e t o t a l d e s p e r s o n n e s y t rava i l l an t p o u r l e
s e r v i c e 10 .

– L e n u m é r o d u s e r v i c e e t l a m o y e n n e d e s s al ai r e s d e s p e r s o n n e s y t rava i l l a n t
pour le service30,

– L e n u m é r o d u s e r v i c e e t l a s o m m e t o t al e d e s s a l a i r e s d e s p e r s o n n e s
t rava i l l a n t a u s e r v i c e 6 0.

61
Les curseurs (19/22)

Clause FOR UPDATE

– L o r s q u e p l u s i e u r s s e s s i o n s p o u r u n e m ê me b a s e d e d o n n é e s s o n t o u ve r t e s,
les lignes d'une table particulière peuvent être mises à jour par plusieurs
u t i l i s a t e u r s a p r è s l ' o u v e r t u r e d u c u r s e u r.

– L e s d o n n é e s m i s e s à j o u r n e p e uv e n t ê t r e v u e s qu e l o r s q u e l e c ur s e u r e st
o u v e r t d e n o u ve a u .

– I l e s t d o n c p r é f é ra b l e de p l a c e r d e s ve r r o u s s u r l e s l i g n e s av a n t de l e s
m e t t r e à j o u r o u d e l e s s u p p r i m e r.

– Le verrouillage des lignes se fait av e c la clause FOR UP DAT E d a n s

l ' i n t e r r o g a t i o n d u c u r s e u r.

62
Les curseurs (20/22)

Syntaxe

SELECT ...
FROM ...
FOR UPDATE [OF column_reference] [NOWAIT | WAIT n];
– Le verro uillage explici te s er t à interdi re l'accè s a ux autr es ses sio ns pend an t
la durée d'une transa ction.

– Le verro uillage des lignes se fa it avant la mise à jo ur ou la suppression.

L'inst ruc tion S ELECT ... FOR UPDATE ide nt if ie le s ligne s qui seron t mi s es à jour
ou supprimé es, puis verroui lle chaque ligne de l'ense mble de résulta ts.
Cela s'avère utile lorsqu'une mise à jour e st basé e sur le s va leurs existante s
d'une ligne. Da ns ce cas, il faut s’assu re r q ue ce tte ligne n'e st pas mod if iée par
une autre se ssion avant la mise à jour. 63
Les curseurs (21/22)

• Le m ot -cl é facult at if NOWAIT in d iq ue a u s er veu r Ora c l e d e n e pa s at t en d re s i l es

li g n es d em a n d ée s s oien t verrou il lée s p a r u n au t re u t i li sa teu r.

• Le p rog ram m e rep ren d imm édia t em ent l e contrôle ; i l p eut a in s i ef f ect u er d'aut res
t ra va u x ava n t d e rées s ayer d' ob t en i r l e ve rrou i ll a g e.

• S i vou s om ett ez l e m ot - clé NOWAIT , le s e r veu r Ora cl e a t t en d q u e l es lig n es s oi ent

d is p on i b les m a is i l p eu t a t t en d re i nd é f i nime nt .

• S i l es l ig n es son t ve rrouill ée s p a r u n e a ut re s es s ion et q u e le m ot - clé NOW AIT es t

in diq u é, l 'ou ve rt ure d u c u rs eu r p rovoq u e un e erre ur ( il f a ut es s ayer d e le r ou vri r
u lt é rieu rem ent) .

• On p eut ut ilis er WAIT p lut ôt q u e NOWAIT et in diq u er l e nom bre d e s ec on d es

p en d ant le sq uell es a tt en dre e t v érif ie r q u e les l ig n es s ont déverrou il lées . Si les
li g n es s on t t ou jou rs ve rroui ll ées a p rès n s ec on d es , un e erre ur es t renvoyée . 64
Les curseurs (22/22)

Clause WHERE CURRENT OF

La clause WHE RE CURRENT OF e st u tili sée conjo inteme n t avec la clause FOR
UPD ATE af in de faire ré fé re nce à la ligne e n cours da ns un curseur e xplicite.

La cla use WHE R E CURRENT OF est utili sé e dans l'in s truc tion UPD ATE ou DE L ETE ,
ta nd is que la clause FOR UPDATE e st d é f inie d ans la déc la rat io n du
cur seur.

La clause FOR UPDATE pe ut être incluse d a ns l'inte rroga tion du cu rseu r af in

que le s ligne s soient verrouillé es lors de l'ouver ture (OPEN).

Exe mple

– M e t t r e à j o u r l e s s a l a i r e s d e s p e r s o n n e s d u s e r v i c e 10 e n l e s a u g m e n t a n t d e 1 0% .
65
Exceptions (1/16)

• P L / S QL p e r m e t d e d é f i n i r d an s u n e z o n e p a r t i c u l i è r e ( d e g e s t i o n d ’e xc e p t i o n ) , l ’a t t it u d e
q u e l e p r o g ram m e d o i t av o i r l o r s q u e c e r t a i n e s e r r e u r s d é f i n i e s o u p r é d é f i n i e s s e
produisent.

• Une erreur survenue lors de l'exécution du code déclenche ce que l'on nomme une
e xc e p t i o n .

• U n c e r t a i n n o m b r e d ’e xc e p t i o n s s o n t p r é d é f i n i e s s o u s O rac l e .

E xe m p l e s :

N O D ATA F O U N D ( Au c u n e d o nn é e tr o u v ée ) ( d e v i e n t v rai d è s q u ’ u n e r e q u ê t e r e n v o i e u n
résultat vide)

TO O M A N Y R O W S ( l ' e x t rac t i o n e x a c t e ra m è n e p l u s q u e l e n o m b r e d e l i g n e s d e m a n d é )

C U R S O R A L R E A DY O P E N ( c u r s e u r d é j à o u v e r t )
66
Exceptions (2/16)

Le code erreur associé est transmis à la section EXCEPTION, pour

laisser à l’utilisateur la possibilité de la gérer et donc de ne pas mettre
fin prématurément à l'application.

Exemple1 : Ecrire un programme plsql qui donne le nom des personnes

embauchées après le 01/01/2000 en utilisant la clause select…into...

Comme la requête ne ramène plus qu’une ligne, l'exception prédéfinie

TOO_MANY_ROWS est générée et transmise à la section EXCEPTION
qui peut traiter le cas et poursuivre l'exécution de l'application.
67
Exceptions (3/16)

E xe m p l e2 :

declare

nompers [Link]%type;

begin

Select nomp into nompers from personne

w h e r e d at e m b p > TO _ D AT E

( ' 0 1/ 0 1/ 2 0 0 0 ', ' D D / M M / Y Y Y Y ' ) ;

E XC E P T IO N

W H E N TO O _ M A N Y _ R O W S TH E N

D B M S _ O U TP U T. P U T _ L I N E ( ' U t i l i s ez un c ur s eu r ' );

end;
68
/
Exceptions (4/16)

Interrompre
Exception
brutalement
interceptée ?
l'exécution
Non

Oui
Exception Exécuter les
instructions Propager
générée
de la section l'exception
EXCEPTION

Interrompre
correctement 69
l'exécution
Exceptions (5/16)

Il existe 3 types d’exceptions

Les e xception pré déf inies d u ser veur Oracle. Ce sont l es erre urs le s
plus fréq ue n tes en la ngage p l /SQL. Ell es son t déclench ées
implicitement .

Les e xceptions non prédé f ini e s du se r veur Oracle. Ce sont les erre urs
stand ard s d’oracle Ser ve r. Elles sont déclanchées imp lici tement.

Les exce ption dé f inie s pa r l'util isateur . Ce son t de s condi tion s, déf inies
pa r le programme ur, com me anormales. Elles son t déclanch ées
explicitement.

70
Exceptions (6/16)

Syntaxe

E X C E P T IO N

W H E N < e x c e pt i o n 1 > [ O R < e x ce p ti o n 2 > O R . . .] T H E N < i n s t r u c t i o n s > ;

W H E N < e x c e pt i o n 3 > [ O R < e x ce p ti o n 2 > O R . . .] T H E N < i n s t r u c t i o n s > ;

W H E N OTH E R S TH E N < i n s t r u ct i o n s > ;

END;

71
Exceptions (7/16)

La s ec t ion de ge s tio n d es exc ep ti ons c om m en c e ave c le m ot - c lé

EXC E PTION.

Lo rs q u’ u ne excep ti on s e p rodu it , un s eul t rait em ent es t ex éc ut é ava nt la

s or t ie du b loc .

La s ec ti on d e t ra it em ent d e s exc ept i on s int erc ep t e s eu lem ent l es exc ep ti on s

q u i s ont d é f in ies ; l es a u t res n e s on t p a s in t erc ept ées s au f s i l a cla us e
WH E N OTH E RS es t p réc i s ée.

Ce tt e d erni ère p erm et d 'i nt ercep t er l es exc epti ons q ui n' ont p a s enc ore ét é
t ra i t ée s .

C’es t p ou r c ela q u e W H E N OTH E RS d oit ê t re la d ern iè re i n s t ru c tion d éf in i e.

72
Exceptions (8/16)

Exc ep t io n pr édé f i n i e Er r eu r Orac l e D es c r i pt i on

ACCESS_INTO_NUL ORA-06530 Assignation d’une valeur à un objet non initialisé

CURSOR_ALREADY_OPEN ORA-06511 Ouverture d’un curseur déjà ouvert

INVALID_CURSOR ORA-01001 Opération interdite sur un curseur

INVALID_NUMBER ORA-01722 Echec sur une conversion d’une chaine de

caractères vers un type number

LOGIN_DENIED ORA-01017 Connexion à oracle avec un utilisateur ou un

mot de passe invalide

PROGRAM_ERROR ORA-06501 PL/SQL a un problème interne

VALUE_ERROR ORA-06502 Erreur d’arithmétique, de conversion,

troncature ou de limite de taille

ZERO_DIVIDE ORA-01476 Division par zero

73
Exceptions (9/16)

Interception d’exceptions pré-définies

Elle se fai t e n utilisant le nom stand ard de l’e xce ption à l’inté rie ur d e la
se ction.

Une se ule exce ption à la fois est décle nché e et traité e.

Exe mple : Ecrire un bloc PL/ SQL permettant de sé lectionne r le nom d’un
e mployé e n conna issant le montant de son salaire sa l.
– S i l e s a l a i r e e n t r é r e nv o i e p l u s d ’ u n e l i g n e , t ra i t e r l ’e xc e p t i o n e n a f f i c h a n t l e
m e s s a g e « I l y a p l u s d ’ u n e m p l o yé ave c l e s a l a i re s a l ».

– S i l e s a l a i r e e n t r é n e r e n vo i e a u c u n e l i g n e , t ra i t e r l ’e xc e p t i o n e n a f f i c h a n t l e m e s s a g e
« Au c u n e m p l o yé ave c l e s a l a i r e s a l ».

– To u t e a u t r e e xc e p t i o n s e ra a f f i c h é e e n u t i l i s a n t l e m e s s a g e « Au t r e e r r e u r ».
74
Exceptions (10/16)

Exceptions définies par l’utilisateur

Les exce ptions dé clarée s par l’utili sateur doivent être dé cla ré es e t
nomm ée s da ns la par ti e D ECLARE du bloc PL/ SQL, dé cle nchée s
e xp lici teme nt da ns la s e ction e xécut able (BEGIN) à l’aide de
l’instruction RAISE et traité es da ns la pa r tie EXCEPTION.

75
Exceptions (11/16)

Syntaxe

DECLARE

n o m _ e xc e p t i o n EXCEPTION;

BEGIN

RAISE nom_exception;

EXCEPTION

W H E N n o m _ e xc e p t i o n T H E N … .

END; 76
Exceptions (12/16)

Exemple : Ecrire un programme PL/SQL permettant de mettre à

jour le nom d’un service et le valider en saisissant au préalable
un numéro et un nom.

Si le numéro de ser vice saisi n’existe pas, une exception est

produite et un message d’erreur s’affiche.

77
Exceptions (13/16)

Procédure raise_application_error
C’es t un e p roc é du re q u i pe rm et d e d éli vrer d es m es s a g es d ’erreu rs d éf ini s p ar
l’ u t il is a teur.

E ll e n e p e ut ê t re ap p elé e q ue d u ra n t l ’e xéc u t i on d ’u n s ou s - p rog ra m m e.

Syn t a xe
Code erreur spécifié par Message (chaine de
l’utilisateur compris caracteres)défini par
entre -20000 et -20999 l’utilisateur pour l’exception

Raise_application_error (numero_erreur,message [,{TRUE|FALSE}]);

True : l’erreur est rangée False(par défaut) :
dans la pile des erreurs l’erreur remplace toutes
78
78
précédentes les erreurs précédentes
Exceptions (14/16)

Cette procé dure pe ut ê tre utilisé e à de ux e ndroits:

– D a n s l a s e c t i o n e x é cu t a b l e

E xe m p l e

BEGIN

D E L ET E F R O M p e r s o n n e W H E R E n u m p = &num;

I F S Q L % N OT F O U N D T H E N

Ra i s e _a p p l i c a t i o n _ e r ro r ( - 20 22 2 , ‘e m p l o y e i n e x i s t an t ’ ) ;

E N D I F;

… 79
Exceptions (15/16)

– Dans la section exception

E xe m p l e

EXCEPTION

W H E N N O _D ATA _ FO U N D T H E N

Ra i s e _a p p l i c a t i o n _ e r ro r ( - 20 22 2 , ‘e m p l o y e i n e x i s t an t ’ ) ;

END;

– L e s e r re u r s a i n s i r e n v oy é e s p a r l a p r o c é d u r e Ra i s e _ a p p l i c a t i o n _ e r r o r s e r o nt
p l u s c o h é r e n t e s p a r ra p p o r t a u x e r r e u r s d u s e r v e u r o ra c l e .

80
Exceptions (16/16)

Exercice

Ecrire un prog ra mme P L/SQL q ui af f iche le nombre d’em ployé s qui

ga gnent 100d de p lus ou d e moins que l e monta nt d’u n sala ire saisi
préa lable ment.

S’il n’y a pa s d’em ployé s d ans cette t ran che de sa laires, a ff iche r un
me ssage à l’ util isate ur e n utilisant une e xception.

S’il y a au moins un e mpl oyé dans ce tt e tra nche, le m essa ge doit

indique r le nombre d’e mployés

Trai ter toute a utre e xception avec un me ssage a dé quat.

81
Procédures et fonctions (1/12)

Il est possible de créer des procédures et des fonctions dans PL/SQL

comme dans n’impor te quel langage de programmation classique.
Les procédures et fonctions sont des blocs PL/SQL nommés, appelés
sous-programmes .
Contrairement aux blocs anonymes:
Ils s ont Com pilés un e s eu le fois e t s ont s t oc ké s d an s l a ba s e d e d on n ées ( d’où
l’a p p e lla t ion d e p roc éd u re s t oc ké e) .

Il es t p os s i b le d e l es a p pe ler p a r d 'a u t res a p p l ic a ti on s

Ils peu vent ac cep t er d e s p a ra m èt res et d a ns l e c a s d es fon cti ons , d oi vent renvoyer
d es va leu rs .

82
Procédures et fonctions (2/12)

Toute fonction ou procédure créée devient un objet à part entière de

la base (comme une table ou une vue, par exemple).

Elle est souvent appelée “procédure ou fonction stockée”.

Elle est donc, entre autres, sensible à la notion de droit (son

créateur peut décider ou non d’en permettre l’utilisation à d’autres
utilisateurs).

Elle est aussi appelable depuis n’importe quel bloc PL/SQL .

83
Procédures et fonctions (3/12)

PROCEDURES
Syntaxe
C R E AT E [ O R R E P L AC E ] P R O C E D U R E < n o m _ p r o c e d u r e >

[ ( a r g u m e n t 1 [ m o d e 1 ] t y p e 1 , a r g u m e n t 2 [ m o d e 2 ] t y p e 2 , . . .) ]

IS

< z o n e d e d é c l a r a t i o n d e va r i a b l e s >

BEGIN

<corps de la procédure>

EXCEPTION

< t r a i t e m e n t d e s e xc e p t i o n s >

END;
84
Procédures et fonctions (4/12)

• C R EATE in d i q ue q u e l 'on veu t c réer u n e p roc é du re s t oc k ée d a n s la b as e

• La c la u s e f ac ulta ti ve OR REPLAC E p erm et d 'éc ra s er u n e p roc éd u re e xis t ant e

p or t a n t le m êm e n om

• N om pr oc é du r e es t l e nom do nn é p a r l' u t il isa t eu r à l a proc é d ure

• Il y a tro is modes pour pa sser les para mètres dans une procédure :
IN (lecture seule), OUT (écriture seule), INOUT (lecture et écriture).

• Le mode IN est réser vé aux pa ramètres qui ne doivent pas être

modif iés par la procédure.

• Le mode OUT, pour les paramètres transmis en résultat, le mode

INOUT pour les variables d ont la valeur peut être mod if iée en sor tie
et consultée par la procédure. 85
Procédures et fonctions (5/12)

L’appel de la procédure se fait :

S oit en u t il isa nt l’i n s t ru c tion

call nom_procédure();
Ou b ie n d an s u n b loc P L SQ L :

BEGIN

nom_procédure;

END;

86
Procédures et fonctions (6/12)

Exe mple1

E c r i r e u n e p r o c é d u r e P L / S Q L p e r m e t t a n t d ’e xt ra i r e c h a q u e e m p l o yé d u s e r vi c e 1 0 e t
l e p o s t e q u i l u i e s t a s s o c i é s o u s l a f o r m e « l ’e m p l o yé … a p o u r p o s t e … ».

Appeler cette procédure.

Exe mple2

E c r i r e u n e p r o c é d u r e P L / S Q L p e r m e t t a n t d ’a f f i c h e r l e c o m p t e à r e b o u r s d ’ u n n o m b r e .

87
Procédures et fonctions (7/12)

FONCTIONS
Syntaxe
C R E ATE [O R R E P L AC E ] F U N C T I O N < n o m _ f o n c t i o n >

[(argument1 [mode1] type1, argument2 [mode2] type2, . . .) ]

R E TU R N t y pe de do n n é e

IS

<zone de déclaration de variables>

BEGI N
<corps de la fonction>

EXCEPTION
< t r a i t e m e n t de s e x c e p t i o n s >
88
END;
Procédures et fonctions (8/12)

La différence entre une procédure et une fonction est qu'une

fonction doit renvoyer une valeur au programme appelant.

L’instruction RETURN devra se trouver dans le corps pour

spécif ier quel résultat est renvoyé.

La liste des arguments est facultative dans la déclaration d'une

fonction.

89
Procédures et fonctions (9/12)

L’appel de la fonction se fait :

S oit en u t il isa nt l’i n s t ru c tion

select nom_fonction() from dual;

Ou b ie n d an s u n b loc P L SQ L :

BEGIN

if nom_fonction(a,b) = c then …;

END;
E xe m p le 1

Ecrire une fo nct ion nbr e_emp p erm etta nt d’indiquer le nomb re tota l
90
d’employés par ser vice .
Procédures et fonctions (10/12)

Fonctions recevant des paramètres

Exe mple1
E c r i r e u n e f o n c t i o n « m i n i m u m » p e r m e t t a n t , à p a r t i r d e d e u x n o mb r e s d o n n é s a e t b,
d ’a f f i c h e r :

- a tel que a<b

- s i a > b a p p e l e r a - b j u s q u’ à c e q u e a s o i t i n f é r i e u r à b

E xé c u t e r c e t t e f o n c t i o n .

Exe mple2
E c r i r e u n e f o n c t i o n n b r e _ e mp _ s e r v p e r m e t t a n t d ’ i n d i q u e r l e n o m b r e d ’e m p l oy é s p o u r
un service donné.

Appeler cette fonction 91

Procédures et fonctions (11/12)

Exe mple3

Ecrire une fo nctio n verif_sal permet tant de détermine r si le sala ire

d'un employé do nné es t s up ér ieur ou inférieur a u sala ire moyen de tou s
les emp loyé s de so n ser vic e. La fonction renvo ie TRU E si le sa la ire de
l'employé e st supérieur a u sala ire moyen de s emp loyés de son ser vice ;
sinon, elle r e nvo ie FALSE. La fo nctio n renvoie NULL si une exc ep tio n
NO_ DATA _FOU ND est génér ée.

Appeler cette fo nctio n po ur un employé cho isi

92
Procédures et fonctions (12/12)

L e s p r o c é d u r e s e t f o n c t i o n s P L / S QL s u p p o r t e n t a s s e z b i e n l a s u r c h a r g e ( c o e x i s t e n c e
d e p r o c é d u r e s d e m ê m e n o m a ya n t d e s l i s t e s d e p a ra m è t r e s d i f f é r e n t e s ) . C ’e s t le
s y s t è m e q u i , a u m o m e n t d e l ’ap p e l , i n f é r e r a , e n f o n c t i o n d u n o m b r e d ’a r g u m e n t s e t
d e l e u r t y p e s , q u e l l e e s t l a b o n n e p r o c é d u r e à a p p e l e r.

Les procédures et fonctions sont des objets stockés. On peut, donc, les supprimer par
des instructions similaires aux instructions de suppression de tables…

DROP PROCEDURE <nom de procedure>

DROP FUNCTION <nom de fonction>

93
Déclencheurs /Triggers (1/13)

Déf inition
• Le s d éc l enc h eurs ou t ri gg e rs s o nt d es s éq u enc es d ’a ct ion s d éf in ies p ar l e
p rog ra m m eu r q u i s e d éc l en ch en t , n on p a s s u r u n a p p e l, m ai s d i rec t em ent quan d
u n é vén em ent p a r ticu lie r ( s p éc if ié l ors d e l a d éf i nit ion d u tri g g er) s u r u n e ou
p lu s i eu rs t a b les s e p rod u it .

• U n t ri g g er s e ra un ob j et s t oc ké (c om m e u n e t a b le o u u n e p ro cé d ure )

Principe
• U n t ri g g er s e l a n c e a u t om a tiq uem en t l ors q u' u n év én em en t s e p ro du i t .

• Pa r évé n em ent , on en t e nd t out e mo dif ic at i on d e s d on n ées se t rou vant d ans l es

t a b l es .

• On s 'e n s er t p ou r c ont rôl er ou a p p li qu er d e s c ont ra int es q u 'il est im p os sibl e d e

f orm u le r d e f a ç on d é cl a ra t ive . 94
Déclencheurs /Triggers (2/13)

E v é n e m e nt s d é c l en c h e u r s

Lo rs d e l a c réa tion d 'un t rigg er, i l c onvi ent de p ré c is er q u el e s t l e t yp e d ‘ évé n eme nt

q u i le d éc l en c h e:
– Insert

– Delete

– Update, …

M o m e nt s d ’ex éc ut i o n

Il f aut ég alem ent p réc i s er si le t rigg e r d oit ê t re exé cut é ava nt ( BE FO RE ) ou ap rè s

( A F T ER ) l‘ év én em e nt .
Durée

L’a c t i on a s s oc iée à u n t rig g er es t u n b l oc P L /S QL en reg is t ré d a n s la b as e .

U n t ri g g er e s t op éra t i on n el j u s q u’à la s u p p res s i on d e l a t a b le à la q ue ll e il e s t l ié . 95

L e n o m d u t r i g g e r d o i t ê t r e u n i q u e d an s l a b a s e d e d o n n é e s
Déclencheurs /Triggers (3/13)

Types de triggers
Le s t ri g g ers li gn e s ( row t ri g g er) s on t ex éc u t és s ép ar é me nt po u r c ha que
l i gn e m od i f i ée da n s l a ta bl e .

– I l s s ont tr è s ut i le s s’ i l f aut me s urer u ne é vo l uti on po u r c er t a in es

va leu r s , ef f ec t u er d es o p éra t i o ns p o u r c ha qu e l ig n e en q ue s t io n .

Le s t ri g g ers d e t a b le ( t rig g er g lo ba l /s t a t eme n t t rig g er) s on t ex éc u t és une

s eu le fo i s l or sq ue d e s m od i f ic a tions s ur v ien n en t s u r u n e t a b l e (m êm e s i
c es m od i f ic ati ons c on c ern en t p l us i eu rs li g ne s d e l a t a b le ).

– Ils s o n t u t il es s i d es o pé ra t io ns d e g r o u p e do ive nt ê t r e r é a li s ées ( c o mm e

le c a l c u l d ’u n e moy en n e, d ’ un e s o m me t o t a le, d ’u n c o m p t eu r, … ).

Pou r d es ra is ons d e p er form anc e, i l est p réf érab l e d ’em ployer les t rig g ers
d e t a b l e p lu t ôt q u e le s t ri g g ers lig n es . 96
Déclencheurs /Triggers (4/13)

Syntaxe

CREATE [OR REP LACE] TRIGGER NomTrigger

{ BEFORE | AF TER} liste _Instructions

ON nom_de _la_Table

[FOR EACH ROW]

[ WHEN condition]

BLOC PL/ SQL

97
Déclencheurs /Triggers (5/13)

L’op t i on BEF OR E /AF T ER in d i q ue l e m om en t d u d éc l en c h eme nt d u t ri g ge r.

Le s i n s tr u ct i on s SQL ( Pa r exemp l e : INSE RT OR UP DATE OR D E LETE )

p eu ve n t êt re t ou t es p rés e nt es c om m e o n p eu t en avo ir j u s t e u n e.

Pou r u n UP DAT E , on p eut sp éc if i er un e lis t e d e colonn es ( U PDAT E O F

lis teAtt rib uts ) . Da n s c e c a s , le t rig g er n e s e d éc l en ch era q u e s ’ il p or t e s u r
l’ u n e de s c ol on n es p ré c is ée s d a n s la l is t e .

F OR E AC H R OW es t u t i li s ée p ou r l es t rig g ers d e n ivea u lig n e.

WH E N co n di t i on : l e t rig g er es t d éc l en c hé s i la c on d it i on es t vra ie p ou r
c h a q u e li gn e .

98
Déclencheurs /Triggers (6/13)

Conditions de déclenchement d’un trigger

Le trigger se dé clenche lorsqu’un é véne ment pré cis sur vie nt : BEFORE
UPD ATE, AF TER D ELETE, AF TER INSERT, …

Ces é vé neme nts sont impor ta nts car ils déf inissent le mome nt
d’e xécu tio n du trigger.

Ain si, lor squ’un tri gger BEFOR E D ELETE e st programmé , il se ra

e xé cuté j uste ava nt la suppression d’un nouvel é léme nt da ns la table .

99
Déclencheurs /Triggers (7/13)

Si plusieurs triggers sont présents pour une même table, l’ordre

d’activation est :

1. BEFORE nive au tab le

2. BEFORE nive au ligne, aussi souve nt que de lignes concerné e s

3. AF TER nive au ligne, aussi souve nt que de lignes concerné e s

4. AF TER nive au tab le

100
Déclencheurs /Triggers (8/13)

Exe mple1 : Trigge r de ta ble

E c r i r e u n d é c le n c h e u r P L/ S Q L p e r m e t t a n t d ’a f f i c h e r u n m e s s a g e d ’e r r e u r « N e
supprimez pas de lignes dans la table affectation » si une o p é ra t i o n de
suppression des lignes de la table affectation est demandée.

E f f e c t u e r, p a r l a s u i t e , l e s i n s t r u c t i o n s s u i v a n t e s :

s e l e c t c o u n t ( *) f r o m a f f e c t a t i o n ;

d e l e t e f r o m a f f e c t a t i o n w h e r e n u m p e r s a f f = 7 47 7;

s e l e c t c o u n t ( *) f r o m a f f e c t a t i o n ;

101
Déclencheurs /Triggers (9/13)

Au niveau de l’instruction FOR EACH ROW, il est

possible avant la modification de chaque ligne, de
lire l'ancienne ligne et la nouvelle ligne par
l'intermédiaire de deux variables structurées :
: O L D. n o m At t r i b u t : c o r r e s p o n d à l a va l e u r ava n t l a t ra n s a c t i o n U P D AT E o u
D E L ET E

: N E W. n o m A tt r i b ut : c o r r e s p o n d à l a va l e u r ap r è s l a t ra n s a c t i o n U P D AT E o u
I N S E RT

Remarque : l o r s q u e l e s va r i ab l e s O L D e t N E W s o n t u t i l i s é e s d an s l a p a r t i e
WHEN condition, il ne faut pas utiliser les “:”
102
Déclencheurs /Triggers (10/13)

Exe mple de trigger sur D ele te

Ecrire un déclencheur PL/SQL «msg_supp »

permettant d’afficher le message « vous avez
supprimé la personne numéro …» si une opération
de suppression d’une ligne dans la table affectation
est effectuée.

Vérifier le déclenchement du trigger

103
Déclencheurs /Triggers (11/13)

Exe mple de trigger sur INSERT

Ecrire un dé c l e n c h e u r P L/ SQ L «ctrl_insertion» permettant d ’a f f i c h e r un

m e s s a g e d ’e r r e u r « o n n e pe u t p a s av o i r u n e mp l o yé e m b a u c h é a p r è s c e t t e
d a t e » s i u n e o p é ra t i o n d ’ i n s e r t i o n d ’ u n e l i g n e d a n s l a t a b l e p e r s o n n e c o n t e n a nt
u n e d a t e d ’e m b a u c h e > d a t e _ d u _j o u r e s t e f f e c t u é e .

I n sé r e r une ligne c o n t e n an t une date > d a t e _d u _ j o u r pour vé r i f i e r le

déclenchement du trigger

104
Déclencheurs /Triggers (12/13)

Déclencheur sur conditions multiples

L o r s q u’ u n t r i g g e r p o r t e s u r l e s o p é rat i o n s L M D, d e s p r é d i c a t s p e u v e n t ê t r e a j o u t é s d a n s
l e c o d e , p o u r i n d i q u e r l e s o p é rat i o n s d e d é c l e n c h e m e n t : I n s e r t i n g , U p d a t i n g , D e l e t i n g

Syntaxe
CREATE TRIGGER ...

BEFORE/AFTER INSERT OR UPDATE OR DELETE ON nom_Table

.......

BEGIN

......

IF INSERTING THEN ....... END IF;

IF UPDATING THEN ........ END IF;

IF DELETING THEN ........ END IF;

...... 105
END;
Déclencheurs /Triggers (13/13)

Exe mple

Ecrire un déclencheur P L/ S Q L « ms g _o p e ra t i o n s » permettant

d’indiquer p a r un m e s s a g e p o u r c h a q u e o p é ra t i o n e f f e c t u é e s i c ’e s t u n e
o p é ra t i o n d ’ i n s e r t i o n , d e m o d i f i c a t i o n o u d e s u p p r e s s i o n .

106