Ingénierie des bases de données
Le Langage PL/SQL
Procedural Language / Structured
Query Language
Auditoire : LGLSI 2
Mahmoud Ltaief1
Chapitre1: Présentation du PL/SQL
Mahmoud Ltaief 2
1. PL/SQL?
• Extension de SQL
• Langage de programmation procédural
• Clauses SQL intégrées dans le code procédural
• PL/SQL est donc un langage de programmation propre à
Oracle :
• Intègre directement les clauses SQL d’interrogation, de
manipulation (généralement pas de définition des données)
• Permet l’encapsulation des données dans du code
• Gestion des exceptions
Mahmoud Ltaief 3
2. Environnement PL/SQL
Mahmoud Ltaief 4
3. Bénéfices de PL/SQL
PL/SQL regroupe les requêtes SQL en un seul bloc qui est
envoyé au serveur en un seul appel
=> Amélioration des performances (moins de communications sur
le réseau)
Permet de créer des bibliothèques de code réutilisable
=> Programmation de fonctions, procédures, packages
Mahmoud Ltaief 5
4. Les programmes PL/SQL
• Bloc anonyme :
Bloc PL/SQL imbriqué dans une application ou créé
interactivement
• Procédure enregistrée :
Bloc nommé enregistré dans le serveur Oracle et qui peut être
invoqué par son nom
• Package :
Module PL/SQL qui regroupe un ensemble de procédures
• Trigger :
Bloc associé à une table et déclenché automatiquement lors
d’une requête
Mahmoud Ltaief 6
5. Bloc PL/SQL
Mahmoud Ltaief 7
5. Bloc PL/SQL
Mahmoud Ltaief 8
6. Exécution d’un bloc PL/SQL
Mahmoud Ltaief 9
Chapitre 2: Les variables
Mahmoud Ltaief 10
1. Les variables dans PL/SQL
Utiliser les variables pour :
• L’enregistrement temporaire des données comme dans tout
langage de programmation procédural.
• La manipulation de données enregistrées afin de réaliser des
calculs et autres manipulations sans accéder à la base de
données
• La réutilisabilité: une fois déclarée, une variable peut être
utilisée plusieurs fois dans une application.
• La facilité de maintenance: en utilisant %TYPE et
%ROWTYPE (présenté plus tard), on déclare des variables
ayant le même type que des attributs de tables, si la définition
de la table change, le type de la variable change de même.
=> Réduction des coûts de maintenance
Mahmoud Ltaief 11
2. Les types de variables
• Scalaires
Valeurs simples, les principaux types sont ceux de SQL
• Composites
Les enregistrements permettent de définir des données
structurées
Mahmoud Ltaief 12
3. Déclaration de variables
• Syntaxe :
<nom> [CONSTANT] <type> [NOT NULL]
[ := | DEFAULT <expression> ] ;
• Exemple :
DECLARE
dateEmprunt_v DATE ;
noDept _v NUMBER(2) NOT NULL := 10 ;
lieu_v VARCHAR2(13) := ‘Paris’ ;
taux_c CONSTANT NUMBER := 20 ;
• Note : constantes et variables NOT NULL doivent être
immédiatement affectées
Mahmoud Ltaief 13
4. Les conventions
• Toute variable doit être déclarée pour pouvoir être utilisée
• Une variable au maximum déclarée par ligne
• Les noms de variable doivent commencer par une lettre et ont
30 caractères au maximum
• Portée de bloc pour les variables, masquage de variables
dans les sous-blocs
• Nom des tables, attributes, ..., objets de la base ont la
priorité sur les noms de variables
• Par convention, utiliser les suffixes :
‘_c’ pour les constantes
‘_v’ pour les variables
‘_g’ pour les variables globales
Mahmoud Ltaief 14
5. Affectation de valeurs dans une variable
• Affectation d'une valeur à une variable :
<nom_de_variable> := <expression>
• Stockage du résultat d'une requête :
SELECT . . .
INTO <nom_de_variable>
FROM . . . WHERE . . .
Mahmoud Ltaief 15
6. Types scalaires PL/SQL
• CHAR [(<taille_max>)]
chaînes de caractères de longueur fixe (max 32767)
• VARCHAR2 (<taille_max>)
chaînes de caractères de longueur variable (max 32767)
• NUMBER [(<p>, <s>)]
nombres réels, p chiffres en tout, s après la virgule
• PLS_INTEGER
Prennent moins de place et sont plus rapides que les valeurs
de type number et binary_integer
• DATE
• BOOLEAN
trois valeurs possibles : TRUE, FALSE et NULL
Mahmoud Ltaief 16
7. Types définis par l’utilisateur
• Syntaxe :
TYPE <nom_type> IS RECORD (
<nomchamps> <type> [ [NOT NULL]
[ := <expression> ] ], ... ) ;
• Exemple :
SQL> DECLARE
TYPE client_t IS RECORD (
numero NUMBER(4),
nom CHAR(20) ,
adresse CHAR(20) ) ;
client1_v client_t ;
BEGIN
client1_v.numero := 2516 ;
...
END ;
/
Mahmoud Ltaief 17
8. Type tableau
• Syntaxe :
TYPE <nom_type> IS VARRAY (<size>) OF <type>
• Exemple :
DECLARE
TYPE tab_emp IS VARRAY(10) OF VARCHAR2(64);
tab1 tab_emp;
BEGIN
tab1 := tab_emp();
tab1.EXTEND(3);
tab1(1) := ‘CLARK’;
tab1(2) := ‘STONE’;
...
DBMS_OUTPUT.PUT_LINE( tab1(1) ) ;
END ;
/
Mahmoud Ltaief 18
9. L’attribut %TYPE et %ROWTYPE
• %TYPE : permet d’identifier dynamiquement le type d’un variable
à partir de :
– la définition d’un attribut de table
– la définition d’une autre variable déclarée précédemment
Remarque: les contraintes NOT NULL de la définition des
attributs de tables ne sont pas conservées avec %TYPE
Exemple :
nomEmploye_v employe.nomEmp%TYPE ;
solde_v NUMBER(7, 2) ;
soldeMinimal_v solde_v%TYPE := 2000 ;
• %ROWTYPE: identifie dynamiquement le type (structuré) d’un
n-uplet d’une table
Exemple :
dateCommande_v Commande.dateCommande%TYPE ;
commande_v Commande%ROWTYPE ;
Mahmoud Ltaief 19
10. Les opérateurs
• Arithmétique: +, -, *, /, **
• Concaténation de chaîne: ||
• Parenthèse pour contrôler les priorités des opérations
• Affectation :=
• Comparaison
=, <>, <, >, <=, >=, IS NULL, LIKE, BETWEEN, IN
• Logique: AND, OR, NOT
Mahmoud Ltaief 20
Exemples
• Incrémenter l’index d’une boucle
compte_v := compte_v + 1;
• Affectation de la valeur d’un drapeau booléen
egales_v := (n1_v = n2_v);
• Rechercher si une variable a une valeur
affecte_v := (nemp_v IS NOT NULL)
Mahmoud Ltaief 21
Chapitre 3: Les structures de contrôle
Mahmoud Ltaief 22
1. Les structures conditionnelles
Syntaxe :
IF <condition> THEN
commandes ;
[ELSIF <condition> THEN
commandes ; ]*
[ELSE
commandes ; ]
END IF ;
Note :
vous pouvez utiliser l’expression IS NULL dans les
conditions
Mahmoud Ltaief 23
Exemple:
IF nomEmploye_v = ‘Tiger’ THEN
salaire_v := salaire_v * 3 ;
ELSIF nomEmploye_v = ‘Scott’ THEN
salaire_v := salaire_v * 2.5 ;
ELSE
salaire_v := salaire_v * 2 ;
END IF;
Mahmoud Ltaief 24
2.1. CASE
Syntaxe:
CASE expression
WHEN expr1 THEN instruction 1;
WHEN expr2 THEN instruction 2;
…
ELSE instructionsN;
END CASE;
Mahmoud Ltaief 25
2.2. Attributs associés aux requêtes
– SQL%ROWCOUNT
Nombre de n-uplets affectés par la dernière requête SQL (entier)
– SQL%FOUND
Booléen, vaut TRUE si la dernière requête SQL a affecté au moins
un tuple et faux sinon
– SQL%NOTFOUND
Booléen, vaut TRUE si la dernière requête SQL n’a affecté aucun
tuple et faux sinon
– SQL%ISOPEN
Booléen indiquant si le curseur est ouvert ou fermé (par défaut,
les curseurs implicites sont toujours fermés à la fin de la requête)
• Note :
A la place de ‘SQL’, utilisez le nom de votre curseur pour identifier
l’état d’un curseur explicite
Mahmoud Ltaief 26
3. Les structures itératives (La boucle LOOP)
Syntaxe:
LOOP
Instructions;
EXIT WHEN condition;
END LOOP;
Exemple :
DECLARE
numEmp_v NUMBER (3) := 1;
BEGIN
LOOP
INSERT INTO Emp (numEmp, nomEmp, jobEmp, numDpt)
VALUES (numEmp_v, ‘Analyste’ || to_char(numEmp),
‘Analyste’, 10) ;
numEmp_v := numEmp_v +1 ;
EXIT WHEN numEmp >= 10 ;
END LOOP ;
END ; Mahmoud Ltaief 27
4. Les structures itératives (La boucle FOR)
Syntaxe :
FOR <compteur> IN [REVERSE] <limite_inf> .. <limite_sup>
LOOP
commandes ;
END LOOP
Exemple :
DECLARE
numEmp_v NUMBER (3);
BEGIN
FOR numEmp_v IN 1 .. 10
LOOP
INSERT INTO Employe (numEmp, nomEmp, jobEmp,
numDpt)
VALUES (numEmp_v, ‘Analyste’ ||
to_char(numEmp_v),‘Analyste’, 10) ;
END LOOP ;
END ; Mahmoud Ltaief 28
5. Les structures itératives (La boucle WHILE)
Syntaxe :
WHILE <condition> LOOP
commandes ;
END LOOP ;
Exemple :
DECLARE
numEmp_v NUMBER (3);
BEGIN
numEmp_v := 1;
WHILE noEmp_v <= 10
LOOP
INSERT INTO Employe (numEmp, nomEmp, jobEmp,
numDpt)
VALUES (numEmp_v, ‘Analyste’|| to_char(numEmp_v),
‘Analyste’, 10);
numEmp_v := numEmp_v +1 ;
END LOOP ;
END ; Mahmoud Ltaief 29
