Qu'est-ce qu'une procédure stockée sql. Données initiales pour exemples. Solutions Collecting From Web de "Quelle est la différence entre une procédure stockée et une vue ?"
procédure stockée - un objet de base de données, qui est un ensemble d'instructions SQL qui est compilé une fois et stocké sur le serveur. Les procédures stockées sont très similaires aux procédures ordinaires dans les langages de haut niveau, elles peuvent avoir des paramètres d'entrée et de sortie et des variables locales, elles peuvent effectuer des calculs numériques et des opérations sur des données de caractères, dont les résultats peuvent être affectés à des variables et des paramètres. Les procédures stockées peuvent effectuer des opérations de base de données standard (à la fois DDL et DML). De plus, les boucles et les branches sont possibles dans les procédures stockées, c'est-à-dire qu'elles peuvent utiliser des instructions pour contrôler le processus d'exécution.
Les procédures stockées sont similaires aux fonctions définies par l'utilisateur (UDF). La principale différence est que les fonctions définies par l'utilisateur peuvent être utilisées comme n'importe quelle autre expression dans une requête SQL, tandis que les procédures stockées doivent être appelées à l'aide de la fonction CALL :
Procédure d'APPEL(…)
EXÉCUTER la procédure(...)
Les procédures stockées peuvent renvoyer des ensembles de résultats, c'est-à-dire les résultats d'une requête SELECT. Ces ensembles de résultats peuvent être traités à l'aide de curseurs, par d'autres procédures stockées renvoyant un pointeur d'ensemble de résultats ou par des applications. Les procédures stockées peuvent également contenir des variables déclarées pour la gestion des données et des curseurs, ce qui vous permet de parcourir plusieurs lignes dans une table. Le standard SQL fournit IF, LOOP, REPEAT, CASE et bien d'autres expressions avec lesquelles travailler. Les procédures stockées peuvent accepter des variables, renvoyer des résultats ou modifier des variables et les renvoyer, selon l'endroit où la variable est déclarée.
L'implémentation des procédures stockées varie d'un SGBD à l'autre. La plupart des principaux fournisseurs de bases de données les prennent en charge sous une forme ou une autre. Selon le SGBD, les procédures stockées peuvent être implémentées dans différents langages de programmation tels que SQL, Java, C ou C++. Les procédures stockées écrites en non-SQL peuvent ou non exécuter des requêtes SQL par elles-mêmes.
Par
Partage de la logique avec d'autres applications. Les procédures stockées encapsulent les fonctionnalités ; cela garantit l'accès aux données et la connectivité de gestion entre les différentes applications.
Isolez les utilisateurs des tables de base de données. Cela vous permet de donner accès aux procédures stockées, mais pas aux données de la table elle-même.
Fournit un mécanisme de protection. Selon le point précédent, si vous ne pouvez accéder aux données que via des procédures stockées, personne d'autre ne peut effacer vos données via la commande SQL DELETE.
Amélioration des performances grâce à la réduction du trafic réseau. Avec les procédures stockées, plusieurs requêtes peuvent être combinées.
Contre
Augmentation de la charge sur le serveur de base de données en raison du fait que la majeure partie du travail est effectuée côté serveur et moins côté client.
Vous devez apprendre beaucoup. Vous devrez apprendre la syntaxe des expressions MySQL pour écrire vos procédures stockées.
Vous dupliquez votre logique d'application à deux endroits : code serveur et code pour les procédures stockées, compliquant ainsi le processus de manipulation des données.
Migration d'un SGBD à un autre (DB2, serveur SQL etc.) peut entraîner des problèmes.
Objectif et avantages des procédures stockées
Les procédures stockées améliorent les performances, améliorent les options de programmation et prennent en charge les fonctions de sécurité des données.
Au lieu de stocker une requête fréquemment utilisée, les clients peuvent se référer à la procédure stockée appropriée. Lorsqu'une procédure stockée est appelée, son contenu est immédiatement traité par le serveur.
En plus de l'exécution réelle de la requête, les procédures stockées vous permettent également d'effectuer des calculs et de manipuler des données - modifier, supprimer, exécuter des instructions DDL (pas dans tous les SGBD !) et appeler d'autres procédures stockées, effectuer une logique transactionnelle complexe. Une seule instruction vous permet d'appeler un script complexe contenu dans une procédure stockée, ce qui évite l'envoi de centaines de commandes sur le réseau et, en particulier, la nécessité de transférer de grandes quantités de données du client vers le serveur.
Dans la plupart des SGBD, la première fois qu'une procédure stockée est exécutée, elle est compilée (parsée et un plan d'accès aux données est généré). À l'avenir, son traitement est plus rapide. Oracle DBMS interprète le code procédural stocké dans le dictionnaire de données. À partir d'Oracle 10g, la soi-disant compilation native (compilation native) du code de la procédure stockée en C puis dans le code machine de la machine cible est prise en charge, après quoi, lorsque la procédure stockée est appelée, son code objet compilé est directement exécuté.
Possibilités de programmation
Une fois que vous avez créé une procédure stockée, vous pouvez l'appeler à tout moment, ce qui offre une modularité et encourage la réutilisation du code. Ce dernier facilite la maintenance de la base de données, car elle est isolée des règles métier changeantes. Vous pouvez modifier une procédure stockée pour se conformer aux nouvelles règles à tout moment. Après cela, toutes les applications l'utilisant se conformeront automatiquement aux nouvelles règles métier sans modification directe.
Sécurité
L'utilisation de procédures stockées vous permet de restreindre ou d'exclure complètement l'accès direct des utilisateurs aux tables de la base de données, en ne laissant aux utilisateurs que les autorisations d'exécuter des procédures stockées qui fournissent un accès indirect et strictement réglementé aux données. De plus, certains SGBD prennent en charge le chiffrement de texte (encapsulage) d'une procédure stockée.
Ces fonctionnalités de sécurité vous permettent d'isoler la structure de la base de données de l'utilisateur, ce qui garantit l'intégrité et la fiabilité de la base de données.
Les actions telles que "l'injection SQL" sont moins probables car des procédures stockées bien écrites valident en outre les paramètres d'entrée avant de transmettre la requête au SGBD.
Implémentation de procédures stockées
Les procédures stockées sont généralement créées à l'aide du langage SQL ou de son implémentation spécifique dans le SGBD choisi. Par exemple, à ces fins, le SGBD Microsoft SQL Server a le langage Transact-SQL, Oracle a PL/SQL, InterBase et Firebird ont PSQL, PostgreSQL a PL/pgSQL, PL/Tcl, PL/Perl, PL/Python, IBM DB2 - SQL/PL (Anglais), dans Informix - SPL. MySQL suit de très près la norme SQL:2003 et son langage est similaire à SQL/PL.
Dans certains SGBD, il est possible d'utiliser des procédures stockées écrites dans n'importe quel langage de programmation capable de créer des fichiers exécutables indépendants, tels que C++ ou Delphi. Dans la terminologie Microsoft SQL Server, ces procédures sont appelées procédures stockées étendues et sont simplement des fonctions contenues dans une DLL Win32. Et, par exemple, dans Interbase et Firebird pour les fonctions appelées depuis DLL / SO, un autre nom est défini - UDF (User Defined Function). Dans MS SQL 2005, il est devenu possible d'écrire des procédures stockées dans n'importe quel langage .NET, et les procédures stockées étendues devraient être abandonnées à l'avenir. Le SGBD Oracle, à son tour, permet d'écrire des procédures stockées dans Langage Java. Dans IBM DB2, l'écriture de procédures stockées et de fonctions dans des langages de programmation conventionnels est la méthode traditionnelle, prise en charge depuis le début, et l'extension procédurale SQL n'a été ajoutée à ce SGBD que tardivement, après son inclusion dans la norme ANSI. Informix prend également en charge les procédures Java et C.
Dans le SGBD Oracle, les procédures stockées peuvent être combinées dans ce que l'on appelle des packages. Le package se compose de deux parties - la spécification (spécification du package en anglais), qui spécifie la définition de la procédure stockée, et le corps (corps du package en anglais), où se trouve son implémentation. Ainsi, Oracle vous permet de séparer l'interface du code du programme de son implémentation.
Dans IBM DB2, les procédures stockées peuvent être combinées en modules.
Syntaxe
CRÉER PROCÉDURE `p2`()
DÉFINISSEUR DE SÉCURITÉ SQL
COMMENTAIRE "Une procédure"
SÉLECTIONNEZ "Bonjour le monde !" ;
La première partie du code crée une procédure stockée. Suivant - contient des paramètres facultatifs. Vient ensuite le nom et enfin le corps de la procédure elle-même.
4 caractéristiques d'une procédure stockée :
Langage : à des fins de portabilité, la valeur par défaut est SQL.
Déterministe : si la procédure renvoie tout le temps le même résultat et prend les mêmes paramètres d'entrée. Ceci est pour le processus de réplication et d'enregistrement. La valeur par défaut est NON DETERMINISTE.
Sécurité SQL : lors de l'appel, les droits de l'utilisateur sont vérifiés. INVOKER est l'utilisateur appelant la procédure stockée. DEFINER est le "créateur" de la procédure. La valeur par défaut est DEFINER.
Commentaire : à des fins de documentation, la valeur par défaut est ""
Appel d'une procédure stockée
CALL nom_procédure_stockée (param1, param2, ....)
CALL procedure1(10 , "string parameter" , @parameter_var);
Modification d'une procédure stockée
MySQL a une instruction ALTER PROCEDURE pour modifier les procédures, mais elle ne convient que pour modifier certaines caractéristiques. Si vous devez modifier les paramètres ou le corps de la procédure, vous devez le supprimer et le recréer.
Suppressionstockéeprocédures
ABANDONNER LA PROCÉDURE SI EXISTE p2 ;
Il s'agit d'une simple commande. L'instruction IF EXISTS détecte une erreur si aucune procédure de ce type n'existe.
Choix
CREATE PROCEDURE proc1() : liste de paramètres vide
CREATE PROCEDURE proc1 (IN varname DATA-TYPE) : un paramètre d'entrée. Le mot IN est facultatif car les paramètres par défaut sont IN (entrant).
CREATE PROCEDURE proc1 (OUT varname DATA-TYPE) : un paramètre de retour.
CREATE PROCEDURE proc1 (INOUT varname DATA-TYPE) : un paramètre, à la fois en entrée et en sortie.
La syntaxe pour déclarer une variable ressemble à ceci :
DECLARE varname DATA-TYPE DEFAULT valeur par défaut ;
Lorsqu'ils travaillent avec SQL Server, les utilisateurs peuvent créer leurs propres procédures qui implémentent certaines actions. Les procédures stockées sont des objets de base de données à part entière et, par conséquent, chacune d'entre elles est stockée dans une base de données spécifique. L'appel direct d'une procédure stockée n'est possible que s'il est effectué dans le contexte de la base de données où réside la procédure.
Types de procédures stockées
SQL Server possède plusieurs types de procédures stockées.
Les procédures stockées système sont conçues pour effectuer diverses actions administratives. Presque toutes les actions 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 des tables système, ce qui, en fin de compte, revient à modifier, ajouter, supprimer et récupérer des données à partir des tables système des bases de données utilisateur et système. Les procédures stockées système sont préfixées par sp_, stockées dans la base de données système et peuvent être appelées 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 complet. Par conséquent, 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 local et global. Les procédures stockées temporaires locales ne peuvent être appelées qu'à partir de la connexion sur laquelle elles ont été créées. Lors de la création d'une telle procédure, il faut lui donner un nom qui commence 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, redémarre ou arrête le serveur. Les procédures stockées temporaires globales sont disponibles pour toute connexion sur un serveur qui a la même procédure. Pour le définir, il suffit de lui donner un nom qui commence par les caractères ##. Ces procédures sont supprimées lors du redémarrage ou de l'arrêt du serveur, ou lors de la fermeture de la connexion dans le cadre de laquelle elles ont été créées.
déclencheurs
déclencheurs sont l'une des variétés de procédures stockées. Leur exécution se produit lorsqu'une instruction de langage de manipulation de données (DML) est exécutée sur la table. Les déclencheurs sont utilisés pour vérifier l'intégrité des données et également pour annuler les transactions.
Gâchette est une procédure SQL compilée dont l'exécution est déterminée par la survenance de certains événements au sein d'une base de données relationnelle. L'utilisation de déclencheurs est, pour la plupart, très pratique pour les utilisateurs de bases de données. Pourtant, leur utilisation est souvent associée à des coûts de ressources supplémentaires pour les opérations d'E/S. Lorsque les mêmes résultats (avec beaucoup moins de surcharge de ressources) peuvent être obtenus à l'aide de procédures stockées ou d'applications, les déclencheurs sont inappropriés.
déclencheurs est un outil de serveur SQL spécial utilisé pour maintenir l'intégrité des données dans une base de données. Les contraintes d'intégrité, les règles et les valeurs par défaut peuvent ne pas toujours fournir le niveau de fonctionnalité souhaité. Il est souvent nécessaire de mettre en œuvre des algorithmes complexes de validation des données pour s'assurer qu'elles sont valides et réelles. De plus, il est parfois nécessaire de suivre les modifications apportées aux valeurs des tables afin de modifier les données associées au besoin. Les déclencheurs peuvent être considérés comme une sorte de filtres qui entrent en action une fois que toutes les opérations ont été effectuées conformément aux règles, aux valeurs par défaut, etc.
Gâchette est un type spécial de procédure stockée qui est exécutée automatiquement par le serveur lorsqu'il essaie de modifier des données dans les tables auxquelles les déclencheurs sont associés. Chaque Gâchette est lié à une table spécifique. Toutes les modifications de données qu'il apporte sont traitées comme une seule transaction. Si une erreur ou une violation de l'intégrité des données est détectée, la transaction est annulée. Par conséquent, les modifications sont interdites. Toutes les modifications déjà apportées par le déclencheur sont également annulées.
Crée gâchette uniquement le propriétaire de la base de données. Cette restriction vous permet d'éviter des changements accidentels dans la structure des tables, les façons de connecter d'autres objets avec eux, etc.
Gâchette est un outil très utile et en même temps dangereux. Ainsi, avec la mauvaise logique de son travail, vous pouvez facilement détruire toute la base de données, donc les déclencheurs doivent être débogués très soigneusement.
Contrairement à un sous-programme normal, gâchette est exécuté implicitement chaque fois que l'événement déclencheur se produit, et il n'a pas d'arguments. Son activation est parfois appelée déclenchement d'un déclencheur. Les déclencheurs atteignent les objectifs suivants :
valider les données saisies et mettre en place des contraintes complexes d'intégrité des données difficiles, voire impossibles, à maintenir avec les contraintes d'intégrité définies sur la table ;
émettre des avertissements qui vous rappellent la nécessité d'effectuer certaines actions lors de la mise à jour d'une table implémentée d'une certaine manière ;
accumulation d'informations d'audit en fixant des informations sur les modifications apportées et les personnes qui les ont effectuées ;
prise en charge de la réplication.
Le format de base de la commande CREATE TRIGGER est illustré ci-dessous :
<Определение_триггера>::=
CREATE TRIGGER nom_trigger
AVANT | APRÈS<триггерное_событие>
SUR<имя_таблицы>
<список_старых_или_новых_псевдонимов>]
<тело_триггера>
Les événements déclencheurs consistent à insérer, supprimer et mettre à jour des lignes dans une table. Dans ce dernier cas, des noms de colonne de table spécifiques peuvent être spécifiés pour l'événement déclencheur. L'heure de début d'un déclencheur est définie à l'aide des mots-clés BEFORE ( Gâchette se déclenche avant l'exécution de ses événements associés) ou APRÈS (après leur exécution).
Les actions effectuées par le déclencheur sont définies pour chaque ligne (FOR EACH ROW) couverte par cet événement, ou une seule fois pour chaque événement (FOR EACH STATEMENT).
Des déclencheurs mal écrits peuvent entraîner de graves problèmes, tels que l'apparition de verrous "morts". Les déclencheurs peuvent bloquer de nombreuses ressources pendant une longue période, vous devez donc faire attention à Attention particulière pour minimiser les conflits d'accès.
Gâchette peuvent être créés uniquement dans la base de données actuelle, mais d'autres bases de données sont accessibles à l'intérieur du déclencheur, y compris celles situées sur un serveur distant.
1. Incluez dans vos procédures la ligne - SET NOCOUNT ON : Avec chaque instruction DML, le serveur SQL nous renvoie soigneusement un message contenant le nombre d'enregistrements traités. Cette information peut nous être utile lors du débogage du code, mais après cela, il sera complètement inutile. En écrivant SET NOCOUNT ON, nous désactivons cette fonctionnalité. Pour les procédures stockées contenant plusieurs expressions ou\et boucles, cette action peut améliorer considérablement les performances, car la quantité de trafic sera considérablement réduite.
CREATE PROC dbo.ProcName
COMME
SET NOCOUNT ON ;
--Code de procédure ici
SELECT colonne1 DE dbo.TblTable1
--Passer SET NOCOUNT à l'état initial
DÉSACTIVER NOCOUNT ;
ALLER
2. 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ù il doit aller et quoi prendre. Avec un grand nombre de bases de données, de tables et de procédures stockées, cela peut considérablement économiser notre temps et nos nerfs.
SELECT * FROM dbo.MyTable --C'est une bonne façon de le faire
-- À la place de
SELECT * FROM MyTable --C'est une mauvaise chose à faire
--Appel de procédure
EXEC dbo.MyProc -- Bon à nouveau
--À la place de
EXEC MyProc --Mauvais !
3. N'utilisez pas le préfixe "sp_" dans le nom de vos procédures stockées : Si notre nom de procédure commence par "sp_", SQL Server cherchera 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 retrouve dans sa base de données.
4. Utilisez IF EXISTS (SELECT 1) au lieu de IF EXISTS (SELECT *): Pour vérifier si un enregistrement existe dans une autre table, nous utilisons l'instruction IF EXISTS. Cette expression renvoie true si au moins une valeur est renvoyée à partir de l'expression interne, peu importe "1", toutes les colonnes ou une table. Les données renvoyées, en principe, ne sont en aucun cas utilisées. Ainsi, pour compresser le trafic lors de la transmission des données, il est plus logique d'utiliser "1", comme indiqué ci-dessous :
IF EXISTS (SELECT 1 FROM sysobjects
WHERE nom = "MaTable" AND type = "U")
5. Utilisez TRY-Catch pour détecter les erreurs : Avant le serveur 2005, après chaque demande, un grand nombre de vérifications d'erreurs étaient écrites dans la procédure. Plus de code consomme toujours plus de ressources et plus de temps. Depuis 2005, SQL Server est apparu plus correct et moyen pratique solutions à ce problème :
COMMENCER ESSAYER
--le code
FIN ESSAYER
COMMENCER À ATTRAPER
-- code de capture d'erreur
FIN DE CAPTURE
Conclusion
En gros, c'est tout ce que j'ai pour aujourd'hui. Je répète encore une fois qu'il ne s'agit ici que des techniques que j'ai personnellement utilisées dans ma pratique, et je peux témoigner de leur efficacité.
PS
Mon premier message, ne jugez pas strictement.
Les procédures stockées SQL sont un module de programme exécutable qui peut être stocké dans 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 programmes d'application pour obtenir de bonnes performances. Il n'est pas rare non plus que de tels objets soient appelés depuis d'autres scripts, ou même depuis une autre section.
Introduction
Beaucoup considèrent qu'ils sont similaires à différentes procédures (respectivement, à l'exception de MS SQL). C'est peut-être vrai. Ils ont des paramètres similaires, ils peuvent produire des valeurs similaires. De plus, dans certains cas, ils sont en contact. Par exemple, ils sont combinés avec des bases de données DDL et DML, ainsi que des fonctions utilisateur (nom de code UDF).
En fait, les procédures stockées SQL présentent un large éventail d'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 est survenu en 2005-2010, lorsqu'un programme de Microsoft a été lancé, appelé SQL Server Management Studio. Avec son aide, travailler avec des bases de données est devenu beaucoup plus facile, plus pratique et plus pratique. D'année en année, celui-ci gagnait en popularité parmi les programmeurs. Aujourd'hui, c'est un programme absolument familier qui, pour les utilisateurs qui "communiquent" avec des bases de données, est devenu comparable à Excel.
Lorsqu'une procédure est appelée, elle est instantanément traitée par le serveur lui-même sans processus inutiles ni intervention de l'utilisateur. Après cela, vous pouvez effectuer n'importe quelle suppression, exécution, modification. L'opérateur DDL est responsable de tout cela, qui effectue seul les actions les plus complexes pour le traitement des 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. Ceux-ci incluent, par exemple, PL / SQL d'Oracle, PSQL dans les systèmes InterBase et Firebird, ainsi que le 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. Cela est également nécessaire pour que ceux qui gèrent ces informations puissent protéger tous les objets contre l'accès non autorisé par des tiers et, par conséquent, 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 sélectionner le type de méthode à utiliser qui est le plus approprié, ce qui permet d'économiser du temps et des efforts. De plus, la procédure elle-même est traitée, ce qui évite le temps énorme consacré à l'échange entre le serveur et l'utilisateur. De plus, le module peut être reprogrammé et modifié dans le bon sens à tout moment. Il convient de noter en particulier la rapidité avec laquelle la procédure stockée SQL est lancée : ce processus est plus rapide que d'autres similaires, ce qui le rend pratique et polyvalent.
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 d'autres utilisateurs aux procédures 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 cliente est l'utilisation de paramètres et de valeurs de retour. Ce dernier n'a pas à transférer de données vers une 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 des paquets de données (mais, encore une fois, éventuellement) à l'application qui l'a appelée, en utilisant diverses méthodes, à l'aide de laquelle un appel à une procédure SQL stockée et un retour peuvent être effectués, par exemple :
Transmission de données à l'aide d'un paramètre de type Output ;
Transmission de données à l'aide d'une instruction de retour ;
Transmission de données à l'aide d'une instruction select.
Voyons maintenant à quoi ressemble ce processus de l'intérieur.
1. Créer 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 de base de données programmable, dans lequel la procédure de création est effectuée par l'opérateur. Les procédures stockées SQL utilisent un processus EXEC pour s'exécuter, qui contient le nom de l'objet lui-même.
Lorsqu'une procédure est créée, 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 que le ou les paramètres, c'est-à-dire le corps de la procédure, ont été écrits, certaines opérations nécessaires doivent être effectuées.
Le fait est que le corps peut contenir des variables locales, et ces variables sont également locales aux procédures. En d'autres termes, ils ne peuvent être considérés que dans le corps d'une procédure Microsoft SQL Server. Les procédures stockées sont considérées comme locales dans ce cas.
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 bonne option dans ce cas est de créer et d'exécuter une procédure avec le nom du schéma dans le classificateur.
Le corps de la procédure peut être de n'importe quel type, comme la création d'une table, l'insertion d'une ou plusieurs lignes de table, la définition du type et de la nature de la base de données, etc. Cependant, le corps de la procédure restreint l'exécution de certaines opérations qu'elle contient. Certaines des limitations importantes sont énuméré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 pas créer de déclencheurs.
2. Définir une variable dans le corps de la procédure
Vous pouvez rendre les variables locales au corps de la procédure, et elles seront 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 pouvez remarquer que plusieurs variables sont définies sur la même 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 où vous le souhaitez. Par exemple, la variable @NAME1 peut être déclarée vers la fin du corps de la procédure. Afin d'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é.
Souvent, les utilisateurs posent la question : "Comment attribuer plusieurs valeurs dans une instruction dans le corps d'une procédure ?" Bien. La question est intéressante, mais elle est beaucoup plus facile à faire que vous ne le pensez. Réponse : avec des paires comme "Select Var = Value". Vous pouvez utiliser ces paires en les séparant par une virgule.
Dans une variété d'exemples, les gens montrent la création d'une procédure stockée simple et son exécution. Cependant, une procédure peut prendre des paramètres tels que le processus appelant aura des valeurs proches de celle-ci (mais pas toujours). S'ils correspondent, les processus correspondants commencent à l'intérieur du corps. Par exemple, si vous créez une procédure qui prendra la ville et la région de l'appelant et renverra 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, par exemple Pubs, pour effectuer ce comptage d'auteurs. Pour obtenir ces bases de données, par exemple, Google télécharge le script SQL à partir de la page SQL2005.
Dans l'exemple précédent, la procédure prend deux paramètres, qui sont langue Anglaise conventionnellement s'appellera @State et @City. Le type de données correspond au type défini dans l'application. Le corps de la procédure a des variables internes @TotalAuthors (nombre total d'auteurs) et cette variable est utilisée pour afficher leur nombre. Vient ensuite la section de sélection des requêtes qui compte tout. Enfin, la valeur calculée est imprimée dans la fenêtre de sortie à l'aide d'une instruction d'impression.
Comment exécuter une procédure stockée en SQL
Il existe deux façons d'effectuer la procédure. La première 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. L'ordre est important dans cette façon de passer les paramètres. Cette méthode est appelée passage d'argument ordinal. Dans la deuxième méthode, les paramètres sont déjà directement affectés, auquel cas l'ordre n'a pas d'importance. Cette deuxième méthode est connue sous le nom de passage d'arguments nommés.
La procédure peut s'écarter quelque peu de la norme. Tout est comme dans l'exemple précédent, mais 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 surbrillance séparément. Les procédures stockées SQL sont transmises en tant que 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 pour le paramètre @City est transmise et le paramètre @State prend la valeur par défaut "CA". Les programmeurs expérimentés conseillent de placer toutes les variables par défaut vers la fin de la liste des paramètres. Sinon, l'exécution n'est pas possible, et il faut alors passer des arguments nommés, ce qui est plus long et plus compliqué.
4. Procédures stockées SQL Server : façons de revenir
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 :
Renvoyer la valeur d'une procédure stockée ;
Paramètre de sortie des procédures stockées ;
Sélectionnez l'une des procédures stockées.
4.1 Renvoyer des valeurs à partir de procédures stockées SQL
Dans cette technique, la procédure affecte une valeur à une variable locale et la renvoie. Une procédure peut également retourner directement une valeur constante. Dans l'exemple suivant, nous avons créé une procédure qui renvoie nombre total auteurs. Si vous comparez cette procédure avec les précédentes, vous pouvez voir que la valeur imprimable est inversée.
Voyons maintenant comment exécuter la procédure et imprimer la valeur renvoyée par celle-ci. 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 d'impression, vous pouvez utiliser une instruction Select telle que Select @RetValue ainsi que OutputValue.
4.2 Sortie de paramètre de procédure stockée SQL
La valeur de retour peut être utilisée pour renvoyer une seule variable, comme nous l'avons vu dans l'exemple précédent. L'utilisation du paramètre Output permet à la procédure d'envoyer une ou plusieurs valeurs variables à l'appelant. Le paramètre de sortie est désigné uniquement par ce mot-clé "Sortie" lors de la création de la procédure. Si un paramètre est spécifié comme paramètre de sortie, l'objet procédure doit lui affecter une valeur. Les procédures stockées SQL, dont des exemples sont présentés ci-dessous, sont ensuite renvoyées avec des informations récapitulatives.
Dans notre exemple, il y aura deux noms de sortie : @TotalAuthors et @TotalNoContract. Ils sont spécifié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 script précédent, deux variables sont déclarées pour voir les valeurs que les procédures stockées MS SQL Server définissent dans le paramètre de sortie. La procédure est alors effectuée en fournissant la valeur normale du paramètre "CA". Les paramètres suivants sont sorties et donc les variables déclarées sont passées dans l'ordre spécifié. Notez que lors du passage de variables, la sortie mot-cléégalement donné 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 une boîte de message.
4.3 Sélection d'une des procédures stockées SQL
Cette technique permet de retourner un ensemble de valeurs sous la forme d'une table de données (RecordSet) à la procédure stockée appelante. Dans cet exemple, la procédure stockée SQL 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étermine 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é. Une telle procédure ici ne renvoie toujours qu'un seul enregistrement ou aucun. Mais une procédure stockée n'a aucune restriction sur le renvoi de plusieurs enregistrements. Il n'est pas rare de voir des exemples où le retour de données à l'aide de paramètres sélectionnés impliquant des variables calculées se produit en fournissant plusieurs totaux.
Pour terminer
Une procédure stockée est une unité de programme assez sérieuse qui retourne ou passe et définit les variables nécessaires grâce à l'application cliente. Étant donné que la procédure stockée elle-même s'exécute sur le serveur, d'énormes quantités d'échanges de données entre le serveur et l'application cliente (pour certains calculs) peuvent être évitées. Cela réduit la charge sur Serveur SQL, qui, bien sûr, passe entre les mains de leurs détenteurs. L'une des sous-espèces sont les 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 grand, voire un grand nombre de nuances qui peuvent être utiles lors de l'apprentissage des procédures stockées, mais cela est plus nécessaire pour ceux qui envisagent de s'impliquer de près dans la programmation, y compris professionnellement.
Déclaration de procédure
CRÉER UNE PROCÉDURE [({IN|OUT|INOUT} [,…])]
[ENSEMBLE DE RÉSULTATS DYNAMIQUES ]
COMMENCER [ATOMIQUE]
FIN
Mots clés
. IN (Entrée) – paramètre d'entrée
. OUT (Sortie) – paramètre de sortie
. INOUT - entrée et sortie, ainsi qu'un champ (pas de paramètres)
. DYNAMIC RESULT SET indique que la procédure peut ouvrir le nombre spécifié de curseurs qui resteront ouverts après le retour de la procédure
Remarques
Il n'est pas recommandé d'utiliser de nombreux paramètres dans les procédures stockées (principalement de grands nombres et des chaînes de caractères) en raison de la congestion du réseau et de la pile. Dans la pratique, les dialectes existants de Transact-SQL, PL/SQL et Informix montrent des écarts significatifs par rapport à la norme, à la fois dans la déclaration et l'utilisation des paramètres, la déclaration des variables et l'invocation des sous-programmes. Microsoft recommande d'utiliser l'approximation suivante pour estimer la taille du cache des procédures stockées :
=(nombre maximum d'utilisateurs simultanés)*(plus grande taille de plan d'exécution)*1,25. La détermination de la taille du plan d'exécution en pages peut se faire à l'aide de la commande : DBCC MEMUSAGE.
Appel de procédure
Dans de nombreux SGBD existants, les procédures stockées sont appelées à l'aide de l'instruction :
EXÉCUTER LA PROCÉDURE [(][)]
Noter: Un appel aux procédures stockées peut être effectué à partir d'une application, d'une autre procédure stockée ou de manière interactive.
Exemple de déclaration de procédure
CREATE PROCEDURE Proc1 AS //déclaration d'une procédure
DECLARE Cur1 CURSOR FOR SELECT SName, City FROM SalesPeople WHERE Rating>200 //déclare un curseur
OPEN Cur1 //ouvre le curseur
FETCH NEXT FROM Cur1 //Lire les données du curseur
TANT QUE @@Fetch_Status=0
COMMENCER
RÉCUPÉRER LE SUIVANT DE Cur1
FIN
CLOSE Cur1 //ferme le curseur
DESAFFECTATION Cur1
EXECUTER Proc1 //exécuter la procédure
Polymorphisme
Deux sous-programmes portant le même nom peuvent être créés dans le même schéma si les paramètres des deux sous-programmes sont suffisamment différents l'un de l'autre pour pouvoir être distingués. Afin de distinguer deux routines portant le même nom dans le même schéma, chacune reçoit un nom alternatif et unique (nom spécifique). Un tel nom peut être explicitement spécifié lors de la définition du sous-programme. Lors de l'appel de sous-programmes portant plusieurs noms identiques, la détermination du sous-programme souhaité s'effectue en plusieurs étapes :
. Initialement, toutes les procédures avec le nom spécifié sont définies, et s'il n'y en a pas, alors toutes les fonctions avec le nom spécifié.
. Pour une analyse plus approfondie, il ne reste que les sous-programmes, par rapport auxquels utilisateur donné a le privilège EXECUTE.
. Pour eux, on sélectionne ceux dont le nombre de paramètres correspond au nombre d'arguments d'appel. Les types de données spécifiés des paramètres et leurs positions sont vérifiés.
. S'il reste plus d'un sous-programme, celui avec le nom qualificatif le plus court est sélectionné.
En pratique, Oracle supporte le polymorphisme pour les fonctions déclarées uniquement dans un package, [courriel protégé]- dans différents schémas, et la surcharge est interdite dans Sybase et MS SQL Server.
Suppression et modification des procédures
L'instruction suivante est utilisée pour supprimer une procédure :
Pour modifier une procédure, utilisez l'instruction :
PROCÉDURE DE MODIFICATION [([{IN|OUT|INOUT}])]
COMMENCER [ATOMIQUE]
FIN
Privilèges pour exécuter des procédures
SUBVENTION EXÉCUTER LE À |PUBLIQUE [AVEC OPTION DE SUBVENTION]
Procédures système
De nombreux SGBD (y compris SQL Server) disposent d'un certain ensemble de procédures stockées système intégrées que vous pouvez utiliser à vos propres fins.