PL/SQL

1
 Généralités (1/2)

 PL/SQL : langage procédural de 3ème génération, extension de SQL

 Déclaration des variables et de constantes
 Définition de sous-programmes
 Gestion des exceptions
 Gestion des transactions : COMMIT, ROLLBACK, SAVEPOINT, …
 Ordres LDD/LMD : Create, Drop, Select, Insert, …

 Avantages de PL/SQL

 Intégration parfaite du SQL : Réalisation des traitements complexes sur

les données d’une BD d’une manière simple, performante et sécurisée
 Support de la programmation orientée objet

2
 Généralités (2/2)

 Très bonnes performances

PL/SQL interprète des blocs de commandes et non pas des ordres SQL
==> Traitement des données est donc interne à la base
==> Réduction du trafic entre la BD et l’application
==> Echanges réseau  et performances globales d’applications 

 Portabilité
Toutes les BDs Oracle 9i ou plus comportent un moteur d’exécution PL/SQL

Oracle est disponible sur un PL/SQL permet de développer

très grand nombre de plates-formes ==> des applications portables

 Facilité de programmation

3
 Bloc PL/SQL (1/2)

DECLARE
Partie
déclarative -- Déclaration des variables/constantes
(facultative) -- Exceptions, curseurs,

BEGIN
Partie
exécutable
-- Commandes/Instructions
(obligatoire)

Partie EXCEPTION
Exception -- Traitements des erreurs à l’exécution
(facultative)

END;

4
 Bloc PL/SQL (2/2)

 Remarques :
 Sous-bloc (uniquement dans les parties Begin et Exception)

 Possibilité de nommer les blocs pour distinguer les différents Begin et

End

Begin Nom_bloc
…
End Nom_bloc;

 -- Commentaire sur une ligne

/* commentaire sur plusieurs lignes */

 Chaque instruction se termine par (;)

5
 Les variables

 PL/SQL gère deux types de variables :

 Variables locales : chaque variable a un type de données associé qui
spécifie son format de stockage, ses contraintes et son jeu valide de
valeurs
 Variables externes :
- Variables PL/SQL déclarées dans les packages (toujours préfixées du
nom du package lors de leur utilisation)

 Remarques :
 Préfixer les variables par la lettre « v_ » ce qui permet de différencier
les variables des colonnes des tables
 Pas de différence entre majuscule et minuscule

6
 Déclarations (1/3)

 Variables
Ex :
V_NumClient Number(10);
V_date_Commande DATE := SYSDATE;
V_Ok Boolean DEFAULT TRUE;
V_Nom Varchar(50) NOT NULL := ‘Mohammed’;

 Constantes
Ex :
PI CONSTANT REAL := 3,14;
TVA CONSTANT Number := 20,6;

7
 Déclarations (2/3)

 Variables faisant référence à la colonne d’une table

Permet de récupérer automatiquement le type de la donnée tel qu’il est
défini dans le dictionnaire de la base

Syntaxe : Nom_variable Nom_table.Nom_colonne%TYPE;

Ex : V_NomClient [Link]%TYPE;

Avantage : Toute modification de la BD sera sans effet sur les

programmes PL/SQL

Remarque : Si la colonne est définie avec NOT NULL, cette contrainte ne

s’applique pas à la variable

8
 Déclarations (3/3)

 Variables faisant référence à la ligne d’une table

L’attribut %ROWTYPE spécifie pour une variable la structure d’une ligne
de table

Syntaxe : Nom_variable Nom_table%ROWTYPE;

Ex : V_Client Client%ROWTYPE;

 Types de variables définis par l’utilisateur

Utilisation de l’ordre SUBTYPE

Ex : Declare
SUBTYPE MonType IS Number(7,2);
V_Salaire MonType;

9
 Enregistrements personnalisés (1/3)

 Déclaration

TYPE TypeRecord IS RECORD

(Nom_Champs { Type_Champ | Variable%Type | [Link]%Type |
Table%ROWTYPE } [NOT NULL], ….)

Ex : Type Enreg_Etudiant IS RECORD (

CNE Number(7),
Nom Varchar(50),
date_naiss Date);

-- Définition d’une variable de type Enreg_Etudiant

v_un_Etudiant Enreg_Etudiant;

10
 Enregistrements personnalisés (2/3)

 Affectation des valeurs

Ex : v_un_Etudiant.CNE := 9487567;
v_un_Etudiant.Nom := ‘Mohammed’;
v_un_Etudiant.date_naiss := ’10/12/2002’;

 Affectation d’un enregistrement

v_un_Etudiant1 Enreg_Etudiant;
v_un_Etudiant2 Enreg_Etudiant;
…
v_un_Etudiant1 := v_un_Etudiant2;

11
 Enregistrements personnalisés (3/3)
SET SERVEROUTPUT ON
DECLARE
TYPE point IS RECORD
(
abscisse NUMBER,
ordonnee NUMBER
);
p point ;
BEGIN
[Link] := 1 ;
[Link] := 3 ;
DBMS_OUTPUT.PUT_LINE( ‘[Link] = ' || [Link] || ' and
[Link] =' || [Link] ) ;
END;
/

12
 Tables PL/SQL (Tableaux) (1/5)

 Déclaration
TYPE Nom_Type IS TABLE OF
(Type_Colonne | Variable%Type | [Link]%Type | Type_Record)
[NOT NULL]
INDEX BY BINARY_INTEGER;
Entiers signés [-2147483647,2147483647]

Ex : DECLARE
Type table_CAR IS TABLE OF CHAR(5) Index By Binary_Integer;
Type Table_Client IS TABLE OF Client%ROWTYPE Index By Binary_Integer;

V_T Table_CAR;
V_Tclients Table_Client;

 Accès aux données

Nom_Tableau(Valeur_clé)
13
 Tables PL/SQL (Tableaux) (2/5)
SET SERVEROUTPUT ON
DECLARE
TYPE TAB IS TABLE OF INTEGER INDEX BY
BINARY_INTEGER;
V_TAB TAB ;
BEGIN
V_TAB( -3 ) := 10 ;
V_TAB( -2 ) := 11 ;
V_TAB( -1 ) := 12 ;
V_TAB( 0 ) := 13 ;
V_TAB( 1 ) := 14 ;
V_TAB( 2 ) := 15 ;
V_TAB( 3 ) := 16 ;
V_TAB( 4 ) := 17 ;

DBMS_OUTPUT.PUT_LINE( V_TAB ( i ) ) ;

END;
14
 Tables PL/SQL (Tableaux) (3/5)

 Déclaration du tableau

TYPE nom_type IS VARRAY ( taille ) OF typeElements ;

 Déclaration d'un tableau

tab nom_type ;

 Allocation d'un tableau

tab := nom_type ( ) ;

 Dimensionnement d'un tableau

[Link]( 4 ) ;

 Utilisation d'un tableau

tab ( i ) := i ;

15
 Tables PL/SQL (Tableaux) (4/5)

SET SERVEROUTPUT ON END LOOP;

DECLARE k := t ( 1 0 ) ;
TYPE numberTab IS VARRAY (10) OF FOR i in REVERSE 2 . . 1 0 LOOP
NUMBER; t ( i ) := t ( i - 1 ) ;
t numberTab ; END LOOP;
i number ; t ( 1 ) := k ;
k number ; FOR i IN 1 . . 1 0 LOOP
BEGIN DBMS_OUTPUT.PUT_LINE( t ( i )
t := numberTab ( ) ; );
t .EXTEND( 1 0 ) ; END LOOP;
FOR i IN 1 . . 1 0 LOOP END;
t ( i ) := i ; /

2013/2014 ORACLE 16
16
 Tables PL/SQL (Tableaux) (5/5)

 Attributs des tables PL/SQL

- EXISTS(n) : TRUE si le nème élément existe

- COUNT : Nombre d’éléments
- FIRST ou LAST : Plus petit et plus grand valeur d’index
- PRIOR(n) : Valeur de l’index qui précède l’index n
- NEXT(n) : Valeur de l’index qui suit l’index n
- DELETE : Supprime tous les éléments du tableau
- DELETE(n) : Supprime le nème élément
- DELETE(n,m) : Supprime les éléments de n à m

17
 Affectation
DECLARE

v_num NUMBER NOT NULL := 0 ;

TYPE Four IS RECORD (

num_four [Link]%type,

desc [Link]%type ) ;

v_four Four;

BEGIN

v_num := v_num+1;

SELECT NC INTO v_num FROM client WHERE NC= v_num+1;

SELECT numfour, raisonsoc

INTO v_four.num_four, v_four.desc

FROM fournisseur

WHERE numfour=4;

DBMS_OUTPUT.PUT_LINE( v_four.desc ) ;

END;
18
 Instructions de contrôle (1/3)

 Instruction de sélection
IF-THEN, IF-THEN-ELSE ou IF-THEN-ELSEIF

IF Condition1 THEN Exemple:

Instructions1; IF MOD(i,5)=5 THEN
ELSEIF Conditions2 THEN
v_qte:=5;
Instructions2;
ELSEIF … ELSEIF MOD(i,7)=0 THEN
ELSE Instructions; v_qte:=7;
END IF; ELSE
v_qte:=i;
END IF;

19
 Instructions de contrôle (2/3)
 Instruction sélective
Exemple:
CASE v_sel
DECLARE
WHEN expression_1 THEN résultat_1;
v_m INTEGER;
…;
v_mention VARCHAR2(20);
WHEN expression_k THEN résultat_k;
BEGIN
[ELSE résultat_par_defaut;]
CASE v_m
END CASE;
WHEN v_m=8 THEN v_mention:=‘REDOUBLE’;
Ou WHEN v_m=10 THEN v_mention:= ‘PASSABLE’;
CASE WHEN v_m=12 THEN v_mention:= ‘ASSEZ-BIEN’;
WHEN v_m= 14 THEN v_mention:= ‘BIEN’;
WHEN condition_1 THEN résultat_1

… WHEN v_m= 16 THEN v_mention:= ‘TRES BIEN’;

End case;
WHEN condition_k THEN résultat_k

[ELSE résultat_par_defaut] END;

END CASE; END;

20
20
 Instructions de contrôle (3/3)

 Instructions itératives

FOR Compteur IN [REVERSE] Min..Max LOOP

-- Instructions;
END LOOP;

WHILE Condition LOOP

-- Instructions
END LOOP;

LOOP
-- Instructions;
-- EXIT WHEN Condition ou EXIT
END LOOP;

21
 Instructions de contrôle (2/2)
Exemple:

Branchements DECLARE
c_max_loop CONSTANT POSITIVE:=30;
--déclaration d’une étiquettev_compt POSITIVE :=1;
<<nom_étiquette>>
BEGIN
-- Saut inconditionnel LOOP
v_compt:=v_compt+1;
GOTO étiquette; IF v_compt=c_max_loop THEN
GOTO e_suivant;
-- Sortie de boucle
END IF;
EXIT WHEN condition; END LOOP;

NB : À utiliser avec énormément de modération

<<e_suivant>>
v_compt:=v_compt*10;
END;

Ou

EXIT WHEN v_compt:=c_max_loop;

22
 Instructions de contrôle (2/2)
Exemple:
Branchements
DECLARE
--déclaration d’une étiquettec_max_loop CONSTANT POSITIVE:=30;
v_compt POSITIVE :=1;
<<nom_étiquette>>
BEGIN
-- Saut inconditionnel
LOOP
GOTO étiquette; v_compt:=v_compt+1;
EXIT WHEN v_compt=c_max_loop;
-- Sortie de boucle END LOOP;
EXIT WHEN condition;
v_compt:=v_compt*10;
NB : À utiliser avec énormément de modération
END;

23
 Les curseurs (1/10)

 Curseur : structure de données (zone mémoire) utilisée par le noyau pour

analyser et interpréter tout ordre SQL

 Curseurs implicites : générés et gérés par le noyau pour chaque ordre SQL
(select, update, delete, insert)

 Curseurs explicites : créés en programmation PL/SQL et utilisés par le

développeur pour gérer les requêtes SELECT qui rapporte plusieurs lignes

Remarque : Même si on est sûr que la requête rapporte une seule ligne, il faut
utiliser un curseur explicite (problème en cas de mise à jour de la base)

 Déclaration d’un curseur :

CURSOR <Nom_Curseur> IS < Commande SELECT>

24
 Les curseurs (2/10)
CURSOR calcul IS
SELECT np, pu*1.2 as PTTC
FROM produit
ORDER BY NumProd;

Tuple calcul%ROWTYPE;

 NB : Un tuple du curseur sera de type

calcul%ROWTYPE

 Ouverture d’un curseur :

OPEN Nom_Curseur;

 Fermeture d’un curseur :

CLOSE Nom_Curseur;

25
 Les curseurs (3/10)

 Gestion automatique d’un curseur :

For enreg IN nom_Cureur LOOP
…
END; Nom_Curseur%ROWTYPE

Ex :
For c_rec IN c_Produit
Loop
DBMS_OUTPUT.PUT_LINE (‘Numéro : ‘ || c_rec.NP||’ – Désignation : ‘
||c_rec.desig||’ – Prix ‘|| c_rec.PU);
End Loop;

26
 Les curseurs (4/10)

 Gestion manuelle d’un curseur (FETCH):

La commande FETCH ramène une à une les lignes à chaque fois que FETCH est
exécutée le curseur avance vers la ligne suivante

FETCH Nom_Curseur INTO Liste_de_variables

(ou variable_type_enregistrement);
Ex :
Loop
Fetch c_client Into Nom_client, ville, salaire;
IF c_client%FOUND Then …
ELSE EXIT;
END IF;
End Loop;

27
 Les curseurs (5/10)
 Gestion automatique d’un curseur

ex. FOR tuple IN calcul LOOP

var1:=[Link];

var2:=[Link];

END LOOP;

 Gestion « manuelle »

ex. Open calcul

LOOP

FETCH calcul INTO tuple;

EXIT WHEN calcul%NOTFOUND;

END LOOP

close calcul
28
 Les curseurs (6/10)

 Attributs des curseurs

- %NOTFOUND : False si Fetch retourne un résultat

- %FOUND : True si Fetch retourne un résultat
- %ROWCOUNT : Nombre de lignes lues
- %ISOPEN : TRUE si le curseur est ouvert

 La clause CURRENT OF

- WHERE CURRENT OF : Permet de faire référence au positionnement dans

un curseur afin de traiter la ligne correspondante (UPDATE, DELETE).

- Il est nécessaire de réserver la ligne lors de la déclaration du curseur par le

positionnement d’un verrou d’intention (FOR UPDATE OF …)

29
 Les curseurs (7/10)

 Exemple
DECLARE
CURSOR c_produit IS
Select * from Produit
FOR UPDATE OF PU;
BEGIN
For Compteur IN c_produit
LOOP
IF [Link] <2000 then
Update Produit
Set PU = PU*1,2
Where curent of c_Produit;
End IF;
End LOOP;
End;
30
 Les curseurs (8/10)

 Curseurs paramètrés :
DECLARE
CURSOR nom_curseur (param1 type1, param2 Type2, …) IS
Commande SELECT (utilisant les paramètres)

 Utilisation :
Open nom_curseur (valeur1, valeur2, …)
Fetch nom_curseur INTO …
…
CLOSE nom_curseur;
Ou : FOR Compteur IN nom_curseur (valeur1, valeur2, ….)
LOOP
…
END LOOP

31
 Les curseurs (9/10)
Declare

cursor C1(numero number) is

select NP, datec

from commandes

where NC=numero;

vc1 c1%rowtype;

Begin

open c1(1);

fetch c1 into vc1;

close c1;

End;

32
 Les curseurs (10/10)
Gestion automatique d’un curseur Gestion manuelle
DECLARE DECLARE
CURSOR calcul IS… CURSOR calcul IS…
BEGIN V_enreg calcul%ROWTYPE;
BEGIN
FOR v_enreg IN calcul LOOP
OPEN calcul;
Var1:=v_enreg.numprod;
LOOP
Var2:=v_enreg.pTTC; FETCH calcul INTO v_enreg;
… EXIT WHEN
END LOOP; Calcul%NOTFOUND;
END;
Var1:=v_enreg.numprod;
 Remarque
Var2:=v_enreg.pTTC;
 Ouverture et fermeture du curseur
…
se fait d’une façon implicite
END LOOP
 Déclaration de v_enreg n’est pas
CLOSE calcul;
obligatoire
END;
33
 Gestion des exceptions (1/6)

Permet d’affecter un traitement approprié aux erreurs qui apparaissent lors de

l’exécution d’un bloc PL/SQL

Défini en interne par la runtime du système

Deux types (division par zéro, dépassement mémoire, …)

Défini par l’utilisateur

34
 Gestion des exceptions (2/6)

 Codes exceptions internes

Code d’erreur SQLCODE Erreur

+1403 NO_DATA_FOUND
-1 DUP_VAL_ON_INDEX
-6502 VALUE_ERROR
-1001 INVALID CURSOR
-1722 INVALID NUMBER
-6501 PROGRAM ERROR
-1017 LOGIN DENIED
-1422 TOO_MANY_ROWS
-1476 ZERO_DIVIDE

35
 Gestion des exceptions (3/6)

 Fonctions PL/SQL pour la gestion d’erreurs

 SQLCODE : Code de la dernière exception levée
 SQLERR : message d’erreur correspondant

 Exceptions externes
 Déclaration :
DECLARE Nom_exception EXCEPTION
 Lever une exception :
RAISE Nom_exception ;

Arrête l’exécution normale du bloc PL/SQL et transfère

le contrôle au gestionnaire de l’exception

36
 Gestion des exceptions (4/6)
 Exceptions personnalisées
 Déclaration : nom_exc EXCEPTION;
 Lever l’exception : IF condition THEN
RAISE nom_exc;

END IF;

 Traitement des exceptions

WHEN nom_exc THEN -- Instruction

ex. WHEN probleme THEN

RAISE_APPLICATION_ERROR(-20501,’Erreur !’);

NB : -20999 ≤ no d’erreur ≤ -20001

37
 Gestion des exceptions (5/6)
Declare

vcode [Link]%type;

others EXCEPTION;

Begin

select NP into vcode

from commande

where NC=1;

Exception

when no_data_found then

delete from clients where NC=1;

when others then

raise_application_error(-20002,’suppression impossible’);

End; 38
 Gestion des exceptions (6/6)
DECLARE

nbp NUMBER(3);

aucun_produit EXCEPTION;

BEGIN -- Comptage des produits

SELECT COUNT(*) INTO nbp FROM produit;

-- Test « il existe des produits » ou pas ?

IF nbp = 0 THEN

RAISE aucun_produit;

END IF;

EXCEPTION

WHEN aucun_produit THEN

RAISE_APPLICATION_ERROR(-20501,‘Erreur : table client vide’);

END;

39
DECLARE
unClient client%ROWTYPE ;
numClient [Link]%type ;
Y_ A_ EU_UNE_ERR EXCEPTION;

BEGIN
FOR numClient IN 1 .. 2 1 LOOP

BEGIN
SELECT * INTO unClient FROM CLIENT WHERE NC = numClient ;
INSERT INTO LECLIENT ( numcli , nomcli , prenomcli) VALUES ([Link] ,
[Link] , [Link] ) ;

EXCEPTION
WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE ( 'Personne n"a l
"identifiant ' ||numClient ) ;

WHEN TOO_MANY_ROWS THEN DBMS_OUTPUT.PUT_LINE (' Cette message

ne devrait jamais apparaître ! ' ) ;

WHEN OTHERS THEN RAISE Y_A_EU_UNE_ERR ;

END;
END LOOP ;
COMMIT;
EXCEPTION
WHEN Y_A_EU_UNE_ ERR THEN
DBMS_OUTPUT.PUT_LINE ( 'SQLCODE = ' || SQLCODE ) ;
DBMS_OUTPUT.PUT_LINE ( ' I l y a eu une Erreur ! ' ) ;
ROLLBACK;
END; 40
 Sous-programmes
 Fonctions

FUNCTION nomf (param1, param2…)

RETURN type_valeur_de_retour IS

-- Déclarations locales

BEGIN

-- Instructions

RETURN valeur_de_retour;

EXCEPTION

-- Traitement des exceptions

END;

41
 Sous-programmes
declare
amodb number;
c number;
d number;
FUNCTION PGDC ( a NUMBER, b NUMBER) RETURN NUMBER IS
begin
c:=a;
d:=b;
while d>0 loop
amodb:=c;
while amodb>=d loop
amodb:=amodb-d;
end loop;
c:=d;
d:=amodb;
end loop;
RETURN(c);
end;
Begin
DBMS_OUTPUT.PUT_LINE (PGDC(48,84));
End; 42
 Sous-programmes
 Procédures

PROCEDURE nomp (param1, param2…) IS

-- Déclarations locales

BEGIN

-- Instructions

EXCEPTION

-- Traitement des exceptions

END;

 Paramètres

nom_param [IN|OUT|IN OUT] TYPE

ex. resultat OUT REAL

43
 Sous-programmes

DECLARE

v_nb NUMBER;

PROCEDURE incr ( val IN OUT NUMBER) IS

BEGIN

val := val + 1 ;

END;

BEGIN

v_nb:=6;

inc(v_nb);

END;

44
 Exemple de programme PL/SQL
 -- Calcul du prix TTC des produits

 -- et recopie dans la table PRODTTC

DECLARE

nbp NUMBER(3);

aucun_produit EXCEPTION;

CURSOR calcul IS

SELECT np, prixuni*1.206 prixttc

FROM produit;

tuple calcul%ROWTYPE;

45
 Exemple de programme PL/SQL

BEGIN

-- Comptage des produits

SELECT COUNT(*) INTO nbp FROM produit;

-- Test « il existe des produits » ou pas ?

IF nbp = 0 THEN

RAISE aucun_produit;

END IF;

46
 Exemple de programme PL/SQL
-- Recopie des valeurs dans la table prodttc

FOR tuple IN calcul LOOP

INSERT INTO prodttc VALUES

([Link], [Link]);

END LOOP;

-- Validation de la transaction

COMMIT;

EXCEPTION

WHEN aucun_produit THEN

RAISE_APPLICATION_ERROR(-20501,

‘Erreur : table produit vide’);

END;

47
 Procédures et fonctions (1/5)

- Sous-programmes hébergés par la base de données

- Peuvent être appelées par des fonctions, des procédures ou des

programmes extérieurs

 Avantages

- compilations des procédures cataloguées (pas de recompilation lors de

l’exécution)

- Exécution immédiate de la procédure si elle est dans la SGA

- Partage de l’exécution d’une procédure par plusieurs utilisateurs (mémoire

partagée)

48
 Procédures et fonctions (2/5)

 Procédure
Synatxe :
Create [or Replace] Procedure Nom_procedure (parametre1, parametre2, …)
IS
-- Zones de déclarations (variables, curseurs, exceptions)
BEGIN
-- Traitements
EXCEPTION
-- Traitement des exceptions
END;
doivent se conforme à la synatxe suivante :
Nom_parametre [IN|OUT|IN OUT] Type_du_parametre

49
 Procédures et fonctions (3/5)

- IN : indique que la variable est passée en entrée

- OUT : indique que la variable est renseignée par la procédure puis renvoyée à l’appelant
- IN OUT : indique que la variable est passée en entrée et renseignée

 Fonctions
Synatxe :
Create [or Replace] Function Nom_fonction (parametre1, parametre2, …)
RETURN Type_Resultat IS
-- Zones de déclarations (variables, curseurs, exceptions)
BEGIN
-- Traitements
-- Clause RETURN
EXCEPTION
-- Traitement des exceptions
-- Clause RETURN
END;
50
 Procédures et fonctions (4/5)

create or replace procedure supp_art (nom_art in char) is

begin

delete from commande where refart=nom_art;

delete from produit where refart=nom_art;

end;

 Execution

EXECUTE supp_art (’AB01’) ;

51
 Procédures et fonctions (5/5)

CREATE OR REPLACE FUNCTION simple RETURN VARCHAR2 IS

BEGIN

RETURN 'Simple Function';

END simple;

 Execution

Select simple from dual;

52
 Les packages (1/6)

Permettent d’encapsuler des procédures, des fonctions, des curseurs et des variables

comme une unité dans la base de données.

 Avantages

- Meilleur moyen de structuration et d’organisation du processus de développement

- Facilité de gestion de privilèges/procédures et fonctions

- Meilleur mécanisme de gestion de la sécurité

- Amélioration des performances (le moteur charge en mémoire le package entier)

 Création d’un package

se fait en deux étapes :

- Création des spécifications du package

- Création du corps du package

53
 Les packages (2/6)

 Partie des spécifications : où sont déclarées les procédures, les fonctions, les
constantes, les variables et les exceptions accessibles par le public

CREATE [OR REPLACE] PACKAGE Nom_package

[IS|AS] Spécifications PL/SQL

Spécifications PL/SQL ::= déclaration des variables

| déclaration d’enregistrements

| déclaration des curseurs

| déclaration d’exceptions

| déclaration des fonctions

| déclaration des procédures

|…

54
 Les packages (3/6)

 Partie corps : définition des procédures (fonctions), des curseurs et des exceptions
qui sont déclarés dans les spécifications (possibilités de définir d’autres objets non
spécifiés dans la partie spécifications : objets privés).

CREATE [OR REPLACE] PACKAGE Body Nom_package

[IS|AS] Corps PL/SQL

 Remarques

- les objets déclarés au niveau des spécifications d’un package ou de son corps

persistent le long de la session et après le premier appel à ce package.

- On peut initialiser les variables explicitement en introduisant dans le corps d’un

package un bloc séparé par BEGIN et END

- Possibilité de nommer plusieurs procédures (fonctions) de la même façon.

55
 Les packages (4/6)

 Exemple : créer un package composé de 2 fonctions qui calculent le chiffre

d’affaires global et le chiffre d’affaires par client pour un employé donné, et une
procédure d’augmentation annuelle (10%) des salaires des employés.

 Partie des spécifications :

Create package Ges_employes IS

function CA(NO_EMP Number) Return Number;

function CA(NO_EMP Number, No_CLi Number) Return Number;

procedure Augmentation_ann;

END Ges_employes;

56
 Les packages (5/6)

 Partie corps :
Create package Body Ges_employes IS
Function CA(NO_EMP Number) Return Number IS
CA1 Number :=0;
begin
select sum(montant) into CA1
from Commande
where Employe_NO= No_Emp;
return (CA1);
end CA;
Function CA(NO_EMP Number, No_CLi Number) Return Number IS
CA2 Number :=0;
begin
select sum(montant) into CA2
from Commande
where Employe_NO = No_Emp and client_No=No_Cli;
return (CA2);
end CA;

57
 Les packages (6/6)

Procedure Augmentation_ann IS

Begin
update E_employe
set salaire = salaire * 1.1;
commit;

end Augmentation_ann;

End Ges_employes;

 Suppression d’un package :

DROP PACKAGE Nom_package;

58
 Les triggers (1/2)

 Runtime (procédure) déclenchée automatiquement par des événements liés à

des actions sur la base.
 Complètent les contraintes d’intégrité en permettant des contrôles et des traitements
plus complexes
(les contraintes d’intégrité sont beaucoup puissantes et très optimisées)

 Création d’un trigger

CREATE [OR REPLACE] TRIGGER Nom_trigger

{BEFORE|AFTER}
{INSERT|DELETE|UPDATE}
ON Nom_table
For each row
[WHEN condition ]
-- Bloc PL/SQL contenant le traitement à effectuer

59
 Les triggers (2/2)

 Variables spécifiques
:OLD.Nom_attribut : valeur de l’attribut avant la mise à jour
:NEW.Nom_attribut : valeur de l’attribut après la mise à jour

 Activation/Désactivation d’un trigger

Activation : Alter trigger Nom_trigger Enable;
Désactivation : Alter trigger Nom_trigger Disable;

 Activation/Désactivation de tous les triggers d’une table

Alter table Nom_table [Diasble|Enable] ALL Triggers;

 Suppression d’un trigger

Drop trigger Nom_trigger;

 Visualisation des triggers

user_triggers, all_triggers, dba_triggers

60
 Exemple1

CREATE TABLE etud(

id NUMBER(4),
Nom VARCHAR2(30),
note NUMBER(6)
);

create trigger lanote before UPDATE of note on etud

for each row
when(:[Link]>:[Link])
declare
evol number;
begin
evol := :[Link] - :[Link];
DBMS_OUTPUT.PUT_LINE(' evolution : ' || evol);
end;
61
 Exemple2

CREATE OR REPLACE TRIGGER sal_check

BEFORE INSERT OR UPDATE OF note ON etud FOR EACH ROW
DECLARE
minnote NUMBER :=0 ;
maxnote NUMBER :=20;
Hors_Plage exception;
BEGIN
IF ( :[Link]<minnote OR :[Link]>maxnote) THEN
RAISE Hors_Plage;
END IF;
EXCEPTION
WHEN Hors_Plage THEN
RAISE_APPLICATION_ERROR(-20322,‘Note invalide’);
END;

62
 Exemple 3

CREATE TABLE Resultat ( cne NUMBER, nome CHAR (20), prenom char(20), module
CHAR(20), note NUMBER );

CREATE TABLE Session2 (cne NUMBER, module CHAR(20) );

CREATE TRIGGER rattrapage

AFTER INSERT ON Resultat FOR EACH ROW

WHEN (:[Link] <10)

BEGIN

INSERT INTO Session2 values(:[Link], :[Link])

END;

63