5.5 Exemples sur les curseurs

Ex1: DECLARE

      CURSOR c_traitement IS
           SELECT nom, prnm, qualif, dattit, trtmnt
           FROM personnel
           ORDER BY 5;

       r_traitement c_traitement%ROWTYPE;
     BEGIN
       OPEN c_traitement;
       LOOP
        FETCH c_traitement INTO r_traitement;
        EXIT WHEN c_traitement%NOTFOUND OR c_traitement%ROWCOUNT = 6;
        INSERT INTO traitement VALUES (r_traitement.nom, r_traitement.prnm,
              r_traitement.qualif,r_traitement.dattit,r_traitement.trtmnt );
       END LOOP;
       CLOSE c_traitement;
     END;

Ex2:DECLARE

    CURSOR c_titulaire IS
           SELECT nom, prnm, datnais, qualif, dattit
           FROM personnel
           WHERE dattit IS NOT NULL
           ORDER BY dattit - datnais ;

    nom      personnel.nom%TYPE;
    prenom   personnel.prnm%TYPE;
    datnais  personnel.datnais%TYPE;
    fonction personnel.qualif%TYPE;
    dattit   personnel.dattit%TYPE;
    agetit   NUMBER(2) := 0;
    P       NUMBER(2) := 0;

   BEGIN
    OPEN c_titulaire;
    WHILE p < 8 AND agetit < 25
    LOOP
       FETCH c_titulaire INTO nom, prenom, datnais, fonction, dattit;
       EXIT WHEN c_titulaire%NOTFOUND;
       p := p +1;
       agetit := ROUND((dattit - datnais) / 365.25);
       INSERT INTO titulaire VALUES(p,nom,prenom,datnais,fonction,dattit,agetit);
    END LOOP;
    CLOSE c_titulaire;
   END;

Ex3: DECLARE

    CURSOR c_anciennete IS
           SELECT nom, prnm, qualif, dattit, trtmnt
           FROM personnel
           WHERE trtmnt IS NOT NULL
           AND dattit IS NOT NULL
           ORDER BY dattit DESC ;

    nom         personnel.nom%TYPE;
    prenom      personnel.prnm%TYPE;
    datnais     personnel.datnais%TYPE;
    fonction    personnel.qualif%TYPE;
    dattit      personnel.dattit%TYPE;
    traitement  personneI.trtmnt%TYPE;
    anciennete  NUMBER(2);

   BEGIN
    OPEN c_anciennete;
    FOR p IN 1..&nbprs
    LOOP
        FETCH c_anciennete INTO nom, prenom, fonction, dattit, traitement ;
        EXIT WHEN c_anciennete%NOTFOUND;
        anciennete := ROUND((SYSDATE - dattit) / 365.25);
        INSERT INTO anciennete VALUES(p,nom,prenom,fonction,anciennete,traitement ) ;
    END LOOP;
    CLOSE c_anciennete;
   END;