La procédure RAISE_APPLICATION_ERROR(n, 'message'), où n est un numéro choisi par
le développeur entre -20000 et -20999 a la particularité de stopper immédiatement le
programme et de renvoyer un numéro et un message d'erreur au programme appelant.
DECLARE
code number;
msg varchar2(200);
CURSOR curart IS
SELECT * FROM article
FOR UPDATE OF qtestock;
recart article%ROWTYPE;
mise_a_jour EXCEPTION;
BEGIN
OPEN curart
LOOP
FETCH curart INTO recart;
EXIT WHEN curart%NOTFOUND;
BEGIN
IF recart.qtestock < 30 THEN
RAISE_APPLICATION_ERROR (-20500,'Programme stoppé');
END IF;
IF recart.qtestock < 100 THEN
RAISE mise_a_jour;
END IF;
EXCEPTION
WHEN mise_a_jour THEN
UPDATE article SET qtestock = qtestock + 100
WHERE CURRENT OF curart;
END;
END LOOP;
CLOSE curart;
EXCEPTION
WHEN OTHERS THEN
code:= SQLCODE;
msg:= SQLERRM(code);
dbms_output.put_line(to_char(code));
dbms_output.put_line(msg);
122
END;
Les fonctions et les procédures stockées
Procédure, fonction et package
Une procédure est une unité de traitement qui peut contenir des commandes SQL de
manipulation de données (LMD), des instructions PL/SQL, des variables, des constantes, des
curseurs et un gestionnaire d'erreurs. Une fonction est une procédure qui retourne une
valeur. Les procédures et les fonctions sont créées comme des objets de la base appartenant
à un utilisateur. Elles sont soumises donc à tous les mécanismes de sécurité et de
confidentialité. Elles sont accessibles à travers les outils d'Oracle, tels que SQL*Plus, Forms...
II en résulte que plusieurs applications (outils) peuvent faire appel à la même procédure ou
fonction.
Un package est une « encapsulation » de plusieurs procédures, fonctions, curseurs, variables
au sein d'une seule unité nommée. Les packages offrent plusieurs avantages par rapport aux
procédures et aux fonctions standard.
Les procédures et les fonctions
La notion de procédure a été conçue dans l'esprit de grouper un ensemble de commandes
SQL avec les instructions procédurales. Avec cette combinaison, l’utilisateur peut ainsi
résoudre des problèmes complexes tout en ayant une flexibilité et une aisance dans son
développement. Les procédures et les fonctions sont utilisées pour augmenter
considérablement la productivité. Elles servent également à gérer la sécurité et l'intégrité des
données et à augmenter les performances.
Du point de vue de la sécurité, l'utilisateur peut autoriser l'accès à certaines tables seulement
à travers les procédures. Les usagers bénéficiant du privilège d'accès aux tables à travers les
procédures ne possèdent aucune autorisation d'accès à ces mêmes tables en dehors du cadre
de ces procédures. Au niveau de l'intégrité, les procédures développées et testées assurent la
même fonctionnalité indépendamment de la partie appelante. Autrement dit, la recompilation
d'une procédure en cas de correction n'exige pas la recompilation de l'ensemble du code de
l'application.
Les performances sont assurées par les facteurs suivants :
- Réduction du trafic sur le réseau (soumission d'un bloc PL/SQL au moteur au lieu d'une
commande SQL).
- Compilation des procédures cataloguées (le moteur ne recompile pas les procédures au
moment de l'exécution).
- Exécution immédiate de la procédure si elle est dans la SGA (réduction des accès disque).
- Partage de l'exécution d'une procédure par plusieurs utilisateurs (notion de mémoire
partagée).
Pour créer un objet procédural, vous devez disposer du privilège système CREATE
PROCEDURE pour votre schéma ou du privilège système CREATE ANY PROCEDURE pour la
création dans un autre schéma.
123
Pour autoriser un autre schéma à exécuter une procédure de votre schéma, vous devez lui
octroyer le privilège EXECUTE.
GRANT EXECUTE ON ma_procedure TO autre_schéma
Création d’une procédure ou d’une fonction
La commande qui permet de créer une procédure est la suivante :
CREATE [OR REPLACE] PROCEDURE
[schéma].nom_procédure [(liste d'arguments)]
{IS | AS}
bloc PL/SQL
Celle qui permet de créer une fonction est la suivante :
CREATE [OR REPLACE] FUNCTION
[schéma].nom_fonction [(liste d'arguments)]
RETURN type
{IS | AS}
bloc PL/SQL
L'option OR REPLACE permet de spécifier au système le remplacement de la procédure ou de
la fonction si elle existe déjà dans la base.
L'utilisateur peut précéder le nom de la procédure (fonction) par celui d'un schéma s'il n'est
pas dans cet environnement, à condition d'avoir les privilèges de création de procédures dans
ce schéma.
<liste d'arguments> est composé des arguments d'entrée, de sortie et d'entrée et de
sortie, séparés par une virgule selon le format suivant :
liste d'arguments := nom d'argument [IN |OUT | IN OUT] type;…
Le mot clé IN indique que la variable est passée en entrée.
Le mot clé OUT indique que la variable est renseignée par la procédure puis renvoyée à
l'appelant.
Le mot clé IN OUT est une combinaison des deux modes précédents. La variable est passée
en entrée, renseignée par la procédure puis renvoyée à l'appelant (équivalant au passage de
paramètres par référence dans les langages de programmation).
Le mot clé RETURN permet de spécifier le type de la donnée de retour de la fonction. Enfin,
le type de données est l'un des types reconnus par Oracle.
124
Le <bloc PL/SQL> doit commencer par le mot clé BEGIN et se terminer par END. Il peut être
composé d'une partie déclarative, d'un corps de la procédure et d'un gestionnaire d'erreurs.
Le nom d'une procédure ne doit pas comporter les caractères : ( - ' " ) []
La création d'une procédure peut se faire à l'aide de l'outil « SQL*Plus », mais nous
conseillons fortement au lecteur d'utiliser l’outil « Oracle SQL Developer » pour la rédaction
de ces procédures, la sauvegarde dans la base de données, ainsi que la compilation et
l’exécution de celle-ci.
La dernière ligne de chaque procédure doit être composée d'un seul caractère '/' pour
spécifier au système l'exécution de sa création. Le fait d'avoir la source d'une procédure sur
un fichier permet de corriger facilement la procédure avec l'option OR REPLACE.
Exemple : Créer une procédure qui permet de baisser le prix d'un article et une fonction qui
affecte un numéro au client ou à l’article (en utilisant des séquences).
CREATE PROCEDURE baisse_prix ( Id IN NUMBER, Taux IN NUMBER) IS
BEGIN
UPDATE article SET article.prixunit = article.priunit*( 1
+ Taux)
WHERE article.idarticle = Id;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RAISE_APPLICATION_ERROR
(-20010,'Article Invalide : ' ||TO_CHAR(Id));
END;
CREATE FUNCTION numero (Nature IN CHAR)
RETURN NUMBER
IS
valeur NUMBER;
BEGIN
IF Nature = 'C' OR Nature = 'c' THEN
SELECT seqcl.nextval INTO valeur FROM dual;
ELSIF Nature = 'A' OR Nature = 'a' THEN
SELECT seqar.nextval INTO valeur FROM dual;
ELSE valeur := -1 ;
END IF;
RETURN(valeur);
END;
En cas d'erreur de compilation, l'utilisateur doit la corriger sur le fichier et la soumettre
ensuite au moteur avec l'option OR REPLACE.
Création d’une fonction sous « Oracle SQL Developer »
Pour créer une fonction, cliquez droit sur « Fonctions » puis « Nouvelle fonction… ».
125
Donner un nom et un schéma à votre fonction. Définissez les paramètres de retours. Ici, nous
n’utiliserons qu’un paramètre de type VARCHAR2. Pour ajouter des paramètres, cliquez sur le « + »
en vert.
Modifiez le code ainsi : nous créons une fonction qui nécessite une chaîne de caractère en entrée et
qui nous retourne cette même chaîne avec une conversion en majuscule.
create or replace FUNCTION CONVERTUPPER (inChaine VARCHAR2) RETURN VARCHAR2
IS
BEGIN
return upper(inChaine);
END;
126
Puis enregistrez votre fonction.
Pour utiliser cette fonction, il suffit de l’appeler en lui transmettant une chaîne de caractère (puisque
notre fonction nécessite un paramètre en entrée). Par exemple :
select convertUpper('coucou') from dual;
Prenons un autre exemple. Voici une fonction qui retourne un commentaire en fonction d’un
paramètre en entrée qui correspond à un chiffre.
create or replace function fn_Satisfaction (satisf integer) return varchar2
as
begin
declare
result varchar2(20);
begin
case
WHEN satisf IS NULL THEN result := 'Sans commentaire';
WHEN satisf between 1 and 2 THEN result := 'Mauvais';
WHEN satisf between 3 and 4 THEN result := 'Passable';
WHEN satisf between 5 and 6 THEN result := 'Moyen';
WHEN satisf between 7 and 8 THEN result := 'Bon';
WHEN satisf >= 9 THEN result := 'Excellent';
end case;
return result;
end;
end;
127
Testons notre fonction :
select fn_Satisfaction(2) from dual;
Utilisons maintenant notre fonction avec un exemple concret : Ici on affiche les noms et des
fournisseurs et leur indice de satisfaction.
SELECT (NOMFOU) AS Nom, (fn_Satisfaction(SATISF)) AS Satisfaction
FROM FOURNISSEUR
128
Création d’une procédure stockée sous « Oracle SQL Developer »
Cliquez droit sur « Nouvelle procédure… ».
Puis cliquez sur le « + » pour ajouter un paramètre.
Donnez un nom et un schéma à votre procédure puis double-cliquez sur le nom du paramètre pour
vous permettre de modifier la valeur. Vous pouvez également modifier le type. Par exemple,
modifiez « VARCHAR2 » à « NUMBER ». Cliquez sur « OK ».
129
La procédure est créée.
Vous pouvez maintenant modifier le code.
Exemple 1 : Création d’une procédure stockée sans paramètre. Créez la procédure stockée
Lst_fournis qui permet d’afficher le code des fournisseurs pour lesquels une commande a été
passée.
130
CREATE OR REPLACE PROCEDURE Lst_fournis is
vnumfou number;
cursor c_numfou is SELECT distinct numfou FROM entcom;
BEGIN
dbms_output.put_line('******* DEBUT prExo1_Lst_fournis
**************');
open c_numfou;
loop
fetch c_numfou into vnumfou;
exit when c_numfou %NOTFOUND;
dbms_output.put_line(vnumfou);
end loop;
close c_numfou;
dbms_output.put_line('******* FIN prExo1_Lst_fournis
**************');
END;
Exécutons maintenant notre procédure.
SELECT distinct numfou FROM entcom;
BEGIN
prExo1_Lst_fournis();
END;
131
Exemple 2 : Création d’une procédure stockée avec un paramètre en entrée.
On crée la procédure stockée Lst_Commandes, qui liste les commandes ayant un libellé
particulier (exemple : urgent) dans le champ « OBSCOM ».
-- Version 1
create or replace
PROCEDURE Lst_Commandes (libelle in varchar2)
as
vnumcom ligcom.numcom%TYPE;
vnomfou fournisseur.nomfou%TYPE;
vlibart produit.libart%TYPE;
vprix float;
vobscom entcom.obscom%TYPE;
132
cursor c_commande is
SELECT ligcom.numcom, nomfou, libart, qtecde*priuni, obscom
FROM entcom, fournisseur, ligcom, produit
WHERE entcom.numfou = fournisseur.numfou
AND entcom.numcom = ligcom.numcom
AND ligcom.codart = produit.codart
AND obscom like '%'||libelle||'%';
BEGIN
open c_commande;
loop
fetch c_commande into vnumcom, vnomfou, vlibart, vprix, vobscom;
exit when c_commande%NOTFOUND;
dbms_output.put_line(vnumcom || ' ' || vobscom || ' ' || vnomfou ||
' ' || vlibart || ' ' || vprix);
end loop;
close c_commande;
END;
Ou
-- Version 2
create or replace
PROCEDURE Lst_Commandes (libelle in varchar2)
as
cursor c_commande is
SELECT ligcom.numcom, nomfou, libart, qtecde*priuni as prix, obscom
FROM entcom, fournisseur, ligcom, produit
WHERE entcom.numfou = fournisseur.numfou
AND entcom.numcom = ligcom.numcom
AND ligcom.codart = produit.codart
AND obscom like '%'||libelle||'%';
BEGIN
for commande in c_commande loop
dbms_output.put_line(commande.numcom || ' ' || commande.obscom
|| ' ' || commande.nomfou || ' '
|| commande.libart || ' ' || commande.prix);
end loop;
END;
Exécutons maintenant notre procédure.
SELECT ligcom.numcom, obscom, nomfou, libart, qtecde * priuni
FROM entcom, fournisseur, ligcom, produit
WHERE entcom.numfou = fournisseur.numfou
AND entcom.numcom = ligcom.numcom
AND ligcom.codart = produit.codart;
BEGIN
Lst_Commandes('urg');
END;
133
Modification d’une procédure (fonction)
Il arrive parfois que l'application évolue, entraînant ainsi une modification dans le schéma de
la base (suppression ou modification de tables). Pour permettre aux procédures (fonctions)
existantes de prendre en compte ces modifications, il faut les recompiler avec la commande
suivante :
ALTER {FUNCTION | PROCEDURE} [schéma.]nom COMPILE;
Cette commande recompile uniquement les procédures cataloguées standard. Il faut avoir les
privilèges nécessaires pour réaliser cette recompilation.
Exemples : Recompiler la procédure et la fonction créées précédemment.
ALTER PROCEDURE baisse_prix COMPILE;
ALTER FUNCTION numero COMPILE;
Suppression d’une procédure (fonction)
Comme tout objet manipulé par Oracle, les procédures et les fonctions peuvent être
supprimées si le besoin est ressenti. Cette suppression est assurée par la commande suivante
:
DROP {FUNCTION | PROCEDURE} [schéma.]nom
Exécution d’une procédure
Sous « SQL*Plus » :
EXECUTE <nom procédure> [( argument1, argument2, ...) ]
Dans un bloc PL/SQL :
BEGIN
…
<nom procédure> [( argument1, argument2, ...) ] ;
…
134
END;
Exécution d’une fonction
Sous « SQL*Plus » :
SELECT <nom fonction> FROM DUAL
Dans un bloc PL/SQL :
BEGIN
…
x := <nom procédure> [( argument1, argument2, ...) ] ;
…
END ;
