Avec la base de données suivantes :

DROP TABLE IF EXISTS panier;
DROP TABLE IF EXISTS ligne;
DROP TABLE IF EXISTS commande;
DROP TABLE IF EXISTS article;
DROP TABLE IF EXISTS client;

CREATE TABLE IF NOT EXISTS article (
    id_article INT AUTO_INCREMENT
    , designation VARCHAR(255)
    , prix NUMERIC(19,4)
    , stock INT
    , PRIMARY KEY (id_article)
)CHARACTER SET 'utf8mb4' ;

CREATE TABLE IF NOT EXISTS client (
    id_client INT AUTO_INCREMENT
    , nom VARCHAR(25)
    , ville VARCHAR(25)
    , PRIMARY KEY (id_client)
)CHARACTER SET 'utf8mb4';

CREATE TABLE IF NOT EXISTS  commande (
    id_commande INT AUTO_INCREMENT
    , date_commande DATE
    , id_client INT
    , PRIMARY KEY (id_commande)
    , CONSTRAINT fk_commande_client_exo3 FOREIGN KEY (id_client) REFERENCES client(id_client)
)CHARACTER SET 'utf8mb4';

CREATE TABLE IF NOT EXISTS ligne (
    id_commande INT
    , id_article INT
    , quantite INT
    , PRIMARY KEY (id_commande,id_article)
    , CONSTRAINT fk_ligne_commande_exo3 FOREIGN KEY (id_commande) REFERENCES commande(id_commande)
    , CONSTRAINT fk_ligne_article_exo3 FOREIGN KEY (id_article) REFERENCES article(id_article)
);

CREATE TABLE IF NOT EXISTS panier (
    id_client INT
    , id_article INT
    , quantite INT
    , PRIMARY KEY (id_client,id_article)
    , CONSTRAINT fk_panier_client_exo3 FOREIGN KEY (id_client) REFERENCES client(id_client)
    , CONSTRAINT fk_panier_article_exo3 FOREIGN KEY (id_article) REFERENCES article(id_article)
);

INSERT INTO client VALUES (1,'Mutz','Ruelisheim'),(2,'Rato','Besancon'),(3,'Goerig','Walheim'),(4,'Walter','Bretten'),(5,'PAUTOT','Belfort'),(6,'PETIT','Belfort'),(7,'SAINT DIZIER','Sevenans'),(8,'SALVI','Offemont'),(9,'TERRAT','Belfort'),(10,'TYRODE','Valdoie'),(11,'ALANKAYA','Bavilliers'),(12,'DAROSEY','Essert'),(13,'duguet','Belfort'),(14,'ESSENBURGER','Belfort'),(15,'JAOUEN','Sevenans'),(16,'molin','Belfort'),(17,'AMGHAR','Belfort'),(18,'BOUCHAUD','Belfort'),(19,'COTTARD','Belfort'),(20,'LAMOTTE','Belfort'),(21,'METTEY','Belfort'),(22,'WOLF','Belfort'),(23,'BISMUTH','Belfort'),(24,'chaillet','Belfort'),(25,'DECOCK','Belfort');

INSERT INTO article VALUES
(1,'Gâteaux chocolat ( pâtisserie)',5.25,10),(2,'gâteau fraise (pâtisserie) ',5.25,10),(3,'Mouchoir ( hygiène)',2.54,10),
(4,'Coca cola ( boisson )',1.52,10),(5,'Salade ( légume ) ',2.00,10),(6,'Choux ( légume ) ',2.50,10);
(7,'Pomme de terre ( légume ) ',1.50,10),(8,'Tomate ( légume ) ',1.50,10),(9,'Haricot ( légume ) ',6.50,10),
(10,'Potiron ( légume ) ',3.00,10),(11,'Poireau ( légume ) ',1.50,10),(12,'Fenouil ( légume ) ',3.00,10),
(13,'Pissenlit ( légume ) ',5.00,10),(14,'Petit pois ( légume ) ',6.00,10),(15,'Poivron ( légume ) ',3.00,10),
(16,'Radis ( légume ) ',2.00,10),(17,'Pomme ( fruit ) ',3.00,10),(18,'Perrier ( 1L eau ) ',1.50,10),
(19,'Vittel ( 1L eau ) ',1.00,10);

INSERT INTO commande VALUES
(1,'2023-04-11',3),
(2,'2023-12-11',2),
(3,'2024-01-28',4),
(4,'2023-09-19',1),
(5,'2019-04-11',3),
(6,'2023-12-11',2),
(7,'2023-03-28',4),
(8,'2023-12-09',1);

INSERT INTO ligne VALUES
(1,4,12),(1,9,4),(1,12,4),(1,15,4),
(2,3,6),(2,6,6),(2,7,6),(2,14,6),
(3,1,3),(3,11,3),(3,12,3),(3,16,3),
(4,2,27),(4,8,5),(4,10,5),(4,17,5),
(5,12,4),
(6,11,3),
(7,8,5),
(8,14,6);

INSERT INTO panier(id_client, id_article, quantite) VALUES
(1,4,4),(1,7,4),
(2,4,4),(2,19,4),
(3,7,4),(3,17,4),
(4,4,4),(4,7,4),(4,17,4),(4,19,4),
(5,4,4),(5,7,4),(5,17,4),(5,19,4),
(6,4,4),(6,7,4),(6,17,4),(6,19,4)
;

exercice 1

Créer une procédure sur le SGBDR MySQL en PL/SQL qui reçoit en paramètre un identifiant de client, cette procédure permet de :

• Créer un enregistrement dans la table commande avec comme date la date d’aujourd’hui et comme identifiant de client le paramètre passé.
• Recopiez les enregistrements de la table “panier” ayant pour identifiant client l’identifiant client passé en paramètre dans la table ligne avec comme identifiant de commande celui de l’enregistrement créé précédemment.
  • Pour cette partie, il est demandé d’utiliser un curseur (qui sélectionne tous les enregistrements de la table “panier” ayant pour identifiant client l’identifiant client passé en paramètre) et une boucle qui crée un enregistrement pour chaque valeur de ce curseur. Dans votre boucle, utilisez un “WHILE”.
  • Testez une autre solution sans curseur https://www.w3schools.com/sql/sql_insert_into_select.asp

Pour tester cette procédure, utiliser la commande suivante :

SET @id_client=1;
SELECT * FROM panier WHERE id_client=@id_client;
-- +-----------+------------+----------+
-- | id_client | id_article | quantite |
-- +-----------+------------+----------+
-- |         1 |          4 |        4 |
-- |         1 |          7 |        4 |
-- +-----------+------------+----------+

SELECT * FROM commande WHERE id_client=@id_client;

-- +-------------+---------------+-----------+
-- | id_commande | date_commande | id_client |
-- +-------------+---------------+-----------+
-- |           4 | 2023-09-19    |         1 |
-- |           8 | 2023-12-09    |         1 |
-- +-------------+---------------+-----------+

CALL creer_commande(@id_client);       -- commande 1
SELECT * FROM panier WHERE id_client=@id_client;

-- +-------------+---------------+-----------+
-- Empty set
-- +-------------+---------------+-----------+

SELECT * FROM commande WHERE id_client=@id_client;

-- +-------------+---------------+-----------+
-- | id_commande | date_commande | id_client |
-- +-------------+---------------+-----------+
-- |           4 | 2023-09-19    |         1 |
-- |           8 | 2023-12-09    |         1 |
-- |           9 | 2024-05-23    |         1 |
-- +-------------+---------------+-----------+

SELECT MAX(id_commande) INTO @id_commande
  FROM commande;
SELECT * FROM ligne WHERE id_commande=@id_commande;

-- +-------------+------------+----------+
-- | id_commande | id_article | quantite |
-- +-------------+------------+----------+
-- |           9 |          4 |        4 |
-- |           9 |          7 |        4 |
-- +-------------+------------+----------+

CALL creer_commande(@id_client);    -- commande 2  : pas de commande vide

-- +-------------+---------------+-----------+
-- ERROR 1644 (45000): Le panier est vide.
-- +-------------+---------------+-----------+

SELECT
    c.id_commande, c.date_commande, cl.nom AS client_nom, cl.ville AS client_ville,
    a.id_article, a.designation, l.quantite, a.prix, a.stock
FROM
commande c JOIN    client cl ON c.id_client = cl.id_client
LEFT JOIN     ligne l ON c.id_commande = l.id_commande
LEFT JOIN     article a ON l.id_article = a.id_article
WHERE     c.id_client = @id_client
ORDER BY     c.date_commande, c.id_commande, l.id_article;

-- +-------------+---------------+------------+--------------+------------+-------------------------------+----------+--------+-------+
-- | id_commande | date_commande | client_nom | client_ville | id_article | designation                   | quantite | prix   | stock |
-- +-------------+---------------+------------+--------------+------------+-------------------------------+----------+--------+-------+
-- |           4 | 2023-09-19    | Mutz       | Ruelisheim   |          2 | gâteau fraise (pâtisserie)    |       27 | 5.2500 |    10 |
-- |           4 | 2023-09-19    | Mutz       | Ruelisheim   |          8 | Tomate ( légume )             |        5 | 1.5000 |    10 |
-- |           4 | 2023-09-19    | Mutz       | Ruelisheim   |         10 | Potiron ( légume )            |        5 | 3.0000 |    10 |
-- |           4 | 2023-09-19    | Mutz       | Ruelisheim   |         17 | Pomme ( fruit )               |        5 | 3.0000 |    10 |
-- |           8 | 2023-12-09    | Mutz       | Ruelisheim   |         14 | Petit pois ( légume )         |        6 | 6.0000 |    10 |
-- |           9 | 2024-05-23    | Mutz       | Ruelisheim   |          4 | Coca cola ( boisson )         |        4 | 1.5200 |     6 |
-- |           9 | 2024-05-23    | Mutz       | Ruelisheim   |          7 | Pomme de terre ( légume )     |        4 | 1.5000 |     6 |
-- +-------------+---------------+------------+--------------+------------+-------------------------------+----------+--------+-------+

INSERT INTO panier(id_client, id_article, quantite) VALUES
(@id_client,7,14),(@id_client,17,4);
CALL creer_commande(@id_client);  -- commande 3 : gestion du stock

-- +-------------+---------------+-----------+
-- ERROR 1644 (45000): Stock insuffisant pour l'article avec id 7 avec comme stock 6
-- +-------------+---------------+-----------+

DELETE FROM panier WHERE id_client=@id_client;
INSERT INTO panier(id_client, id_article, quantite) VALUES
(@id_client,7,2),(@id_client,17,3);

CALL creer_commande(@id_client);  -- commande 3 : gestion du stock

SELECT
    c.id_commande, c.date_commande, cl.nom AS client_nom, cl.ville AS client_ville,
    a.id_article, a.designation, l.quantite, a.prix, a.stock
FROM
commande c JOIN    client cl ON c.id_client = cl.id_client
LEFT JOIN     ligne l ON c.id_commande = l.id_commande
LEFT JOIN     article a ON l.id_article = a.id_article
WHERE     c.id_client = @id_client
ORDER BY     c.date_commande, c.id_commande, l.id_article;

-- +-------------+---------------+------------+--------------+------------+-------------------------------+----------+--------+-------+
-- | id_commande | date_commande | client_nom | client_ville | id_article | designation                   | quantite | prix   | stock |
-- +-------------+---------------+------------+--------------+------------+-------------------------------+----------+--------+-------+
-- |           4 | 2023-09-19    | Mutz       | Ruelisheim   |          2 | gâteau fraise (pâtisserie)    |       27 | 5.2500 |    10 |
-- |           4 | 2023-09-19    | Mutz       | Ruelisheim   |          8 | Tomate ( légume )             |        5 | 1.5000 |    10 |
-- |           4 | 2023-09-19    | Mutz       | Ruelisheim   |         10 | Potiron ( légume )            |        5 | 3.0000 |    10 |
-- |           4 | 2023-09-19    | Mutz       | Ruelisheim   |         17 | Pomme ( fruit )               |        5 | 3.0000 |     7 |
-- |           8 | 2023-12-09    | Mutz       | Ruelisheim   |         14 | Petit pois ( légume )         |        6 | 6.0000 |    10 |
-- |           9 | 2024-05-23    | Mutz       | Ruelisheim   |          4 | Coca cola ( boisson )         |        4 | 1.5200 |     6 |
-- |           9 | 2024-05-23    | Mutz       | Ruelisheim   |          7 | Pomme de terre ( légume )     |        4 | 1.5000 |     4 |
-- |          10 | 2024-05-23    | Mutz       | Ruelisheim   |          7 | Pomme de terre ( légume )     |        2 | 1.5000 |     4 |
-- |          10 | 2024-05-23    | Mutz       | Ruelisheim   |         17 | Pomme ( fruit )               |        3 | 3.0000 |     7 |
-- +-------------+---------------+------------+--------------+------------+-------------------------------+----------+--------+-------+

exercice 2

En testant, le SQL ci-dessus, on peut voir que la procédure PL/SQL autorise des commandes sans articles et que le stock n’est pas géré ; modifier votre script pour éviter ce problème.

Dans votre script, il faut prendre en compte est les opérations suivantes :

• Ajouter une transaction.
• Vérifiez si le curseur sur la table “panier” retourne des lignes et générez une erreur si ce n’est pas le cas.
• Si le stock de produit n’est pas suffisant,
  • faire une levée d’exception et retourner un message d’erreur. Toutes les opérations sont annulées (rollback).
• Sinon
  • décrémentez le stock de produit.

exercice 3

• Faire un script python qui teste cette procédure PL/SQL. Le script python affiche que tout s’est bien passé ou l’erreur et son code.

Il suffit de modifier la connexion (les paramètres), l’appel de la procédure et supprimer 3 lignes qui ne servent à rien.

#! /usr/bin/python
# -*- coding:utf-8 -*-

import pymysql
import sys

# Vérifier que le script a reçu le bon nombre d'arguments
if len(sys.argv) != 2:
    print("Usage: python script.py <num_client>")
    sys.exit(1)

# 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_client = int(sys.argv[1])
    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
    print(e)
    error_message = e.args[1]
    print(f"Erreur MySQL: {error_message}")

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

Exemple de résultat dans un terminal :