Institut Supérieur des Etudes Technologiques de GAFSA
Cours :PL/SQL
Chapitre 5 :
Les fonctions et les procédures en PL/SQL
Narjes KHALIFA
[email protected]
Introduction
Une procédure est un bloc PL/SQL nommé
Une fonction est identique à une procédure à la différence qu’elle retourne
une valeur
Les procédures (fonctions) permettent de :
Réduire le trafic sur le réseau (les procédures sont locales sur le
serveur)
Masquer la complexité du code SQL (simple appel de procédure avec
passage d’arguments)
Sécuriser l’accès aux données
Optimiser le code
2
Les procédures
Syntaxe
CREATE [OR REPLACE] PROCEDURE <NOM_PROCEDURE>([VAR1 IN
<TYPE_VAR1>], [VAR1 OUT <TYPE_VAR1>]
IS
[DECLARATION LOCALE ]
BEGIN
-- corps PL/SQL
[EXCEPTION ]
END ;
3
Les procédures
• CREATE : Créé la procédure stockée <NOM_PROCEDURE>
• [OR REPLACE] : permet de recréer la procédure si elle existe déjà.
•PROCEDURE : Mot clé pour définir qu’il s’agit d’une procédure stockée.
<NOM_PROCEDURE> : Le nom de la PROCEDURE à créer.
([VAR1 IN <TYPE_VAR1>], .., [VARN OUT <TYPE_VARN>]) : Les
paramètres de notre PROCEDURE, NOM_VARIABLE, IN ou OUT ou IN
OUT et le type du paramètre (VARCHAR2, NUMBER, DATE, etc…).
IN : paramètre en entrée.
OUT : paramètre en sortie.
IN OUT : paramètre en entrée et en sortie.
Les paramètres sont facultatifs. Nous pouvons déclarer une PROCEDURE
sans paramètres d’entrées ou de sorties.
• IS : Mot clé pour déclarer le début de la PROCEDURE.
4
Les procédures
Exemple 1
CREATE OR REPLACE PROCEDURE Ajout_dept (id_dep IN
departement.NUMDEPT%TYPE, nom_dep IN
departement.DNOM %TYPE, NB OUT number)
IS
BEGIN
insert into departement(NUMDEPT,DNOM) values(id_dep,nom_dep);
commit;
select count(*) into NB from departement;
DBMS_OUTPUT.PUT_LINE('Le nombre de departement est : ' ||NB);
END;
5
Les procédures
Pour faire appel à cette PROCEDURE, vous pouvez utiliser
le bloc PL/SQL suivant:
DECLARE
nb NUMBER;
BEGIN
Ajout_dept (80,'Vente',nb);
Ajout_dept (90,'TECH',nb);
END;
/
Pour visualiser les erreurs , vous pouvez utiliser la
commande: show err
6
Les procédures
Exemple 2 :
CREATE OR REPLACE PROCEDURE VERIFICATION (id_emp IN
employe.matricule % type)
IS
mat employe.matricule% type;
BEGIN
select matricule into mat from employe where
matricule=id_emp;
DBMS_OUTPUT.PUT_LINE(id_emp || ' existe ');
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE(id_emp || ' n''existe');
END;
/
7
Les procédures
Pour faire appel à cette PROCEDURE, vous pouvez utiliser
le bloc PL/SQL suivant:
BEGIN
verification(7000);
verification (7800);
END;
/
8
Les fonctions
Une fonction est une procédure qui retourne une valeur. La seule
différence syntaxique par rapport à une procédure se traduit par
la présence du mot clé RETURN.
CREATE [OR REPLACE] FUNCTION <NOM_FUNCTION>
([VAR1 IN <TYPE_VAR1>], [VAR1 IN <TYPE_VAR1>], .... [VARN IN
<TYPE_VARN>])
RETURN <TYPE_RETOUR> IS
[Déclaration variables à utiliser]
BEGIN
--corps PL/SQL
END; /
Tous les paramètres d'une fonction sont en mode IN (dans ce cas,
9
on n'est pas oblige d'ecrire le mode).
Les fonctions
Exemple 1 :
CREATE OR REPLACE Function sum_check (id_emp employe.matricule% TYPE )
RETURN BOOLEAN IS
mat employe.matricule %type;
v_nom employe.enom %type;
avg_sal employe.salaire%type;
sal employe.salaire%TYPE;
BEGIN
select matricule, enom, salaire into mat,v_nom,sal from employe where
matricule=id_emp;
select avg(salaire) into avg_sal from employe;
if( avg_sal>sal)THEN
return TRUE;
ELSE return false;
END IF;
END;/
10
Les fonctions
Exemple 1 :
BEGIN
if (sum_check (7800)) then
DBMS_OUTPUT.PUT_LINE ('AVG SAL > SAL');
ELSE
DBMS_OUTPUT.PUT_LINE ('AVG SAL <SAL');
END IF;
END;
/
11
Les fonctions
Exemple 2 :
CREATE OR REPLACE FUNCTION department_name (deptno
departement.numdept%type )
RETURN varchar2 IS
dname departement.dnom %type;
BEGIN
select DNOM into dname from departement where
NUMDEPT=deptno;
RETURN dname;
END;
/
12
Les fonctions
Exemple 2 :
DECLARE
dname departement.dnom % type;
BEGIN
dname:=department_name(10);
DBMS_OUTPUT.PUT_LINE(dname);
end;
/
13
