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, #iRespProj,, VilleP, Budget)
EMBAUCHE(#NumSS,#NumProj, DateEmb, Profil)
GRILLE_SAL(Profil, Salaire)

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

1. Dessiner le MCD sur Looping de ce MLD

• Rappels : Pour exécuter une procédure dans PL-SQL sur MySQL ou MariaDB, utilisez la commande: CALL nom_procedure(liste_paramètres)

  • Contrairement à ORACLE, il n’existe pas d’équivalent de la commande SHOW err

CONSTITUTION DE LA BASE DE DONNÉES

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');

4. Utiliser une IA générative (réaliser un prompt) pour modifier le script ci-dessus :

• ajouter les clés primaires
• ajouter les clés étrangères
• modifier le type des colonnes pour qu’il soit compatible sur MySQL
• ajouter la fonction STR_TO_DATE(‘1-01-2012’, ‘%d-%m-%Y’), utiliser rechercher/remplacer (CTRL R ou CTRL H)

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');

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

1. Exo 1 . Écrivez un bloc PL/SQL anonyme 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..

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 a disparu

2. Exo2. Écrivez un bloc PL/SQL anonyme 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 dans votre script la supprime puis la re-crée .

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;

4. (Curseur avec des paramètres) Écrivez un bloc PL/SQL anonyme 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;

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 AfficherProjetsEmployes2();
+--------------------------------------+
| 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é 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)

5. (Utilisation CASE et Curseur)Écrire un bloc anonyme 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.

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]> 

6. (Exception prédéfinie) Écrire une procédure PL/SQL qui a 3 paramètres: le numSS, le nomE et prenomE d’un nouvel employé à insérer dans la table Employé. S’il existe déjà un autre employé avec le même numSS dans la table Employé elle doit afficher un message d’erreur sans insérer l’utilisateur. Dans le cas contraire la procédure doit insérer le nouvel employé et afficher un message de confirmation. Utilisez l’exception prédéfinie NO_DATA_FOUND.

SELECT profil, salaire  FROM Grille_sal;
CALL AugmenterSalaires();
SELECT profil, salaire  FROM Grille_sal;

7. (Exception utilisateur) Écrire une procédure qui prend comme paramètre le numéro et le budget d’un projet et qui l’ajoute à la table projet seulement si la somme 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.

CALL InsererNouvelEmploye(1234567, 'Alain', 'Paul');
SELECT *  FROM Employe;
CALL InsererNouvelEmploye(22334, 'Adam', 'Funk');
SELECT *  FROM Employe;