1. Qu'est-ce que PL/SQL ?
PL/SQL (Procedural Language SQL) est le langage procédural d'Oracle.
SQL seul :
SELECT *
FROM Employees;
PL/SQL ajoute :
- Variables
- Conditions
- Boucles
- Gestion des exceptions
- Procédures
- Fonctions
- Packages
Exemple :
DECLARE
v_salary NUMBER;
BEGIN
SELECT Salary
INTO v_salary
FROM Employees
WHERE EmployeeId = 1;
DBMS_OUTPUT.PUT_LINE(v_salary);
END;
/
Avantages
Traitement côté base
Moins d'allers-retours réseau
Performance
Sécurité
Centralisation de la logique métier
2. Procédure stockée (Stored Procedure)
Une procédure est un bloc PL/SQL stocké dans la base.
Exemple :
CREATE OR REPLACE PROCEDURE UpdateSalary
(
p_employee_id NUMBER,
p_salary NUMBER
)
AS
BEGIN
UPDATE Employees
SET Salary = p_salary
WHERE EmployeeId = p_employee_id;
COMMIT;
END;
/
Exécution :
EXEC UpdateSalary(100, 5000);
Paramètres
IN
Lecture seule.
p_employee_id IN NUMBER
OUT
Retourne une valeur.
CREATE PROCEDURE GetSalary
(
p_employee_id IN NUMBER,
p_salary OUT NUMBER
)
IN OUT
Lecture et modification.
p_amount IN OUT NUMBER
3. Fonction PL/SQL
Différence principale :
Une fonction retourne une valeur.
CREATE OR REPLACE FUNCTION GetBonus
(
p_salary NUMBER
)
RETURN NUMBER
AS
BEGIN
RETURN p_salary * 0.10;
END;
/
Utilisation :
SELECT GetBonus(Salary)
FROM Employees;
Procédure vs Fonction
| Procédure | Fonction |
|---|---|
| Peut ne rien retourner | Retour obligatoire |
| Appelée via EXEC | Peut être utilisée dans un SELECT |
| Actions métier | Calculs |
4. Packages
Sujet très fréquent en entretien Oracle.
Un package est un conteneur regroupant :
Procédures
Fonctions
Variables
Types
Curseurs
Comparable à une classe statique en C#.
Package Specification
Interface publique.
CREATE OR REPLACE PACKAGE EmployeePkg
AS
PROCEDURE UpdateSalary(
p_employee_id NUMBER,
p_salary NUMBER);
FUNCTION GetSalary(
p_employee_id NUMBER)
RETURN NUMBER;
END EmployeePkg;
/
Package Body
Implémentation.
CREATE OR REPLACE PACKAGE BODY EmployeePkg
AS
PROCEDURE UpdateSalary
(
p_employee_id NUMBER,
p_salary NUMBER
)
IS
BEGIN
UPDATE Employees
SET Salary = p_salary
WHERE EmployeeId = p_employee_id;
END;
FUNCTION GetSalary
(
p_employee_id NUMBER
)
RETURN NUMBER
IS
v_salary NUMBER;
BEGIN
SELECT Salary
INTO v_salary
FROM Employees
WHERE EmployeeId = p_employee_id;
RETURN v_salary;
END;
END EmployeePkg;
/
Utilisation
EXEC EmployeePkg.UpdateSalary(100, 6000);
ou
SELECT EmployeePkg.GetSalary(100)
FROM Dual;
Pourquoi utiliser des Packages ?
Organisation
Au lieu de :
100 procédures dispersées
on a :
CustomerPkg
PaymentPkg
AccountPkg
TradePkg
Encapsulation
Comme en C# :
public class AccountService
{
}
En Oracle :
AccountPkg
Performance
Oracle garde le package compilé en mémoire.
Moins de recompilations.
Gestion des Exceptions
Très demandée.
BEGIN
UPDATE Accounts
SET Balance = Balance - 100;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Not Found');
WHEN OTHERS THEN
ROLLBACK;
RAISE;
END;
/
Curseurs
Utilisés pour parcourir plusieurs lignes.
DECLARE
CURSOR c_emp IS
SELECT EmployeeId
FROM Employees;
BEGIN
FOR emp IN c_emp LOOP
DBMS_OUTPUT.PUT_LINE(emp.EmployeeId);
END LOOP;
END;
/
Transactions PL/SQL
BEGIN
UPDATE Accounts
SET Balance = Balance - 100
WHERE Id = 1;
UPDATE Accounts
SET Balance = Balance + 100
WHERE Id = 2;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
END;
/
Questions fréquentes en entretien
Pourquoi utiliser une procédure stockée ?
Réduire les allers-retours réseau, centraliser la logique métier, améliorer les performances et sécuriser l'accès aux données.
Différence Procédure et Fonction ?
Une fonction retourne obligatoirement une valeur et peut être utilisée dans une requête SQL. Une procédure exécute principalement des actions métier et ne retourne pas forcément de valeur.
Pourquoi utiliser un Package ?
Pour regrouper des procédures et fonctions liées à un même domaine métier, améliorer l'organisation du code, l'encapsulation et les performances grâce à la compilation en mémoire.
Réponse entretien senior (banque)
Dans les environnements Oracle, j'ai utilisé PL/SQL pour implémenter des procédures stockées, des fonctions et des packages afin de centraliser les traitements métier côté base. Les packages permettent d'organiser les objets par domaine fonctionnel, tandis que les procédures stockées réduisent les échanges entre l'application .NET et la base de données. J'accorde également une attention particulière à la gestion des transactions, des exceptions et aux performances des requêtes via l'indexation et l'analyse des plans d'exécution.