Remerciez-le!

Remerciez @Admin pour avoir partagé cet document gratuitement, de la manière la plus simple, en partageant sur les réseaux sociaux.

Chapitre 3 : Interaction avec Oracle et les curseurs SGBD-PL\SQL (Procedural La

Chapitre 3 : Interaction avec Oracle et les curseurs SGBD-PL\SQL (Procedural Language / Structured Query Language) Faîçal Felhi felhi_fayssal@yahoo.fr 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 implicite Curseur 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 Erreur Oracle Server ORA-01422 TOO_MANY_ROWS Le SELECT n’identifie aucune ligne Erreur Oracle Server ORA-01403 NO_DATA_FOUND 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. 12 Ces attributs sont utilisables comme des fonctions dans les ordres PL/SQL MAIS PAS dans les commandes SQL. 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 15 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 Ouverture Accès aux lignes du curseur Fermeture Section DECLARE Section EXECUTABLE 16 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 17 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; 18 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 19 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 20 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 valeurTRUE %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 instruction SQL 21 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 22 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; 23 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; 24 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; 25  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 uploads/Finance/ chap3-plsql.pdf