Manipulation des curseurs
Attributs des curseurs
Utilisation simplifi´ee des curseurs
Curseurs param´etr´es
PL / SQL
Les curseurs
Ines BAKLOUTI
Ecole Sup´erieure Priv´ee d’Ing´enierie et de Technologies
Manipulation des curseurs
Attributs des curseurs
Utilisation simplifi´ee des curseurs
Curseurs param´etr´es
Plan
1 Manipulation des curseurs
D´eclaration
Ouverture
Ex´ecution
Fermuture
2 Attributs des curseurs
3 Utilisation simplifi´ee des curseurs
4 Curseurs param´etr´es
2/23
Manipulation des curseurs
Attributs des curseurs
Utilisation simplifi´ee des curseurs
Curseurs param´etr´es
Introduction
Un curseur est une variable qui pointe vers le r´esultat d’une requˆete
SQL,
Types de curseurs:
Curseurs implicites:
D´eclar´es implicitement et manipul´es par SQL
Pour toute requˆete SQL du LMD et pour les interrogations qui
retournent un seul enregistrement
Curseurs explicites:
D´eclar´es et manipul´es par l’utilisateur
Pour les interrogations qui retournent plus qu’un enregistrement
3/23
Manipulation des curseurs
Attributs des curseurs
Utilisation simplifi´ee des curseurs
Curseurs param´etr´es
D´eclaration
Ouverture
Ex´ecution
Fermuture
Plan
1 Manipulation des curseurs
D´eclaration
Ouverture
Ex´ecution
Fermuture
2 Attributs des curseurs
3 Utilisation simplifi´ee des curseurs
4 Curseurs param´etr´es
4/23
Manipulation des curseurs
Attributs des curseurs
Utilisation simplifi´ee des curseurs
Curseurs param´etr´es
D´eclaration
Ouverture
Ex´ecution
Fermuture
Manipulation des curseurs
5/23
Manipulation des curseurs
Attributs des curseurs
Utilisation simplifi´ee des curseurs
Curseurs param´etr´es
D´eclaration
Ouverture
Ex´ecution
Fermuture
D´eclaration
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´ee des curseurs
Curseurs param´etr´es
Publicité
D´eclaration
Ouverture
Ex´ecution
Fermuture
Ouverture
Syntaxe
OPEN nom curseur;
Ouvre le curseur pour ex´ecuter la requˆete et identifier l’ensemble des
lignes.
Allocation m´emoire pour y stocker le r´esultat.
Si la requˆete ne retourne pas de ligne, aucune exception n’est signal´ee.
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
Utilisation simplifi´ee des curseurs
Curseurs param´etr´es
D´eclaration
Ouverture
Ex´ecution
Fermuture
Ex´ecution
Syntaxe
FETCH nom curseur INTO [variable1, variable2, . . .
| record name];
Lecture de la zone point´ee par le curseur.
Affectation des valeurs de la ligne courante dans les variables de
sortie (variables hˆotes).
Passage `a l’enregistrement suivant.
Il faut:
Pr´evoir le mˆeme nombre de variables
Testez si le curseur contient des lignes
8/23
Manipulation des curseurs
Attributs des curseurs
Utilisation simplifi´ee des curseurs
Curseurs param´etr´es
D´eclaration
Ouverture
Ex´ecution
Fermuture
Ex´ecution
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´e no:
’||v no||’ Nom: ’||v Fname||’Pr´enom: ’||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´e no:
’||v no||’ Nom: ’||v Fname||’Pr´enom: ’||v Lname);
Publicité
END LOOP;
END;
9/23
Manipulation des curseurs
Attributs des curseurs
Utilisation simplifi´ee des curseurs
Curseurs param´etr´es
D´eclaration
Ouverture
Ex´ecution
Fermuture
Fermuture
Syntaxe
CLOSE nom curseur;
Fermeture du curseur.
Lib´eration de la zone m´emoire allou´ee pour le r´esultat de la requˆete.
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´e no: ’||v no);
END LOOP;
close cur emp;
END;
10/23
Manipulation des curseurs
Attributs des curseurs
Utilisation simplifi´ee des curseurs
Curseurs param´etr´es
Plan
1 Manipulation des curseurs
D´eclaration
Ouverture
Ex´ecution
Fermuture
2 Attributs des curseurs
3 Utilisation simplifi´ee des curseurs
4 Curseurs param´etr´es
11/23
Manipulation des curseurs
Attributs des curseurs
Utilisation simplifi´ee des curseurs
Curseurs param´etr´es
Attributs des curseurs
Chaque curseur poss`ede quatre attributs:
%ISOPEN G´en`ere un bool´een VRAI lorsque le curseur sp´ecifi´e en
argument est ouvert.
%FOUND G´en`ere un bool´een VRAI lorsque le FETCH r´eussi
(donn´ees lues).
%NOTFOUND Inverse de %FOUND (g´en´eralement plus utilis´e que
%FOUND).
%ROWCOUNT Renvoie le nombre de lignes contenues.
Chaque attribut s’utilise en ´etant pr´efix´e par le nom du curseur:
nom curseur%ATTRIBUT
12/23
Manipulation des curseurs
Attributs des curseurs
Utilisation simplifi´ee des curseurs
Curseurs param´etr´es
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´ee des curseurs
Curseurs param´etr´es
Plan
1 Manipulation des curseurs
D´eclaration
Publicité
Ouverture
Ex´ecution
Fermuture
2 Attributs des curseurs
3 Utilisation simplifi´ee des curseurs
4 Curseurs param´etr´es
14/23
Manipulation des curseurs
Attributs des curseurs
Utilisation simplifi´ee des curseurs
Curseurs param´etr´es
Utilisation simplifi´ee des curseurs
Utilisation du type RECORD
D´eclarer implicitement un enregistrement (type record) dont les
´el´ements sont de mˆeme type que les colonnes retourn´ees 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´e par le fetch:
Fetch cur emp into rec emp;
15/23
Manipulation des curseurs
Attributs des curseurs
Utilisation simplifi´ee des curseurs
Curseurs param´etr´es
Utilisation simplifi´ee 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´e 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´ee des curseurs
Curseurs param´etr´es
Utilisation simplifi´ee des curseurs
Utilisation de la structure FOR .. IN
La syntaxe FOR .. IN permet d’´eviter de d´eclarer l’enregistrement hˆote
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;
pas de d´eclaration 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´ee des curseurs
Curseurs param´etr´es
Utilisation simplifi´ee 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´e no: ’||rec emp.employee id||’ Nom:
’||rec emp.last name);
END LOOP;
END;
18/23
Manipulation des curseurs
Attributs des curseurs
Utilisation simplifi´ee des curseurs
Curseurs param´etr´es
Publicité
Utilisation simplifi´ee des curseurs
Utilisation de sous requˆete dans la structure FOR .. IN
La syntaxe FOR .. IN avec utilisation de sous requˆete permet d’´eviter de
d´eclarer le curseur dans la partie DECLARE.
Syntaxe
FOR nom record IN requˆete select
– d´eclaration 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´eclaration du curseur
pas de d´eclaration 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´ee des curseurs
Curseurs param´etr´es
Utilisation simplifi´ee des curseurs
Utilisation de sous requˆete dans la structure FOR .. IN
Exemple
BEGIN
FOR rec emp IN (select employee id, last name from employees)
LOOP
dbms output.put line(’Employ´e no: ’||rec emp.employee id||’ Nom:
’||rec emp.last name);
END LOOP;
END;
20/23
Manipulation des curseurs
Attributs des curseurs
Utilisation simplifi´ee des curseurs
Curseurs param´etr´es
Plan
1 Manipulation des curseurs
D´eclaration
Ouverture
Ex´ecution
Fermuture
2 Attributs des curseurs
3 Utilisation simplifi´ee des curseurs
4 Curseurs param´etr´es
21/23
Manipulation des curseurs
Attributs des curseurs
Utilisation simplifi´ee des curseurs
Curseurs param´etr´es
Curseurs param´etr´es
Objectif : param´etrer la requˆete associ´ee `a un curseur pour ´eviter de
multiplier les curseurs similaires.
Syntaxe de d´efinition: Cursor nom curseur (param1 type, param2
type, . . . ) IS requˆete select;
Les valeurs des parametres sont transmises a l’ouverture du curseur:
OPEN nom curseur(valeurParam1, valeurParam2, . . . );
Il faut ´evidemment fermer le curseur avant de l’appeler avec d’autres
valeurs pour les param`etres (sauf si on utilise la boucle for qui ferme
automatiquement le curseur).
22/23
Manipulation des curseurs
Attributs des curseurs
Utilisation simplifi´ee des curseurs
Curseurs param´etr´es
Curseurs param´etr´es
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´e no: ’||rec emp.employee id||’ Nom:
’||rec emp.last name||’ Salaire: ’||rec emp.salary);
END LOOP;
CLOSE cur emp;
END;
23/23