Les procédures et Les fonctions en PL/SQL
I) Les procédures
Une procédure est un bloc PL/SQL nommé qui exécute une ou plusieurs actions.
Une procédure peut être stockée dans la base de données, comme tout autre objet, et peut être
réutilisée à son souhait.
1) Syntaxe :
CREATE [OR REPLACE] PROCEDURE nom_proc
(param1 [mode] type_donnée,param2 [mode] type_donnée,….)
IS
Bloc PL/SQL
Où :
Mode : IN (par défaut) | out | IN OUT
Type_donnée : char, date, varchar2, number, %type,….
Note bien :
- Le bloc PL/SQL ne contient pas ‘DECLARE’
- Une procédure peut être créée soit sous SQL*Plus, soit sous ‘Procedure Builder’ qui est un outil
d’Oracle, dont le rôle est de créer des procédures, fonctions, packages et triggers de la base de
données.
Exemple 1
CREATE OR REPLACE PROCEDURE proc_sal (p_empno salaries.numero%type) IS
v_nom salaries.ename%type;
v_sal salaries.salaire%type;
BEGIN
SELECT nom, salaire INTO v_nom, v_salaire FROM salaries WHERE numero = p_empno;
DBMS_OUTPUT.PUT_LINE(p_empno || ‘ ‘ ||v_nom||’ ‘||v_salaire);
END;
/
Execute proc_sal(300);
Exemple 2
Create or replace procedure augmenterSalaire (s_mat in salaries.matricule%type,tx in
number)is
BEGIN
update salaries set salaire = salaire + salaire * tx / 100 where matricule = s_mat;
END;
/
Programme test :
Declare
cursor empCursor is select * from salaries;
minSal salaries.SALaire%TYPE := &smic;
begin
for employeeRec in empCursor loop
if employeeRec.salaire < minSal then
augmenterSalaire (employeeRec.matricule, 10) ;
end if;
end loop;
end;
/
1
2) Création d’une procédure sous SQL*Plus
- Écrire la procédure dans l’éditeur et sauvegarder avec extension .sql
- Créer la procédure avec RUN.
- Utiliser ‘SHOW ERRORS’ pour voir les erreurs de la compilation.
Note bien :
Sous SQL*Plus, ne pas confondre le fichier qui contient la procédure et la procédure elle-même.
Get c :/../fichier.sql
Run : création de la procédure.
Pour exécuter la procédure : execute nom_proc(paramètres)
Note bien :
Écrire le programme PL/SQL qui crée la procédure.On peut sauvegarder la procédure
SQL*Plus : fichier _ save as _ c:\Travail\nom_fichier.sql puis L’appeler à partir de l’éditeur
GET c:\Travail\nom_fichier.sql
RUN (exécuter le fichier .sql et non la procédure Création de la procédure Sinon SHOW ERRORS
Exécuter la procédure _ EXECUTE nom_proc(param)
II) Les fonctions
Les fonctions sont semblables aux procédures, mais retournent une valeur résultat. Une fonction
diffère d'une procédure par le fait qu'on peut l'utiliser dans une expression.
C’est aussi des blocs PL/SQL nommés qui retournent une valeur.
Une fonction peut être stockée dans la base de données comme les autres objets de la base de
données.
1) Syntaxe :
Pour déclarer une FUNCTION:
CREATE : Créé la fonction <NOM_FUNCTION>.
OR REPLACE : Facultatif mais vaut mieux l’utiliser. Cela permet de recréer la
FUNCTION <NOM_FUNCTION> si elle existe déjà.
FUNCTION : Mot clé pour définir qu’il s’agit d’une fonction.
<NOM_FUNCTION> : Le nom de la FUNCTION à créer.
([VAR1 IN <TYPE_VAR1>], .., [VARN OUT ]) : Les paramètres de notre FUNCTION,
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 FUNCTION sans paramètres
d’entrées ou de sorties.
IS : Mot clé pour déclarer le début de la FUNCTION.
Variables à utiliser : Déclaration des variables qu’on va utiliser dans notre FUNCTION.
BEGIN : Début du bloc PL/SQL de la FUNCTION.
Corps du programme : Bloc PL/SQL de la FUNCTION.
END; : Fin de notre FUNCTION.
/ : Permet de créer la FUNCTION .
2
Par exemple : Écrire une fonction avec un seul paramètre en entrée (No employé qui retourne le
salaire d’un employé.
CREATE OR REPLACE FUNCTION sal_emp(v_mat IN salaries.matricule%type)
RETURN number IS
v_sal salaries.salaire%type := 0;
BEGIN
SELECT salaire INTO v_sal FROM EMP WHERE matricule = v_mat;
Return (v_sal);
END;
Autre Exemple
CREATE OR REPLACE FUNCTION MesActeurs(v_idFilm INTEGER) RETURN VARCHAR
IS
resultat VARCHAR(255);
BEGIN
FOR art IN
(SELECT Artiste.* FROM Role, Artiste
WHERE idFilm = v_idFilm AND idActeur=idArtiste)
LOOP
IF (resultat IS NOT NULL) THEN
resultat := resultat || ', ' || art.prenom || ' ' || art.nom;
ELSE
resultat := art.prenom || ' ' || art.nom;
END IF;
END LOOP;
return resultat;
END;
/
