La gestion efficace des bases de données est essentielle pour les entreprises modernes.
Sans une structure adéquate, les opérations peuvent devenir inefficaces et coûteuses, entraînant des erreurs de données.
Cet article explore comment les programmes stockés en PL/SQL peuvent optimiser vos processus de base de données, assurant ainsi une exécution fluide et fiable.
Maîtriser le langage SQL en environnement SGBDR. SQL n'aura plus de secrets pour vous !
L’objectif de ce document est de renforcer la compréhension des programmes stockés dans PL/SQL, y compris les procédures et fonctions stockées avec ou sans arguments, les fonctions retournant des valeurs scalaires ou des tables, ainsi que la gestion des arguments avec différents modes d’affectation (IN, OUT, IN OUT, NOCOPY). En maîtrisant ces concepts, les utilisateurs pourront créer et manipuler des programmes stockés pour optimiser les opérations de base de données et assurer la modularité du code PL/SQL.
Les procédures stockées
Avec et sans argument : Créer des procédures qui acceptent des paramètres pour modifier ou récupérer des données.
Les fonctions en PL/SQL
Fonctions avec et sans arguments, renvoyant des valeurs scalaires ou des tables.
Les modes d’affectation des arguments
- IN :Pour passer des valeurs en lecture seule.
- OUT :Pour retourner des valeurs en sortie.
- IN OUT :Pour lire et écrire sur le même paramètre.
- NOCOPY :Pour optimiser la performance en évitant la copie des valeurs en mémoire.
Exercices PL/SQL : Programmes stockés
Énoncé de l’exercice :
- Création de la procédure updatequantite :Créez une procédure stockée nommée updatequantite qui met à jour la quantité en stock d’un article. La référence de l’article et la quantité seront passés en paramètres (arguments) à cette procédure.
- Création de la fonction valeurstock :Créez une fonction nommée valeurstock qui retourne la valeur totale des articles en stock.
- Création de la fonction articleparcategorie :Créez une fonction non stockée nommée articleparcategorie qui retourne le nombre d’articles par catégorie pour toutes les catégories et affiche les résultats.
Solution 1: Procédure updatequantite pour Mettre à Jour la Quantité en Stock
Explication de la solution : La procédure updatequantite prend en entrée deux paramètres : la référence de l’article (ID ou code unique) et la quantité à mettre à jour. Cette procédure met à jour la table des articles en ajustant la quantité en stock de l’article correspondant à la référence fournie.
CREATE OR REPLACE PROCEDURE updatequantite (
p_reference_article IN VARCHAR2,
p_quantite IN NUMBER
) AS
BEGIN
UPDATE ARTICLES
SET QUANTITE_STOCK = p_quantite
WHERE REFERENCE_ART = p_reference_article;
DBMS_OUTPUT.PUT_LINE('Quantité mise à jour avec succès pour l\'article ' || p_reference_article);
END;
/
Résultat attendu :
Lors de l’exécution de la procédure updatequantite, si l’article existe, sa quantité en stock est mise à jour avec succès, et un message « Quantité mise à jour avec succès pour l’article X » s’affiche.
Si l’article n’existe pas, aucun changement n’est apporté, mais le message ne sera affiché qu’en cas de succès.
Solution 2: Fonction valeurstock pour calculer la valeur totale du stock
Explication de la solution : La fonction valeurstock calcule la valeur totale des articles en stock en multipliant la quantité par le prix unitaire de chaque article. Elle retourne ensuite cette valeur.
CREATE OR REPLACE FUNCTION valeurstock RETURN NUMBER AS
v_valeur_total NUMBER;
BEGIN
SELECT SUM(QUANTITE_STOCK * PRIX_HT) INTO v_valeur_total
FROM ARTICLES;
RETURN v_valeur_total;
END;
/
Résultat attendu :
En appelant la fonction valeurstock, elle retourne la valeur totale des articles en stock.
Par exemple, si la valeur totale est calculée à 10000, cette valeur sera renvoyée, et elle peut être utilisée dans des requêtes ou affichée dans l’application.
Solution 3: Fonction articleparcategorie pour compter les articles par catégorie
Explication de la solution : La fonction articleparcategorie n’est pas stockée dans la base de données et est exécutée pour afficher le nombre d’articles pour chaque catégorie. Cette fonction retourne les résultats sous forme de texte affiché dans la console.
DECLARE
CURSOR c_categories IS
SELECT C.LIBELLE_CAT, COUNT(A.REFERENCE_ART) AS nb_articles
FROM CATEGORIES C
LEFT JOIN ARTICLES A ON C.CODE_CAT = A.CODE_CAT
GROUP BY C.LIBELLE_CAT;
v_libelle_cat CATEGORIES.LIBELLE_CAT%TYPE;
v_nb_articles NUMBER;
BEGIN
OPEN c_categories;
LOOP
FETCH c_categories INTO v_libelle_cat, v_nb_articles;
EXIT WHEN c_categories%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('Catégorie: ' || v_libelle_cat || ' - Nombre d\'articles: ' || v_nb_articles);
END LOOP;
CLOSE c_categories;
END;
/
Résultat attendu :
La fonction articleparcategorie affiche pour chaque catégorie le nombre total d’articles présents.
Tests pratiques : Procédures PL/SQL
- Test pour la procédure updatequantite
Ce test va appeler la procédure updatequantite pour mettre à jour la quantité en stock d’un article spécifique. Après l’exécution, vous pouvez vérifier le résultat en consultant la base de données.
BEGIN
-- Appel de la procédure pour mettre à jour la quantité en stock de l'article "A001" avec une nouvelle quantité de 50
updatequantite(p_reference_article => 'A001', p_quantite => 50);
END;
/
Instructions de vérification :
Exécutez le test et observez le message Quantité mise à jour avec succès pour l’article A001 dans la console.
- Test pour la fonction valeurstock
Ce test va appeler la fonction valeurstock pour obtenir la valeur totale des articles en stock.
DECLARE
v_valeur_total NUMBER;
BEGIN
-- Appel de la fonction pour calculer la valeur totale des articles en stock
v_valeur_total := valeurstock;
DBMS_OUTPUT.PUT_LINE('Valeur totale du stock : ' || v_valeur_total);
END;
/
Instructions de vérification :
Exécutez le test et observez la sortie Valeur totale du stock : <valeur> qui correspond à la somme totale des valeurs en stock calculées par la fonction.
La valeur affichée doit être égale à la somme de (QUANTITE_STOCK * PRIX_HT) pour chaque article dans ARTICLES.
- Test pour la fonction articleparcategorie
Ce test va exécuter le bloc de code contenant la fonction articleparcategorie pour afficher le nombre d’articles par catégorie.
BEGIN
-- Exécution de la fonction non stockée pour afficher le nombre d'articles par catégorie
DECLARE
CURSOR c_categories IS
SELECT C.LIBELLE_CAT, COUNT(A.REFERENCE_ART) AS nb_articles
FROM CATEGORIES C
LEFT JOIN ARTICLES A ON C.CODE_CAT = A.CODE_CAT
GROUP BY C.LIBELLE_CAT;
v_libelle_cat CATEGORIES.LIBELLE_CAT%TYPE;
v_nb_articles NUMBER;
BEGIN
OPEN c_categories;
LOOP
FETCH c_categories INTO v_libelle_cat, v_nb_articles;
EXIT WHEN c_categories%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('Catégorie: ' || v_libelle_cat || ' - Nombre d\'articles: ' || v_nb_articles);
END LOOP;
CLOSE c_categories;
END;
END;
/
Instructions de vérification :
Exécutez le test pour afficher les catégories et le nombre d’articles pour chacune,vous devriez voir des lignes comme
Formez-vous gratuitement avec Alphorm !
Maîtrisez les compétences clés en IT grâce à nos formations gratuites et accélérez votre carrière dès aujourd'hui.
FAQ
Comment les procédures stockées fonctionnent-elles en PL/SQL ?
Quelles sont les différences entre les fonctions et les procédures en PL/SQL ?
Quels sont les modes d'affectation des arguments en PL/SQL ?
Comment créer et tester une procédure stockée en PL/SQL ?
Comment calculer la valeur totale des articles en stock avec PL/SQL ?
Conclusion
En comprenant les programmes stockés PL/SQL, vous pouvez transformer vos opérations de base de données. Quels autres aspects du PL/SQL aimeriez-vous explorer pour optimiser votre flux de travail ?
