DDL · LMD · LID (SELECT) · Blocs PL/SQL · Curseurs · Sous-p

Oracle SQL & PL/SQL — Cours Complet | DDL · LMD · LID · PL/SQL · Curseurs · Triggers · Exceptions Page 1
 TABLE DES MATIÈRES
Chapitre 1 DDL : Tables, Contraintes, Vues, Index, Séquences, Synonymes
Chapitre 2 LMD : INSERT, UPDATE, DELETE, MERGE + Analyse IA
Chapitre 3 LID (SELECT) : Fonctions, Jointures, Sous-requêtes, Analytiques
Chapitre 4 Blocs Anonymes PL/SQL : Syntaxe, Types, Structures de contrôle
Chapitre 5 Curseurs : Explicites, Implicites, FOR LOOP, Paramétrés, REF CURSOR
Chapitre 6 Sous-programmes : Procédures, Fonctions, Packages
Chapitre 7 Triggers : DML, INSTEAD OF, DDL, Système, Audit
Chapitre 8 Exceptions : Prédéfinies, Définies, PRAGMA, RAISE_APPLICATION_ERROR
QCM 50 Questions à Choix Multiple avec corrigé
EXAMEN Examen Final 1h30 — 4 parties, 80 points, corrigé complet

Oracle SQL & PL/SQL — Cours Complet | DDL · LMD · LID · PL/SQL · Curseurs · Triggers · Exceptions Page 2
 Chap. 1 Langage de Définition de Données (DDL)
Créer et gérer les objets de la base : tables, contraintes, vues, index, séquences, synonymes.

1.1 Types de données Oracle

Type Catégorie Description

VARCHAR2(n) Chaîne Variable, max 4000 car. — nom VARCHAR2(100)

CHAR(n) Chaîne Longueur fixe — genre CHAR(1)

NUMBER(p,s) Numérique p chiffres, s décimales — salaire NUMBER(10,2)

DATE Date Date + heure (DD/MM/YY HH:MI:SS)

TIMESTAMP Date Précision sous-secondes

CLOB LOB Gros texte jusqu'à 4 Go

BOOLEAN PL/SQL TRUE / FALSE / NULL (pas en SQL pur)

1.2 CREATE TABLE

Chaque colonne est définie avec un nom, un type et des contraintes. On distingue les contraintes inline (sur la colonne) et
out-of-line (en fin de table pour les contraintes multi-colonnes).

■ Exemple : Table EMPLOYE avec toutes les contraintes

CREATE TABLE employe (
emp_id NUMBER(6)
CONSTRAINT pk_emp PRIMARY KEY,
nom VARCHAR2(50) NOT NULL,
prenom VARCHAR2(50) NOT NULL,
email VARCHAR2(100)
CONSTRAINT uq_emp_email UNIQUE,
salaire NUMBER(10,2)
CONSTRAINT ck_emp_sal CHECK (salaire > 0),
dept_id NUMBER(4)
CONSTRAINT fk_emp_dept
REFERENCES departement(dept_id) ON DELETE SET NULL,
manager_id NUMBER(6) REFERENCES employe(emp_id),
date_emb DATE DEFAULT SYSDATE,
statut CHAR(1) DEFAULT 'A'
CONSTRAINT ck_emp_statut CHECK (statut IN ('A','I','P'))
);

1.3 Contraintes d'intégrité

Contrainte Rôle Exemple

PRIMARY KEY Clé unique + NOT NULL CONSTRAINT pk_emp PRIMARY KEY

NOT NULL Valeur obligatoire nom VARCHAR2(50) NOT NULL

UNIQUE Valeur unique (NULL admis) email VARCHAR2 UNIQUE

CHECK Condition booléenne CHECK (salaire > 0)

FOREIGN KEY Référence externe REFERENCES dept(dept_id) ON DELETE SET NULL

DEFAULT Valeur par défaut date_emb DATE DEFAULT SYSDATE

■ ON DELETE CASCADE supprime les lignes enfants. ON DELETE SET NULL met la FK a NULL.

1.4 ALTER TABLE

■ Exemple : Ajouter, modifier, supprimer colonnes et contraintes
ALTER TABLE employe ADD telephone VARCHAR2(15);
ALTER TABLE employe MODIFY nom VARCHAR2(80) NOT NULL;
ALTER TABLE employe DROP COLUMN telephone;

Oracle SQL & PL/SQL — Cours Complet | DDL · LMD · LID · PL/SQL · Curseurs · Triggers · Exceptions Page 3
 ALTER TABLE employe RENAME COLUMN nom TO nom_famille;
ALTER TABLE employe ADD CONSTRAINT ck_email CHECK (email LIKE '%@%.%');
ALTER TABLE employe DISABLE CONSTRAINT pk_emp CASCADE;
ALTER TABLE employe ENABLE CONSTRAINT pk_emp;
ALTER TABLE employe DROP CONSTRAINT ck_email;

1.5 CREATE TABLE AS SELECT

■ Exemple : Copier structure et/ou donnees
-- Copie complete (structure + donnees)
CREATE TABLE employe_2024 AS SELECT * FROM employe WHERE EXTRACT(YEAR FROM date_emb)=2024;

-- Structure seule (condition toujours fausse)

CREATE TABLE employe_vide AS SELECT * FROM employe WHERE 1=2;

-- Copie avec colonnes calculees

CREATE TABLE rapport_sal AS
SELECT emp_id, nom||' '||prenom AS nom_complet,
salaire AS mensuel, salaire*12 AS annuel
FROM employe WHERE statut='A';
■ Les contraintes PK/FK/CHECK ne sont pas copiees. Seul NOT NULL peut l'etre.

1.6 Vues (VIEW)

■ Exemple : Vue simple, complexe et WITH CHECK OPTION
-- Vue simple (modifiable)
CREATE OR REPLACE VIEW v_employes_actifs AS
SELECT emp_id, nom, prenom, salaire, dept_id FROM employe
WHERE statut='A'
WITH CHECK OPTION; -- empeche d'inserer statut != 'A' via la vue

-- Vue complexe avec fonction analytique (lecture seule)

CREATE OR REPLACE VIEW v_emp_dept AS
SELECT e.emp_id, [Link], [Link], d.nom_dept, [Link],
ROUND([Link]/AVG([Link]) OVER (PARTITION BY e.dept_id)*100,1) pct_moy
FROM employe e JOIN departement d ON e.dept_id=d.dept_id
WHERE [Link]='A';

DROP VIEW v_employes_actifs;

1.7 Index
■ Exemple : Types d'index : simple, composite, fonctionnel, invisible
CREATE INDEX idx_emp_nom ON employe(nom);
CREATE INDEX idx_emp_dept_sal ON employe(dept_id, salaire DESC);
CREATE UNIQUE INDEX idx_emp_email ON employe(email);
CREATE INDEX idx_emp_nom_upper ON employe(UPPER(nom)); -- basé fonction
CREATE INDEX idx_test ON employe(statut) INVISIBLE;
ALTER INDEX idx_test VISIBLE;
ALTER INDEX idx_emp_nom REBUILD; -- reconstruire si fragmente
DROP INDEX idx_emp_nom;

1.8 Sequences
■ Exemple : Creer, utiliser, modifier, supprimer une sequence
CREATE SEQUENCE seq_emp
START WITH 100 INCREMENT BY 1 MAXVALUE 999999 NOCYCLE CACHE 20;

INSERT INTO employe(emp_id, nom, prenom, salaire, dept_id)

VALUES (seq_emp.NEXTVAL, 'Ben Salah', 'Omar', 3800, 10);

SELECT seq_emp.CURRVAL FROM DUAL; -- valeur courante (meme session)

Oracle SQL & PL/SQL — Cours Complet | DDL · LMD · LID · PL/SQL · Curseurs · Triggers · Exceptions Page 4
 ALTER SEQUENCE seq_emp INCREMENT BY 5;
DROP SEQUENCE seq_emp;

-- Oracle 12c+ : IDENTITY (auto-increment)

-- emp_id NUMBER GENERATED ALWAYS AS IDENTITY

1.9 Synonymes
■ Exemple : Synonymes prives et publics
CREATE SYNONYM emp FOR employe; -- prive (meme utilisateur)
CREATE PUBLIC SYNONYM dept FOR [Link]; -- tous les utilisateurs
CREATE SYNONYM emp_remote FOR employe@db_paris; -- objet distant
DROP SYNONYM emp;
DROP PUBLIC SYNONYM dept;

Oracle SQL & PL/SQL — Cours Complet | DDL · LMD · LID · PL/SQL · Curseurs · Triggers · Exceptions Page 5
 Chap. 2 LMD — Manipulation Intelligente des Données
INSERT, UPDATE, DELETE, MERGE avec analyse de performance et détection des requêtes dangereuses.

2.1 INSERT
■ Exemple : Insertion explicite, implicite, multiple (INSERT ALL)
-- Explicite (recommande)
INSERT INTO employe(emp_id,nom,prenom,email,salaire,dept_id)
VALUES (seq_emp.NEXTVAL,'Gharbi','Sami','sami@[Link]',4200,20);

-- Via SELECT (insertion multiple)

INSERT INTO employe_backup
SELECT emp_id,nom,prenom,email,salaire,dept_id,date_emb,statut
FROM employe WHERE date_emb < ADD_MONTHS(SYSDATE,-12);

-- INSERT ALL conditionnel

INSERT ALL
WHEN salaire < 3000 THEN INTO employe_junior VALUES(emp_id,nom,prenom,salaire,dept_id)
WHEN salaire BETWEEN 3000 AND 7000 THEN INTO employe_senior VALUES(emp_id,nom,prenom,salaire,dept_i
d)
ELSE INTO employe_cadre VALUES(emp_id,nom,prenom,salaire,dept_id)
SELECT emp_id,nom,prenom,salaire,dept_id FROM employe WHERE statut='A';

2.2 UPDATE
■ Exemple : Simple, multi-colonnes et avec sous-requete
-- Simple
UPDATE employe SET salaire=5000 WHERE emp_id=101;

-- Multi-colonnes
UPDATE employe SET salaire=salaire*1.10, statut='A', date_emb=SYSDATE WHERE emp_id=102;

-- Sous-requete correlee (augmenter selon budget du dept)

UPDATE employe e
SET salaire = salaire * 1.08
WHERE dept_id IN (SELECT dept_id FROM departement WHERE budget > 100000);

-- UPDATE avec EXISTS

UPDATE employe e SET statut='P'
WHERE EXISTS (SELECT 1 FROM participe p WHERE p.emp_id=e.emp_id AND p.nb_heures>200);

■ UPDATE sans WHERE modifie TOUTES les lignes ! Toujours tester avec SELECT d'abord.

2.3 DELETE
■ Exemple : Suppression simple et avec sous-requete
DELETE FROM employe WHERE statut='I' AND date_emb < ADD_MONTHS(SYSDATE,-36);

DELETE FROM employe

WHERE dept_id NOT IN (SELECT dept_id FROM departement WHERE budget > 0);

-- DELETE vs TRUNCATE
DELETE FROM employe_log; -- annulable (DML), lent sur grande table
TRUNCATE TABLE employe_log; -- NON annulable (DDL), tres rapide

■ DELETE sans WHERE supprime TOUT ! TRUNCATE ne peut pas etre annule par ROLLBACK.

2.4 MERGE (UPSERT)

■ Exemple : Inserer ou mettre a jour selon l'existence
MERGE INTO employe tgt
USING (SELECT emp_id, nom, prenom, salaire FROM employe_tmp) src
ON (tgt.emp_id = src.emp_id)

Oracle SQL & PL/SQL — Cours Complet | DDL · LMD · LID · PL/SQL · Curseurs · Triggers · Exceptions Page 6
 WHEN MATCHED THEN
UPDATE SET [Link]=[Link], [Link]=[Link]
WHERE [Link] <> [Link]
WHEN NOT MATCHED THEN
INSERT(emp_id,nom,prenom,salaire,dept_id,statut)
VALUES(src.emp_id,[Link],[Link],[Link],10,'A');

2.5 Analyse IA — Detection des requetes couteuses

Detection des requetes dangereuses et optimisation
UPDATE/DELETE sans WHERE => toutes les lignes affectees !
INSERT en boucle PL/SQL => preferer FORALL ou INSERT...SELECT (10x-100x plus rapide)
Utiliser EXPLAIN PLAN + DBMS_XPLAN.DISPLAY pour analyser le cout d'execution
BULK COLLECT + FORALL : reduit les allers-retours SQL/PL/SQL et améliore les perfs massivement

■ Exemple : EXPLAIN PLAN + FORALL (optimisation masse)

-- Analyser le cout
EXPLAIN PLAN FOR UPDATE employe SET salaire=salaire*1.05 WHERE dept_id=10;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

-- Optimisation avec FORALL

DECLARE
TYPE t_ids IS TABLE OF employe.emp_id%TYPE;
TYPE t_sals IS TABLE OF [Link]%TYPE;
v_ids t_ids; v_sals t_sals;
BEGIN
SELECT emp_id, ROUND(salaire*1.10,2)
BULK COLLECT INTO v_ids, v_sals
FROM employe WHERE dept_id=10;

FORALL i IN v_ids.FIRST..v_ids.LAST
UPDATE employe SET salaire=v_sals(i) WHERE emp_id=v_ids(i);
COMMIT;
DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT || ' employes mis a jour.');
END;
/

Oracle SQL & PL/SQL — Cours Complet | DDL · LMD · LID · PL/SQL · Curseurs · Triggers · Exceptions Page 7
 Chap. 3 Langage d'Interrogation de Données (SELECT)
Restriction, tri, fonctions, jointures, sous-requêtes, opérateurs ensemblistes, fonctions analytiques.

3.1 Syntaxe complète SELECT

■ Exemple : Structure d'un SELECT
SELECT [DISTINCT] colonne, expression AS alias
FROM table [alias] [JOIN ... ON ...]
WHERE condition_ligne
GROUP BY colonne
HAVING condition_groupe
ORDER BY colonne [ASC|DESC] [NULLS FIRST|LAST];

■ Exemple : DISTINCT, WHERE, ORDER BY, NULLS LAST

SELECT DISTINCT dept_id FROM employe ORDER BY dept_id;

SELECT nom, salaire, NVL(bonus,0) bonus

FROM employe
WHERE statut='A' AND salaire BETWEEN 2000 AND 8000
ORDER BY salaire DESC NULLS LAST, nom ASC;

3.2 Opérateurs de comparaison

Op&#233;rateur Description Exemple

= / != / <> Egal / Diff&#233;rent WHERE statut='A'

< <= > >= Comparaison WHERE salaire>=3000

BETWEEN a AND b Intervalle ferm&#233; WHERE sal BETWEEN 2000 AND 5000

IN (v1,v2,...) Liste de valeurs WHERE dept_id IN (10,20,30)

LIKE 'motif' % = n car., _ = 1 car. WHERE nom LIKE 'B%'

IS NULL / IS NOT NULL Test NULL WHERE bonus IS NULL

NOT + op&#233;rateur N&#233;gation WHERE dept_id NOT IN (40,50)

3.3 Fonctions numériques

■ Exemple : ROUND, TRUNC, MOD, CEIL, FLOOR, POWER, SQRT
SELECT ROUND(3567.456, 2) FROM DUAL; -- 3567.46
SELECT ROUND(3567.456,-2) FROM DUAL; -- 3600
SELECT TRUNC(3567.456, 2) FROM DUAL; -- 3567.45
SELECT MOD(17, 5) FROM DUAL; -- 2
SELECT CEIL(3.01) FROM DUAL; -- 4
SELECT FLOOR(3.99) FROM DUAL; -- 3
SELECT ABS(-123) FROM DUAL; -- 123
SELECT POWER(2, 8) FROM DUAL; -- 256
SELECT SQRT(225) FROM DUAL; -- 15
SELECT GREATEST(3,7,2,9,5) FROM DUAL; -- 9
SELECT LEAST(3,7,2,9,5) FROM DUAL; -- 2

3.4 Fonctions de chaînes

■ Exemple : UPPER, LOWER, SUBSTR, LENGTH, INSTR, REPLACE, LPAD...
SELECT UPPER('oracle sql') FROM DUAL; -- ORACLE SQL
SELECT LOWER('ORACLE SQL') FROM DUAL; -- oracle sql
SELECT INITCAP('bon jour') FROM DUAL; -- Bon Jour
SELECT LENGTH('Oracle') FROM DUAL; -- 6
SELECT SUBSTR('Oracle SQL',8,3) FROM DUAL; -- SQL
SELECT INSTR('Oracle SQL','SQL') FROM DUAL; -- 8
SELECT LPAD('42',6,'0') FROM DUAL; -- 000042
SELECT RPAD('OK',5,'-') FROM DUAL; -- OK---

Oracle SQL & PL/SQL — Cours Complet | DDL · LMD · LID · PL/SQL · Curseurs · Triggers · Exceptions Page 8
 SELECT TRIM(' espace ') FROM DUAL; -- espace
SELECT REPLACE('abcabc','a','X') FROM DUAL; -- XbcXbc
SELECT TRANSLATE('SQL','SL','sl') FROM DUAL; -- sQl

3.5 Fonctions de date

■ Exemple : SYSDATE, ADD_MONTHS, MONTHS_BETWEEN, LAST_DAY, TO_CHAR...
SELECT SYSDATE FROM DUAL;
SELECT ADD_MONTHS(SYSDATE, 3) FROM DUAL;
SELECT MONTHS_BETWEEN(SYSDATE, date_emb) FROM employe;
SELECT NEXT_DAY(SYSDATE,'MONDAY') FROM DUAL;
SELECT LAST_DAY(SYSDATE) FROM DUAL;
SELECT TRUNC(SYSDATE,'MONTH') FROM DUAL; -- 1er du mois
SELECT EXTRACT(YEAR FROM SYSDATE) FROM DUAL;
SELECT TO_CHAR(SYSDATE,'DD/MM/YYYY HH24:MI:SS') FROM DUAL;
SELECT TO_DATE('15/06/2024','DD/MM/YYYY') FROM DUAL;

3.6 Fonctions de conversion & valeurs NULL

■ Exemple : TO_CHAR, TO_NUMBER, NVL, NVL2, COALESCE, NULLIF, DECODE, CASE
SELECT TO_CHAR(salaire,'999G999D99') FROM employe;
SELECT TO_NUMBER('3.14') FROM DUAL;
SELECT NVL(bonus, 0) FROM employe;
SELECT NVL2(bonus,'Avec bonus','Sans bonus') FROM employe;
SELECT COALESCE(bonus, commission, 0) FROM employe;
SELECT NULLIF(dept_id, 0) FROM employe;

SELECT DECODE(statut,'A','Actif','I','Inactif','P','Probatoire','?')
FROM employe;

SELECT nom,
CASE WHEN salaire < 2000 THEN 'Bas'
WHEN salaire < 5000 THEN 'Moyen'
ELSE 'Eleve' END AS tranche
FROM employe;

3.7 Fonctions de groupe

■ Exemple : COUNT, SUM, AVG, MIN, MAX, GROUP BY, HAVING, ROLLUP, LISTAGG
SELECT dept_id,
COUNT(*) nb_emp,
COUNT(bonus) nb_avec_bonus,
SUM(salaire) masse_sal,
ROUND(AVG(salaire),2) moy_sal,
MIN(salaire) min_sal,
MAX(salaire) max_sal,
LISTAGG(nom,', ') WITHIN GROUP(ORDER BY nom) AS noms
FROM employe
WHERE statut='A'
GROUP BY dept_id
HAVING AVG(salaire) > 3000
ORDER BY masse_sal DESC;

-- ROLLUP : sous-totaux hierarchiques

SELECT dept_id, statut, SUM(salaire)
FROM employe GROUP BY ROLLUP(dept_id, statut);

-- CUBE : toutes les combinaisons

SELECT dept_id, statut, SUM(salaire)
FROM employe GROUP BY CUBE(dept_id, statut);

3.8 Jointures

Oracle SQL & PL/SQL — Cours Complet | DDL · LMD · LID · PL/SQL · Curseurs · Triggers · Exceptions Page 9
 ■ Exemple : INNER, LEFT, RIGHT, FULL, SELF, CROSS JOIN
-- INNER JOIN
SELECT [Link], d.nom_dept
FROM employe e JOIN departement d ON e.dept_id=d.dept_id;

-- LEFT OUTER JOIN (tous les depts, meme sans employes)

SELECT d.nom_dept, COUNT(e.emp_id) nb
FROM departement d LEFT JOIN employe e ON d.dept_id=e.dept_id
GROUP BY d.nom_dept;

-- SELF JOIN (manager de l'employe)

SELECT [Link] AS employe, [Link] AS manager
FROM employe e LEFT JOIN employe m ON e.manager_id=m.emp_id;

-- Jointure sur 3 tables

SELECT [Link], d.nom_dept, [Link], pa.nb_heures
FROM employe e
JOIN departement d ON e.dept_id=d.dept_id
JOIN participe pa ON e.emp_id=pa.emp_id
JOIN projet p ON pa.proj_id=p.proj_id
WHERE pa.nb_heures > 20;

3.9 Sous-requêtes & Opérateurs ensemblistes

■ Exemple : Scalaire, correlee, EXISTS, IN, ANY, ALL, UNION, INTERSECT, MINUS
-- Scalaire
SELECT nom, salaire FROM employe
WHERE salaire=(SELECT MAX(salaire) FROM employe);

-- Correlee : employes au-dessus de la moyenne du dept

SELECT nom, salaire FROM employe e
WHERE salaire > (SELECT AVG(salaire) FROM employe WHERE dept_id=e.dept_id);

-- EXISTS (plus rapide que IN sur grandes tables)

SELECT nom FROM departement d
WHERE EXISTS (SELECT 1 FROM employe e WHERE e.dept_id=d.dept_id AND [Link]>8000);

-- Vue inline (sous-requete dans FROM)

SELECT d.nom_dept, [Link]
FROM departement d
JOIN (SELECT dept_id, AVG(salaire) moy FROM employe GROUP BY dept_id) s
ON d.dept_id=s.dept_id;

-- Operateurs ensemblistes
SELECT emp_id FROM employe WHERE dept_id=10
UNION
SELECT emp_id FROM employe WHERE statut='P';

SELECT emp_id FROM equipe_a INTERSECT SELECT emp_id FROM equipe_b;

SELECT emp_id FROM employe MINUS SELECT emp_id FROM employe_backup;

3.10 Fonctions analytiques (fenetres)

■ Exemple : ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD, SUM OVER
SELECT emp_id, nom, salaire, dept_id,
ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salaire DESC) rang_dept,
RANK() OVER (ORDER BY salaire DESC) rang_global,
DENSE_RANK() OVER (PARTITION BY dept_id ORDER BY salaire DESC) rang_dense,
LAG(salaire,1,0) OVER (PARTITION BY dept_id ORDER BY salaire) sal_prec,
LEAD(salaire,1,0) OVER (PARTITION BY dept_id ORDER BY salaire) sal_suiv,
SUM(salaire) OVER (PARTITION BY dept_id) total_dept,
ROUND(salaire/SUM(salaire) OVER (PARTITION BY dept_id)*100,1) pct_dept

Oracle SQL & PL/SQL — Cours Complet | DDL · LMD · LID · PL/SQL · Curseurs · Triggers · Exceptions Page 10
 FROM employe WHERE statut='A'
ORDER BY dept_id, rang_dept;

Oracle SQL & PL/SQL — Cours Complet | DDL · LMD · LID · PL/SQL · Curseurs · Triggers · Exceptions Page 11
 Chap. 4 Structure d'un Bloc Anonyme PL/SQL
Syntaxe, variables, types, structures de contrôle et blocs imbriqués.

4.1 Architecture d'un bloc PL/SQL

■ Exemple : Bloc anonyme complet
DECLARE
-- Constante
c_taux_ir CONSTANT NUMBER := 0.25;
-- Variables simples
v_nom VARCHAR2(50);
v_salaire NUMBER(10,2) := 0;
-- Ancrees au schema
v_emp_row employe%ROWTYPE;
v_dept_id employe.dept_id%TYPE;
-- RECORD personnalise
TYPE t_fiche IS RECORD (nom_complet VARCHAR2(100), sal_net NUMBER);
v_fiche t_fiche;
-- Tableau associe
TYPE t_noms IS TABLE OF VARCHAR2(50) INDEX BY PLS_INTEGER;
v_noms t_noms;
BEGIN
SELECT * INTO v_emp_row FROM employe WHERE emp_id=101;
v_fiche.nom_complet := v_emp_row.nom || ' ' || v_emp_row.prenom;
v_fiche.sal_net := v_emp_row.salaire * (1 - c_taux_ir);

v_noms(1):='Alice'; v_noms(2):='Bob';
FOR i IN v_noms.FIRST..v_noms.LAST LOOP
DBMS_OUTPUT.PUT_LINE(v_noms(i));
END LOOP;
DBMS_OUTPUT.PUT_LINE(v_fiche.nom_complet||' - '||v_fiche.sal_net);
EXCEPTION
WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('Employe 101 inexistant.');
WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
/

4.2 Types de donnees PL/SQL

Type Declaration Utilisation

Scalaire v_x NUMBER(8,2) := 0; Valeur simple

%TYPE v_s [Link]%TYPE; Ancre au type d'une colonne

%ROWTYPE v_r emp%ROWTYPE; Ligne entiere d'une table

RECORD TYPE t IS RECORD(a NUMBER, b VARCHAR2);

Structure perso

TABLE INDEX BY TYPE t IS TABLE OF NUMBER INDEX BY PLS_INTEGER;

Tableau dynamique

VARRAY TYPE t IS VARRAY(10) OF VARCHAR2(50);Tableau taille fixe

BOOLEAN v_ok BOOLEAN := TRUE; TRUE / FALSE / NULL

REF CURSOR TYPE t IS REF CURSOR; Curseur variable

4.3 Structures de controle

■ Exemple : IF/ELSIF/ELSE, CASE, LOOP, WHILE, FOR
-- IF / ELSIF / ELSE
IF v_salaire >= 10000 THEN v_cat := 'Dirigeant';
ELSIF v_salaire >= 6000 THEN v_cat := 'Cadre';
ELSIF v_salaire >= 3000 THEN v_cat := 'Technicien';
ELSE v_cat := 'Operateur';

Oracle SQL & PL/SQL — Cours Complet | DDL · LMD · LID · PL/SQL · Curseurs · Triggers · Exceptions Page 12
 END IF;

-- CASE cherche
v_msg := CASE v_statut
WHEN 'A' THEN 'Actif' WHEN 'I' THEN 'Inactif'
WHEN 'P' THEN 'Probatoire' ELSE 'Inconnu'
END;

-- LOOP avec EXIT WHEN

v_i:=1; v_sum:=0;
LOOP
v_sum := v_sum + v_i; v_i := v_i + 1;
EXIT WHEN v_i > 100;
END LOOP;
DBMS_OUTPUT.PUT_LINE('Somme = '||v_sum); -- 5050

-- WHILE
WHILE v_solde > 0 LOOP v_solde:=v_solde-v_mens; v_nb:=v_nb+1; END LOOP;

-- FOR numerique
FOR i IN REVERSE 1..5 LOOP
DBMS_OUTPUT.PUT_LINE('Rebours : '||i);
END LOOP;

4.4 Blocs imbriqués

■ Exemple : Portee des variables et blocs etiquetes
DECLARE
v_global VARCHAR2(20) := 'GLOBAL';
v_x NUMBER := 10;
BEGIN
DBMS_OUTPUT.PUT_LINE(v_global); -- GLOBAL

<>
DECLARE
v_local VARCHAR2(20) := 'LOCAL';
v_x NUMBER := 99; -- masque le v_x externe
BEGIN
DBMS_OUTPUT.PUT_LINE(v_local); -- LOCAL
DBMS_OUTPUT.PUT_LINE(v_x); -- 99
EXCEPTION
WHEN OTHERS THEN NULL;
END bloc_interne;

DBMS_OUTPUT.PUT_LINE(v_global); -- GLOBAL (v_local inaccessible)

DBMS_OUTPUT.PUT_LINE(v_x); -- 10
END;
/

Oracle SQL & PL/SQL — Cours Complet | DDL · LMD · LID · PL/SQL · Curseurs · Triggers · Exceptions Page 13
 Chap. 5 Les Curseurs PL/SQL
Curseurs explicites, implicites, FOR LOOP, paramétrés et REF CURSOR.

5.1 Curseur explicite — cycle complet

■ Exemple : DECLARE, OPEN, FETCH, CLOSE + attributs
DECLARE
CURSOR c_emp IS
SELECT emp_id, nom, prenom, salaire
FROM employe WHERE statut='A' ORDER BY salaire DESC;
v_rec c_emp%ROWTYPE;
v_total NUMBER := 0;
BEGIN
IF NOT c_emp%ISOPEN THEN OPEN c_emp; END IF;

LOOP
FETCH c_emp INTO v_rec;
EXIT WHEN c_emp%NOTFOUND;
v_total := v_total + v_rec.salaire;
DBMS_OUTPUT.PUT_LINE(LPAD(c_emp%ROWCOUNT,4)||'. '||
RPAD(v_rec.nom,20)||v_rec.salaire);
END LOOP;

DBMS_OUTPUT.PUT_LINE('Total lignes : '||c_emp%ROWCOUNT);

DBMS_OUTPUT.PUT_LINE('Masse sal. : '||v_total);
CLOSE c_emp;
END;
/

5.2 Attributs de curseur

Attribut Signification

%FOUND TRUE si le dernier FETCH a retourne une ligne

%NOTFOUND TRUE si le dernier FETCH n'a rien retourne (fin du curseur)

%ROWCOUNT Nombre de lignes lues depuis l'ouverture

%ISOPEN TRUE si le curseur est actuellement ouvert

5.3 Curseur FOR LOOP (recommande)

■ Exemple : FOR LOOP sur curseur declare et inline
-- FOR LOOP sur curseur declare (OPEN/FETCH/CLOSE automatiques)
DECLARE
CURSOR c_dept IS
SELECT dept_id, nom_dept FROM departement ORDER BY dept_id;
BEGIN
FOR rec IN c_dept LOOP
DBMS_OUTPUT.PUT_LINE(rec.dept_id||' - '||rec.nom_dept);
END LOOP;
END;
/

-- FOR LOOP avec curseur inline (encore plus concis)

BEGIN
FOR rec IN (SELECT nom, salaire FROM employe
WHERE dept_id=10 ORDER BY salaire DESC) LOOP
DBMS_OUTPUT.PUT_LINE([Link]||' : '||[Link]);
END LOOP;
END;
/

Oracle SQL & PL/SQL — Cours Complet | DDL · LMD · LID · PL/SQL · Curseurs · Triggers · Exceptions Page 14
 5.4 Curseur implicite SQL%
■ Exemple : Attributs SQL%FOUND, SQL%NOTFOUND, SQL%ROWCOUNT
BEGIN
UPDATE employe SET salaire=salaire*1.08 WHERE dept_id=20;

IF SQL%FOUND THEN
DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT||' employe(s) augmente(s).');
ELSE
DBMS_OUTPUT.PUT_LINE('Aucun employe dans ce departement.');
END IF;

DELETE FROM employe_log WHERE log_date < SYSDATE-90;

IF SQL%ROWCOUNT > 0 THEN
DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT||' log(s) purges.');
END IF;
COMMIT;
END;
/

5.5 Curseur paramétré

■ Exemple : Curseur avec parametres et valeurs par defaut
DECLARE
CURSOR c_emp_dept (
p_dept NUMBER,
p_sal_min NUMBER DEFAULT 0,
p_statut CHAR DEFAULT 'A'
) IS
SELECT emp_id, nom, prenom, salaire
FROM employe
WHERE dept_id=p_dept AND salaire>=p_sal_min AND statut=p_statut
ORDER BY salaire DESC;
BEGIN
DBMS_OUTPUT.PUT_LINE('=== Dept 10 (sal >= 3000) ===');
FOR rec IN c_emp_dept(10, 3000) LOOP
DBMS_OUTPUT.PUT_LINE(' '||[Link]||' : '||[Link]);
END LOOP;

DBMS_OUTPUT.PUT_LINE('=== Dept 20 (sal >= 4500) ===');

FOR rec IN c_emp_dept(20, 4500) LOOP
DBMS_OUTPUT.PUT_LINE(' '||[Link]||' : '||[Link]);
END LOOP;
END;
/

5.6 REF CURSOR (curseur variable)

■ Exemple : REF CURSOR faible : requête determinee a l'execution
DECLARE
TYPE t_cursor IS REF CURSOR; -- faible (pas de structure fixee)
v_cur t_cursor;
v_rec employe%ROWTYPE;
v_dept NUMBER := 10;
BEGIN
IF v_dept IS NOT NULL THEN
OPEN v_cur FOR SELECT * FROM employe WHERE dept_id=v_dept;
ELSE
OPEN v_cur FOR SELECT * FROM employe;
END IF;

LOOP
FETCH v_cur INTO v_rec;

Oracle SQL & PL/SQL — Cours Complet | DDL · LMD · LID · PL/SQL · Curseurs · Triggers · Exceptions Page 15
 EXIT WHEN v_cur%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(v_rec.nom||' - '||v_rec.salaire);
END LOOP;
CLOSE v_cur;
END;
/

Oracle SQL & PL/SQL — Cours Complet | DDL · LMD · LID · PL/SQL · Curseurs · Triggers · Exceptions Page 16
 Sous-programmes : Procédures, Fonctions &
Chap. 6
Packages
Procédures stockées, fonctions, surcharge, packages — curseurs dans sous-programmes.

6.1 Procédures stockées

Une procédure est un bloc PL/SQL nommé et stocké en base. Paramètres : IN (entrée), OUT (sortie), IN OUT (les deux).

■ Exemple : Procedure GERER_EMPLOYE avec IN, OUT, IN OUT

CREATE OR REPLACE PROCEDURE gerer_employe (
p_action IN VARCHAR2,
p_emp_id IN OUT employe.emp_id%TYPE,
p_nom IN [Link]%TYPE DEFAULT NULL,
p_salaire IN [Link]%TYPE DEFAULT NULL,
p_dept_id IN employe.dept_id%TYPE DEFAULT NULL,
p_message OUT VARCHAR2
) AS
BEGIN
CASE UPPER(p_action)
WHEN 'CREATE' THEN
SELECT seq_emp.NEXTVAL INTO p_emp_id FROM DUAL;
INSERT INTO employe(emp_id,nom,salaire,dept_id,statut)
VALUES(p_emp_id,p_nom,NVL(p_salaire,2500),NVL(p_dept_id,10),'A');
p_message := 'Employe '||p_emp_id||' cree.';
WHEN 'UPDATE' THEN
UPDATE employe
SET nom=NVL(p_nom,nom), salaire=NVL(p_salaire,salaire), dept_id=NVL(p_dept_id,dept_id)
WHERE emp_id=p_emp_id;
IF SQL%NOTFOUND THEN
RAISE_APPLICATION_ERROR(-20001,'Employe '||p_emp_id||' introuvable.');
END IF;
p_message := SQL%ROWCOUNT||' ligne(s) mise(s) a jour.';
WHEN 'DELETE' THEN
DELETE FROM employe WHERE emp_id=p_emp_id;
p_message := 'Employe supprime.';
ELSE
RAISE_APPLICATION_ERROR(-20002,'Action inconnue : '||p_action);
END CASE;
COMMIT;
EXCEPTION
WHEN OTHERS THEN ROLLBACK; p_message:='ERREUR ['||SQLCODE||'] : '||SQLERRM;
END;
/

-- Appel
DECLARE v_id NUMBER; v_msg VARCHAR2(200); BEGIN
gerer_employe('CREATE',v_id,'Mrad',4500,20,v_msg);
DBMS_OUTPUT.PUT_LINE(v_msg||' ID='||v_id);
END;
/

6.2 Fonctions stockées

■ Exemple : Fonction GRADE_SALAIRE utilisable dans SELECT
CREATE OR REPLACE FUNCTION grade_salaire (
p_emp_id IN employe.emp_id%TYPE
) RETURN VARCHAR2 DETERMINISTIC
IS
v_sal NUMBER; v_moy NUMBER;
BEGIN
SELECT [Link], AVG([Link])

Oracle SQL & PL/SQL — Cours Complet | DDL · LMD · LID · PL/SQL · Curseurs · Triggers · Exceptions Page 17
 INTO v_sal, v_moy
FROM employe e JOIN employe e2 ON e2.dept_id=e.dept_id
WHERE e.emp_id=p_emp_id GROUP BY [Link];

RETURN CASE
WHEN v_sal >= v_moy*1.20 THEN 'Au-dessus (+20%)'
WHEN v_sal >= v_moy*0.90 THEN 'Dans la moyenne'
ELSE 'En-dessous'
END;
EXCEPTION
WHEN NO_DATA_FOUND THEN RETURN 'Inconnu';
END;
/

SELECT emp_id, nom, salaire, grade_salaire(emp_id) AS grade

FROM employe WHERE dept_id=10;

6.3 Packages (spec + corps)

■ Exemple : Package PKG_RH : specification et corps complets
-- SPECIFICATION (interface publique)
CREATE OR REPLACE PACKAGE pkg_rh AS
c_sal_min CONSTANT NUMBER := 1000;
g_nb_ops NUMBER := 0;
PROCEDURE augmenter_salaire(p_dept_id NUMBER, p_pct NUMBER DEFAULT 5);
FUNCTION masse_salariale (p_dept_id NUMBER) RETURN NUMBER;
FUNCTION nb_employes (p_dept_id NUMBER) RETURN NUMBER;
PROCEDURE afficher_rapport (p_dept_id NUMBER);
PROCEDURE chercher(p_emp_id NUMBER); -- surcharge 1
PROCEDURE chercher(p_nom VARCHAR2); -- surcharge 2
END pkg_rh;
/

-- CORPS (implementation)
CREATE OR REPLACE PACKAGE BODY pkg_rh AS
PROCEDURE log_op(p_msg VARCHAR2) IS -- procedure privee
BEGIN g_nb_ops:=g_nb_ops+1; DBMS_OUTPUT.PUT_LINE('[LOG#'||g_nb_ops||'] '||p_msg); END;

PROCEDURE augmenter_salaire(p_dept_id NUMBER, p_pct NUMBER DEFAULT 5) IS

BEGIN
UPDATE employe SET salaire=GREATEST(salaire*(1+p_pct/100),c_sal_min) WHERE dept_id=p_dept_id;
log_op('Augmentation dept '||p_dept_id||' : '||SQL%ROWCOUNT||' emp.'); COMMIT;
END;

FUNCTION masse_salariale(p_dept_id NUMBER) RETURN NUMBER IS v NUMBER; BEGIN

SELECT NVL(SUM(salaire),0) INTO v FROM employe WHERE dept_id=p_dept_id AND statut='A'; RETURN v
; END;

FUNCTION nb_employes(p_dept_id NUMBER) RETURN NUMBER IS v NUMBER; BEGIN

SELECT COUNT(*) INTO v FROM employe WHERE dept_id=p_dept_id; RETURN v; END;

PROCEDURE afficher_rapport(p_dept_id NUMBER) IS BEGIN

DBMS_OUTPUT.PUT_LINE('=== Rapport Dept '||p_dept_id||' ===');
DBMS_OUTPUT.PUT_LINE('Employes : '||nb_employes(p_dept_id));
DBMS_OUTPUT.PUT_LINE('Masse sal. : '||masse_salariale(p_dept_id)||' TND');
END;

PROCEDURE chercher(p_emp_id NUMBER) IS v employe%ROWTYPE; BEGIN

SELECT * INTO v FROM employe WHERE emp_id=p_emp_id;
DBMS_OUTPUT.PUT_LINE([Link]||' '||[Link]);
END;

Oracle SQL & PL/SQL — Cours Complet | DDL · LMD · LID · PL/SQL · Curseurs · Triggers · Exceptions Page 18
 PROCEDURE chercher(p_nom VARCHAR2) IS BEGIN
FOR r IN (SELECT * FROM employe WHERE UPPER(nom) LIKE UPPER(p_nom)||'%') LOOP
DBMS_OUTPUT.PUT_LINE(r.emp_id||' - '||[Link]);
END LOOP;
END;
END pkg_rh;
/

-- Appels
BEGIN pkg_rh.afficher_rapport(10); pkg_rh.augmenter_salaire(20,8);
DBMS_OUTPUT.PUT_LINE('Ops: '||pkg_rh.g_nb_ops); END;
/

Oracle SQL & PL/SQL — Cours Complet | DDL · LMD · LID · PL/SQL · Curseurs · Triggers · Exceptions Page 19
 Chap. 7 Les Déclencheurs (Triggers)
Triggers DML (BEFORE/AFTER/INSTEAD OF), DDL, système, audit et validation.

7.1 Types de triggers

Type Declenchement FOR EACH ROW :NEW / :OLD

BEFORE ROW Avant chaque ligne INSERT/UPDATE/DELETE

OUI Modifiables (:NEW) — lecture (:OLD)

AFTER ROW Apres chaque ligne OUI Lecture seule

BEFORE STMT Avant l'instruction entiere NON Indisponibles

AFTER STMT Apres l'instruction entiere NON Indisponibles

INSTEAD OF Remplace l'operation sur une VUE OUI Lecture seule

DDL Apres CREATE/ALTER/DROP — Contexte evenement

Systeme LOGON/LOGOFF/STARTUP — Infos session

■ Exemple : Trigger BEFORE INSERT : auto-increment + validation

CREATE OR REPLACE TRIGGER trg_emp_bi
BEFORE INSERT ON employe FOR EACH ROW
DECLARE v_dept_count NUMBER;
BEGIN
IF :NEW.emp_id IS NULL THEN
SELECT seq_emp.NEXTVAL INTO :NEW.emp_id FROM DUAL;
END IF;
:NEW.date_emb := NVL(:NEW.date_emb, SYSDATE);
:[Link] := NVL(UPPER(:[Link]), 'A');

SELECT COUNT(*) INTO v_dept_count FROM departement WHERE dept_id=:NEW.dept_id;

IF v_dept_count=0 THEN
RAISE_APPLICATION_ERROR(-20010,'Departement '||:NEW.dept_id||' inexistant.');
END IF;
IF :[Link] < 800 THEN
RAISE_APPLICATION_ERROR(-20011,'Salaire '||:[Link]||' < SMIG (800 TND).');
END IF;
END;
/

■ Exemple : Trigger AFTER UPDATE : audit des modifications de salaire

CREATE TABLE audit_employe (
audit_id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
emp_id NUMBER, champ VARCHAR2(30),
ancienne_val VARCHAR2(200), nouvelle_val VARCHAR2(200),
modifie_par VARCHAR2(50) DEFAULT USER,
modifie_le TIMESTAMP DEFAULT SYSTIMESTAMP,
adresse_ip VARCHAR2(50)
);

CREATE OR REPLACE TRIGGER trg_emp_audit

AFTER UPDATE ON employe FOR EACH ROW
BEGIN
IF NVL(:[Link],-1) <> NVL(:[Link],-1) THEN
INSERT INTO audit_employe(emp_id,champ,ancienne_val,nouvelle_val,adresse_ip)
VALUES(:OLD.emp_id,'SALAIRE',:[Link],:[Link],
SYS_CONTEXT('USERENV','IP_ADDRESS'));
END IF;
IF NVL(:OLD.dept_id,-1) <> NVL(:NEW.dept_id,-1) THEN
INSERT INTO audit_employe(emp_id,champ,ancienne_val,nouvelle_val,adresse_ip)
VALUES(:OLD.emp_id,'DEPT_ID',:OLD.dept_id,:NEW.dept_id,
SYS_CONTEXT('USERENV','IP_ADDRESS'));

Oracle SQL & PL/SQL — Cours Complet | DDL · LMD · LID · PL/SQL · Curseurs · Triggers · Exceptions Page 20
 END IF;
END;
/

■ Exemple : Trigger INSTEAD OF sur vue complexe

CREATE OR REPLACE TRIGGER trg_v_emp_dept_ins
INSTEAD OF INSERT ON v_emp_dept FOR EACH ROW
DECLARE v_exists NUMBER;
BEGIN
SELECT COUNT(*) INTO v_exists FROM departement WHERE dept_id=:NEW.dept_id;
IF v_exists=0 THEN
INSERT INTO departement(dept_id,nom_dept) VALUES(:NEW.dept_id,NVL(:NEW.nom_dept,'Nouveau'));
END IF;
INSERT INTO employe(emp_id,nom,prenom,salaire,dept_id)
VALUES(NVL(:NEW.emp_id,seq_emp.NEXTVAL),:[Link],:[Link],:[Link],:NEW.dept_id);
END;
/

■ Exemple : Trigger systeme LOGON : securite et journalisation

CREATE TABLE connexions_log (
log_id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
utilisateur VARCHAR2(50), adresse_ip VARCHAR2(50),
date_heure TIMESTAMP DEFAULT SYSTIMESTAMP, action VARCHAR2(10)
);

CREATE OR REPLACE TRIGGER trg_logon_secure

AFTER LOGON ON DATABASE
DECLARE
e_table_manquante EXCEPTION;
PRAGMA EXCEPTION_INIT(e_table_manquante, -942);
v_jour VARCHAR2(3) := TO_CHAR(SYSDATE,'DY','NLS_DATE_LANGUAGE=ENGLISH');
v_ip VARCHAR2(50):= SYS_CONTEXT('USERENV','IP_ADDRESS');
BEGIN
INSERT INTO connexions_log(utilisateur,adresse_ip,action) VALUES(USER,v_ip,'LOGON');

IF v_jour IN ('SAT','SUN') AND USER NOT IN ('SYS','SYSTEM') THEN

RAISE_APPLICATION_ERROR(-20099,
'Connexion interdite le week-end pour '||USER);
END IF;
EXCEPTION
WHEN e_table_manquante THEN NULL;
WHEN OTHERS THEN IF SQLCODE=-20099 THEN RAISE; END IF; NULL;
END;
/

7.2 Gestion des triggers

■ Exemple : Activer, desactiver, supprimer
ALTER TRIGGER trg_emp_audit DISABLE;
ALTER TRIGGER trg_emp_audit ENABLE;
ALTER TABLE employe DISABLE ALL TRIGGERS;
ALTER TABLE employe ENABLE ALL TRIGGERS;
DROP TRIGGER trg_emp_audit;
SELECT trigger_name, trigger_type, status FROM user_triggers WHERE table_name='EMPLOYE';

Oracle SQL & PL/SQL — Cours Complet | DDL · LMD · LID · PL/SQL · Curseurs · Triggers · Exceptions Page 21
 Chap. 8 Les Exceptions PL/SQL
Exceptions prédéfinies, définies, PRAGMA EXCEPTION_INIT, RAISE_APPLICATION_ERROR, propagation.

8.1 Exceptions prédéfinies Oracle

Exception SQLCODE Cause

NO_DATA_FOUND -1403 SELECT INTO sans resultat

TOO_MANY_ROWS -1422 SELECT INTO retourne > 1 ligne

ZERO_DIVIDE -1476 Division par zero

VALUE_ERROR -6502 Erreur de conversion ou de taille

INVALID_NUMBER -1722 Chaine non convertible en nombre

DUP_VAL_ON_INDEX -1 Violation contrainte UNIQUE ou PK

INVALID_CURSOR -1001 Operation invalide sur curseur

CURSOR_ALREADY_OPEN -6511 OPEN sur curseur deja ouvert

LOGIN_DENIED -1017 Mot de passe incorrect

STORAGE_ERROR -6500 Memoire insuffisante

TIMEOUT_ON_RESOURCE -54 Timeout d'attente ressource

■ Exemple : Gestion de plusieurs exceptions predefinies

DECLARE
v_emp employe%ROWTYPE;
v_id NUMBER := 101;
BEGIN
SELECT * INTO v_emp FROM employe WHERE emp_id=v_id;
DBMS_OUTPUT.PUT_LINE('Nom : '||v_emp.nom||' | Sal : '||v_emp.salaire);
EXCEPTION
WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('Employe '||v_id||' introuvable.');
WHEN TOO_MANY_ROWS THEN DBMS_OUTPUT.PUT_LINE('Plusieurs employes avec ID '||v_id);
WHEN VALUE_ERROR THEN DBMS_OUTPUT.PUT_LINE('Erreur de valeur : '||SQLERRM);
WHEN OTHERS THEN
ROLLBACK;
DBMS_OUTPUT.PUT_LINE('Erreur inattendue ['||SQLCODE||'] : '||SQLERRM);
END;
/

8.2 Exceptions définies par l'utilisateur

■ Exemple : Declaration, RAISE et gestion d'exceptions metier
DECLARE
e_salaire_invalide EXCEPTION;
e_dept_complet EXCEPTION;
e_email_invalide EXCEPTION;
v_salaire NUMBER := 500;
v_dept_id NUMBER := 10;
v_email VARCHAR2(50) := 'sans_arobase';
v_nb_emp NUMBER;
BEGIN
IF v_salaire < 1000 THEN RAISE e_salaire_invalide; END IF;

SELECT COUNT(*) INTO v_nb_emp FROM employe WHERE dept_id=v_dept_id;

IF v_nb_emp >= 50 THEN RAISE e_dept_complet; END IF;

IF v_email NOT LIKE '%@%.%' THEN RAISE e_email_invalide; END IF;

INSERT INTO employe(emp_id,nom,email,salaire,dept_id)

Oracle SQL & PL/SQL — Cours Complet | DDL · LMD · LID · PL/SQL · Curseurs · Triggers · Exceptions Page 22
 VALUES(seq_emp.NEXTVAL,'Test',v_email,v_salaire,v_dept_id);
COMMIT;

EXCEPTION
WHEN e_salaire_invalide THEN DBMS_OUTPUT.PUT_LINE('Salaire < minimum (1000 TND).');
WHEN e_dept_complet THEN DBMS_OUTPUT.PUT_LINE('Departement complet (50 max).');
WHEN e_email_invalide THEN DBMS_OUTPUT.PUT_LINE('Email invalide : '||v_email);
WHEN OTHERS THEN ROLLBACK; DBMS_OUTPUT.PUT_LINE('Erreur : '||SQLERRM);
END;
/

8.3 PRAGMA EXCEPTION_INIT

■ Exemple : Lier un code ORA a un nom d'exception
DECLARE
e_fk_violation EXCEPTION;
e_doublon EXCEPTION;
e_null_interdit EXCEPTION;
PRAGMA EXCEPTION_INIT(e_fk_violation, -2292); -- enfant existant
PRAGMA EXCEPTION_INIT(e_doublon, -1); -- unicite violee
PRAGMA EXCEPTION_INIT(e_null_interdit, -1400); -- NOT NULL viole
BEGIN
DELETE FROM departement WHERE dept_id=10; COMMIT;
EXCEPTION
WHEN e_fk_violation THEN ROLLBACK; DBMS_OUTPUT.PUT_LINE('Des employes sont lies a ce dept.');
WHEN e_doublon THEN ROLLBACK; DBMS_OUTPUT.PUT_LINE('Enregistrement duplique.');
WHEN e_null_interdit THEN ROLLBACK; DBMS_OUTPUT.PUT_LINE('Valeur obligatoire manquante.');
WHEN OTHERS THEN ROLLBACK; DBMS_OUTPUT.PUT_LINE('Err ['||SQLCODE||'] '||SQLERRM);
END;
/

8.4 RAISE_APPLICATION_ERROR
■ Exemple : Erreurs metier personnalisees (-20000 a -20999)
CREATE OR REPLACE PROCEDURE valider_employe(p_emp_id NUMBER, p_sal NUMBER, p_dept NUMBER) IS
v_moy NUMBER; v_budget NUMBER; v_masse NUMBER;
BEGIN
IF p_sal <= 0 THEN
RAISE_APPLICATION_ERROR(-20001,'Salaire invalide ('||p_sal||'). Doit etre > 0.');
END IF;

SELECT AVG(salaire) INTO v_moy FROM employe WHERE dept_id=p_dept AND emp_id!=p_emp_id;
IF p_sal > NVL(v_moy,p_sal)*3 THEN
RAISE_APPLICATION_ERROR(-20002,
'Salaire '||p_sal||' depasse 3x la moyenne du dept ('||ROUND(NVL(v_moy,0),0)||').');
END IF;

SELECT budget, NVL(SUM([Link]),0) INTO v_budget, v_masse

FROM departement d LEFT JOIN employe e ON d.dept_id=e.dept_id
WHERE d.dept_id=p_dept GROUP BY [Link];

IF (v_masse+p_sal) > v_budget*0.6 THEN

RAISE_APPLICATION_ERROR(-20003,'Masse salariale depasserait 60% du budget.');
END IF;
END;
/

8.5 Propagation d'exceptions (RAISE sans argument)

■ Exemple : Re-lever une exception vers le bloc parent
DECLARE
PROCEDURE log_erreur(p_msg VARCHAR2) IS BEGIN

Oracle SQL & PL/SQL — Cours Complet | DDL · LMD · LID · PL/SQL · Curseurs · Triggers · Exceptions Page 23
 INSERT INTO error_log(message,log_date,utilisateur) VALUES(p_msg,SYSDATE,USER);
END;
BEGIN
BEGIN -- bloc interne
UPDATE employe SET salaire=-1 WHERE emp_id=101;
EXCEPTION
WHEN OTHERS THEN
log_erreur('UPDATE emp 101 : '||SQLERRM);
ROLLBACK;
RAISE; -- propage au bloc parent SANS perdre SQLCODE/SQLERRM
END;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Bloc externe : '||SQLERRM);
COMMIT; -- valide uniquement le INSERT dans error_log
END;
/

8.6 FORMAT_ERROR_BACKTRACE — Pile d'appels

■ Exemple : Localiser exactement la ligne en erreur
CREATE OR REPLACE PROCEDURE proc_b IS BEGIN RAISE ZERO_DIVIDE; END;
/
CREATE OR REPLACE PROCEDURE proc_a IS BEGIN proc_b; END;
/

BEGIN proc_a;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Code : '||SQLCODE);
DBMS_OUTPUT.PUT_LINE('Message : '||SQLERRM);
DBMS_OUTPUT.PUT_LINE('Pile : '||CHR(10)||DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
END;
/

Oracle SQL & PL/SQL — Cours Complet | DDL · LMD · LID · PL/SQL · Curseurs · Triggers · Exceptions Page 24
 QCM — 50 Questions à Choix Multiple
Une seule réponse correcte par question. Durée conseillée : 30 min. Corrigé en fin de section.

Q1. Quelle commande crée une table Oracle ?

A) CREATE TABLE
B) BUILD TABLE
C) MAKE TABLE
D) GENERATE TABLE

Q2. Quelle contrainte garantit unicité ET absence de NULL ?

A) UNIQUE
B) NOT NULL
C) PRIMARY KEY
D) CHECK

Q3. ALTER TABLE permet de :

A) Interroger une table
B) Modifier la structure d'une table existante
C) Créer une copie
D) Renommer la base

Q4. CREATE TABLE emp AS SELECT * FROM employe WHERE 1=2 :

A) Crée une table avec données
B) Crée une table vide (structure seule)
C) Crée une vue
D) Génère une erreur

Q5. Une vue INSTEAD OF trigger sert à :

A) Remplacer un index
B) Rendre une vue complexe modifiable
C) Intercepter un DDL
D) Auditer les connexions

Q6. NEXTVAL et CURRVAL sont des pseudo-colonnes de :

A) Index
B) Séquence
C) Vue
D) Synonyme

Q7. CREATE PUBLIC SYNONYM rend l'objet accessible à :

A) Son créateur seul
B) Tous les utilisateurs
C) Les DBA uniquement
D) La session courante

Q8. INSERT explicite :

A) Omet les noms de colonnes
B) Liste les colonnes avant VALUES
C) Insère toujours dans toutes les colonnes
D) Nécessite une séquence

Q9. INSERT ALL permet :

A) Une seule table
B) Plusieurs tables en une instruction
C) Une vue uniquement
D) Un fichier externe

Q10. UPDATE sans WHERE affecte :

A) La première ligne
B) 10 lignes

Oracle SQL & PL/SQL — Cours Complet | DDL · LMD · LID · PL/SQL · Curseurs · Triggers · Exceptions Page 25
 C) TOUTES les lignes
D) Aucune ligne

Q11. Différence clé TRUNCATE vs DELETE :

A) TRUNCATE est plus lent
B) TRUNCATE non annulable (DDL)
C) DELETE supprime l'index
D) TRUNCATE ajoute des lignes

Q12. MERGE permet de :

A) Fusionner deux bases
B) Insérer ou mettre à jour selon l'existence
C) Copier une table
D) Joindre deux requêtes

Q13. BETWEEN 10 AND 20 équivaut à :

A) > 10 AND < 20
B) >= 10 AND <= 20
C) >= 10 OR <= 20
D) > 10 AND <= 20

Q14. LIKE '_a%' trouve les chaînes :

A) Commençant par 'a'
B) Ayant 'a' en 2e position
C) Se terminant par 'a'
D) Contenant uniquement 'a'

Q15. ROUND(3.456, 1) retourne :

A) 3.4
B) 3.46
C) 3.5
D) 4

Q16. NVL2(NULL, 'X', 'Y') retourne :

A) NULL
B) 'X'
C) 'Y'
D) Erreur

Q17. MONTHS_BETWEEN retourne :

A) Des jours
B) Des mois (décimal possible)
C) Des années
D) Des heures

Q18. GROUP BY sans HAVING :

A) Erreur de syntaxe
B) Groupe sans filtrer les groupes
C) N'existe pas
D) Nécessite ORDER BY

Q19. LISTAGG est :

A) Une fonction de groupe qui concatène
B) Une fonction scalaire
C) Une conversion
D) Analytique seulement

Q20. FULL OUTER JOIN retourne :

A) Lignes communes uniquement
B) Toutes les lignes des deux tables

Oracle SQL & PL/SQL — Cours Complet | DDL · LMD · LID · PL/SQL · Curseurs · Triggers · Exceptions Page 26
 C) Produit cartésien
D) Lignes sans correspondance uniquement

Q21. EXISTS est préféré à IN quand :

A) La sous-requête est vide
B) La sous-requête retourne beaucoup de lignes
C) On compare des dates
D) On utilise GROUP BY

Q22. UNION ALL vs UNION :

A) UNION ALL plus lent
B) UNION ALL conserve les doublons et est plus rapide
C) UNION ALL supprime les doublons
D) Identiques

Q23. ROW_NUMBER() OVER (PARTITION BY dept ORDER BY sal DESC) :

A) Somme par département
B) Numérote les lignes par département, salaire déc.
C) Classe les départements
D) Rang avec ex aequo

Q24. La section DECLARE en PL/SQL est :

A) Obligatoire
B) Optionnelle
C) Remplace BEGIN
D) Toujours en dernier

Q25. v_emp employe%ROWTYPE déclare :

A) Un tableau
B) Une variable avec la structure d'une ligne
C) Un curseur
D) Un synonyme

Q26. EXIT WHEN c%NOTFOUND sort de la boucle quand :

A) Curseur non ouvert
B) Dernier FETCH sans résultat
C) Table vide
D) Curseur fermé

Q27. Le curseur FOR LOOP gère automatiquement :

A) Les transactions
B) OPEN, FETCH et CLOSE
C) Les exceptions
D) Les séquences

Q28. SQL%ROWCOUNT après UPDATE retourne :

A) Nombre de colonnes
B) Nombre de lignes modifiées
C) ID de transaction
D) Toujours 0 ou 1

Q29. Un curseur paramétré est utile pour :

A) Éviter de déclarer plusieurs curseurs similaires
B) Sécuriser les données
C) Remplacer les index
D) Éviter les exceptions

Q30. REF CURSOR faible peut pointer vers :

A) Structure fixe seulement
B) N'importe quel SELECT

Oracle SQL & PL/SQL — Cours Complet | DDL · LMD · LID · PL/SQL · Curseurs · Triggers · Exceptions Page 27
 C) Vues uniquement
D) Procédures stockées

Q31. Différence procédure / fonction :

A) Procédure retourne une valeur
B) Fonction retourne une valeur, utilisable dans SELECT
C) Identiques
D) Procédure sans paramètres

Q32. Paramètre IN OUT :

A) Lecture seule
B) Écriture seule
C) Lecture et écriture
D) N'existe pas

Q33. DETERMINISTIC dans une fonction :

A) Autorise les modifications
B) Oracle peut mettre en cache le résultat
C) Rend la fonction obligatoire
D) Lève une exception

Q34. Un package Oracle contient :

A) Uniquement des procédures
B) Spécification (interface) + corps (implémentation)
C) Uniquement des fonctions
D) Tables et vues

Q35. La surcharge (overloading) dans un package :

A) Deux procédures de même nom avec paramètres différents
B) Accélère les requêtes
C) Chiffre le code
D) Appelle une procédure depuis trigger

Q36. Trigger BEFORE ROW permet de :

A) Modifier :NEW avant l'insertion réelle
B) Lire seulement :OLD
C) Valider après la mise à jour
D) Journaliser après suppression

Q37. :OLD est disponible dans un trigger :

A) INSERT uniquement
B) DELETE et UPDATE
C) Tous les types
D) AFTER uniquement

Q38. WHEN ([Link] > [Link]) dans un trigger :

A) Lève exception si faux
B) Conditionne l'exécution
C) Crée une contrainte CHECK
D) Est obligatoire

Q39. ALTER TABLE employe DISABLE ALL TRIGGERS :

A) Supprime tous les triggers
B) Désactive temporairement
C) Renomme les triggers
D) Recompile

Q40. SYS_CONTEXT('USERENV','IP_ADDRESS') retourne :

A) Nom de la table
B) Adresse IP du client

Oracle SQL & PL/SQL — Cours Complet | DDL · LMD · LID · PL/SQL · Curseurs · Triggers · Exceptions Page 28
 C) Numéro de session
D) Nom du trigger

Q41. NO_DATA_FOUND est levée par :

A) DELETE sans WHERE
B) SELECT INTO sans résultat
C) Index manquant
D) Connexion refusée

Q42. TOO_MANY_ROWS est levée par :

A) UPDATE sur trop de lignes
B) SELECT INTO retournant > 1 ligne
C) Table trop grande
D) FETCH dépassant la limite

Q43. DUP_VAL_ON_INDEX correspond à :

A) ORA-01403
B) ORA-01422
C) ORA-00001
D) ORA-01476

Q44. PRAGMA EXCEPTION_INIT lie :

A) Deux exceptions
B) Code ORA à un nom d'exception PL/SQL
C) Exception à une procédure
D) Trigger à une exception

Q45. RAISE_APPLICATION_ERROR : codes valides :

A) -1 à -9999
B) -20000 à -20999
C) 1 à 20000
D) -100 à -200

Q46. SQLERRM retourne :

A) Code numérique
B) Message d'erreur associé à SQLCODE
C) Nom de la table
D) Ligne PL/SQL en erreur

Q47. WHEN OTHERS doit être placé :

A) En premier
B) En dernier (après tous les WHEN spécifiques)
C) Dans DECLARE
D) N'importe où

Q48. RAISE sans argument dans un gestionnaire :

A) Crée une nouvelle exception
B) Propage l'exception courante au bloc parent
C) Ignore l'exception
D) Affiche le message

Q49. DBMS_UTILITY.FORMAT_ERROR_BACKTRACE retourne :

A) Liste des tables impliquées
B) Pile d'appels avec numéros de lignes
C) Plan d'exécution
D) Statistiques de performance

Q50. COMMIT dans un trigger DML FOR EACH ROW :

A) Valide l'opération
B) Est interdit (erreur)

Oracle SQL & PL/SQL — Cours Complet | DDL · LMD · LID · PL/SQL · Curseurs · Triggers · Exceptions Page 29
 C) Valide uniquement l'audit
D) N'a aucun effet

Oracle SQL & PL/SQL — Cours Complet | DDL · LMD · LID · PL/SQL · Curseurs · Triggers · Exceptions Page 30
 Corrigé du QCM
Q1:A Q2:C Q3:B Q4:B Q5:B Q6:B Q7:B Q8:B Q9:B Q10:C

Q11:B Q12:B Q13:B Q14:B Q15:C Q16:C Q17:B Q18:B Q19:A Q20:B

Q21:B Q22:B Q23:B Q24:B Q25:B Q26:B Q27:B Q28:B Q29:A Q30:B

Q31:B Q32:C Q33:B Q34:B Q35:A Q36:A Q37:B Q38:B Q39:B Q40:B

Q41:B Q42:B Q43:C Q44:B Q45:B Q46:B Q47:B Q48:B Q49:B Q50:B

Oracle SQL & PL/SQL — Cours Complet | DDL · LMD · LID · PL/SQL · Curseurs · Triggers · Exceptions Page 31
 Schém
DEPARTEMENT
Durée : 1h30 | Documents interdits | /80 points

PARTIE 1 — DDL & Contraintes (20 pts)

Q1 (6 pts) — Créez SEQ_PROJ et la table PROJET :
La séquence : start=1, increment=1, cache=10, nocycle. La table PROJET : proj_id PK via séquence, titre VARCHAR2(100)
UNIQUE NOT NULL, budget NUMBER(12,2) CHECK>0 DEFAULT 10000, dept_id FK vers DEPARTEMENT ON DELETE
SET NULL, date_debut DATE DEFAULT SYSDATE, statut CHAR(1) CHECK IN ('A','T','C') DEFAULT 'A'.

Q2 (4 pts) — Modifiez EMPLOYE :

a) Ajoutez bonus NUMBER(8,2) DEFAULT 0 NOT NULL. (2 pts) b) Ajoutez contrainte CHECK : bonus >= 0 AND bonus <=
salaire*0.5. (2 pts)

Q3 (5 pts) — Créez la vue V_RAPPORT_DEPT :

Affiche : dept_id, nom_dept, ville, nb_employes, sal_moyen (rond.2), masse_sal, budget,
pct_budget=masse_sal/budget*100. Uniquement employés actifs. Tri par pct_budget DESC.

Q4 (5 pts) — Objets complementaires :

a) Index composite sur EMPLOYE(dept_id, statut). (2 pts) b) Synonyme privé EMP pointant vers EMPLOYE. (1 pt) c)
Expliquez pourquoi un index sur EMPLOYE(statut) seul serait peu utile si 90% des employes ont statut='A'. (2 pts)

PARTIE 2 — LMD & LID (SELECT) (20 pts)

Q5 (4 pts) — DML :
a) INSERT ALL : sal<2500 => EMPLOYE_JUNIOR, sal 2500-6000 => EMPLOYE_MOYEN, sinon => EMPLOYE_SENIOR.
Source : SELECT depuis EMPLOYE. (2 pts) b) UPDATE+10% salaire des employes participant a un projet budget>80000
(sous-requete EXISTS). (2 pts)

Q6 (4 pts) — Fonctions analytiques :

Pour chaque employe actif : emp_id, nom, salaire, nom_dept, rang dans le dept (sal DESC), moyenne du dept, pourcentage
de la masse salariale du dept. Utiliser OVER(PARTITION BY).

Q7 (4 pts) — Sous-requetes :
a) Employes dont salaire > moyenne de LEUR departement (sous-requete correlee). (2 pts) b) Departements sans aucun
employe actif (NOT EXISTS). (2 pts)

Q8 (4 pts) — Jointures multiples :

Affichez : nom employe, nom_dept, titre projet, nb_heures, budget projet. Uniquement employes participant a un projet avec
nb_heures>30. Tri par nb_heures DESC.

Q9 (4 pts) — Ensembliste + ROLLUP :

a) INTERSECT : employes dans dept 10 ET participant a un projet. (2 pts) b) ROLLUP : masse salariale par (ville, nom_dept)
avec sous-totaux. (2 pts)

PARTIE 3 — PL/SQL : Blocs, Curseurs, Sous-programmes (25 pts)

Q10 (5 pts) — Bloc anonyme avec curseur paramétré :
PL/SQL qui : 1) Declare c_proj_dept(p_dept_id) retournant projets+budget. 2) Parcourt les depts 10, 20, 30 avec le meme
curseur. 3) Affiche titre et budget de chaque projet. 4) Affiche le total projets traites (%ROWCOUNT). 5) Gere
NO_DATA_FOUND et OTHERS.

Q11 (8 pts) — Procedure CALCULER_PRIME :

CALCULER_PRIME(p_emp_id IN, p_prime OUT, p_message OUT) : 1) Recupere salaire et total heures de tous les projets.
2) Prime : >100h=>15%, 50-100h=>10%, <50h=>5%, aucun projet=>0%. 3) Met a jour bonus + COMMIT. 4) Retourne
montant et message detaille. 5) Gere NO_DATA_FOUND et OTHERS avec ROLLBACK.

Q12 (6 pts) — Fonction STATUT_BUDGET :

STATUT_BUDGET(p_dept_id) RETURN VARCHAR2 : 'SAIN' si masse_sal < 40% budget, 'SURVEILLE' si 40-70%,
'CRITIQUE' si 70-100%, 'DEPASSEMENT' si >100%. Utilisable dans SELECT.

Q13 (6 pts) — Archivage en masse :

Oracle SQL & PL/SQL — Cours Complet | DDL · LMD · LID · PL/SQL · Curseurs · Triggers · Exceptions Page 32
 PL/SQL : 1) Cursor FOR LOOP sur employes inactifs depuis >2 ans. 2) Insere dans EMPLOYE_ARCHIVE (meme structure +
archive_date). 3) Supprime de EMPLOYE. 4) COMMIT tous les 100 enregistrements. 5) Affiche total archive.

PARTIE 4 — Triggers & Exceptions (15 pts)

Q14 (6 pts) — Trigger TRG_EMP_CTRL :
BEFORE INSERT OR UPDATE sur EMPLOYE FOR EACH ROW : 1) Interdit modification de emp_id en UPDATE (err
-20051). 2) Interdit salaire > 3x moyenne du dept (err -20050). 3) Si UPDATE salaire : insere dans AUDIT_EMPLOYE. 4) Si
INSERT et date_emb < 30 jours : force statut='A'. 5) Utilise INSERTING, UPDATING('SALAIRE'), :NEW/:OLD.

Q15 (5 pts) — Procedure SUPPRIMER_DEPARTEMENT :

1) e_fk_violation avec PRAGMA EXCEPTION_INIT ORA-02292. 2) e_budget_eleve si budget > 500000. 3)
NO_DATA_FOUND si dept inexistant. 4) Chaque exception : ROLLBACK + message. 5) WHEN OTHERS : ROLLBACK +
SQLERRM + SQLCODE.

Q16 (4 pts) — Trigger LOGON securise :

AFTER LOGON ON DATABASE : 1) Insere dans CONNEXIONS_LOG via SYS_CONTEXT. 2) Interdit connexions le
week-end sauf SYS/SYSTEM. 3) PRAGMA EXCEPTION_INIT ORA-00942 (table inexistante). 4)
RAISE_APPLICATION_ERROR -20099 en cas d'interdiction.

Oracle SQL & PL/SQL — Cours Complet | DDL · LMD · LID · PL/SQL · Curseurs · Triggers · Exceptions Page 33
 CORRIGÉ COMPLET DE L'EXAMEN FINAL
✓ Corrigé — Question 1 (6 pts)
■ Exemple : Q1 — Séquence + CREATE TABLE PROJET
CREATE SEQUENCE seq_proj START WITH 1 INCREMENT BY 1 NOCYCLE CACHE 10;

CREATE TABLE projet (

proj_id NUMBER(8) CONSTRAINT pk_proj PRIMARY KEY,
titre VARCHAR2(100) NOT NULL CONSTRAINT uq_proj_titre UNIQUE,
budget NUMBER(12,2) DEFAULT 10000 CONSTRAINT ck_proj_bud CHECK (budget>0),
dept_id NUMBER(4) CONSTRAINT fk_proj_dept
REFERENCES departement(dept_id) ON DELETE SET NULL,
date_debut DATE DEFAULT SYSDATE,
statut CHAR(1) DEFAULT 'A'
CONSTRAINT ck_proj_stat CHECK (statut IN ('A','T','C'))
);

CREATE OR REPLACE TRIGGER trg_proj_bi

BEFORE INSERT ON projet FOR EACH ROW
BEGIN
IF :NEW.proj_id IS NULL THEN
SELECT seq_proj.NEXTVAL INTO :NEW.proj_id FROM DUAL;
END IF;
END;
/

✓ Corrigé — Question 2 (4 pts)

■ Exemple : Q2 — ALTER TABLE EMPLOYE
ALTER TABLE employe ADD bonus NUMBER(8,2) DEFAULT 0 NOT NULL;
ALTER TABLE employe ADD CONSTRAINT ck_emp_bonus CHECK (bonus>=0 AND bonus<=salaire*0.5);

✓ Corrigé — Question 3 (5 pts)

■ Exemple : Q3 — Vue V_RAPPORT_DEPT
CREATE OR REPLACE VIEW v_rapport_dept AS
SELECT d.dept_id, d.nom_dept, [Link],
COUNT(e.emp_id) AS nb_employes,
ROUND(AVG([Link]),2) AS sal_moyen,
SUM([Link]) AS masse_sal,
[Link],
ROUND(SUM([Link])/[Link]*100,2) AS pct_budget
FROM departement d
LEFT JOIN employe e ON d.dept_id=e.dept_id AND [Link]='A'
GROUP BY d.dept_id, d.nom_dept, [Link], [Link]
ORDER BY pct_budget DESC;

✓ Corrigé — Question 4 (5 pts)

■ Exemple : Q4 — Index, synonyme et explication
-- a) Index composite
CREATE INDEX idx_emp_dept_statut ON employe(dept_id, statut);

-- b) Synonyme prive
CREATE SYNONYM emp FOR employe;

-- c) Explication :
-- Un index sur statut (faible cardinalite : 90% 'A') n'est pas selectif.
-- Oracle lit presque autant de blocs avec l'index que sans (Full Table Scan).
-- L'optimiseur ignorera souvent cet index.
-- Solution : index bitmap (OLAP) ou index sur (dept_id, statut).

✓ Corrigé — Question 5 (4 pts)

■ Exemple : Q5 — INSERT ALL + UPDATE EXISTS

Oracle SQL & PL/SQL — Cours Complet | DDL · LMD · LID · PL/SQL · Curseurs · Triggers · Exceptions Page 34
 INSERT ALL
WHEN salaire < 2500 THEN INTO employe_junior VALUES(emp_id,nom,prenom,salaire,dept_id)
WHEN salaire BETWEEN 2500 AND 6000 THEN INTO employe_moyen VALUES(emp_id,nom,prenom,salaire,dept_id
)
ELSE INTO employe_senior VALUES(emp_id,nom,prenom,salaire,dept_id)
SELECT emp_id,nom,prenom,salaire,dept_id FROM employe WHERE statut='A';

UPDATE employe e SET salaire=salaire*1.10

WHERE EXISTS (
SELECT 1 FROM participe pa JOIN projet p ON pa.proj_id=p.proj_id
WHERE pa.emp_id=e.emp_id AND [Link]>80000
); COMMIT;

✓ Corrigé — Question 6 (4 pts)

■ Exemple : Q6 — Fonctions analytiques
SELECT e.emp_id, [Link], [Link], d.nom_dept,
RANK() OVER (PARTITION BY e.dept_id ORDER BY [Link] DESC) rang_dept,
ROUND(AVG([Link]) OVER (PARTITION BY e.dept_id),2) moy_dept,
ROUND([Link]/SUM([Link]) OVER (PARTITION BY e.dept_id)*100,2) pct_dept
FROM employe e JOIN departement d ON e.dept_id=d.dept_id
WHERE [Link]='A' ORDER BY e.dept_id, rang_dept;

✓ Corrigé — Question 7 (4 pts)

■ Exemple : Q7 — Sous-requête corrélée + NOT EXISTS
-- a) Sous-requete correlee
SELECT [Link], [Link], [Link], e.dept_id FROM employe e
WHERE [Link] > (SELECT AVG([Link]) FROM employe e2
WHERE e2.dept_id=e.dept_id AND [Link]='A')
AND [Link]='A' ORDER BY e.dept_id, [Link] DESC;

-- b) Departements sans employe actif

SELECT d.dept_id, d.nom_dept FROM departement d
WHERE NOT EXISTS (SELECT 1 FROM employe e WHERE e.dept_id=d.dept_id AND [Link]='A');

✓ Corrigé — Question 8 (4 pts)

■ Exemple : Q8 — Jointures multiples
SELECT [Link], [Link], d.nom_dept, [Link], pa.nb_heures, [Link] AS budget_projet
FROM employe e
JOIN departement d ON e.dept_id=d.dept_id
JOIN participe pa ON e.emp_id=pa.emp_id
JOIN projet p ON pa.proj_id=p.proj_id
WHERE pa.nb_heures>30 AND [Link]='A'
ORDER BY pa.nb_heures DESC;

✓ Corrigé — Question 9 (4 pts)

■ Exemple : Q9 — INTERSECT + ROLLUP
-- a) INTERSECT
SELECT emp_id FROM employe WHERE dept_id=10
INTERSECT
SELECT emp_id FROM participe;

-- b) ROLLUP
SELECT [Link], d.nom_dept, SUM([Link]) masse_sal
FROM departement d LEFT JOIN employe e ON d.dept_id=e.dept_id AND [Link]='A'
GROUP BY ROLLUP([Link], d.nom_dept)
ORDER BY [Link] NULLS LAST, d.nom_dept NULLS LAST;

✓ Corrigé — Question 10 (5 pts)

■ Exemple : Q10 — Bloc avec curseur paramétré
DECLARE
CURSOR c_proj_dept(p_dept_id NUMBER) IS

Oracle SQL & PL/SQL — Cours Complet | DDL · LMD · LID · PL/SQL · Curseurs · Triggers · Exceptions Page 35
 SELECT proj_id, titre, budget FROM projet WHERE dept_id=p_dept_id ORDER BY budget DESC;
v_total PLS_INTEGER := 0;
TYPE t_depts IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
v_depts t_depts;
BEGIN
v_depts(1):=10; v_depts(2):=20; v_depts(3):=30;
FOR d IN v_depts.FIRST..v_depts.LAST LOOP
DBMS_OUTPUT.PUT_LINE('=== Dept '||v_depts(d)||' ===');
FOR rec IN c_proj_dept(v_depts(d)) LOOP
v_total := v_total+1;
DBMS_OUTPUT.PUT_LINE(' '||RPAD([Link],30)||[Link]||' TND');
END LOOP;
END LOOP;
DBMS_OUTPUT.PUT_LINE('Total projets : '||v_total);
EXCEPTION
WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('Aucun projet.');
WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Err ['||SQLCODE||'] '||SQLERRM);
END;
/

✓ Corrigé — Question 11 (8 pts)

■ Exemple : Q11 — Procédure CALCULER_PRIME
CREATE OR REPLACE PROCEDURE calculer_prime(
p_emp_id IN employe.emp_id%TYPE, p_prime OUT NUMBER, p_message OUT VARCHAR2) IS
v_salaire [Link]%TYPE;
v_heures NUMBER;
v_pct NUMBER;
BEGIN
SELECT salaire INTO v_salaire FROM employe WHERE emp_id=p_emp_id;
SELECT NVL(SUM(nb_heures),0) INTO v_heures FROM participe WHERE emp_id=p_emp_id;

v_pct := CASE WHEN v_heures>100 THEN 0.15 WHEN v_heures>=50 THEN 0.10
WHEN v_heures>0 THEN 0.05 ELSE 0 END;
p_prime := ROUND(v_salaire*v_pct,2);
p_message := 'Emp '||p_emp_id||' | Heures:'||v_heures||' | Taux:'||(v_pct*100)||'% | Prime:'||p_pri
me||' TND';

UPDATE employe SET bonus=p_prime WHERE emp_id=p_emp_id;

COMMIT;
EXCEPTION
WHEN NO_DATA_FOUND THEN p_prime:=0; p_message:='Employe '||p_emp_id||' introuvable.';
WHEN OTHERS THEN ROLLBACK; p_prime:=0; p_message:='Err ['||SQLCODE||'] '||SQLERRM;
END;
/

✓ Corrigé — Question 12 (6 pts)

■ Exemple : Q12 — Fonction STATUT_BUDGET
CREATE OR REPLACE FUNCTION statut_budget(p_dept_id IN NUMBER) RETURN VARCHAR2 IS
v_budget NUMBER; v_masse NUMBER; v_pct NUMBER;
BEGIN
SELECT [Link], NVL(SUM([Link]),0)
INTO v_budget, v_masse
FROM departement d LEFT JOIN employe e ON d.dept_id=e.dept_id AND [Link]='A'
WHERE d.dept_id=p_dept_id GROUP BY [Link];

IF v_budget=0 THEN RETURN 'BUDGET_NUL'; END IF;

v_pct := v_masse/v_budget*100;
RETURN CASE WHEN v_pct<40 THEN 'SAIN'
WHEN v_pct<70 THEN 'SURVEILLE'
WHEN v_pct<=100 THEN 'CRITIQUE'

Oracle SQL & PL/SQL — Cours Complet | DDL · LMD · LID · PL/SQL · Curseurs · Triggers · Exceptions Page 36
 ELSE 'DEPASSEMENT' END;
EXCEPTION
WHEN NO_DATA_FOUND THEN RETURN 'DEPT_INEXISTANT';
END;
/

SELECT dept_id, nom_dept, statut_budget(dept_id) statut FROM departement ORDER BY dept_id;

✓ Corrigé — Question 13 (6 pts)

■ Exemple : Q13 — Archivage en masse
DECLARE
v_count PLS_INTEGER := 0;
c_batch_size CONSTANT PLS_INTEGER := 100;
BEGIN
FOR rec IN (SELECT * FROM employe WHERE statut='I' AND date_emb < SYSDATE-730 ORDER BY emp_id) LOOP
INSERT INTO employe_archive
VALUES(rec.emp_id,[Link],[Link],[Link],[Link],
rec.dept_id,rec.manager_id,rec.date_emb,[Link],NVL([Link],0),SYSDATE);
DELETE FROM employe WHERE emp_id=rec.emp_id;
v_count := v_count+1;
IF MOD(v_count,c_batch_size)=0 THEN
COMMIT;
DBMS_OUTPUT.PUT_LINE('Lot valide : '||v_count||' employes archives.');
END IF;
END LOOP;
COMMIT;
DBMS_OUTPUT.PUT_LINE('=== Termine : '||v_count||' employe(s) archive(s) ===');
EXCEPTION
WHEN OTHERS THEN ROLLBACK;
DBMS_OUTPUT.PUT_LINE('Erreur apres '||v_count||' archivages : '||SQLERRM);
END;
/

✓ Corrigé — Question 14 (6 pts)

■ Exemple : Q14 — Trigger TRG_EMP_CTRL
CREATE OR REPLACE TRIGGER trg_emp_ctrl
BEFORE INSERT OR UPDATE ON employe FOR EACH ROW
DECLARE v_moy NUMBER;
BEGIN
IF UPDATING AND :OLD.emp_id != :NEW.emp_id THEN
RAISE_APPLICATION_ERROR(-20051,'Modification de emp_id interdite.');
END IF;

SELECT AVG(salaire) INTO v_moy FROM employe

WHERE dept_id=:NEW.dept_id AND emp_id != NVL(:OLD.emp_id,-1);

IF :[Link] > NVL(v_moy,:[Link])*3 THEN

RAISE_APPLICATION_ERROR(-20050,
'Salaire '||:[Link]||' > 3x moy dept ('||ROUND(NVL(v_moy,0),0)||').');
END IF;

IF UPDATING('SALAIRE') AND :[Link] != :[Link] THEN

INSERT INTO audit_employe(emp_id,champ,ancienne_val,nouvelle_val)
VALUES(:OLD.emp_id,'SALAIRE',:[Link],:[Link]);
END IF;

IF INSERTING AND NVL(:NEW.date_emb,SYSDATE) >= SYSDATE-30 THEN

:[Link] := 'A';
END IF;
END;

Oracle SQL & PL/SQL — Cours Complet | DDL · LMD · LID · PL/SQL · Curseurs · Triggers · Exceptions Page 37
 /

✓ Corrigé — Question 15 (5 pts)

■ Exemple : Q15 — Procédure SUPPRIMER_DEPARTEMENT
CREATE OR REPLACE PROCEDURE supprimer_departement(p_dept_id IN NUMBER) IS
e_fk_violation EXCEPTION; PRAGMA EXCEPTION_INIT(e_fk_violation,-2292);
e_budget_eleve EXCEPTION;
v_budget [Link]%TYPE;
v_nom departement.nom_dept%TYPE;
BEGIN
SELECT nom_dept, budget INTO v_nom, v_budget FROM departement WHERE dept_id=p_dept_id;
IF v_budget > 500000 THEN RAISE e_budget_eleve; END IF;
DELETE FROM departement WHERE dept_id=p_dept_id;
COMMIT;
DBMS_OUTPUT.PUT_LINE('Departement '||v_nom||' supprime.');
EXCEPTION
WHEN NO_DATA_FOUND THEN ROLLBACK; DBMS_OUTPUT.PUT_LINE('Dept '||p_dept_id||' introuvable.');
WHEN e_fk_violation THEN ROLLBACK; DBMS_OUTPUT.PUT_LINE('Des employes sont lies. Transferez-les d'
'abord.');
WHEN e_budget_eleve THEN ROLLBACK; DBMS_OUTPUT.PUT_LINE('Budget '||v_budget||' > 500000. Suppressi
on refusee.');
WHEN OTHERS THEN ROLLBACK; DBMS_OUTPUT.PUT_LINE('Err ['||SQLCODE||'] : '||SQLERRM);
END;
/

✓ Corrigé — Question 16 (4 pts)

■ Exemple : Q16 — Trigger AFTER LOGON securise
CREATE TABLE connexions_log(
log_id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
utilisateur VARCHAR2(50), adresse_ip VARCHAR2(50),
date_heure TIMESTAMP DEFAULT SYSTIMESTAMP, action VARCHAR2(10));

CREATE OR REPLACE TRIGGER trg_logon_secure

AFTER LOGON ON DATABASE
DECLARE
e_table_man EXCEPTION; PRAGMA EXCEPTION_INIT(e_table_man,-942);
v_jour VARCHAR2(3) := TO_CHAR(SYSDATE,'DY','NLS_DATE_LANGUAGE=ENGLISH');
v_ip VARCHAR2(50):= SYS_CONTEXT('USERENV','IP_ADDRESS');
BEGIN
INSERT INTO connexions_log(utilisateur,adresse_ip,action) VALUES(USER,v_ip,'LOGON');
IF v_jour IN ('SAT','SUN') AND USER NOT IN ('SYS','SYSTEM') THEN
RAISE_APPLICATION_ERROR(-20099,'Connexion interdite le week-end pour '||USER||' (IP:'||v_ip||')
.');
END IF;
EXCEPTION
WHEN e_table_man THEN NULL;
WHEN OTHERS THEN IF SQLCODE=-20099 THEN RAISE; END IF; NULL;
END;
/

Q5:4 | Q6:4 | Q7:4 | Q8:4 | Q9:4) Partie 3 — PL/SQL : Blocs, Curseurs, Sous-prog. : 25 pts (Q10:5 | Q11:8 | Q1

Oracle SQL & PL/SQL — Cours Complet | DDL · LMD · LID · PL/SQL · Curseurs · Triggers · Exceptions Page 38