مقدمه :
در اين مقاله به يکي از روشهاي پياده سازي ساختارهاي درختي يا سلسله مراتبي در پايگاه داده هاي رابطه اي مي پردازم
در تحليل برخي از سيستم ها و مسائل با پايگاه داده هاي سلسله مراتبي مواجه هستيم ، به اين معني که بين برخي از موجوديت هاي سيستم رابطه پدر فرزندي وجود دارد و در واقع با يک ساختار درختي مواجهيم .
به عنوان مثال رابطه بين بخش هاي مختلف يک سازمان (چارت سازماني) ، رابطه پيش نيازي بين دروس ، رابطه زير شاخه يا زير مجموعه بودن بين موجوديت ها و ...
پياده کردن اين نوع روابط در پايگاه داده رابطه اي کار آساني نيست . به عنوان مثال پيدا کردن تمام بخشهاي زير مجموعه يک سازمان يا يافتن تمام پيش نيازهاي يک درس و ...
در اين موارد مي توانيم از توابع بازگشتي استفاده کنيم .
براي درک بهتر مطلب به بررسي مسئله چارت سازماني مي پردازيم .
سازمانها معمولا داراي يک سلسله مراتب سازماني يا همان چارت سازماني هستند به اين معنا که هر بخش آن سازمان زير مجموعه ( فرزند ) يک بخش ديگر سازمان است و بخش هاي ديگر سازمان ممکن است زير مجموعه اين بخش باشند ( اين بخش پدر بخش هاي ديگري باشد ) .
حال ما تابعي مي نويسيم که تمام بخش هاي زير مجموعه يک بخش را برگرداند .
اين تابع به اين صورت عمل مي کند که ابتدا تمام بخش هايي که ورودي تابع پدر آنها ست را انتخاب مي کند و براي هر يک از آنها دوباره خودش (بازگشتي) را اين بار با 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 براي پياده سازي ساختارهاي سلسله مراتبي ارائه نموده است .
| 
12/08/2010 13:25 
محمد زنگنه
