Dernière mise à jour: 14.08.2017

Souvent, une opération de données représente un ensemble d'instructions qui doivent être exécutées dans un certain ordre. Par exemple, lors de l'ajout d'un achat de produit, vous devez saisir des données dans le tableau des commandes. Cependant, avant de procéder, vous devez vérifier si le produit que vous achetez est en stock. Vous devrez peut-être vérifier un certain nombre de conditions supplémentaires. Autrement dit, le processus d'achat d'un produit couvre plusieurs actions qui doivent être effectuées dans un certain ordre. Et dans ce cas, il serait plus optimal d'encapsuler toutes ces actions dans un seul objet - procédure stockée(procédure stockée).

Autrement dit, les procédures stockées sont essentiellement un ensemble d’instructions exécutées comme une seule unité. Ainsi, les procédures stockées permettent de simplifier des opérations complexes et de les placer dans un seul objet. Le processus d'achat d'un produit changera, il suffira donc de changer le code de la procédure. Autrement dit, la procédure simplifie également la gestion du code.

Les procédures stockées vous permettent également de restreindre l'accès aux données des tables et ainsi de réduire la probabilité d'actions indésirables intentionnelles ou non par rapport à ces données.

Et un autre aspect important est la performance. Les procédures stockées s'exécutent généralement plus rapidement que les instructions SQL classiques. En effet, le code de la procédure est compilé une fois lors de son premier lancement, puis enregistré sous forme compilée.

Pour créer une procédure stockée, utilisez la commande CREATE PROCEDURE ou CREATE PROC.

La procédure stockée a donc trois principales caractéristiques: simplification du code, sécurité et performance.

Par exemple, disons qu'il existe une table dans la base de données qui stocke des données sur les produits :

CREATE TABLE Products (Id INT IDENTITY PRIMARY KEY, ProductName NVARCHAR(30) NOT NULL, Fabricant NVARCHAR(20) NOT NULL, ProductCount INT DEFAULT 0, Price MONEY NOT NULL );

Créons une procédure stockée pour récupérer les données de cette table :

UTILISER la base de données produits ; ALLER CRÉER UNE PROCÉDURE Résumé du produit AS SELECT Nom du produit AS Produit, Fabricant, Prix FROM Produits

Étant donné que la commande CREATE PROCEDURE doit être appelée dans un package distinct, la commande USE qui définit la base de données actuelle est suivie d'une commande GO pour définir un nouveau package.

Après le nom de la procédure, il devrait y avoir mot-clé COMME.

Pour séparer le corps d'une procédure du reste du script, le code de la procédure est souvent placé dans un bloc BEGIN...END :

UTILISER la base de données produits ; ALLER CRÉER UNE PROCÉDURE Résumé du produit COMME COMMENCER SELECT Nom du produit AS Produit, Fabricant, Prix FROM Produits FIN ;

Après avoir ajouté la procédure, nous pouvons la voir dans le nœud de base de données dans SQL Server Management Studio dans le sous-nœud Programmabilité -> Procédures stockées:

Et nous pourrons également contrôler la procédure via une interface visuelle.

Exécution de la procédure

Pour exécuter une procédure stockée, appelez la commande EXEC ou EXECUTE :

Résumé du produit EXEC

Supprimer une procédure

Pour supprimer une procédure, utilisez la commande DROP PROCEDURE :

PROCÉDURE DE DROPRésumé du produit

Stocké Procédures SQL sont un module de programme exécutable qui peut être stocké sous la forme de divers objets. En d'autres termes, c'est un objet qui contient des instructions SQL. Ces procédures stockées peuvent être exécutées dans le client d'application pour obtenir bonne performance. De plus, ces objets sont souvent appelés depuis d'autres scripts ou même depuis une autre section.

Introduction

Beaucoup de gens pensent qu'elles ressemblent à diverses procédures (respectivement, à l'exception de MS SQL). C'est peut-être vrai. Ils ont des paramètres similaires et peuvent produire des valeurs similaires. De plus, dans certains cas, ils se touchent. Par exemple, ils sont combinés avec des bases de données DDL et DML, ainsi qu'avec des fonctions utilisateur (nom de code UDF).

En réalité, les procédures stockées SQL ont large éventail avantages qui les distinguent des processus similaires. Sécurité, flexibilité de programmation, productivité - tout cela attire de plus en plus d'utilisateurs travaillant avec des bases de données. Le pic de popularité des procédures s'est produit en 2005-2010, lorsqu'un programme de Microsoft appelé « SQL Server Management Studio » a été publié. Avec son aide, travailler avec des bases de données est devenu beaucoup plus simple, plus pratique et plus pratique. D’année en année, celui-ci gagnait en popularité auprès des programmeurs. Aujourd'hui, il s'agit d'un programme absolument familier qui, pour les utilisateurs qui « communiquent » avec des bases de données, est comparable à Excel.

Lorsqu'une procédure est appelée, elle est immédiatement traitée par le serveur lui-même sans processus inutiles et l'intervention des utilisateurs. Après cela, vous pouvez effectuer toute suppression, exécution ou modification. L'opérateur DDL est responsable de tout cela, qui effectue seul les actions les plus complexes pour traiter les objets. De plus, tout cela se passe très rapidement, et le serveur n'est pas réellement chargé. Cette vitesse et ces performances vous permettent de transférer très rapidement de grandes quantités d'informations de l'utilisateur vers le serveur et vice versa.

Pour mettre en œuvre cette technologie de travail avec l'information, il existe plusieurs langages de programmation. Il s'agit par exemple du PL/SQL d'Oracle, du PSQL dans les systèmes InterBase et Firebird, ainsi que du classique Microsoft Transact-SQL. Tous sont conçus pour créer et exécuter des procédures stockées, ce qui permet aux grands processeurs de bases de données d'utiliser leurs propres algorithmes. Ceci est également nécessaire pour que ceux qui gèrent ces informations puissent protéger tous les objets contre tout accès non autorisé par des tiers et, par conséquent, contre la création, la modification ou la suppression de certaines données.

Productivité

Ces objets de base de données peuvent être programmés de différentes manières. Cela permet aux utilisateurs de choisir le type de méthode utilisée qui est la plus appropriée, économisant ainsi du temps et des efforts. De plus, la procédure est traitée elle-même, ce qui évite le temps considérable consacré à la communication entre le serveur et l'utilisateur. Le module peut également être reprogrammé et modifié en la bonne directionà tout moment absolument. Il convient particulièrement de noter la rapidité avec laquelle la procédure stockée SQL est lancée : ce processus se produit plus rapidement que d'autres similaires, ce qui le rend pratique et universel.

Sécurité

Ce type de traitement de l'information se distingue des processus similaires en ce qu'il garantit une sécurité accrue. Ceci est assuré par le fait que l'accès aux procédures par d'autres utilisateurs peut être complètement exclu. Cela permettra à l'administrateur d'effectuer des opérations avec eux de manière indépendante, sans crainte d'interception d'informations ou d'accès non autorisé à la base de données.

Transfert de données

La relation entre la procédure stockée SQL et l'application client réside dans l'utilisation de paramètres et de valeurs de retour. Ce dernier n'a pas besoin de transmettre les données à la procédure stockée, mais ces informations (principalement à la demande de l'utilisateur) sont traitées pour SQL. Une fois que la procédure stockée a terminé son travail, elle renvoie les paquets de données (mais encore une fois, facultativement) à l'application qui l'a appelée, en utilisant diverses méthodes, à l'aide duquel un appel à une procédure SQL stockée et un retour peuvent être effectués, par exemple :

Transférer des données à l'aide d'un paramètre de type Sortie ;

Transmission des données à l'aide de l'opérateur de retour ;

Transmission de données à l'aide de l'opérateur de sélection.

Voyons maintenant à quoi ressemble ce processus de l’intérieur.

1. Créez une procédure stockée EXEC dans SQL

Vous pouvez créer une procédure dans MS SQL (Managment Studio). Une fois la procédure créée, elle sera répertoriée dans le nœud programmable de la base de données, dans lequel la procédure de création est exécutée par l'opérateur. Pour s'exécuter, les procédures stockées SQL utilisent un processus EXEC qui contient le nom de l'objet lui-même.

Lorsque vous créez une procédure, son nom apparaît en premier, suivi d'un ou plusieurs paramètres qui lui sont affectés. Les paramètres peuvent être facultatifs. Une fois le ou les paramètres, c'est-à-dire le corps de la procédure, écrits, certaines opérations nécessaires doivent être effectuées.

Le fait est qu’un corps peut contenir des variables locales, et ces variables sont également locales par rapport aux procédures. En d’autres termes, ils ne peuvent être visualisés que dans le corps d’une procédure Microsoft SQL Server. Dans ce cas, les procédures stockées sont considérées comme locales.

Ainsi, pour créer une procédure, nous avons besoin du nom de la procédure et d’au moins un paramètre comme corps de la procédure. Notez qu'une excellente option dans ce cas consiste à créer et à exécuter une procédure avec le nom du schéma dans le classificateur.

Le corps de la procédure peut être de toute nature, comme la création d'une table, l'insertion d'une ou plusieurs lignes d'une table, l'établissement du type et de la nature de la base de données, etc. Toutefois, l'organisme de procédure restreint l'exécution de certaines opérations en son sein. Certaines des limitations importantes sont répertoriées ci-dessous :

Le corps ne doit créer aucune autre procédure stockée ;

Le corps ne doit pas créer une fausse impression de l’objet ;

Le corps ne doit créer aucun déclencheur.

2. Définition d'une variable dans le corps de la procédure

Vous pouvez rendre les variables locales au corps de la procédure, et elles résideront alors exclusivement dans le corps de la procédure. Il est recommandé de créer des variables au début du corps de la procédure stockée. Mais vous pouvez également définir des variables n'importe où dans le corps d'un objet donné.

Parfois, vous remarquerez peut-être que plusieurs variables sont définies sur une seule ligne et que chaque paramètre de variable est séparé par une virgule. Notez également que la variable est préfixée par @. Dans le corps de la procédure, vous pouvez définir la variable là où vous le souhaitez. Par exemple, la variable @NAME1 peut être déclarée vers la fin du corps de la procédure. Pour attribuer une valeur à une variable déclarée, un ensemble de données personnelles est utilisé. Contrairement à la situation où plusieurs variables sont déclarées sur la même ligne, dans cette situation, un seul ensemble de données personnelles est utilisé.

Les utilisateurs posent souvent la question : « Comment attribuer plusieurs valeurs dans une seule instruction dans le corps d'une procédure ? Bien. C'est une question intéressante, mais c'est beaucoup plus facile à faire que vous ne le pensez. Réponse : En utilisant des paires telles que "Select Var = value". Vous pouvez utiliser ces paires en les séparant par une virgule.

Une variété d'exemples montrent des personnes créant une procédure stockée simple et l'exécutant. Cependant, une procédure peut accepter des paramètres tels que le processus qui l'appelle aura des valeurs proches d'elle (mais pas toujours). S'ils coïncident, les processus correspondants commencent à l'intérieur du corps. Par exemple, si vous créez une procédure qui acceptera une ville et une région de l'appelant et renverra des données sur le nombre d'auteurs appartenant à la ville et à la région correspondantes. La procédure interrogera les tables d'auteurs de la base de données, telles que Pubs, pour effectuer ce décompte des auteurs. Pour obtenir ces bases de données, par exemple, Google télécharge le script SQL depuis la page SQL2005.

Dans l'exemple précédent, la procédure prend deux paramètres, qui en anglais seront classiquement appelés @State et @City. Le type de données correspond au type défini dans l'application. Le corps de la procédure possède des variables internes @TotalAuthors, et cette variable permet d'afficher le nombre d'auteurs. Vient ensuite la section de sélection des requêtes, qui calcule tout. Enfin, la valeur calculée est imprimée dans la fenêtre de sortie à l'aide de l'instruction print.

Comment exécuter une procédure stockée en SQL

Il existe deux manières d'effectuer la procédure. La première méthode montre, en passant des paramètres, comment une liste séparée par des virgules est exécutée après le nom de la procédure. Disons que nous avons deux valeurs (comme dans l'exemple précédent). Ces valeurs sont collectées à l'aide des variables de paramètre de procédure @State et @City. Dans cette méthode de transmission des paramètres, l’ordre est important. Cette méthode est appelée passage d’arguments ordinaux. Dans la deuxième méthode, les paramètres sont déjà directement attribués et dans ce cas, l'ordre n'a pas d'importance. Cette deuxième méthode est connue sous le nom de transmission d’arguments nommés.

La procédure peut différer légèrement de la procédure habituelle. Tout est pareil que dans l'exemple précédent, mais seulement ici les paramètres sont décalés. Autrement dit, le paramètre @City est stocké en premier et @State est stocké à côté de la valeur par défaut. Le paramètre par défaut est généralement mis en évidence séparément. Les procédures stockées SQL sont transmises comme de simples paramètres. Dans ce cas, à condition que le paramètre « UT » remplace la valeur par défaut « CA ». Lors de la deuxième exécution, une seule valeur d'argument est transmise pour le paramètre @City et le paramètre @State prend la valeur par défaut « CA ». Les programmeurs expérimentés conseillent que toutes les variables soient situées par défaut vers la fin de la liste des paramètres. Sinon, l'exécution n'est pas possible et vous devez alors travailler avec des arguments nommés, ce qui est plus long et plus complexe.

4. Procédures stockées SQL Server : méthodes de retour

Il existe trois manières importantes d’envoyer des données dans une procédure stockée appelée. Ils sont listés ci-dessous :

Renvoie la valeur d'une procédure stockée ;

Sortie des paramètres de procédure stockée ;

Sélection de l'une des procédures stockées.

4.1 Renvoi de valeurs à partir de procédures stockées SQL

Dans cette technique, une procédure attribue une valeur à une variable locale et la renvoie. Une procédure peut également renvoyer directement une valeur constante. Dans l'exemple suivant, nous avons créé une procédure qui renvoie le nombre total d'auteurs. Si vous comparez cette procédure avec les précédentes, vous constaterez que la valeur d'impression est inversée.

Voyons maintenant comment exécuter une procédure et imprimer sa valeur de retour. L'exécution de la procédure nécessite la définition d'une variable et l'impression, qui est effectuée après tout ce processus. Notez qu'au lieu d'une instruction print, vous pouvez utiliser une instruction Select, telle que Select @RetValue ainsi que OutputValue.

4.2 Sortie des paramètres de procédure stockée SQL

La valeur de réponse peut être utilisée pour renvoyer une seule variable, ce que nous avons vu dans l'exemple précédent. L'utilisation du paramètre Output permet à une procédure d'envoyer une ou plusieurs valeurs variables à l'appelant. Le paramètre de sortie est désigné précisément par ce mot-clé « Output » lors de la création d'une procédure. Si un paramètre est donné comme paramètre de sortie, alors l'objet procédure doit lui attribuer une valeur. Les procédures stockées SQL, dont des exemples peuvent être vus ci-dessous, renvoient dans ce cas des informations récapitulatives.

Dans notre exemple, il y aura deux noms de sortie : @TotalAuthors et @TotalNoContract. Ils sont indiqués dans la liste des paramètres. Ces variables attribuent des valeurs dans le corps de la procédure. Lorsque nous utilisons des paramètres de sortie, l'appelant peut voir la valeur définie dans le corps de la procédure.

De plus, dans le scénario précédent, deux variables sont déclarées pour voir les valeurs définies par les procédures stockées MS SQL Server dans le paramètre de sortie. Ensuite, la procédure est effectuée en fournissant la valeur normale du paramètre « CA ». Les paramètres suivants sont des paramètres de sortie et les variables déclarées sont donc transmises dans l'ordre spécifié. Notez que lors du passage de variables, le mot-clé de sortie est également défini ici. Une fois la procédure terminée avec succès, les valeurs renvoyées par les paramètres de sortie sont affichées dans la fenêtre de message.

4.3 Sélection d'une des procédures stockées SQL

Cette technique est utilisée pour renvoyer un ensemble de valeurs sous forme de table de données (RecordSet) à la procédure stockée appelante. En cela Exemple SQL Une procédure stockée avec les paramètres @AuthID interroge la table Authors en filtrant les enregistrements renvoyés à l'aide de ce paramètre @AuthId. L'instruction Select décide de ce qui doit être renvoyé à l'appelant de la procédure stockée. Lorsque la procédure stockée est exécutée, l'AuthId est renvoyé. Cette procédure renvoie ici toujours un seul enregistrement, voire aucun. Mais une procédure stockée n'a aucune restriction quant au renvoi de plusieurs enregistrements. Il n'est pas rare de voir des exemples où les données sont renvoyées à l'aide de paramètres sélectionnés impliquant des variables calculées en fournissant plusieurs totaux.

Enfin

La procédure stockée est assez sérieuse module logiciel, renvoyer ou transmettre, ainsi que définir les variables nécessaires grâce à l'application client. Étant donné que la procédure stockée s'exécute elle-même sur le serveur, d'énormes quantités d'échanges de données entre le serveur et l'application client (pour certains calculs) peuvent être évitées. Cela vous permet de réduire la charge sur les serveurs SQL, ce qui profite bien entendu à leurs propriétaires. L'un des sous-types est celui des procédures stockées T SQL, mais leur étude est nécessaire pour ceux qui créent des bases de données impressionnantes. Il existe également un nombre important, voire énorme, de nuances qui peuvent être utiles lors de l'étude des procédures stockées, mais cela est davantage nécessaire pour ceux qui envisagent de s'impliquer dans la programmation, y compris professionnellement.

Procédure stockée procédure stockée) est un objet de programme de base de données nommé. SQL Server dispose de plusieurs types de procédures stockées.

Procédures stockées système procédure stockée système) sont fournis par les développeurs de SGBD et sont utilisés pour effectuer des actions avec le répertoire système ou obtenir informations système. Leurs noms commencent généralement par le préfixe « sp_ ». Vous exécutez tous les types de procédures stockées à l'aide de la commande EXECUTE, qui peut être abrégée en EXEC. Par exemple, la procédure stockée sp_helplogins, exécutée sans paramètres, produit deux rapports sur les noms de comptes (Anglais) logins) et les utilisateurs correspondants dans chaque base de données (Anglais) utilisateurs).

EXEC sp_helplogins;

Pour donner une idée des actions effectuées à l'aide des procédures stockées système, Tableau 10.6 montre quelques exemples. Au total, il existe plus d'un millier de procédures stockées système dans SQL Server.

Tableau 10.6

Exemples de procédures stockées du système SQL Server

L'utilisateur peut créer des procédures stockées dans les bases de données utilisateur et dans la base de données des objets temporaires. Dans ce dernier cas, la procédure stockée sera temporel. Comme pour les tables temporaires, le nom d'une procédure stockée temporaire doit commencer par le préfixe "#" s'il s'agit d'une procédure stockée temporaire locale, ou par "##" s'il s'agit d'une procédure globale. Une procédure temporaire locale ne peut être utilisée qu'au sein de la connexion dans laquelle elle a été créée, une procédure globale peut également être utilisée au sein d'autres connexions.

Les objets programmables SQL Server peuvent être créés à l'aide d'outils ou d'assemblys Transact-SQL (Anglais) assembly) dans l’environnement CRL (Common Language Runtime) de Microsoft.Net Framework. Ce tutoriel ne couvrira que la première méthode.

Pour créer des procédures stockées, utilisez l'instruction CREATE PROCEDURE (peut être raccourcie en PROC), dont le format est indiqué ci-dessous :

CREATE (PROC I PROCEDURE) nom_proc [ ; nombre ]

[(gparamètre data_type)

[« par défaut] |

[AVEC [ ,...n ] ]

[POUR LA RÉPLICATION]

AS ([ BEGIN ] sql_statement [;] [ ...n ] [ FIN ] )

Si une procédure stockée (ou trigger, fonction, vue) est créée avec l'option ENCRYPTION, son code est transformé de telle manière que le texte devient illisible. Dans le même temps, comme indiqué dans, l'algorithme utilisé a été transféré de versions précédentes SQL Server ne peut pas être considéré comme un algorithme de protection fiable - il existe des utilitaires qui vous permettent d'effectuer rapidement une conversion inverse.

L'option RECOMPILE précise que le système recompilera le texte à chaque appel de la procédure. Dans le cas normal, la procédure compilée lors de la première exécution est stockée dans le cache, ce qui permet d'augmenter les performances.

EXECUTE AS spécifie le contexte de sécurité dans lequel la procédure doit être exécutée. Ensuite, l'une des valeurs f CALLER | SOI | PROPRIÉTAIRE | "nom d'utilisateur"). CALLER est la valeur par défaut et signifie que le code sera exécuté dans le contexte de sécurité de l'utilisateur appelant ce module. En conséquence, l'utilisateur doit disposer d'autorisations non seulement sur l'objet programmable lui-même, mais également sur les autres objets de base de données affectés par celui-ci. EXECUTE AS SELF signifie utiliser le contexte de l'utilisateur créant ou modifiant l'objet programmable. OWNER précise que le code sera exécuté dans le contexte du propriétaire actuel de la procédure. Si aucun propriétaire n'est spécifié pour ce système, alors le propriétaire du schéma auquel il appartient est présumé. EXECUTE AS "user_name" vous permet de spécifier explicitement le nom d'utilisateur (entre guillemets simples).

Les paramètres peuvent être spécifiés pour une procédure. Ce sont des variables locales utilisées pour transmettre des valeurs à une procédure. Si un paramètre est déclaré avec le mot-clé OUTPUT (ou OUT en abrégé), il s'agit d'une valeur de sortie : la valeur qui lui est donnée dans la procédure après son achèvement peut être utilisée par le programme qui a appelé la procédure. Le mot clé READONLY signifie que la valeur du paramètre ne peut pas être modifiée dans la procédure stockée.

Les paramètres peuvent se voir attribuer des valeurs par défaut, qui seront utilisées si la valeur du paramètre n'est pas explicitement spécifiée lors de l'appel de la procédure. Regardons un exemple :

CREATE PROC surma (@a int, @b int=0,

©résultat int SORTIE) AS

FIXER @result=0a+0b

Nous avons créé une procédure avec trois paramètres, et le paramètre @b a une valeur par défaut de =0, et le paramètre @result est un paramètre de sortie : il renvoie la valeur au programme appelant. Les actions effectuées sont assez simples - le paramètre de sortie reçoit la valeur de la somme de deux paramètres d'entrée.

Lorsque vous travaillez dans SQL Server Management Studio, la procédure stockée créée se trouve dans la section des objets de base de données programmables. (Anglais) Programmabilité) dans la sous-section relative aux procédures stockées (Fig. 10.2).

Lors de l'appel d'une procédure, vous pouvez utiliser à la fois des variables et des constantes comme paramètres d'entrée. Regardons deux exemples. Dans le premier, les paramètres d'entrée de la procédure sont explicitement spécifiés comme constantes et le mot-clé OUTPUT est spécifié pour le paramètre de sortie dans l'appel. La deuxième option utilise la valeur d'une variable comme premier paramètre d'entrée et spécifie que la valeur par défaut doit être utilisée pour le deuxième paramètre à l'aide du mot-clé DEFAULT :

Riz. 10.2.

DÉCLARE @с int;

EXEC somme 10.5,@c OUTPUT ;

IMPRIMER 0c ; – 15 seront affichés

DÉCLARER Gi int = 5 ;

– lors de l'appel, utilisez la valeur par défaut

EXEC somme Gi,DEFAULT , 0c OUTPUT ;

IMPRIMER 0c ; – 5 sera affiché

Considérons maintenant un exemple avec l'analyse du code retour par lequel se termine la procédure. Supposons que nous devions calculer combien de livres de la table Bookl sont publiés au cours d’une période donnée. De plus, si l’année initiale est supérieure à l’année finale, la procédure renvoie « 1 » et ne compte pas, sinon, on compte le nombre de livres et renvoie 0 :

CREATE PROC dbo.rownum (0FirsYear int, GLastYear int, 0result int OUTPUT) AS

SI 0PremierAnnée>0AnnéeDernière RETOUR 1

SET @result= (SELECT COUNT(*) FROM dbo.Bookl

O ENTRE 0FirsYear ET 0LastYear);

Considérons une variante d'appel de cette procédure, dans laquelle le code retour est stocké dans la variable entière 0ret, après quoi sa valeur est analysée (dans ce cas ce sera 1). La fonction CAST utilisée dans l'instruction PRINT est utilisée pour convertir la valeur de la variable entière Gres en un type chaîne :

DÉCLARER 0ret int, Gres int

EXEC Gret = numéro de ligne 2004, 2002, Gres OUT ;

IF 0ret=l PRINT "L'année de début est supérieure à l'année de fin"

PRINT "Nombre de livres" + CAST(Gres as varchar(20))

Les procédures stockées peuvent non seulement lire les données d'une table, mais également modifier les données et même créer des tables et un certain nombre d'autres objets de base de données.

Toutefois, vous ne pouvez pas créer de schémas, de fonctions, de déclencheurs, de procédures et de vues à partir d'une procédure stockée.

L'exemple suivant illustre à la fois ces fonctionnalités et les problèmes liés à la portée des objets temporaires. La procédure stockée suivante vérifie l'existence de la table temporaire #TaL2 ; si cette table n'existe pas, il la crée. Après cela, les valeurs de deux colonnes sont saisies dans le tableau #TaL2 et le contenu du tableau est affiché à l'aide de l'instruction SELECT :

CRÉER PROC My_Procl (@id int, @name varchar(30))

SI OBJECT_ID("tempdb.dbo.#Tab21) EST NULL

INSERT INTO dbo.#Tab2 (id, nom)VALUES (0id,0name)

SELECT * FROM dbo. #Onglet2 –№1

Avant d'appeler la procédure stockée pour la première fois, nous allons créer la table temporaire #TaL2 qui y est utilisée. Faites attention à l'opérateur EXEC. Dans les exemples précédents, les paramètres ont été passés à la procédure « par position », mais dans ce cas un format différent pour passer les paramètres est utilisé – « par nom », le nom du paramètre et sa valeur sont explicitement indiqués :

CREATE TABLE dbo.#Tab2 (id int, nom varchar(30));

EXEC My_Procl 0name="lvan", 0id=2;

SELECT * FROM dbo.#Tab2; –№2

Dans l'exemple donné Instruction SELECT fonctionnera deux fois : la première fois – à l’intérieur de la procédure, la deuxième fois – à partir du fragment de code appelant (marqué du commentaire « N° 2 »).

Avant le deuxième appel à la procédure, nous supprimerons la table temporaire #TaL2. Ensuite, une table temporaire du même nom sera créée à partir de la procédure stockée :

DROP TABLE dbo.#Tab2;

EXEC My_Procl 0name="Ivan", 0id=2;

SELECT * FROM dbo.#Tab2; –№2

Dans ce cas, seule l'instruction SELECT située à l'intérieur de la procédure (avec le commentaire « Xa 1 ») affichera les données. L'exécution de SELECT "No. 2" entraînera une erreur, car la table temporaire créée dans la procédure stockée sera déjà supprimée de la base de données tempdb au moment du retour de la procédure.

Vous pouvez supprimer une procédure stockée à l'aide de l'instruction DROP PROCEDURE. Son format est présenté ci-dessous. Vous pouvez supprimer plusieurs procédures stockées avec une seule instruction, en les répertoriant séparées par des virgules :

DROP (PROCÉDURE PROC I) ( procédure ) [

Par exemple, supprimons la procédure summa créée précédemment :

Somme DROP PROC ;

Vous pouvez apporter des modifications à une procédure existante (et même la redéfinir) en utilisant Instruction ALTER PROCÉDURE

abréviation PROC). À l'exception du mot-clé ALTER, le format de l'instruction est essentiellement le même que celui de CREATE PROCEDURE. Par exemple, modifions la procédure dbo. rownum, en le configurant pour qu'il s'exécute dans le contexte de sécurité du propriétaire :

ALTER PROC dbo.rownum (SFirsYear int,

SLastYear int, Sresult int OUTPUT)

AVEC EXÉCUTER EN TANT QUE Propriétaire – option installable

IF 0FirsYear>0LastYear RETOUR 1 ELSE BEGIN

SET 0result= (SELECT COUNT(*) FROM dbo.Bookl

O ENTRE SFirsYear ET SLastYear);

Dans certains cas, il peut être nécessaire de générer dynamiquement une commande et de l'exécuter sur le serveur de base de données. Ce problème peut également être résolu à l'aide de l'opérateur EXEC. L'exemple ci-dessous récupère les enregistrements de la table Bookl si l'attribut Year est égal à la valeur spécifiée par la variable :

DÉCLARER 0у int = 2000 ;

EXEC ("SELECT * FROM dbo.Bookl WHERE = "+@y) ;

L’exécution d’instructions générées dynamiquement crée les conditions préalables à la mise en œuvre d’attaques informatiques telles que « l’injection SQL » (Anglais) injection SQL). L'essence de l'attaque est que l'attaquant injecte son propre code SQL dans une requête générée dynamiquement. Cela se produit généralement lorsque les paramètres substitués sont extraits des résultats de la saisie de l'utilisateur.

Modifions légèrement l'exemple précédent :

DÉCLARER 0 à varchar(100);

SET 0у="2ООО" ; – nous l'avons reçu de l'utilisateur

Si nous supposons que nous avons reçu de l'utilisateur la valeur de chaîne attribuée dans l'instruction SET (peu importe comment, par exemple, via une application Web), alors l'exemple illustre le comportement « normal » de notre code.

DÉCLARER 0 à varchar(100);

SET 0у="2000 ; DELETE FROM dbo.Book2" ; – injection

EXEC("SELECT * FROM dbo.Book2 WHERE ="+0y);

Il est recommandé, si possible, d'utiliser dans de tels cas la procédure stockée système sp_executcsql, qui permet de contrôler le type de paramètres, ce qui est l'un des freins à Injection SQL. Sans entrer dans le détail de son format, regardons un exemple similaire à celui présenté précédemment :

EXECUTE sp_executesql

N"SELECT * FROM dbo.Bookl OÙ =0y",

Cela spécifie explicitement le type de paramètre utilisé dans la requête, et SQL Server le contrôlera lors de l'exécution. La lettre « N » avant les guillemets indique qu'il s'agit d'une constante littérale Unicode, comme l'exige la procédure. Un paramètre peut se voir attribuer non seulement une valeur constante, mais également la valeur d'une autre variable.

procédure stockée n’est possible que si elle est réalisée dans le contexte de la base de données où se trouve la procédure.

Types de procédures stockées

SQL Server a plusieurs types procédures stockées.

• Système procédures stockées conçu pour effectuer diverses actions administratives. Presque toutes les activités d'administration du serveur sont effectuées avec leur aide. On peut dire que systémique procédures stockées sont une interface qui permet de travailler avec les tables système, ce qui revient finalement à modifier, ajouter, supprimer et récupérer des données des tables système des bases de données utilisateur et système. Système procédures stockées ont le préfixe sp_, sont stockés dans la base de données système et peuvent être appelés dans le contexte de n'importe quelle autre base de données.
• Coutume procédures stockées mettre en œuvre certaines actions. Procédures stockées– un objet de base de données à part entière. En conséquence, chacun procédure stockée se trouve dans une base de données spécifique où il est exécuté.
• Temporaire procédures stockées n'existent que pendant un certain temps, après quoi ils sont automatiquement détruits par le serveur. Ils sont divisés en locaux et mondiaux. Temporaire local procédures stockées ne peuvent être appelés qu'à partir de la connexion dans laquelle ils ont été créés. Lors de la création d'une telle procédure, vous devez lui donner un nom commençant par un seul caractère #. Comme tous les objets temporaires, procédures stockées de ce type sont automatiquement supprimés lorsque l'utilisateur se déconnecte ou que le serveur est redémarré ou arrêté. Temporaire global procédures stockées sont disponibles pour toute connexion depuis un serveur ayant la même procédure. Pour le définir, il suffit de lui donner un nom commençant par les caractères ## . Ces procédures sont supprimées au redémarrage ou à l'arrêt du serveur, ou à la fermeture de la connexion dans le contexte dans lequel elles ont été créées.

Créer, modifier et supprimer des procédures stockées

Création procédure stockée implique de résoudre les problèmes suivants :

• déterminer le type de création procédure stockée: temporaire ou personnalisé. De plus, vous pouvez créer votre propre système procédure stockée, en lui donnant un nom préfixé par sp_ et en le plaçant dans base du système données. Cette procédure sera disponible dans le contexte de n'importe quelle base de données de serveur local ;
• planifier les droits d’accès. En créant procédure stockée il faut tenir compte du fait qu'il aura les mêmes droits d'accès aux objets de la base de données que l'utilisateur qui l'a créé ;
• définition paramètres de procédure stockée. Semblable aux procédures incluses dans la plupart des langages de programmation, procédures stockées peut avoir des paramètres d'entrée et de sortie ;
• développement de code procédure stockée. Le code de procédure peut contenir une séquence de commandes SQL, y compris des appels à d'autres procédures stockées.

En créer un nouveau et modifier un existant procédure stockée effectué à l'aide de la commande suivante :

<определение_процедуры>::= (CREATE | ALTER ) nom_procédure [;numéro] [ (@parameter_name data_type ) [=default] ][,...n] AS sql_operator [...n]

Regardons les paramètres de cette commande.

En utilisant les préfixes sp_ ​​​​​​, # , ## , la procédure créée peut être définie comme système ou temporaire. Comme vous pouvez le voir dans la syntaxe de la commande, il n'est pas permis de spécifier le nom du propriétaire qui sera propriétaire de la procédure créée, ainsi que le nom de la base de données où elle doit se trouver. Ainsi, afin de placer le créé procédure stockée dans une base de données spécifique, vous devez émettre la commande CREATE PROCEDURE dans le contexte de cette base de données. En se détournant du corps procédure stockée des noms abrégés peuvent être utilisés pour les objets de la même base de données, c'est-à-dire sans spécifier le nom de la base de données. Lorsque vous devez accéder à des objets situés dans d'autres bases de données, la spécification du nom de la base de données est obligatoire.

Le numéro dans le nom est un numéro d'identification procédure stockée, qui l'identifie de manière unique dans un groupe de procédures. Pour faciliter la gestion, les procédures sont logiquement du même type procédures stockées peuvent être regroupés en leur attribuant le même nom mais des numéros d'identification différents.

Pour transférer les données d'entrée et de sortie dans le fichier créé procédure stockée On peut utiliser des paramètres dont les noms, comme les noms de variables locales, doivent commencer par le symbole @. Un procédure stockée Vous pouvez spécifier plusieurs paramètres séparés par des virgules. Le corps d'une procédure ne doit pas utiliser de variables locales dont les noms coïncident avec les noms des paramètres de cette procédure.

Pour déterminer le type de données que le correspondant paramètre de procédure stockée, n'importe quel type convient Données SQL, y compris ceux définis par l'utilisateur. Cependant, le type de données CURSOR ne peut être utilisé que comme paramètre de sortie procédure stockée, c'est à dire. en spécifiant le mot-clé OUTPUT.

La présence du mot clé OUTPUT signifie que le paramètre correspondant est destiné à renvoyer des données de procédure stockée. Cependant, cela ne signifie pas que le paramètre n'est pas adapté pour transmettre des valeurs à procédure stockée. La spécification du mot-clé OUTPUT demande au serveur de quitter procédure stockée attribuer la valeur actuelle du paramètre à la variable locale qui a été spécifiée lors de l'appel de la procédure comme valeur du paramètre. Notez que lors de la spécification du mot-clé OUTPUT, la valeur du paramètre correspondant lors de l'appel de la procédure ne peut être définie qu'à l'aide d'une variable locale. Toutes les expressions ou constantes autorisées pour les paramètres réguliers ne sont pas autorisées.

Le mot-clé VARYING est utilisé conjointement avec

Dans Microsoft SQL Server pour implémenter et automatiser vos propres algorithmes ( calculs), vous pouvez utiliser des procédures stockées, nous parlerons donc aujourd'hui de la façon dont elles sont créées, modifiées et supprimées.

Mais d'abord, un peu de théorie pour que vous compreniez ce que sont les procédures stockées et pourquoi elles sont nécessaires dans T-SQL.

Note! Pour les programmeurs débutants, je recommande les documents utiles suivants sur T-SQL :

• Pour une étude plus détaillée du langage T-SQL, je recommande également de lire le livre - The T-SQL Programmer's Path. Tutoriel sur le langage Transact-SQL ;
• Cours professionnels en ligne sur T-SQL

Que sont les procédures stockées dans T-SQL ?

Procédures stockées– ce sont des objets de base de données qui contiennent un algorithme sous la forme d’un ensemble d’instructions SQL. En d’autres termes, nous pouvons dire que les procédures stockées sont des programmes contenus dans une base de données. Les procédures stockées sont utilisées pour stocker du code réutilisable sur le serveur, par exemple, vous avez écrit un certain algorithme, un calcul séquentiel ou une instruction SQL en plusieurs étapes, et afin de ne pas exécuter à chaque fois toutes les instructions incluses dans cet algorithme, vous pouvez le formater en tant que procédure stockée. Parallèlement, lorsque vous créez une procédure SQL, le serveur compile le code, puis, à chaque fois que vous exécuterez cette procédure SQL, le serveur ne la recompilera pas.

Afin d'exécuter une procédure stockée dans SQL Server, vous devez écrire la commande EXECUTE avant son nom ; il est également possible d'abréger cette commande en EXEC. L'appel d'une procédure stockée dans une instruction SELECT, par exemple, en tant que fonction ne fonctionnera plus, c'est-à-dire les procédures sont lancées séparément.

Dans les procédures stockées, contrairement aux fonctions, il est déjà possible d'effectuer des opérations de modification de données telles que : UNSERT, UPDATE, DELETE. Vous pouvez également utiliser des instructions SQL de presque tous les types dans les procédures, par exemple CREATE TABLE pour créer des tables ou EXECUTE, c'est-à-dire appeler d'autres procédures. L'exception concerne plusieurs types d'instructions, telles que : la création ou la modification de fonctions, de vues, de déclencheurs, la création de schémas et plusieurs autres instructions similaires, par exemple, vous ne pouvez pas non plus changer le contexte de connexion à la base de données (USE) dans une procédure stockée.

Une procédure stockée peut avoir des paramètres d'entrée et des paramètres de sortie, elle peut renvoyer des données tabulaires ou ne rien renvoyer, exécuter uniquement les instructions qu'elle contient.

Les procédures stockées sont très utiles, elles nous aident à automatiser ou à simplifier de nombreuses opérations, par exemple, vous devez constamment générer divers rapports analytiques complexes à l'aide de tableaux croisés dynamiques, c'est-à-dire Opérateur PIVOT. Pour faciliter la formulation de requêtes avec cet opérateur ( comme vous le savez, la syntaxe de PIVOT est assez complexe), Vous pouvez écrire une procédure qui générera dynamiquement des rapports de synthèse pour vous, par exemple, le matériel « Dynamic PIVOT in T-SQL » fournit un exemple d'implémentation de cette fonctionnalité sous la forme d'une procédure stockée.

Exemples d'utilisation de procédures stockées dans Microsoft SQL Server

Données sources pour exemples

Tous les exemples ci-dessous seront exécutés dans Microsoft SQL Server 2016 Express. Afin de démontrer comment les procédures stockées fonctionnent avec des données réelles, nous avons besoin de ces données, créons-les. Par exemple, créons une table de test et ajoutons-y quelques enregistrements, disons que ce sera une table contenant une liste de produits avec leurs prix.

Instruction pour créer une table CREATE TABLE TestTable( INT IDENTITY(1,1) NOT NULL, INT NOT NULL, VARCHAR(100) NOT NULL, MONEY NULL) GO -- Instruction pour ajouter des données INSERT INTO TestTable(CategoryId, ProductName, Price) VALEURS (1, "Souris", 100), (1, "Clavier", 200), (2, "Téléphone", 400) GO --Sélectionner la requête SELECT * FROM TestTable

Nous avons les données, passons maintenant à la création de procédures stockées.

Création d'une procédure stockée dans T-SQL - l'instruction CREATE PROCEDURE

Les procédures stockées sont créées à l'aide d'une instruction CRÉER UNE PROCÉDURE, après cette instruction vous devez écrire le nom de votre procédure, puis, si nécessaire, définir les paramètres d'entrée et de sortie entre parenthèses. Après cela, vous écrivez le mot-clé AS et ouvrez le bloc d'instructions avec le mot-clé BEGIN, fermez ce bloc avec le mot END. À l'intérieur de ce bloc, vous écrivez toutes les instructions qui implémentent votre algorithme ou une sorte de calcul séquentiel, en d'autres termes, vous programmez en T-SQL.

Par exemple, écrivons une procédure stockée qui ajoutera nouvelle entrée, c'est à dire. nouveau produità notre table de test. Pour ce faire, nous définirons trois paramètres d'entrée : @CategoryId – identifiant de la catégorie de produit, @ProductName – nom du produit et @Price – prix du produit, ce paramètre Nous en aurons un facultatif, c'est-à-dire il ne sera pas nécessaire de le transmettre à la procédure ( par exemple, nous ne connaissons pas encore le prix), à cet effet nous fixerons une valeur par défaut dans sa définition. Ces paramètres sont dans le corps de la procédure, c'est-à-dire dans le bloc BEGIN...END peut être utilisé, tout comme les variables normales ( Comme vous le savez, les variables sont désignées par le signe @). Si vous devez spécifier des paramètres de sortie, après le nom du paramètre, indiquez le mot-clé SORTIE ( ou OUT pour faire court).

Dans le bloc BEGIN...END, nous écrirons une instruction pour ajouter des données, ainsi qu'une instruction SELECT à la fin de la procédure, afin que la procédure stockée nous renvoie des données tabulaires sur les produits de la catégorie spécifiée, en tenant compte compte du nouveau produit qui vient d'être ajouté. Également dans cette procédure stockée, j'ai ajouté le traitement du paramètre entrant, à savoir la suppression espaces supplémentaires au début et à la fin d'une ligne de texte afin d'éliminer les situations où plusieurs espaces ont été accidentellement saisis.

Voici le code de cette procédure ( Je l'ai aussi commenté).

Créer une procédure CREATE PROCEDURE TestProcedure (--Paramètres d'entrée @CategoryId INT, @ProductName VARCHAR(100), @Price MONEY = 0) COMME BEGIN --Instructions qui implémentent votre algorithme --Traitement des paramètres entrants --Suppression des espaces supplémentaires au début et à la fin de la ligne de texte SET @ProductName = LTRIM(RTRIM(@ProductName)); --Ajouter un nouvel enregistrement INSERT INTO TestTable(CategoryId, ProductName, Price) VALUES (@CategoryId, @ProductName, @Price) --Renvoyer les données SELECT * FROM TestTable WHERE CategoryId = @CategoryId END GO

Exécution d'une procédure stockée dans T-SQL - commande EXECUTE

Vous pouvez exécuter une procédure stockée, comme je l'ai déjà noté, à l'aide de la commande EXECUTE ou EXEC. Les paramètres entrants sont transmis aux procédures en les listant simplement et en spécifiant les valeurs appropriées après le nom de la procédure ( pour les paramètres de sortie, vous devez également spécifier la commande OUTPUT). Cependant, les noms des paramètres ne peuvent pas être spécifiés, mais dans ce cas, il est nécessaire de suivre la séquence de spécification des valeurs, c'est-à-dire spécifier les valeurs dans l'ordre dans lequel les paramètres d'entrée sont définis ( cela s'applique également aux paramètres de sortie).

Les paramètres qui ont des valeurs par défaut n'ont pas besoin d'être spécifiés, ce sont les paramètres dits facultatifs.

Voici quelques manières différentes mais équivalentes d’exécuter des procédures stockées, en particulier notre procédure de test.

1. Appelez la procédure sans préciser le prix EXECUTE TestProcedure @CategoryId = 1, @ProductName = "Test product 1" --2. Appelez la procédure indiquant le prix EXEC TestProcedure @CategoryId = 1, @ProductName = "Test product 2", @Price = 300 --3. Appeler la procédure sans préciser le nom des paramètres EXEC TestProcedure 1, "Test product 3", 400

Modification d'une procédure stockée en T-SQL - Instruction ALTER PROCEDURE

Vous pouvez apporter des modifications à l'algorithme de la procédure en utilisant les instructions MODIFIER LA PROCÉDURE. En d'autres termes, pour modifier une procédure déjà existante, il vous suffit d'écrire ALTER PROCEDURE au lieu de CREATE PROCEDURE, et de modifier tout le reste si nécessaire.

Disons que nous devons apporter des modifications à notre procédure de test, par exemple le paramètre @Price, c'est-à-dire prix, nous le rendrons obligatoire, pour cela nous supprimerons la valeur par défaut, et imaginons également que nous n'avons plus besoin d'obtenir l'ensemble de données résultant, pour cela nous supprimerons simplement l'instruction SELECT de la procédure stockée.

Nous modifions la procédure ALTER PROCEDURE TestProcedure (--Paramètres entrants @CategoryId INT, @ProductName VARCHAR(100), @Price MONEY) AS BEGIN --Instructions qui implémentent votre algorithme --Traitement des paramètres entrants --Suppression des espaces supplémentaires au début et fin des lignes de texte SET @ProductName = LTRIM(RTRIM(@ProductName)); --Ajouter un nouvel enregistrement INSERT INTO TestTable(CategoryId, ProductName, Price) VALUES (@CategoryId, @ProductName, @Price) END GO

Suppression d'une procédure stockée dans T-SQL - Instruction DROP PROCEDURE

Si nécessaire, vous pouvez supprimer la procédure stockée ; cela se fait en utilisant les instructions PROCÉDURE DE CHUTE.

Par exemple, supprimons la procédure de test que nous avons créée.

PROCÉDURE DE CHUTE

Lors de la suppression de procédures stockées, il convient de rappeler que si la procédure est référencée par d'autres procédures ou instructions SQL, après sa suppression, elles échoueront avec une erreur, car la procédure à laquelle elles font référence n'existe plus.

C'est tout ce que j'ai, j'espère que le matériel vous a été intéressant et utile, au revoir !