[SQL Server] Utilisation de cursor
Je suis sous SQL 2005 donc je n’ai pas accès à la commande MERGE qui fait l’objet d’un autre article sur ce blog (autrement dit si vous avez SQL 2008c’est exemple est plus optimisé d’autre l’article sur le MERGE)
L’idée est la suivante : j’ai une table stock qui contient des produits et leur quantité, par ailleurs j’ai une table livraison qui contient des produits et leur quantité ce qui correspond au contenu du camion (oui Robert il est beau ton camion).
Quand il y a des produits dans le camion de Robert qui existe déjà dans mon stock, je veux que les quantités s’ajoutent. Quand il y a des produits en livraison que je n’ai pas en stock je veux créer une nouvelle ligne dans ma table stock.
Voici le script de création des deux tables :
USE [Base_stock]
GO
CREATE TABLE [dbo].[Table_Livraison](
[Id_Livraison] [int] IDENTITY(1,1) NOT NULL,
[id_Produit_Livraison] [int] NULL,
[Produit_Livraison] [varchar](50) NULL,
[quantite_Livraison] [int] NULL,
CONSTRAINT [PK_Table_Livraison] PRIMARY KEY CLUSTERED
(
[Id_Livraison] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Table_Stock](
[Id_Stock] [int] IDENTITY(1,1) NOT NULL,
[Id_Produit_Stock] [int] NULL,
[Produit_Stock] [varchar](50) NULL,
[Quantite_Stock] [int] NULL,
CONSTRAINT [PK_Table_Stock] PRIMARY KEY CLUSTERED
(
[Id_Stock] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
Ensuite je vais mettre quelques données dans mes deux tables pour la démo.
use Base_stock
GO
INSERT INTO [Base_stock].[dbo].[Table_Stock]
([Id_Produit_Stock]
,[Produit_Stock]
,[Quantite_Stock])
VALUES (1,'nutella',10)
INSERT INTO [Base_stock].[dbo].[Table_Stock]
([Id_Produit_Stock]
,[Produit_Stock]
,[Quantite_Stock])
VALUES (5,'compote',20)
INSERT INTO [Base_stock].[dbo].[Table_Stock]
([Id_Produit_Stock]
,[Produit_Stock]
,[Quantite_Stock])
VALUES(6,'brocolis',50)
INSERT INTO [Base_stock].[dbo].[Table_Stock]
([Id_Produit_Stock]
,[Produit_Stock]
,[Quantite_Stock])
VALUES (7,'pommes',30)
INSERT INTO [Base_stock].[dbo].[Table_Stock]
([Id_Produit_Stock]
,[Produit_Stock]
,[Quantite_Stock])
VALUES(8,'coca',40)
GO
INSERT INTO [Base_stock].[dbo].[Table_Livraison]
([id_Produit_Livraison]
,[Produit_Livraison]
,[quantite_Livraison])
VALUES
(2,'biere',10)
INSERT INTO [Base_stock].[dbo].[Table_Livraison]
([id_Produit_Livraison]
,[Produit_Livraison]
,[quantite_Livraison])
VALUES(3,'tomates',200)
INSERT INTO [Base_stock].[dbo].[Table_Livraison]
([id_Produit_Livraison]
,[Produit_Livraison]
,[quantite_Livraison])
VALUES(5,'compote',10)
INSERT INTO [Base_stock].[dbo].[Table_Livraison]
([id_Produit_Livraison]
,[Produit_Livraison]
,[quantite_Livraison])
VALUES(1,'nutella',30)
INSERT INTO [Base_stock].[dbo].[Table_Livraison]
([id_Produit_Livraison]
,[Produit_Livraison]
,[quantite_Livraison])
VALUES (9,'beurre',10)
GO
Donc pour récapituler voici le contenu de mes 2 tables.
Bien, maintenant la mise en place du CURSOR voici le script après on en parle
declare @Id_Produit_Livraison int
declare @Produit_Livraison varchar(50)
declare @Quantite_Livraison int
declare @maquantite_en_stock int
declare curseur_Livraison CURSOR for
select Id_Produit_Livraison ,Produit_Livraison ,Quantite_Livraison from dbo.Table_Livraison
open curseur_Livraison
fetch next from curseur_Livraison into @Id_Produit_Livraison ,@Produit_Livraison ,@Quantite_Livraison
while @@FETCH_STATUS=0
Begin
set @maquantite_en_stock=(select isnull(Quantite_stock,0) from dbo.Table_stock where Id_Produit_stock=@Id_Produit_Livraison)
if @maquantite_en_stock<>0
begin
print 'update'
update dbo.Table_stock set Quantite_stock=Quantite_stock+@Quantite_Livraison where Id_Produit_stock=@Id_Produit_Livraison
end
else
begin
print 'insert'
insert dbo.Table_stock values (@Id_Produit_Livraison,@Produit_Livraison,@Quantite_Livraison)
end
fetch next from curseur_Livraison into @Id_Produit_Livraison ,@Produit_Livraison ,@Quantite_Livraison
End
close curseur_Livraison
deallocate curseur_Livraison
Je commence par déclarer les variables dont j’aurais besoin :
declare @Id_Produit_Livraison int
declare @Produit_Livraison varchar(50)
declare @Quantite_Livraison int
declare @maquantite_en_stock int
Ensuite je déclare un curseur et je l’associe à une commande select dont le nom des colonnes ressemble étrangement aux noms de mes variables…
declare curseur_Livraison CURSOR for
select Id_Produit_Livraison ,Produit_Livraison ,Quantite_Livraison from dbo.Table_Livraison
J’ouvre mon curseur (open), jeme positionne sur la première ligne(fetch next) et je vais associer mes variables à chacune des colonnes de mon select , bien sûr il faut que les type des variables et des colonnes soient les mêmes.
open curseur_Livraison
fetch next from curseur_Livraison into @Id_Produit_Livraison ,@Produit_Livraison ,@Quantite_Livraison
Je lui dit que tant qu’il y a des lignes à traitée (c'est-à-dire leslignes issuent du select associé au CURSOR) je travaille.
while @@FETCH_STATUS=0
Begin
Mon travail étant le suivant : si ,pour le produit en livraison sur lequel est mon curseur actuellement,ily aune quantité en stock alors je la metà jour, sinon c’est que le produit n’existe pas et donc je fait une insertion.
set @maquantite_en_stock=(select isnull(Quantite_stock,0) from dbo.Table_stock where Id_Produit_stock=@Id_Produit_Livraison)
if @maquantite_en_stock<>0
begin
print 'update'
update dbo.Table_stock set Quantite_stock=Quantite_stock+@Quantite_Livraison where Id_Produit_stock=@Id_Produit_Livraison
end
else
begin
print 'insert'
insert dbo.Table_stock values (@Id_Produit_Livraison,@Produit_Livraison,@Quantite_Livraison)
end
Le truc c’est que je possède toutes les information de la ligne de ma table livraison puisqu’elle sont dans les différents variable liées au curseur donc autant les utilisées.
Une fois mon traitement fait pour une ligne, je passe à la ligne suivante en disant à mon curseur d’avancer d’une ligne et ce faisant de me remplir les variables avec les données de cette ligne. ETC…
fetch next from curseur_Livraison into @Id_Produit_Livraison ,@Produit_Livraison ,@Quantite_Livraison
End
Une fois tous ces traitements faits il ne me reste plus qu’à sortir proprement en fermant le curseur et en dés allouant son emplacement mémoire.
close curseur_Livraison
deallocate curseur_Livraison
Le résultat est le suivant :
On voit bien que la quantité de « nutella » par exemple est passée de 10 à 40 (30 au départ + 10 livrés).
La bière qui n’était pas en stock au départ est venue s’insérer avec la quantité livrée. Etc.…
Voilà une commande qui peut venir s’insérer facilement dans des procédures stockées, des scripts et pourquoi pas des fonctions.