Manipulation des curseurs
Attributs des curseurs
Utilisation simplifiée des curseurs
Curseurs paramétrés
PL / SQL
Les curseurs
Ines BAKLOUTI
Ecole Supérieure Privée d’Ingénierie et de Technologies
Manipulation des curseurs
Attributs des curseurs
Utilisation simplifiée des curseurs
Curseurs paramétrés
Plan
1 Manipulation des curseurs
Déclaration
Ouverture
Exécution
Fermuture
2 Attributs des curseurs
3 Utilisation simplifiée des curseurs
4 Curseurs paramétrés
2/23
Manipulation des curseurs
Attributs des curseurs
Utilisation simplifiée des curseurs
Curseurs paramétrés
Introduction
Un curseur est une variable qui pointe vers le résultat d’une requête
SQL,
Types de curseurs:
Curseurs implicites:
Déclarés implicitement et manipulés par SQL
Pour toute requête SQL du LMD et pour les interrogations qui
retournent un seul enregistrement
Curseurs explicites:
Déclarés et manipulés par l’utilisateur
Pour les interrogations qui retournent plus qu’un enregistrement
3/23
Manipulation des curseurs
Attributs des curseurs
Utilisation simplifiée des curseurs
Curseurs paramétrés
Déclaration
Ouverture
Exécution
Fermuture
Plan
1 Manipulation des curseurs
Déclaration
Ouverture
Exécution
Fermuture
2 Attributs des curseurs
3 Utilisation simplifiée des curseurs
4 Curseurs paramétrés
4/23
Manipulation des curseurs
Attributs des curseurs
Utilisation simplifiée des curseurs
Curseurs paramétrés
Déclaration
Ouverture
Exécution
Fermuture
Manipulation des curseurs
5/23
Manipulation des curseurs
Attributs des curseurs
Utilisation simplifiée des curseurs
Curseurs paramétrés
Déclaration
Ouverture
Exécution
Fermuture
Déclaration
Syntaxe
CURSOR nom curseur IS instruction select;
Exemple
DECLARE
CURSOR cur emp IS
SELECT employee id, first name, last name FROM employees
WHERE department id = 100;
CURSOR cur dept IS
SELECT * FROM departments
ORDER BY department id;
6/23
Manipulation des curseurs
Attributs des curseurs
Utilisation simplifiée des curseurs
Curseurs paramétrés
Déclaration
Ouverture
Exécution
Fermuture
Ouverture
Syntaxe
OPEN nom curseur;
Ouvre le curseur pour exécuter la requête et identifier l’ensemble des
lignes.
Allocation mémoire pour y stocker le résultat.
Si la requête ne retourne pas de ligne, aucune exception n’est signalée.
Exemple
DECLARE
CURSOR cur emp IS
SELECT employee id, first name, last name FROM employees
WHERE department id = 100;
BEGIN
OPEN cur emp;
END;
7/23
Manipulation des curseurs
Attributs des curseurs
Publicité
Utilisation simplifiée des curseurs
Curseurs paramétrés
Déclaration
Ouverture
Exécution
Fermuture
Exécution
Syntaxe
FETCH nom curseur INTO [variable1, variable2, . . .
| record name];
Lecture de la zone pointée par le curseur.
Affectation des valeurs de la ligne courante dans les variables de
sortie (variables hôtes).
Passage à l’enregistrement suivant.
Il faut:
Prévoir le même nombre de variables
Testez si le curseur contient des lignes
8/23
Manipulation des curseurs
Attributs des curseurs
Utilisation simplifiée des curseurs
Curseurs paramétrés
Déclaration
Ouverture
Exécution
Fermuture
Exécution
Exemple - version 1
DECLARE
CURSOR cur emp IS
SELECT employee id, first name, last name
FROM employees
WHERE department id = 100;
v no employees.employee id%TYPE;
v Fname employees.first name%TYPE;
v Lname employees.last name%TYPE;
BEGIN
Exemple - version 2
DECLARE
CURSOR cur emp IS
SELECT employee id, first name, last name
FROM employees
WHERE department id = 100;
v no employees.employee id%TYPE;
v Fname employees.first name%TYPE;
v Lname employees.last name%TYPE;
IF NOT cur emp%ISOPEN THEN
BEGIN
OPEN cur emp;
END IF;
FETCH cur emp INTO v no, v Fname,
v Lname;
WHILE cur emp%FOUND
LOOP
DBMS OUTPUT.PUT LINE(’Employé no:
’||v no||’ Nom: ’||v Fname||’Prénom: ’||v Lname);
FETCH cur emp INTO v no, v Fname,
v Lname;
END LOOP;
END;
IF NOT cur emp%ISOPEN THEN
OPEN cur emp;
END IF;
LOOP
FETCH cur emp INTO v no, v Fname,
v Lname;
EXIT WHEN cur emp%NOTFOUND;
DBMS OUTPUT.PUT LINE(’Employé no:
’||v no||’ Nom: ’||v Fname||’Prénom: ’||v Lname);
END LOOP;
END;
9/23
Manipulation des curseurs
Attributs des curseurs
Utilisation simplifiée des curseurs
Curseurs paramétrés
Déclaration
Ouverture
Exécution
Fermuture
Fermuture
Syntaxe
CLOSE nom curseur;
Fermeture du curseur.
Libération de la zone mémoire allouée pour le résultat de la requête.
Exemple
DECLARE
CURSOR cur emp IS
SELECT employee id FROM employees WHERE department id = 100;
v no employees.employee id%TYPE;
BEGIN
OPEN cur emp;
LOOP
FETCH cur emp INTO v no;
EXIT WHEN cur emp%NOTFOUND;
DBMS OUTPUT.PUT LINE(’Employé no: ’||v no);
END LOOP;
close cur emp;
END;
10/23
Manipulation des curseurs
Attributs des curseurs
Utilisation simplifiée des curseurs
Curseurs paramétrés
Plan
1 Manipulation des curseurs
Déclaration
Ouverture
Exécution
Fermuture
2 Attributs des curseurs
3 Utilisation simplifiée des curseurs
4 Curseurs paramétrés
Publicité
11/23
Manipulation des curseurs
Attributs des curseurs
Utilisation simplifiée des curseurs
Curseurs paramétrés
Attributs des curseurs
Chaque curseur possède quatre attributs:
%ISOPEN Génère un booléen VRAI lorsque le curseur spécifié en
argument est ouvert.
%FOUND Génère un booléen VRAI lorsque le FETCH réussi
(données lues).
%NOTFOUND Inverse de %FOUND (généralement plus utilisé que
%FOUND).
%ROWCOUNT Renvoie le nombre de lignes contenues.
Chaque attribut s’utilise en étant préfixé par le nom du curseur:
nom curseur%ATTRIBUT
12/23
Manipulation des curseurs
Attributs des curseurs
Utilisation simplifiée des curseurs
Curseurs paramétrés
Attributs des curseurs
Exemple
DECLARE
CURSOR cur emp IS SELECT employee id, last name FROM employees;
v no employees.employee id%TYPE;
v Lname employees.last name%TYPE;
BEGIN
IF NOT cur emp%ISOPEN THEN
OPEN cur emp;
END IF;
FETCH cur emp INTO v no, v Lname;
WHILE cur emp%FOUND
LOOP
dbms output.put line(cur emp%rowcount);
FETCH cur emp INTO v no, v Lname;
END LOOP;
CLOSE cur emp;
END;
13/23
Manipulation des curseurs
Attributs des curseurs
Utilisation simplifiée des curseurs
Curseurs paramétrés
Plan
1 Manipulation des curseurs
Déclaration
Ouverture
Exécution
Fermuture
2 Attributs des curseurs
3 Utilisation simplifiée des curseurs
4 Curseurs paramétrés
14/23
Manipulation des curseurs
Attributs des curseurs
Utilisation simplifiée des curseurs
Curseurs paramétrés
Utilisation simplifiée des curseurs
Utilisation du type RECORD
Déclarer implicitement un enregistrement (type record) dont les
éléments sont de même type que les colonnes retournées par le
curseur.
Exemple
DECLARE
CURSOR cur emp IS
SELECT employee id, last name FROM employees;
rec emp cur emp%ROWTYPE;
Pour utiliser les colonnes de l’enregistrement:
rec emp.employee id
rec emp.last name
L’enregistrement est renseigné par le fetch:
Fetch cur emp into rec emp;
15/23
Manipulation des curseurs
Attributs des curseurs
Utilisation simplifiée des curseurs
Curseurs paramétrés
Utilisation simplifiée des curseurs
Utilisation du type RECORD
Exemple
DECLARE
CURSOR cur emp IS SELECT employee id, last name FROM employees;
rec emp cur emp%ROWTYPE;
BEGIN
OPEN cur emp;
LOOP
FETCH cur emp INTO rec emp;
EXIT WHEN cur emp%NOTFOUND;
dbms output.put line(’Employé no: ’||rec emp.employee id||’ Nom:
’||rec emp.last name);
END LOOP;
CLOSE cur emp;
END;
16/23
Manipulation des curseurs
Attributs des curseurs
Utilisation simplifiée des curseurs
Curseurs paramétrés
Utilisation simplifiée des curseurs
Utilisation de la structure FOR .. IN
La syntaxe FOR .. IN permet d’éviter de déclarer l’enregistrement hôte
dans la partie DECLARE.
Syntaxe
FOR nom record IN nom curseur
LOOP
– ouverture automatique du curseur
– fetch automatique
– condition de sortie automatique
/ traitement /
END LOOP;
– fermuture automatique du curseur
END;
Publicité
pas de déclaration de record
On utilise les variables par nom record.nom variable par exemple:
rec emp.employee id
17/23
Manipulation des curseurs
Attributs des curseurs
Utilisation simplifiée des curseurs
Curseurs paramétrés
Utilisation simplifiée des curseurs
Utilisation de la structure FOR .. IN
Exemple
DECLARE
CURSOR cur emp IS SELECT employee id, last name FROM employees;
BEGIN
FOR rec emp IN cur emp
LOOP
dbms output.put line(’Employé no: ’||rec emp.employee id||’ Nom:
’||rec emp.last name);
END LOOP;
END;
18/23
Manipulation des curseurs
Attributs des curseurs
Utilisation simplifiée des curseurs
Curseurs paramétrés
Utilisation simplifiée des curseurs
Utilisation de sous requête dans la structure FOR .. IN
La syntaxe FOR .. IN avec utilisation de sous requête permet d’éviter de
déclarer le curseur dans la partie DECLARE.
Syntaxe
FOR nom record IN requête select
– déclaration implicite du record et du curseur
LOOP
– ouverture automatique du curseur
– fetch automatique
– condition de sortie automatique
/ traitement /
END LOOP;
– fermuture automatique du curseur
END;
pas de déclaration du curseur
pas de déclaration de record
On utilise les variables par nom record.nom variable par exemple:
rec emp.employee id
19/23
Manipulation des curseurs
Attributs des curseurs
Utilisation simplifiée des curseurs
Curseurs paramétrés
Utilisation simplifiée des curseurs
Utilisation de sous requête dans la structure FOR .. IN
Exemple
BEGIN
FOR rec emp IN (select employee id, last name from employees)
LOOP
dbms output.put line(’Employé no: ’||rec emp.employee id||’ Nom:
’||rec emp.last name);
END LOOP;
END;
20/23
Manipulation des curseurs
Attributs des curseurs
Utilisation simplifiée des curseurs
Curseurs paramétrés
Plan
1 Manipulation des curseurs
Déclaration
Ouverture
Exécution
Fermuture
2 Attributs des curseurs
3 Utilisation simplifiée des curseurs
4 Curseurs paramétrés
21/23
Manipulation des curseurs
Attributs des curseurs
Utilisation simplifiée des curseurs
Curseurs paramétrés
Curseurs paramétrés
Objectif : paramétrer la requête associée à un curseur pour éviter de
multiplier les curseurs similaires.
Syntaxe de définition: Cursor nom curseur (param1 type, param2
type, . . . ) IS requête select;
Les valeurs des parametres sont transmises a l’ouverture du curseur:
OPEN nom curseur(valeurParam1, valeurParam2, . . . );
Il faut évidemment fermer le curseur avant de l’appeler avec d’autres
valeurs pour les paramètres (sauf si on utilise la boucle for qui ferme
automatiquement le curseur).
22/23
Manipulation des curseurs
Attributs des curseurs
Utilisation simplifiée des curseurs
Curseurs paramétrés
Curseurs paramétrés
Exemple
DECLARE
CURSOR cur emp(v dept number,v sal employees.salary%TYPE) IS SELECT
employee id, last name,salary FROM employees where department id=v dept and
salary>v sal;
rec emp cur emp%ROWTYPE;
BEGIN
OPEN cur emp(80,6000);
LOOP
FETCH cur emp INTO rec emp;
EXIT WHEN cur emp%NOTFOUND;
dbms output.put line(’Employé no: ’||rec emp.employee id||’ Nom:
’||rec emp.last name||’ Salaire: ’||rec emp.salary);
END LOOP;
CLOSE cur emp;
END;
23/23