Introduction

 SQL est un langage déclaratif non procédural

 Certains traitements complexes nécessitent

l'utilisation d'un langage procédural permettant la
manipulation de variables et de structures de contrôle
telles que les boucles et les conditions.

 Il est parfois nécessaire d'effectuer des traitements

complexes sur le résultat d'une requête SQL ou de
l'injecter dans une nouvelle requête SQL.
2
PL/SQL
 PL/SQL: Programming Language/Structured Query Language

 PL/SQL est un langage propre aux SGBDR Oracle

 La plupart des SGBDR disposent de leur langage de

programmation procédural (SQL Server, PostgreSQL …)

 PL/SQL permet de définir des blocs d'instructions anonymes,

des procédures et des fonctions

 L'utilisation de variables permet de faire communiquer les

structures de contrôle du programme avec des requêtes SQL

3
Utilisations de PL/SQL
 PL/SQL permet d'écrire des triggers (procédures
déclenchées par la modification de données)

 PL/SQL est utilisé pour définir des procédures stockées

pouvant être appelée par la suite.

 PL/SQL permet d'écrire des fonctions utilisables par

les requêtes SQL.

4
Blocs PL/SQL
Le langage PL/SQL permet de définir 3 types de blocs:

1. Blocs anonymes: bloc composé d'une ensemble de

déclarations de variables et d'instructions à exécuter

2. Procédures stockées: procédure définie et stockée par le

SGBDR, pouvant être appelée dans n'importe quel bloc
PL/SQL.

3. Fonctions stockées: fonction définie et stockée par le

SGBDR, pouvant être utilisée dans n'importe quel bloc
PL/SQL ou requête SQL.

5
Structure d'un bloc PL/SQL
Un bloc peut contenir les sections suivantes:
DECLARE
--Déclaration de variables
BEGIN
-- Séquence d'instructions
EXCEPTION
-- Gestion des exceptions (division par zéro, …)
END;

Les sections déclaration de variables et gestion des exceptions sont

facultatives.

Les commentaires sur plusieurs lignes sont encadré par '/*' et '*/' .
Les commentaires sur une ligne commencent par '--' (deux tiraits).

6
Zone DECLARE
La zone DECLARE permet de déclarer et d'initialiser les variables, les
constantes et les curseurs (résultat temporaire d'une requête).

Déclaration de constante:
nom_vconst CONSTANT type := valeur;

Déclaration de variable:
nom_var type [NOT NULL := valeur];

Exemple:

DECLARE
nom VARCHAR2(60);
age NUMBER(3) NOT NULL := 18;
tva CONSTANT NUMBER(3,2) := 0.17;

7
Types de variables
 Tous les types de données Oracle sont présents dans PL/SQL

 Le type BOOLEAN existe en PL/SQL

test BOOLEAN := true;

 Il est possible d'associer à une variable le type d'une variable existante,

de la colonne d'une table ou d'une vue.
v_prenom Employé.Prénom%TYPE;

 Une variable peut contenir tous les attributs d'une table.

v_emp Employé%ROWTYPE
v_emp est déclarée comme une variable pouvant stocker une ligne de la
table Employé.

8
Variables
Une variable peut:
1. être initialisée au moment de sa déclaration
var type := valeur;
v_salaire NUMBER (7,2) := 42000;

2. se voir affecter une valeur

var := valeur;
v_prenom := 'Fateh';

3. recevoir une valeur provenant d'une requête SQL

SELECT col INTO var
FROM table
…
La requête doit obligatoirement renvoyer une seul tuple!!!!
SELECT Date_Naissance INTO v_date_nais
FROM Employé
WHERE Num_Employé = 1009;

9
Exemple 1
DECLARE
v_emp Employé%ROWTYPE;

BEGIN

SELECT * INTO v_emp

FROM Employé
WHERE Num_Employé = 1009;

dbms_output.put_line('Nom: ' || v_emp.Nom || ' Prénom: ' || v_emp.Prénom);

END;

La fonction put_line du package dbms_output permet d'afficher une chaine de

caractères (équivalent de printf en C).

10
RECORD
RECORD permet de définir un nouveau type composé de
champs élémentaires. (équivalent aux structures C)

TYPE nom IS RECORD(

Champs1 type,
Champs2 type,
…);

11
RECORD
TYPE etat_civil IS RECORD(
Nom Employé.Nom%TYPE,
Prénom Employé.Prénom%TYPE,
Date_Naissance Employé.Date_Naissance%TYPE);

v_personne etat_civil;
v_personne.Nom = 'Kermi';
v_personne.Prénom = 'Youcef';
v_personne.Date_Naissance = '19/08/1983';
12
 Condition (IF THEN ELSE)
IF condition THEN DECLARE
Bloc d'instructions 1 v_emp Employé%ROWTYPE;
[ELSE BEGIN
SELECT * INTO v_emp
Bloc d'instructions
2] FROM Employé
WHERE Num_Employé = 1009;
END IF;
IF v_emp.Date_Naissance < '18/04/1947' THEN
INSERT INTO Retraité
VALUES(v_emp.Num_Employé, v_emp.Nom,
v_emp.Prénom, v_emp.Date_Naissance,
v_emp.Fonction, v_emp.Est_Cadre)
END IF;

13
Condition (IF THEN ELSEIF)
IF condition THEN
Bloc d'instructions 1
[ELSEIF
Bloc d'instructions 2]
…
[ELSE
Bloc d'instructions n]

END IF;
14
Choix
CASE expression
WHEN expression1 THEN Bloc1;
[WHEN expression2 THEN Bloc2;]
…
[ELSE Bloc n;]
END CASE;

Expression équivalente au switch en C

Expression doit correspondre à un type atomique.
15
Boucles
Boucle WHILE:
WHILE condition LOOP
Boucle FOR:
Bloc d'instructions FOR cpt IN [REVERSE] i to j LOOP
END LOOP; Bloc d'instructions
Boucle LOOP: END LOOP;
LOOP Si REVERSE n'est pas utilisé i > j
Bloc d'instructions 1 Sinon i < j
[EXIT WHEN condition]
Bloc d'instructions 2
END LOOP; 16
Curseurs
Un curseur est une zone mémoire temporaire utilisée au moment de l'exécution
d'une requête SQL. Un curseur contient les informations relatives à une requête
SQL et aux lignes sélectionnées par cette requête.

Il existe deux types de curseurs:

Les curseurs implicites: créé automatiquement lors de l'exécution d'une requête

INSERT, UPDATE, DELETE ou d'une requête SELECT…INTO ne retournant qu'un
seul tuple.

Les curseurs explicites: créé par le développeur PL/SQL lors de l'exécution d'une
requête SELECT… INTO retourne plus d'un tuple. Bien que contenant tous les
tuples résultats d'une requête, les tuples ne peuvent être accédés qu'un seul à la
fois. Le tuple accédé est appelé le tuple courant. L'accès à un tuple fait passé un
pointeur au tuple suivant. Il est ainsi possible de consulter tous les tuples résultat
du premier au dernier en se déplaçant à chaque fois d'un tuple dans le résultat.

17
Curseur implicite
 Curseur généré automatiquement lors de l'exécution d'une requête
INSRET, UPDATE, DELETE ou SELECT… INTO retournant un seul
tuple.

 Le curseur implicite est nommé SQL.

 Lors de l'exécution d'une requête INSERT, UPDATE ou DELETE

PL/SQL offre un ensemble d'attributs pour obtenir des informations
sur les tuples affectés par la requête (nombre de tuples …).

 Lors de l'exécution d'une requête SELECT…INTO retournant

théoriquement un seul tuple, PL/SQL offre des attributs permettant de
savoir si un tuple a été retourné. PL/SQL génère une erreur si aucun
tuple n'est retourné.

18
Curseur implicite
Les attributs du curseur implicite sont:
 %FOUND
 %NOTFOUND
 %ROWCOUNT

SQL%FOUND retourne la valeur true si la requête INSERT, UPDATE ou DELETE exécuté a

affecté au moins un tuple. Elle retourne true également si la requête SELECT… INTO ayant
généré le curseur a renvoyé au moins un tuple. Retourne la valeur false si aucun tuple n'est
affectée par l'instruction INSERT, UPDATE ou DELETE. Retourne false également si aucun
tuple n'est retourné par la requête SELECT…INTO.

SQL%NOTFOUND retourne la valeur false si la requête INSERT, UPDATE ou DELETE

exécuté a affecté au moins un tuple. Elle retourne false également si la requête SELECT..
INTO ayant généré le curseur a renvoyé au moins un tuple. Retourne la valeur true si aucun
tuple n'est affectée par l'instruction INSERT, UPDATE ou DELETE. Retourne true également
si aucun tuple n'est retourné par la requête SELECT…INTO.

19
Curseur implicite
SQL%ROWCOUNT retourne le nombre de tuples affectés par la
requête INSERT, UPDATE ou DELETE, ainsi que le nombre de
tuples renvoyés par la requête SELECT …INTO.

v_emp Employé%ROWTYPE;
…
SELECT * INTO v_emp
FROM Employé
WHERE Num_Employé = 1009;

SQL%FOUND retourne true

SQL%NOTFOUND retourne false
SQL%ROWCOUNT retourne 1

20
Exemple
DECLARE
nb_tuples
BEGIN
DELETE FROM Employé WHERE Num_Employé = 1009;
nb_tuples := SQL%ROWCOUNT;
IF nb_tuples = 0 THEN
dbms_output.putline('Aucun tuple supprimé');
ELSEIF nb_tuple = 1 THEN
dbms_output.putline('1 tuple supprimé');
ELSE
dbms_output.put_line(nb_tuples || ' supprimés');
END IF;
END;

21
Curseur explicite
 Curseur utilisé pour traiter les requête SELECT… renvoyant
plusieurs tuples.

 Déclaré dans la zone DECLARE du bloc.

CURSOR nom_curseur IS SELECT …

Exemple:

DECLARE
CURSOR projets IS SELECT Description, Budget FROM Projet;
22
Curseur explicite
Il y a 4 étapes dans la manipulation d'un curseur
explicite:

1. Déclaration: dans la zone DECLARE

2. Ouverture du curseur: utilisation de OPEN
3. Transfert de données dans des variables (ou RECORD)
PL/SQL: utilisation de FETCH
4. Fermeture du curseur: utilisation de CLOSE

23
Utilisation du curseur
 Une fois qu'un curseur explicite est déclaré, il est
possible de l'utiliser dans la zone d'exécution du bloc.

 L'utilisation du curseur passe par 3 étapes:

1. Ouvrir le curseur
2. Lire les tuples du curseur un par un
3. Fermer le curseur

24
Ouvrir un curseur
OPEN nom_curseur;

Cette opération va consister à exécuter la requête

associée au curseur.

Exemple:
DECLARE
…
BEGIN
OPEN projets;
25
Lecture des tuples
FETCH nom_curseur INTO var1[, var2, …];
Ou
FETCH nom_curseur INTO record;

Cette opération va copier les valeurs des colonnes du

tuple courant dans les variables correspondantes ou dans
les champs du RCORD.

Après chaque FETCH, un pointeur de position se déplace

vers le tuple suivant faisant de lui le tuple courant.

26
Lecture de tuples
DECLARE
v_description Projet.Description%TYPE;
v_budget Projet.Budget%TYPE;
CURSOR projets IS SELECT Decription, Budget FROM
Projet;
BEGIN
OPEN projets;
FETCH projets INTO v_description, v_budget;
Les variables doivent être placées dans le même ordre que les
attributs correspondants du curseur. Les variables doivent
avoir des types compatibles avec les attributs correspondants.

27
Tuple courant
 Quand un curseur est ouvert, le premier tuple du
résultat devient le tuple courant.

 Après le premier FETCH le pointeur de position se

déplace vers le deuxième tuple qui devient le tuple
courant.
 Après chaque FETCH le pointeur se déplace vers le
tuple suivant.
 Essayer de lire après être arrivé à la fin du résultat
provoque une erreur.
28
 Lecture de tuples
Description Budget
Mise en place d'un réseau 39000.00
OPEN projets; intranet
Développement d'une
FETCH projets …; application de gestion 86000.00
FETCH projets …; Réalisation d'un CD-ROM 15000.00
FETCH projets …; interactif de formation
FETCH projets …; Développement d'un site 50000.00
internet

v_description = 'Mise en place d'un réseau intranet' v_budget = 39000.00

v_description = 'Développement d'une application de gestion ' v_budget = 86000.00
v_description = 'Réalisation d'un CD-ROM interactif de formation' v_budget = 15000.00
v_description = 'Développement d'un site internet' v_budget = 50000.00

29
Lecture de tuples
DECLARE
TYPE Desc_bu IS RECORD(
description Projet.Description%TYPE,
budget Projet.Budget%TYPE);
tpl Desc_bu;
CURSOR projets IS SELECT Decription, Budget FROM
Projet;
BEGIN
OPEN projets;
FETCH projets INTO tpl;
Le RECORD doit avoir le même schéma que le curseur
(même nombre de champs et types compatibles)
30
Fermeture du curseur
Après avoir utilisé un curseur explicite il est nécesaire de
le fermer.

CLOSE nom_curseur;

Exemple:
CLOSE projets;

31
Attributs du curseur explicite
Le curseur explicite dispose des attributs suivants:
 %FOUND
 %NOTFOUND
 %ROWCOUNT
 %ISOPEN

 Les 3 premiers ont la même signification que pour les

curseurs implicites.
 %ISOPEN retourne true si un curseur est ouvert. False
dans le cas contraire.

32
Parcourir un curseur 1
Il est possible d'utiliser une boucle pour parcourir un curseur.
DECLARE
…
BEGIN
OPEN projets;
LOOP
FETCH projets INTO description_v, budget_v;
EXIT WHEN projets%NOTFOUND;
dbms_output.put_line('Decription: ' || v_desription
|| ' Budget: ' || v_budget);
END LOOP;
CLOSE projets;
END;
33
 Lecture de tuples
Description Budget
Mise en place d'un réseau 39000.00
OPEN projets; intranet
Développement d'une
FETCH projets …; application de gestion 86000.00
FETCH projets …; Réalisation d'un CD-ROM 15000.00
FETCH projets …; interactif de formation
FETCH projets …; Développement d'un site 50000.00
internet

v_description = 'Mise en place d'un réseau intranet' v_budget = 39000.00

v_description = 'Développement d'une application de gestion ' v_budget = 86000.00
v_description = 'Réalisation d'un CD-ROM interactif de formation' v_budget = 15000.00
v_description = 'Développement d'un site internet' v_budget = 50000.00

34
Parcourir un curseur 2
DECLARE
…
BEGIN
OPEN projets;
FETCH projets INTO v_description, v_budget;
WHILE projets%FOUND THEN
LOOP
dbms_output.putline('Decription: ' || v_desription
|| ' Budget: ' || v_budget);
FETCH projets INTO v_description, v_budget;
END LOOP;
CLOSE projets;
END;
35
Parcourir un curseur 3
DECLARE
…
BEGIN
FOR tpl IN projets
dbms_output.putline('Decription: ' ||
tpl.desription || ' Budget: ' || tpl.budget);
END LOOP;
END;

36
Procédure stockée
 Une procédure stockée (ou procédure) est un bloc PL/SQL
permettant de réaliser un certains nombre d'instructions.

 Une procédure à un prototype et un corps.

 Le prototype est constitué du nom de la procédure et d'un
ensemble de paramètres.
 Le corps est un bloc PL/SQL.

 Une procédure stockées peut être appelée par n'importe

quel bloc PL/SQL.

37
Paramètres
Il faut distinguer trois types de paramètres:

 Paramètres IN: utilisé pour passer une valeur du bloc qui a

fait l'appel à la procédure.

 Paramètres OUT: utilisé pour passer une valeur de la

procédure au bloc qui l'a appelée.

 Paramètres IN OUT: utilisé pour passer une valeur à la

procédure et pour retournée une valeur au bloc appelant.

38
Procédure stockée
CREATE [OR REPLACE] PROCEDURE nom [paramètres]
IS
DECLARE
…
BEGIN
…
END;

REPLACE: permet, dans le cas où la procédure existe déjà, de

la remplacer.
39
Exemple
CREATE OR REPLACE PROCEDURE affiche_budget(v_projet_num IN
Projet.Num_Projet%TYPE)
IS
v_budget Projet.Budget%TYPE;
BEGIN
SELECT Budget INTO v_budget FROM Projet WHERE Num_Projet =
v_projet_num;
dbms_output.putline('Le budget du projet ' || v_projet_num || ' est de '
|| v_budget);
END;

40
Procédure
Pour tester cette procédure il suffit de taper dans Oracle
SQL developer:
EXECUTE affiche_budget(122);

Ou dans un bloc PL/SQL:

Affiche_budget(122);

41
Fonction
Les fonctions PL/SQL sont des procédures stockées
ayant une valeur de retour.

Les fonctions peuvent être appelées dans des blocs

PL/SQL ou dans des requêtes SQL.

42
Fonction
CREATE [OR REPLACE] FUNCTION nom [paramètres]
RETURN type_retour
IS
/* Déclaration de variables*/
var1 type;
var2 type;
…
BEGIN
…
return var1;
EXCEPTION
…
return var2;
END;

43
Exemple
CREATE OR REPLACE FUNCTION fn_budget (v_projet_num IN
Projet.Num_Projet%TYPE)
RETURN Projet.Budget%TYPE
IS
v_budget Projet.Budget%TYPE;
BEGIN
SELECT Budget INTO v_budget FROM Projet WHERE
Num_Projet = v_projet_num;
return v_budget;
END;

44
Fonction
Pour tester cette fonction, il suffit de taper dans Oracle
SQL Developer:

SELECT fn_budget(108)
FROM DUAL;

Ou dans un bloc PL/SQL:

var := fn_budget(108);

45
Exception
 PL/SQL permet la gestion des exceptions
 PL/SQL dispose d'exceptions prédéfinies
 Il est possible au développeur de définir de nouvelles
exceptions
 Lorsqu'une exception survient, un traitement adapté doit
être exécuté et/ou un message d'exception affiché à
l'utilisateur
 Chaque message d'exception dispose
 d'un type d'exception
 d'un code erreur
 et d'un message à afficher

46
Gestion des exceptions
DECLARE
…
BEGIN
…
EXCEPTION
WHEN exception1 THEN
gestion exception1
WHEN exception2 THEN
gestion exception2
…
END;

47
Types d'exceptions prédéfinies
 CURSOR_ALREADY_OPEN: se produit quand on essaye
d'ouvrir un curseur déjà ouvert.

 NO_DATA_FOUND: se produit quand une requête SELECT

… INTO ne retourne aucun tuple.

 ZERO_DIVIDE: se produit lorsque on essaye de diviser un

nombre par 0.
 TOO_MANY_ROWS: se produit lorsque on place dans un
record ou une variable plus d'un tuple à la fois.
…

48
Lever une erreur
 La fonction PL/SQL RAISE_APPLICATION_ERROR
permet au développeur de lever une erreur s'il constate
une anomalie dans l'exécution d'un bloc PL/SQL.

 RAISE_APPLICATION_ERROR prend en paramètres:

 Un code erreur compris entre -20000 et -20999
 Un message d'erreur

49
Lever une erreur
DECLARE
…
budget_faible EXCEPTION;
BEGIN
FOR tpl IN projets
LOOP
IF tpl.budget< 15000 THEN
RAISE budget_faible;
END IF;
END LOOP;
EXCEPTION
WHEN budget_faible THEN
RAISE_APPLICATION_ERROR(-20000, 'Budget trop faible');
END;

50
Packages
Oracle met à la disposition des développeurs un
ensemble d'objets (procédures, de fonctions, de type, de
constantes…) prédéfinies et regroupées dans des
packages.

Il est également possible de créer soit même ses propres

packages PL/SQL regroupant ses propres objets PL/SQL.

Exemple:
La procédure dbms_putline du package dbms_output.

51
Séparateur
Dans un script comprenant plusieurs blocs PL/SQL
indépendants, il est nécessaire de terminer chaque bloc
par un '/'.

CREATE OR REPLACE FUNCTION F1

…
END;
/
CREATE OR REPLACE PROCEDURE P1
…
END;

52
53
Contraintes d'intégrité
Certaines contraintes intégrités ne peuvent être exprimées de
manière déclarative lors de la définition d'une table.

Contraintes impliquant plusieurs tuples

Contraintes impliquant des tuples de plusieurs tables
Contraintes temporelles
…
Exemples:
La période d'affectation d'un employé à un projet doit
correspondre à la période d'affectation de son supérieur
La période d'affectation d'un employé à un projet doit être
comprise entre la date de début et la date de fin du projet
Le salaire d'un employé ne peut diminuer

54
Triggers sous Oracle
Pour vérifier ces contraintes d'intégrité, il est possible
d'utiliser des Triggers (déclencheurs).

Un Trigger est une procédure stockée PL/SQL associée à une

vue ou une table. Le Trigger déclenché par un évènement et
doit réaliser une action (bloc d'instructions PL/SQL).

Les types d'évènements pouvant déclencher un trigger sont:

une instruction d'insertion dans une table
une instruction de suppression dans un table
une instruction de mise à jour d'un ou plusieurs attributs dans
un table

55
Schéma Exemple
Projet (Num_Projet, Description, Date_Début, Date_Fin, Budget)

Employé (Num_Employé, Nom, Prénom, Date_Naissance, Fonction,

Salaire)

Affectation (Num_Employé, Num_Projet, Debut_Affect, Fin_Affect,

Supérieur)

56
 Relation Employé

Num_Employé Nom Prénom Date_Naissance Fonction Salaire

1001 Belaid Toufik 12/05/1965 Concepteur 37000
1009 Touati Rachid 13/09/1941 Chef de projet 58000
1023 Kadri Amine 23/11/1970 Développeur 29000
1053 Djabi Fatiha 04/06/1980 Analyste 21500
1026 Bouras Kamel 19/04/1968 Administrateur 36000
1005 Djabi Fatiha 22/08/1976 Développeur 32750

57
Déclaration de Trigger
Un trigger peut être exécuté avant (BEFORE) ou après (AFTER) l'évènement
déclencheur.

CREATE [OR REPLACE] TRIGGER nom_trigger

BEFORE|AFTER INSRET OR DELETE OR UPDATE ON nom_table

CREATE TRIGGER nom_trigger

BEFORE|AFTER INSERT OR DELETE OR UPDATE ON nom_table
…

CREATE TRIGGER insert_employe

AFTER INSERT ON Employé
…

CREATE TRIGGER modif_projet

AFTER DELETE OR UPDATE ON Projet
…

58
Déclaration de Trigger
Pour l'évènement déclencheur UPDATE, il est possible
de spécifier la liste des attributs devant être modifiés
pour déclencher le Trigger. Si aucun n'est spécifié,
n'importe quel attribut modifié déclenchera le Trigger.

Exemple:
CREATE TRIGGER modif_projet
AFTER UPDATE OF Date_Début, Date_Fin, Budget ON Projet
…

59
Types de Triggers
Il existe sous Oracle deux types de Triggers:
 Triggers exécutés une seule fois par instruction (Trigger
instruction, Trigger statement)
 Triggers exécutés une fois par tuple affecté (Trigger ligne,
Trigger row)

Pour le premier type, si un Trigger est déclenché par la

modification de plusieurs tuples, il ne sera exécuté qu'une
seule fois.

Pour le second type, si un Trigger est déclenché par une

modification concernant plusieurs tuples, il sera déclenché
une fois pour chaque tuple modifié.

60
Types de Triggers
Exemple 1:
Un Trigger devant vérifier que la somme de tous les
salaires des employés doit rester inférieure à
1000000.00DA ne doit se déclencher qu'une seule fois
même plusieurs salaires sont modifiés par une
instruction de mise à jour.

UPDATE Employé SET Salaire = Salaire*1.1

Requête impactant 6 tuples.
Trigger exécuté une seule fois.

61
 Relation Employé

Num_Employé Nom Prénom Date_Naissance Fonction Salaire

1001 Belaid Toufik 12/05/1965 Concepteur 37000
1009 Touati Rachid 13/09/1941 Chef de projet 58000
1023 Kadri Amine 23/11/1970 Développeur 29000
1053 Djabi Fatiha 04/06/1980 Analyste 21500
1026 Bouras Kamel 19/04/1968 Administrateur 36000
1005 Djabi Fatiha 22/08/1976 Développeur 32750

62
Types de Triggers
Exemple 2:
Un Trigger devant vérifier que chaque salaire a augmenté
et non pas diminué doit être déclenché pour chaque
tuple impacté par la mise à jour

UPDATE Employé SET Salaire = Salaire*1.1

Requête impactant 6 tuples.
Trigger exécuté 6 fois.

63
Type de Triggers
Par défaut, un Trigger ne s'exécute qu'une fois si l'évènement
déclencheur survient.

Pour exprimé le fait qu'un Trigger s'applique à chaque tuple

impacté par une modification il faut ajouter la clause FOR
EACH ROW.

Exemple:
CREATE TRIGGER modif_employe
AFTER UPDATE Salaire ON Employé
FOR EACH ROW
…
64
Triggers ligne
Il est possible, pour les Triggers ligne, d'effectuer une sélection sur
les tuples à traiter grâce à une expression logique SQL. Cette
expression logique ne peut pas comporter de sous-requête.

La clause WHEN est utilisée pour introduire cette requête.

CREATE TRIGGER modif_salaire

AFTER UPDATE Salaire ON Employé
FOR EACH ROW
WHEN (Fonction = 'Concepteur' OR Fonction = 'Chef de projet')
...
Empêchera l'exécution du Trigger pour les employés dont la
fonction n'est pas concepteur ou chef de projet.

65
Trigger ligne
CREATE TRIGGER modif_salaire
AFTER UPDATE Salaire ON Employé
FOR EACH ROW WHEN (old.Salaire < 250000.00)
...

Le Trigger ne s'exécute que pour les employés pour

lesquels l'ancien salaire est inférieur à 250000.00DA.

Si le l'ancien salaire est NULL le Trigger ne s'exécutera

pas non plus.

66
Corps du Trigger
Le corps du Trigger est constitué d'un bloc PL/SQL. Il
peut contenir des instructions SQL et du code de
contrôle PL/SQL.

Le code PL/SQL constituant le corps du Trigger peut

faire appel à des fonctions ou des procédures stockés
déjà définies.

67
Noms de corrélation
Dans un Trigger ligne, il est possible d'accéder à l'ancienne et à la
nouvelle valeur d'un attribut modifié du tuple en cours de
traitement.

Les noms de corrélation permettent de désigner ces deux valeurs.

Nouvelle valeur du tuple: NEW

Ancienne valeur du tuple: OLD

Exemple:
Ancien salaire: (avant mise à jour) OLD.Salaire
Nouveau salaire (après mise à jour): NEW.Salaire
68
Noms de corrélation
Pur un Trigger déclenché par une instruction INSERT,
seules les nouvelles valeurs des attributs peuvent être
consultées (NEW).

Pour un Trigger déclenché par une instruction DELETE,

seules les anciennes valeurs des attributs peuvent être
consultées (OLD).

69
Restrictions
Seules les instructions DML(SELECT, INSERT, UPDATE,
DELETE) sont autorisées dans le corps d'un Trigger. Il n'est
pas permis d'utiliser les instructions DDL (CREATE, ALTER,
DROP).

Une Trigger ligne ne peut ni lire, ni modifier la table

concernée par l'évènement ayant déclenché ce Trigger. Pour
réaliser ce type d'opération il faut créer une table temporaire
répliquant les données de cette table.

Il est possible de déclencher des Triggers en cascade. Oracle

autorise des cascade de 32 Triggers au maximum.
70
Exemple
Ecrire un Trigger vérifiant que le nouveau salaire d'un
employé après modification est supérieur à l'ancien salaire
(uniquement pour les employés ayant un salaire de moins de
250000.00DA).

CREATE TRIGGER modif_salaire

AFTER UPDATE Salaire ON Employé
FOR EACH ROW WHEN(old.Salaire <250000.00)
BEGIN
IF :old.Salaire > :new.Salaire THEN
RAISE_APPLICATION_ERROR(-20545, 'Nouveau salaire trop faible');
END IF;
END;

Les noms de corrélation doivent être précédés par ':' (Sauf dans la clause WHEN).

71
Exemple
Ecrire un Trigger vérifiant que les dates de début et de fin d'affectation
sont bornées par les dates de début et de fin du projet auquel correspond
l'affectation.

CREATE TRIGGER insert_affectation

AFTER INSERT ON Affectation
FOR EACH ROW
DECLARE
v_debut DATE;
v_fin DATE;
BEGIN
SELECT Date_Début, Date_Fin INTO v_debut, v_fin
FROM Projet
WHERE Num_Projet = :NEW.Num_Projet;
IF :NEW.Debut_Affect < v_debut OR :NEW.Fin_Affect > v_fin THEN
RAISE_APPLICATION_ERROR(-20544, 'Affectation hors période de projet');
END IF;
END;

72
Référencement
Si une table du schéma porte le nom new (resp. old) il est
impossible d'utiliser les noms de corrélation (new et old). Le
SGBD interprétera ces noms comme les noms des tables du
schéma.

Pour remédier à cela il est possible d'associer ces noms de

corrélation à des références (alias).

Pour cela il suffit d'utiliser la clause:

REFERNCING OLD AS o NEW AS n

73
Référencement
CREATE TRIGGER insert_affectation
AFTER INSERT ON Affectation
REFERENCING NEW AS nouv
FOR EACH ROW
DECLARE
v_debut DATE;
v_fin DATE;
BEGIN
SELECT Date_Début, Date_Fin INTO v_debut, v_fin
FROM Projet
WHERE Num_Projet = :nouv.Num_Projet;
IF :nouv.Debut_Affect < v_debut OR :nouv.Fin_Affect > v_fin THEN
RAISE_APPLICATION_ERROR(-20544, 'Affectation hors période de
projet');
END IF;
END;

74
Exercice
Ecrivez des Triggers permettant de:
1. Insérer chaque employé supprimé de la table
employé et dont l'âge est supérieur à 65 ans dans la
table retraité.

2. Vérifier pour une nouvelle affectation que l'employé

affecté a un salaire inférieur ou égal à son supérieur.

3. Empêcher la modification d'un projet qui a déjà

commencé.

75
Corrigé
CREATE OR REPLACE TRIGGER suppr_employe
AFTER DELETE ON Employé
FOR EACH ROW WHEN (OLD.Date_Naissance < '22/04/1947')
BEGIN
INSERT INTO Retraité VALUES(:OLD.Num_Employé, :OLD.Nom,
:OLD.Prénom, :OLD.Date_Naissance, :OLD.Fonction, :OLD.Salaire);
END;

76
Corrigé
CREATE OR REPLACE TRIGGER insert_affect
AFTER INSERT ON Affectation
FOR EACH ROW WHEN (NEW.Supérieur IS NOT NULL)
DECLARE
v_sal_emp Employé.Salaire%TYPE;
v_sal_sup Employé.Salaire%TYPE;
BEGIN
SELECT Salaire INTO v_sal_emp FROM Employé WHERE Num_Employé =
:NEW.Num_Employé;
SELECT Salaire INTO v_sal_sup FROM Employé WHERE Num_Employé =
:NEW.Supérieur;
IF v_sal_emp > v_sal_sup THEN
RAISE_APPLICATION_ERROR(-20420, 'Salaire du supérieur trop faible');
END IF;
END;

77
Corrigé
CREATE OR REPLACE TRIGGER modif_proj_comm
AFTER UPDATE ON Projet
FOR EACH ROW
BEGIN
IF :OLD.Date_Début < SYSDATE THEN
RAISE_APPLICATION_ERROR(-20147, 'Projet ayant débuté.
Modification impossible');
END IF;
END;

78