- Partie 2 -

1
Curseurs

2
 Les curseurs

Toutes les requêtes SQL sont associées à un curseur

Ce curseur représente la zone mémoire utilisée pour

exécuter la requête

• il existe deux types de curseurs:

- Curseurs implicites

- Curseurs explicites

3
Les curseurs implicites

4
 Les curseurs implicites
• Ils sont associés aux ordres SELECT, INSERT, DELETE
et UPDATE.

• ils sont déclarés automatiquement par ORACLE lors

de l'exécution de la requête.

• Attention : un seul enregistrement doit être le résultat

pour une requête SELECT

5
• Exemple d’un curseur implicite :

DECLARE
v_deptno NUMBER(2);

.....
BEGIN
SELECT deptno
INTO v_deptno  Obligatoire
FROM dept
WHERE upper(deptname) = ‘INFORMATIQUE’;
…
END;

6
Les curseurs explicites

7
 Les curseurs explicites

• Les curseurs explicites servent à retourner plusieurs lignes

avec un Select

• Les curseurs explicites sont déclarés explicitement par le

programmeur.

• Curseur explicite : une commande Select déclaré et nommé

8
 Les curseurs explicites
– Un curseur explicite, contrairement au curseur implicite
est géré par l'utilisateur pour traiter un ordre Select qui
ramène plusieurs lignes
– Tout curseur explicite géré dans la section exécution doit
avoir été déclaré dans la section déclarative

• Exigences du curseur explicite

– Déclaration Section
– Ouverture DECLARE
– Accès aux lignes du curseur
Section
– Fermeture
EXECUTABLE
9
 Les curseurs explicites
Un curseur explicite doit être explicitement :
– déclaré dans la section DECLARE
– géré par le développeur dans la section exécutable.
– La gestion d ’ un curseur consiste à exécuter les
opérations suivantes :
• ouverture du curseur,
• lecture et traitement des lignes contenues dans le
curseur,
• fermeture du curseur.

10
 Déclaration des curseurs
Syntaxe:
CURSOR nom_du_curseur IS
un énoncé SELECT ;

• Ne pas inclure la clause INTO dans la déclaration du curseur.

• Si le traitement des lignes doit être fait dans un ordre
spécifique, on utilise la clause ORDER BY dans la requête.

11
 Déclaration des curseurs

Exemple:

DECLARE
CURSOR C1 IS
SELECT NomArt, QteArt
FROM Article
WHERE QteArt < 500;

12
 Ouverture du curseur

Syntaxe :
OPEN nom_du_curseur;

• Ouvrir le curseur pour exécuter la requête

• Si la requête ne renvoie aucune ligne, aucune exception

n’aurait lieu.

13
 Exemple
DECLARE
CURSOR c1 IS
SELECT NomArt, QteArt
FROM Article
WHERE QteArt < 500;

BEGIN
OPEN c1 ;
… utilisation de c1 ...
...

14
 Recherche des données dans le curseur

Programme PL/SQL CURSOR

FETCH
c1
variables
DECLARE
CURSOR c1 IS SELECT ……;
……
BEGIN
OPEN c1;
FETCH c1 INTO variables;
……
…… OPEN

BD
15
 Recherche des données dans le curseur

• Rechercher les informations de la ligne en cours

et les mettre dans des variables.

Syntaxe:
FETCH nom_du_curseur INTO [variable1, [variable2, …]
| nom_de_record];

• FETCH permet l’accès aux lignes d’un curseur :

– Ramène le contenu de la ligne courante
– Assigne les données dans les variables de INTO
– Déplace le pointeur vers la ligne suivante

l
16
 Contrôle des curseurs explicites
Ouvrir le
curseur : Etape 1
Pointeur

Variable
Recherche Etape 2 FETCH
d’une
ligne du Pointeur
curseur

Continuer tant
qu’il y a des lignes Etape n

Variable
FETCH
Pointeur
17
Exemple: DECLARE
CURSOR c1 IS
SELECT NomArt, QteArt
FROM Article
WHERE QteArt < 500;
…
BEGIN
OPEN c1;
FETCH c1 INTO v_NomArt, v_QteArt;
-- utilisation du contenu de v_NomArt et
v_QteArt

Chaque FETCH ramène une ligne … 18

...
 Recherche des données dans le curseur

Programme PL/SQL
CURSOR
FETCH
c1
variables
DECLARE
CURSOR c1 IS SELECT ……;
BEGIN
OPEN c1;
LOOP
FETCH c1 INTO variables;
………
EXIT WHEN c1%NOTFOUND; OPEN
………
END LOOP;
CLOSE c1;
END; BD
Chaque FETCH ramène une ligne … 19
 Les attributs du curseur explicite

Obtenir les informations d’état du curseur.

Attribut Type Description

nomCurseur%ISOPEN BOOLEAN Prend la valeur TRUE si le curseur
est ouvert.
nomCurseur%NOTFOUND BOOLEAN Prend la valeur TRUE si le
FETCH le plus récent ne retourne
aucune ligne.
nomCurseur%FOUND BOOLEAN Prend la valeur TRUE si le
FETCH le plus récent retourne
une ligne.
nomCurseur%ROWCOUN NUMBER Retourne le nombre de lignes
T traitées jusqu’ici.
20
 L’attribut %ISOPEN

• La recherche des lignes n’est possible que si le curseur est

ouvert.
• Utiliser l’attribut %ISOPEN avant un FETCH pour tester si
le curseur est ouvert ou non.
Exemple :
IF NOT c1 %ISOPEN THEN
OPEN C1
END IF;
LOOP
FETCH C1 …
….
21
 Les attributs %FOUND, %NOTFOUND et
%ROWCOUNT

• Utiliser l’attribut %ROWCOUNT pour fournir le nombre

exact des lignes traitées.

• Utiliser les attributs %FOUND et %NOT FOUND pour

formuler le test d’arrêt de la boucle.

22
 Les attributs %FOUND, %NOTFOUND et
%ROWCOUNT
Exemple: DECLARE
CURSOR c1 IS
SELECT …
…
LOOP
FETCH c1 INTO …;
IF c1 %ROWCOUNT > 20 THEN
………..
EXIT WHEN c1%NOTFOUND;
………..
END LOOP;
23
 Fermeture du curseur

Syntaxe
CLOSE nom_du_curseur;

• Fermer le curseur après la fin du traitement des lignes.

• Rouvrir le curseur si nécessaire.
• On ne peut pas rechercher des informations dans un
curseur si ce dernier est fermé.

24
 Contrôle des curseurs explicites

Yes/No

No/Yes
FOUND/
DECLARE OPEN FETCH CLOSE
NOTFOUND

•Crée •Pointer •Charger •Test •Libérer le

une zone sur la la ligne d’existence curseur.
SQL première en cours des lignes
ligne du dans des
•Retour à
curseur variables
FETCH si
ligne
trouvée

25
 Les curseurs et les Records
Traitement des lignes par l’affectation des valeurs à des records:

CURSOR Etud_Curs IS
SELECT etud_id, nom, age
FROM Etudiants
WHERE age < 26;

Etud_Record Etud_Curs%ROWTYPE;
BEGIN
OPEN Etud_Curs;
FETCH Etud_Curs INTO Etud_Record;
-- utilisation du contenu de Etud_Record …
FETCH Etud_Curs INTO Etud_Record;
………
26
 Exercice: Les boucles sur les curseurs

CURSOR Etud_Curs IS
SELECT etud_id, nom, age
FROM Etudiants
WHERE age < 26;

Etud_Record Etud_Curs%ROWTYPE;
BEGIN
-- Afficher etud_id + nom + age de chaque étudiant ?
…
END;
/

27
 Exercice: Les boucles sur les curseurs
CURSOR Etud_Curs IS
SELECT etud_id, nom, age
FROM Etudiants WHERE age < 26;
Etud_Record Etud_Curs%ROWTYPE;
BEGIN
OPEN Etud_Curs;
LOOP
FETCH Etud_Curs INTO Etud_Record;
EXIT WHEN Etud_Curs %NOTFOUND;
DBMS_OUTPUT.PUT_LINE(Etud_Record.etud_id || ' '||
Etud_Record.nom ||' '||
Etud_Record.age);
END LOOP;
CLOSE Etud_Curs; … 28
 Exercice
curseurs: explicite et implicite

29
Soit le schéma relationnel suivant :
EMP (Matr, NomE, Poste, …)
PROJET (CodeP, NomP)
PARTICIPATION (Matr#, CodeP#, Fonction)

Définir un bloc PL/SQL permettant de lire les matricules et noms des

employés ainsi que le nombre de projets effectués par chacun d’eux,
en utilisant les deux types de curseurs: explicite et implicite.

30
DECLARE
Cursor Cur_emp IS select Matr, NomE from EMP ;
rec_emp Cur_emp%rowtype;
nbPr Number := 0;
BEGIN
Open Cur_emp;
loop
Fetch Cur_emp into rec_emp;
exit when Cur_emp%notFound;
select COUNT(p.CodeP) into nbPr from Participation p
Where p.Matr = rec_emp.Matr ;
DBMS_output.put_line(rec_emp.Matr ||rec_emp.NomE||’:’||nbPr);
end loop;
if(Cur_emp%rowcount = 0) then DBMS_output.put_line('Aucun Employé trouvé...');
close Cur_emp; … … …
31
Exercices

32
Soit le schéma relationnel suivant :
EMP (Matr, NomE, Poste, DateEmb, Sup#, Salaire, Comm,
NumDept#)
DEPT (NumDept, NomDept, Lieu, directeur#)
PROJET (CodeP, NomP)
PARTICIPATION (Matr#, CodeP#, Fonction)
Répondez aux questions suivantes :
1. Définir un bloc PL/SQL anonyme permettant de lire les détails
des projets effectués par tous les employés, le résultat est trié
selon la matricule employé.
2. Définir un bloc PL/SQL anonyme permettant de lire les détails
de tous les employés ainsi que le nombre de projets effectués
par chacun d’eux.
A. En utilisant deux curseurs explicites.
B. En utilisant un curseur explicite et un curseur
implicite.
33
 Solution Question 1
DECLARE
Cursor C_emp IS select P.Matr, P.CodeP, Pr.NomP from Participation P, Projet Pr
Where P.CodeP = Pr.CodeP order by P.Matr;
E_emp Exception;
rec C_emp%rowtype;
BEGIN
Open C_emp;
loop
Fetch C_emp into rec;
exit when C_emp%notFound;
DBMS_output.put_line(RPAD(rec.Matr,15) ||
RPAD(rec.CodeP,15) || RPAD(rec.NomP,15));
end loop;
34
if(C_emp%rowcount = 0)
then raise E_emp;
end if;
close C_emp;
EXCEPTION
when E_emp then
DBMS_output.put_line('Aucun Projet trouvé...');
--when others then
--DBMS_output.put_line('Exception non reconnue...');
END;

35
 Solution Question 2
DECLARE
Cursor C_emp IS select E.Matr, E.NomE, E.Poste, E.NumDept from EMP E
order by E.Matr;
Cursor C_NBProjets (P_Matr EMP.MATR%type) IS select count(P.CodeP) from
Participation P, EMP E Where P.Matr = E.Matr and E.Matr = P_Matr group by
P.Matr;
E_emp Exception;
rec C_emp%rowtype;
nbPr Number :=0;
BEGIN
Open C_emp;
loop
Fetch C_emp into rec;
exit when C_emp%notFound; 36
 Open C_NBProjets(rec.Matr);
nbPr:=0;
Fetch C_NBProjets into nbPr;
DBMS_output.put_line(RPAD(rec.Matr,15) || RPAD(rec.NomE,15) ||
RPAD(rec.Poste,15) || RPAD(rec.NumDept,15) || RPAD(nbPr,15));
close C_NBProjets;
end loop;
if(C_emp%rowcount = 0)
then raise E_emp;
end if;
close C_emp;
EXCEPTION
when E_emp then
DBMS_output.put_line('Aucun Projet trouvé...');
--when others then
--DBMS_output.put_line('Exception non reconnue...');
END;
37
Exercices simples :
1. Créer un bloc PL/SQL pour insérer un nouveau projet dans la table
PROJET.
2. Créer un bloc PL/SQL permettant de mettre à jour le pourcentage de
commission d’un employé en fonction du total de ses ventes.
- si la somme est inférieure à 100,000 passer la commission à 10
- si la somme est comprise entre 100,000 et 1,000,000 inclus passer la
commission à 15
- si la somme excède 1,000,000 passer la commission à 20
- si aucune commande n’existe pour cet employé, mettre la commission à 0
Valider la modification (commit)

38
ACCEPT p_id PROMPT 'Entrer un numéro de vendeur : '
DECLARE
v_somme_total NUMBER(11,2);
v_comm employes.commission%TYPE;
BEGIN
SELECT SUM(total)
INTO v_somme_total
FROM commandes
WHERE vendeur_id = &p_id;

IF v_somme_total < 100000 THEN v_comm := 10;

ELSIF v_somme_total <= 1000000 THEN v_comm := 15;
ELSIF v_somme_total > 1000000 THEN v_comm := 20;
ELSE v_comm := 0;
END IF;
UPDATE employes
SET commission = v_comm
WHERE id = &p_id;
COMMIT;
END;
/ 39