Exercices : fonction et procédure¶

Pour vous pratiquer avec la création d'une procédure et d'une fonction.

Procédure : Résumé des matchs¶

Avec la base de données Peewee et la table suivante :

create table resumeMatch (
noMatch NUMBER,
equipeGagnante VARCHAR2(30),
equipePerdante VARCHAR2(30));

Écrire une procédure qui rempli la table avec les informations suivantes :

Pour chaque match qui a été joué (ARemporteMatch différent de ?), conserver le numéro de match, le nom de l’équipe gagnante et le nom de l’équipe perdante.
Capter les erreurs TOO_MANY_ROWS et NO_DATA_FOUND et afficher un message significatif en intégrant la fonction SQLERRM (DBMS_OUTPU.PUT_LINE).

Ne pas oublier SET SERVEROUTPUT ON; au début du fichier, avant les blocs PL/SQL.

Exécution¶

Exécuter la procédure avec un bloc anonyme :

BEGIN
    nomProcedure;
END;
/

CORRIGÉ¶

Solution

CREATE OR REPLACE PROCEDURE remplirResumeMatch
IS
--Cursor qui extrait les matchs qui ont été joués
CURSOR matchs IS (
    SELECT distinct pk_mtcNoMatch 
    FROM MATCH 
    INNER JOIN jxequipematch
    ON pk_mtcNoMatch = fk_jxemNoMatch
    WHERE jxemARemporteMatch <> '?'
    );

--Variables pour recevoir le retour de requêtes SELECT
equipeGagnante equipe.eqpnomequipe%TYPE;
equipePerdante equipe.eqpnomequipe%TYPE;

BEGIN
--Pour chaque match dans le curseur, trouver l'équipe gagnante, l'équipe perdante, et conserver le tout dans la table de résumé des matchs
FOR unMatch IN matchs LOOP
    --Extraire l'équipe gagnante
    SELECT eqpNomEquipe into equipeGagnante 
    from equipe
    inner join JXEQUIPEMATCH 
    on pk_eqpNoEquipe = fk_jxemNoEquipe
    where fk_jxemNoMatch = unMatch.pk_mtcNoMatch
    and jxemARemporteMatch = 'O';

    --Extraire l'équipe perdante
    SELECT eqpNomEquipe into equipePerdante 
    from equipe
    inner join JXEQUIPEMATCH 
    on pk_eqpNoEquipe = fk_jxemNoEquipe
    where fk_jxemNoMatch = unMatch.pk_mtcNoMatch
    and jxemARemporteMatch = 'N';

    --Conserver dans la table de résumé
    INSERT INTO resumeMatch VALUES (unMatch.pk_mtcNoMatch, equipeGagnante, equipePerdante);
END LOOP;

EXCEPTION
    WHEN TOO_MANY_ROWS THEN
        DBMS_OUTPUT.PUT_LINE('Un SELECT retourne trop de lignes.' || SQLERRM);
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('Un SELECT ne retourne rien.' || SQLERRM);
END;
/

Fonction : Nombre matchs gagnés¶

Écrire une fonction qui reçoit en paramètres :

le nom d'une équipe
une année

et qui retourne le nombre de matchs gagnés par cette équipe lors de l'année reçue.

Exécution¶

Tester avec la requête :

SELECT eqpNomEquipe, calculerMatchsGagnes(eqpNomEquipe, 2025) FROM Equipe;

Solution¶

Solution

CREATE OR REPLACE FUNCTION calculerMatchsGagnes(nomEquipe VARCHAR2, annee NUMBER)
RETURN NUMBER
IS
    --Numéro d,équipe pour extraire les bonnes données
    numEquipe Equipe.pk_eqpNoEquipe%TYPE;

    --Nombre matchs à retourner
    nombreMatchsGagnes NUMBER(2);
    BEGIN
    --Trouver le numéro de l'équipe
    SELECT pk_eqpNoEquipe INTO numEquipe
    FROM Equipe
    WHERE eqpNomEquipe = nomEquipe;

    --Compter le nombre de matchs gagnés par cette équipe à l'année donnée
    SELECT count(*) INTO nombreMatchsGagnes
    FROM JxEquipeMatch
    INNER JOIN Match 
    ON fk_jxemNoMatch = pk_mtcNoMatch
    WHERE fk_jxemNoEquipe = numEquipe 
    AND EXTRACT(YEAR FROM mtcDateMatch) = annee
    AND jxemARemporteMatch = 'O';

    RETURN nombreMatchsGagnes;
END;
/