Cours SQL les différents types de procédures stockées

Cours SQL les différents types de procédures stockées, tutoriel & guide de travaux pratiques en pdf.

Les différents types de procédures stockées

Procédure stockée système
Ensemble de procédures stockées fournies par SQL Server pour la réalisation d’opérations telles que l’extraction d’informations du catalogue système ou l’exécution de tâches d’administration.
Nombre d’activités administratives dans Microsoft® SQL Server™ 2000 s’exécutent à l’aide d’un type spécial de procédure connu sous le nom de procédure stockée système. Les procédures stockées système sont créées et enregistrées dans la base de données master et ont le préfixe sp_. Les procédures stockées du système peuvent s’exécuter depuis n’importe quelle base de données, sans avoir à qualifier complètement le nom de la procédure stockée, en utilisant le nom de base de données master.
Il est fortement recommandé de ne pas créer de procédures stockées avec le préfixe sp_. SQL Server recherche toujours une procédure stockée en commençant par sp_ dans l’ordre suivant :
1. elle existe dans la base de données master ;
2. ensuite, en fonction des éventuels identificateurs fournis (nom de base de données ou propriétaire) ;
3. enfin, avec dbo comme propriétaire si aucun propriétaire n’est spécifié.
Par conséquent, bien qu’il puisse exister dans la base de données en cours une procédure stockée créée par l’utilisateur ayant le préfixe sp_, la base de données master est toujours analysée la première, même si la procédure stockée est qualifiée avec le nom de la base de données.

Informations sur les procédures stockées
Pour afficher le texte utilisé pour créer la procédure, exécutez sp_helptext dans la base de données dans laquelle la procédure se trouve en passant le nom de la procédure en paramètre.
Pour obtenir une liste des objets référencés par une procédure, utilisez sp_depends.
Pour renommer une procédure, utilisez sp_rename
Important Si une procédure stockée créée par un utilisateur porte le même nom qu’une procédure stockée système, celle de l’utilisateur ne s’exécutera jamais.

Procédures stockées temporaires
Les procédures stockées temporaires privées et globales, comme les tables temporaires, peuvent être créées en ajoutant les préfixes # et # # à leur nom. # désigne une procédure stockée temporaire locale, et # #, une procédure stockée temporaire globale. Ces procédures n’existent plus après l’arrêt de SQL Server.
Les procédures stockées temporaires locales sont disponibles au sein d’une seule session d’utilisateur. Tandis que les procédures stockées temporaires globales sur l’ensemble des sessions d’utilisateur.
Les procédures stockées temporaires sont utiles lorsque vous vous connectez à des versions antérieures de SQL Server qui ne prennent pas en charge la réutilisation des plans d’exécution des instructions ou lots d’instructions Transact-SQL.
Pour créer et exécuter les procédures stockées temporaires :
Create procedure # #procedure_name

Procédures stockées distantes
Les procédures stockées distantes sont une ancienne fonctionnalité de Microsoft® SQL Server™ 2000. Leur fonctionnalité dans Transact-SQL est limitée à l’exécution d’une procédure stockée sur une installation SQL Server distante. Les requêtes distribuées introduites dans la version 7.0 de SQL Server prennent en charge cette possibilité ainsi que l’accès à des tables dans des sources de données OLE DB hétérogènes directement à partir d’instructions Transact-SQL locales. Au lieu d’utiliser un appel de procédure stockée distante sur SQL Server 2000, utilisez des requêtes distribuées et une instruction EXECUTE pour exécuter une procédure stockée sur un serveur distant.
Une instance SQL Server 2000 peut envoyer et recevoir des appels de procédures stockées distantes à d’autres instances de SQL Server 2000 et SQL Server version 7.0. Une instance SQL Server 2000 peut également envoyer des appels de procédures stockées distantes vers des instances SQL Server 6.0 ou 6.5 et en recevoir. Un serveur exécutant SQL Server 2000 peut recevoir des appels de procédures stockées distantes d’une instance SQL Server 4.21a, mais l’instance SQL Server 2000 ne peut pas faire des appels de procédures stockées distantes
à l’instance SQL Server 4.21a. L’instance SQL Server 4.21a ne peut pas reconnaître la version du flux de données tabulaires (TDS, Tabular Data Stream) utilisée par SQL Server 2000.

Procédures stockées étendues
Les procédures stockées étendues vous permettent de créer vos propres routines externes dans un langage de programmation comme le langage C. Les procédures stockées étendues apparaissent aux utilisateurs comme des procédures stockées normales et s’exécutent de la même façon. Des paramètres peuvent être passés à une procédure stockée étendue pour renvoyer des résultats et un état. Les procédures stockées étendues permettent d’étendre les fonctionnalités de Microsoft® SQL Server™ 2000.
Les procédures stockées étendues sont des bibliothèques de liaison dynamique (DLL, dynamic-link library) que SQL Server peut charger et exécuter dynamiquement. Elles s’exécutent directement dans l’espace d’adresse de SQL Server et sont programmées au moyen de l’API Open Data Services de SQL Server.
Une fois que la procédure stockée étendue est écrite, les membres du rôle de serveur fixe sysadmin peuvent l’inscrire dans SQL Server, puis donner l’autorisation de l’exécuter à d’autres utilisateurs. Les procédures stockées étendues ne peuvent être ajoutées qu’à la base de données master.
Les procédures stockées étendues sont généralement identifiées par le préfixe xp_
3. Codification des procédures stockée
3.1. Création des procédures stockées
Vous pouvez créer une procédure stockée en utilisant l’instruction Transact-SQL CREATE PROCEDURE. Lisez les informations ci-dessous avant de créer une procédure stockée.
• L’instruction CREATE PROCEDURE ne peut pas s’utiliser conjointement avec d’autres instructions SQL dans un même lot d’instructions.
• L’autorisation de créer des procédures stockées revient par défaut au propriétaire de la base de données, qui peut la transmettre à d’autres utilisateurs.
• Les procédures stockées sont des objets de base de données et leur nom doit respecter les règles gouvernant les identificateurs.
• Vous ne pouvez créer une procédure stockée que dans la base de données en cours. Pour créer une procédure stockée, vous devez préciser :
• es paramètres d’entrée et de sortie de la procédure ou du lot appelant ;
• les instructions de programmation qui exécutent les opérations dans la base de données, y compris l’appel à d’autres procédures ;
• la valeur d’état renvoyée à la procédure ou au lot appelant pour indiquer la réussite ou l’échec et, dans ce cas, la raison de l’échec.
Syntaxe
CREATE PROC [ EDURE ] procedure_name [ ; number ]
{ @parameter data_type } AS sql_statement [ …n ]
Arguments
procedure_name
Nom de la nouvelle procédure stockée. Les noms des procédures doivent respecter les règles applicables aux identificateurs et doivent être uniques dans la base de données et pour son propriétaire. ;number
Nombre entier facultatif utilisé pour regrouper les procédures de même nom afin qu’elles puissent être supprimées ensemble à l’aide d’une seule instruction DROP PROCEDURE. Par exemple, les procédures utilisées avec une application appelée order peuvent être nommées orderproc;1, orderproc;2, etc. L’instruction DROP PROCEDURE orderproc abandonne le groupe tout entier.
@parameter
Un paramètre de la procédure. Vous pouvez déclarer un ou plusieurs paramètres dans une instruction CREATE PROCEDURE. La valeur de chaque paramètre déclaré doit être fournie par l’utilisateur lors de l’exécution de la procédure (sauf si vous définissez une valeur par défaut pour le paramètre). Une procédure stockée peut comprendre au maximum 2100 paramètres.
Spécifiez un nom de paramètre en plaçant le signe @ comme premier caractère. Ce nom doit respecter les règles gouvernant les identificateurs. Un paramètre est local à une procédure, vous pouvez donc utiliser le même nom dans d’autres procédures.
data_type
Type de données du paramètre. Tous les types de données y compris les types text, ntext et image, peuvent être utilisés comme paramètre dans une procédure stockée.
Espace réservé qui indique que plusieurs instructions Transact-SQL peuvent être incluses dans cette procédure.

1. Définition et avantages de procédures stockées
2. Les différents types de procédures stockées
3. Codification des procédures stockées
3.1. Création des procédures stockées
3.2. Modification des procédures stockées
3.3. Suppression des procédures stockées
3.4. Appel d’une procédure stockées
3.5. Définition des paramètres, traitement des erreurs
3.6. Utilisation de NOCOUT, EXISTS
4. Exemples
5. Exercices