Base de données avancée:
PL/SQL
CHAPITRE2: PL/SQL
PROCEDURAL LANGUAGE/SQL
Asma ELADEL
[email protected]
A.U: 2021/2022
PL/SQL
2
PL/SQL(ProceduralLanguage/SQL) est un langage
procédural d’ORACLE structuré en BLOCS.
Extension du SQL: des requêtes SQL intégrées avec
les structures de contrôle habituelles (alternatives,
répétitives )
Un programme PL/SQL est composé de fonctions, de
procédures, de triggers..
Peut s’exécuter comme bloc anonyme, procédure ou
une fonction
Asma ELADEL
PL/SQL: Intérêt
3
L’intérêt du PL/SQL est de pouvoir dans un même
traitement combiner la puissance des instructions SQL et
la souplesse d’un langage procédural.
Le fonctionnement de PL/SQL est basé sur
l’interprétation d’un bloc de commandes.
Dans l’environnement PL/SQL, les ordres SQL et
PL/SQL sont regroupés en blocs; un bloc ne demande
qu’un seul transfert pour l'exécution de l’ensemble des
commandes contenues dans le bloc.
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.
Asma ELADEL
PL/SQL
4
1. PL/SQL: un langage de programmation
Il contient un ensemble d'instructions permettant de
mettre en oeuvre les principes de l'algorithmique:
Déclaration de variables, de types, de fonctions,
de procédures
Instructions d'affectation, conditionnelles,
itératives
Fonctions numériques et de manipulations de
chaînes de caractères, de dates
Asma ELADEL
PL/SQL
5
2. Intégration du langage SQL
Commande SELECT
Commande INSERT, UPDATE, DELETE
Commande de gestion de transaction COMMIT,
ROLLBACK, SAVEPOINT
Utilisation de fonctions SQL : TO_CHAR,
TO_NUMBER, SUBSTR, ...
Asma ELADEL
PL/SQL
6
3. Gestion de curseurs
Instructions DECLARE, OPEN, FETCH, CLOSE
4. Gestion des erreurs
Gestion standard des erreurs par ORACLE
Définition et gestion de traitements d'erreurs propres
au développeur
5. PL/SQL dans le monde ORACLE
Disponible dans les outils de développement
ORACLE*FORMS, SQL*plus, ...
Définition de Curseurs, de triggers, de procédures
stockées.
Asma ELADEL
Les blocs PL/SQL
7
Le bloc est l'unité de programmation PL/SQL.
Un programme ou une procédure PL/SQL est un
ensemble de un ou plusieurs blocs. Chaque bloc
comporte trois sections :
1. Section déclaration (optionnelle)
2. Section corps du bloc ou d’exécution (obligatoire)
3. Section Gestion des exceptions e traitement des
erreurs (optionnelle)
Les blocs peuvent être imbriqués.
Asma ELADEL
Structure d'un bloc PL/SQL
8
DECLARE
/* Déclaration des variables, des types, des curseurs,
fonctions et procédures */
BEGIN
/* Instructions PL/SQL ; tout instruction est terminée
par ; */
EXCEPTION
/* Traitement des erreurs */
END;
Asma ELADEL
Structure d'un bloc PL/SQL
9
Un bloc PL/SQL peut contenir:
Toute instruction du LMD ( SELECT, INSERT, UPDATE,
DELETE)
Les commandes de gestion des transactions ( COMMIT,
ROLLBACK, SAVEPOINT )
Les sections DECLARE et EXCEPTION sont optionnelles.
Chaque instruction se termine par ;
Les blocs peuvent être imbriqués
Les sous blocs ont la même structure que les blocs.
Une variable est visible dans le bloc où elle est déclarée et
dans tous ses sous-blocs.
Si une variable est déclarée dans un premier bloc et aussi
dans un sous bloc, la variable du bloc supérieur n’est plus
visible dans le sous-bloc
Asma ELADEL
Bloc PL/SQL: Déclaration
10
1. Section déclaration
Dans cette section, on déclare toutes les variables
nécessaires à l’exécution du programme PL/SQL.
Section facultative
Commence par le mot clé DECLARE
Asma ELADEL
Bloc PL/SQL: Déclaration
11
Identificateurs Oracle : 30 caractères au plus,
Commence par une lettre,
Peut contenir lettres, chiffres, _, $ et #
Doivent être déclarées avant d’être utilisées
Déclarations multiples interdites.
Asma ELADEL
Bloc PL/SQL: Déclaration
12
La partie déclarative dans un bloc PL/SQL, peut
comporter les déclarations suivantes:
Déclarations des variables scalaires, constantes
Déclaration des variables curseurs
Déclarations des types composés
Enregistrement(Record),
Table
Asma ELADEL
Bloc PL/SQL: Déclaration
13
Type de variables
Scalaires : reçoivent une seule valeur
Composées: permettent de définir des groupes de champs et de les
manipuler dans des blocs PL/SQL (tels que les records) et Table.
Types de données scalaires
Nombres: X Number(7,5);
Y Number;
Caractères: S Char(20); -- Chaîne de taille fixe
T Varchar2(30); -- Chaîne de taille variable
Dates: D Date NOT NULL := SYSDATE;
Booléens: B Boolean : = True;
Asma ELADEL
Bloc PL/SQL: Types de variables
14
Asma ELADEL
Bloc PL/SQL: Types de variables
15
Asma ELADEL
Bloc PL/SQL: Déclaration
16
Types issus de SQL
CHAR, NUMBER, DATE, VARCHAR2
Types PL/SQL
BOOLEAN, SMALLINT, BINARY_INTEGER,
DECIMAL, FLOAT, INTEGER, REAL, ROWID
Asma ELADEL
Bloc PL/SQL: Déclaration des variables scalaires
17
Variables ou constantes
Nom-de-variable [CONSTANT ]Type [NOT NULL ][ := DEFAULT
|Expr ];
Expr : une constante ou un calcul faisant éventuellement référence à une
variable précédemment déclarée. L'initialisation est obligatoire si
CONSTANT ou NOT NULL sont utilisés.
Exemple: DECLARE
Nom_duclient char (30);
X number:=12;
Y number:=X*X;
x VARCHAR2(10);
PI constant number(7,5):=3.14159 ;
Asma ELADEL
Bloc PL/SQL: Déclaration des variables scalaires
18
Variables ou constantes
Nom-variable nom-table.nom-attribut%TYPE;
Exemple :
film exemplaire.numFilm%TYPE;
Asma ELADEL
Bloc PL/SQL: Déclaration %TYPE
19
Variables %TYPE
Syntaxe
Nom_var [CONSTANT] Identifiant%TYPE [NOT NULL]
[ :=expr_pl/sql] ;
Exemples
V_COMPT COMPTEUR%TYPE;
V_NOMP PRIX.NOMP%TYPE;
Asma ELADEL
Bloc PL/SQL: Déclaration %TYPE
20
Variables %TYPE: Avantages
• Le type de données de la colonne peut être inconnu.
• Le type de données de la colonne peut changer en exécution.
Exemple
DECLARE
v_last_name s_emp.last_name%TYPE;
v_first_name s_emp.first_name%TYPE;
v_balance NUMBER( 7, 2 );
v_minimum_balance v_balance%TYPE := 10;
Asma ELADEL
Bloc PL/SQL: Déclaration pour un
enregistrement %ROWTYPE
21
Variables %ROWTYPE
Syntaxe
Nom_var [CONSTANT] Identifiant%ROWTYPE [NOT NULL] [ :=expr_pl/sql] ;
La déclaration pour un enregistrement se fait:
Soit par référence à une structure de table
nom-variable nom-table%ROWTYPE;
de curseur en utilisant ROWTYPE
nom-variable nom-curseur%ROWTYPE;
Asma ELADEL
Bloc PL/SQL: Déclaration %ROWTYPE
22
Variables %ROWTYPE: Avantages
• Le nombre de colonnes, ainsi que les types de données des colonnes de la
table de référence peuvent être inconnus.
• Le nombre de colonnes, ainsi que le type des colonnes de la table de
référence peuvent changer en exécution
• Utile lorsqu'on recherche
– Une ligne avec l'ordre SELECT.
– Plusieurs lignes avec un curseur explicite.
Exemple
DECLARE
dept_record s_dept%ROWTYPE;
emp_record s_emp%ROWTYPE;
Asma ELADEL
Bloc PL/SQL: Déclaration %ROWTYPE
23
Exemples
DECLARE
agent employe%ROWTYPE -- employe est la table employe de la base.
Au niveau traitement, on pourra écrire :
BEGIN
SELECT * -- Sélection de tous les champs
INTO agent BEGIN
SELECT nom,dt_entree
FROM employe
INTO agent.nom, agent.dt_entree
WHERE nom=‘DUMAS’; OU FROM employe
END; WHERE nom=‘DUMAS';
-- Sélection de certains champs
Asma ELADEL
Bloc PL/SQL: Déclaration pour une Table
24
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
Cet index est déclaré de type BINARY_INTEGER
Asma ELADEL
Bloc PL/SQL: Déclaration pour une Table
25
Deux étapes :
• Déclaration du type de l’élément de la table
• Déclaration de la variable de type table
Asma ELADEL
Bloc PL/SQL: Déclaration pour une Table
26
Déclaration du type de l’élément de la table :
TYPE nom-du-type-table
IS TABLE OF type-argument
INDEX BY BINARY_INTEGER;
Déclaration de la variable de type table:
nom-variable nom-du-type-table;
Asma ELADEL
Exemple de déclaration pour une Table
27
DECLARE
TYPE tabNom IS TABLE OF VARCHAR2(20)
INDEX BY BINARY_INTEGER;
tableNom tabNom;
i BINARY_INTEGER;
BEGIN
tableNom(5) := 'Dupont';
i := 10;
tableNom(i) := 'Dupond';
END;
/
Asma ELADEL
Bloc PL/SQL: Déclaration des Curseurs
28
Variables Curseur
Le programmeur ne déclare dans la section DECLARE que les curseurs dits
explicites c'est à dire une zone mémoire associée à une requête select qui
permet de récupérer les n-uplets de la requête.
Syntaxe
CURSOR nom_curseur [(nom_paramètre type_paramètre)] IS
requête_select [FOR UPDATE OF nom_attribut]
Exemples :
CURSOR CURSEUR1 IS
Select nomp,ct
From Prix
Where nomf='SAMACO';
CURSOR Bonclient(V_solde number(8)) IS
Select nomc,adresc
From client
Where solde>V_solde;
Asma ELADEL
Exemple de déclaration
29
DECLARE
NumeroNUMBER(4);
nom VARCHAR2(30);
Salaire NUMBER(8,2);
Date_naissanceDATE;
// assignation de valeur
AugmentatationNUMBER (4) := 5;
Groupe VARCHAR2(10) := ‘groupe1’;
// utilisation du %TYPE. Permet de déclarer des variable de même
type que des variables déjà déclarée.
Numero_Client yacoubsa.clients.numclient%type
permet de déclarer le numéro du client du même type que le
numclient de la table clients de l’usager yacoubsa.
Asma ELADEL
Exemple de déclaration
30
Suite Exemple de déclarations:
Salaire_MIN NUMBER(7,2);
Salaire_MAX Salaire_Min%TYPE;
Acteur VARCHAR2(30);
Realisateur Acteur%TYPE:=‘Spielberg’;
// on peut donner le type ROWTYPE lorsqu’il s’agit
de déclarer un enregistrement (une ligne).
Enregistrement etudiant%ROWTYPE
Asma ELADEL
Bloc PL/SQL: Affectation
31
Assignation (Suite)
Par l’opérateur d’affectation : :=
Nom_variable := expression ;
Exemple
X := 0 ;
Y := ( X+5) * Y ;
Par la clause SELECT….INTO
Exemple: DECLARE
Nom Dept.Dname%type ;
BEGIN
SELECT Dname INTO Nom
FROM Dept WHERE DeptNo=20
END;
Instruction FETCH avec un curseur
Asma ELADEL
Bloc PL/SQL: Corps du bloc
32
2. Section corps du bloc
Contient les instructions du programme et
éventuellement, à la fin, la section de traitement des
erreurs
Obligatoire
Introduite par le mot clé BEGIN
Se termine par le mot clé END
Asma ELADEL
Bloc PL/SQL: Structures de contrôle
33
Structure alternative
Structure répétitives
Asma ELADEL
Structures alternatives
34
Asma ELADEL
Structures alternatives
35
Condition
< Opérande > < Opérateur > < Opérande >
Opérateurs utilisables
= ; ! = ; <> ; < ; <= ;>; >=
IS [NOT] NULL
[NOT] BETWEEN …. AND
[NOT] IN
[NOT] LIKE
AND,OR,NOT
Avec plusieurs instructions dans THEN, il faut utiliser un bloc
(Begin).
Asma ELADEL
Structures alternatives: Exemple 1
36
Asma ELADEL
Structures alternatives: Exemple 2
37
Asma ELADEL
IF –END IF
38
Dans une alternative, les mot réservés IF, THEN et
END IF sont obligatoires. Les autres (ELSIF et ELSE)
sont optionnels
•Exercice: écrire un bloc PL/SQL qui permet de
déclarer deux variable de type NUMBER(vente et
bonus) et qui met à jour le salaire de l’employé
comme suit: (salaire = salaire+bonus)
–Si vente est > 1000 alors bonus = vente*50%
–Sinon bonus = vente *20%
Asma ELADEL
CASE ---WHEN
39
•L’instruction CASE: permet d’exécuter un bloc PL/SQL selon la valeur d’une
variable
•Exemple:
CREATE OR REPLACE PROCEDURE CASE1(CHOIX IN NUMBER) AS
BEGIN
CASE CHOIX
WHEN 1 THEN INSERT INTO employes(salaire )VALUES (44,28000);
WHEN 2 THEN UPDATE employes SET salaire = salaire +10
where numemp=10;
ELSE dbms_output.put_line('pas bon choix');
END CASE;
END;
Asma ELADEL
CASE ---WHEN
40
Asma ELADEL
Structures répétitives
41
Asma ELADEL
Structures répétitives
42
Asma ELADEL
Structures répétitives
43
Asma ELADEL
Structures répétitives: LOOP–EXIT WHEN–END LOOP
44
LOOP
…..
EXIT WHEN CONDITION ou EXIT
…..
END LOOP;
Exemple: Afficher les entiers de 1 à 10.
i:= 1 ;
LOOP
….
i:= i +1 ;
EXIT WHEN i >10 ;
…. if i>10 then EXIT; end if;
END LOOP;
Asma ELADEL
Structures répétitives: Exemple
45
CREATE OR REPLACE FUNCTION COMPTER
RETURN NUMBER AS
Compteur NUMBER :=0;
BEGIN
LOOP
compteur:=compteur+1; Obligation d'utiliser la commande EXIT
EXIT WHEN compteur=10; pour éviter une boucle infinie,
facultativement quand une condition est
END LOOP ; vraie.
RETURN compteur;
END;
Asma ELADEL
Loop avec EXIT
46
Lors de l’exécution d’un loop, on peut décider de sortir immédiatement de la
boucle avec la clause EXIT
Exemple
DECLARE
Credit NUMBER := 0;
BEGIN
LOOP
Credit:= Credit+ 1;
IF Credit> 3 THEN
EXIT; --on sort du loop
END IF;
END LOOP;
--pour afficher le résultat du looptout de suite
DBMS_OUTPUT.PUT_LINE ('Credit: ' || TO_CHAR(Credit));
END;
Asma ELADEL
Structures répétitives: FOR-IN-LOOP-END
47
FOR compteur IN [ REVERSE] inf...sup LOOP
…..
END LOOP;
Exemple
FOR I IN 1..10 LOOP
J:= J* 3 ;
INSERT INTO T VALUES ( I, J );
J:= J* 3 ;
INSERT INTO T VALUES ( I, J );
END LOOP;
Asma ELADEL
Structures répétitives: FOR-IN-LOOP-END
48
BEGIN
FOR i IN 1..3 LOOP
DBMS_OUTPUT.PUT_LINE (TO_CHAR(i));
END LOOP;
END;
Asma ELADEL
Structures répétitives: While-LOOP
49
DECLARE
I NUMBER:=1;
BEGIN
WHILE I < 10 LOOP
I:= I+1;
DBMS_OUTPUT.PUT_LINE (TO_CHAR(I));
END LOOP;
END;
Asma ELADEL
NULL
50
Signifie "ne rien faire " , passer à l’instruction suivante
Exemple:
IF I>=10 THEN
NULL
ELSE
INSERT INTO Dept VALUES ( 50, 'Marketing', 'Casa');
END IF ;
Asma ELADEL
Affichage
51
Activer le retour écran :
set serveroutput on
Asma ELADEL
Affichage: Exemple
52
set serveroutput on
DECLARE
i NUMBER;
BEGIN
FOR i IN 1..5 LOOP
dbms output.put line('Nombre : ' || i) ;
END LOOP ;
END ;
/
Si '/' seul sur une ligne : n d'une définition (déclenche
l‘évaluation).
Asma ELADEL
Affichage: Exemple
53
DECLARE
emp1 Employes%ROWTYPE; -- record basé sur la
structure d'une ligne de la table Eemployes;
BEGIN
SELECT * INTO emp1 FROM Employes WHERE Id=3;
-- afficher les données du record
dbms_output.put_line('Nom : '|| emp1.Nom);
dbms_output.put_line('Salaire : '|| emp1.Salaire);
dbms_output.put_line('Age : '|| emp1.Age);
END;
Asma ELADEL
Les Exceptions
54
3. Section traitement des erreurs
Facultative
Introduite par le mot clé EXCEPTION
Asma ELADEL
Les exceptions: Syntaxe
55
BEGIN
...
EXCEPTION
WHEN exception1 THEN
...
WHEN exception2 THEN
...
WHEN OTHERS THEN --optionnel
...
END;
Asma ELADEL
Les exceptions: Déclaration
56
DECLARE
Nom-exception EXCEPTION ;
...
BEGIN
...
Declenchement d'exceptions :
RAISE Nom-exception;
Asma ELADEL
Les Exceptions: Définition
57
Une erreur ou avertissement PL+SQL peut survenir en
cours d'exécution et soulever une exception.
Une exception ne provoque pas nécessairement l'arrêt du
programme si elle est saisie par un bloc (dans la partie
EXCEPTION ).
Une exception non saisie remonte dans la procédure
appelante (ou elle peut être saisie).
Deux types d’exceptions:
Prédéfinie par le système ou
Déclarée/ définie par l'utilisateur (programmeur).
Asma ELADEL
Les Exceptions: Traitement
58
Le traitement d'une exception se fait par la règle
when
when<nomd'exception> then <sequence d'instructions>;
où la séquence d'instructions est exécuté quand
l'exception donnée est soulevée.
Asma ELADEL
Exceptions prédéfinies
59
Exemples d'exceptions système
CURSOR_ALREADY_OPEN: tentative d'ouverture de curseur déjà
ouvert
INVALID_CURSOR: par exemple fetch sur un curseur déjà fermé
NO_DATA_FOUND: aucune ligne retournée (select into ou fetch)
TOO_MANY_ROWS: select into retourne plusieurs lignes
...
ZERO_DIVIDE: tentative de division par zéro
Exemple d'usage
when NO DATA FOUND then rollback;
Asma ELADEL
Exceptions utilisateur
60
Elles doivent être déclarées avec le type EXCEPTION.
On les lève avec l'instruction RAISE.
DECLARE
salaire NUMBER(8,2) ;
salaire trop bas EXCEPTION ;
BEGIN
select sal into salaire from emp where matr = 50 ;
IF salaire < 300 THEN
RAISE salaire trop bas ;
END IF ;
EXCEPTION
WHEN salaire trop bas THEN
dbms output.put line(`Salaire trop bas') ;
WHEN OTHERS THEN
dbms output.put line(SQLERRM) ;
END ;
Asma ELADEL
Exception: Exemple 1
61
Augmenter de 5% les salaires des employés du
département '123' sans toutefois dépasser 4000
Asma ELADEL
Les exceptions: Exemple1
62
Les traitements d'exceptions sont définis à la fin du
bloc instructions par la clause Exception.
La variable tropGrand est déclarée de type
exception, pour être utilisée dans raise.
Asma ELADEL
Les exceptions: Exemple 2
63
DECLARE EXCEPTION
WHEN NO_DATA_FOUND THEN
erreurEx EXCEPTION; DBMS_OUTPUT.PUT_LINE('numero
num exemplaire.numExemplaire inconnu');
%TYPE; WHEN erreurEx THEN
DBMS_OUTPUT.PUT_LINE(num || '
film exemplaire.numFilm%TYPE; probleme');
pb exemplaire.probleme%TYPE; END;
BEGIN /
SELECT numExemplaire, numFilm, probleme
INTO num, film, pb
FROM exemplaire WHERE numExemplaire = 1;
IF probleme IS NOT NULL
THEN RAISE erreurEx; END IF;
DBMS_OUTPUT.PUT_LINE(num || ' OK');
Asma ELADEL
Les exceptions: Exemple 3
64
Asma ELADEL
