Les Procédure

Contrairement aux fonctions stockées, les procédures stockées n'ont pas de valeur de retour explicite.
Elles sont principalement utilisées pour effectuer des tâches, des opérations de mise à jour, des requêtes complexes ou des séquences d'instructions SQL.

une procédure permet de :

Calculer et afficher des résultats
Calculer et manipuler les tables:
insert :insérer une ligne
delete :supprimer une ligne
update :Modifier une ligne
drop :Supprimer une table , une fonction...
....

syntaxe:

delimiter //
 Create procedure nomProcedure (paramter 1 type1,parameter 2 type 2,....)
 begin
    les instructions :
	   select , if , else ,for ...
 end;

Remarque

pour appeler une procédure :
call nomProcedure(paramèters)

Exemples

Exemple1

Créer une procédure qui peremet d'afficher la lites des produits d'une marque passée en paramètre

delimiter //
create procedure if not exists afficherProduitMarque(marqueProduit varchar(25))
begin
select * from produit where marque=marqueProduit;
end;

appeler la précédure :

call afficherProduitMarque('marque1');

Les paramètres : IN ,OUT

Les paramètres IN et OUT dans une procédure stockée en MySQL sont utilisés pour définir les paramètres d'entrée et de sortie de la procédure.

Exemple 1

Crée une nouvelle procédure stockée nommée "calculer_somme_procedure"

DELIMITER //
CREATE PROCEDURE calculer_somme_procedure(
    IN a INT,        -- Paramètre d'entrée IN : Première valeur
    IN b INT,        -- Paramètre d'entrée IN : Deuxième valeur
    OUT resultat INT -- Paramètre de sortie OUT : Résultat de la somme
)
BEGIN
    -- Calcule la somme des deux valeurs d'entrée
    SET resultat = a + b;
END//

Exemple d'appel de la procédure

-- Déclare une variable pour stocker le résultat
SET @sum_result = 0;
-- Appelle la procédure en fournissant les paramètres d'entrée et de sortie
CALL calculer_somme_procedure(5, 3, @sum_result);

-- Affiche le résultat de la somme
SELECT @sum_result;

Exemple 2

-- Crée une nouvelle procédure stockée nommée "calculer_cout_total_procedure"
DELIMITER //
CREATE PROCEDURE calculer_cout_total_procedure(
    IN produit_id INT, -- Paramètre d'entrée : ID du produit
    IN quantite INT,   -- Paramètre d'entrée : quantité achetée
    OUT cout DECIMAL(10, 2) -- Paramètre de sortie : coût total
)
BEGIN
    -- Déclare une variable locale "prix_produit" pour stocker le prix du produit
    DECLARE prix_produit DECIMAL(10, 2);

    -- Obtient le prix du produit en fonction de l'ID du produit
    SELECT prix INTO prix_produit FROM produits WHERE id = produit_id;

    -- Calcule le coût total
    SET cout = prix_produit * quantite;
END//

Exemple d'appel de la procédure

-- Déclare une variable pour stocker le coût total
SET @total_cost = 0;
-- Appelle la procédure en fournissant les paramètres d'entrée et de sortie
CALL calculer_cout_total_procedure(1, 5, @total_cost);
-- Affiche le coût total calculé
SELECT @total_cost;

Exemple3

Q Créez une procédure stockée nommée "nombreProduit" qui prend en entrée la marque d'un produit et renvoie le nombre de produits de cette marque.
```
CREATE PROCEDURE nombreProduit(marqueProduit VARCHAR(25))
BEGIN
    SELECT COUNT(*) FROM produit WHERE marque = marqueProduit;
END;
```
CALL nombreProduit('NomMarque');

Q Comment pouvez-vous créer une procédure stockée nommée "ajouterProduit" qui insère un nouveau produit dans la table "produit" avec des valeurs spécifiées?

CREATE PROCEDURE ajouterProduit(nomProduit VARCHAR(25), qteProduit FLOAT, prixProduit FLOAT, marqueProduit VARCHAR(25))
BEGIN
    INSERT INTO produit (nom, qte, prix, marque) VALUES (nomProduit, qteProduit, prixProduit, marqueProduit);
END;

CALL ajouterProduit('Ordinateur', 10, 999, 'HP');

Q Créez une procédure stockée nommée "majPrixMarque" qui met à jour les prix de tous les produits d'une marque donnée.
```
CREATE PROCEDURE majPrixMarque(marqueProduit VARCHAR(25), nouveauPrix FLOAT)
BEGIN
    UPDATE produit SET prix = nouveauPrix WHERE marque = marqueProduit;
END;
```
CALL majPrixMarque('NomMarque', 149.99);
Q Comment pouvez-vous créer une procédure stockée nommée "supprimerProduitParID" qui supprime un produit en fonction de son ID?
```
CREATE PROCEDURE supprimerProduitParID(idProduit INT)
BEGIN
    DELETE FROM produit WHERE id = idProduit;
END;
```
CALL supprimerProduitParID(5);
Q Pouvez-vous créer une procédure stockée nommée "incrementerQuantite" qui augmente la quantité en stock d'un produit donné?
```
CREATE PROCEDURE incrementerQuantite(idProduit INT, increment INT)
BEGIN
    UPDATE produit SET qte = qte + increment WHERE id = idProduit;
END;
```
CALL incrementerQuantite(3, 5);
Q Créez une procédure stockée nommée "diminuerQuantitePrixEleve" qui diminue la quantité en stock de tous les produits dont le prix est supérieur à 100.
```
CREATE PROCEDURE diminuerQuantitePrixEleve()
BEGIN
    UPDATE produit SET qte = qte - 1 WHERE prix > 100;
END;
```
CALL diminuerQuantitePrixEleve();
Q Comment pouvez-vous créer une procédure stockée nommée "augmenterPrixSaufMoinsCher" qui augmente le prix de tous les produits de 5 %, sauf ceux dont le prix est inférieur à 50?
```
CREATE PROCEDURE augmenterPrixSaufMoinsCher()
BEGIN
    UPDATE produit SET prix = prix * 1.05 WHERE prix >= 50;
END;
```
CALL augmenterPrixSaufMoinsCher();
Q Pouvez-vous créer une procédure stockée nommée "supprimerProduitsSansStock" qui supprime tous les produits dont la quantité en stock est nulle?
```
CREATE PROCEDURE supprimerProduitsSansStock()
BEGIN
    DELETE FROM produit WHERE qte = 0;
END;
```
CALL supprimerProduitsSansStock();
Q Créez une procédure stockée nommée "diminuerQuantitePrixEleve" qui diminue la quantité en stock de tous les produits dont le prix est supérieur à 100.
```
CREATE PROCEDURE diminuerQuantitePrixEleve()
BEGIN
    UPDATE produit SET qte = qte - 1 WHERE prix > 100;
END;
```
CALL diminuerQuantitePrixEleve();
Q Comment pouvez-vous créer une procédure stockée nommée "augmenterPrixSaufMoinsCher" qui augmente le prix de tous les produits de 5 %, sauf ceux dont le prix est inférieur à 50?
```
CREATE PROCEDURE augmenterPrixSaufMoinsCher()
BEGIN
    UPDATE produit SET prix = prix * 1.05 WHERE prix >= 50;
END;
```

Exemple2

Afin de réaliser plusieurs examples Veuillez ajouter les tables suivantes en plus la table produit déjà crée:

Client(id,nom,prenom,ville)
vente(#idclient,#idProduit,dateVente,prixVente,quantiteVendue)

1 Créer la procédure supprimerProduit(idProduit) permet de supprimer le produit passé en paramètre
2 Créer la procédure totaleVente(idProduit) permet d'afficher le totale des vente (QuantiteVendue* prixVente) pour le produit passé en paramètre
3 Créer la procédure ProduitEnManqueStock() permet d'afficher la listes des produits qui stock=0
4 Créer la procédure ListeClientParMarque(marqueProduit) permet d'afficher la liste des clients qui achètent la marque passée en paramètre

Exemple3

soit le schéma de la base de données relationnelle suivante

joueur(id,nom)
jeux(id,nom,categorie)
partieJouee(id,idJoueur,idJeux,nbreHeure)

Créer les procédures suivantes:

Q.1Créer une procédure qui permet d'obtenir tous les joueurs et leurs jeux associés :

CREATE PROCEDURE GetJoueursEtJeux()
AS
BEGIN
    SELECT j.nom AS JoueurNom, jj.nom AS JeuNom, jj.categorie AS JeuCategorie, pj.nbreHeure AS NombreHeuresJouées
    FROM joueur j
    JOIN partieJouee pj ON j.id = pj.idJoueur
    JOIN jeux jj ON pj.idJeux = jj.id;
END;

Q.2 Créer une procédure qui permet d'obtenir les joueurs ayant joué à un jeu spécifique ( passé en paramètre):

CREATE PROCEDURE GetJoueursPourJeu(@jeuID INT)
AS
BEGIN
    SELECT j.nom AS JoueurNom
    FROM joueur j
    JOIN partieJouee pj ON j.id = pj.idJoueur
    WHERE pj.idJeux = @jeuID;
END;

Q.3 Créer une procédure qui permet d'obtenir le nombre d'heures totales jouées par chaque joueur :

CREATE PROCEDURE GetHeuresJouéesParJoueur()
AS
BEGIN
    SELECT j.nom AS JoueurNom, SUM(pj.nbreHeure) AS TotalHeuresJouées
    FROM joueur j
    JOIN partieJouee pj ON j.id = pj.idJoueur
    GROUP BY j.nom;
END;

Q.4 Créer une procédure qui permet d'obtenir le nombre d'heures totales jouées par catégorie de jeu :

CREATE PROCEDURE GetHeuresJouéesParCatégorie()
AS
BEGIN
    SELECT jj.categorie AS JeuCategorie, SUM(pj.nbreHeure) AS TotalHeuresJouées
    FROM jeux jj
    JOIN partieJouee pj ON jj.id = pj.idJeux
    GROUP BY jj.categorie;
END;

Q.5 Créer une procédure qui permet d'obtenir les joueurs ayant joué plus de X heures( passé en paramètre) :

CREATE PROCEDURE GetJoueursAvecPlusHeures(@heures INT)
AS
BEGIN
    SELECT j.nom AS JoueurNom, SUM(pj.nbreHeure) AS TotalHeuresJouées
    FROM joueur j
    JOIN partieJouee pj ON j.id = pj.idJoueur
    GROUP BY j.nom
    HAVING SUM(pj.nbreHeure) > @heures;
END;

Q.6 Créer une procédure qui permet d'obtenir la liste des jeux et le nombre d'heures jouées pour chaque jeu :

CREATE PROCEDURE GetJeuxEtHeuresJouées()
AS
BEGIN
    SELECT jj.nom AS JeuNom, jj.categorie AS JeuCategorie, SUM(pj.nbreHeure) AS TotalHeuresJouées
    FROM jeux jj
    JOIN partieJouee pj ON jj.id = pj.idJeux
    GROUP BY jj.nom, jj.categorie;
END;

Q.7 Créer une procédure qui permet d'obtenir le joueur ayant joué le plus d'heures :

CREATE PROCEDURE GetJoueurAvecPlusHeures()
AS
BEGIN
    SELECT TOP 1 j.nom AS JoueurNom, SUM(pj.nbreHeure) AS TotalHeuresJouées
    FROM joueur j
    JOIN partieJouee pj ON j.id = pj.idJoueur
    GROUP BY j.nom
    ORDER BY SUM(pj.nbreHeure) DESC;
END;

Q.8 Créer une procédure qui permet d'obtenir le joueur ayant joué le moins d'heures :

CREATE PROCEDURE GetJoueurAvecMoinsHeures()
AS
BEGIN
    SELECT TOP 1 j.nom AS JoueurNom, SUM(pj.nbreHeure) AS TotalHeuresJouées
    FROM joueur j
    JOIN partieJouee pj ON j.id = pj.idJoueur
    GROUP BY j.nom
    ORDER BY SUM(pj.nbreHeure) ASC;
END;

Q.9 Créer une procédure qui permet d'obtenir les jeux auxquels un joueur dont son nom est passé en paramètre a joué :

CREATE PROCEDURE GetJeuxPourJoueur(@joueurID INT)
AS
BEGIN
    SELECT jj.nom AS JeuNom, jj.categorie AS JeuCategorie, pj.nbreHeure AS HeuresJouées
    FROM partieJouee pj
    JOIN jeux jj ON pj.idJeux = jj.id
    WHERE pj.idJoueur = @joueurID;
END;

Q.10 Créer une procédure qui permet d'obtenir le nombre d'heures jouées pour chaque joueur dans un jeu spécifique(nom passé en paramètre) :

CREATE PROCEDURE GetHeuresJouéesPourJoueurEtJeu(@joueurID INT, @jeuID INT)
AS
BEGIN
    SELECT j.nom AS JoueurNom, jj.nom AS JeuNom, SUM(pj.nbreHeure) AS TotalHeuresJouées
    FROM joueur j
    JOIN partieJouee pj ON j.id = pj.idJoueur
    JOIN jeux jj ON pj.idJeux = jj.id
    WHERE j.id = @joueurID AND jj.id = @jeuID
    GROUP BY j.nom, jj.nom;
END;

Fonctions et procédures

Les triggers

la Sécurité de données

Exercices

Mysql

SELECT

DataTypes

UPDATE

DELETE

CREATE

DROP

RENAME

ALTER

Tables

Les Procédure

Remarque

Exemples