Transcript PL /SQL Procédure, Fonction et Package

PL /SQL









Introduction
Bloc PL/SQL
Déclaration des variable
Structure de contrôle
Curseurs
Les exceptions
p
Les fonctions et procédures
Les packages
Les triggers
2013-2014
N.EL FADDOULI
1
Procédure, Fonction et Package




Définition
Utilité
Structure
Exemples
2013-2014
N.EL FADDOULI
49
Page 1
Procédures et fonctions


PL/SQL est aussi utilisé pour définir des procédures et fonctions stockées
dans la BD
BD..
Syntaxe:: Procédure
Syntaxe
CREATE [ OR REPLACE ] PROCEDURE
Nom_procédure [ (liste d’arguments
d’arguments))] { IS
IS|
| AS
AS}}
[ variables locales ]
Corps PL
PL--SQL

syntaxe: Fonction
syntaxe:
CREATE [ OR REPLACE ] FUNCTION
Nom_fonction [ ( liste d’arguments ) ] RETURN type { IS
IS|
| AS
AS}}
[ variables locales ]
Corps PL
PL--SQL
2013-2014
N.EL FADDOULI
50
Procédures et fonctions

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 BD
BD..

Li d’arguments
Liste
d’
d’arguments:
: nom_arg [ IN
IN|
| OUT
OUT|
| IN OUT ] Type
T

IN:: la variable est passée en entrée
IN

OUT:: la variable est renseignée par la procédure puis renvoyée à
OUT
l’appelant



IN OUT:
OUT: passage par référence.
référence.
le mot RETURN permet de spécifier le type de la donnée de retour
retour..
Le corps PL/SQL doit commencer par le mot clé BEGIN et se termine par
END.. Il peut être composé d’une partie déclarative , d’un corps de la
END
procédure et d’un gestionnaire d’erreurs.
d’erreurs.
2013-2014
N.EL FADDOULI
51
Page 2
Exemples (1
(1/3)
Exemple : Créer une procédure qui permet de baisser le prix d'un produit de la
table::
table
Produit (Nump,
Nump, nomp,
nomp, pu, qstock
qstock))
CREATE
PROCEDURE
baisse_prix
(
nprod IN NUMBER,
Taux IN NUMBER ) IS
BEGIN
if Taux <1 then
produit SET PU= PU* ( 1 - Taux)
UPDATE p
WHERE Nump
Nump=
= nprod ;
end if;
if;
END ;
2013-2014
N.EL FADDOULI
52
Exemples (2
(2/3)
Exemple : Créer une fonction qui retourne le nom d’un client
cname, …)
Client (Clientno,
Clientno, cname,
CREATE FUNCTION NomClient
N Cli ( N IN Number
N b ) RETURN Varchar
Number)
V h 2(30
Varchar2
30)) IS
S Client.
Client.Cname%
Cname%Type
Type;;
BEGIN
Select Cname into S from Client Where Client
Client..ClientNo = N;
Return (S)
(S);;
EXCEPTION
WHEN
NO_DATA_FOUND
THEN
Return ('Aucun');
('Aucun');
END ;
2013-2014
N.EL FADDOULI
53
Page 3
Exemples (3
(3/3)
Appel d'une procédure ou fonction
SQL> Declare
A Client
Client..ClientNo%
ClientNo%type;
type; S
Client
Client..Cname%
Cname%type
type;;
BEGIN
A := &Numero_Client;
Numero_Client;
S := NomClient
NomClient(A)
(A);;
dbms_output..put_line
dbms_output
put_line(( S );
END ;
2009-2010
2013-2014
N.EL FADDOULI
54
Modification d’une procédure ( fonction)

Si la base de données évolue, il faut recompiler les procédures
existantes pour qu’elles
qu elles tiennent compte de ces modifications
modifications..

La commande est la suivante:
ALTER { FUNCTION | PROCEDURE } nom COMPILE
Exemple:
Exemple:
ALTER PROCEDURE Baisse_prix
Baisse prix COMPILE;
ALTER FUNCTION NomClient COMPILE;
2013-2014
N.EL FADDOULI
55
Page 4
Suppression d’une procédure ( fonction)

Pour supprimer
pp
une p
procédure
DROP { FUNCTION |PROCEDURE } nom
2013-2014
N.EL FADDOULI
56
Les soussous-blocs

Un bloc PL/SQL peut contenir un sous bloc:
Declare
Variables
Begin
…..
Declare
Variables
Begin
g
….
End;
…..
End;
2013-2014
N.EL FADDOULI
57
Page 5
Les soussous-blocs: Exemple
Declare
a number;
number; b varchar
varchar22(30);
30); c number;
number;
B i
Begin
a:=10
a:=
10;; b:='Imagination'; c:= 20;
20;
Declare
a number
number; b varchar
varchar22(30);
30); d number
number;;
Begin
d:=cc+5;
d:=
a:=40
a:=
40;; b:='programmation';
p g
;
End;
b:=b||' Esprit'; a:=a+d
a:=a+d;
dbms_output..put_line(
dbms_output
put_line(a||' '||b||' '||c);
End;
2013-2014
N.EL FADDOULI
58
Les packages

Encapsuler des procédures, des fonctions, des curseurs et des
variables comme une unité dans la base de données.
données.

Etapes de création
création::

Création des spécifications du package

spécifier la partie publique du package (fonctions, procédures,
types, variables, constantes, exceptions et curseurs)
curseurs).

Création du corps du package

défi i les
définir
l procédures,
éd
l fonctions,
les
f
i
l curseurs et les
les
l exceptions
i
quii
sont déclarés dans les spécifications du package
package..

définir d’autres objets non déclarés dans les spécifications
spécifications.. Ces objets
sont alors privés
privés..
2013-2014
N.EL FADDOULI
59
Page 6
Les packages

Création des spécifications du package
CREATE [ OR REPLACE ] PACKAGE nom_package
{ IS
IS|| AS }
spécifications PL/SQL
END nom_package;
spécification PL/SQL::=
::=declaration_de_variable|
declaration_de_variable|
declaration_d_enregistrement|
declaration_de_curseur|
declaration_d_execption|
declaration_de_procedure|
declaration_de_fonction…
2013-2014
N.EL FADDOULI
60
Exemple
CREATE OR REPLACE PACKAGE gest_empl IS
-- Variables publiques
Sal EMP
EMP..sal
sal%
%Type ;
-- Fonctions et procédures publiques
FUNCTION Augmentation (NumEmp IN EMP.
EMP.empno
empno%
%Type,
Pourcent IN NUMBER ) Return NUMBER ;
PROCEDURE Test_Augmentation ( NumEmp IN EMPLOYE.
EMPLOYE.empno
empno%
%Type,
Pourcent IN OUT NUMBER ) ;
END gest_empl
gest_empl;;
2013-2014
N.EL FADDOULI
61
Page 7
Les packages

Création du corps du package
CREATE [ OR REPLACE ] PACKAGE BODY nom_package
{ IS
IS|| AS }
spécifications PL/SQL
END nom_package
nom_package;;
spécification PL/SQL::=
::=declaration_de_variable
declaration_de_variable||
declaration_d_enregistrement|
declaration_d_enregistrement
|
declaration_de_curseur|
declaration de curseur|
declaration_de_curseur
curseur|
declaration_d_execption||
declaration_d_execption
declaration_de_procedure|
declaration_de_procedure
|
declaration_de_fonction…
declaration_de_fonction
…
2013-2014
N.EL FADDOULI
62
Exemple
CREATE OR REPLACE PACKAGE BODY gest_empl IS
E
EMP
EMP%
%Rowtype ; ---Variables
Variables privées
-- Fonctions publiques
FUNCTION Augmentation ( Numemp IN EMP.empno
EMP.empno%Type
%Type ,
Pourcent IN NUMBER
) Return NUMBER
IS
Salaire EMP.sal%Type ;
BEGIN
Select sal Into Salaire From EMP Where empno = Numemp ;
Salaire := Salaire * (Pourcent + 1 ); -- augmentation virtuelle
-- Affectation de la variable globale publique
Return( Salaire ) ;
-- retour de la valeur
Sal := Salaire ;
END Augmentation;
2013-2014
N.EL FADDOULI
63
Page 8
-- Procédure privée
PROCEDURE Affiche_Salaires IS
CURSOR
C_EMP IS select * from EMP ;
BEGIN
OPEN C_EMP ; FETCH C_EMP Into E ;
While C_EMP%
C_EMP%FOUND Loop
dbms_output..put_line
dbms_output
put_line((
'Employé ' || E.ename
' -> '|| To_char(
To_char( E.sal ) );
||
FETCH C_EMP
C EMP Into
I
E;
End loop ;
CLOSE C_EMP ;
END Affiche_Salaires ;
2013-2014
N.EL FADDOULI
64
-- Procédures publiques
PROCEDURE Test_Augmentation ( Numemp IN
EMP.empno%Type
EMP.empno
%Type , Pourcent IN OUT NUMBER ) IS
Salaire EMP.sal%Type ;
BEGIN
Select sal Into Salaire From EMP Where empno = Numemp ;
Pourcent := Salaire * (Pourcent + 1) ; -- augmentation virtuelle
Affiche_Salaires ;
-- appel procédure privée
END Test_Augmentation
Test_Augmentation;;
END gest_empl
gest_empl;; /
2013-2014
N.EL FADDOULI
65
Page 9
Package

L'accès à un objet d'un paquetage :
nom_paquetage.nom_objet[(
[(liste
liste paramètres
paramètres)]
)]

Exemple:: Appel de la fonction Augmentation du paquetage
Exemple
SQL> Declare
A emp
emp..sal%
sal%Type ;
Begin
A := gest_empl.Augmentation
Augmentation(( 100,
100, 0.2 ) ;
…..
dbms_output.put_line(
dbms_output.
put_line(gest_empl.sal)
sal);;
…..
End ; /
2013-2014
N.EL FADDOULI
66
Page 10