PL/SQL

Procedural Language /SQL

 Langage PL/SQL
PLAN
Introduction
Avantages de PL/SQL
Les principales caractéristiques du PL/SQL
Les variables et les constantes
Les instructions de base
Les curseurs
Gestion des exceptions
Les procédures, les fonctions…
Les triggers
 Introduction
 PL/SQL (Procedural Language /SQL), L’extension procédurale proposée
par oracle pour SQL
 Il permet de combiner des requêtes SQL (SELECT, INSERT, UPDATE et
DELETE) et des instructions procédurales (boucles, conditions…),
 Créer des traitements complexes destinés à être stockés sur le serveur de
base de données (objets serveur),
 Comme on le sait, les structures de contrôle habituelles d’un langage (IF,
WHILE…) ne font pas partie intégrante de la norme SQL. Oracle les
prend en compte dans PL/SQL
 Introduction

Update produit set prix =2500 where id_fournisseur=1

Pour le fournisseur N° Code PL/SQL

Si Moyenne(prix)>2000 Procedure:
IF …. THEN
Augmenter le prix de 5%
sinon ELSEIF

Augmenter le prix de 3% ENDIF

 Avantage du langage PL/SQL
Ce langage propose des performances pour le traitement des transactions et
offre les avantages suivantes:

• Intégration complète du SQL

• Prise en charge de la programmation orientés objet (surcharge)
• Parfaite intégration avec Oracle et Java. PL/SQL est un langage
propriétaire d'Oracle, on peut lancer des sous-programme PL/SQL à
partir de Java et de même appeler des procédures Java à partir d’un bloc
PL/SQL
 Avantage du langage PL/SQL

Dans un environnement client serveur, chaque

instruction SQL donne lieu à l’envoi d’un
message du client vers le serveur suivi de la
réponse du serveur vers le client

Un bloc PL/SQL donne lieu à un seul échange

sur le réseau entre le client et le serveur. Les
résultat intermédiaires sont traités côté serveur et
seul le résultat final est retourné au client
 Langage PL/SQL

Construction de procédures ou fonctions stockées qui

améliorent le mode client-serveur par stockage des procédures
ou fonctions souvent utilisées au niveau serveur:

Gestion des erreurs

Construction de triggers (ou déclencheurs)
 Structure d’un bloc PL/SQL

Un programme ou une procédure PL/SQL est un

ensemble de un ou plusieurs blocs. Chaque bloc
comporte trois sections :

1. Section déclaration
2. Section corps du bloc
3. Section traitement des erreurs
 Section déclaration

 ELLe Contient la description des structures et

des variables utilisées dans le bloc

 Section facultative

 Commence par le mot clé DECLARE

 Section corps du bloc

 Contient les instructions du programme et

éventuellement, à la fin, la section de traitement des
erreurs

 Obligatoire

 Introduite par le mot clé BEGIN

 Se termine par le mot clé END

 Section traitement des erreurs

 Facultative

 Introduite par le mot clé EXCEPTION

 Syntaxe
DECLARE
déclaration
BEGIN
corps-du-bloc
EXCEPTION
traitement-des-erreurs
END;
/
 Exemple

SET SERVEROUTPUT ON
DECLARE
x VARCHAR2(10);
BEGIN
x := 'Bonjour';
DBMS_OUTPUT.PUT_LINE(x);
END;
/
 Exemple (2)

DECLARE
erreurEx EXCEPTION;
num [Link]%TYPE;
film [Link]%TYPE;
pb [Link]%TYPE;
BEGIN
…
 Exemple (2 suite)
….
BEGIN
SELECT numExemplaire, numFilm, probleme INTO num, film,
pb
FROMexemplaire WHEREnumExemplaire = 1;
IFprobleme ISNOTNULL THEN
RAISEerreurEx;
ENDIF;
DBMS_OUTPUT.PUT_LINE(num||' OK');
…..
 Exemple (2 suite )

…..
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('numéro inconnu');
WHEN erreurEx THEN DBMS_OUTPUT.PUT_LINE(num ||
'problème');
END;
/
 Types de variables

 Variables scalaires

 Types composés
• Enregistrement (record)
• Table
 Variables scalaires

 Types issus de SQL : CHAR,

NUMBER, DATE, VARCHAR2
 Types PL/SQL : BOOLEAN, SMALLINT,
BINARY_INTEGER, DECIMAL, FLOAT,
INTEGER, REAL, ROWID
 Les variables hôtes sont préfixées par
« : » ce sont des variables définies dans
l’environnement extérieur au bloc et utilisées
dans le bloc
Déclaration des Variables scalaires

 nom-variable nom-du-type;
• Exemple :
x VARCHAR2(10);

 nom-variable [Link]-colonne%TYPE;
• Exemple :
film [Link]%TYPE;
Exemple
Déclaration pour un enregistrement

 Soit par référence à une structure de table ou de

curseur en utilisant la notation %ROWTYPE :
nom-variable nom-table%ROWTYPE;
nom-variable nom-curseur%ROWTYPE;
Déclaration pour un enregistrement

 Soit par énumération des rubriques qui la composent. Cela

se fait en deux étapes :
• Déclaration du type enregistrement
TYPE nom-du-type-record IS RECORD (
nom-attribut1 type-attribut1,
nom-attribut2 type-attribut2, ...);
• Déclaration de la variable de type enregistrement
nom-variable nom-du-type-record;
 Exemple
DECLARE
TYPE revenu IS RECORD (
Nom [Link]%type,
Salaire Number (9,2));
Rev_pilote revenu;
BEGIN
DBMS_OUTPUT.PUT_LINE (rev_pilote.nom || ' ' ||
rev_pilote.salaire);
END;
/
 Exemple
Accept nom_entréPrompt ‘ Entrez lenom dupilote’
DECLARE
TYPE revenu IS RECORD (
Nom [Link]%type:= ‘&nom_entré’,
Salaire Number (9,2):= 8000.00);
Rev_pilote revenu;
BEGIN
DBMS_OUTPUT.PUT_LINE (' le nom du pilote est: ' ||
rev_pilote.nom);
END;
 Tables

 Structure composée d’éléments d’un même type

scalaire
 L’accès à un élément de la table s’effectue grâce
à un indice, ou clé primaire
 Cet index est déclaré de type BINARY_INTEGER
(valeurs entières signées)
Déclaration pour une table

 Deux étapes :
• Déclaration du type de l’élément de la table
• Déclaration de la variable de type table
Déclaration pour une table

 Déclaration du type de l’élément de la table :

TYPE nom-du-type-table
ISTABLEOF type-argument
INDEXBYBINARY_INTEGER;

 Déclaration de la variable de type table :

nom-variable nom-du-type-table;
 Exemple
DECLARE
TYPE tabNomIS TABLE OF VARCHAR2(20)
INDEX BY BINARY_INTEGER;
tableNomtabNom;
i BINARY_INTEGER;
BEGIN
tableNom(5) := 'Dupont';
i := 10;
tableNom(i) := 'Dupond';
END;
Variables (scalaires ou composées)

 Valeur initiale :
nom-variable nom-type := valeur;
 Constante :
nom-variable nom-type DEFAULT valeur;
ou
nom-variable CONSTANTnom-type := valeur;
Variables (scalaires ou composées)

 Visibilité : une variable est utilisable dans le bloc

où elle a été définie ainsi que dans les blocs
imbriqués dans le bloc de définition, sauf si elle est
redéfinie dans un bloc interne
Conversion de type

 Explicite avec

TO_CHAR, TO_DATE, TO_NUMBER,

RAWTOHEX, HEXTORAW

 Implicites, par conversion automatique

Les instructions
 Les instructions

1. Affectations
2. Instructions du langage SQL : CLOSE, COMMIT, DELETE,
FETCH, INSERT, LOCK, OPEN, ROLLBACK,
SAVEPOINT, SELECT, SET TRANSACTION, UPDATE
3. Instructions de contrôle itératif ou répétitif
4. Instructions de gestion de curseurs
5. Instructions de gestion des erreurs
 L’Affectation

 Opérateur d’affectation :=

 Option INTO dans un ordre SELECT

 Instruction FETCH avec un curseur

 Exemple
DECLARE
Type t_nom istable of Char(35)
INDEX BY BINARY_INTEGER;
tableNomt_nom;
I BINARY_INTEGER;
BEGIN
tableNom(5) := ‘Arthur';
i := 10;
tableNom(i) := ‘Arthur';
END;
 Exemple
DECLARE
U_nom [Link]%TYPE;
U_sal [Link]%TYPE;
BEGIN
SELECTnom, sal INTOu_nom, u_sal FROMPilote
WHERE nopilote=‘7937’;
DBMS_OUTPUT.PUT_LINE(u_nom ||' '|| u_sal);
END;
 Exemple
DECLARE
TYPE t_pilote IS RECORD (
nom_pilote [Link]%TYPE,
Revenu_pilote Number (5,2));
Employé t_pilote;
BEGIN
Employé.nom_pilote := ‘DUPUY’;
Employé.revenu_pilote :=12345.00;
END;
 Exemple
DECLARE
TYPE t_emprec IS RECORD (
r_nom [Link]%TYPE,
r_sal [Link]%TYPE);
Emprec t_emprec;
BEGIN
SELECTnom, sal INTOemprecFROMPilote WHEREnopilote
=‘7937’;
DBMS_OUTPUT.PUT_LINE(emprec.r_nom ||' '|| emprec.r_sal);
END;
Structures de contrôle

 Structure alternative

 Structure répétitives
Structures alternatives

IF condition THEN instructions;

END IF;

IF condition THEN instructions;

ELSE instructions; END IF;

IF condition THEN instructions;

ELSIF condition THEN instructions;
ELSE instructions; END IF;
Exemple
Structures répétitives

LOOP instructions; END LOOP; Cette boucle est infinie: il

faut utiliser EXIT pour en
LOOP instructions; ... sortir
EXIT WHEN condition; ...
END LOOP;
LOOP ...
IF condition THEN EXIT; END IF;
... END LOOP;
Exemple
Structures répétitives
L’instructionForcontrôlelenombre d’exécutionsdes instructionsdelastructurerépétitive
par incrémentation ettestd’unevariableindice
FOR variable-indice IN[REVERSE] val-début .. val-fin
LOOP instructions; END LOOP;

 variable-indice est une variable locale (locale à la boucle) non déclarée

 val-début et val-fin sont des variables locales déclarées et initialisées

ou alors des constantes

 le pas est -1 si REVERSE est présent, sinon il est égal à +1

 Structures répétitives

L’instruction While répète les instructions de la structure

répétitive tant que la condition à la valeur est Vrai

WHILE condition
LOOP
instructions;
END LOOP;
Exemple
Avec la boucle While Avec la boucle For
 L’interaction avec la base de données

La clause INTO en PL/SQL

La clause INTO permet de passer des valeurs d’une table
dans des variables
 L’interaction avec la base de données

Mettre à jour des données

Ex: Augmenter le salaire de tous les pilotes ayant le salaire
entre 19000 et 23000
 L’interaction avec la base de données

Supprimer des données

Ex: supprimer les lignes qu’on nombre d’heure de vol est
<600 à partir de la table Avion
 L’interaction avec la base de données

• Initialiser une transaction avec la première instruction

LMD suivant COMMIT ou ROLLBACK
• Utiliser les instructions SQL COMMIT et ROLLBACK pour
mettre fin explicitement à une transaction
Les curseurs

Curseur
 Les curseurs
 Dès l’instant où on exécute une requête SQL, il y a création d’un
curseur.

 Un curseur est une variable qui pointe vers le résultat d’une requête
SQL,

 Un curseur est une zone de travail de l’environnement utilisateur qui

contient les informations relatives à l’instruction ou requête SQL :
• Le texte source de l’ordre SQL
• Le texte «compilé» de l’ordre SQL
• Un tampon pour une ligne du résultat
• Le statut (cursor status)
• Des informations de travail et de contrôle
Curseurs explicites

Variable

- Créer une - Exécute - Charger la - Tester l’existence - Libérer

zone mémoire - Identifier ligne en de la ligne l’ensemble
SQL l’ensemble cours dans - Si des lignes actif
actif des variables existent, revenir
à FETCH
Curseurs explicites

 Obligatoires pour un SELECT

susceptible de produire plusieurs
lignes résultat
 Quatre étapes :
1) Déclaration du curseur
2) Ouverture du curseur
3) Traitement des lignes du résultat
4) Fermeture du curseur
Déclaration du curseur

 Association d’un nom de curseur à une

requête SELECT
 Se fait dans la section DECLARE d’un bloc
PL/SQL
CURSOR nom-curseur IS requête;
 Un curseur peut être paramétré :
CURSOR nom-curseur (nom-p1 type-p1
[:= val-défaut], ...) IS requête;
 Exemple

DECLARE
Cursor C1 IS select nom from pilote
where sal>1000;

Cursor C2 (psal Number (7,2), pcom number(7,2)) IS

select ename from emp
where sal> psal and comm>pcom;
Ouverture d’un curseur

 Alloue un espace mémoire au curseur et

positionne les éventuels verrous
OPENnom-curseur;
ou
OPENnom-curseur (liste-paramètres-effectifs);
 Pour les paramètres, association par position
ou par nom sous la forme
paramètre-formel (Lors de la déclaration)=>
paramètre-réel(Lors de l’ouverture)
 Exemple

 OPEN C1;

 OPEN C2 (1600, 1800); Par position

 OPEN C2 (q => 1800, p => 1600); Par nom

Traitement des lignes

Les lignes obtenues par l’exécution de la requête SQL sont

distribuées une à une par l’exécution d’un ordre FETCH . Pour
chaque ligne, cette instruction transfère les valeurs des attributs
projetés par l’ordre Select dans des variables PL/SQL.
La syntaxe utilisés est la suivante:

FETCHnom-curseur INTOliste-variables;
ou
FETCHnom-curseur INTOnom-enregistrement;
Exemple
Exemple 2
Exemple 3
Forme syntaxique condensée avec la boucle FOR

La forme condensée utilise la structure For pour distribuer

les lignes résultats selon la construction suivante:

DECLARE
CURSOR nom_curseur IS requête;
Begin
for nom_enregistrement IN nom_curseur
[(paramètres effectifs)]
Loop
Traitement;
end loop;
end;
Forme syntaxique condensée avec la boucle FOR
Statut d’un curseur
Pour Obtenir les informations d’état concernant un curseur:
NomCurseur%Attribut
Modification des données

 Se fait habituellement avec INSERT, UPDATE

ou DELETE
 Possibilité d’utiliser la clause FOR UPDATE dans la
déclaration du curseur. Cela permet d’utiliser la
clause
CURRENT OF nom-curseur
dans la clause WHERE des instructions UPDATE et
DELETE. Cela permet de modifier la ligne du curseur
traitée par le dernier FETCH, et donc d’accélérer
l’accès à cette ligne
Exemple

Dans une autre session

Exemple
Gestion des erreurs (erreurs standard)
Le langage PL/SQL offre au développeur un mécanisme de gestion des
exceptions. Il permet de préciser la logique du traitement des erreurs survenues
dans un bloc PL/SQL. Il s’agit donc d’un point clé dans l’efficacité du langage qui
permettra de protéger l’intégrité du système. Il existe deux types d’exception:

- Interne: les exceptions internes sont générées par le moteur du système

(division par zéro, connexion non établie, table inexistante, privilèges
insuffisants, mémoire saturée,….),

- Externe: les exceptions externes sont générées par l’utilisateur

Gestion des erreurs (erreurs standard)
Les erreurs Oracle générées par le noyau sont numérotées (ORA-xxxxx). Il a donc
fallu établir une table de correspondance entre les erreurs ORACLE et des noms
d’exceptions
Voici quelques exemple d’exceptions prédéfinis et des codes correspondants:
Gestion des erreurs (erreurs standard)
Gestion des erreurs (erreurs standard)

 La nature d’une erreur peut être connue par

appel au fonctions SQLCODE et SQLERRM
 SQLCODE renvoie le statut d’erreur de la
dernière instruction SQL exécutée (0 si n’y a pas
d’erreur)
 SQLERRM renvoie le message d’erreur
correspondant à SQLCODE
Gestion des erreurs (erreurs standard)
Gestion des erreurs (erreurs standard)
Exception utilisateur
DECLARE
Nom_erreur EXCEPTION;
BEGIN
...
IF (anomalie) THEN
RAISE Nom_erreur ;
END IF;
...
EXCEPTION
WHENNom_erreur THENtraitement;
….
End;
Exemple
Erreurs anonymes

 Pour les codes d’erreur n’ayant pas de nom

associé, il est possible de définir un nom
d’erreur (code entre -20000 et -20999)
Erreurs anonymes
Les procédures
L’objectif

Définition d’une procédure

Créer une procédure
Faire la distinction entre les paramètres formels et les paramètres
réels
Répertorier les fonctions des différents modes des paramètres
Créer des procédures avec des paramètres
Appeler une procédure
Traiter des exceptions dans les procédures
Supprimer une procédures
Définition d’une procédure

 une procédure est un type de sous-programme qui exécute une

action
Une procédure peut être stockée en tant qu’objet de schéma dans la
base de données en vue d’exécutions répétées
Pourquoi les procédures?

 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
Mieux garantir l’intégrité des données (encapsulation des données par les
procédures)
Sécuriser l’accès aux données (accès à certaines tables seulement à travers les
procédures)
 Optimiser le code (les procédures sont compilées avant l’exécution du
programme et elles sont exécutées immédiatement si elles se trouvent dans la
SGA (Zone mémoire gérée par ORACLE). De plus une procédure peut être
exécutée par plusieurs utilisateurs.
Syntaxe pour la création de procédures

CREATE [OR REPLACE] PROCEDURE nom_procedure

[(parameter1 [mode1] datatype1,
[(parameter2 [mode2] datatype2,
...)]
IS
PL/SQL BLOCK;

 L’option REPLACE indique que, si la procédure existe,

elle sera supprimée et remplacée par la nouvelle
version créée avec l’instruction
 Le bloc PL/SQL commence par Begin ou par la
déclaration de variables locales et se termine pas end
ou par END Procedure_name
Paramètres Formels/Réels

 Les paramètres Formels sont des variables déclarées

dans la liste de paramètres d’une spécifications de
sous-programme
Exemple:

 Les paramètres réels sont des variables ou des

expressions référencées dans la liste de paramètres
d’un appel de sous-programme
Exemple:
Modes des paramètres des procédures

RQ: Datatype ne peut être que la

définition %type ou %Rowtype, ou
un type de données explicite sans
spécifications de taille
Modes des paramètres des procédures

Par défaut, le paramètre IN est transmis par référence et pour les

paramètres OUT et IN OUT sont transmis par valeur
Exemple de paramètre IN:
Exemple de paramètre OUT:
Exemple de paramètre IN OUT:
Déclarer des sous-programmes:
Appeler une procédure depuis un bloc PL/SQL anonyme:
Appeler une procédure depuis une autre procédure:
Appeler une procédure depuis une autre procédure:
Appeler une procédure depuis une autre procédure:
Question:

Créer une procédure qui affiche le nombre des

employées qu’on le department_id=30
Exemples:
Exemples:
Exemples:
Exemples avec exception
Supprimer une procédure dans la base de données:

Syntaxe:

DROP PROCEDURE procedure_name;

Exemple:
DROP PROCEDURE Modifier_salaire;
Les fonctions
Les objectifs:

• Décrire les différentes utilisations des fonctions

• Créer des fonctions stockées
• Appeler une fonction
• Supprimer une fonction
• Faire la comparaison entre une procédure et
une fonction
Introduction

 Une fonction est un bloc PL/SQL nommé qui renvoie

une valeur
 Une fonction peut être stockée en tant qu’objet de
schéma dans la base de données en vue d’exécutions
répétées
 Une fonction est appelée dans une expression
Syntaxe de création de la fonction

CREATE [ORREPLACE] FUNCTION nom_fonction

[(argument [IN] type, ...)]
RETURN type-retour
[ IS | AS]
bloc-fonction;
 Les paramètres sont forcément en entrée (IN)
 Dans le bloc-fonction :
RETURN nom-variable;
Exemple
Exécution des fonctions
Appeler une fonction dans une expression PL/SQL
Créer une variable destinée à recevoir la valeur renvoyée
Exécuter la fonction
La valeur renvoyée pas l’instruction RETURN sera placée
dans la variable

Remarque: Evitez d’utiliser les modes OUT et IN OUT avec

les fonctions
Exécution des fonctions
Les avantages des fonctions définies par l’utilisateur dans les
expressions SQL

Elles complètent le langage SQL en permettant de réaliser des

traitements qui seraient trop complexes, voire impossibles en
SQL
Utilisées dans la clause WHERE pour filtrer les données, elles
peuvent s’avérer plus efficaces qu’un filtrage au sein de
l’application
Elles permettent de manipuler les chaînes de caractères
Exemple
Exemple
L’emplacement d’appel des fonctions définies par l’utilisateur

 Liste de sélection d’une commande SELECT

 Condition des clauses WHERE et HAVING

 Clause Values de la commande INSERT

Clause SET de la commande UPDATE

Restrictions relatives à l’appel des fonctions à partir
d’expression SQL

Pour pouvoir être appelée depuis des expressions SQL, une fonction définie par
l’utilisateur doit:
 être une fonction stockée (ce n’est pas le cas pour les procédures stockées)
En tant que paramètres, accepter uniquement des types de données SQL
valides (et non des types spécifiques au langage PL/SQl
 Renvoyer des types de données SQL valide et non des types spécifiques au
langage PL/SQL
Les fonctions appelées depuis des expressions SQL (SELECT, UPDATE, DELETE
en parallèle) ne peuvent modifier aucune tables de la BDD
Supression d’une procédure/fonction stockée

DROP FUNCTION nom_fonction;

EXEMPLE:

DROP FUNCTION get_salaire;

Comparer les procédures et les fonctions
Procédures Fonctions
S’exécutent en tant qu’instruction Sont appelées dans une expressions
PL/SQL
Ne contiennent pas de clause RETURN Doivent contenir une clause RETURN
dans l’entête dans l’entête

Peuvent transférer zéro, une ou Doivent renvoyer une seule valeur

plusieurs valeurs

Peuvent contenir une instruction Doivent contenir au moins une

RETURN instruction RETURN
Exercices
1. Ecrire une procédure qui insère le total des achats de chaque client
dans une table déjà crée Table_CA_Client (numeroclient number, total
number)
2. Ecrire une fonction qui calcule le total des achats d’un client donné
3. Ecrire une procédure qui supprime les clients qui n’ont pas réalisé un
total achat>3000dhs
4. Ecrire une fonction qui renvoie le nombre des produits achetés par
un client donné (le numéro du client est entré comme paramètre)
5. Ecrire une procédure qui stocke les noms des clients qui ont acheté
au minimum 2 produits, dans un tableau indexé par des entiers
La procédure doit utiliser :
- Un curseur pour parcourir les noms des clients
- Un tableau contenant les noms des clients qu’ont acheté au
minimum 2 produits
- Les exceptions pour gérer les erreurs (des données inexistantes,
curseur incorrect,…).
Les Packages
Présentation des packages

Les packages:
 Regroupent des types PL/SQL, des éléments et des sous-programmes
présentant une relation logique
 Sont constitués de deux éléments:

 Spécification (Déclaration)

 Corps (définition)
Ne peuvent pas être appelés, paramétrés ou imbriqués
Permettent au serveur Oracle de lire simultanément plusieurs objets en
mémoire
Composants d’un package

Spécification du
package

Corps du
package
Développer un package

L’enregistrement du texte de l’instruction CREATE PACKAGE dans deux fichiers

SQL distincts facilite les modifications ultérieures du package

 Spécification: CREATE PACKAGE

 Corps: CREATE PACKAGE BODY

Créer la spécification du package

Syntaxe:
CREATE [Or REPLACE] PACKAGE package_name
IS/ AS
Définition des types utilisés dans le package;
prototype de toutes les procédures et fonctions du package;
END package_name;

 L’option Relpace supprime et recrée la spécification du package

 Toutes les structures déclarées dans une spécification de package peuvent
être visibles pas les utilisateurs disposant de privilèges sur le package
Créer la spécification du package

Spécification du
package
Créer la spécification du package

Spécification du package:

• G_var est une variable globale dont la valeur d’initialisation est 0,10
• Public_procedure est une procédure publique implémentée dans le
corps du package
Créer le corps du package

Syntaxe:
CREATE [Or REPLACE] PACKAGE BODY package_name
IS/ AS
Déclaration de variables privées;
END package_name;

 L’option Relpace supprime et recrée le corps du package

 Les identificateurs définis exclusivement dans le corps du package sont des
structures privées. Ils ne sont pas visibles à l’extérieur du corps du package
 Toutes les structures privées doivent être déclarées avant d’être utilisées
dans les structures publiques
Composants d’un package

Spécification du
package

Corps du
package
Corps d’un package
Exemple:
Corps d’un package
Exemple:
Exemple:
Appeler des structures de package
Appeler une procédure de package depuis SQL*Plus

EXECUTE nom_package.nom_procedure (valeurs paramètres…)

Appeler une procédure de package dans un autre schéma

EXECUTE nom_utilisateur.nom_package.nom_procedure (valeurs

paramètres…)
 Déclarer un package sans corps

CREATE OR REMPLACE PACKAGE global_var

IS
PI constant Number := 3,14;
Age constant Number := 20;
R constant Number := 10;
End globale_var;

Execute DMNS_output.put_line(‘age=‘|| globale_var.age);

Execute DMNS_output.put_line(‘Air d’un disque=‘||

2*globale_var.PI*globale_var.R);
Référencer une variable publique depuis une procédure
autonome
 Supprimer des packages

La syntaxe de suppression de la spécification et le corps d’un package:

DROP PACKAGE package_name;

La syntaxe de suppression le corps d’un package:

DROP PACKAGE BODY package_name;
 Suppression des packages

Pour afficher les informations sur un package:

Select * from user_objects
where object_name=‘nom package’;

Pour afficher le code source d’une spécification

Select * from user_souce
Where name=‘nom package’ and type=‘PACKAGE’ ;

Pour afficher le code source d’un corps (Body)

Select * from user_souce
Where name=‘nom package’ and type=‘PACKAGE BODY’ ;
 Avantages liés aux packages
 Modularité : Encapsule les structures associées
 Conception simplifiée des applications : La spécification et le
corps sont codés et compilés séparément
 Masquage des informations:
• Seules les déclarations contenues dans la spécification du
package sont visible et accessibles aux applications
• Les structures privées du corps du package sont masquées et
inaccessibles
• L’ensemble du code est masqué dans le corps du package
 Avantages liés aux packages

 Performances accrues :
• L’ensembles du package est chargé en mémoire la première
fois que celui-ci est référencé
• Une seule copie est chargée en mémoire pour l’ensembles
des utilisateurs
 Surcharge: plusieurs sous-programmes portant le même nom
 Exercices
6. Ecrire un package contenant la spécification suivante :
- Une procédure « add-client » pour insérer les informations
d’un client
- Une procédure « add_client » pour insérer les informations
d’un client sauf la ville
- Une procédure « get_client » qui affiche les informations d’un
client donné
- Une fonction « get_age » qui retourne l’age d’un client donné
- Une procédure « delete_client » qui supprime un client donné
- Une procédure « delete_client » pour supprimer les clients
dont l’age est dans la liste {20,30,40}
7. Implémenter le corps de toutes les spécifications
Les déclencheurs ou Triggers
 Les objectifs

 Décrire différents types de déclencheurs/trigger

 Décrire les déclencheurs de base de données et leur utilisation

 Créer des déclencheurs de base de données

 Décrire les règles d’activation des déclencheurs de base de

données

 Supprimer des déclencheurs de base de données

Règles relatives à la conception de déclencheurs

 Il est conseillé de concevoir des déclencheurs pour:

• exécuter des actions associées
•Centraliser des opérations globales
 Si le code PL/SQL est très long, Créer des procédures stockées, et
les appeler dans un déclencheur

 L’utilisation excessive de déclencheurs peut entraîner des

interdépendances complexes dont la gestion peut s’avérer difficile
dans les applications volumineuses
Les éléments d’un déclencheur
Créer des déclencheurs LMD

Une instruction de déclenchement comporte les éléments suivants:

1. Moment du déclenchement
• BEFORE (Avant), AFTER (Après),
2. Evénement déclencheur : Insert, UPDATE ou DELETE
3. Nom de la table: sur la table ou la vue
4. Type de déclencheur: ligne ou instruction
5. Clause WHEN: Condition restrictive par ligne
6. Corps du déclencheur: bloc PL/SQL
Composants des déclencheurs LMD

1. Moment
Moment de déclenchement: à quel moment le déclencheur doit-il
s’exécuter?
 BEFORE: exécution du corps du déclencheur avant le
déclenchement de l’événement LMD sur une table
 AFTER: exécution du corps du déclencheur après le
déclenchement de l’événement LMD sur une table
Composants des déclencheurs LMD

2. Evénement
Evénement utilisateur déclencheur: quelle instruction LMD
entraîne l’exécution du déclencheur?
Vous pouvez utiliser les instructions suivantes
 INSERT
 UPDATE
 DELETE
Composants des déclencheurs LMD
3. Type
Type de déclencheur: le corps du déclencheur doit-il s’exécuter une
seule fois ou pour chaque ligne concernée par l’instruction?

 Instruction: le corps du déclencheur s’exécute une seule fois

pour l’événement déclencheur. Il s’agit du comportement par
défaut. Un déclencheur sur instruction s’exécute une fois, même
si aucune ligne n’est affectée

 Ligne: le corps du déclencheur s’exécute une fois pour chaque

ligne concernée par l’événement déclencheur. Un déclencheur
sur ligne ne s’exécute pas si l’événement déclencheur n’affecte
aucune ligne
Composants des déclencheurs LMD
4. Corps

Corps du déclencheur: Quelle action le déclencheur doit-il

effectuer?
Le corps du déclencheur est un bloc PL/SQL ou un appel de
procédure (PL/SQL ou Java)

Remarque:

 Les déclencheurs sur ligne utilisent des noms de corrélation

pour accéder aux anciennes ou nouvelles valeurs de colonne de
la ligne en cours de traitement

 La taille d’un déclencheur est limitée à 32 Ko

Séquence d’exécution
Manipulation concerne une seule ligne
Séquence d’exécution
Manipulation concerne une plusieurs lignes
Création de déclencheurs sur une instruction LMD
Syntaxe:

Remarque: Les noms des déclencheurs doivent être unique au

sein d’un même schéma
Création de déclencheurs sur une instruction LMD
Syntaxe:

Remarque: En cas d’échec d’un déclencheur de base de données,

l’instruction de déclenchement est annulée
Prédicats conditionnels
Création de déclencheurs sur ligne LMD
Syntaxe:

Utilisation: New et OLD

• Si nous ajoutons un client dont le nom est Ahmed alors nous
récupérons ce nom grâce à la variable: [Link]
• Dans le cas de suppression ou modification, les anciennes
valeurs sont dans la variable: [Link]
Création de déclencheurs sur ligne LMD
Exemple:

Vous ne pouvez pas modifier le salaire de cet employé

Utilisation des qualificatifs OLD et NEW
Restreindre l’action d’un déclencheur sur ligne
Différences entre les déclencheurs et les procédures
Tables système

 USER_TRIGGERS

 ALL_TRIGGERS

 DBA_TRIGGERS
Suppression

DROP TRIGGER nom-déclencheur;

Exemple:

Drop TRIGGER secure_emp;

Remarque: lorsqu’une table est supprimée, tous ses

déclencheurs sont également supprimés
Activation/Désactivation des déclencheurs

Désactiver ou réactiver un déclencheurde basede données:

 ALTERTRIGGER nom_déclencheur DISABLE / ENABLE;

Désactiver ou réactiver tousles déclencheursd’unetable :

 ALTER TABLE nom_table DISABLE / ENABLE ALL

TRIGGERS;
Restrictions

• Un déclencheur ne peut modifier la valeur d’un

attribut déclaré avec l’une des contraintes PRIMARY
KEY, UNIQUE ou FOREIGN KEY

• Un déclencheur ne peut pas consulter les données

d’une table en mutation : une table en mutation est
une table directement ou indirectement concernée par
l’événement qui a provoqué la mise en œuvre du
déclencheur