Pelajaran 2: Membuat dan mengelola data dalam tabel hierarkis

Artikel
07/30/2024

Berlaku untuk: SQL Server Azure SQL Database Azure SQL Managed Instance

Di Pelajaran 1, Anda memodifikasi tabel yang ada untuk menggunakan jenis data hierarkiid , dan mengisi kolom hierarkiid dengan representasi data yang ada. Dalam pelajaran ini, Anda memulai dengan tabel baru, dan menyisipkan data dengan menggunakan metode hierarkis. Kemudian, Anda mengkueri dan memanipulasi data dengan menggunakan metode hierarkis.

Prasyarat

Untuk menyelesaikan tutorial ini, Anda memerlukan SQL Server Management Studio, akses ke server yang menjalankan SQL Server, dan AdventureWorks2022 database.

Instal SQL Server Management Studio (SSMS).
Instal Edisi Pengembang SQL Server 2022.
Unduh database sampel AdventureWorks.

Instruksi untuk memulihkan database di SQL Server Management Directory ada di sini: Memulihkan Pencadangan Database Menggunakan SSMS.

Membuat tabel menggunakan tipe data hierarkis

Contoh berikut membuat tabel bernama EmployeeOrg, yang menyertakan data karyawan bersama dengan hierarki pelaporan mereka. Contoh membuat tabel dalam AdventureWorks2022 database, tetapi itu opsional. Agar contoh tetap sederhana, tabel ini hanya menyertakan lima kolom:

OrgNode adalah kolom hierarki yang menyimpan hubungan hierarkis.
OrgLevel adalah kolom komputasi, berdasarkan OrgNode kolom yang menyimpan setiap tingkat simpul dalam hierarki. Ini digunakan untuk indeks yang mengutamakan luas.
EmployeeID berisi nomor identifikasi karyawan umum yang digunakan untuk aplikasi seperti penggajian. Dalam pengembangan aplikasi baru, aplikasi dapat menggunakan OrgNode kolom dan kolom terpisah EmployeeID ini tidak diperlukan.
EmpName berisi nama karyawan.
Title berisi jabatan karyawan.

Membuat tabel EmployeeOrg

Di jendela Editor Kueri, jalankan kode berikut untuk membuat EmployeeOrg tabel. OrgNode Menentukan kolom sebagai kunci utama dengan indeks berkluster membuat indeks kedalaman pertama:

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

Jalankan kode berikut untuk membuat indeks komposit pada OrgLevel kolom dan OrgNode untuk mendukung pencarian yang mengutamakan luas yang efisien:
```
CREATE UNIQUE INDEX EmployeeOrgNc1
ON HumanResources.EmployeeOrg(OrgLevel, OrgNode);
GO
```

Tabel sekarang siap untuk data. Tugas berikutnya akan mengisi tabel dengan menggunakan metode hierarkis.

Mengisi tabel hierarkis menggunakan metode hierarkis

AdventureWorks2022 memiliki delapan karyawan yang bekerja di departemen Pemasaran. Hierarki karyawan terlihat seperti ini:

David, EmployeeID 6, adalah Manajer Pemasaran. Tiga Spesialis Pemasaran melaporkan kepada David:

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

Asisten Wanida Pemasaran (EmployeeID 269), laporan ke Sariya, dan Asisten Mary Pemasaran (EmployeeID 272), laporan ke John.

Sisipkan akar pohon hierarki

Contoh berikut menyisipkan David Manajer Pemasaran ke dalam tabel di akar hierarki. Kolom OrdLevel adalah kolom komputasi. Oleh karena itu, itu bukan bagian INSERT dari pernyataan. Rekaman pertama ini menggunakan metode GetRoot (Mesin Database) untuk mengisi rekaman pertama ini sebagai akar hierarki.
```
INSERT HumanResources.EmployeeOrg (OrgNode, EmployeeID, EmpName, Title)
VALUES (hierarchyid::GetRoot(), 6, 'David', 'Marketing Manager');
GO
```

Jalankan kode berikut untuk memeriksa baris awal dalam tabel:

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

Berikut set hasilnya.

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

Seperti pada pelajaran sebelumnya, kami menggunakan ToString() metode untuk mengonversi jenis data hierarkis ke format yang lebih mudah dipahami.

Menyisipkan karyawan subordinat

Sariya melaporkan ke David. Untuk menyisipkan Sariya's simpul, Anda harus membuat nilai hierarkiid tipe data yang sesuaiOrgNode. Kode berikut membuat variabel hierarkiid jenis data dan mengisinya dengan nilai OrgNode akar tabel. Kemudian menggunakan variabel tersebut dengan metode GetDescendant (Mesin Database) untuk menyisipkan baris yang merupakan simpul subordinat. GetDescendant mengambil dua argumen. Tinjau opsi berikut untuk nilai argumen:
- Jika induk adalah NULL, GetDescendant mengembalikan NULL.
- Jika induk bukan NULL, dan keduanya child1 dan child2 adalah NULL, GetDescendant mengembalikan anak dari induk.
- Jika induk dan child1 bukan NULL, dan child2 adalah NULL, GetDescendant mengembalikan anak dari induk yang lebih besar dari child1.
- Jika induk dan child2 bukan NULL dan child1 adalah NULL, GetDescendant mengembalikan anak dari induk kurang dari child2.
- Jika induk, , dan child2 semuanya bukan NULL, GetDescendant mengembalikan turunan induk yang lebih besar dari child1 dan kurang dari child2. child1
Kode berikut menggunakan (NULL, NULL) argumen induk akar karena belum ada baris apa pun dalam tabel kecuali root. Jalankan kode berikut untuk menyisipkan 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');
```

Ulangi kueri dari prosedur pertama untuk mengkueri tabel dan lihat bagaimana entri muncul:

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

Berikut set hasilnya.

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

Membuat prosedur untuk memasukkan simpul baru

Untuk menyederhanakan memasukkan data, buat prosedur tersimpan berikut untuk menambahkan karyawan ke EmployeeOrg tabel. Prosedur ini menerima nilai input tentang karyawan yang ditambahkan. Ini termasuk EmployeeID manajer karyawan baru, nomor karyawan EmployeeID baru, serta nama depan dan jabatan mereka. Prosedur ini menggunakan GetDescendant() dan juga metode GetAncestor (Mesin Database). Jalankan kode berikut untuk membuat prosedur:

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

Contoh berikut menambahkan empat karyawan tersisa yang melaporkan secara langsung atau tidak langsung ke 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';

Sekali lagi, jalankan kueri berikut memeriksa baris dalam EmployeeOrg tabel:

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

Berikut set hasilnya.

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

Tabel sekarang sepenuhnya diisi dengan organisasi Pemasaran.

Mengkueri tabel hierarkis menggunakan metode hierarki

Sekarang setelah tabel HumanResources.EmployeeOrg diisi sepenuhnya, tugas ini memperlihatkan kepada Anda cara mengkueri hierarki menggunakan beberapa metode hierarkis.

Menemukan simpul subordinat

Sariya memiliki satu karyawan bawahan. Untuk mengkueri bawahan Sariya, jalankan kueri berikut yang menggunakan metode IsDescendantOf (Mesin Database):
```
DECLARE @CurrentEmployee HIERARCHYID

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

SELECT *
FROM HumanResources.EmployeeOrg
WHERE OrgNode.IsDescendantOf(@CurrentEmployee ) = 1;
```
Hasilnya mencantumkan dan Sariya Wanida. Sariyadicantumkan karena nilai tersebut adalah turunan pada tingkat .0 Wanidaadalah keturunan pada tingkat.1
Anda juga dapat mengkueri informasi ini dengan menggunakan metode GetAncestor (Mesin Database). GetAncestor mengambil argumen untuk tingkat yang coba Anda kembalikan. Karena Wanida adalah satu tingkat di bawah Sariya, gunakan GetAncestor(1) seperti yang ditunjukkan dalam kode berikut:
```
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
```
Kali ini hasilnya hanya mencantumkan Wanida.

Sekarang ubah menjadi @CurrentEmployee David (EmployeeID 6) dan level menjadi 2. Jalankan yang berikut ini untuk mengembalikan 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

Kali ini, Anda juga menerima Mary yang juga melapor ke David, dua tingkat turun.

Menggunakan GetRoot, dan GetLevel

Ketika hierarki tumbuh lebih besar, lebih sulit untuk menentukan di mana anggota berada dalam hierarki. Gunakan metode GetLevel (Mesin Database) untuk menemukan berapa banyak tingkat turun setiap baris dalam hierarki. Jalankan kode berikut untuk melihat tingkat semua baris:
```
SELECT OrgNode.ToString() AS Text_OrgNode,
OrgNode.GetLevel() AS EmpLevel, *
FROM HumanResources.EmployeeOrg;
GO
```
Gunakan metode GetRoot (Mesin Database) untuk menemukan simpul akar dalam hierarki. Kode berikut mengembalikan satu baris, yang merupakan akarnya:
```
SELECT OrgNode.ToString() AS Text_OrgNode, *
FROM HumanResources.EmployeeOrg
WHERE OrgNode = HIERARCHYID::GetRoot();
GO
```

Menyusun ulang data dalam tabel hierarkis menggunakan metode hierarkis

Berlaku untuk: SQL Server

Mengatur ulang hierarki adalah tugas pemeliharaan umum. Dalam tugas ini, kita menggunakan UPDATE pernyataan dengan metode GetReparentedValue (Mesin Database) untuk terlebih dahulu memindahkan satu baris ke lokasi baru dalam hierarki. Kemudian kita memindahkan seluruh subtree ke lokasi baru.

Metode ini GetReparentedValue mengambil dua argumen. Argumen pertama menjelaskan bagian hierarki yang akan dimodifikasi. Misalnya, jika hierarki adalah /1/4/2/3/ dan Anda ingin mengubah /1/4/ bagian, hierarki menjadi /2/1/2/3/, membiarkan dua simpul terakhir (2/3/) tidak berubah, Anda harus memberikan node yang berubah (/1/4/) sebagai argumen pertama. Argumen kedua menyediakan tingkat hierarki baru, dalam contoh /2/1/kami . Dua argumen tidak harus berisi jumlah tingkat yang sama.

Memindahkan satu baris ke lokasi baru dalam hierarki

Saat ini Wanida melapor ke Sariya. Dalam prosedur ini, Anda memindahkan Wanida dari simpul /1/1/saat ini , sehingga orang ini melaporkan ke Jill. Simpul baru menjadi /3/1/ demikian /1/ adalah argumen pertama dan /3/ merupakan yang kedua. Ini sesuai dengan OrgNode nilai Sariya dan Jill. Jalankan kode berikut untuk memindahkan Wanida dari organisasi Sariya ke 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

Jalankan kode berikut untuk melihat hasilnya:

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

Wanida sekarang berada di simpul /3/1/.

Mengatur ulang bagian hierarki

Untuk menunjukkan cara memindahkan sejumlah besar orang secara bersamaan, pertama-tama jalankan kode berikut untuk menambahkan pelaporan magang ke Wanida:
```
EXEC AddEmp 269, 291, 'Kevin', 'Marketing Intern';
GO
```

Sekarang Kevin melapor ke Wanida, yang melapor ke Jill, yang melapor ke David. Itu berarti bahwa Kevin berada di tingkat /3/1/1/. Untuk memindahkan semua bawahan Jill ke manajer baru, kami memperbarui semua simpul yang memiliki /3/ OrgNode nilai baru. Jalankan kode berikut untuk memperbarui Wanida untuk melapor ke Sariya, tetapi tetap lapor Kevin ke 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;

Jalankan kode berikut untuk melihat hasilnya:

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

Berikut set hasilnya.

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

Seluruh pohon organisasi yang melapor ke Jill (baik Wanida maupun Kevin), sekarang melapor ke Sariya.

Untuk prosedur tersimpan untuk mengatur ulang bagian hierarki, lihat bagian Memindahkan subtrees data Hierarkis (SQL Server).

Bagikan melalui

Pelajaran 2: Membuat dan mengelola data dalam tabel hierarkis

Prasyarat

Membuat tabel menggunakan tipe data hierarkis

Membuat tabel EmployeeOrg

Mengisi tabel hierarkis menggunakan metode hierarkis

Sisipkan akar pohon hierarki

Menyisipkan karyawan subordinat

Membuat prosedur untuk memasukkan simpul baru

Mengkueri tabel hierarkis menggunakan metode hierarki

Menemukan simpul subordinat

Menggunakan GetRoot, dan GetLevel

Menyusun ulang data dalam tabel hierarkis menggunakan metode hierarkis

Memindahkan satu baris ke lokasi baru dalam hierarki

Mengatur ulang bagian hierarki

Saran dan Komentar

Sumber Daya Tambahan: