PL/SQL
Mehdi HAJJI
SGBD II2
Plan du cours
} Introduction
} Structure dun programme
} Les variables
} Les structures de contr les
} Interactions simples avec une Base de donn es
} Les curseurs
} Les exceptions
} Les proc dures et les fonctions
} Les triggers
} Les packages
PL/SQL
-M. HAJJI-
2
Introduction
} SQL est un langage non proc dural
} Les traitements complexes sont parfois difficiles crire
si on ne peut utiliser des variables et les structures de
programmation comme les boucles
} On ressent vite le besoin dun langage proc dural pour
lier plusieurs requ tes SQL avec des variables et dans les
structures de programmation habituelles (les boucles et
les alternatives)
} Extension de SQL : des requ tes SQL cohabitent avec les
structures de contr le habituelles de la programmation
structur e (blocs, alternatives, boucles)
PL/SQL
-M. HAJJI-
3
Introduction
} Un programme est constitu de proc dures et de
fonctions
} Des variables permettent l change dinformation entre
les requ tes SQL et le reste du programme
PL/SQL
-M. HAJJI-
4
Structure dun programme
} Blocs
} Un programme est structur en blocs dinstructions de 3
types:
} proc dures anonymes
} proc dures nomm es
} fonctions nomm es
} Un bloc peut contenir dautres blocs
PL/SQL
-M. HAJJI-
5
Structure dun programme
} Structure dun bloc
DECLARE
-- d finitions de variables
BEGIN
END;
/
-- Les instructions ex cuter
EXCEPTION
-- La r cup ration des erreurs
PL/SQL
-M. HAJJI-
6
Les variables
7
PL/SQL
-M. HAJJI-
Variables
} Identificateur Oracle:
} 30 caract res au plus
} Commence par une lettre
} Peut contenir chiffres, lettres, _, $ et #
} Pas sensible la casse
} Port e habituelle des langages blocs
} Doivent tre d clar es avant d tre utilis es
PL/SQL
-M. HAJJI-
8
Commentaires
} -- pour une fin de ligne
} /* pour plusieurs
lignes */
PL/SQL
-M. HAJJI-
9
D claration dune variable
identifiant datatype [:= | DEFAULT expr];
Declare
v_hiredate DATE;
v_deptno NUMBER(2) NOT NULL := 10;
v_location VARCHAR2(13) := 'Atlanta';
v_deptno NUMBER(2) NOT NULL := 10;
v_location VARCHAR2(13) := 'Atlanta';
c_comm CONSTANT NUMBER := 1400;
c_comm CONSTANT NUMBER := 1400;
} D clarations multiples interdites :
i, j integer;
PL/SQL
-M. HAJJI-
10
Variables: Lattribut %TYPE
} D clarer une variable partir de :
} la d finition dune colonne de la base de donn es
} la d finition dune variable pr c demment d clar e
} Pr fixer %TYPE avec :
} La table et la colonne de la base
} Le nom de la variable pr c demment d clar e
...
v_ename emp.ename%TYPE;
v_balance NUMBER(7,2);
v_min_balance v_balance%TYPE := 10;
...
PL/SQL
-M. HAJJI-
11
Variables: Lattribut %ROWTYPE
} Une variable peut contenir toutes les colonnes dune
ligne dune table
...
employe emp%ROWTYPE;
...
} d clare que la variable employe contiendra une ligne de la
table emp
PL/SQL
-M. HAJJI-
12
Entr e/Sortie
} Une alternative pour laffichage de donn es partir dun
bloc PL/SQL
DBMS_OUTPUT.PUT_LINE
dbms_output.put_line('nb = ' || nb);
} Doit tre autoris e sous SQL*Plus laide de :
SET SERVEROUTPUT ON
} Pour saisir des donn es
Accept
Accept nom_variable PROMPT Saisir variable : ;
PL/SQL
-M. HAJJI-
13
Entr e/Sortie
} Exemple
set serveroutput on
accept vstring prompt "Please enter your name: ";
declare
v_line varchar2(40);
begin
v_line := 'Hello '||'&vstring';
dbms_output.put_line(v_line);
end;
/
PL/SQL
-M. HAJJI-
14
Exemple
employe emp%ROWTYPE;
nom emp.nome%TYPE;
&
SELECT * INTO employe
FROM emp
WHERE matr = 900;
nom := employe.nome;
employe.dept := 20;
&
INSERT INTO emp
VALUES employe;
PL/SQL
-M. HAJJI-
15
Type RECORD
} Equivalent struct du langage C
TYPE nomRecord IS RECORD (
champ1 type1,
champ2 type2,
&);
} Exemple
TYPE emp2 IS RECORD (
matr integer,
nom varchar(30));
employe emp2;
employe.matr := 500;
PL/SQL
-M. HAJJI-
16
Affectation
} Plusieurs fa ons de donner une valeur une variable :
} n :=
} n par la directive INTO de la requ te SELECT
} Exemples :
dateNaissance := 10/10/2004;
SELECT nome INTO nom
FROM emp
WHERE matr = 509;
PL/SQL
-M. HAJJI-
17
Exercices
} Ecrire un programme PL/SQL qui permet dafficher les
donn es dun joueur dont le num ro est saisi par
lutilisateur.
} Ecrire un programme PL/SQL qui permet dafficher les
informations dun joueur, la prime et le sponsor pour un
num ro, un tournoi et une ann e saisis.
PL/SQL
-M. HAJJI-
18
Les structures de contr les
19
PL/SQL
-M. HAJJI-
Structures conditionnelles
IF condition THEN
instructions;
END IF;
IF condition THEN
instructions1;
ELSE
END IF;
instructions2;
IF condition1 THEN
instructions1;
ELSEIF condition2 THEN
instructions2;
ELSEIF &
&
ELSE
instructionsN;
END IF;
PL/SQL
-M. HAJJI-
20
Structure choix multiples
CASE expression
WHEN expr1 THEN instructions1;
WHEN expr2 THEN instructions2;
&
ELSE instructionsN;
END CASE;
PL/SQL
-M. HAJJI-
21
Structures r p titives
} Boucle Tant que
WHILE condition LOOP
instructions;
Publicité
END LOOP;
} Boucle g n rale
LOOP
instructions;
EXIT ;
instructions;
END LOOP;
} Boucle pour
FOR compteur IN inf..sup LOOP
FOR i IN 1..100 LOOP
instructions;
END LOOP;
somme := somme + i;
END LOOP;
PL/SQL
-M. HAJJI-
22
Interactions simples avec une Base
de donn es
23
PL/SQL
-M. HAJJI-
Interactions avec la base de donn es
} select expr1, expr2,& into var1, var2,& met des valeurs
de la BD dans une ou plusieurs variables
} Le select ne doit renvoyer quune seule ligne
} Avec Oracle il nest pas possible dinclure un select sans
into dans une proc dure ;
PL/SQL
-M. HAJJI-
24
Exemple
DECLARE
BEGIN
v_nom emp.nome%TYPE;
v_emp emp%ROWTYPE;
SELECT nome INTO v_nom
FROM emp
WHERE matr = 500;
SELECT * INTO v_emp
FROM emp
WHERE matr = 500;
PL/SQL
-M. HAJJI-
25
Modification de donn es
} Les requ tes SQL (insert, update, delete,&) peuvent
utiliser les variables PL/SQL
} Les commit et rollback doivent tre explicites ; aucun
nest effectu automatiquement la sortie dun bloc
} Plus de d tails pour linsertion de donn es:
PL/SQL
-M. HAJJI-
26
Exemple
DECLARE
v_emp emp%ROWTYPE;
v_nom emp.nome%TYPE;
BEGIN
v_nom := 'Dupond';
insert into emp (matr, nome)
values (600, v_nom);
v_emp.matr := 610;
v_emp.nome := 'Durand';
insert into emp (matr, nome)
values (v_emp.matr, v_emp.nome);
commit;
END;
PL/SQL
-M. HAJJI-
27
Exemple
DECLARE
v_emp emp%rowtype;
BEGIN
SELECT * INTO v_emp FROM emp
WHERE nome = 'LEROY';
v_emp.matr := v_emp.matr + 5;
v_emp.nome := 'Toto';
INSERT INTO emp
VALUES v_emp;
END;
PL/SQL
-M. HAJJI-
28
Extraction des donn es: Erreurs
} Si le select renvoie plus dune ligne, une exception
TOO_MANY_ROWS (ORA-01422) est lev e.
} Si le select ne renvoie aucune ligne, une exception
NO_DATA_FOUND (ORA-01403) est lev e.
PL/SQL
-M. HAJJI-
29
Les curseurs
30
PL/SQL
-M. HAJJI-
Fonctionnalit s
} Toutes les requ tes SQL sont associ es un curseur
} Ce curseur repr sente la zone m moire utilis e pour
parser et ex cuter la requ te
} Le curseur peut tre implicite (pas d clar par
lutilisateur) ou explicite
} Les curseurs explicites servent retourner plusieurs
lignes avec un select
PL/SQL
-M. HAJJI-
31
Attributs des curseurs
} Tous les curseurs ont des attributs que lutilisateur peut
utiliser
} %ROWCOUNT : nombre de lignes trait es par le curseur
} %FOUND : vrai si au moins une ligne a t trait e par la
requ te ou le dernier fetch
} %NOTFOUND : vrai si aucune ligne na t trait e par la
requ te ou le dernier fetch
} %ISOPEN : vrai si le curseur est ouvert (utile seulement pour
les curseurs explicites)
PL/SQL
-M. HAJJI-
32
Curseurs implicites
} Les curseurs implicites sont tous nomm s SQL
DECLARE
nb_lignes integer;
BEGIN
DELETE FROM emp
WHERE dept = 10;
nb_lignes := SQL%ROWCOUNT;
. . .
PL/SQL
-M. HAJJI-
33
Curseurs explicites
} Pour traiter les select qui renvoient plusieurs lignes
} Ils doivent tre d clar s
} Le code doit les utiliser explicitement avec les ordres
OPEN, FETCH et CLOSE
} Le plus souvent on les utilise dans une boucle dont on
sort quand lattribut NOTFOUND du curseur est vrai
PL/SQL
-M. HAJJI-
34
Curseurs explicites
DECLARE
CURSOR salaires IS
SELECT sal
FROM emp
WHERE dept = 10;
salaire numeric(8, 2);
total numeric(10, 2) := 0;
BEGIN
OPEN salaires;
LOOP
FETCH salaires INTO salaire;
EXIT WHEN salaires%NOTFOUND;
IF salaire IS NOT NULL THEN
total := total + salaire;
DBMS_OUTPUT.put_line(total);
END IF;
END LOOP;
CLOSE salaires; -- Ne pas oublier
DBMS_OUTPUT.put_line(total);
END;
PL/SQL
-M. HAJJI-
35
Curseurs explicites
} On peut d clarer un type row associ un curseur
DECLARE
CURSOR c IS
SELECT matr, nome, sal FROM emp;
employe c%ROWTYPE;
BEGIN
OPEN c;
FETCH c INTO employe;
IF employe.sal IS NULL THEN &
PL/SQL
-M. HAJJI-
36
Curseurs explicites
} Elle simplifie la programmation car elle vite dutiliser
explicitement les instruction OPEN, FETCH, CLOSE
} En plus elle d clare implicitement une variable de type
row associ e au curseur
DECLARE
CURSOR c IS
SELECT dept, nome FROM emp
WHERE dept = 10;
BEGIN
FOR employe IN c LOOP
dbms_output.put_line(employe.nome);
END LOOP;
END;
PL/SQL
-M. HAJJI-
37
Curseurs param tr s
} Un curseur param tr peut servir plusieurs fois avec des
valeurs des param tres diff rentes
} On doit fermer le curseur entre chaque utilisation de
param tres diff rents (sauf si on utilise FOR qui ferme
automatiquement le curseur )
DECLARE
CURSOR c(p_dept integer) IS
SELECT dept, nome FROM emp
WHERE dept = p_dept;
BEGIN
FOR employe in c(10) LOOP
dbms_output.put_line(employe.nome);
END LOOP;
FOR employe in c(20) LOOP
dbms_output.put_line(employe.nome);
END LOOP;
END;
PL/SQL
-M. HAJJI-
38
Ligne courante dun curseur
} La ligne courante dun curseur est d plac e chaque
appel de linstruction FETCH
} On est parfois amen modifier la ligne courante
pendant le parcours du curseur
} Pour cela on peut utiliser la clause WHERE CURRENT
OF pour d signer cette ligne courante dans un ordre
LMD (insert, update, delete)
} Il est n cessaire davoir d clar le curseur avec la clause
FOR UPDATE pour que le bloc compile
PL/SQL
-M. HAJJI-
39
La clause FOR UPDATE
} FOR UPDATE
} Cette clause bloque toute la ligne ou seulement les
colonnes sp cifi es
} Les autres transactions ne pourront modifier les valeurs
tant que le curseur naura pas quitt cette ligne
PL/SQL
-M. HAJJI-
40
La clause FOR UPDATE
} Exemple
DECLARE
CURSOR c IS
SELECT matr, nome, sal
FROM emp
Publicité
WHERE dept = 10
FOR UPDATE OF emp.sal;
&
IF salaire IS NOT NULL THEN
total := total + salaire;
ELSE -- met 0 la place de null
UPDATE emp SET sal = 0
WHERE CURRENT of c;
END IF;
PL/SQL
-M. HAJJI-
41
Les exceptions
42
PL/SQL
-M. HAJJI-
Introduction
} Une exception est une erreur qui survient durant une
ex cution
} 2 types dexception :
} pr d finie par Oracle
} d finie par le programmeur
} Structure dun bloc:
DECLARE
-- d finitions de variables
BEGIN
-- Les instructions ex cuter
EXCEPTION
-- La r cup ration des erreurs
END;
PL/SQL
-M. HAJJI-
43
Saisi des exceptions
} Une exception ne provoque pas n cessairement larr t du
programme si elle est saisie par un bloc (dans la partie
EXCEPTION )
} Une exception non saisie remonte dans la proc dure
appelante (o elle peut tre saisie)
} Exceptions pr d finies
} NO_DATA_FOUND
} TOO_MANY_ROWS
} VALUE_ERROR (erreur arithm tique)
} ZERO_DIVIDE
} &
PL/SQL
-M. HAJJI-
44
Traitement des exceptions
BEGIN
&
EXCEPTION
WHEN NO_DATA_FOUND THEN
. . .
WHEN TOO_MANY_ROWS THEN
. . .
WHEN OTHERS THEN -- optionnel
. . .
END;
PL/SQL
-M. HAJJI-
45
Exception utilisateur
} Elles doivent tre d clar es avec le type EXCEPTION
} On les l ve avec linstruction RAISE
DECLARE
salaire numeric(8,2);
salaire_trop_bas EXCEPTION;
BEGIN
SELECT sal INTO salaire FROM emp
WHERE matr = 50;
IF salaire < 300 THEN
raise salaire_trop_bas;
END IF;
-- suite du bloc
EXCEPTION
WHEN salaire_trop_bas THEN . . .;
WHEN OTHERS THEN
dbms_output.put_line(SQLERRM);
END;
PL/SQL
-M. HAJJI-
46
Exercices
} Ecrire un bloc PL/SQL qui permet dafficher les joueurs nais
apr s 1970.
} Ecrire un bloc PL/SQL qui permet dafficher toutes les
rencontres qui se sont d roul es Roland Garros en 1992.
Laffichage doit se faire sous la forme:
Gagnant VS Perdant
} Ecrire un bloc PL/SQL qui permet dafficher la moyenne des
gains pour chaque tournoi.
} Ecrire un bloc PL/SQL sponsorJoueurs.sql qui permet
dafficher les joueurs de chaque Sponsor.
} Ecrire un bloc PL/SQL qui permet dins rer dans une Table
primeTournoi le total des primes pour chaque tournoi et
chaque ann e.
PL/SQL
-M. HAJJI-
47
Exercices
} Ecrire un bloc PL/SQL qui permet dins rer dans une
table palmares le num ro, nom et pr nom dun
joueur, le nombre de match jou dans sa carri re, le
nombre de match gagn , le nombre de match perdu et le
total de ses gains.
} Ecrire un bloc PL/SQL qui permet de mettre jour les
informations nombre de match jou s, nombre de matchs
gagn s, nombre de matchs perdus et total des gains.
PL/SQL
-M. HAJJI-
48
Les proc dures et les fonctions
49
PL/SQL
-M. HAJJI-
Bloc anonyme ou nomm
} Un bloc anonyme PL/SQL est un bloc DECLARE
BEGIN END comme dans les exemples pr c dents
} Dans SQL*PLUS on peut ex cuter directement un bloc
PL/SQL anonyme en tapant sa d finition
} Le plus souvent, on cr e plut t une proc dure ou une
fonction nomm e pour r utiliser le code
PL/SQL
-M. HAJJI-
50
Cr ation dune proc dure
CREATE OR Replace
PROCEDURE(<liste params>) IS
-- d claration des variables
BEGIN
-- code de la proc dure
END;
} Pas de DECLARE ; les variables sont d clar es entre IS et
BEGIN
} Si la proc dure ne n cessite aucune d claration, le code
est pr c d de IS BEGIN
PL/SQL
-M. HAJJI-
51
Cr ation dune fonction
CREATE OR Replace
FUNCTION(<liste params>)
RETURN <type retour> IS
-- d claration des variables
BEGIN
-- code de la proc dure
END;
PL/SQL
-M. HAJJI-
52
Passage de param tres
} Dans la d finition dune proc dure on indique le type de
passage que lon veut pour les param tres :
} IN pour le passage par valeur
} IN OUT pour le passage par r f rence
} OUT pour le passage par r f rence mais pour un param tre
dont la valeur nest pas utilis e en entr e
} Pour les fonctions, seul le passage par valeur (IN) est
autoris
PL/SQL
-M. HAJJI-
53
Utilisation des proc dures et fonctions
} Sous SQL*PLUS, il faut taper une derni re ligne contenant
/ pour compiler une proc dure ou une fonction
} Les proc dures et fonctions peuvent tre utilis es dans
dautres proc dures ou fonctions ou dans des blocs
PL/SQL anonymes
} Les fonctions peuvent aussi tre utilis es dans les
requ tes SQL
} Exemple
CREATE OR REPLACE
FUNCTION euro_to_fr(somme IN number)
RETURN number IS
taux CONSTANT NUMBER := 6.55957;
BEGIN
return somme * taux;
END;
PL/SQL
-M. HAJJI-
54
Utilisation des proc dures et fonctions
} Dans un bloc anonyme
DECLARE
CURSOR c(p_dept integer) IS
SELECT dept, nome, sal FROM emp
WHERE dept = p_dept;
BEGIN
FOR employe IN c(10) LOOP
dbms_output.put_line(employe.nome
|| ' gagne '
|| euro_to_fr(employe.sal)
|| ' francs');
END LOOP;
END;
} Dans une requ te
SELECT nome, sal, euro_to_fr(sal)
FROM emp;
PL/SQL
-M. HAJJI-
55
Ex cution dune proc dure
} Sous SQL*PLUS on ex cute une proc dure PL/SQL avec
la commande EXECUTE :
EXECUTE nomProc dure(param1, &);
} Sous SQL*PLUS on ex cute une fonction PL/SQL avec la
commande select:
SELECT nomFonction(param1, &) FROM dual;
PL/SQL
-M. HAJJI-
56
Exercices
} Ecrire une fonction MaxPS qui permet de retourner le
maximum entre deux valeurs a et b .
} Ex cuter la fonction MaxPS directement sous SQL*PLUS,
en d finissant les param tres.
} Dans un programme principal, afficher pour un tournoi et
une ann e donn s, le Nom et Pr nom du joueur ayant eu
la plus haute prime, en faisant appel la fonction MaxPS.
} Dans un programme principal, afficher le Nom et Pr nom
du joueur ayant eu le cumul le plus important durant une
ann e donn e, en utilisant la fonction MaxPS.
PL/SQL
-M. HAJJI-
57
Exercices
} Ecrire une fonction RatePS qui permet de calculer le
pourcentage dune valeur par rapport une valeur
globale.
} Dans un programme principal, afficher pour un tournoi et
une ann e donn s, le Nom, Pr nom et le pourcentage de
la prime de chaque joueur par rapport la prime globale.
} Dans un programme principal, afficher le Nom et le
Pr nom du joueur, ainsi que le pourcentage de sa prime
par rapport au cumul de toutes les primes dans une
ann e donn e, pour le joueur ayant eu le plus important
pourcentage. On utilisera les fonctions RatePS et MaxPS.
} Ex cuter la fonction RatePS sous SQL*Plus.
PL/SQL
-M. HAJJI-
58
Exercices
} Ecrire une proc dure WinnersPS qui prend en param tre le
nom dun tournoi, et qui affiche la liste des vainqueurs de ce
tournoi (Nom et Pr nom) ainsi que lann e.
Publicité
} Ex cuter la proc dure WinnersPS partir de SQL*Plus.
} Dans un programme PL/SQL, faire appel la proc dure
WinnersPS pour afficher la liste des vainqueurs des tournois
qui se sont pass s dans un intervalle de dates donn .
} Ecrire une proc dure UpdatePalmPS qui permet de mettre
jour les champs nombre de match jou , nombre de match gagn ,
nombre de match perdu et total des gains de la table
palmares (TP pr c dent). Les valeurs sont pass es en
param tres.
PL/SQL
-M. HAJJI-
59
Les Triggers
60
PL/SQL
-M. HAJJI-
Introduction
} Un trigger est un programme qui se d clenche
automatiquement suite un v nement
A la diff rence dune proc dure stock e, on ne peut pas
appeler un trigger explicitement.
} En base de donn es, l v nement est une instruction du DML
qui modifie la base.(INSERT, DELETE, UPDATE)
} Ces triggers font partie du sch ma de la base. Leur code
compil est conserv (comme pour les programmes stock s)
} Les triggers peuvent servir v rifier des contraintes que lon
ne peut pas d finir de fa on d clarative
} Ils peuvent aussi g rer de la redondance dinformation.
} Ils peuvent aussi servir collecter des informations sur les
mises- -jour de la base
PL/SQL
-M. HAJJI-
61
Syntaxe
CREATE [ OR REPLACE ] TRIGGER <nom_trigger>
<instant> <liste_evts>
ON <nom_table> [ FOR EACH ROW ]
[ WHEN ( <condition> ) ]
<corps>
PL/SQL
-M. HAJJI-
62
Ent te du trigger
} On d finit
} La table
} Les instructions qui d clenchent le trigger
} Le moment o le trigger va se d clencher par rapport
linstruction (avant ou apr s)
} Si le trigger se d clenche
} une seule fois pour toute linstruction (i.e. trigger instruction ou
trigger de table)
} ou une fois pour chaque ligne modifi e/ins r e/supprim e (i.e. trigger
ligne, avec loption FOR EACH ROW)
} Et ventuellement une condition suppl mentaire de
d clenchement (clause WHEN) pour les triggers ligne.
PL/SQL
-M. HAJJI-
63
Conditions dun trigger
} Le trigger se d clenche lorsquun v nement pr cis
survient : BEFORE UPDATE, AFTER DELETE, AFTER
INSERT, &
} Ces v nements sont importants car ils d finissent le
moment dex cution du trigger.
} Ainsi, lorsquun trigger BEFORE INSERT est programm ,
il sera ex cut juste avant linsertion dun nouvel l ment
dans la table.
PL/SQL
-M. HAJJI-
64
Conditions dun trigger
} Si le trigger doit d terminer si linstruction est autoris e :
utiliser BEFORE
} Si le trigger doit fabriquer la valeur dune colonne pour
pouvoir ensuite la mettre dans la table : utiliser BEFORE.
} Par exemple : trigger ligne qui fabrique la valeur de clef
primaire partir dune s quence.
} Si on a besoin que linstruction soit termin e pour
ex cuter le corps du trigger : utiliser AFTER
} pour des triggers lignes, il se peut que lutilisation de
Before ou After nait aucune importance.
PL/SQL
-M. HAJJI-
65
Code du trigger
} Le trigger Oracle doit tre crit en PL/SQL.
} La forme dun trigger est encore tr s d pendante du SGBD utilis .
CREATE OR REPLACE TRIGGER Print_salary_changes
BEFORE UPDATE ON Emp_tab
FOR EACH ROW
WHEN (new.Empno > 0)
DECLARE sal_diff number;
BEGIN
sal_diff := :new.sal - :old.sal;
dbms_output.put(' Old : ' || :old.sal || 'New : ' || :new.sal || 'Difference : ' ||
sal_diff);
END ;
} Ce trigger est d clench lorsque la table Emp_tab est mise jour.
} Pour chaque modification (lignes mises jour), le trigger va calculer puis
afficher respectivement lancien salaire, le nouveau salaire et la diff rence
entre ces deux salaires.
} La condition pr cis e (WHEN new.Empno > 0) restreint le
d clenchement du trigger (ex cut uniquement si new.Empno > 0)
PL/SQL
-M. HAJJI-
66
Les types de triggers
} Il existe deux types de triggers diff rents : les triggers de
table (STATEMENT) et les triggers de ligne (ROW).
} Les triggers de table sont ex cut s une seule fois lorsque
des modifications surviennent sur une table (m me si ces
modifications concernent plusieurs lignes de la table).
} Ils sont utiles si des op rations de groupe doivent tre
r alis es (comme le calcul dune moyenne, dune somme
totale, dun compteur, &).
} Pour des raisons de performance, il est pr f rable
demployer ces triggers plut t que les triggers lignes.
PL/SQL
-M. HAJJI-
67
Les types de triggers
} Exemple
CREATE TRIGGER log
AFTER INSERT OR UPDATE ON Emp_tab
BEGIN
INSERT INTO log(table, date, username, action)
VALUES ('Emp_tab', sysdate, sys_context('USERENV',
'CURRENT_USER'), 'INSERT/UPDATE on Emp_tab') ;
END ;
} Ce trigger table enregistre dans une table log la trace de la
modification de la table Emp_tab.
} On m morise ici le moment de la modification et lutilisateur
qui la provoqu .
} Il nest donc ex cut quune seule fois par modification de la
table Emp_tab.
PL/SQL
-M. HAJJI-
68
Les types de triggers
} Les triggers lignes sont ex cut s s par ment pour chaque
ligne modifi e dans la table.
} Ils sont tr s utiles sil faut mesurer une volution pour
certaines valeurs, effectuer des op rations pour chaque ligne
en question.
} Lors de la cr ation de triggers lignes, il est possible davoir
acc s la valeur ancienne et la valeur nouvelle gr ce aux mots
cl s OLD et NEW.
} Il faut pr fixer ces qualificatifs de deux points (:) dans tout
ordre SQL ou PL/SQL les utilisant.
} Il ne faut pas utiliser de pr fixe deux points (:) quand les qualificatifs
sont utilis s dans la condition de restriction WHEN
} Il nest pas possible davoir acc s ces valeurs dans les triggers
de table.
PL/SQL
-M. HAJJI-
69
Les types de triggers
} Exemple
CREATE OR REPLACE TRIGGER totalSalaire
AFTER UPDATE OF salaire ON emp
REFERENCING OLD AS ancien,
NEW AS nouveau
FOR EACH ROW
update cumul
set augmentation = augmentation + nouveau.salaire - ancien.salaire
where matricule = ancien.matricule
PL/SQL
-M. HAJJI-
70
Les types de triggers
} :OLD et :NEW
CREATE OR REPLACE TRIGGER totalSalaire
AFTER UPDATE OF salaire ON emp
FOR EACH ROW
UPDATE cumul
SET augmentation = augmentation + :NEW.salaire - :OLD.salaire
WHERE matricule = :OLD.matricule
} La clause WHEN
CREATE OR REPLACE TRIGGER modifsalaire
BEFORE UPDATE OF sal ON emp
FOR EACH ROW
WHEN (new.sal < old.sal)
BEGIN
raise_application_error(-20001,'Interdit de baisser le salaire ! ('
|| :old.nome || ')');
END;
PL/SQL
-M. HAJJI-
71
Les types de trigger
} Trigger de table :
} Le trigger est ex cut une fois pour lensemble de linstruction
pas de notion de ligne courante.
} Trigger ligne :
} notion de ligne courante, :old d signe la ligne avant
modication et :new d signe la ligne apr s modication.
Insert
Delete
Update
PL/SQL
:old
null
:new
Valeur ins r e
Valeur supprim e
Null
Valeur avant modification
Valeur apr s modification
-M. HAJJI-
72
Ordre dex cution es triggers
} Pour une instruction du DML sur une table de la base, il
peut y avoir 4 sortes de triggers possibles selon linstant
(before, after) et le type (instruction ou ligne).
} Ces triggers se d clenchent dans lordre suivant :
} Trigger(s) instruction BEFORE
} Pour chaque ligne concern e
} Trigger(s) ligne BEFORE
} Trigger(s) ligne AFTER
} Trigger(s) instruction AFTER
PL/SQL
-M. HAJJI-
73
Activation / D sactivation des triggers
} Lorsquun trigger est cr , il est automatiquement activ
} D sactiver un trigger :
ALTER TRIGGER trigger_name DISABLE
} Activer un trigger :
ALTER TRIGGER trigger_name ENABLE
} Activer ou d sactiver tous les triggers dune table :
ALTER TABLE table_name DISABLE | ENABLE ALL
TRIGGERS
PL/SQL
-M. HAJJI-
74
Remarques
} Recompiler un trigger:
ALTER TRIGGER trigger_name COMPILE
} le trigger est recompil sans perdre sa validit (actif) ou son
invalidit (non actif).
} Afficher les erreurs de compilation :
SHOW ERRORS
} Supprimer un trigger :
DROP TRIGGER nomTrigger
} Ordres interdits
} Les ordres COMMIT et ROLLBACK sont interdits dans un
Publicité
trigger
PL/SQL
-M. HAJJI-
75
Remarques
} Lorsque lon veut appeler une proc dure depuis un
trigger on utilise la commande CALL :
CREATE TRIGGER TEST3
BEFORE INSERT ON EMP
CALL AFFICHER
/
} Nom des triggers et leurs propri taires
SELECT owner, object_name
FROM all_objects
WHERE object_type = TRIGGER'
ORDER BY owner, object_name;
PL/SQL
-M. HAJJI-
76
Exercices
} Ecrire un trigger qui permet de mettre jour la table
palmares chaque nouvelle rencontre jou e.
} Ecrire un trigger qui contr le les primes des joueurs. Si
un joueur participe un tournoi, il doit obligatoirement
avoir une prime. Dans le cas contraire, la prime doit tre
gale 10000 .
} Ecrire un trigger qui contr le le minimum de prime pour
les joueurs perdant dans les tournoi.
} Ecrire un trigger qui contr le le maximum de prime pour
les joueurs vainqueurs de tournois.
PL/SQL
-M. HAJJI-
77
Exercices
} Ecrire un trigger qui se d clenche avant l'insertion d'un tuple
dans la table Gain, et qui transforme la valeur de la prime
en euro si la date du tournoi est ant rieure 2001.
} Taux de conversion : 1 franc = 0,152
} Ecrire un trigger qui se d clenche avant linsertion dun
tuple dans la table gain, et qui v rifie si au moins une
rencontre a t enregistr e pour le joueur en question
dans le tournoi et lann e consid r .
PL/SQL
-M. HAJJI-
78
Les Packages
79
PL/SQL
-M. HAJJI-
Introduction
} Un package est similaire la notion de classe dans
lorient objet.
} Il regroupe des proc dures, des fonctions, des variables,
des constantes, des curseurs et des traitements
dexceptions qui ont un lien logique entre eux, sous une
seule entit .
} Les proc dures et les fonctions peuvent tre:
} Publiques: PUBLIC: cest dire appel es depuis lext rieur
du package.
} Priv es: PRIVATE: qui sont invisibles lext rieur et
accessibles uniquement des proc dures du m me
package.
PL/SQL
-M. HAJJI-
80
Organisation dun Package
} Un paquetage est organis en deux parties distinctes
} Une partie sp cification
} Permet de sp cifier la fois les fonctions et proc dures
publiques ainsi que les d clarations des types, variables,
constantes, exceptions et curseurs utilis s dans le paquetage et
visibles par le programme appelant.
} Une partie corps
} Contient les blocs et les sp cifications de tous les objets
publics list s dans la partie sp cification.
} Cette partie peut inclure des objets qui ne sont pas list s dans
la partie sp cification, et sont donc priv s.
} Cette partie peut galement contenir du code qui sera ex cut
chaque invocation du paquetage par l'utilisateur
PL/SQL
-M. HAJJI-
81
Organisation dun Package
} La d claration de la partie sp cification d'un paquetage
s'effectue avec l'instruction
CREATE PACKAGE
} Celle de la partie corps avec l'instruction
CREATE PACKAGE BODY
PL/SQL
-M. HAJJI-
82
Exemple: Sp cification dun package
CREATE OR REPLACE PACKAGE Pkg_Finance
IS
-- Variables globales et publiques
GN_Salaire EMP.sal%Type ;
-- Fonctions publiques
FUNCTION F_Test_Augmentation
(
PN_Numemp IN EMP.empno%Type
,PN_Pourcent IN NUMBER
) Return NUMBER ;
-- Proc dures publiques
PROCEDURE Test_Augmentation
(
PN$Numemp IN EMP.empno%Type -- num ro de l'employ
,PN$Pourcent IN OUT NUMBER -- pourcentage d'augmentation
) ;
End Pkg_Finance ;
/
PL/SQL
-M. HAJJI-
83
Exemple : Corps dun package
CREATE OR REPLACE PACKAGE BODY Pkg_Finance IS
-- Variables globales priv es
GR_Emp EMP%Rowtype ;
-- Proc dure priv es
PROCEDURE Affiche_Salaires
IS
CURSOR C_EMP IS select * from EMP ;
BEGIN
OPEN C_EMP ;
Loop
FETCH C_EMP Into GR_Emp ;
Exit when C_EMP%NOTFOUND ;
dbms_output.put_line( 'Employ ' || GR_Emp.ename || ' --> ' ||
Lpad( To_char( GR_Emp.sal ), 10 ) ) ;
End loop ;
CLOSE C_EMP ;
END Affiche_Salaires ;
PL/SQL
-M. HAJJI-
84
Exemple : Corps dun package
-- Fonctions publiques
FUNCTION F_Test_Augmentation (PN_Numemp IN EMP.empno%Type,
PN_Pourcent IN NUMBER 25 )
Return NUMBER
IS
LN_Salaire EMP.sal%Type ;
BEGIN
Select sal Into LN_Salaire From EMP Where empno = PN_Numemp ;
-- augmentation virtuelle de l'employ 31 LN_Salaire := LN_Salaire *
PN_Pourcent ;
-- Affectation de la variable globale publique
GN_Salaire := LN_Salaire ;
Return( LN_Salaire ) ; -- retour de la valeur
END F_Test_Augmentation;
PL/SQL
-M. HAJJI-
85
Exemple : Corps dun package
-- Proc dures publiques
PROCEDURE Test_Augmentation ( PN_Numemp IN EMP.empno%Type,
PN_Pourcent IN OUT NUMBER)
IS
LN_Salaire EMP.sal%Type ;
BEGIN
Select sal Into LN_Salaire From EMP Where empno = PN_Numemp ;
-- augmentation virtuelle de l'employ
PN_Pourcent := LN_Salaire * PN_Pourcent ;
-- appel proc dure priv e
Affiche_Salaires ;
END Test_Augmentation;
END Pkg_Finance;
/
PL/SQL
-M. HAJJI-
86
Organisation dun Package
} La sp cification du paquetage est cr e avec
} une variable globale et publique : GN_Salaire
} une proc dure publique : PROCEDURE Test_Augmentation
} une fonction publique : FUNCTION F_Test_Augmentation
qui sont visibles depuis l'ext rieur (le programme appelant)
} Le corps du paquetage est cr avec une proc dure priv e :
} PROCEDURE Affiche_Salaires qui n'est visible que dans le corps du
paquetage
} Le corps d finit galement une variable globale au corps du
paquetage :
} GR_Emp utilis e par la proc dure priv e
PL/SQL
-M. HAJJI-
87
Acc s/Suppression
} L'acc s un objet d'un paquetage est r alis avec la
syntaxe suivante :
nom_paquetage.nom_objet[(liste param tres)]
} L'ex cution d'une proc dure d'un package s'effectue par
la commande
EXECUTE nom_package.nom_proc dure(liste_param tres
effectifs) ;
} Suppression dun Package
} DROP PACKAGE package_name;
} DROP PACKAGE BODY package_name;
PL/SQL
-M. HAJJI-
88
Acc s
} Appel de la fonction F_Test_Augmentation du paquetage
Declare
Begin
End ;
/
LN_Salaire emp.sal%Type ;
Select sal Into LN_Salaire From EMP Where empno = 7369 ;
dbms_output.put_line( 'Salaire de 7369 avant augmentation
|| To_char( LN_Salaire ) ) ;
dbms_output.put_line( 'Salaire de 7369 apr s augmentation
|| To_char( Pkg_Finance.F_Test_Augmentation( 7369, 1.1 ) ) ) ;
PL/SQL
-M. HAJJI-
89
Acc s
} Appel de la proc dure Test_Augmentation du paquetage
Declare
Begin
LN_Pourcent NUMBER := 1.1 ;
Pkg_Finance.Test_Augmentation( 7369, LN_Pourcent ) ;
dbms_output.put_line( 'Employ 7369 apr s augmentation : ' || To_char(
LN_Pourcent ) ) ;
End ;
/
PL/SQL
-M. HAJJI-
90
Acc s
} Interrogation de la variable globale publique : GN_Salaire
Begin
End ;
/
dbms_output.put_line( 'Valeur salaire du package : '
|| To_char( Pkg_Finance.GN_Salaire ) ) ;
PL/SQL
-M. HAJJI-
91
Merci&
PL/SQL
-M. HAJJI-
92