PL / SQL

4ème année GI Noreddine Gherabi

 Langage PL/SQL

PLAN
q Introduction,
q Avantages de PL/SQL,
q Les principales caractéristiques du PL / SQL,
q Les variables et les constantes,
q Les instructions de base
q Interaction avec la base de données à partir de PL/SQL
q Les curseurs
q Gestion des exceptions
q Les procédures, Les fonctions…
q Les triggers
 Introduction

q PL/SQL (Procedural Language / SQL), l’extension procédurale

proposée par Oracle pour SQL,
q Il permet de combiner des requêtes SQL (SELECT, INSERT,
UPDATE et DELETE) et des instructions procédurales (boucles,
conditions...),
q Créer des traitements complexes destinés à être stockés sur le
serveur de base de données (objets serveur),
q Comme on le sait, les structures de contrôle habituelles d’un
langage (IF, WHILE…) ne font pas partie intégrante de la
norme SQL. Oracle les prend en compte dans PL/SQL.
 Introduction

Id_Produit Libellé Marque Prix ID_Fournisseur

P1 HP600 HP 1000 1
P2 Epson30 Epson 2300 1
P3 IBM 7380 IBM 4600 2
P4 Lexmark310 Lexmark 2000 3

Update produit set prix = 2500 where id_fournisseur=1

CODE PL/SQL
Pour le fournisseur N°1 Procedure :
IF … THEN
Si Moyenne(prix) >2000
Augmenter le prix de 5% ELSEIF
Sinon
Augmenter le prix de 3% ELSE

END IF
 Avantage du langage PL/SQL

Ce langage propose des-performances pour le traitement des

transactions et offre les avantages suivants :
—
q Intégration complète du SQL,
q Prise en charge de la programmation orientée objet (surcharge),
q Parfaite Intégration avec Oracle et Java, en fait PL/SQL est un
langage propriétaire d’Oracle,
- On peut lancer des sous-programme PL/SQL à partir de Java et
de même, on peut appeler des procédures Java à partir d’un bloc
Pl/SQL
 Avantage du langage PL/SQL

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.

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
 Langage PL/SQL

Construction de procédures ou fonctions stockées qui

améliorent le mode client-serveur par stockage des
procédures ou fonctions souvent utilisées au niveau
serveur :

q Gestion des erreurs

q Construction de triggers (ou déclencheurs)
 Langage PL/SQL

Un programme ou une procédure PL/SQL est un

ensemble d’un ou plusieurs blocs. Chaque bloc comporte
trois sections :

1. Section déclaration
2. Section corps du bloc
3. Section traitement des erreurs
 Structure de bloc PL/SQL

DECLARE (facultatif)

Déclarer les objets PL/SQL à utiliser dans ce bloc

BEGIN (obligatoire)

Définir les instructions exécutables

EXCEPTION (facultatif)

Définir les actions à entreprendre en cas d'erreur ou d'exception

END; (obligatoire)
 Structure de bloc PL/SQL

Les types des blocs

 Outils de programmation PL/SQL

Outil: SQLPLUS Outil : SQL Developer

 Mon premier programme PL/SQL

SET SERVEROUTPUT ON;

BEGIN
DBMS_OUTPUT.put_line (’ Bonjour tout le monde’);
END ;
/
 PL / SQL
Déclaration des variables

4ème année GI Noreddine Gherabi

 Déclaration des variables

q Les types habituels correspondants aux types SQL2 ou Oracle :

integer, varchar,...

q Les types composites adaptés à la récupération des colonnes et des

lignes des tables SQL : %TYPE, %ROWTYPE
Règles :

§ Adopter les conventions de dénomination des objets.

§ Initialiser les constantes et les variables déclarées NOT
NULL.
§ Initialiser les identifiants en utilisant l'opérateur d'affectation
( := ) ou le mot réservé DEFAULT.
§ Déclarer au plus un identifiant par ligne.
 Déclarer des variables PL/SQL

Syntaxe :
identifier [CONSTANT] datatype [NOT NULL]
[:= | DEFAULT expr];

Exemples
:
DECLARE
v_DateNaissance DATE;
v_NDept NUMBER(2) NOT NULL := 10;
v_Ville VARCHAR2(13) := ’Rabat';
c_ CodeP CONSTANT NUMBER := 1400;
v_valide BOOLEAN NOT NULL := TRUE;
 Déclarer des variables PL/SQL
v Les types d’oracle
 Déclarer des variables avec l'attribut %TYPE

Une variable prend le même type qu’une autre variable :

...
v_Nom client.nom%TYPE;
v_age NUMBER(7,2);
v_min_age v_age%TYPE := 10;
...

RQ : NOT NULL ne s’applique pas aux variables déclarées avec

%TYPE.
 Créer un enregistrement PL/SQL

Déclarer des variables pour le stockage du nom, du poste et du salaire

d'un nouvel employé.
Exemple :
...
TYPE emp_record_type IS RECORD
( nom VARCHAR2(25),
fonction VARCHAR2(10),
salaire NUMBER(8,2)
);

emp_record emp_record_type;
...

RQ : - NOT NULL peut être appliqué au champ

- champ peut être déclaré avec %TYPE.
 Entrée- sortie dans PL/SQL

v Il est évidemment possible d’utiliser des fonctions prédéfinies d’entrée/

sortie. Ces fonctions servent essentiellement durant la phase de test des
procédures PL/SQL

Pour entrer une valeur : ACCEPT msg PROMPT

Pour afficher une valeur: DBMS_OUTPUT.put_line ('Test : ' || message);
Exemple :
ACCEPT msg PROMPT ‘Entrer votre message’
DECLARE
message varchar2(100):= ‘&msg’;
BEGIN
DBMS_OUTPUT.put_line ('Test : ' || message);
END;
 PL / SQL
Les instructions de base

4ème année Génie Informatique Noreddine Gherabi

 Instructions de base

IF-THEN-ELSIF
v L'instruction IF-ELSE est une des premières structures conditionnelles que l'on
apprend en programmation. Elle est composée d'une condition. Si cette condition
est vraie, alors les instructions après le IF sont exécutées. Si la condition est
fausse, se sont les instructions sous le ELSE qui sont exécutées.

Exemple :
. . .
IF v_start > 100 THEN
v_start := 2 * v_start;

ELSIF v_start >= 50 THEN

v_start := 0.5 * v_start;
ELSE
v_start := 0.1 * v_start;
END IF;
. . .
 Instructions de base

Instruction Case
Dans le langage SQL, la commande “CASE … WHEN …” permet d’utiliser
des conditions de type “si / sinon” (cf. if / else) similaire à un langage de
programmation pour retourner un résultat disponible entre plusieurs
possibilités
Exemple :
DECLARE
Mention char(1);
BEGIN
CASE
WHEN Mention =‘T’ THEN
DBMS_OUTPUT.PUT_LINE(’Mention Très bien’);
WHEN Mention =‘B' THEN
DBMS_OUTPUT.PUT_LINE('Mention bien’);
ELSE
DBMS_OUTPUT.PUT_LINE(‘Mention Passable’);
END CASE;
END;
 Instructions de base

Les boucles
 Instructions de base

Boucle simple

Exemple :

DECLARE
Compte Number:=0;
BEGIN
LOOP
DBMS_OUTPUT.PUT_LINE(Compte);
compte := compte + 1;
EXIT WHEN compte > 10;
END LOOP;
END;
 Instructions de base

Boucle FOR

Exemple :

DECLARE

BEGIN
FOR i IN 1..10 LOOP
DBMS_OUTPUT.PUT_LINE( i );
END LOOP;
END;
 Instructions de base

Boucle WHILE

Exemple :

DECLARE
i INTEGER;
BEGIN
While i < 10 LOOP
DBMS_OUTPUT.PUT_LINE( i );
i:=i+1;
END LOOP;
END;
 PL / SQL
Interaction avec la base de données
Les collections

4ème année Génie Informatique Noreddine Gherabi

 Interaction avec la base de données

La clause INTO en PL/SQL

La clause INTO permet de passer des valeurs d’une table dans des variables
Exemple :
DECLARE
v_id NUMBER(4);
v_ville varchar(20);
BEGIN
SELECT id_Emp, ville_Emp
INTO v_id, v_ville
FROM Employe
WHERE nom_Emp= ’Ahmadi';
...
END;
 Interaction avec la base de données

Mettre à jour des données

Augmenter le salaire de tous les employés du service
commercial dans la table EMPLOYE.
Exemple :

DECLARE
v_salaire employe.salaire%TYPE := 2000;
BEGIN
UPDATE employe
SET salaire = salaire + v_salaire
WHERE service= ’commercial';
END;

RQ : En l'absence de modification de ligne, aucune erreur ne se produit

 Interaction avec la base de données

Supprimer les données

Supprimer les lignes appartenant au service achat à partir de la table

EMPLOYE
Exemple :
DECLARE
v_service employe.service%TYPE := ‘achat’;
BEGIN
DELETE FROM employees
WHERE service = v_service;
END;
 Instructions COMMIT et ROLLBACK

v Initialiser une transaction avec la première instruction

LMD suivant COMMIT ou ROLLBACK

v Utiliser les instructions SQL COMMIT et ROLLBACK

pour mettre fin explicitement à une transaction
 Déclarer des variables avec l'attribut %ROWTYPE

Déclarer une variable permettant de stocker les mêmes informations

relatives à un employé telles qu'elles figurent dans la table
EMPLOYEES.

Exemples

DECLARE
Emp_record Employees%ROWTYPE;
...
BEGIN
SELECT * INTO Emp_record
FROM Employees
WHERE ...
 Gestion des tableaux

Un tableau en PL/SQL est en fait une table qui ne comporte qu’une seule colonne. Cette
colonne ne peut correspondre qu’à un type scalaire et non à un type composé (table ou
record).
La structure de données nécessaire pour gérer une table ORACLE nécessitera donc
autant de types tables que la table possède de colonnes. On pourra éventuellement
regrouper ces tables à l’intérieur d’un RECORD ( Enregistrement).

0 1 2 3 4 5
Text0 Text1 Text2 Text3 Text4 Text5

TYPE tableau IS TABLE OF VARCHAR(20)

INDEX BY BINARY_INTEGER;
 Gestion des tableaux

DECLARE
TYPE nom_type_tableau IS TABLE OF VARCHAR(20)
INDEX BY BINARY_INTEGER;
-- Déclaration de la variable tab de type nom_type_tableau

tab nom_type_tableau ;

BEGIN
tab(1) := ‘ligne 1’ ;
tab(2) := ‘ligne 2’ ;

-- Affichage du premier élément de la variable tab,

-- c'est à dire "ligne 1“
IF tab.EXISTS(1) THEN
DBMS_OUTPUT.PUT_LINE(tab(1)) ;
END IF;
END ;
 Gestion des tableaux

Les fonctions d’un tableau

Fonction
COUNT retourne le nombre d’éléments de la table
EXISTS (n) retourne TRUE si l’élément n existe
LAST retourne la valeur du dernier index
FIRST retourne le premier index
NEXT (n) élément suivant
PRIOR(n) élément précédent
DELETE supprime toute la table
DELETE(n) supprime l’élément n

Exemple :
IF table_name.EXISTS(1) THEN
INSERT INTO ...
...