--Question1 :
CREATE OR REPLACE FUNCTION joueurLicencie (p_nomjoueur VARCHAR2)
RETURN NUMBER IS
v_nom VARCHAR2(50);
BEGIN
SELECT NomJoueur INTO v_nom FROM JOUEUR WHERE NomJoueur=p_nomjoueur;
RETURN 1;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN 0;
WHEN OTHERS THEN
RETURN 0;
END;
--Question2:
CREATE OR REPLACE FUNCTION participeAuTournoi(p_nomjoueur VARCHAR2, p_lieutournoi
VARCHAR2, p_annee CHAR)
RETURN NUMBER IS
v_count NUMBER;
BEGIN
SELECT COUNT(*) INTO v_count FROM RENCONTRE
WHERE (NOMGGAGNANT=p_nomjoueur OR NOMPERDANT=p_nomjoueur)
AND LIEUTOURNOI=p_lieutournoi
AND ANNEE=p_annee;
IF v_count >= 1 THEN RETURN 1;
ELSE RETURN 0;
END IF;
EXCEPTION
WHEN OTHERS THEN RETURN 0;
END;
/
--QUESTION 3:
CREATE OR REPLACE FUNCTION sommePrimeDeTournoi (p_lieu VARCHAR2, p_annee CHAR)
RETURN NUMBER IS
v_somme NUMBER :=0;
CURSOR C IS SELECT PRIME FROM GAIN WHERE lieutournoi=p_lieu AND annee=p_annee ;
BEGIN
FOR r IN C LOOP
v_somme:=v_somme+NVL([Link],0);
END LOOP;
RETURN v_somme;
END;
/
--or without cursor
CREATE OR REPLACE FUNCTION sommePrimeDeTournoi (p_lieu VARCHAR2, p_annee CHAR)
RETURN NUMBER IS
v_somme NUMBER := 0;
BEGIN
SELECT SUM(Prime) INTO v_somme
FROM GAIN
WHERE LieuTournoi = p_lieu
AND Annee = p_annee;
RETURN NVL(v_somme, 0);
EXCEPTION
WHEN NO_DATA_FOUND THEN RETURN 0;
WHEN OTHERS THEN RETURN 0;
END;
/
--QUESTION 4:
CREATE OR REPLACE FUNCTION rencontresGagneTournoi (p_nom VARCHAR2, p_lieu VARCHAR2,
p_annee CHAR)
RETURN NUMBER IS
v_gagne NUMBER;
BEGIN
SELECT COUNT(*) INTO v_gagne FROM RENCONTRE
WHERE LieuTournoi=p_lieu
AND Annee=p_annee
AND NomGagnant= p_nom;
RETURN v_gagne;
EXCEPTION
WHEN NO_DATA_FOUND THEN RETURN 0;
WHEN OTHERS THEN RETURN 0;
END;
/
--with cursor:
CREATE OR REPLACE FUNCTION rencontresGagneTournoi (p_nomjoueur VARCHAR2, p_lieu
VARCHAR2, p_annee CHAR)
RETURN NUMBER IS
v_gagne NUMBER := 0;
CURSOR c IS
SELECT NomGagnant
FROM RENCONTRE
WHERE LieuTournoi = p_lieu
AND Annee = p_annee
AND NomGagnant = p_nomjoueur;
BEGIN
FOR r IN c LOOP
v_gagne := v_gagne + 1;
END LOOP;
RETURN v_gagne;
EXCEPTION
WHEN NO_DATA_FOUND THEN RETURN 0;
WHEN OTHERS THEN RETURN 0;
END;
/
--QUESTION5
CREATE OR REPLACE FUNCTION estToujoursGagnant (p_nom VARCHAR2)
RETURN NUMBER IS
v_count NUMBER;
v_c NUMBER;
BEGIN
SELECT COUNT(*) INTO v_count FROM RENCONTRE
WHERE NomGagnant=p_nom AND NomPerdant=p_nom;
SELECT COUNT(*) INTO v_c FROM RENCONTRE
WHERE NomGagnant=p_nom;
IF (v_count>0 AND v_count = v_c) THEN RETURN 1;
ELSE RETURN 0;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN RETURN 0;
WHEN OTHERS THEN RETURN 0;
END;
/
--Question6:
CREATE OR REPLACE PROCEDURE affectivePrime(
p_LieuTournoi VARCHAR2,
p_Annee CHAR
) IS
v_total_prime NUMBER := 0;
v_winner VARCHAR2(50);
v_runner_up VARCHAR2(50);
v_semi_finalists_count NUMBER := 0;
v_quarter_finalists_count NUMBER := 0;
v_other_players_count NUMBER := 0;
v_remaining_prime NUMBER := 0;
v_prime_per_other NUMBER := 0;
-- Cursor for all participants
CURSOR c_participants IS
SELECT DISTINCT NomJoueur
FROM RENCONTRE
WHERE LieuTournoi = p_LieuTournoi AND Annee = p_Annee;
-- Cursor for semi-finalists (lost in semi-finals)
CURSOR c_semi_finalists IS
SELECT NomPerdant
FROM RENCONTRE
WHERE LieuTournoi = p_LieuTournoi AND Annee = p_Annee AND Score = 'SF';
-- Cursor for quarter-finalists (lost in quarter-finals)
CURSOR c_quarter_finalists IS
SELECT NomPerdant
FROM RENCONTRE
WHERE LieuTournoi = p_LieuTournoi AND Annee = p_Annee AND Score = 'QF';
BEGIN
-- Step 1: Calculate total prize money using the function from Q3
v_total_prime := sommePrimeDeTournoi(p_LieuTournoi, p_Annee);
IF v_total_prime = 0 THEN
DBMS_OUTPUT.PUT_LINE('Aucune prime disponible pour ce tournoi.');
RETURN;
END IF;
-- Step 2: Identify winner and runner-up from the final match
BEGIN
SELECT NomGagnant, NomPerdant
INTO v_winner, v_runner_up
FROM RENCONTRE
WHERE LieuTournoi = p_LieuTournoi AND Annee = p_Annee AND Score = 'F';
-- Assign 35% to winner
INSERT INTO GAIN (NomJoueur, LieuTournoi, Annee, Prime)
VALUES (v_winner, p_LieuTournoi, p_Annee, v_total_prime * 0.35);
-- Assign 15% to runner-up
INSERT INTO GAIN (NomJoueur, LieuTournoi, Annee, Prime)
VALUES (v_runner_up, p_LieuTournoi, p_Annee, v_total_prime * 0.15);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Match final non trouvé.');
RETURN;
END;
-- Step 3: Assign prizes to semi-finalists (10% each)
FOR sf IN c_semi_finalists LOOP
v_semi_finalists_count := v_semi_finalists_count + 1;
INSERT INTO GAIN (NomJoueur, LieuTournoi, Annee, Prime)
VALUES ([Link], p_LieuTournoi, p_Annee, v_total_prime * 0.10);
END LOOP;
-- Step 4: Assign prizes to quarter-finalists (5% each)
FOR qf IN c_quarter_finalists LOOP
v_quarter_finalists_count := v_quarter_finalists_count + 1;
INSERT INTO GAIN (NomJoueur, LieuTournoi, Annee, Prime)
VALUES ([Link], p_LieuTournoi, p_Annee, v_total_prime * 0.05);
END LOOP;
-- Step 5: Count other participants and distribute remaining 35%
FOR p IN c_participants LOOP
IF [Link] != v_winner AND [Link] != v_runner_up THEN
-- Exclude semi-finalists and quarter-finalists
IF NOT EXISTS (
SELECT 1 FROM RENCONTRE
WHERE NomPerdant = [Link] AND LieuTournoi = p_LieuTournoi
AND Annee = p_Annee AND Score IN ('SF', 'QF')
) THEN
v_other_players_count := v_other_players_count + 1;
END IF;
END IF;
END LOOP;
-- Distribute remaining prime evenly
v_remaining_prime := v_total_prime * 0.35;
IF v_other_players_count > 0 THEN
v_prime_per_other := v_remaining_prime / v_other_players_count;
-- Assign to other participants
FOR p IN c_participants LOOP
IF [Link] != v_winner AND [Link] != v_runner_up THEN
IF NOT EXISTS (
SELECT 1 FROM RENCONTRE
WHERE NomPerdant = [Link] AND LieuTournoi = p_LieuTournoi
AND Annee = p_Annee AND Score IN ('SF', 'QF')
) THEN
INSERT INTO GAIN (NomJoueur, LieuTournoi, Annee, Prime)
VALUES ([Link], p_LieuTournoi, p_Annee, v_prime_per_other);
END IF;
END IF;
END LOOP;
END IF;
COMMIT;
DBMS_OUTPUT.PUT_LINE('Primes attribuées avec succès.');
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Erreur lors de l''attribution des primes : ' || SQLERRM);
ROLLBACK;
END;
/
ANOTHER EXERCICE :
CREATE OR REPLACE FUNCTION livreDisponible (p_isbn VARCHAR2)
RETURN NUMBER IS
v_count NUMBER;
BEGIN
SELECT COUNT(*) INTO v_count
FROM EMPRUNT
WHERE ISBN = p_isbn AND DateRetour IS NULL;
RETURN CASE WHEN v_count = 0 THEN 1 ELSE 0 END;
EXCEPTION
WHEN NO_DATA_FOUND THEN RETURN 1;
WHEN OTHERS THEN RETURN 0;
END;
/
CREATE OR REPLACE FUNCTION sommeAmendes (p_matricule VARCHAR2)
RETURN NUMBER IS
v_somme NUMBER := 0;
BEGIN
SELECT NVL(SUM(Nvl(Amende, 0)), 0) INTO v_somme
FROM RETARD
WHERE Matricule = p_matricule;
RETURN v_somme;
EXCEPTION
WHEN NO_DATA_FOUND THEN RETURN 0;
WHEN OTHERS THEN RETURN 0;
END;
/
CREATE OR REPLACE FUNCTION nombreEmprunts (p_matricule VARCHAR2)
RETURN NUMBER IS
v_count NUMBER;
BEGIN
SELECT COUNT(*) INTO v_count
FROM EMPRUNT
WHERE Matricule = p_matricule AND DateRetour IS NOT NULL;
RETURN v_count;
EXCEPTION
WHEN NO_DATA_FOUND THEN RETURN 0;
WHEN OTHERS THEN RETURN 0;
END;
/
CREATE OR REPLACE FUNCTION aucunRetard (p_matricule VARCHAR2)
RETURN NUMBER IS
v_count NUMBER;
BEGIN
SELECT COUNT(*) INTO v_count
FROM RETARD
WHERE Matricule = p_matricule;
RETURN CASE WHEN v_count = 0 THEN 1 ELSE 0 END;
EXCEPTION
WHEN NO_DATA_FOUND THEN RETURN 1;
WHEN OTHERS THEN RETURN 0;
END;
/
CREATE OR REPLACE PROCEDURE mettreAJourRetard IS
v_retard NUMBER;
CURSOR c_emprunts IS
SELECT Matricule, ISBN, DateEmprunt, DateRetour
FROM EMPRUNT
WHERE DateRetour IS NOT NULL;
BEGIN
FOR e IN c_emprunts LOOP
SELECT FLOOR(SYSDATE - DateRetour) INTO v_retard
FROM DUAL
WHERE DateRetour < SYSDATE AND DateRetour IS NOT NULL;
IF v_retard > 0 THEN
INSERT INTO RETARD (Matricule, ISBN, Amende)
VALUES ([Link], [Link], v_retard * 1)
ON DUPLICATE KEY UPDATE Amende = v_retard * 1;
END IF;
END LOOP;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Erreur : ' || SQLERRM);
ROLLBACK;
END;
/