Corrigé Exercices SQLPLUS Oracle TD Oracle SQL PLUS - Partie 2 -

 - Consulter la page de l'exercice.
- Solution de l'exercice.


Correction Exercice 3


create table emp_fr as select * from emp;
delete from emp_fr;




-- Remplissage de la table EMP_FR

DECLARE

   n NUMBER(2);
   CURSOR employes IS
   SELECT empno, ename, job, mgr, hiredate, sal, comm, deptno
   FROM emp;
   employe employes%ROWTYPE;
   newsal emp.sal%TYPE;
   newcomm emp.comm%TYPE;
   empvide EXCEPTION;

BEGIN

-- Test table vide

SELECT COUNT(*) INTO n FROM emp;
  IF n=0 THEN
     RAISE empvide;
  END IF;

-- Remplissage emp_fr

FOR employe IN employes LOOP

    -- Calculs
         newsal:=employe.sal*6;
         newsal:=newsal*1.25;

        IF employe.comm IS NOT NULL THEN
           newcomm:=employe.comm*6;
        ELSE
           newcomm:=NULL;
        END IF;

-- Insertion donnees

INSERT INTO emp_fr VALUES(employe.empno, employe.ename, employe.job, employe.mgr, employe.hiredate, newsal, newcomm, employe.deptno);
END LOOP;

EXCEPTION
WHEN empvide THEN RAISE_APPLICATION_ERROR(-20501,'Pas d employe !');

END;