TD7

      SELECT * FROM jobs;

      -- 1
      DROP TABLE jobs;
      CREATE  TABLE JOBS
      (
        id NUMBER,
        poste VARCHAR2(100),
        CONSTRAINT pk_jobs PRIMARY KEY (id, poste)
      );

      -- 1.

      CREATE  OR REPLACE PROCEDURE AJOUT_JOB(id IN jobs.id%TYPE , poste IN jobs.poste%TYPE )
        IS
          e_same_id EXCEPTION;
          e_same_poste EXCEPTION;
          CURSOR c_job IS SELECT * FROM jobs;
        BEGIN
          FOR v_job IN c_job LOOP
            IF v_job.id = id THEN
              RAISE e_same_id;
            END IF;

            IF v_job.poste = poste THEN
              RAISE e_same_poste;
            END IF;

          END LOOP;
          INSERT INTO JOBS VALUES(id, poste);

        EXCEPTION
          WHEN e_same_id THEN
            PRINT('Attention : ID ' || id  || ' est déja présent dans la table jobs.');
          WHEN e_same_poste THEN
            PRINT('Le Metier ' || poste || ' est déja présent avec un autre ID.');

        END ;


      BEGIN

        AJOUT_JOB(1, 'Secrétaire');
        AJOUT_JOB(2, 'Tresorier');
        AJOUT_JOB(3, 'Président');
        AJOUT_JOB(4, 'Vice Président');
      END;

      BEGIN

        AJOUT_JOB(5, 'Secrétaire');
        AJOUT_JOB(6, 'Tresorier');
        AJOUT_JOB(7, 'Président');
        AJOUT_JOB(8, 'Vice Président');
      END;


  COMMIT ;

      -- 2.

      CREATE  OR REPLACE  PROCEDURE MODIF_job(in_id IN NUMBER, newPoste IN VARCHAR2) IS
        v_id NUMBER;

        BEGIN
          UPDATE jobs SET poste = newPoste WHERE id = in_id;
      END;


  BEGIN
    MODIF_job(1, 'Secrétaire');
  END;

  SELECT * FROM JOBS;





  --3
      SELECT * FROM emp;

      CREATE  OR REPLACE PROCEDURE empSupp IS

        CURSOR c_emp IS SELECT * FROM emp;
        empSuppName  emp.ename%TYPE;

      BEGIN
        FOR v_emp IN c_emp LOOP
          SELECT ename INTO empSuppName FROM emp WHERE emp.EMPNO =7902;
          DBMS_OUTPUT.PUT_LINE('Nom : ' || v_emp.ENAME || ' Supérieur : ' || empSuppName);
        END LOOP;
      END;

  SELECT * FROM emp;

  BEGIN
    empSupp();
  END;



  -- 4

   CREATE  OR REPLACE PROCEDURE SALMED (in_id IN NUMBER) IS
     CURSOR c_emp IS SELECT * FROM emp;
     salRef emp.sal%TYPE;

    BEGIN
      SELECT sal INTO salRef FROM emp WHERE EMPNO = in_id;
      FOR v_emp IN c_emp LOOP
        IF v_emp.EMPNO <> in_id AND (v_emp.sal + v_emp.COMM > salRef) THEN
          DBMS_OUTPUT.PUT_LINE(v_emp.ENAME || 'gagne plus que ' || in_id);
        END IF;
      END LOOP;
    END;


  BEGIN
    SALMED(7521);
  END;




  -- 5
  -- Le meme job ou un salaire > a l'employé (passé en nom)


    CREATE  OR REPLACE  PROCEDURE  sameOrMore(in_ename IN emp.ename%TYPE) IS
      CURSOR c_emp IS SELECT * FROM emp;
      jobRef emp.job%TYPE;
      salRef emp.sal%TYPE;
      BEGIN
        SELECT job INTO jobRef FROM emp WHERE ENAME = in_ename;
        SELECT sal INTO salRef FROM emp WHERE ENAME = in_ename;

        FOR v_emp IN c_emp LOOP
          IF v_emp.JOB =  jobRef OR v_emp.sal > salRef THEN
            DBMS_OUTPUT.PUT_LINE(v_emp.ENAME);
          END IF;


        END LOOP;
      END;


  BEGIN
    sameOrMore('SMITH');
  END;


  -- 6
  -- Generer la liste des N plus gros salaire

      CREATE OR REPLACE PROCEDURE Nbest(in_n IN NUMBER) IS

        CURSOR c_emp IS SELECT * FROM emp ORDER BY sal DESC ;
        v_emp emp%ROWTYPE;
        rowCount INTEGER;
        e_notEnoughRow EXCEPTION;

      BEGIN
        SELECT COUNT(*) INTO rowCount FROM emp;
        OPEN c_emp;
        IF in_n > rowCount THEN
          RAISE e_notEnoughRow;
        END IF;
        FOR i IN  0..in_n LOOP
          FETCH  c_emp INTO v_emp;
          DBMS_OUTPUT.put_line('Nom : ' ||v_emp.ENAME || '  Sal : '|| v_emp.SAL);
      END LOOP;
        CLOSE c_emp;

      EXCEPTION
          WHEN e_notEnoughRow THEN
        DBMS_OUTPUT.PUT_LINE('Erreur : Vous avez demandé trop d employés');


      END;


  BEGIN
  NBEST(100);
  END;


--7
-- Afficher par ordres décroissant les départements sans employé

      SELECT COUNT(*) FROM emp GROUP BY DEPTNO;
      SELECT deptno FROM dept;
  CREATE  OR REPLACE  PROCEDURE deptWithoutEmpDesc IS

    CURSOR c_dept IS SELECT DEPTNO FROM dept;
    nbEmp INTEGER;
    BEGIN
      FOR v_dept IN c_dept LOOP
        SELECT COUNT(*) INTO nbEmp FROM emp WHERE deptno = v_dept.DEPTNO;
        IF nbEmp = 0 THEN
          DBMS_OUTPUT.PUT_LINE('Le département numéro ' || v_dept.DEPTNO || ' n a pas d employé.');
        END IF;
      END LOOP;
    END;



-- 9
-- Creer une vue donnant la somme des salaires par département
      DECLARE

        CURSOR c_emp IS SELECT deptno FROM dept;
        sal emp.sal%TYPE := 0;
        currentMoy emp.sal%TYPE;
      BEGIN
        FOR v_emp IN c_emp LOOP

          SELECT SUM(sal) INTO currentMoy FROM emp WHERE deptno = v_emp.DEPTNO;

          INSERT INTO dept2 (BUDGET) VALUES (currentMoy) WHERE deptno = v_emp.deptno;
        END LOOP;

      END;

      SELECT * FROM dept2;