Exercice corrigé sur les fonctions et procédures - Oracle PL/SQL

Considérez les relations suivantes :

•  Employe(nuempl:string, nomempl:string, hebdo: integer,salaire integer, #affect:integer)
•  Service(nuserv:integer, nomserv:string, #chef:integer)
•  Projet(nuproj:integer, nomproj:string, #resp:integer)
•  Travail(#nuempl:integer, #nuproj:integer, duree:integer)

Pour simplifier l'exploration de la base de données, il vous est demandé d'implémenter les fonctions ou procédures suivantes :

1. Ecrire une procédure qui prend comme arguments un identifiant d'employé et un nombre, puis mettre à jour le salaire de l'employé donné avec le nombre donné.  Voir la réponse 

   CREATE OR REPLACE PROCEDURE update_salaire(idemp IN Employe.nuempl%type, sal IN number)
   IS
   BEGIN
       UPDATE EMPLOYE SET salaire=salaire+sal WHERE nuempl=idemp;
   END;
   /
                                               

2. Ecrire une fonction qui compte le nombre d'employés participant à un projet donné.  Voir la réponse 

   CREATE OR REPLACE FUNCTION projet_employe(idproj IN number)
   RETURN number
   IS
       nb number;
   BEGIN
       select count(*) into nb from Travail where nuproj=idproj;
       return nb;
   END;
   /
                                               

3. Ecrire une fonction qui compte le nombre de projets supervisés par les employés d'un service donné.  Voir la réponse 

   CREATE OR REPLACE FUNCTION projet_service(idservice IN number)
   RETURN number
   IS
       nb number;
   BEGIN
       select count(*) into nb from projet where resp in (select nuempl from employe where affect=idservice);
       return nb;
   END;
   /
                                               

4. Ecrire une fonction qui compte le nombre de projets auxquels participe l'employé donné.  Voir la réponse 

   CREATE OR REPLACE FUNCTION projet_employe(idemp IN number)
   RETURN number
   IS
       nb number;
   BEGIN
       select count(*) into nb from Travail where nuempl=idemp;
       return nb;
   END;
   /
                                               

5. Ecrire une fonction qui renvoie la chaîne 'Salaire faible' si le salaire de l'employé donné est supérieur à 2000\$ sinon retourner 'Bon salaire'.  Voir la réponse 

   CREATE OR REPLACE FUNCTION etat_salaire(salaire IN number)
   RETURN VARCHAR2
   BEGIN
       IF salaire<2000 THEN
           return 'Salaire faible';
       ELSE
           return 'Bon salaire';
       END IF
   END;
   /
                                               

6. Ecrire une fonction, qui compte le nombre d'employés qui prennent en charge plus que le nombre de projets donné.  Voir la réponse 

   CREATE OR REPLACE FUNCTION resp_pop(nbproj IN number)
   RETURN number
   IS
       nb number;
   BEGIN
       Select count(*) into nb from employe e where (select count(*) from projet p where e.nuempl=p.resp)> nbproj ;
       return nb;
   END;
   /
                                               

7. Ecrire une procédure qui insère l'employé donné dans une table de sauvegarde nommée 'ALERT_EMPLOYE'.  Voir la réponse 

   CREATE OR REPLACE PROCEDURE employe_backup(emp Employe%rowtype)
   IS
   BEGIN
       INSERT INTO ALERT_EMPLOYE VALUES(emp.nuempl,emp,nomemp,emp.hebdo,emp.salaire,emp.affect);
   END;
   /