Del Cursor y otros demonios

El titulo de este post es un pequeño
homenaje a una novela del Nobel Gabriel García Márquez, su nombre me vino a la
mente después de asociarlo a una serie de usos y malas prácticas que por lo
general los desarrolladores cometemos al iniciar con la programación  a nivel de Base de Datos; queriendo de forma
inconsciente extrapolar todos nuestros conocimientos y “manías”en lenguajes
procedimentales y orientados a objetos para transferirlos a nuestras soluciones
en T-SQL.

Cuantas veces nos hemos topado en
diferentes foros, reuniones o en la misma oficina con comentarios como: Eso es fácil,
mételo en un cursor y ….. La triste historia inicia, en especial cuando meses después
escuchamos: La consulta está demasiado lenta, se tarda N horas y sigue
creciendo… Generalmente comienzan a llover soluciones mágicas, en las que por
descabelladas que puedan parecer se han llegado a implementar, algunas simples
y poco efectivas hasta algunas muy caras y poco productivas.

Es en este punto donde elementos
como los Cursores transtornan la salud de nuestras aplicaciones, convirtiéndose
en un lastre difícil de quitar, en especial si forma parte de la cultura del
desarrollador.  Como principio
fundamental que puede resultar orientador esta una afirmación que hacía en uno
de sus textos Itzik Ben-Gan al mencionar que el uso de cursores representaba
una opción viable si se requería manipular una fila a la vez.

A continuación tomo prestado de
los foros de MSDN una consulta en la que el usuario se avocaba directamente al
cursor como el método de solución elegido, decía algo así:

“Quiero preguntarles como puedo hacer un script
en el que se maneje un cursor para depurar una base de datos…bueno tengo una
tabla llamada auditoria_homologacion donde tengo varios registros con id
autonumerico pero el resto de los campos están repetidos, entonces
cuando se busca un registro por cualquier campo
(menos el id_auditoria)  me aparecen varios registros iguales,
entonces debo buscar los registros repetidos, luego tengo que mirar que esos
registros que están repetidos no estén  amarrados a otra
tabla que se llama log_error_homologacion en donde tengo el fk_id_auditoria de
la tabla auditoria_homologacion, 
(entonces los que están en la tabla log_error_homologacion no
se borran)
, pero hay mas
registros repetidos los cuales no tienen error pero debo borrar
los demás que están repetidos 
(por ejemplo tengo 5 registros con diferente id_auditoria pero el resto
de los campos en igual..debo borrar 4 y dejar 1)
 y no
se como hacerlo por medio de un cursor, agradezco de antemano
la colaboración me es de gran ayuda gracias.”

Aunque la forma de explicarlo no
fue la idónea, la idea central consistía en obtener un conjunto de filas
duplicadas y verificar que las mismas se encontraban en una tabla de log, caso
contrario eliminarlas.   Partiendo de la
afirmación de Ben-Gan en este caso no es necesario procesar una fila a la vez,
por el contrario generar una solución basada en cursores sería extremadamente
deficiente y muy tediosa.

Para solventar esta situación
procedimos al uso de una solución basada en CTE (Common Table Expression) y la función
ROW_NUMBER(), aquí presento el código:

 

 

DECLARE
@AuditoriaHomologacion TABLE (IDAuditoria INT,Columna1 VARCHAR(30),Columna2
INT,Columna3 VARCHAR(30),Columna4 INT)

DECLARE
@LogErrorHomologacion TABLE (IDAuditoria INT)

 

INSERT
@AuditoriaHomologacion

VALUES  (1,’LAUREN FONSECA’,1,’NICARAGUA’,2),

               (2,’LAUREN
FONSECA’,1,’NICARAGUA’,2),

               (3,’LAUREN
FONSECA’,1,’NICARAGUA’,2),

               (4,’OCTAVIO
HERNANDEZ’,1,’ESPANA’,2),

               (5,’OCTAVIO
HERNANDEZ’,1,’ESPANA’,2),

               (6,’LAUREN GIOVANNA
HERNANDEZ’,1,’ESPANA’,2),

               (7,’LAUREN GIOVANNA
HERNANDEZ’,1,’ESPANA’,2)

 

INSERT
@LogErrorHomologacion

VALUES (7),(5)

 

; WITH MiCTE AS

(

  SELECT IDAuditoria,ROW_NUMBER()
OVER(PARTITION BY Columna1,Columna2,Columna3,Columna4 ORDER BY IDAuditoria)AS
‘SEC’

  FROM @AuditoriaHomologacion

)

DELETE FROM MiCTE

WHERE MiCTE.SEC >1 AND
NOT EXISTS(SELECT IDAuditoria FROM @LogErrorHomologacion L WHERE
L.IDAuditoria=MiCTE.IDAuditoria)

 

–Verificando
filas eliminadas

SELECT
* FROM @AuditoriaHomologacion

 

En esta solución planteada se
potenciaban aspectos que permiten contar con un código mantenible y sobre todo
enfocado en aspectos orientados al manejo de <<set theory>>.

 

Pero como no todo es desolador
y gris,  un escenario común en donde el
uso de cursores es viable puede ser en tareas administrativas de nuestras base
de datos,  el siguiente ejemplo permite
recorrer todas las tablas de una Base de Datos y obtener información sobre las
mismas (filas, tamaño,etc) a través de la ejecución del sp_spacedused.

 

USE AdventureWorks

GO

 

DECLARE  @tabla VARCHAR(128)

 

CREATE TABLE #rowcount
(tablename varchar(128), rowcnt int)

exec sp_MSforeachtable

   ‘insert into #rowcount select ”?”,
count(*) from ?’

 

DECLARE tablas CURSOR FOR

SELECT tablename FROM
#rowcount

OPEN tablas

FETCH NEXT FROM tablas INTO
@tabla

WHILE @@fetch_status = 0

 BEGIN

  EXEC sp_spaceused @tabla

  FETCH NEXT FROM tablas INTO @tabla

 END

CLOSE tablas

DEALLOCATE tablas

 

DROP TABLE #rowcount

 

Por lo pronto un hasta luego, seguimos escribiendo,
pero sobre todo aprendiendo.   

Email: info@geohernandez.com     Website: http://geeks.ms/blogs/ghernandez

A Lauren …

 

 

Aquellas pequeñas cosas, transformando datos XML en un rowset

El intercambio de información entre empresas y
organizaciones representan una tarea cada vez más común, por esto no es de
extrañar el amplio uso y aceptación del formato XML como estándar para el
intercambio de dicha información.  SQL Server cuenta entre sus muchas características con una
serie de funciones que nos permiten potenciar el uso de XML, pero en esta ocasión
nos centraremos el proceso de transformación de datos XML en un conjunto de
datos (rowset).

El procesamiento de XML se realiza en cinco pasos
fundamentales como se ilustra en la siguiente imagen:

 

Una vez que se cuenta con el documento XML (bien formado)
este se procede a procesar por medio del procedimiento almacenado del sistema
sp_xml_preparedocument, el cual nos permite  validar un documento XML y transformarlo en
una estructura de árbol en memoria, cuyo  contenido podemos consultar por medio de la
instrucción OPENXML para obtener las columnas deseadas y así manipular los
datos en la forma requerida.

Iniciemos con un ejemplo simple, suponiendo que formamos
parte de la empresa dedicada a la reventa de artículos por internet y estamos
recibiendo información de un proveedor externo, el documento XML es el
siguiente:

 

DECLARE @doc xml

SET @doc = ‘<?xml
version=”1.0″ ?>

            <Factura
FacturaID=”1000″ ClienteID=”123″
FechaFactura=”2012-01-14″ FormaPago=”1″>

              <Lineas>

                <Linea ProductoID=”12″
Cantidad=”2″
PrecioUnitario=”12.99″><Descripcion>Encendedor marca
Zippo</Descripcion></Linea>

                <Linea
ProductoID=”41″ Cantidad=”1″
PrecioUnitario=”17.45″><Descripcion>Ron Flor de
Cana</Descripcion></Linea>

               
<Linea ProductoID=”2″ Cantidad=”1″
PrecioUnitario=”2.99″><Descripcion>Bebida embotellada
natural</Descripcion></Linea>

              </Lineas>

            </Factura>’

 

DECLARE @IdentificadorDoc
int

EXEC sp_xml_preparedocument
@IdentificadorDoc OUTPUT, @doc

 

De forma simple podemos decir que
el sp_xml_preparedocument
nos permite procesar un documento XML y obtener una representación del mismo,
el resultado de este sp es un código identificador que apunta hacia la
estructura de árbol del XML creado previamente. 
Una descripción más amplia se encuentra en el siguiente link:

http://msdn.microsoft.com/es-es/library/ms187367.aspx

 

Una vez que construida la estructura o árbol en memoria debemos
acceder a ella, para esto nos apoyamos en la instrucción OPENXML , este nos
permite obtener un conjunto de datos a partir del árbol.  La instrucción OPENXML tiene la siguiente estructura:

OPENXML( idoc int [ in] , rowpattern nvarchar [
in ] , [ flags byte [ in ] ] )

[ WITH ( SchemaDeclaration | TableName ) ]

 

El parámetro idoc
representaría en nuestro caso el parámetro de salida resultante de la ejecución
del sp_xml_preparedocument, en donde especifica la representación interna del
documento XML, el patrón de filas (rowpattern)  es el patrón XPATH que permite  definir la ruta o nodos que deberá reconocerse
en la lectura del XML, a continuación nuestro código:

 

SELECT *
FROM

OPENXML(@IdentificadorDoc, ‘/Factura/Lineas/Linea’)

WITH

(              FacturaTarget       int ‘../../@FacturaID’,

                ClienteTarget       int ‘../../@ClienteID’,

                FechaFacturaTarget            datetime
‘../../@FechaFactura’,

                FormaPagoTarget   int ‘../../@FormaPago’,

                DetallesTarget      varchar(30) ‘./Descripcion’,

                CantidadTarget    int ‘@Cantidad’,

                PrecioUnitarioTarget          float ‘@PrecioUnitario’)

 

En este caso especifico  utilizamos una opción conocida como patrones
de columna, estos contienen un patron XPath para la recuperación de los nodos y
este será relativo al patrón de fila (rowpattern) especificado.  Y esto que quiere decir? Que la recuperación
de los nodos estará basada en una expresión o expresiones en donde se navegara
a través de la jerarquía del documento XML para obtener los datos deseados.

Aquí caben notar dos cosas:

OPENXML(@IdentificadorDoc, ‘/Factura/Lineas/Linea’)

Le estamos indicando a la
instrucción que debe tomar la representación interna perteneciente al documento
que acabamos de transformar, posteriormente le indicamos hasta que nivel deseo “navegar”
en la estructura del documento XML. 
Posteriormente comenzamos a construir la estructura que deseamos
recuperar, especificando el nombre del campo, su tipo de datos  y el origen o expresión XPATH a partir de
donde obtendrá los datos.

WITH

(              FacturaTarget       int ‘../../@FacturaID’,

                ClienteTarget       int ‘../../@ClienteID’,

                FechaFacturaTarget            datetime
‘../../@FechaFactura’,

                FormaPagoTarget   int ‘../../@FormaPago’,

                DetallesTarget      varchar(30) ‘./Descripcion’,

                CantidadTarget    int ‘@Cantidad’,

                PrecioUnitarioTarget          float ‘@PrecioUnitario’)

El manejo o navegación se realize desde el ultimo nodo que
se especifico en el patron de fila, cabe mencionar que los atributos se
representan por medio de @nombreatributo, el siguiente caso funciona así:

FacturaTarget       int ‘../../@FacturaID’ 

FacturaTarget
int: Es el campo con su respectivo tipo de datos que almacenara el resultado
devuelto por la expresión.

../../@FacturaID: Cabe recordar que
según el patrón de fila este tiene una estructura ‘/Factura/Lineas/Linea’ , en este caso se
quiere recuperar el atributo FacturaID, este se encuentra en el nodo
correspondiente a Factura, por lo que si partimos de la ruta especificada en el
patrón de filas y viendo el documento XML tenemos que subir dos niveles , para
este caso se usa la expresión << ../>>.

En los casos de los atributos que se encuentran en el ultimo
nivel (Linea) solo se debe de anteponer el símbolo @NombreAtributo.

Un caso especial aquí es el del ELEMENTO Descripcion, debido
a su naturaleza y ubicación dentro del XML para obtener su valor se debe de
especificar de la siguiente manera:

./Descripcion’

Aquí le indicamos que seleccione el nodo actual a partir del
nodo raíz (nodo Linea), como punto de ayuda presento una tabla resumen de las
principales expresiones de ruta usadas en XPath.

Expresión

Descripción

nodename

Selecciona todos los nodos hijos del
nodename

/

Selecciona a
partir del nodo raiz.

.

Selecciona el nodo actual

..

Selecciona el padre del nodo actual

@

Selecciona atributos

 

Con estos pasos descritos anteriormente ya tenemos como
resultado los campos extraidos directamente de un documento XML.  Nuestra consulta completa quedaría así:

DECLARE @doc xml

SET @doc = ‘<?xml
version=”1.0″ ?>

            <Factura
FacturaID=”1000″ ClienteID=”123″
FechaFactura=”2012-01-14″ FormaPago=”1″>

              <Lineas>

                <Linea
ProductoID=”12″ Cantidad=”2″
PrecioUnitario=”12.99″><Descripcion>Encendedor marca
Zippo</Descripcion></Linea>

                <Linea
ProductoID=”41″ Cantidad=”1″
PrecioUnitario=”17.45″><Descripcion>Ron Flor de
Cana</Descripcion></Linea>

                <Linea
ProductoID=”2″ Cantidad=”1″
PrecioUnitario=”2.99″><Descripcion>Bebida embotellada natural</Descripcion></Linea>

              </Lineas>

           
</Factura>’

 

DECLARE
@IdentificadorDoc int

EXEC sp_xml_preparedocument @IdentificadorDoc
OUTPUT, @doc

 

SELECT * FROM

OPENXML(@IdentificadorDoc, ‘/Factura/Lineas/Linea’)

WITH

(      FacturaTarget int ‘../../@FacturaID’,

       ClienteTarget int ‘../../@ClienteID’,

       FechaFacturaTarget  datetime ‘../../@FechaFactura’,

       FormaPagoTarget   int ‘../../@FormaPago’,

       DetallesTarget      varchar(30) ‘./Descripcion’,

       CantidadTarget      int ‘/@Cantidad’,

       PrecioUnitarioTarget       float ‘@PrecioUnitario’)

 

EXEC sp_xml_removedocument @IdentificadorDoc

GO

 

Espero les haya servido de ayuda y seguimos escribiendo,
pero sobre todo aprendiendo…

Gio

Email: info@geohernandez.com