Cursores
Los cursores permiten almacenar los datos de una consulta T-SQL en memoria y poder manipular los datos de los elementos resultantes para realizar operaciones con ellos. Se recomienda su uso con cierto cuidado porque consume mucha memoria, la instrucción select que llena el cursor debe ser lo más selectiva posible.
Proceso para declarar, abrir, usar, cerrar y liberar los datos de un cursor
1. Declarar el cursor, utilizando DECLARE Cursor
2. Abrir el cursor, utilizando OPEN
3. Leer los datos del cursor, utilizando FETCH … INTO
4. Cerrar el cursor, utilizando CLOSE
5. Liberar el cursor, utilizando DEALLOCATE
Las instrucciones reducidas para los pasos anteriores son:
1. Para declarar el cursor
DECLARE NombreCursor CURSOR FOR Instrucción Select
2. Abrir el cursor
OPEN NombreDelCursor
3. Lectura de los datos del cursor, va a depender del tipo de cursor. Hay de avance hacia adelante solamente y Scroll.
FETCH NombreDelCursor
Si se va a recorrer el cursor, se debe almacenar los datos de cada registro en variables previamente definidas con la variante siguiente:
FETCH NombreDelCursor INTO ListaVariables
Para lectura de manera automática de los registros del cursor se usa la estructura While, el bucle de esta estructura se ejecutará siempre que la función @@FETCH_STATUS sea igual a CERO. La función @@FETCH_STATUS reporta CERO (0) cuando la instrucción Fetch lee un registro. Al finalizar @@FETCH_STATUS toma el valor de -1.
FETCH NombreDelCursor INTO ListaVariables
WHILE (@@FETCH_STATUS = 0)
BEGIN
… instrucciones del bloque
FETCH NombreDelCursor INTO ListaVariables
END — FIN DEL BUCLE WHILE
4. Cerrar el cursor
CLOSE NombreDelCursor
5. Liberar el espacio de memoria ocupado por el cursor
DEALLOCATE NombreDelCursor
Ejercicios
Ejercicio 1
Cursor que reporta los Empleados
Declare cursorEmpleados cursor for
select EmployeeID, LastName, FirstName from Employees
go
— Abrir el cursor
open cursorEmpleados
go
— Los datos disponibles se visualizar con Fetch, ejecutar varias veces para ver los resultados.
Fetch cursorEmpleados
go
— La función @@FETCH_STATUS reporta CERO si hay registro
— reporta -1 si ya no existen registros para mostrar
select @@FETCH_STATUS
go
— Cerrar el cursor, liberar de memoria
close cursorEmpleados
Deallocate cursorEmpleados
go
Ejercicio 2
Cursor que reporte la lista de productos, si las unidades en orden son mayores al stock, mostrar COMPRAR URGENTE, de lo contrario mostrar STOCK ADECUADO
Nota: El ejercicio muestra mensajes, los que en la práctica no son realmente útiles.
Declare cursorProductoComprasUrgente cursor for
SELECT P.ProductID, P.ProductName,
P.UnitsInStock, P.UnitsOnOrder
from Products As P
Open cursorProductoComprasUrgente
Declare @Codigo int, @Descripcion nvarchar(40),
@Stock Numeric(9,2), @PorAtender Numeric(9,2)
Fetch cursorProductoComprasUrgente into @Codigo,
@Descripcion, @Stock, @PorAtender
Print ‘================ LISTADO ======================’
While (@@FETCH_STATUS = 0)
Begin
Declare @Mensaje nvarchar(20)
If (@PorAtender > @Stock)
Begin
Set @Mensaje = ‘COMPRAR URGENTE’
End
Else
Begin
Set @Mensaje = ‘STOCK ADECUADO’
End
— reportar el registro y luego leer el siguiente
Print ‘Código: ‘ + STR(@Codigo)
Print ‘Descripción: ‘ + @Descripcion + ‘ Stock: ‘ + Ltrim(STR(@Stock)) + ‘ Por Atender: ‘ + Ltrim(Str(@PorAtender))
Print ‘Mensaje: ‘ + @Mensaje
Print »
Print ‘————————————————‘
Fetch cursorProductoComprasUrgente into @Codigo,
@Descripcion, @Stock, @PorAtender
End
Close cursorProductoComprasUrgente
Deallocate cursorProductoComprasUrgente
go
El resultado se muestra en la siguiente imagen
Cursor de tipo Scroll
Para un cursor de tipo Scroll se pueden usar: Firts, Next, Prior, Last, Relative n,
Absolute n para mostrar los diferentes registros.
Cursor Scroll para Categorias
Declare cursorCategorias Scroll cursor
for select * from Categories
Open cursorCategorias
go
Note que en la definición del cursor se ha utilizado la palabra Scroll.
— Mostrar los datos
Fetch cursorCategorias
go
— Registro 6
Fetch Absolute 6 from cursorCategorias
go
— Siguiente
Fetch Next from cursorCategorias
go
— Anterior
Fetch Prior from cursorCategorias
go
— Último
Fetch Last from cursorCategorias
go
— Tres anteriores
Fetch Relative -3 from cursorCategorias
go
— Dos hacia adelante
Fetch Relative 2 from cursorCategorias
go
— Primero
Fetch First from cursorCategorias
go
— Cerrar y Liberar
Close cursorCategorias
Deallocate cursorCategorias
go
Cursores anidados
— Listado de las categorías y sus productos
Declare cursorCategoriasListadoProductos cursor for
select C.CategoryID, C.CategoryName from Categories As C
Open cursorCategoriasListadoProductos
Declare @CodigoCategoria int, @NombreCategoria nvarchar(15)
Fetch cursorCategoriasListadoProductos into @CodigoCategoria, @NombreCategoria
Print ‘============================================================’
Print ‘============ Listado de Productos por categoria ==========’
Print ‘============================================================’
Print »
While (@@FETCH_STATUS = 0) — Recorre el cursor de Categorías
Begin
Print ‘Código Categoria: ‘ + Ltrim(Str(@CodigoCategoria)) + Space(2) +
‘Categoría: ‘ + @NombreCategoria
— Definir el Cursor para los productos – CURSOR ANIDADO
Declare cursorProductosCategoria cursor for
select P.ProductID, P.ProductName, P.UnitPrice from Products As P
where P.CategoryID = @CodigoCategoria
Open cursorProductosCategoria
Declare @CodigoProducto int, @NombreProducto nvarchar(40),
@Precio Numeric(9,2)
Fetch cursorProductosCategoria into @CodigoProducto, @NombreProducto, @Precio
Print »
Print ‘====================== PRODUCTOS ==========================’
Print ‘ CODIGO DESCRIPCION PRECIO ‘
While (@@FETCH_STATUS = 0) — Estructura para Productos
Begin
Print Space(5) + Ltrim(str(@CodigoProducto)) + Space(10) + @NombreProducto + space(20) + Ltrim(str(@Precio))
Fetch cursorProductosCategoria into @CodigoProducto, @NombreProducto, @Precio
End — Final While de Productos
Print »
Close cursorProductosCategoria
Deallocate cursorProductosCategoria
— Leer la siguiente categoria
Fetch cursorCategoriasListadoProductos into @CodigoCategoria, @NombreCategoria
End — Final While de Categorias
Close cursorCategoriasListadoProductos
Deallocate cursorCategoriasListadoProductos
go
Incluir Cantidad de Productos, Valor del Stock por categoria
Cantidad Total del Productos y Valor total del Stock
Es necesario usar Acumuladores.
Declare cursorCategorias cursor for
select CategoryID, CategoryName from Categories
Open cursorCategorias
Declare @CodigoCategoria Integer, @NombreCategoria nvarchar(15)
Fetch cursorCategorias into @CodigoCategoria, @NombreCategoria
Declare @CantidadTotaldeProductos Numeric(9,2), @ValorTotaldelStock Numeric(9,2) — Variables para totales
set @CantidadTotaldeProductos = 0
Set @ValorTotaldelStock = 0
Print ‘=====================================’
Print ‘=====PRODUCTOS POR CATEGORIA=========’
Print ‘=====================================’
While (@@FETCH_STATUS = 0)
Begin
Print ‘Código Categoria: ‘ + Ltrim(Str(@CodigoCategoria))
Print ‘Nombre Categoria: ‘ + Ltrim(@NombreCategoria)
Print ‘===================================================’
— Definir el cursor para los productos de la categoria actual
Declare cursorProductoPorCategoria cursor for
select ProductID, ProductName, UnitPrice, UnitsInStock from Products
where CategoryID = @CodigoCategoria
Open cursorProductoPorCategoria
Declare @CodigoProducto Integer, @NombreProducto nvarchar(40), @Precio Numeric(9,2), @Stock Numeric(9,2)
Fetch cursorProductoPorCategoria into @CodigoProducto, @NombreProducto, @Precio, @Stock
Declare @CantidadProductosPorCategoria Numeric(9,2), @ValorStockPorCategoria Numeric(9,2)
Set @CantidadProductosPorCategoria = 0
Set @ValorStockPorCategoria = 0
While (@@FETCH_STATUS = 0) — Para Productos
Begin
Print ‘Código: ‘ + Ltrim(Str(@CodigoProducto)) + ‘ Descripción: ‘ + Ltrim(@NombreProducto)
— Acumular
Set @CantidadProductosPorCategoria = @CantidadProductosPorCategoria + @Stock
Set @ValorStockPorCategoria = @ValorStockPorCategoria + @Precio* @Stock
Fetch cursorProductoPorCategoria into @CodigoProducto, @NombreProducto, @Precio,@Stock
End
Print ‘Cantidad Productos: ‘ + Ltrim(Str(@CantidadProductosPorCategoria))
Print ‘Valor del Stock: ‘ + Ltrim(Str(@ValorStockPorCategoria))
Close cursorProductoPorCategoria
Deallocate cursorProductoPorCategoria
Print »
— Acumulado Total
Set @CantidadTotaldeProductos = @CantidadTotaldeProductos + @CantidadProductosPorCategoria
Set @ValorTotaldelStock = @ValorTotaldelStock + @ValorStockPorCategoria
Fetch cursorCategorias into @CodigoCategoria, @NombreCategoria
End
Print ‘Cantidad Total Productos: ‘ + Ltrim(Str(@CantidadTotaldeProductos))
Print ‘Valor Total del Stock: ‘ + Ltrim(Str(@ValorTotaldelStock))
Close cursorCategorias
Deallocate cursorCategorias
go
El resultado se muestra para la primera categoría en la siguiente imagen
