Royaume du Maroc
Université Hassan Premier
Faculté des Sciences et Techniques De Settat
Bases de données
Chapitre 6 :PL/SQL
Kaoutar ERRAKHA Année Universitaire : 2022_2023
[Link]@[Link]
PLAN
Introduction
Développement bloc simple
Traitements Conditionnels et Traitements Répétitifs
Curseurs
Gestion des Erreurs (EXCEPTION)
PROCEDURES FONCTIONS ET PACKAGES
Cours Base de donnèes 2
•3
Curseurs
CURSEUR
Qu’est ce qu’un Curseur ?
Une zone mémoire de taille fixe contenant le résultat d'une requête. Utilisée pour
interpréter et analyser les ordres SQL.
Le nombre de curseurs ouverts simultanément est défini par le paramètre
OPEN_CURSORS dans le PFILE de la base.
Cours Base de donnèes 4
CURSEUR
Cours Base de donnèes 5
CURSEUR
Cours Base de donnèes 6
CURSEUR
L'utilisation d'un curseur nécessite 4 étapes :
1. Déclaration du curseur : Section DECLARE
2. Ouverture du curseur : Section BEGIN
3. Traitement des lignes : Section BEGIN
4. Fermeture du curseur : Section BEGIN OU EXCEPTION
Cours Base de donnèes 7
CURSEUR
La déclaration d'un curseur
La déclaration du curseur permet de stocker l'ordre Select dans le curseur.
Le curseur se définit dans la partie Declare d'un bloc PL/Sql.
Cursor nomcurseur IS Requete_SELECT ;
Cours Base de donnèes 8
CURSEUR
LES CURSEURS EXPLICITES
Cours Base de donnèes 9
CURSEUR
L'ouverture du curseur
L’ouverture du curseur réalise :
1. l'allocation mémoire du curseur
2. l'analyse sémantique et syntaxique de l'ordre
3. le positionnement de verrous éventuels (si select for update...)
C’est seulement à l’ouverture du curseur que la requête SQL s’éxecute.
L'ouverture du curseur se fait dans la section Begin du Bloc.
OPEN nomcurseur ;
10
Cours Base de donnèes 10
CURSEUR
L'ouverture du curseur
Declare
Cursor DEPT10 is
select ename, sal from emp where deptno=10 order by
sal ;
Begin
...Open DEPT10;
.....
End ;
11
Cours Base de donnèes 11
CURSEUR
Traitement des lignes
Après l'exécution du Select
Les lignes ramenées sont traitées une par une,
La valeur de chaque colonne du Select doit être stockée dans une variable réceptrice
définie dans la partie Declare du bloc.
Le fetch ramène une seule ligne à la fois,
Pour traiter n lignes il faut une boucle.
FETCH nomcurseur INTO liste_variables ou Nom_enregistrement;
12
Cours Base de donnèes 12
CURSEUR
13
Cours Base de donnèes 13
CURSEUR
Attributs d’un Curseur Explicite
Obtenir des informations sur le curseur en utilisant les attributs de curseur.
14
Cours Base de donnèes 14
CURSEUR
La fermeture du curseur
Après le traitement des lignes, l'étape de fermeture permet d'effectuer la libération de la
place mémoire.
CLOSE nomcurseur; Close dept10 ;
15
Cours Base de donnèes 15
CURSEUR
Boucle LOOP pour un curseur
16
Cours Base de donnèes 16
CURSEUR
Boucle FOR pour un curseur
17
Cours Base de donnèes 17
CURSEUR
MODIFICATION DE DONNEES
Les modifications de données s’effectuent normalement par les instructions SQL :
INSERT, UPDATE et DELETE
PL/Sql permet la possibilité d’utiliser l’option CURRENT OF nom_curseur dans la
clause WHERE des instructions UPDATE et DELETE.
Cette option permet de modifier ou de supprimer la ligne distribuée par la commande
FETCH.
Pour utiliser cette option, il faut ajouter la clause FOR UPDATE à la fin de la
définition du curseur.
Cette clause FOR UPDATE [OF col1, col2,…] bloque toute la ligne ou seulement les
colonnes spécifiées Les autres transactions ne pourront modifier les valeurs tant que le
curseur n’aura pas quitté cette ligne
18
Cours Base de donnèes 18
CURSEUR
19
Cours Base de donnèes 19
CURSEUR
Clause WHERE CURRENT OF – Exemple
Modifier les lignes correspondantes à un critère avec un curseur.
20
Cours Base de donnèes 20
•4
• GESTION DES ERREURS
(EXCEPTION)
GESTION DES ERREURS (EXCEPTION)
Exception
• Une exception est une erreur qui survient durant une exécution
2 types d’exception :
Interne :execption définie par le système
Externe :exception définie par l’utilisateur
22
Cours Base de donnèes 22
GESTION DES ERREURS (EXCEPTION)
Exception
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,
espace disque insuffisant, ...).
Une erreur interne est produite quand un bloc PL/Sql ne suit pas une règle
d'Oracle ou dépasse une limite dépendant du système d'exploitation.
Chaque erreur ORACLE correspond un code SQL (SQLCODE)
Les exceptions externes sont générées par l’utilisateur.
23
Cours Base de donnèes 23
GESTION DES ERREURS (EXCEPTION)
Intercepter les Exceptions - Syntaxe
24
Cours Base de donnèes 24
GESTION DES ERREURS (EXCEPTION)
Règles pour intercepter les Exceptions
Le mot clé EXCEPTION débute la section de la gestion des exceptions
Plusieurs exceptions sont permises (définie et prédéfinie)
Une seule exception est exécutée avant de sortir d’un bloc
WHEN OTHERS est la dernière clause
Intercepte toutes les exceptions non gérées dans la même section d’exception
Utilisez le gestionnaire d’erreurs OTHERS et placez le en dernier lieu après tous les
autres gestionnaire d’erreurs, sinon il interceptera toutes les exceptions mêmes celle qui
sont prédéfinies
25
Cours Base de donnèes 25
GESTION DES ERREURS (EXCEPTION)
Les erreurs internes d’Oracle prédéfinies
26
Cours Base de donnèes 26
GESTION DES ERREURS (EXCEPTION)
Les erreurs internes d’Oracle prédéfinies
CURSOR_ALREADY_OPEN : tentative d’ouverture d’un curseur déjà ouvert.
DUP_VAL_ON_INDEX: insertion d’une ligne en doublon
INVALID_CURSOR : opération incorrecte sur un curseur, comme par exemple la
fermeture d’un curseur qui n’a pas été ouvert.
LOGIN_DENIED : connexion à la base échouée car le nom utilisateur ou le mot de
passe est invalide.
NO_DATA_FOUND : déclenché si la commande SELECT INTO ne retourne aucune
ligne ou si on fait référence à un enregistrement non initialise d’un tableau PL/SQL.
NOT_LOGGED_ON : tentative d’accès à la base sans être connecté.
PROGRAM_ERROR : problème général dû au PL/SQL
27
Cours Base de donnèes 27
GESTION DES ERREURS (EXCEPTION)
Les erreurs internes d’Oracle prédéfinies
ROWTYPE_MISMATCH : survient lorsque une variable curseur d’un programme hôte
retourne une valeur dans une variable curseur d’un bloc PL/SQL qui n’a pas le même
type.
STORAGE_ERROR : problème de ressources mémoire dû à PL/SQL.
TIMEOUT_ON_RESOURCE : dépassement du temps dans l’attente de libération des
ressources (lié aux paramètres de la base).
TOO_MANY_ROWS : la commande SELECT INTO retourne plus d’une ligne.
VALUE_ERROR : erreur arithmétique, de conversion, ou de contrainte de taille.
ZERO_DIVIDE : tentative de division par zéro.
28
Cours Base de donnèes 28
GESTION DES ERREURS (EXCEPTION)
Exemple exception interne
29
Cours Base de donnèes 29
GESTION DES ERREURS (EXCEPTION)
Exceptions Utilisateur (externes)
PL/SQL permet à l'utilisateur de définir ses propres exceptions.
La gestion des anomalies utilisateur peut se faire dans un bloc PL/SQL en effectuant les opérations suivantes :
1. Nommer l'erreur (type exception) dans la partie Declare du bloc.
2. DECLARE Nom_ano Exception;
3. Déterminer l'erreur et passer la main au traitement approprié par la commande Raise. BEGIN If
(condition_anomalie) then raise Nom_ano ;
4. Effectuer le traitement défini dans la partie EXCEPTION du Bloc. EXCEPTION WHEN (Nom_ano) then
(traitement);
30
Cours Base de donnèes 30
GESTION DES ERREURS (EXCEPTION)
Exceptions Utilisateur (externes)
31
Cours Base de donnèes 31
GESTION DES ERREURS (EXCEPTION)
Exceptions Utilisateur (externes)
32
Cours Base de donnèes 32
GESTION DES ERREURS (EXCEPTION)
Fonctions d’interception des erreurs
SQLCODE
Renvoie la valeur numérique associé au code de l’erreur.
Vous pouvez l’assigneer à une variable de type number
SQLERRM
Renvoie le message associé au code de l’erreur.
33
Cours Base de donnèes 33
GESTION DES ERREURS (EXCEPTION)
Exemple de SQLCODE
34
Cours Base de donnèes 34
GESTION DES ERREURS (EXCEPTION)
35
Cours Base de donnèes 35
•5
PROCEDURES,
FONCTIONS ET
PACKAGES
PROCÉDURES, FONCTIONS ET PACKAGE
Généralité
Une procédure est un bloc PL/SQL nommé.
Une fonction est une procédure qui retourne une valeur.
Un package est un agrégat de procédures et de fonctions.
Les packages, procédures, ou fonctions peuvent être appelés depuis toutes les
applications qui possèdent une interface avec ORACLE (SQL*PLUS…)
37
Cours Base de donnèes 37
PROCEDURES,FONCTIONS ET PACKAGE
Généralité
Les procédures (fonctions) permettent de :
Réduire le trafic sur le réseau (les procédures sont locales sur le serveur)
Mettre en œuvre une architecture client/serveur de procédureset rendre indépendant le code
client de celui des procédures
Masquer la complexité du code SQL (simple appel de procédure avec passage d’arguments)
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.
38
Cours Base de donnèes 38
PROCÉDURES, FONCTIONS ET PACKAGE
Généralité
Les packages permettent :
De regrouper des procédures ou des fonctions (ou les deux). On évite ainsi d’avoir autant de
sources que de procédures.
De travailler en équipes et l’architecture applicative peuvent donc plus facilement s’organiser du
côté serveur, où les packages regrouperont des procédures (Sélection de tous les articles, Sélection
d’un article, Mise à jour d’un article, Suppression d’un article, Ajout d’un article).
Les packages sont utilisés comme de simples librairies par les programmes clients
(librairies distantes « sur le serveur »)
39
Cours Base de donnèes 39
PROCÉDURES
Procédures
Les procédures ont un ensemble de paramètres modifiables en entrée et en sortie.
40
Cours Base de donnèes 40
PROCÉDURES
Procédures
41
Cours Base de donnèes 41
PROCÉDURES
Procédure Exemple
Compter le nombre d'employés pour un département donné.
42
Cours Base de donnèes 42
PROCÉDURES, FONCTIONS ET PACKAGE
Procédure Exemple
Exemple de procédure qui modifie le salaire d’un employé.
Arguments : Identifiant de l’employée, Taux
43
Cours Base de donnèes 43
PROCEDURES
44
Cours Base de donnèes 44
COMPILATION DE LA PROCÉDURE
Il faut compiler le fichier sql qui s’appelle ici modifie_salaire.sql (attention dans cet
exemple le nom du script correspond à celui de la procédure, c’est bien le nom du script
sql qu’il faut passer en argument à la commande start).
45
Cours Base de donnèes 45
APPEL DE LA PROCÉDURE
46
Cours Base de donnèes 46
APPEL DE LA PROCÉDURE
47
Cours Base de donnèes 47
CORRECTION DES ERREURS
Si le script contient des erreurs, la commande show err permet de visualiser les erreurs.
Pour visualiser le script global :
commande l (lettre l)
pour visualiser la ligne 4 : commande l4
48
Cours Base de donnèes 48
FONCTIONS
Fonctions
Une fonction est une procédure qui retourne une valeur. La seule différence syntaxique
par rapport à une procédure 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).
49
Cours Base de donnèes 49
FONCTIONS
Fonctions(Exemple)
Compter le nombre d'employés pour un département donné.
50
Cours Base de donnèes 50
FONCTIONS
51
Cours Base de donnèes 51
LES PACKAGES
52
Cours Base de donnèes 52
LA STRUCTURE GÉNÉRALE D’UN PACKAGE
53
Cours Base de donnèes 53
LA STRUCTURE GÉNÉRALE D’UN PACKAGE
Un package est composé d’un en tête et d’un corps :
L’en tête comporte les types de données définis et les prototypes de toutes les procédures
(fonctions) du package.
Le corps correspond à l’implémentation (définition) des procédures (fonctions).
Le premier END marque la fin de l’en tête du package. Cette partie doit se terminer par / pour
que l’en tête soit compilé.
Le corps (body) du package consiste à implémenter (définir) l’ensemble des procédures ou
fonctions qui le constitue. Chaque procédure est définie normalement avec ses clauses BEGIN ...
END.
Le package se termine par / sur la dernière ligne.
54
Cours Base de donnèes 54
LA STRUCTURE GÉNÉRALE D’UN PACKAGE
Exemple
55
Cours Base de donnèes 55
LA STRUCTURE GÉNÉRALE D’UN PACKAGE
Compilation du package paquet1
56
Cours Base de donnèes 56
LA STRUCTURE GÉNÉRALE D’UN PACKAGE
57
Cours Base de donnèes 57
EXERCICES
Considérez les relations suivantes :
Employe(nuempl:string, nomempl:string, hebdo: integer,salaire integer, #affect:integer)
Service(nuserv:integer, nomserv:string, #chef:integer)
Projet(nuproj:integer, nomproj:string, #resp:integer)
Travail(#nuempl:integer, #nuproj:integer, duree:integer)
I. Ecrire une procédure qui prend comme arguments un identifiant d'employé et un
nombre, puis mettre à jour le salaire de l'employé donné avec le nombre donné.
II. Ecrire une fonction qui compte le nombre d'employés participant à un projet donné.
III. Ecrire une fonction qui compte le nombre de projets supervisés par les employés d'un
service donné
IV. Ecrire une fonction qui compte le nombre de projets auxquels participe l'employé
donné
58
Cours Base de donnèes 58
