GUIDE SQL & PL/SQL

Référence Pédagogique Complète — LDD · LMD · LID · PL/SQL

Ce guide est une référence pédagogique du langage SQL (Oracle) et PL/SQL. Chaque concept est
accompagné d'une explication et d'exemples concrets.
 PARTIE 1 — LDD : Langage de Définition de Données

Le LDD (DDL en anglais) regroupe les instructions qui permettent de créer, modifier et supprimer la
structure des objets de la base de données : tables, vues, séquences, index et synonymes.

■ 1.1 Tables

Création d'une table

Une table est la structure de base pour stocker des données. On y définit les colonnes, leur type et leurs
contraintes.

-- Syntaxe générale CREATE TABLE employes ( id NUMBER(5) PRIMARY KEY, nom

VARCHAR2(50) NOT NULL, salaire NUMBER(10,2) CHECK (salaire > 0), email VARCHAR2(100)
UNIQUE, dept_id NUMBER(3) REFERENCES departements(id) ); -- Créer une table à partir
d'une requête SELECT CREATE TABLE employes_backup AS SELECT * FROM employes WHERE
salaire > 3000;

Les 5 contraintes d'intégrité

Contrainte Rôle Exemple

NOT NULL La colonne ne peut pas être vide nom VARCHAR2(50) NOT NULL

UNIQUE Chaque valeur est unique dans la colonne email VARCHAR2(100) UNIQUE

CHECK Vérifie une condition logique CHECK (age >= 18)

PRIMARY KEY Identifiant unique d'une ligne (NOT NULL + id NUMBER PRIMARY KEY
UNIQUE)

FOREIGN KEY Lien vers la clé primaire d'une autre table REFERENCES dept(id)

■ Une PRIMARY KEY combine automatiquement NOT NULL et UNIQUE. Elle est obligatoire pour référencer
la table depuis une FOREIGN KEY.

Modification d'une table (ALTER TABLE)

-- Ajouter une colonne ALTER TABLE employes ADD (telephone VARCHAR2(15)); -- Modifier
le type d'une colonne ALTER TABLE employes MODIFY (nom VARCHAR2(80)); -- Supprimer
une colonne ALTER TABLE employes DROP COLUMN telephone; -- Ajouter une contrainte
ALTER TABLE employes ADD CONSTRAINT ck_sal CHECK (salaire BETWEEN 1000 AND 50000); --
Désactiver / Réactiver une contrainte ALTER TABLE employes DISABLE CONSTRAINT ck_sal;
ALTER TABLE employes ENABLE CONSTRAINT ck_sal;

Suppression et renommage
 -- Supprimer la table et toutes ses données définitivement DROP TABLE employes; --
Vider la table (plus rapide que DELETE, impossible à ROLLBACK) TRUNCATE TABLE
employes; -- Renommer une table RENAME employes TO salaries;

■■ TRUNCATE est irréversible : il n'est pas possible d'annuler l'opération avec ROLLBACK. Utilisez
DELETE si vous avez besoin de pouvoir annuler.

■ 1.2 Vues

Une vue est une table virtuelle dont le contenu est défini par une requête SELECT. Elle ne stocke pas de
données mais présente une fenêtre sur les données réelles.

-- Créer ou remplacer une vue CREATE OR REPLACE VIEW v_employes_rh AS SELECT id, nom,
salaire, dept_id FROM employes WHERE dept_id = 10 WITH CHECK OPTION; -- empêche
d'insérer des lignes hors du filtre -- Vue en lecture seule CREATE OR REPLACE VIEW
v_salaires AS SELECT nom, salaire FROM employes WITH READ ONLY; -- Supprimer une vue
DROP VIEW v_employes_rh;

■ WITH CHECK OPTION garantit que toute INSERT ou UPDATE via la vue respecte la condition WHERE
de la vue. WITH READ ONLY interdit toute modification.

■ 1.3 Séquences

Une séquence est un générateur de nombres entiers, utilisé principalement pour créer automatiquement
des valeurs de clé primaire.

-- Créer une séquence démarrant à 1, incrémentée de 1 CREATE SEQUENCE seq_employe

START WITH 1 INCREMENT BY 1 MAXVALUE 99999 NOCYCLE CACHE 20; -- Utilisation lors
d'une insertion INSERT INTO employes (id, nom, salaire) VALUES (seq_employe.NEXTVAL,
'Alice Martin', 4200); -- Consulter la valeur courante (après au moins un NEXTVAL)
SELECT seq_employe.CURRVAL FROM dual; -- Supprimer la séquence DROP SEQUENCE
seq_employe;

■ NEXTVAL incrémente et retourne la nouvelle valeur. CURRVAL retourne la dernière valeur produite par
NEXTVAL dans la session courante. CACHE pré-alloue des valeurs en mémoire pour de meilleures
performances.

■ 1.4 Index

Un index est une structure de données qui accélère les recherches sur une colonne, au prix d'un espace
de stockage supplémentaire. Il contient la valeur de la clé et l'adresse du bloc contenant cette ligne.
 -- Index standard sur une colonne CREATE INDEX idx_employe_nom ON employes (nom); --
Index unique (garantit l'unicité en plus de l'accélération) CREATE UNIQUE INDEX
idx_employe_email ON employes (email); -- Index composite sur plusieurs colonnes
CREATE INDEX idx_emp_dept ON employes (dept_id, nom); -- Renommer un index ALTER
INDEX idx_employe_nom RENAME TO idx_emp_nom; -- Supprimer un index DROP INDEX
idx_emp_nom;

■ Créez un index si : la colonne est souvent utilisée dans WHERE ou JOIN, la table contient beaucoup de
lignes, ou la colonne a de nombreuses valeurs NULL. Trop d'index ralentissent les
INSERT/UPDATE/DELETE.

■ 1.5 Synonymes

Un synonyme est un alias pour un objet de la base (table, vue, séquence…). Il simplifie les noms longs
ou masque la structure réelle.

-- Créer un synonyme public (visible par tous les utilisateurs) CREATE PUBLIC SYNONYM
emp FOR rh_schema.employes; -- Utilisation : on peut maintenant écrire SELECT * FROM
emp; -- au lieu de SELECT * FROM rh_schema.employes; -- Supprimer le synonyme DROP
SYNONYM emp;
 PARTIE 2 — LMD : Langage de Manipulation de Données

Le LMD (DML en anglais) permet d'insérer, modifier et supprimer des lignes dans les tables.

■ 2.1 Insertion (INSERT)

-- Insertion en précisant les colonnes (recommandé) INSERT INTO employes (id, nom,
salaire, dept_id) VALUES (seq_employe.NEXTVAL, 'Bob Dupont', 3500, 10); -- Insertion
implicite (dans l'ordre des colonnes de la table) INSERT INTO employes VALUES (2,
'Claire Morel', 4800, 20, 'claire@[Link]'); -- Copier des lignes depuis une autre
table INSERT INTO employes_backup (id, nom, salaire) SELECT id, nom, salaire FROM
employes WHERE dept_id = 10;

■ 2.2 Mise à jour (UPDATE)

-- Augmenter le salaire d'un employé UPDATE employes SET salaire = 5000 WHERE id = 1;
-- Aligner le salaire sur une valeur calculée depuis une autre table UPDATE employes
e SET salaire = (SELECT salaire_ref FROM grilles g WHERE [Link] = [Link]) WHERE
dept_id = 20; -- Mettre à jour plusieurs colonnes UPDATE employes SET salaire = 4500,
dept_id = 30 WHERE nom = 'Bob Dupont';

■ 2.3 Suppression (DELETE)

-- Supprimer toutes les lignes d'une table DELETE FROM employes; -- Supprimer selon
une condition DELETE FROM employes WHERE salaire < 2000; -- Supprimer à partir d'une
sous-requête DELETE FROM employes WHERE dept_id = (SELECT id FROM departements WHERE
nom = 'Comptabilité');

■■ DELETE peut être annulé avec ROLLBACK tant que la transaction n'est pas validée (COMMIT).
TRUNCATE TABLE est irréversible mais beaucoup plus rapide.
 PARTIE 3 — LID : Langage d'Interrogation de Données

Le LID (DQL) est centré sur l'instruction SELECT, qui permet d'interroger la base.

■ 3.1 SELECT de base & alias

-- Sélectionner toutes les colonnes SELECT * FROM employes; -- Sélectionner des

colonnes spécifiques avec alias SELECT nom AS "Nom complet", salaire * 12 "Revenu
annuel" FROM employes; -- Éliminer les doublons SELECT DISTINCT dept_id FROM
employes; -- Concaténation de chaînes SELECT nom || ' travaille au département ' ||
dept_id AS info FROM employes;

■ 3.2 Opérateurs de filtrage (WHERE)

Opérateur Usage Exemple SQL

BETWEEN Plage de valeurs WHERE salaire BETWEEN 2000 AND 5000

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

LIKE Motif de chaîne WHERE nom LIKE 'Du%' -- commence par Du

IS NULL Valeur manquante WHERE email IS NULL

NOT IN Exclusion d'une liste WHERE dept_id NOT IN (40, 50)

AND/OR Combinaison de conditions WHERE salaire > 3000 AND dept_id = 10

■ Ordre de priorité des opérateurs logiques : parenthèses > comparaisons > NOT > AND > OR. Utilisez des
parenthèses pour éviter toute ambiguïté.

■ 3.3 ORDER BY

-- Tri par salaire décroissant SELECT nom, salaire FROM employes ORDER BY salaire
DESC; -- Tri sur plusieurs critères SELECT nom, dept_id, salaire FROM employes ORDER
BY dept_id ASC, salaire DESC;

■ 3.4 Fonctions de caractères

Fonction Description Exemple

LOWER(ch) Tout en minuscules LOWER('ALICE') → 'alice'

UPPER(ch) Tout en majuscules UPPER('alice') → 'ALICE'

INITCAP(ch) 1re lettre de chaque mot en maj INITCAP('alice martin') → 'Alice
Martin'

SUBSTR(ch,pos,long) Extraction d'une sous-chaîne SUBSTR('Oracle',1,3) → 'Ora'

LENGTH(ch) Longueur de la chaîne LENGTH('SQL') → 3

REPLACE(ch,c1,c2) Remplace c1 par c2 REPLACE('SGBD','BD','QL') →

'SGQL'

TRIM(ch) Supprime espaces en début/fin TRIM(' hello ') → 'hello'

INSTR(ch1,ch2) Position de ch2 dans ch1 INSTR('Oracle',3) → position

■ 3.5 Fonctions numériques

Fonction Description Exemple

ROUND(n,p) Arrondi à p décimales ROUND(3.567, 2) → 3.57

TRUNC(n,p) Tronque à p décimales TRUNC(3.567, 2) → 3.56

MOD(n1,n2) Reste de la division MOD(10,3) → 1

POWER(n,e) Puissance POWER(2,10) → 1024

SQRT(n) Racine carrée SQRT(144) → 12

ABS(n) Valeur absolue ABS(-42) → 42

CEIL(n) Entier supérieur ou égal CEIL(3.2) → 4

FLOOR(n) Entier inférieur ou égal FLOOR(3.9) → 3

■ 3.6 Fonctions de conversion

-- TO_CHAR : convertit une date ou un nombre en chaîne SELECT TO_CHAR(SYSDATE,

'DD/MM/YYYY HH24:MI') AS auj FROM dual; -- Résultat : '14/05/2024 09:30' -- TO_DATE :
convertit une chaîne en date SELECT * FROM employes WHERE date_embauche >
TO_DATE('01/01/2020', 'DD/MM/YYYY'); -- TO_NUMBER : convertit une chaîne en nombre
SELECT * FROM employes WHERE salaire >= TO_NUMBER('3000');

■ 3.7 Fonctions multi-lignes (GROUP BY)

-- Nombre total d'employés SELECT COUNT(*) AS nb_employes FROM employes; -- Salaire

moyen, min et max par département SELECT dept_id, AVG(salaire) AS salaire_moyen,
MIN(salaire) AS salaire_min, MAX(salaire) AS salaire_max, SUM(salaire) AS
masse_salariale FROM employes GROUP BY dept_id HAVING AVG(salaire) > 3000 ORDER BY
salaire_moyen DESC;
 ■ HAVING filtre les groupes (après GROUP BY), à la différence de WHERE qui filtre les lignes (avant
GROUP BY). AVG, SUM, MIN, MAX et COUNT ignorent les valeurs NULL.

■ 3.8 Fonctions analytiques (OVER)

Les fonctions analytiques calculent une valeur pour chaque ligne en tenant compte d'un ensemble de
lignes voisines (fenêtre), sans réduire le nombre de résultats.

-- Numéroter les employés par ordre de salaire décroissant SELECT nom, salaire,
ROW_NUMBER() OVER (ORDER BY salaire DESC) AS rang FROM employes; -- Classer avec
ex-aequo (RANK laisse des trous, DENSE_RANK non) SELECT nom, salaire, RANK() OVER
(ORDER BY salaire DESC) AS rang_avec_trous, DENSE_RANK() OVER (ORDER BY salaire DESC)
AS rang_continu FROM employes; -- Premier et dernier salaire par département SELECT
dept_id, nom, salaire, FIRST_VALUE(salaire) OVER (PARTITION BY dept_id ORDER BY
salaire DESC) AS max_dept, LAST_VALUE(salaire) OVER (PARTITION BY dept_id ORDER BY
salaire DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS min_dept
FROM employes;

■ 3.9 Jointures

Type de jointure Description Résultat

INNER JOIN Lignes ayant une correspondance dans les deux Intersection
tables

LEFT OUTER JOIN Toutes les lignes de la table gauche + Gauche + intersection
correspondances

RIGHT OUTER JOIN Toutes les lignes de la table droite + Droite + intersection
correspondances

FULL OUTER JOIN Toutes les lignes des deux tables Union complète

NATURAL JOIN JOIN automatique sur colonnes de même nom Variable

Auto-jointure Jointure d'une table avec elle-même Hiérarchie

-- INNER JOIN : employés avec le nom de leur département SELECT [Link], [Link] AS
departement FROM employes e INNER JOIN departements d ON e.dept_id = [Link]; -- LEFT
OUTER JOIN : tous les employés, même sans département SELECT [Link], [Link] AS
departement FROM employes e LEFT OUTER JOIN departements d ON e.dept_id = [Link]; --
Auto-jointure : trouver les managers SELECT [Link] AS employe, [Link] AS manager FROM
employes e INNER JOIN employes m ON e.manager_id = [Link];
 PARTIE 4 — PL/SQL : Programmation Procédurale

PL/SQL (Procedural Language / SQL) est l'extension procédurale d'Oracle. Il permet d'écrire des blocs de
code structurés avec des variables, des boucles, des conditions et la gestion des erreurs.

■ 4.1 Structure d'un bloc PL/SQL

DECLARE -- Section de déclaration (optionnelle) v_nom VARCHAR2(50); v_salaire

NUMBER(10,2) := 0; v_date DATE DEFAULT SYSDATE; c_taux CONSTANT NUMBER := 0.1; --
constante BEGIN -- Section d'exécution (obligatoire) SELECT nom, salaire INTO v_nom,
v_salaire FROM employes WHERE id = 1; DBMS_OUTPUT.PUT_LINE('Employé : ' || v_nom);
DBMS_OUTPUT.PUT_LINE('Salaire : ' || v_salaire); EXCEPTION -- Section de gestion des
erreurs (optionnelle) WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('Aucun employé
trouvé.'); WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Erreur : ' || SQLERRM); END; /

■ 4.2 Structures de contrôle

-- IF / ELSIF / ELSE IF v_salaire > 5000 THEN DBMS_OUTPUT.PUT_LINE('Salaire élevé');

ELSIF v_salaire > 3000 THEN DBMS_OUTPUT.PUT_LINE('Salaire moyen'); ELSE
DBMS_OUTPUT.PUT_LINE('Salaire bas'); END IF; -- CASE CASE v_dept WHEN 10 THEN
DBMS_OUTPUT.PUT_LINE('Comptabilité'); WHEN 20 THEN
DBMS_OUTPUT.PUT_LINE('Informatique'); ELSE DBMS_OUTPUT.PUT_LINE('Autre'); END CASE;
-- Boucle LOOP avec EXIT DECLARE i NUMBER := 1; BEGIN LOOP DBMS_OUTPUT.PUT_LINE('Tour
' || i); i := i + 1; EXIT WHEN i > 5; END LOOP; END; / -- Boucle FOR (1 à 5) BEGIN
FOR i IN 1..5 LOOP DBMS_OUTPUT.PUT_LINE('Tour ' || i); END LOOP; END; / -- Boucle
WHILE DECLARE i NUMBER := 1; BEGIN WHILE i <= 5 LOOP i := i + 1; END LOOP; END; /

■ 4.3 Curseurs

Un curseur est un pointeur vers le résultat d'une requête SELECT multi-lignes. Il permet de parcourir les
résultats ligne par ligne.

-- Curseur explicite avec LOOP DECLARE CURSOR cur_emp IS SELECT nom, salaire FROM
employes WHERE dept_id = 10 ORDER BY salaire DESC; v_nom [Link]%TYPE; v_salaire
[Link]%TYPE; BEGIN OPEN cur_emp; LOOP FETCH cur_emp INTO v_nom, v_salaire;
EXIT WHEN cur_emp%NOTFOUND; -- sortir si plus de lignes DBMS_OUTPUT.PUT_LINE(v_nom ||
' : ' || v_salaire); END LOOP; CLOSE cur_emp; END; / -- Curseur FOR (plus simple,
ouverture/fermeture automatiques) BEGIN FOR rec IN (SELECT nom, salaire FROM employes
WHERE dept_id = 10) LOOP DBMS_OUTPUT.PUT_LINE([Link] || ' : ' || [Link]); END
LOOP; END; / -- Curseur paramétré DECLARE CURSOR cur_dept (p_dept NUMBER) IS SELECT
nom FROM employes WHERE dept_id = p_dept; BEGIN FOR rec IN cur_dept(20) LOOP
DBMS_OUTPUT.PUT_LINE([Link]); END LOOP; END; /

Attribut Signification
%ISOPEN Vrai si le curseur est actuellement ouvert

%FOUND Vrai si le dernier FETCH a retourné une ligne

%NOTFOUND Vrai si le dernier FETCH n'a retourné aucune ligne

%ROWCOUNT Nombre de lignes lues depuis l'ouverture du curseur

■ 4.4 Procédures et fonctions stockées

-- Procédure stockée : augmenter le salaire d'un département CREATE OR REPLACE

PROCEDURE augmenter_salaires ( p_dept IN NUMBER, p_pct IN NUMBER, p_nb_maj OUT NUMBER
) IS BEGIN UPDATE employes SET salaire = salaire * (1 + p_pct/100) WHERE dept_id =
p_dept; p_nb_maj := SQL%ROWCOUNT; COMMIT; END augmenter_salaires; / -- Appel de la
procédure DECLARE v_nb NUMBER; BEGIN augmenter_salaires(10, 5, v_nb);
DBMS_OUTPUT.PUT_LINE(v_nb || ' employé(s) mis à jour.'); END; / -- Fonction stockée :
calculer la prime CREATE OR REPLACE FUNCTION calc_prime (p_salaire NUMBER) RETURN
NUMBER IS BEGIN IF p_salaire > 5000 THEN RETURN p_salaire * 0.15; ELSE RETURN
p_salaire * 0.10; END IF; END calc_prime; / -- Utilisation de la fonction dans SELECT
SELECT nom, salaire, calc_prime(salaire) AS prime FROM employes;

■ 4.5 Triggers

Un trigger (déclencheur) est un bloc PL/SQL qui s'exécute automatiquement en réponse à un événement
(INSERT, UPDATE, DELETE) sur une table.

-- Trigger de table : journaliser toute modification CREATE OR REPLACE TRIGGER

trig_audit_employes AFTER INSERT OR UPDATE OR DELETE ON employes BEGIN CASE WHEN
INSERTING THEN DBMS_OUTPUT.PUT_LINE('Nouvel employé ajouté.'); WHEN UPDATING THEN
DBMS_OUTPUT.PUT_LINE('Employé modifié.'); WHEN DELETING THEN
DBMS_OUTPUT.PUT_LINE('Employé supprimé.'); END CASE; END; / -- Trigger de ligne (FOR
EACH ROW) : limiter les baisses de salaire CREATE OR REPLACE TRIGGER trig_salaire_min
BEFORE UPDATE OF salaire ON employes FOR EACH ROW WHEN ([Link] < [Link] *
0.9) -- baisse > 10% BEGIN RAISE_APPLICATION_ERROR(-20001, 'Baisse de salaire
supérieure à 10% interdite.'); END; /

■ Dans un trigger FOR EACH ROW : :NEW contient les nouvelles valeurs, :OLD contient les anciennes
valeurs. Les triggers BEFORE peuvent modifier :NEW avant l'écriture.

■ 4.6 Gestion des exceptions

Exception Oracle Déclenchée quand…

NO_DATA_FOUND SELECT INTO ne retourne aucune ligne

TOO_MANY_ROWS SELECT INTO retourne plus d'une ligne

ZERO_DIVIDE Division par zéro

DUP_VAL_ON_INDEX Violation de contrainte UNIQUE ou PRIMARY KEY

INVALID_CURSOR Opération sur un curseur non ouvert

CURSOR_ALREADY_OPEN Tentative d'ouverture d'un curseur déjà ouvert

INVALID_NUMBER Conversion de chaîne en nombre invalide

ROWTYPE_MISMATCH Incompatibilité de types entre curseur et variable

-- Exemple de gestion complète des exceptions CREATE OR REPLACE PROCEDURE get_employe

(p_id IN NUMBER) IS v_nom [Link]%TYPE; v_salaire [Link]%TYPE; --
Exception utilisateur personnalisée ex_salaire_negatif EXCEPTION; BEGIN SELECT nom,
salaire INTO v_nom, v_salaire FROM employes WHERE id = p_id; IF v_salaire < 0 THEN
RAISE ex_salaire_negatif; END IF; DBMS_OUTPUT.PUT_LINE(v_nom || ' gagne ' ||
v_salaire); EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('Employé
introuvable (id=' || p_id || ')'); WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE('Plusieurs employés trouvés !'); WHEN ex_salaire_negatif THEN
DBMS_OUTPUT.PUT_LINE('Erreur : salaire négatif détecté.'); WHEN OTHERS THEN ROLLBACK;
DBMS_OUTPUT.PUT_LINE('Erreur ' || SQLCODE || ' : ' || SQLERRM); END get_employe; /

Ce guide couvre les concepts fondamentaux de SQL Oracle et PL/SQL. Pour approfondir, consultez la
documentation officielle Oracle Database sur [Link].