Cours 4 : PL/SQL

Procedural Language/SQL
Blocs, variables, instructions, structures de
contrôle, curseurs, gestion des erreurs,
procédures/fonctions stockées, packages,
triggers
 PL/SQL
Chapitre 3 de la norme SQL3 sous le
nom SQL/PSM (Persistent Stored
Modules)
Langage procédural plus portable
Un script SQL Developeur peut contenir
des blocs de sous-programmes en
PL/SQL
Traitement de transactions
2018-2019 Bases de données 2
 PL/SQL (2)
Construction de procédures ou
fonctions stockées qui améliorent le
mode client-serveur par stockage des
procédures ou fonctions souvent
utilisées au niveau serveur
Gestion des erreurs (à la ADA)
Construction de triggers (ou
déclencheurs)
2018-2019 Bases de données 3
 Structure d’un bloc
Un programme ou une procédure
PL/SQL est un ensemble de un ou
plusieurs blocs. Chaque bloc comporte
trois sections :

1. Section déclaration
2. Section corps du bloc
3. Section traitement des erreurs
2018-2019 Bases de données 4
 1. Section déclaration

Contient la description des structures

et des variables utilisées dans le bloc

Section facultative

Commence par le mot clé DECLARE

2018-2019 Bases de données 5

 2. Section corps du bloc
Contient les instructions du
programme et éventuellement, à la fin,
la section de traitement des erreurs
Obligatoire
Introduite par le mot clé BEGIN
Se termine par le mot clé END

2018-2019 Bases de données 6

 3. Section traitement
des erreurs

Facultative

Introduite par le mot clé EXCEPTION

2018-2019 Bases de données 7

 Syntaxe
DECLARE
déclaration
BEGIN
corps-du-bloc
EXCEPTION
traitement-des-erreurs
END;
/ ← A ajouter obligatoirement
dans l’exécution d’un script
2018-2019 Bases de données 8
 Exemple
SET SERVEROUTPUT ON
DECLARE
x VARCHAR2(10);
BEGIN
x := 'Bonjour';
DBMS_OUTPUT.PUT_LINE(x);
END;
/
2018-2019 Bases de données 9
 Exemple (2)
DECLARE
erreurNb EXCEPTION;
nom [Link]%TYPE;
min [Link]%TYPE;
max [Link]%TYPE;
BEGIN
...

2018-2019 Bases de données 10

 Exemple (2 – suite)
...
BEGIN
SELECT nomVoyage, nbMin, nbMax
INTO nom, min, max
FROM Voyage WHERE numVoyage = 1;
IF max < min
THEN RAISE erreurNb; END IF;
DBMS_OUTPUT.PUT_LINE (nom || ' OK');
EXCEPTION
... 2018-2019 Bases de données 11
 Exemple (2 – suite et fin)
...
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE(
'numéro inconnu');
WHEN erreurNb THEN
DBMS_OUTPUT.PUT_LINE(
nom || ' NBMIN > NBMAX');
END;
/2018-2019 Bases de données 12
 Types de variables

Variables scalaires

Types composés
• Record
• Table

2018-2019 Bases de données 13

 Variables scalaires
Types issus de SQL : CHAR, NUMBER,
DATE, VARCHAR2

Types PL/SQL : BOOLEAN, SMALLINT,

BINARY_INTEGER, DECIMAL, FLOAT,
INTEGER, REAL, ROWID

Les variables hôtes sont préfixées par « : »

2018-2019 Bases de données 14
 Déclaration des variables
scalaires

nom-variable nom-du-type;

nom-variable [Link]-attribut%TYPE;

2018-2019 Bases de données 15

 Déclaration pour un
enregistrement (record)
Soit par référence à une structure de table ou
de curseur en utilisant ROWTYPE

nom-variable nom-table%ROWTYPE;

nom-variable nom-curseur%ROWTYPE;

2018-2019 Bases de données 16

 Déclaration pour un
enregistrement (record) (2)
Soit par énumération des rubriques qui la
composent. Cela se fait en deux étapes :
• Déclaration du type enregistrement
TYPE nom-du-type IS RECORD (
nom-attribut1 type-attribut1,
nom-attribut2 type-attribut2, ...);
• Déclarationdelavariabledetypeenregistrement
nom-variable nom-du-type;
2018-2019 Bases de données 17
 Exemple
DECLARE
TYPE recVoyage IS RECORD (
libelle [Link]%TYPE,
prixPropose NUMBER(8,2));
voyPerso recVoyage; ...
BEGIN
[Link] := 3601.43;
...
2018-2019 Bases de données 18
 Tables
Structure composée d’éléments d’un
même type scalaire
L’accès à un élément de la table
s’effectue grâce à un indice, ou clé
primaire
Cet index est déclaré de type
BINARY_INTEGER (valeurs entières
signées)
2018-2019 Bases de données 19
 Déclaration pour une table
Deux étapes :
• Déclarationdu type de l’élément de latable :
TYPE nom-du-type IS TABLE OF type-argument INDEX
BY BINARY_INTEGER;
• Déclaration de la variable de type table :
nom-variable nom-du-type;

2018-2019 Bases de données 20

 Exemple
DECLARE
TYPE tabNom IS TABLE OF VARCHAR2(20)
INDEX BY BINARY_INTEGER;
tableNom tabNom; ...
BEGIN
tableNom(3) := 'Dupont';
...
2018-2019 Bases de données 21
 Variables (scalaires ou
composées)
Valeur initiale :
nom-variable nom-du-type := valeur;
Constante :
nom-variable nom-du-type DEFAULT valeur;
ou
nom-variableCONSTANTnom-du-
type:=valeur;
2018-2019 Bases de données 22
 Variables (scalaires ou
composées) (2)

Visibilité : une variable est utilisable

dans le bloc où elle a été définie ainsi
que dans les blocs imbriqués dans le
bloc de définition, sauf si elle est
redéfinie dans un bloc interne

2018-2019 Bases de données 23

 Conversion de type
Explicite avec
TO_CHAR, TO_DATE, TO_NUMBER,
RAWTOHEX, HEXTORAW
Implicites, par conversion automatique

2018-2019 Bases de données 24

 Instructions
Affectations

Instructions du langage SQL : CLOSE,

COMMIT, DELETE, FETCH, INSERT,
LOCK, OPEN, ROLLBACK,
SAVEPOINT, SELECT, SET
TRANSACTION, UPDATE

2018-2019 Bases de données 25

 Instructions (2)

Instructions de contrôle itératif ou

répétitif

Instructions de gestion de curseurs

Instructions de gestion des erreurs

2018-2019 Bases de données 26

 Affectation

Opérateur d’affectation :=

Option INTO dans un ordre SELECT

Instruction FETCH avec un curseur

2018-2019 Bases de données 27

 Exemple
DECLARE
TYPE tabNom IS TABLE OF VARCHAR2(20)
INDEX BY BINARY_INTEGER;
tableNom tabNom;
i BINARY_INTEGER;
TYPE recVoyage IS RECORD (
libelle [Link]%TYPE,
prixPropose NUMBER(8,2));
voyPerso recVoyage;
convers NUMBER(8,6);
BEGIN
Bases de données 28
...
 Exemple (suite)
...
BEGIN
convers := 6.55957;
tableNom(5) := 'Dupont';
i := 10;
tableNom(i) := 'Dupond';
[Link] := 'Découverte du Japon';
[Link] := 1989.08;
END;

2018-2019 Bases de données 29

 Exemple (2)
DECLARE
v_nom [Link]%TYPE;
v_min [Link]%TYPE;
TYPE recVoyage IS RECORD (
r_nom [Link]%TYPE,
r_min [Link]%TYPE);
r_voyage recVoyage;
rr_Voyage Voyage%ROWTYPE;
BEGIN
... Bases de données 30
 Exemple (2 – suite et fin)
...
BEGIN
SELECT nomVoyage, nbMin
INTO v_nom, v_min
FROM Voyage WHERE numVoyage = 517;
SELECT nomVoyage, nbMin INTO r_Voyage
FROM Voyage WHERE numVoyage = 364;
SELECT * INTO rr_Voyage
FROM Voyage WHERE numVoyage = 618;
END;
2018-2019 Bases de données 31
 Structures de contrôle

Structure alternative

Structure répétitives

2018-2019 Bases de données 32

 Structures alternatives

IF condition THEN instructions;

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

2018-2019 Bases de données 33

 Structures répétitives

LOOP instructions; END LOOP;

LOOP instructions; ...
EXIT WHEN condition; ...
END LOOP;
LOOP ...
IF condition THEN EXIT; END IF;
... END LOOP;

2018-2019 Bases de données 34

 Structures répétitives (2)
FOR variable-indice IN [REVERSE]
val-début .. val-fin
LOOP instructions; END LOOP;
variable-indice est une variable locale (locale à la
boucle) non déclarée
val-début et val-fin sont des variables locales
déclarées et initialisées ou alors des constantes
le pas est -1 si REVERSE est présent, sinon il est
égal à +1

2018-2019 Bases de données 35

 Structures répétitives (3)

WHILE condition
LOOP
instructions;
END LOOP;

2018-2019 Bases de données 36

 Les curseurs
Il y a création d’un curseur dès qu’on
exécute une instruction SQL. C’est une zone
de travail de l’environnement utilisateur qui
contient les informations relatives à
l’instruction SQL :
• Le texte source de l’ordre SQL
• Le texte «compilé» de l’ordre SQL
• Un tampon pour une ligne du résultat
• Le statut (cursor status)
• Des informations de travail et de contrôle
2018-2019 Bases de données 37
 Curseurs implicites
Gérés automatiquement par le noyau
dans les cas suivants :
• Une instruction SELECT exécutée sous
SQL Developer
• Une instruction SELECT donnant une
seule ligne de résultat sous PL/SQL
• Les instructions UPDATE, INSERT et
DELETE
• …
2018-2019 Bases de données 38
 Curseurs explicites
Obligatoires pour un SELECT
susceptible de produire plusieurs
lignes résultat
Quatre étapes :
1) Déclaration du curseur
2) Ouverture du curseur
3) Traitement des lignes du résultat
4) Fermeture du curseur
2018-2019 Bases de données 39
 1) Déclaration du curseur
Association d’un nom de curseur à une
requête SELECT
Se fait dans la section DECLARE d’un
bloc PL/SQL
CURSOR nom-curseur IS requête;
Un curseur peut être paramétré :
CURSOR nom-curseur (nom-p1 type-p1
[:= val-défaut], ...) IS requête;
2018-2019 Bases de données 40
 Exemple
DECLARE
CURSOR C1 IS SELECT numVoyage
FROM Tarif WHERE prix > 1000;
CURSOR C2 (p NUMBER(8),q NUMBER(8)) IS
SELECT nomVoyage FROM Voyage
WHERE nbMin >= p
AND nbMax <= q;
BEGIN
...
2018-2019 Bases de données 41
 2) Ouverture d’un curseur
Alloue un espace mémoire au curseur
et positionne les éventuels verrous
OPEN nom-curseur;
ou
OPEN nom-curseur(liste-par-effectifs);
Pour les paramètres, association par
position ou par nom sous la forme
paramètre-formel => paramètre-réel
2018-2019 Bases de données 42
 Exemple

OPEN C1;

OPEN C2 (6, 20);

OPEN C2 (q => 20, p => 6);

2018-2019 Bases de données 43

 3) Traitement des lignes
Autant d’instructions FETCH que de
lignes résultats :
FETCH nom-curseur
INTO liste-variables;
ou
FETCH nom-curseur
INTO nom-enregistrement;
Au moins quatre formes possibles
2018-2019 Bases de données 44
 Première forme : exemple
DECLARE
CURSOR C3 IS SELECT numVoyage,
nomVoyage FROM Voyage;
v_num [Link]%TYPE;
v_nom [Link]%TYPE;
BEGIN
OPEN C3; LOOP
FETCH C3 INTO v_num, v_nom;
EXIT WHEN C3%NOTFOUND;
Traitement ;
END LOOP; CLOSE C3;
END; Bases de données 45
Première forme : exemple (bis)
DECLARE
CURSOR C3 IS SELECT numVoyage,
nomVoyage FROM Voyage;
v_num [Link]%TYPE;
v_nom [Link]%TYPE;
BEGIN
OPEN C3; LOOP
FETCH C3 INTO v_num, v_nom;
EXIT WHEN C3%NOTFOUND;
dbms_output.put_line(v_num|| ' ' ||v_nom) ;
END LOOP; CLOSE C3;
END;
2018-2019 Bases de données 46
 Deuxième forme : exemple
DECLARE
CURSOR C3 IS SELECT numVoyage,
nomVoyage FROM Voyage;
TYPE recVOY IS RECORD(
v_num [Link]%TYPE,
v_nom [Link]%TYPE);
r_voy recVOY;
BEGIN
OPEN C3; LOOP FETCH C3 INTO r_voy;
EXIT WHEN C3%NOTFOUND;
Traitement ; END LOOP; CLOSE C3;
END;
2018-2019 Bases de données 47
Deuxième forme : exemple (bis)
DECLARE
CURSOR C3 IS SELECT numVoyage,
nomVoyage FROM Voyage;
TYPE recVOY IS RECORD(
v_num [Link]%TYPE,
v_nom [Link]%TYPE);
r_voy recVOY;
BEGIN
OPEN C3; LOOP FETCH C3 INTO r_voy;
EXIT WHEN C3%NOTFOUND;
dbms_output.put_line(r_voy.v_num|| ' '
||r_voy.v_nom); END LOOP; CLOSE C3;
END; Bases de données 48
 Troisième forme : exemple
DECLARE
CURSOR C3 IS SELECT numVoyage,
nomVoyage FROM Voyage;
r_voy C3%ROWTYPE;
BEGIN
OPEN C3; LOOP
FETCH C3 INTO r_voy;
EXIT WHEN C3%NOTFOUND;
Traitement ;
END LOOP; CLOSE C3;
END;
2018-2019 Bases de données 49
Troisième forme : exemple (bis)
DECLARE
CURSOR C3 IS SELECT numVoyage,
nomVoyage FROM Voyage;
r_voy C3%ROWTYPE;
BEGIN
OPEN C3; LOOP
FETCH C3 INTO r_voy;
EXIT WHEN C3%NOTFOUND;
dbms_output.put_line(r_voy.numvoyage|| ' '
||r_voy.nomvoyage) ;
END LOOP; CLOSE C3;
END; Bases de données 50
 Quatrième forme : exemple
DECLARE
CURSOR C3 IS SELECT numVoyage,
nomVoyage FROM Voyage;
BEGIN
FOR rec IN C3 LOOP
Traitement ;
END LOOP;
END;

2018-2019 Bases de données 51

Quatrième forme : exemple (bis)
DECLARE
CURSOR C3 IS SELECT numVoyage,
nomVoyage FROM Voyage;
BEGIN
FOR rec IN C3 LOOP
dbms_output.put_line([Link]|| ' '
||[Link]) ;
END LOOP;
END;

2018-2019 Bases de données 52

 Statut d’un curseur

Attribut Valeur
%FOUND Vrai si exécution correcte de l’ordre SQL
%NOTFOUND Vrai si exécution incorrecte de l’ordre SQL
%ISOPEN Vrai si curseur ouvert
%ROWCOUNT Nombre de lignes traitées par l’ordre SQL,
évolue à chaque ligne traitée par un
FETCH (zéro au départ)

2018-2019 Bases de données 53

 Statut d’un curseur (2)

Curseur implicite Curseur explicite

SQL%FOUND nom-curseur%FOUND
SQL%NOTFOUND nom-curseur%NOTFOUND
SQL%ISOPEN nom-curseur%ISOPEN
SQL%ROWCOUNT nom-curseur%ROWCOUNT

2018-2019 Bases de données 54

 Modification des données
Se fait habituellement avec INSERT, UPDATE
ou DELETE
Possibilité d’utiliser la clause FOR UPDATE dans
la déclaration du curseur. Cela permet d’utiliser
la clause
CURRENT OF nom-curseur
dans la clause WHERE des instructions
UPDATE et DELETE. Cela permet de modifier la
ligne du curseur traitée par le dernier FETCH, et
donc
2018-2019
d’accélérer l’accès à cette
Bases de données
ligne 55
 Exemple
DECLARE
CURSOR C IS SELECT *
FROM Tarif FOR UPDATE OF prix;
aug [Link]%TYPE;
BEGIN
FOR rec IN C LOOP
IF [Link] < 1000 THEN aug := [Link]*0.2;
ELSIF [Link] < 2000 THEN aug := [Link]*0.1;
ELSE aug := 0; DBMS_OUTPUT.PUT_LINE('OK ' || [Link]
|| ' ' || [Link]); END IF;
UPDATE Tarif
SET prix = prix+aug WHERE CURRENT OF C;
END LOOP;
END; Bases de données 56
 Modification des données (2)
Dans le cas d’une clause FOR
UPDATE, la table est verrouillée en
mode row share (RS). Les lignes
concernées par le verrou sont les lignes
du SELECT de la définition du curseur
En général, un COMMIT à
l’emplacement de la flèche ferme le
curseur. Mais ça n’est pas vrai sous
Oracle en PL/SQL
2018-2019 Bases de données 57
 Gestion des erreurs
(erreurs standard)
Code d’erreur SQLCODE Erreur
100 NO_DATA_FOUND
-1 DUP_VAL_ON_INDEX
-6502 VALUE_ERROR
-1001 INVALID CURSOR
-1722 INVALID NUMBER
-6501 PROGRAM ERROR
-1017 LOGIN DENIED
-1422 TOO_MANY_ROWS
-1476 ZERO_DIVIDE
2018-2019 Bases de données 58
 Gestion des erreurs
(erreurs standard) (2)
La nature d’une erreur peut être connue
par appel au fonctions SQLCODE et
SQLERRM
SQLCODE renvoie le statut d’erreur de
la dernière instruction SQL exécutée (0 si
n’y a pas d’erreur)
SQLERRM renvoie le message d’erreur
correspondant à SQLCODE
2018-2019 Bases de données 59
 Erreurs utilisateur
DECLARE
nom-anomalie EXCEPTION;
BEGIN
...
IF ... THEN RAISE nom-anomalie;
...
EXCEPTION
WHEN nom-anomalie THEN traitement;
END; Bases de données 60
 Erreurs anonymes

Pour les codes d’erreur n’ayant pas de

nom associé, il est possible de définir un
nom d’erreur (code entre -20000 et
-20999)

2018-2019 Bases de données 61

 Exemple
DECLARE
e EXCEPTION;
PRAGMA EXCEPTION_INIT(e, -20091);
...
BEGIN
...
IF ... THEN RAISE e;
EXCEPTION
WHEN e THEN ...
END; Bases de données 62
 Exemple (2)
DECLARE
e exception;
BEGIN
...
IF ... THEN RAISE e; END IF;
...
EXCEPTION
WHEN e THEN RAISE_APPLICATION_ERROR(
-20099, 'nom inéxistant');
END;
2018-2019 Bases de données 63
 Exemple (2 bis)
DECLARE
...
BEGIN
...
IF ... THEN RAISE_APPLICATION_ERROR(
-20099, 'nom inéxistant');
END IF;
END;

2018-2019 Bases de données 64

 Description du traitement
de l’erreur (syntaxe)
BEGIN
...
EXCEPTION
WHEN nom-erreur1 THEN traitement-erreur1;
...
WHEN nom-erreurn THEN traitement-erreurn;
WHEN OTHERS THEN traitement-autres-erreurs;
END;
2018-2019 Bases de données 65
 Description du traitement
de l’erreur (syntaxe) (2)

Possibilité d’écrire :

WHEN nom-erreur1 OR nom-erreur2

THEN ... ;

2018-2019 Bases de données 66

 Exemple
...
EXCEPTION
WHEN NO_DATA_FOUND THEN ...;
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(
'SQLCODE = '||TO_CHAR(SQLCODE));
DBMS_OUTPUT.PUT_LINE(
'SQLERRM : '||TO_CHAR(SQLERRM));
END;
Bases de données 67
 Procédures stockées
CREATE [OR REPLACE] PROCEDURE nom-procédure
[(argument [mode] type, ...)]
[IS | AS]
bloc-procédure;
argument : nom d’un paramètre formel
mode : définit si le paramètre formel est en
entrée (IN), en sortie (OUT) ou en entrée-
sortie (IN OUT). Par défaut : IN
type : le type du paramètre formel
bloc-procédure : le corps PL/SQL de la
procédure Bases de données 68
 Exemple
CREATE OR REPLACE PROCEDURE
modifierPrix (num IN [Link]%TYPE) IS
BEGIN
BEGIN
UPDATE Tarif SET prix = prix * .50
WHERE numVoyage = num AND dateDeb < '31-12-19';
END;
BEGIN
UPDATE Tarif SET prix = prix * .60
WHERE numVoyage = num AND dateDeb > '31-12-20';
END;
END modifierPrix;
2018-2019 Bases de données 69
 Fonctions stockées
CREATE [OR REPLACE] FUNCTION nom-fonction
[(argument [IN] type, ...)]
RETURN type-retour
[IS | AS]
bloc-fonction;
Les paramètres sont forcément en entrée
(IN)
Dans le bloc-fonction :
RETURN nom-variable;
2018-2019 Bases de données 70
 Exemple
CREATE OR REPLACE FUNCTION
moyennePrix (dd IN DATE ) RETURN NUMBER IS
moy [Link]%TYPE := 0;
e EXCEPTION;
BEGIN
SELECT AVG(prix) INTO moy FROM Tarif
WHERE dateDeb = dd;
IF moy IS NULL THEN RAISE e; END IF;
RETURN moy;
EXCEPTION
WHEN e THEN RETURN 0;
END moyennePrix; Bases de données 71
 Informations à propos des
procédures/fonctions
Erreurs
• USER_ERRORS
• ALL_ERRORS
• DBA_ERRORS
Infos sur les procédures/fonctions :
• USER_OBJECTS
• ALL_OBJECTS
• DBA_OBJECTS
Infos sur les textes source :
• USER_SOURCE
• ALL_SOURCE
• DBA_SOURCE
Bases de données 72
 Supression d’une
procédure/fonction stockée

DROP PROCEDURE nom-procedure;

DROP FUNCTION nom-fonction;

2018-2019 Bases de données 73

 Appel d’une
procédure/fonction stockée
dans un bloc PL/SQL

nom-procédure (liste-paramètres-effectifs);

nom-variable := nom-fonction(liste-paramètres-
effectifs);

2018-2019 Bases de données 74

 Appel d’une
procédure/fonction stockée
dans un script SQL Developer
EXECUTE nom-procédure (liste-paramètres-
effectifs);

EXECUTE :nom-variable := nom-fonction (liste-

paramètres-effectifs);

EXECUTE DBMS_OUTPUT.PUT_LINE(nom-
fonction (liste-paramètres-effectifs));
2018-2019 Bases de données 75
 Exemple
1. Création procédure
CREATE OR REPLACE PROCEDURE
nvVoy(num [Link]%TYPE,
nom [Link]%TYPE)
IS
BEGIN
INSERT INTO Voyage (numVoyage,
nomVoyage) VALUES (num, nom);
END;
2018-2019 Bases de données 76
 Exemple
2. Exécution procédure

EXECUTE
nvVoy (400, 'Voyage en Irlande');

2018-2019 Bases de données 77

 Exemple
2bis. Exécution procédure
EXECUTE nvVoy (&num, &nom);

Enter Substitution Variable

NUM: 400

Enter Substitution Variable

NOM: 'Voyage en Irlande'
2018-2019 Bases de données 78
 Exemple
2ter. Exécution procédure
PROMPT Entrez les infos sur un
nouvel voyage
ACCEPT num PROMPT Numéro
ACCEPT nom PROMPT Nom
Numéro : 400
Nom : 'Voyage en Irlande'
EXECUTE nvVoy (&num, &nom);
2018-2019 Bases de données 79
 Exemple
1. Création fonction
CREATE OR REPLACE FUNCTION
moyenne(nom [Link]%TYPE)
RETURN NUMBER IS moy [Link]%TYPE;
BEGIN
SELECT AVG(prix) INTO moy FROM Tarif
WHERE numVoyage IN (SELECT numVoyage
FROM Voyage WHERE nomVoyage = nom);
RETURN moy;
END; Bases de données 80
 Exemple
2. Exécution fonction

EXECUTE DBMS_OUTPUT.PUT_LINE (
moyenne('Circuit Heleni'));

2018-2019 Bases de données 81

 Exemple
2bis. Exécution fonction

EXECUTE DBMS_OUTPUT.PUT_LINE (
moyenne(&nom));

Enter Substitution Variable

NOM: 'Circuit Heleni'

2018-2019 Bases de données 82

 Exemple
2ter. Exécution fonction
ACCEPT nom PROMPT Nom
Nom : 'Circuit Heleni'

EXECUTE DBMS_OUTPUT.PUT_LINE (
moyenne(&nom));

2018-2019 Bases de données 83

 Exemple
2quater. Exécution fonction

VARIABLE moy NUMBER

EXECUTE :moy := moyenne('Circuit Heleni');
PRINT moy

2018-2019 Bases de données 84

 Gestion des erreurs
Erreur détectée par le SGBD
Erreur générée par l’utilisateur

Chaque catégorie peut être prise en

compte dans la section EXCEPTION
ou par l’environnement appelant

2018-2019 Bases de données 85

 Gestion par une
section EXCEPTION
Dans ce cas, l’exécution de la procédure ou
de la fonction est toujours considérée
comme réussie par l’environnement
appelant. En général, on stocke les
messages d’erreurs dans une table
spécifique accessible à l’environnement
Exemple : en cas de suppression d’un
voyage, vérifier qu’il n’a pas de dates de
départ prévues

2018-2019 Bases de données 86

 Exemple
CREATE PROCEDURE delVoyage(
num IN [Link]%TYPE) IS
filler CHAR(1); erreur EXCEPTION;
BEGIN
SELECT 'x' INTO filler FROM Tarif
WHERE numVoyage = num; RAISE erreur;
EXCEPTION
WHEN NO_DATA_FOUND THEN DELETE FROM Voyage
WHERE numVoyage = num; COMMIT;
WHEN erreur OR TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE ('Le voyage numéro : '||num||' a
déjà des dates de départ prevues');COMMIT;
END delVoyage;
Bases de données 87
 Gestion des erreurs
par l’environnement
Erreur émise par le SGBD : le code d’erreur,
sous la forme ORA_xxxxx, et le message
associé sont transmis au bloc appelant
Erreur générée par l’utilisateur : utilisation de
la procédure standard
RAISE_APPLICATION_ERROR(numéro,
texte);
• Le numéro doit être compris entre -20000 et -
20999
• L’exécution de RAISE_APPLICATION_ERROR
annule la transaction en cours 88
 Exemple (on ne tient pas
compte des CIR)
CREATE PROCEDURE delVoy (
num IN [Link]%TYPE) IS
v_nom [Link]%TYPE;
BEGIN
SELECT nomVoyage INTO v_nom FROM Voyage
WHERE numVoyage = num;
DELETE FROM Voyage WHERE numVoyage = num;
DBMS_OUTPUT.PUT_LINE('Voyage '||v_nom||' supprimé');
EXCEPTION
WHEN NO_DATA_FOUND THEN
RAISE_APPLICATION_ERROR (-20002,'Le voyage '||num||'
n''existe pas');
END delVoy; 89
 Packages
Un package contient deux types de
procédures ou fonctions :
• Publiques
• Privées

2018-2019 Bases de données 90

 Packages (2)
Deux parties distinctes dans un
package (chaque partie doit être créée
et compilée séparément) :
• La partie déclaration ou spécification qui
contient la déclaration des procédures,
fonctions, variables et traitement
d’exceptions de type public (accessibles de
l’extérieur du package)
• La partie corps, ou body, qui contient la
définition des procédures ou fonctions de
type public déclarées dans la partie
spécification ainsi que les déclarations de
procédures ou fonctions de type privé 91
 Partie déclaration
CREATE [OR REPLACE] PACKAGE
nom-package [IS | AS]
[déclaration-de-variable;]
[déclaration-de-curseur;]
[déclaration-de-procédure;]
[déclaration-de-fonction;]
[déclaration-d’exception;]
END nom-package;
2018-2019 Bases de données 92
 Exemple
Etape Voyage
Ville numVoyage numVoyage
numVille numVille nomVoyage
nomVille numOrdre nbMin
numPays duree nbMax
Pays typePension
Tarif
numPays
numVoyage
nomPays
dateDeb
formalite
prix
vaccin

2018-2019 Bases de données 93

 Déclaration : exemple
CREATE OR REPLACE PACKAGE gestionVoy
IS
dateMax CONSTANT DATE := SYSDATE + 30;
FUNCTION lePays (maVille
[Link]%TYPE) RETURN
[Link]%TYPE;
PROCEDURE etablirAgenda(monVoyage
[Link]%TYPE);
END gestionVoy;
2018-2019 Bases de données 94
 Partie corps
CREATE [OR REPLACE] PACKAGE BODY
nom-package [IS | AS]
[définition-de-variable;]
[définition-de-curseur;]
[définition-de-procédure;]
[définition-de-fonction;]
[définition-d’exception;]
END nom-package;
2018-2019 Bases de données 95
 Corps : exemple
CREATE OR REPLACE PACKAGE BODY gestionVoy IS
FUNCTION lePays (maVille [Link]%TYPE)
RETURN [Link]%TYPE IS P [Link]%TYPE;
BEGIN SELECT nomPays INTO P FROM Pays WHERE
numPays = (SELECT numPays FROM Ville WHERE
nomVille = maVille); RETURN P; END lePays;
PROCEDURE etablirAgenda(monVoyage
[Link]%TYPE) IS
CURSOR voyageCUR IS SELECT * FROM Tarif WHERE
numVoyage = monVoyage; BEGIN FOR r IN voyageCUR
LOOP IF [Link] <= dateMax
THEN DBMS_OUTPUT.PUT_LINE ([Link]||[Link]);
END IF; END LOOP; END etablirAgenda;
END gestionVoy;
Bases de données 96
 Référence à un élément
d’un package
En PL/SQL :
[Link]-élément;
En mode interactif :
EXECUTE [Link]-variable := ...
EXECUTE [Link]-procedure
(paramètres-effectifs)
EXECUTE :nom-variable := [Link]-
function (paramètres-effectifs);

2018-2019 Bases de données 97

 Suppression d’un package

Pour la totalité du package :

DROP PACKAGE nom-package;

Pour seulement le corps :

DROP PACKAGE BODY nom-package;

2018-2019 Bases de données 98

Surcharge d’une procédure ou
d’une fonction
A l’intérieur d’un package, il est possible
de surcharger une procédure ou une
fonction, c’est-à-dire de définir plusieurs
procédures ou fonctions avec le même
nom mais avec une liste de paramètres
différente

2018-2019 Bases de données 99

 Exemple

On peut prévoir deux fonctions de

même nom qui calculent, pour un
réalisateur passé en paramètre, le
nombre de films mis en scène par cette
personne. Une fonction aura le nom
comme paramètre, c’est-à-dire une
chaîne de caractères, l’autre le numéro
d’individu, c’est-à-dire un NUMBER

2018-2019 Bases de données 100

 Packages (3)
Lorsqu’un package est utilisé par plusieurs
sessions, chaque session utilise sa propre
copie des variables et des curseurs.
Un utilisateur doit posséder le privilège
CREATE PROCEDURE pour créer un
package qui utilise ses propres objets
Un utilisateur doit posséder le privilège
CREATE ANY PROCEDURE pour créer un
package qui utilise n’importe quels objets
2018-2019 Bases de données 101
 Les déclencheurs (triggers)
Un traitement déclenché par un
événement
L’exécution d’un déclencheur est un
succès ou un échec
En cas d’échec, l’exécution du
traitement est stoppée, mais la
transaction qui l’a appelé peut soit
continuer soit être annulée

2018-2019 Bases de données 102

 12 types de déclencheurs
3 événements
• INSERT
• UPDATE
• DELETE
2 modes
• Ordre
• Ligne (FOR EACH ROW)
2 moments
• BEFORE
• AFTER
2018-2019 Bases de données 103
 Ordre d’exécution
Il est possible d’associer un et un seul
déclencheur de chaque type à chaque
table. Lorsque plusieurs déclencheurs
sont associés à la même table, l’ordre
d’exécution est le suivant :
1. Déclencheur par ordre BEFORE
2. Pour chaque ligne (FOR EACH ROW)
• Déclencheur par ligne BEFORE
• Déclencheur par ligne AFTER
3. Déclencheur par ordre AFTER
2018-2019 Bases de données 104
 Déclencheur par ordre

CREATE [OR REPLACE] TRIGGER

nom-déclencheur
moment
événement [OR événement]
ON nom-table
bloc-PL/SQL;

2018-2019 Bases de données 105

 Exemple
CREATE OR REPLACE TRIGGER
ajoutVoyage
BEFORE
INSERT ON Voyage
BEGIN
IF USER != 'FFIOREN' THEN
RAISE_APPLICATION_ERROR (-20001,
'Utilisateur interdit'); END IF;
END ajoutVoyage;
2018-2019 Bases de données 106
 Déclencheur par ordre (2)

Pour l’événement UPDATE, on peut

spécifier les attributs concernés en
mettant
UPDATE OF nom-attribut1, ...

2018-2019 Bases de données 107

 Exemple
CREATE OR REPLACE TRIGGER
updateVoyage
BEFORE
UPDATE OF nomVoyage ON Voyage
BEGIN
IF USER != 'FFIOREN' THEN
RAISE_APPLICATION_ERROR (-20001,
'Utilisateur interdit'); END IF;
END updateVoyage;
2018-2019 Bases de données 108
 Déclencheur par ordre
BEFORE
Un déclencheur par ordre avec l’option
BEFORE peut servir à soumettre
l’exécution d’un traitement de mise à
jour d’une table à certaines conditions,
avec émission d’un message d’erreur si
les conditions ne sont pas vérifiées

2018-2019 Bases de données 109

Déclencheur par ordre AFTER

Un déclencheur par ordre avec l’option

AFTER peut servir à faire des
validations a posteriori afin de vérifier
que les modifications se sont bien
déroulées. Il peut aussi permettre de
propager des modifications dans
plusieurs tables

2018-2019 Bases de données 110

 Déclencheur par ligne
CREATE [OR REPLACE] TRIGGER
nom-déclencheur
moment
événement [OR événement]
ON nom-table
FOR EACH ROW
[WHERE condition]
bloc-PL/SQL;
2018-2019 Bases de données 111
 Déclencheur par ligne (2)
On peut faire référence, dans la
condition WHERE ou dans le bloc
PL/SQL associé au déclencheur
à la valeur d’un attribut avant
modification en préfixant le nom de
l’attribut par :OLD,
et/ou à la valeur après modification en
préfixant le nom de l’attribut par :NEW
2018-2019 Bases de données 112
 Déclencheur par ligne (3)
La valeur prise en compte dépend de
l’ordre SQL :
Ordre SQL :OLD :NEW
INSERT NULL Valeur créée
Valeur avant
DELETE NULL
suppression
Valeur avant Valeur après
UPDATE
modification modification
2018-2019 Bases de données 113
 Exemple
CREATE OR REPLACE TRIGGER auditVoy
AFTER DELETE OR UPDATE OR INSERT
ON Voyage FOR EACH ROW On suppose ici qu’il
BEGIN existe une table
IF DELETING OR UPDATING THEN reportVoyage qui a
INSERT INTO reportVoyage la meme structure
de la table Voyage
VALUES(SYSDATE, :[Link],
:[Link], :[Link], :[Link],
:[Link]);
END IF;
IF INSERTING THEN NULL; END IF;
END; Bases de données 114
 Déclencheur par ligne (4)
La clause WHERE ne peut pas contenir
de requête SQL
Un déclencheur par ligne avec l’option
BEFORE peut servir à effectuer des
traitements d’initialisation avant
l’exécution des modifications sur la table
Un déclencheur par ligne avec l’option
AFTER permet de propager les
modifications ou de gérer l’historique
2018-2019 Bases de données 115
 Tables système

USER_TRIGGERS

ALL_TRIGGERS

DBA_TRIGGERS

2018-2019 Bases de données 116

 Suppression

DROP TRIGGER nom-déclencheur;

2018-2019 Bases de données 117

 Activation/Désactivation
ALTER TRIGGER nom-déclencheur
DISABLE;
ALTER TABLE nom-table DISABLE ALL
TRIGGERS;
ALTER TRIGGER nom-déclencheur
ENABLE;
ALTER TABLE nom-table ENABLE ALL
TRIGGERS;
2018-2019 Bases de données 118
 Restrictions
L’exécution d’un déclencheur dont le bloc
PL/SQL inclut des ordres INSERT, DELETE
ou UPDATE peut entraîner la mise en œuvre
d’un autre déclencheur associé à la table
modifiée par ces actions
Dans ce cas, lors de l’exécution d’un
déclencheur de type ligne :
• Aucun ordre SQL ne doit consulter ou
modifier une table déjà utilisée en mode
modification par un autre utilisateur
2018-2019 Bases de données 119
 Restrictions (suite)
• Un déclencheur ne peut modifier la valeur
d’un attribut déclaré avec l’une des
contraintes PRIMARY KEY, UNIQUE ou
FOREIGN KEY
• Un déclencheur ne peut pas consulter les
données d’une table en mutation : une
table en mutation est une table directement
ou indirectement concernée par
l’événement qui a provoqué la mise en
œuvre du déclencheur

2018-2019 Bases de données 120

 Exemple
CREATE OR REPLACE TRIGGER verifPrix
AFTER UPDATE OF prix OR INSERT ON Tarif
FOR EACH ROW
DECLARE
v_min [Link]%TYPE;
v_max [Link]%TYPE;
BEGIN
SELECT MIN(prix), MAX(prix)
INTO v_min, v_max
FROM Tarif;
IF :[Link] > v_max OR :[Link] < v_min
THEN RAISE_APPLICATION_ERROR (-20002, 'Le prix
'|| TO_CHAR(:[Link])||' est hors limites'); END
IF;
END verifPrix;
Bases de données 121
 Exemple (suite)
Exécution :
INSERT INTO Tarif VALUES (6, SYSDATE, 1000)
Error starting at line 1 in command:
INSERT INTO Tarif VALUES (6, SYSDATE, 1000)
Error report:
SQL Error: ORA-04091: la table [Link] est
en mutation ; le déclencheur ou la fonction ne peut la
voir

2018-2019 Bases de données 122

 Erreurs de compilation
(warnings)

Sous SQL Developer, pour afficher les

erreurs de compilation :
• Se positionner sur l’objet crée avec des
erreurs
• Avec la touche droite de la souris,
sélectionner « Compile for Debug »

2018-2019 Bases de données 123