Module 140

Administrer et exploiter des bases de données

Jour 5 - Suite PL/SQL

# Objectifs du jour

Objectifs du jour

  • Modification du Mindmap

  • Suite de l'introduction au PL/SQL

  • Présentation des séquences oracle

  • Exercices PL/SQL

# Mise à jour du mindmap

Modification du mindmap

# Règlement concernant les jeux

Règlement concernant les jeux

  • Selon mémento EPT :

    • En tant qu'utilisateur, il convient de se rappeler qu'il est notamment interdit d'installer ou copier des fichiers musique, vidéo, film, jeux ou démos autres que ceux nécessaires au déroulement de la formation

  • Démarrer Lanschool en début de leçon

# Corrections des exercices

Correction de l'exercice 4.3

 

  • Correction de l'exercice 4.3 - Fibonacci (Création d'une fonction)

  • Corrigés sur le Gitbook :

Introduction PL/SQL (Suite)

  • SELECT INTO
  • Curseurs
  • Exceptions
# Introduction PL/SQL - Suite

SELECT ... INTO

# Introduction PL/SQL

Exemple de SELECT INTO dans HR :

DECLARE
  prenom VARCHAR2(100);
  nom VARCHAR2(100);
BEGIN
  -- Sélectionner le prénom et le nom de l'employé avec EMPLOYEE_ID = 100
  SELECT FIRST_NAME, LAST_NAME
  INTO prenom, nom
  FROM HR.EMPLOYEES
  WHERE EMPLOYEE_ID = 100;

  -- Afficher les résultats
  DBMS_OUTPUT.PUT_LINE('Employé : ' || prenom || ' ' || nom);
END;
/

Exemple de %TYPE :

DECLARE
  prenom HR.EMPLOYEES.FIRST_NAME%TYPE,
  nom HR.EMPLOYEES.LAST_NAME%TYPE
BEGIN
   ...
END;
/
# Introduction PL/SQL

Exercice 4.4

Fonction de calcul des salaires

d'un département :

Introduction PL/SQL

Curseurs

# Introduction PL/SQL
  • Pour récupérer (et exploiter) les résultats de requêtes
DECLARE
  CURSOR curseur_employes IS
    SELECT first_name, last_name FROM hr.employees WHERE department_id = 10;

1. Déclarer le curseur avec la requête

2. Ouvrir le curseur lorsqu'on souhaite l'utiliser

OPEN curseur_employes;

3. Récupérer les données de l'enregistrement en cours et passer à la ligne suivante

FETCH curseur_employes INTO prenom, nom;

4. Pour finir, il faut fermer le curseur quand il n'est plus utilisé

CLOSE curseur_employes;

Curseurs

# Introduction PL/SQL

Un certain nombre d’informations sur l'état d’un curseur sont exploitables à l’aide d’attributs prédéfinis :

  • %FOUND : Renvoi vrai si le dernier FETCH a trouvé un enregistrement
  • %NOTFOUND : Renvoi vrai si le dernier FETCH n'a pas trouvé d'enregistrement
    • Beaucoup utilisé pour quitter les boucles à la fin d'un curseur : EXIT WHEN curseur_employes%NOTFOUND;
  • %ROWCOUNT : Renvoi le nombre de lignes trouvés par le curseur au moment de l'interrogation
  • %ISOPEN : Permet de déterminer si le curseur est ouvert
EXIT WHEN curseur_employes%NOTFOUND; -- Sortir si fin du curseur

Exemple d'utilisation de l'état d'un curseur :

%ROWTYPE

# Introduction PL/SQL

%ROWTYPE : Comme %TYPE, mais il permet de prendre le type utilisé pour une ligne entière d'une table donnée.

ligne_employe hr.employees%ROWTYPE; -- Variable de type ligne

Exemple de déclaration :

Et d'utilisation :

DBMS_OUTPUT.PUT_LINE('Nom : ' || ligne_employe.first_name || ' ' || ligne_employe.last_name);

Curseur - Exemple complet

# Introduction PL/SQL
DECLARE
  CURSOR curseur_employes IS -- Curseur contenant la requête
    SELECT employee_id, first_name, last_name, salary
    FROM hr.employees
    WHERE department_id = 50;

  ligne_employe hr.employees%ROWTYPE; -- Variable de type ligne
BEGIN
  OPEN curseur_employes; -- Ouvrir le curseur
  LOOP
    FETCH curseur_employes INTO ligne_employe; -- Affectation dans la variable %ROWTYPE
    EXIT WHEN curseur_employes%NOTFOUND; -- Sortir si fin du curseur

    -- Afficher les données récupérées
    DBMS_OUTPUT.PUT_LINE('Employé ID : ' || ligne_employe.employee_id);
    DBMS_OUTPUT.PUT_LINE('Nom : ' || ligne_employe.first_name || ' ' || ligne_employe.last_name);
    DBMS_OUTPUT.PUT_LINE('Salaire : ' || ligne_employe.salary);
    DBMS_OUTPUT.PUT_LINE('---------------------------');
  END LOOP;
  CLOSE curseur_employes; -- Fermer le curseur
END;
/

Curseur - Exemple complet

# Introduction PL/SQL
DECLARE
  CURSOR curseur_employes IS
    SELECT employee_id, first_name, last_name, salary
    FROM hr.employees
    WHERE department_id = 50;
BEGIN
  -- Utilisation de la boucle FOR pour parcourir les résultats
  FOR ligne_employe IN curseur_employes LOOP
    DBMS_OUTPUT.PUT_LINE('Employé ID : ' || ligne_employe.employee_id);
    DBMS_OUTPUT.PUT_LINE('Nom : ' || ligne_employe.first_name || ' ' || ligne_employe.last_name);
    DBMS_OUTPUT.PUT_LINE('Salaire : ' || ligne_employe.salary);
    DBMS_OUTPUT.PUT_LINE('---------------------------');
  END LOOP;
END;
/

Il est possible de simplifier l'exemple précédent. En effet, un curseur peut être parcouru facilement grâce à une boucle FOR.

Dans ce cas, l'ouverture, la fermeture et le parcours est implicite, ce qui facilite le développement :

# Introduction PL/SQL

Exercice 4.5

Augmentation de tous les salaires à
4000 minimum :

Introduction PL/SQL

Gestion des exceptions

# Introduction PL/SQL

La gestion des exceptions en PL/SQL est un aspect crucial pour créer des applications robustes et fiables.

Il existe deux types principaux d'exceptions en PL/SQL :

  1. Exceptions prédéfinies : Gérées automatiquement par Oracle, comme NO_DATA_FOUND, ZERO_DIVIDE, etc.
  2. Exceptions personnalisées : Définies et gérées par les développeurs pour des conditions spécifiques.

Syntaxe :

BEGIN
  -- Code susceptible de provoquer une erreur
EXCEPTION
  WHEN exception_name THEN
    -- Code de gestion de l'exception
  WHEN OTHERS THEN
    -- Gestion par défaut pour toutes les autres exceptions
END;

Gestion des exceptions

# Introduction PL/SQL

Exemple d'exception personnalisée :

DECLARE
  salaire_max EXCEPTION; -- Déclaration de l'exception
  seuil CONSTANT NUMBER := 5000;
  v_salaire NUMBER;
BEGIN
  -- Récupération du salaire d'un employé (par exemple, avec un ID spécifique)
  SELECT salaire_empl INTO v_salaire 
  FROM tb_employes
  WHERE pk_empl = 1;

  -- Vérification si le salaire dépasse le seuil
  IF v_salaire > seuil THEN
    RAISE salaire_max; -- Déclenchement de l'exception
  END IF;

  -- Affichage si tout est correct
  DBMS_OUTPUT.PUT_LINE('Salaire valide : ' || v_salaire);

EXCEPTION
  -- Gestion de l'exception
  WHEN salaire_max THEN
    DBMS_OUTPUT.PUT_LINE('Erreur : Le salaire dépasse le seuil autorisé.');
END;
/

Gestion des exceptions

# Introduction PL/SQL

Voici une liste des exceptions prédéfinies les plus couramment utilisées en PL/SQL :

NO_DATA_FOUND Déclenchée lorsqu'une requête SELECT INTO ne retourne aucune ligne.
TOO_MANY_ROWS
 		 Survient lorsqu'une requête SELECT INTO retourne plus d'une ligne.
 
ZERO_DIVIDE Se produit lors d'une tentative de division par zéro.
VALUE_ERROR Déclenchée en cas d'erreur arithmétique, de conversion ou de troncature.
CURSOR_ALREADY_OPEN Se produit lorsqu'on tente d'ouvrir un curseur déjà ouvert.
INVALID_NUMBER Survient lors d'une tentative de conversion d'une chaîne en nombre échoue.
CASE_NOT_FOUND Déclenchée lorsqu'aucune des conditions d'une instruction CASE n'est satisfaite
OTHERS Cette exception spéciale permet de capturer toutes les autres erreurs non spécifiées explicitement.

Ces exceptions prédéfinies sont automatiquement déclenchées par Oracle lorsque les conditions correspondantes sont rencontrées, facilitant ainsi la gestion des erreurs courantes dans les programmes PL/SQL.

Gestion des exceptions

# Introduction PL/SQL

Exemple d'exception prédéfinie :

DECLARE
  v_nom VARCHAR2(100);
  v_id NUMBER := 99;
BEGIN
  -- Récupération du nom de l'employé
  SELECT nom_empl INTO v_nom 
  FROM tb_employes
  WHERE pk_empl = v_id;

  -- Affichage du nom si trouvé
  DBMS_OUTPUT.PUT_LINE('Nom de l\'employé : ' || v_nom);

EXCEPTION
  -- Gestion de l'exception prédéfinie NO_DATA_FOUND
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('Erreur : Aucun employé trouvé avec l\'ID ' || v_id || '.');
  -- Pour les autres exceptions, on affiche SQLERRM
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
/

SQLERRM est une fonction intégrée de PL/SQL qui retourne le message d'erreur associé à l'exception courante. Cela permet de comprendre la cause exacte de l'erreur.

# Introduction PL/SQL

Exercice 4.6

Créer une procédure pour lister tous
les produits d'une catégorie donnée :

Introduction PL/SQL

# Travail pour la prochaine fois

Travail pour la prochaine fois

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

M140 - Jour 5

By paranor

M140 - Jour 5

  • 91

More from paranor