Exemples de procédures stockées MS SQL. Procédures stockées en T-SQL - création, modification, suppression. Exemples d'utilisation de procédures stockées dans Microsoft SQL Server

Procédures stockées

Le sujet de ce chapitre est l'un des outils les plus puissants proposés aux développeurs d'applications de base de données InterBase pour implémenter la logique métier (anglais, stoied proceduies) permettant d'implémenter une partie importante de la logique applicative au niveau de la base de données et ainsi d'augmenter. les performances de l'ensemble de l'application, centraliser le traitement des données et réduire la quantité de code nécessaire pour accomplir les tâches. Presque tout est suffisant. application complexe Les bases de données ne sont pas complètes sans l'utilisation de procédures stockées.
En plus de ces avantages bien connus liés à l'utilisation de procédures stockées, communs à la plupart des SGBD relationnels, les procédures stockées InterBase peuvent agir comme des ensembles de données presque complets, permettant aux résultats qu'elles renvoient d'être utilisés dans des requêtes SQL ordinaires.
Souvent, les développeurs novices imaginent les procédures stockées simplement comme un ensemble de requêtes SQL spécifiques qui font quelque chose à l'intérieur de la base de données, et il existe une opinion selon laquelle travailler avec des procédures stockées est beaucoup plus difficile que d'implémenter la même fonctionnalité dans une application client, de manière hautement sophistiquée. niveau de langue
Alors, que sont les procédures stockées dans InterBase ?
Une procédure stockée (SP) fait partie des métadonnées de la base de données, qui est un sous-programme compilé dans la représentation interne InterBase, écrite en langue spéciale, dont le compilateur est intégré au cœur du serveur InteiBase
Une procédure stockée peut être appelée depuis applications clientes, à partir de déclencheurs et d'autres procédures stockées. La procédure stockée s'exécute à l'intérieur du processus serveur et peut manipuler les données de la base de données, ainsi que renvoyer les résultats de son exécution au client qui l'a appelée (c'est-à-dire le déclencheur, HP, l'application).
base fonctionnalités puissantes, intégré à HP, est un langage de programmation procédural qui comprend à la fois des instructions modifiées du SQL standard, telles que INSERT, UPDATE et SELECT, ainsi que des outils pour organiser les branches et les boucles (IF, WHILE), ainsi que des moyens de gestion des erreurs et exceptions Le langage des procédures stockées vous permet d'implémenter des algorithmes complexes pour travailler avec des données, et en raison de l'accent mis sur le travail avec des données relationnelles, HP est beaucoup plus compact que des procédures similaires sur langues traditionnelles.
Il convient de noter que le même langage de programmation est utilisé pour les déclencheurs, à l'exception d'un certain nombre de fonctionnalités et de limitations. Les différences entre le sous-ensemble du langage utilisé dans les triggers et le langage HP sont abordées en détail dans le chapitre « Triggers » (partie 1).

Exemple de procédure stockée simple

Il est temps de créer votre première procédure stockée et de l'utiliser comme exemple pour apprendre le processus de création de procédures stockées. Mais d'abord, nous devrions dire quelques mots sur la façon de travailler avec les procédures stockées. Le fait est que HP doit sa réputation d'outil obscur et peu pratique à des outils standard extrêmement médiocres pour développer et déboguer des procédures stockées. La documentation InterBase recommande de créer des procédures à l'aide de fichiers de script SQL contenant du texte HP, qui sont fournis en entrée à l'interpréteur isql, et ainsi de créer et de modifier HP If dans ce script SQL au stade de la compilation du texte de la procédure dans BLR (à propos de BLR , voir Chapitre "Structure de la base de données InterBase" (Partie 4)) si une erreur se produit, isql affichera un message indiquant sur quelle ligne du fichier de script SQL cette erreur s'est produite. Corrigez l’erreur et recommencez. À propos du débogage dans compréhension moderne Ce mot, c'est-à-dire le traçage de l'exécution, avec la possibilité d'examiner les valeurs intermédiaires des variables, n'est pas du tout discuté. Evidemment, cette approche ne contribue pas à accroître l'attractivité des procédures stockées aux yeux du développeur
Cependant, en plus de l'approche minimaliste standard du développement HP<_\ществ\ют также инструменты сторонних разработчиков, которые делают работу с хранимыми процедурами весьма удобной Большинство универсальных продуктов для работы с InterBase, перечисленных в приложении "Инструменты администратора и разработчика InterBase", предоставляют удобный инструментарий для работы с ХП. Мы рекомендуем обязательно воспользоваться одним из этих инструментов для работы с хранимыми процедурами и изложение материала будем вести в предположении, что у вас имеется удобный GUI-инструмент, избавляющий от написания традиционных SQL-скриптов
La syntaxe des procédures stockées est décrite comme suit :

Nom de CRÉER UNE PROCÉDURE
[ (type de données param [, type de données param ...]) ]
)]
COMME
;
< procedure_body> = []
< block>
< vanable_declaration_list> =
DECLARE VARIABLE type de données var ;

=
COMMENCER
< compound_statement>
[< compound_statement> ...]
FIN
< compound_statement> = (déclaration;)

Cela semble assez volumineux et peut même être encombrant, mais en fait tout est très simple Afin de maîtriser progressivement la syntaxe, regardons des exemples progressivement plus complexes.
Voici donc un exemple de procédure stockée très simple qui prend deux nombres en entrée, les additionne et renvoie le résultat :

CREATE PROCEDURE SP_Add(first_arg DOUBLE PRECISION,
second_arg DOUBLE PRÉCISION)
RETOURS (Résultat DOUBLE PRÉCISION)
COMME
COMMENCER
Résultat=first_arg+second_arg ;
SUSPENDRE;
FIN

Comme vous pouvez le constater, tout est simple : après la commande CREATE PROCEDURE, le nom de la procédure nouvellement créée est indiqué (qui doit être unique au sein de la base de données) - dans ce cas SP_Add, puis les paramètres d'entrée HP - first_arg et second_arg - sont répertoriés entre parenthèses, séparés par des virgules, indiquant leurs types.
La liste des paramètres d'entrée est une partie facultative de l'instruction CREATE PROCEDURE - il existe des cas où une procédure reçoit toutes les données pour son travail via des requêtes vers des tables à l'intérieur du corps de la procédure.

Les procédures stockées utilisent n'importe quel type de données scalaire InteiBase Il ne permet pas l'utilisation de tableaux et de types définis par l'utilisateur - domaines

Vient ensuite le mot-clé RETURNS, après quoi les paramètres renvoyés sont répertoriés entre parenthèses, indiquant leurs types - dans ce cas, un seul - Résultat.
Si la procédure ne doit pas renvoyer de paramètres, alors le mot RETURNS et la liste des paramètres renvoyés sont manquants.
Après RETURNSQ, le mot-clé AS est spécifié. Avant que le mot-clé AS ne disparaisse titre, et après ça - technique procédures.
Le corps d'une procédure stockée est une liste de descriptions de ses variables internes (locales) (si elles existent, nous les examinerons plus en détail ci-dessous), séparées par un point-virgule (;) et un bloc d'instructions entouré d'un opérateur crochets DÉBUT FIN. Dans ce cas, le corps du HP est très simple : nous demandons d'ajouter deux arguments d'entrée et d'attribuer leur résultat à la sortie, puis d'appeler la commande SUSPEND. Un peu plus tard, nous expliquerons l'essence de l'action de cette commande, mais pour l'instant nous noterons seulement qu'elle est nécessaire pour transférer les paramètres de retour là où la procédure stockée a été appelée.

Délimiteurs dans les procédures stockées

Notez qu'une instruction dans une procédure se termine par un point-virgule (;). Comme vous le savez, le point-virgule est un séparateur de commande standard dans SQL - c'est un signal à l'interpréteur SQL que le texte de la commande a été saisi dans son intégralité et qu'il doit commencer à le traiter. N'arriverait-il pas que si l'interpréteur SQL trouve un point-virgule au milieu du HP, il considérera que la commande a été saisie dans son intégralité et tentera d'exécuter une partie de la procédure stockée ? Cette hypothèse n’est pas sans fondement. En effet, si vous créez un fichier dans lequel écrire l'exemple ci-dessus, ajoutez une commande de connexion depuis la base de données et essayez d'exécuter ce script SQL à l'aide de l'interpréteur isql, une erreur sera renvoyée en raison de la fin inattendue, de l'avis de l'interprète. de la commande de création de procédure stockée. Si vous créez des procédures stockées à l'aide de fichiers de script SQL, sans utiliser d'outils de développement InterBase spécialisés, alors avant chaque commande de création HP (il en va de même pour les déclencheurs), vous devez remplacer le séparateur de commande de script par un autre caractère autre qu'un point-virgule, et après le texte HP pour le restaurer. La commande isql qui modifie le séparateur de clause SQL ressemble à ceci :

DÉFINIR LA DURÉE

Pour un cas typique de création d’une procédure stockée, cela ressemble à ceci :

DÉFINIR LE TERME^ ;
CRÉER UNE PROCÉDURE some_procedure
... . .
FIN
^
DÉFINIR LA DURÉE ;^

Appel d'une procédure stockée

Mais revenons à notre procédure stockée. Maintenant qu'il a été créé, vous devez l'appeler d'une manière ou d'une autre, lui transmettre des paramètres et obtenir les résultats renvoyés. C'est très simple à faire - il suffit d'écrire une requête SQL comme celle-ci :

SÉLECTIONNER *
DE Sp_add(181.35, 23.09)

Cette requête nous renverra une ligne contenant un seul champ Résultat, qui contiendra la somme des nombres 181,35 et 23,09, soit 204,44.
Ainsi, notre procédure peut être utilisée de manière ordinaire Requêtes SQL s'exécutant comme dans programmes clients, ainsi que dans d'autres HP ou déclencheurs. Cette utilisation de notre procédure est rendue possible grâce à l'utilisation de la commande SUSPEND en fin de procédure stockée.
Le fait est que dans InterBase (et dans tous ses clones), il existe deux types de procédures stockées : les procédures sélectionnables et les procédures exécutables. La différence dans le fonctionnement de ces deux types de HP est que les procédures d'échantillonnage renvoient généralement de nombreux ensembles de paramètres de sortie, regroupés ligne par ligne, qui ressemblent à un ensemble de données, tandis que les procédures exécutables peuvent soit ne renvoyer aucun paramètre, soit renvoyer uniquement un ensemble de paramètres de sortie, répertoriés dans Returns, où une ligne de paramètres. Les procédures Select sont appelées dans les requêtes SELECT et les procédures exécutables sont appelées à l'aide de la commande EXECUTE PROCEDURE.
Les deux types de procédures stockées ont la même syntaxe de création et ne sont formellement pas différents, donc toute procédure exécutable peut être appelée dans une requête SELECT et toute procédure de sélection peut être appelée à l'aide de EXECUTE PROCEDURE. La question est de savoir comment HP se comportera lorsque différents types appel. En d’autres termes, la différence réside dans la conception de la procédure pour un type d’appel particulier. Autrement dit, la procédure de sélection est spécifiquement créée pour être appelée à partir d'une requête SELECT et la procédure exécutable est spécifiquement créée pour être appelée à l'aide de EXECUTE PROCEDURE. Voyons quelles sont les différences dans la conception de ces deux types de HP.
Afin de comprendre comment fonctionne la procédure d’échantillonnage, vous devrez approfondir un peu la théorie. Imaginons une requête SQL classique telle que SELECT ID, NAME FROM Table_example. À la suite de son exécution, nous obtenons un tableau composé de deux colonnes (ID et NAME) et d'un certain nombre de lignes (égal au nombre de lignes de la table Table_example). La table renvoyée par cette requête est également appelée un ensemble Données SQL Pensons à la façon dont l'ensemble de données est formé lors de l'exécution de cette requête. Le serveur, après avoir reçu la requête, détermine à quelles tables elle se réfère, puis découvre quel sous-ensemble d'enregistrements de ces tables doit être inclus dans le résultat de la requête. Ensuite, le serveur lit chaque enregistrement qui satisfait aux résultats de la requête et en fait une sélection. champs obligatoires(dans notre cas, il s'agit de l'ID et du NOM) et les envoie au client. Ensuite, le processus est répété à nouveau - et ainsi de suite pour chaque enregistrement sélectionné.
Toute cette digression est nécessaire pour que le cher lecteur comprenne que tous les ensembles de données SQL sont générés ligne par ligne, y compris dans les procédures stockées ! Et la principale différence entre les procédures de récupération et les procédures exécutables est que les premières sont conçues pour renvoyer plusieurs lignes, tandis que les secondes sont conçues pour n'en renvoyer qu'une seule. C'est pourquoi ils sont utilisés différemment : la procédure select est appelée à l'aide de la commande SELECT, qui "exige" que la procédure abandonne tous les enregistrements qu'elle peut renvoyer. La procédure exécutable est appelée à l'aide de EXECUTE PROCEDURE, qui « supprime » une seule ligne du HP et ignore le reste (même s'ils existent !).
Examinons un exemple de procédure d'échantillonnage pour que ce soit plus clair. Pour > pardon, créons une procédure stockée qui fonctionne exactement comme une requête SELECT ID, NAME FROM Table_Example, c'est-à-dire qu'elle sélectionne simplement les champs ID et NAME dans la table entière. Voici cet exemple :

CRÉER UNE PROCÉDURE Simple_Select_SP
RETOURS (
procID ENTIER,
procNAME VARCHAR(80))
COMME
COMMENCER
POUR
SELECT ID, NOM FROM table_example
INTO:procID, :procNAME
FAIRE
COMMENCER
SUSPENDRE;
FIN
FIN

Regardons les étapes de cette procédure, appelée Simple_Select_SP. Comme vous pouvez le voir, il n'a aucun paramètre d'entrée et possède deux paramètres de sortie : ID et NAME. Le plus intéressant réside bien sûr dans le corps de la procédure. La construction FOR SELECT est utilisée ici :

POUR
SELECT ID, NOM FROM table_example
INTO:procID, :procNAME
FAIRE
COMMENCER

/*faire quelque chose avec les variables procID et procName*/

FIN

Ce morceau de code signifie ce qui suit : pour chaque ligne sélectionnée dans la table Table_example, placez les valeurs sélectionnées dans les variables procID et procName, puis faites quelque chose avec ces variables.
Vous pourriez faire une grimace et demander : « Variables ? Quelles autres variables ? 9 » C'est un peu la surprise de ce chapitre que nous puissions utiliser des variables dans des procédures stockées. Dans le langage HP, vous pouvez déclarer vos propres variables locales dans une procédure et utiliser les paramètres d'entrée et de sortie comme variables.
Afin de déclarer une variable locale dans une procédure stockée, vous devez placer sa description après le mot clé AS et avant le premier mot BEGIN. La description de la variable locale ressemble à ceci :

DÉCLARER UNE VARIABLE ;

Par exemple, pour déclarer une variable locale entière Mylnt, vous insérerez la déclaration suivante entre AS et BEGIN

DÉCLARER LA VARIABLE Mylnt INTEGER ;

Les variables de notre exemple commencent par deux points. Ceci est fait car ils sont accessibles dans SQL. POUR les commandes SELECT, donc, pour faire la distinction entre les champs des tables utilisées dans SELECT et les variables, ces dernières doivent être précédées de deux points. Après tout, les variables peuvent avoir exactement le même nom que les champs des tables !
Mais les deux points précédant un nom de variable ne doivent être utilisés que dans les requêtes SQL. En dehors des textes, une variable est référencée sans deux-points, par exemple :

procName="Un nom";

Mais revenons au corps de notre procédure. La clause FOR SELECT renvoie les données non pas sous forme de tableau – un ensemble de données, mais une ligne à la fois. Chaque champ renvoyé doit être placé dans sa propre variable : ID => procID, NAME => procName. Dans la partie DO, ces variables sont envoyées au client qui a appelé la procédure à l'aide de la commande SUSPEND
Ainsi, la commande FOR SELECT...DO parcourt les enregistrements sélectionnés dans la partie SELECT de la commande. Dans le corps de la boucle formée par la partie DO, l'enregistrement généré suivant est transféré au client à l'aide de la commande SUSPEND.
Ainsi, la procédure de sélection est conçue pour renvoyer une ou plusieurs lignes, pour lesquelles une boucle est organisée à l'intérieur du corps HP qui remplit les paramètres variables résultants. Et à la fin du corps de cette boucle, il y a toujours une commande SUSPEND, qui renverra la ligne de données suivante au client.

Boucles et instructions de branchement

En plus de la commande FOR SELECT...DO, qui organise une boucle à travers les enregistrements d'une sélection, il existe un autre type de boucle - WHILE...DO, qui vous permet d'organiser une boucle basée sur la vérification d'éventuelles conditions. Voici un exemple de HP utilisant la boucle WHILE..DO. Cette procédure renvoie les carrés des entiers de 0 à 99 :

CRÉER UN QUAD DE PROCÉDURE
RETOURS (QUADRAT ENTIER)
COMME
DÉCLARER LA VARIABLE I ENTIER ;
COMMENCER
je = 1 ;
PENDANT QUE(je<100) DO
COMMENCER
QUADRAT = je*je;
je=je+1 ;
SUSPENDRE;
FIN
FIN

À la suite de l'exécution de la requête SELECT FROM QUAD, nous recevrons une table contenant une colonne QUADRAT, qui contiendra les carrés des entiers de 1 à 99.
En plus d'itérer les résultats d'un échantillon SQL et d'une boucle classique, le langage de procédure stockée utilise l'opérateur IF...THEN..ELSE, qui permet d'organiser le branchement en fonction de l'exécution d'éventuelles conditions. Sa syntaxe est similaire. à la plupart des opérateurs de branchement dans les langages de programmation de haut niveau, comme Pascal et C.
Examinons un exemple plus complexe de procédure stockée qui effectue les opérations suivantes.

1. Calcule le prix moyen dans la table Table_example (voir chapitre "Tables Clés Primaires et Générateurs")
2. Ensuite, pour chaque entrée du tableau, il effectue la vérification suivante : si le prix existant (PRICE) est supérieur au prix moyen, alors il fixe un prix égal au prix moyen, majoré d'un pourcentage fixe spécifié.
3. Si le prix existant est inférieur ou égal au prix moyen, fixe alors un prix égal au prix précédent, majoré de la moitié de la différence entre le prix précédent et le prix moyen. prix moyen.
4. Renvoie toutes les lignes modifiées du tableau.

Tout d'abord, définissons le nom du HP, ainsi que les paramètres d'entrée et de sortie. Tout cela est écrit dans l'en-tête de la procédure stockée.

CRÉER UNE PROCÉDURE Augmenter les prix (
Pourcentage2laugmentation DOUBLE PRÉCISION)
RETOURS (ID INTEGER, NOM VARCHAR(SO), new_price DOUBLE
LA PRÉCISION COMME

La procédure s'appellera IncreasePrices, elle a un paramètre d'entrée Peiceni21nciease de type DOUBLE PRECISION et 3 paramètres de sortie - ID, NAME et new_pnce. Notez que les deux premiers paramètres de sortie ont les mêmes noms que les champs de la table Table_example avec laquelle nous allons travailler. Ceci est autorisé par les règles du langage de procédure stockée.
Nous devons maintenant déclarer une variable locale qui sera utilisée pour stocker la valeur moyenne. La déclaration ressemblera à ceci :

DÉCLARE VARIABLE avg_price DOUBLE PRÉCISION ;

Passons maintenant au corps de la procédure stockée. Ouvrez le corps du HP. mot-clé COMMENCER.
Nous devons d’abord effectuer la première étape de notre algorithme : calculer le prix moyen. Pour ce faire, nous utiliserons le type de requête suivant :

SELECT AVG(Prix_l)
FROM Table_Exemple
DANS : prix_avg, -

Cette demande utilise fonction d'agrégation AVG, qui renvoie la valeur moyenne du champ PRICE_1 parmi les lignes de requête sélectionnées - dans notre cas, la valeur moyenne de PRICE_1 sur l'ensemble de la table Table_example. La valeur renvoyée par la requête est placée dans la variable avg_price. Notez que la variable avg_pnce est précédée de deux points pour la distinguer des champs utilisés dans la requête.
Fonctionnalité de cette demande est qu'il renvoie toujours exactement un seul enregistrement. De telles requêtes sont appelées requêtes singleton. Et seules ces sélections peuvent être utilisées dans les procédures stockées. Si une requête renvoie plus d'une ligne, elle doit alors être formatée comme une construction FOR SELECT...DO, qui organise une boucle pour traiter chaque ligne renvoyée.
Nous avons donc obtenu le prix moyen. Vous devez maintenant parcourir l'intégralité du tableau, comparer la valeur du prix de chaque entrée avec le prix moyen et prendre les mesures appropriées.
Dès le début, nous organisons la recherche de chaque enregistrement de la table Table_example

POUR
SELECT ID, NOM, PRICE_1
FROM Table_Exemple
INTO :ID, :NAME, :new_price
FAIRE
COMMENCER
/*_ici nous décrivons chaque entrée*/
FIN

Lorsque cette construction est exécutée, les données seront extraites de la table Table_example ligne par ligne et les valeurs de champ de chaque ligne seront affectées aux variables ID, NAME et new_pnce. N'oubliez pas, bien sûr, que ces variables sont déclarées comme paramètres de sortie, mais il n'y a pas lieu de s'inquiéter du fait que les données sélectionnées seront renvoyées comme résultats : le fait que les paramètres de sortie soient affectés à quelque chose ne signifie pas que le client appelant le HP recevra immédiatement ces valeurs ! Les paramètres ne sont transmis que lorsque la commande SUSPEND est exécutée, et avant cela, nous pouvons utiliser les paramètres de sortie comme variables ordinaires - dans notre exemple, nous faisons exactement cela avec le paramètre new_price.
Ainsi, à l'intérieur du corps de la boucle BEGIN... END, nous pouvons traiter les valeurs de chaque ligne. Comme vous vous en souvenez, nous devons déterminer comment le prix existant se compare à la moyenne et prendre les mesures appropriées. Nous implémentons cette procédure de comparaison en utilisant l'instruction IF :

SI (new_price > avg_price) ALORS /*si le prix existant est supérieur au prix moyen*/
COMMENCER
/*puis nous fixerons un nouveau prix égal au prix moyen, plus un pourcentage fixe */
new_price = (avg_price + avg_price*(Percent2Increase/100));
MISE À JOUR Table_exemple
FIXER PRICE_1 = :nouveau_prix
OÙ ID = :ID;
FIN
AUTRE
COMMENCER
/* Si le prix existant est inférieur ou égal au prix moyen, alors fixez un prix égal au prix précédent, plus la moitié de la différence entre le prix précédent et le prix moyen */
new_price = (new_pnce + ((avg_pnce new_price)/2)) ;
MISE À JOUR Table_exemple
SET PRICE_1 = :nouveau_prix
OÙ ID = .ID ;
FIN

Comme vous pouvez le voir, le résultat est une construction IF assez volumineuse, qui serait difficile à comprendre sans les commentaires inclus dans les symboles /**/.
Afin de modifier le prix en fonction de la différence calculée, nous utiliserons l'instruction UPDATE, qui nous permet de modifier enregistrements existants- un ou plusieurs. Afin d'indiquer sans ambiguïté dans quel enregistrement le prix doit être modifié, nous utilisons le champ de clé primaire dans la condition WHERE, en le comparant avec la valeur de la variable qui stocke la valeur ID pour l'enregistrement actuel : ID=:ID. Notez que la variable ID est précédée de deux points.
Après avoir exécuté la construction IF...THEN...ELSE, les variables ID, NAME et new_price contiennent des données que nous devons retourner au client qui a appelé la procédure. Pour ce faire, après IF, vous devez insérer la commande SUSPEND, qui enverra les données d'où le HP a été appelé. Pendant le transfert, la procédure sera suspendue, et lorsqu'un nouvel enregistrement sera demandé au HP, il le sera. sera continué à nouveau - et cela continuera jusqu'à ce que FOR SELECT...DO ne parcoure pas tous les enregistrements de sa requête.
Il convient de noter qu'en plus de la commande SUSPEND, qui suspend uniquement la procédure stockée, il existe une commande EXIT qui termine la procédure stockée après avoir transmis la chaîne. Cependant, la commande EXIT est assez rarement utilisée, car elle sert principalement à interrompre la boucle lorsqu'une condition est atteinte.
Cependant, dans le cas où la procédure a été appelée avec une instruction SELECT et complétée avec EXIT, la dernière ligne récupérée ne sera pas renvoyée. Autrement dit, si vous devez interrompre la procédure tout en récupérant cette chaîne, vous devez utiliser la séquence

SUSPENDRE;
SORTIE;

L'objectif principal de EXIT est de recevoir des ensembles de données singleton, des paramètres renvoyés en appelant EXECUTE PROCEDURE. Dans ce cas, les valeurs des paramètres de sortie sont définies, mais l'ensemble de données SQL n'est pas généré à partir d'elles et l'exécution de la procédure se termine.
Écrivons le texte de notre procédure stockée dans son intégralité afin de pouvoir saisir sa logique en un coup d'œil :

CRÉER UNE PROCÉDURE Augmenter les prix (
Pourcentage2Augmentation DOUBLE PRÉCISION)
RETOURS (ID INTEGER, NOM VARCHAR(80),
new_price DOUBLE PRÉCISION) AS
DÉCLARE VARIABLE avg_price DOUBLE PRÉCISION ;
COMMENCER
SELECT AVG(Prix_l)
FROM Table_Exemple
INTO: prix_moy;
POUR
SELECT ID, NOM, PRICE_1
FROM Table_Exemple
INTO :ID, :NAME, :new_price
FAIRE
COMMENCER
/*traiter chaque enregistrement ici*/
SI (new_pnce > avg_price) ALORS /*si le prix existant est supérieur au prix moyen*/
COMMENCER
/*fixe un nouveau prix égal au prix moyen plus un pourcentage fixe */
new_price = (avg_price + avg_price*(Percent2lncrease/100));
MISE À JOUR Table_exemple
FIXER PRICE_1 = :nouveau_prix
OÙ ID = :ID;
FIN
AUTRE
COMMENCER
/* Si le prix existant est inférieur ou égal au prix moyen, alors fixe un prix égal au prix précédent plus la moitié de la différence entre le prix précédent et le prix moyen */
new_price = (new_price + ((avg_price - new_price)/2));
MISE À JOUR Table_exemple
SET PRICE_1 = :nouveau_prix
OÙ ID = :ID;
FIN
SUSPENDRE;
FIN
FIN

Cet exemple de procédure stockée illustre l’utilisation de constructions et de déclencheurs de langage de procédure stockée de base. Nous verrons ensuite comment utiliser les procédures stockées pour résoudre certains problèmes courants.

Procédures stockées récursives

Les procédures stockées InterBase peuvent être récursives. Cela signifie qu'une procédure stockée peut s'appeler elle-même. Jusqu'à 1000 niveaux d'imbrication de procédures stockées sont autorisés, mais il ne faut pas oublier que les ressources libres sur le serveur peuvent s'épuiser avant que l'imbrication maximale de HP ne soit atteinte.
Une utilisation courante des procédures stockées consiste à traiter les structures arborescentes stockées dans une base de données. Les arbres sont souvent utilisés dans la composition des produits, les entrepôts, le personnel et d'autres applications courantes.
Regardons un exemple de procédure stockée qui sélectionne tous les produits d'un certain type, à partir d'un certain niveau d'imbrication.
Posons le problème suivant : nous avons un répertoire de biens avec structure hiérarchique ce type :

Marchandises
- Appareils électroménagers
- Réfrigérateurs
- Trois chambres
- Double chambre
- Chambre unique
- Machines à laver
- Verticale
- Frontal
- Classique
- Étroit
- Technologie informatique
....

Cette structure du répertoire des catégories de produits peut comporter des branches de différentes profondeurs. et augmentent également avec le temps. Notre tâche est d'assurer la sélection de tous les éléments finis du répertoire avec "l'extension du nom complet", à partir de n'importe quel nœud. Par exemple, si nous sélectionnons le nœud « Machines à laver », alors nous devons obtenir les catégories suivantes :

Machines à laver - verticales
Machines à laver - Façade Classique
Machines à laver - Avant étroit

Définissons la structure de la table pour stocker les informations du répertoire de produits. Nous utilisons un schéma simplifié pour organiser l'arbre en un seul tableau :

CRÉER UNE TABLE
(ID_GOOD INTEGER NON NULL,
ID_PARENT_GOOD ENTIER,
GOOD_NAME VARCHAR(80),
contrainte clé primaire pkGooci (ID_GOOD));

Nous créons une table GoodsTree, dans laquelle il n'y a que 3 champs : ID_GOOD - l'identifiant intelligent de la catégorie, ID_PARENT_GOOD - l'identifiant de la société mère de cette catégorie et GOOD_NAME - le nom de la catégorie. Pour garantir l'intégrité des données de cette table, nous allons imposer une contrainte de clé étrangère sur cette table :

ALTER TABLE Arbre de marchandises
AJOUTER UNE CONTRAINTE FK_goodstree
CLÉ ÉTRANGÈRE (ID_PARENT_GOOD)
RÉFÉRENCES GOODSTPEE (ID__GOOD)

La table fait référence à elle-même et cette clé étrangère en garde la trace. afin que le tableau ne contienne pas de références à des parents inexistants et empêche également les tentatives de suppression de catégories de produits ayant des enfants.
Entrons les données suivantes dans notre tableau :

Maintenant que nous avons un endroit pour stocker les données, nous pouvons commencer à créer une procédure stockée qui affichera toutes les catégories de produits « finales » sous une forme « étendue » - par exemple, pour la catégorie « Trois chambres », la catégorie complète le nom serait "Réfrigérateurs pour appareils électroménagers" à trois chambres".
Les procédures stockées qui traitent les structures arborescentes ont leur propre terminologie. Chaque élément de l'arbre est appelé un nœud ; et la relation entre les nœuds se référençant les uns aux autres est appelée relation parent-enfant. Les nœuds qui se trouvent tout au bout de l’arbre et qui n’ont pas d’enfants sont appelés « feuilles ».
Pour cette procédure stockée, le paramètre d'entrée sera l'identifiant de catégorie, à partir duquel nous devrons démarrer le zoom. La procédure stockée aura vue suivante:

CRÉER UNE PROCÉDURE GETFULLNAME (ID_GOOD2SHOW INTEGER)
RETOURS (FULL_GOODS_NAME VARCHAR(1000),
ID_CHILD_GOOD INTEGER)
COMME
DÉCLARER LA VARIABLE CURR_CHILD_NAME VARCHAR(80);
COMMENCER
/*0organiser externe Boucle POUR SELECT sur les descendants immédiats du produit avec ID_GOOD=ID_GOOD2SHOW */
POUR SELECT gtl.id_good, gtl.good_name
DE GoodsTree gtl
OÙ gtl.id_parent_good=:ID_good2show
INTO :ID_CHILD_GOOD, :full_goods_name
FAIRE
COMMENCER
/"Vérifiez à l'aide de la fonction EXISTS, qui renvoie VRAI si la requête entre parenthèses renvoie au moins une ligne. Si le nœud trouvé avec ID_PARENT_GOOD = ID_CHILD_GOOD n'a pas d'enfant, alors il s'agit d'une « feuille » de l'arbre et est inclus dans les résultats */
SI (N'EXISTE PAS(
SELECT * FROM GoodsTree
OÙ GoodsTree.id_parent_good=:id_child_good))
ALORS
COMMENCER
/* Passe la « feuille » de l'arbre aux résultats */
SUSPENDRE;
FIN
AUTRE
/* Pour les nœuds qui ont des enfants*/
COMMENCER
/*enregistre le nom du nœud parent dans une variable temporaire */
CURR_CHILD_NAME=full_goods_name ;
/* exécute cette procédure de manière récursive */
POUR
SELECT ID_CHILD_GOOD, full_goods_name
DEPUIS GETFULLNAME (:ID_CHILD_GOOD)
INTO :ID_CHILD_GOOD, :full_goods_name
COMMENCER
/*ajoute le nom du nœud parent au nom de l'enfant trouvé à l'aide de l'opération de concaténation de chaînes || */
full_goods_name=CURR_CHILD_NAME| " " | full_goods_name,-
SUSPENDRE; /* renvoie le nom complet du produit*/
FIN
FIN
FIN
FIN

Si nous remplissons cette procédure avec le paramètre d'entrée ID_GOOD2SHOW= 1, nous obtenons ce qui suit :

Comme vous pouvez le voir, à l'aide d'une procédure stockée récursive, nous avons parcouru toute l'arborescence des catégories et affiché le nom complet des catégories « feuilles » situées tout au bout des branches.

Conclusion

Ceci conclut notre examen des principales fonctionnalités du langage de procédure stockée. Évidemment, il est impossible de maîtriser entièrement le développement de procédures stockées en un seul chapitre, mais nous avons essayé ici d'introduire et d'expliquer les concepts de base associés aux procédures stockées. Les conceptions et techniques décrites pour la conception HP peuvent être appliquées dans la plupart des applications de bases de données.
Certaines des questions importantes liées au développement de procédures stockées seront abordées dans le chapitre suivant - "Capacités avancées du langage de procédure stockée InterBase", consacré à la gestion des exceptions, à la résolution des situations d'erreur dans les procédures stockées et à l'utilisation de tableaux.

DANS MicrosoftSQL Serveur 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 Langage T-SQL Je recommande également de lire le livre - The T-SQL Programmer's Way. Tutoriel sur le langage Transact-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 algorithme, un calcul séquentiel ou une instruction SQL en plusieurs étapes, et afin de ne pas exécuter toutes les instructions qu'il contient à chaque fois. 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 table d'essai et ajoutez-y plusieurs enregistrements, disons qu'il s'agira d'un tableau 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 FIN. À 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 un nouvel enregistrement, 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 sera 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 dans catégorie spécifiée en tenant 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é saisis accidentellement.

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 valeurs correspondantes 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 à l'aide des instructions..

PROCÉDURE DE CHUTE

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

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 l'avoir supprimée, 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 !

Procédure stockée- Ce type spécial Package d'instructions Transact-SQL créé à l'aide de Langage SQL et les extensions de procédure. La principale différence entre un package et une procédure stockée est que cette dernière est stockée en tant qu'objet de base de données. En d’autres termes, les procédures stockées sont stockées côté serveur pour améliorer les performances et la cohérence des tâches répétables.

Le moteur de base de données prend en charge les procédures stockées et les procédures système. Les procédures stockées sont créées de la même manière que tous les autres objets de base de données, c'est-à-dire en utilisant le langage DDL. Procédures système sont fournis par le moteur de base de données et peuvent être utilisés pour accéder aux informations dans répertoire système et ses modifications.

Lorsque vous créez une procédure stockée, vous pouvez définir une liste facultative de paramètres. De cette façon, la procédure acceptera les arguments appropriés à chaque appel. Les procédures stockées peuvent renvoyer une valeur contenant défini par l'utilisateur informations ou, en cas d'erreur, un message d'erreur correspondant.

La procédure stockée est précompilée avant d'être stockée en tant qu'objet dans la base de données. La forme précompilée de la procédure est stockée dans la base de données et utilisée à chaque appel. Cette propriété des procédures stockées offre l’avantage important d’éliminer (dans presque tous les cas) les compilations de procédures répétées et d’obtenir des améliorations de performances correspondantes. Cette propriété des procédures stockées a également un effet positif sur la quantité de données échangées entre le système de base de données et les applications. En particulier, l’appel d’une procédure stockée d’une taille de plusieurs milliers d’octets peut nécessiter moins de 50 octets. Lorsque plusieurs utilisateurs effectuent des tâches répétitives à l’aide de procédures stockées, l’effet cumulé de ces économies peut être très important.

Les procédures stockées peuvent également être utilisées aux fins suivantes :

pour créer un journal des actions avec des tables de base de données.

L'utilisation de procédures stockées offre un niveau de contrôle de sécurité qui va bien au-delà de la sécurité fournie par l'utilisation des instructions GRANT et REVOKE, qui accordent différents privilèges d'accès aux utilisateurs. Cela est possible car l'autorisation d'exécuter une procédure stockée est indépendante de l'autorisation de modifier les objets contenus dans la procédure stockée, comme décrit dans la section suivante.

Les procédures stockées qui créent des journaux d'opérations d'écriture et/ou de lecture de table fournissent une option supplémentaire pour la sécurité de la base de données. Grâce à de telles procédures, l'administrateur de base de données peut surveiller les modifications apportées à la base de données par les utilisateurs ou les programmes d'application.

Création et exécution de procédures stockées

Les procédures stockées sont créées à l'aide d'une instruction CRÉER UNE PROCÉDURE, qui a la syntaxe suivante :

CREATE PROC nom_proc [((@param1) type1 [ VARYING] [= default1] )] (, ...) AS batch | NOM EXTERNE nom_méthode Conventions de syntaxe

Le paramètre schema_name spécifie le nom du schéma attribué par le propriétaire de la procédure stockée créée. Le paramètre proc_name spécifie le nom de la procédure stockée. Le paramètre @param1 est un paramètre de procédure (argument formel) dont le type de données est déterminé par le paramètre type1. Les paramètres de procédure sont locaux dans la procédure, tout comme les variables locales sont locales dans le package. Les paramètres de procédure sont des valeurs transmises par l'appelant à la procédure pour y être utilisée. Le paramètre default1 spécifie la valeur par défaut du paramètre de procédure correspondant. (La valeur par défaut peut également être NULL.)

Option SORTIE indique qu'un paramètre de procédure est un paramètre de retour et peut être utilisé pour renvoyer une valeur d'une procédure stockée à la procédure ou au système appelant.

Comme mentionné précédemment, la forme précompilée d'une procédure est stockée dans la base de données et utilisée à chaque appel. Si pour une raison quelconque la procédure stockée doit être compilée à chaque appel, lors de la déclaration de la procédure, utilisez AVEC option RECOMPILE. L’utilisation de l’option WITH RECOMPILE annule l’un des avantages les plus importants des procédures stockées : l’amélioration des performances due à une seule compilation. Par conséquent, l'option WITH RECOMPILE ne doit être utilisée que lorsque les objets de base de données utilisés par la procédure stockée sont fréquemment modifiés.

Clause EXECUTE AS définit le contexte de sécurité dans lequel la procédure stockée doit s'exécuter après son appel. En définissant ce contexte, avec en utilisant la base de données Le moteur peut contrôler la sélection des comptes d'utilisateurs pour vérifier les autorisations d'accès aux objets référencés par une procédure stockée donnée.

Par défaut, seuls les membres du rôle de serveur fixe sysadmin et les rôles de base de données fixes db_owner ou db_ddladmin peuvent utiliser l'instruction CREATE PROCEDURE. Mais les membres de ces rôles peuvent attribuer ce droit à d'autres utilisateurs à l'aide de l'instruction PROCÉDURE DE CRÉATION DE SUBVENTION.

L'exemple ci-dessous montre comment créer une procédure stockée simple pour utiliser la table Projet :

UTILISER SampleDb ; ALLER CRÉER UNE PROCÉDURE Augmenter le budget (@percent INT=5) AS UPDATE Project SET Budget = Budget + Budget * @percent/100 ;

Comme indiqué précédemment, pour séparer deux paquets, utilisez Instructions ALLER. L'instruction CREATE PROCEDURE ne peut pas être combinée avec d'autres instructions Transact-SQL dans le même lot. La procédure stockée IncreaseBudget augmente les budgets de tous les projets d'un certain pourcentage, déterminé par le paramètre @percent. La procédure définit également une valeur de pourcentage par défaut (5) qui est utilisée si cet argument n'est pas présent lors de l'exécution de la procédure.

Les procédures stockées peuvent accéder à des tables qui n'existent pas. Cette propriété vous permet de déboguer le code de procédure sans créer au préalable les tables appropriées ni même vous connecter au serveur de destination.

Contrairement aux procédures stockées primaires, qui sont toujours stockées dans la base de données actuelle, il est possible de créer des procédures stockées temporaires qui sont toujours stockées dans la base de données système temporaire tempdb. Une raison de créer des procédures stockées temporaires peut être d'éviter d'exécuter de manière répétée un groupe spécifique d'instructions lors de la connexion à une base de données. Vous pouvez créer des procédures temporaires locales ou globales. Pour ce faire, le nom de la procédure locale est spécifié avec un seul caractère # (#proc_name) et le nom de la procédure globale est spécifié avec un double caractère (##proc_name).

Une procédure stockée temporaire locale ne peut être exécutée que par l'utilisateur qui l'a créée, et uniquement lorsqu'il est connecté à la base de données dans laquelle elle a été créée. Une procédure temporaire globale peut être exécutée par tous les utilisateurs, mais seulement jusqu'à ce que la dernière connexion sur laquelle elle est exécutée (généralement la connexion du créateur de la procédure) se termine.

Le cycle de vie d'une procédure stockée se compose de deux étapes : sa création et son exécution. Chaque procédure est créée une fois et exécutée plusieurs fois. La procédure stockée est exécutée en utilisant EXÉCUTER les instructions un utilisateur qui est propriétaire d'une procédure ou qui dispose du privilège EXECUTE pour accéder à cette procédure. L'instruction EXECUTE a la syntaxe suivante :

[] [@return_status =] (proc_name | @proc_name_var) ([[@parameter1 =] valeur | [@parameter1=] @variable ] | DEFAULT).. Conventions de syntaxe

À l'exception du paramètre return_status, tous les paramètres de l'instruction EXECUTE ont la même signification logique que les mêmes paramètres de l'instruction CREATE PROCEDURE. Le paramètre return_status spécifie une variable entière qui stocke l'état de retour de la procédure. Une valeur peut être attribuée à un paramètre à l'aide d'une constante (valeur) ou d'une variable locale (@variable). L'ordre des valeurs des paramètres nommés n'est pas important, mais les valeurs des paramètres sans nom doivent être fournies dans l'ordre dans lequel elles sont définies dans l'instruction CREATE PROCEDURE.

Clause PAR DÉFAUT fournit la valeur par défaut d'un paramètre de procédure spécifié dans la définition de la procédure. Lorsqu'une procédure attend une valeur pour un paramètre pour lequel aucune valeur par défaut n'a été définie et que le paramètre est manquant ou que le mot clé DEFAULT est spécifié, une erreur se produit.

Lorsque l'instruction EXECUTE est la première instruction d'un lot, le mot clé EXECUTE peut être omis. Cependant, il est plus sûr d’inclure ce mot dans chaque paquet. L'utilisation de l'instruction EXECUTE est illustrée dans l'exemple ci-dessous :

UTILISER SampleDb ; EXÉCUTER Augmenter le budget 10 ;

L'instruction EXECUTE dans cet exemple exécute la procédure stockée IncreaseBudget, qui augmente le budget de tous les projets de 10 %.

L'exemple ci-dessous montre comment créer une procédure stockée pour traiter les données dans les tables Employee et Works_on :

L'exemple de procédure ModifyEmpId illustre l'utilisation de procédures stockées dans le cadre du processus de maintien de l'intégrité référentielle (dans ce cas entre les tables Employee et Works_on). Une procédure stockée similaire peut être utilisée dans une définition de déclencheur, qui fournit en fait une intégrité référentielle.

L'exemple suivant montre l'utilisation d'une clause OUTPUT dans une procédure stockée :

Cette procédure stockée peut être exécutée à l'aide des instructions suivantes :

DÉCLARE @quantityDeleteEmployee INT ; EXECUTE DeleteEmployee @empId=18316, @counter=@quantityDeleteEmployee OUTPUT ; PRINT N"Employés supprimés : " + convert(nvarchar(30), @quantityDeleteEmployee);

Cette procédure compte le nombre de projets sur lesquels travaille l'employé portant le matricule @empId et affecte la valeur résultante au paramètre ©counter. Une fois que toutes les lignes d'un matricule donné ont été supprimées des tables Employee et Works_on, la valeur calculée est affectée à la variable @quantityDeleteEmployee.

La valeur du paramètre est renvoyée à la procédure appelante uniquement si l'option OUTPUT est spécifiée. Dans l'exemple ci-dessus, la procédure DeleteEmployee transmet le paramètre @counter à la procédure appelante, la procédure stockée renvoie donc une valeur au système. Par conséquent, le paramètre @counter doit être spécifié à la fois dans l'option OUTPUT lors de la déclaration d'une procédure et dans l'instruction EXECUTE lors de son appel.

Clause WITH RESULTS SETS de l'instruction EXECUTE

Dans SQL Server 2012, pour l'instruction EXECUTE, vous entrez Clause AVEC ENSEMBLES DE RÉSULTATS, grâce auquel, lorsque certaines conditions sont remplies, vous pouvez modifier la forme de l'ensemble de résultats d'une procédure stockée.

Les deux exemples suivants aideront à expliquer cette phrase. Le premier exemple est un exemple d'introduction qui montre à quoi pourrait ressembler le résultat lorsque la clause WITH RESULTS SETS est omise :

La procédure EmployeesInDept est procédure simple, qui affiche les matricules et les noms de tous les employés travaillant dans un service spécifique. Le numéro de service est un paramètre de la procédure et doit être précisé lors de son appel. L'exécution de cette procédure produit un tableau avec deux colonnes dont les en-têtes correspondent aux noms des colonnes correspondantes dans la table de la base de données, c'est-à-dire Identifiant et nom de famille. Pour modifier les en-têtes des colonnes de résultats (ainsi que leur type de données), SQL Server 2012 utilise la nouvelle clause WITH RESULTS SETS. L'application de cette phrase est illustrée dans l'exemple ci-dessous :

UTILISER SampleDb ; EXEC EmployeesInDept "d1" AVEC DES ENSEMBLES DE RÉSULTATS (( INT NOT NULL, [LastName] CHAR(20) NOT NULL));

Le résultat de l’exécution d’une procédure stockée ainsi appelée sera le suivant :

Comme vous pouvez le constater, l'exécution d'une procédure stockée à l'aide de la clause WITH RESULT SETS dans l'instruction EXECUTE vous permet de modifier les noms et les types de données des colonnes du jeu de résultats produit par la procédure. Ainsi, cette nouvelle fonctionnalité offre une plus grande flexibilité dans l'exécution des procédures stockées et le placement de leurs résultats dans une nouvelle table.

Modification de la structure des procédures stockées

Le moteur de base de données prend également en charge l'instruction MODIFIER LA PROCÉDURE pour modifier la structure des procédures stockées. L'instruction ALTER PROCEDURE est généralement utilisée pour modifier les instructions Transact-SQL au sein d'une procédure. Tous les paramètres de l'instruction ALTER PROCEDURE ont la même signification que les mêmes paramètres de l'instruction CREATE PROCEDURE. L'objectif principal de l'utilisation de cette instruction est d'éviter de remplacer les droits de procédure stockée existants.

Le moteur de base de données prend en charge Type de données CURSEUR. Ce type de données est utilisé pour déclarer des curseurs dans des procédures stockées. Curseur est une construction de programmation utilisée pour stocker les résultats d'une requête (généralement un ensemble de lignes) et permettre aux utilisateurs d'afficher ce résultat ligne par ligne.

Pour supprimer une ou un groupe de procédures stockées, utilisez Instruction PROCÉDURE DE CHUTE. Seuls le propriétaire ou les membres des rôles fixes db_owner et sysadmin peuvent supprimer une procédure stockée.

Procédures stockées et CLR

SQL Server prend en charge le Common Language Runtime (CLR), qui vous permet de développer objets divers bases de données (procédures stockées, fonctions définies par l'utilisateur, déclencheurs, fonctions statistiques définies par l'utilisateur et types personnalisés données), en utilisant C# et Visual Basic. Le CLR vous permet également d'exécuter ces objets à l'aide du système d'exécution commun.

Le Common Language Runtime est activé et désactivé à l'aide de l'option clr_enabled procédure système sp_configure, qui est lancé pour exécution par instruction RECONFIGURER. L'exemple suivant montre comment utiliser la procédure système sp_configure pour activer le CLR :

UTILISER SampleDb ; EXEC sp_configure "clr_enabled",1 RECONFIGURER

Pour créer, compiler et enregistrer une procédure à l'aide du Common Language Runtime, vous devez effectuer la séquence d'étapes suivante dans l'ordre indiqué :

Créez une procédure stockée en C# ou Visual Basic, puis compilez-la à l'aide du compilateur approprié.

Utilisation des instructions CRÉER UN ASSEMBLAGE, créez le fichier exécutable correspondant.

Exécutez la procédure à l'aide de l'instruction EXECUTE.

L'image ci-dessous montre diagramme graphique les étapes décrites précédemment. Ci-dessous, plus description détaillée ce processus.

Créez d’abord le programme requis dans un environnement de développement tel que Studio visuel. Compiler programme prêt à l'emploi en code objet à l’aide du compilateur C# ou Visual Basic. Ce code est enregistré dans le fichier bibliothèque dynamique(.dll) qui sert de source à l'instruction CREATE ASSEMBLY qui crée le code exécutable intermédiaire. Ensuite, émettez une instruction CREATE PROCEDURE pour enregistrer le code en cours d'exécution en tant qu'objet de base de données. Enfin, exécutez la procédure à l’aide de l’instruction EXECUTE familière.

L'exemple ci-dessous montre le code source d'une procédure stockée en C# :

Utilisation de System.Data.SqlClient ; en utilisant Microsoft.SqlServer.Server ; classe partielle publique StoredProcedures ( public static int CountEmployees() ( int rows; SqlConnection connection = new SqlConnection("Context Connection=true"); connection.Open(); SqlCommand cmd = connection.CreateCommand(); cmd.CommandText = "select count(*) as "Nombre d'employés" " + "from Employee"; rows = (int)cmd.ExecuteScalar(); connection.Close(); return rows; ) )

Cette procédure implémente une requête pour compter le nombre de lignes dans la table Employee. L'utilisation de directives au début d'un programme spécifie les espaces de noms requis pour exécuter le programme. L'utilisation de ces directives vous permet de spécifier des noms de classe dans le code source sans spécifier explicitement les espaces de noms correspondants. Ensuite, la classe StoredProcedures est définie, pour laquelle Attribut SQLProcedure, qui informe le compilateur que cette classe est une procédure stockée. La méthode CountEmployees() est définie dans le code de la classe. Une connexion au système de base de données est établie via une instance de la classe Connexion SQL. Pour ouvrir une connexion, la méthode Open() de cette instance est utilisée. UN Méthode CreateCommand() vous permet d'accéder à une instance d'une classe Commande SQL, à laquelle la commande SQL requise est transmise.

Dans l'extrait de code suivant :

Cmd.CommandText = "select count(*) as "Nombre d'employés" " + "from Employee";

utilise une instruction SELECT pour compter le nombre de lignes dans la table Employee et afficher le résultat. Le texte de la commande est spécifié en définissant la propriété CommandText de la variable cmd sur l'instance renvoyée par la méthode CreateCommand(). On l'appelle ensuite Méthode ExecuteScalar() Instance SQLCommand. Cette méthode renvoie une valeur scalaire qui est convertie en type entier int data et affecté à la variable rows.

Vous pouvez maintenant compiler ce code à l'aide de Visual Studio. J'ai ajouté cette classe à un projet appelé CLRStoredProcedures afin que Visual Studio compile un assembly du même nom avec une extension *.dll. L'exemple ci-dessous montre l'étape suivante de la création d'une procédure stockée : la création du code exécutable. Avant d'exécuter le code dans cet exemple, vous devez connaître l'emplacement du fichier dll compilé (généralement situé dans le dossier Debug du projet).

UTILISER SampleDb ; ALLER CRÉER UN ASSEMBLAGE CLRStoredProcedures À PARTIR DE "D:\Projects\CLRStoredProcedures\bin\Debug\CLRStoredProcedures.dll" AVEC PERMISSION_SET = SAFE

L'instruction CREATE ASSEMBLY prend le code managé en entrée et crée un objet correspondant sur lequel vous pouvez créer des procédures stockées CLR, des fonctions définies par l'utilisateur et des déclencheurs. Cette instruction a la syntaxe suivante :

CREATE ASSEMBLY nom_assembly [ AUTHORIZATION nom_propriétaire ] FROM (fichier_dll) Conventions de syntaxe

Le paramètre assembly_name spécifie le nom de l'assembly. La clause facultative AUTHORIZATION spécifie le nom du rôle en tant que propriétaire de cet assembly. La clause FROM spécifie le chemin où se trouve l'assembly à charger.

Clause AVEC PERMISSION_SET est une clause très importante de l'instruction CREATE ASSEMBLY et doit toujours être spécifiée. Il définit l'ensemble des autorisations accordées au code assembleur. L'ensemble d'autorisations SAFE est le plus restrictif. Le code assembleur qui dispose de ces droits ne peut pas accéder aux ressources système, tels que des fichiers. L'ensemble de droits EXTERNAL_ACCESS permet au code assembleur d'accéder à certaines ressources système externes, tandis que l'ensemble de droits UNSAFE permet un accès illimité aux ressources à la fois à l'intérieur et à l'extérieur du système de base de données.

Pour enregistrer les informations du code assembleur, l'utilisateur doit être en mesure d'émettre une instruction CREATE ASSEMBLY. Le propriétaire de l’assembly est l’utilisateur (ou le rôle) qui exécute l’instruction. Vous pouvez désigner un autre utilisateur comme propriétaire de l'assembly à l'aide de la clause AUTHORIZATION de l'instruction CREATE SCHEMA.

Le moteur de base de données prend également en charge les instructions ALTER ASSEMBLY et DROP ASSEMBLY. Instruction ALTER ASSEMBLY utilisé pour mettre à jour l'assembly vers dernière version. Cette instruction ajoute ou supprime également des fichiers associés à l'assembly correspondant. Instruction d'ASSEMBLAGE DE GOUTTE Supprime l'assembly spécifié et tous ses fichiers associés de la base de données actuelle.

L'exemple suivant montre comment créer une procédure stockée basée sur le code managé que vous avez implémenté précédemment :

UTILISER SampleDb ; ALLER CRÉER UNE PROCÉDURE CountEmployees COMME NOM EXTERNE CLRStoredProcedures.StoredProcedures.CountEmployees

L'instruction CREATE PROCEDURE de l'exemple diffère de la même instruction des exemples précédents en ce sens qu'elle contient Paramètre NOM EXTERNE. Ce paramètre précise que le code est généré Environnement CLR. Le nom dans cette phrase se compose de trois parties :

nom_assembly.nom_classe.nom_méthode

assembly_name - indique le nom de l'assembly ;

class_name - indique le nom de la classe générale ;

nom_méthode - partie facultative, spécifie le nom de la méthode définie dans la classe.

L'exécution de la procédure CountEmployees est illustrée dans l'exemple ci-dessous :

UTILISER SampleDb ; DECLARE @count INT EXECUTE @count = CountEmployees PRINT @count -- Retour 7

L'instruction PRINT renvoie le nombre actuel de lignes dans la table Employee.

Nous considérons une situation dans laquelle les procédures stockées peuvent dégrader les performances des requêtes.

Lors de la compilation de procédures stockées dans MS SQL Server 2000, les procédures stockées sont placées dans le cache de procédures, ce qui peut améliorer les performances lors de leur exécution en éliminant le besoin d'analyser, d'optimiser et de compiler le code de procédure stockée.
D’un autre côté, le stockage du code compilé d’une procédure stockée présente des pièges qui peuvent avoir l’effet inverse.
Le fait est que lors de la compilation d'une procédure stockée, le plan d'exécution des instructions qui composent le code de la procédure est compilé en conséquence, si la procédure stockée compilée est mise en cache, alors son plan d'exécution est mis en cache et, par conséquent, la procédure stockée ne le sera pas ; optimisé pour une situation spécifique et des paramètres de requête.
Faisons une petite expérience pour le démontrer.

ÉTAPE 1. Création d'une base de données.
Pour l'expérience, créons base de données séparée données.

CRÉER UNE BASE DE DONNÉES test_sp_perf
ON (NAME="test_data", FILENAME="c:\temp\test_data", SIZE=1, MAXSIZE=10,FILEGROWTH=1Mb)
CONNEXION (NAME="test_log", FILENAME="c:\temp\test_log", SIZE=1, MAXSIZE=10,FILEGROWTH=1Mb)

ÉTAPE 2. Création d'un tableau.
CREATE TABLE sp_perf_test(column1 int, column2 char(5000))

ÉTAPE 3. Remplir le tableau avec des lignes de test. Les lignes en double sont intentionnellement ajoutées à un tableau. 10 000 lignes avec des nombres de 1 à 10 000 et 10 000 lignes avec des nombres de 50 000.

DÉCLARER @i int
FIXER @i=1
PENDANT (@i<10000)
COMMENCER
INSERT INTO sp_perf_test(column1, column2) VALUES (@i,"Test string #"+CAST (@i as char(8)))
INSERT INTO sp_perf_test(column1, column2) VALUES(50000,"Test string #"+CAST (@i as char(8)))
ENSEMBLE @i= @i+1
FIN

SELECT COUNT(*) FROM sp_perf_test
ALLER

ÉTAPE 4. Création d'un index non clusterisé. Étant donné que le plan d'exécution est mis en cache avec la procédure, l'index sera utilisé de la même manière pour tous les appels.

CRÉER UN INDEX NON CLUSTERÉ CL_perf_test ON sp_perf_test(column1)
ALLER

ÉTAPE 5. Création d'une procédure stockée. La procédure exécute simplement une instruction SELECT avec une condition.

CRÉER PROC proc1 (@param int)
COMME
SELECT colonne1, colonne2 FROM sp_perf_test OÙ colonne1=@param
ALLER

ÉTAPE 6. Exécution d'une procédure stockée. Lors de l'exécution d'une procédure vulnérable, un paramètre sélectif est spécifiquement utilisé. À la suite de la procédure, nous obtenons 1 ligne. Le plan d'exécution indique l'utilisation d'un index non clusterisé, car La requête est sélective et c'est le meilleur moyen de récupérer la ligne. Une procédure optimisée pour récupérer une seule ligne est stockée dans le cache procédural.

Exécution proc1 1234
ALLER

ÉTAPE 7. Exécutez une procédure stockée avec un paramètre non sélectif. La valeur utilisée comme paramètre est 50 000. Il y a environ 10 000 lignes avec cette valeur dans la première colonne ; par conséquent, l'utilisation d'un index non clusterisé et l'opération de recherche de signets sont inefficaces, mais comme le code compilé avec le plan d'exécution est stocké dans le cache procédural, c'est ce qui sera utilisé. Le plan d'exécution le montre, ainsi que le fait qu'une opération de recherche de signets a été effectuée pour 9999 lignes.

Exécution proc1 50000
ALLER

ÉTAPE 8. Effectuer une sélection de lignes avec le premier champ égal à 50 000. Lors de l'exécution d'une requête distincte, la requête est optimisée et compilée avec une valeur spécifique pour la première colonne. En conséquence, l'optimiseur de requêtes détermine que le champ est dupliqué plusieurs fois et décide d'utiliser l'opération d'analyse de table, qui dans ce cas est beaucoup plus efficace que l'utilisation d'un index non clusterisé.

SELECT colonne1, colonne2 FROM sp_perf_test OÙ colonne1=50000
ALLER

Ainsi, nous pouvons conclure que l’utilisation de procédures stockées n’améliore pas toujours les performances des requêtes. Vous devez être très prudent avec les procédures stockées qui opèrent sur des résultats avec un nombre variable de lignes et qui utilisent des plans d'exécution différents.
Vous pouvez utiliser le script pour répéter l'expérience sur votre serveur MS SQL.

Incluez une ligne dans vos procédures - SET NOCOUNT ON :

Avec chaque expression DML, le serveur SQL nous renvoie soigneusement un message contenant le nombre d'enregistrements traités. Ces informations peuvent nous être utiles lors du débogage du code, mais après cela elles seront totalement inutiles. En écrivant SET NOCOUNT ON, on désactive cette fonction. Pour les procédures stockées contenant plusieurs expressions ou/et boucles, cette action peut entraîner une augmentation significative des performances, car la quantité de trafic sera considérablement réduite.

Transact-SQL

Utilisez le nom du schéma avec le nom de l'objet :

Eh bien, je pense que c'est clair. Cette opération indique au serveur où chercher les objets et au lieu de fouiller au hasard dans ses bacs, il saura immédiatement où aller et quoi emporter. Avec un grand nombre de bases de données, de tables et de procédures stockées, cela peut nous faire gagner beaucoup de temps et de nerfs.

Transact-SQL

SELECT * FROM dbo.MyTable --Le faire de cette façon est une bonne chose -- Au lieu de SELECT * FROM MyTable --Et le faire de cette façon est une mauvaise --Appeler la procédure EXEC dbo.MyProc --Bien encore --Au lieu d'EXEC MyProc --Mauvais!

N'utilisez pas le préfixe "sp_" dans le nom de vos procédures stockées :

Si le nom de notre procédure commence par "sp_", SQL Server recherchera d'abord dans sa base de données principale. Le fait est que ce préfixe est utilisé pour les procédures stockées internes personnelles du serveur. Par conséquent, son utilisation peut entraîner des coûts supplémentaires et même des résultats incorrects si une procédure portant le même nom que la vôtre se trouve dans sa base de données.

Utilisez SI EXISTE (SELECT 1) au lieu de SI EXISTE (SELECT *) :

Pour vérifier l'existence d'un enregistrement dans une autre table, nous utilisons l'instruction IF EXISTS. Cette expression renvoie vrai si au moins une valeur est renvoyée par l'expression interne, peu importe « 1 », toutes les colonnes ou un tableau. Les données renvoyées ne sont en principe utilisées d’aucune façon. Ainsi, pour compresser le trafic lors de la transmission des données, il est plus logique d'utiliser « 1 », comme indiqué ci-dessous.