Fondements des bases de données
Programmation en PL/SQL Oracle – procédures, fonctions et
exceptions
Équipe pédagogique BD
[Link]
Version du 13 octobre 2016
Bloc anonyme ou nommé
I Un bloc anonyme PL/SQL est un bloc DECLARE ...BEGIN ...END
comme dans les exemples précédents.
I On peut exécuter directement un bloc PL/SQL anonyme.
I On passe plutôt une procédure ou une fonction nommée pour
réutiliser le code.
Procédure sans paramètre
Exemple
CREATE OR REPLACE PROCEDURE list_nom_emps I S
BEGIN
DECLARE
CURSOR c_nom_emps I S
SELECT nom , a d r e s s e
FROM emp ;
BEGIN
FOR v_emp IN c_nom_emps LOOP
dbms_output . p u t _ l i n e (
’ C l i e n t ␣ : ␣ ’ | | UPPER( v_emp . nom ) | |
’ ␣ V i l l e ␣ : ␣ ’ | | v_emp . a d r e s s e ) ;
END LOOP ;
END;
END;
/
CALL list_nom_emps ( ) ;
Procédure avec paramètres
Exemple
CREATE OR REPLACE PROCEDURE
l i s t e _ n o m _ e m p s ( v i l l e IN v a r c h a r 2 , v _ r e s u l t OUT number ) I S
BEGIN
BEGIN
SELECT COUNT( ∗ ) INTO v _ r e s u l t
FROM emp
WHERE a d r e s s e LIKE ( ’% ’ | | v i l l e | | ’% ’ ) ; IN lecture seule
END; OUT écriture seule
END;
/ IN OUT lecture et
écriture
DECLARE
v _ r e s u l t number ;
BEGIN
liste_nom_emps ( ’ Manchester ’ , v _ r e s u l t ) ;
dbms_output . p u t _ l i n e ( v _ r e s u l t ) ;
END;
/
Fonctions sans paramètre
Exemple
CREATE OR REPLACE FUNCTION nb_emps
RETURN NUMBER −− Type de r e t o u r
IS
BEGIN
DECLARE
i NUMBER;
BEGIN
SELECT COUNT( ∗ ) INTO i
FROM emp ;
RETURN i ;
END;
END;
/
DECLARE
BEGIN
dbms_output . p u t _ l i n e ( nb_emps ( ) ) ;
END;
/
Fonctions avec paramètres
Exemple
CREATE OR REPLACE FUNCTION e u r o _ t o _ f r ( v_somme IN number )
RETURN NUMBER
IS
BEGIN
DECLARE
t a u x CONSTANT number := 6 . 5 5 9 5 7 ;
BEGIN
RETURN v_somme ∗ t a u x ;
END;
END;
/
DECLARE
BEGIN
dbms_output . p u t _ l i n e ( e u r o _ t o _ f r ( 1 5 . 2 4 ) ) ;
END;
/
Seuls les paramètres IN (en lecture seule) sont autorisés
Un peu plus ...
I Les procédures et fonctions peuvent être utilisées dans d’autres
procédures ou fonctions ou dans des blocs PL/SQL anonymes
I Les fonctions peuvent aussi être utilisées dans les requêtes
I Table système contenant les procédures et fonctions : user_source
SELECT * FROM user_source
Pratique
I Déclaration d’une variable globale avec le préfixe « : »
I Description des paramètres : DESC nom_procedure
I Suppression de procédures ou fonctions :
I DROP PROCEDURE nom_procedure
I DROP FUNCTION nom_fonction
I DUAL est une pseudo table avec une seule colonne.
Les exceptions
Une exception est une erreur qui survient durant une exécution, elle est
soit :
I prédéfinie par Oracle,
I définie par le programmeur.
Exceptions prédéfinies
NO_DATA_FOUND quand SELECT ...INTO ne retourne aucune ligne.
TOO_MANY_ROWS quand SELECT ...INTO retourne plusieurs lignes.
VALUE_ERROR érreur numérique.
ZERO_DIVIDE division par zéro
OTHERS toutes erreurs non interceptées.
Traitement des exceptions
Saisir une exception
I Une exception ne provoque pas nécessairement l’arrêt du
programme : elle peut être saisie par une partie EXCEPTION.
I Une exception non saisie remonte dans la procédure appelante (où
elle peut être saisie).
Exemple
BEGIN
...
EXCEPTION
WHEN NO_DATA_FOUND THEN
...
WHEN TOO_MANY_ROWS THEN
...
WHEN OTHERS THEN −−o p t i o n n e l
...
END;
Exceptions utilisateur
I Elles doivent être déclarées avec le type EXCEPTION
I On les lève avec l’instruction RAISE
I On peut aussi utiliser raise_application_error quand on veut
que l’exception remonte à l’utilisateur (sans la saisir)
Exemple
DECLARE
e_custom EXCEPTION ;
PRAGMA EXCEPTION_INIT ( e_custom , −20001 ) ;
BEGIN
r a i s e _ a p p l i c a t i o n _ e r r o r ( −20001 , ’My␣ custom ␣ e r r o r ’ ) ;
−− RAISE e_custom ;
EXCEPTION
WHEN e_custom THEN
dbms_output . p u t _ l i n e ( ’A␣ / custom / ␣ e r r o r ␣ was ␣ e n c o u n t e r e d −␣ ’
| | SQLCODE | | ’ ␣ : ␣ ’ | | SQLERRM ) ;
WHEN OTHERS THEN
dbms_output . p u t _ l i n e ( ’ An␣ e r r o r ␣ was ␣ e n c o u n t e r e d ␣−␣ ’
| | SQLCODE | | ’ ␣ : ␣ ’ | | SQLERRM ) ;
END;
/
Exceptions utilisateur
Exemple
DECLARE
v _ s a l a i r e emp . s a l%TYPE ;
e _ t r o p _ b a s EXCEPTION ;
PRAGMA EXCEPTION_INIT ( e _t rop _b as , −20001 ) ;
BEGIN
SELECT s a l INTO v _ s a l a i r e
FROM emp
WHERE matr = 1 ;
I F v _ s a l a i r e < 2000 THEN
RAISE e _ t r o p _ b a s ;
END I F ;
EXCEPTION
WHEN e _ t r o p _ b a s THEN
dbms_output . p u t _ l i n e ( ’A␣ / custom / ␣ e r r o r ␣ was ␣ e n c o u n t e r e d ␣−␣ ’
| | SQLCODE | | ’ ␣ : ␣ s a l a i r e ␣ t r o p ␣ b a s ’ ) ;
WHEN OTHERS THEN
dbms_output . p u t _ l i n e ( ’ An␣ e r r o r ␣ was ␣ e n c o u n t e r e d ␣−␣ ’
| | SQLCODE | | ’ ␣ : ␣ ’ | | SQLERRM ) ;
END;
/
Fin du cours.
