ORACLE Langage PL/SQL ORACLE Langage PL/SQL

SOMMAIRE PROGRAMMATION EN PL/SQL

1. Notions générales ............................................................................................................... 2
2. Le Bloc PL/SQL................................................................................................................. 2 1. Notions générales
2.1 Structure d’un bloc PL/SQL....................................................................................... 2
2.2 Exemple...................................................................................................................... 3 SQL est un langage ensembliste et non procédural. PL/SQL est un langage
3. Les variables....................................................................................................................... 3 procédural, qui intègre des ordres SQL de gestion de la base de données.
3.1 Types de variables utilisées en PL/SQL.................................................................... 4
3.2 Déclaration de variables et constante ......................................................................... 4
3.3 Initialisation et visibilité des variables ....................................................................... 6 Ces instructions SQL intégrées dans PL/SQL sont :
4. Traitements conditionnels .................................................................................................. 8
4.1 Définitions et syntaxe................................................................................................. 8 • partie LID : SELECT
4.2 Exemple...................................................................................................................... 9 • partie LMD :INSERT, UPDATE, DELETE,
5. Traitements répétitifs.......................................................................................................... 9 • partie gestion des transactions : Commit, ROLLBACK
5.1 Définition et types : .................................................................................................. 10
5.2 La boucle de base ..................................................................................................... 10 • fonctions : to_char, to_date, UPPER, SUBTR, ROUND, …
5.3 La boucle FOR ......................................................................................................... 10
5.4 La boucle WHILE .................................................................................................... 11 Les Instructions spécifiques à PL/SQL concernent :
6. Les curseurs en PL/SQL................................................................................................... 12
6.1 Définition et types .................................................................................................... 12 définition de variables
6.2 Etapes d’utilisation d’un curseur explicite ............................................................... 12 traitements conditionnels
6.3 Les attributs d’un curseur ......................................................................................... 14
6.4 Exemple.................................................................................................................... 17 traitements répétitifs
6.5 Simplification d’écriture avec les curseurs .............................................................. 17 traitements des curseurs
6.6 Curseur paramétré .................................................................................................... 19 traitements des erreurs
6.7 La clause CURRENT OF ......................................................................................... 20
7. Gestion des erreurs ........................................................................................................... 21 2. Le Bloc PL/SQL
7.1 Section exception ..................................................................................................... 21
7.2 Anomalie programme utilisateur.............................................................................. 22
7.3 Erreur oracle ............................................................................................................. 23
PL/SQL n’interprète pas une commande, mais un ensemble de commandes
8. EXERCICE ...................................................................................................................... 24 contenu dans un bloc PL/SQL

2.1 Structure d’un bloc PL/SQL

Un bloc est composé de trois sections :

DECLARE
Déclaration de variables, constantes, exceptions, curseurs
BEGIN [<<nom-bloc>>]
Instructions SQLet PL/SQL
EXCEPTION
traitements des exceptions (gestion des erreurs)
END ; ou [END nom-bloc]

Remarques :
• Les sections DECLARE et EXCEPTION sont facultatives.

Professeur : ASSALE Adjé Louis 1/24 INP-HB Professeur : ASSALE Adjé Louis 2/24 INP-HB
ORACLE Langage PL/SQL ORACLE Langage PL/SQL

• Chaque instruction de n’importe quelle section est terminée par un point 3.1 Types de variables utilisées en PL/SQL
virgule (;)
• Dans la section BEGIN, possibilité d’inclure des sous-blocs(imbrication Variables locales :
de blocs) • type scalaire,
• Possibilité de placer des commentaires: • type composé
-- commentaires sur une ligne ou
/* commentaires sur plusieurs lignes */ Variables de l’environnement extérieur à PL/SQL :

Un bloc PL/SQL s’édite généralement dans un fichier de commandes. C’est un • champs d’écran ou SQL*FORMS (préfixé de ″ :″) ;
fichier texte, dont l’exécution dans SQL*PLUS s’effectue par : • variables définies en langage hôte dans PRO*(les variables sont toujours
Start chemin\nom_fichier précédées de ":" ;
On peut inclure dans ce fichier, les commandes de SQL*PLUS suivantes : • variables définies dans SQL * plus par Accepte (préfixé de "&") par
Prompt message qui permet d’afficher un message Variable (préfixé de ″ :″).
Accept variable qui permet de rentrer une information (variable de
SQL*PLUS)
3.2 Déclaration de variables et constante
2.2 Exemple
Les variables locales se définissent dans la partie DECLARE d’un bloc PL/SQL.
Prompt nom de produit désiré - Variables PL/SQL de type scalaire :
Accept nom_ prod outre les types CHAR, NUMBER, DATE et VARCHAR2 disponible dans le
DECLARE langage SQL, PL/SQL offre les types supplémentaires suivants : BOOLEAN,
qte_stock Number(5); SMALLINT, BINARY_INTEGER, DECIMAL, FLOAT, INTEGER, REAL,
BEGIN ROWID.
SELECT quantite into qte_stock Syntaxe :
FROM stock nom_var CHAR ; --longeur maxi 255
WHERE produit= '&nom_prod'; nom_var NUMBER ; -- longueur maxi 38
-- contrôler et mettre à jour ce stock nom_var DATE ; --(DD-MON-YY)
IF qte_stock>o
THEN UPDATE stock Exemples
SET quantite = quantite - 1 DECLARE
WHERE produit= '&nom_prod'; nom char(15) ;
INSERT into Vente numero number ;
VALUES ('&nom_prod'||'vendu', sysdate) ; date_ jour date ;
ELSE salaire number (7,2) ;
INSERT into commande BEGIN
VALUES ('&nom_prod'||'Demande', sysdate) ; …
END IF; END ;
COMMIT;
END;
DECLARE
3. Les variables reponse Boolean ;
BEGIN

Professeur : ASSALE Adjé Louis 3/24 INP-HB Professeur : ASSALE Adjé Louis 4/24 INP-HB
ORACLE Langage PL/SQL ORACLE Langage PL/SQL

… <nom_champ> <type_champ>, …)
END ; 2. Déclaration de la variable de type enregistrement
nom_variable nom_type ;
Il est possible de déclarer une variable par référence à une colonne d’une table
par la notation %type, selon la syntaxe : Emeple :
nom _variable nom_table.nom_colonne%TYPE ; TYPE notation IS RECORD
Exemple : (nom etudiant.nom%type, cours matiere.mat%type,
DECLARE note suit.note%type) ;
le_nom etudiant.Nom%TYPE ; note_etud notation ;
BEGIN
… • variable de type table
END ; Le type table représente une 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é
- Variables de type composé : primaire, déclaré de type BINARY_INTEGER qui permet de stocker des
valeurs entières signées. La déclaration s’effectue en 2 étapes
• variable reprenant la même structure qu’une ligne d’une table : 1. Déclaration du type de l’élément de la table :
TYPE <nom_type> IS TABLE of <type_champ> INDEX BY
Syntaxe : BINARY_INTEGER ;
nom_var table%ROWTYPE ; 2. Déclaration de la variable de type table
nom_variable nom_type ;
Exemple
DECLARE Exemple :
enreg etudiant%ROWTYPE ; TYPE t_nom iS TABLE OF Varchar(10) INDEX BY BINARY-
BEGIN INTEGER ;
… Table_nom t_nom;
END
Enreg
Definition de constantes:
nom_variable <type> DEFAULT <valeur> ;
Ou nom_variable CONSTANT <type> := <valeur> ;

3.3 Initialisation et visibilité des variables

Enreg.N_Etud Enreg.Nom Enreg.Prenom

L’initialisation d’une variable peut se faire par :
• L’opérateur := dans les sections DECLARE, BEGIN et EXCEPTION.
Chaque variable de la structure ENREG a même nom et même type que la • L’ordre SELECT…INTO… dans la section BEGIN.
colonne associée • Le traitement d’un curseur dans la section BEGIN (voir plus loin).

• variable de type RECORD : Une variable est visible dans le bloc ou elle a été déclarée, et dans les blocs
la déclaration s’effectue en deux étapes : imbriqués si elle n’a pas été redéfinie.
1. Déclaration du type enregistrement
TYPE <nom_type> IS RECORD (<nom_champ> <type_champ>, - L’opérateur :=

Professeur : ASSALE Adjé Louis 5/24 INP-HB Professeur : ASSALE Adjé Louis 6/24 INP-HB
ORACLE Langage PL/SQL ORACLE Langage PL/SQL

DECLARE and e.N_Etud# = emp.N_liv#

nom char(10) := ‘KOUASSI’ ; …
salaire number (9,2) := 1300000 ; END;
reponse Boolean := True;
BEGIN - Visibilité des variables dans des blocs imbriqués
… DECLARE
END ; compte Number(5) ;
• cas des constantes credit_max Number(9,2);
DECLARE BEGIN
Pi constant number(7,2) :=3.14; …; compte (Number)
BEGIN …; credit_max
… DECLARE
END ; compte Char(20) ;
• Interdire les valeurs non renseignées avec la clause NOT NULL balance1 Number(9,2) ;
DECLARE
debut Number NOT NULL:=1000 ; BEGIN
BEGIN …; compte (Char)
… …; balance1
END ; …; credit_max
END ;
- L’ordre SELECT …; compte (Number)
• Syntaxe : …; credit_max
SELECT col1, col2 DECLARE
INTO var1,va2 Balance2 Number(9,2);
FROM table BEGIN
[WHERE condition]; …; balance 2
• Règle : …; compte (number )
- La clause INTO est obligatoire …; credit-max
- Le select doit obligatoirement ramener une ligne et une seule , sinon erreur. END ;
(pour traiter un ordre SELECT qui pourrait ramener plusieurs lignes, on utilise …; compte (Number)
un curseur). …; credit_max
• exemple END ;
DECLARE
nom_etud char(40) ; 4. Traitements conditionnels
prenom_etud char(40) ;
livre livre.Titre% TYPE ;
4.1 Définitions et syntaxe
BEGIN
SELECT nom, prenom, titre C’est l’exécution d’instructions en fonction de résultats d’une condition
INTO nom_etud, prenom_etud, livre La syntaxe est :
FROM etudiant e, livre l, emprunt emp IF condition1 THEN traitement1 ;
WHERE nom = ’Kouassi’ ELSIF condition2 THEN traitement2 ;
and e.N_Etud# = emp.N_Etud # ELSE traitement3

Professeur : ASSALE Adjé Louis 7/24 INP-HB Professeur : ASSALE Adjé Louis 8/24 INP-HB
ORACLE Langage PL/SQL ORACLE Langage PL/SQL

END IF ; 5.1 Définition et types :

Les opérateurs utilisés dans les conditions sont les mêmes que dans SQL :
=, <, >, !=, >=, <=, IS NULL, IS NOT NULL, BETWEEN, LIKE, C’est un ensemble d’instructions écrites une seule fois et exécutées à plusieurs
AND, OR,… reprises
PL/SQL permet d’effectuer des traitements répétitifs grâce à la clause LOOP.
Règles:
• dès que l’une des conditions est vraie, exécution du traitement qui suit le Il existe 4 types de boucles :
THEN la boucle de base
• si aucune condition n’est vraie, exécution du traitement ELSE la boucle FOR
• seules les clauses IF, THEN, END IF sont obligatoires la boucle WHILE
la boucle CURSOR…FOR(cf traitement des curseurs)
4.2 Exemple
5.2 La boucle de base
DECLARE
emploi char(10) ; Syntaxe :
nom char(15) := ’KOUASSI’ BEGIN
mes char(30) LOOP[<<label>>]
BEGIN instructions ;
SELECT job INTO emploi END LOOP [label] ;
FROM Employé END ;
WHERE nomemp = nom ;
IF emploi IS NULL Sortie de la boucle de base par la commande : EXIT [label] [WHEN condition]
THEN mes :=nom||'n''a pas d''emploi' ; où label est le nom de la boucle.
ELSIF emploi ='vendeur' Exemple :
THEN UPDATE Employé Insérer les 10 premiers chiffres dans la table Resultat
SET commission = 1000
WHERE nomemp = nom; Declare
mes := nom|| 'commission modifiée' nombre Number :=1 ;
ELSE UPDATE Employé BEGIN
SET commission = 0 LOOP
WHERE nomemp = nom ; INSERT INTO Resultat
Mes := nom|| 'pas de commission' ; VALUES (nbre) ;
END IF ; nombre := nombre +1
INSERT INTO Resultat EXIT WHEN nombre >10 ;
VALUES (mes) ; END LOOP ;
COMMIT ; END ;
END ;

5. Traitements répétitifs 5.3 La boucle FOR

Syntaxe :
FOR indice IN [REVERSE] exp1..exp2

Professeur : ASSALE Adjé Louis 9/24 INP-HB Professeur : ASSALE Adjé Louis 10/24 INP-HB
ORACLE Langage PL/SQL ORACLE Langage PL/SQL

LOOP WHILE reste >= 4

instructions LOOP
END LOOP ; reste :=reste-4 ;
Règles : END LOOP ;
• déclaration implicite de la variable indice INSERT INTO Resultat
• exp1, exp2 : constantes, expressions ou variables VALUES(reste, ‘reste division de 236 par 4’) ;
• sans l’option REVERSE, indice varie de exp1 à exp2 avec un END ;
incrément de 1
• avec l’option REVERSE indice varie de exp2 à exp1 avec, un pas 6. Les curseurs en PL/SQL
de –1
6.1 Définition et types
Exemple : calcul de factorielle5
DECLARE
fact Number :=1 ; Définition :
BEGIN
FOR i IN 1..5 C’est une zone de mémoire de taille fixe, utilisée par le noyau d’oracle pour
LOOP analyser et interpréter tout ordre SQL.
fact :=fact + i; Les statuts d’exécution de l’ordre se trouvent dans le curseur. Il existe deux
END LOOP ; types de curseurs :
INSERT INTO Resultat • Le curseur implicite : curseur SQL généré et géré par le noyau pour
VALUES (fact, ‘factorielle5’) ; chaque ordre SQL d’un bloc.
END ; • Le curseur explicite : curseur SQL généré et géré par l’utilisateur
pour traiter un ordre SELECT qui ramène plusieurs lignes.

5.4 La boucle WHILE

6.2 Etapes d’utilisation d’un curseur explicite
L’exécution de la boucle se fait tant que la condition de la clause WHILE est
vérifiée L’utilisation d’un curseur pour traiter un ordre SELECT susceptible de ramener
Syntaxe : plusieurs lignes nécessite quatre étapes :
BEGIN • Déclaration du curseur
WHILE condition • Ouverture du curseur
LOOP • Traitement des lignes
instructions ; • Fermeture du curseur.
END LOOP ; - Déclaration :
END ;
Tout curseur explicite utilisé dans un bloc PL/SQL doit être déclaré dans la
La condition est une combinaison d’expressions au moyen d’opérateurs :<, <=, section DECLARE du bloc en donnant :
>,>=, =, !=, <>, AND, OR, LIKE,… - son nom,
- l’ordre SELECT associé.
Exemple : reste de la division de 236 par 4 Syntaxe
DECLARE CURSOR nom_curseur IS ordre _ select ;
reste number := 236 ;
BEGIN Exemple :

Professeur : ASSALE Adjé Louis 11/24 INP-HB Professeur : ASSALE Adjé Louis 12/24 INP-HB
ORACLE Langage PL/SQL ORACLE Langage PL/SQL

DECLARE ORDER BY prix ;

CURSOR roman IS titre_l livre.titre%TYPE ;
SELECT titre, auteur FROM livre nom livre.auteur%TYPE ;
WHERE genre = 'roman' ; cout_l livre.prix%TYPE ;
BEGIN BEGIN
…; OPEN roman ;
…; LOOP ;
END; FETCH roman INTO titre_l, nom, cout_l ;
IF cout_ l> 100
- Ouverture THEN INSERT INTO Resultat
VALUES( titre_l, nom, cout_l) ;
Après avoir déclaré un curseur, on l’ouvre pour faire exécuter l’ordre SELECT. END IF ;
L’ouverture du curseur se fait dans la section BEGIN du bloc EXIT WHEN cout_l >= 125 ;
Syntaxe END LOOP ;
OPEN nom_ curseur ; CLOSE roman ;
END :
Exemple
DECLARE - Fermeture
CURSOR roman IS Après le traitement des lignes, pour libérer la place mémoire, on ferme le
SELECT titre, auteur FROM livre curseur
WHERE genre = 'roman' ; Syntaxe
BEGIN CLOSE nom_curseur ;
…;
OPEN roman ; EXERCICE
…; Trouver les n plus coûteux livres de la table Livre.
END ;

- Traitement des lignes : 6.3 Les attributs d’un curseur

Après l’exécution du SELECT les lignes ramenées sont traitées une par une, la Les attributs d’un curseur (implicite ou explicite) sont des indicateurs sur l’état
valeur de chaque colonne du SELECT doit être stockée dans une variable du curseur.
réceptrice. %FOUND concerne la dernière ligne traitée
Syntaxe : %NOTFOUND
FETCH nom_ curseur INTO liste_ variables ; % ISOPEN concerne l’ouverture d’un curseur
% ROWCOUNT concerne le nombre de lignes déjà traitées
Le FETCH ramène une seule ligne à la fois : pour traiter n lignes, prévoir une
boucle. - Attribut %FOUND :
Il est de type booléen.
EXEMPLE • Pour un curseur implicite la syntaxe est : SQL%FOUND la valeur est
DECLARE TRUE (vrai) quand pour les instructions INSERT UPDATE DELETE
CURSOR roman is une ligne au moins a été traitée. Pour l’instruction SELECT… INTO
SELECT titre, auteur, prix FROM livre une ligne et une seule a été ramenée.
WHERE genre = roman

Professeur : ASSALE Adjé Louis 13/24 INP-HB Professeur : ASSALE Adjé Louis 14/24 INP-HB
ORACLE Langage PL/SQL ORACLE Langage PL/SQL

• Pour curseur explicite la syntaxe est : nom_ curseur%FOUND la THEN INSERT INTO Resultat
valeur est TRUE quand le dernier FETCH a ramené une ligne VALUES (titre_l, nom, cout_l);
END IF ;
EXEMPLE. END LOOP;
DECLARE CLOSE roman;
CURSOR roman IS END ;
…
… - Attribut %ISOPEN
BEGIN
OPEN roman; Est de type booléen
FETCH roman INTO titre_l,nom, cout_l ; • Curseur implicite: SQL %ISOPEN toujours à FALSE car oracle
WHILE roman%FOUND renferme les curseurs après utilisation.
LOOP • Curseur explicite : nom_curseur%ISOPEN renvoie la valeur TRUE
IF cout_l > 100 quand le curseur est ouvert.
THEN INSERT INTO Resultat
VALUES( titre_l , nom, cout_l) ; EXEMPLE
END IF ; DECLARE
FETCH roman INTO titre_l, nom, cout_l ; CURSOR roman IS
END LOOP; …
CLOSE roman; BEGIN
END; IF NOT(roman%ISOPEN)
THEN OPEN roman;
- Attribut %NOTFOUND END IF ;
Est de type booléen. LOOP
• Curseur implicite: SQL%NOTFOUND FETCH INTO titre_l,nom,cout_l
la valeur est TRUE quand les instructions INSERT, UPDATE et EXIT WHEN roman%NOTFOUND ;
DELETE ne traitent aucune ligne et quand l’instruction SELECT … IF cout_l>100
INTO ne ramène pas de lignes. THEN INSERT INTO Resultat
• Curseur explicite : nom_curseur%NOTFOUND la valeur est TRUE VALUES(titre_l, nom, cout_l) ;
quand le dernier FETCH n’a pas ramené de ligne. END IF;
END LOOP;
EXEMPLE CLOSE roman;
DECLARE END;
CURSOR roman IS
SELECT titre, auteur, prix FROM Livre - Attribut %ROWCOUNT
WHERE genre = ‘roman’ est de type numérique
ORDER BY prix ; • Curseur implicite : SQL%ROWCOUNT ramène pour les instructions
BEGIN INSERT, UPDATE et DELETE le nombre de lignes traitées.
OPEN roman; Quand l’instruction SELECT…INTO :
LOOP ne ramène aucune ligne, il renvoie 0
FETCH INTO titre_l, nom, cout_l ramène exactement une ligne, il renvoie 1
EXIT WHEN roman %NOTFOUND; ramène plus d’une ligne, il renvoie 2
IF cout_l > 100

Professeur : ASSALE Adjé Louis 15/24 INP-HB Professeur : ASSALE Adjé Louis 16/24 INP-HB
ORACLE Langage PL/SQL ORACLE Langage PL/SQL

• Curseur explicite : nom_curseur%ROWCOUNT traduit la nième ligne SELECT titre, auteur

ramenée par le FETCH. FROM Livre
WHERE genre = ‘roman’ ;
genre_roman roman%ROWTYPE ;
6.4 Exemple BEGIN
DECLARE OPEN roman
CURSOR type IS LOOP
SELECT titre, genre, auteur, FROM Livre ; FETCH roman INTO genre_ roman
titre_l Livre.titre%TYPE ; EXIT WHEN roman%NOTFOUND;
genre_l Livre.genre%TYPE ; INSERT INTO Resultat
nom Livre.auteur%TYPE ; VALUES ( genre_roman.titre, genre_roman.auteur);
BEGIN END LOOP ;
OPEN type ; CLOSE roman ;
LOOP END ;
FETCH type INTO titre_l, genre_l, nom ;
EXIT WHEN type%NOTFOUND OR type%ROWCOUNT > 10 ; - Traitement du curseur :
IF genre_l = 'roman' s’utilise pour faciliter la programmation des curseurs dans les boucles.
THEN INSERT INTO Resultat
VALUES (titre_l, nom);
END IF ;
END LOOP ; DECLARE
CLOSE type ; CURSOR nom_ curseur IS SELECT… ;
END ; BEGIN
FOR nom_rec IN nom_curseur LOOP
/* … traitement … */
END LOOP;
6.5 Simplification d’écriture avec les curseurs

- Déclaration de variables : DECLARE

CURSOR nom_curseur IS SELECT… ;
nom_rec nom_curseur%ROWTYPE;
Déclaration implicite d’une structure dont les éléments sont identiques aux BEGIN
colonnes ramenées par le curseur. OPEN nom_curseur ;
Syntaxe : LOOP
Dans la partie déclaration du bloc FETCH nom_curseur INTO nom_rec;
DECLARE EXIT WHEN nom_curseur%NOTFOUND;
CURSOR nom_curseur IS ordre_select ; /* …. Traitement … */
END LOOP;
nom_stucture nom_curseur%ROWTYPE ; CLOSE nom_curseur;

Les éléments de la structure sont identifiés par : nom_structure.nom_colonne.

La structure est renseignée par le FETCH : N.B. : pas de Fetch dans le FOR, et pas de déclaration pour la variable nom_rec
FETCH nom_curseur INTO nom_structure;
Exemple EXEMPLE
DECLARE DECLARE
CURSOR roman IS CURSOR roman IS

Professeur : ASSALE Adjé Louis 17/24 INP-HB Professeur : ASSALE Adjé Louis 18/24 INP-HB
ORACLE Langage PL/SQL ORACLE Langage PL/SQL

SELECT titre, auteur, prix OPEN nom_curseur (val1,val2, ...);

FROM Livre /* ou FOR nom_structure IN nom_curseur (val1, val2, …) ; */
WHERE genre = 'roman'
nom_livre roman%ROWTYPE; • Les types utilisés sont : CHAR, NUMBER, DATE, BOOLEAN, sans
BEGIN spécification de longueur.
FOR nom_livre IN roman • Passage des valeurs des paramètres à l’ouverture du curseur.
LOOP
IF nom_livre prix > 100 Exemple
THEN INSERT INTO Resultat DECLARE
VALUES (nom_livre.titre, nom_livre.auteur); CURSOR le_genre (genre_livre char) IS SELECT titre, auteur FROM
END IF ; Livre
END LOOP ; WHERE genre = genre_livre.
END; BEGIN
FOR un_livre IN le_genre (‘roman’)
- Déclaration du curseur dans la boucle FOR: LOOP
Syntaxe INSERT INTO Resultat
FOR nom_record IN (SELECT … ) VALUES (un_livre.titre, un_livre.auteur) ;
LOOP END LOOP ;
traitement; FOR un_livre IN le_genre(‘poésie’)
END LOOP; LOOP
Cette syntaxe évite la déclaration du curseur INSERT INTO Resultat
VALUES (un_livre.titre, un_livre.Auteur)
Exemple END LOOP;
BEGIN END;
FOR nom_livre IN (SELECT titre, auteur FROM Livre WHERE genre
= 'roman')
LOOP 6.7 La clause CURRENT OF
INSERT INTO Resultat
VALUES (nom_livre.titre, nom_livre.Auteur) ; Objectif :
END LOOP ; La clause CURRENT OF permet d’accéder directement en modification ou en
END; suppression à la ligne que vient de ramener l’ordre FETCH.
Il faut au préalable réserver les lignes lors de la déclaration du curseur par un
verrou d’intention (… FOR UPDATE OF nom_col … ).
6.6 Curseur paramétré
Exemple : augmenter les lignes dont le prix est supérieur à 100
Objectif : DECLARE
Pourvoir réutiliser un même curseur avec des valeurs différentes, dans CURSOR cout IS SELECT titre, prix FROM livre FOR UPDATE OF
un même bloc PL/SQL prix;
Syntaxe BEGIN
DECLARE FOR cout_rec .in cout
CURSOR nom_curseur (para1 TYPE, para2 TYPE, …) LOOP IF cout_rec.prix > 100
IS order_ select … ; THEN INSERT INTO Resultat
BEGIN VALUES (cout_rec.titre, cout_rec.prix * 1.5)

Professeur : ASSALE Adjé Louis 19/24 INP-HB Professeur : ASSALE Adjé Louis 20/24 INP-HB
ORACLE Langage PL/SQL ORACLE Langage PL/SQL

UPDATE livre SET prix = prix *1.5 7.2 Anomalie programme utilisateur
WHERE CURRENT OF cout ;
END IF; Syntaxe :
END LOOP; DECLARE
END; …
nom_erreur EXCEPTION ;
EXEMPLE2: créer une table type de deux colonnes genre et total. Remplir la …
colonne genre des différents genres de livre, mettre à jour la colonne total avec BEGIN
la somme totale des prix des livres. …
DECLARE IF (anomalie)
CURSOR c_type IS SELECT genre, total FROM type FOR UPDATE THEN RAISE nom_erreur;
OF total …
BEGIN EXCEPTION
FOR genre_type IN c_type WHEN nom_erreur THEN (traitement) ;
LOOP UPDATE type END ;
SET total = (SELECT sum(prix) FROM livre WHERE genre =
genre_type.genre) Remarque : on sort du bloc après exécution du traitement
WHERE CURRENT OF c_ type;
END LOOP; Exemple : diminution des prix des livres de 100 francs
END;
/ DECLARE
SELECT * FROM livre Prix_limite EXCEPTION ;
/ CURSOR cout IS SELECT titre, prix
FROM livre
7. Gestion des erreurs ORDER BY prix DESC
FOR UPDATE OF prix
BEGIN
7.1 Section exception FOR cout_rec IN cout
LOOP
La section EXCEPTION permet d’afficher un traitement approprié aux erreurs IF cout_rec prix >100
survenues lors de l’exécution du bloc PL/SQL. THEN INSERT INTO Resultat
On distingue deux types d’erreurs : VALUES(cout_rec.titre, cout_rec.prix - 100) ;
• Erreur interne ORACLE (SLQCODE !=0) UPDATE livre SET prix = prix - 100
• Erreur programme utilisateur. WHERE CURRENT OF cout;
ELSE RAISE prix_limite ;
Règles à respecter : END IF;
• Définir et donner un nom à chaque erreur (différent pour erreur END LOOP ;
utilisateur et erreur ORACLE) EXCEPTION
• Associer le nom d’erreur à la section EXCEPTION dans la partie WHEN prix_limite THEN
DECLARE. INSERT INTO resultat2
• Définir le traitement à effectuer dans la partie EXCEPTION VALUES (‘prix inférieur à 100f’) ;
END ;

Professeur : ASSALE Adjé Louis 21/24 INP-HB Professeur : ASSALE Adjé Louis 22/24 INP-HB
ORACLE Langage PL/SQL ORACLE Langage PL/SQL

7.3 Erreur oracle PROGRAM_ERROR -6501

STORAGE_ERROR -6500
Syntaxe : TIME_OUT_ON_RESOURCE -51
DECLARE TOO_MANY_ROW -1422
… VALUE_ERROR -6502
nom_erreur EXCEPTION ; ZERO_DIVIDE -1476
PRAGMA EXCEPTION_INIT(nom_erreur, code_erreur) ; OTHERS toutes les autres erreurs non explicitement
… nommées.
BEGIN
… EXEMPLE : détection de doublons sur une clé primaire
BEGIN
Remarque : dès que l’erreur ORACLE est rencontrée, passage automatique à la :
section EXCEPTION pour réaliser ce traitement approprié à l’erreur. INSERT INTO dept
… VALUES (numero, nom, ville) ;
EXCEPTION :
WHEN nom_erreur THEN EXCEPTION
(traitement) ; WHEN DUP_VAL_INDEX THEN
[WHEN OTHERS THEN (traitement) ; ] INSERT INTO ERREUR
VALUES (numero, ‘déjà inseré’);
Remarque : sortie du bloc après exécution du traitement END ;

EXEMPLE : traitement de l’erreur 1002 : FETCHOUT OF SEQUENCE 8. EXERCICE

DECLARE
fetch_out_of EXCEPTION; • Ecrire un fichier de commande qui permet de calculer et afficher une table
PRAGMA EXCEPTION_INIT (fetch_out_of, -1002); de multiplication d’un nombre n donné. On créera au préalable une table
BEGIN Resultat à 2 colonnes pour enregistrer les résultats.
... On fera une méthode avec la boucle For et une autre avec la boucle
(traitement du curseur); While.
… • Afficher les nième et n+1ième bons étudiants toute matière confondue en
EXCEPTOIN utilisant un fichier de commandes.
WHEN fetch_out_of THEN • Ecrire un fichier de commande qui :
(traitement de l’erreur) 1) demande le nom d’un étudiant
END ; 2) affiche les notes de l’étudiant dans chacune de ses matières
Erreurs prédéfinies: 3) affiche la moyenne des notes pour chacune des matières de l’étudiant
Certaines erreurs ORACLE ont déjà un nom prédéfini – pas besoin de les 4) effectue la mise à jour de la note de l’étudiant :
déclarer, ni de les associer à un numéro de code erreur. - si sa note est comprise entre la note moyenne et 100 alors
Liste : augmenter sa note de 10%
DUP_VAL_ON_INDEX -1 - sinon lui donner la note moyenne comme nouvelle note.
INVALID_CURSOR -1001
INVALID_NUMBER 1722
LOGIN_DENIED -1017
NO_DATA_FOUND +100
NO_LOGGED_ON -1012

Professeur : ASSALE Adjé Louis 23/24 INP-HB Professeur : ASSALE Adjé Louis 24/24 INP-HB