Cursores

Como crear cursores en SQL Server

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.

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



Como crear un cursor en SQL Server
1. Para declarar el cursor

DECLARE NombreCursor [Scroll] 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á ciempre que la función @@FETCH_STATUS sea igual a CERO. La función @@FETCH_STATUS reporta CERO (0) cuando la instrucción Fetch 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

Cursor de tipo Scroll

Los cursores de tipo Scroll se pueden recorrer hacia adelante o hacia atrás. Para un cursor de tipo Scroll se pueden usar:  Firts, Next, Prior, Last, Relative n, Absolute n para mostrar los diferentes registros.

4. Cerrar el cursor

CLOSE NombreDelCursor

5. Liberar el espacio de memoria ocupado por el cursor

DEALLOCATE NombreDelCursor

Ejercicios

  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 y reporta -1 si ya no existen registros para mostrar. (Ver Funciones de Cursores)

select @@FETCH_STATUS
go
— Cerrar el cursor, liberar de memoria
close cursorEmpleados
Deallocate cursorEmpleados
go

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

Cursor de tipo Scroll

3. 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



Como crear cursores anidados en SQL Server

4. 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

 

5. 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