INGÉNIERIE DES BASES DE DONNÉES

Chapitre1: Les langages de 4eme génération actuels

Wissal Midani
wissal.midani15@[Link]
1
Généralités
Introduction

Les structures de contrôle habituelles d’un langage (IF, WHILE…) ne font pas partie intégrante
de la norme SQL. Elles apparaissent dans une sous-partie optionnelle de la norme (ISO/ IEC 9075-5:1996.
Flow-control statements). Oracle les prend en compte dans PL/SQL. Nombre de concepts de PL/SQL
proviennent du langage Ada.
Le langage PL/SQL (Procedural Langage/Structured Query Langage) est le langage de prédilection
d’Oracle depuis la version 6. Ce langage est une extension de SQL car il permet de faire cohabiter des
structures de contrôle (si, pour et tant que) avec des instructions SQL (principalement SELECT, INSERT,
UPDATE et DELETE). PL/SQL est aussi utilisé par des outils d’Oracle (Forms, Report et Graphics).

1
Généralités
Environnement client-serveur
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. Il est préférable de travailler avec un bloc
PL/SQL plutôt qu’avec une suite d’instructions SQL susceptibles d’encombrer le trafic réseau. En effet, un
bloc PL/SQL donne lieu à un seul échange sur le réseau entre le client et le serveur. Les résultats
intermédiaires sont traités côté serveur et seul le résultat final est retourné au client.

Figure1 : Trafic sur le réseau d’instructions SQL 2

 Généralités
Avantages
Les principaux avantages de PL/SQL sont :
 La modularité (un bloc d’instruction peut être composé d’un autre, etc.) : un bloc peut être nommé
pour devenir une procédure ou une fonction cataloguée, donc réutilisable. Une procédure, ou fonction,
cataloguée peut être incluse dans un paquetage (package) pour mieux contrôler et réutiliser ces
composants logiciels.
 La portabilité : un programme PL/SQL est indépendant du système d’exploitation qui héberge le serveur
Oracle. En changeant de système, les applicatifs n’ont pas à être modifiés.
 L’intégration avec les données des tables : on retrouvera avec PL/SQL tous les types de données et
instructions disponibles sous SQL, et des mécanismes pour parcourir des résultats de requêtes (curseurs),
pour traiter des erreurs (exceptions), pour manipuler des données complexes (paquetages DBMS_xxx) et
pour programmer des transactions (COMMIT, ROLLBACK, SAVEPOINT).
3
Généralités
Structure d’un programme
Un programme PL/SQL qui n’est pas nommé (aussi appelé bloc) est composé de trois sections comme
le montre la figure suivante :
 DECLARE (section optionnelle) déclare les variables, types, curseurs, exceptions, etc. ;
 BEGIN (section obligatoire) contient le code PL/SQL incluant ou non des directives SQL (jusqu’à
l’instruction END;). Le caractère « / » termine un bloc pour son exécution dans l’interface SQL*Plus. Nous
n’indiquons pas ce signe dans nos exemples pour ne pas surcharger le code, mais vous devrez l’inclure à
la fin de vos blocs.
 EXCEPTION (section optionnelle) permet de traiter les erreurs retournées par le SGBD à la suite
d’exécutions d’instructions SQL.

4
Généralités
Structure d’un programme

Figure 2 : Strcture d’un bloc PL/SQL

5
Généralités
Portée des objets
Un bloc peut être imbriqué dans le code d’un autre bloc (on parle de sous-bloc). Un sous-bloc peut aussi se
trouver dans la partie des exceptions. Un sous-bloc commence par BEGIN et se termine par END.
La portée d’un objet (variable, type, curseur, exception, etc.) est la zone du programme qui peut y accéder.
Un bloc qui déclare qu’un objet peut y accéder, ainsi que les sous-blocs. En revanche, un objet déclaré dans
un sous-bloc n’est pas visible du bloc supérieur (principe des accolades de C et Java).

Figure3 : Visibilité des objets 6

Généralités
Jeu de caractères
Comme SQL, les programmes PL/SQL sont capables d’interpréter les caractères suivants :
 lettres A à Z et a à z ;
 chiffres de 0 à 9 ;
 symboles ( ) + - * / < > = ! ~ ^ ; : . ' @ % , " # $ & _ | { } ? [ ] ;
 tabulations, espaces et retours-chariot.
Comme SQL, PL/SQL n’est pas sensible à la casse (not case sensitive). Ainsi numéroBrevet et
NuméroBREVET désignent le même identificateur (tout est traduit en majuscules au niveau du dictionnaire
des données). Les règles d’écriture concernant l’indentation et les espaces entre variables, mots-clés et
instructions doivent être respectées dans un souci de lisibilité.

7
Généralités
Jeu de caractères

8
Généralités
Identificateurs
Avant de parler des différents types de variables PL/SQL, décrivons comment il est possible de nommer des
objets PL/SQL (variables, curseurs, exceptions, etc.).
Un identificateur commence par une lettre suivie (optionnel) de symboles (lettres, chiffres, $, _, #).
Un identificateur peut contenir jusqu’à trente caractères. Les autres signes pourtant connus du langage sont
interdits comme le montre le tableau suivant :

9
Généralités
Commentaires
PL/SQL supporte deux types de commentaires :
 Monolignes, commençant au symbole -- et finissant à la fin de la ligne ;
 Multilignes, commençant par /* et finissant par */.
Le tableau suivant décrit quelques exemples :

10
Généralités
Commentaires

Il n’est pas possible d’imbriquer des commentaires. Pour les programmes PL/SQL qui sont utilisés par
des précompilateurs, il faut employer des commentaires multilignes.

11
Variables
Variables
Un programme PL/SQL est capable de manipuler des variables et des constantes (dont la valeur est
invariable). Les variables et les constantes sont déclarées (et éventuellement initialisées) dans la section
DECLARE. Ces objets permettent de transmettre des valeurs à des sous programmes via des paramètres,
ou d’afficher des états de sortie sous l’interface SQL*Plus.
Plusieurs types de variables sont manipulés par un programme PL/SQL :
 Variables PL/SQL :
• scalaires recevant une seule valeur d’un type SQL (exemple : colonne d’une table) ;
• composites (%ROWTYPE, RECORD et TYPE) ;
• références (REF) ;
• LOB (locators).
 Variables non PL/SQL : définies sous SQL*Plus (de substitution et globales), variables
hôtes (déclarées dans des programmes précompilés). 12
Variables
Variables scalaires
La déclaration d’une variable scalaire est de la forme suivante :

identificateur [CONSTANT] typeDeDonnée [NOT NULL] [:= | DEFAULT

expression];

 CONSTANT précise qu’il s’agit d’une constante ;

 NOT NULL pose une contrainte en ligne sur la variable ;

 DEFAULT permet d’initialiser la variable (équivaut à l’affectation :=).

Le tableau suivant décrit quelques exemples :

13
Variables
Variables scalaires

Il n’est pas possible d’affecter une valeur nulle à une variable définie NOT NULL (l’erreur renvoyée
est l’exception prédéfinie VALUE_ERROR).
La contrainte NOT NULL doit être suivie d’une clause d’initialisation.
14
Variables
Affectations
Il existe plusieurs possibilités pour affecter une valeur à une variable :
 l’affectation comme on la connaît dans les langages de programmation (variable := expression) ;
 par la directive DEFAULT ;
 par la directive INTO d’une requête (SELECT … INTO variable FROM …).
Le tableau suivant décrit quelques exemples :

15
Variables
Affectations

16
Variables
Restrictions
Il est impossible d’utiliser un identificateur dans une expression s’il n’est pas déclaré au préalable.
Ici, la déclaration de la variable maxi est incorrecte :
DECLARE
maxi NUMBER := 2 * mini;
mini NUMBER := 15;
À l’inverse de la plupart des langages récents, les déclarations multiples ne sont pas permises.
Celle qui suit est incorrecte :
DECLARE
i, j, k NUMBER;

17
Variables
Variables %TYPE
La directive %TYPE déclare une variable selon la définition d’une colonne d’une table ou d’une vue existante.
Elle permet aussi de déclarer une variable conformément à une autre variable précédemment déclarée.
Il faut faire préfixer la directive %TYPE avec le nom de la table et celui de la colonne (identificateur
[Link]%TYPE) ou avec le nom d’une variable existante (identificateur2
identificateur1%TYPE). Le tableau suivant décrit cette syntaxe :

18
Variables
Variables %ROWTYPE
La directive %ROWTYPE permet de travailler au niveau d’un enregistrement (record). Ce dernier est
composé d’un ensemble de colonnes. L’enregistrement peut contenir toutes les colonnes d’une table ou
seulement certaines.
Cette directive est très utile du point de vue de la maintenance des applicatifs. Utilisés à bon escient, elle
diminue les changements à apporter au code en cas de modification des types des colonnes de la table.
Il est aussi possible d’insérer dans une table ou de modifier une table en utilisant une variable du type
%ROWTYPE.

Le tableau suivant décrit ces cas d’utilisation :

19
Variables
Variables %ROWTYPE

Les colonnes récupérées par la directive %ROWTYPE n’héritent pas des contraintes NOT NULL qui
seraient éventuellement déclarées au niveau de la table.
20
Variables
Variables RECORD
Alors que la directive %ROWTYPE permet de déclarer une structure composée de colonnes de tables, elle
ne convient pas à des structures de données personnalisées. Le type de données RECORD (disponible
depuis la version 7) définit vos propres structures de données (l’équivalent du struct en C). Depuis la version
8, les types RECORD peuvent inclure des LOB (BLOB, CLOB et BFILE) ou des extensions objets (REF,
TABLE ou VARRAY).
La syntaxe générale pour déclarer un RECORD est la suivante :

TYPE nomRecord IS RECORD

( nomChamp typeDonnées [[NOT NULL] {:= | DEFAULT} expression]
[,nomChamp typeDonnées… ]… );

21
Variables
Variables RECORD
L’exemple suivant décrit l’utilisation d’un record :

22
Variables
Variables RECORD
Les types RECORD ne peuvent pas être stockés dans une table. En revanche, il est possible qu’un champ
d’un RECORD soit lui-même un RECORD, ou soit déclaré avec les directives %TYPE ou %ROWTYPE. L’exemple
suivant illustre le RECORD r_vols déclaré avec ces trois possibilités :

DECLARE
TYPE avionAirbus_rec IS RECORD
(nserie CHAR(10), nomAvion CHAR(20),
usine CHAR(10) := 'Blagnac', nbHVol NUMBER(7,2));
TYPE vols_rec IS RECORD
r_aéronef avionAirbus_rec, dateVol DATE,
rty_coPilote Pilote%ROWTYPE, affretéPar [Link]%TYPE);

23
Variables
Variables RECORD

Les RECORD ne peuvent pas être comparés (nullité, égalité et inégalité), ainsi les tests suivants sont
incorrects :
v1 avionAirbus_rec;
v2 vols_rec;
v3 vols_rec;
BEGIN
…
IF v1 IS NULL THEN …
IF v2 > v3 THEN …

24
Variables
Variables tableaux (type TABLE)
Les variables de type TABLE (associative arrays) permettent de définir et de manipuler des tableaux
dynamiques (car définis sans dimension initiale). Un tableau est composé d’une clé primaire (de type
BINARY_INTEGER) et d’une colonne (de type scalaire, TYPE, ROWTYPE ou RECORD) pour stocker chaque

élément.
Syntaxe
La syntaxe générale pour déclarer un type de tableau et une variable tableau est la suivante :

TYPE nomTypeTableau IS TABLE OF

{typeScalaire | variable%TYPE | [Link]%TYPE} [NOT NULL]
| table.%ROWTYPE
[INDEX BY BINARY_INTEGER];
nomTableau nomTypeTableau;
25
Variables
Variables tableaux (type TABLE)
L’option INDEX BY BINARY_INTEGER est facultative depuis la version 8 de PL/SQL. Si elle est
omise, le type déclaré est considéré comme une nested table (extension objet). Si elle est présente,
l’indexation ne commence pas nécessairement à 1 et peut être même négative (l’intervalle de valeurs
du type BINARY_INTEGER va de – 2 147 483 647 à 2 147 483 647).

L’exemple suivant décrit la déclaration de trois tableaux et l’affectation de valeurs à différents indices (– 1, – 2
et 7800). L’accès à des champs d’éléments complexes se fait à l’aide de la notation pointée (voir la dernière
instruction).

26
Variables
Variables tableaux (type TABLE)

27
Variables
Variables tableaux (type TABLE)
Fonctions pour les tableaux
PL/SQL propose un ensemble de fonctions qui permettent de manipuler des tableaux (également disponibles
pour les nested tables et varrays). Ces fonctions sont les suivantes (les trois dernières sont des procédures) :

Il n’est pas possible actuellement d’appeler une de ces fonctions dans une instruction SQL (SELECT,
INSERT, UPDATE ou DELETE).
28
Variables
Variables tableaux (type TABLE)
Les exemples suivants décrivent l’utilisation de ces fonctions :

29
Variables
Résolution de noms
Lors des conflits potentiels de noms (variables ou colonnes) dans des instructions SQL (principalement
INSERT, UPDATE, DELETE et SELECT), le nom de la colonne de la table est prioritairement interprété au

détriment de la variable (de même nom).

Dans l’exemple suivant, l’instruction DELETE supprime tous les pilotes (et non pas seulement le pilote 'Pierre
Lamothe'), car Oracle considère les deux identificateurs comme la colonne de la table et non pas comme
deux variables différentes !

DECLARE
nom CHAR(20) := 'Pierre Lamothe';
BEGIN
DELETE FROM Pilote WHERE nom = nom ;
…

30
Variables
Résolution de noms
Pour se prémunir de tels effets de bord, deux solutions existent. La première consiste à nommer toutes les
variables explicitement et différemment des colonnes. La deuxième consiste à utiliser une étiquette de bloc
(block label) pour lever les ambiguïtés. Le tableau suivant illustre ces solutions concernant notre exemple :

31
Variables
Opérateurs
Les opérateurs SQL étudiés au chapitre 4 (logiques, arithmétiques, concaténation…) sont disponibles aussi
avec PL/SQL. Les règles de priorité sont les mêmes que dans le cas de SQL.

L’opérateur IS NULL permet de tester une expression avec la valeur NULL. Toute expression
arithmétique contenant une valeur nulle est évaluée à NULL.

Le tableau suivant illustre quelques utilisations possibles d’opérateurs logiques :

32
Variables
Variables de substitution
Il est possible de passer en paramètres d’entrée d’un bloc PL/SQL des variables définies sous SQL*Plus.
Ces variables sont dites de substitution. On accède aux valeurs d’une telle variable dans le code PL/SQL en
faisant préfixer le nom de la variable du symbole « & » (avec ou sans guillemets simples suivant qu’il s’agit
d’un nombre ou pas).

Le tableau suivant illustre un exemple de deux variables de substitution. La directive ACCEPT (lecture de la
variable au clavier) est détaillée dans le chapitre relatif à SQL*Plus. Dans cet exemple on extrait le nom et le
nombre d’heures de vol d’un pilote. Son numéro de brevet et la durée du vol sont lus au clavier et la durée
est ajoutée au nombre d’heures de vol du pilote. Il est à noter qu’il ne faut pas déclarer des variables de
substitution.

33
Variables
Variables de substitution

Il faut exécuter le bloc à l’aide de la commande start et non pas par copier-coller d’un éditeur
de texte vers la fenêtre SQL*Plus (à cause des instructions d’entrée ACCEPT).
34
Variables
Variables de session
Il est possible de définir des variables de session (globales) définies sous SQL*Plus au niveau d’un bloc
PL/SQL. La directive SQL*Plus à utiliser en début de bloc est VARIABLE. Dans le code PL/SQL, il faut faire
préfixer le nom de la variable de session du symbole « : ». L’affichage de la variable sous SQL*Plus est
réalisé par la directive PRINT.
Le tableau suivant illustre un exemple de variable de session :

35
Variables
Conventions recommandées
Adoptez les conventions d’écriture suivantes pour que vos programmes PL/SQL soient plus facilement
lisibles et maintenables :

36
Structures de contrôles
Les types de structures

En tant que langage procédural, PL/SQL offre la possibilité de programmer :

 les structures conditionnelles si et cas (IF… et CASE) ;
 les structures répétitives tant que, répéter et pour (WHILE, LOOP, FOR).

37
Structures de contrôles
Structures conditionnelles
PL/SQL propose deux structures pour programmer une action conditionnelle : la structure IF et la structure
CASE.
 Trois formes de IF
Suivant les tests à programmer, on peut distinguer trois formes de structure IF : IF-THEN (si-alors) IF-
THEN-ELSE (avec le sinon à programmer), et IF-THEN-ELSIF (imbrications de conditions).
Le tableau suivant décrit l’écriture des différentes structures conditionnelles IF. Notez « END IF » en fin de
structure et non pas « ENDIF ». L’exemple affiche un message différent selon la nature du numéro de
téléphone contenu dans la variable v_téléphone.
La fonction PUT_LINE du paquetage DBMS_OUTPUT permet d’afficher une chaîne de caractères dans
l’interface SQL*Plus. Nous étudierons plus loin les fonctions de ce paquetage.

38
Structures de contrôles
Structures conditionnelles

39
Structures de contrôles
Structures conditionnelles
 Structure CASE
Comme l’instruction IF, la structure CASE permet d’exécuter une séquence d’instructions en fonction de
différentes conditions. La structure CASE est utile lorsqu’il faut évaluer une même expression et proposer
plusieurs traitements pour diverses conditions.
En fonction de la nature de l’expression et des conditions, une des deux écritures suivantes peut être utilisée :

40
Structures de contrôles
Structures conditionnelles
Le tableau suivant décrit l’écriture avec IF d’une programmation qu’il est plus rationnel d’effectuer avec une
structure CASE (de type searched) :

41
Structures de contrôles
Structures répétitives
Les trois structures répétitives tant que, répéter et pour utilisent l’instruction LOOP… END LOOP.
 Structure tant que
La structure tant que se programme à l’aide de la syntaxe suivante. Avant chaque itération (et notamment
avant la première), la condition est évaluée. Si elle est vraie, la séquence d’instructions est exécutée, puis la
condition est réévaluée pour un éventuel nouveau passage dans la boucle. Ce processus continue jusqu’à ce
que la condition soit fausse pour passer en séquence après le END LOOP. Quand la condition n’est jamais
fausse, on dit que le programme boucle…

WHILE condition LOOP

instructions;
END LOOP;

42
Structures de contrôles
Structures répétitives
Cette structure est la plus puissante car elle permet de programmer aussi un répéter et un pour. Elle doit
être utilisée quand il est nécessaire de tester une condition avant d’exécuter les instructions contenues dans
la boucle.

Le tableau suivant décrit la programmation de deux tant que. Le premier calcule la somme des 100 premiers
entiers. Le second recherche le premier numéro 4 dans une chaîne de caractères.

43
Structures de contrôles
Structures répétitives

44
Structures de contrôles
Structures répétitives
 Structure répéter
La structure répéter se programme à l’aide de la syntaxe LOOP EXIT suivante :

LOOP
instructions;
EXIT [WHEN condition;]
END LOOP;
La particularité de cette structure est que la première itération est effectuée quelles que soient les conditions
initiales. La condition n’est évaluée qu’en fin de boucle.
 Si aucune condition n’est spécifiée (WHEN condition absent), la sortie de la boucle est immédiate dès la fin
des instructions.
 Si la condition est fausse, la séquence d’instructions est de nouveau exécutée. Ce processus continue
jusqu’à ce que la condition soit vraie pour passer en séquence après le END LOOP. 45
Structures de contrôles
Structures répétitives
 Quand la condition n’est jamais fausse, on dit aussi que le programme boucle…
Le tableau suivant décrit la programmation de la somme des 100 premiers entiers et de la recherche du
premier numéro 4 dans une chaîne de caractères à l’aide de la structure répéter.

46
Structures de contrôles
Structures répétitives
Cette structure doit être utilisée quand il n’est pas nécessaire de tester la condition avec les données initiales
avant d’exécuter les instructions contenues dans la boucle.

 Structure pour
Célèbre pour les parcours de vecteurs, tableaux et matrices en tout genre, la structure pour se caractérise par
la connaissance a priori du nombre d’itérations que le programmeur souhaite faire effectuer à son algorithme.
La syntaxe générale de cette structure est la suivante :

FOR compteur IN [REVERSE] valeurInf..valeurSup LOOP

instructions;
END LOOP;

47
Structures de contrôles
Structures répétitives
Le nombre d’itérations est calculé dès le premier passage dans la condition et n’est jamais réévalué par la
suite quelles que soient les instructions contenues dans la boucle. À la première itération le compteur reçoit
automatiquement la valeur initiale (valeurInf). Après chaque passage le compteur est de fait incrémenté (ou
décrémenté si l’option REVERSE a été choisie). La sortie de la boucle est automatique après l’itération
correspondant à la valeur finale du compteur (valeurSup). La déclaration de la variable compteur n’est pas
obligatoire. Il ne faut pas modifier cette variable dans le corps de la boucle (ou alors si on veut modifier
volontairement son pas).

Le tableau suivant décrit la programmation de la somme des 100 premiers entiers et de la recherche du
premier numéro 4 dans une chaîne de caractères à l’aide de la structure pour.

48
Structures de contrôles
Structures répétitives

49
Structures de contrôles
Structures répétitives
Cette structure convient bien pour le premier exemple car on sait a priori qu’il faut faire 100 itérations. Pour le
second, cette structure peut être utilisée mais est moins efficace car elle impose de parcourir tous les
éléments de la chaîne alors qu’on pourrait interrompre le traitement dès le numéro trouvé. De plus il est
nécessaire de modifier le test dans la boucle de manière à ne garder que le premier numéro trouvé (et pas le
dernier si le test n’était pas changé).

50
Structures de contrôles
Structures répétitives
 Boucles avec étiquettes
Comme les blocs de traitements, les boucles peuvent être étiquetées. L’étiquette est notée par un identifiant
qui apparaît après l’instruction de fin de boucle par la syntaxe suivante :

<<etiquette>>
LOOP
instructions;
END LOOP etiquette;

Ce mécanisme présente les deux avantages suivants :

 meilleure lisibilité du code ;
 sortie possible de plusieurs boucles imbriquées : de la boucle courante et de celle(s) qui l’inclut(ent).

51
Structures de contrôles
Structures répétitives
L’exemple suivant décrit la programmation de la recherche d’un code d’une carte bleue (ici 8595) en
considérant tous les codes possibles (en partant de 0000). Quatre boucles sont imbriquées et on doit sortir
du programme dès que le code est trouvé pour ne pas examiner les autres combinaisons.

L’étiquette <<principal>> marque la première boucle. La boucle la plus imbriquée possède deux
conditions de sortie : la nominale EXIT WHEN… et la sortie forcée EXIT principal WHEN…

52
Structures de contrôles
Structures répétitives

53
Curseurs
Introduction
 Un curseur est une zone mémoire qui permet de traiter individuellement chaque ligne renvoyée par un
SELECT.
 La mémoire privée stocke l'ensemble des résultats récupérés lors de l'exécution du SQL et les attributs du
curseur.
 Toutes les instructions SQL intégrées dans un bloc PL/SQL sont exécutées comme un curseur.
 Un curseur, durant son existence (de l’ouverture à la fermeture), contient en permanence l’adresse de la
ligne courante.
 Les curseurs peuvent être classés en curseurs implicites et explicites.

54
Curseurs
Curseurs implicites
 PL/SQL utilise un curseur implicite pour chaque opération du LMD de SQL (INSERT, UPDATE et
DELETE).
 Ce curseur porte le nom SQL et il est exploitable après avoir exécuté l’instruction.
 La commande qui suit le LMD remplace l’ancien curseur par un nouveau.
Les instructions propres aux curseurs implicites sont définies dans le tableau suivant :
Instructions Description
SQL%ROWCOUNT Nombre de lignes affectées par la dernière instruction
LMD.
SQL%FOUND Booléen valant TRUE si la dernière instruction LMD
affecte au moins un enregistrement.
SQL%NOTFOUND Booléen valant TRUE si la dernière instruction LMD
n’affecte aucun enregistrement.
SQL%ISOPEN Il renvoie toujours FALSE pour les curseurs implicites,
55
Curseurs
Curseurs implicites
DECLARE
V_Rows_Updated NUMBER;
BEGIN
UPDATE EMP
SET sal = sal*2
WHERE deptno = 10;
V_Rows_Updated := SQL%ROWCOUNT;
INSERT INTO History_Tab
Values (‘Dept 10’, V_Rows_Updated, SYSDATE);
END ;

56
Curseurs
Curseurs implicites
 Un curseur explicite, contrairement au curseur implicite est géré par l'utilisateur pour traiter un ordre
SELECT qui ramène plusieurs lignes

 C’est une commande SELECT pouvant ramener plusieurs lignes et qui est totalement à la charge du
développeur.

57
Curseurs
Curseurs explicites
L'utilisation d'un curseur explicite comprend les étapes suivantes :

YES
FOUN NO
D/NOT
DECLARE OPEN FETCH FOUN CLOSE
D

Créer une Pointer sur la Charger la premier Test de l’existence Libérer le curseur
zone SQL Périmer ligne ligne en cour dans des lignes
du curseur des variables
Retour à FETCH si
la ligne est trouvée

58
Curseurs
Curseurs explicites
Les instructions propres aux curseurs sont définies dans le tableau suivant :

Instructions Description
%FOUND TRUE si l’énoncé SQL le plus récent affecte au
moins une ou plusieurs lignes.
%NOTFOUND TRUE si l’énoncé SQL le plus récent n’affecte
aucune ligne.
%ROWCOUNT Nombre de lignes affecté par l’énoncé SQL le plus
récent.
%ISOPEN TRUE si le curseur est ouvert, FALSE sinon.

59
Curseurs
Curseurs explicites
1) Déclaration du curseur :
Déclaration du curseur pour l'initialisation de la mémoire.

CURSOR nom_Curseur IS
Requête_SQL;

Un curseur peut être paramétré :

CURSOR nom_Curseur (paramètre1, paramètre2,…) IS

Requête_SQL;

60
Curseurs
Curseurs explicites

2) Ouverture du curseur :
L'ouverture du curseur alloue la mémoire pour le curseur et le rend prêt pour l'extraction des lignes retournées
par l'instruction SQL dans celui-ci.

OPEN nom_Curseur ;

61
Curseurs
Curseurs explicites
3) Traitement des lignes :
L'extraction des données implique un accès à une ligne à la fois.
Fonctionnalités:
 Ramène le contenu de la ligne courante
 Assigne les données dans les variables de INTO
 Déplace le pointeur vers la ligne suivante

FETCH nom_Curseur INTO

Liste_variables;

3) Fermeture du curseur :
Fermer le curseur signifie libérer la mémoire allouée.

CLOSE nom_Curseur;
62
Curseurs
Curseurs explicites
Declare
cursor dept_10 is
select ename, sal From emp where deptno= 10 order by sal;
nom [Link]%TYPE;
salaire [Link]%TYPE;
Begin
Open dept_10;
Loop
Fetch dept_10 into nom,salaire;
Exit when dept_10%NOTFOUND or dept_10%ROWCOUNT > 15;
If salaire > 2500
Then insert into résultat values (nom,salaire);
end if;
end loop;
Close dept_10;
End; 63
Curseurs
Curseurs explicites
 Les curseurs et les Records
Traitement des lignes de l’ensemble actif par l’affectation des valeurs à des records PL/SQL.

Nom_enregistrement nom_curseur%ROWTYPE ;

DECLARE INSERT INTO num_nom_emp VALUES

CURSOR curs-emp IS (enr_emp.empno, enr_emp.ename);
Select empno, ename From EMP; END LOOP;
enr_emp curs_emp%ROWTYPE; CLOSE curs_emp;
BEGIN END;
OPEN curs_emp;
LOOP
FETCH curs_emp into enr_emp;
EXIT when curs_emp%NOTFOUND;

64
Curseurs
Curseurs explicites
 Les curseurs et boucle FOR
Traitement des lignes de l’ensemble actif par l’affectation des valeurs à des records PL/SQL.
 Traitement des lignes dans un curseur implicite
 L’ouverture, le FETCH et le fermeture sont implicitement gérés
 Le nom de l’enregistrement est implicitement déclaré

FOR nom_enregistrement IN nom_curseur

LOOP
instruction(s)
END LOOP;

65
Curseurs
Curseurs explicites
DECLARE
CURSOR curs-emp IS
Select empno, ename From EMP;
BEGIN
FOR enr_emp IN curs_emp;
LOOP
INSERT INTO num_nom_emp VALUES(enr_emp.empno,enr_emp.ename);
END LOOP;
END;

66
Curseurs
Curseurs explicites
 Les curseurs et Clause : FOR UPDATE

 Permet un verrouillage explicite

 Interdire l’accès durant l’opération.
 Les verrous sont libérés à la fin de la transaction.

CURSOR nomCurseur[(paramètres)] IS
SELECT … FROM {nomTable | nomVue } WHERE …
FOR UPDATE [OF [[schéma.] {nomTable | nomVue }.]colonne [, …]
[ NOWAIT | WAIT entier ]

67
Curseurs
Curseurs explicites
DECLARE Cursorcurs_emp IS
SELECT empno, ename, sal
FROM EMP
WHERE deptno= 30
FOR UPDATE NOWAIT;

 Clause : WHERE CURRENT OF

 Permet la mise à jour ou la suppression de l’enregistrement courant.

 Il faut avoir inclus la clause FOR UPDATE dans la requête du curseur.

68
Curseurs
Curseurs explicites
DECLARE Cursorcurs_emp IS
Select empno, ename, sal
From EMP
Where deptno= 30
FOR UPDATE NOWAIT;
BEGIN
FOR enr_emp IN curs_emp
LOOP
UPDATE EMP SET SAL:= enr_emp.sal*1.1
WHERE CURRENT OF curs_emp;
END LOOP;
END; 69
Exceptions
Introduction
 En PL/SQL les erreurs sont considérées comme des exceptions.
 Une exception est un identifiant PL/SQL généré lors de l’exécution d’un Bloc .
 Elle termine le corps principal des instructions.
 Il est possible de spécifier des instructions qui seront exécutées juste avant l’arrêt.
 Une exception peut être prédéfinie par le système ou déclarée par l'utilisateur.

EXCEPTION
WHEN exception1 [OR exception2 …] THEN
instructions;
[WHEN exception3 [OR exception4 …] THEN
instructions; ]
[WHEN OTHERS THEN
instructions; ]
70
Exceptions
Exceptions prédéfinies

71
Exceptions
Exceptions prédéfinies

72
Exceptions
Exceptions prédéfinies
DECLARE
lname VARCHAR2(15);
BEGIN
SELECT last_name INTO lname FROM employees WHERE first_name=’John’;
DBMS_OUTPUT.PUT_LINE (’Le nom de John est : ’ ||lname);
EXCEPTION
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE (’La requête revoie plusieurs lignes, utilisez un
curseur’);
WHEN NO_DATA_FOUND THEN
DBMS [Link] LINE (’Aucun employé ne porte le prénom John’);
END; 73
Exceptions
Exceptions non prédéfinies
 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)
 L’exception déclarée et implicitement déclenchée:
1) Déclarer le nom de l’exception oracle non-prédéfinie.

exception_nom EXCEPTION;

2) Associer l’exception déclarée au code standard de l’erreur oracle en utilisant l’instruction.

PROGMA EXCEPTION_INIT (exception, erreur_number);

3) Traiter l’exception ainsi déclarée dans la section EXCEPTION.

74
Exceptions
Exceptions non prédéfinies
DECLARE
e_emps EXCEPTION;
PRAGMA EXCEPTION_INIT (e_emp, -2292);
v_deptno [Link]%type:=&p_deptno ;
BEGIN
Delete from dept Where deptno=v_deptno;
Commit;
EXCEPTION
WHEN e_emps THEN
dbms_output.pu_line(‘Suppression impossible du dept : ‘ ||
to_char(v_deptno) || ‘employés existant ‘);
END; 75
Exceptions
Exceptions Utilisateur nommés
 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 :
 Sont définies dans la section DECLARE.
 Sont déclenchées explicitement dans la section BEGIN par l'instruction RAISE.
 Dans la section EXCEPTION, référencer le nom défini dans la section DECLARE.

Déclarer Déclencher Traiter

Section déclaration Section Exécutable Section Exception

Déclencher explicitement
Nommer l ’exception l’exception via l’instruction RAISE Traiter l’exception déclencher
76
Exceptions
Exceptions Utilisateur nommés

DECLARE
...
Nom_ano EXCEPTION;
BEGIN
instructions ;
IF (condition_anomalie) THEN RAISE Nom_ano;
...
EXCEPTION
WHEN Nom_ano THEN (traitement);
END ;

77
Exceptions
Exceptions Utilisateur nommés
DECLARE EXECPTION
Erreur_comm exception ; When erreur_comm then Insert into
v_pilot pilote%rowtype ; erreur values(v_pilot.nom, ‘ Commission
BEGIN > salaire’) ;
Select * into v_pilot From Pilote When NO_DATA_FOUND Then Insert into
Where nopilot = ‘7100’ ; erreur values(v_pilot.nopilot, ‘ non
If v_pilot.comm > [Link] trouvé’) ;
Then Raise erreur_comm ; END ;
....

78
Exceptions
RAISE_APPLICATION_ERROR
 Procédure qui délivre des messages d'erreurs définis par l'utilisateur à partir de sous-programmes
stockés.
 Elle est appelée uniquement à partir d'un sous-programme stocké en cours d'exécution.

raise_application_error(error_number, message);

 error_number: valeur (comprise entre -20000 et -20999) définie par l'utilisateur pour l'exception.
 message: message défini par l'utilisateur pour l'exception.
 raise_application_error peut être utilisée dans la section exécutable ou bien dans la section
EXCEPTION

79
Exceptions
RAISE_APPLICATION_ERROR
Exemple 1:

EXCEPTION
WHEN NO_DATA_FOUND THEN
RAISE_APPLICATION_ERROR (-20201, 'Message erreur’);
END;
Exemple 2:
DELETE FROM EMP
WHERE MGR = v_mgr;
IF SQL%NOTFOUND THEN
RAISE_APPLICATION_ERROR(-20202,‘Numéro de MGR éronné');
END IF;
80
Exceptions
RAISE_APPLICATION_ERROR
 Procédure qui délivre des messages d'erreurs définis par l'utilisateur à partir de sous-programmes
stockés.
 Elle est appelée uniquement à partir d'un sous-programme stocké en cours d'exécution.

raise_application_error(error_number, message);

 error_number: valeur (comprise entre -20000 et -20999) définie par l'utilisateur pour l'exception.
 message: message défini par l'utilisateur pour l'exception.
 raise_application_error peut être utilisée dans la section exécutable ou bien dans la section
EXCEPTION

79
Exceptions
RAISE_APPLICATION_ERROR
Exemple 1:

EXCEPTION
WHEN NO_DATA_FOUND THEN
RAISE_APPLICATION_ERROR (-20201, 'Message erreur’);
END;
Exemple 2:
DELETE FROM EMP
WHERE MGR = v_mgr;
IF SQL%NOTFOUND THEN
RAISE_APPLICATION_ERROR(-20202,‘Numéro de MGR éronné');
END IF;
80
Trigger (déclencheur)
Introduction

 Les triggers sont des simples procédures stockées qui s’exécutent implicitement lorsqu’une instruction
INSERT, DELETE ou UPDATE porte sur la table.
 Un traitement procédural lié à une et une seule table.
 Répertorié dans le dictionnaire des données.
 Se déclenche automatiquement lors d'un événement sur la table.
 Reste actif tant que la table existe.
 Peut être activé ou désactivé.

79
Trigger (déclencheur)
Déclaration

CREATE TRIGGER nomDéclencheur

{ BEFORE | AFTER}
{ DELETE | INSERT | UPDATE [OF col1 [,col2]…] } ON tablename
[FOR EACH ROW ]
[WHEN (condition)]
DECLARE
declarations;
BEGIN
bloc d'instructions PL/SQL;
END;

80
Trigger (déclencheur)
Déclaration
 BEFORE | AFTER précise la chronologie entre l’action à réaliser par le déclencheur LMD et la réalisation
de l’événement (exemple BEFORE INSERT programmera l’exécution du déclencheur avant de réaliser
l’insertion).
 DELETE | INSERT | UPDATE précise la nature de l’événement pour les déclencheurs LMD.
 ON tablename spécifie la table, ou la vue, associée au déclencheur LMD.
 FOR EACH ROW différencie les déclencheurs LMD au niveau ligne ou au niveau état.
 WHEN conditionne l’exécution du déclencheur.

81
Trigger (déclencheur)
Variables
Lors de la création de triggers lignes, il est possible d’avoir accès à la valeur ancienne et la valeur nouvelle
grâce aux mots clés OLD et NEW.

 La nouvelle valeur est appelée :[Link]

 L'ancienne valeur est appelée :[Link]

Dans la clause WHEN, les colonnes avec NEW ou OLD sont sans :

82
Trigger (déclencheur)
Types de trigger
 Déclencheur d’instruction ou ordre (sans FOR EACH ROW)
 Exécuté une seule fois avant ou après la totalité de l’événement, indépendamment du nombre de
lignes affectés

 Ne permet pas d’accéder aux ou modifier les valeurs des lignes affectées avant ou après l’événement
(:[Link] et :[Link]) ni de spécifier des conditions WHEN,

83
Trigger (déclencheur)
Types de trigger
CREATE TRIGGER PERSON_UPDATE_SALAIRE
BEFORE UPDATE ON Employe
BEGIN
DBMS_OUTPUT.PUT_LINE(’ Avant la mise à jour de quelque employé’);
END;
Maintenant, exécutant update...
SQL> UPDATE Employe SET sal= sal+(sal*0.1);
Avant la mise à jour de quelque employé
2 rows updated.

84
Trigger (déclencheur)
Types de trigger

 Déclencheur de ligne (avec FOR EACH ROW):

 exécuté une fois pour chaque ligne affectée par l’événement spécifié (insert |update |delete)
 Permet d’accéder aux valeurs des lignes affectées

Si le trigger est "before" les valeurs de la ligne peuvent être changées avant leurs insertions ou
modifications dans la base de données.

85
Trigger (déclencheur)
Types de trigger
CREATE TRIGGER Employe_UPDATE_Salaire
BEFORE UPDATE ON Employe
FOR EACH ROW
BEGIN
DBMS_OUTPUT.PUT_LINE(’Avant la mise à jour ’ || TO_CHAR(:[Link]) || ’ vers ’ ||
TO_CHAR(:[Link]));
END;
Maintenant, exécutant update...
SQL> UPDATE Employe SET sal= sal+(sal*0.1);
Avant la mise à jour 1000 vers 1500 3 rows updated.
Avant la mise à jour 2000 vers 3000
Avant la mise à jour 4000 vers 6000 86
Trigger (déclencheur)
prédicats conditionnels
Quand un trigger comporte plusieurs instructions de déclenchement (par exemple INSERT OR DELETE
OR UPDATE), on peut utiliser des prédicats conditionnels (INSERTING, DELETING et UPDATING) pour
exécuter des blocs de code spécifiques pour chaque instruction de déclenchement.

CREATE TRIGGER t_emp

IF UPDATING('colonne') THEN
BEFORE INSERT OR DELETEOR UPDATE
---traitement pour la mise à jour
ON Emp
END IF;
FOR EACH ROW IF DELETING THEN
BEGIN ---traitement pour la suppression
END IF;
IF INSERTING THEN END;
---traitement pour l'insertion
END IF;
87
Trigger (déclencheur)
Gestion des déclencheurs
Désactivation d'un TRIGGER
ALTER TRIGGER <nom_trigger> DISABLE;
Réactivation d'un TRIGGER
ALTER TRIGGER <nom_trigger> ENABLE;
Désactivation de tous le triggers d'une table
ALTER TABLE <nom_table> DISABLE ALL TRIGGERS;
Désactivation de tous le triggers d'une table
ALTER TABLE <nom_table> ENABLE ALL TRIGGERS;

88
Trigger (déclencheur)
Exercice
Soit le schéma relationnel suivant :

• Employe (id_emp, nom,salaire, niveau)

• Projet (id_projet, nom, heure_travaille, lieu, # id-dir)
• Departement (nom,#id_projet, #id_emp)
• Directeur (id-dir, nom, type)
1) Les salaires des employés doivent toujours être maximisés.
2) Chaque projet à un effective max de 15 employés.
3) Seuls les directeurs principales sont responsables plus de deux projets.
4) Le nombre d’employés de niveau 3 doit être supérieur au nombre d’employés de niveau 2.
5) Les employés du projet dirigés par directeur secondaire est supérieur au nombre d’employés de
niveau 1.
89
PROCEDURES, FONCTIONS ET PACKAGES
Généralité

 On appelle les sous-programmes « fonctions » ou « procédures», car ils sont compilés et résident dans la
base de données.
 Une procédure est un bloc PL/SQL nommé.
 Une fonction est une procédure qui retourne une valeur.
 Il est possible de retrouver leur code au niveau du dictionnaire des donnée
 Lors d’un appel d’une fonction ou d’une procédure, le noyau recompile le programme si un objet cité dans
le code a été modifié (ajout d’une colonne dans une table, modification de la taille d’une colonne…) et le
charge en mémoire.
 Un package est un agrégat de procédures et de fonctions.

90
PROCEDURES, FONCTIONS ET PACKAGES
Avantages
 Les procédures et les fonctions permettent de :
 Réduire le trafic sur le réseau (les procédures sont locales sur le serveur).
 Mettre en oeuvre une architecture client/serveur de procédures et rendre indépendant le code client
de celui des procédures (à l’API près).
 Masquer la complexité du code SQL (simple appel de procédure avec passage d’arguments).
 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.

91
PROCEDURES, FONCTIONS ET PACKAGES
Avantages
 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 à forte cohésion intra (Sélection de tous les
articles, Sélection d’un article, Mise à jour d’un article, Suppression d’un article, Ajout d’un article).

92
PROCEDURES, FONCTIONS ET PACKAGES
Procédures
 Les procédures ont un ensemble de paramètres modifiables en entrée et en sortie.
 Les variables déclarées dans une procédure ne sont pas accessibles en dehors
d’elle-même (variable locale).
CREATE PROCEDURE nomProcédure
[(paramètre1 [ IN | OUT | IN OUT ] typeSQL, . . . ,
[paramètreN [ IN | OUT | IN OUT ] typeSQL)]
{ IS | AS }
[déclarations_de_variables_locales]
BEGIN
section_exécutable;
[section_exception];
END[nom_procedure];
93
PROCEDURES, FONCTIONS ET PACKAGES
Procédures
Argument Signification

IN Valeur par défaut.

Argument en entrée.
Ne être modifiée par la procédure.

OUT Argument en sortie (modifiée par la procédure) .

IN OUT Argument en entrée sortie.

Peut être lue et modifiée par la procédure.

94
PROCEDURES, FONCTIONS ET PACKAGES
Procédures
 Exemple 1:
Compter le nombre d'employés pour un département donné.

CREATE PROCEDURE proc_dept (p_no IN [Link]%TYPE)

IS
v_no NUMBER;
BEGIN
SELECT COUNT(deptno)INTO v_no
FROM emp
WHERE deptno=p_no;
DBMS_OUTPUT.PUT_LINE('Nombre d'employés : '||' '||v_no);

END proc_dept ;
95
PROCEDURES, FONCTIONS ET PACKAGES
Procédures
 Exemple 2:
Exemple de procédure qui modifie le salaire d’un employé.
• Arguments : Identifiant de l’employée, Taux modifie_salaire.sql

CREATE PROCEDURE modifie_salaire (id in number, Taux in number) IS

begin

UPDATE employe set salaire=salaire *(1+taux) where Id_emp= id;

EXCEPTION

when no_data_found then

DBMS_OUTPUT.PUT_LINE(‘Employé inconnu : ‘ ||to_char(id));

End modifie_salaire ;
96
PROCEDURES, FONCTIONS ET PACKAGES
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).

97
PROCEDURES, FONCTIONS ET PACKAGES
Fonctions
CREATE FUNCTION nomFonction
[(paramètre1 [ IN | OUT | IN OUT ] typeSQL, . . . ,
[paramètreN [ IN | OUT | IN OUT ] typeSQL)]
RETURN typeSQL
{ IS | AS }
[déclaration-variabels_locales]
BEGIN
section_exécutable;
RETURN(variable_retour);
[section_exception];
END [nom_fonction];
98
PROCEDURES, FONCTIONS ET PACKAGES
Fonctions
Exemple 1:

Compter le nombre d'employés pour un département donné.

CREATE FUNCTION proc_dept (p_no IN [Link]%TYPE)

RETURN NUMBER AS v_no NUMBER;

BEGIN

SELECT COUNT(deptno) INTO v_no

FROM emp

WHERE deptno=p_no;

RETRUN (v_no);

END proc_dept ;
99
PROCEDURES, FONCTIONS ET PACKAGES
Fonctions
Exemple 2:

Vérifier le salaire pour chaque titre d'employé.

CREATE FUNCTION salaire_ok(salaire REAL, titre [Link]%type) RETURN BOOLEAN IS

sal_minREAL;

sal_maxREAL;

BEGIN

SELECT bas_sal, haut_sal INTO sal_min, sal_max

FROM salaires

WHERE job = titre;

RETURN(salaire>= sal_min) AND (salaire<= sal_max);

END salaire_ok; 100

PROCEDURES, FONCTIONS ET PACKAGES
Appel d’un sous-programme
 L’appel d’un sous-programme dans un programme PL/SQL:
<nom-procédure | nom-fonction> (Liste arguments );
Exemple :
proc_dept(20);
IF salaire_ok(sal_nouv, titre) THEN ...

 L’appel d’un sous-programme sous SQL*Plus:

EXECUTE <nom-procédure | nom-fonction> (Liste arguments );

101
PROCEDURES, FONCTIONS ET PACKAGES
Compilation, Recompilation et Destruction d’un sous-programme
 Compilation un sous-programme:
USER_ERRORS (SELECT LINE,POSITION,TEXT FROM USER_ERRORS WHERE
NAME='nomFonction/nomProcédure’;);
Ou bien
SQL> START nom_ sous-programme ;

SQL> show err ;

 Modifier un sous-programme:
ALTER {PROCEDURE | FUNCTION} nom_ sous-programme COMPILE ;
 Supprimer un sous-programme :
DROP {PROCEDURE | FUNCTION} nom_ sous-programme;

102
PROCEDURES, FONCTIONS ET PACKAGES
Paquetages
 Un package contient deux types de procédures ou fonctions :
 Publiques
 Privées
 Deux parties distinctes dans un package (chaque partie doit être créée et compilée séparément) :
 La partie déclaration ou spécification : qui contient la déclaration des procédures, fonctions, variables et
traitement d’exceptions de type public (accessibles de l’extérieur du package).
 La partie corps ou body : qui contient la définition des procédures ou fonctions de type public déclarées
dans la partie spécification ainsi que les déclarations de ou fonctions de type privé.

103
PROCEDURES, FONCTIONS ET PACKAGES
Paquetages
Un package est composé d’un spécification et d’un corps :

104
PROCEDURES, FONCTIONS ET PACKAGES
Paquetages
Déclaration de la spécification d’un paquetage :

CREATE PACKAGE nomPaquetage

{IS | AS}
[déclaration-de-variable;]
[déclaration-de-curseur;]
[déclaration-de-procédure;]
[déclaration-de-fonction;]
[déclaration-d’exception;]
END [nomPaquetage];

105
PROCEDURES, FONCTIONS ET PACKAGES
Paquetages
 Exemple 1:

CREATE PACKAGE gestionV IS

dateMax CONSTANT DATE := SYSDATE – 30;
FUNCTION leReal (monFilm [Link]%TYPE)
RETURN [Link]%TYPE;
PROCEDURE etablirAgenda(monClient [Link]%TYPE);
END gestionV;

106
PROCEDURES, FONCTIONS ET PACKAGES
Paquetages
Déclaration de l’implémentation d’un paquetage :

CREATE PACKAGE BODY nomPaquetage

{IS | AS}
[définition-de-variable;]
[définition-de-curseur;]
[définition-de-procédure;]
[définition-de-fonction;]
[définition-d’exception;]
END [nomPaquetage];

107
PROCEDURES, FONCTIONS ET PACKAGES
Paquetages
CREATE PACKAGE BODY gestionV
IS
FUNCTION leReal (monFilm [Link]%TYPE) RETURN [Link]%TYPE
IS
R [Link]%TYPE;
BEGIN
SELECT nomIndividu INTO R FROM individu
WHERE numIndividu = (SELECT realisateurv FROM film WHERE titre = monFilm);
RETURN R;
END leReal;

108
PROCEDURES, FONCTIONS ET PACKAGES
Paquetages
PROCEDURE etablirAgenda(monClient [Link]%TYPE)
IS
CURSOR clientCUR IS SELECT * FROM location WHERE login = monClient;
BEGIN
FOR r IN clientCUR LOOP
IF [Link] <= dateMax THEN
DBMS_OUTPUT.PUT_LINE([Link]||[Link]);
END IF;
END LOOP;
END etablirAgenda;
END gestionV;

109
PROCEDURES, FONCTIONS ET PACKAGES
Paquetages
 Référence à un élément d’un package
 En PL/SQL :
[Link]-élément;
 En mode interactif :
EXECUTE [Link]-variable := ...
EXECUTE [Link]-procedure (paramètres-effectifs)
EXECUTE :nom-variable := [Link] (paramètres-effectifs);
 Exemple d’exécution:
EXECUTE [Link] (′mo′);
EXECUTE DBMS_OUTPUT.PUT_LINE ([Link] (′The Departed′));

110
PROCEDURES, FONCTIONS ET PACKAGES
Paquetages
 Suppression d’un package
 Pour la totalité du package :
DROP PACKAGE nom-package;
 Pour seulement le corps :
DROP PACKAGE BODY nom-package;

 Surcharge d’une procédure ou d’une fonction

A l’intérieur d’un package, il est possible de surcharger une procédure ou une fonction, c’est-à-dire de définir
plusieurs procédures ou fonctions avec le même nom mais avec une liste de paramètres différente.
Exemple:
On peut prévoir deux fonctions de même nom qui calculent, pour un réalisateur passé en paramètre, le
nombre de films mis en scène par cette personne. Une fonction aura le nom comme paramètre, c’est-à-dire
une chaîne de caractères, l’autre le numéro d’individu, c’est-à-dire un NUMBER. 111
PROCEDURES, FONCTIONS ET PACKAGES
Exercice

Table Avion Table Vol

Table Pilote Table Depart

Table Escale
Table Passager Table Reservation

112
PROCEDURES, FONCTIONS ET PACKAGES
Exercice
1. Ecrivez un programme PL/SQL qui insère le vol AF110 partant de Paris à 21h40 et arrivant à Du
blin à 23h10 (hypothèse : le vol n’est pas déjà présent dans la table).

2. Ecrivez un programme PL/SQL qui propose les vols pour un tour du monde au départ de Paris avec
des escales et des durées d’escale prédéfinies dans la table Escales. Le nombre d’escales à faire doit
être demandé à l’utilisateur. Hypothèse de travail : pour chaque escale il existe un vol et un seul
satisfaisant les contraintes.

3. Ecrivez un programme PL/SQL qui calcule la moyenne des salaires des pilotes dont l’âge est entre 30 et
40 ans.

113
PROCEDURES, FONCTIONS ET PACKAGES
Exercice
4. Modifiez le programme PL/SQL de l’exercice 2 pour qu’il fonctionne même si plusieurs vols
satisfont les contraintes.

5. Ecrivez une procédure PL/SQL capable de faire des propositions de tours du monde, prenant en
entrée la ville de départ (qui est aussi la destination finale) et deux bornes (supérieure et inférieure) pour le
nombre d’escales. Dans ce cas il n’y a pas de liste prédéfinie d’escales et on ne s’intéresse pas à la durée
des escales. Cherchez à utiliser une procédure ou fonction récursive (PL/SQL admet la récursivité).

6. Transformez le programme du dernier exercice du précédent ED en procédure prenant comme

paramètre le nombre d’escales. Regroupez-la avec la procédure de l’exercice précédent dans un
paquetage. Pouvons-nous définir des curseurs et/ou variables communes entre les deux procédures ?
114