La feuille TAD

0. Rappel SQL de base

SELECT — clauses & ordre logique

SELECT DISTINCT col1, col2 AS alias, COUNT(*)   -- 5. projection
FROM   t1 a JOIN t2 b ON a.id = b.id        -- 1. sources
WHERE  a.x > 10                              -- 2. filtre lignes
GROUP BY col1, col2                            -- 3. regroupe
HAVING COUNT(*) > 1                        -- 4. filtre groupes
ORDER BY col1 ASC, col2 DESC;             -- 6. tri

Ordre d'exécution : FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY. Limiter (Oracle) : WHERE ROWNUM <= 10 (avant tri) ou … ORDER BY x FETCH FIRST 10 ROWS ONLY (12c+).

Jointure

-- joint deux tables sur une condition d'égalité
SELECT e.nom, d.libelle
FROM emp e JOIN dept d ON e.dno = d.dno;
-- ancienne syntaxe équivalente : virgule + WHERE
SELECT e.nom, d.libelle FROM emp e, dept d WHERE e.dno = d.dno;

USING(col) remplace ON a.col = b.col quand la colonne porte le même nom.

SELECT dno, COUNT(*), SUM(sal),
       AVG(sal), MIN(sal), MAX(sal)
FROM emp
GROUP BY dno          -- toute col non agrégée
HAVING AVG(sal) > 2000;

q1 UNION q2      -- sans doublons
q1 UNION ALL q2  -- avec doublons (+ rapide)
q1 INTERSECT q2  -- lignes communes
q1 MINUS q2      -- dans q1 pas q2 (Oracle)

Sous-requêtes

-- dans WHERE : IN / comparateur (= si 1 seule valeur)
SELECT * FROM emp WHERE dno IN (SELECT dno FROM dept WHERE loc='PARIS');
SELECT * FROM emp WHERE sal > (SELECT AVG(sal) FROM emp);
-- corrélée (référence la requête externe)
SELECT * FROM emp e WHERE EXISTS (SELECT 1 FROM dept d WHERE d.dno=e.dno);
-- dans FROM (table dérivée, alias requis)
SELECT x.dno FROM (SELECT dno, AVG(sal) m FROM emp GROUP BY dno) x WHERE x.m>2000;

⚠ NOT IN + un NULL dans la liste ⇒ résultat vide : préférer NOT EXISTS. Filtres : = <> < >, BETWEEN a AND b, LIKE 'A%' (%=0..n, _=1 car.), IN(…), IS [NOT] NULL (jamais = NULL), priorité NOT > AND > OR.

LMD — manipulation

INSERT INTO emp (id, nom, sal) VALUES (1, 'Ali', 1500);
INSERT INTO archive (id, nom) SELECT id, nom FROM emp WHERE sal<1000;
UPDATE emp SET sal = sal*1.1, grade = 'B' WHERE dno = 10;
DELETE FROM emp WHERE sal < 800;        -- sans WHERE = tout !
-- MERGE : upsert (maj si présent, insère sinon)
MERGE INTO emp e USING src s ON (e.id = s.id)
  WHEN MATCHED THEN UPDATE SET e.sal = s.sal
  WHEN NOT MATCHED THEN INSERT (id, sal) VALUES (s.id, s.sal);

LDD — tables & contraintes

CREATE TABLE emp (
  id    NUMBER      PRIMARY KEY,                -- en ligne
  nom   VARCHAR2(50) NOT NULL,
  email VARCHAR2(80) UNIQUE,
  sal   NUMBER(8,2)  DEFAULT 0 CHECK (sal >= 0),
  dno   NUMBER,
  -- contraintes nommées (table-level) : recommandé
  CONSTRAINT fk_dept FOREIGN KEY (dno) REFERENCES dept(dno) ON DELETE SET NULL,
  CONSTRAINT ck_sal  CHECK (sal BETWEEN 0 AND 99999)
);
ALTER TABLE emp ADD tel VARCHAR2(15);   ALTER TABLE emp MODIFY nom VARCHAR2(80);
ALTER TABLE emp DROP COLUMN tel;          DROP TABLE emp;
TRUNCATE TABLE emp;   -- vide (rapide, non-rollback, garde la structure)

5 contraintes : PRIMARY KEY (unique+non NULL) · FOREIGN KEY … REFERENCES · NOT NULL · UNIQUE · CHECK ; + DEFAULT. DELETE=LMD (rollback) ; TRUNCATE/DROP=LDD (commit implicite).

Fonctions Oracle utiles

NVL(comm, 0)              -- NULL → 0   ·   COALESCE(a,b,c) = 1re non-NULL
DECODE(g,'A',100,'B',50,0)  -- if/then/else (else=0)
CASE WHEN sal>2000 THEN 'haut' ELSE 'bas' END
'M. '||nom            -- concat   ·   SUBSTR(nom,1,3)   ·   LENGTH(nom)
TO_CHAR(d,'DD/MM/YYYY')   TO_DATE('25/12/2025','DD/MM/YYYY')
SYSDATE   ROUND(x,2)   TRUNC(x)   SELECT SYSDATE FROM DUAL;  -- DUAL=table technique 1 ligne

1. CM1 — SPARC · users · rôles · vues encre bleue

SPARC = 3 niveaux : externe (vues par user) → conceptuel (modèle global) → physique (stockage). Niveau externe Oracle = users · rôles · vues. Rôles d'admin créés à la base : SYS, SYSTEM.

CREATE USER nom IDENTIFIED BY pwd
  DEFAULT TABLESPACE users
  TEMPORARY TABLESPACE temp;
-- à la création : AUCUN privilège
-- (pas même CREATE SESSION)
GRANT  priv|role TO   user|role;
REVOKE priv|role FROM user|role;
CREATE ROLE r [IDENTIFIED BY pwd];
SET ROLE r IDENTIFIED BY pwd;

Vue logique = requête ré-exécutée, aucun stockage. Vue matérialisée (VM) = contenu dupliqué physiquement et rafraîchi (dé-normalisation perf). Doit contenir la clé primaire des sources. Privilèges VM : sys CREATE MATERIALIZED + obj GRANT SELECT sur les sources.

-- vue logique : filtre / projection
CREATE [OR REPLACE] VIEW v_client AS
  SELECT nom, prenom FROM Client WHERE ville = 'Cergy';
-- vue logique sur jointure (plusieurs tables)
CREATE VIEW v_cmd AS
  SELECT c.nom, k.id_cmde, k.total
  FROM Client c JOIN Commande k ON c.id = k.id_client;

-- vue matérialisée (stockée + rafraîchie)
CREATE MATERIALIZED VIEW LOG ON Client;   -- requis pour REFRESH FAST
CREATE MATERIALIZED VIEW vm
  REFRESH FAST ON COMMIT                     -- méthode + mode
  [BUILD DEFERRED]                          -- créée vide, peuplée au 1er refresh
  AS SELECT * FROM Client;
EXEC DBMS_MVIEW.REFRESH('vm');            -- refresh manuel (ON DEMAND)

Les vues réparties (recomposition de fragments H par UNION, V par JOINTURE) sont au §4 BDDR.

2. CM2 — tablespaces · indexes · clusters encre verte

Hiérarchie de stockage : Base → Tablespace → Fichier → Segment → Extension → Bloc logique → bloc physique. 5 segments : Données · Index · Temporaire (tris, sinon SYSTEM) · Amorçage (dictionnaire, dans SYSTEM) · Rollback. 3 fichiers : .dbf données · .rdo/.log Redo Log · .ctl contrôle (min 1+2+1, dans init.ora).

CREATE [UNIQUE] INDEX i ON t(col [ASC|DESC]) TABLESPACE ts;
CREATE INDEX i ON t(col) REVERSE;     -- inversé ; annuler : ALTER INDEX i REBUILD NOREVERSE
CREATE INDEX i ON t(f(col));          -- par fonction (f DETERMINISTIC ; SYSDATE ne l'est PAS)
CREATE BITMAP INDEX i ON t(col);       -- faible sélectivité (EE)
CREATE CLUSTER c(col1 type1) SIZE n TABLESPACE ts;
CREATE TABLE t (…) CLUSTER c(colT);    CREATE INDEX i ON CLUSTER c;  -- OBLIGATOIRE
-- cluster d'une table existante : ① new table CLUSTER ② copier ③ drop ④ renommer

3. CM3 — transactions encre bordeaux

SAVEPOINT sp;
ROLLBACK TO SAVEPOINT sp;  -- annule jusqu'au jalon
ROLLBACK;   -- annule tout   COMMIT; -- valide
SET TRANSACTION READ ONLY; -- lecture consistante
CREATE PUBLIC SYNONYM s FOR objet;
CREATE SCHEMA AUTHORIZATION nom …;

4. CM4 — bases de données réparties (BDDR) encre prune

BDDR = données sur plusieurs sites reliés par réseau. Conception : top-down (global → fragmenté) ou bottom-up (intégration de bases locales). Atouts : fiabilité, perf, croissance ; coûts : synchro, sécurité, interblocage distribué.

CREATE [PUBLIC] DATABASE LINK lien
  CONNECT TO user IDENTIFIED BY pwd
  USING 'nomService';
SELECT * FROM table@lien;
DROP DATABASE LINK lien;

-- recomposition H → UNION
CREATE VIEW V AS
  SELECT * FROM T@s1
  UNION SELECT * FROM T@s2;
-- recomposition V → JOINTURE (clé)
CREATE VIEW V AS
  SELECT T1.cle, T1.a, T2.b
  FROM T1@s1, T2@s2
  WHERE T1.cle = T2.cle;

Réplication (relation recopiée sur d'autres sites) via VM : CREATE MATERIALIZED VIEW image REFRESH FAST AS SELECT … FROM T@lien; — atout : disponibilité, lecture parallèle ; coût : cohérence des copies.

5. PL/SQL — bases & types encre sienne

DECLARE
  -- variables, types, curseurs
BEGIN
  -- SQL + instructions PL/SQL
EXCEPTION
  WHEN err THEN …
END;
-- debug : SET SERVEROUTPUT ON
DBMS_OUTPUT.PUT_LINE('x='||TO_CHAR(n));

v_prix  NUMBER(6,2);
v_nom   client.nom%TYPE;
v_lig   client%ROWTYPE;
TYPE t_rec IS RECORD(nom VARCHAR2(50), age NUMBER);
TYPE t_tab IS TABLE OF eleve.nom%TYPE
            INDEX BY PLS_INTEGER;   -- assoc (sparse)
TYPE t_arr IS VARRAY(7) OF VARCHAR2(8); -- borné

IF c1 THEN … ELSIF c2 THEN … ELSE … END IF;
LOOP … EXIT WHEN cond; END LOOP;
FOR i IN deb..fin LOOP … END LOOP;
WHILE cond LOOP … END LOOP;

-- TABLE assoc (résiste aux trous)
i := t.FIRST;
WHILE i IS NOT NULL LOOP
  … ; i := t.NEXT(i);
END LOOP;
FOR i IN 1..v.COUNT LOOP … END LOOP; -- VARRAY

6. Triggers & séquences

CREATE [OR REPLACE] TRIGGER nom
  BEFORE|AFTER INSERT|DELETE|UPDATE [OF cols] ON tbl
  [FOR EACH ROW] [WHEN cond]
DECLARE … BEGIN … END;
-- plusieurs événements :
BEFORE INSERT OR DELETE ON tbl … IF INSERTING THEN … ELSIF DELETING THEN … END IF;
CREATE [OR REPLACE] TRIGGER nom INSTEAD OF INSERT ON vue BEGIN … END;  -- vue non modifiable

CREATE SEQUENCE seq [START WITH m] [INCREMENT BY n]   -- défauts : 1 et 1
  [MINVALUE k] [MAXVALUE p] [CYCLE|NOCYCLE] [CACHE l|NOCACHE];
seq.NEXTVAL  -- génère + incrémente     seq.CURRVAL  -- valeur courante

7. Procédures · fonctions · curseurs

CREATE [OR REPLACE] PROCEDURE nom(
  p_1 IN     NUMBER DEFAULT 0,
  p_2 OUT    VARCHAR2,
  p_3 IN OUT NUMBER)
IS            -- types SANS taille !
  v client.nom%TYPE;
BEGIN … END;
CREATE [OR REPLACE] FUNCTION f(p IN NUMBER)
RETURN NUMBER IS BEGIN … RETURN v; END;

-- curseur explicite
CURSOR cur IS SELECT * FROM tbl;
v cur%ROWTYPE;
BEGIN
  OPEN cur;
  LOOP
    FETCH cur INTO v;
    EXIT WHEN cur%NOTFOUND;
    …
  END LOOP;
  CLOSE cur;
END;

FOR r IN cur LOOP … END LOOP;            -- FOR LOOP : open/fetch/close auto
FOR r IN (SELECT … FROM …) LOOP …        -- curseur implicite sur SELECT
CURSOR c(p NUMBER) IS SELECT … WHERE no=p;  OPEN c(12);     -- paramétré
SELECT col INTO v FROM … ;                  -- implicite (1 ligne attendue)
EXECUTE IMMEDIATE req INTO v USING param;  -- SQL dynamique

Transactions PL/SQL : COMMIT; · ROLLBACK [TO sp]; · SAVEPOINT sp; · SET TRANSACTION [READ ONLY] [NAME 'tr'];. Transaction autonome (log/audit indépendant) : PRAGMA AUTONOMOUS_TRANSACTION; + COMMIT; obligatoire. Une procédure stockée ne devrait pas commiter (laisser à l'appelant), sauf si autonome.

Fonctions natives (à connaître pour le problème) : || · SUBSTR(s,i,n) · LENGTH · TRIM · NVL(x,déf) · COALESCE · DECODE · TO_CHAR(d,'YYYY') · TO_DATE · TO_NUMBER · ROUND/TRUNC · NEXT_DAY · SYSDATE.

8. Exceptions & packages

EXCEPTION
  WHEN NO_DATA_FOUND THEN …  -- SELECT INTO vide (01403)
  WHEN TOO_MANY_ROWS THEN … -- INTO multi → curseur (01422)
  WHEN DUP_VAL_ON_INDEX THEN … -- doublon clé (00001)
  WHEN ZERO_DIVIDE THEN …
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE(SQLCODE||' '||SQLERRM);

-- header (signatures publiques)
CREATE [OR REPLACE] PACKAGE pkg IS
  c_PI CONSTANT NUMBER := 3.14;
  PROCEDURE logue(p IN VARCHAR2);
  FUNCTION  rech(id IN NUMBER) RETURN NUMBER;
END pkg;
-- body (implémentation + privé)
CREATE [OR REPLACE] PACKAGE BODY pkg IS
  PROCEDURE logue(p IN VARCHAR2) IS BEGIN … END;
END pkg;

★ Patterns d'examen — à écrire les yeux fermés

Le problème PL/SQL (~6 pts) tombe sur ces structures ; le problème BDDR (~6 pts) sur la recomposition de vues réparties.

CREATE OR REPLACE TRIGGER trig_id
BEFORE INSERT ON tbl FOR EACH ROW
BEGIN
  :new.id := seq_tbl.NEXTVAL;
END;

CREATE OR REPLACE TRIGGER trig_chk
BEFORE UPDATE OF qte ON tbl FOR EACH ROW
BEGIN
  IF :new.qte < 0 THEN
    RAISE_APPLICATION_ERROR(-20100, 'qte < 0');
  END IF;
END;

CREATE OR REPLACE TRIGGER trig_ca
AFTER INSERT ON commande FOR EACH ROW
BEGIN
  UPDATE client SET cumul = cumul + :new.total
  WHERE id = :new.id_client;
END;

CREATE OR REPLACE PROCEDURE liste(p_id IN NUMBER)
IS
  CURSOR c IS SELECT id, total FROM Commande
            WHERE id_client = p_id;
  v_nom Client.nom%TYPE;
BEGIN
  SELECT nom INTO v_nom FROM Client WHERE id=p_id;
  FOR r IN c LOOP
    DBMS_OUTPUT.PUT_LINE(r.id||' '||r.total);
  END LOOP;
EXCEPTION
  WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('introuvable');
END;

CREATE OR REPLACE TRIGGER trig_v
INSTEAD OF INSERT ON vue_jointe
DECLARE v_id parent.id%TYPE;
BEGIN
  BEGIN
    SELECT id INTO v_id FROM parent WHERE nom=:new.np;
  EXCEPTION WHEN NO_DATA_FOUND THEN
    INSERT INTO parent VALUES(seq.NEXTVAL, :new.np)
      RETURNING id INTO v_id;
  END;
  INSERT INTO enfant VALUES(seq2.NEXTVAL, :new.ne, v_id);
END;

🎯 Questions de cours — à ne pas rater

Notions conceptuelles les plus susceptibles en QCM / réflexion (les moins évidentes à retenir).