Introducción, conceptos y definiciones básicas
Los cursores en SQL Server pueden definirse como un elemento que representará a un conjunto de datos determinado por una consulta T-SQL. Los cursores permiten recorrer fila a fila, leer y eventualmente modificar dicho conjunto de resultados.
Existe una amplia variedad de opciones y de funciones para crear y operar sobre cursores en SQL Server. En este artículo, donde hemos utilizado SQL Server 2008 Enterprise Edition para los ejemplos, damos 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. Estos son: Declaración, Apertura, Acceso a datos, Cierre y Desalojo. A continuación detallamos 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 representa el nombre del cursor y la sentencia “SELECT ProductNumber,Name FROM Production.Product” es el conjunto de datos del mismo.
Como comentamos previamente, en este paso es posible definir algunas características del comportamiento del cursor, por ejemplo la sentencia:
DECLARE ProdInfo CURSOR READ_ONLY FOR SELECT Name FROM Production.Product
Indica 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 permite 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 consta de recorrer los resultados del cursor. La instrucción FETCH permite 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:
FETCH NEXT FROM ProdInfo INTO @Description
Esta sentencia toma la siguiente fila de resultados del cursor y lo aloja 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 realiza 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.
En este punto será también posible modificar o eliminar las filas que se van recorriendo, como veremos al final del artículo.
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
Ejemplo de cursor
A continuación juntaremos todos los pasos descriptos previamente y 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 un 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 cuales se adaptarán mejor a nuestras necesidades.
Opciones y más opciones
Hasta este momento todo es muy sencillo, una pregunta que al menos a mí me parece interesante de hacernos es ¿qué sucederá si mientras estamos recorriendo los registros en nuestro cursor los datos son modificados externamente?, para poder representar este escenario una posibilidad es ejecutar el cursor que habíamos definido previamente en modo debug en el SQL Server Management Studio, como se muestra a continuación:
Si mientras los datos son recorridos por el cursor, (En el ejemplo puede verse que la variable @Description ha tomado el valor “Serious back-country riding….”) en forma paralela ejecutamos una consulta de actualización (mientras dejamos el cursor esperando en la sentencia FETCH) desde otro SQL Server Management Studio, como se muestra en el siguiente ejemplo:
Notaremos que en la próxima iteración cuando el comando FETCH es ejecutado, los cambios actualizados externamente serán automáticamente leídos. En el ejemplo que hemos propuesto, puede verse que la variable @Description ha tomado el valor “!A true multi-sport bike that offers…” agregándose el signo «!» al comienzo del contenido del campo Description. Si probamos eliminar o insertar registros notaremos que los cambios son también reflejados.
Este comportamiento dependerá del tipo de cursor a crearse y se determinará en el momento de su definición, para este caso específico, el tipo de cursor se denominará cursor dinámico y es la definición asumida por defecto, aunque puede definirse explícitamente de la siguiente forma:
DECLARE ProdInfo CURSOR DYNAMIC FOR SELECT….
Otra posibilidad disponible es la siguiente:
DECLARE ProdInfo CURSOR STATIC FOR SELECT…..
Si realizamos las mismas pruebas veremos que el comportamiento del cursor es completamente distinto y la actualización (tanto como las inserciones y eliminaciones de registros) externas serán ignorada hasta que el cursor sea reabierto.
La tercera opción disponible es la denominada KEYSET que permitirá al cursor detectar las actualizaciones y eliminaciones, pero no registros insertados, la forma de definir un cursor KEYSET es la siguiente:
DECLARE ProdInfo CURSOR KEYSET FOR SELECT….
Hay un detalle que creo que es muy interesante en este punto, para poder dar un ejemplo, supongamos que poseemos la tabla llamada Ejemplo1 (con unos pocos registros insertados), como se muestra en la imagen a continuación:
Y definimos un cursor KEYSET que recorrerá la tabla mientras que paralelamente realizamos modificaciones (UPDATES) externamente sobre los registros que el cursor va recorriendo, al igual que lo habíamos hecho previamente sobre la tabla Production.ProductDescription.
Si hacemos esta prueba veremos que algo extraño sucede, y es que el cursor se comporta como un cursor STATIC a pesar de haberse definido como KEYSET. Si observamos más detalladamente la estructura de la tabla Ejemplo1 notaremos otro detalle, y es que no hay clave primaria definida. Si definimos una clave primaria (obviamente sobre la columna ID en este caso) todo funcionará según lo esperado.
En este caso aunque a primera vista este comportamiento parezca incomprensible, es en realidad bastante lógico, ya que todo cursor KEYSET copiará las claves de la consulta que recorrerá en una tabla temporal en la base de datos tempdb, y utilizará esta clave para acceder a las tablas originales, pero en este caso no hay ninguna clave que pueda copiar, el cursor no tiene forma de referenciar a la tabla original. La estrategia que tomará el cursor bajo esta situación es copiar directamente todas las filas a una tabla temporal en tempdb y esta es exactamente la estrategia que utilizan los cursores STATIC (creo esta observación nos servirá de paso para cuestionarnos si los cursores STATIC o KEYSET son la opción más deseable en temas relacionados con la performance). La conclusión de esta prueba es que en ciertas ocasiones un cursor de un tipo determinado podrá modificar su comportamiento dependiendo de otras condiciones, este comportamiento es conocido como conversión implícita de cursor. Más adelante retormaremos este concepto.
Finalmente existe una cuarta opción denominada FAST_FORWARD, la cual creará un cursor de solo avance y solo lectura. Aún no hemos mencionado que significa un cursor de solo avance ni un cursor de solo lectura, pero lo haremos en unos pocos párrafos.
Si nos detenemos en la sentencia FETCH del código que hemos escrito hasta el momento podría parecernos interesante el hecho que la misma se encuentra acompañada de la sentencia NEXT, la cual como hemos visto en los ejemplos devuelve la próxima fila de resultados, pero no es de sorprender que exista además la posibilidad de retroceder el cursor para acceder la fila anterior a la actual. Las sentencias FIRST, PRIOR, NEXT y LAST (en combinación con la sentencia FETCH) perimirán acceder a la primera, previa, próxima y última fila respectivamente. Por otra parte las opciones ABSOLUTE y RELATIVE permitirán avanzar o retroceder una cantidad determinada de registros (en forma absoluta o relativa al registro actual) pudiéndose utilizar, además de constantes, variables del tipo smallint, tinyint o int para indicar la magnitud del desplazamiento. En el siguiente ejemplo (que si bien no parece darle mucha importancia al algoritmo del viajero) se muestran algunas de estas posibilidades:
Para que todas estas opciones de desplazamiento se encuentren disponibles el cursor deberá definirse como SCROLL, que es la opción por defecto, por lo que no es necesario definirlo explícitamente, pero podrá hacerse tal como se muestra en el ejemplo previo.
Otra posibilidad es la de definir el cursor como FORWARD_ONLY. Esta opción, como su nombre nos indica, no permitirá realizar movimientos de retroceso en el cursor, en realidad la opción es aún más restrictiva y solo permitirá efectuar operaciones FETCH NEXT, pero a cambio nos bridará una mucho mejor performance que la opción SCROLL.
Actualización de datos y modos de bloqueo
Hasta el momento, los cursores que hemos visto, realizaban operaciones de lectura de datos, no vale la pena mencionar que es totalmente factible incluir operaciones de actualización de datos mientras que los mismos son leídos, como se muestra en el ejemplo:
No me imagino una opción menos performante para actualizar la columna Description, pero omitamos ese detalle, ya que solo hemos incluido estas líneas de código para mencionar que es posible definir cursores actualizables y realizar la misma tarea de la siguiente forma (aunque obviamente la performance sigue siendo igual de lamentable):
Un detalle a mencionar es que en este caso el cursor deberá ser DYNAMIC o KEYSET (siempre que existan las claves primarias necesarias para la segunda opción) ya que en caso contrario la actualización fallará.
Es posible incluso indicar en el cursor que columnas se modificarán, utilizando la sentencia FOR UPDATE FOR, (en caso contrario se asumirá que cualquier columna podrá modificarse, como sucede en el ejemplo previo). Para probarlo realizaremos la siguiente modificación en el codigo:
Se ha producido un error debido a que la columna “Description” no ha sido informada como una columna de modificación. La modificación funcionará correctamente si la columna Description es agregada a la lista de campos modificables.
Cabe mencionar que la sentencia FOR UPDATE OF no protegerá al campo generando un error si el registro es eliminado, para verificar esto podrá reemplazarse la sentencia UPDATE por DELETE como se muestra a continuación:
DELETE FROM Production.ProductDescription WHERE CURRENT OF ProdInfo
En este punto, donde ya nos hemos formulado infinidad de preguntas, podremos continuar con nuestras interrogantes y peguntarnos si la actualización utilizando las sentencia UPDATE o DELETE con WHERE CURRENT OF tendrá algún detalle del cual no nos hemos percatado aún. Si estamos tranquilos porque en los casos previos todo ha funcionado según lo esperado, deberíamos hacer la siguiente prueba; En un cursor de modificación luego de que una fila es leída por el cursor, pero antes que el mismo la modifique, la modificaremos externamente, por ejemplo utilizando otro SQL Server Management Studio.
Realizar esta tarea es sencillo si es posible debugear el código, como ya hemos hecho previamente y como se indica a continuación:
En este caso la actualización de la fila en cuestión (o sea la fila que ha sido leída pero actualizada luego que la sentencia update externa ha modificado los datos) no podrá realizarse y se obtendrá un mensaje similar al siguiente:
Lo que ha sucedido estará relacionado con el tipo de bloqueo del cursor y el mensaje “Optimistic concurrency check failed” es todo un indicio. Los cursores son por defecto optimistas, esto significa que cuando leen un registro utilizan un número de versión para saber si el mismo ha sido externamente modificado, si el número de versión ha cambiado del leído en la sentencia FETCH la actualización fallará como nos ha sucedido en el ejemplo previo. Esta táctica de bloqueos es bastante lógica para los cursores, si consideramos que son operaciones extremadamente lentas, por lo que no es deseable que mantengan registros bloqueados. La opción OPTIMISTIC es la asumida por defecto, aunque podrá especificarse explícitamente utilizando la no sorpresiva palabra clave OPTIMISTIC tal como se muestra en el ejemplo:
DECLARE ProdInfo CURSOR DYNAMIC FORWARD_ONLY OPTIMISTIC FOR SELECT ...
Es posible modificar este comportamiento y definir cursores que bloqueen la fila mientras la misma es modificada, la palabra clave SCROLL_LOCKS permitirá realizar esta poco recomendable modificación del comportamiento del cursor. Bajo este contexto, si repetimos la prueba previa notaremos que la sentencia UPDATE externa quedará en espera, ya que ha sido bloqueda por el cursor. Ni pensar en la escalabilidad del proyecto que emplee esta posibilidad, quizás el termino pesimista sea más que adecuado.
Finalmente la última opción disponible de bloqueo, conocida como READ_ONLY, no realizará bloqueos ni controles de versión pero no permitirá actualizar registros.
Previamente habíamos mencionado a los cursores del tipo FAST_FORWARD, este tipo de cursor definirá un cursor READ_ONLY y FORWARD_ONLY conjuntamente y será la opción de cursor más performante posible.
Los cursores además podrán definirse como locales (LOCAL) o globales (GLOBAL), en el primer caso tendrán el alcance del stored procedure, trigger o batch (grupo de sentencias) que lo ha definido, en el segundo caso su alcance será a nivel de conexión.
Por otra parte, podrá incluirse en la definicion del cursor la palabra clave TYPE_WARNING, que indicará que se informen las conversiones implícitas de un cursor, y si nos preguntamos ¿qué es una conversión implícita de cursor?, recordaremos que habíamos mencionado un ejemplo hace algunos párrafos cuando un cursor definido como KEYSET se comportaba como cursor STATIC, eso es exactamente una conversión implícita de cursor (o degradación del cursor). Existen varias conversiones de este tipo dependiendo del tipo de cursor definido y la condición subyacente (y es indudablemente útil estar familiarizado con ellas para evitar confusiones), para quienes estén interesados en conocerlas en profundidad les dejo el siguiente enlace:
la forma de incluir esta palabra clave en la declaración se muestra en el siguiente ejemplo:
DECLARE ProdInfo CURSOR KEYSET TYPE_WARNING FOR SELECT [Descripcion] FROM Ejemplo1
Si el cursor es convertido, se indicará con un mensaje similar al siguiente:
The created cursor is not of the requested type.
Finalmente, un último detalle a comentar es que además del diverso conjunto de opciones que hemos visto en este articulo, los cursores en SQL Server aceptarán las opciones de sintaxis SQL-92 de las cuales podemos destacar, la posibilidad de crear cursores de un tipo denominado INSENSITIVE, que no son más que cursores de tipo SCROLL Y READ_ONLY conjuntamente.
Como resumen final, podemos comentar que los cursores son una posibilidad potente y de fácil uso para acceder y modificar datos sobre SQL Server, pero no debemos olvidar que son muy costosos en términos de performance, por lo que cuando sea aplicable otro tipo de opción (como por ejemplo una variable tabla) la misma será en el común de los casos una elección menos costosa. Por otra parte, cuando no sea posible evitar el uso de cursores es importante recordar que los cursores READ_ONLY serán la opción de bloqueo menos costosa y que un cursor FORWARD_ONLY será la mejor opción de recorrido. Si tenemos la suerte de poder combinar ambas opciones (READ_ONLY y FORWARD_ONLY) en un cursor FAST_FORWARD estaremos en el mejor de los casos.
También es importante recordar que las opciones STATIC y KEYSET no son las más deseables ya que copiarán datos (ya sea claves o filas enteras) a la tabla tmpdb y que el bloqueo SCROLL_LOCKS es el menos popular, ya que comprometerá más la escalabilidad que las otras opciones de bloqueo.
Terminando con las conclusiomes solo me queda por desear que este artículo haya sido de ayuda a quienes no estén familiarizados con cursores y para quienes ya los conozcan, espero que hayan encontrado en estas líneas algún detalle inesperado o que habían olvidado. Nos vemos en el próximo artículo!.