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 trois 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.

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)
- Utilisez ChatGPT ou Gemini pour répondre aux questions du TD mais avec le SGBDR MySQL.
- 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, 'Ada','Tan Lee', 'Marseille',   '21-3-1994'  ),
(11123, 'Franck', 'Morel', 'Lille',   '10-01-1945'  ),
);


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

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)

💡 exemple de solution proposer par l’IA générative

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

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

Utiliser une IA générative (réaliser un prompt) pour modifier la procédure PL-SQL ci-dessus :

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   |
| ----------------------------------- |
| Employé: Florent, Girac             |
| Employé: Mayla, Aoun                |
| ----------------------------------- |
| Projet: BAJA dans la ville Paris    |
| ----------------------------------- |
| ----------------------------------- |
| Projet: SKALA dans la ville Lyon    |
| ----------------------------------- |
| Employé: Christine, Lara            |
+-------------------------------------+
12 rows in set (0,006 sec)

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

Exo 2 (Curseur Implicite). É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. Utilisez les attributs d’un curseur implicite.

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

L’instruction SELECT * FROM Employe; permet de voir que franck a disparu

Exo3 (Curseur). É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. Utilisez un curseur.

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)

(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 avec des paramètres.

voir question 1

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

(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;

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

utilisation des procédures dans un script python

Proposer une exemple de programme python qui utilise la dernière procédure


import pymysql

# Connexion à la base de données MySQL
conn = pymysql.connect(
    host="votre_hôte",
    user="votre_utilisateur",
    password="votre_mot_de_passe",
    database="votre_base_de_données",
    port=3306  # Remplacez par le port MySQL utilisé dans votre configuration
)

try:
    # Création d'un curseur
    cursor = conn.cursor()

    # Exécution de la procédure stockée
    num_ss = 123456789
    nom_e = "Doe"
    prenom_e = "John"
    cursor.callproc("NomDeVotreProcedureMySQL", (num_ss, nom_e, prenom_e))

    # Commit pour valider les modifications
    conn.commit()

    # Affichage du résultat ou de tout message renvoyé par la procédure
    result = cursor.fetchall()
    print(result)

except pymysql.Error as e:
    # Récupération du message d'erreur
    error_message = e.args[1]
    print(f"Erreur MySQL: {error_message}")

finally:
    # Fermeture du curseur et de la connexion
    cursor.close()
    conn.close()

afficher le resultat de la requête ainsi que le message d’erreur
essayer de modifier le message d’erreur

https://www.w3schools.com/python/python_try_except.asp