Ingénierie des Bases de données
2ème Année SI
Chapitre 2
Introduction au Langage PL/SQL
Samir BOUCETTA
[Link]@[Link]
1er Semestre 2023/2024
Sommaire
Syntaxe de PL/SQL
Les types de données
Les structures de contrôle
Les procédures et les fonctions
Les curseurs
Introduction au PL/SQL
Objectifs
Maîtriser la syntaxe de PL/SQL
Écrire des blocs PL/SQL
3
Syntaxe de PL/SQL
Définition de PL/SQL
Langage procédural Structures de
programmation standard :
- L’affectation.
- Déclaration de variables et de constantes.
- Définition de types abstraits;
- Utilisation des structures de contrôle :
– Structure conditionnelle.
– Structure itérative.
- Déclaration de procédure et de fonction.
Extension de SQL
- Intègre directement les instructions de LMD.
4
Syntaxe de PL/SQL
Structure d’un programme PL/SQL
Un programme PL/SQL est composé de blocs
imbriqués, chacun bloc est composé de sections :
–Section de déclaration (optionnelle)
–Section d’exécution (obligatoire)
–Section d’exception (optionnelle)
5
Syntaxe de PL/SQL
Sections d'un programme PL/SQL
Section déclaration
Section exécution
Section exception
Note. Un bloc PL/SQL peut être anonyme ou
nommé telle que les procédures et les
fonctions 6
Syntaxe de PL/SQL
Exemple
On suppose construite la table suivante :
Test(numéro, date_entrée)
Declare S.D.
max_record Constant number:=100;
i number :=1; Bloc
PL/SQL
Begin S.E.
For i in 1 .. max_record loop
Insert into test
Values(i , current_date);
End loop;
End;
7
Syntaxe de PL/SQL
Description
– Le bloc est anonyme (ni procédure, ni fonction).
– Il comporte une section de déclaration et une
section d’exécution:
La section de déclaration (declare)
- Déclaration d’une constante de type number,
- Déclaration d’une variable de type number.
La section d’exécution (begin et end;)
-Instruction itérative: for
-Instruction sql : insert.
8
Syntaxe de PL/SQL
Section de déclaration
Déclaration des variables et des constantes qui seront
Utilisées dans la section d’exécution.
La déclaration d’une variable :
- nom,
- type,
- et peut avoir une valeur initiale,
- et une terminaison par un point virgule.
La syntaxe de déclaration d’une variable
Nom_variable type [ :=valeur_initiale | Default valeur_initiale ];
Note. Par défaut, Les variables PL/SQL sont initialisées à
NULL.
9
Syntaxe de PL/SQL
Section de déclaration (suite)
La déclaration de constante :
-nom,
-type,
-et une valeur initiale.
La syntaxe de déclaration d’une constante :
Nom_constante Constant type_constante := valeur_initiale ;
10
Syntaxe de PL/SQL
Exemples
- Date_naissance Date ;
- Salaire number (8,2) :=8000,00 ;
- Max_nombre Constant number :=100 ;
11
Syntaxe de PL/SQL
Les types de données
-Types SQL :Number, Varchar2, varchar,
char, date
-le type colonne : nom_table.champ%type
-le type ligne (tuples): nom_table%rowtype
-Type défini par l’utilisateur
-Sous-type
12
Syntaxe de PL/SQL
Variables de type colonne
Syntaxe
Nom_variable nom_table.colonne%type ;
Exemple
vsalaire [Link]%type ;
Remarque
Le type de la variable dépend de celui de la colonne de la table.
13
Syntaxe de PL/SQL
Variables de type ligne
Syntaxe
Nom_variable nom_table%Rowtype ;
Exemple1
ligne_etudiant Etudiant%rowtype ;
ligne_etudiant contient tous les
champs de la table étudiant :
- ligne_etudiant.nom :=‘Ali’ ;
- ligne_etudiant.année :=1 ;
14
Syntaxe de PL/SQL
Variables de type ligne(suite)
Exemple2
vemp emp%rowtype
vemp contient tous les champs de la table
emp :
[Link] := 9000 ,00 ;
[Link] := ‘Ali’;
Remarque
Une variable de type ligne dépend de la
structure de la table de base.
15
Syntaxe de PL/SQL
Commentaires
Commentaire ligne
-- Ceci est un commentaire ligne
Commentaire plusieurs lignes
/* Ceci est un commentaire
sur
(+) lignes */
16
Syntaxe de PL/SQL
Section d’exécution
- Section obligatoire délimitée par begin et end
- Comporte l’ensemble des instructions à exécuter
Les instructions incluent :
- Instruction d’affectation.
- Instruction NULL.
- Instruction LMD.
- Structures de contrôles.
- Bloc PL/SQL.
17
Syntaxe de PL/SQL
Instructions d’affectation
Syntaxe
nom_variable | nom_constante := expression ;
Buts
- Affecter la valeur d’une expression à une variable
(section d’exécution)
- Initialiser une variable (section de déclaration)
- Affecter la valeur d’une expression à une constante
(section de déclaration)
Exemples :
i :=i+1 ;
max_nbre constant number :=100;
18
Syntaxe de PL/SQL
Instruction NULL
Syntaxe NULL ;
But Indiquer qu’aucune action ne doit être
exécutée.
Exemple
If i<10 then
i :=i+1 ;
Else
NULL;
End if;
19
Syntaxe de PL/SQL
Structures de contrôle
Les structures conditionnelles
- if
- case
Les structures itératives
- Loop…end loop
- While…end loop
- for…end loop
20
Syntaxe de PL/SQL
La structure if
Syntaxe
If condition_1 then liste_instructions_1;
[elsif condition_2 then liste_instructions_2] ;
…
[elsif condition_n then liste_instruction_n] ;
[else liste_instructions ] ;
end If;
21
Syntaxe de PL/SQL
La structure if (exemple)
If ancienneté > 10 then
Prime :=salaire*1.1;
elsif ancienneté > 5 then
Prime :=salaire*0.5;
else
Prime :=salaire*0.1;
end If;
22
Syntaxe de PL/SQL
La structure case (choix multiple)
Syntaxe 1
Case expr
when val1 then liste_instr1
when val2 then liste_instr2
…
[else liste_instr]
end case;
23
Syntaxe de PL/SQL
La structure case(exemple)
Case a
when 1 then dbms_output.put_line(‘a=1’);
when 1+2 then
dbms_output.put_line(‘a=3’);
else dbms_output.put_line(‘a=‘||a);
end case;
24
Syntaxe de PL/SQL
La structure case (choix multiple)
Syntaxe 2
Case
when condition1 then liste_instr1
when condition2 then liste_instr2
…
[else liste_instr]
end case;
25
Syntaxe de PL/SQL
La structure case(exemple)
Case
when a<=10 then
dbms_output.put_line(‘nombre petit’);
when a > 10 then
dbms_output.put_line(‘nombre grand’);
else dbms_output.put_line(‘a est inconnue’);
end case;
26
Syntaxe de PL/SQL
La structure loop..end loop
Syntaxe
Loop
Liste_instrs
End loop;
But
Exécuter plusieurs fois la liste d’instructions.
Remarque
Ces instructions seront exécutées de manière infinie.
Pour sortir de cette boucle nous utilisons l’instruction EXIT.
27
Syntaxe de PL/SQL
L’instruction Exit condition à satisfaire pour sortir de la
Syntaxe boucle encadrante
Exit [when condition] ;
But
Quitter la boucle qui l’encadre avec ou sans
condition.
Exemple
Loop
i:=i+1;
Exit when i > 100;
End loop; 28
Syntaxe de PL/SQL
La structure while… loop
Syntaxe
while condition loop
liste_instructions;
end loop;
29
Syntaxe de PL/SQL
La structure while… loop (exemple)
begin
i:=1 ;
While i<10 loop
Insert into exemplaires(num_livre,num_invt)
Values(1,i);
i:=i+1;
end loop;
End;
30
Syntaxe de PL/SQL
Instruction For…Loop
Variable déclarée
Syntaxe
For compteur in [reverse] BornInf..BornSup loop
liste_instructions
End loop;
Exemple:
Declare
i int;
Begin
For i in 3..10 loop
Insert into inventaire(num_livre,num_invt)
Values(1,i);
End loop;
31
End;
Syntaxe de PL/SQL
Les instructions SQL (LMD)
PL/SQL intègre les instructions SQL. L’exemple suivant
donne une idée sur l’utilisation des instructions SQL dans un
programme PL/SQL.
Declare
i int ;
Begin
For i in 3..10 loop
Insert into inventaire(num_livre,num_invt)
Values(1,i);
End loop; variable
commit;
SQL
end;
32
Syntaxe de PL/SQL
L’instruction Select
Syntaxe
Select liste_colonnes_exprs
into liste_variables | variable
from liste_tables …
avec
liste_colonnes_exprs = liste_variables.
But : Affectation des valeurs de liste_colonnes_exprs dans
les variables de liste_variables ou dans les champs de la
variable
33
Syntaxe de PL/SQL
L’instruction Select(exemple 1)
declare
salaire_max number;
begin
Select max(salaire)
Into salaire_max
From emp
Where nserv=’1’;
Dbms_output.put_line(‘salaire maximal est’ || salaire_max);
End;
34
Syntaxe de PL/SQL
L’instruction Select(exemple 2)
declare
vemp emp%rowtype;
begin
Select *
Into vemp
From emp
Where nemp=’1’;
Dbms_output.put_line(‘sal :’|| [Link] || ‘ ‘...);
End;
35
Syntaxe de PL/SQL
L’instruction Select(exemple 3)
declare
vsalaire number; vSalaire [Link]%TYPE ;
vnom varchar2(20);
begin
Select nom, salaire
Into vnom,vsalaire
From emp
Where nemp=’1’;
Dbms_output.put_line(‘nom’ || vnom || ‘ ‘ || ‘salaire:’ || vsalaire);
End;
36
Syntaxe de PL/SQL
Procédure
Syntaxe de déclaration
Procedure nom_procedure [( liste_paramètres )] is|as
[liste_variables_constantes]
begin
liste_instructions ;
end [nom_procedure] ;
Chaque élément de liste_paramètres a la forme suivante :
nom_paramètre [IN | OUT | IN OUT] type_données [{:= |default }
valeur_initiale]
Le IN | OUT | IN OUT s’appelle mode du paramètre.
37
Syntaxe de PL/SQL
Procédure (suite)
Le mode du paramètre
–Le mode IN : le paramètre transmit une valeur à la procédure.
le paramètre effectif peut être une constante, une expression ou
une variable initialisée.
–Le mode OUT : le paramètre reçoit une valeur de la procédure.
paramètre effectif doit être une variable.
–Le mode IN OUT : le paramètre transmet une valeur à la
procédure et en reçoit une valeur.
Dans ce cas le paramètre effectif peut être une variable initialisée.
Remarque
Le mode in est le mode par défaut.
Lors de la définition de la procédure on peut spécifier avec le mode IN
une valeur par défaut ce qui n’est pas le cas avec OUT et IN OUT.
38
Syntaxe de PL/SQL
Procédure (suite)
–Liste_variables_constantes :
définit les variables et les constantes
locales de la procédure.
–Liste_instructions:
Liste des instructions de la procédure
39
Syntaxe de PL/SQL
Procédure (exemple 1)
Declare
nemp integer ;
Procedure augment_salaire(num_emp in int) is
Begin
Update emp
Set salaire = salaire *1.1
Where nemp=num_emp;
commit;
End augment_salaire;
Begin -- bloc anonyme
nemp:=10;
augment_salaire(nemp) ;
End ;
Appel à la procédure 40
Syntaxe de PL/SQL
Procédure (exemple 2)
Declare
nemp integer ;
vsal number;
Procedure extraire_salaire(num_emp in int :=1, sal out number)
is
Begin
select salaire into sal from emp
Where nemp=num_emp;
End extraire_salaire;
Begin -- bloc anonyme
nemp:=10;
extraire_salaire(nemp, vsal); Appel à la procédure
-- extraire_salaire(num_emp =>nemp , sal =>vsal);
dbms_output.put_line(vsal);
End ;
41
Syntaxe de PL/SQL
Procédure (exemple 3)
Declare
-- nemp integer ;
vsal number;
Procedure extraire_salaire(num_emp in int :=1, sal out number)
is
Begin
select salaire into sal from emp
Where nemp=num_emp;
End augment_salaire;
Begin -- bloc anonyme
-- nemp:=10;
extraire_salaire(sal => vsal); Appel à la procédure
dbms_output.put_line(vasl);
End ;
42
Syntaxe de PL/SQL
Fonction
Syntaxe de déclaration
Function nom_fonction [(liste_paramètres)] return type
is
[liste_variables_constantes]
begin
liste_instructions ;
return valeur_retour ;
end [nom_fonction] ;
43
Syntaxe de PL/SQL
Fonction (exemple)
Construire une fonction qui calcule la somme des salaires d’un
service donné en argument.
Declare
num_serv integer;
Function service_salaire(num_service in integer) return number is
Total_salaire number ;
Begin
select sum(salaire) into Total_salaire
From emp
Where nserv=num_service;
Return Total_salaire;
End service_salaire;
Begin --bloc anonyme
num_serv :=10;
dbms_output.put_line(service_salaire(num_serv));
End;
Appel à la fonction 44
Syntaxe de PL/SQL
Bloc anonyme
Syntaxe
Declare
Liste_variables_constantes_procedures_fonctions ;
Begin
Liste_instructions ;
End ;
45
Syntaxe de PL/SQL
Bloc anonyme (exemple)
Construire un bloc qui augmente de 10%, le salaire des
employés de numéro 1 à 10. La valeur maximale sera déclarée
comme une constante.
Declare
max constant int :=10 ;
procedure modif_salaire is
begin
update emp
set salaire =salaire*1.1
where nemp between 1 and max ;
commit;
end;
begin
modif_salaire;
end;
46
Syntaxe de PL/SQL
Procédure et fonction stockées
Une procédure stockée est une procédure PL/SQL créée par la
commande create et stockée dans la BD.
Syntaxe de création
Create [or replace] procedure nomProcedure(…)
is|as
déclarations
begin
…
end;
/
Pour supprimer une procédure :
Drop procedure nomProcedure;
Pour compiler une procédure
Alter procedure NomProcedure compile;
47
Syntaxe de PL/SQL
Procédure et fonction stockées(suite)
Une fonction stockée est une fonction PL/SQL créée par la commande
create et stockée dans la BD.
Syntaxe
create [or replace] function nomFonction(…) return
type
is|as
…
Pour supprimer une fonction :
Drop function nomFunction;
Pour compiler une fonction
Alter function nomfonction Compile;
48
Syntaxe de PL/SQL
Procédure stockée(exemple )
Calcul de la somme de salaire d’un service
Create procedure somSal(pserv number)
Is
sal number;
Begin
Select sum(salaire) into sal from emp
Where nserv=pserv;
Dbms_output.put_line('somme des salaires'||sal);
End;
49
Syntaxe de PL/SQL
Fonction stockée(exemple )
Create Function somSal(pserv number) return
number
is
sal number;
Begin
Select sum(salaire) into sal from emp
Where nserv=pserv;
return sal;
End;
50
Syntaxe de PL/SQL
Les curseurs
Définition
–Un curseur est une structure qui décrit un ensemble de lignes issues d’une
requête select
–Un curseur est caractérisé par un nom
–Un curseur permet la manipulation individuelle des lignes.
Types de curseurs
–Curseur implicite ==> si la requête select retourne au plus une ligne(into)
–Curseur explicite ==> si la requête select retourne plusieurs lignes
Les curseurs explicites doivent être déclarés
Syntaxe
Cursor nom_curseur is
Requête_séléction ;
51
Syntaxe de PL/SQL
Curseur (exemple)
Cursor C is
Select num_etudiant , prenom , nom
From etudiant
Where annee=’1’ ;
52
Syntaxe de PL/SQL
Manipulation d’un curseur
Ouverture d’un curseur
Open nomCurseur ;
Cette instruction réalise les tâches suivantes :
1. Exécution de la requête associée au curseur.
2. Récupération des lignes.
3. Mettre (le pointeur) le curseur avant la première ligne.
Récupération des lignes du curseur
Fetch nom_curseur into liste_variables |variable ;
Cette instruction fait aussi passer le curseur(pointeur) à la ligne
suivante.
Fermeture d’un curseur
Close nom_curseur ;
53
Syntaxe de PL/SQL
Manipulation d’un curseur(exemple )
Declare begin
Cursor c is --définition open c ; --ouverture
Select salaire, nemp loop --parcours de lignes
from emp fetch c into v_salaire , v_nemp;
where nserv=1; exit when c%notfound;
if v_salaire< 5000 then
v_salaire [Link]%type; update emp
v_nemp [Link]%type ; set salaire=salaire*1.1
where nemp=v_nemp;
end if;
end loop;
close c; --fermeture du curseur
end;
54
Syntaxe de PL/SQL
Cursor for..loop
Ce type de curseur résume les instructions
i. Open,
ii. fetch
iii. et close
Par
For variable in nomCurseur loop
…
End loop;
index
55
Syntaxe de PL/SQL
Curseur for..loop (exemple )
Declare
Cursor c is --définition
Select salaire, nemp
from employe
where nserv=1;
Index non déclaré
begin
for ligne in c loop
if [Link]< 5000 then
update emp
set salaire=salaire*1.1
where nemp=[Link];
end if;
end loop;
end;
56
Syntaxe de PL/SQL
Curseur avec paramètres
Un curseur peut avoir des paramètres
Syntaxe
Cursor Nom_curseur (liste_paramètres) is
Requêtes_ sélection ;
Les paramètres effectifs sont transmis lors de l’ouverture
du curseur :
-Open nom_curseur (liste_paramètres) ;
-For variable in nom_curseur (liste_paramètres) loop…
Le mode des paramètres est in.
57
Syntaxe de PL/SQL
Curseur avec paramètres (exemple )
Declare
Cursor c (pnserv int) is --définition
Select salaire, nemp
from employe
where nserv=pnserv;
begin
for ligne in c(10) loop Paramètre effectif
if [Link]< 5000 then
update emp
set salaire=salaire*1.1
where nemp=[Link];
end if;
end loop;
end;
58
Syntaxe de PL/SQL
Curseur avec paramètres(exemple )
Declare Begin
Cursor c ( pnserv int default 1) is vnserv:=10;
Select salaire, nemp open c(vnserv) ; --ouverture
from employe loop --parcours de lignes
where nserv=pnserv; fetch c into v_salaire , v_nemp;
exit when c%notfound;
v_salaire [Link]%type; if v_salaire< 5000 then
v_nemp [Link]%type ; update emp
vnserv int; set salaire=salaire*1.1
where nemp=v_nemp;
end if;
end loop;
close c; --fermeture du curseur
59
end;
Syntaxe de PL/SQL
Curseur automatique
-Curseur sans nom
-La sélection associée est définie à l’exécution
-La variable de parcours n’est pas déclarée.
Syntaxe de création Requête seléction
For ligne in (sélection) loop
Liste_instrs Variable non
déclarée
End loop;
60
Syntaxe de PL/SQL
Curseur variable
Curseur qu’on peut ouvrir avec des requêtes
sélections différentes.
Par exemple
- Open c for select * from emp;
close c;
- Open c for select * from service;…
61
Syntaxe de PL/SQL
Curseur variable (suite)
=>
- Définition d’un type de référence de
curseurs
Syntaxe
type nomType is Ref cursor;
Exemple
type Tcv is ref cursor;
62
Syntaxe de PL/SQL
Curseur variable (suite)
-Déclaration d’une variable curseur
Variable_curseur type_curseur
-Exemple
c Tcv;
c accepte différentes requêtes SQL.
-Ouverture d’un curseur variable
Open variable_curseur for sélection;
Note. Fetch et close 63
Syntaxe de PL/SQL
Curseur variable (exemple)
En utilisant (séparément) le même curseur:
-Afficher le nom et le prénom des employés
-Afficher le nombre d’employés et la moyenne
des salaires
64
Syntaxe de PL/SQL
Curseur variable (exemple)
Declare
type tcv is ref cursor;
c tcv;
vnom [Link]%type;
vprénom [Link]énom%type;
vmoy_sal number ;
vcompt_emp number;
Begin
-->
65
Syntaxe de PL/SQL
Curseur variable (exemple suite)
begin
open c for select nom, prénom from emp;
dbms_output.put_line('nom prénom');
loop
fetch c into vnom, vprénom;
exit when c%notfound;
dbms_output.put_line(vnom || ' ' || vprénom);
end loop;
close c; -->
66
Syntaxe de PL/SQL
Curseur variable (exemple fin)
open c for select count(nemp), avg(salaire) from
emp;
fetch c into vcompt_emp, vmoy_sal;
dbms_output.put_line ('nbre emp :'|| vcompt_emp);
dbms_output.put_line('moyen salaire :'|| vmoy_sal );
close c;
end; -- fin du bloc
67
Syntaxe de PL/SQL
Curseur et MAJ des données
Un curseur s'utilise pour MAJ des données
Utilisation de
-for update pour verrouiller les données à MAJ.
-Current of pour référencer la ligne en cours dans le
curseur
Syntaxe
cursor nomCursor [(liste_paramètres)] is
req_sélection
for update [of liste_colonnes] [no wait| wait
nbre_secondes]
68
Syntaxe de PL/SQL
Curseur et MAJ des données(exemple 1)
declare
cursor c is select nemp, nom, salaire, com from emp for
update;
begin
for enr in c loop
if [Link] is null then
update emp
set com=salaire*0.1
where current of c;
end if;
end loop;
end;
69
Syntaxe de PL/SQL
Curseur et MAJ des données(exemple 2)
declare
cursor c is select nemp, nom, salaire, com from emp for
update;
begin
for enr in c loop
if [Link] is null then
update emp
set com=salaire*0.1
where current of c;
end if;
end loop;
end;
70
