Base de données Avancées
(Language PL/SQL)
Esprit School of Business
Ons Abdelkhalek
Objectif du cours
• Enrichir la formation des étudiants par la
manipulation procédurale d’une BD
• Comprendre les concepts de base d'un SGBD
• Apprendre l'aspect procédural de PL/SQL
(Procedural Language extension to SQL)
• Traiter les résultats des commandes SQL via ce
langage
• Développer des structures de programmes
basées sur des commandes SQL (fonctions,
procédures stockées, packages et déclencheurs).
ESB - 2021/ 2022 3
Plan du cours
– Chapitre 1: Présentation du Langage PL-SQL
– Chapitre 2: Gestion des données et structures de
contrôle
– Chapitre 3: Les curseurs
– Chapitre 4: La gestion des erreurs
– Chapitre 5: Sous programmes stockés : Procédures
et Fonctions
– Chapitre 6: Les packages sous PL-SQL
– Chapitre 7: Les triggers
ESB - 2021/ 2022 4
Le langage PL-SQL
Chapitre 1
Présentation du Langage PL-SQL
1. Introduction
• Le langage SQL est un langage "ensembliste",
c'est-à-dire qu'il ne manipule qu'un ensemble de
données satisfaisant des critères de recherche.
• PL/SQL est un langage "procédural et structuré ",
il permet de traiter de manière conditionnelle les
données retournées par un ordre SQL.
• Créé par Oracle, PL/SQL est utilisé dans le cadre
de bases de données relationnelles.
ESB - 2021/ 2022 7
1. Introduction
• Le langage PL/SQL, abréviation de "Procedural
Language extensions to SQL",
• PL/SQL étend SQL en lui ajoutant les éléments
suivants:
– Les variables et les types.
– Les structures de contrôle et les boucles.
– Les procédures et les fonctions.
– Les types d'objets et les méthodes.
• À la base, PL/SQL est un langage interprété, mais
depuis la version 9i RC1, le code peut être
compilé en code machine.
ESB - 2021/ 2022 8
2. Architecture PL-SQL
ESB - 2021/ 2022 9
2. Architecture PL-SQL
• Le moteur de base de données, Oracle,
coordonne tous les appels en direction de la
base.
• Le SQL et le PL/SQL comportent chacun un
"moteur d'exécution" associé, respectivement le
SQL STATEMENT EXECUTOR et le PROCEDURAL
STATEMENT EXECUTOR.
• Lorsque le serveur reçoit un appel pour exécuter
un programme PL/SQL, la version compilée du
programme est chargée en mémoire puis
exécutée par les moteurs PL/SQL et SQL.
ESB - 2021/ 2022 10
2. Architecture PL-SQL
• Le PL/SQL est utilisé dans de nombreux
produits Oracle, parmi lesquels :
– Oracle Forms et Oracle Reports
– Oracle Application Express
– Oracle Warehouse Builder
ESB - 2021/ 2022 11
3. Caractéristiques du langage PL / SQL
• Par contre, on trouve des Instructions
spécifiques à PL/SQL :
– Définition de variables
– Traitements conditionnels
– Traitements répétitifs
– Traitement des curseurs : parcours de table ligne par
ligne
– Traitement des erreurs
– Traitement des triggers: procédures exécutée lors de
la survenue d’un certain type d’événement sur une
table ou une colonne (ajout d’une ligne, modification
d’une colonne, suppression d’une ligne,…).
ESB - 2021/ 2022 12
4. Composants du langage PL/SQL
• PL/SQL est constitué de:
– Langage de manipulation de données de SQL (DML)
(INSERT, UPDATE, DELETE)
– Langage d’interrogation de données de SQL
(SELECT)
– La gestion de transactions (TCL) (COMMIT,
ROLLBACK, SAVEPOINT)
– Les fonctions standards de SQL
– La partie procédurale
ESB - 2021/ 2022 13
4. Composants du langage PL/SQL
• Il ne comporte pas:
– Langage de définition de données (DDL) : ALTER,
CREATE, RENAME
– Langage de contrôle de données (DCL) : GRANT ,
REVOKE
ESB - 2021/ 2022 14
Chapitre 2
Gestion des données et structures
de contrôle
1. Structure d’un Bloc PL/SQL
[ DECLARE
-- La section déclaration de variables, constantes,
exceptions, curseurs : description des structures et
des variables utilisées dans le bloc. (Cette section est
facultative )
]
BEGIN
-- La section corps du bloc :Instructions SQL et PL/SQL
[ EXCEPTION
-- La section traitement des exceptions (Cette section
est facultative)
]
END [nom_du_bloc] ;
/ ESB - 2021/ 2022 16
1. Structure d’un Bloc PL/SQL
ESB - 2021/ 2022 17
1. Structure d’un Bloc PL/SQL:
Type de Bloc PL/SQL
Anonyme Procedure Fonction
DECLARE PROCEDURE <name> FUNCTION <name>
BEGIN IS RETURN <datatype>
-statements BEGIN IS
EXCEPTION -statements BEGIN
END; EXCEPTION -statements
END; EXCEPTION
END;
ESB - 2021/ 2022 18
1. Structure d’un Bloc PL/SQL
• Chaque instruction se termine par un « ; »
• Les commentaires :
-- sur une ligne
ou
/* sur plusieurs
lignes */
ESB - 2021/ 2022 19
1. Structure d’un Bloc PL/SQL
• Exemple:
SQL> BEGIN
DELETE FROM vol WHERE novol='AF8809';
INSERT INTO vol
(novol,vildep,vilar,dep_h,dep_mn,ar_h,ar_mn,ch_jour)
VALUES ('AF8810','PARIS','DJERBA',9,0,11,45,0);
COMMIT;
END;
/
Procédure PL/SQL terminée avec succès.
ESB - 2021/ 2022 20
1.1 Imbrication de blocs
• Les blocs PL/SQL peuvent être imbriqués les uns dans les autres:
DECLARE
…
BEGIN
DECLARE
…..
BEGIN
……
END ;
………
END ;
/ ESB - 2021/ 2022 21
2. Variables utilisées dans PL/SQL
• La déclaration d'une variable consiste à
allouer un espace pour stocker et modifier
une valeur.
• Elle se fait dans la partie DECLARE du bloc
PL/SQL.
• La variable a un type et peut recevoir une
valeur par défaut et/ou un statut NOT NULL.
ESB - 2021/ 2022 22
2. Variables utilisées dans PL/SQL
• Différents types de variables :
– Variables de types ORACLE
– Variables de type BOOLEAN
– Variables faisant référence au dictionnaire de
données
ESB - 2021/ 2022 23
2.1 Variables de type oracle
• Syntaxe :
Identifiant type [(precisionscale)] [NOT NULL] [:= valeur];
• Les identifiants doivent être différents des noms de colonnes.
• Exemple :
DECLARE
v_deptno NUMBER(2) NOT NULL := 10;
v_hiredate DATE;
v_location VARCHAR2(13) := 'Atlanta';
BEGIN
...
END;
/ ESB - 2021/ 2022 24
2.1.1 Variables de type scalaire
• Binary_integer :
entiers entre -2 147 483 647 et +2 147 483 647
• PLS_INTEGER:
Entier compris entre -2 147 483 647 et +2 147
483 647 (Plus rapide que BINARY_INTEGER car il
utilise les registres du processeur)
• Date : type date d’Oracle
• Rowid : type interne à Oracle composé de 6
octets qui permet d’identifier une ligne unique
d’une table et d’offrir un accès rapide aux
données.
ESB - 2021/ 2022 25
2.1.1 Variables de type scalaire
• Composées
– RECORD
Déclaration d'un type RECORD :
TYPE nom_type IS RECORD (déclaration de propriétés);
Déclaration d'une variable enregistrement de ce
type : nom_variable nom_type;
ESB - 2021/ 2022 26
2.1.2 Variables de type caractère
• Char [(n)] : chaîne de caractère à taille fixe
allant jusqu’à 32 767 caractère (au lieu de 255
dans la définition des colonnes des tables)
• VARCHAR2(n): chaîne de caractère de
longueur variable allant jusqu’à 32 767 (au
lieu de 2000 dans la définition des colonnes
des tables)
• LONG: Chaîne de caractères de longueur
variable avec au maximum 32760 octets
ESB - 2021/ 2022 27
2.1.3 Variable de Type number
• NUMBER[(e,d)]: Nombre réel avec « e »
chiffres significatifs stockés et « d » décimales
• Positive : entiers positifs
• Natural : entiers naturels
• Decimal , float, integer, real : sont des sous-
types de NUMBER
ESB - 2021/ 2022 28
2.1.3 Variable de Type booléen
• Syntaxe :
nom_var BOOLEAN;
• Exemple :
DECLARE
flag BOOLEAN;
BEGIN
...
END;
ESB - 2021/ 2022 29
2.2 Variable de même type qu’un champ
• On peut déclarer une variable en faisant référence à un
champ de table sans connaitre son type et sa taille
• Syntaxe :
nom_var [Link]%TYPE;
• Exemple :
DECLARE
nom [Link]%TYPE;
BEGIN
...
END;
ESB - 2021/ 2022 30
2.3 Variable de même type qu’une autre
variable
• On peut déclarer une variable en faisant référence à une
autre variable sans connaitre son type et sa taille
• Syntaxe :
nom_var2 nom_var1%TYPE;
• Exemple :
DECLARE
ancien_sal NUMBER(5);
nouveau_sal ancien_sal%TYPE; --NUMBER(5);
BEGIN
...
END;
/
ESB - 2021/ 2022 31
2.4 Variable de même structure qu'une
ligne d'une table de la base
• Au lieu de déclarer un ensemble de variables référençant à
chaque colonne de la table, on pourrait déclarer une
variable de même structure qu’une ligne de la table
• Syntaxe :
nom_var table%ROWTYPE;
• Exemple :
DECLARE
ligne pilote%ROWTYPE;
BEGIN
...
END ;
/
ESB - 2021/ 2022 32
2.5 Déclaration de constante
• Une constante est une variable qui ne change pas
de valeur lors du traitement, la déclaration,
comme pour les variables, se fait dans la section
DECLARE
• Syntaxe
Identifiant CONSTANT type [(precision,scale)]
[NOT NULL] [:= valeur];
• Exemple
PI CONSTANT Number (9,5) := 3,14159;
ESB - 2021/ 2022 33
2.6 Affectation de variable
• peut être faite de 2 façons différentes :
– En utilisant l'opérateur :=
exemple
Ma_variable := 10 ;
Ma_chaine := 'Chaîne de caractères' ;
– Ou par une requête
Syntaxe
SELECT propriété1, propriété2, ...
INTO variable_1, variable_2, ...
FROM relations
WHERE condition;
ESB - 2021/ 2022 34
2.6 Affectation de variable
• Exemple
DECLARE
v_deptno NUMBER(2);
v_loc VARCHAR2(15);
BEGIN
SELECT deptno, loc
INTO v_deptno, v_loc
FROM dept
WHERE dname = 'SALES';
... ESB - 2021/ 2022 35
2.6 Affectation de variable
• Les variables logiques reçoivent TRUE , FALSE
ou bien le résultat d’une expression logique.
• Exemple
– Bien payé := V_sal>1550;
– Trouve := True;
ESB - 2021/ 2022 36
2.7 Types composés
• PL/SQL offre deux types composés :
– enregistrement (RECORD)
– et table (TABLE)
ESB - 2021/ 2022 37
2.7.1 Types composés:
Enregistrement
• Déclaration : La déclaration de variable de ce type se
fait:
1) Soit par référence à une structure de table, en utilisant la
notion %ROWTYPE.
Syntaxe :
Nom_varibale nom_table%rowtype;
Exemple:
info_pilote pilote%rowtype;
• Rq: Rappel du schéma de la table pilote:
Pilote (nopilot, nomp, adresse, sal, comm, embauche)
ESB - 2021/ 2022 38
2.7.1 Types composés:
Enregistrement
2) Soit par énumération des rubriques qui la
composent. Dans ce cas, la déclaration se fait
en 2 étapes :
A- Déclaration du type enregistrement :
– Syntaxe : TYPE nom_type IS RECORD (nom_champ
type_champ,…) ;
– Exemple: TYPE t_pilote IS RECORD (nom_pilote
[Link]%type, revenu_pilote number(8,2)) ;
ESB - 2021/ 2022 39
2.7.1 Types composés:
Enregistrement
B- Déclaration de la variable de type
enregistrement :
– Syntaxe : nom_variable nom_type ;
– Exemple : Employe t_pilote ;
ESB - 2021/ 2022 40
2.7.2 Types composés:
Table
• Le type table représente une 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 déclarée de type
BINARY_INTEGER.
• La déclaration de variable de ce type se fait en 2
étapes :
1-Déclaration du type de l’élément de la table
2-Déclaration de la variable de type table
ESB - 2021/ 2022 41
2.7.2 Types composés:
Table
1-Déclaration du type de l’élément de la table :
TYPE nom_type IS TABLE OF type_champ INDEX BY
BINARY_INTEGER;
2-Déclaration de la variable de type table :
nom_variable nom_type ;
• Exemple :
– TYPE t_pilote IS TABLE OF CHAR(35) INDEX BY
BINARY_INTEGER ;
– T1 t_pilote ;
ESB - 2021/ 2022 42
3. Conversion explicite
• TO_CHAR(nombre,format)
Renvoie la chaîne de caractères obtenue en
convertissant « nombre » en fonction de «
format ». Exp: to_char(125, '999')
• TO_DATE(chaîne,format)
Permet de convertir une chaîne de caractères
en type date. Le format est identique à celui
de la fonction TO_CHAR. Exp :
to_date('11 Fev 2013', 'DD Mon YYYY')
ESB - 2021/ 2022 43
4. Conversion implicite
• Affectation d’un nombre dans une chaîne:
V_message VARCHAR2 := 1500;
• Affectation d’un nombre représenté sous
forme de chaine dans une variable
numérique:
V_sal number(9,3) := v_message;
• Conversion entre DATE et CHAR:
V_today VARCHAR2(9) := SYSDATE;
V_date DATE := ’23_FEV_07’;
ESB - 2021/ 2022 44
5. Les Opérateurs
• Opérateurs arithmétiques:
+ - * / **
• Opérateur de concaténation: ||
• Opérateurs de comparaison:
= > < <= >= <>
IS NULL, LIKE, BETWEEN, IN
• Opérateurs logiques
AND, OR, NOT
ESB - 2021/ 2022 45
6. Affichage sur l’écran
• Il est utile d’afficher les valeurs des variables
sur écran
• L’application « DBMS_OUTPUT » permet
d'envoyer des messages depuis un bloc
PL/SQL.
• La procédure « PUT_LINE » de cette
application permet de placer des informations
dans un tampon qui pourra être lu par un
autre bloc PL/SQL.
ESB - 2021/ 2022 46
6. Affichage sur l’écran
• Pour afficher un commentaire:
DBMS_OUTPUT.PUT_LINE(‘texte_mess’);
• Pour afficher le contenu d’une variable on utilise:
DBMS_OUTPUT.PUT_LINE(nom_var);
• L’affichage d’un commentaire suivi de l’affichage du
contenu d’une variable:
DBMS_OUTPUT.PUT_LINE(‘texte_mess’ || nom_var);
• L’affichage ne sera visualisé que lorsque l’ordre SQL
suivant est exécuté dans l’environnement SQL+:
SQL> SET SERVEROUTPUT ON;
ESB - 2021/ 2022 47
6. Affichage sur l’écran
• Exemple:
SQL> SET SERVEROUTPUT ON;
BEGIN
DBMS_OUTPUT.PUT_LINE ('Hello World');
END;
ESB - 2021/ 2022 48
7. Instructions de contrôle
• PL/SQL offre des structures de contrôle:
–Alternatives
–Répétitives.
ESB - 2021/ 2022 49
7.1 Structure alternative
• Cette structure permet l’exécution d’une
séquence d’instructions sous le contrôle d’une
condition. 3 formes existent :
1) IF condition THEN 3) IF condition THEN
{...traitements...} {... traitements...}
END IF; ELSIF condition THEN
2) IF condition THEN {... traitements...}
{... traitements...} ELSE
ELSE {... traitements...}
{... traitements...} END IF;
END IF;
ESB - 2021/ 2022 50
7.1 Structure alternative
…
BEGIN
IF monthly_value <= 4000 THEN
ILevel := 'Low Income';
ELSIF monthly_value > 4000 and monthly_value <= 7000 THEN
ILevel := 'Avg Income';
ELSIF monthly_value > 7000 and monthly_value <= 15000 THEN
ILevel := 'Moderate Income';
ELSE
ILevel := 'High Income';
END IF;
RETURN ILevel;
END;
ESB - 2021/ 2022 51
7.2 Structures répétitives
• LOOP répète indéfiniment une séquence
d’instructions
1) LOOP 2) LOOP
Instructions Instructions
IF condition EXIT WHEN condition;
END LOOP;
THEN EXIT;
END IF;
END LOOP;
ESB - 2021/ 2022 52
7.2 Structures répétitives
• FOR contrôle le nombre d’exécutions des
instructions de la structure répétitive par
incrémentation et test d’une variable indice. Le
pas ne peut être égal qu’à 1.
FOR variable_indice IN [REVERSE] val_debut .. val_fin
LOOP
Instructions
END LOOP;
ESB - 2021/ 2022 53
7.2 Structures répétitives
• WHILE répète les instructions de la structure
répétitive tant que la condition de la valeur est
True.
WHILE condition
LOOP
Instructions
END LOOP;
ESB - 2021/ 2022 54