Recursividad con SQL Server

La vez pasada expuse un tema bastante interesante sobre el empleo de valores comunes en SQL Server, pues bien, hoy les he traído un concepto igual de bueno sobre el uso de la recursividad, así como de funciones recursivas en SQL Server.

Para empezar ¿qué es la recursividad? La recursividad (en informática) está definida como la propiedad que tiene un objeto o una entidad para auto-invocarse, es decir, puede llamarse a sí misma repetidas veces, y sin embargo, lo hará devolviendo valores  diferentes cada vez que lo haga, sin perder nunca la esencia original, e incluso los valores que vaya devolviendo servirán como medio por el cual el objeto o la entidad puedan seguir su curso recursivo. La recursividad no sólo está presente en la informática, sino también dentro de la vida misma, encontrándolo como un concepto bastante curioso, e incluso paradójico si tomamos en cuenta que la auto-llamada se realiza inclusive antes de que el objeto esté creado en tiempo real.
Para explicarme mejor ¿sabes que significa GNU? Pues bien, agárrate porque es recursivo: las siglas GNU significan GNU Not Unix, lo que significa en español GNU no es Unix, es curioso; siempre te preguntarás que es GNU, volviendo a lo mismo.

Aquí otro ejemplo de recursividad. Ojo: A mí no me gusta Gokú por si acaso, es sólo que la imagen logra evocar recursividad. La gente que ve anime es retrasada mental y tiene el pene tan pequeño que si un día se le ocurre masturbarse tendrá que coger una de esas pinzas para sacar bigotes.

Volviendo al tema; este artículo viene por algo que siempre veía en mi instituto, de la mayoría (por no decir todos) y hasta de algunos profesores, que definían la estructura de ubigeo (o localización demográfica, para que se entienda mejor) con unas dos, tres e incluso en los casos más disparatados con hasta 4 tablas:

Modelo de ubigeo mediocre que siempre encontraba en mi instituto. Y cuando preguntaba las respuestas eran peores.

Una vez explicado esto de la recursividad, vayamos manos a la obra. Creamos la base que nos servirá para el ejemplo, así como sus tablas y las inserciones respectivas:

Código:

create database bd_ejemplo_recursivo
on
(
	name = 'bd_ejemplo_recursivo_Data.mdf',
	filename = 'D:\BaseDatos2005\bd_ejemplo_recursivo_Data.mdf',
	size = 3,
	filegrowth = 1,
	maxsize = 100
)
log on 
(
	name = 'bd_ejemplo_recursivo_Log.ldf',
	filename = 'D:\BaseDatos2005\bd_ejemplo_recursivo_Log.ldf',
	size = 3,
	filegrowth = 1,
	maxsize = 100
)
go
use bd_ejemplo_recursivo

create table tm_ubigeo
(
	tm_idubigeo int identity(1,1) primary key not null,
	tm_nomubigeo varchar(150),
	tm_idubigeosup int,
	tm_tipoubigeo varchar(2),
	ta_idindest varchar(2)
)
go


create table ta_valorcomun
(
	ta_idvalor int identity(1,1) primary key not null,
	ta_idcampo varchar(50),
	ta_idcodigo varchar(2),
	ta_denomina varchar(50)
)
go

create function [dbo].[fc_Obtenervalorcomun]
(
	@idcampo varchar(50),
	@idcodigo varchar(2)
)
returns varchar(50)
as
begin
	declare @denomina as varchar(50)
	select @denomina = ta_denomina from ta_valorcomun
		where rtrim(upper(ta_idcampo)) = rtrim(upper(@idcampo)) and rtrim(ta_idcodigo) = rtrim(@idcodigo)
	return @denomina
end

INSERT INTO tm_ubigeo (tm_nomubigeo, tm_tipoubigeo, tm_idubigeosup, ta_idindest) VALUES  ('PERÚ','00',0,'00')
INSERT INTO tm_ubigeo (tm_nomubigeo, tm_tipoubigeo, tm_idubigeosup, ta_idindest) VALUES  ('TUMBES','01',1,'00')
INSERT INTO tm_ubigeo (tm_nomubigeo, tm_tipoubigeo, tm_idubigeosup, ta_idindest) VALUES  ('PIURA','01',1,'00')
INSERT INTO tm_ubigeo (tm_nomubigeo, tm_tipoubigeo, tm_idubigeosup, ta_idindest) VALUES  ('LAMBAYEQUE','01',1,'00')
INSERT INTO tm_ubigeo (tm_nomubigeo, tm_tipoubigeo, tm_idubigeosup, ta_idindest) VALUES  ('LA LIBERTAD','01',1,'00')
INSERT INTO tm_ubigeo (tm_nomubigeo, tm_tipoubigeo, tm_idubigeosup, ta_idindest) VALUES  ('CAJAMARCA','01',1,'00')
INSERT INTO tm_ubigeo (tm_nomubigeo, tm_tipoubigeo, tm_idubigeosup, ta_idindest) VALUES  ('ANCASH','01',1,'00')
INSERT INTO tm_ubigeo (tm_nomubigeo, tm_tipoubigeo, tm_idubigeosup, ta_idindest) VALUES  ('AMAZONAS','01',1,'00')
INSERT INTO tm_ubigeo (tm_nomubigeo, tm_tipoubigeo, tm_idubigeosup, ta_idindest) VALUES  ('AREQUIPA','01',1,'00')
INSERT INTO tm_ubigeo (tm_nomubigeo, tm_tipoubigeo, tm_idubigeosup, ta_idindest) VALUES  ('CUSCO','01',1,'00')
INSERT INTO tm_ubigeo (tm_nomubigeo, tm_tipoubigeo, tm_idubigeosup, ta_idindest) VALUES  ('LORETO','01',1,'00')
INSERT INTO tm_ubigeo (tm_nomubigeo, tm_tipoubigeo, tm_idubigeosup, ta_idindest) VALUES  ('UCAYALI','01',1,'00')
INSERT INTO tm_ubigeo (tm_nomubigeo, tm_tipoubigeo, tm_idubigeosup, ta_idindest) VALUES  ('ICA','01',1,'00')
INSERT INTO tm_ubigeo (tm_nomubigeo, tm_tipoubigeo, tm_idubigeosup, ta_idindest) VALUES  ('MOQUEGUA','01',1,'00')
INSERT INTO tm_ubigeo (tm_nomubigeo, tm_tipoubigeo, tm_idubigeosup, ta_idindest) VALUES  ('PASCO','01',1,'00')
INSERT INTO tm_ubigeo (tm_nomubigeo, tm_tipoubigeo, tm_idubigeosup, ta_idindest) VALUES  ('JUNIN','01',1,'00')
INSERT INTO tm_ubigeo (tm_nomubigeo, tm_tipoubigeo, tm_idubigeosup, ta_idindest) VALUES  ('AYACUCHO','01',1,'00')
INSERT INTO tm_ubigeo (tm_nomubigeo, tm_tipoubigeo, tm_idubigeosup, ta_idindest) VALUES  ('APURIMAC','01',1,'00')
INSERT INTO tm_ubigeo (tm_nomubigeo, tm_tipoubigeo, tm_idubigeosup, ta_idindest) VALUES  ('MADRE DE DIOS','01',1,'00')
INSERT INTO tm_ubigeo (tm_nomubigeo, tm_tipoubigeo, tm_idubigeosup, ta_idindest) VALUES  ('PUNO','01',1,'00')
INSERT INTO tm_ubigeo (tm_nomubigeo, tm_tipoubigeo, tm_idubigeosup, ta_idindest) VALUES  ('TACNA','01',1,'00')
INSERT INTO tm_ubigeo (tm_nomubigeo, tm_tipoubigeo, tm_idubigeosup, ta_idindest) VALUES  ('LIMA','01',1,'00')
INSERT INTO tm_ubigeo (tm_nomubigeo, tm_tipoubigeo, tm_idubigeosup, ta_idindest) VALUES  ('HUANUCO','01',1,'00')
INSERT INTO tm_ubigeo (tm_nomubigeo, tm_tipoubigeo, tm_idubigeosup, ta_idindest) VALUES  ('HUANCAVELICA','01',1,'00')
INSERT INTO tm_ubigeo (tm_nomubigeo, tm_tipoubigeo, tm_idubigeosup, ta_idindest) VALUES  ('PROVINCIA CONSTITUCIONAL DEL CALLAO','01',1,'00')
INSERT INTO tm_ubigeo (tm_nomubigeo, tm_tipoubigeo, tm_idubigeosup, ta_idindest) VALUES  ('CHICLAYO','02',4,'00')
INSERT INTO tm_ubigeo (tm_nomubigeo, tm_tipoubigeo, tm_idubigeosup, ta_idindest) VALUES  ('LAMBAYEQUE','02',4,'00')
INSERT INTO tm_ubigeo (tm_nomubigeo, tm_tipoubigeo, tm_idubigeosup, ta_idindest) VALUES  ('FERREÑAFE','02',4,'00')
INSERT INTO tm_ubigeo (tm_nomubigeo, tm_tipoubigeo, tm_idubigeosup, ta_idindest) VALUES  ('CHICLAYO','03',27,'00')
INSERT INTO tm_ubigeo (tm_nomubigeo, tm_tipoubigeo, tm_idubigeosup, ta_idindest) VALUES  ('LA VICTORIA','03',27,'00')
INSERT INTO tm_ubigeo (tm_nomubigeo, tm_tipoubigeo, tm_idubigeosup, ta_idindest) VALUES  ('PIURA','02',3,'00')
INSERT INTO tm_ubigeo (tm_nomubigeo, tm_tipoubigeo, tm_idubigeosup, ta_idindest) VALUES  ('PAITA','02',3,'00')
INSERT INTO tm_ubigeo (tm_nomubigeo, tm_tipoubigeo, tm_idubigeosup, ta_idindest) VALUES  ('CACHAPOYAS','02',8,'00')
INSERT INTO tm_ubigeo (tm_nomubigeo, tm_tipoubigeo, tm_idubigeosup, ta_idindest) VALUES  ('BAGUA','02',8,'00')
INSERT INTO tm_ubigeo (tm_nomubigeo, tm_tipoubigeo, tm_idubigeosup, ta_idindest) VALUES  ('BONGARA','02',8,'00')
INSERT INTO tm_ubigeo (tm_nomubigeo, tm_tipoubigeo, tm_idubigeosup, ta_idindest) VALUES  ('LUYA','02',8,'00')
INSERT INTO tm_ubigeo (tm_nomubigeo, tm_tipoubigeo, tm_idubigeosup, ta_idindest) VALUES  ('RODRIGUEZ DE MENDOZA','02',8,'00')
INSERT INTO tm_ubigeo (tm_nomubigeo, tm_tipoubigeo, tm_idubigeosup, ta_idindest) VALUES  ('CONDORCANQUI','02',8,'00')
INSERT INTO tm_ubigeo (tm_nomubigeo, tm_tipoubigeo, tm_idubigeosup, ta_idindest) VALUES  ('UTCUBAMBA','02',8,'00')
INSERT INTO tm_ubigeo (tm_nomubigeo, tm_tipoubigeo, tm_idubigeosup, ta_idindest) VALUES  ('CHACHAPOYAS','03',36,'00')
INSERT INTO tm_ubigeo (tm_nomubigeo, tm_tipoubigeo, tm_idubigeosup, ta_idindest) VALUES  ('BAGUA','03',37,'00')
INSERT INTO tm_ubigeo (tm_nomubigeo, tm_tipoubigeo, tm_idubigeosup, ta_idindest) VALUES  ('JUMBILLA','03',38,'00')
INSERT INTO tm_ubigeo (tm_nomubigeo, tm_tipoubigeo, tm_idubigeosup, ta_idindest) VALUES  ('LAMUD','03',39,'00')
INSERT INTO tm_ubigeo (tm_nomubigeo, tm_tipoubigeo, tm_idubigeosup, ta_idindest) VALUES  ('MENDOZA','03',40,'00')
INSERT INTO tm_ubigeo (tm_nomubigeo, tm_tipoubigeo, tm_idubigeosup, ta_idindest) VALUES  ('SANTA MARIA DE NIEVA','03',41,'00')
INSERT INTO tm_ubigeo (tm_nomubigeo, tm_tipoubigeo, tm_idubigeosup, ta_idindest) VALUES  ('BAGUA GRANDE','03',42,'00')
INSERT INTO tm_ubigeo (tm_nomubigeo, tm_tipoubigeo, tm_idubigeosup, ta_idindest) VALUES  ('HUARAZ','02',7,'00')
INSERT INTO tm_ubigeo (tm_nomubigeo, tm_tipoubigeo, tm_idubigeosup, ta_idindest) VALUES  ('AIJA','02',7,'00')
INSERT INTO tm_ubigeo (tm_nomubigeo, tm_tipoubigeo, tm_idubigeosup, ta_idindest) VALUES  ('ANTONIO RAYMONDI','02',7,'00')
INSERT INTO tm_ubigeo (tm_nomubigeo, tm_tipoubigeo, tm_idubigeosup, ta_idindest) VALUES  ('ASUNCION','02',7,'00')
INSERT INTO tm_ubigeo (tm_nomubigeo, tm_tipoubigeo, tm_idubigeosup, ta_idindest) VALUES  ('BOLOGNESI','02',7,'00')
INSERT INTO tm_ubigeo (tm_nomubigeo, tm_tipoubigeo, tm_idubigeosup, ta_idindest) VALUES  ('CARHUAZ','02',7,'00')
INSERT INTO tm_ubigeo (tm_nomubigeo, tm_tipoubigeo, tm_idubigeosup, ta_idindest) VALUES  ('CARLOS FERMIN FITZCARRALD','02',7,'00')
INSERT INTO tm_ubigeo (tm_nomubigeo, tm_tipoubigeo, tm_idubigeosup, ta_idindest) VALUES  ('CASMA','02',7,'00')
INSERT INTO tm_ubigeo (tm_nomubigeo, tm_tipoubigeo, tm_idubigeosup, ta_idindest) VALUES  ('CORONGO','02',7,'00')
INSERT INTO tm_ubigeo (tm_nomubigeo, tm_tipoubigeo, tm_idubigeosup, ta_idindest) VALUES  ('HUARI','02',7,'00')
INSERT INTO tm_ubigeo (tm_nomubigeo, tm_tipoubigeo, tm_idubigeosup, ta_idindest) VALUES  ('HUARMEY','02',7,'00')
INSERT INTO tm_ubigeo (tm_nomubigeo, tm_tipoubigeo, tm_idubigeosup, ta_idindest) VALUES  ('HUAYLAS','02',7,'00')
INSERT INTO tm_ubigeo (tm_nomubigeo, tm_tipoubigeo, tm_idubigeosup, ta_idindest) VALUES  ('MARISCAL LUZURIAGA','02',7,'00')
INSERT INTO tm_ubigeo (tm_nomubigeo, tm_tipoubigeo, tm_idubigeosup, ta_idindest) VALUES  ('OCROS','02',7,'00')
INSERT INTO tm_ubigeo (tm_nomubigeo, tm_tipoubigeo, tm_idubigeosup, ta_idindest) VALUES  ('PALLASCA','02',7,'00')
INSERT INTO tm_ubigeo (tm_nomubigeo, tm_tipoubigeo, tm_idubigeosup, ta_idindest) VALUES  ('POMABAMBA','02',7,'00')
INSERT INTO tm_ubigeo (tm_nomubigeo, tm_tipoubigeo, tm_idubigeosup, ta_idindest) VALUES  ('RECUAY','02',7,'00')
INSERT INTO tm_ubigeo (tm_nomubigeo, tm_tipoubigeo, tm_idubigeosup, ta_idindest) VALUES  ('SANTA','02',7,'00')
INSERT INTO tm_ubigeo (tm_nomubigeo, tm_tipoubigeo, tm_idubigeosup, ta_idindest) VALUES  ('SIHUAS','02',7,'00')
INSERT INTO tm_ubigeo (tm_nomubigeo, tm_tipoubigeo, tm_idubigeosup, ta_idindest) VALUES  ('YUNGAY','02',7,'00')
INSERT INTO tm_ubigeo (tm_nomubigeo, tm_tipoubigeo, tm_idubigeosup, ta_idindest) VALUES  ('HUARAZ','03',50,'00')
INSERT INTO tm_ubigeo (tm_nomubigeo, tm_tipoubigeo, tm_idubigeosup, ta_idindest) VALUES  ('AIJA','03',51,'00')
INSERT INTO tm_ubigeo (tm_nomubigeo, tm_tipoubigeo, tm_idubigeosup, ta_idindest) VALUES  ('LLAMELLIN','03',52,'00')
INSERT INTO tm_ubigeo (tm_nomubigeo, tm_tipoubigeo, tm_idubigeosup, ta_idindest) VALUES  ('CHACAS','03',53,'00')
INSERT INTO tm_ubigeo (tm_nomubigeo, tm_tipoubigeo, tm_idubigeosup, ta_idindest) VALUES  ('CHIQUIAN','03',54,'00')
INSERT INTO tm_ubigeo (tm_nomubigeo, tm_tipoubigeo, tm_idubigeosup, ta_idindest) VALUES  ('CARHUAZ','03',55,'00')
INSERT INTO tm_ubigeo (tm_nomubigeo, tm_tipoubigeo, tm_idubigeosup, ta_idindest) VALUES  ('SAN LUIS','03',56,'00')
INSERT INTO tm_ubigeo (tm_nomubigeo, tm_tipoubigeo, tm_idubigeosup, ta_idindest) VALUES  ('JOSE LEONARDO ORTIZ','03',27,'00')
INSERT INTO tm_ubigeo (tm_nomubigeo, tm_tipoubigeo, tm_idubigeosup, ta_idindest) VALUES  ('SAN IGNACIO','02',6,'00')


insert into ta_valorcomun (ta_idcampo, ta_idcodigo, ta_denomina) values ('ta_idindest', '00', 'ACTIVO')
insert into ta_valorcomun (ta_idcampo, ta_idcodigo, ta_denomina) values ('ta_idindest', '01', 'INACTIVO')
insert into ta_valorcomun (ta_idcampo, ta_idcodigo, ta_denomina) values ('tm_tipoubigeo', '00', 'PAIS')
insert into ta_valorcomun (ta_idcampo, ta_idcodigo, ta_denomina) values ('tm_tipoubigeo', '01', 'DEPARTAMENTO')
insert into ta_valorcomun (ta_idcampo, ta_idcodigo, ta_denomina) values ('tm_tipoubigeo', '02', 'PROVINCIA')
insert into ta_valorcomun (ta_idcampo, ta_idcodigo, ta_denomina) values ('tm_tipoubigeo', '03', 'DISTRITO')

Una vez ejecutado el script mostrado veamos cómo se nos muestran los datos:

Código:

select * from tm_ubigeo

Si nos fijamos, veremos que en la tabla tm_ubigeo tenemos el campo tm_idubigeosup, el cual es muy importante ya que en este campo haremos referencia al id del cual depende, que no será nada más y nada menos que el valor contenido en el campo tm_idubigeo de algún registro de la tabla que servirá de registro “padre” o “jefe”,  y por ende, se aplicará la recursividad para mostrar esta suerte de “dependencia recursiva”.

En resumen, el objetivo es mostrar una cierta estructura jerárquica:

• PERÚ -> País
• Lambayeque -> Departamento (o región)
• Chiclayo -> Provincia
• Chiclayo -> Distrito

Además, déjame contarte que esta forma de presentar esta clase de organización no tiene límites, y que incluso se pueden aplicar a conceptos como la categorización, jerarquías, organigramas, etc., etc., etc.

Se ve bien ¿no? Pero ahora ¿cómo es que mostramos esta información, de tal manera que el usuario también entienda esta estructura, tal como la lista anterior? Pues bien, para estos menesteres se ha creado una función, cortesía de un maestro gran amigo mío, Fernando Pinzón Monja, que es programador y fue en un tiempo una fuente interminable de inspiración y conocimiento. Dejándose de mariconadas, veamos la función (por supuesto programa en Transact-SQL):

Código:

create function fc_ubigeo (@codubigeo int)
returns @x table ( tm_idubigeo int, tm_nomubigeo varchar(150), tm_idubigeosup int, tm_tipoubigeo varchar(2), ta_idindest varchar(2))
as 
begin
	--Declaramos las variables que utilizaremos para recoger los valores del cursor
	declare @refcodubi int,	
		@nomubigeo varchar(150),
		@idubigeosup int,
		@tipoubigeo varchar(2),
		@idindest varchar(2)	
	--Insertamos en la variable de retorno de la función (que es de tipo tabla) los valores seleccionados de la tabla ubigeo, según el id de la tabla que ingresemos como parámetro a la función
	insert into @x (tm_idubigeo, tm_nomubigeo, tm_idubigeosup, tm_tipoubigeo, ta_idindest)
		select tm_idubigeo, tm_nomubigeo, tm_idubigeosup, tm_tipoubigeo, ta_idindest from tm_ubigeo where tm_idubigeo = @codubigeo
	--Definimos el cursor, contruyéndolo a partir de una consulta idéntica a la anterior
	declare curubigeo cursor local for
		select tm_idubigeo, tm_nomubigeo, tm_idubigeosup, tm_tipoubigeo, ta_idindest from tm_ubigeo where tm_idubigeosup = @codubigeo
	--Abrimos el cursor
	open curubigeo
	--Asignamos a las variables los valores devueltos por el cursor durante su recorrido por la consulta
	fetch next from curubigeo into  @refcodubi, @nomubigeo, @idubigeosup,  @tipoubigeo, @idindest
	--Mientras el cursor siga corriendo
	while @@fetch_status = 0
		begin
			--Insertamos en la variable @x los valores que hacen falta para completar la consulta
			--Como te darás cuenta, esta vez insertamos a partir de la función misma, cumpliendo la recursividad
			--Y además, los valores de la consulta que estamos insertando son según lo que contenga la variable @refcodubi,
			--Y en cada llamada que se haga, este valor cambiará tantas veces como registros nos devuelva la consulta, mostrando valores diferentes cada vez
			--Le he agregado una tabulación al nombre de ubigeo para mostrar la estructura jerárquica en la consulta
			insert into @x (tm_idubigeo, tm_nomubigeo, tm_idubigeosup, tm_tipoubigeo, ta_idindest)
				select tm_idubigeo, '     ' + tm_nomubigeo, tm_idubigeosup, tm_tipoubigeo, ta_idindest from dbo.fc_ubigeo (@refcodubi)
			--Por regla general de los cursores, volvemos a colocar el fetch next para que el cursor siga su curso
			fetch next from curubigeo into @refcodubi, @nomubigeo, @idubigeosup, @tipoubigeo, @idindest
		end
	-- Cerramos el cursor
	close curubigeo
	-- Lo destruimos, matamos, violamos y enterramos
	deallocate curubigeo
	--Retornamos la tabla resultante
	return
end 

Como podrán notar, la función devolverá un valor de tabla, es decir, la función en tiempo de ejecución se comportará lo mismo que una tabla (sí, le podrás hacer consultas, JOIN y toda la cosa) y además hace uso de sólo parámetro (una variable entera) y en su estructura se define un cursor, elemento necesario para lograr que la función cumpla su cometido.
Ahora bien, ejecutemos esta consulta:

Código:

select *,
	dbo.fc_Obtenervalorcomun('tm_tipoubigeo', tm_tipoubigeo) as TipoUbigeo, 
	dbo.fc_Obtenervalorcomun('ta_idindest', ta_idindest) as Estado
from fc_ubigeo(1)

Y se nos mostrará lo siguiente:

Como ven, resulta muy útil y práctico este tipo de estructuras, pero eso sí, los cursores tienen un punto débil: por su misma naturaleza, tienden a consumir muchos recursos cuando los datos que procesan son demasiados (yo he probado esta estructura con más de 150 mil registros y se demora de uno a dos segundos, no es mucho pero como informáticos sabemos que el tiempo vale oro y vale nada a la vez).
Hasta aquí nos quedamos, en la semana o en la próxima que viene subiré ejemplos sobre cómo utilizar la recursividad y lo útil que resulta a la hora de programar nuestras aplicaciones web o de escritorio. Buena suerte y pórtense mal.

lovelessisma@gmail.com

Descargar