Examen 2019 PL/SQL

-- ex1
SET SERVEROUTPUT ON
ACCEPT code_Formation PROMPT 'Saisir le code la formation.';
ACCEPT code_Etudiant PROMPT 'Saisir le code de l''etudiant.';
DECLARE
  TYPE record_UV IS RECORD (CODUV1 INSCRIPTIONS.CODUV1, NOMUV1 INSCRIPTIONS.NOMUV1,
                            CODUV2 INSCRIPTIONS.CODUV1, NOMUV2 INSCRIPTIONS.NOMUV1,
                            CODUV3 INSCRIPTIONS.CODUV1, NOMUV3 INSCRIPTIONS.NOMUV1
                            );
  UV  record_UV;

  v_check INSCRIPTIONS%ROWTYPE; -- 3la wed dak test fl question 2
BEGIN
-- 1 -- fetching CODUi & NOMUVi for a given formation code.
  SELECT
  CODUV1, NOMUV1
  , CODUV2, NOMUV2
  , CODUV3, NOMUV3
  INTO UV
  FROM INSCRIPTIONS
  WHERE CODEFORM = &code_Formation
  GROUP BY CODEFORM;

-- 2 -- Si l'etudiant est deja inscrit à une filiere autre que ce que l'utilisateur a saisit, on la modifie.
  SELECT *
  INTO v_check
  FROM INSCRIPTIONS
  WHERE NUMETUD = &cod_Etudiant
  AND CODFORM != &cod_Formation;

  IF SQL%FOUND THEN -- ila l9a chi etudiant kayn
    UPDATE INSCRIPTIONS
    SET CODFORM = &cod_Formation
      , LIBELLE = ( SELECT LIBELLE
                    FROM INSCRIPTIONS
                    WHERE CODFORM = &cod_Formation )
      , CODUV1 = UV.CODUV1, NOMUV1 = UV.NOMUV1
      , CODUV2 = UV.CODUV2, NOMUV2 = UV.NOMUV2
      , CODUV3 = UV.CODUV3, NOMUV3 = UV.NOMUV3
      , NOTE1 = NULL, NOTE2 = NULL, NOTE3 = NULL
      , MOYENNE = NULL, MENTION = NULL
    WHERE NUMETUD = &cod_Etudiant;
  ELSE
      raise_application_error(-20003, "Etudiant introuvable ou déjà inscrit à cette filière.");
  END IF;

-- 3 -- Le calcul de la moyenne et de la mention de l'etudiant.
  -- had l blan dyl CASE rah 3adi, bhal switch case dyl C
  -- o had le3Ba dyl SET (MOYENNE, MENTION) bhal l blan dyl SELECT INTO o kat3tih 2 variable
  -- ye3Ni hna hssbt l moyenne b dik requete o f nefss l we9t l mention o returnithom bjuj
  UPDATE INSCRIPTIONS
  SET (MOYENNE, MENTION)=( SELECT (NOTE1+NOTE2+NOTE3)/3 AS MOYENNE,
                              CASE
                                  WHEN MOYENNE < 10 THEN 'AJ'
                                  WHEN MOYENNE >= 10 AND MOYENNE < 12 THEN 'PA'
                                  WHEN MOYENNE >= 12 AND MOYENNE < 14 THEN 'AB'
                                  WHEN MOYENNE >= 14 AND MOYENNE < 16 THEN 'B'
                                  WHEN MOYENNE >= 16 AND MOYENNE < 18 THEN 'TB'
                                  ELSE 'E'
                              END AS MENTION
                            FROM INSCRIPTIONS
                            WHERE NUMETUD = &cod_Etudiant
                            AND CODEFORM = &cod_Formation )
  WHERE NUMETUD = &cod_Etudiant
  AND CODEFORM = &cod_Formation;

EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('Formation inconnue');

END;
/

-- ex2
-- 1 --
CREATE OR REPLACE TRIGGER pret_mens
BEFORE INSERT
ON PRET_SANS_INTERET
FOR EACH ROW
DECLARE
  v_PRETNO NUMBER;
  v_SALN NUMBER;
BEGIN
  SELECT PRETNO -- si ce requete retourne plusieurs lignes, ça veut dire que l'employé a déjà fait 2 prêts, une exception va se lancer.
  INTO v_PRETNO
  FROM PRET_SANS_INTERET
  WHERE EMPNO = :NEW.EMPNO;

  SELECT SALN -- hna kenjbdo salaire dyalo bch n7ssbo bih dak l pourcentage
  INTO v_SALN
  FROM EMP
  WHERE EMPNO = v_EMPNO;

  IF (:NEW.MONTANT / :NEW.DUREE) > 0.3*(SALN) THEN -- si la mensualité du prêt dépasse 30% celle du client, une exception va se lancer
    raise_application_error(-20005,'La mensualité du prêt dépasse 30% celle du client');
  END IF;

EXCEPTION
  WHEN TOO_MANY_ROWS THEN
    DBMS_OUTPUT.put_line('Peut pas ajouter plus que 2 prêts par employé.');

END;
/

-- 2 --
-- souscription ze3Ma l ajout dyl new ligne f table dyl pret
CREATE OR REPLACE TRIGGER saln
AFTER INSERT
ON PRET_SANS_INTERET
FOR EACH ROW
BEGIN
  -- kif gelna mli khuna khda l pret,
  -- kikhess salaire net dyalo f kol mois ytn9ss bch ykhelss lina l pret
  -- dik DUREE dyl pret o l montant huwa chhal khda fl PRET
  -- ila khda 2000Dh o galihom bli ghanrej3O likom f derf 3am
  -- kol chhar ghadi ne9sso lih 2000 / 12 = hh
  UPDATE EMP
  SET SALN = SALB - :NEW.MONTANT / :NEW.DUREE
  WHERE EMPNO = :NEW.EMPNO;
END;
/