présentation

Le problème ci-dessous s’inspire du problème sur Oracle du TP suivant : http://www-bd.lip6.fr/wiki/site/enseignement/licence/2i009/documents-tme/tme-plsql

Nous travaillerons par la suite sur les tables suivantes qui permettent de décrire des employés, des projets, l’affectation des employés aux projets et les grilles de salaire pour différents profils de postes :

EMPLOYE(NumSS, NomE, PrenomE, VilleE, DateNaiss)
PROJET(NumProj, NomProj, #RespProj,, VilleP, Budget)
EMBAUCHE(#NumSS,#NumProj, DateEmb, #Profil)
GRILLE_SAL(Profil, Salaire)

La clé primaire de chaque table est soulignée.

Modèle conceptuel de données et base de données

  1. Dessiner le MCD sur Looping à partir de ce MLD


  1. Réaliser un script pour MySQL à partir du script ci-dessous prévu pour ORACLE :


create table Employe( 
 NumSS number(5) , 
 NomE varchar2(20) ,
 PrenomE varchar2(20) , 
 VilleE varchar2(20) , 
 DateNaiss date 
); 


create table Projet( 
 NumProj Number(3),
 NomProj varchar2(20),
 RespProj number, 
 VilleP varchar2(20), 
 Budget number 
); 

create table Grille_sal( 
 profil varchar2(20), 
 salaire number(7,2)
); 


create table Embauche( 
 NumSS number ,
 NumProj number , 
 DateEmb date default sysdate, 
 Profil varchar2(20)
);

insert into Employe (NumSS, NomE, PrenomE, VilleE, DateNaiss)
values (
(22334, 'Adam', 'Funk', 'Paris',   '1-12-1982'  ),
(45566, 'Rachid', 'Allaoui', 'Lyon',   '13-4-1986'  ),
(77889, 'Florent', 'Girac' , 'Marseille',   '4-11-1990'  ),
(90011, 'Mayla', 'Aoun', 'Lyon',   '26-3-1987'  ),
(22233, 'Christine', 'Lara', 'Paris',   '9-8-1982'  ),
(34445, 'Amel', 'Orlando', 'Lyon',   '14-2-1976'  ),
(55666, 'Mohsen', 'Charef', 'Paris',   '28-5-1991'  ),
(77788, 'Tim', 'Arabi', 'Marseille',   '8-6-1984'  ),
(89990, 'Fernando', 'Lopez', 'Lyon',   '5-10-1993'  ),
(11122, 'Alain','Tan Lee', 'Marseille',   '21-3-1994'  ),
(11123, 'Franck', 'Morel', 'Lille',   '10-01-1945'  ),
(11124, 'Albert', 'Maure', 'Paris',   '10-01-1948'  ),
(11125, 'Beatrice', 'Malloire', 'Paris',   '10-01-1946'  ),
(11126, 'Christian', 'Millan', 'Paris',   '10-01-1947'  )
);


insert into Projet values (123, 'ADOOP', 22334, 'Paris', 120000);
insert into Projet values (757, 'SKALA', 45566, 'Lyon', 180000);
insert into Projet values (890, 'BAJA', 22334, 'Paris', 24000);


insert into grille_sal values ('Admin', 80000);
insert into grille_sal values ('Deve', 45000);
insert into grille_sal values ('Tech', 35000);

insert into Embauche values (77889, 123,   '1-3-2014'  ,'Deve');
insert into Embauche values (90011, 123,   '1-5-2014'  ,'Tech');
insert into Embauche values (22233, 757,   '1-3-2014'  ,'Deve');
💡 exemple de solution proposée par l’IA générative 

DROP TABLE IF EXISTS Embauche,Grille_sal,Projet,Employe;

CREATE TABLE Employe (
    NumSS INT(5) PRIMARY KEY,
    NomE VARCHAR(20),
    PrenomE VARCHAR(20),
    VilleE VARCHAR(20),
    DateNaiss DATE
);

CREATE TABLE Projet (
    NumProj INT(3) PRIMARY KEY,
    NomProj VARCHAR(20),
    RespProj INT,
    VilleP VARCHAR(20),
    Budget DECIMAL(10,2),
    FOREIGN KEY (RespProj) REFERENCES Employe(NumSS)
);

CREATE TABLE Grille_sal (
    profil VARCHAR(20) PRIMARY KEY,
    salaire DECIMAL(7,2)
);

CREATE TABLE Embauche (
    NumSS INT,
    NumProj INT,
    DateEmb DATE DEFAULT (CURRENT_DATE), -- curdate() , -- CURRENT_DATE, 
    Profil VARCHAR(20),
    PRIMARY KEY (NumSS, NumProj, DateEmb),
    FOREIGN KEY (NumSS) REFERENCES Employe(NumSS),
    FOREIGN KEY (NumProj) REFERENCES Projet(NumProj),
    FOREIGN KEY (Profil) REFERENCES Grille_sal(profil)
);

INSERT INTO Employe (NumSS, NomE, PrenomE, VilleE, DateNaiss)
VALUES
(22334, 'Adam', 'Funk', 'Paris', STR_TO_DATE('01-12-1982', '%d-%m-%Y')),
(45566, 'Rachid', 'Allaoui', 'Lyon', STR_TO_DATE('13-04-1986', '%d-%m-%Y')),
(77889, 'Florent', 'Girac', 'Marseille', STR_TO_DATE('04-11-1990', '%d-%m-%Y')),
(90011, 'Mayla', 'Aoun', 'Lyon', STR_TO_DATE('26-03-1987', '%d-%m-%Y')),
(22233, 'Christine', 'Lara', 'Paris', STR_TO_DATE('09-08-1982', '%d-%m-%Y')),
(34445, 'Amel', 'Orlando', 'Lyon', STR_TO_DATE('14-02-1976', '%d-%m-%Y')),
(55666, 'Mohsen', 'Charef', 'Paris', STR_TO_DATE('28-05-1991', '%d-%m-%Y')),
(77788, 'Tim', 'Arabi', 'Marseille', STR_TO_DATE('08-06-1984', '%d-%m-%Y')),
(89990, 'Fernando', 'Lopez', 'Lyon', STR_TO_DATE('05-10-1993', '%d-%m-%Y')),
(11122, 'Alain', 'Tan Lee', 'Marseille', STR_TO_DATE('21-03-1994', '%d-%m-%Y')),
(11123, 'Franck', 'Morel', 'Lille', STR_TO_DATE('10-01-1945', '%d-%m-%Y')),
(11124, 'Albert', 'Maure', 'Paris', STR_TO_DATE('10-01-1948', '%d-%m-%Y')),
(11125, 'Beatrice', 'Malloire', 'Paris', STR_TO_DATE('10-01-1946', '%d-%m-%Y')),
(11126,'Christian', 'Millan', 'Paris', STR_TO_DATE('10-01-1947', '%d-%m-%Y'))
;


INSERT INTO Projet VALUES (123, 'ADOOP', 22334, 'Paris', 120000);
INSERT INTO Projet VALUES (757, 'SKALA', 45566, 'Lyon', 180000);
INSERT INTO Projet VALUES (890, 'BAJA', 22334, 'Paris', 24000);

INSERT INTO Grille_sal VALUES ('Admin', 80000);
INSERT INTO Grille_sal VALUES ('Deve', 45000);
INSERT INTO Grille_sal VALUES ('Tech', 35000);

INSERT INTO Embauche VALUES (77889, 123, STR_TO_DATE('01-03-2014', '%d-%m-%Y'), 'Deve');
INSERT INTO Embauche VALUES (90011, 123, STR_TO_DATE('01-05-2014', '%d-%m-%Y'), 'Tech');
INSERT INTO Embauche VALUES (22233, 757, STR_TO_DATE('01-03-2014', '%d-%m-%Y'), 'Deve');


Exercices PL-SQL

La question 1 du TP sur oracle sera traitée à la question 3 (c’est la même chose mais c’est bien plus compliqué sur MySQL)



  1. Exo 1 . Écrivez une procédure PL/SQL de nom “SupprimerEmployes70ansOuPlus” qui supprime tous les employés de la table EMPLOYE qui ont 70 ans ou plus et qui affiche le nombre de lignes qui ont été supprimées ou le message ‘Aucun employé supprimé’ si aucun employé n’a plus de 70 ans. Faire une démonstration (validation) dans un terminal..


Faire valider votre script en affichant dans un terminal :

MariaDB [BDD_s2_tp]> CALL SupprimerEmployes70ansOuPlus();
+-----------------------------+
| Message                     |
+-----------------------------+
| 4 employé(s) supprimé(s).   |
+-----------------------------+
1 row in set (0,004 sec)
...

MariaDB [BDD_s2_tp]> CALL SupprimerEmployes70ansOuPlus();
+---------------------------+
| Message                   |
+---------------------------+
| Aucun employé supprimé.   |
+---------------------------+

L’instruction SELECT * FROM Employe; permet de voir que franck, albert … ont disparus



  1. Exo 2. Écrivez une procédure PL/SQL de nom “ModifierProfilsEtSalaire” qui modifie tous les noms des profils dans la table “Grille_sal” en ajoutant la lettre ‘P’ devant chaque nom de profil. Pour chaque profil modifié dans la table “Grille_sal” les valeurs correspondantes dans la table embauche doivent également être modifiées. Attention à la clé étrangère : il faut que votre script la supprime puis la re-crée .


Faire valider votre script en affichant dans un terminal : 

MariaDB [BDD_s2_tp]> SELECT profil, salaire
    ->         FROM Grille_sal;
+--------+----------+
| profil | salaire  |
+--------+----------+
| Admin  | 80000.00 |
| Deve   | 45000.00 |
| Tech   | 35000.00 |
+--------+----------+
3 rows in set (0,001 sec)

MariaDB [BDD_s2_tp]> SHOW CREATE TABLE Embauche;
+----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table    | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        |
+----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Embauche | CREATE TABLE `Embauche` (
  `NumSS` int(11) NOT NULL,
  `NumProj` int(11) NOT NULL,
  `DateEmb` date NOT NULL DEFAULT curdate(),
  `Profil` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`NumSS`,`NumProj`,`DateEmb`),
  KEY `NumProj` (`NumProj`),
  KEY `Profil` (`Profil`),
  CONSTRAINT `Embauche_ibfk_1` FOREIGN KEY (`NumSS`) REFERENCES `Employe` (`NumSS`),
  CONSTRAINT `Embauche_ibfk_2` FOREIGN KEY (`NumProj`) REFERENCES `Projet` (`NumProj`),
  CONSTRAINT `Embauche_ibfk_3` FOREIGN KEY (`Profil`) REFERENCES `Grille_sal` (`profil`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci |
+----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0,000 sec)

MariaDB [BDD_s2_tp]> CALL ModifierProfilsEtSalaire();
+-----------------------------------------------+  
| Message                                       |
+-----------------------------------------------+
| Les profils ont été modifiés avec succès.     |
+-----------------------------------------------+
1 row in set (0,568 sec)

Query OK, 12 rows affected (0,568 sec)

MariaDB [BDD_s2_tp]> SELECT profil, salaire
    ->         FROM Grille_sal;
+--------+----------+
| profil | salaire  |
+--------+----------+
| PAdmin | 80000.00 |
| PDeve  | 45000.00 |
| PTech  | 35000.00 |
+--------+----------+
3 rows in set (0,001 sec)

SELECT * FROM Embauche;



  1. (Curseur avec des paramètres) Écrivez une procédure PL/SQL de nom “AfficherProjetsEmployes” qui affiche tous les noms de projet, pour chaque nom de projet on affiche les noms des employés affectés à ce projet. Utiliser un curseur, utiliser une boucle avec l’instruction WHILE .


voir question 1 dans le tp sur oracle


Modifier la procédure PL-SQL ci-dessus (écrite pour ORACLE) :

DECLARE 
    trouve BOOLEAN;
BEGIN
   FOR  r  IN (SELECT numproj, nomproj, villep FROM Projet ORDER BY nomproj)
    LOOP
             dbms_output.put_line('-----------------------------------');
             dbms_output.put_line('Projet: '||r.nomproj||' dans la ville  '||r.villep);
             dbms_output.put_line('-----------------------------------');
                       trouve := FALSE;
                       FOR r2 IN (SELECT nome, prenome FROM employe e, embauche b 
                                      WHERE e.numss=b.numss AND b.numproj=r.numproj )
                       LOOP
                            dbms_output.put_line('Employé: '|| r2.nome||', '|| r2.prenome);
                            trouve := TRUE;
                      END LOOP;
                      IF(trouve = FALSE) THEN
                            dbms_output.put_line('Pas d''employé');
                       END IF;
    END LOOP;
END;


Faire valider votre script en affichant dans un terminal (

le résultat de l’instruction CALL AfficherProjetsEmployes(); dans un terminal connecté à MySQL avec le jeu de test donné est :

MariaDB [BDD_s2_tp]> CALL AfficherProjetsEmployes();
+--------------------------------------+
| Ligne                                |
+--------------------------------------+
| -----------------------------------  |
| Projet: ADOOP dans la ville Paris    |
| -----------------------------------  |
| Nombre d'employés: 2                 |
| Employé(s): Florent, Girac           |
| Employé(s): Mayla, Aoun              |
| -----------------------------------  |
| Projet: BAJA dans la ville Paris     |
| -----------------------------------  |
| Nombre d'employés: 0                 |
| *** personne affectée sur ce projet  |
| -----------------------------------  |
| Projet: SKALA dans la ville Lyon     |
| -----------------------------------  |
| Nombre d'employés: 1                 |
| Employé(s): Christine, Lara          |
+--------------------------------------+
16 rows in set (0,007 sec)



  1. (Utilisation CASE et Curseur) Écrivez une procédure PL/SQL de nom “AugmenterSalaires” qui augmente tous les salaires dans la table Grille_Sal. Les salaires inférieurs à 40000 sont augmentés de 30%, les salaires compris entre 40000 et 60000 de 20% et les salaires supérieurs à 60000 sont augmentés de 10%. Utilisez un CASE pour tester les différentes valeurs possibles du salaire.


Faire valider votre script en affichant dans un terminal : 

MariaDB [BDD_s2_tp]> SELECT profil, salaire
    ->         FROM Grille_sal;
+--------+----------+
| profil | salaire  |
+--------+----------+
| PAdmin | 80000.00 |
| PDeve  | 45000.00 |
| PTech  | 35000.00 |
+--------+----------+
3 rows in set (0,001 sec)

MariaDB [BDD_s2_tp]> CALL AugmenterSalaires();
+-------------------------------------------------+
| Message                                         |
+-------------------------------------------------+
| Les salaires ont été augmentés avec succès.     |
+-------------------------------------------------+
1 row in set (0,010 sec)

Query OK, 3 rows affected (0,010 sec)

MariaDB [BDD_s2_tp]> SELECT profil, salaire
    ->         FROM Grille_sal;
+--------+----------+
| profil | salaire  |
+--------+----------+
| PAdmin | 88000.00 |
| PDeve  | 54000.00 |
| PTech  | 45500.00 |
+--------+----------+
3 rows in set (0,001 sec)

MariaDB [BDD_s2_tp]>



  1. (Exception prédéfinie) Écrire une procédure PL/SQL “InsererNouvelEmploye” qui a 3 paramètres : le numSS, le nomE et le prenomE d’un nouvel employé à insérer dans la table Employé. S’il existe déjà un employé avec le même numSS dans la table Employé, elle doit afficher un message d’erreur sans insérer l’employé. Dans le cas contraire la procédure doit insérer le nouvel employé et afficher un message de confirmation. Utilisez le gestionnaire d’erreur MySQL DECLARE … HANDLER FOR SQLSTATE ‘23000’ à la place de l’exception Oracle NO_DATA_FOUND.


Faire valider votre script en affichant dans un terminal : 

MariaDB [BDD_s2_tp]> CALL InsererNouvelEmploye(1234567, 'Alain', 'Paul');
+---------------------------------------+
| Message                               |
+---------------------------------------+
| Employé inséré avec succès.           |
+---------------------------------------+
1 row in set (0,004 sec)

MariaDB [BDD_s2_tp]> SELECT * FROM Employe;
...

MariaDB [BDD_s2_tp]> CALL InsererNouvelEmploye(22334, 'Adam', 'Funk');
+------------------------------------------------+
| Message                                        |
+------------------------------------------------+
| Erreur : cet employé existe déjà.              |
+------------------------------------------------+
1 row in set (0,002 sec)

MariaDB [BDD_s2_tp]> SELECT * FROM Employe;



  1. (Exception utilisateur) Écrire une procédure “InsererNouveauProjet” qui prend comme paramètres le numéro et le budget d’un projet et qui l’ajoute à la table Projet seulement si la somme de tous les budgets après l’ajout ne dépasse pas 400000. Dans le cas contraire le projet n’est pas ajouté et un message d’erreur est affiché à l’utilisateur. Utilisez une exception utilisateur.


Faire valider votre script en affichant dans un terminal : 

MariaDB [BDD_s2_tp]> SELECT SUM(Budget) FROM Projet;
+-------------+
| SUM(Budget) |
+-------------+
|      324000 |
+-------------+
1 row in set (0,001 sec)

MariaDB [BDD_s2_tp]> CALL InsererNouvelProjet(999, 50000);
+---------------------------------------+
| Message                               |
+---------------------------------------+
| Projet inséré avec succès.            |
+---------------------------------------+
1 row in set (0,004 sec)

MariaDB [BDD_s2_tp]> SELECT * FROM Projet;
+---------+---------+----------+--------+--------+
| NumProj | NomProj | RespProj | VilleP | Budget |
+---------+---------+----------+--------+--------+
|     123 | ADOOP   |    22334 | Paris  | 120000 |
|     757 | SKALA   |    45566 | Lyon   | 180000 |
|     890 | BAJA    |    22334 | Paris  |  24000 |
|     999 | TEST    |     NULL | NULL   |  50000 |
+---------+---------+----------+--------+--------+
4 rows in set (0,001 sec)

MariaDB [BDD_s2_tp]> CALL InsererNouvelProjet(998, 999999);
+----------------------------------------------------------------+
| Message                                                        |
+----------------------------------------------------------------+
| Erreur : le budget total dépasserait 400000. Projet non ajouté.|
+----------------------------------------------------------------+
1 row in set (0,002 sec)

MariaDB [BDD_s2_tp]> SELECT * FROM Projet;
+---------+---------+----------+--------+--------+
| NumProj | NomProj | RespProj | VilleP | Budget |
+---------+---------+----------+--------+--------+
|     123 | ADOOP   |    22334 | Paris  | 120000 |
|     757 | SKALA   |    45566 | Lyon   | 180000 |
|     890 | BAJA    |    22334 | Paris  |  24000 |
|     999 | TEST    |     NULL | NULL   |  50000 |
+---------+---------+----------+--------+--------+
4 rows in set (0,001 sec)


  1. (Trigger) Écrire un trigger de nom “VerifierBudgetUpdate” qui se déclenche avant toute modification du budget d’un projet dans la table Projet. Si le nouveau budget est inférieur au budget actuel, la modification est annulée et un message d’erreur est affiché. Dans le cas contraire la modification est acceptée.


Faire valider votre script en affichant dans un terminal : 

MariaDB [BDD_s2_tp]> SELECT NumProj, NomProj, Budget FROM Projet;
+---------+---------+--------+
| NumProj | NomProj | Budget |
+---------+---------+--------+
|     123 | ADOOP   | 120000 |
|     757 | SKALA   | 180000 |
|     890 | BAJA    |  24000 |
+---------+---------+--------+
3 rows in set (0,001 sec)

MariaDB [BDD_s2_tp]> UPDATE Projet SET Budget = 200000 WHERE NumProj = 123;
Query OK, 1 row affected (0,004 sec)

MariaDB [BDD_s2_tp]> SELECT NumProj, NomProj, Budget FROM Projet;
+---------+---------+--------+
| NumProj | NomProj | Budget |
+---------+---------+--------+
|     123 | ADOOP   | 200000 |
|     757 | SKALA   | 180000 |
|     890 | BAJA    |  24000 |
+---------+---------+--------+
3 rows in set (0,001 sec)

MariaDB [BDD_s2_tp]> UPDATE Projet SET Budget = 50000 WHERE NumProj = 123;
ERROR 1644 (45000): Erreur : le nouveau budget (50000) est inférieur au budget actuel (200000). Modification annulée.

MariaDB [BDD_s2_tp]> SELECT NumProj, NomProj, Budget FROM Projet;
+---------+---------+--------+
| NumProj | NomProj | Budget |
+---------+---------+--------+
|     123 | ADOOP   | 200000 |
|     757 | SKALA   | 180000 |
|     890 | BAJA    |  24000 |
+---------+---------+--------+
3 rows in set (0,001 sec)