Procédures Stockées
|
Procédures Stockées |
Pour créer une procédure :
Dans SQL, pour créer une procédure, vous démarrez avec l'expression CREATE PROCEDURE. Vous pouvez également utiliser CREATE PROC. Les deux expressions produisent le même résultat. Tout comme dans votre base de données, vous devez nommer votre procédure :
Après le nom de la procédure, tapez le mot-clé AS. La section, le groupe de mots, ou un groupe de lignes après le mot-clé AS est appelé le corps de la procédure. Il énonce ce que vous voulez que la procédure fasse ou ce que vous souhaitez produire. Sur cette base, la syntaxe la plus simple de la création d'une procédure est la suivante : CREATE PROCEDURE ProcedureName AS Body of the Procedure Il est important de garder à l'esprit qu'il existe de nombreuses autres questions liées à la création d'une procédure, mais pour l'instant, nous allons examiner cette syntaxe. Après avoir créé la procédure, vous devez la stocker sous la forme d'un objet dans votre base de données. Pour le faire, dans la barre d'outils de l'Editeur SQL, vous pouvez cliquer sur le bouton Execute
Sous la forme d'un objet de base de données SQL Server régulière, vous pouvez modifier une procédure stockée sans recréer. Pour le faire :
Dans chaque cas, un code squelette serait produit pour vous.Vous pouvez ensuite modifier pour créer une nouvelle version de votre procédure stockée. Après avoir modifié le code, vous pouvez exécuter l'instruction SQL pour mettre à jour la procédure stockée. Dans SQL, la formule de base pour modifier une procédure stockée est : ALTER PROCEDURE ProcedureName AS Body of Procedure
L'une des plus grandes caractéristiques d'une procédure stockée est qu'elle est considérée comme un objet dans son propre droit. Par conséquent, après l'avoir créée, si vous n'en avez plus besoin, vous pouvez vous en débarrasser. Il existe différents types de procédures stockées, dont certaines sont considérées comme temporaires. Ces types de procédures se suppriment eux-mêmes lorsque ce n'est plus nécessaire, comme lorsque la personne qui a créé la procédure stockée se déconnecte de la base de données ou arrête l'ordinateur. Sinon, pour supprimer une procédure, vous pouvez utiliser l'Explorateur d'objets ou SQL. Comme nous l'avons vu avec des tables, même si vous créez une procédure à l'aide de l'Explorateur d'objets, vous pouvez la supprimer en utilisant SQL et vice versa. Pour supprimer une procédure dans l'Explorateur d'objets, après avoir élargi sa base de données, ses fonctionnalités de programmation et sa procédure stockée de nœuds, vous pouvez cliquez avec le bouton droit de la souris sur la procédure stockée, puis cliquez sur Supprimer. Vous pouvez également cliquer dessus dans l'Explorateur d'objets pour la sélectionner et appuyez sur la touche Suppr. La boîte de dialogue Supprimer un objet vous permettra de prendre une décision. Pour supprimer une procédure dans SQL, la syntaxe à utiliser est la suivante : DROP PROCEDURE ProcedureName Bien entendu, vous devez vous assurer que vous êtes dans la base de données de droite ainsi que la ProcedureName existe.
La procédure la plus simple, que vous pouvez écrire comprendrait probablement de la sélection des colonnes d'une table. Cela se fait avec l'opérateur SELECT et en appliquant les techniques que nous avons examinées pour l'analyse des données. Par exemple, pour créer une procédure stockée qui tiendra une liste des étudiants d'une table nommée Students, vous créez la procédure comme suit : CREATE PROCEDURE GetStudentIdentification
AS
BEGIN
SELECT FirstName, LastName, DateOfBirth, Gender
FROM Students
END
GO
Outre les opérations SELECT, dans le cadre d'une procédure stockée, vous pouvez effectuer des opérations de base de données que nous avons appliquées jusqu'à présent. Celles-ci incluent la création et la gestion des enregistrements, etc..
Pour obtenir les résultats de la création d'une procédure stockée, vous devez exécuter (en d'autres termes, pour utiliser une procédure stockée, vous devez l'appeler). Pour exécuter une procédure, vous utilisez leMot-clé EXECUTE suivi du nom de la procédure. Bien qu'il existe d'autres problèmes liés à l'exécution d'une procédure, pour l'instant, nous allons examiner que la syntaxe la plus simple pour appeler une procédure est : EXECUTE ProcedureName Au lieu de EXECUTE, vous pouvez également utiliser le mot-clé EXEC : EXEC ProcedureName Par exemple, si vous avez une procédure nommée GetStudentIdentification, pour l'exécuter, vous taperez : EXECUTE GetStudentIdentification Vous pouvez également faire précéder le nom de la procédure avec son schéma, tel que dbo. Voici un exemple : EXECUTE dbo.GetStudentIdentification ; Vous pouvez également faire précéder le nom du schéma avec le nom de la base de données. Voici un exemple : EXECUTE ROSH.dbo.GetStudentIdentification ;
L'un des avantages de l'utilisation d'une procédure stockée est que non seulement elle peut produire la même expression comme nous l'avons vu au cours de l'analyse, mais aussi elle peut stocker une telle expression pour être rappelée à tout moment sans avoir à la réécrire (l'expression). Sur cette base, vous pouvez créer une expression qui combine un premier et un nom pour produire et stocker un nom complet. Voici un exemple : CREATE PROCEDURE GetStudentIdentification
AS
BEGIN
SELECT FullName = FirstName + ' ' + LastName,
DateOfBirth, Gender
FROM Students
END
GO
Une procédure stockée peut également appeler une fonction dans son corps. Pour le faire, suivez les mêmes règles que nous avons examinées pour appeler des fonctions au cours de l'analyse de données. Voici un exemple d'une procédure stockée qui appelle une fonction : USE ROSH ;
GO
CREATE PROCEDURE GetStudentsAges
AS
BEGIN
SELECT FullName = FirstName + N ' ' + LastName,
DATEDIFF(year, DateOfBirth, GETDATE()) AS Age,
Gender
FROM Students
END
GO
Voici un exemple de l'exécution de la procédure :
Toutes les procédures stockées que nous avons créées et utilisées jusqu'à présent supposées que les valeurs dont ils avaient besoin étaient déjà dans une table de la base de données. Dans certains cas, il se peut que vous deviez créer une procédure stockée qui implique des valeurs qui ne font pas partie de la base de données. Sur un tel scénario, pour la procédure de transporter son affectation, vous fournirez une ou plusieurs valeurs. Une valeur externe qui est fournie pour une procédure stockée est appelée un paramètre. Lorsque vous créez une procédure stockée, vous devez également créer le paramètre si vous le jugez nécessaire. Lorsque la création d'une procédure est équipée d'un paramètre, il est dit que la procédure stockée accepte un argument. Une procédure stockée peut également prendre plusieurs arguments. Lorsque vous exécutez une procédure stockée qui prend un ou plusieurs arguments, vous devez fournir une valeur pour chaque argument. Dans ce cas, on dit que vous passez une valeur de l'argument. Il y a des cas lorsque vous n'avez pas à fournir un argument.
Pour créer une procédure stockée qui prend un argument, tapez la formule CREATE PROCEDURE ou CREATE PROC suivie du nom de la procédure, puis tapez le nom de l'argument commençant par @. Le paramètre est créé comme une colonne d'une table. Autrement dit, un paramètre doit avoir un nom, un type de données et une longueur facultative. Voici la syntaxe que vous utiliserez : CREATE PROCEDURE ProcedureName @ParameterName DataType AS Body of the Procedure Lors de la mise en oeuvre de la procédure stockée, vous pouvez définir ce que vous voulez faire avec les paramètres, dans le corps de la procédure. Une façon dont vous pouvez utiliser un paramètre consiste à exécuter une requête dont l'utilisateur fournirait le coefficient. Par exemple, imaginez que vous souhaitez créer une procédure qui, dès qu'elle est exécutée, pourrait être fournie avec un sexe, puis elle affichera la liste des élèves de ce genre. Étant donné que vous souhaitez que l'utilisateur spécifie le sexe des étudiants à afficher, vous pouvez créer une procédure stockée qui reçoit le sexe. Voici un exemple : CREATE PROC GetListOfStudentsByGender
@Gdr NVARCHAR(12)
AS
SELECT FirstName, LastName,
DateOfBirth, HomePhone, Gender
FROM Students
WHERE Gender = @ Gdr
Comme je l'ai mentionné déjà, lors de l'exécution d'une procédure stockée qui prend un paramètre, assurez-vous que vous indiquez une valeur pour le paramètre. La syntaxe utilisée est la suivante : EXEC ProcedureName ParameterValue Si le paramètre est Boolean ou numérique, veillez à ce que vous fournissez la valeur 0 ou pour une valeur booléenne ou un autre numéro pour le type numérique. Si le paramètre est un caractère ou une chaîne, saisissez sa valeur unique entre-guillemets. Voici un exemple : EXEC ROSH.dbo.GetListOfStudentsByGender N'Male ' ; Voici un exemple d'exécution :
Notez que nous pourrions avoir omis d' inclure la colonne de genre dans l'instruction puisqu'elle pourrait être implicite à l'utilisateur. Un autre type de procédure stockée peut être fait à prendre plus d'un paramètre. Dans ce cas, créez les paramètres dans la section avant le mot clé AS, séparé par une virgule. La syntaxe que vous utiliseriez est la suivante : CREATE PROCEDURE ProcedureName @ParameterName1 DataType, @ParameterName2 DataType, @ParameterName_n DataType AS Body of the Procedure Voici un exemple : USE ROSH ;
GO
CREATE PROCEDURE IdentifyStudentsByState
@Gdr type nvarchar (20),
@StateOrProvince char(2)
AS
BEGIN
SELECT FullName = LastName + ', N' + FirstName,
DATEDIFF(year, DateOfBirth, GETDATE()) AS Age,
Gender
FROM Students
WHERE (Gender = @Gdr) AND (State = @StateOrProvince)
END
GO
Lorsque vous appelez une procédure stockée qui prend plus d'un paramètre, vous devez toujours fournir une valeur pour chaque paramètre, mais vous avez deux possibilités. La technique la plus simple consiste à fournir une valeur pour chaque paramètre dans l'ordre exact qu'ils apparaissent dans la procédure stockée. Voici un exemple : USE ROSH ; GO EXEC ROSH.dbo.IdentifyStudentsByState 'Fémale', N'MD ' ; GO Cela donnerait :
Alternativement, vous pouvez fournir la valeur de chaque paramètre dans l'ordre de votre choix. Examinez la procédure suivante qui prend 3 arguments : USE ROSH ;
GO
CREATE PROCEDURE IdentifySomeStudents
@Gdr nvarchar (20),
@StateOrProvince nchar (2),
@HomeStatus bit
AS
BEGIN
SELECT FullName = LastName + N ',' + FirstName,
DATEDIFF(year, DateOfBirth, GETDATE()) AS Age,
Gender
FROM Students
WHERE (Gender = @Gdr) AND
(@StateOrProvince) AND
(SPHome = @HomeStatus)
END
GO
En appelant ce type de procédure, vous pouvez taper le nom de chaque paramètre et lui attribuer la valeur correspondante. Voici un exemple : USE ROSH ; GO EXEC IdentifySomeStudents @ HomeStatus = 1, @StateOrProvince = N 'MD', @Gdr = N 'Female' ; GO Voici un exemple de l'exécution de la procédure :
Imaginez que vous créez une base de données pour un magasin et une table qui contient la liste des objets vendus dans le magasin :
Supposez que vous avez rempli la table avec quelques éléments comme suit :
Imaginez que vous souhaitez créer un mécanisme de calcul du prix d'un élément après qu'une remise y a été appliquée. Une telle procédure peut être créée comme suit : CREATE PROC CalculateNetPrice @discount DECIMAL AS SELECT ItemName, UnitPrice-(UnitPrice * @discount/100) FROM StoreItems Cela peut être exécuté comme suit :
Si vous envisagez de créer une procédure stockée qui prend un argument et savoir que l'argument aura probablement la même valeur la plupart du temps, vous pouvez fournir cette valeur comme paramètre mais laisser un espace pour d'autres valeurs de cet argument. Une valeur donnée à un argument est appelée par défaut. Cela implique que, lorsque l'utilisateur appelle cette procédure stockée, si l'utilisateur ne fournit pas une valeur pour l'argument, la valeur par défaut serait utilisée. Pour créer une procédure stockée qui prend un argument qui comporte une valeur par défaut, après la déclaration de la valeur, sur son côté droit, tapez = suivi par la valeur souhaitée. Voici un exemple appliqué à la base de données ci-dessus : CREATE PROC CalculateDiscountedPrice
@discount décimal = 10,00
AS
SELECT ItemName, UnitPrice-(UnitPrice * @discount/100)
FROM StoreItems ;
GO
Lors de l'exécution d'une procédure stockée qui prend un argument par défaut, vous n'avez pas à fournir une valeur pour l'argument, si la valeur par défaut vous convient. Sur cette base, la procédure stockée ci-dessus peut être appelée comme suit :
Si la valeur par défaut ne s'applique pas à votre calcul actuel, vous pouvez fournir une valeur pour l'argument. Voici un exemple :
En utilisant cette même approche, vous pouvez créer une procédure stockée qui prend plusieurs arguments avec les valeurs par défaut. Pour fournir une valeur par défaut pour chaque argument, après sa déclaration, entrez la valeur souhaitée pour son côté droit. Voici un exemple d'une procédure stockée qui prend deux arguments, chacun avec une valeur par défaut : CREATE PROC CalculateSalePrice
@Discount decimal = 20,00,
@TaxRate decimal = 7,75
AS
SELECT ItemName AS [Item Description],
UnitPrice As [Marked Price],
UnitPrice * @Discount/100 As [Discount Amt],
UnitPrice-(UnitPrice * @Discount/100) As [After Discount],
UnitPrice* @TaxRate/100 As [Tax Amount],
(UnitPrice * @TaxRate/100) + UnitPrice-
(UnitPrice * @Discount/100) + (@TaxRate/100) As [Net Price]
FROM StoreItems ;
GO
Voici un exemple de l'exécution de la procédure :
Lors de l'appel d'une procédure stockée qui prend plusieurs arguments et tous les arguments ayant des valeurs par défaut, vous n'avez pas besoin de fournir une valeur pour chaque argument, vous pouvez fournir une valeur pour seulement un ou certains de ces arguments. La procédure ci-dessus peut être appelée avec un argument comme suit : EXEC CalculateSalePrice2 55,00 Dans ce cas, l' (les) autre (s) argument (s) utiliserait e(nt) leur valeur par défaut. Nous avons vu que, lors de l'appel d'une procédure stockée qui prend plusieurs arguments, vous n'aviez pas à fournir les valeurs des arguments dans l'ordre exact qu'ils apparaissaient dans la procédure, vous deviez simplement taper le nom de chaque argument et lui attribuer la valeur souhaitée. De la même façon, si une procédure stockée accepte plusieurs arguments et certains de ces arguments ont une valeur par défaut, lors de l'appel, vous pouvez fournir les valeurs dans l'ordre de votre choix, en tapant le nom de chaque argument et affecter la valeur souhaitée. Sur cette base, la procédure stockée ci-dessus peut être appelée avec uniquement la valeur du deuxième argument comme suit : EXEC CalculateSalePrice2 @ TaxRate = 8.55 Dans ce cas, le premier argument pourrait utiliser sa valeur par défaut.
Transact-SQL utilise la notion de transmettre un argument par référence. Ce type d'argument est transmis à une procédure mais il est censé retourner une valeur. En d'autres termes, vous pouvez créer une procédure stockée qui prend un paramètre, mais le but du paramètre est d'exécuter une nouvelle valeur lorsque la procédure se termine, afin que vous puissiez utiliser cette valeur comme bon vous semble. Pour créer un paramètre qui renvoie une valeur de la procédure stockée, après le nom de la procédure, si vous souhaitez que la procédure stockée prenne des arguments, tapez-les. Dans le cas contraire, omettez-les. D'autre part, vous devez transmettre au moins un argument, nommez-le en commençant par le symbole @, spécifiez son type de données, puis entrez le mot-clé OUTPUT sur sa droite. Sur cette base, vous pouvez utiliser la syntaxe de base suivante : CREATE PROCEDURE ProcedureName @ParameterName DataType OUTPUT AS Body of the procédure Dans le corps de la procédure, vous pouvez effectuer l'affectation comme bon vous semble. La règle principale que vous devez suivre est que, avant la fin de la procédure, vous devez avoir indiqué une valeur pour l'argument OUTPUT. C'est la valeur que l'argument tiendra lorsque la procédure stockée s'arrête. Voici un exemple : CREATE PROCEDURE dbo.CreateFullName
@FName nvarchar (20),
@LName nvarchar (20),
@FullName nvarchar (42)OUTPUT
AS
SELECT @ FullName = @ LName + ', N' + @ FName
GO
Lorsque vous appelez la procédure stockée, vous devez transmettre un argument pour le paramètre OUTPUT et, une fois de plus, vous devez taper OUTPUT sur le côté droit de l'argument. N'oubliez pas que la procédure stockée reviendrait à l'argument. Cela signifie que, après l'appel de la procédure, vous pouvez reprendre l'argument OUTPUT et l'utiliser comme bon vous semble. Voici un exemple : DECLARE @FirstName nvarchar(20),
@LastName nvarchar (20),
@Full nvarchar(42)
SET @FirstName = N'Melanie ';
SET @LatName = N'Johanssen ' ;
EXECUTE dbo.CreateFullName @FirstName, @LastName, @Full OUTPUT
SELECT @Full ;
GO
L'un des avantages de l'utilisation d'une fonction ou une procédure stockée est qu'elle a accès aux tables et aux enregistrements de la base de données. Cela signifie que vous pouvez accéder aux colonnes et aux enregistrements tant que vous spécifiez la table ou la vue, ce qui est fait avec une clause FROM associée à une instruction SELECT. Examinez la procédure stockée suivante créée dans une base de données qui contienne un tableau nommé Students : USE ROSH ;
GO
CREATE PROCEDURE ShowStudentsFullNames
@FullName nvarchar(42) OUTPUT
AS
SELECT @FullName = LastName + ', N' + FirstName FROM Students ;
GO
Lorsque vous exécutez cette procédure stockée, elle fonctionnerait sur les enregistrements de la table. Une des particularités d'une procédure stockée qui prend un argument OUTPUT est qu'elle ne peut renvoyer qu'une seule valeur. Prenons l'exemple suivant de l'exécution de la procédure ci-dessus :
Lors de l'appel d'une telle procédure, si vous ne spécifiez pas une condition pour produire un résultat particulier, l'interprète SQL dans ce cas sélectionnerait le dernier enregistrement. Cela signifie que vous devez toujours vous assurer que votre procédure stockée qui prend un paramètre OUTPUT aurait un moyen d'isoler un résultat. Si la procédure stockée traite une instruction SELECT, vous pouvez utiliser une condition WHERE. Voici un exemple d'une telle procédure : USE ROSH ;
GO
CREATE PROCEDURE ShowStudentsFullNames
@FullName nvarchar(42) OUTPUT
AS
SELECT @FullName = LastName + ', N' + FirstName FROM Students
WHERE StudentID = 8;
GO
Lorsque cette procédure est exécutée, elle produirait uniquement l'enregistrement stocké dans le 8e rang de la table.
|
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
|
||
| Précédent | Copyright © 2009 Yevol | Suivant |
|
|
||
Formation pratique : Présentation des procédures stockées
. Si le code de la procédure est bon, il serait créé et un nouveau nœud pour son nom pourrait être ajouté à la section des procédures stockées de la base de données.
