Tutoriel Oracle SQL - CoderFacile

← Retour aux tutoriels

Si vous voulez transposer vos réflexes Oracle vers PostgreSQL, consultez aussi le tutoriel PostgreSQL SQL, qui inclut un guide de différences Oracle / PostgreSQL pour adapter vos requêtes plus vite.

Parcours d'apprentissage (novice vers expert)

Objectif: passer des requêtes simples à des requêtes robustes et optimisées en environnement réel.

Niveau novice: lire les requêtes de base (SELECT, WHERE, ORDER BY).
Niveau intermédiaire: maîtriser jointures, sous-requêtes, fonctions analytiques.
Niveau avancé: analyser et optimiser les plans d'exécution.

Fonctionnement: exécute chaque requête, observe le résultat, puis explique pourquoi la requête retourne ces lignes.

1. Requêtes de base (DQL)

SELECT

-- Sélection simple
SELECT * FROM employees;

SELECT employee_id, first_name, last_name, salary
FROM employees;

-- DISTINCT
SELECT DISTINCT department_id FROM employees;

-- Alias
SELECT 
    first_name AS prenom,
    last_name AS nom,
    salary * 12 AS salaire_annuel
FROM employees;

-- Concaténation
SELECT first_name || ' ' || last_name AS nom_complet
FROM employees;

WHERE (filtrage)

-- Opérateurs de comparaison
SELECT * FROM employees WHERE salary > 5000;
SELECT * FROM employees WHERE hire_date >= DATE '2020-01-01';

-- Opérateurs logiques
SELECT * FROM employees 
WHERE salary BETWEEN 5000 AND 10000
  AND department_id IN (10, 20, 30);

-- LIKE (pattern matching)
SELECT * FROM employees WHERE last_name LIKE 'S%';
SELECT * FROM employees WHERE email LIKE '%@oracle.com';
SELECT * FROM employees WHERE phone_number LIKE '___-____'; -- 3 puis 4 caractères

-- IS NULL / IS NOT NULL
SELECT * FROM employees WHERE commission_pct IS NULL;

-- Expressions régulières (Oracle 10g+)
SELECT * FROM employees 
WHERE REGEXP_LIKE(email, '^[A-Z0-9._%+-]+@[A-Z0-9.-]+\.[A-Z]{2,}$', 'i');

2. Tri et limitation

-- ORDER BY
SELECT * FROM employees 
ORDER BY salary DESC;

SELECT * FROM employees 
ORDER BY department_id ASC, salary DESC;

-- NULLS FIRST / NULLS LAST
SELECT * FROM employees 
ORDER BY commission_pct NULLS LAST;

-- FETCH FIRST (Oracle 12c+)
SELECT * FROM employees 
ORDER BY salary DESC
FETCH FIRST 10 ROWS ONLY;

-- OFFSET (pagination)
SELECT * FROM employees 
ORDER BY employee_id
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

-- ROWNUM (ancien style)
SELECT * FROM (
    SELECT * FROM employees ORDER BY salary DESC
)
WHERE ROWNUM <= 10;

3. Jointures

INNER JOIN

-- ANSI SQL-92 (recommandé)
SELECT e.first_name, e.last_name, d.department_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id;

-- Oracle style (ancien)
SELECT e.first_name, e.last_name, d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id;

OUTER JOIN

-- LEFT OUTER JOIN
SELECT e.first_name, d.department_name
FROM employees e
LEFT OUTER JOIN departments d ON e.department_id = d.department_id;

-- RIGHT OUTER JOIN
SELECT e.first_name, d.department_name
FROM employees e
RIGHT OUTER JOIN departments d ON e.department_id = d.department_id;

-- FULL OUTER JOIN
SELECT e.first_name, d.department_name
FROM employees e
FULL OUTER JOIN departments d ON e.department_id = d.department_id;

-- Oracle style (+)
SELECT e.first_name, d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id(+);  -- LEFT JOIN

SELF JOIN et CROSS JOIN

-- Self join (hiérarchie)
SELECT 
    e.first_name AS employe,
    m.first_name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.employee_id;

-- Cross join (produit cartésien)
SELECT * FROM colors CROSS JOIN sizes;

4. Agrégations

Fonctions d'agrégation

-- COUNT, SUM, AVG, MIN, MAX
SELECT 
    COUNT(*) AS total_employes,
    COUNT(DISTINCT department_id) AS nb_departements,
    AVG(salary) AS salaire_moyen,
    MIN(hire_date) AS premiere_embauche,
    MAX(salary) AS salaire_max
FROM employees;

-- GROUP BY
SELECT 
    department_id,
    COUNT(*) AS nb_employes,
    AVG(salary) AS salaire_moyen
FROM employees
GROUP BY department_id
ORDER BY department_id;

-- HAVING (filtrage après agrégation)
SELECT department_id, AVG(salary) AS avg_sal
FROM employees
GROUP BY department_id
HAVING AVG(salary) > 5000;

GROUP BY avancé

-- ROLLUP (sous-totaux hiérarchiques)
SELECT department_id, job_id, SUM(salary)
FROM employees
GROUP BY ROLLUP(department_id, job_id);

-- CUBE (toutes les combinaisons)
SELECT department_id, job_id, SUM(salary)
FROM employees
GROUP BY CUBE(department_id, job_id);

-- GROUPING SETS
SELECT department_id, job_id, SUM(salary)
FROM employees
GROUP BY GROUPING SETS (
    (department_id, job_id),
    (department_id),
    ()
);

5. Sous-requêtes

Sous-requête scalaire

-- Dans WHERE
SELECT * FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

-- Dans SELECT
SELECT 
    first_name,
    salary,
    (SELECT AVG(salary) FROM employees) AS avg_salary
FROM employees;

Sous-requête IN / NOT IN

SELECT * FROM employees
WHERE department_id IN (
    SELECT department_id 
    FROM departments 
    WHERE location_id = 1700
);

-- EXISTS (plus performant)
SELECT * FROM employees e
WHERE EXISTS (
    SELECT 1 FROM departments d
    WHERE d.department_id = e.department_id
      AND d.location_id = 1700
);

Sous-requête corrélée

-- Employés gagnant plus que la moyenne de leur département
SELECT e1.first_name, e1.salary, e1.department_id
FROM employees e1
WHERE salary > (
    SELECT AVG(salary)
    FROM employees e2
    WHERE e2.department_id = e1.department_id
);

6. Fonctions analytiques (Window Functions)

Ranking

-- ROW_NUMBER (numéro séquentiel)
SELECT 
    first_name,
    salary,
    ROW_NUMBER() OVER (ORDER BY salary DESC) AS rang
FROM employees;

-- RANK (avec ex-aequo, sauts)
SELECT 
    first_name,
    salary,
    RANK() OVER (ORDER BY salary DESC) AS rang
FROM employees;

-- DENSE_RANK (avec ex-aequo, sans sauts)
SELECT 
    first_name,
    salary,
    DENSE_RANK() OVER (ORDER BY salary DESC) AS rang
FROM employees;

-- PARTITION BY (par groupe)
SELECT 
    department_id,
    first_name,
    salary,
    RANK() OVER (
        PARTITION BY department_id 
        ORDER BY salary DESC
    ) AS rang_dept
FROM employees;

Fonctions fenêtrées

-- LAG / LEAD (valeurs précédente/suivante)
SELECT 
    employee_id,
    salary,
    LAG(salary, 1) OVER (ORDER BY employee_id) AS prev_salary,
    LEAD(salary, 1) OVER (ORDER BY employee_id) AS next_salary
FROM employees;

-- FIRST_VALUE / LAST_VALUE
SELECT 
    employee_id,
    salary,
    FIRST_VALUE(salary) OVER (
        PARTITION BY department_id 
        ORDER BY salary DESC
    ) AS highest_in_dept
FROM employees;

-- SUM, AVG avec fenêtre glissante
SELECT 
    employee_id,
    salary,
    SUM(salary) OVER (
        ORDER BY employee_id
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS rolling_sum
FROM employees;

7. Opérateurs ensemblistes

-- UNION (élimine les doublons)
SELECT employee_id FROM employees WHERE department_id = 10
UNION
SELECT employee_id FROM employees WHERE salary > 10000;

-- UNION ALL (garde les doublons, plus rapide)
SELECT employee_id FROM employees WHERE department_id = 10
UNION ALL
SELECT employee_id FROM employees WHERE department_id = 20;

-- INTERSECT
SELECT employee_id FROM employees WHERE department_id = 10
INTERSECT
SELECT employee_id FROM employees WHERE salary > 5000;

-- MINUS (différence)
SELECT employee_id FROM employees WHERE department_id = 10
MINUS
SELECT employee_id FROM employees WHERE salary < 3000;

8. CTE (Common Table Expressions)

-- CTE simple
WITH high_earners AS (
    SELECT * FROM employees WHERE salary > 10000
)
SELECT department_id, COUNT(*) 
FROM high_earners
GROUP BY department_id;

-- CTE multiples
WITH 
dept_avg AS (
    SELECT department_id, AVG(salary) AS avg_sal
    FROM employees
    GROUP BY department_id
),
high_depts AS (
    SELECT department_id FROM dept_avg WHERE avg_sal > 7000
)
SELECT e.* FROM employees e
WHERE e.department_id IN (SELECT department_id FROM high_depts);

-- CTE récursive (hiérarchie)
WITH emp_hierarchy (employee_id, manager_id, level, path) AS (
    -- Ancre
    SELECT employee_id, manager_id, 1, first_name
    FROM employees
    WHERE manager_id IS NULL
    
    UNION ALL
    
    -- Partie récursive
    SELECT e.employee_id, e.manager_id, eh.level + 1, 
           eh.path || ' > ' || e.first_name
    FROM employees e
    JOIN emp_hierarchy eh ON e.manager_id = eh.employee_id
)
SELECT * FROM emp_hierarchy ORDER BY level, employee_id;

9. Modification de données (DML)

INSERT

-- Insert simple
INSERT INTO employees (employee_id, first_name, last_name, email, hire_date)
VALUES (207, 'John', 'Doe', 'jdoe@company.com', SYSDATE);

-- Insert multiple (Oracle 23c+)
INSERT INTO employees (employee_id, first_name, last_name)
VALUES 
    (208, 'Jane', 'Smith'),
    (209, 'Bob', 'Johnson');

-- Insert depuis SELECT
INSERT INTO employees_archive
SELECT * FROM employees WHERE hire_date < DATE '2010-01-01';

-- Insert ALL (conditionnel)
INSERT ALL
    WHEN salary > 10000 THEN INTO high_sal VALUES (employee_id, salary)
    WHEN salary < 5000 THEN INTO low_sal VALUES (employee_id, salary)
    ELSE INTO mid_sal VALUES (employee_id, salary)
SELECT employee_id, salary FROM employees;

UPDATE

-- Update simple
UPDATE employees
SET salary = salary * 1.10
WHERE department_id = 50;

-- Update multiple colonnes
UPDATE employees
SET salary = salary * 1.15,
    commission_pct = 0.05
WHERE job_id = 'SA_REP';

-- Update depuis sous-requête
UPDATE employees e
SET salary = (
    SELECT AVG(salary) * 1.1
    FROM employees
    WHERE department_id = e.department_id
)
WHERE department_id IN (10, 20);

-- MERGE (upsert)
MERGE INTO employees_target t
USING employees_source s ON (t.employee_id = s.employee_id)
WHEN MATCHED THEN
    UPDATE SET t.salary = s.salary
WHEN NOT MATCHED THEN
    INSERT (employee_id, first_name, salary)
    VALUES (s.employee_id, s.first_name, s.salary);

DELETE

-- Delete simple
DELETE FROM employees WHERE employee_id = 999;

-- Delete avec sous-requête
DELETE FROM employees
WHERE department_id IN (
    SELECT department_id FROM departments WHERE location_id = 1700
);

-- TRUNCATE (plus rapide, pas de rollback)
TRUNCATE TABLE temp_data;

10. DDL (Data Definition Language)

CREATE TABLE

-- Table simple
CREATE TABLE customers (
    customer_id NUMBER(10) PRIMARY KEY,
    first_name VARCHAR2(50) NOT NULL,
    last_name VARCHAR2(50) NOT NULL,
    email VARCHAR2(100) UNIQUE,
    created_at DATE DEFAULT SYSDATE,
    is_active NUMBER(1) DEFAULT 1 CHECK (is_active IN (0, 1))
);

-- Contraintes
CREATE TABLE orders (
    order_id NUMBER PRIMARY KEY,
    customer_id NUMBER NOT NULL,
    order_date DATE DEFAULT SYSDATE,
    total_amount NUMBER(10, 2),
    CONSTRAINT fk_customer 
        FOREIGN KEY (customer_id) 
        REFERENCES customers(customer_id)
        ON DELETE CASCADE,
    CONSTRAINT chk_amount CHECK (total_amount >= 0)
);

-- Table depuis SELECT
CREATE TABLE employees_backup AS
SELECT * FROM employees WHERE 1=0;  -- Structure uniquement

ALTER TABLE

-- Ajouter colonne
ALTER TABLE customers ADD phone VARCHAR2(20);

-- Modifier colonne
ALTER TABLE customers MODIFY email VARCHAR2(150);

-- Supprimer colonne
ALTER TABLE customers DROP COLUMN phone;

-- Renommer colonne
ALTER TABLE customers RENAME COLUMN email TO email_address;

-- Ajouter contrainte
ALTER TABLE customers 
ADD CONSTRAINT chk_email CHECK (email LIKE '%@%');

INDEX

-- Index simple
CREATE INDEX idx_customers_email ON customers(email);

-- Index composite
CREATE INDEX idx_orders_cust_date 
ON orders(customer_id, order_date);

-- Index unique
CREATE UNIQUE INDEX idx_customers_email_unique 
ON customers(email);

-- Index basé sur fonction
CREATE INDEX idx_customers_upper_name 
ON customers(UPPER(last_name));

-- Bitmap index (pour faible cardinalité)
CREATE BITMAP INDEX idx_orders_status ON orders(status);

11. Transactions

-- Transaction simple
BEGIN
    UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
    UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;
    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE;
END;
/

-- Savepoint
SAVEPOINT before_delete;
DELETE FROM temp_data;
-- Oops, erreur!
ROLLBACK TO before_delete;

-- Set transaction
SET TRANSACTION READ ONLY;
-- Ou
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

12. Optimisation

EXPLAIN PLAN

-- Analyser une requête
EXPLAIN PLAN FOR
SELECT * FROM employees WHERE department_id = 50;

-- Voir le plan
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

-- AUTOTRACE (SQL*Plus)
SET AUTOTRACE ON EXPLAIN;
SELECT * FROM employees WHERE salary > 5000;

Hints

-- Forcer l'utilisation d'un index
SELECT /*+ INDEX(employees idx_emp_dept) */
    * FROM employees WHERE department_id = 50;

-- Parallélisme
SELECT /*+ PARALLEL(employees, 4) */
    * FROM employees;

-- FULL table scan
SELECT /*+ FULL(employees) */
    * FROM employees WHERE department_id = 50;

-- Ordre de jointure
SELECT /*+ ORDERED */
    e.first_name, d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id;

Conseils d'optimisation

Utiliser EXISTS plutôt que IN pour de grandes tables
Éviter SELECT *, spécifier les colonnes
Utiliser UNION ALL plutôt que UNION si pas de doublons
Créer des index sur les colonnes de jointure et de filtrage
Utiliser BULK COLLECT et FORALL en PL/SQL
Partitionner les très grandes tables
Analyser régulièrement les statistiques : DBMS_STATS.GATHER_TABLE_STATS

Défi progression (vers expert)

Mission: optimiser un rapport SQL réel sur une table volumineuse.

Réussite niveau 1: écrire une requête correcte avec jointures et filtres.
Réussite niveau 2: comparer deux versions via EXPLAIN PLAN.
Réussite niveau 3: proposer indexes/hints argumentés et mesurer le gain.

← Retour aux tutoriels | Voir le tutoriel PL/SQL →