Les 20 meilleures questions d'entretien PL/SQL et leurs réponses en 2025

PL/SQL, qui est l'extension du langage procédural Oracle de SQL, est quelque chose que vous devez connaître si vous allez travailler avec des bases de données Oracle. C'est le cas pour des fonctions importantes telles que celles d'administrateur de base de données, de développeur et d'analyste de données, qui doivent toutes gérer une logique commerciale complexe au sein de la base de données.

Je pense que les recruteurs évaluent les candidats sur trois aspects essentiels :

• Compréhension conceptuelle : Principes de base, types de données, structures de contrôle et gestion des exceptions.
• Capacité pratique de codage : Rédiger des procédures stockées, des fonctions, des déclencheurs et des paquets efficaces.
• Considérations sur les performances : Optimisation du code PL/SQL pour améliorer le temps d'exécution, minimiser la consommation de ressources et éviter les erreurs telles que le changement excessif de contexte entre SQL et PL/SQL.

Pour vous aider, je vous propose un guide sur les questions d'entretien PL/SQL. Je commencerai par les concepts fondamentaux, puis j'aborderai des sujets plus avancés tels que le traitement en masse, le SQL dynamique et l'optimisation des performances.

Si vous souhaitez comprendre les concepts fondamentaux de la base de données Oracle, je vous recommande de suivre notre cours Introduction à Oracle SQL, qui est une ressource vraiment importante pour apprendre comment interagir avec la base de données Oracle et comment la base de données utilise PL/SQL pour traiter les requêtes.

Questions d'entretien PL/SQL pour débutants

Lors de la phase initiale de l'entretien, l'examinateur peut poser des questions fondamentales pour évaluer votre connaissance des concepts de base des bases de données et de PL/SQL. Essayez d'étudier ces questions et réponses pour vous préparer à la phase initiale de l'entretien.

1. Qu'est-ce que PL/SQL ?

PL/SQL est l'extension procédurale d'Oracle pour SQL. Contrairement à SQL, qui est déclaratif et se concentre sur l'extraction et la manipulation de données, PL/SQL permet aux développeurs de mettre en œuvre une logique procédurale, ce qui en fait un outil puissant pour l'écriture de règles commerciales complexes dans la base de données. Il prend en charge les variables, les boucles, les conditionnelles, la gestion des exceptions et la programmation modulaire par le biais de procédures, de fonctions et de paquets.

2. Quelle est la structure de base d'un bloc PL/SQL ?

Un bloc PL/SQL est l'unité fondamentale d'exécution en PL/SQL et se compose de quatre sections principales :

• DECLARE (facultatif) : Utilisé pour définir les variables, les constantes, les curseurs et les types définis par l'utilisateur.

• BEGIN: La section exécutable où sont écrites les requêtes SQL et les instructions procédurales.

• EXCEPTION (facultatif) : Gère les erreurs d'exécution et les exceptions afin d'assurer une récupération gracieuse des erreurs.

• END;: Marque la fin du bloc.

3. Quels sont les types de données PL/SQL essentiels ?

PL/SQL prend en charge différents types de données, classés comme suit :

• Types scalaires: Les types à valeur unique tels que NUMBER, VARCHAR2, DATE, BOOLEAN.

• Types de composites : Collections telles que RECORD (structures personnalisées) et TABLE/VARRAY (tableaux).

• Types de référence : Pointeurs vers des objets de base de données, tels que REF CURSOR pour le traitement dynamique des requêtes.

4. Quelles sont les structures de contrôle de base en PL/SQL ?

PL/SQL comprend plusieurs structures de contrôle qui aident à gérer le flux d'un programme :

• Boucles : Il s'agit notamment de LOOP, FOR LOOP, et WHILE LOOP, qui permettent l'exécution répétitive de déclarations.

• Déclarations conditionnelles : Il s'agit notamment des instructions IF et CASE, qui exécutent différents blocs de code en fonction de conditions. La fonction DECODE() est un autre bon exemple de conditionnel qui mérite d'être étudié. 

Questions d'entretien PL/SQL intermédiaires

Après avoir abordé les questions de base, passons maintenant à des questions d'entretien de niveau intermédiaire sur la structure des données. Après avoir testé vos connaissances de base, les examinateurs vont maintenant tester vos compétences techniques dans la mise en œuvre et l'utilisation des concepts PL/SQL.

5. Quelle est la différence entre les procédures stockées et les fonctions ?

Les procédures stockées et les fonctions sont toutes deux des blocs de code PL/SQL réutilisables, mais elles ont des objectifs assez différents.

Les procédures stockées sont utilisées pour effectuer des opérations qui ne renvoient pas de valeur, telles que l'insertion, la mise à jour ou la suppression de données. Ils sont utilisés pour les tâches qui modifient les données ou effectuent des opérations complexes sans renvoyer de résultat.

Par exemple, la procédure ci-dessous met à jour le site salary de l'employé dont l'adresse est employee_id en y ajoutant l'adresse p_increment, de sorte que le salaire soit mis à jour de manière dynamique en fonction des paramètres d'entrée,

CREATE PROCEDURE update_salary(p_emp_id NUMBER, p_increment NUMBER) AS  
BEGIN  
    -- Update the salary of the employee with the given ID  
    UPDATE employees  
    SET salary = salary + p_increment  
    WHERE employee_id = p_emp_id;  
END;

Les fonctions, quant à elles, renvoient une valeur après avoir effectué des opérations. Ils conviennent aux calculs ou à la recherche de données qui doivent renvoyer un résultat.

La fonction ci-dessous simplifie la recherche du salaire d'un employé, ce qui permet de la réutiliser dans des requêtes SQL ou d'autres procédures.

CREATE FUNCTION get_employee_salary(p_emp_id NUMBER) RETURN NUMBER AS  
    v_salary NUMBER;  
BEGIN  
    -- Retrieve the salary for the given employee ID  
    SELECT salary INTO v_salary  
    FROM employees  
    WHERE employee_id = p_emp_id;  
    
    -- Return the retrieved salary  
    RETURN v_salary;  
END;

6. Qu'est-ce qu'un paquet PL/SQL ?

Les packages PL/SQL sont des collections de procédures, de fonctions et de variables apparentées qui encapsulent le code pour une meilleure organisation et réutilisation. Ils se composent de deux parties :

• Spécification de l'emballage : Déclare les éléments publics (procédures, fonctions, variables).
• Corps de l'emballage : Contient les détails de la mise en œuvre des procédures et des fonctions déclarées dans la spécification.

Par exemple, la requête ci-dessous crée un paquetage employee_pkg qui définit une procédure pour augmenter le salaire d'un employé et une fonction pour récupérer le nombre total d'employés, leurs implémentations devant être fournies dans le corps du paquetage.

-- Create a package named 'employee_pkg'
CREATE PACKAGE employee_pkg AS  

    -- Procedure to increase an employee's salary by a percentage  
    PROCEDURE raise_salary(p_emp_id NUMBER, p_percent NUMBER);  

    -- Function to return the total number of employees  
    FUNCTION get_total_employees RETURN NUMBER;  

END employee_pkg;

7. Que sont les déclencheurs PL/SQL ?

Les déclencheurs sont des blocs PL/SQL qui s'exécutent automatiquement en réponse à des événements spécifiques de la base de données, tels que des insertions, des mises à jour ou des suppressions. Les déclencheurs sont utilisés pour appliquer les règles de gestion. Ils sont également couramment utilisés pour effectuer des audits. Ils sont classés en plusieurs catégories :

• Déclencheurs au niveau de la ligne : Exécutez une fois pour chaque ligne affectée.
• Déclencheurs au niveau de la déclaration : Exécutez une fois par instruction SQL, quel que soit le nombre de lignes affectées.

La requête ci-dessous crée un déclencheur AFTER UPDATE trg_salary_audit sur le tableau employees qui enregistre les modifications de salaire dans le tableau salary_audit table, en saisissant l'ID de l'employé, l'ancien et le nouveau salaire, ainsi que l'horodatage de la mise à jour.

-- Create or replace a trigger 'trg_salary_audit'
CREATE OR REPLACE TRIGGER trg_salary_audit  
AFTER UPDATE OF salary ON employees  -- Fires after salary updates in 'employees' table
FOR EACH ROW  -- Executes for each updated row
BEGIN  
    -- Inserts old and new salary details into 'salary_audit' table
    INSERT INTO salary_audit (employee_id, old_salary, new_salary, change_date)  
    VALUES (:OLD.employee_id, :OLD.salary, :NEW.salary, SYSDATE);  
END;

Étudiez notre tutoriel sur les déclencheurs SQL afin d'être prêt au cas où les déclencheurs seraient évoqués lors de l'entretien. 

8. Quelles sont les méthodes de gestion des exceptions en PL/SQL ?

PL/SQL fournit des mécanismes de gestion des erreurs pour assurer la stabilité et éviter les pannes. Les types d'exceptions sont les suivants :

• Exceptions prédéfinies : Les exceptions intégrées telles que NO_DATA_FOUND, TOO_MANY_ROWS, et ZERO_DIVIDE.

• Exceptions définies par l'utilisateur : Les exceptions personnalisées sont déclarées à l'aide de EXCEPTION et levées à l'aide de RAISE.

Par exemple, le bloc PL/SQL suivant récupère le salaire d'un employé dont l'ID est 100, lève une exception personnalisée si le salaire est inférieur à 1000 et gère les erreurs éventuelles, y compris les enregistrements d'employés manquants et les exceptions inattendues.

DECLARE  
    v_salary NUMBER;  -- Variable to store the employee's salary  
    e_low_salary EXCEPTION;  -- Custom exception for low salary  

BEGIN  
    -- Retrieve salary of employee with ID 100  
    SELECT salary INTO v_salary FROM employees WHERE employee_id = 100;  

    -- Raise exception if salary is below 1000  
    IF v_salary < 1000 THEN  
        RAISE e_low_salary;  
    END IF;  

EXCEPTION  
    WHEN NO_DATA_FOUND THEN  -- Handle case where employee ID is not found  
        DBMS_OUTPUT.PUT_LINE('Employee not found.');  
    WHEN e_low_salary THEN  -- Handle custom low salary exception  
        DBMS_OUTPUT.PUT_LINE('Salary is below the allowed threshold.');  
    WHEN OTHERS THEN  -- Handle any other unexpected errors  
        DBMS_OUTPUT.PUT_LINE('An unexpected error occurred: ' || SQLERRM);  

END;

Les meilleures pratiques en matière de gestion des exceptions sont les suivantes :

• Anticipez toujours les exceptions potentielles.
• Utilisez des messages d'erreur significatifs.
• Enregistrez les exceptions à des fins d'audit.
• Veillez à ce que le code de gestion des exceptions soit concis et axé sur la récupération.

9. Comment pouvez-vous vérifier si une instruction UPDATE est exécutée ou non ?

L'attribut SQL %NOTFOUND peut être utilisé pour déterminer si l'instruction UPDATE a modifié avec succès des enregistrements. Si la dernière instruction SQL exécutée n'a affecté aucune ligne, cette variable renvoie TRUE.

Par exemple, la requête ci-dessous met à jour le salaire des employés du département 10 en l'augmentant de 10 %, puis vérifie si des lignes ont été affectées par l'instruction UPDATE à l'aide de l'attribut %NOTFOUND. Si aucune ligne n'a été mise à jour, il affiche un message indiquant "Aucune ligne n'a été mise à jour". Si des lignes ont été mises à jour, le nombre de lignes concernées est indiqué à l'aide de l'attribut SQL%ROWCOUNT.

DECLARE
    -- Declare a variable to store the number of rows updated
    rows_updated INTEGER;
BEGIN
    -- Perform an UPDATE statement on the 'employees' table
    UPDATE employees
    SET salary = salary * 1.1
    WHERE department_id = 10;

    -- Check if any rows were updated by using %NOTFOUND
    IF SQL%NOTFOUND THEN
        -- If no rows were updated, print a message
        DBMS_OUTPUT.PUT_LINE('No rows were updated.');
    ELSE
        -- If rows were updated, print how many rows were affected
        rows_updated := SQL%ROWCOUNT;  -- Store the number of rows updated
        DBMS_OUTPUT.PUT_LINE(rows_updated || ' rows were updated.');
    END IF;
END;
/

Questions d'entretien avancées sur PL/SQL

Examinons maintenant quelques questions d'entretien plus poussées, au cas où vous postuleriez à un poste plus élevé exigeant davantage d'expérience.

10. Quelles sont les méthodes d'optimisation des performances en PL/SQL ?

Minimiser les changements de contexte entre SQL et PL/SQL est crucial pour optimiser les performances. Chaque commutation entraîne des frais généraux qui peuvent ralentir les temps d'exécution, en particulier dans les situations impliquant des transitions fréquentes entre les deux.

11. Comment utilisez-vous les opérations en bloc pour minimiser les changements de contexte ?

PL/SQL fournit des techniques de traitement en masse pour optimiser l'interaction entre SQL et PL/SQL en récupérant ou en modifiant plusieurs lignes à la fois.

Par exemple, le bloc PL/SQL ci-dessous récupère tous les employés du département 10 à l'aide de BULK COLLECT dans une collection et la parcourt pour imprimer le nom de chaque employé, ce qui améliore les performances en minimisant les changements de contexte entre SQL et PL/SQL.

DECLARE  
    -- Define a table-type collection based on the 'employees' table structure  
    TYPE emp_table IS TABLE OF employees%ROWTYPE;  
    v_emps emp_table;  -- Declare a variable of this type  

BEGIN  
    -- Bulk fetch employees from department 10 into the collection  
    SELECT * BULK COLLECT INTO v_emps FROM employees WHERE department_id = 10;  

    -- Loop through the collection and print employee names  
    FOR i IN 1..v_emps.COUNT LOOP  
        DBMS_OUTPUT.PUT_LINE('Employee: ' || v_emps(i).employee_name);  
    END LOOP;  

END;

De plus, ce bloc PL/SQL utilise FORALL pour la mise à jour en masse, augmentant le salaire de 10% pour les employés avec les ID 101, 102 et 103.

DECLARE  
    -- Define a table-type collection for employee IDs  
    TYPE t_emp_ids IS TABLE OF employees.employee_id%TYPE;  
    -- Initialize collection with specific employee IDs  
    v_emp_ids t_emp_ids := t_emp_ids(101, 102, 103);  

BEGIN  
    -- Bulk update salaries by 10% for specified employee IDs  
    FORALL i IN 1..v_emp_ids.COUNT  
        UPDATE employees SET salary = salary * 1.10 WHERE employee_id = v_emp_ids(i);  

END;

12. Que sont les curseurs dynamiques SQL et Ref ?

Le SQL dynamique permet d'exécuter des instructions SQL de manière dynamique au moment de l'exécution, ce qui est utile lorsqu'il s'agit de noms de tableaux, de colonnes ou de structures de requêtes variables.

Le bloc PL/SQL suivant utilise la SQL dynamique pour compter le nombre de lignes dans le tableau employees et imprime le résultat. J'aime ce type d'approche parce qu'elle permet une certaine flexibilité.

DECLARE  
    v_table_name VARCHAR2(50) := 'employees';  -- Store table name  
    v_count NUMBER;  -- Variable to hold row count  

BEGIN  
    -- Dynamically count rows in the specified table  
    EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM ' || v_table_name INTO v_count;  

    -- Print the total count  
    DBMS_OUTPUT.PUT_LINE('Total Employees: ' || v_count);  

END;  

Les curseurs Ref sont des curseurs dynamiques qui peuvent être ouverts, récupérés et fermés au moment de l'exécution, ce qui permet de transmettre les résultats des requêtes entre les unités du programme.

Le bloc PL/SQL ci-dessous utilise REF CURSOR pour récupérer et imprimer les noms des employés du département 20. Le curseur est ouvert dynamiquement, parcouru à l'aide d'une boucle et fermé après traitement.

DECLARE  
    -- Define a REF CURSOR type  
    TYPE emp_ref_cursor IS REF CURSOR;  
    v_cursor emp_ref_cursor;  -- Declare a cursor variable  
    v_name employees.employee_name%TYPE;  -- Variable to store employee name  

BEGIN  
    -- Open the cursor for employees in department 20  
    OPEN v_cursor FOR SELECT employee_name FROM employees WHERE department_id = 20;  

    -- Fetch and print employee names in a loop  
    LOOP  
        FETCH v_cursor INTO v_name;  
        EXIT WHEN v_cursor%NOTFOUND;  
        DBMS_OUTPUT.PUT_LINE('Employee: ' || v_name);  
    END LOOP;  

    -- Close the cursor  
    CLOSE v_cursor;  

END;

13. Comment gérez-vous les erreurs de mutation des tableaux ?

Les erreurs de mutation de tableau se produisent lorsqu'un déclencheur tente de modifier le tableau sur lequel il est déclenché. Un déclencheur composé permet de diviser l'exécution du déclencheur en plusieurs phases telles que BEFORE, AFTER, et FOR EACH ROW afin d'éviter les problèmes de modification directe.

Le déclencheur composé ci-dessous enregistre efficacement les modifications de salaire dans le tableau salary_audit en collectant les données avant chaque mise à jour de ligne et en effectuant une insertion en bloc après l'instruction, ce qui réduit les changements de contexte et améliore les performances.

CREATE OR REPLACE TRIGGER trg_salary_audit  
FOR UPDATE OF salary ON employees  -- Trigger fires on salary updates  
COMPOUND TRIGGER  

    -- Declare an associative array to store audit records  
    TYPE t_salary_audit IS TABLE OF salary_audit%ROWTYPE INDEX BY PLS_INTEGER;  
    v_audit_data t_salary_audit;  
    v_idx PLS_INTEGER := 0;  

    -- Before updating each row, store old and new salary details  
    BEFORE EACH ROW IS  
    BEGIN  
        v_idx := v_idx + 1;  
        v_audit_data(v_idx).employee_id := :OLD.employee_id;  
        v_audit_data(v_idx).old_salary := :OLD.salary;  
        v_audit_data(v_idx).new_salary := :NEW.salary;  
        v_audit_data(v_idx).change_date := SYSDATE;  
    END BEFORE EACH ROW;  

    -- After the statement, insert all audit records in bulk  
    AFTER STATEMENT IS  
    BEGIN  
        FORALL i IN 1..v_idx  
            INSERT INTO salary_audit VALUES v_audit_data(i);  
    END AFTER STATEMENT;  

END trg_salary_audit;

14. Qu'est-ce que la surcharge en PL/SQL ?

La surcharge permet de définir dans un paquet plusieurs procédures ou fonctions portant le même nom mais ayant des paramètres différents. Cela permet d'améliorer la lisibilité et la maintenabilité du code en proposant plusieurs façons d'effectuer des opérations similaires.

Dans la requête ci-dessous, le corps du paquet met en œuvre deux procédures surchargées nommées update_salary: L'une augmente le salaire d'un employé d'un montant déterminé, tandis que l'autre fixe un nouveau salaire avec une date d'entrée en vigueur, en mettant à jour le tableau employees en conséquence.

-- Create the package specification
CREATE OR REPLACE PACKAGE emp_pkg AS  
    -- Procedure to increment salary by a specified amount  
    PROCEDURE update_salary(p_emp_id NUMBER, p_increment NUMBER);

    -- Overloaded procedure to set a new salary with an effective date  
    PROCEDURE update_salary(p_emp_id NUMBER, p_new_salary NUMBER, p_effective_date DATE);
END emp_pkg;
/

-- Create the package body
CREATE OR REPLACE PACKAGE BODY emp_pkg AS  

    -- Procedure to increment salary by a specified amount  
    PROCEDURE update_salary(p_emp_id NUMBER, p_increment NUMBER) AS  
    BEGIN  
        UPDATE employees  
        SET salary = salary + p_increment  
        WHERE employee_id = p_emp_id;  
    END update_salary;  

    -- Overloaded procedure to set a new salary with an effective date  
    PROCEDURE update_salary(p_emp_id NUMBER, p_new_salary NUMBER, p_effective_date DATE) AS  
    BEGIN  
        UPDATE employees  
        SET salary = p_new_salary, last_update = p_effective_date  
        WHERE employee_id = p_emp_id;  
    END update_salary;  

END emp_pkg;
/

15. Quels sont les directives du compilateur et les pragmas en PL/SQL ?

PL/SQL fournit des directives de compilation (PRAGMA) pour optimiser le code et gérer les exceptions. Les pragmas les plus courants sont les suivants :

• PRAGMA EXCEPTION_INIT: Associe une exception définie par l'utilisateur à un code d'erreur Oracle.

• PRAGMA SERIALLY_REUSABLE: Optimise l'utilisation de la mémoire des paquets pour une meilleure évolutivité.

Le bloc PL/SQL ci-dessous gère l'insertion d'un employé dont le salaire n'est pas valide en utilisant une exception personnalisée e_invalid_salary associée au code d'erreur -20001. Si l'exception est levée, un message d'erreur est imprimé.

DECLARE  
    e_invalid_salary EXCEPTION;  -- Declare custom exception for invalid salary  
    PRAGMA EXCEPTION_INIT(e_invalid_salary, -20001);  -- Associate exception with error code -20001  
BEGIN  
    -- Attempt to insert an employee with invalid salary  
    INSERT INTO employees (employee_id, salary) VALUES (999, -1000);  
EXCEPTION  
    -- Handle the custom exception and print a message  
    WHEN e_invalid_salary THEN  
        DBMS_OUTPUT.PUT_LINE('Invalid salary detected!');  
END;  

16. Quelles sont les différentes méthodes de traçage et de débogage du code PL/SQL ?

PL/SQL fournit plusieurs packages intégrés pour le suivi et le débogage des performances du code. Les méthodes courantes consistent à utiliser DBMS_TRACE pour suivre le déroulement de l'exécution, DBMS_APPLICATION_INFO pour surveiller l'activité de la session et DBMS_SESSION pour recueillir des informations de diagnostic au niveau de la session.

La requête ci-dessous active d'abord le suivi SQL à l'aide de DBMS_SESSION.set_sql_trace(TRUE), puis définit les informations de session spécifiques au client à l'aide de DBMS_APPLICATION_INFO.set_client_info() pour la surveillance. Le bloc PL/SQL est exécuté, ce qui simule une opération UPDATE. Enfin, il désactive le traçage après l'exécution en utilisant DBMS_SESSION.set_sql_trace(FALSE).

-- Enable tracing for the current session using DBMS_SESSION
BEGIN
   -- Start session-level tracing
   DBMS_SESSION.set_sql_trace(TRUE);
END;
/

-- Set application information using DBMS_APPLICATION_INFO
BEGIN
   -- Set the application name and action for session monitoring
   DBMS_APPLICATION_INFO.set_client_info('Trace Debug Session');
   DBMS_APPLICATION_INFO.set_action('Debugging PL/SQL Code');
END;
/

-- Example PL/SQL block that simulates a process for debugging
DECLARE
   v_employee_id NUMBER := 100;
BEGIN
   -- Example query to fetch employee details
   FOR rec IN (SELECT first_name, last_name FROM employees WHERE employee_id = v_employee_id) LOOP
      DBMS_OUTPUT.put_line('Employee: ' || rec.first_name || ' ' || rec.last_name);
   END LOOP;
   
   -- Simulate some logic that could be traced
   IF v_employee_id = 100 THEN
      DBMS_OUTPUT.put_line('Employee ID is 100');
   END IF;
END;
/

-- Disable tracing after the session is complete
BEGIN
   -- Stop session-level tracing
   DBMS_SESSION.set_sql_trace(FALSE);
END;
/

Questions d'entretien PL/SQL basées sur des scénarios

Dans les applications d'entreprise réelles, PL/SQL est utilisé pour gérer une logique commerciale complexe. Les intervieweurs évaluent souvent la capacité d'un candidat à appliquer PL/SQL dans des scénarios pratiques. Vous trouverez ci-dessous quelques défis majeurs, ainsi que des stratégies pour les résoudre.

Pour cette dernière section, si vous passez un entretien, je vous encourage à réfléchir à la manière de répondre aux questions en suivant ce modèle, mais essayez, bien sûr, de réorienter votre réponse en fonction de votre secteur d'activité spécifique ou de votre propre expérience. Il en va de même si vous interrogez un candidat et que vous souhaitez avoir des idées sur la manière de poser une question et de juger de la rigueur de la réponse, auquel cas j'espère que cette section vous servira d'inspiration pour vos questions et vos critères d'évaluation.