École Polytechnique de Sousse
Enseignante : Amira Dhokar
Section : 3ème année Ing Info
Bases de données avancées
TP 1 : Initiation au langage PL/SQL
Objectifs du TP :
— S’initier au langage PL/SQL,
— Comprendre la différence entre SQL et PL/SQL,
— Écrire un bloc PL/SQL simple.
1 Notions théoriques
1.1 Présentation du langage PL/SQL
PL/SQL (Procedural Language/Structured Query Language) est une combinaison
de SQL avec les fonctionnalités procédurales des langages de programmation. Il a été
développé par Oracle Corporation au début des années 90 pour améliorer les capacités de
SQL. PL/SQL est l’un des trois principaux langages de programmation intégrés dans la
base de données Oracle, avec SQL lui-même et Java.
— PL/SQL est un langage de traitement transactionnel hautement portable et per-
formant.
— PL/SQL fournit un environnement de programmation intégré, interprété et indé-
pendant du système d’exploitation.
— PL/SQL peut également être appelé directement depuis l’interface de ligne de
commande SQL*Plus.
— Un appel direct peut également être effectué à partir d’appels de langage de pro-
grammation externe vers la base de données.
— La syntaxe générale de PL/SQL est basée sur celle du langage de programmation
ADA et Pascal.
1.2 Avantages de PL/SQL
PL/SQL présente les avantages suivants :
— PL/SQL permet d’envoyer un bloc entier d’instructions à la base de données en
une seule fois. Cela réduit le trafic réseau et offre des performances élevées pour
les applications.
— PL/SQL offre une productivité élevée aux programmeurs car il peut interroger,
transformer et mettre à jour les données d’une base de données.
— PL/SQL permet de gagner du temps aux niveaux conception et débogage grâce à
des fonctionnalités puissantes, telles que la gestion des exceptions, l’encapsulation,
le masquage des données et les types de données orientés objet.
1
— Les applications écrites en PL/SQL sont entièrement portables.
— PL/SQL offre un niveau de sécurité élevé.
1.3 Différence entre SQL et PL/SQL
SQL PL/SQL
SQL est une requête unique utilisée PL/SQL est un bloc de codes utilisé
pour effectuer des opérations LMD et pour écrire l’intégralité des blocs de
LDD. programme/procédure/fonction, etc.
Exécuté en une seule instruction. Exécuté comme un bloc entier.
Utilisé pour manipuler des données. Utilisé pour créer une application.
Ne peut pas contenir de code PL/SQL. C’est une extension de SQL, donc il
peut contenir du SQL à l’intérieur.
1.4 Structure d’un bloc PL/SQL
Un bloc PL/SQL est composé de trois sections :
DECLARE
-- Section de d c l a r a t i o n ( optionnelle )
-- Variables , constantes , curseurs
BEGIN
-- Section d ’ e x c u t i o n ( obligatoire )
-- Instructions SQL et PL / SQL
EXCEPTION
-- Section d ’ exceptions ( optionnelle )
-- Gestion des erreurs
END ;
1.5 Déclaration de variables et constantes
En PL/SQL, une variable ou constante est déclarée en respectant la syntaxe suivante :
identifier [ CONSTANT ] datatype [ NOT NULL ] [ := | DEFAULT expr ];
1.5.1 Types classiques :
— VARCHAR2(n) : Chaîne de caractères de longueur variable
— CHAR(n) : Chaîne de caractères de longueur fixe
— NUMBER(p,s) : Nombre avec précision p et échelle s
— DATE : Date
— BOOLEAN : Booléen (TRUE, FALSE, NULL)
— BINARY_INTEGER : Entier
Exemples :
v_gender CHAR (1) ;
v_count BINARY_INTEGER := 0;
v_total_sal NUMBER (9 , 2) := 0;
2
v_order_date DATE := SYSDATE ;
c_tax_rate CONSTANT NUMBER (3 , 2) := 8.25;
v_valid BOOLEAN NOT NULL := TRUE ;
1.5.2 L’Attribut %TYPE :
Il permet de déclarer une variable à partir :
— D’une autre variable déclarée précédemment,
— De la définition d’une colonne de la base de données.
On doit préfixer %TYPE avec :
— La table et la colonne de la base de données
— Le nom de la variable déclarée précédemment
PL/SQL détermine le type de donnée et la taille de la variable.
Exemple :
[Link]%TYPE est le type de l’attribut titre de la table Film.
1.5.3 L’Attribut %ROWTYPE :
L’attribut %ROWTYPE est utilisé pour déclarer des variables PL/SQL de type enre-
gistrement avec des zones qui correspondent aux colonnes d’une table ou d’une vue.
Le nombre de colonnes, ainsi que les types de données des colonnes de la table de
référence peuvent être inconnus.
Ce type est utile lorsqu’on recherche :
— Une ligne avec l’ordre SELECT.
— Plusieurs lignes avec un curseur explicite.
Exemple :
DECLARE
agent employe % ROWTYPE ; -- employe est la table employe de la
base
BEGIN
SELECT * -- S l e c t i o n de tous les champs
INTO agent
FROM employe
WHERE nom = ’ DUMAS ’;
END ;
1.5.4 Blocs Imbriqués et Portée d’une Variable
Les variables déclarées dans un bloc externe sont visibles dans les blocs internes. Les
variables déclarées dans un bloc interne ne sont pas visibles dans le bloc externe.
1.6 Opérateurs en PL/SQL
Par défaut, en PL/SQL, on pourra appliquer les opérateurs suivants :
— Logiques (AND, OR, NOT),
— Arithmétiques (+, -, *, /),
— Concaténation (||),
— Opérateur exponentiel (**),
— Parenthèses pour contrôler l’ordre des opérations.
3
1.7 Interaction avec Oracle
1.7.1 Récupérer des Données (Extraire des données)
Pour retrouver des lignes de la base de données avec le SELECT on pourra utiliser la
syntaxe suivante :
SELECT select_list
INTO variable_name | record_name
FROM table
WHERE condition ;
Exemple :
Pour retrouver toutes les informations d’un employé donné :
DECLARE
v_nom emp . nom % TYPE ;
v_emp emp % ROWTYPE ;
BEGIN
SELECT nom INTO v_nom FROM emp WHERE matr = 500;
SELECT * INTO v_emp FROM emp WHERE matr = 500;
END ;
1.7.2 Insertion de Données
Exemple :
Pour ajouter de nouveaux employés à la base de données :
DECLARE
v_emp emp % ROWTYPE ;
v_nom emp . nom % TYPE ;
BEGIN
v_nom := ’ ALAMI ’;
INSERT INTO emp ( matr , nom ) VALUES (600 , v_nom ) ;
v_emp . matr := 610;
v_emp . nom := ’ TOTO ’;
INSERT INTO emp ( matr , nom ) VALUES ( v_emp . matr , v_emp . nom ) ;
COMMIT ;
END ; -- Fin du bloc PL / SQL
2 Partie pratique
Exercice 1 :
Parmi les déclarations de variables suivantes, déterminer celles qui sont incorrectes :
v_id NUMBER (4) ;
v_x , v_y , v_z VARCHAR2 (10) ;
name VARCHAR2 (30) ;
Pi CONSTANT NUMBER := 3.14159;
v_en_stock BOOLEAN := 1;
4
Exercice 2 :
Écrire un script PL/SQL qui permet de déclarer une variable de chaîne de caractères
puis d’affecter à cette variable votre nom et prénom.
Exercice 3 :
Écrire un script PL/SQL qui permet de saisir deux nombres a et b, puis d’effectuer
une permutation entre eux. Afficher les deux nombres avant et après la permutation.
Exercice 4 :
Soit le modèle relationnel relatif à la base « Gestion des magasins » suivant :
— Produit (Numprod, Desprod, Poids, Qte_stk, #NumMag)
— Magasin (NumMag, AdresseM, Surface)
— Client (NumClt, NomC, AgeClt, AdresseC)
— Commande (NumClt, Numprod, DateC, QteC, Prix_vente)
Avec :
— Les attributs soulignés sont des clés primaires
— Les attributs précédés par le caractère « # » sont des clés étrangères
— Numprod, NumMag et NumClt : sont de type numérique de taille 6
— Desprod, NomC, AdresseC et AdresseM : sont de type chaîne de caractères de
longueur maximale 30
— L’âge de client est de type numérique de taille 2
— Poids, Qte_stk, Surface, QteC, Prix_vente : sont des réels de taille 8 chiffres au
total dont trois chiffres après la virgule
— NumClt et NumProd : Clé primaire multiple
— DateC est de type date
1. Écrire un script SQL permettant de créer les tables.
2. Écrire un script SQL permettant d’insérer un enregistrement dans chaque table.
3. Écrire un script PL/SQL pour insérer deux autres enregistrements dans chaque
table.
4. Écrire un script PL/SQL permettant d’afficher toutes les informations d’un client
donné.
5. Écrire un script PL/SQL permettant d’augmenter la quantité en stock d’un produit
donné de 5.
6. Supprimer la ligne du produit = savon.