Bases de données : Vues, Déclencheurs, Transactions, Procédures stockées et Fonctions

Vues

Une vue est une table virtuelle, c'est-à-dire qu'elle n'existe pas physiquement dans la base de données. Essentiellement, une vue est une requête SQL nommée qui renvoie un ensemble de données dynamique. Les utilisateurs peuvent interagir avec une vue comme s'il s'agissait d'une table réelle.

L'utilisation des vues peut simplifier des requêtes complexes en encapsulant des sous-requêtes ou des jointures dans un objet nommé. Cependant, les vues peuvent introduire des problèmes de performance, et une dépendance excessive aux vues stockées dans la base de données peut entraîner un couplage fort entre l'application et la base de données, rendant les modifications SQL difficiles à étendre.

Création d'une Vue

Voici un exemple illustrant la création d'une vue pour simplifier une requête conditionnelle.


-- Tables initiales
SELECT * FROM course;
-- +-----+--------+------------+
-- | cid | cname  | teacher_id |
-- +-----+--------+------------+
-- |   1 | Biologie|          1 |
-- |   2 | Physique|          2 |
-- |   3 | Sport   |          3 |
-- |   4 | Art     |          2 |
-- +-----+--------+------------+

SELECT * FROM teacher;
-- +-----+-----------------+
-- | tid | tname           |
-- +-----+-----------------+
-- |   1 | Prof. Zhang     |
-- |   2 | Prof. Li        |
-- |   3 | Prof. Liu       |
-- |   4 | Prof. Zhu       |
-- |   5 | Prof. Jian      |
-- +-----+-----------------+

-- Requête originale pour trouver les cours enseignés par le Prof. Li
SELECT cname FROM course WHERE teacher_id = (SELECT tid FROM teacher WHERE tname='Prof. Li');
-- +--------+
-- | cname  |
-- +--------+
-- | Physique|
-- | Art     |
-- +--------+

-- Création d'une vue pour stocker l'ID du Prof. Li
CREATE VIEW prof_li_id AS SELECT tid FROM teacher WHERE tname='Prof. Li';

-- Utilisation de la vue dans la requête
SELECT cname FROM course WHERE teacher_id = (SELECT tid FROM prof_li_id);
-- +--------+
-- | cname  |
-- +--------+
-- | Physique|
-- | Art     |
-- +--------+

Note importante : Bien que l'utilisation de vues évite de réécrire des sous-requêtes, leur performance peut être inférieure à celle des sous-requêtes directes. De plus, les vues stockées dans la base de données créent une forte dépendance. Les modifications des vues nécessitent souvent une intervention du DBA, ce qui peut être un processus long et coûteux.

Utilisation et Modification des Vues

Les vues peuvent être utilisées pour interroger des données, et dans certains cas, elles peuvent également être utilisées pour insérer, mettre à jour ou supprimer des données dans la table sous-jacente. Cependant, la modification des enregistrements via des vues impliquant plusieurs tables n'est généralement pas possible.


-- Création d'une vue sur la table 'course'
CREATE VIEW course_view AS SELECT cid, cname, teacher_id FROM course;

-- Sélection des données via la vue
SELECT * FROM course_view;
-- +-----+--------+------------+
-- | cid | cname  | teacher_id |
-- +-----+--------+------------+
-- |   1 | Biologie|          1 |
-- |   2 | Physique|          2 |
-- |   3 | Sport   |          3 |
-- |   4 | Art     |          2 |
-- +-----+--------+------------+

-- Mise à jour des données via la vue (affecte la table 'course')
UPDATE course_view SET cname='Mathématiques' WHERE cid=1;

-- Insertion de données via la vue (affecte la table 'course')
INSERT INTO course_view (cid, cname, teacher_id) VALUES (5, 'Chimie', 3);

-- Vérification des modifications dans la table originale 'course'
SELECT * FROM course;
-- +-----+--------------+------------+
-- | cid | cname        | teacher_id |
-- +-----+--------------+------------+
-- |   1 | Mathématiques|          1 |
-- |   2 | Physique     |          2 |
-- |   3 | Sport        |          3 |
-- |   4 | Art          |          2 |
-- |   5 | Chimie       |          3 |
-- +-----+--------------+------------+

-- Modification de la définition d'une vue
ALTER VIEW prof_li_id AS SELECT * FROM course WHERE cid > 2;

-- Sélection des données après modification de la vue
SELECT * FROM prof_li_id;
-- +-----+--------+------------+
-- | cid | cname  | teacher_id |
-- +-----+--------+------------+
-- |   3 | Sport  |          3 |
-- |   4 | Art    |          2 |
-- |   5 | Chimie |          3 |
-- +-----+--------+------------+

Suppression d'une Vue


DROP VIEW prof_li_id;

Déclencheurs (Triggers)

Les déclencheurs permettent d'exécuter automatiquement des actions avant ou après des opérations de modification (INSERT, UPDATE, DELETE) sur une table spécifique. Ils ne s'activent pas pour les opérations de lecture (SELECT).

Création d'un Déclencheur

La syntaxe générale pour créer un déclencheur est la suivante :


-- Avant insertion
CREATE TRIGGER trigger_name BEFORE INSERT ON table_name FOR EACH ROW BEGIN ... END;

-- Après insertion
CREATE TRIGGER trigger_name AFTER INSERT ON table_name FOR EACH ROW BEGIN ... END;

-- Avant suppression
CREATE TRIGGER trigger_name BEFORE DELETE ON table_name FOR EACH ROW BEGIN ... END;

-- Après suppression
CREATE TRIGGER trigger_name AFTER DELETE ON table_name FOR EACH ROW BEGIN ... END;

-- Avant mise à jour
CREATE TRIGGER trigger_name BEFORE UPDATE ON table_name FOR EACH ROW BEGIN ... END;

-- Après mise à jour
CREATE TRIGGER trigger_name AFTER UPDATE ON table_name FOR EACH ROW BEGIN ... END;

Exemple : Créer un déclencheur qui enregistre les commandes échouées dans une table de journalisation.


-- Préparation des tables
CREATE TABLE cmd (
   id INT PRIMARY KEY AUTO_INCREMENT,
   USER CHAR(32),
   priv CHAR(10),
   cmd CHAR(64),
   sub_time DATETIME,
   success ENUM('yes', 'no')
);

CREATE TABLE errlog (
   id INT PRIMARY KEY AUTO_INCREMENT,
   err_cmd CHAR(64),
   err_time DATETIME
);

-- Définition du délimiteur pour les blocs BEGIN...END
DELIMITER //

-- Création du déclencheur qui s'exécute après une insertion dans 'cmd'
CREATE TRIGGER tri_after_insert_cmd AFTER INSERT ON cmd FOR EACH ROW
BEGIN
   -- Vérifie si la commande a échoué (NEW fait référence à la ligne nouvellement insérée)
   IF NEW.success = 'no' THEN
       -- Insère l'échec dans la table errlog
       INSERT INTO errlog (err_cmd, err_time) VALUES (NEW.cmd, NEW.sub_time);
   END IF;
END//

-- Rétablissement du délimiteur par défaut
DELIMITER ;

-- Insertion de données dans la table 'cmd' pour déclencher le trigger
INSERT INTO cmd (USER, priv, cmd, sub_time, success) VALUES
   ('elton', '0755', 'ls -l /etc', NOW(), 'yes'),
   ('elton', '0755', 'cat /etc/passwd', NOW(), 'no'),
   ('elton', '0755', 'useradd xxx', NOW(), 'no'),
   ('elton', '0755', 'ps aux', NOW(), 'yes');

-- Vérification de la table errlog
SELECT * FROM errlog;
-- +----+-----------------+---------------------+
-- | id | err_cmd         | err_time            |
-- +----+-----------------+---------------------+
-- |  1 | cat /etc/passwd | 2023-10-27 10:00:00 | -- (Le temps sera celui de l'exécution)
-- |  2 | useradd xxx     | 2023-10-27 10:00:00 |
-- +----+-----------------+---------------------+

Dans un déclencheur, NEW fait référence à la ligne qui est sur le point d'être insérée ou mise à jour, tandis que OLD fait référence à la ligne qui est sur le point d'être supprimée ou mise à jour.

Suppression d'un Déclencheur


DROP TRIGGER tri_after_insert_cmd;

Transactions

Une transaction regroupe plusieurs opérations SQL en une seule unité atomique. Si l'une des opérations échoue, toutes les modifications effectuées dans le cadre de la transaction sont annulées (rollback), garantissant ainsi l'intégrité des données.

Gestion des Transactions

Les commandes clés sont START TRANSACTION (ou BEGIN), COMMIT (pour valider les changements) et ROLLBACK (pour annuler les changements).


-- Préparation de la table 'user'
CREATE TABLE user (
   id INT PRIMARY KEY AUTO_INCREMENT,
   name CHAR(32),
   balance INT
);

INSERT INTO user(name, balance) VALUES
('Alice', 1000),
('Bob', 1000),
('Charlie', 1000);

-- Début d'une transaction pour un transfert d'argent
START TRANSACTION;

-- Opérations : Alice envoie 100 à Bob, Bob donne 10 à Charlie
UPDATE user SET balance = balance - 100 WHERE name = 'Alice'; -- Alice dépense 100
UPDATE user SET balance = balance + 90 WHERE name = 'Bob';   -- Bob reçoit 90 (après déduction du frais)
UPDATE user SET balance = balance + 10 WHERE name = 'Charlie'; -- Charlie reçoit 10

-- Validation de la transaction si tout s'est bien passé
COMMIT;

-- Exemple avec annulation (rollback) en cas d'erreur
START TRANSACTION;

UPDATE user SET balance = balance - 100 WHERE name = 'Alice';
UPDATE user SET balance = balance + 90 WHERE name = 'Bob';
-- Supposons qu'une erreur se produise ici, par exemple une faute de frappe :
-- UPDATE user SET balance = balance + 10 WHERE name = 'Charliee'; -- Nom incorrect

-- Si une erreur est détectée, on annule tout :
ROLLBACK;

-- Si la transaction est annulée, les données reviennent à leur état initial
SELECT * FROM user;
-- +----+---------+---------+
-- | id | name    | balance |
-- +----+---------+---------+
-- |  1 | Alice   |    1000 |
-- |  2 | Bob     |    1000 |
-- |  3 | Charlie |    1000 |
-- +----+---------+---------+

Procédures Stockées

Présentation

Une procédure stockée est un ensemble d'instructions SQL compilées qui sont stockées dans la base de données. Elles peuvent être appelées par leur nom pour exécuter la séquence d'instructions.

Avantages :

Modularité : Réduit le code SQL répétitif dans les applications.
Performance : Le SQL est précompilé et stocké, réduisant la latence réseau lors de l'appel.
Sécurité : Permet de contrôler l'accès aux données en accordant des permissions d'exécution sur les procédures plutôt que sur les tables directement.

Inconvénients :

Maintenance : Les modifications nécessitent souvent une intervention sur la base de données, ce qui peut être moins pratique que de modifier le code de l'application.
Débogage : Le débogage des procédures stockées peut être plus complexe.

Création de Procédures Stockées

Procédure sans paramètre :


DELIMITER //
CREATE PROCEDURE ShowAllBlogs()
BEGIN
   SELECT id, name, sub_time FROM blog;
   INSERT INTO blog (name, sub_time) VALUES ('Nouveau Blog', NOW());
END //
DELIMITER ;

-- Appel de la procédure
CALL ShowAllBlogs();

Paramètres (IN, OUT, INOUT) :

IN : Paramètre d'entrée, utilisé pour passer des valeurs à la procédure.
OUT : Paramètre de sortie, utilisé pour renvoyer une valeur depuis la procédure.
INOUT : Paramètre combinant entrée et sortie.


-- Procédure avec un paramètre IN
DELIMITER //
CREATE PROCEDURE GetBlogsAfterId(IN start_id INT)
BEGIN
   SELECT id, name, sub_time FROM blog WHERE id > start_id;
END //
DELIMITER ;

-- Appel avec un paramètre IN
CALL GetBlogsAfterId(3);

-- Procédure avec un paramètre OUT
DELIMITER //
CREATE PROCEDURE GetBlogCount(OUT blog_count INT)
BEGIN
   SELECT COUNT(*) INTO blog_count FROM blog;
END //
DELIMITER ;

-- Appel avec un paramètre OUT
SET @count = 0;
CALL GetBlogCount(@count);
SELECT @count;

-- Procédure avec un paramètre INOUT
DELIMITER //
CREATE PROCEDURE IncrementId(INOUT current_id INT)
BEGIN
   SELECT id, name, sub_time FROM blog WHERE id = current_id;
   SET current_id = current_id + 1; -- Modifie la valeur du paramètre
END //
DELIMITER ;

-- Appel avec un paramètre INOUT
SET @my_id = 1;
CALL IncrementId(@my_id);
SELECT @my_id; -- Devrait afficher 2 si le blog avec ID 1 existe

Exécution de Procédures Stockées


-- Sans paramètres
CALL procedure_name();

-- Avec des paramètres IN
CALL procedure_name(value1, value2);

-- Avec des paramètres IN, OUT, INOUT
SET @out_param = 0;
SET @inout_param = 5;
CALL procedure_name(value1, @out_param, @inout_param);
SELECT @out_param, @inout_param;

Suppression d'une Procédure Stockée


DROP PROCEDURE procedure_name;

Fonctions

Fonctions Intégrées

MySQL fournit une riche bibliothèque de fonctions prédéfinies pour diverses opérations.

Fonctions Mathématiques : ROUND(), RAND(), etc.
Fonctions d'Agrégation : AVG(), COUNT(), SUM(), GROUP_CONCAT().
Fonctions de Chaîne : CONCAT(), LENGTH(), SUBSTRING(), UPPER(), LOWER().
Fonctions Date et Heure : NOW(), DATE_FORMAT(), YEAR(), MONTH().
Fonctions de Hachage/Chiffrement : MD5(), PASSWORD().
Fonctions de Contrôle de Flux : IF(), IFNULL(), CASE.

Exemple avec `DATE_FORMAT` :


-- Préparation de la table 'blog'
CREATE TABLE blog (
   id INT PRIMARY KEY AUTO_INCREMENT,
   name CHAR(32),
   sub_time DATETIME
);

INSERT INTO blog (name, sub_time) VALUES
('Article 1', '2015-03-01 11:31:21'),
('Article 2', '2015-03-11 16:31:21'),
('Article 3', '2016-07-01 10:21:31'),
('Article 4', '2016-07-22 09:23:21'),
('Article 5', '2016-07-23 10:11:11'),
('Article 6', '2016-07-25 11:21:31'),
('Article 7', '2017-03-01 15:33:21'),
('Article 8', '2017-03-01 17:32:21'),
('Article 9', '2017-03-01 18:31:21');

-- Compter les articles par mois d'ajout
SELECT DATE_FORMAT(sub_time, '%Y-%m') AS month, COUNT(1) AS count
FROM blog
GROUP BY month;
-- +---------+-------+
-- | month   | count |
-- +---------+-------+
-- | 2015-03 |     2 |
-- | 2016-07 |     4 |
-- | 2017-03 |     3 |
-- +---------+-------+

Fonctions Définies par l'Utilisateur (UDF)

Il est possible de créer ses propres fonctions SQL pour encapsuler une logique réutilisable.


DELIMITER //
-- Fonction simple qui additionne deux entiers
CREATE FUNCTION AddNumbers(num1 INT, num2 INT)
RETURNS INT
DETERMINISTIC -- Indique que la fonction retourne toujours le même résultat pour les mêmes entrées
BEGIN
   DECLARE result INT;
   SET result = num1 + num2;
   RETURN result;
END //
DELIMITER ;

-- Appel de la fonction définie par l'utilisateur
SELECT AddNumbers(10, 5); -- Résultat : 15

-- Utilisation dans une requête
SELECT id, name, AddNumbers(id, 100) AS modified_id FROM blog;

Note : Les fonctions définies par l'utilisateur ne peuvent pas contenir d'instructions SQL qui modifient les données (INSERT, UPDATE, DELETE). Pour cela, utilisez les procédures stockées.

Suppression d'une Fonction


DROP FUNCTION function_name;

Contrôle de Flux

Les procédures stockées et les fonctions peuvent utiliser des structures de contrôle de flux comme les conditions et les boucles.

Instructions Conditionnelles


DELIMITER //
CREATE PROCEDURE CheckNumber(IN num INT)
BEGIN
   IF num > 10 THEN
       SELECT 'Number is greater than 10';
   ELSEIF num = 10 THEN
       SELECT 'Number is equal to 10';
   ELSE
       SELECT 'Number is less than 10';
   END IF;
END //
DELIMITER ;

CALL CheckNumber(15);
CALL CheckNumber(10);
CALL CheckNumber(5);

Boucles

WHILE :


DELIMITER //
CREATE PROCEDURE Countdown(IN start_num INT)
BEGIN
   DECLARE counter INT DEFAULT start_num;
   WHILE counter > 0 DO
       SELECT counter;
       SET counter = counter - 1;
   END WHILE;
END //
DELIMITER ;

CALL Countdown(3);

REPEAT :


DELIMITER //
CREATE PROCEDURE RepeatExample(IN limit_num INT)
BEGIN
   DECLARE i INT DEFAULT 0;
   REPEAT
       SELECT i;
       SET i = i + 1;
   UNTIL i >= limit_num
   ENDREPEAT;
END //
DELIMITER ;

CALL RepeatExample(3);

LOOP avec LEAVE et ITERATE :


DELIMITER //
CREATE PROCEDURE LoopExample()
BEGIN
   DECLARE i INT DEFAULT 0;
   my_loop: LOOP
       SET i = i + 1;
       IF i < 5 THEN
           ITERATE my_loop; -- Similaire à 'continue' dans d'autres langages
       END IF;
       IF i >= 10 THEN
           LEAVE my_loop; -- Similaire à 'break'
       END IF;
       SELECT i; -- S'exécute seulement si i est entre 5 et 9 inclus
   END LOOP my_loop;
END //
DELIMITER ;

CALL LoopExample();

Étiquettes: SQL Vues déclencheurs transactions Procédures stockées

Publié le 7 juillet à 18h47

L'Atelier Monstre

Bases de données : Vues, Déclencheurs, Transactions, Procédures stockées et Fonctions

Vues

Création d'une Vue

Utilisation et Modification des Vues

Suppression d'une Vue

Déclencheurs (Triggers)

Création d'un Déclencheur

Suppression d'un Déclencheur

Transactions

Gestion des Transactions

Procédures Stockées

Présentation

Création de Procédures Stockées

Exécution de Procédures Stockées

Suppression d'une Procédure Stockée

Fonctions

Fonctions Intégrées

Exemple avec `DATE_FORMAT` :

Fonctions Définies par l'Utilisateur (UDF)

Suppression d'une Fonction

Contrôle de Flux

Instructions Conditionnelles

Boucles

Étiquettes Populaires

L'Atelier Monstre

Bases de données : Vues, Déclencheurs, Transactions, Procédures stockées et Fonctions

Vues

Création d'une Vue

Utilisation et Modification des Vues

Suppression d'une Vue

Déclencheurs (Triggers)

Création d'un Déclencheur

Suppression d'un Déclencheur

Transactions

Gestion des Transactions

Procédures Stockées

Présentation

Création de Procédures Stockées

Exécution de Procédures Stockées

Suppression d'une Procédure Stockée

Fonctions

Fonctions Intégrées

Exemple avec DATE_FORMAT :

Fonctions Définies par l'Utilisateur (UDF)

Suppression d'une Fonction

Contrôle de Flux

Instructions Conditionnelles

Boucles

Étiquettes Populaires

Exemple avec `DATE_FORMAT` :