Procédures stockées (moteur de base de données)

Une procédure stockée dans SQL Server est un groupe d’une ou plusieurs instructions Transact-SQL ou une référence à une méthode CLR (Common Runtime Language) microsoft .NET Framework. Les procédures ressemblent à des constructions dans d’autres langages de programmation, car elles peuvent :

• Acceptez les paramètres d’entrée et retournez plusieurs valeurs sous la forme de paramètres de sortie au programme appelant.

• Contiennent des instructions de programmation qui effectuent des opérations dans la base de données. Il s’agit notamment d’appeler d’autres procédures.

• Retournez une valeur de statut à un programme appelant pour indiquer la réussite ou l’échec (et la raison de l’échec).

Avantages de l’utilisation de procédures stockées

La liste suivante décrit certains avantages de l’utilisation de procédures.

Trafic réseau serveur/client réduit
Les commandes d'une procédure sont exécutées comme un seul lot de codes. Cela peut réduire considérablement le trafic réseau entre le serveur et le client, car seul l'appel pour exécuter la procédure est envoyé sur le réseau. Sans l’encapsulation de code fournie par une procédure, chaque ligne de code individuelle doit traverser le réseau.

Sécurité renforcée
Plusieurs utilisateurs et programmes clients peuvent effectuer des opérations sur les objets de base de données sous-jacents par le biais d'une procédure, même si les utilisateurs et les programmes n'ont pas d'autorisations directes sur ces objets sous-jacents. La procédure contrôle les processus et activités effectués et protège les objets de base de données sous-jacents. Cela élimine la nécessité d'accorder des autorisations au niveau de l'objet individuel et simplifie les couches de sécurité.

La clause EXECUTE AS peut être spécifiée dans l’instruction CREATE PROCEDURE pour permettre l’emprunt d’identité d’un autre utilisateur, ou permettre aux utilisateurs ou applications d’effectuer certaines activités de base de données sans avoir besoin d’autorisations directes sur les objets et commandes sous-jacents. Par exemple, certaines actions telles que TRUNCATE TABLE ne disposent pas d’autorisations accordées. Pour exécuter TRUNCATE TABLE, l’utilisateur doit disposer d’autorisations ALTER sur la table spécifiée. L’octroi d’autorisations de modification sur une table peut ne pas être idéal, car l’utilisateur aura des pouvoirs nettement supérieurs à la simple capacité de tronquer une table. En incorporant l’instruction TRUNCATE TABLE dans un module et en spécifiant que ce module s’exécute en tant qu’utilisateur disposant d’autorisations pour modifier la table, vous pouvez étendre les autorisations pour tronquer la table à l’utilisateur auquel vous accordez des autorisations EXECUTE sur le module.

Lors de l’appel d’une procédure sur le réseau, seul l’appel à exécuter la procédure est visible. Par conséquent, les utilisateurs malveillants ne peuvent pas voir les noms d’objets de table et de base de données, incorporer des instructions Transact-SQL propres ou rechercher des données critiques.

L’utilisation de paramètres de procédure permet de se protéger contre les attaques par injection SQL. Étant donné que l’entrée de paramètre est traitée comme une valeur littérale et non comme du code exécutable, il est plus difficile pour un attaquant d’insérer une commande dans les instructions Transact-SQL dans la procédure et de compromettre la sécurité.

Les procédures peuvent être chiffrées, ce qui permet d’obfusquer le code source. Pour plus d’informations, consultez CHIFFREMENT SQL Server.

Réutilisation du code
Le code de toute opération de base de données répétitive est le candidat idéal pour l’encapsulation dans les procédures. Cela élimine les réécritures inutiles du même code, réduit l’incohérence du code et permet au code d’être accessible et exécuté par n’importe quel utilisateur ou application possédant les autorisations nécessaires.

Maintenance plus facile
Lorsque les applications clientes appellent des procédures et conservent les opérations de base de données dans la couche Données, seules les procédures doivent être mises à jour pour toutes les modifications apportées dans la base de données sous-jacente. La couche Application reste distincte et n’a pas à savoir comment modifier les dispositions, relations ou processus de base de données.

performances améliorées
Par défaut, une procédure compile la première fois qu’elle est exécutée et crée un plan d’exécution réutilisé pour les exécutions suivantes. Étant donné que le processeur de requêtes n’a pas besoin de créer un plan, il faut généralement moins de temps pour traiter la procédure.

S’il y a eu une modification significative des tables ou des données référencées par la procédure, le plan précompilé peut en fait entraîner l’exécution plus lente de la procédure. Dans ce cas, recompiler la procédure et forcer un nouveau plan d’exécution peut améliorer les performances.

Types de procédures stockées

Définie par l'utilisateur
Une procédure définie par l’utilisateur peut être créée dans une base de données définie par l’utilisateur ou dans toutes les bases de données système, à l’exception de la base de données resource . La procédure peut être développée dans Transact-SQL ou en tant que référence à une méthode CLR (Common Runtime Language) microsoft .NET Framework.

Temporaire
Les procédures temporaires sont une forme de procédures définies par l’utilisateur. Les procédures temporaires sont comme une procédure permanente, mais les procédures temporaires sont stockées dans tempdb. Il existe deux types de procédures temporaires : locales et globales. Elles se différencient par leur nom, leur visibilité et leur disponibilité. Les procédures temporaires locales ont un seul signe numérique (#) comme premier caractère de leur nom ; elles sont visibles uniquement pour la connexion utilisateur actuelle et sont supprimées lorsque la connexion est fermée. Les procédures temporaires globales ont deux signes numériques (##) comme les deux premiers caractères de leur nom ; ils sont visibles par n’importe quel utilisateur une fois qu’ils ont été créés et supprimés à la fin de la dernière session à l’aide de la procédure.

Système
Les procédures système sont incluses dans SQL Server. Ils sont physiquement stockés dans la base de données de ressources interne, masquée et apparaissent logiquement dans le schéma sys de chaque base de données définie par le système et l’utilisateur. En outre, la base de données msdb contient également des procédures stockées système dans le schéma dbo qui sont utilisées pour planifier des alertes et des travaux. Étant donné que les procédures système commencent par le préfixe sp_, nous vous recommandons de ne pas utiliser ce préfixe lors de l’affectation de noms aux procédures définies par l’utilisateur. Pour obtenir la liste complète des procédures système, consultez Procédures stockées système (Transact-SQL)

SQL Server prend en charge les procédures système qui fournissent une interface de SQL Server vers des programmes externes pour diverses activités de maintenance. Ces procédures étendues utilisent le préfixe xp_. Pour obtenir la liste complète des procédures étendues, consultez Procédures stockées étendues générales (Transact-SQL).

User-Defined étendu
Les procédures étendues permettent de créer des routines externes dans un langage de programmation tel que C. Ces procédures sont des DLL qu’une instance de SQL Server peut charger et exécuter dynamiquement.

Tâches associées

Contenu associé

Procédures stockées CLR