Dernière mise à jour: 14.08.2017

Souvent, l'opération de données présente un ensemble d'instructions à effectuer dans une séquence spécifique. Par exemple, lors de l'achat de l'achat de marchandises, vous devez effectuer des données dans le tableau des commandes. Cependant, avant cela, il est nécessaire de vérifier, et s'il existe un produit acheté en stock. Peut-être qu'il sera nécessaire de vérifier un autre nombre de conditions supplémentaires. En fait, le processus d'achat de biens couvre plusieurs actions qui doivent être effectuées dans une certaine séquence. Et dans ce cas, il encapsulera de manière plus optimale toutes ces actions dans un objet - procédure stockée Procédure stockée).

C'est-à-dire que les procédures essentiellement stockées présentent un ensemble d'instructions effectuées en tant qu'oquet unique. Ainsi, les procédures stockées permettent de simplifier les opérations complètes et de les prendre en un seul objet. Le processus d'achat de biens changera, respectivement, il suffira de modifier le code de procédure. C'est-à-dire que la procédure simplifie également le code de gestion.

Les procédures stockées également vous permettent de limiter l'accès à des données dans des tableaux et de réduire ainsi la probabilité d'actions indésirables délibérées ou inconscientes pour ces données.

Et un aspect plus important - performance. Les procédures stockées sont généralement effectuées plus rapidement que les instructions SQL normales. Tous parce que le code de procédure est compilé une fois à sa première fois, puis persistait sous forme compilée.

Pour créer une procédure stockée, la procédure Créer ou créer la commande PROC est utilisée.

Ainsi, la procédure stockée a trois principales caractéristiques: Simplifier le code, la sécurité et les performances.

Par exemple, laissez la base de données avoir une table qui stocke des données sur les produits:

Créer des produits de table (ID int Intentialité clé principale, NomName NvarchaRar (30) Not Null, fabricant Nvarchar (20) Not Null, ProduitCompt Int par défaut 0, prix de prix non null);

Créez une procédure stockée pour extraire les données de ce tableau:

Utiliser des produitsDB; Go Créer Procédure ProduitsMariez comme Sélectionnez ProductName comme produit, fabricant, prix à partir de produits

Depuis que l'équipe de création de procédure devrait être appelée emballage séparé, puis après la commande Utiliser, qui définit la base de données actuelle, utilisez la commande Go pour déterminer le nouveau package.

Après le nom de la procédure devrait aller mot-clé COMME.

Pour séparer le corps de la procédure à partir du reste du script, le code de procédure est souvent placé au début de l'unité de fin:

Utiliser des produitsDB; Go Créer Procédure ProduitsMariez comme Début Sélectionnez ProduitName comme produit, fabricant, prix à partir de produits fin;

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

Et nous serons en mesure de gérer la procédure également via l'interface visuelle.

Procédure

Pour exécuter la procédure stockée, la commande Exec ou Execute est appelée:

Produits Execurary

Suppression de la procédure

La commande de procédure de chute s'applique à la procédure suivante:

Produits de procédure DROP.

1. Examiner les opérateurs décrivant les procédures stockées et les principes de transfert de leurs paramètres d'entrée et de sortie.
2. Examinez la procédure de création et de débogage des procédures stockées sur le serveur MS SQL Server 2000.
3. Développer cinq procédures stockées de base pour base académique Data "Bibliothèque".
4. Préparez un rapport sur le travail effectué sous forme électronique.

1. Informations générales sur les procédures stockées

Procédure stockée (procédure stockée) - Il s'agit d'un ensemble de commandes, stockées sur le serveur et exécutées dans son ensemble. Les procédures stockées sont un mécanisme avec lequel vous pouvez créer des sous-routines exécutées sur le serveur et contrôlé par ses processus. De telles sous-programmes peuvent être activées par l'application les appelant. De plus, ils peuvent être causés par des règles prenant en charge l'intégrité des données ou des déclencheurs.

Les procédures stockées peuvent renvoyer des valeurs. Dans la procédure, vous pouvez comparer les valeurs saisies par l'utilisateur avec les informations définies à l'avance dans le système. Les procédures stockées sont utilisées dans le fonctionnement de puissantes solutions matérielles SQL Server. Ils sont basés sur des bases de données et interagissent de près avec l'optimiseur SQL Server. Cela vous permet d'obtenir des performances élevées pendant le traitement des données.

Dans les procédures stockées, il est possible de transmettre des valeurs et d'obtenir d'eux les résultats du travail et non nécessairement à la table de travail. La procédure stockée peut calculer les résultats pendant le fonctionnement.

Les procédures stockées sont deux types: normal et Élargi. Les procédures stockées conventionnelles sont un ensemble de commandes sur Transact-SQL, tandis que les procédures stockées étendues sont présentées sous forme de bibliothèques dynamiques (DLL). Ces procédures, contrairement à l'habituel, ont le préfixe XP_. Le serveur dispose d'un ensemble standard de procédures étendues, mais les utilisateurs peuvent écrire leurs procédures dans n'importe quel langage de programmation. La principale chose est d'utiliser l'interface de programmation Sql Serveur ouvert Services de données API. Les procédures stockées étendues ne peuvent être que dans la base de données principale.

Les procédures stockées conventionnelles peuvent également être divisées en deux types: systémique et personnalisé. Procédures système - c'est procédures standardsServir le fonctionnement du serveur; Custom - Toutes les procédures créées par l'utilisateur.

1.1. Avantages des procédures stockées

Dans le cas le plus général, les procédures stockées ont les avantages suivants:

• Haute performance. C'est le résultat de l'emplacement des procédures stockées sur le serveur. Le serveur est généralement une machine plus puissante, le temps d'exécution de la procédure sur le serveur est donc significativement inférieur à celui de poste de travail. De plus, des informations de la base de données et de la procédure stockée sont situées dans le même système, la transmission d'enregistrements sur le réseau n'est pratiquement pas dépensée. Les procédures stockées ont un accès direct aux bases de données, ce qui rend le travail avec les informations très rapidement.
• L'avantage du développement du système dans l'architecture client-serveur. C'est la possibilité de créer séparément un logiciel client et un serveur. Cet avantage est essentiel dans le développement et, grâce à cela, vous pouvez réduire considérablement le temps requis pour la fin du projet. Le code exécuté sur le serveur peut être développé séparément à partir du code de la pièce client. Dans ce cas, les composants de la partie serveur peuvent être partagés par les composants du côté du client.
• Niveau de sécurité. Les procédures stockées peuvent servir d'outil d'amélioration de la sécurité. Vous pouvez créer des procédures stockées qui effectuent les opérations d'ajout, de modification, de suppression et d'affichage des listes d'affichage et d'obtenir un contrôle sur chacun des aspects de l'accès à l'information.
• Renforcement des règles du serveur fonctionnant avec des données. C'est l'une des causes les plus importantes de l'utilisation du noyau de base de données intelligent. Les procédures stockées vous permettent d'appliquer des règles et une autre logique qui permet de contrôler les informations activées au système.

Même si langue SQL Il est défini comme un serveur SQL non---présent, SQL utilise des mots-clés liés à la gestion de l'exécution de la procédure. Ces mots-clés sont utilisés lors de la création de procédures pouvant être enregistrées pour une exécution ultérieure. Les procédures stockées peuvent être appliquées au lieu de programmes créés à l'aide de langues standard Programmation (par exemple, avec ou Visual Basic) et effectuer des opérations dans la base de données données SQL Serveur.

Les procédures stockées sont compilées à la première exécution et sont enregistrées dans la table système de la base de données actuelle. Lors de la compilation, ils sont optimisés. Dans ce cas, le meilleur moyen d'accéder aux informations de la table est sélectionné. Cette optimisation prend en compte la position réelle des données dans le tableau, les index disponibles, la charge de la table, etc.

Les procédures stockées compilées peuvent améliorer considérablement les performances du système. Cela vaut la peine, cependant, il convient de noter que les statistiques de données du moment de la création de la procédure jusqu'à ce que son exécution puisse être obsolète et que les indices peuvent devenir inefficaces. Et bien que vous puissiez mettre à niveau des statistiques et ajouter de nouveaux index plus efficaces, le plan d'exécution de la procédure est déjà compilé, c'est-à-dire que la procédure est compilée et, par conséquent, la méthode d'accès aux données peut cesser d'être efficace. Par conséquent, il est possible de recompiler les procédures à chaque appel.

D'autre part, la recompilation prendra du temps à chaque fois. Par conséquent, la question de l'efficacité de la recompilation de la procédure ou de la compilation ponctuelle de sa mise en œuvre est assez subtile et doit être prise en compte pour chaque cas spécifique séparément.

Les procédures stockées peuvent être effectuées sur une machine locale ou sur un système SQL Server distant. Cela permet d'activer les processus sur d'autres machines et de travailler non seulement avec des bases de données locales, mais également avec des informations sur plusieurs serveurs.

Programmes d'application écrits dans l'une des langues de haut niveau, telles que ou Visual Basic .NET, peuvent également provoquer des procédures stockées, ce qui garantit solution optimale sur la distribution de la charge entre logiciel Partie client et SQL Server.

1.2. Création de procédures stockées

Créer une instruction de procédure s'applique pour créer une procédure stockée. Le nom de la procédure stockée peut comporter jusqu'à 128 caractères, y compris les symboles # et ##. Syntaxe de définition de procédure:

Créer une qualité de nom de processus [; numéro]
[(@ Paramètre type_data) [\u003d valeur___uomolement]]] [, ... n]

Comme
<Инструкции_SQL>

Considérez les paramètres de cette commande:

• Nom: Nom de procédure - Nom de la procédure; Doit satisfaire les règles d'identificateurs: sa longueur ne peut pas dépasser 128 caractères; Pour les procédures horaires locales, le nom est utilisé avant le nom et pour les procédures temporaires mondiales - Signes ##;
• Le nombre est un entier optionnel utilisé pour grouper plusieurs procédures sous un nom;
• @ Paramètre type_data - une liste de noms de paramètres de procédure indiquant le type de données approprié pour chacun; Ces paramètres peuvent comporter jusqu'à 2100. La valeur du paramètre est autorisée à transmettre null. Tous les types de données peuvent être utilisés à l'exception des types de texte, de Ntext et d'images. Vous pouvez utiliser le type de données du curseur comme paramètre de sortie (sortie de mots clés ou variant). Les paramètres avec les données du curseur peuvent être uniquement des paramètres de sortie;
• Variant est un mot clé qui détermine que le paramètre résultant est utilisé comme paramètre de résultat (utilisé uniquement pour le type de curseur);
• Sortie - suggère que le paramètre spécifié peut être utilisé comme sortie;
• valorisation_to_ux - utilisé dans le cas lorsque vous appelez la procédure, le paramètre manque; Il doit y avoir une constante et peut inclure des caractères de masque (%, _, [,], ^) et valeur null ;
• Avec recompilation - mots-clés montrant que SQL Server n'enregistrera pas le plan de procédure dans le cache et la créera à chaque fois lorsqu'il est exécuté;
• Avec le cryptage - Mots-clés montrant que SQL Server cryptera la procédure avant d'écrire sur la table du système SysComments. Pour que le texte des procédures cryptés soit restauré, il est nécessaire de supprimer les cortices des tables de SysComments correspondant à eux après cryptage;
• Pour la réplication - Mots-clés indiquant que cette procédure n'est créée que pour la réplication. Cette option est incompatible avec les mots-clés avec recompilation;
• Comme - le début de la définition du texte de la procédure;
• <Инструкции_SQL> - Ensemble instructions admissibles SQL, limité uniquement par la taille maximale de la procédure stockée - 128 Ko. Invalid Les opérateurs suivants sont les opérateurs suivants: Alter la base de données, Alter Procédure, Alter Tableau, Créer par défaut, Créer la procédure, Autter Trigger, Alter View, Créer une base de données, Créer une règle, Créer un schéma, Créer une gâchette, Créer une vue, Disque init, Disque redimensionnée, Disque , Déposez par défaut, procédure de chute, règle de chute, déclencheur de chute, affichage de la liste déroulante, base de données de résine, journal de restauration, reconfiguration, mise à jour des statistiques.

Considérons un exemple de la procédure stockée. Nous développerons une procédure stockée qui compte et affiche le nombre de copies de livres actuellement dans la bibliothèque:

Créer la procédure Nombre_EX1.
- Procédure de comptage du nombre de copies des livres,
- actuellement dans la bibliothèque,
- pas sur les lecteurs de la main
Comme
- Définir la variable locale temporaire
Déclarer @n int
Sélectionnez @n \u003d Count (*) à partir de l'exemplar où YES_NO \u003d "1"
Sélectionnez @n.
Va.

Étant donné que la procédure stockée est un composant de base de données à part entière, la manière dont vous avez déjà compris, vous pouvez créer une nouvelle procédure pour la base de données actuelle. Lorsque vous travaillez dans SQL Server Query Analyzer, la définition de la base de données actuelle est effectuée à l'aide de l'opérateur Utilisation, suivie du nom de la base de données où la procédure stockée doit être créée. Sélectionnez la base de données actuelle peut également être utilisée à l'aide de la liste déroulante.

Après avoir créé dans le système stocké procédures SQL Le serveur compile et vérifie les sous-programmes exécutables. Si des problèmes se produisent, la procédure est rejetée. Avant de re-diffuser, l'erreur doit être éliminée.

SQL Server 2000 utilise reporté nommé nommé nommé nommé nommé.

Si vous laissez le système avec une procédure stockée non spécifiée, tout en essayant de l'exécuter, l'utilisateur recevra un message d'erreur.

Vous pouvez également créer une procédure stockée à l'aide de SQL Server Enterprise Manager:

Afin de vérifier les performances de la procédure stockée créée, vous devez passer à Query Analyzer et exécuter la procédure d'exécution par l'opérateur. EXECT.<имя процедуры> . Les résultats du lancement des procédures créées par nous sont présentés à la Fig. quatre.

Figure. 4. Exécutez la procédure stockée dans Query Analyzer

Figure. 5. Le résultat de la procédure sans opérateur de sortie à l'écran

1.3. Paramètres des procédures stockées

Les procédures stockées sont un outil très puissant, mais une efficacité maximale ne peut être obtenue que en leur rendant dynamique. Le développeur doit être en mesure de transmettre la valeur à la procédure stockée avec laquelle elle fonctionnera, c'est-à-dire les paramètres. Vous trouverez ci-dessous les principes de base de l'application des paramètres dans les procédures stockées.

• Pour la procédure, vous pouvez définir un ou plusieurs paramètres.
• Les paramètres sont utilisés comme emplacements de stockage nommés, comme des variables dans les langages de programmation, tels que C, Visual Basic .NET.
• Le nom du paramètre est nécessairement précédé par le symbole @.
• Les noms de paramètres sont locaux dans cette procédure où ils sont définis.
• Les paramètres servent à transmettre la procédure d'information lors de l'exécution. Ils interféreront avec la ligne de commande après le nom de la procédure.
• Si la procédure comporte plusieurs paramètres, ils sont séparés par des virgules.
• Pour déterminer le type d'informations transmis comme un paramètre, utilisez systémique ou types personnalisés Les données.

Vous trouverez ci-dessous la définition d'une procédure ayant un paramètre d'entrée. Modifiez la tâche précédente et nous assumerons toutes les copies des livres, mais seulement des instances d'un livre particulier. Les livres sont définitivement identifiés par l'ISBN unique, nous allons donc transférer ce paramètre à la procédure. Dans ce cas, le texte de la procédure stockée changera et aura le formulaire suivant:

Créer la procédure Nombre_EX (@isbn varchar (14))
Comme
Déclarer @n int
Sélectionnez @n.
Va.

Lorsque vous démarrez cette procédure, nous devons transmettre la valeur de paramètre d'entrée (Fig. 6).

Figure. 6. Exécution de la procédure avec le transfert du paramètre

Pour créer plusieurs versions de la même procédure qui ont le même nom, suivez le nom principal pour mettre un point-virgule et un entier. Comment faire cela est affiché dans l'exemple suivant, où la création de deux procédures avec le même nom est décrite, mais avec des numéros de versions différentes (1 et 2). Le numéro est utilisé pour contrôler la version de cette procédure. Si le numéro de version n'est pas spécifié, la première version de la procédure est effectuée. Cette option n'est pas affichée dans l'exemple précédent, mais, néanmoins, il est disponible pour votre application.

Pour afficher un message identifiant la version, les deux procédures appliquent l'instruction d'impression. La première version considère le nombre d'instances libres et la seconde est le nombre de copies sur les mains de ce livre.

Le texte des deux versions des procédures est ci-dessous:

Créer la procédure Nombre_EX_ALL; une
(@Isbn varchar (14))
- Procédure de comptage des copies gratuites d'un livre donné
Comme
Déclarer @n int
Sélectionnez @n \u003d Count (*) de Exemplar où ISBN \u003d @ISBN et YES_NO \u003d "1"
Sélectionnez @n.
--
Va.
--
Créer la procédure Nombre_EX_ALL; 2.
(@Isbn varchar (14))
- Procédure de comptage des copies gratuites d'un livre donné
Comme
Déclarez @ n1 int
SELECT @ N1 \u003d COUNT (*) à partir d'Exemplar où ISBN \u003d @ISBN et YES_NO \u003d "0"
Sélectionnez @ N1.
Va.

Les résultats de la procédure avec différentes versions sont illustrés à la Fig. 7.

Figure. 7. Exécutez les résultats différentes versions idem et la même procédure stockée

Lorsque vous écrivez plusieurs versions, vous devez vous rappeler les limitations suivantes: car toutes les versions de la procédure sont compilées ensemble, toutes les variables locales sont considérées comme courantes. Par conséquent, si elle est requise par l'algorithme de traitement, vous devez utiliser différents noms des variables internes que nous avons effectuées, appelant la variable @n dans la deuxième procédure par le @ N1.

Nous avons écrit les procédures ne renvoyons aucun paramètre, ils affichent simplement le numéro résultant. Cependant, le plus souvent, nous devons obtenir un paramètre pour un traitement ultérieur. Il existe plusieurs façons de renvoyer des paramètres de la procédure stockée. Le plus facile est d'utiliser les valeurs de retour d'opérateur de retour. Cet opérateur vous permettra de renvoyer une valeur numérique. Mais nous devons spécifier le nom de la variable ou de l'expression, qui est attribué au paramètre renvoyé. Les valeurs suivantes sont renvoyées par l'opérateur de retour réservé par le système:

Le code	Valeur
0	Tout va bien
-1.	L'objet n'est pas trouvé
-2	Erreur de type de données
-3.	Le processus a été victime de "impasse"
-4.	erreur d'accès
-5	Erreur de syntaxe
-6	Une erreur
-7	Erreur avec des ressources (pas de place)
-8.	Il y avait une erreur interne corrigée
-9	La limite du système est épuisée
-dix.	Violation incorrigible de l'intégrité interne
-11	Même
-12.	Destruction d'une table ou d'un index
-13	Destruction de la base de données
-14	Erreur d'équipement

Afin de ne pas contredire le système, nous ne pouvons récupérer que des nombres positifs entiers via ce paramètre.

Par exemple, nous pouvons modifier le texte de la procédure stockée numérique précédemment écrite comme suit:

Créer la procédure Nombre_Ex2 (@isbn varchar (14))
Comme
Déclarer @n int
Sélectionnez @n \u003d Count (*) de l'exemplar
Où isbn \u003d @isbn et yes_no \u003d "1"
- renvoyer la valeur de la variable @n,
- Si la valeur de la variable n'est pas définie, renvoyez 0
Retour regroupement (@n, 0)
Va.

Maintenant, nous pouvons obtenir la valeur de la variable @n et l'utiliser pour un traitement ultérieur. Dans ce cas, la valeur renvoyée est attribuée à la procédure la plus stockée et pour l'analyser, vous pouvez utiliser le format d'instruction d'appel suivant de la procédure stockée:

EXECT.<переменная> = <имя_процедуры> <значение_входных_параметров>

Un exemple d'appel de notre procédure est illustré à la Fig. huit.

Figure. 8. Transmission de la valeur renvoyée de la procédure stockée de la variable locale

Les paramètres d'entrée des procédures stockées peuvent utiliser la valeur par défaut. Cette valeur sera utilisée si la valeur du paramètre n'a pas été spécifiée lors de l'appelant la procédure.

La valeur par défaut est définie via le signe égal après avoir décrit le paramètre d'entrée et son type. Considérez la procédure stockée, qui considère le nombre de copies des livres de l'année de sortie spécifiée. Année de développement par défaut - 2006.

Créer une procédure ex_books_now (@Year int \u003d 2006)
- Compter le nombre de copies des livres de l'année de sortie spécifiée
Comme
Déclarez @n_books int
Sélectionnez @n_books \u003d compter (*) à partir de livres, exemplaire
Où books.isbn \u003d exemplar.isbn et yearizd \u003d @Year
Retour coalesce (@n_books, 0)
Va.

En figue. 9 montre un exemple d'appel de cette procédure, indiquant le paramètre d'entrée et sans elle.

Figure. 9. Appelant la procédure stockée avec le paramètre et sans paramètre

Tous les exemples de l'utilisation de paramètres dans les procédures stockées prévu uniquement uniquement des paramètres d'entrée. Cependant, les paramètres peuvent être la sortie. Cela signifie que la valeur du paramètre après l'achèvement de la procédure sera transférée à celle qui a provoqué cette procédure (une autre procédure, un déclencheur, un package de commandes, etc.). Naturellement, afin d'obtenir le paramètre de sortie, lorsque l'appel doit être spécifié comme un paramètre réel n'est pas une constante, mais une variable.

Notez que la définition de la procédure de paramètre car la sortie ne vous oblige pas à l'utiliser en tant que telle. C'est-à-dire que si vous spécifiez une constante comme paramètre réel, les erreurs ne se produiront pas et il sera utilisé comme paramètre d'entrée normal.

Pour spécifier que le paramètre de sortie est utilisé, l'instruction de sortie est utilisée. Ce mot clé est enregistré après avoir décrit le paramètre. Lorsque vous décrivez les paramètres des procédures stockées, il est souhaitable de définir les valeurs des paramètres de sortie après l'entrée.

Considérons un exemple d'utilisation des paramètres de sortie. Nous écrivons la procédure stockée, laquelle pour un livre donné calcule le nombre total de ses instances dans la bibliothèque et le nombre d'instances libres. Nous ne pourrons pas utiliser l'opérateur de retour de retour ici car il ne renvoie qu'une seule valeur. Nous devons donc déterminer les paramètres de sortie ici. Le texte de la procédure stockée peut ressembler à ceci:

Créer la procédure Nombre_Books_All.
(@Isbn varchar (14), @ Fall int sortie, @free int Sortie)
- Procédure de calcul du nombre total de décharges du livre donné
- et le nombre de spécimens libres
Comme
- Compter le nombre total de copies
Sélectionnez @All \u003d Count (*) de Exemplar où ISBN \u003d @Isbn
Sélectionnez @free \u003d Count (*) à partir de l'exemplar où ISBN \u003d @Isbn et YES_NO \u003d "1"
Va.

Un exemple de cette procédure est illustré à la Fig. dix.

Figure. 10. Test des procédures stockées avec les paramètres de sortie

Comme mentionné précédemment, afin d'obtenir la valeur des paramètres de sortie à analyser, nous devons les définir avec des variables et ces variables doivent être décrites par l'opérateur déclaratif. Le dernier opérateur de sortie nous a permis de simplement afficher les valeurs obtenues.

Les paramètres de la procédure peuvent même être des variables de curseur de type. Pour ce faire, la variable doit être décrite comme un type spécial de données variant, sans liaison aux types de données système standard. De plus, il faut préciser qu'il s'agit d'un curseur de type variable.

Nous écrivons la procédure la plus simple qui affiche une liste de livres dans notre bibliothèque. Dans ce cas, s'il n'y a plus de livres, nous apportons leurs noms dans la procédure elle-même, et si la liste des livres dépasse le nombre spécifié, nous les transmettons sous forme de curseur avec un programme ou un module d'appel.

Le texte de la procédure est la suivante:

Créer une procédure get3titles.
(@Mycursor curseur de sortie variable)
- Imprimer Titres de procédure avec curseur
Comme
- Déterminer local type de variable Curseur dans la procédure
Ensemble @mycursor \u003d curseur
Pour sélectionner le titre distinct
Des livres.
- Ouvrez le curseur
Ouvrez @mycursor.
- Décrire les variables locales internes
Déclarer @title varchar (80), @cnt int
--- Installez l'état initial du compteur de livres
SET @CNT \u003d 0
- Aller à la première chaîne du curseur
- alors qu'il y a des cordes de curseur,
- C'est-à-dire que la transition vers nouvelle chaîne Corriger
Tandis que (@@ fetch_status \u003d 0) et (@cnt<= 2) BEGIN
Imprimer @titer
Chercher à partir de @mycursor inte @title
- changer le statut du compteur de livres
SET @CNT \u003d @CNT + 1
Finir.
Si @cnt \u003d 0 Imprimer "Pas de livres appropriés"
Va.

Un exemple d'appel de cette procédure stockée est illustré à la Fig. Onze.

Dans la procédure d'appel, le curseur doit être décrit comme une variable locale. Ensuite, nous avons appelé notre procédure et nous l'a remis le nom du curseur de type variable local. La procédure a commencé à travailler et nous a apporté les trois premiers noms de l'écran, puis transféré le contrôle de la procédure de l'appelant et qui a poursuivi le traitement du curseur. Pour ce faire, il a organisé un cycle de type tandis que sur la variable globale @@ fetch_status, qui suit l'état du curseur, puis dans le cycle a déplacé toutes les autres lignes de curseur.

Dans la fenêtre de sortie, nous voyons un intervalle élargi entre les trois premières lignes et les noms suivants. Cet intervalle indique que la gestion est transférée au programme externe.

Notez que la variable @title, être une locale pour la procédure, sera détruite après l'achèvement de son fonctionnement, donc dans la procédure d'appel qui est déclarée à nouveau. Créer et ouvrir un curseur dans cet exemple se produit dans la procédure, la fermeture, la destruction et le traitement supplémentaire sont effectués dans le bloc de commande dans lequel la procédure est appelée.

Le moyen le plus simple de voir le texte de la procédure, de les modifier ou de la supprimer à l'aide de l'interface graphique de l'entreprise Manager. Mais vous pouvez le faire à l'aide de procédures SPÉCIAIRES STOBLED SQL-SQL. Dans Transact-SQL, la visualisation de la procédure est effectuée à l'aide de la procédure SP_HELPTEXTEXTExt et la procédure SP_HELP System vous permet d'afficher des informations de contrôle sur la procédure. SP_HELPTEXT et SP_HELP Les procédures système sont utilisées et pour afficher de tels objets de base de données tels que des tables, des règles et des paramètres par défaut.

Informations sur toutes les versions d'une procédure, quel que soit le numéro, est affiché immédiatement. La suppression de différentes versions d'une procédure stockée se produit également simultanément. Dans l'exemple suivant, il est montré comment les versions 1 et 2 des définitions de Count_Ex_All sont affichées lorsque son nom est indiqué en tant que paramètre SP_HELPTEXT SYSTÈME SYSTEM (Fig. 12).

Figure. 12. Afficher la procédure stockée du texte à l'aide de la procédure stockée du système

La procédure système SP_HELP affiche les caractéristiques et les paramètres de la procédure créée comme suit:

Nom. Propriétaire Taper	Créé_datetime.
Comte_books_all. dBO. procédure stockée.	2006-12-06 23:15:01.217
Le nom du paramètre. Taper Longueur précle.	Escalader	Param_order Collation
@Isbn. varchar. 14 14	NUL	1	Cyrillic_general_ci_as.
@tout int. 4 10	0	2	NUL
@libre int. 4 10	0	3	NUL

Essayez de déchiffrer ces paramètres vous-même. De quoi parlent-ils?

1.4. Compiler la procédure stockée

L'avantage d'utiliser des procédures stockées pour effectuer l'ensemble d'instructions de transact-SQL est qu'ils sont compilés à la première exécution. Au cours du processus de compilation, l'instruction Transact-SQL est convertie de leur présentation symbolique initiale sous la forme exécutable. Tous les objets auxquels l'appel de la procédure est également converti en une vision alternative. Par exemple, les noms des tables sont convertis en identificateurs d'objet et les noms de colonne des identifiants de colonne.

Le plan d'exécution est créé de la même manière pour exécuter une instruction Transact-SQL. Ce plan contient, par exemple, des index utilisées pour lire des lignes des tables auxquelles la procédure est tirée. Le plan de procédure est enregistré dans le cache et est utilisé pour chaque appel.

Remarque: La taille de la cache de procédure peut être déterminée de manière à pouvoir contenir la plupart ou la totalité de la procédure. Cela permettra de sauvegarder le temps nécessaire pour générer un plan de procédure.

1.5 Re-compilation automatique

Typiquement, le plan d'exécution est dans les procédures de cache. Cela vous permet d'accroître la productivité lorsqu'il est exécuté. Toutefois, dans certaines circonstances, la procédure est automatiquement recompilée.

• La procédure est toujours recompilée lorsque le serveur SQL est démarré. Cela se produit généralement après le redémarrage du système d'exploitation et lorsque la procédure est effectuée pour la première fois après la création.
• Le plan de procédure est toujours automatiquement recompilé si l'indice de table est supprimé sur lequel la procédure est tirée. Étant donné que le plan actuel semble lire les lignes de table à l'indice qui n'existe plus, vous devez créer un nouveau plan d'exécution. Les demandes de procédure ne seront exécutées que si elles sont mises à jour.
• La compilation du plan d'exécution a lieu dans le cas où l'autre utilisateur fonctionne actuellement avec ce plan. Pour le deuxième utilisateur, une copie individuelle du plan d'exécution est créée. Si la première copie du plan n'était pas occupée, il ne serait pas nécessaire de créer une deuxième copie. Lorsqu'un utilisateur complète l'exécution de la procédure, le plan d'exécution est disponible dans le cache d'un autre utilisateur qui a une résolution d'accès appropriée.
• La procédure est automatiquement recompilée s'il est supprimé et mis à jour. Étant donné que la nouvelle procédure peut différer de l'ancienne version, toutes les copies du plan d'exécution dans le cache sont supprimées et le plan est compilé à nouveau.

SQL Server cherche à optimiser les procédures stockées en mettant en cache les procédures les plus utilisées de manière intensive. Par conséquent, l'ancien plan d'exécution chargé dans le cache peut être utilisé à la place d'un nouveau plan. Pour éviter ce problème, supprimez et recréez une procédure stockée ou d'arrêter et de réactiver SQL Server. Cela nettoie le cache de la procédure et exclura la probabilité de travailler avec l'ancien plan d'exécution.

La procédure peut également être créée avec l'option Recompiler. Dans ce cas, il va automatiquement recompiler à chaque fois. L'option avec recompilation doit être appliquée dans les cas où la procédure fait référence à des tables très dynamiques dont les chaînes sont souvent ajoutées sont supprimées ou mises à jour car elles entraînent des modifications importantes dans les index définis pour les tableaux.

Si les procédures de ré-compilation ne sont pas automatiquement produites, elle peut être effectuée de force. Par exemple, si les statistiques utilisées pour déterminer l'utilisation de l'index dans cette requête ou si un nouvel indice a été créé, une recompulation forcée doit être effectuée. Pour remplir la ré-compilée forcée dans l'instruction Exécute, l'offre de recompilation est appliquée:

Exécuter NAME_NAMES;
Comme
<инструкции Transact-SQL>
Avec recompilation

Si la procédure fonctionne avec des paramètres qui contrôlent la procédure de son exécution, l'option Recompilis doit être utilisée. Si les paramètres de la procédure stockée peuvent déterminer le meilleur chemin d'exécution, il est recommandé de former un plan d'exécution pendant le fonctionnement, plutôt que de la créer lorsque la procédure est d'abord appelée à une utilisation avec tous les appels ultérieurs.

Remarque: Il est parfois difficile de déterminer si l'option de recompilation est nécessaire lors de la création d'une procédure ou non. S'il y a des doutes, il vaut mieux ne pas appliquer cette option, car la procédure de ré-compilation de chaque exécution entraînera une perte d'une période très précieuse du processeur central. Si vous avez besoin d'une compilation à l'avenir lors de la réalisation d'une procédure stockée, il sera possible de le produire en ajoutant la proposition avec recompiler à l'instruction Exécuter.

Vous ne pouvez pas appliquer l'option avec recompiler dans les instructions de création de procédure contenant l'option de réplication pour la réplication. Cette option est utilisée pour créer une procédure effectuée lors de la réplication.

1.6. Procédures stockées de nidification

Dans les procédures stockées, d'autres procédures stockées peuvent être contestées, toutefois, il existe une restriction des niveaux de nidification. Le niveau de nidification maximum est de 32. Le niveau de nidification actuel peut être déterminé à l'aide d'une variable globale @@ Nestlevel.

2. Fonctions définies par l'utilisateur (UDF)

Dans MS SQL Server 2000, de nombreuses fonctions prédéterminées permettent une variété d'actions. Cependant, il peut toujours être nécessaire d'utiliser certaines fonctions spécifiques. Pour ce faire, commencer par la version 8.0 (2000), il a la possibilité de décrire les fonctions utilisateur (fonctions définies par l'utilisateur, UDF) et de les stocker sous la forme d'un objet de base de données à part entière, ainsi que des procédures stockées, des représentations, etc.

La commodité d'utiliser des fonctions définies par l'utilisateur est évidente. Contrairement aux procédures stockées, les fonctions peuvent être intégrées directement à l'instruction SELECT et pour les utiliser pour obtenir des valeurs spécifiques (dans la section Sélectionner) et sous forme de source de données (dans la section de la section).

Lorsque vous utilisez UDF comme sources de données, leur avantage sur les représentations réside dans le fait que UDF, contrairement aux vues, peut avoir des paramètres d'entrée avec lesquels vous pouvez influencer le résultat de la fonction de la fonction.

Les fonctions définies par l'utilisateur peuvent être trois types: fonctions scalaires, fonctions en ligne et plusieurs fonctions de fonctionnement qui renvoient le résultat de la table. Considérons tous ces types de fonctions.

2.1. Fonctions scalaires

Les fonctions scalaires renvoient un résultat scalaire. Ce résultat peut être n'importe quel type décrit ci-dessus, à l'exception des types de texte, de Ntext, d'images et d'horodatage. C'est la forme la plus simple de la fonction. Sa syntaxe a la forme suivante:

Retour scalar_typ_data

Commencer.
Body_function
Retour scalar_mapsion
Finir.

• Le paramètre de cryptage a déjà été décrit dans la section dédiée aux procédures stockées;
• Schéma - Lie la fonction au diagramme. Cela signifie qu'il est impossible de supprimer des tables ou des vues sur la base desquelles une fonction est basée, sans suppression ou modification de la fonction elle-même. Il est également impossible de modifier la structure de ces tables si la partie variable est utilisée par la fonction. Ainsi, cette option vous permet d'exclure les situations lorsque la fonction utilise des tables ou des vues, et une personne, sans le savoir, les supprimées ou les a changées;
• Retour scalar_typ_data - décrit le type de données qui renvoie une fonction;
• scalar_mapsia - une expression qui renvoie directement le résultat de la fonction. Il doit avoir le même type que celui décrit après les retours;
• la fonction Body_Function est un ensemble d'instructions sur Transact-SQL.

Considérons des exemples d'utilisation des fonctions scalaires.

Créez une fonction de deux entiers fournis à l'entrée sous forme de paramètres choisira le plus petit.

Laissez la fonction ressemble à ceci:

Créer une fonction min_num (@a int, @b int)
Retourne int.
Commencer.
Déclarer @c int
Si un.< @b SET @c = @a
Sinon Set @c \u003d @b
Retour @c.
Finir.

Nous allons maintenant effectuer cette fonctionnalité:

Sélectionnez DBO.MIN_NUM (4, 7)

En conséquence, nous obtenons la valeur de 4.

Vous pouvez appliquer cette fonctionnalité pour trouver la plus petite parmi les valeurs de la colonne de la table:

Sélectionnez Min_lvl, max_lvl, min_num (min_lvl, max_lvl)
Des emplois.

Créez une fonction qui recevra le paramètre Type DateTime et renvoyez la date et l'heure correspondant au début du jour spécifié. Par exemple, si le paramètre d'entrée est 20.09.03 13:31, le résultat sera de 20,09,03 00:00.

Créer une fonction dbo.dadibine (@DAT DateTime)
Retourne smalldateTime comme.
Commencer.
Convertisseur de retour (DateTime, plancher (convert (flotteur, @DAT)))
Finir.

Ici, la fonction de conversion effectue la conversion de type. Premièrement, le type de date-heure est entraîné par le type de flotteur. Avec ce meneur, la partie entière est le nombre de jours, comptant à partir du 1er janvier 1900 et de la période fractionnaire. Ensuite, on arrondit dans un tout plus petit à l'aide de la fonction de plancher et apportant au type de date-heure.

Vérifiez la fonction d'action:

Sélectionnez Dbo.DayBegin (getDate ())

Ici getDate () est une fonction qui renvoie la date et l'heure actuelles.

Les fonctions précédentes ont été utilisées lors du calcul uniquement des paramètres d'entrée. Cependant, vous pouvez utiliser les données stockées dans la base de données.

Créez une fonctionnalité qui recevra deux dates comme paramètres: le début et la fin de l'intervalle de temps - et calculez les revenus de vente totale de cet intervalle. La date de vente et la quantité sera tirée de la table des ventes et les prix des éditions vendus sont dans la table Titres.

Créer une fonction dbo.sumsales (@datebegin DateTime, @Dateend DateTime)
Retourne de l'argent.
Comme
Commencer.
Déclarez @sum Money.
Sélectionnez @sum \u003d somme (t.price * s.qty)

Retour @sum
Finir.

2.2. Fonctions en ligne

Ce type de fonctions ne renvoie pas une valeur scalaire en conséquence, et la table, ou plutôt, le jeu de données. Cela peut être très pratique dans les cas où dans différentes procédures, déclencheurs, etc., le même type de sous-requête est souvent effectué. Ensuite, au lieu d'écrire cette demande partout, vous pouvez créer une fonction et utiliser davantage.

Les fonctions de ce type sont encore plus utiles dans les cas où il est nécessaire que la table renvoyée dépend des paramètres d'entrée. Comme vous le savez, les vues ne peuvent pas avoir de paramètres, mais uniquement les fonctions en ligne peuvent résoudre le problème.

La fonctionnalité de la FUCCIA en ligne est qu'elles ne peuvent contenir qu'une seule demande dans leur corps. Ainsi, les fonctions de ce type sont très similaires aux vues, mais elles peuvent en outre avoir des paramètres d'entrée. Syntaxe de fonction inline:

Créer une fonction [Propriétaire] Nom de la fonction
([(Nom du paramètre scalar_tip_data [\u003d valeur_p__omolement]) [, ... N]])
Table de retour

Revenir [(<запрос>)]

En définissant la fonction, il est indiqué qu'il retournera la table;<запрос> - Ceci est la requête, dont le résultat sera le résultat de la fonction.

Nous écrivons une fonction similaire à une fonction scalaire du dernier exemple, mais qui revenons non seulement au résultat de la somme, mais également des lignes de vente, y compris la date de vente, le nom du livre, le prix, le nombre de pièces et la quantité de vendre. Seules les ventes qui tombent dans la période spécifiée doivent être sélectionnées. Nous chiffrons le texte de la fonction afin que d'autres utilisateurs puissent l'utiliser, mais ne pouvaient pas la lire et le corriger:

Créer une fonction Sales_Period (@datebegin DateTime, @Dateend DateTime)
Table de retour
Avec cryptage
Comme
Revenir (
Sélectionnez T.Title, T.Price, S.qty, Ord_Date, T.Price * s.qty comme Stoim
De Titres T Joindre des ventes S sur T.TITle_ID \u003d S.TITLE_ID
Où ord_date entre @dateebegin et @dateend
)

Appelez maintenant cette fonctionnalité. Comme déjà mentionné, vous pouvez appeler cela uniquement dans la section de l'opérateur Select:

Sélectionnez * à partir de Sales_Period ("01.09.94", "13.09.94")

2.3. Plusieurs fonctions de fonctionnement qui renvoient le résultat de la table

Le premier type de fonctions examiné a permis d'utiliser de nombreuses instructions sur Transact-SQL, mais seul un résultat scalaire est renvoyé. Le deuxième type de fonctions pourrait renvoyer des tables, mais son corps ne représente qu'une seule demande. Les fonctions multijoueurs qui renvoient un résultat tabulaire vous permettent de combiner les propriétés des deux premières fonctions, c'est-à-dire qu'ils peuvent contenir dans le corps beaucoup d'instructions sur TRANSACT-SQL et renvoyer une table par conséquent. La syntaxe de la fonction multifonctionnelle:

Créer une fonction [Propriétaire] Nom de la fonction
([(Nom du paramètre scalar_tip_data [\u003d valeur_p__omolement]) [, ... N]])
Retour @ name_named_teult table
<описание_таблицы>

Commencer.
<тело_функции>
Revenir.
Finir.

• Tableau<описание_таблицы> - décrit la structure de la table renvoyée;
• <описание_таблицы> - contient l'énumération des colonnes et des restrictions.

Envisagez maintenant un exemple qui ne peut être effectué que d'utiliser les fonctions de ce type.

Soit y avoir un arborescence de répertoires et des fichiers qui y sont allongés. Laissez l'ensemble de la structure dans la base de données sous forme de tableaux (fig. 13). En fait, ici, nous avons une structure hiérarchique pour les répertoires. Le schéma contient donc la connexion de la table des dossiers avec elle-même.

Figure. 13. Structure de la base de données pour décrire la hiérarchie des fichiers et des répertoires

Maintenant, écrivez une fonction qui recevra l'identifiant de répertoire à l'entrée et de sortie tous les fichiers qui sont stockés et dans tous les répertoires dans la hiérarchie. Par exemple, si les catalogues de l'Institut sont créés dans le catalogue de la faculté1, de la faculté2, etc., il existe des catalogues du département et, dans chacun des répertoires, il existe des fichiers, puis lorsque vous spécifiez le paramètre de l'identificateur de répertoire, le L'institut doit avoir une liste de tous les fichiers pour tous ces catalogues. Pour chaque fichier, le nom, la taille et la date de création doivent être affichés.

Il est impossible de résoudre la tâche à l'aide de fonctions inline, car SQL n'est pas destiné à effectuer des requêtes hiérarchiques, une requête SQL n'est donc pas à faire ici. La fonction scalaire ne peut pas non plus être appliquée, car le résultat devrait être une table. Nous devons nous aider et venir une fonction multifonctionnelle qui retourne la table:

Créer une fonction dbo.getfiles (@folder_id int)
Table de retour @Files (Nom Varchar (100), Date_Create DateTime, FileSize Int)
Commencer.
Déclarez la table @TMP (folder_id int)
Déclarer @cnt int
Insérer dans les valeurs @TMP (@Folder_ID)
SET @CNT \u003d 1
Tandis que @cnt.<> 0 commence.
Insérer dans @TMP Select folder_id
Des dossiers f jointure @tmp t sur f f.parent \u003d t.folder_id
Où folder_id de @tmp) où folder_id de
SET @CNT \u003d @@ RowCount
Finir.
Insérer dans @files (Nom, Date_Create, FileSize)
Sélectionnez F.Name, f.date_create, f.filesize
Des fichiers f joindre des dossiers flne sur f.folder_id \u003d fl.id
Rejoindre @TMP T sur fl.id \u003d t.folder_id
Revenir.
Finir.

Ici, dans le cycle de la variable @TMP, ajoutez tous les répertoires ci-joints à tous les niveaux de nidification jusqu'à ce que les annuaires imbriqués restent. Ensuite, tous les attributs de fichier nécessaires dans les répertoires répertoriés dans la variable @TMP sont enregistrés dans la variable de résultat @Files.

Tâches pour un travail indépendant

Vous devez créer et déboguer cinq procédures stockées à partir de la liste obligatoire suivante:

Procédure 1. Augmentation du moment de la livraison des copies du livre pendant une semaine si le délai de livraison actuel se situe dans les trois jours précédant la date actuelle jusqu'à trois jours après la date actuelle.

Procédure 2. Compter le nombre d'instances libres d'un livre donné.

Procédure 3. Vérifiez l'existence du lecteur avec le nom de famille spécifié et la date de naissance.

Procédure 4. Entrer dans un nouveau lecteur avec une vérification de son existence dans la base de données et la définition de son nouveau numéro de ticket de Reader.

Procédure 5. Compter l'amende en termes monétaires pour les lecteurs de débiteur.

Brève description des procédures

Procédure 1. Augmentation des livres

Pour chaque entrée de la table Exemplar, elle est cochée si la date de livraison du livre tombe dans un intervalle de temps donné. Si cela frappe, la date de retour du livre augmente pendant une semaine. Lorsque vous effectuez la procédure, vous devez utiliser la fonction Dates:

Dateadd (jour,<число добавляемых дней>, <начальная дата>)

Procédure 2. Compter le nombre d'instances libres du livre spécifié

Le paramètre d'entrée de la procédure est un iSBN - un chiffre unique du livre. La procédure renvoie 0 (zéro) si toutes les instances de ce livre sont sur les mains des lecteurs. La procédure renvoie la valeur de n, égale au nombre de copies du livre, qui sont actuellement entre les mains des lecteurs.

Si les livres avec l'ISBN prédéterminé ne sont pas dans la bibliothèque, la procédure renvoie -100 (moins cent).

Procédure 3. Vérification de l'existence du lecteur avec le nom de famille spécifié et la date de naissance

La procédure renvoie le lecteur du lecteur si le lecteur est avec de telles données existant et 0 (zéro) sinon.

Lors de la comparaison de la date de naissance, vous devez utiliser la fonction de conversion convert () pour convertir la date de naissance - le type de variable de caractères Varcharchar (8) utilisé comme paramètre d'entrée de la procédure sur les données de données, qui est utilisée dans la table des lecteurs. Sinon, l'opération de comparaison lors de la recherche de ce lecteur ne fonctionnera pas.

Procédure 4. Entrer dans un nouveau lecteur

La procédure comporte cinq paramètres d'entrée et de trois sorties.

Paramètres d'entrée:

• Nom complet avec les initiales;
• Adresse;
• Date de naissance;
• Téléphone fixe;
• Ouvrier du téléphone.

Paramètres de sortie:

• Numéro du lecteur;
• Un signe de savoir si le lecteur a été enregistré auparavant dans la bibliothèque (0 - n'était pas, 1 était);
• Le nombre de livres répertoriés pour le lecteur.

Procédure 5. Compter bien en termes monétaires pour les lecteurs de débiteur

La procédure fonctionne avec le curseur, qui contient une liste de numéros de billets de lecteur de tous les débiteurs. Au cours des travaux, une table temporaire globale ## DOLEG devrait être créée, dans laquelle sa dette totale sera répertoriée pour chaque débiteur pour tous les livres qu'il dure plus longtemps. La compensation monétaire est calculée dans 0,5% du prix du livre pour la journée de retard.

Procédure pour effectuer des travaux

• copies des écrans (captures d'écran) confirmant les modifications apportées à la base de données;
• le contenu des tables de base de données nécessaires pour confirmer l'exactitude du travail;
• procédure stockée en texte avec des commentaires;
• procédé de démarrage de la procédure stockée avec la sortie des résultats de performance.

Des tâches supplémentaires

Les procédures stockées supplémentaires suivantes sont destinées aux tâches individuelles.

Procédure 6. Compter le nombre de livres sur une zone donnée qui existe actuellement dans la bibliothèque au moins dans une seule instance. Le sujet est transmis comme paramètre d'entrée.

Procédure 7. Entrez un nouveau livre avec une indication de ses instances. Lorsque vous entrez des instances du nouveau livre, n'oubliez pas d'entrer leurs numéros d'inventaire corrects. Pense comment ça peut être fait. Je vous rappelle que vous avez des fonctions max et min vous permettant de trouver la valeur maximale ou minimale de tout attribut numérique aux outils Select Demander.

Procédure 8. Formation d'une table avec une liste de lecteurs débiteurs, c'est-à-dire ceux qui étaient censés renvoyer des livres à la bibliothèque, mais pas encore retournés. Dans la table résultante, chaque lecteur de débiteur ne devrait apparaître qu'une seule fois, quel que soit le nombre de livres qu'il doit. En plus du nom et du numéro du lecteur dans la table résultante, vous devez spécifier l'adresse et l'adresse téléphonique.

Procédure 9. Recherchez une copie gratuite du nom spécifié du livre. S'il y a une copie gratuite, la procédure renvoie le numéro d'inventaire de l'instance; Sinon, la procédure renvoie une liste de lecteurs qui ont ce livre, indiquant la date du retour du livre et le téléphone du lecteur.

Procédure 10. La sortie de la liste des lecteurs qui ne détiennent pas un seul livre sur leurs mains pour le moment. Dans la liste, spécifiez le nom et le téléphone.

Procédure 11. La sortie de la liste des livres indiquant le nombre de copies de ce livre dans la bibliothèque et le nombre d'instances libres pour le moment.

version imprimée

Les procédures stockées SQL sont un module logiciel exécutable pouvant être stocké dans divers objets. En d'autres termes, il s'agit d'un objet dans lequel les instructions SQL sont contenues. Ces procédures stockées peuvent être effectuées dans le client d'application pour obtenir de bonnes performances. De plus, de tels objets sont souvent invoqués à partir d'autres scénarios ou même de toute autre partition.

introduction

Beaucoup croient qu'ils sont similaires aux procédures de divers (respectivement, sauf MS SQL). Peut-être que c'est vrai. Ils ont des paramètres similaires, ils peuvent produire des valeurs similaires. De plus, dans certains cas, ils entrent 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 réalité, les procédures stockées SQL ont un large éventail d'avantages qui les affectent parmi de tels processus. Sécurité, variabilité de programmation, productivité - Tout cela attire les utilisateurs travaillant avec des bases de données, de plus en plus. Le pic de la popularité des procédures est tombé pour 2005-2010, lorsque le programme de Microsoft appelé "SQL Server Management Studio" a été publié. Avec cela, il a été beaucoup plus facile de travailler avec des bases de données, plus pratique et plus pratique. D'année en année, cela gagnait la popularité dans les programmeurs. Aujourd'hui est un programme absolument familier que pour les utilisateurs, "communiquer" avec des bases de données, se tenait sur un pair avec "Excel".

Lorsque vous appelez une procédure, il est instantanément traité par le serveur lui-même sans processus inutiles et intervention de l'utilisateur. Après cela, vous pouvez exercer tout renvoi, exécution, changement. Pour tout cela, il est répondu par l'opérateur DDL, qui seul fait les effets les plus complexes du traitement des objets. Et tout cela se produit très rapidement et le serveur n'est en réalité pas chargé. Une telle vitesse et performance vous permettent de transmettre très rapidement de grandes quantités d'informations de l'utilisateur au serveur et inversement.

Pour mettre en œuvre cette technologie, il existe plusieurs langages de programmation. Celles-ci incluent, par exemple, PL / SQL de Oracle, PSQL dans les systèmes Interbase et Firebird, ainsi que le classique «Microsoft» Transact-SQL. Tous sont destinés à créer et à effectuer des procédures stockées, ce qui permet aux grands manuels de base de données d'utiliser leurs propres algorithmes. Cela est nécessaire pour ceux qui gèrent ces informations peuvent protéger tous les objets d'un accès non autorisé de personnes tierces et, en conséquence, de créer ou de supprimer 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é, qui sera le plus approprié, ce qui permet d'économiser de la force et du temps. De plus, la procédure elle-même est traitée, ce qui évite les énormes coûts d'échange temporaires entre le serveur et l'utilisateur. De plus, le module peut être reprogrammé et remplacé par la direction souhaitée dans une absolument à tout moment. Il convient notamment de noter la vitesse avec laquelle le début de la procédure stockée SQL est démarré: 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 est différent des processus similaires en ce qu'il garantit une sécurité accrue. Ceci est assuré en raison du fait que l'accès des autres utilisateurs aux procédures peut être complètement éliminé. Cela permettra à l'administrateur de mener des opérations avec eux de manière indépendante, sans crainte d'intercepter des informations ni un 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 consiste à utiliser des paramètres et des valeurs renvoyées. Ce dernier ne peut pas être transféré aux données de la procédure stockée, cependant, ces informations (principalement sur la demande de l'utilisateur) et traitées pour SQL. Une fois la procédure stockée terminée ses travaux, il renvoie des paquets de données en arrière (mais, à nouveau, si vous le souhaitez) à l'application qui l'a provoquée à l'aide de diverses méthodes pouvant être implémentées comme un appel à la procédure stockée SQL et de retour, par exemple:

Transmission de données à l'aide du paramètre de type de sortie;

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

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

Et maintenant, nous allons comprendre comment ce processus a l'air de l'intérieur.

1. Création d'une procédure stockée Exec dans SQL

Vous pouvez créer une procédure dans MS SQL (Studio de gestion). Une fois la procédure créée, il sera répertorié dans un nœud de base de données programmable dans lequel la procédure de création est effectuée par l'opérateur. Pour effectuer les procédures SQL stockées, utilisez le processus Exec qui contient le nom de l'objet lui-même.

Lors de la création de la procédure, son nom apparaît d'abord, après lequel un ou plusieurs paramètres attribués à celui-ci. Les paramètres peuvent être facultatifs. Après le (s) paramètre (s), c'est-à-dire que le corps de la procédure sera écrit, vous devez effectuer des opérations nécessaires.

Le fait est que le corps peut avoir des variables locales situées dedans et ces variables sont locales et relatives aux procédures. En d'autres termes, ils ne peuvent être considérés que dans le corps de la procédure Microsoft SQL Server. Les procédures stockées dans ce cas 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 en tant que corps de la procédure. Veuillez noter qu'une excellente option dans ce cas est la création et l'exécution de la procédure avec le nom du schéma dans le classificateur.

Le corps de la procédure peut avoir n'importe quel type d'exemple, tel que la création d'une table, insérer une ou plusieurs lignes de table, régler le type et le caractère de la base de données, etc. Néanmoins, le corps de la procédure limite l'exécution de certaines opérations. Certaines des restrictions importantes sont énumérées ci-dessous:

Le corps ne doit pas créer d'autre procédure stockée;

Le corps ne doit pas créer une fausse compréhension de l'objet;

Le corps ne doit pas créer de déclencheurs.

2. Installation de la variable dans le corps de la procédure

Vous pouvez faire des variables locales pour la procédure du corps, puis ils seront uniquement à l'intérieur du corps de la procédure. La bonne pratique consiste à créer des variables au début du corps de la procédure stockée. Mais vous pouvez également définir des variables partout dans le corps de cet objet.

Parfois, vous pouvez voir que plusieurs variables sont installées en une ligne et chaque paramètre variable est séparé par une virgule. Notez également que la variable a un préfixe @. Dans le corps de la procédure, vous pouvez définir la variable où vous voulez. Par exemple, la variable @ nom1 peut être déclarée plus proche de la fin du corps de la procédure. Afin d'attribuer la valeur de la variable annoncée, un ensemble de données personnelles est utilisé. Contrairement à la situation lorsqu'il est déclaré plus d'une variable d'une ligne, un seul ensemble de données à caractère personnel est utilisé dans une telle situation.

Souvent, les utilisateurs posent une question: "Comment attribuer plusieurs valeurs dans un opérateur dans le corps de la procédure?" Bien. La question est intéressante, mais il est beaucoup plus facile de faire ce que vous pensez. Réponse: Utilisation de la vapeur telle que "Sélectionnez Var \u003d Valeur". Vous pouvez utiliser ces paires, séparant leur virgule.

Dans une grande variété d'exemples, les gens montrent la création d'une simple procédure stockée et l'exécutant. Toutefois, la procédure peut prendre de tels paramètres que le processus le faisant causer aura les valeurs proches (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 renvoyera les données sur le nombre d'auteurs se réfèrent à la ville et à la région correspondantes. La procédure demandera par exemple la table des auteurs de la base de données, par exemple pour effectuer ce comptage d'auteurs. Pour obtenir ces bases de données, par exemple, Google charte le script SQL à partir de la page SQL2005.

Dans l'exemple précédent, la procédure prend deux paramètres qui, en anglais, s'appellera conditionnellement @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 @Totalathors (auteurs totaux), et cette variable est utilisée pour afficher leur quantité. La section de sélection suivante apparaît, qui compte tout. Enfin, la valeur calculée est affichée dans la fenêtre de sortie à l'aide de l'instruction Imprimer.

Comment effectuer une procédure stockée dans SQL

Il y a deux façons d'effectuer la procédure. Le premier chemin indique le passage des paramètres que la liste séparée par la virgule est exécutée après le nom de la procédure. Supposons que nous ayons deux valeurs (comme dans l'exemple précédent). Ces valeurs sont collectées à l'aide des variables de paramètres de la procédure @state et @City. Dans cette méthode de transmission de paramètres, la commande est importante. Cette méthode s'appelle le transfert ordinaire des arguments. Dans la deuxième méthode, les paramètres sont déjà directement nommés et, dans ce cas, la commande n'est pas importante. Cette deuxième méthode est connue sous le nom de transfert d'arguments nommés.

La procédure peut être quelque peu déviée de typique. STLIKELY, comme dans l'exemple précédent, mais seulement ici les paramètres sont décalés. C'est-à-dire que le paramètre @City est enregistré en premier et @state est stocké à côté de la valeur par défaut. Le paramètre par défaut est généralement séparé séparément. Les procédures stockées SQL passent comme simplement des paramètres. Dans ce cas, à condition que le paramètre UT remplace le "ca" par défaut. Dans la deuxième exécution, une seule valeur de l'argument pour le paramètre @City passe et le paramètre @state prend la valeur par défaut "CA". Les programmeurs expérimentés conseillent que toutes les variables par défaut sont plus proches de la fin de la liste des paramètres. Sinon, l'exécution n'est pas possible, puis vous devez travailler avec le transfert d'arguments nommés, ce qui est de plus en plus difficile.

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

Il existe trois moyens importants d'envoyer des données dans la procédure stockée causée. Ils sont énumérés ci-dessous:

Renvoyer la valeur de la procédure stockée;

La sortie des procédures stockées;

Le choix de l'une des procédures stockées.

4.1 Retour des valeurs de procédure stockées SQL

Dans cette technique, la procédure attribue la valeur de la variable locale et le renvoie. La 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 pouvez voir que la valeur d'impression est remplacée par l'opposé.

Voyons maintenant comment effectuer la procédure et afficher la valeur renvoyée à elle. L'exécution de la procédure nécessite la mise en place d'une variable et d'une impression, qui est effectuée après l'ensemble du processus. Veuillez noter qu'au lieu de l'instruction Imprimer, vous pouvez utiliser l'opérateur Select, par exemple, sélectionnez @retvalue, ainsi que de la sortieValue.

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

La valeur de réponse peut être utilisée pour renvoyer une variable que nous avons vue dans l'exemple précédent. L'utilisation du paramètre de sortie permet la procédure d'envoyer une ou plusieurs valeurs variables pour l'appelant. Le paramètre de sortie est indiqué comme si ce mot clé "Sortie" lors de la création d'une procédure. Si le paramètre est spécifié en tant que paramètre de sortie, l'objet de la procédure doit lui attribuer une valeur. Les procédures stockées SQL, des exemples qui peuvent être considérés ci-dessous, auquel cas sont renvoyés avec les informations finales.

Dans notre exemple, il y aura deux noms de sortie: @totalathors 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 les 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 annoncées pour voir les valeurs qui définissent les procédures MS SQL Server stockées dans le paramètre de sortie. Ensuite, la procédure est effectuée en soumettant la valeur normale du paramètre "CA". Les paramètres suivants sont émis et, par conséquent, les variables déclarées sont transmises de la manière prescrite. Veuillez noter que lorsque vous passez des variables, le mot clé de sortie est également défini ici. Une fois la procédure réussie, les valeurs renvoyées par les paramètres de sortie sont affichées dans la fenêtre Messages.

4.3 Sélection d'une procédure stockée SQL

Cette technique permet de renvoyer l'ensemble des valeurs sous forme de tableau de données (enregistrement d'enregistrement) sur la procédure stockée causant. Dans cet exemple, la procédure stockée SQL avec les paramètres @AuthID demande la table "auteurs" en filtrant les entrées renvoyées à l'aide de ce paramètre @AuthID. L'instruction SELECT décide qu'il doit être renvoyé à la procédure stockée appelante. Lors de l'exécution de la procédure stockée, l'AuthID est transmis. Une telle procédure ne renvoie ici qu'une seule entrée ou une personne. Mais la procédure stockée n'a aucune restriction sur le retour de plusieurs enregistrements. Souvent, vous pouvez trouver des exemples dans lesquels le retour des données utilisant des paramètres sélectionnés avec la participation des variables calculées se produit en fournissant plusieurs valeurs de résultat.

Pour terminer

La procédure stockée est un module logiciel assez grave qui renvoie ou transmettant, ainsi que l'installation des variables nécessaires grâce à l'application cliente. Étant donné que la procédure stockée est effectuée sur le serveur lui-même, l'échange de données dans d'énormes volumes entre le serveur et l'application client (pour certains calculs) peut être évitée. Cela vous permet de réduire la charge sur le serveur SQL, qui, bien sûr, va à la main de leurs détenteurs. L'une des sous-espèces sont stockées les procédures T SQL, mais leur étude est nécessaire pour ceux qui sont engagés dans la création de bases de données impressionnantes. Il existe également un grand nombre de nuances importantes qui peuvent être utiles lors de l'étude des procédures stockées, mais il est nécessaire davantage de ceux qui envisagent de participer étroitement dans la programmation, y compris de manière professionnelle.

but du travail - Apprenez à créer et à utiliser des procédures stockées sur le serveur de base de données.

1. Études de tous les exemples, analyse des résultats de leur exécution dans l'utilitaire SQL Server Management Studio. Vérification de la présence de processus créés dans la base de données actuelle.

2. Effectuez tous les exemples et tâches au cours des travaux de laboratoire.

3. Effectuer des tâches individuelles par des options.

Explication du travail

Pour maîtriser les procédures stockées de programmation, utilisez la base de données lorsque la base de données de noms Db_books.qui a été créé en laboratoire Numéro 1. Lorsque vous effectuez des exemples et des tâches, payez la conformité aux noms de la base de données, des tables et d'autres objets du projet.

Procédures stockées Il existe un ensemble de commandes composées d'un ou de plusieurs opérateurs SQL ou fonctions et stockés dans la base de données sous forme de compilation.

Types de procédures stockées

Les procédures stockées du système sont destinées à 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 du système sont une interface qui permet de travailler avec des tables système. Les procédures stockées du système ont le préfixe SP_, sont stockées dans la base de données système et peuvent être causées dans le contexte de toute autre base de données.

Les procédures stockées sur mesure mettent en œuvre certaines actions. Procédures stockées - Un objet de base de données à part entière. En conséquence, chaque procédure stockée est située dans une base de données spécifique, où elle est effectuée.

Les procédures stockées temporaires n'existent que pendant un certain temps, après quoi elles sont automatiquement détruites par le serveur. Ils sont divisés en losal et global. Les procédures de stockage temporaires locales ne peuvent être provoquées que de ce composé dans lequel elle est créée. Lors de la création d'une telle procédure, il doit donner un nom à partir d'un numéro de symbole. Comme tous les objets temporaires, les procédures stockées de ce type sont automatiquement supprimées lorsque l'utilisateur est déconnecté, redémarrez ou arrêtez le serveur. Les procédures globales stockées temporaires sont disponibles pour toutes les connexions de serveur sur lesquelles il existe la même procédure. Pour le déterminer, il suffit de lui donner un nom à partir de ## caractères. Ces procédures sont supprimées lors du redémarrage ou de l'arrêt du serveur, ainsi que lors de la fermeture de la connexion, dans le contexte dont ils ont été créés.

Créer, changer de procédures stockées

La création de la procédure stockée implique la décision des tarifs suivants: planification des droits d'accès. Lors de la création d'une procédure stockée, il convient de garder à l'esprit qu'il aura les mêmes droits d'accès aux objets de base de données, ce qui a créé son utilisateur; Déterminer les paramètres des procédures de magasin, les procédures stockées peuvent avoir des paramètres d'entrée et de sortie; Développement de la procédure stockée du code. Le code de procédure peut contenir une séquence de toutes commandes SQL, y compris appelant d'autres procédures stockées.

La syntaxe de l'opérateur de création d'une nouvelle ou modification de la procédure stockée disponible dans les désignations SQL Server MS SQL:

(Créer | Alter) PROP [EDURE] NOM_NAME [; NUMBER] [(@ PARAMETER_NAME TYPE_DATA) [Variation] [\u003d Défaut] [SORTIE]] [, ... N] [avec (recompiler | Cryptage | Recompiler, cryptage)] [Pour la réplication] comme opérateur SQL_ [... N]

Considérez les paramètres de cette commande.

Utiliser SP_, #, ## Les préfixes créés par la procédure peuvent être définis comme systémiques ou temporaires. Comme on peut le voir à partir de la syntaxe de commande, il n'est pas autorisé à spécifier le nom du propriétaire auquel la procédure créée, ainsi que le nom de la base de données, où elle doit être placée. Ainsi, pour placer la procédure stockée créée dans une base de données spécifique, vous devez exécuter la commande Créer une procédure dans le contexte de cette base de données. Lors de la mise en contact du corps de la procédure stockée sur des objets de la même base de données, vous pouvez utiliser des noms raccourcis, c'est-à-dire sans spécifier le nom de la base de données. Lorsque vous devez vous référer à des objets situés dans d'autres bases de données, la spécification du nom de la base de données doit.

Pour transférer les données d'entrée et de sortie de la procédure stockée créée, les noms de paramètres doivent commencer par le symbole @. Dans une procédure stockée, vous pouvez définir de nombreux paramètres séparés par des virgules. Dans le corps de la procédure, les variables locales ne doivent pas être appliquées, dont les noms sont coïncidés avec les noms des paramètres de cette procédure. Pour déterminer le type de ces paramètres de la procédure stockée, tous les types de données SQL conviennent, y compris les utilisateurs définis par l'utilisateur. Cependant, le type de données du curseur ne peut être utilisé que comme paramètre de sortie de la procédure stockée, c'est-à-dire Avec le mot clé de sortie.

La présence du mot clé de sortie signifie que le paramètre correspondant est conçu pour renvoyer les données de la procédure stockée. Cependant, cela ne signifie pas que le paramètre ne convient pas à la transmission de valeurs dans la procédure stockée. Spécification du mot clé de sortie indique au serveur lors de la sortie de la procédure stockée pour attribuer la valeur actuelle du paramètre Variable local, spécifiée lorsque la procédure est appelée la valeur du paramètre. Notez que lorsque vous spécifiez la sortie du mot-clé, la valeur du paramètre correspondant lors de l'appel de la procédure ne peut être spécifiée que par une variable locale. Il n'est pas permis d'utiliser des expressions ou des constantes autorisées pour les paramètres conventionnels. Le mot clé variables est utilisé conjointement avec le paramètre de sortie ayant un type de curseur. Il détermine que le paramètre de sortie sera l'ensemble résultant.

Le mot clé par défaut est une valeur qui acceptera le paramètre par défaut correspondant. Ainsi, lorsque la procédure est appelée, vous ne pouvez pas spécifier 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 de l'appel ultérieur, les procédures seront déjà utilisées. Cependant, dans certains cas, il est toujours nécessaire de recompiler le code de la procédure. La spécification de la recompilation des mots-clés prescrit le système pour créer un plan de procédure stocké pour chaque appel.

Le paramètre de réplication est en demande lors de la réplication des données et de l'inclusion de la procédure stockée créée en tant qu'article de publication. Le mot clé de cryptage indique au serveur de chiffrer le code de la procédure stockée, qui peut fournir une protection contre l'utilisation d'algorithmes de copyright qui mettent en œuvre le travail de la procédure stockée. Le mot clé tel que situé au début du corps de la procédure stockée. Dans le corps de la procédure, presque toutes les commandes SQL peuvent être appliquées, des transactions déclarées, bloquent la serrure et appelez d'autres procédures stockées. La sortie de la procédure stockée peut être effectuée à l'aide de la commande de retour.

Suppression de la procédure stockée

Procédure de chute (nom) [, ... N]

Effectuer une procédure stockée

Pour effectuer la procédure stockée, la commande est utilisée: [[[EXEC [UTE] Nom_name [; Number] [[[@ Parameter_Name \u003d] (valeur | @_Name_Name) [Sortie] | [Défaut]] [, ... n]

Si l'appel à la procédure stockée n'est pas la seule commande dans l'emballage, la présence de la commande Execute est requise. De plus, cette commande est tenue d'appeler la procédure de l'organe d'une autre procédure ou de déclenchement.

Utilisation du mot-clé de sortie Lorsque vous appelez la procédure est résolu uniquement pour les paramètres annoncés lors de la création d'une procédure avec une sortie de mots clés.

Lorsque vous appelez une procédure, le mot clé par défaut est spécifié pour le paramètre, la valeur par défaut sera utilisée. Essentiellement, le mot spécifié par défaut n'est autorisé que pour ces paramètres pour lesquels la valeur par défaut est définie.

À partir de la syntaxe de la commande Execute, il est clair que les noms de paramètres peuvent être omis lorsque la procédure est appelée. Cependant, dans ce cas, l'utilisateur doit spécifier les valeurs des paramètres dans le même ordre, dans lequel ils ont été répertoriés lors de la création d'une procédure. Attribuez la valeur par défaut au paramètre en le transmettant simplement lorsqu'il est répertorié, il est impossible. Si les paramètres sont nécessaires pour lesquels la valeur par défaut est définie, une indication de nom de paramètre suffisamment explicite lors de l'appel de la procédure stockée. De plus, de cette manière, vous pouvez énumérer les paramètres et leurs valeurs dans un ordre arbitraire.

Notez que lorsque vous appelez la procédure, les noms des paramètres avec des valeurs ou uniquement les valeurs sans le nom du paramètre sont indiqués. Leur combinaison n'est pas autorisée.

Utilisez le retour dans la procédure stockée

Vous permet de quitter la procédure à tout moment de la condition spécifiée et vous permet également de transférer le résultat de la procédure par le numéro par lequel vous pouvez juger de la qualité et de la correction de la procédure. Un exemple de création d'une procédure sans paramètres:

Créer une procédure Count_Books comme Select Count (CODE_BOOBOOK) à partir de livres Go

Exercice 1.

Exec comté_books.

Vérifiez le résultat.

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

Créer la procédure Nombre_Books_Pages @cout_pages @cout_pages comme INT Sélectionnez Count (CODE_Book) à partir de livres où pages\u003e \u003d @count_pages Go

Tâche 2.. Créer cette procédure Dans la section Procédures stockées des données DB_Books via l'utilitaire SQL Server Management Studio Studio. Exécutez-le à l'aide de la commande

Exec comptage_books_pages 100.

Vérifiez le résultat.

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

Créer la procédure comptage_books_title @count_pages comme INT, @title comme Char (10) Comme Select Count (CODE_BOOKBOO) à partir de livres Où pages\u003e \u003d @count_pages et titre_book comme @Title Go

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

Exec comté_books_title 100, "p%"

Vérifiez le résultat.

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

Créer la procédure Nombre_Books_itogo @Count_Pages Int, @title Char (10), @ITOGO INT SORTIE COMME SELECT @ITOGO \u003d Count (CODE_Book) à partir de livres Où pages\u003e \u003d @count_pages et titre_book comme @ Title Go

Tâche 4. Créez cette procédure dans la section Procédures stockées des données DB_Books via l'utilitaire SQL Server Management Studio. Exécuter à l'aide de la commande SET:

SQL\u003e Déclarez @q comme INT Exec compte_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 de retour:

Créer une procédure Checkname @param Int comme si (Sélectionnez Nom_Author d'auteurs où Code_Author \u003d @param) \u003d "Pouchkin A." Retour 1 d'autre retour 2

Tâche 5. Créez cette procédure dans la section Procédures stockées des données DB_Books via l'utilitaire SQL Server Management Studio. Exécutez-le en utilisant Commandes:

Déclare @return_status Int Exec @return_status \u003d Checkname 1 Sélectionnez "Statut de retour" \u003d @return_status

Un exemple de création d'une procédure sans paramètres pour augmenter la valeur de champ clé dans le tableau des achats 2 fois:

Créer proc ute update_proc comme achats de mise à jour SET CODE_PURCHASE \u003d CODE_PURCHASE * 2

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

Exec update_proc

Un exemple de procédure avec un paramètre d'entrée pour toutes les informations sur un auteurs spécifiques:

Créer Proc Select_Author @k Char (30) en sélectionnant * des auteurs où nom_author \u003d @k

Tâche 7.

EXEC SELECT_AUTHOR "POUTCHIN A.S." ou select_author @ k \u003d "pushkin a.s." ou exécuté select_author @ k \u003d "pushkin a.s."

Un exemple de création d'une procédure avec un paramètre d'entrée et une valeur par défaut pour augmenter la valeur de champ clé dans le tableau des achats à un nombre de fois spécifié (par défaut 2 fois):

Créer proc ute update_proc @p int \u003d 2 comme mise à jour des achats de jeu de fichiers_purchase \u003d 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 des données DB_Books via l'utilitaire SQL Server Management Studio. Exécutez-le en utilisant Commandes:

Exec Update_Proc 4 ou Exec Update_Proc @p \u003d 4 ou Exec update_proc - La valeur par défaut est utilisée.

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

Créer proc compte_purchase @ d1 smalldateTime, @ d2 smalldateTime, @c int sortie comme SELECT @ C \u003d COUNT (code_Purchase) à partir d'achats où Date_order entre @ d1 et @ d2 Set @c \u003d isnull (@c, 0)

Tâche 9. Créez cette procédure dans la section Procédures stockées des données DB_Books via l'utilitaire SQL Server Management Studio. Exécutez-le en utilisant Commandes:

Déclarez @ c2 int EXEC Count_Purchases '01 - juin 2006 ', '01 - Jul- 2006', @ C2 Sortie Select @ C2

Options de K. travail de laboratoire №4

Général. Dans l'utilitaire SQL Server Management Studio pour créer nouvelle page Pour le code ("Créer une requête"). Fabriquez par programme une base de données Active DB_Books à l'aide de l'opérateur d'utilisation. Créez des procédures stockées à l'aide des opérateurs Créer des procédures et déterminez indépendamment les noms des procédures. Chaque procédure fonctionnera par une requête SQL effectuée dans le deuxième ouvrage de laboratoire. De plus, le code de requête SQL doit être modifié de manière à pouvoir être transmis aux valeurs de champ pour lesquelles la recherche est effectuée.

Par exemple, la tâche initiale et la demande en laboratoire Numéro 2:

/ * Choisissez parmi les fournisseurs de livres de référence (Table des livraisons) Noms, téléphones et auberge (nom_company, téléphone et auberge), dont le nom de la société (nom_company champ) "Oao World".

Sélectionnez Nom_Company, Téléphone, Inn des livraisons où Name_Company \u003d "Oao World"

* / - Cette procédure de travail sera créée:

Créer Proc Select_Name_Company @compan Char (30) As Select Name_Company, Téléphone, Inn des livraisons Où_company \u003d @company

-L'arrier de la procédure est utilisé par la commande:

Exec Select_Name_Company "OJSC World"

Liste de tâches

Dans l'utilitaire SQL Server Management Studio, créez un nouveau programme. Programmation de manière programmatique Une base de données individuelle active créée dans le numéro de travail de laboratoire 1, à l'aide de l'opérateur d'utilisation. Créez des procédures stockées à l'aide des opérateurs Créer des procédures et déterminez indépendamment les noms des procédures. Chaque procédure effectuera une requête SQL présentée sous la forme de tâches distinctes par des options.

Option 1

1. Pour retirer une liste d'employés ayant au moins un enfant.

2. Retirer une liste d'enfants qui ont donné des cadeaux à la période spécifiée.

3. retirer une liste de parents qui ont des mineurs enfants.

4. Retirer des informations sur les cadeaux avec un coût de plus que le numéro spécifié trié par date.

Option 2.

1. Liste des instruments avec type spécifié.

2. Retirez le nombre de périphériques réparés et le coût total des réparations à l'assistant spécifié.

3. Apporter la liste des propriétaires d'instruments et le nombre de leurs appels, triés par le nombre d'appels descendant.

4. Affiche des informations sur les maîtres avec une décharge supérieure au nombre spécifié ou avec une date de réception inférieure à la date spécifiée.

Option 3.

2. Pour afficher une liste des codes de vente pour lesquels les couleurs vendues sont supérieures au nombre spécifié.

3. Retirez la vente, le montant, le vendeur et la fleur au code de vente spécifié.

4. Affichez une liste de couleurs et de note pour des fleurs avec une hauteur du nombre spécifié ou de la floraison.

Option 4.

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

2. Afficher une liste de livraisons, qui vendaient plus que le nombre spécifié du même médicament.

3. Pour retirer la date de livraison, le montant, le titre de la tête du fournisseur et le nom du médicament sur le code de réception plus que le nombre spécifié.

Option 5.

2. Affichez une liste d'équipements écrits pour la raison indiquée.

3. Pour retirer la date de réception, le nom de l'équipement, le nom de la responsabilité et la date de radiation de l'équipement déconnecté à la période spécifiée.

4. Affichez la liste des équipements avec le type spécifié ou avec la date de réception plus qu'une valeur spécifique.

Option 6.

1. Liste des plats avec poids plus que le nombre spécifié.

2. Pour retirer une liste de produits, dans le titre dont le fragment spécifié du mot est trouvé.

3. Retirez le volume du produit, le nom du plat, le nom du produit avec le code du plat de la spécifiée sens initial Sur une valeur finale définie.

4. Affichez le processus de cuisson et le nom du plat avec la quantité de glucides est supérieur à une certaine valeur ou la quantité de calories est supérieure à la valeur spécifiée.

Option 7.

1. Retirer une liste d'employés avec ce poste.

3. Pour retirer la date d'enregistrement, le type de document, le nom du greffier et le nom de l'organisation pour les documents enregistrés dans la période spécifiée.

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

Option 8.

1. Retirer une liste d'employés avec la raison spécifiée du licenciement.

3. Pour retirer la date d'enregistrement, la raison du licenciement, le nom de l'employé pour les documents enregistrés dans la période spécifiée.

Option 9.

1. Retirer une liste d'employés qui brave les vacances du type spécifié.

2. Fil une liste de documents à la date d'enregistrement à la période spécifiée.

3. Pour retirer la date d'enregistrement, le type de vacances, la FIO de l'employé pour les documents enregistrés dans 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 10.

1. Retirer une liste d'employés avec ce poste.

2. Pour retirer une liste de documents dans lesquels le fragment spécifié du mot est trouvé.

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

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

Option 11.

1. Pour retirer une liste des employés nommés à la position spécifiée.

2. Fil une liste de documents à la date d'enregistrement à la période spécifiée.

3. Pour retirer la date d'enregistrement, la position, le nom complet des documents enregistrés à 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. Pour retirer une liste de personnes Whoighing Equipment pour embaucher et le nombre de leurs appels, triés par le nombre d'appels descendant.

Option 13.

1. Affichez la liste des équipements avec le type spécifié. 2. Affichez la liste des équipements qui ont écrit un employé spécifique.

3. Affichez la quantité d'équipement écrite groupé par équipement.

4. Afficher des informations sur les employés à la date d'emploi plus d'une date précise.

Option 14.

1. Affichez la liste des fleurs avec le type de feuille spécifié.

2. Fil une liste des codes de revenu pour lesquels les couleurs vendues sont plus qu'une certaine valeur.

3. Supprimer la date de réception, le montant, les noms du fournisseur et des couleurs sur un code spécifique du fournisseur.

4. Liste des couleurs et des variétés pour les fleurs avec une hauteur de plus d'un certain nombre ou d'une floraison.

Option 15.

1. Pour retirer une liste de clients qui ont atteint les chiffres pendant la période spécifiée.

2. retirer un montant total de paiements pour chaque client.

3. Retirez la date d'arrivée, le type de pièce, les noms des clients enregistrés à la période spécifiée.

4. Fil une liste des clients enregistrés dans un certain type.

Option 16.

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

2. Publiez une liste d'équipements qui a pris un certain client à louer.

3. Retirer une liste de personnes Qui alerte le matériel à embaucher et le nombre de leurs appels triés par le nombre d'appels descendant.

4. Désapprenez les informations triés par adresses.

Option 17.

1. Pour afficher une liste de valeurs avec les achats coûtant plus qu'une certaine valeur ou une période de garantie plus que le nombre spécifié.

2. Apporter une liste des emplacements de valeurs matérielles, dans le titre dont le mot spécifié est trouvé.

3. Supprimez la valeur de la valeur des valeurs avec le code dans la plage spécifiée.

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

Option 18.

1. Affichez une liste des travaux de réparation effectués par un maître spécifique.

2. Pour retirer une liste des étapes des travaux figurant dans le travail, dans le titre dont le mot spécifié est trouvé.

3. Dériver le montant du coût des étapes de réparation des travaux de travail avec le code dans la plage spécifiée.

4. Fil une liste de maîtres de la date d'emploi dans la plage spécifiée.

Option 19.

1. retirer une liste de médicaments avec une certaine indication.

2. Fil une liste de chèques de chèques pour lesquels plus d'un certain nombre de médicaments vendus.

3. Pour retirer la date de vente, le montant, le nom complet et la remède pour un chèque avec le numéro spécifié.

4. Affichez une liste de médicaments et unités de mesure pour les médicaments avec un emballage dans l'emballage, plus que le nombre spécifié ou le code du médicament est inférieur à une certaine valeur.

Option 20.

1. Retirer une liste d'employés avec ce poste.

2. Pour retirer une liste de documents dans lesquels le fragment spécifié du mot est trouvé.

3. Pour retirer la date d'enregistrement, le type de document, le nom complet et le fait de l'exécution des documents enregistrés dans la période spécifiée.

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

Dans MySQL 5, de nombreuses nouvelles fonctionnalités sont les plus lourdes de la création de procédures stockées. Dans cette leçon, je vous dirai ce qu'ils représentent, ainsi que de la manière dont ils peuvent vous faciliter la vie.

introduction

La procédure stockée est une méthode d'encapsulation d'actions récurrentes. Dans les procédures stockées, vous pouvez déclarer des variables, gérer des flux de données et appliquer d'autres techniques de programmation.

La raison de leur création est claire et confirmée par une utilisation fréquente. D'autre part, si vous parlez à ceux qui travaillent avec eux de manière irrégulière, les opinions sont divisées en deux flancs complètement opposés. N'oubliez pas.

Par

• Séparation de la logique avec d'autres applications. Procédures stockées encapsulez la fonctionnalité; Cela garantit la connectivité aux données et les gérer entre différentes applications.
• Isolation utilisateur des tables de base de données. Cela vous permet d'accéder aux procédures stockées, mais pas aux données elles-mêmes.
• Fournit un mécanisme de sécurité. Conformément au paragraphe précédent, si vous ne pouvez accéder aux données uniquement via des procédures stockées, personne d'autre ne peut effacer vos données via la commande SQL Supprimer.
• Améliorer les performances à la suite de la réduction trafic réseau. Avec des procédures stockées, de nombreuses demandes peuvent être combinées.

Vs

• Charge accrue sur le serveur de base de données en raison du fait que la plupart des travaux sont effectués sur la partie serveur et plus petit sur le client.
• Il y aura beaucoup de choses à penser. Vous devrez apprendre syntaxe MySQL expressions d'écriture de leurs procédures stockées.
• Vous dupliquez la logique de votre application à deux endroits: code serveur et code des procédures stockées, compliquant ainsi le processus de manipulation des données.
• La migration avec un SGBS sur un autre (DB2, SQL Server, etc.) peut entraîner des problèmes.

L'outil dans lequel je travaille est appelé navigateur de requête MySQL, il est assez standard pour interagir avec des bases de données. Outil ligne de commande MySQL est un autre excellent choix. Je vous parle de cela pour la raison pour laquelle la phpmyadmin préférée de tout le monde ne prend pas en charge l'exécution de procédures stockées.

Au fait, j'utilise la structure élémentaire des tables afin que vous vous sentiez plus facile de comprendre ce sujet. Après tout, je parle de procédures stockées et ils sont assez complexes de plonger dans la structure volumineuse des tables.

Étape 1: Nous mettons le limiteur

Le limiteur est un caractère ou une chaîne de symbole utilisée pour indiquer le client MySQL que vous avez terminé l'écriture de l'expression SQL. L'éternité complète était le limiteur était un symbole d'un point-virgule. Cependant, des problèmes peuvent survenir, car il peut y avoir plusieurs expressions dans la procédure stockée, chacune devoir se terminer par un point-virgule. Dans cette leçon, j'utilise une chaîne "//" comme limiteur.

Étape 2: Comment travailler avec des procédures stockées

Créer une procédure stockée

Délimiter // Créer une procédure `p2`` () Langue SQL SQL Security Security Security Security Security Security Défileur de sécurité" Une procédure "Commencez à sélectionner" Hello World! "; FINIR //

La première partie du code crée une procédure stockée. Suivant - contient des paramètres facultatifs. Ensuite, il y a un nom et, enfin, le corps de la procédure elle-même.

Les noms des procédures stockées sont sensibles au registre. Vous ne pouvez pas non plus créer plusieurs procédures avec le même nom. À l'intérieur de la procédure stockée ne peut pas être des expressions qui modifient la base de données elle-même.

4 Caractéristiques de la procédure stockée:

• Langue: Pour effectuer la portabilité, le SQL par défaut est spécifié.
• Déterministe: si la procédure renvoie le même résultat tout le même résultat et prend les mêmes paramètres entrants. Ceci est pour le processus de réplication et d'inscription. La valeur par défaut n'est pas déterministe.
• Security SQL: Pendant l'appel, les droits de l'utilisateur sont vérifiés. Invoker est un utilisateur causant une procédure stockée. Définisseur est la procédure "Créateur". Valeur par défaut - Définisseur.
• Commentaire: Pour la documenciation, la valeur par défaut est ""

Appel de procédure stockée

Pour invoquer une procédure stockée, vous devez imprimer un mot-clé d'appel, puis le nom de la procédure et spécifier les paramètres (variables ou valeurs) entre parenthèses. Les supports sont requis.

Appeler stored_procedure_name (param1, param2, ....) Appelez la procédure d'appel1 (10, "Paramètre de chaîne", @parameter_var);

Modifier la procédure stockée

MySQL a modifier l'expression de procédure pour modifier les procédures, mais elle convient uniquement à ne changer certaines caractéristiques. Si vous devez modifier les paramètres ou le corps de la procédure, vous devez le supprimer et la créer à nouveau.

Suppression de la procédure stockée

La procédure de chute si existe P2;

Ceci est une simple équipe. Une expression si elle existe une erreur si une telle procédure n'existe pas.

Étape 3: Paramètres

Voyons comment les paramètres peuvent être transmis à la procédure stockée.

• Créer procédure proc1 (): Liste vide des paramètres
• Créer la procédure ProC1 (en type de données VARNAME): un paramètre entrant. Le mot est facultatif, car les paramètres par défaut sont (entrants).
• Créer une procédure PROC1 (OUT VARNAME Type de données): Un paramètre renvoyé.
• Créer la procédure PROC1 (Type de données VARNAME INOUT): un paramètre en même temps entrant et retourné.

Naturellement, vous pouvez définir plusieurs paramètres de types différents.

Un exemple de paramètre dans

Délimiteur // Créer une procédure `proc_in` (en var1 int) Commencez à sélectionner Var1 + 2 en conséquence; FINIR //

Exemple de paramètre

Délimiteur // Créer une procédure `proc_out` (Out Var1 Varchar (100)) Début Set Var1 \u003d" Ceci est un test "; FINIR //

Un exemple du paramètre Inout

Délimiteur // Créer une procédure `proc_inout` (Out Var1 int) Commencez ensemble VAR1 \u003d VAR1 * 2; FINIR //

Étape 4: Variables

Maintenant, je vais vous apprendre à créer des variables et à les maintenir dans les procédures. Vous devez les déclarer clairement au début du bloc de début / fin, ainsi que leurs types de données. Une fois que vous avez déclaré une variable, vous pouvez l'utiliser au même endroit où les variables de session, les littéraux ou les noms de haut-parleurs.

La syntaxe de la déclaration de la variable ressemble à ceci:

Déclarez Varname Type de données par défaut ValueValue;

Déclarons plusieurs variables:

Déclarez A, B INT par défaut 5; Déclarez STR Varchar (50); Déclarez aujourd'hui horodatage par défaut actuel_date; Déclarez v1, v2, v3 tinyint;

Travailler avec des variables

Une fois que vous avez déclaré une variable, vous pouvez spécifier la valeur à l'aide de SET ou Sélectionner des commandes:

Délimiter // Créer une procédure `Var_Proc` (dans Paramstr Varchar (20)) Commencez à déclarer A, B INT par défaut 5; Déclarez STR Varchar (50); Déclarez aujourd'hui horodatage par défaut actuel_date; Déclarez v1, v2, v3 tinyint; Insérer dans la table1 valeurs (a); Set str \u003d "Je suis une chaîne"; Sélectionnez Concat (Str, Paramstr), aujourd'hui à partir de TABLEA2 où B\u003e \u003d 5; FINIR //

Étape 5: Structures de gestion des flux

MySQL prend en charge si, cas, itérer, loop en boucle, tout en répétant la gestion des flux dans la procédure stockée. Nous allons examiner comment utiliser si, le cas et pendant, comme ils sont le plus souvent utilisés.

Si conçu

En utilisant le design IF, nous pouvons effectuer les tâches contenant les conditions:

Délimiter // Créer la procédure `proc_if` (dans param1 int) commence à déclarer variable1 int; Définir la variable1 \u003d param1 + 1; Si variable1 \u003d 0 theten Sélectionnez la variable1; FIN SI; Si param1 \u003d 0 theten Sélectionnez "Valeur du paramètre \u003d 0"; Select Select "Valeur des paramètres<> 0 "; fin si; fin //

Conception de cas

L'affaire est une autre méthode de vérification des conditions et de choisir une solution appropriée. C'est un excellent moyen de remplacer plusieurs des conceptions. La conception peut être décrite de deux manières, offrant une flexibilité pour contrôler plusieurs expressions conditionnelles.

Délimiter // Créer la procédure `proc_case` (dans param1 int) commence à déclarer variable1 INT; Définir la variable1 \u003d param1 + 1; Variable de cas1 lorsque 0 puis insérez dans la table1 Valeurs (param1); Quand 1 puis insérer dans la table1 valeurs (variable1); Sinon insérer dans la table1 valeurs (99); Cas de fin; FINIR //

Délimiter // Créer la procédure `proc_case` (dans param1 int) commence à déclarer variable1 INT; Définir la variable1 \u003d param1 + 1; Cas lorsque la variable1 \u003d 0 puis insérez dans la table1 Valeurs (param1); Lorsque la variable1 \u003d 1, insérez dans la table1 Valeurs (variable1); Sinon insérer dans la table1 valeurs (99); Cas de fin; FINIR //

Construction de temps

Techniquement, il existe trois types de cycles: pendant le cycle, le cycle de boucle et répéter le cycle. Vous pouvez également organiser un cycle en utilisant la technique de programmation "Darth Vader": expressions goto. Voici un exemple de cycle:

Délimiter // Créer la procédure` proc_while` (dans param1 int) commencez à déclarer variable1, variable2 INT; Définir la variable1 \u003d 0; Tandis que variable1.< param1 DO INSERT INTO table1 VALUES (param1); SELECT COUNT(*) INTO variable2 FROM table1; SET variable1 = variable1 + 1; END WHILE; END //

Étape 6: curseurs

Les curseurs sont utilisés pour transmettre la demande renvoyée par la ligne, ainsi que le traitement de chaque rangée.

MySQL prend en charge les curseurs dans les procédures stockées. Voici une brève syntaxe de la création et de l'utilisation du curseur.

Déclarez le curseur de noms de curseur pour choisir ...; / * Cursor AD et le remplissant * / Déclarez Continuer le gestionnaire pour non trouvé / * Que faire, quand il n'y a plus d'entrées * / Ouvrez le nom du curseur; / * Ouvrez le curseur * / récupérer le désign du curseur dans la variable [, la variable]; / * Attribuez une valeur variable égale à la valeur de la colonne de courant * / Fermer le nom du curseur; / * Fermer le curseur * /

Dans cet exemple, nous allons passer des opérations simples à l'aide du curseur:

Délimiteur // Créer une procédure `proc_cursor` (OUT PARAM1 INT) commence à déclarer A, B, C INT; Déclarez le curseur CUR1 pour sélectionner COL1 de TABLE1; Déclarez le gestionnaire de poursuite pour non trouvé ensemble B \u003d 1; Ouvrir Cur1; Ensemble B \u003d 0; Ensemble C \u003d 0; Tandis que b \u003d 0 fetch Cur1 dans un; Si b \u003d 0 puis réglez c \u003d c + a; FIN SI; Se terminer pendant; Fermer CUR1; Définir param1 \u003d c; FINIR //

Les curseurs ont trois propriétés que vous devez comprendre pour éviter d'obtenir des résultats inattendus:

• Non sensible: Il était une fois une fois, le curseur n'imposera pas de modifications dans la table qui s'est produite plus tard. En fait, MySQL ne garantit pas ce que le curseur sera mis à jour, alors ne le portez pas.
• En savoir que: les curseurs ne peuvent pas être modifiés.
• Sans rembobinage: le curseur est capable de ne passer que dans une direction - en avant, vous ne pourrez pas sauter les cordes sans les choisir.

Conclusion

Dans cette leçon, je vous ai introduit les bases de la collaboration avec les procédures stockées et avec des propriétés spécifiques qui lui sont associées. Bien sûr, vous devrez approfondir les connaissances dans des domaines tels que la sécurité, les expressions SQL et l'optimisation avant de devenir un véritable gourous de procédure MySQL.

Vous devez calculer quels avantages vous donneront l'utilisation de procédures stockées dans votre application spécifique et ne crée que les procédures nécessaires. En général, j'utilise des procédures; À mon avis, ils devraient être mis en œuvre dans des projets en conséquence de leur sécurité, de leur service de code et de la conception générale. En outre, n'oubliez pas que les procédures MySQL fonctionnent toujours. Attendez-vous à des améliorations concernant la fonctionnalité et les améliorations. S'il vous plaît n'hésitez pas à partager des opinions.