Création de procédures stockées sur le serveur Microsoft SQL. Procédures stockées en SQL

Procédures stockées

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

Exemple de procédure stockée simple

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

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

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

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

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

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

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

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

Délimiteurs dans les procédures stockées

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

DÉFINIR LA DURÉE

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

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

Appel d'une procédure stockée

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

SÉLECTIONNER *
DE Sp_add(181.35, 23.09)

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

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

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

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

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

FIN

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

DÉCLARER UNE VARIABLE ;

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

DÉCLARER LA VARIABLE Mylnt INTEGER ;

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

procName="Un nom";

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

Boucles et instructions de branchement

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

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

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

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

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

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

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

DÉCLARE VARIABLE avg_price DOUBLE PRÉCISION ;

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

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

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

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

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

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

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

SUSPENDRE;
SORTIE;

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

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

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

Procédures stockées récursives

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

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

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

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

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

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

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

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

Le tableau fait référence à lui-même et au donné clé externe garde un oeil dessus. afin que le tableau ne contienne pas de références à des parents inexistants et empêche également les tentatives de suppression de catégories de produits ayant des enfants.
Entrons les données suivantes dans notre tableau :

ID_BON

1
2
3
4
5
6
7
8
9
10
11
12

ID_PARENT_GOOD

0
1
1
2
2
4
4
4
5
5
10
10

RÉPUTATION

MARCHANDISES
appareils électroménagers
Ordinateurs et composants
Réfrigérateurs
Machines à laver
Trois chambres
Double chambre
Chambre unique
Verticale
Frontale
Étroit
Classique

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

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

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

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

Conclusion

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

Le concept de procédures stockées est défini. Fournit des exemples de création, de modification et d’utilisation de procédures stockées avec des paramètres. La définition des paramètres d'entrée et de sortie est donnée. Des exemples de création et d'appel de procédures stockées sont fournis.

Le concept de procédure stockée

Procédures stockées sont des groupes de personnes interconnectées Instructions SQL, dont l’utilisation rend le travail du programmeur plus facile et plus flexible, puisque procédure stockée est souvent beaucoup plus simple qu'une séquence d'instructions SQL individuelles. Les procédures stockées sont un ensemble de commandes composées d'une ou plusieurs instructions ou fonctions SQL et stockées sous une forme compilée dans une base de données. Exécution dans la base de données procédures stockées Au lieu d'instructions SQL individuelles, l'utilisateur bénéficie des avantages suivants :

les opérateurs nécessaires sont déjà contenus dans la base de données ;
ils ont tous passé le stade analyse et sont au format exécutable ; avant exécuter une procédure stockée SQL Server génère un plan d'exécution pour celui-ci, effectue son optimisation et sa compilation ;
procédures stockées soutien programmation modulaire , car ils vous permettent de diviser les tâches volumineuses en parties indépendantes, plus petites et plus faciles à gérer ;
procédures stockées peut causer d'autres procédures stockées et fonctions ;
procédures stockées peut être appelé à partir d'autres types de programmes d'application ;
généralement, procédures stockées s'exécuter plus rapidement qu'une séquence d'instructions individuelles ;
procédures stockées plus simples à utiliser : elles peuvent être constituées de dizaines ou de centaines de commandes, mais pour les exécuter il suffit de préciser le nom de celle souhaitée procédure stockée. Cela permet de réduire la taille de la requête envoyée du client vers le serveur, et donc la charge sur le réseau.

Le stockage des procédures au même endroit où elles sont exécutées réduit la quantité de données transférées sur le réseau et améliore les performances globales du système. Application procédures stockées simplifie l'entretien systèmes logiciels et y apporter des modifications. Généralement, toutes les contraintes d'intégrité sous forme de règles et d'algorithmes de traitement des données sont implémentées sur le serveur de base de données et sont disponibles pour l'application finale sous la forme d'un ensemble. procédures stockées, qui représentent l'interface de traitement des données. Pour garantir l'intégrité des données, ainsi qu'à des fins de sécurité, l'application ne reçoit généralement pas d'accès direct aux données - tout travail avec celles-ci est effectué en appelant certains procédures stockées.

Cette approche rend très simple la modification des algorithmes de traitement des données, immédiatement disponibles pour tous les utilisateurs du réseau, et offre la possibilité d'étendre le système sans apporter de modifications à l'application elle-même : il suffit de changer procédure stockée sur le serveur de base de données. Le développeur n'a pas besoin de recompiler l'application, d'en créer des copies ou de demander aux utilisateurs de travailler avec la nouvelle version. Les utilisateurs peuvent même ne pas savoir que des modifications ont été apportées au système.

Procédures stockées exister indépendamment des tables ou de tout autre objet de base de données. Ils sont appelés par le programme client, un autre procédure stockée ou déclencheur. Le développeur peut gérer les droits d'accès à procédure stockée, autorisant ou interdisant son exécution. Changer le code procédure stockée autorisé uniquement par son propriétaire ou un membre d'un rôle de base de données fixe. Si nécessaire, vous pouvez en transférer la propriété d'un utilisateur à un autre.

Procédures stockées dans l'environnement MS SQL Server

Lorsqu'ils travaillent avec SQL Server, les utilisateurs peuvent créer leurs propres procédures qui implémentent certaines actions. Procédures stockées sont des objets de base de données à part entière, et donc chacun d'eux est stocké dans une base de données spécifique. Appel direct procédure stockée n’est possible que si elle est réalisée dans le contexte de la base de données où se trouve la procédure.

Types de procédures stockées

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

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

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

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

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

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

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

Regardons les paramètres de cette commande.

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

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

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

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

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

Le mot clé VARYING est utilisé conjointement avec le paramètre OUTPUT, qui est de type CURSOR. Il détermine que paramètre de sortie il y aura un ensemble de résultats.

Le mot-clé DEFAULT représente la valeur que le correspondant paramètre par défaut. 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 procédure stockée chaque fois qu'elle appelle.

Le paramètre FOR REPLICATION est requis lors de la réplication des données et de l'activation du fichier créé. procédure stockée comme article à publier.

Le mot-clé ENCRYPTION demande au serveur de chiffrer le code procédure stockée, qui peut fournir une protection contre l'utilisation d'algorithmes propriétaires qui mettent en œuvre le travail procédure stockée.

Le mot-clé AS est placé au début du corps lui-même procédure stockée, c'est à dire. un ensemble de commandes SQL à l'aide desquelles telle ou telle action sera mise en œuvre. Dans le corps de la procédure, presque toutes les commandes SQL peuvent être utilisées, les transactions peuvent être déclarées, les verrous peuvent être définis et d'autres peuvent être appelés. procédures stockées. Sortir de procédure stockée peut être effectué à l'aide de la commande RETURN.

Suppression d'une procédure stockée effectué par la commande :

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

Exécuter une procédure stockée

Pour exécuter une procédure stockée La commande utilisée est :

[[ EXEC [ UTE] nom_procédure [;numéro] [[@parameter_name=](value | @variable_name) |][,...n]

Si l'appel procédure stockée n'est pas la seule commande du package, la présence de 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 l'appel à une procédure. créer une 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, il sera utilisé valeur par défaut. Naturellement, le mot spécifié DEFAULT n'est autorisé que pour les paramètres pour lesquels il est défini valeur par défaut.

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éer une procédure. Attribuer au paramètre valeur par défaut, vous ne pouvez pas simplement l'ignorer lors de la mise en vente. Si vous souhaitez omettre les paramètres pour lesquels il est défini valeur par défaut, il suffit de spécifier explicitement les noms des paramètres lors de l'appel 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.

Exemple 12.1. Procédure sans paramètres. Développer une procédure pour obtenir les noms et les coûts des marchandises achetées par Ivanov.

CREATE PROC my_proc1 AS SELECT Product.Name, Product.Price*Transaction.Quantity AS Cost, Customer.Last Name FROM Customer INNER JOIN (Product INNER JOIN Transaction ON Product.ProductCode=Transaction.ProductCode) ON Customer.CustomerCode=Transaction.CustomerCode WHERE Client .Nom='Ivanov' Exemple 12.1. Procédure d'obtention des noms et valeurs des biens achetés par Ivanov.