Fonctions, procédures, curseurs et déclencheurs en SQL

Guide complet : fonctions, procédures, curseurs et déclencheurs dans SQL

Dans les systèmes de gestion de bases de données relationnelles (SGBDR), divers composants tels que les fonctions, les procédures, les curseurs et les déclencheurs jouent un rôle essentiel rôles dans l’amélioration de la flexibilité et de la fonctionnalité des systèmes de bases de données. Ils permettent aux développeurs de mettre en œuvre une logique métier personnalisée, d'automatiser les tâches répétitives et de gérer les données plus efficacement.

Ce guide fournira une explication complète de ces composants, ainsi que des exemples d'extraits de code pour chacun.

---

1. Fonctions en SQL

Une fonction en SQL est un programme stocké qui peut accepter des entrées, effectuer des opérations et renvoyer une valeur. C'est similaire à une procédure, mais une fonction doit renvoyer une valeur et elle peut être utilisée dans des requêtes comme n'importe quelle autre expression.

Points clés :

• Les fonctions peuvent être invoquées à partir de requêtes.
• Les fonctions renvoient une valeur unique.
• Ils peuvent prendre des paramètres d'entrée.
• Ils sont généralement utilisés pour les calculs, les transformations et la récupération de données.

Exemple de fonction (syntaxe SQL Server)

Écrivons une fonction simple qui calcule le carré d'un nombre.

CREATE FUNCTION dbo.SquareNumber (@Number INT)
RETURNS INT
AS
BEGIN
    RETURN @Number * @Number
END

Utilisation :

SELECT dbo.SquareNumber(4); -- Output: 16

Cette fonction prend un entier en entrée, calcule son carré et renvoie le résultat.

---

2. Procédures en SQL

Une procédure (également appelée procédure stockée) est un ensemble d'instructions SQL qui peuvent être exécutées comme une unité. Les procédures peuvent prendre des paramètres, effectuer des opérations telles que l'insertion, la mise à jour, la suppression et la sélection, et renvoyer plusieurs résultats (mais pas directement une seule valeur comme les fonctions).

Points clés :

• Les procédures ne renvoient pas nécessairement une valeur, mais elles peuvent renvoyer plusieurs ensembles de résultats.
• Ils peuvent effectuer plusieurs opérations.
• Les procédures peuvent être invoquées explicitement à l'aide de la commande EXEC.

Exemple de procédure (syntaxe SQL Server)

Écrivons une procédure pour mettre à jour le salaire d'un employé.

CREATE PROCEDURE dbo.UpdateSalary 
    @EmployeeID INT, 
    @NewSalary DECIMAL
AS
BEGIN
    UPDATE Employees
    SET Salary = @NewSalary
    WHERE EmployeeID = @EmployeeID;
END

Utilisation :

EXEC dbo.UpdateSalary @EmployeeID = 101, @NewSalary = 75000;

Cette procédure prend un EmployeeID et un NewSalary comme entrées, met à jour le salaire de l'employé et ne renvoie aucune valeur.

---

3. Curseurs en SQL

Un curseur en SQL est un objet de base de données qui vous permet de récupérer et de traiter chaque ligne renvoyée par une requête une à la fois. Ceci est particulièrement utile lorsque vous devez effectuer des opérations ligne par ligne, telles que des mises à jour ou des suppressions, qui ne sont pas facilement gérées dans une seule opération basée sur un ensemble.

Points clés :

• Les curseurs peuvent être utilisés pour parcourir les ensembles de résultats de requête.
• Ils sont généralement utilisés lorsque les opérations basées sur des ensembles ne suffisent pas.
• Les curseurs peuvent être classés en différents types (statiques, dynamiques, avant uniquement, etc.).

Exemple de curseur (syntaxe SQL Server)

Écrivons un exemple en utilisant un curseur pour mettre à jour le salaire de tous les employés de 10 %

CREATE FUNCTION dbo.SquareNumber (@Number INT)
RETURNS INT
AS
BEGIN
    RETURN @Number * @Number
END

Explication :

1. Nous déclarons un curseur SalaryCursor qui sélectionne l'EmployeeID et le Salary dans la table Employees.
2. Nous ouvrons le curseur et récupérons la première ligne dans les variables.
3. Dans la boucle WHILE, nous mettons à jour le salaire de chaque employé en le multipliant par 1,1 (augmentation de 10%).
4. Après avoir traité toutes les lignes, nous fermons et libérons le curseur.

---

4. Déclencheurs en SQL

Un déclencheur est un type spécial de procédure stockée qui s'exécute automatiquement (ou se "déclenche") lorsque des événements de base de données spécifiques se produisent, tels que INSERT, UPDATE ou DELETE sur une table. Les déclencheurs sont utiles pour appliquer des règles métier, maintenir l'intégrité des données ou mettre à jour automatiquement les tables associées lorsque des modifications se produisent.

Points clés :

• Les déclencheurs peuvent être AVANT ou APRÈS l'événement (insérer, mettre à jour, supprimer).
• Les déclencheurs peuvent se déclencher une fois par instruction ou une fois par ligne (selon le type).
• Ils sont souvent utilisés pour appliquer des règles d'intégrité ou suivre les modifications.

Exemple de déclencheur (syntaxe SQL Server)

Créons un déclencheur qui met automatiquement à jour la colonne LastModified chaque fois que le salaire d'un employé est mis à jour.

SELECT dbo.SquareNumber(4); -- Output: 16

Explication :

1. Le déclencheur trg_UpdateSalary se déclenche après une opération UPDATE sur la table Employees.
2. À l'intérieur du déclencheur, nous vérifions si la colonne Salaire a été mise à jour à l'aide de la fonction UPDATE().
3. Si le Salaire a été mis à jour, nous modifions la colonne LastModified avec la date et l'heure actuelles (GETDATE()).
4. La table insérée est une table spéciale qui contient les nouvelles valeurs après l'opération de mise à jour, et nous l'utilisons pour mettre à jour le champ LastModified pour le(s) employé(s) modifié(s).

---

Résumé des composants SQL

Component	Description	Example Use Case
Function	A stored program that returns a single value and can be used in queries.	Calculate the square of a number.
Procedure	A stored program that can perform multiple actions (insert, update, delete) but does not return a value.	Update an employee’s salary.
Cursor	A mechanism for iterating over a result set row-by-row, used for operations that cannot be easily expressed in set-based SQL.	Update all employees’ salaries by a fixed percentage.
Trigger	A stored program that automatically executes when specific database events (INSERT, UPDATE, DELETE) occur.	Automatically update a timestamp column when a record is modified.

---

Conclusion

• Les Fonctions et procédures sont essentielles pour modulariser la logique métier et les opérations réutilisables dans la base de données. Les fonctions sont davantage axées sur le renvoi d'une valeur, tandis que les procédures peuvent gérer plusieurs tâches mais ne renvoient pas de valeurs directement.
• Les Curseurs sont utilisés lorsque vous devez traiter des données ligne par ligne, bien que les opérations basées sur des ensembles soient généralement plus efficaces.
• Les Déclencheurs permettent des réponses automatiques aux événements de la base de données, garantissant l'intégrité des données et appliquant les règles sans nécessiter d'intervention manuelle.

Chacun de ces composants remplit un objectif unique en rendant votre base de données plus flexible, plus maintenable et plus efficace, en particulier dans les environnements de bases de données complexes.

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!