Lección 2: Creación y administración de los datos de una tabla jerárquica

Artículo
08/01/2024

Se aplica a: SQL Server Azure SQL Database Azure SQL Managed Instance

En la lección 1, ha modificado una tabla existente para usar el tipo de datos hierarchyid y ha rellenado la columna hierarchyid con la representación de los datos existentes. En esta lección, se inicia con una nueva tabla e insertará los datos utilizando los métodos jerárquicos. A continuación, se consultan y manipulan los datos utilizando los métodos jerárquicos.

Requisitos previos

Para llevar a cabo este tutorial necesita tener SQL Server Management Studio, acceso a un servidor que ejecute SQL Server y una base de datos AdventureWorks2022.

Instale SQL Server Management Studio (SSMS).
Instale SQL Server 2022 Developer Edition.
Descargue la base de datos de ejemplo AdventureWorks.

Aquí encontrará instrucciones para restaurar bases de datos en SSMS: Restaurar backup de una base de datos con SSMS.

Creación de una tabla mediante el tipo de datos hierarchyid

El ejemplo siguiente crea una tabla denominada EmployeeOrg, que contiene los datos del empleado y la jerarquía correspondiente. El ejemplo crea la tabla en la base de datos AdventureWorks2022 , aunque esta ubicación es opcional. Para mantener un esquema sencillo del ejemplo, esta tabla solo incluye cinco columnas:

OrgNode es una columna hierarchyid que almacena la relación jerárquica.
OrgLevel es una columna calculada que se basa en la columna OrgNode y que almacena todos los niveles de nodos de la jerarquía. Se usa para crear un índice con prioridad a la amplitud.
EmployeeID contiene el número de identificación típico del empleado que se utiliza para aplicaciones como, por ejemplo, la nómina. En el nuevo desarrollo de aplicaciones, las aplicaciones pueden utilizar la columna OrgNode y la columna EmployeeID independiente no es necesaria.
EmpName contiene el nombre del empleado.
Title contiene el título del empleado.

Creación de la tabla EmployeeOrg

En una ventana del Editor de consultas, ejecute el código siguiente para crear la tabla EmployeeOrg . Al especificar la columna OrgNode como la clave principal con un índice clúster, se crea un índice con prioridad a la profundidad:

USE AdventureWorks2022;
GO

IF OBJECT_ID('HumanResources.EmployeeOrg') IS NOT NULL
    DROP TABLE HumanResources.EmployeeOrg

CREATE TABLE HumanResources.EmployeeOrg (
    OrgNode HIERARCHYID PRIMARY KEY CLUSTERED,
    OrgLevel AS OrgNode.GetLevel(),
    EmployeeID INT UNIQUE NOT NULL,
    EmpName VARCHAR(20) NOT NULL,
    Title VARCHAR(20) NULL
);
GO

Ejecute el código siguiente para crear un índice compuesto en las columnas OrgLevel y OrgNode que admita búsquedas eficaces con prioridad a la amplitud:
```
CREATE UNIQUE INDEX EmployeeOrgNc1
ON HumanResources.EmployeeOrg(OrgLevel, OrgNode);
GO
```

Ahora ya puede usar la tabla para trabajar con datos. La siguiente tarea rellenará la tabla mediante métodos jerárquicos.

Relleno de una tabla jerárquica mediante métodos jerárquicos

AdventureWorks2022 tiene 8 empleados trabajando en el departamento de marketing. La jerarquía de empleados ofrece el siguiente aspecto:

David, EmployeeID 6, es el director de marketing. Tres especialistas en marketing notifican a David:

Sariya, EmployeeID 46
John, EmployeeID 271
Jill, EmployeeID 119

El asistente Wanida de marketing (EmployeeID 269), informa a Sariyay el asistente de marketing Mary (EmployeeID 272), informa a John.

Inserción de la raíz del árbol de jerarquía

El ejemplo siguiente inserta a David, director de marketing, en la raíz de la jerarquía dentro de la tabla. La columna OrdLevel es una columna calculada. Por lo tanto, no forma parte de la instrucción INSERT. Este primer registro usa el método GetRoot (Motor de base de datos) para que se rellene este primer registro como la raíz de la jerarquía.
```
INSERT HumanResources.EmployeeOrg (OrgNode, EmployeeID, EmpName, Title)
VALUES (hierarchyid::GetRoot(), 6, 'David', 'Marketing Manager');
GO
```

Ejecute el código siguiente para examinar la fila inicial de la tabla:

SELECT OrgNode.ToString() AS Text_OrgNode,
OrgNode, OrgLevel, EmployeeID, EmpName, Title
FROM HumanResources.EmployeeOrg;

Este es el conjunto de resultados.

Text_OrgNode OrgNode OrgLevel EmployeeID EmpName Title
------------ ------- -------- ---------- ------- -----------------
/            Ox      0        6          David   Marketing Manager

Al igual que en la lección anterior, se usa el método ToString() para convertir el tipo de datos hierarchyid en un formato más comprensible.

Inserción de un empleado subordinado

Sariya informa a David. Para insertar el nodo Sariya's, debe crear un valor OrgNode adecuado del tipo de datos de hierarchyid. El código siguiente crea una variable de tipo de datos de hierarchyid y lo rellena con el valor raíz OrgNode de la tabla. Después, usa esa variable con el método GetDescendant (Motor de base de datos) para insertar una fila que es un nodo subordinado. GetDescendant toma dos argumentos. Revise las opciones siguientes de los valores de argumento:
- Si el elemento primario es NULL, GetDescendant devuelve NULL.
- Si el elemento primario no es NULL, y tanto child1 como child2 son NULL, GetDescendant devuelve un elemento secundario del elemento primario.
- Si el elemento primario y child1 no son NULL, y child2 es NULL, GetDescendant devuelve un elemento secundario de primario mayor que child1.
- Si el elemento primario y child2 no son NULL, y child1 es NULL, GetDescendant devuelve un elemento secundario de primario menor que child2.
- Si el elemento primario, child1, y child2 no son NULL, GetDescendant devuelve un elemento secundario del elemento primario mayor que child1 y menor que child2.
El código siguiente utiliza los argumentos (NULL, NULL) de la raíz primaria porque todavía no hay filas en la tabla (excepto la raíz). Ejecute el código siguiente para insertar Sariya:
```
DECLARE @Manager HIERARCHYID
SELECT @Manager = HIERARCHYID::GetRoot()
FROM HumanResources.EmployeeOrg;

INSERT HumanResources.EmployeeOrg (OrgNode, EmployeeID, EmpName, Title)
VALUES
(@Manager.GetDescendant(NULL, NULL), 46, 'Sariya', 'Marketing Specialist');
```

Repita la consulta realizada con el primer procedimiento para consultar la tabla y ver cómo aparecen las entradas:

SELECT OrgNode.ToString() AS Text_OrgNode,
OrgNode, OrgLevel, EmployeeID, EmpName, Title
FROM HumanResources.EmployeeOrg;

Este es el conjunto de resultados.

Text_OrgNode OrgNode OrgLevel EmployeeID EmpName Title
------------ ------- -------- ---------- ------- -----------------
/            Ox      0        6          David   Marketing Manager
/1/          0x58    1        46         Sariya  Marketing Specialist

Creación de un procedimiento para escribir nuevos nodos

Para simplificar la entrada de datos, cree el siguiente procedimiento almacenado a fin de agregar empleados a la tabla EmployeeOrg. El procedimiento acepta valores de entrada sobre el empleado que se está agregando. Este proceso contiene el EmployeeID del jefe del nuevo empleado, el número EmployeeID del nuevo empleado, así como su nombre y puesto. El procedimiento usa GetDescendant() y también el método GetAncestor (Motor de base de datos). Ejecute el código siguiente para crear el procedimiento:

CREATE PROCEDURE AddEmp (
    @mgrid INT,
    @empid INT,
    @e_name VARCHAR(20),
    @title VARCHAR(20)
)
AS
BEGIN
    DECLARE @mOrgNode HIERARCHYID, @lc HIERARCHYID;

    SELECT @mOrgNode = OrgNode
    FROM HumanResources.EmployeeOrg
    WHERE EmployeeID = @mgrid;

    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

    BEGIN TRANSACTION;

    SELECT @lc = max(OrgNode)
    FROM HumanResources.EmployeeOrg
    WHERE OrgNode.GetAncestor(1) = @mOrgNode;

    INSERT HumanResources.EmployeeOrg (OrgNode, EmployeeID, EmpName, Title)
    VALUES (@mOrgNode.GetDescendant(@lc, NULL), @empid, @e_name, @title);

    COMMIT;
END;
GO

El ejemplo siguiente agrega los 4 empleados restantes que notifican directa o indirectamente a David.

EXEC AddEmp 6, 271, 'John', 'Marketing Specialist';
EXEC AddEmp 6, 119, 'Jill', 'Marketing Specialist';
EXEC AddEmp 46, 269, 'Wanida', 'Marketing Assistant';
EXEC AddEmp 271, 272, 'Mary', 'Marketing Assistant';

Vuelva a ejecutar la consulta siguiente y examine las filas de la tabla EmployeeOrg:

SELECT OrgNode.ToString() AS Text_OrgNode,
OrgNode, OrgLevel, EmployeeID, EmpName, Title
FROM HumanResources.EmployeeOrg;
GO

Este es el conjunto de resultados.

Text_OrgNode OrgNode OrgLevel EmployeeID EmpName Title
------------ ------- -------- ---------- ------- --------------------
/            Ox      0        6          David   Marketing Manager
/1/          0x58    1        46         Sariya  Marketing Specialist
/1/1/        0x5AC0  2        269        Wanida  Marketing Assistant
/2/          0x68    1        271        John    Marketing Specialist
/2/1/        0x6AC0  2        272        Mary    Marketing Assistant
/3/          0x78    1        119        Jill    Marketing Specialist

La tabla estará totalmente rellena con la organización del departamento de marketing.

Consultar una tabla jerárquica mediante métodos de jerarquía

Ahora que está totalmente rellena la tabla HumanResources.EmployeeOrg, esta tarea muestra cómo realizar una consulta en la jerarquía utilizando alguno de los métodos jerárquicos.

Búsqueda de nodos subordinados

Sariya tiene un empleado subordinado. Para consultar los subordinados de Sariya, ejecute la consulta siguiente que usa el método IsDescendantOf (Motor de base de datos):
```
DECLARE @CurrentEmployee HIERARCHYID

SELECT @CurrentEmployee = OrgNode
FROM HumanResources.EmployeeOrg
WHERE EmployeeID = 46;

SELECT *
FROM HumanResources.EmployeeOrg
WHERE OrgNode.IsDescendantOf(@CurrentEmployee ) = 1;
```
El resultado enumera Sariya y Wanida. Sariya aparece porque el valor es el descendiente de nivel 0. Wanida es el descendiente de nivel 1.
También se puede consultar esta información mediante el método GetAncestor (Motor de base de datos). GetAncestor toma un argumento para el nivel que está intentando devolver. Puesto que Wanida está un nivel por debajo de Sariya, use GetAncestor(1) como se muestra en el código siguiente:
```
DECLARE @CurrentEmployee HIERARCHYID

SELECT @CurrentEmployee = OrgNode
FROM HumanResources.EmployeeOrg
WHERE EmployeeID = 46;

SELECT OrgNode.ToString() AS Text_OrgNode, *
FROM HumanResources.EmployeeOrg
WHERE OrgNode.GetAncestor(1) = @CurrentEmployee
```
Esta vez el resultado solo muestra a Wanida.

Ahora cambie @CurrentEmployee a David (EmployeeID 6) y el nivel a 2. Ejecute lo siguiente para que también devuelva a Wanida:

DECLARE @CurrentEmployee HIERARCHYID

SELECT @CurrentEmployee = OrgNode
FROM HumanResources.EmployeeOrg
WHERE EmployeeID = 6;

SELECT OrgNode.ToString() AS Text_OrgNode, *
FROM HumanResources.EmployeeOrg
WHERE OrgNode.GetAncestor(2) = @CurrentEmployee

Esta vez, recibe también a Mary que también notifica a David, dos niveles más abajo.

Uso de GetRoot y GetLevel

A medida que la jerarquía crece, es más difícil determinar el lugar que ocupan los miembros en la jerarquía. Use el método GetLevel (Motor de base de datos) para buscar cuántos niveles existen bajo cada fila de la jerarquía. Ejecute el código siguiente para ver los niveles de todas las filas:
```
SELECT OrgNode.ToString() AS Text_OrgNode,
OrgNode.GetLevel() AS EmpLevel, *
FROM HumanResources.EmployeeOrg;
GO
```
Use el método GetRoot (Motor de base de datos) para buscar el nodo raíz en la jerarquía. El código siguiente devuelve la fila única que es la raíz:
```
SELECT OrgNode.ToString() AS Text_OrgNode, *
FROM HumanResources.EmployeeOrg
WHERE OrgNode = HIERARCHYID::GetRoot();
GO
```

Reordenación de los datos de una tabla jerárquica mediante métodos jerárquicos

Se aplica a: SQL Server

Reorganizar una jerarquía es una tarea de mantenimiento común. En esta tarea, usaremos una instrucción UPDATE con el método GetReparentedValue (Motor de base de datos) para mover primero una única fila a una nueva ubicación en la jerarquía. A continuación, movemos un subárbol completo a una nueva ubicación.

El método GetReparentedValue tiene dos argumentos. El primer argumento describe la parte de la jerarquía que se va a modificar. Por ejemplo, si una jerarquía es /1/4/2/3/ y quiere cambiar la sección/1/4/, la jerarquía se vuelve /2/1/2/3/, dejando los dos últimos nodos (2/3/) sin modificar. Debe proporcionar los nodos que cambian (/1/4/) como el primer argumento. El segundo argumento proporciona el nuevo nivel de jerarquía; en nuestro ejemplo /2/1/. No es necesario que los dos argumentos tengan el mismo número de niveles.

Desplazamiento de una fila a una ubicación nueva en la jerarquía

Actualmente Wanida notifica a Sariya. En este procedimiento, se mueve a Wanida de su nodo actual /1/1/, de modo que esta persona notificará a Jill. Su nuevo nodo se volverá /3/1/ para que /1/ sea el primer argumento y /3/ el segundo. Estos se corresponden con los valores OrgNode de Sariya y Jill. Ejecute el código siguiente para mover a Wanida de la organización de Sariya a la de Jill:

DECLARE @CurrentEmployee HIERARCHYID,
    @OldParent HIERARCHYID,
    @NewParent HIERARCHYID;

SELECT @CurrentEmployee = OrgNode
FROM HumanResources.EmployeeOrg
WHERE EmployeeID = 269;

SELECT @OldParent = OrgNode
FROM HumanResources.EmployeeOrg
WHERE EmployeeID = 46;

SELECT @NewParent = OrgNode
FROM HumanResources.EmployeeOrg
WHERE EmployeeID = 119;

UPDATE HumanResources.EmployeeOrg
SET OrgNode = @CurrentEmployee.GetReparentedValue(@OldParent, @NewParent)
WHERE OrgNode = @CurrentEmployee;
GO

Ejecute el código siguiente para ver el resultado:

SELECT OrgNode.ToString() AS Text_OrgNode,
OrgNode, OrgLevel, EmployeeID, EmpName, Title
FROM HumanResources.EmployeeOrg;
GO

Wanida está ahora en el nodo /3/1/.

Reorganización de una sección de una jerarquía

Para mostrar cómo mover al mismo tiempo un gran número de personas, ejecute primero el código siguiente para agregar un becario a cargo de Wanida:
```
EXEC AddEmp 269, 291, 'Kevin', 'Marketing Intern';
GO
```

Ahora Kevin notifica a Wanida, quien notifica a Jill, quien, a su vez, notifica a David. Eso quiere decir que Kevin está en el nivel /3/1/1/. Para mover todos los subordinados de Jill a un nuevo administrador, vamos a actualizar a un nuevo valor todos los nodos que tienen /3/ como OrgNode. Ejecute el código siguiente para actualizar a Wanida de manera que dependa de Sariya, pero dejando que Kevin dependa de Wanida:

DECLARE @OldParent HIERARCHYID,
    @NewParent HIERARCHYID

SELECT @OldParent = OrgNode
FROM HumanResources.EmployeeOrg
WHERE EmployeeID = 119;-- Jill

SELECT @NewParent = OrgNode
FROM HumanResources.EmployeeOrg
WHERE EmployeeID = 46;-- Sariya

DECLARE children_cursor CURSOR
FOR
SELECT OrgNode
FROM HumanResources.EmployeeOrg
WHERE OrgNode.GetAncestor(1) = @OldParent;

DECLARE @ChildId HIERARCHYID;

OPEN children_cursor

FETCH NEXT
FROM children_cursor
INTO @ChildId;

WHILE @@FETCH_STATUS = 0
BEGIN
    START:

    DECLARE @NewId HIERARCHYID;

    SELECT @NewId = @NewParent.GetDescendant(MAX(OrgNode), NULL)
    FROM HumanResources.EmployeeOrg
    WHERE OrgNode.GetAncestor(1) = @NewParent;

    UPDATE HumanResources.EmployeeOrg
    SET OrgNode = OrgNode.GetReparentedValue(@ChildId, @NewId)
    WHERE OrgNode.IsDescendantOf(@ChildId) = 1;

    IF @@error <> 0
        GOTO START -- On error, retry

    FETCH NEXT
    FROM children_cursor
    INTO @ChildId;
END

CLOSE children_cursor;

DEALLOCATE children_cursor;

Ejecute el código siguiente para ver el resultado:

SELECT OrgNode.ToString() AS Text_OrgNode,
OrgNode, OrgLevel, EmployeeID, EmpName, Title
FROM HumanResources.EmployeeOrg;
GO

Este es el conjunto de resultados.

Text_OrgNode OrgNode OrgLevel EmployeeID EmpName Title
------------ ------- -------- ---------- ------- -----------------
/            Ox      0        6          David   Marketing Manager
/1/          0x58    1        46         Sariya  Marketing Specialist
/1/1/        0x5AC0  2        269        Wanida  Marketing Assistant
/1/1/1/      0x5AD0  3        291        Kevin   Marketing Intern
/2/          0x68    1        271        John    Marketing Specialist
/2/1/        0x6AC0  2        272        Mary    Marketing Assistant
/3/          0x78    1        119        Jill    Marketing Specialist

El árbol completo de la organización que notificó a Jill (tanto Wanida como Kevin) ahora notifica a Sariya.

Para obtener un procedimiento almacenado para reorganizar una sección de una jerarquía, consulte la sección Mover subárboles de datos jerárquicos (SQL Server).

Compartir vía

Lección 2: Creación y administración de los datos de una tabla jerárquica

Requisitos previos

Creación de una tabla mediante el tipo de datos hierarchyid

Creación de la tabla EmployeeOrg

Relleno de una tabla jerárquica mediante métodos jerárquicos

Inserción de la raíz del árbol de jerarquía

Inserción de un empleado subordinado

Creación de un procedimiento para escribir nuevos nodos

Consultar una tabla jerárquica mediante métodos de jerarquía

Búsqueda de nodos subordinados

Uso de GetRoot y GetLevel

Reordenación de los datos de una tabla jerárquica mediante métodos jerárquicos

Desplazamiento de una fila a una ubicación nueva en la jerarquía

Reorganización de una sección de una jerarquía

Comentarios

Recursos adicionales