Tablas y Funciones de SQL Server para Implementar

Tablas y Funciones de SQL Server para Implementar una Jerarquía sin Límite de Niveles Leonel Morales Díaz Ingeniería Simple leonel@ingenieriasimple. com 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ódigo Descripción Hijos Código. Padre Descripción Nietos Código. Hijo. Padre 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 Código. Padre Descripción

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 1 2 3 4 5 1. 1 1. 2 1. 3 2. 1 2. 2 2. 3 3. 1 3. 2 4. 1 4. 2 5. 1 5. 2 1. 1. 1. 2 Cuenta Activo Pasivo Capital Gastos Ingresos Circulante Fijo Diferido Acciones al portador Acciones preferentes Fijos Variables Caja 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 CREATE TABLE Nomenclatura( Código n. Var. Char(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]') And (len(Código)=1 Or Not dbo. Cuenta. Padre(Código) Is Null)), Cuenta n. Var. Char(30) NULL, Constraint PK_Nomenclatura Primary Key Clustered ( Código ASC ) ) Nomenclatura Código Cuenta

Función para chequeo • Cuenta. Padre(@Código) – Encuentra la cuenta padre de @Código – Si no hay devuelve “Null” CREATE FUNCTION Cuenta. Padre ( @Código n. Var. Char(13) ) RETURNS n. Var. Char(13) AS BEGIN DECLARE @Resu n. Var. Char(13) Set @Resu = Null If Char. Index('. ', @Código) > 0 Begin Declare @Posible. Padre n. Var. Char(13) Set @Posible. Padre = RTrim(@Código) While Right(@Posible. Padre, 1) <> '. ' Set @Posible. Padre = Left(@Posible. Padre, Len(@Posible. Padre)-1) Select @Resu = Código From Nomenclatura Where Código = @Posible. Padre End RETURN @Resu END

Padre y nivel • Se pueden implementar con campos calculados • Padre – El valor devuelto por Cuenta. Padre • 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 CREATE TABLE Nomenclatura( Código n. Var. Char(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]') And (len(Código)=1 Or Not dbo. Cuenta. Padre(Código) Is Null)), Cuenta n. Var. Char(30) NULL, Padre As dbo. Cuenta. Padre(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 ) ) Nomenclatura Código Cuenta Padre Nivel

Datos de tabla Nomenclatura Select * From Nomenclatura Código ------1 1. 01. 001 1. 01. 002. 0001 1. 002. 0002 1. 002. 0003 1. 02 1. 03 2 2. 01 2. 02 2. 03 3 3. 01 3. 02 4 4. 01 4. 02 5 5. 01 5. 02 Cuenta ---------------Activo Circulante Caja Bancos Banco Industrial Banco Continental Banco Internacional Fijo Diferido Pasivo Circulante Fijo Diferido Capital Acciones al portador Acciones preferentes Gastos Fijos Variables Ingresos Fijos Variables (22 row(s) affected) Padre ------NULL 1 1. 01. 002 1 1 NULL 2 2 2 NULL 3 3 NULL 4 4 NULL 5 5 Nivel -----1 2 3 3 4 4 4 2 2 1 2 2

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. Padre – Descripción • El resto serán calculados – Padre – Nivel – Ruta Datos Código. Padre Descripción

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ódigo. Padre Int NULL Constraint FK_Datos_Código. Padre Foreign Key References Datos ( Código ), Descripción n. Var. Char(Max), Constraint PK_Datos Primary Key Clustered ( Código ASC ) ) Datos Código. Padre Descripción

Registros para pruebas Select * From Datos Código -----1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 Código. Padre -----NULL NULL 1 1 1 1 Descripción -----------Guatemala El Salvador Honduras Nicaragua Costa Rica Belice Panamá Guatemala Sacatepequez Chimaltenango Sololá Totonicapán Huehuetenango Quetzaltenango San Marcos Retalhuleu Suchitepequez Escuintla Santa Rosa Jutiapa 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 1 1 1 1 8 8 9 9 9 8 11 11 13 39 13 41 41 (43 row(s) affected) Jalapa Zacapa Izabal Baja Verapaz Alta Verapaz Quiché Petén El Progreso Ciudad de Guatemala Mixco Villa Nueva Jocotenango San Juan Sacatepequez San Raymundo Antigua Guatemala Amatitlán Atitlán San Pedro La Laguna Chiantla Los Regadillos Huehuetenango El Terrero El Cambote

Lista de padres • Padres: – Registros sin padre – Código. Padre Is Null Select * From Datos Where Código. Padre Is Null Código -----1 2 3 4 5 6 7 Código. Padre -----NULL NULL (7 row(s) affected) Descripción ---------Guatemala El Salvador Honduras Nicaragua Costa Rica Belice Panamá

Lista de registros en nivel “n” • Se necesita una función que calcule el nivel • Puede ser recursiva Create Function Calcula. Nivel. Dato ( @Código As Int ) Returns Int As Begin Declare @Código. Padre Int Declare @Nivel Int Select @Código. Padre = Código. Padre From Datos Where Código = @Código If (@Código. Padre) Is Null Set @Nivel = 1 Else Set @Nivel = dbo. Calcula. Nivel. Dato(@Código. Padre) + 1 Return @Nivel End Select * From Datos Where dbo. Calcula. Nivel. Dato(Código) = 3 Código -----29 30 31 32 33 34 35 36 37 38 39 41 Código. Padre -----8 8 9 9 9 8 11 11 13 13 (12 row(s) affected) Descripción ----------Ciudad de Guatemala Mixco Villa Nueva Jocotenango San Juan Sacatepequez San Raymundo Antigua Guatemala Amatitlán Atitlán San Pedro La Laguna Chiantla Huehuetenango

Nivel como campo calculado • Se puede incorporar el nivel como campo calculado – Usando la función Calcula. Nivel. Dato Select * From Datos Where Nivel = 3 or Nivel = 4 CREATE TABLE Datos( Código Int NOT NULL, Código. Padre Int NULL Constraint FK_Datos_Código. Padre Foreign Key References Datos ( Código ), Descripción n. Var. Char(Max), Nivel As dbo. Calcula. Nivel. Dato(Código), Constraint PK_Datos Primary Key Clustered ( Código ASC ) ) Código -----29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 Código. Padre -----8 8 9 9 9 8 11 11 13 39 13 41 41 (15 row(s) affected) Descripción -----------Ciudad de Guatemala Mixco Villa Nueva Jocotenango San Juan Sacatepequez San Raymundo Antigua Guatemala Amatitlán Atitlán San Pedro La Laguna Chiantla Los Regadillos Huehuetenango El Terrero El Cambote Nivel -----3 3 3 4 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 Compone. Path. Dato ( @Código As Int ) Returns n. Var. Char(Max) As Begin Declare @Código. Padre Int Declare @Path n. Var. Char(Max) Set @Path = '>' + Convert(n. Var. Char(Max), @Código) + '=' Select @Código. Padre = Código. Padre From Datos Where Código = @Código If Not (@Código. Padre) Is Null Set @Path = dbo. Compone. Path. Dato(@Código. Padre) + @Path Return @Path End Select Código, dbo. Compone. Path. Dato(Código) From Datos Where Nivel = 3 or Nivel = 4 Código -----29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 --------->1=>8=>29= >1=>8=>30= >1=>8=>31= >1=>8=>32= >1=>9=>33= >1=>9=>34= >1=>9=>35= >1=>8=>36= >1=>11=>37= >1=>11=>38= >1=>13=>39=>40= >1=>13=>41=>42= >1=>13=>41=>43= (15 row(s) affected)

Ruta como campo calculado • Similar al caso de Nivel CREATE TABLE Datos( Código Int NOT NULL, Código. Padre Int NULL Constraint FK_Datos_Código. Padre Foreign Key References Datos ( Código ), Descripción n. Var. Char(Max), Nivel As dbo. Calcula. Nivel. Dato(Código), Ruta As dbo. Compone. Path. Dato(Código), Constraint PK_Datos Primary Key Clustered ( Código ASC ) ) Select Código, Nivel, Ruta From Datos Where Nivel = 4 Código -----40 42 43 Nivel -----4 4 4 (3 row(s) affected) Ruta -------->1=>13=>39=>40= >1=>13=>41=>42= >1=>13=>41=>43=

¡Ahora sí! Descendientes de “R” • Descendientes de “R” tienen la ruta de “R” en su ruta Declare @Ruta n. Var. Char(Max) Select @Ruta = Ruta From Datos Where Código = 13 Select * From Datos Where Ruta Like @Ruta + '%' And Ruta <> @Ruta Código -----39 40 41 42 43 Código. Padre -----13 39 13 41 41 (5 row(s) affected) Descripción -------Chiantla Los Regadillos Huehuetenango El Terrero El Cambote Nivel -----3 4 4 Ruta -------->1=>13=>39=>40= >1=>13=>41=>42= >1=>13=>41=>43=

Descendientes de “P” en nivel “m” • Igual que el anterior – Pero condición sobre el nivel Declare @Ruta n. Var. Char(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 -----39 41 Código. Padre -----13 13 Descripción ------Chiantla Huehuetenango Nivel -----3 3 Ruta ------>1=>13=>39= >1=>13=>41= (2 row(s) affected) Select * From Datos Where Ruta Like @Ruta + '%' And Ruta <> @Ruta And Nivel = 4 Código -----40 42 43 Código. Padre -----39 41 41 (3 row(s) affected) Descripción -------Los Regadillos El Terrero El Cambote Nivel -----4 4 4 Ruta -------->1=>13=>39=>40= >1=>13=>41=>42= >1=>13=>41=>43=

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 Calcula. Nivel. Dato ( @Código As Int ) Returns Int As Begin Declare @Código. Padre Int Select @Código. Padre = Código. Padre From Datos Where Código = @Código Declare @Nivel Int Set @Nivel = 1 While Not @Código. Padre Is Null Begin Set @Nivel = @Nivel + 1 Select @Código. Padre = Código. Padre From Datos Where Código = @Código. Padre End Return @Nivel End

Formas no recursivas • Función de composición de rutas Create Function Compone. Path. Dato ( @Código As Int ) Returns n. Var. Char(Max) As Begin Declare @Código. Padre Int Declare @Path n. Var. Char(Max) Select @Código. Padre = Código. Padre, @Path = '>' + Convert(n. Var. Char(Max), Código) + '=' From Datos Where Código = @Código While Not @Código. Padre Is Null Select @Código. Padre = Código. Padre, @Path = '>' + Convert(n. Var. Char(Max), Código) + '=' + @Path From Datos Where Código = @Código. Padre Return @Path End