PL/SQL — triggers & séquences

1. PL/SQL — généralités

• Déporter les traitements de manipulation de données vers le SGBD.
• Stockage du code sous forme de :
  • triggers (réactifs aux événements)
  • fonctions / procédures (appelables)
  • packages (regroupements)
• Langage propre : PL/SQL — Procedural Language for SQL.
• Fournit un ensemble de fonctionnalités communes aux applicatifs.

Structure générale d'un bloc

Un bloc PL/SQL est une descente structurée : déclarations, code, gestion d'erreurs, fin.

DECLARE
   <bloc de variables>
BEGIN
   <bloc de traitements>
EXCEPTION
   WHEN err1 THEN ...
   WHEN err2 THEN ...
   WHEN OTHERS THEN ...
END;

Sortie standard — pour debug

• Package DBMS_OUTPUT · procédure PUT_LINE(...)
• Activation côté session : SET SERVEROUTPUT ON

DBMS_OUTPUT.PUT_LINE('Coucou ' || TO_CHAR(12));

2. Variables et affectations

Trois familles de variables :

• scalaires : nombres, chaînes, date…
• composées : RECORD, TABLE, VARRAY
• curseurs (voir CM6)

-- Type prédéfini
v_prix NUMBER(6,2);

-- Type déduit d'une colonne
v_nom client.nom%TYPE;

Affectation — trois formes

-- ① Classique avec :=
v_tva := 19.6;

-- ② Sur requête avec INTO
v_etudiant etudiant%ROWTYPE;
...
SELECT * INTO v_etudiant FROM etudiant WHERE id = 42;

-- ③ Sur curseur avec FETCH (cf CM6)
FETCH mon_curseur INTO v_ligne;

3. Types composés

Type RECORD — comme une struct C

-- Définir le type
TYPE t_rec_eleve IS RECORD (
   nom    VARCHAR2(50),
   prenom VARCHAR2(30),
   age    NUMBER(3)
);

-- Déclarer la variable
v_eleve t_rec_eleve;

-- Variante : déduit d'une table
v_adresse adresse%ROWTYPE;

Types tableau — TABLE vs VARRAY

• Une seule dimension. Mais possibilité de TABLE de TABLE ou VARRAY de VARRAY.
• Choix selon : mode de parcours + connaissance de la taille.

-- TABLE associative
TYPE tab_noms IS TABLE OF eleve.nom%TYPE
   INDEX BY PLS_INTEGER;

-- VARRAY borné à 7 éléments
TYPE tab_jours IS VARRAY(7) OF VARCHAR2(8);

v_noms  tab_noms;
v_jours tab_jours;

-- Affectations
v_noms(3308) := 'toto';
v_noms(6712) := 'titi';

v_jours := tab_jours('lundi', 'mardi', 'mercredi',
   'jeudi', 'vendredi', 'samedi', 'dimanche');

4. Instructions de contrôle

Conditionnelle

IF cond1 THEN
   trt1;
ELSIF cond2 THEN
   trt2;
ELSE
   trt3;
END IF;

Trois sortes de boucles

-- ① LOOP avec EXIT WHEN
LOOP
   trt;
   EXIT WHEN condition;
END LOOP;

-- ② FOR avec borne
FOR v_it IN deb..fin LOOP
   trt;
END LOOP;

-- ③ WHILE
WHILE condition LOOP
   trt;
END LOOP;

Parcours de tableau associatif

-- Variante FIRST / NEXT (résistant aux trous d'index)
BEGIN
   i := v_noms.FIRST;
   WHILE i IS NOT NULL LOOP
      DBMS_OUTPUT.PUT_LINE('Élève ' || v_noms(i) || ', ID : ' || TO_CHAR(i));
      i := v_noms.NEXT(i);
   END LOOP;
END;

-- Variante FOR i IN FIRST..LAST
FOR i IN v_noms.FIRST..v_noms.LAST LOOP
   DBMS_OUTPUT.PUT_LINE(v_noms(i));
END LOOP;

Parcours de tableau classique (VARRAY)

• Premier élément : indice 1
• Taille : v_tab.COUNT

FOR i IN 1..v_jours.COUNT LOOP
   DBMS_OUTPUT.PUT_LINE(v_jours(i));
END LOOP;

5. Triggers

Instructions de déclenchement

Moment de déclenchement autour de l'événement : BEFORE ou AFTER.

Le trigger BEFORE peut modifier les valeurs (:new) ou refuser l'événement. Le trigger AFTER agit après confirmation.

Syntaxe trigger sur table

CREATE [OR REPLACE] TRIGGER nom_trig
BEFORE|AFTER INSERT|DELETE|UPDATE ON nom_table
[FOR EACH ROW]
[WHEN condition]
DECLARE
   variables
BEGIN
   instructions PL/SQL
END;

Plusieurs déclenchements / colonne particulière

-- Plusieurs événements dans un seul trigger
CREATE OR REPLACE TRIGGER nom_trig
BEFORE INSERT OR DELETE ON nom_table
BEGIN
   IF INSERTING THEN
      ...
   ELSIF DELETING THEN
      ...
   END IF;
END;

-- Sur colonnes particulières (UPDATE uniquement)
CREATE OR REPLACE TRIGGER nom_trig
BEFORE UPDATE OF col1, col2 ON nom_table
DECLARE ...
BEGIN ...
END;

États et gestion du trigger

-- Activer / désactiver
ALTER TRIGGER nom_trig {ENABLE|DISABLE};

-- Recompiler après modification
ALTER TRIGGER nom_trig COMPILE;

-- Supprimer
DROP TRIGGER nom_trig;

Exemple : mise à jour de stock après commande

CREATE OR REPLACE TRIGGER trig_maj_stock
AFTER INSERT ON ligne_commande
FOR EACH ROW
BEGIN
   UPDATE PRODUIT
      SET nb_stock = nb_stock - :new.quantite
      WHERE id_produit = :new.id_produit;
END;

Exemple garde-fou — refus d'une donnée invalide

CREATE OR REPLACE TRIGGER trig_stock_positif
BEFORE UPDATE OF nb_stock ON produit
FOR EACH ROW
BEGIN
   IF :new.nb_stock < 0 THEN
      RAISE_APPLICATION_ERROR(-20012, 'Mise à jour du stock impossible (<0)');
   END IF;
END;

Triggers sur vue — INSTEAD OF

Une vue accepte des modifications de données uniquement si elle contient des clés/champs uniques, pas de regroupement ni de sous-requête. Pour les vues non modifiables, on utilise un trigger INSTEAD OF qui remplace le comportement par défaut.

CREATE [OR REPLACE] TRIGGER nom_trig
INSTEAD OF INSERT ON nom_vue
[DECLARE]
   ...
BEGIN
   traitement alternatif
END;

Exemple complet — vue jointure film + réalisateur

CREATE OR REPLACE VIEW vue_film_real AS
   SELECT num_film, titre, annee, f.num_ind AS id_real, nom, prenom
   FROM film f, individu i
   WHERE f.NUM_IND = i.NUM_IND;

CREATE OR REPLACE TRIGGER trig_ins_vue_film_real
INSTEAD OF INSERT ON vue_film_real
DECLARE
   v_id_real INDIVIDU.NUM_IND%TYPE;
BEGIN
   BEGIN
      SELECT num_ind INTO v_id_real
      FROM INDIVIDU
      WHERE num_ind = :new.id_real;
   EXCEPTION
      WHEN NO_DATA_FOUND THEN
         INSERT INTO individu VALUES (seq_id_real.NEXTVAL, :new.nom, :new.prenom)
            RETURNING num_ind INTO v_id_real;
   END;

   INSERT INTO film (num_film, titre, annee, num_ind)
   VALUES (seq_id_film.NEXTVAL, :new.titre, :new.annee, v_id_real);
END;

6. Séquences

• Permettent de générer automatiquement des valeurs entières (typiquement des clés).
• Le déclenchement d'un trigger associé à une table permet la gestion d'auto-incrémentation.
• Une séquence peut être incrémentée et interrogée.

Syntaxe

CREATE SEQUENCE nom_seq
[INCREMENT BY n]      -- défaut : 1
[START WITH m]        -- défaut : 1
[MINVALUE k]
[MAXVALUE p]
[CYCLE | NOCYCLE]
[CACHE l | NOCACHE];

Accesseurs

INSERT INTO users VALUES (seq_id_usr.NEXTVAL, 'toto');
SELECT seq_id_usr.CURRVAL INTO valeurID FROM DUAL;

Pattern canonique — auto-incrément par trigger

CREATE OR REPLACE TRIGGER id_eleve
BEFORE INSERT ON eleve
FOR EACH ROW
DECLARE
   v_user VARCHAR2(10);
BEGIN
   -- nom de l'utilisateur connecté
   SELECT USER INTO v_user FROM DUAL;

   :new.id_eleve  := seq_id_eleve.NEXTVAL;
   :new.date_ajout := SYSDATE;
   :new.cree_par  := v_user;
END;

Auto-incrémentation transparente : l'application insère sans ID, le trigger l'attribue via la séquence.

★ Réviser le chapitre

Pour vérifier ta compréhension

🃏 Flashcards éclair

Clique pour retourner. Tout retourner

DECLARE
BEGIN
EXCEPTION
END;

CREATE OR REPLACE TRIGGER nom
BEFORE|AFTER INSERT ON tbl
FOR EACH ROW
BEGIN … END;

RAISE_APPLICATION_ERROR(
   -20012,
   'message'
);

CREATE SEQUENCE seq
START WITH 1
INCREMENT BY 1;

BEFORE INSERT ON tbl
FOR EACH ROW
BEGIN
   :new.id := seq.NEXTVAL;
END;

DBMS_OUTPUT.PUT_LINE('msg');
-- côté session :
SET SERVEROUTPUT ON;

✎ Quiz éclair

📌 Pour la feuille recto-verso