tablas y funciones de sql server para implementar una jerarquía sin límite de niveles leonel...

Tablas y Funciones de SQL Server para Implementar una

Jerarquía sin Límite de NivelesLeonel Morales Díaz

Ingeniería Simple

[email protected]

Copyright 2008 by Leonel Morales Díaz – Ingeniería Simple.Derechos reservados Disponible en: http://www.ingenieriasimple.com/TSQL

Jerarquías usuales

• Estructura tabla padre – tabla hijo– La llave primaria de la padre es llave foránea

en la hija– Puede haber una tabla nieto

• Hasta una biznieto

Padres

CódigoDescripción

Hijos

CódigoCódigoPadreDescripción

Nietos

CódigoCódigoHijoPadre

Descripción

Problemas en jerarquías

• Limitada a tres niveles– O a la cantidad de niveles establecida– Inflexibilidad:

• Nuevos niveles reales deben ser “adaptados”

– La estructura es permanente y coercitiva• Aunque un nivel ya no sea necesario

• Dificultad de consultas– Se trata de relacionar tres o más tablas

Jerarquías de una sola tabla

• Estructura registro hijo – registro padre– La llave primaria es llave foránea de la misma

tabla• Si no hay padre la llave foránea es nula

– Tabla con relación a sí misma

Datos


Ventajas jerarquía unitabla

• Ilimitados niveles– Se ajusta a las necesidades reales

• Consultas más sencillas

• Estructura más simple– Llega a conocerse muy bien

• Bastante flexible

Desventajas

• El nivel del registro no se conoce inmediatamente– En la jerarquía tradicional se conoce el nivel

con solo saber a qué tabla pertenece– Se necesita agregar campos para esto

Jerarquía contable

• Usualmente por posiciones en una cadena– #.##.###.####– Cuenta, subcuenta, sub-subcuenta, cuenta de detalle,

etc.

• Puede ser una sola tabla– Con referencia a sí misma

• La cantidad de niveles está pre-establecida• No hay necesidad de campo con cuenta padre• Usualmente se llama Nomenclatura Contable

Ejemplo jerarquía contable

• En el código está implícito el código del padre

• Puede ser necesario poner validaciones para evitar que se inserte un código sin padre– Los códigos de longitud

1 no tienen padre

• Una sola tabla

Código Cuenta1 Activo2 Pasivo3 Capital4 Gastos5 Ingresos

1.1 Circulante1.2 Fijo1.3 Diferido2.1 Circulante2.2 Fijo2.3 Diferido3.1 Acciones al portador3.2 Acciones preferentes4.1 Fijos4.2 Variables5.1 Fijos5.2 Variables

1.1.1 Caja1.1.2 Bancos

Nomenclatura

• Implementación– Puede hacerse mediante “constraints” de tipo

“Check”• Y una función para encontrar el código padre• Si la función devuelve “Null” no se acepta

Nomenclatura

CódigoCuenta

CREATE TABLE Nomenclatura(Código nVarChar(13) NOT NULL

CONSTRAINT Código_Nomenclatura Check ((Código Like '[1-9]' Or Código Like '[1-9].[0-9][0-9]' Or Código Like '[1-9].[0-9][0-9].[0-9][0-9][0-9]' Or Código Like '[1-9].[0-9][0-9].[0-9][0-9][0-9].[0-9][0-9][0-9][0-9]')And (len(Código)=1 Or Not dbo.CuentaPadre(Código) Is Null)),

Cuenta nVarChar(30) NULL,Constraint PK_Nomenclatura Primary Key Clustered

( Código ASC ))

Función para chequeo

• CuentaPadre(@Código)– Encuentra la cuenta padre de @Código– Si no hay devuelve “Null”

CREATE FUNCTION CuentaPadre ( @Código nVarChar(13) )RETURNS nVarChar(13) AS BEGIN

DECLARE @Resu nVarChar(13)Set @Resu = NullIf CharIndex('.',@Código) > 0 Begin

Declare @PosiblePadre nVarChar(13)Set @PosiblePadre = RTrim(@Código)While Right(@PosiblePadre,1) <> '.'

Set @PosiblePadre = Left(@PosiblePadre,Len(@PosiblePadre)-1)Set @PosiblePadre = Left(@PosiblePadre,Len(@PosiblePadre)-1)

Select @Resu = Código From NomenclaturaWhere Código = @PosiblePadre

EndRETURN @Resu

END

Padre y nivel

• Se pueden implementar con campos calculados

• Padre– El valor devuelto por CuentaPadre

• Nivel– El número de puntos más 1– Se puede hacer con una función que los

cuente o aprovechando la función Like

Tabla con padre y nivel

Nomenclatura

CódigoCuentaPadreNivel

CREATE TABLE Nomenclatura(Código nVarChar(13) NOT NULL

CONSTRAINT Código_Nomenclatura Check ((Código Like '[1-9]' Or Código Like '[1-9].[0-9][0-9]' Or Código Like '[1-9].[0-9][0-9].[0-9][0-9][0-9]' Or Código Like '[1-9].[0-9][0-9].[0-9][0-9][0-9].[0-9][0-9][0-9][0-9]')And (len(Código)=1 Or Not dbo.CuentaPadre(Código) Is Null)),

Cuenta nVarChar(30) NULL,Padre As dbo.CuentaPadre(Código),Nivel As Case

When Código Like '%.%.%.%' Then 4 When Código Like '%.%.%' Then 3 When Código Like '%.%' Then 2 Else 1

End,Constraint PK_Nomenclatura Primary Key Clustered

( Código ASC ))

Datos de tabla Nomenclatura

Select * From Nomenclatura

Código Cuenta Padre Nivel------------- ------------------------------ ------------- -----------1 Activo NULL 11.01 Circulante 1 21.01.001 Caja 1.01 31.01.002 Bancos 1.01 31.01.002.0001 Banco Industrial 1.01.002 41.01.002.0002 Banco Continental 1.01.002 41.01.002.0003 Banco Internacional 1.01.002 41.02 Fijo 1 21.03 Diferido 1 22 Pasivo NULL 12.01 Circulante 2 22.02 Fijo 2 22.03 Diferido 2 23 Capital NULL 13.01 Acciones al portador 3 23.02 Acciones preferentes 3 24 Gastos NULL 14.01 Fijos 4 24.02 Variables 4 25 Ingresos NULL 15.01 Fijos 5 25.02 Variables 5 2

(22 row(s) affected)

Otros tipos de jerarquía

• Por ruta o “path”– Similar a la de directorios de windows– Se tiene un nodo “raíz” y un separador

• C:, D:, etc., son raíces• \ es el separador

– Todos los nodos de un mismo nivel tienen la misma cantidad de separadores en la ruta

– Todos los hijos de un mismo nodo comparten el mismo prefijo

• Generalización: jerarquía por prefijo– Puede o no existir separador

• En cualquier caso se usa solo una tabla

Planteamiento

• Partiendo de una jerarquía de una sola tabla construir las consultas para:– Obtener la lista de padres

• Registros sin padre

– Obtener la lista de registros en el nivel “n”– Obtener la lista de registros descendientes

del registro “R”– Obtener la lista de registros que descienden

del registro “P” y están en el nivel “m”

Tabla básica

• Solo tres campos– Código– CódigoPadre– Descripción

• El resto serán calculados– Padre– Nivel– Ruta

Datos


Creación de la tabla básica

• La tabla permite almacenar cualquier jerarquía

• En este ejemplo se usará para países y provincias geográficas (departamentos), municipios, etc.

CREATE TABLE Datos(Código Int NOT NULL,CódigoPadre Int NULL

Constraint FK_Datos_CódigoPadre Foreign Key References Datos ( Código ),

Descripción nVarChar(Max),Constraint PK_Datos Primary Key Clustered

( Código ASC ))

Datos


Registros para pruebasSelect * From Datos

Código CódigoPadre Descripción----------- ----------- -----------------------1 NULL Guatemala2 NULL El Salvador3 NULL Honduras4 NULL Nicaragua5 NULL Costa Rica6 NULL Belice7 NULL Panamá8 1 Guatemala9 1 Sacatepequez10 1 Chimaltenango11 1 Sololá12 1 Totonicapán13 1 Huehuetenango14 1 Quetzaltenango15 1 San Marcos16 1 Retalhuleu17 1 Suchitepequez18 1 Escuintla19 1 Santa Rosa20 1 Jutiapa

21 1 Jalapa22 1 Zacapa23 1 Izabal24 1 Baja Verapaz25 1 Alta Verapaz26 1 Quiché27 1 Petén28 1 El Progreso29 8 Ciudad de Guatemala30 8 Mixco31 8 Villa Nueva32 8 Jocotenango33 9 San Juan Sacatepequez34 9 San Raymundo35 9 Antigua Guatemala36 8 Amatitlán37 11 Atitlán38 11 San Pedro La Laguna39 13 Chiantla40 39 Los Regadillos41 13 Huehuetenango42 41 El Terrero43 41 El Cambote


Lista de padres

• Padres:– Registros sin padre– CódigoPadre Is Null

Select * From Datos Where CódigoPadre Is Null

Código CódigoPadre Descripción----------- ----------- ------------------1 NULL Guatemala2 NULL El Salvador3 NULL Honduras4 NULL Nicaragua5 NULL Costa Rica6 NULL Belice7 NULL Panamá

(7 row(s) affected)

Lista de registros en nivel “n”

• Se necesita una función que calcule el nivel

• Puede ser recursiva

Create Function CalculaNivelDato( @Código As Int )Returns IntAs Begin

Declare @CódigoPadre IntDeclare @Nivel Int

Select @CódigoPadre = CódigoPadreFrom Datos Where Código = @Código

If (@CódigoPadre) Is NullSet @Nivel = 1

ElseSet @Nivel = dbo.CalculaNivelDato(@CódigoPadre) + 1

Return @NivelEnd

Select * From Datos Where dbo.CalculaNivelDato(Código) = 3

Código CódigoPadre Descripción----------- ----------- ---------------------29 8 Ciudad de Guatemala30 8 Mixco31 8 Villa Nueva32 8 Jocotenango33 9 San Juan Sacatepequez34 9 San Raymundo35 9 Antigua Guatemala36 8 Amatitlán37 11 Atitlán38 11 San Pedro La Laguna39 13 Chiantla41 13 Huehuetenango


Nivel como campo calculado

• Se puede incorporar el nivel como campo calculado– Usando la función CalculaNivelDato



Descripción nVarChar(Max),Nivel As dbo.CalculaNivelDato(Código),Constraint PK_Datos Primary Key Clustered

( Código ASC ))

Select * From Datos Where Nivel = 3 or Nivel = 4

Código CódigoPadre Descripción Nivel----------- ----------- ---------------------- ------29 8 Ciudad de Guatemala 330 8 Mixco 331 8 Villa Nueva 332 8 Jocotenango 333 9 San Juan Sacatepequez 334 9 San Raymundo 335 9 Antigua Guatemala 336 8 Amatitlán 337 11 Atitlán 338 11 San Pedro La Laguna 339 13 Chiantla 340 39 Los Regadillos 441 13 Huehuetenango 342 41 El Terrero 443 41 El Cambote 4


Lista de descendientes de “R”

• Prerrequisito:– Función que construye el “path” hacía la raíz

• También se puede hacer recursiva

– Usa delimitadores: “>” antes y “=“ después• Para evitar el código 30 se confunda con el 3030

por ejemplo• Facilita las búsquedas

– Ejemplo: path de 30:

>1=>8=>30=

Función de ruta

Create Function ComponePathDato( @Código As Int )Returns nVarChar(Max)As Begin

Declare @CódigoPadre IntDeclare @Path nVarChar(Max)Set @Path = '>' + Convert(nVarChar(Max),@Código) + '='

Select @CódigoPadre = CódigoPadreFrom Datos Where Código = @CódigoIf Not (@CódigoPadre) Is Null

Set @Path = dbo.ComponePathDato(@CódigoPadre) + @Path

Return @PathEnd

Select Código, dbo.ComponePathDato(Código)From DatosWhere Nivel = 3 or Nivel = 4

Código ----------- -------------------29 >1=>8=>29=30 >1=>8=>30=31 >1=>8=>31=32 >1=>8=>32=33 >1=>9=>33=34 >1=>9=>34=35 >1=>9=>35=36 >1=>8=>36=37 >1=>11=>37=38 >1=>11=>38=39 >1=>13=>39=40 >1=>13=>39=>40=41 >1=>13=>41=42 >1=>13=>41=>42=43 >1=>13=>41=>43=


Ruta como campo calculado

• Similar al caso de Nivel



Descripción nVarChar(Max),Nivel As dbo.CalculaNivelDato(Código),Ruta As dbo.ComponePathDato(Código),Constraint PK_Datos Primary Key Clustered

( Código ASC ))

Select Código, Nivel, RutaFrom Datos Where Nivel = 4

Código Nivel Ruta----------- ----------- ----------------40 4 >1=>13=>39=>40=42 4 >1=>13=>41=>42=43 4 >1=>13=>41=>43=

(3 row(s) affected)

¡Ahora sí! Descendientes de “R”

• Descendientes de “R” tienen la ruta de “R” en su ruta

Declare @Ruta nVarChar(Max)

Select @Ruta = Ruta From Datos Where Código = 13

Select * From Datos Where Ruta Like @Ruta + '%' And Ruta <> @Ruta

Código CódigoPadre Descripción Nivel Ruta----------- ----------- --------------- ----------- ----------------39 13 Chiantla 3 >1=>13=>39=40 39 Los Regadillos 4 >1=>13=>39=>40=41 13 Huehuetenango 3 >1=>13=>41=42 41 El Terrero 4 >1=>13=>41=>42=43 41 El Cambote 4 >1=>13=>41=>43=

(5 row(s) affected)

Descendientes de “P” en nivel “m”

• Igual que el anterior– Pero con condición sobre el nivelDeclare @Ruta nVarChar(Max)

Select @Ruta = Ruta From Datos Where Código = 13

Select * From Datos Where Ruta Like @Ruta + '%' And Ruta <> @Ruta And Nivel = 3

Código CódigoPadre Descripción Nivel Ruta----------- ----------- ------------- ----------- ------------39 13 Chiantla 3 >1=>13=>39=41 13 Huehuetenango 3 >1=>13=>41=

(2 row(s) affected)

Select * From Datos Where Ruta Like @Ruta + '%' And Ruta <> @Ruta And Nivel = 4

Código CódigoPadre Descripción Nivel Ruta----------- ----------- --------------- ----------- ----------------40 39 Los Regadillos 4 >1=>13=>39=>40=42 41 El Terrero 4 >1=>13=>41=>42=43 41 El Cambote 4 >1=>13=>41=>43=

(3 row(s) affected)

Variaciones de las funciones

• Calcular el nivel a partir de la ruta– El nivel es el número de “>” o “=“ en la ruta

• Transformar las funciones a formas no recursivas

• Usar vistas para evitar los campos calculados

• Poner el código en “Identity” generado automáticamente

Formas no recursivas

• Función de cálculo de nivel

Create Function CalculaNivelDato( @Código As Int )Returns IntAs Begin

Declare @CódigoPadre IntSelect @CódigoPadre = CódigoPadre From Datos Where Código = @CódigoDeclare @Nivel IntSet @Nivel = 1While Not @CódigoPadre Is Null Begin

Set @Nivel = @Nivel + 1Select @CódigoPadre = CódigoPadre From Datos Where Código = @CódigoPadre

EndReturn @Nivel

End

Formas no recursivas

• Función de composición de rutas

Create Function ComponePathDato( @Código As Int )Returns nVarChar(Max)As Begin

Declare @CódigoPadre IntDeclare @Path nVarChar(Max)Select @CódigoPadre = CódigoPadre,@Path = '>' + Convert(nVarChar(Max),Código) + '='From Datos Where Código = @CódigoWhile Not @CódigoPadre Is Null

Select @CódigoPadre = CódigoPadre,@Path = '>' + Convert(nVarChar(Max),Código) + '=' + @PathFrom Datos Where Código = @CódigoPadre

Return @PathEnd

tablas y funciones de sql server para implementar una jerarquía sin límite de niveles leonel...

Documents