Les procédures stockées SQL sont des modules de programme exécutables qui peuvent être stockés 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 indiqué par ceci mot-clé"Sortie" 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 est un type spécial de package d'instructions Transact-SQL créé à l'aide du langage SQL et des extensions procédurales. 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 du catalogue système et les modifier.

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 des informations définies par l'utilisateur ou, en cas d'erreur, un message d'erreur approprié.

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 opportunité supplémentaire assurer 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, le moteur de base de données 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 la procédure stocké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 SupprimerEmployee @empId=18316, @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 une procédure simple qui affiche les matricules et les noms de famille 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. Le 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 Common Language Runtime

SQL Server prend en charge le Common Language Runtime (CLR), qui vous permet de développer divers objets de base de données (procédures stockées, fonctions définies par l'utilisateur, déclencheurs, agrégations définies par l'utilisateur et types de données personnalisés) à l'aide de 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 CLR, 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é.

Mode d'emploi CRÉER UN ASSEMBLAGE, créez le fichier exécutable correspondant.

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

La figure ci-dessous montre un diagramme graphique des étapes décrites précédemment. Ci-dessous, plus Description détaillée ce processus.

Tout d'abord, créez le programme requis dans un environnement de développement tel que Visual Studio. Compilez le programme terminé en code objet à l'aide d'un compilateur C# ou Visual Basic. Ce code est stocké dans un fichier de bibliothèque de liens dynamiques (.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" " + "de l'employé"; 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 un type de données entier et affectée à 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 disposant de ces droits ne peut pas accéder aux ressources système externes telles que les 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 ci-dessous 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. Cette option spécifie que le code est généré par le Common Language Runtime. 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.

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 !

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.

Le nom de la procédure doit être suivi du mot clé AS.

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

But du travail– apprendre à créer et utiliser des procédures stockées sur le serveur de base de données.

1. Parcourez tous les exemples, analysez les résultats de leur exécution dans l'utilitaire SQL Server Management Studio. Vérification de la présence des procédures créées dans la base de données actuelle.

2. Réaliser tous les exemples et tâches pendant les travaux de laboratoire.

3. Effectuer des tâches individuelles selon les options.

Explications pour réaliser les travaux

Pour maîtriser la programmation de procédures stockées, nous utilisons un exemple de base de données appelée DB_Livres, qui a été créé dans le cadre du travail de laboratoire n°1. Lorsque vous effectuez des exemples et des tâches, faites attention à la correspondance des noms de la base de données, des tables et des autres objets du projet.

Procédures stockées sont un ensemble de commandes composé d'un ou plusieurs Instructions SQL ou fonctions et stockés dans une base de données sous forme compilée.

Types de procédures stockées

Les procédures stockées système sont conçues pour effectuer diverses actions administratives. Presque toutes les activités d'administration du serveur sont effectuées avec leur aide. On peut dire que les procédures stockées système sont une interface qui permet de travailler avec les tables système. Les procédures stockées système ont le préfixe sp_ et sont stockées dans base du système données et peut être appelé dans le contexte de toute autre base de données.

Les procédures stockées personnalisées implémentent certaines actions. Les procédures stockées sont un objet de base de données à part entière. De ce fait, chaque procédure stockée se trouve dans une base de données spécifique, où elle est exécutée.

Les procédures stockées temporaires n'existent que pendant une courte période, après quoi elles sont automatiquement détruites par le serveur. Ils sont divisés en locaux et mondiaux. Les procédures stockées temporaires locales ne peuvent être appelées qu'à partir de la connexion dans laquelle elles sont créées. 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, les procédures stockées de ce type sont automatiquement supprimées lorsque l'utilisateur se déconnecte ou que le serveur est redémarré ou arrêté. Les procédures stockées temporaires globales sont disponibles pour toute connexion à partir d'un serveur disposant de 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éation, modification de procédures stockées

Créer une procédure stockée implique de résoudre les problèmes suivants : planification des droits d'accès. Lorsque vous créez une procédure stockée, sachez qu'elle aura les mêmes droits d'accès aux objets de la base de données que l'utilisateur qui l'a créée ; définir les paramètres d'une procédure stockée ; les procédures stockées peuvent avoir des paramètres d'entrée et de sortie ; développement de code de 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.

La syntaxe de l'opérateur pour créer une nouvelle ou modifier une procédure stockée existante en notation MS SQL Server :

( CREATE | ALTER ) PROC[ EDURE] nom_procédure [ ;numéro] [ ( @nom_paramètre type_données ) [ VARYING ] [ = DEFAULT ] [ OUTPUT ] ] [ ,... n] [ AVEC ( RECOMPILE | CHIFFREMENT | RECOMPILE, CHIFFREMENT ) ] [ POUR LA RÉPLICATION] AS sql_statement [ ... n]

Regardons les paramètres de cette commande.

À l'aide des 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. Par conséquent, pour placer la procédure stockée que vous créez dans une base de données spécifique, vous devez émettre la commande CREATE PROCEDURE dans le contexte de cette base de données. Lorsque vous accédez à des objets de la même base de données à partir du corps d'une procédure stockée, vous pouvez utiliser des noms abrégés, 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.

Pour transmettre des données d'entrée et de sortie, les noms de paramètres dans la procédure stockée que vous créez doivent commencer par le caractère @. Vous pouvez spécifier plusieurs paramètres dans une seule procédure stockée, 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 des paramètres d'une procédure stockée, tous les types conviennent 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 d'une 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 d'une procédure stockée. Cependant, cela ne signifie pas que le paramètre n'est pas adapté pour transmettre des valeurs à une procédure stockée. La spécification du mot clé OUTPUT demande au serveur, lors de la sortie d'une procédure stockée, d'attribuer la valeur actuelle du paramètre à la variable locale qui a été spécifiée comme valeur de paramètre lors de l'appel de la procédure. 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 normaux ne sont pas autorisées. Le mot clé VARYING est utilisé conjointement avec le paramètre OUTPUT, qui est de type CURSOR. Il spécifie que la sortie sera l'ensemble de résultats.

Le mot-clé DEFAULT représente la valeur que prendra par défaut le paramètre correspondant. Ainsi, lors de l'appel d'une procédure, vous n'avez pas besoin de spécifier explicitement la valeur du paramètre correspondant.

Étant donné que le serveur met en cache le plan d'exécution de la requête et le code compilé, lors du prochain appel de la procédure, les valeurs prêtes à l'emploi seront utilisées. Cependant, dans certains cas, il est encore nécessaire de recompiler le code de la procédure. La spécification du mot clé RECOMPILE demande au système de créer un plan d'exécution pour la procédure stockée à chaque fois qu'elle est appelée.

Le paramètre FOR REPLICATION est requis lors de la réplication de données et de l'inclusion de la procédure stockée créée en tant qu'article dans une publication. Le mot clé ENCRYPTION demande au serveur de chiffrer le code de la procédure stockée, ce qui peut fournir une protection contre l'utilisation d'algorithmes propriétaires qui implémentent la procédure stockée. Le mot clé AS est placé au début du corps de la procédure stockée lui-même. Le corps de la procédure peut utiliser presque toutes les commandes SQL, déclarer des transactions, définir des verrous et appeler d'autres procédures stockées. Vous pouvez quitter une procédure stockée à l'aide de la commande RETURN.

Suppression d'une procédure stockée

DROP PROCEDURE (nom_procédure) [,... n]

Exécuter une procédure stockée

Pour exécuter une procédure stockée, utilisez la commande : [ [ EXEC [ UTE] nom_procédure [ ;numéro] [ [ @parameter_name= ] ( valeur | @variable_name) [ OUTPUT ] | [ PAR DÉFAUT ] ] [ ,... n]

Si l'appel de procédure stockée n'est pas la seule commande du lot, la commande EXECUTE est requise. De plus, cette commande est nécessaire pour appeler une procédure depuis le corps d’une autre procédure ou d’un autre trigger.

L'utilisation du mot-clé OUTPUT lors de l'appel d'une procédure n'est autorisée que pour les paramètres déclarés lors de la création de la procédure avec le mot-clé OUTPUT.

Lorsque le mot-clé DEFAULT est spécifié pour un paramètre lors de l'appel d'une procédure, la valeur par défaut sera utilisée. Naturellement, le mot DEFAULT spécifié n'est autorisé que pour les paramètres pour lesquels une valeur par défaut est définie.

La syntaxe de la commande EXECUTE montre que les noms de paramètres peuvent être omis lors de l'appel d'une procédure. Cependant, dans ce cas, l'utilisateur doit spécifier les valeurs des paramètres dans le même ordre dans lequel elles ont été répertoriées lors de la création de la procédure. Vous ne pouvez pas attribuer une valeur par défaut à un paramètre en l'omettant simplement lors de l'énumération. Si vous souhaitez omettre les paramètres ayant une valeur par défaut, il suffit de spécifier explicitement les noms des paramètres lors de l'appel de la procédure stockée. De plus, vous pouvez ainsi lister les paramètres et leurs valeurs dans n'importe quel ordre.

Notez que lors de l'appel d'une procédure, soit les noms de paramètres avec des valeurs, soit seules les valeurs sans nom de paramètre sont spécifiés. Leur combinaison n'est pas autorisée.

Utilisation de RETURN dans une procédure stockée

Vous permet de quitter la procédure à tout moment selon une condition spécifiée, et vous permet également de transmettre le résultat de la procédure sous forme de nombre, par lequel vous pouvez juger de la qualité et de l'exactitude de la procédure. Un exemple de création d'une procédure sans paramètres :

CREATE PROCEDURE Count_Books AS SELECT COUNT (Code_book) FROM Books GO

Exercice 1.

EXEC Count_Books

Vérifiez le résultat.

Un exemple de création d'une procédure avec un paramètre d'entrée :

CRÉER UNE PROCÉDURE Count_Books_Pages @Count_pages AS INT AS SELECT COUNT (Code_book) FROM Books WHERE Pages>= @Count_pages GO

Tâche 2. Créez cette procédure dans la section Procédures stockées de la base de données DB_Books à l'aide de l'utilitaire SQL Server Management Studio. Exécutez-le en utilisant la commande

EXEC Count_Books_Pages 100

Vérifiez le résultat.

Un exemple de création d'une procédure avec des paramètres d'entrée :

CREATE PROCEDURE Count_Books_Title @Count_pages AS INT , @Title AS CHAR (10 ) AS SELECT COUNT (Code_book) FROM Books WHERE Pages>= @Count_pages AND Title_book LIKE @Title GO

Tâche 3. Créez cette procédure dans la section Procédures stockées de la base de données DB_Books à l'aide de l'utilitaire SQL Server Management Studio. Exécutez-le en utilisant la commande

EXEC Count_Books_Titre 100, "P%"

Vérifiez le résultat.

Un exemple de création d'une procédure avec des paramètres d'entrée et un paramètre de sortie :

CREATE PROCEDURE Count_Books_Itogo @Count_pages INT , @Title CHAR (10 ) , @Itogo INT OUTPUT AS SELECT @Itogo = COUNT (Code_book) FROM Books WHERE Pages>= @Count_pages AND Title_book LIKE @Title GO

Tâche 4. Créez cette procédure dans la section Procédures stockées de la base de données DB_Books à l'aide de l'utilitaire SQL Server Management Studio. Exécutez en utilisant le jeu de commandes :

Sql> Déclarez @q As int EXEC Count_Books_Itogo 100, "P%", @q sortie sélectionnez @q

Vérifiez le résultat.

Un exemple de création d'une procédure avec des paramètres d'entrée et RETURN :

CREATE PROCEDURE checkname @param INT AS IF (SELECT Name_author FROM auteurs WHERE Code_author = @param) = "Pouchkine A.S." RETOUR 1 AUTRE RETOUR 2

Tâche 5. Créez cette procédure dans la section Procédures stockées de la base de données DB_Books à l'aide de l'utilitaire SQL Server Management Studio. Exécutez-le à l'aide des commandes :

DECLARE @return_status INT EXEC @return_status = checkname 1 SELECT "Return Status" = @return_status

Un exemple de création d'une procédure sans paramètres pour augmenter de 2 fois la valeur d'un champ clé de la table Achats :

CREATE PROC update_proc AS UPDATE Achats SET Code_purchase = Code_purchase* 2

Tâche 6. Créez cette procédure dans la section Procédures stockées de la base de données DB_Books à l'aide de l'utilitaire SQL Server Management Studio. Exécutez-le en utilisant la commande

EXEC update_proc

Un exemple de procédure avec un paramètre d'entrée pour obtenir toutes les informations sur un auteur précis :

CREATE PROC select_author @k CHAR (30 ) AS SELECT * FROM Auteurs WHERE name_author= @k

Tâche 7.

EXEC select_author "Pouchkine A.S." ou select_author @k= "Pouchkine A.S." ou EXEC select_author @k= "Pouchkine A.S."

Un exemple de création d'une procédure avec un paramètre de saisie et une valeur par défaut pour augmenter la valeur d'un champ clé de la table Achats d'un nombre de fois spécifié (2 fois par défaut) :

CREATE PROC update_proc @p INT = 2 AS UPDATE Achats SET Code_purchase = Code_purchase * @p

La procédure ne renvoie aucune donnée.

Tâche 8. Créez cette procédure dans la section Procédures stockées de la base de données DB_Books à l'aide de l'utilitaire SQL Server Management Studio. Exécutez-le à l'aide des commandes :

EXEC update_proc 4 ou EXEC update_proc @p = 4 ou EXEC update_proc --la valeur par défaut sera utilisée.

Un exemple de création d'une procédure avec des paramètres d'entrée et de sortie. Créez une procédure pour déterminer le nombre de commandes terminées au cours d'une période spécifiée :

CREATE PROC count_purchases @d1 SMALLDATETIME, @d2 SMALLDATETIME, @c INT OUTPUT AS SELECT @c= COUNT (Code_purchase) FROM Purchases WHERE Date_order BETWEEN @d1 AND @d2 SET @c = ISNULL (@c, 0 )

Tâche 9. Créez cette procédure dans la section Procédures stockées de la base de données DB_Books à l'aide de l'utilitaire SQL Server Management Studio. Exécutez-le à l'aide des commandes :

DECLARE @c2 INT EXEC count_purchases '01-jun-2006', '01-jul-2006', @c2 OUTPUT SELECT @c2

Options pour les tâches travail de laboratoire №4

Dispositions générales. Dans SQL Server Management Studio, créez nouvelle page pour le code (le bouton « Créer une demande »). Rendre active par programme la base de données DB_Books créée à l’aide de l’instruction Use. Créez des procédures stockées à l'aide des instructions Create Procedure et définissez vous-même les noms des procédures. Chaque procédure exécutera une requête SQL qui a été exécutée dans le deuxième laboratoire. De plus, le code SQL des requêtes doit être modifié afin qu'elles puissent transmettre les valeurs des champs utilisés pour la recherche.

Par exemple, la tâche initiale et la demande dans le travail de laboratoire n°2 :

/*Sélectionner dans l'annuaire des fournisseurs (Table de livraison) les noms des sociétés, les numéros de téléphone et INN (champs Nom_entreprise, Téléphone et INN), dont le nom de la société (champ Nom_entreprise) est « OJSC MIR ».

SELECT Nom_entreprise, Téléphone, INN FROM Livraisons WHERE Nom_entreprise = "OJSC MIR"

*/ –Dans ce travail, la procédure suivante sera créée :

CREATE PROC select_name_company @comp CHAR (30 ) AS SELECT Name_company, Phone, INN FROM Livraisons WHERE Name_company = @comp

–Pour lancer la procédure, utilisez la commande :

EXEC select_name_company "JSC MIR"

Liste de tâches

Créez un nouveau programme dans SQL Server Management Studio. Rendre active par programme la base de données individuelle créée dans le travail de laboratoire n°1 à l'aide de l'instruction Use. Créez des procédures stockées à l'aide des instructions Create Procedure et définissez vous-même les noms des procédures. Chaque procédure exécutera une requête SQL, qui se présente sous la forme de tâches distinctes selon les options.

Option 1

1. Affichez une liste des salariés qui ont au moins un enfant.

2. Affichez une liste des enfants qui ont reçu des cadeaux pendant la période spécifiée.

3. Affichez une liste des parents qui ont des enfants mineurs.

4. Affichez des informations sur les cadeaux d'une valeur supérieure au nombre spécifié, triés par date.

Option 2

1. Affichez une liste des appareils avec le type spécifié.

2. Affichez le nombre d'appareils réparés et le coût total des réparations effectuées par le technicien spécifié.

3. Affichez une liste des propriétaires d'appareils et le nombre de leurs demandes, triés par nombre de demandes par ordre décroissant.

4. Afficher des informations sur les artisans avec un rang supérieur au nombre spécifié ou avec une date d'embauche inférieure à la date spécifiée.

Option 3

2. Affichez une liste des codes de vente qui ont vendu des fleurs pour un montant supérieur au nombre spécifié.

3. Affichez la date de vente, le montant, le vendeur et la fleur selon le code de vente spécifié.

4. Affichez une liste de fleurs et la variété des fleurs dont la hauteur est supérieure au nombre ou à la floraison spécifié.

Option 4

1. Afficher une liste de médicaments avec l'indication d'utilisation spécifiée.

2. Affichez une liste des dates de livraison pour lesquelles un nombre supérieur à la quantité spécifiée du médicament du même nom a été vendu.

3. Affichez la date de livraison, le montant, le nom complet du responsable du fournisseur et le nom du médicament par code de réception supérieur au numéro spécifié.

Option 5

2. Affichez une liste des équipements mis hors service pour la raison spécifiée.

3. Afficher la date de réception, le nom de l'équipement, le nom complet de la personne responsable et la date de radiation pour l'équipement radié au cours de la période spécifiée.

4. Afficher une liste d'équipements avec un type spécifié ou avec une date de réception supérieure à une certaine valeur

Option 6

1. Affichez une liste de plats dont le poids est supérieur au nombre spécifié.

2. Affichez une liste de produits dont les noms contiennent le fragment de mot spécifié.

3. Affichez le volume du produit, le nom du plat, le nom du produit avec le code du plat de la valeur initiale spécifiée à une certaine valeur finale.

4. Afficher l'ordre de préparation d'un plat et le nom d'un plat avec la quantité de glucides supérieure à une certaine valeur ou la quantité de calories supérieure à une valeur spécifiée.

Option 7

1. Affichez une liste des employés occupant le poste spécifié.

3. Affichez la date d'enregistrement, le type de document, le nom complet du registraire et le nom de l'organisation pour les documents enregistrés pendant la période spécifiée.

4. Afficher une liste de documents enregistrés avec un type de document spécifique ou avec une date d'enregistrement supérieure à la valeur spécifiée.

Option 8

1. Affichez une liste des employés avec le motif de licenciement spécifié.

3. Afficher la date d'inscription, le motif du licenciement, le nom complet du salarié pour les documents enregistrés pendant la période indiquée.

Option 9

1. Affichez une liste des employés qui ont pris un congé du type spécifié.

2. Affichez une liste de documents avec une date d'enregistrement dans la période spécifiée.

3. Afficher la date d'inscription, le type de congé, le nom complet du salarié pour les documents enregistrés pendant la période indiquée.

4. Affichez une liste de documents enregistrés avec un code de document dans la plage spécifiée.

Option 10

1. Affichez une liste des employés occupant le poste spécifié.

2. Affichez une liste de documents dont le contenu contient le fragment de mot spécifié.

3. Affichez la date d'enregistrement, le type de document, le nom complet de l'expéditeur et le nom de l'organisation pour les documents enregistrés pendant la période spécifiée.

4. Affichez une liste de documents enregistrés avec le type de document spécifié ou avec un code de document inférieur à une certaine valeur.

Option 11

1. Affichez une liste des employés affectés au poste spécifié.

2. Affichez une liste de documents avec une date d'enregistrement dans la période spécifiée.

3. Afficher la date d'inscription, le poste, le nom complet du salarié pour les documents enregistrés pendant la période spécifiée.

4. Affichez une liste de documents enregistrés avec un code de document dans la plage spécifiée.

Option 12

3. Afficher une liste des personnes ayant loué du matériel et le nombre de leurs demandes, triées par nombre de demandes par ordre décroissant.

Option 13

1. Affichez une liste des équipements avec le type spécifié. 2. Affichez une liste des équipements qui ont été radiés par un employé spécifique.

3. Affichez la quantité d'équipements mis hors service, regroupés par type d'équipement.

4. Affichez des informations sur les employés dont la date d'embauche est supérieure à une certaine date.

Option 14

1. Imprimez une liste de fleurs avec le type de feuille spécifié.

2. Affichez une liste des codes de reçu pour lesquels des fleurs ont été vendues pour des montants supérieurs à une certaine valeur.

3. Affichez la date de réception, le montant, le nom du fournisseur et les couleurs par un code fournisseur spécifique.

4. Affichez une liste de fleurs et la variété des fleurs dont la hauteur est supérieure à un certain nombre ou qui fleurissent.

Option 15

1. Affichez une liste des clients qui se sont enregistrés dans les chambres pendant la période spécifiée.

2. Affichez le montant total des paiements des chambres pour chaque client.

3. Affichez la date d'arrivée, le type de chambre, le nom complet des clients inscrits pendant la période spécifiée.

4. Affichez une liste des clients enregistrés dans les salles d'un certain type.

Option 16

1. Affichez une liste des équipements avec le type spécifié.

2. Affichez une liste des équipements loués par un client spécifique.

3. Afficher une liste des personnes ayant loué du matériel et le nombre de leurs demandes, triées par nombre de demandes par ordre décroissant.

4. Affichez les informations sur les clients triés par adresse.

Option 17

1. Affichez une liste d'objets de valeur avec un prix d'achat supérieur à une certaine valeur ou une période de garantie supérieure à un nombre spécifié.

2. Affichez une liste d'emplacements de biens matériels dont les noms contiennent le mot spécifié.

3. Affichez la somme de la valeur des valeurs avec un code dans la plage spécifiée.

4. Affichez une liste des personnes financièrement responsables avec la date d'emploi dans la plage spécifiée.

Option 18

1. Afficher une liste des réparations effectuées par un technicien spécifique.

2. Affichez une liste des étapes de travail incluses dans l'ouvrage dont le titre contient le mot spécifié.

3. Affichez la somme du coût des étapes de travail de réparation pour les travaux avec un code dans la plage spécifiée.

4. Affichez une liste de contremaîtres avec la date d'embauche dans la plage spécifiée.

Option 19

1. Afficher une liste de médicaments avec une indication précise.

2. Affichez une liste des numéros de reçus pour lesquels plus d'un certain nombre de médicaments ont été vendus.

3. Affichez la date de vente, le montant, le nom du caissier et le médicament sur le reçu avec le numéro spécifié.

4. Afficher une liste de médicaments et d'unités de mesure pour les médicaments dont la quantité dans l'emballage est supérieure au nombre spécifié ou un code de médicament inférieur à une certaine valeur.

Option 20

1. Affichez une liste des employés occupant le poste spécifié.

2. Affichez une liste de documents dont le contenu contient le fragment de mot spécifié.

3. Afficher la date d'enregistrement, le type de document, le nom complet de l'exécuteur testamentaire et le fait d'exécution pour les documents enregistrés pendant la période spécifiée.

4. Affichez une liste de documents enregistrés avec le type de document ou le code de document spécifié dans une certaine plage.