PL/SQL: Manipulation des curseurs

Curseurs PL/SQL

• Un curseur est un pointeur vers le jeu de résultats d'une requête SQL.

Types de Curseurs

• Les curseurs implicites sont automatiquement déclarés et gérés par Oracle pour les instructions DML et les requêtes à une seule ligne.
• Les curseurs explicites sont déclarés et gérés par le programmeur, pour les requêtes renvoyant plusieurs lignes.

Manipulation des Curseurs

• Déclaration : CURSOR nom_du_curseur IS requête_SELECT;
  • Exemple: CURSOR curseur_emp IS SELECT employee_id, last_name FROM employees WHERE department_id = 100;
• Ouverture : OPEN nom_du_curseur;
  • Alloue de la mémoire pour le jeu de résultats.
  • Aucune exception n'est levée si la requête ne renvoie aucune ligne.
• Extraction des données : FETCH nom_du_curseur INTO liste_de_variables|nom_de_l_enregistrement;
  • Récupère les valeurs de la ligne courante dans des variables.
  • Déplace le pointeur vers la ligne suivante.
• Fermeture : CLOSE nom_du_curseur;
  • Libère la mémoire allouée et le curseur peut être rouvert après fermeture.

Attributs des Curseurs

• %ISOPEN : Booléen indiquant si le curseur est ouvert.
• %FOUND : Booléen indiquant si la dernière extraction a réussi.
• %NOTFOUND : Booléen indiquant si la dernière extraction a échoué (plus de lignes).
• %ROWCOUNT : Nombre de lignes extraites jusqu'à présent.
• Exemple d'utilisation :

IF curseur_emp%ISOPEN THEN
    DBMS_OUTPUT.PUT_LINE('Lignes traitées : ' || curseur_emp%ROWCOUNT);
END IF;

Utilisation d'Enregistrements %ROWTYPE

• Déclaration :

DECLARE
    CURSOR curseur_emp IS SELECT employee_id, last_name FROM employees;
    enregistrement_emp curseur_emp%ROWTYPE;
BEGIN
    OPEN curseur_emp;
    LOOP
        FETCH curseur_emp INTO enregistrement_emp;
        EXIT WHEN curseur_emp%NOTFOUND;
        -- Accéder aux champs via enregistrement_emp.employee_id, enregistrement_emp.last_name
    END LOOP;
    CLOSE curseur_emp;
END;

Boucle FOR avec Curseur

• Déclaration la plus simple :

DECLARE
    CURSOR curseur_emp IS SELECT employee_id, last_name FROM employees;
BEGIN
    FOR enregistrement_emp IN curseur_emp LOOP
        -- Ouverture, extraction, vérification de sortie et fermeture automatiques
        -- Accéder aux champs via enregistrement_emp.employee_id, enregistrement_emp.last_name
    END LOOP;
END;

Boucle FOR avec Sous-requête

• Méthode plus concise :

BEGIN
    FOR enregistrement_emp IN (SELECT employee_id, last_name FROM employees) LOOP
        -- Gestion automatique du curseur
        -- Accéder aux champs via enregistrement_emp.employee_id, enregistrement_emp.last_name
    END LOOP;
END;    

Curseurs Paramétrés

• Permettent de passer des paramètres pour personnaliser les résultats des requêtes.
• Syntaxe : CURSOR nom_du_curseur (param1 type, param2 type,...) IS requête_SELECT_utilisant_les_paramètres;
• Exemple :

DECLARE
    CURSOR curseur_emp_dept(dept_id NUMBER, salaire_min NUMBER) IS
    SELECT employee_id, last_name FROM employees
    WHERE department_id = dept_id AND salary > salaire_min;
BEGIN
    -- Ouverture avec des paramètres spécifiques
    FOR enregistrement_emp IN curseur_emp_dept(80, 5000) LOOP
        DBMS_OUTPUT.PUT_LINE(enregistrement_emp.last_name);
    END LOOP;
END;

Bonnes Pratiques

• Toujours fermer les curseurs après utilisation pour libérer les ressources.
• Utiliser les boucles FOR avec curseur lorsque possible pour une gestion automatique.
• Vérifier les attributs des curseurs (%FOUND, %NOTFOUND) pour gérer les jeux de résultats vides.
• Paramétrer les curseurs pour des modèles de requêtes réutilisables.
• Utiliser les enregistrements %ROWTYPE pour simplifier les déclarations de variables.

Comparaison des Techniques de Curseurs

• Basique : Déclaration complète, gestion manuelle, traitement complexe.
• %ROWTYPE : Curseur + enregistrement, gestion manuelle, gestion structurée des lignes.
• Boucle FOR : Curseur uniquement, gestion automatique, itération simple.
• Sous-requête FOR : Aucune déclaration nécessaire, gestion automatique, requêtes ad-hoc rapides.
• Paramétré : Curseur paramétré, gestion manuelle/automatique, requêtes réutilisables.

Composants SQL

• LDD (Langage de Définition de Données) : CREATE, ALTER, DROP
• LMD (Langage de Manipulation de Données) : INSERT, UPDATE, DELETE
• LID (Langage d'Interrogation de Données) : SELECT
• LCD (Langage de Contrôle de Données) : GRANT, REVOKE

Langage de Définition de Données (LDD) : Tables

• Création :

CREATE TABLE nom_table (
    colonne1 type_donnees [contraintes],
    colonne2 type_donnees [contraintes],
    ...
);

• Modification :
  • Ajouter une colonne : ALTER TABLE nom_table ADD nom_colonne type_donnees;
  • Modifier une colonne : ALTER TABLE nom_table MODIFY nom_colonne nouveau_type;
  • Supprimer une colonne : ALTER TABLE nom_table DROP COLUMN nom_colonne;
• Suppression : DROP TABLE nom_table;
• Renommage : RENAME ancien_nom TO nouveau_nom;

Contraintes

• NOT NULL
• UNIQUE
• PRIMARY KEY
• FOREIGN KEY
• CHECK
• Ajout de contraintes : ALTER TABLE nom_table ADD CONSTRAINT nom_contrainte type_contrainte(colonne);
• Suppression de contraintes : ALTER TABLE nom_table DROP CONSTRAINT nom_contrainte;

Vues

• Création :

CREATE [OR REPLACE] VIEW nom_vue AS
SELECT colonnes FROM tables [WHERE conditions];

• Suppression : DROP VIEW nom_vue;

Séquences

• Création :

CREATE SEQUENCE nom_sequence
[INCREMENT BY n]
[START WITH n]
[MAXVALUE n | NOMAXVALUE]
[MINVALUE n | NOMINVALUE]
[CYCLE | NOCYCLE]
[CACHE n | NOCACHE];

• Utilisation : nom_sequence.NEXTVAL et nom_sequence.CURRVAL
• Suppression : DROP SEQUENCE nom_sequence;

Index

• Création : CREATE [UNIQUE] INDEX nom_index ON nom_table(colonne1, colonne2,...);
• Suppression : DROP INDEX nom_index;

Synonymes

• Création : CREATE [PUBLIC] SYNONYM nom_synonyme FOR nom_objet;
• Suppression : DROP SYNONYM nom_synonyme;

Langage de Manipulation de Données (LMD) : INSERT

• Syntaxe de base : INSERT INTO nom_table VALUES (valeur1, valeur2,...);
• Spécification de colonnes : INSERT INTO nom_table (colonne1, colonne2,...) VALUES (valeur1, valeur2,...);
• À partir d'une autre table :

INSERT INTO nom_table (colonne1, colonne2,...)
SELECT colonne1, colonne2,... FROM table_source [WHERE condition];

UPDATE

• Syntaxe de base : UPDATE nom_table SET colonne1 = valeur1, colonne2 = valeur2 [WHERE condition];
• Avec sous-requête :

UPDATE nom_table SET (colonne1, colonne2) =
(SELECT colonne1, colonne2 FROM table_source WHERE condition);

DELETE

• Syntaxe de base : DELETE FROM nom_table [WHERE condition];
• Avec sous-requête : DELETE FROM nom_table WHERE colonne IN (SELECT colonne FROM table_source WHERE condition);

Langage d'Interrogation de Données (LID) : SELECT

• Syntaxe de base : SELECT [DISTINCT] colonne1, colonne2,... FROM nom_table [WHERE condition] [ORDER BY colonne1 [ASC|DESC], colonne2 [ASC|DESC],...];
• Fonctions de caractères : LOWER(), UPPER(), SUBSTR(), LENGTH(), etc.
• Fonctions numériques : ROUND(), TRUNC(), MOD(), POWER(), etc.
• Fonctions de date : SYSDATE, ADD_MONTHS(), MONTHS_BETWEEN(), etc.
• Fonctions de conversion : TO_CHAR(), TO_DATE(), TO_NUMBER()
• Autres fonctions : NVL(), NVL2(), DECODE(), CASE, COALESCE()
• Fonctions Analytiques
• Group Functions
  • Functions: AVG(), COUNT(), MAX(), MIN(), SUM(), STDDEV(), VARIANCE()
  • GROUP BY: Regroupe les lignes ayant les mêmes valeurs.
  • HAVING: Filtre les groupes.

Jointures

• Jointure interne : Renvoie les lignes lorsqu'il y a une correspondance dans les deux tables.
• Jointures externes :
  • LEFT OUTER JOIN : Renvoie toutes les lignes de la table de gauche.
  • RIGHT OUTER JOIN : Renvoie toutes les lignes de la table de droite.
  • FULL OUTER JOIN : Renvoie toutes les lignes lorsqu'il y a une correspondance dans l'une ou l'autre table.
• Auto-jointure : Jointure d'une table avec elle-même.
• Jointure naturelle : Joint des tables sur des colonnes de même nom.
• Produit cartésien : Renvoie toutes les combinaisons possibles de lignes.

Opérateurs Ensemblistes

• UNION : Combine les résultats, supprime les doublons.
• UNION ALL : Combine les résultats, conserve les doublons.
• INTERSECT : Renvoie les lignes communes.
• MINUS : Renvoie les lignes de la première requête absentes de la seconde.

Sous-requêtes

• Sous-requêtes à une ligne : Utiliser avec =, >, <, etc.
• Sous-requêtes à plusieurs lignes : Utiliser avec IN, ANY, ALL

PL/SQL

• Structure de Bloc

DECLARE
    -- Déclarations de variables
BEGIN
    -- Instructions exécutables
    [EXCEPTION
        -- Gestionnaires d'exceptions]
END;

Types de Variables

• Types scalaires : NUMBER, VARCHAR2, DATE, BOOLEAN, etc.
• Types composites : RECORD, TABLE
• Types référence : REF CURSOR
• Types LOB : BLOB, CLOB, NCLOB, BFILE

Structures de Contrôle

IF condition THEN
    instructions;
ELSIF condition THEN
    instructions;
ELSE
    instructions;
END IF;

CASE variable
    WHEN valeur1 THEN instructions;
    WHEN valeur2 THEN instructions;
    ELSE instructions;
END CASE;

Boucles

LOOP
    instructions;
    EXIT WHEN condition;
END LOOP;

WHILE condition LOOP
    instructions;
END LOOP;

FOR compteur IN [REVERSE] début...fin LOOP
    instructions;
END LOOP;

SQL dans PL/SQL

SELECT colonne INTO variable FROM table WHERE condition;

• Opérations sur les curseurs : DECLARE, OPEN, FETCH, CLOSE

Gestiion des Exceptions

EXCEPTION
    WHEN exception1 THEN
        instructions;
    WHEN exception2 THEN
        instructions;
    WHEN OTHERS THEN
        instructions;
END;