Les fonctions, les procédures et les package

en PL/SQL

1
Plan

• Les Procédures en PL/SQL

• Les Fonctions en PL/SQL

• Les Package en PL/SQL

2
 Introduction
• Une procédure est un bloc PL/SQL nommé sans valeur de retour stocké sur le
serveur oracle

• Une fonction retourne une valeur

• Les packages regrouperont des fonctions et des procédures fortement liées conçus
pour répondre à des besoins spécifiques et permettre une utilisation autonome

• Exemple : Package pour la gestion des articles d’une société

• Sélection de tous les articles,

• Mise à jour d’un article,
• Suppression d’un article,
• Ajout d’un article.

• Les packages sont utilisés par les programmes clients comme de simples librairies
distantes sur le serveur
3
Les procédures : Syntaxe

CREATE [ OR REPLACE ] PROCEDURE [<user>] nom_proc

(arg1 IN type1 [DEFAULT val_initiale], arg2 ..)

AS ou Is

[ Déclarations des variables locales ]

Begin

//code de la procedure

Exception

//Gestion des exceptions

End;
4
Les procédures : Syntaxe

5
 Exemple : Compter le nombre
d’employés pour un département donné
CREATE OR REPLACE PROCEDURE Proc_Dept (NumD IN dept.deptno%TYPE)
IS

v_NbrEmp NUMBER;

BEGIN

SELECT COUNT(deptno)
INTO v_NbrEmp
FROM emp
WHERE deptno=NumD;
DBMS_OUTPUT.PUT_LINE('Nombre des employés : '||' '||v_NbrEmp);

END;
6
Exemple: Paramètre d’entrées/sortie

CREATE OR REPLACE PROCEDURE format_tel (v_tel IN OUT VARCHAR2)

IS

BEGIN
v_tel := substr(v_tel,6,8);

END ;

• substr(ch, Debut, Longueur) : Fonction Sql permettant d’extraire une

sous-chaine à partir de de la chaine ‘ch’ partant de la position ‘Debut’
ayant une taille définie par ‘Longueur’

7
Explication
• La procédure format_tel prend un numéro de téléphone en entrée
(v_tel) et modifie son format en lui affectant le numéro de téléphone
sans l’indicateur téléphonique international.

• Exemple :

begin
ch:=‘0021671002002’;
format_tel (ch) ;
Dbms_Output.Put_Line(‘le numéro de téléphone sans
indicateur international est’ || ch);
End;

• Après l’exécution du bloc, le message suivant sera affiché:

le numéro de téléphone sans indicateur international est : 71002002

8
Exemple : Mettre à jour le salaire
d’un employé (Taux et ID en entrée)
Script modifsalaire.sql

create or replace procedure modifier_salaire (id number, taux number)

Is

Begin
update emp
set salaire=salaire*(1+taux)
where empno= id;

Exception

when no_data_found then

DBMS_OUTPUT.PUT_LINE(‘Employé inconnu : ‘||to_char(id));
9
End;
Compilation de la procédure
modifier_salaire
• Compilation : Pour compiler le script sql ‘modifsalaire.sql’

SQL>start modifsalaire.sql
Procedure created

• Si le script contient des erreurs, la commande show err[ors] permet de visualiser les
erreurs.
SQL>start modifsalaire.sql
Warning: Procedure created with compilation errors

SQL>show err
Errors for Procedure Modifie_salaire

10
Appel et exécution de la procédure
modifier_salaire

 Mode SQLPLUS :

EXECUTE [owner]<nom_proc> (Liste arguments);

Exemple : EXECUTE modifier_salaire(3,10);

 Appel dans un bloc : [owner]<nom_proc> (Liste arguments);

Begin
modifier_salaire (15,-0.5);
End;

11
Les fonctions

• Une fonction est une procédure qui retourne une valeur.

• La seule différence syntaxique se traduit par la présence du mot clé RETURN

• Une fonction précise le type de donnée qu’elle retourne dans son prototype
(signature de la fonction).

• Le retour d’une valeur se traduit par l’instruction RETURN (valeur).

12
Les fonctions : Syntaxe

CREATE [ OR REPLACE ] FUNCTION [<user>] nom_function

(arg1 IN type1 [DEFAULT val_initiale], arg2 ..)

RETURN Type_Retour AS

[ Déclarations des variables locales ]

Begin
//code fonction
Return (val_retour);

Exception

//Gestion des exceptions

End;
13
Exemple : Calculer le nombre
d’employés pour un département donné

CREATE OR REPLACE FUNCTION Funct_Dept (NumD IN dept.deptno%TYPE)

RETURN NUMBER AS

v_NbrEmp NUMBER;
BEGIN

SELECT COUNT(deptno)
INTO v_NbrEmp
FROM emp
WHERE deptno=NumD;
RETURN v_NbrEmp;
END;

14
Appel et exécution de la fonction
Funct_Dept
 Mode SQLPLUS :
Syntaxe :
• EXECUTE var_externe := [owner]<nom_func> (Liste arguments);
• Select [owner]<nom_func> (Liste arguments) from Dual;

Exemple :
• EXECUTE resultat := Funct_Dept(10);
• Select Funct_Dept(10) from Dual;

 Appel dans un bloc PL/SQL :

Begin
resultat := Funct_Dept (10);
DBMS_OUTPUT.PUT_LINE(‘Le nombre d’employés du département
10 est ‘||resultat);
15
End;
Les Packages : Structure générale

CREATE [ OR REPLACE ] Package nom_package IS

//Définitions des types utilisés dans le package (tableau, record,…);

//Déclaration des variables globales;
//constantes
//Exceptions
//Prototypes de toutes les procédures et fonctions du package;

END nom_package;

CREATE [ OR REPLACE ] Package Body nom_package IS

//Définition de la première fonction;

//Définition de la deuxième procédure;
Etc…

End
16 Nom_Package;
Les Packages : Structure générale

• Un package est composé d’un entête et d’un corps :

 L’en tête comporte:

-Les types de données définis
-Les prototypes de toutes les procédures et fonctions
du package.
-Etc.

 Le corps correspond à l’implémentation des procédures et

des fonctions. Chaque procédure est définie avec ses clauses
BEGIN ... END.

• Le premier END marque la fin de l’en tête du package.

• Le deuxième END marque la fin de lu corps du package.

17
 Les Package : Fichier Package1.sql

Create or replace Package Gestion_Emp is

Function Recherche_Emp(v_id emp.empno%type) return boolean;

Procedure MisAjour_Salaire(v_id emp.empno%type, v_taux number);

Function Moyenne_Salaire(v_deptno emp.deptno%type) return Number

Procedure Affiche_Emp(v_id emp.empno%type);

End Gestion_Emp;

18
 Fichier Package1.sql : suite

Create or replace package Body Gestion_Emp is

Function Recherche_Emp(v_id emp.empno%type) return boolean

Is
V_nom emp.ename%type
Begin
Select ename into v_nom from emp where empno=v_id;
return true;
Exception
Where no_data_found then
Return false;
End

19
Fichier Package1.sql : suite

Procedure Affiche_Emp(v_id emp.empno%type)

As
Begin

If (Recherche_Emp (v_id) ) then

Select * into v_recup from emp where empno=v_id;

Dbms_Output.Put_Line('Lemployé : '|| v_recup.ename|| ‘
occupe le poste'||v_recup.poste || ' salaire : '|| v_recup.salaire
|| 'recruté le :'||v_recup.dateemb);

End if;

End;
20
 Fichier Package1.sql : suite

Procedure MisAjour_Salaire (v_id emp.empno%type, v_taux number)

Is
Begin
if(Recherche_Emp (v_id) )then
Update emp set salaire = salaire*(1+v_taux) Where empno=v_id;
End If;
Commit;
End;

Function Moyenne_Salaire(v_deptno emp.deptno%type) return Number

Is
Begin
Select avg(salaire) Into v_moyenne From emp Where deptno=v_deptno;
End;

End;21 // Fin package body

 Les package : Explication

• Package Gestion_Emp comporte deux procédures et deux fonctions.

• Compilation:

 SQL> Start package1.sql Package created.

Package body created

• Opérations sur les packages:

 Exécution de la procédure MisAjour_Salaire du package

Gestion_emp :
SQL> begin
Gestion_emp.MisAjour_salaire(4,50);
end; /
PL/SQL procedure successfully completed.
22
 Opération sur les packages
 Exécution de la fonction Recherche_Emp du package Gestion_emp :

Declare
Exist boolean;
Begin

Exist := Gestion_emp.Recherche_Emp(8);
If (Exist) then
Dbms_OutPut.Put_Line (‘Employé existant’);
Else
Dbms_OutPut.Put_Line(‘Employé inexistant’);
end; /

On suppose que l’employé avec l’ID 8 existe.

L’affichage à la console sera alors :

PL/SQL procedure successfully completed.
23 Employé Existant