🟦 PL/SQL — Guide complet (Oracle)

Structure canonique

DECLARE
  -- Déclarations (variables, constantes, types)
  v_customer_id  NUMBER;
  v_name         VARCHAR2(200);
BEGIN
  -- Logique
  SELECT customer_id, name
    INTO v_customer_id, v_name
    FROM customers
   WHERE email = 'a@b.com';

  DBMS_OUTPUT.PUT_LINE('ID='||v_customer_id||' name='||v_name);

EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('No customer');
  WHEN TOO_MANY_ROWS THEN
    DBMS_OUTPUT.PUT_LINE('Email not unique!');
  WHEN OTHERS THEN
    -- à traiter proprement (log + re-raise si nécessaire)
    DBMS_OUTPUT.PUT_LINE('Unexpected: '||SQLERRM);
END;
/

Types & variables (rappel)

Scope & bonnes pratiques

• %TYPE pour coller au schéma (moins d’erreurs lors des évolutions).
• NULL = valeur “inconnue” : attention aux comparaisons (utiliser IS NULL).
• Préférer constants et noms explicites (préfixes v_/p_/g_).
• Éviter DBMS_OUTPUT en prod → instrumentation propre (table log / APEX_DEBUG / etc.).

Truc pro : variables ancrées

v_salary employees.salary%TYPE;
v_emp    employees%ROWTYPE;

Cursor FOR loop (simple)

FOR r IN (SELECT customer_id FROM customers WHERE status='ACTIVE') LOOP
  -- traitement
  NULL;
END LOOP;

Bulk Collect + FORALL (pro perf)

DECLARE
  TYPE t_ids IS TABLE OF customers.customer_id%TYPE;
  v_ids t_ids;
BEGIN
  SELECT customer_id BULK COLLECT INTO v_ids
  FROM customers
  WHERE status='ACTIVE';

  FORALL i IN 1..v_ids.COUNT
    UPDATE customers SET last_seen = SYSTIMESTAMP
    WHERE customer_id = v_ids(i);
END;
/

Règles de perf

• Préférer SQL set-based (UPDATE/INSERT MERGE) plutôt que boucles.
• Si boucle obligatoire : BULK COLLECT (par batch) + FORALL.
• Utiliser un LIMIT sur bulk collect en très gros volumes.
• Mesurer : instrumentation + stats + plans SQL (sinon “optimisations” inutiles).

Bulk par batches

LOOP
  FETCH c BULK COLLECT INTO v_rows LIMIT 1000;
  EXIT WHEN v_rows.COUNT = 0;

  FORALL i IN 1..v_rows.COUNT
    -- DML
END LOOP;

Package Spec (contrat public)

CREATE OR REPLACE PACKAGE pkg_customer AS
  PROCEDURE create_customer(p_email IN VARCHAR2, p_name IN VARCHAR2, o_id OUT NUMBER);
  FUNCTION  get_status(p_id IN NUMBER) RETURN VARCHAR2;

  -- Constantes publiques (si utile)
  c_status_active CONSTANT VARCHAR2(10) := 'ACTIVE';
END pkg_customer;
/

Package Body (implémentation)

CREATE OR REPLACE PACKAGE BODY pkg_customer AS

  -- Private helper (non exposé)
  FUNCTION normalize_email(p_email IN VARCHAR2) RETURN VARCHAR2 IS
  BEGIN
    RETURN LOWER(TRIM(p_email));
  END;

  PROCEDURE create_customer(p_email IN VARCHAR2, p_name IN VARCHAR2, o_id OUT NUMBER) AS
  BEGIN
    INSERT INTO customers(email, name)
    VALUES(normalize_email(p_email), p_name)
    RETURNING customer_id INTO o_id;
  END;

  FUNCTION get_status(p_id IN NUMBER) RETURN VARCHAR2 AS
    v_status customers.status%TYPE;
  BEGIN
    SELECT status INTO v_status FROM customers WHERE customer_id = p_id;
    RETURN v_status;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN RETURN 'UNKNOWN';
  END;

BEGIN
  -- Section d'initialisation du package (optionnelle)
  NULL;
END pkg_customer;
/

Pourquoi les packages sont “la brique pro”

• Encapsulation : tu exposes une API stable, tu caches les détails.
• Contrat : la spec sert de documentation vivante.
• Organisation : regrouper par domaine (customer, billing, batch, security).
• Versioning : limiter les breaking changes pour les apps clientes.

Conventions (recommandées)

EXECUTE IMMEDIATE (avec bind variables)

DECLARE
  v_sql   VARCHAR2(4000);
  v_cnt   NUMBER;
  p_status VARCHAR2(20) := 'ACTIVE';
BEGIN
  v_sql := 'SELECT COUNT(*) FROM customers WHERE status = :1';
  EXECUTE IMMEDIATE v_sql INTO v_cnt USING p_status;
  DBMS_OUTPUT.PUT_LINE('cnt='||v_cnt);
END;
/

DML dynamique (bind)

v_sql := 'UPDATE customers SET status = :1 WHERE customer_id = :2';
EXECUTE IMMEDIATE v_sql USING 'INACTIVE', 1234;

Ce qu’il ne faut PAS faire

-- ❌ Injection + hard parse :
v_sql := 'SELECT * FROM customers WHERE email = ''' || p_email || '''';

Règles de sécurité

• Toujours utiliser des bind variables pour les valeurs.
• Si besoin d’objets dynamiques (table/col) : whitelists + DBMS_ASSERT.
• Limiter les privilèges (least privilege).

Exceptions Oracle courantes

RAISE_APPLICATION_ERROR (métier)

IF p_amount <= 0 THEN
  RAISE_APPLICATION_ERROR(-20001, 'Amount must be > 0');
END IF;

Stratégie pro (recommandée)

Logging minimal (pattern)

BEGIN
  -- ...
EXCEPTION
  WHEN OTHERS THEN
    INSERT INTO app_error_log(at_ts, code, msg, backtrace)
    VALUES(SYSTIMESTAMP, SQLCODE, SQLERRM, DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
    RAISE;
END;

Definer rights vs Invoker rights

CREATE OR REPLACE PACKAGE pkg_admin AUTHID CURRENT_USER AS
  PROCEDURE do_admin_task;
END;
/

Règles “least privilege”

• Donner le minimum de GRANT nécessaire (SELECT/EXECUTE ciblés).
• Éviter de sur-privilégier un schéma “app” (limiter CREATE ANY / etc.).
• Auditer les EXECUTE sur packages sensibles.
• Dynamic SQL : bind + whitelist + DBMS_ASSERT.

• Écrire des packages “pur métier” (fonctions) testables sans dépendances externes.
• Isoler la logique DML dans des procédures, tester avec datasets contrôlés.
• Utiliser des transactions / savepoints pour rollback entre tests.
• Centraliser des utilitaires de test (seed data, cleanup).

utPLSQL (idée)

-- Ex pseudo (concept)
-- describe('pkg_customer')
-- it('should create customer')
-- expect(select count(*) ...).to_equal(1)

CI / pipeline (piste)

Syntaxe

-- bloc
DECLARE ... BEGIN ... EXCEPTION ... END; /

-- select into
SELECT col INTO v FROM t WHERE ...;

-- insert returning
INSERT ... RETURNING id INTO v_id;

-- exceptions
WHEN NO_DATA_FOUND THEN ...
WHEN OTHERS THEN ... SQLERRM ...

-- procedure/function
PROCEDURE p(x IN NUMBER, o OUT NUMBER);
FUNCTION f(x IN NUMBER) RETURN VARCHAR2;

-- package
PACKAGE spec / PACKAGE BODY

Perf & sécurité

-- bulk (volumes)
BULK COLLECT ... LIMIT
FORALL ... DML

-- dynamic SQL safe
EXECUTE IMMEDIATE '... :1 ...' USING v;

-- droits
AUTHID CURRENT_USER (invoker)
sinon definer rights

-- log erreurs
SQLCODE / SQLERRM / FORMAT_ERROR_BACKTRACE