Cursor en Store Procedure SQL Server
En este ejercicio se va a crear un cursor el cual analizará el volumen de compras de un cliente, los clientes y su volumen de compras se van a guardar en tablas diferentes, separando a los clientes que han comprado por encima de la media de todos los pedidos y los que han comprado menos en otra tabla.
Para facilidad del trabajo y que todos puedan verificar los resultados se va a utilizar la información de la base de datos Northwind, se creará una nueva base de datos y copiará la información de Northwind.
Para más información:
Ver Cursores en SQL Server
Ver Procedimientos almacenados
Ver Variables en SQL Server
Ver Agrupamientos
Desarrollo del ejercicio
create database Ventas
go
use Ventas
go
El diagrama ilustra lo que se va a crear:
Create table Cliente
(ClienteCodigo nchar(5),
ClienteNombre nvarchar(100),
ClienteCiudad nvarchar(100),
constraint ClientePK primary key (ClienteCodigo)
)
go
Insertar los clientes de la tab la Customers de Nortwind
insert into Cliente
select CustomerID, CompanyName, City from Northwind.dbo.Customers
go
Crear la tabla Pedidos
Create table Pedidos
(
PedidosCodigo nchar(5),
PedidosFecha Date,
ClienteCodigo nchar(5),
constraint PedidosPK primary key (PedidosCodigo),
constraint PedidosClientesFK foreign key (ClienteCodigo)
references Cliente(ClienteCodigo)
)
go
Insertar las órdenes de la tabla Orders de Nortwind
insert into Pedidos
select OrderID, OrderDate, CustomerID from Northwind.dbo.Orders
go
Crear la tabla Productos
Create table Productos
(
ProductosCodigo int,
ProductosDescripcion nvarchar(100),
ProductosUnidad nvarchar(100),
ProductosPrecio Numeric(9,2),
ProductosStock Numeric(9,2),
constraint ProductosPK Primary key (ProductosCodigo)
)
go
Insertar los productos de la tabla Products de Northwind
insert into Productos
select ProductID, ProductName, QuantityPerUnit,
UnitPrice, UnitsInStock
from Northwind.dbo.Products
go
Crear la tabla Detalle de pedidos
Create table DetallePedidos
(
PedidosCodigo nchar(5),
ProductosCodigo int,
PedidoCantidad Numeric(9,2),
PedidoPrecio Numeric(9,2),
PedidoDescuento Numeric(5,3)
constraint DetallePedidosPK primary key (PedidosCodigo, ProductosCodigo),
constraint DetallePedidosProductosFK Foreign key (ProductosCodigo)
references Productos(ProductosCodigo),
constraint DetallePedidosPedidosFK Foreign key (PedidosCodigo)
references Pedidos(PedidosCodigo)
)
go
Insertar los registros de la tabla Order Details de Northwind
insert into DetallePedidos
select OrderID, ProductID, Quantity, UnitPrice, Discount
from Northwind.dbo.[Order Details]
go
Las tablas para el llenado del resultado son VIP y Normal.
create table VIP(
IdCliente nchar(5),
NombreCliente nvarchar(100),
Ciudad nvarchar(100),
totalgastado Numeric(9,2)
)
create table NORMAL(
IdCliente nchar(5),
NombreCliente nvarchar(100),
Ciudad nvarchar(100),
totalgastado Numeric(9,2)
)
La instrucción select del cursor es la siguiente.
Ver Agrupamientos.
select c.ClienteCodigo, C.ClienteNombre, C.ClienteCiudad,
SUM(D.PedidoPrecio*D.PedidoCantidad *(1-D.PedidoDescuento))
As ‘Total’
from Cliente c, DetallePedidos d, Pedidos p
where d.PedidosCodigo = p.PedidosCodigo
and c.ClienteCodigo =p.ClienteCodigo
group by c.ClienteCodigo, c.ClienteNombre, C.ClienteCiudad
go
La imagen muestra los clientes y el total de compras.
Cálculo de la media de las compras, Total de ventas/Total Pedidos
Total Venta
select sum(d.PedidoPrecio* d.PedidoCantidad (1-D.PedidoDescuento)) As ‘Suma’ from DetallePedidos d, Pedidos p where d.PedidosCodigo =p. PedidosCodigo go
Total Pedidos
select Count(P.PedidosCodigo) from Pedidos P
go
La media de los pedidos
Declare @Media Numeric(9,2)
Set @Media = ( select sum(d.PedidoPrecio d.PedidoCantidad *(1- D.PedidoDescuento)) As ‘Suma’
from DetallePedidos d, Pedidos p
where d.PedidosCodigo =p. PedidosCodigo ) /
(select Count(P.PedidosCodigo) from Pedidos P)
select @Media
go
El Procedimiento almacenado para llenar las tablas es como sigue:
Create procedure spClientesVipNormal
As
begin
set nocount on
declare CursorRuta cursor for
select c.ClienteCodigo, C.ClienteNombre, C.ClienteCiudad,
SUM(D.PedidoPrecio*D.PedidoCantidad *(1-D.PedidoDescuento))
As ‘Total’
from Cliente c, DetallePedidos d, Pedidos p
where d.PedidosCodigo = p.PedidosCodigo
and c.ClienteCodigo =p.ClienteCodigo
group by c.ClienteCodigo, c.ClienteNombre, C.ClienteCiudad
— Variables
declare @id nchar(5)
declare @nombre nvarchar (100)
declare @ciudad nvarchar (100)
declare @totalgastado Numeric(9,2)
— Abrir el Cursor
open CursorRuta
— Calcular la media
Declare @Media Numeric(9,2)
Set @Media =
( select sum(d.PedidoPrecio* d.PedidoCantidad *(1-D.PedidoDescuento)) As ‘Suma’
from DetallePedidos d, Pedidos p
where d.PedidosCodigo =p. PedidosCodigo ) /
(select Count(P.PedidosCodigo) from Pedidos P)
— Leer los datos del primer registro del cursor
fetch CursorRuta into @id, @nombre, @ciudad, @totalgastado
— Eliminar el contenido de las tablas
Delete VIP
Delete NORMAL
— Variables para los espacios del reporte.
Declare @NombreMasLargo int Set @NombreMasLargo =
(select max(len(ClienteNombre)) from Cliente) + 2
Declare @CiudadMasLarga int Set @CiudadMasLarga =
(select max(len(ClienteCiudad)) from Cliente) + 2
Print ‘================================== LISTADO ===================================================’
while (@@FETCH_STATUS=0)
begin
if (@totalgastado >= @media)
begin
insert into VIP values (@id, @nombre, @ciudad, @totalgastado)
print @id+’ ‘+ @nombre+ space(@NombreMasLargo – len(@nombre)) +
@ciudad+ space(@CiudadMasLarga – len(@ciudad)) +
cast(@totalgastado as nvarchar (10)) + Space(12)+ ‘VIP’
end
else
begin
insert into NORMAL values (@id,@nombre, @ciudad,@totalgastado)
print @id+’ ‘+ @nombre+ space(@NombreMasLargo – len(@nombre)) +
@ciudad+ space(@CiudadMasLarga – len(@ciudad)) +
cast(@totalgastado as nvarchar (10)) + Space(10) + ‘NORMAL’
End
— Leer el siguiente registro
fetch CursorRuta into @id, @nombre, @ciudad, @totalgastado
end
close CursorRuta
deallocate CursorRuta
end
go
Se puede usar una variable tipo tabla para evitar el uso de la sentencia Print. Ver Cursores con variables tipo tabla.
Al ejecutar el procedimiento almacenado
Execute spClientesVipNormal
go
Es necesario resaltar que el reporte del procedimiento almacenado usando la sentencia Print no se debe incluir en un sistema en producción, además de las consideraciones y cuidados que se deben tener en incluir cursores en los sistemas.
La información en las tablas VIP y NORMAL
Select * from vip
go
Select * from NORMAL
go
