Module 140

Administrer et exploiter des bases de données

Jour 4 - PL/SQL

# Objectifs du jour

Objectifs du jour

  • Correction des exercices de la série 3

  • Plan d'exécution d'une requête

  • Introduction au PL/SQL

    • Variables, boucle, etc

    • Débogueur

    • Procédure et fonctions

    • Curseurs

    • Exceptions

  • Exercices PL/SQL

# Corrections des exercices

Correction des exercices

  • Correction des exercices de la série 3

    • Tablespaces - 3.1

    • Vue - 3.2

    • Vue matérialisée - 3.3

  • Corrigés sur le Gitbook :

# Plan d'exécution d'une requête

Plan d'exécution 
d'une requête

Le plan d'exécution d'une requête décrit comment Oracle prévoit d'accéder aux données pour satisfaire une requête SQL particulière. Il contient les opérations que la base de données entreprend pour récupérer les résultats.

Utilité du Plan d'Exécution :

- Compréhension du flux d'exécution

- Optimisation des Performances

- Diagnostique des problèmes de
performances

- Aide pour déterminer les index

 

# Plan d'exécution d'une requête

Exemple sur le schéma magasin

EXPLAIN PLAN FOR
SELECT c.nom_cli, c.pre_cli, l.nom_loc, l.npa_loc, COUNT(co.pk_com) AS total_commandes
FROM clients c
JOIN localites l ON c.fk_loc_cli = l.pk_loc
JOIN commandes co ON co.fk_cli_com = c.pk_cli
WHERE l.nom_loc = 'Lausanne'
GROUP BY c.nom_cli, c.pre_cli, l.nom_loc, l.npa_loc
ORDER BY total_commandes DESC;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY());

Nous voyons ici l'utilisation des index mais également que toute la table des commandes est parcourue dans la boucle (ligne bleue) !

# Plan d'exécution d'une requête

Exemple sur le schéma magasin

CREATE INDEX ind_fk_com_cli ON commandes(fk_cli_com);

Nous pouvons donc ajouter un index sur la clé étrangère (fk) :

Nous voyons cette fois que c'est l'index qui est utilisé et que la table n'est plus parcourue entièrement :

Introduction PL/SQL

PL/SQL (Procedural Language/Structured Query Language) est une extension du SQL propre à Oracle. Il permet d'écrire des programmes complets en combinant des instructions SQL avec des structures de programmation procédurales, comme les boucles et les conditions.

# Introduction PL/SQL

Avantages

  • Automatiser et optimiser les processus au sein des bases de données.
  • Permet d'implémenter les règles de gestion de l'entreprise qui ne peuvent pas être implémentées par les contraintes traditionnelles.
  • Centralisation des règles métiers garantissant leur application uniforme à travers toutes les applications
  • Offre des mécanismes avancés pour capturer et gérer les exceptions, assurant la stabilité des applications.
# Introduction PL/SQL

Exemples

  • Un produit ne peut pas être commandé si son stock disponible est insuffisant.
  • Les dates de début et de fin d'une réservation doivent respecter les disponibilités des chambres.
  • ...

Introduction PL/SQL

# Introduction PL/SQL
-- Permet d'afficher les sorties de la console
SET SERVEROUTPUT ON;

-- Bloc PL/SQL pour afficher Hello World
BEGIN
   DBMS_OUTPUT.PUT_LINE('Hello World !');
END;
/

Afficher du texte :

Avec une variable :

-- Bloc PL/SQL pour afficher Hello World
DECLARE
  ma_variable VARCHAR2(25);
BEGIN
  ma_variable := 'Hello World !';
  DBMS_OUTPUT.PUT_LINE(ma_variable);
END;
/

Voir le document Introduction à PL/SQL dans les ressources du jour pour plus d'informations

# Introduction PL/SQL

Boucle :

-- Bloc FOR
BEGIN
  FOR i IN 1..5 LOOP
    DBMS_OUTPUT.PUT_LINE('Compteur : ' || i);
  END LOOP;
END;
/

Exercice 4.1 - Suite Fibonacci :

Introduction PL/SQL

Procédures et fonctions

# Introduction PL/SQL

Procédure :

-- Procédure disant bonjour à une personne
CREATE OR REPLACE PROCEDURE Dire_Bonjour(
  p_nom IN VARCHAR2 -- Paramètre d'entrée
) 
IS
  texte_bonjour VARCHAR2(100) := 'Bonjour, ';
BEGIN
  DBMS_OUTPUT.PUT_LINE(texte_bonjour || p_nom || ' !');
END;
/

Appel de la procédure :

BEGIN
  Dire_Bonjour('Normand');
END;
/

Procédures et fonctions (paramètres)

# Introduction PL/SQL

Les Paramètres en PL/SQL : IN, OUT, IN OUT

Type Mode Description
IN Lecture uniquement Passe une valeur à la procédure ou fonction. Ne peut pas être modifié.
OUT Écriture uniquement Retourne une valeur. Pas initialisé à l'entrée.
IN OUT Lecture et écriture Passe une valeur initiale, la procédure peut la modifier et renvoyer le résultat. 
# Introduction PL/SQL

Exemple IN :

PROCEDURE Afficher_Nom(p_nom IN VARCHAR2);
BEGIN
  DBMS_OUTPUT.PUT_LINE('Nom fourni : ' || p_nom);
END;
/

Exemple OUT :

PROCEDURE Donner_Age(p_age OUT NUMBER);
BEGIN
  p_age := 30; -- Définit une valeur pour le paramètre OUT
END;
/

Exemple IN OUT :

PROCEDURE Ajuster_Salaire(p_salaire IN OUT NUMBER);
BEGIN
  p_salaire := p_salaire * 1.1; -- Augmente le salaire de 10%
END;
/

Procédures et fonctions (paramètres)

# Introduction PL/SQL

Attribuer les droits nécessaires depuis System :

GRANT DEBUG CONNECT SESSION TO magasin;
GRANT DEBUG ANY PROCEDURE TO magasin;

Déboguer en PL/SQL

Ouvrir la procédure à déboguer :

1) Compiler pour le débogage

2) Placer des points d'arrêts aux endroits souhaités

3) Lancer le débogage avec l'icône de la coccinelle

# Introduction PL/SQL

Exercice 4.2

 

Adaptation de la suite fibonacci :

Introduction PL/SQL

Procédures et fonctions

# Introduction PL/SQL

Fonction :

-- Fonction qui retourne la longeur d'un texte
CREATE OR REPLACE FUNCTION Longueur_Texte(
  p_texte IN VARCHAR2 -- Paramètre d'entrée : le texte dont on veut la longueur
) RETURN NUMBER -- La fonction retourne un nombre
IS
  longueur NUMBER; -- Variable pour stocker la longueur
BEGIN
  longueur := LENGTH(p_texte); -- Calcul de la longueur
  RETURN longueur; -- Retourner le résultat
END;
/

Appel de la fonction :

BEGIN
  DBMS_OUTPUT.PUT_LINE('Longueur : ' || Longueur_Texte('Bonjour PL/SQL'));
END;
/
# Introduction PL/SQL

Exercice 4.3

 

Adaptation de la suite fibonacci :

Introduction PL/SQL

# Travail pour la prochaine fois

Travail pour la prochaine fois

  • Parcours du document introduction PL/SQL

  • Terminer les exercices PL/SQL (jusqu'au 4.3)

Merci de votre attention 😊
Bonne fin de journée !