Chapitre 3: Procédures, fonctions et Triggers
Paris ***** Du 6 au 7 septembre 2010
Introduction au Langage PL/SQL
Pr: M.GHAILANI Langage de programmation PL/SQL
Chapitre 3: Procédures, fonctions et Triggers
Qu'est-ce qu’une procédure?
Une procédure stockée (PS) est un ensemble nommé
d’instructions PL/SQL, précompilé et stocké sur le
Paris ***** Du 6 au 7 septembre 2010
serveur. Les PS constituent une méthode privilégiée pour
encapsuler les tâches répétitives afin de les exécuter
efficacement.
Pr: M.GHAILANI Langage de programmation PL/SQL
Chapitre 3: Procédures, fonctions et Triggers
Quelques avantages des PS?
Performance: chaque fois qu’une requête SQL est
exécutée, le serveur construit un plan d’exécution avant
Paris ***** Du 6 au 7 septembre 2010
d’exécuter la requête. Les PS sont plus performantes car la
première fois que la PS est exécutée, le serveur crée le
plan d’exécution, ensuite les exécutions ultérieures de
cette PS seront beaucoup plus rapides parce que le
serveur ne recrée pas un nouveau plan.
Pr: M.GHAILANI Langage de programmation PL/SQL
Chapitre 3: Procédures, fonctions et Triggers
Quelques avantages des PS?
Réutilisabilité: Une fois que la PS est créée on peut
l’appeler à n’importe quel moment. Ceci permet une
Paris ***** Du 6 au 7 septembre 2010
modularité et encourage la réutilisation du code.
Accès Réseau: Les PS contribuent à la réduction du
trafic sur le réseau. Au lieu d’envoyer des centaines
d’instructions SQL sur le réseau les utilisateurs peuvent
effectuer une opération complexe à l’aide d’une seule
instruction réduisant ainsi le nombre de demandes
échangées entre le client et le serveur.
Pr: M.GHAILANI Langage de programmation PL/SQL
Chapitre 3: Procédures, fonctions et Triggers
Syntaxe de création d’une PS sans paramètres
Create or replace procedure nom_proc
is
les variables locales sont à
Paris ***** Du 6 au 7 septembre 2010
BEGIN
-- code de la procédure
déclarer entre IS et BEGIN
END;
Pr: M.GHAILANI Langage de programmation PL/SQL
Chapitre 3: Procédures, fonctions et Triggers
Syntaxe de création d’une PS avec paramètres
Create or replace procedure nom_proc (PARAM1 mode type1,……)
is
mode définit le type du paramètre:
Paris ***** Du 6 au 7 septembre 2010
BEGIN
- IN : paramètre d’entrée
-- code de la procédure - OUT : paramètre de sortie
END; - IN OUT : paramètre d’entrée / sortie
Pr: M.GHAILANI Langage de programmation PL/SQL
Chapitre 3: Procédures, fonctions et Triggers
Exemple d’une PS avec paramètres
Paris ***** Du 6 au 7 septembre 2010
Pr: M.GHAILANI Langage de programmation PL/SQL
Chapitre 3: Procédures, fonctions et Triggers
Qu'est-ce qu’une fonction?
Une fonction est semblable à une procédure stockée à la
différence qu’elle renvoie toujours une valeur.
Paris ***** Du 6 au 7 septembre 2010
Pr: M.GHAILANI Langage de programmation PL/SQL
Chapitre 3: Procédures, fonctions et Triggers
Syntaxe de création d’une fonction sans paramètres
Create or replace FUNCTION nom_fonction
return type_retourné
Paris ***** Du 6 au 7 septembre 2010
is
BEGIN
-- code de la fonction
return (var_resultat);
END;
Pr: M.GHAILANI Langage de programmation PL/SQL
Chapitre 3: Procédures, fonctions et Triggers
Syntaxe de création d’une fonction avec paramètres
Create or replace FUNCTION nom_fonction (PARAM1 mode type1,……)
return type_retourné
Paris ***** Du 6 au 7 septembre 2010
is
BEGIN
-- code de la fonction
return (var_resultat);
END;
Pr: M.GHAILANI Langage de programmation PL/SQL
Chapitre 3: Procédures, fonctions et Triggers
Exemple d’une fonction avec paramètres
Paris ***** Du 6 au 7 septembre 2010
Pr: M.GHAILANI Langage de programmation PL/SQL
Chapitre 3: Procédures, fonctions et Triggers
Qu'est-ce qu’un déclencheur (ou Trigger)?
❑ Un déclencheur est une procédure stockée qui
s’exécute, en arrière plan, en cas de mise à jour (insert,
Paris ***** Du 6 au 7 septembre 2010
update ou delete) des données qu’il protège;
❑ Ces procédures se déclenchent automatiquement après
que l’événement concerné a été soulevé;
❑ Un trigger est toujours rattaché à une table ou à une vue.
Pr: M.GHAILANI Langage de programmation PL/SQL
Chapitre 3: Procédures, fonctions et Triggers
Syntaxe de création d’un déclencheur
CREATE [OR REPLACE] TRIGGER <nom_trigger>
{BEFORE | AFTER } |INSTEAD OF
Paris ***** Du 6 au 7 septembre 2010
{INSERT| DELETE | UPDATE [OF colonnes]} Evénement
ON <nom_table|nom_vue>
[FOR EACH ROW] Pour chaque ligne
[WHEN <condition>] Condition
<blocPLSQL> Action
Pr: M.GHAILANI Langage de programmation PL/SQL
Chapitre 3: Procédures, fonctions et Triggers
Syntaxe de création d’un déclencheur
Option Signification
Paris ***** Du 6 au 7 septembre 2010
BEFORE |AFTER Déclenche le trigger avant ou après la
modification des données
INSTEAD OF Le corps du déclencheur est exécuté à la
place de l’ordre PL/SQL envoyé sur la table
ou la vue.
Pr: M.GHAILANI Langage de programmation PL/SQL
Chapitre 3: Procédures, fonctions et Triggers
Spécification d’une action en fonction de l’événement
Usage de WHEN:
case
❑ WHEN INSERTING then --Action1
Paris ***** Du 6 au 7 septembre 2010
❑ WHEN DELETING then --Action2
❑ WHEN UPDATING then –Action3
END Case
Usage de IF
❑ IF INSERTING THEN ….. END IF;
❑ IF DELETING THEN ….. END IF;
❑ IF UPDATING THEN ….. END IF;
Pr: M.GHAILANI Langage de programmation PL/SQL
Chapitre 3: Procédures, fonctions et Triggers
Les types de triggers
Il existe deux types de triggers différents :
❑ Les triggers de table: sont exécutés une seule fois lorsque des
Paris ***** Du 6 au 7 septembre 2010
modifications surviennent sur une table. Ils sont utiles si des
opérations de groupe doivent être réalisées (comme le calcul
d’une moyenne, d’une somme totale, d’un compteur, …).
❑ les triggers de ligne (ROW) : sont exécutés « séparément »
pour chaque ligne concernée par l'instruction insert, update ou
delete. [FOR EACH ROW]
Pr: M.GHAILANI Langage de programmation PL/SQL
Chapitre 3: Procédures, fonctions et Triggers
Accès aux anciennes et nouvelles valeurs (triggers de ligne )
Les anciennes et nouvelles valeurs de la ligne courante sont
référencées par :OLD.colonne et :NEW.colonne.
Paris ***** Du 6 au 7 septembre 2010
Ancienne valeur Nouvelle valeur
:OLD.colonne :NEW.colonne
INSERT NULL Nouvelle valeur
DELETE Ancienne valeur NULL
UPDATE Ancienne valeur Nouvelle valeur
Pr: M.GHAILANI Langage de programmation PL/SQL
Chapitre 3: Procédures, fonctions et Triggers
Exemple : Accès aux valeurs des colonnes en Trigger
Table Article
Code_art Libellé Prix_Uni Qte_stock
1 Pc Portable 6000 10
Paris ***** Du 6 au 7 septembre 2010
2 Tv Samsung 3000 5 :NEW
Insert into Article
(1) Values (2,’Tv Samsung’,3000,5);
Update Article :NEW.qte_stock=15
(2) Set Qte_stock=15 Where code_art=2; 2 Tv Samsung 3000 15
:OLD.qte_stock=5
Delete from Article
(3) 2 Tv Samsung 3000 15
Where code_art=2;
:OLD
Pr: M.GHAILANI Langage de programmation PL/SQL
Chapitre 3: Procédures, fonctions et Triggers
Exemple 1 d’un trigger de ligne
Paris ***** Du 6 au 7 septembre 2010
Pr: M.GHAILANI Langage de programmation PL/SQL
Chapitre 3: Procédures, fonctions et Triggers
Exemple 2 d’un trigger de ligne
Paris ***** Du 6 au 7 septembre 2010
❑ Dans le bloc PL/SQL, on peut faire référence aux colonnes par les
préfixes :NEW et :OLD
❑ Dans la clause WHEN, les colonnes sont accessibles via NEW ou OLD
Pr: M.GHAILANI Langage de programmation PL/SQL
Chapitre 3: Procédures, fonctions et Triggers
Désactivation et Réactivation d'un TRIGGER
Désactivation d'un TRIGGER ➔ ALTER TRIGGER <nom_trigger> DISABLE;
Réactivation d'un TRIGGER ➔ ALTER TRIGGER <nom_trigger> ENABLE;
Paris ***** Du 6 au 7 septembre 2010
Désactivation de tous les triggers d'une table
ALTER TABLE <nom_table> DISABLE ALL TRIGGERS;
Réactivation de tous les triggers d'une table
ALTER TABLE <nom_table> ENABLE ALL TRIGGERS;
Pr: M.GHAILANI Langage de programmation PL/SQL
