ورود به سیستم 
 
    محتویات سایت
        برچسب های محبوب 








 
   پیاده سازی ساختار های سلسله مراتبی در SQL Server
  در این مقاله به بررسی شیوه پیاده سازی ساختار های سلسله مراتبی (درختی) در SQL Server می پردازم .
   SQL Server
   ۳۱۶۲۳
   این مقاله حاوی فایل ضمیمه نمی باشد
   محمد زنگنه
   ۱۳۸۶/۱۲/۱
نسخه قابل چاپ نسخه قابل چاپ

مقدمه :
در اين مقاله به يکي از روشهاي پياده سازي ساختارهاي درختي يا سلسله مراتبي در پايگاه داده هاي رابطه اي مي پردازم

در تحليل برخي از سيستم ها و مسائل با پايگاه داده هاي سلسله مراتبي مواجه هستيم ، به اين معني که بين برخي از موجوديت هاي سيستم رابطه پدر فرزندي وجود دارد و در واقع با يک ساختار درختي مواجهيم .
به عنوان مثال رابطه بين بخش هاي مختلف يک سازمان (چارت سازماني) ، رابطه پيش نيازي بين دروس ، رابطه زير شاخه يا زير مجموعه بودن بين موجوديت ها و ...

پياده کردن اين نوع روابط در پايگاه داده رابطه اي کار آساني نيست . به عنوان مثال پيدا کردن تمام بخشهاي  زير مجموعه يک سازمان يا يافتن تمام پيش نيازهاي يک درس و ...
در اين موارد مي توانيم از توابع بازگشتي استفاده کنيم .

براي درک بهتر مطلب به بررسي مسئله چارت سازماني مي پردازيم .
سازمانها معمولا داراي يک سلسله مراتب سازماني يا همان چارت سازماني هستند به اين معنا که هر بخش آن سازمان زير مجموعه ( فرزند ) يک بخش ديگر سازمان است و بخش هاي ديگر سازمان ممکن است زير مجموعه اين بخش باشند ( اين بخش پدر بخش هاي ديگري باشد ) .
مانند شکل زير :

حال ما تابعي مي نويسيم که تمام بخش هاي زير مجموعه يک بخش را برگرداند .
اين تابع به اين صورت عمل مي کند که ابتدا تمام بخش هايي که ورودي تابع پدر آنها ست را انتخاب مي کند و براي هر يک از آنها دوباره خودش (بازگشتي) را اين بار با ID  فرزند صدا مي کند و نتيجه را در يک متغير از نوع Table  اضافه مي کند .
تابع زير ID يک بخش را گرفته و جدولي شامل تمام بخش هاي زير مجموعه آنرا بر مي گرداند .


CREATE FUNCTION [dbo].[fn_GetAllChildDepartment]
(@DepartmentID INT )
RETURNS @tbl_Temp TABLE (DepartmentID NVARCHAR(100))
AS
BEGIN
DECLARE @Parentid INT
SELECT @Parentid = ParentID FROM tbl_Department WHERE Department_ID = @DepartmentID

--    IF @grName <> ''
        INSERT INTO @tbl_Temp (DepartmentID) VALUES (@DepartmentID)
    DECLARE @temp_ID INT
    
    DECLARE CUR CURSOR FOR SELECT Department_ID FROM tbl_Department WHERE ParentID = @DepartmentID
    OPEN CUR
    FETCH NEXT FROM CUR INTO @temp_ID
    WHILE @@FETCH_STATUS = 0
        BEGIN
            INSERT INTO @tbl_Temp (DepartmentID) SELECT DepartmentID FROM fn_GetAllChildDepartment(@temp_ID)
            FETCH NEXT FROM CUR INTO @temp_ID
        END
    CLOSE CUR
    DEALLOCATE CUR
--END

--SELECT * FROM @tbl_Temp
RETURN

END

حال تابعي مي نويسم که تمام پدران بک سازمان را در قالب يک رشته که با يک کاراکتر خاص "/" به هم متصل شده اند بازگرداند .
در تابع زير بر خلاف تابع قبل ما از پايين به بالا حرکت مي کنيم يعني از سمت فرزند به سمت پدر .


CREATE FUNCTION [dbo].[Navigation](@ID    INT)
RETURNS NVARCHAR(1000)
AS
BEGIN

DECLARE    @ParentID    INT
DECLARE @Name        NVARCHAR(200)
DECLARE @Result        NVARCHAR(1000)

SELECT @ParentID = ParentID, @Name = Department_Name FROM tbl_Department  WHERE Department_ID = @ID
IF @ParentID <> 0
    SET @Name = @Name + '\' + dbo.Navigation(@ParentID)

RETURN @Name
END

شيوه استفاده از تابع بالا به شکل زير است :


SELECT * ,dbo.Navigation(Department_ID)  FROM tbl_Department WHERE Department_ID = 12

مطالب اين مقاله هم در SQL Server 2000 و هم در 2005  قابل استفاده هستند .
البته SQL Server 2005  امکان جديدي تحت عنوان CTE براي پياده سازي ساختارهاي سلسله مراتبي ارائه نموده است .

و من الله التوفيق