Procedures (Procédures)

Procédures stockées (Procedures)

Les procédures stockées sont des blocs de code SQL qui peuvent être appelés et exécutés à partir d’autres parties de votre code SQL.

Elles permettent de regrouper des instructions SQL en un seul bloc logique et de les exécuter à partir de n’importe où dans votre code.

Les procédures stockées sont souvent utilisées pour effectuer des tâches répétitives, pour améliorer les performances et pour simplifier la maintenance du code.

Elles s’apparentent aux fonctions dans les langages de programmation.

Procedures et SQLite

Pas de procédures stockées dans SQLite

SQLite étant un moteur de base de données léger, il ne prend pas en charge les procédures stockées pour des raisons de simplicité et de performances.

Certains plugins SQLite et l’utilisation de fonctions définies par l’utilisateur (Application-Defined SQL Functions) peuvent être utilisés pour simuler des procédures stockées.

SQLite étant souvent encapsulé dans des applications, les procédures stockées sont généralement gérées par le code de l’application.

Il est sinon tout à fait possible de remplacer les procédures par des transactions.

Les procédures stockées sont cependant prises en charge par la plupart des autres moteurs de base de données, tels que MySQL, PostgreSQL, SQL Server, Oracle, etc.

Les exemples présentés ont été vérifié avec MySQL 9 et supérieur. Il faudra peut-être adapter les exemples pour d’autres moteurs de base de données.

Si vous voulez tester les exemples de procédures, vous pouvez installer MySQL sur votre machine ou utiliser un service en ligne comme db-fiddle.

Création d’une procédure

Comme pour les triggers, on change le délimiteur pour définir une procédure stockée.

DELIMITER //
CREATE PROCEDURE nom_procedure( param1 TYPE, param2 TYPE)
BEGIN
    -- Instructions
END//
DELIMITER ;

Exemple

DELIMITER //
CREATE PROCEDURE ajoute_100_aux_comptes()
BEGIN
    UPDATE Comptes
    SET solde = solde + 100
    WHERE id = 1;
END//
DELIMITER ;

Version commentée

DELIMITER // -- On change le délimiteur
CREATE PROCEDURE ajoute_100_aux_comptes() -- Nom de la procédure
BEGIN -- Début du bloc
    UPDATE Comptes -- Instruction SQL
    SET solde = solde + 100
    WHERE id = 1;
END// -- Fin du bloc
DELIMITER ; -- On rétablit le délimiteur par défaut

Version transaction pour SQLite :

BEGIN;
UPDATE Comptes
SET solde = solde + 100
WHERE id = 1;
COMMIT;

Paramètres

Les procédures stockées peuvent accepter des paramètres en entrée et retourner des valeurs en sortie.

CREATE PROCEDURE nom_procedure( IN _param1 TYPE, OUT _param2 TYPE, INOUT _param3 TYPE)
BEGIN
    -- Instructions
END;

Exemple

DELIMITER //
CREATE PROCEDURE maj_solde( IN _id_compte INT, IN _montant INT)
BEGIN
    UPDATE Comptes
    SET solde = solde + _montant
    WHERE id =  _id_compte;
END//
DELIMITER ;

Paramètres de sortie

Les procédures stockées peuvent également retourner des valeurs en sortie.

Contrairement aux langages de programmation qui utilisent le mot-clé return, les procédures stockées utilisent le mot-clé OUT sur les paramètres de sortie.

Après execution de la procédure, les valeurs des paramètres de sortie peuvent être récupérées.

CREATE PROCEDURE nom_procedure( INOUT _param1 TYPE, OUT _param2 TYPE)
BEGIN
    -- Instructions
END;

Exemple

DELIMITER //
CREATE PROCEDURE solde_max( OUT _max_solde INT )
BEGIN
    SELECT MAX(solde) INTO  _max_solde
    FROM Comptes;
END//
DELIMITER ;

CALL solde_max(@_max_solde); -- Appel de la procédure

SELECT @_max_solde; -- Affichage de la valeur de sortie

”Programmation” dans les procédures

Variables locales

Les procédures stockées peuvent déclarer des variables locales pour stocker des valeurs temporaires.

CREATE PROCEDURE nom_procedure()
BEGIN
    DECLARE nom_variable TYPE;
    -- Instructions
END;

Pas de support des variables dans SQLite

SQLite ne supporte pas les variables locales dans les procédures stockées.

On pourra créer des tables temporaires pour stocker des valeurs temporaires et donc simuler des variables locales.

Exemple

DROP PROCEDURE IF EXISTS maj_solde;
DELIMITER //
CREATE PROCEDURE maj_solde( IN _id_compte INT, IN  _montant INT)
BEGIN
    DECLARE _solde_compte INT;

    SELECT solde INTO _solde_compte FROM Comptes WHERE id =  _id_compte;

    UPDATE Comptes
    SET solde = _solde_compte +  _montant
    WHERE id =  _id_compte;
END//
DELIMITER ;

Programmation

Curseurs

Les curseurs sont utilisés pour parcourir les résultats d’une requête SQL.

Les curseurs sont souvent utilisés dans les procédures stockées pour traiter les résultats d’une requête ligne par ligne.

DECLARE nom_curseur CURSOR FOR SELECT colonne1, colonne2 FROM table;
OPEN nom_curseur;
FETCH nom_curseur INTO variable1, variable2;
CLOSE nom_curseur;

Exemple

DELIMITER //
CREATE PROCEDURE affiche_comptes()
BEGIN
    DECLARE id_compte INT;
    DECLARE solde_compte INT;
    DECLARE cur CURSOR FOR SELECT id, solde FROM Comptes;
    OPEN cur;
    FETCH cur INTO id_compte, solde_compte;
    WHILE @FETCH_STATUS = 0 DO
        SELECT id_compte, solde_compte;
        FETCH cur INTO id_compte, solde_compte;
        -- Ici on pourra executer des instructions qui utilisent les valeurs des colonnes
    END WHILE;
    CLOSE cur;
END//
DELIMITER ;

CALL affiche_comptes();

Programmation

Instruction CASE

Les instructions CASE peuvent être utilisées dans les procédures stockées pour effectuer des opérations conditionnelles.

CASE
    WHEN condition1 THEN instruction1
    WHEN condition2 THEN instruction2
    ELSE instruction3
END CASE;

Exemple

DROP PROCEDURE IF EXISTS maj_solde;
DELIMITER //
CREATE PROCEDURE maj_solde( IN _id_compte INT, IN  _montant INT)
BEGIN
    DECLARE _solde_compte INT;

    SELECT solde INTO _solde_compte FROM Comptes WHERE id =  _id_compte;

    UPDATE Comptes
    SET solde = CASE
        WHEN _solde_compte > 1000 THEN _solde_compte +  _montant
        ELSE _solde_compte
    END
    WHERE id =  _id_compte;
END//
DELIMITER ;

Programmation

Instruction IF

Les instructions IF peuvent être utilisées dans les procédures stockées pour effectuer des opérations conditionnelles.

IF condition THEN instruction1;
ELSE instruction2;
END IF;

Exemple

DROP PROCEDURE IF EXISTS maj_solde;
DELIMITER //
CREATE PROCEDURE maj_solde( _id_compte INT,  _montant INT)
BEGIN
    DECLARE _solde_compte INT;

    SELECT solde INTO _solde_compte FROM Comptes WHERE id =  _id_compte;

    IF _solde_compte > 1000 THEN
        UPDATE Comptes
        SET solde = _solde_compte +  _montant
        WHERE id =  _id_compte;
    END IF;
END//
DELIMITER ;

Appel d’une procédure

CALL nom_procedure();

Exemples

CALL maj_solde(1, 100);

En utilisant un paramètre de sortie :

CALL solde_max(@_max);
SELECT @_max;

Remarque

Lors de l’appel d’une procédure stockée, les paramètres de sortie doivent être précédés du caractère @.

Suppression d’une procédure

DROP PROCEDURE IF EXISTS nom_procedure;

Procédures et Javascript

Certains SGBD comme MySQL supporte désormais le langage Javascript pour écrire des procédures stockées.

CREATE PROCEDURE maj_solde( _id_compte INT,  _montant INT)
LANGUAGE JAVASCRIPT AS $$
    let solde_compte = 0;
    let query = `SELECT solde FROM Comptes WHERE id = ${_id_compte}`;
    let result = mysql.query(query);
    if (result.length > 0) {
        solde_compte = result[0].solde;
    }
    let new_solde = solde_compte + _montant;
    query = `UPDATE Comptes SET solde = ${new_solde} WHERE id = ${_id_compte}`;
    mysql.query(query);
$$;

Procédures et langages de programmation

Appel de procédures dans une application

Voici quelques exemples d’appels de procédures stockées dans différentes langages de programmation.

PHP

<?php
$pdo = new PDO('mysql:host=localhost;dbname;', 'user', 'password'); // Connexion à la base de données
$stmt = $pdo->prepare("CALL maj_solde(1, 100)"); // Préparation de la requête SQL
$stmt->execute(); // Execution de la procédure
?>

Python

import mysql.connector
mydb = mysql.connector.connect(host="localhost", user="user", password="password", database="db" )
mycursor = mydb.cursor()
mycursor.callproc('maj_solde', (1, 100))
mydb.commit()

NodeJS

const mysql = require('mysql');
const connection = mysql.createConnection({ host: 'localhost', user: 'user', password: 'password', database: 'db' });
connection.connect();
connection.query('CALL maj_solde(1, 100)', function (error, results, fields) {
    if (error) throw error;
    console.log('Solde mis à jour');
});
connection.end();

Java

import java.sql.*;
public class Main {
    public static void main(String[] args) {
        try {
            Connection conn = DriverManager.getConnection("jdbc:mysql://localhost/db", "user", "password");
            CallableStatement cstmt = conn.prepareCall("{call maj_solde(?, ?)}");
            cstmt.setInt(1, 1);
            cstmt.setInt(2, 100);
            cstmt.execute();
            System.out.println("Solde mis à jour");
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Procédure et SQLite

SQLite ne supporte pas les procédures stockées, il faudra définir les procédures dans le code de l’application.

PHP

<?php
$GLOBALS["pdo"] = new PDO('sqlite:ma_base.db'); // Connexion à la base de données

function maj_solde($id_compte, $montant) {
    global $pdo;
    $stmt = $pdo->prepare("SELECT solde FROM Comptes WHERE id = ?");
    $stmt->execute([$id_compte]);
    $solde = $stmt->fetchColumn();
    $new_solde = $solde + $montant;
    $stmt = $pdo->prepare("UPDATE Comptes SET solde = ? WHERE id = ?");
    $stmt->execute([$new_solde, $id_compte]);
}

maj_solde(1, 100);
?>

Python

import sqlite3

function maj_solde(id_compte, montant):
    conn = sqlite3.connect('ma_base.db')
    cur = conn.cursor()
    cur.execute("SELECT solde FROM Comptes WHERE id = ?", (id_compte,))
    solde = cur.fetchone()[0]
    new_solde = solde + montant
    cur.execute("UPDATE Comptes SET solde = ? WHERE id = ?", (new_solde, id_compte))
    conn.commit()

maj_solde(1, 100)

NodeJS

const sqlite3 = require('sqlite3').verbose();
const db = new sqlite3.Database('ma_base.db');

function maj_solde(id_compte, montant) {
    db.serialize(() => {
        db.get("SELECT solde FROM Comptes WHERE id = ?", [id_compte], (err, row) => {
            if (err) {
                console.error(err.message);
            }
            let solde = row.solde;
            let new_solde = solde + montant;
            db.run("UPDATE Comptes SET solde = ? WHERE id = ?", [new_solde, id_compte], (err) => {
                if (err) {
                    console.error(err.message);
                }
                console.log(`Solde mis à jour`);
            });
        });
    });
}

maj_solde(1, 100);

Java

import java.sql.*;

public class Main {

    public void maj_solde(int id_compte, int montant) {
        try {
            Connection conn = DriverManager.getConnection("jdbc:sqlite:ma_base.db");
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery("SELECT solde FROM Comptes WHERE id = 1");
            int solde = rs.getInt("solde");
            int new_solde = solde + 100;
            stmt.executeUpdate("UPDATE Comptes SET solde = " + new_solde + " WHERE id = 1");
            System.out.println("Solde mis à jour");
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static void main(String[] args) {
        Main main = new Main();
        main.maj_solde(1, 100);
    }
}

Conclusion

• Les procédures stockées permettent de regrouper des instructions SQL en un seul bloc logique
• Elles peuvent ensuite être appelées directement depuis le SGBD ou bien depuis une application
• Elles permettent de simplifier la maintenance du code, d’améliorer les performances et de réduire la duplication du code.
• Pour SQLite quqi ne supporte pas les procédures, il faudra définir les procédures dans le code de l’application

Exercice

Créez une procédure stockée bonus qui :

• augmente le solde des comptes d’un pourcentage passé en paramètre, uniquement si le solde est supérieur au montant total de ses commandes
• qui renvoie les identifiants des comptes mis à jour

Solution

CREATE PROCEDURE bonus( _pourcentage INT)
BEGIN
    DECLARE _id_compte INT;
    DECLARE _solde_compte INT;
    DECLARE _total_commandes INT;
    DECLARE _bonus INT;
    DECLARE _cur CURSOR FOR SELECT id, solde FROM Comptes;
    OPEN _cur;
    FETCH _cur INTO _id_compte, _solde_compte;
    WHILE @FETCH_STATUS = 0 DO
        SELECT SUM(montant) INTO _total_commandes FROM Commandes WHERE id_compte = _id_compte;
        IF _solde_compte > _total_commandes THEN
            SET _bonus = _solde_compte *  _pourcentage / 100;
            UPDATE Comptes SET solde = solde + _bonus WHERE id = _id_compte;
            SELECT _id_compte;
        END IF;
        FETCH _cur INTO    id_compte, _solde_compte;
    END WHILE;
    CLOSE _cur;
END;