SGBD-PL\SQL
(Procedural Language / Structured Query Language)
Chapitre 3 : Interaction avec Oracle et
les curseurs
Faîçal Felhi
[email protected]
I. Interactions simples avec la base
Extraire, modifier, inserer.
Le select ne doit renvoyer qu’une seule ligne
Si le select renvoie plus d’une ligne : exception « TOO_MANY_ROWS »
(ORA-01422)
Si le select ne renvoie aucune ligne : exception « NO_DATA_FOUND »
(ORA-01403)
pour ramener des lignes : les curseurs
2
II. Les curseurs
Toutes les requêtes SQL sont associées à un curseur
Ce curseur représente la zone mémoire utilisée pour analyser
et exécuter la requête
Le curseur peut être implicite (pas déclaré par l’utilisateur) ou
explicite
Les curseurs explicites servent à retourner plusieurs lignes
avec un select
On distingue deux types de curseurs :
Les curseurs explicites
Les curseurs implicites
3
Notion de curseur
Le tableau suivant indique, par opération de manipulation, les
commandes SQL susceptibles de jouer le rôle de curseur implicite
et/ou explicite :
Opération Curseur Curseur
implicite explicite
MAJ Insert, Update,
Delete
Interrogation Select …Into Select
4
II.1. 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 résultat pour une requête
SELECT
5
Exemple d’un curseur implicite
DECLARE
V_deptno NUMBER(2);
V_Loc VARCHAR2(15);
.....
BEGIN
SELECT deptno, loc
INTO v_deptno, v_loc Obligatoire
FROM dept
WHERE upper(dname) = ‘SALES’;
…
END;
6
7
Exemple 2
8
Exception du select
Condition Nom Exception
Le SELECT identifie plus d’une ligne TOO_MANY_ROWS
Erreur Oracle Server ORA-01422
Le SELECT n’identifie aucune ligne NO_DATA_FOUND
Erreur Oracle Server ORA-01403
9
Exemple 3
10
Exemple 4
11
Attributs du curseur implicite
PL/SQL fournit des attributs permettant d’évaluer le résultat d’un curseur implicite :
Attribut Description
SQL%ROWCOUNT Entier : Nombre de lignes affectées par
le dernier ordre SQL
SQL%FOUND Booléen : TRUE si le dernier ordre SQL
affecte au moins une ligne
SQL%NOTFOUND Booléen : TRUE si le dernier ordre SQL
n’affecte aucune ligne
SQL%ISOPEN Toujours FALSE pour les curseurs
implicites.
Ces attributs sont utilisables comme des fonctions dans les ordres
PL/SQL MAIS PAS dans les commandes SQL.
12
Exemple
DECLARE
V_Rows_Updated NUMBER;
BEGIN
UPDATE EMP
SET sal = sal*1.1
WHERE deptno = 10;
V_Rows_Updated := SQL%ROWCOUNT;
-- SQL%ISOPEN est FALSE
INSERT INTO History_Tab
Values (‘Dept 10’, V_Rows_Updated, SYSDATE);
END ;
13
II.2. Les curseurs explicites
Un curseur est une zone mémoire de taille fixe, utilisée par le moteur
SQL pour analyser et interpréter un ordre SQL
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
C’est une commande SELECT pouvant ramener plusieurs lignes et
qui est totalement à la charge du développeur.
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 : ouverture du curseur, lecture et
traitement des lignes, fermeture.
14
Motivation
Besoin de consulter n-uplets issus d’une ou de plusieurs tables de la base de données
Effectuer des traitements en examinant chaque ligne individuellement
Curseur explicite
Est une commande Select déclaré et nommé
Généralisation du curseur implicite d’interrogation
Possédant les quatre attributs suivants : %ISOPEN, %FOUND, %NOTFOUND et
%ROWCOUNT.
Exigences du curseur explicite
Déclaration Section DECLARE
Ouverture
Accès aux lignes du curseur Section EXECUTABLE
Fermeture
15
Déclaration d’un curseur : Syntaxe
CURSOR nom_curseur [(paramètre1, paramètre2,…)] IS SELECT [FOR UPDATE
[OF colonne1, colonne2,…] [NOWAIT]];
Où
nom_curseur : est le nom du curseur
Paramètre : est un paramètre formel décrit comme suit :
FOR UPDATE [OF colonne1, colonne2,…] : place un verrou exclusif portant sur des
n-uplets de la table du SELECT
[OF colonne1, colonne2,…] : les colonnes à mettre à jour par le curseur sont
verrouillées
NOWAIT : pas d’attente pour accéder aux n-uplets
16
Déclaration d’un curseur : Exemples
DECLARE
CURSOR C1_Cher IS
SELECT Cnom, Labno
FROM chercheur
WHERE Sal> 1200;
--------------------------------------------------------------------------------
CURSOR C2_Cher (P_Labno IN NUMBER) IS
-- IN est optionnel
SELECT Grade, Cnom
FROM chercheur
WHERE Labno = P_Labno;
----------------------------------------------------------------------------------
CURSOR C3_CHER (P_Min NUMBER DEFAULT 0, P_Max NUMBER DEFAULT 99) IS
SELECT ….
FROM ….
WHERE …BETWEEN P_Min AND P_Max;
17
Ouverture d’un curseur : Syntaxe
OPEN nom_curseur [(paramètre_effectif,…)];
Exemples
OPEN C1_Cher;
OPEN C2_Cher (10);
V_Labno :=10;
OPEN C2_Cher (V_Labno);
• Accès aux lignes d’un curseur : Syntaxe
FETCH nom_curseur INTO variable1, variable2,…;
• Fonctionnalités
– 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
18
Accès aux lignes d’un curseur : Exemple
DECLARE
CURSOR C1_Cher IS
SELECT Cnom, Labno
FROM Chercheur
WHERE Sal >1200;
V_Cnom Chercheur.Cnom%TYPE;
V_Labno Chercheur.Labno%TYPE;
BEGIN
OPEN C1_Cher;--Exécution du SELECT
LOOP
FETCH C1_Cher INTOV_Cnom, V_Labno;
EXIT WHEN C1_Cher%NOTFOUND;--Test de sortie
-- ici %FOUND est TRUE
-- traitement de la ligne ramenée par FETCH
…
END LOOP;
…
END; --fin du bloc
19
Fermeture d’un curseur
Libération de l’espace mémoire alloué
Réouverture si nécessaire
Syntaxe
CLOSE nom_curseur;
Remarque
toute opération sur un curseur fermé (Fetch, %attribut) engendra l’exception prédéfinie
INVALID_CURSOR
Attributs d’un curseur explicite
%FOUND : Cet attribut prend la valeur TRUE lorsque une ligne est ramenée, sinon
il prend la valeur FALSE
%NOTFOUND : Cet attribut prend la valeur FALSE lorsque une ligne est ramenée,
sinon il prend la valeur TRUE
%ISOPEN : Cet attribut prend la valeur TRUE lorsque le curseur indiqué est ouvert,
sinon il prend la valeur FALSE
%ROWCOUNT : Cet attribut retourne le nombre de lignes impactées par la dernière
20
instruction SQL
Curseur et enregistrement : Syntaxe
Nom_enregistrement nom_curseur%ROWTYPE;
Exemple
DECLARE
CURSOR C1 IS
SELECT Cnom, Sal
FROM chercheur
WHERE Labno=10;
Rec1 C1%ROWTYPE;-- Rec1 de même schéma que C1
V_Cnom Chercheur.Cnom%TYPE;
…
BEGIN
OPEN C1;
LOOP
FETCH C1 INTO Rec1;
EXIT WHEN C1%NOTFOUND OR C1%ROWCOUNT >5;
END LOOP;
Close C1;
END;__ fin bloc
21
Boucle dédiée curseur
PL/SQL offre une boucle FOR spéciale pour les curseurs explicites. Elle prend en charge toutes
les opérations du curseur (OPEN, FETCH, EXIT et CLOSE)
Syntaxe 1 : cas d’un curseur nommé
FOR nom_record IN nom_curseur [(paramètre,…)]
LOOP
…
--traitement de la ligne courante
END LOOP;
Syntaxe 2 : cas d’un curseur anonyme
FOR nom_record IN (Commande_SELECT)
LOOP
…
--traitement de la ligne courante
END LOOP;
22
Boucle dédiée curseur : Exemple 1
DECLARE
CURSOR C1 (P_DATEREC DATE) IS
SELECT Cnom, Grade FROM Chercheur
WHERE DATEREC < P_DATEREC;
BEGIN
FOR REC IN C1 (’01-JAN-99’) -- curseur ouvert
LOOP -- ici une ligne est disponible
-- traitement des lignes, une par itération
END LOOP;
/* Ici le curseur est automatiquement fermé
les attributs ne sont pas utilisables
Toute référence à REC est invalide*/
-- suite traitement
END;
23
Boucle dédiée curseur : Exemple 2
DECLARE
……
BEGIN
…
V_TOT_SAL NUMBER :=0;
FOR REC IN (SELECT Cnom, Sal
FROM Chercheur
WHERE Labno=10)
LOOP
-- traitement de la ligne courante
IF Rec.Sal > 1000 THEN …
END LOOP;
….
END;
24
N.B:
Le seul ordre qui offre le choix entre curseur implicite et explicite est
le SELECT lorsqu’il ne ramène qu’une seule ligne.
Il n’existe pas de curseur explicite de mise à jour
Un curseur explicite ne peut être qu’un SELECT : utiliser
systématiquement des CE pour les ordres SELECT
Inconvénients de CI par rapport au CE
25
