Les fonctions table sont de deux types :
-
Les fonctions table en ligne, qui ne contiennent qu’une seule instruction SELECT
déterminant le format de la table renvoyée.
160
-
Les fonctions table multi instructions, qui déclarent le format d’une table virtuelle, avant
de la remplir par des instructions SELECT.
CREATE FUNCTION <Inline_Function_Name, sysname, FunctionName>
(
-- Add the parameters for the function here
<@param1, sysname, @p1> <Data_Type_For_Param1, , int>,
<@param2, sysname, @p2> <Data_Type_For_Param2, , char>
)
RETURNS TABLE
AS
RETURN
(
-- Add the SELECT statement with parameter references here
SELECT 0
)
GO
Les fonctions table et table multi-instructions sont différentes dans le sens où le format de
retour est différent. En effet, la fonction table retourne la solution d’un requête SELECT, alors
que la fonction table multi-instruction, retournera une variable de type table, contenant
l’instruction SELECT opérée par la fonction.
Fonction table simple
CREATE FUNCTION recommander_stock (@Id int, @seuil int)
RETURNS TABLE
AS
RETURN (SELECT * FROM Stock WHERE Id_Stock = @Id AND Quantite < @Seuil)
Dans ce cas-là, après l’instruction de création de fonction, CREATE FUNCTION, on indique
simplement que la fonction retourne une donnée de type table avec la clause RETURNS
TABLE. Par la suite, après la clause AS, on précise quelle instruction doit être retournée dans
la valeur de retour de type table de la fonction.
Exemple 1 : Créer la fonction fn_CA_Fournisseur, qui en fonction d’un code fournisseur et
d’une année entrés en paramètre, restituera le CA potentiel de ce fournisseur pour l’année
souhaitée.
CREATE FUNCTION vente.fn_CA_Fournisseur
(@codfou int, @datcom date)
RETURNS TABLE
AS
RETURN (
SELECT f.NUMFOU, NOMFOU, CAST(sum(QTECDE * (CAST(priuni as money) *
1.2060)) as money) as 'CA'
FROM vente.FOURNISSEUR f
INNER JOIN vente.ENTCOM e
ON f.NUMFOU = e.NUMFOU
INNER JOIN vente.ligcom
ON e.NUMCOM = vente.ligcom.NUMCOM
WHERE e.NUMFOU = @codfou
AND YEAR (@datcom) = YEAR(GETDATE())
GROUP BY f.NUMFOU, NOMFOU)
Testons notre fonction :
SELECT * FROM vente.fn_CA_Fournisseur('120', '27/01/2011')
161
Exemple 2 : Utilisons l’option SCHEMABINDING. SCHEMABINDING indique que la fonction
est liée aux objets de base de données auxquels elle fait référence. Toutes modification
(ALTER) ou suppression (DROP) de ces objets sont vouées à l’échec. La liaison de la fonction
aux objets auxquels elle fait référence est supprimée uniquement lorsqu'une des actions
suivantes se produit :
- La fonction est supprimée.
- La fonction est modifiée, avec l'instruction ALTER, sans spécification de l'option
SCHEMABINDING.
Créez une table FOURNIS_IND de structure identique à FOURNISSEUR de notre base de
données « Papyrus » et insérez les lignes de la table FOURNISSEUR dont la colonne indice de
satisfaction est 'Bon'.
CREATE FUNCTION vente.fournis
(@com varchar(50))
RETURNS TABLE
AS
RETURN (
SELECT *
FROM vente.FOURNISSEUR
-- Nous utilisons une fonction créée précédemment
WHERE vente.fn_Date(satisf) = @com)
Testons notre fonction vente.fn_CA_Fournisseur :
SELECT * FROM vente.fournis('Bon')
On créer la table FOURNIS_IND.
CREATE TABLE FOURNIS_IND (NUMFOU int, NOMFOU varchar(30), RUEFOU
varchar(30), POSFOU varchar(5), VILFOU varchar(30), CONFOU varchar(15),
SATISF tinyint)
On insère les données :
INSERT INTO FOURNIS_IND (NUMFOU, NOMFOU, RUEFOU, POSFOU, VILFOU, CONFOU,
SATISF)
SELECT *
FROM vente.fournis('Bon')
On modifie le schéma :
ALTER SCHEMA vente TRANSFER dbo.FOURNIS_IND
Sans ce schéma, il sera impossible de définir l'option WITH SCHEMABINDING pour
l'énoncer ci-dessous.
On créez la fonction fn_ Compte, avec l’option SCHEMABINDING qui délivre le nombre de
lignes de la table FOURNIS_IND.
CREATE FUNCTION vente.fn_Compte
(@com varchar(50))
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN (
SELECT count(*) AS 'Nbr Lignes'
162
FROM vente.FOURNIS_IND
)
Fonction table multi-instruction
CREATE FUNCTION table_multi (@Id int)
RETURNS @variable TABLE (Id_Stock int, Quantite int, Nom_Entrepos
varchar(25))
AS
BEGIN
SELECT @variable = (SELECT Id_Stock, Quantite, Nom_Entrepos
FROM Entrepos E INNER JOIN Stock S
ON E.Id_Entrepos = S.Id_Entrepos
WHERE S.Id_Stock = @Id)
RETURN
END
Dans ce cas, en revanche, la valeur de retour est toujours une table, simplement, on donne à
une variable ce type et on définit les colonnes qu’elle contient. Les valeurs définies par le
SELECT y seront alors contenues. Il faut faire attention, en contrepartie, à ce que le nombre
de colonnes dans la variable retournée par la fonction ait le même nombre de colonnes que le
résultat retourné par l’instruction SELECT.
Modification d’une fonction
La modification d’une fonction n’est possible que par une instruction T-SQL DDL, l’instruction
ALTER FUNCTION. Par l’interface, lorsque l’on clique droit « Modifier » :
Nous arrivons directement sur notre code Transaq-SQL :
163
En effet, dans ce cas-là, les commandes CREATE FUNCTION et ALTER FUNTION auront
quasiment la même fonction, puisque dans les deux cas, tout le corps de la fonction doit être
réécrit et totalité. Il n’est pas possible d’ajouter ou supprimer seulement une seule ligne dans
celui-ci. Leur seule différence réside donc dans le fait que la fonction soit créée ou modifiée.
Voyons donc la syntaxe qui permet la modification d’une fonction en Transact SQL :
ALTER FUNCTION nombre_element_stock (@Entrepos int)
RETURNS int
AS
BEGIN
DECLARE @nb int
SELECT @nb = COUNT(Id_Stock)
FROM Stock
WHERE Id_Entrepos = @Entrepos
RETURN @nb
END
GO
On remarque donc aisément que la seule modification qu’il existe entre la création et la
modification d’une fonction réside dans le remplacement du mot clé CREATE FUNCTION par
le mot clé ALTER FUNCTION.
Suppression d’une fonction
La suppression d’une fonction peut, comme pour les procédures stockées ou tout autre objet
de la base, être faite de deux manières. La première est la méthode graphique. Nous ne
détaillerons pas cette méthode, puisqu’elle est la même pour tout objet de la base. Nous
rappellerons juste qu'il vous suffit d’étendre, dans votre explorateur d’objet, les nœuds
correspondants au chemin de votre fonction, de faire un clic droit sur celle-ci, et de
sélectionner « Supprimer ».
164
Avec le langage Transact SQL, la méthode est aussi la même que pour tout autre objet de la
base. Nous utiliserons l’instruction DROP et nous l’adapterons au cas d’une fonction. Voici la
méthode type de suppression d’une fonction par code T-SQL :
DROP FUNCTION nombre_element_stock
L’instruction DROPFUNCTION nous permet donc de supprimer une fonction, de n’importe
quel type. Il suffit juste de préciser le nom de la fonction après cette instruction.
Procédures Stockées
Les procédures stockées sont des ensembles d’instructions du DML, pouvant être exécutées
par simple appel de leur nom ou par l’instruction EXECUTE. Les procédures stockées sont de
véritables programmes qui peuvent recevoir des paramètres, être exécutés à distance,
renvoyer des valeurs et possédant leurs propres droits d’accès (EXECUTE). Celles-ci sont
compilées une première fois, puis placées en cache mémoire, ce qui rend leur exécution plus
performante du fait que le code soit précompilé. Les procédures stockées sont contenues dans
la base de données, et sont appelables par leurs noms. Il existe une multitude de procédures
stockées pré intégré dans SQL Server lors de l’installation qui servent principalement à la
maintenance des bases de données utilisateur. Celle-ci commence toujours par les trois
caractères « sp_ » comme stored procedure. Pour résumer les avantages des procédures
stockées, nous allons lister leurs utilisations :
- Accroissement des performances.
- Sécurité d’exécution.
- Possibilité de manipuler les données système.
- Implémente le traitement en cascade et l’enchaînement d’instructions.
Création d’une procédure stockée
Pour créer une procédure stockée, nous sommes obligés de passer par du code T-SQL. En
revanche, il existe un assistant de génération automatique de la structure d’une procédure
stockée. Nous allons tout d’abord étudier la structure générale d’une procédure stockée avec
cette génération automatique, puis nous donnerons un exemple, présent dans le script de la
base que nous utilisons, pour bien comprendre les notions exposées sur les procédures
stockées.
165
Tout d’abord, pour générer le script automatiquement, étendez les nœuds de l’explorateur
d’objet comme ceci :
Maintenant, pour créer une nouvelle procédure stockée en générant le code
automatiquement, il vous suffit de faire un clic droit sur le nœud « Procédures stockées »
et de choisir l’option « Nouvelle procédure stockée… ».
Une nouvelle fenêtre de requête s’ouvre dans SSMS, vous proposant le code pour créer une
nouvelle procédure stockée. Le code est le suivant :
CREATE PROCEDURE <Procedure_Name, sysname, ProcedureName>
-- Add the parameters for the stored procedure here
<@Param1, sysname, @p1> <Datatype_For_Param1, , int> =
<Default_Value_For_Param1, , 0>,
<@Param2, sysname, @p2> <Datatype_For_Param2, , int> =
<Default_Value_For_Param2, , 0>
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
SELECT <@Param1, sysname, @p1>, <@Param2, sysname, @p2>
END
GO
Nous allons maintenant détailler le code.
CREATE PROCEDURE <Procedure_Name, sysname, ProcedureName>
Nous créons une procédure stockée avec l’instruction DDL CREATE PROCEDURE suivie du
nom à donner à la procédure. Ce nom vous permettra de l’appeler et de la reconnaître dans la
base.
-- Add the parameters for the stored procedure here
166
<@Param1, sysname, @p1> <Datatype_For_Param1, , int> =
<Default_Value_For_Param1, , 0>,
<@Param2, sysname, @p2> <Datatype_For_Param2, , int> =
<Default_Value_For_Param2, , 0>
Nous devons ensuite préciser les variables que prend en paramètre la procédure stockée,
durant son appel. Ces variables vont nous servir par la suite dans la définition des actions que
la procédure stockée fait. Nous pouvons initialiser ou non les variables, le plus important est
bien entendu de donner un nom conventionnel et un type de donnée à nos variables.
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
SELECT <@Param1, sysname, @p1>, <@Param2, sysname, @p2>
END
GO
Les instructions AS BEGIN et END sont les délimiteurs du code à utiliser par la procédure
stockée. Toutes les instructions comprises entre ces deux mots clés seront prises en compte
et exécutées par la procédure stockée.
Maintenant que nous avons présenté la structure générale de création d’une procédure
stockée, prenons des exemples concrets pour l’illustrer à partir de notre base de données
« Papyrus ».
Exemple 1 : Créez la procédure stockée Lst_fournis correspondante à la requête : « Afficher
le code des fournisseurs pour lesquels une commande a été passée ».
USE Papyrus
GO
CREATE PROCEDURE Lst_fournis2
AS
SELECT DISTINCT NUMFOU
FROM vente.ENTCOM
On exécute notre procédure stockée pour la vérifier :
EXEC Lst_fournis
EXEC ou EXECUTE est une instruction permettant de lancer une requête ou une procédure
stockée au sein d'une procédure ou un trigger. La plupart du temps il n'est pas nécessaire
d'utilise l'instruction EXEC, si l'intégralité de la commande SQL ou de la procédure à lancer
est connue. Mais lorsqu'il s'agit par exemple d'un ordre SQL contenant de nombreux
paramètres, alors il est nécessaire de le définir dynamiquement.
167
Après la déclaration de création de procédures, et la déclaration des paramètres, il est
possible de définir des options grâce à une clause WITH ou une clause FOR. Ces options sont
les suivantes :
- WITH RECOMPILE : la procédure sera recompilée à chaque exécution.
- WITH ENCRYPTION : Permet de crypter le code dans la table système.
- FOR REPLICATION : Permet de préciser que la procédure sera utilisée lors de la
réplication.
Exemple 2 : Reprenons l’exemple précèdent avec l’option WITH RECOMPILE.
CREATE PROCEDURE Lst_fournisBis
WITH RECOMPILE
AS
SELECT DISTINCT NUMFOU
FROM vente.ENTCOM
On teste notre procédure stockée de la même façon :
EXEC Lst_fournis
SQL Server recompile automatiquement les procédures stockées et les déclencheurs quand il
est avantageux de le faire.
Cependant, vous pouvez forcer la recompilation des procédures stockées et des déclencheurs
lors de leur prochaine exécution. L'exemple suivant engendre la recompilation des procédures
stockées et des déclencheurs qui agissent sur la table PRODUIT lors de leur prochaine
exécution.
USE Papyrus;
GO
EXEC sp_recompile N'vente.PRODUIT';
GO
Exemple 3 : Création d’une procédure stockée avec un paramètre en entrée. On créer la
procédure stockée « Lst_Commandes », qui liste les commandes ayant un libellé particulier
dans le champ OBSCOM.
CREATE PROCEDURE Lst_Commandes
@libelle varchar(50)
AS
SELECT e.NUMCOM, NOMFOU, LIBART, QTECDE * cast(PRIUNI as money) as 'Sous
Total'
FROM vente.ENTCOM e
INNER JOIN vente.FOURNISSEUR f
ON e.NUMFOU = f.NUMFOU
INNER JOIN vente.LIGCOM l
ON e.NUMCOM = l.NUMCOM
INNER JOIN vente.PRODUIT p
168
ON l.CODART = p.CODART
WHERE e.OBSCOM like (@libelle)
On teste notre procédure stockée : Il est alors nécessaire d’entrer des valeurs pour les
paramètres indiqués dans la procédure stockée.
EXEC Lst_Commandes '%urgent%'
Exemple 4 : Création d’une procédure stockée avec des paramètres en entrée et en sortie. On
créer la procédure stockée « CA_Fournisseur », qui pour un code fournisseur et une année
entré en paramètre, calcule et restitue le CA potentiel de ce fournisseur pour l’année
souhaitée. On exécutera la requête que si le code fournisseur est valide, c’est-à-dire qui
existe dans la table FOURNISSEUR, sinon on renverra un code d’état égal à –100.
CREATE PROCEDURE CA_Fournisseur
@numfou int,
@datcom date
AS
BEGIN
/* Empêche le message indiquant le nombre de lignes concernées par une
instruction
ou une procédure stockée Transact-SQL d'être renvoyé avec l'ensemble de
résultats. */
SET NOCOUNT ON;
-- On déclare nos variables
DECLARE @comm varchar(50)
-- Si le fournisseur existe
IF EXISTS(SELECT NOMFOU FROM vente.FOURNISSEUR WHERE NUMFOU = @numfou)
BEGIN
SET @comm = 'FOURNISSEUR : '+ Convert(varchar(25),@numfou) + ' ' + @comm
PRINT @comm
PRINT 'LISTE DES ARTICLES EN COMMANDE'
/* La requête suivante calcule le chiffre d'affaires par fournisseur pour
l'année donnée
sachant que les prix indiqués sont hors tars et que le taux TVA est de
20,60 % */
SELECT f.NUMFOU, NOMFOU, CAST(sum(QTECDE * (CAST(priuni as money) *
1.2060)) as money) as 'CA'
FROM vente.FOURNISSEUR f
INNER JOIN vente.ENTCOM e
ON f.NUMFOU = e.NUMFOU
INNER JOIN vente.ligcom
ON e.NUMCOM = vente.ligcom.NUMCOM
WHERE e.NUMFOU = @numfou
AND YEAR (DATCOM) = YEAR(GETDATE())
GROUP BY f.NUMFOU, NOMFOU
169
-- Si le fournisseur exist, on retourne le code 0 après avoir exécuté la
requête
RETURN 0;
END
-- Si le fournisseur n'existe pas, on retourne le code -100
ELSE
RETURN -100;
END
GO
On teste notre procédure stockée :
USE Papyrus
DECLARE @retour int
DECLARE @date date
DECLARE @lefourn int
SET @lefourn = 120
SET @date = '27/01/2011'
EXEC @retour = CA_Fournisseur @lefourn, @date
IF @retour = 0
PRINT 'Le fournisseur ' + convert(varchar(4), @lefourn) + ' existe bien !'
ELSE
PRINT 'Le fournisseur ' + convert(varchar(4), @lefourn) + ' n''existe pas
!'
Sur l’onglet « Messages » nous obtenons l’information suivante :
USE Papyrus
DECLARE @retour int
DECLARE @date date
DECLARE @lefourn int
SET @lefourn = 100
SET @date = '27/01/2011'
EXEC @retour = CA_Fournisseur @lefourn, @date
IF @retour = 0
PRINT 'Le fournisseur ' + convert(varchar(4), @lefourn) + ' existe bien !'
ELSE
PRINT 'Le fournisseur ' + convert(varchar(4), @lefourn) + ' n''existe pas
!'
170
Exemple 5 : Créez une nouvelle procédure stockée « CA2_ Fournisseur » qui lorsque le code
FOURNISSEUR n’existe pas, renvoie le message s’inscrivant dans le journal d’erreurs du
serveur et dans le journal des évènements.
Avant toute chose, nous devons créer le message utilisate
