Partie 2 · Logique

Fonctions et procédures stockées

Le responsable technique demande d'exploiter explicitement des fonctions et des procédures stockées. Il ne s'agit pas de prouver que tu connais la syntaxe, mais de démontrer que chaque mécanisme répond à un vrai besoin du cas.

Fonctions SQL

Il faut proposer au moins deux fonctions utiles : une fonction de calcul ou d'aide à la qualification métier (le score), une fonction de transformation ou d'interprétation d'un résultat (par exemple convertir un score en niveau faible/moyen/élevé), éventuellement une fonction de mise en forme ou de catégorisation. Pour chacune, il faut expliquer : son rôle, ses paramètres, sa valeur de retour, les hypothèses prises et les cas limites à gérer (par exemple un budget NULL).

Procédures stockées

Plusieurs procédures structurantes : une procédure d'insertion ou d'intégration d'un marché, une procédure de traitement d'un lot, une procédure d'alimentation ou de rafraîchissement du dashboard, éventuellement une procédure de maintenance ou de purge. Le cas précise : « Vous ne devez pas livrer une simple suite d'INSERT » : la logique de contrôle, de calcul, d'échec et de traçabilité doit être pensée dès la conception.

Fonction	Procédure
(hors cours, notion générale) calcule et renvoie une valeur, utilisable dans une requête	(hors cours) exécute une suite d'actions appelée explicitement

Vérifie que tu as compris

Parmi ces propositions, laquelle est une bonne fonction SQL pour ce cas ?

Ce que SQLite ne sait pas faire

SQLite n'a ni procédures stockées ni fonctions SQL définies par l'utilisateur en SQL pur (hors cours). On exprime donc la règle du score en SQL avec CASE (c'est le calcul que la fonction encapsulerait), ou on écrit la procédure côté Python. Dans un SGBD comme MySQL (hors cours, exemple ajouté), on écrirait CREATE FUNCTION / CREATE PROCEDURE, avec CALL pour appeler une procédure et un gestionnaire d'erreur pour annuler un traitement en échec.

L'important, indépendamment du SGBD, est la qualité de la logique : contrôler les données, calculer, gérer l'échec, tracer.

À toi de coder

Calculer un score et un niveau (faible, moyen, élevé)

La qualification attribue un score selon des critères (mots-clés, montant, localisation) puis un niveau faible / moyen / élevé. Les points et seuils ci-dessous sont un exemple ajouté (le cours n'impose pas de barème) : +3 si le titre contient « modulaire » ou « préfabriqu », +2 si le budget est ≥ 500000, +1 si la localisation est « Île-de-France » ; niveau élevé si score ≥ 5, moyen si ≥ 3, sinon faible.

SQLite n'a pas de fonctions stockées ; on exprime la règle avec CASE. Un budget NULL ne rapporte aucun point. Renvoie id, score, niveau trié par id.

import sqlite3
con = sqlite3.connect(":memory:", isolation_level=None)
con.executescript("""
CREATE TABLE marche(id INTEGER PRIMARY KEY, titre TEXT, budget REAL, localisation TEXT);
INSERT INTO marche VALUES
 (1,'Classes modulaires temporaires',600000,'Île-de-France'),
 (2,'Voirie communale',80000,'Bretagne'),
 (3,'Base vie préfabriquée chantier',120000,'Occitanie'),
 (4,'Extension modulaire école',NULL,'Île-de-France');
""")
sql = """
SELECT id, score,
       CASE WHEN score >= ___ THEN 'élevé' WHEN score >= ___ THEN 'moyen' ELSE 'faible' END AS niveau
FROM (
  SELECT id,
         (CASE WHEN titre LIKE '%modulaire%' OR titre LIKE '%préfabriqu%' THEN 3 ELSE 0 END)
       + (CASE WHEN budget >= ___ THEN 2 ELSE 0 END)
       + (CASE WHEN localisation = 'Île-de-France' THEN 1 ELSE 0 END) AS score
  FROM marche
)
ORDER BY id
"""
rows = con.execute(sql).fetchall()

Ctrl/⌘+Entrée

Vérifie que tu as compris

Un budget est NULL dans une annonce. Que doit prévoir la fonction de score ?