Cursores en Sql Server
CURSORES
EN SQL SERVER
DEFINICIÓN.
En SQL Server un cursor puede
definirse como un elemento que representará a un conjunto de datos determinado
por una consulta T-SQL, el cursor permitirá recorrer fila a fila, leer y
eventualmente modificar dicho conjunto de resultados.
SQL Server ofrecerá una amplia
variedad de opciones y de funciones para crear y operar sobre cursores. En este
artículo, donde hemos utilizado SQL Server 2008 Enterprise Edition en los
ejemplos, daremos un paseo por las posibilidades disponibles.
La creación y utilización de
un cursor estará compuesta, como es de esperarse, por una serie de instrucciones
T-SQL, las cuales podrán separarse en grupos bien diferenciados, los cuales
son: Declaración, Apertura, Acceso a datos, Cierre y Desalojo, a continuación,
detallaremos cada grupo de instrucciones.
Declaración
El primer paso constará de la
declaración del cursor, donde se indicarán (junto con el nombre del cursor) la
consulta que el mismo representará y algunas otras características bastante
interesantes.
Un ejemplo de declaración de
cursor es el siguiente:
DECLARE ProdInfo CURSOR FOR
SELECT Name FROM Production.Product
Donde ProdInfo representará al
nombre del cursor y la sentencia “SELECT ProductNumber,Name FROM
Production.Product” será el conjunto de datos del mismo.
Como comentamos previamente,
es posible en este paso definir algunas características del comportamiento del
cursor, por ejemplo la sentencia:
DECLARE ProdInfo CURSOR
READ_ONLY FOR SELECT Name FROM Production.Product
Indicará que el cursor será de
solo lectura, más adelante veremos en detalle las opciones disponibles, por el
momento nuestro objetivo es crear un cursor lo más simple posible.
Apertura
La apertura del cursor
ejecutará la consulta definida en el paso previo y cargará los datos en el
mismo. La función OPEN de T-SQL permitirá efectuar esta terea, para continuar
con el ejemplo previo la forma de abrir el cursor será la siguiente:
OPEN ProdInfo
Recorrido del cursor y acceso
a los datos
Este paso constará de recorrer
los resultados del cursor, la instrucción FETCH permitirá efectuar dicha
operación. Las filas leídas podrán copiarse a variables utilizando la sentencia
INTO en combinación con la sentencia FETCH, por ejemplo la sentencia:
FETCH NEXT FROM ProdInfo INTO
@Description
Tomará la siguiente fila de
resultados del cursor y lo alojará en la variable @Description.
Un detalle a comentar es que
en la sentencia INTO (como puede verse en el ejemplo anterior) el mapeo entre
columnas del cursor y variables se realizará implícitamente, asignándose la
primera columna a la primera variable, la segunda columna a la segunda variable
y así sucesivamente. Esto implica que deberán crearse tantas variables como
columnas se definan en la declaración del cursor y las mismas deberán ubicarse
en el mismo orden que se encuentran definidas las columnas en la sentencia
SELECT de la declaración.
Como cada sentencia FETCH
leerá un registro, una pregunta interesante que podríamos hacernos es, ¿de qué
manera podremos saber si existe un próximo o previo registro, o si hemos
llegado al límite (ya sea superior o inferior)?. La respuesta se encontrará en
una variable de SQL Server llamada @@FETCH_STATUS que tomará el valor 0 si la
lectura del registro ha sido correcta.
Cierre
del cursor
En el cierre del cursor se
liberarán los registros tomados por el mismo. Una vez que el cursor es cerrado
ya no podrá recorrerse el conjunto de resultados hasta que el mismo sea
reabierto, la sentencia CLOSE cerrará un cursor abierto y la sintaxis puede
verse a continuación:
CLOSE ProdInfo
Desalojo
del cursor
Este paso eliminará la
referencia al cursor definido previamente, por lo que ya no será posible
realizar una reapertura del mismo, en este caso la sintaxis será:
DEALLOCATE ProdInfo
SINTAXIS
DECLARE Employee_Cursor CURSOR
FOR
SELECT LastName, FirstName
FROM AdventureWorks2012.HumanResources.vEmployee
WHERE LastName like 'B%';
OPEN Employee_Cursor;
FETCH NEXT FROM Employee_Cursor;
WHILE @@FETCH_STATUS =
0
BEGIN
FETCH NEXT FROM Employee_Cursor
END;
CLOSE Employee_Cursor;
DEALLOCATE Employee_Cursor
SINTAXIS
DEL DECLARE
ISO Syntax
DECLARE cursor_name [ INSENSITIVE ]
[ SCROLL ] CURSOR
FOR select_statement
[ FOR { READ ONLY
| UPDATE [ OF column_name [ ,...n ] ] }
]
[;]
Transact-SQL Extended Syntax
DECLARE cursor_name CURSOR [ LOCAL | GLOBAL ]
[ FORWARD_ONLY |
SCROLL ]
[ STATIC | KEYSET
| DYNAMIC | FAST_FORWARD ]
[ READ_ONLY |
SCROLL_LOCKS | OPTIMISTIC ]
[ TYPE_WARNING ]
FOR select_statement
[ FOR UPDATE [ OF
column_name [ ,...n ] ] ]
[;]
Argumentos
cursor_name
Es el nombre del cursor de
servidor de Transact-SQL definido. cursor_name debe respetar las reglas de los
identificadores.
INSENSITIVE
Define un cursor que hace una
copia temporal de los datos que utiliza. Todas las solicitudes que se realizan
al cursor se responden desde esta tabla temporal de tempdb; por tanto, las
modificaciones realizadas en las tablas base no se reflejan en los datos
devueltos por las operaciones de captura realizadas en el cursor y, además,
este cursor no admite modificaciones. Cuando se utiliza la sintaxis de ISO, si
se omite INSENSITIVE, las eliminaciones y actualizaciones confirmadas
realizadas en las tablas subyacentes (por cualquier usuario) se reflejan en
capturas posteriores.
SCROLL
Especifica que están disponibles
todas las opciones de captura (FIRST, LAST, PRIOR, NEXT, RELATIVE, ABSOLUTE).
Si no se especifica SCROLL en una instrucción DECLARE CURSOR de ISO, la única
opción de captura que se admite es NEXT. No es posible especificar SCROLL si se
incluye también FAST_FORWARD.
select_statement
Es una instrucción SELECT
estándar que define el conjunto de resultados del cursor. Las palabras clave
FOR BROWSE e INTO no están permitidas en la instrucción select_statement de una
declaración de cursor.
SQL Server convierte
implícitamente el cursor a otro tipo si las cláusulas de la instrucción
select_statement entran en conflicto con la funcionalidad del tipo de cursor
solicitado.
READ
ONLY
Evita que se efectúen
actualizaciones a través de este cursor. No es posible hacer referencia al
cursor en una cláusula WHERE CURRENT OF de una instrucción UPDATE o DELETE.
Esta opción reemplaza la capacidad predeterminada de actualizar el cursor.
UPDATE
[OF column_name [,...n]]
Define las columnas
actualizables en el cursor. Si se especifica OF column_name [,...n], solo las
columnas enumeradas admiten modificaciones. Si se especifica UPDATE sin indicar
una lista de columnas, se pueden actualizar todas las columnas.
cursor_name
Es el nombre del cursor de
servidor de Transact-SQL definido. cursor_name debe respetar las reglas de los
identificadores.
LOCAL
Especifica que el alcance del
cursor es local para el proceso por lotes, procedimiento almacenado o
desencadenador en que se creó el cursor. El nombre del cursor solo es válido en
este ámbito. Es posible hacer referencia al cursor mediante variables de cursor
locales del lote, procedimiento almacenado, desencadenador o parámetro OUTPUT
del procedimiento almacenado. El parámetro OUTPUT se utiliza para devolver el
cursor local al proceso por lotes, procedimiento almacenado o desencadenador
que realiza la llamada, el cual puede asignar el parámetro a una variable de
cursor para hacer referencia al cursor después de que el procedimiento
almacenado finalice. La asignación del cursor se cancela implícitamente cuando
el proceso por lotes, procedimiento almacenado o desencadenador finaliza, a
menos que el cursor se haya devuelto en un parámetro OUTPUT. En ese caso, la
asignación del cursor se cancela cuando se cancela la asignación de la última
variable que hace referencia al mismo o cuando ésta se sale del ámbito.
GLOBAL
Especifica que el alcance del
cursor es global para la conexión. Se puede hacer referencia al nombre del
cursor en cualquier procedimiento almacenado o lote que se ejecute durante la
conexión. La asignación del cursor solo se cancela implícitamente cuando se
produce la desconexión.
Nota
Si no se especifica GLOBAL ni
LOCAL, el valor predeterminado se controla mediante la configuración de la
opción de base de datos default to local cursor.
FORWARD_ONLY
Especifica que el cursor solo
se puede desplazar de la primera a la última fila. FETCH NEXT es la única
opción de captura admitida. Si se especifica FORWARD_ONLY sin las palabras
clave STATIC, KEYSET o DYNAMIC, el cursor funciona como un cursor DYNAMIC.
Cuando no se especifica FORWARD_ONLY ni SCROLL, FORWARD_ONLY es la opción
predeterminada, salvo que se incluyan las palabras clave STATIC, KEYSET o
DYNAMIC. Los cursores STATIC, KEYSET y DYNAMIC utilizan SCROLL como valor
predeterminado. A diferencia de las API de base de datos, como ODBC y ADO,
FORWARD_ONLY se puede utilizar con los cursores STATIC, KEYSET y DYNAMIC de
Transact-SQL.
STATIC
Define un cursor que hace una
copia temporal de los datos que utiliza. Todas las solicitudes que se realizan
al cursor se responden desde esta tabla temporal de tempdb; por tanto, las
modificaciones realizadas en las tablas base no se reflejan en los datos
devueltos por las operaciones de captura realizadas en el cursor y, además,
este cursor no admite modificaciones.
KEYSET
Especifica que la pertenencia
y el orden de las filas del cursor se fijan cuando se abre este cursor. El
conjunto de claves que identifica las filas de forma única está integrado en la
tabla denominada tempdb de keyset.
Nota
Si la consulta hace referencia
por lo menos a una tabla sin un índice único, el cursor de conjunto de claves
se convierte en cursor estático.
Los cambios realizados en
valores de las tablas base que no son de clave, ya sean realizados por el
propietario del cursor o confirmados por otros usuarios, son visibles cuando el
propietario se desplaza por el cursor. Las inserciones realizadas por otros
usuarios no son visibles (no es posible hacer inserciones a través de un cursor
de servidor de Transact-SQL). Si se elimina una fila, un intento para capturar
la fila devuelve un @@FETCH_STATUS de -2. Las actualizaciones de valores de
clave de fuera del cursor son similares a la eliminación de la fila anterior
seguida por la inserción de la nueva fila. La fila con los nuevos valores no
está visible, y los intentos de capturar la fila con los valores anteriores
devuelven un @@FETCH_STATUS de -2. Los nuevos valores están visibles si la
actualización se realiza a través del cursor especificando la cláusula WHERE
CURRENT OF.
DYNAMIC
Define un cursor que, al
desplazarse por él, refleja en su conjunto de resultados todos los cambios
realizados en los datos de las filas. Los valores de los datos, el orden y la
pertenencia de las filas pueden cambiar en cada captura. La opción de captura ABSOLUTE
no se puede utilizar en los cursores dinámicos.
FAST_FORWARD
Especifica un cursor
FORWARD_ONLY, READ_ONLY con las optimizaciones de rendimiento habilitadas. No
se puede especificar FAST_FORWARD si se especifica también SCROLL o FOR_UPDATE.
Nota
FAST_FORWARD y FORWARD_ONLY
pueden usarse en la misma instrucción DECLARE CURSOR.
READ_ONLY
Evita que se efectúen
actualizaciones a través de este cursor. No es posible hacer referencia al
cursor en una cláusula WHERE CURRENT OF de una instrucción UPDATE o DELETE.
Esta opción reemplaza la capacidad predeterminada de actualizar el cursor.
SCROLL_LOCKS
Especifica que existan
garantías de que las actualizaciones o las cancelaciones posicionadas
realizadas a través del cursor se lleven a cabo correctamente. SQL Server
bloquea las filas mientras se leen en el cursor para garantizar su
disponibilidad en modificaciones posteriores. No es posible especificar
SCROLL_LOCKS si se especifica también FAST_FORWARD o STATIC.
OPTIMISTIC
Especifica que las
actualizaciones o las cancelaciones posicionadas realizadas a través del cursor
no se lleven a cabo correctamente si la fila se ha actualizado desde que se
leyó en el cursor. SQL Server no bloquea las filas cuando se leen en el cursor.
En su lugar, usa comparaciones de valores de columna timestamp o un valor de
suma de comprobación si la tabla no tiene columnas timestamp para determinar si
la fila se ha modificado después de leerla en el cursor. Si la fila se ha
modificado, la actualización o eliminación posicionada fracasa. No es posible
especificar OPTIMISTIC si se especifica también FAST_FORWARD.
TYPE_WARNING
Especifica que se envía un
mensaje de advertencia al cliente si el cursor se convierte implícitamente del
tipo solicitado a otro.
select_statement
Es una instrucción SELECT
estándar que define el conjunto de resultados del cursor. Las palabras clave
COMPUTE, COMPUTE BY, FOR BROWSE e INTO no están permitidas en la instrucción
select_statement de una declaración de cursor.
Nota
Puede usar una sugerencia de
consulta en una declaración de cursor, pero si usa también la cláusula FOR
UPDATE OF, debe especificar OPTION (query_hint) después de FOR UPDATE OF.
SQL Server convierte
implícitamente el cursor a otro tipo si las cláusulas de la instrucción
select_statement entran en conflicto con la funcionalidad del tipo de cursor
solicitado. Para obtener más información, vea el tema relativo a las
conversiones de cursor implícitas.
FOR
UPDATE [OF column_name [,...n]]
Define las columnas
actualizables en el cursor. Si se especifica OF column_name [,...n], solo las
columnas enumeradas admiten modificaciones. Si se especifica UPDATE sin una
lista de columnas, se pueden actualizar todas las columnas, a menos que se haya
especificado la opción de simultaneidad READ_ONLY.
ADMINISTRACIÓN DE PROCEDIMIENTOS ALMACENADOS
Actualizar
datos en cursores de SQL Server
Al buscar y actualizar datos a
través de SQL Server cursores, un SQL Server aplicación de consumidor de
proveedor OLE DB de Native Client está limitado por las mismas consideraciones
y restricciones que se aplican a cualquier otra aplicación cliente.
Solo las filas de cursores de
SQL Server participan en el control del acceso simultáneo a datos. Cuando el
consumidor solicita un conjunto de filas modificable, DBPROP_LOCKMODE controla
el control de simultaneidad. Para modificar el nivel de control de acceso
simultáneo, el consumidor establece la propiedad DBPROP_LOCKMODE antes de abrir
el conjunto de filas.
Los niveles de aislamiento de
las transacciones pueden producir diferencias significativas en la posición de
las filas si el diseño de la aplicación cliente permite que las transacciones
permanezcan abiertas durante largos períodos de tiempo. De forma predeterminada,
la SQL Server proveedor Native Client OLE DB utiliza el nivel de aislamiento de
lectura confirmada especificado por DBPROPVAL_TI_READCOMMITTED. El SQL Server
proveedor OLE DB de Native Client admite el aislamiento de lectura de datos
sucio cuando la simultaneidad del conjunto de filas es de solo lectura. Por
consiguiente, el consumidor puede solicitar un nivel superior de aislamiento en
un conjunto de filas modificable pero no puede solicitar con éxito un nivel
inferior.
Modos
de actualización inmediata y retrasada
En el modo de actualización
inmediata, cada llamada a IRowsetChange:: SetData hace un recorrido de ida y a
la SQL Server. Si el consumidor realiza varios cambios en una sola fila,
resulta más eficaz enviar todos los cambios con una sola SetData llamar.
En el modo de actualización
retrasada, se realiza un ida y vuelta a la SQL Server para cada fila indicada
en el cRows y rghRows parámetros de IRowsetUpdate:: Update.
En ambos modos, un viaje de
ida y vuelta representa una transacción distinta cuando no queda abierto ningún
objeto de transacción para el conjunto de filas.
Cuando se utiliza
IRowsetUpdate:: Update, el SQL Server proveedor OLE DB de Native Client intenta
procesar cada fila indicada. Un error que se producen debido a valores de
datos, la longitud o el estado no válidos para cualquier fila no detiene SQL
Server procesamiento del proveedor OLE DB de Native Client. Se pueden modificar
todas o ninguna de las demás filas que participan en la actualización. El
consumidor debe examinar el valor devuelto prgRowStatus matriz para determinar
el error concreta fila cuando la SQL Server proveedor OLE DB de Native Client
devuelve DB_S_ERRORSOCCURRED.
Un consumidor no debe suponer
que las filas se procesan en un orden determinado. Si un consumidor necesita el
procesamiento ordenado de modificación de datos en más de una fila, debe
establecer ese orden en la lógica de la aplicación y abrir una transacción para
incluir en ella el proceso.
Procedimientos CREATE
El procedimiento Create realiza la sentencia INSERT, la cual creará un nuevo registro. Tiene un parámetro para cada columna en la tabla.
IF OBJECT_ID('cusp_CustomerCreate') IS NOT NULL
BEGIN
DROP PROC usp_CustomerCreate
END
GO
CREATE PROCEDURE usp_CustomerCreate
@FirstName varchar(20),
@LastName varchar(20),
@Email varchar(20),
@PhoneNumber int
AS
BEGIN
INSERT INTO Customer (
FirstName,
LastName,
Email,
PhoneNumber)
VALUES (
@FirstName,
@LastName,
@Email,
@PhoneNumber)
SET @CustomerID = SCOPE_IDENTITY()
SELECT
FirstName = @FirstName,
LastName = @LastName,
Email = @Email,
PhoneNumber =@PhoneNumber
FROM Customer
WHERE CustomerID = @CustomerID
END
La línea SET @CustomerID = SCOPE_IDENTITY() captura el valor de identidad. La función SCOPE_IDENTITY()retorna el último valor de identidad insertado en una columna de identidad en el mismo alcance (un procedimiento almacenado, un desencadenador, una función o lote). Dos sentencias están en el mismo alcance si ellas están en el mismo procedimiento almacenado, función o lote.
Procedimientos READ
El procedimiento Leer (Read) lee los registros de la tabla basado en la llave primaria especificada en el parámetro de entrada:
IF OBJECT_ID('cusp_CustomerRead') IS NOT NULL
BEGIN
DROP PROC cusp_CustomerRead
END
GO
CREATE PROC cusp_CustomerRead
@CustomerID int
AS
BEGIN
SELECT CustomerID, FirstName, LastName, Email, PhoneNumber
FROM Customer
WHERE (CustomerID = @CustomerID)
END
GO
Procedimientos UPDATE
El procedimiento Actualizar (Update) realiza una sentencia UPDATE en la tabla basado en la llave primaria para un registro especificado en la cláusula WHERE
de la sentencia. Al igual que el procedimiento Crear, tiene un parámetro para cada columna en la tabla:
IF OBJECT_ID('cusp_CustomerUpdate') IS NOT NULL
BEGIN
DROP PROC cusp_CustomerUpdate
END
GO
CREATE PROC cusp_CustomerUpdate
@CustomerID int,
@FirstName varchar(20),
@LastName varchar(20),
@Email varchar(20),
@PhoneNumber int
AS
BEGIN
UPDATE Customer
SET FirstName = @FirstName,
LastName = @LastName,
Email = @Email,
PhoneNumber = @PhoneNumber
WHERE CustomerID = @CustomerID
END
GO
Procedimientos DELETE
El procedimiento Eliminar (Delete) elimina una fila especificada en la cláusula WHERE:
IF OBJECT_ID('cusp_CustomerDelete') IS NOT NULL
BEGIN
DROP PROC cusp_CustomerDelete
END
GO
CREATE PROC cusp_CustomerDelete
@CustomerID int
AS
BEGIN
DELETE
FROM Customer
WHERE CustomerID = @CustomerID
END
GO
Procedimientos CREATE
El procedimiento Create realiza la sentencia INSERT, la cual creará un nuevo registro. Tiene un parámetro para cada columna en la tabla.
IF OBJECT_ID('cusp_CustomerCreate') IS NOT NULL
BEGIN
DROP PROC usp_CustomerCreate
END
GO
CREATE PROCEDURE usp_CustomerCreate
@FirstName varchar(20),
@LastName varchar(20),
@Email varchar(20),
@PhoneNumber int
AS
BEGIN
INSERT INTO Customer (
FirstName,
LastName,
Email,
PhoneNumber)
VALUES (
@FirstName,
@LastName,
@Email,
@PhoneNumber)
SET @CustomerID = SCOPE_IDENTITY()
SELECT
FirstName = @FirstName,
LastName = @LastName,
Email = @Email,
PhoneNumber =@PhoneNumber
FROM Customer
WHERE CustomerID = @CustomerID
END
La línea SET @CustomerID = SCOPE_IDENTITY() captura el valor de identidad. La función SCOPE_IDENTITY()retorna el último valor de identidad insertado en una columna de identidad en el mismo alcance (un procedimiento almacenado, un desencadenador, una función o lote). Dos sentencias están en el mismo alcance si ellas están en el mismo procedimiento almacenado, función o lote.
Procedimientos READ
El procedimiento Leer (Read) lee los registros de la tabla basado en la llave primaria especificada en el parámetro de entrada:
IF OBJECT_ID('cusp_CustomerRead') IS NOT NULL
BEGIN
DROP PROC cusp_CustomerRead
END
GO
CREATE PROC cusp_CustomerRead
@CustomerID int
AS
BEGIN
SELECT CustomerID, FirstName, LastName, Email, PhoneNumber
FROM Customer
WHERE (CustomerID = @CustomerID)
END
GO
Procedimientos UPDATE
El procedimiento Actualizar (Update) realiza una sentencia UPDATE en la tabla basado en la llave primaria para un registro especificado en la cláusula WHERE
de la sentencia. Al igual que el procedimiento Crear, tiene un parámetro para cada columna en la tabla:
IF OBJECT_ID('cusp_CustomerUpdate') IS NOT NULL
BEGIN
DROP PROC cusp_CustomerUpdate
END
GO
CREATE PROC cusp_CustomerUpdate
@CustomerID int,
@FirstName varchar(20),
@LastName varchar(20),
@Email varchar(20),
@PhoneNumber int
AS
BEGIN
UPDATE Customer
SET FirstName = @FirstName,
LastName = @LastName,
Email = @Email,
PhoneNumber = @PhoneNumber
WHERE CustomerID = @CustomerID
END
GO
Procedimientos DELETE
El procedimiento Eliminar (Delete) elimina una fila especificada en la cláusula WHERE:
IF OBJECT_ID('cusp_CustomerDelete') IS NOT NULL
BEGIN
DROP PROC cusp_CustomerDelete
END
GO
CREATE PROC cusp_CustomerDelete
@CustomerID int
AS
BEGIN
DELETE
FROM Customer
WHERE CustomerID = @CustomerID
END
GO
EJEMPLOS
DE CURSORES
A. Uso de cursores simples y
su sintaxis
El conjunto de resultados
generado al abrir este cursor contiene todas las filas y todas las columnas de
la tabla. Este cursor se puede actualizar, y todas las actualizaciones y
eliminaciones se representan en las búsquedas realizadas para este cursor.
FETCH NEXT es la única búsqueda disponible porque la opción SCROLL no se ha
especificado.
EJEMPLO
DE DECLARE.
DECLARE vend_cursor CURSOR
FOR SELECT * FROM Purchasing.Vendor
OPEN vend_cursor
FETCH NEXT FROM vend_cursor
Uso de cursores anidados
para elaborar resultados de informes
En el ejemplo siguiente se
muestra cómo se pueden anidar los cursores para elaborar informes complejos. El
cursor interno se declara para cada proveedor.
EJEMPLO
DE CURSOR COMPLETO.
SET NOCOUNT ON;
DECLARE @vendor_id int, @vendor_name nvarchar(50),
@message varchar(80), @product nvarchar(50);
PRINT '-------- Vendor Products Report --------';
DECLARE vendor_cursor CURSOR FOR
SELECT VendorID, Name
FROM Purchasing.Vendor
WHERE PreferredVendorStatus =
1
ORDER BY VendorID;
OPEN vendor_cursor
FETCH NEXT FROM vendor_cursor
INTO @vendor_id, @vendor_name
WHILE @@FETCH_STATUS =
0
BEGIN
PRINT ' '
SELECT @message = '----- Products From Vendor: ' +
@vendor_name
PRINT @message
-- Declare an
inner cursor based
-- on vendor_id
from the outer cursor.
DECLARE product_cursor CURSOR
FOR
SELECT v.Name
FROM Purchasing.ProductVendor pv, Production.Product v
WHERE pv.ProductID = v.ProductID AND
pv.VendorID = @vendor_id -- Variable value
from the outer cursor
OPEN product_cursor
FETCH NEXT FROM product_cursor INTO @product
IF @@FETCH_STATUS <>
0
PRINT '
<<None>>'
WHILE @@FETCH_STATUS =
0
BEGIN
SELECT @message = ' ' + @product
PRINT @message
FETCH NEXT FROM product_cursor INTO @product
END
CLOSE product_cursor
DEALLOCATE product_cursor
-- Get the
next vendor.
FETCH NEXT FROM vendor_cursor
INTO @vendor_id, @vendor_name
END
CLOSE vendor_cursor;
DEALLOCATE vendor_cursor;
EJEMPLO
MÁS SENCILLO DE CURSOR
A continuación, crearemos el
ejemplo más simple de un cursor el cual recorrerá la tabla
Production.ProductDescription de la base de datos AdventureWorks y mostrará por
pantalla el valor del registro Description luego que el mismo ha sido copiado a
la variable @Description.
DECLARE @Description AS nvarchar(400)
DECLARE ProdInfo CURSOR FOR SELECT [Description] FROM Production.ProductDescription
OPEN ProdInfo
FETCH NEXT FROM ProdInfo INTO @Description
WHILE @@fetch_status = 0
BEGIN
PRINT @Description
FETCH NEXT FROM ProdInfo INTO @Description
END
CLOSE ProdInfo
DEALLOCATE ProdInfo
En el ejemplo puede verse la
utilización de la variable @@FETCH_STATUS para verificar la existencia de la
fila leída, por otra parte, la sentencia FETCH NEXT devolverá la próxima fila
de datos, siempre que la misma exista.
Este ejemplo, a pesar de
parecer tan trivial y claro esconde una amplia variedad de comportamientos que
el cursor asumirá, y que en este caso han tomado el valor por defecto, ya que
no los hemos definido explícitamente.
El próximo paso será dar una
recorrida por estas opciones y entender cuáles se adaptarán mejor a nuestras
necesidades.
RESUMEN
En SQL Server un cursor puede
definirse como un elemento que representará a un conjunto de datos determinado
por una consulta T-SQL, el cursor permitirá recorrer fila a fila, leer y
eventualmente modificar dicho conjunto de resultados.
DECLARACIÓN
El primer paso constará de la
declaración del cursor, donde se indicarán (junto con el nombre del cursor) la
consulta que el mismo representará y algunas otras características bastante
interesantes.
APERTURA
La apertura del cursor ejecutará la
consulta definida en el paso previo y cargará los datos en el mismo. La función
OPEN de T-SQL permitirá efectuar esta terea, para continuar con el ejemplo
previo la forma de abrir el cursor
CIERRE
En el cierre del cursor se
liberarán los registros tomados por el mismo. Una vez que el cursor es cerrado
ya no podrá recorrerse el conjunto de resultados hasta que el mismo sea
reabierto, la sentencia CLOSE cerrará un cursor abierto.
DESALOJO
Este paso eliminará la
referencia al cursor definido previamente, por lo que ya no será posible
realizar una reapertura del mismo.
SINTAXIS
DECLARE @COL1 VARCHAR(50),@COL2 VARCHAR(30),@COL3 VARCHAR(30),@COL4 INT
--DECLARACION
DEL CURSOR
DECLARE C_ALUMNO_HERMANOS CURSOR
FOR
SELECT Nombres,ApellidoPaterno,ApellidoMaterno,NroHermanos FROM Alumno
WHERE NroHermanos>0
--ABRIR
DATOS
OPEN C_ALUMNO_HERMANOS
--LISTAR
DATOS
FETCH C_ALUMNO_HERMANOS INTO
@COL1,@COL2,@COL3,@COL4
WHILE(@@FETCH_STATUS=0)
BEGIN
PRINT @COL1 +' | '+ @COL2+' | '+@COL3+' | '+RTRIM(STR(@COL4))
FETCH C_ALUMNO_HERMANOS INTO
@COL1,@COL2,@COL3,@COL4
END
--CERRAR
CURSOR
CLOSE C_ALUMNO_HERMANOS
--DESALOJAR
DEALLOCATE C_ALUMNO_HERMANOS
Procedimientos CREATE
El procedimiento Create realiza la sentencia INSERT, la cual creará un nuevo registro. Tiene un parámetro para cada columna en la tabla
Procedimientos UPDATE
El procedimiento Actualizar (Update) realiza una sentencia UPDATE en la tabla basado en la llave primaria para un registro especificado en la cláusula WHERE de la sentencia. Al igual que el procedimiento Crear, tiene un parámetro para cada columna en la tabla.
Procedimientos DELETE
El procedimiento Eliminar (Delete) elimina una fila especificada en la cláusula WHERE.
SUMMARY
In SQL Server a cursor can be
defined as an element that will represent a set of data determined by a T-SQL
query, the cursor will allow to go through row by row, read and possibly modify
said set of results.
DECLARATION
The first step will consist of
the declaration of the cursor, where they will indicate (along with the name of
the cursor) the query that it will represent and some other interesting
features.
OPENING
The cursor opening will
execute the query defined in the previous step and will load the data in it.
The OPEN function of T-SQL will allow to carry out this task, to continue with
the previous example the way to open the cursor
CLOSING
At the closing of the cursor
the records taken by it will be released. Once the cursor is closed the result
set can not be traversed until it is reopened, the CLOSE statement will close
an open cursor.
EVICTION
This step will eliminate the
reference to the previously defined cursor, so it will no longer be possible to
reopen it.
SYNTAX
DECLARE @ COL1 VARCHAR (50), @
COL2 VARCHAR (30), @ COL3 VARCHAR (30), @ COL4 INT
- CURSOR DECLARATION
DECLARE C_ALUMNO_HERMANOS CURSOR FOR
SELECT Names, LastNamePaterno,
LastNameMaterno, NroHermanos FROM Student
WHERE NroHermanos> 0
--DO DATA
OPEN C_ALUMNO_HERMANOS
--LIST DATA
FETCH C_ALUMNO_HERMANOS INTO @
COL1, @ COL2, @ COL3, @ COL4
WHILE (@@ FETCH_STATUS = 0)
BEGIN
PRINT @ COL1 + '| '+ @ COL2 +'
| '+ @ COL3 +' | '+ RTRIM (STR (@ COL4))
FETCH C_ALUMNO_HERMANOS INTO @
COL1, @ COL2, @ COL3, @ COL4
END
--CLOSE CURSOR
CLOSE C_ALUMNO_HERMANOS
--EVICT
DEALLOCATE C_ALUMNO_HERMANOS
CREATE
procedures
The Create procedure performs
the INSERT statement, which will create a new record. It has a parameter for
each column in the table.
UPDATE
procedures
The Update procedure performs
an UPDATE statement on the table based on the primary key for a record specified
in the WHERE clause of the statement. Like the Create procedure, it has a
parameter for each column in the table.
DELETE
procedures
The Delete procedure deletes a
row specified in the WHERE clause.
RECOMENDACIONES
De forma general se podría
decir que:
Si la consulta es veloz y toma
pocos segundos y no va a cambiar esa velocidad a medida que aumente el tamaño
la base de datos (al menos no de forma repentina), se podría mantener los
cursores.
Sin embargo, si usted cree que
los datos que manejan los cursores van a aumentar a miles o peor a millones de
filas, vale la pena reemplazar el cursor.
Los cursores en general son
utilizados por gente que se siente cómodo con el manejo secuencial, pero el
cursor en sí es lento. Bloquea datos y consume muchos recursos.
Si no tiene otra alternativa a
cursores, recomendamos usar la opción FAST_FORWARD (si es aplicable) y
READ_ONLY para optimizar los cursores.
CONCLUSIÓN
Los cursores son muy útiles
sobre todo cuando no podemos tener todas las columnas que necesitamos en un
solo SELECT. Si podemos tener a todas las columnas en un solo SELECT puede ser
más práctico usar el comando SELECT … INTO
Como el Firebird nos permite
tener abiertos dos, tres, o más cursores al mismo tiempo entonces podemos
extraer de cada uno de ellos las columnas que necesitamos para procesarlas.
Muchas veces, en lugar de
estar escribiendo un SELECT complicado, lleno de JOINs o de UNIONs es mucho más
fácil, rápido y sencillo, usar varios cursores, se nos simplifica la vida de
esta manera.
Los cursores son una gran
facilidad que el Firebird nos provee, hay que utilizarlos para que nuestro
código fuente sea más legible.
APRECIACIÓN DEL EQUIPO
Los cursores nos amplían el
procesamiento de los resultados porque permiten situarse en filas específicas
del conjunto de resultados y eso nos da una ventaja de mejor acceso a la información
de la base de datos. También recuperan una fila o un bloque de filas y aceptan
modificaciones de los datos de las filas en la posición actual del conjunto de resultados.
En si los cursores nos facilitan o simplifican la manera de acceso a nuestra información.
GLOSARIO
READ
ONLY
Evita que se efectúen
actualizaciones a través de este cursor. No es posible hacer referencia al
cursor en una cláusula WHERE CURRENT OF de una instrucción UPDATE o DELETE.
SCROLL
Especifica que están disponibles
todas las opciones de captura (FIRST, LAST, PRIOR, NEXT, RELATIVE, ABSOLUTE).
LOCAL
Especifica que el alcance del
cursor es local para el proceso por lotes, procedimiento almacenado o
desencadenador en que se creó el cursor. El nombre del cursor solo es válido en
este ámbito.
SCROLL_LOCKS
Especifica que existan
garantías de que las actualizaciones o las cancelaciones posicionadas
realizadas a través del cursor se lleven a cabo correctamente.
FAST_FORWARD
Especifica un cursor
FORWARD_ONLY, READ_ONLY con las optimizaciones de rendimiento habilitadas. No
se puede especificar FAST_FORWARD si se especifica también SCROLL o FOR_UPDATE.
TYPE_WARNING
Especifica que se envía un
mensaje de advertencia al cliente si el cursor se convierte implícitamente del
tipo solicitado a otro.
KEYSET
Especifica que la pertenencia
y el orden de las filas del cursor se fijan cuando se abre este cursor. El
conjunto de claves que identifica las filas de forma única está integrado en la
tabla denominada tempdb de keyset.
LINKOGRAFIA
Creo que puedo ampliar e indicar los motivos para evitarlos
ResponderEliminarhttps://thedevelopmentstages.com/ejemplos-de-cursores-en-sql-server/