ESC را فشار دهید تا بسته شود

زمیوس آموزش، یادگیری و سرگرمی

آموزش جامع تحلیل داده‌های سلسله‌مراتبی در Oracle با SYS_CONNECT_BY_PATH

SYS_CONNECT_BY_PATH

مقدمه: SYS_CONNECT_BY_PATH در اوراکل چه کاربردی دارد؟

آیا تا به حال نیاز داشتید داده‌هایی را نمایش دهید که ساختار درختی دارند؟ مثلاً ساختار سازمانی یک شرکت یا دسته‌بندی محصولات یک فروشگاه آنلاین؟ اگر بله، تابع قدرتمند SYS_CONNECT_BY_PATH در Oracle دقیقاً چیزی است که به آن نیاز دارید.

در این مقاله اوراکل SQL، قصد داریم قدم به قدم با مفهوم داده‌های سلسله‌مراتبی در Oracle آشنا شویم و ببینیم چطور می‌توان با استفاده از SYS_CONNECT_BY_PATH آن‌ها را تحلیل و گزارش‌گیری کرد.

اگر می خواهید با قابلیت و کوئری بازگشتی (Recursive Query)  در پایگاه داده اوراکل  بیشتر آشنا بشید نوشته زیر را مطالعه کنید:

در این نوشته شما می خوانید

داده‌های سلسله‌مراتبی چی هستند؟

داده‌های سلسله‌مراتبی (Hierarchical Data) نوعی از داده‌ها هستند که در آن هر رکورد می‌تواند یک والد (Parent) و چندین فرزند (Child) داشته باشد. این نوع ساختار برای مدل‌سازی مواردی مانند:

  • ساختار اداری یک سازمان
  • درخت دسته‌بندی محصولات
  • سیستم فایل (Folders/Subfolders)

بسیار کاربرد دارد.

در Oracle، با استفاده از ساختار CONNECT BY PRIOR می‌توان چنین داده‌هایی را به سادگی پیمایش کرد.

تابع SYS_CONNECT_BY_PATH چیست؟

تابع SYS_CONNECT_BY_PATH(column, delimiter) یک رشته متنی باز می‌گرداند که مسیر کامل یک رکورد از ریشه تا خود آن رکورد را نمایش می‌دهد.

این تابع برای ساخت breadcrumb‌ها، مسیر دسترسی یا نمایش درختی ایده‌آل است.

مثال واقعی: ساختار سازمانی

بیایید یک ساختار سازمانی ساده بسازیم تا ببینیم چطور این تابع کار می‌کند.

۱. ساخت جدول

				
					CREATE TABLE employees (
    emp_id     NUMBER PRIMARY KEY,
    emp_name   VARCHAR2(100),
    manager_id NUMBER
);

۲. درج داده‌ها

				
					INSERT INTO employees VALUES (1, 'Ali',    NULL);
INSERT INTO employees VALUES (2, 'Sara',   1);
INSERT INTO employees VALUES (3, 'Reza',   1);
INSERT INTO employees VALUES (4, 'Niloofar', 2);
INSERT INTO employees VALUES (5, 'Amir',   2);
INSERT INTO employees VALUES (6, 'Kaveh',  3);
COMMIT;

۳. نمایش مسیر هر کارمند از مدیر کل

				
					SELECT 
    emp_id,
    emp_name,
    manager_id,
    LEVEL AS tree_level,
    SYS_CONNECT_BY_PATH(emp_name, ' -> ') AS path
FROM 
    employees
START WITH 
    manager_id IS NULL
CONNECT BY 
    PRIOR emp_id = manager_id;

خروجی نمونه:

شناسه نام کارمند شناسه مدیر سطح مسیر
۱ Ali - ۱ → Ali
۲ Sara ۱ ۲ → Ali → Sara
۴ Niloofar ۲ ۳ → Ali → Sara → Niloofar
۵ Amir ۲ ۳ → Ali → Sara → Amir
۳ Reza ۱ ۲ → Ali → Reza
۶ Kaveh ۳ ۳ → Ali → Reza → Kaveh

نکات مهم برای تحلیل بهتر

✅ مرتب‌سازی فرزندان در درخت با ORDER SIBLINGS BY

				
					ORDER SIBLINGS BY emp_name

این عبارت به شما کمک می‌کند که فرزندان یک والد به ترتیب خاصی نمایش داده شوند.

✅ پیدا کردن فقط برگ‌ها (Leaf nodes)

برای یافتن گره‌هایی که فرزندی ندارند:

				
					WHERE emp_id NOT IN (
    SELECT DISTINCT manager_id FROM employees WHERE manager_id
    IS NOT NULL
)

کاربردهای واقعی SYS_CONNECT_BY_PATH

  • نمایش درختی ساختار شرکت‌ها
  • ساخت breadcrumb UI برای دسته‌بندی‌ها
  • گزارش مسیر کالا از دسته‌ی اصلی تا زیرشاخه
  • شناسایی ساختارهای غیرنرمال در سلسله‌مراتب

مزایا و هشدارها

مزایا:

✔️ سادگی استفاده
✔️ عدم نیاز به کدهای پیچیده
✔️ نمایش مسیر کامل با یک تابع

هشدارها:

⚠️ از حلقه‌های والد-فرزندی (Circular Reference) دوری کنید
⚠️ بررسی کنید که داده‌ها ساختار منطقی داشته باشند

سوالات متداول درباره SYS_CONNECT_BY_PATH در اوراکل SQL

عبارت CONNECT BY PRIOR یکی از ویژگی‌های سنتی Oracle برای پیمایش سلسله‌مراتب درون یک جدول است.

این روش ساده، قدرتمند و سریع است و نیازی به تعریف مکرر ندارد.
در مقابل، WITH RECURSIVE که از Oracle 11g به بعد (با محدودیت‌هایی) و به‌طور کامل در Oracle 12c معرفی شد، ساختاری مشابه CTE در سایر پایگاه‌های داده مانند PostgreSQL دارد و انعطاف‌پذیری بیشتری برای ساختارهای پیچیده‌تر دارد، ولی نیاز به نگارش بیشتری دارد.

تابع SYS_CONNECT_BY_PATH(column, delimiter) مسیر یک رکورد از ریشه (پدر بالادستی) تا خودش را به صورت رشته‌ای برمی‌گرداند.

این تابع برای نمایش مسیر کامل در داده‌های درختی (مثل breadcrumb یا ساختار درختی شرکت) استفاده می‌شود.

مثلاً خروجی:

-> مدیر کل -> معاون -> سرپرست -> کارمند

برای نمایش فقط Leaf Nodes (یعنی گره‌هایی که فرزندی ندارند) می‌توانید از کوئری زیر استفاده کنید:

SELECT *
FROM employees
WHERE emp_id NOT IN (
SELECT DISTINCT manager_id
FROM employees
WHERE manager_id IS NOT NULL
)
START WITH manager_id IS NULL
CONNECT BY PRIOR emp_id = manager_id;

این روش فقط گره‌هایی را نشان می‌دهد که خودشان مدیر نیستند.

برای مرتب‌سازی بچه‌های هر گره در کوئری سلسله‌مراتبی Oracle، از عبارت ORDER SIBLINGS BY استفاده کنید:

SELECT emp_name,
SYS_CONNECT_BY_PATH(emp_name, ‘ > ‘) AS path
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR emp_id = manager_id
ORDER SIBLINGS BY emp_name;

این عبارت ترتیب نمایش فرزندان هر مدیر را بر اساس نام یا هر ستون دیگری مشخص می‌کند.

جمع‌بندی: چرا باید از این قابلیت‌ها استفاده کنیم؟

تابع SYS_CONNECT_BY_PATH یکی از توابع کلیدی برای تحلیل داده‌های درختی در Oracle است.

با چند خط SQL ساده می‌توانید مسیر کامل هر رکورد را از ریشه تا برگ ببینید. این ابزار برای مدیران بانک اطلاعاتی، توسعه‌دهندگان، تحلیل‌گران و حتی طراحان UI کاربرد فراوانی دارد.

اگر با داده‌های سلسله‌مراتبی کار می‌کنید، تسلط بر این تابع می‌تواند کار شما را چند برابر سریع‌تر و هوشمندانه‌تر کند.

📢 نظر شما چیست؟ اگر شما هم اطلاعات و تجربه خوبی در مورد فانکشن  SYS_CONNECT_BY_PATH  در اوراکل SQL دارید خوشحال میشم در بخش نظرات، تجربه های ارزشمندتان را با ما به اشتراک بگذارید! 🚀

میثم راد

من یه برنامه نویسم که حسابی با دیتابیس اوراکل رفیقم! از اونایی ام که تا چیزی رو کامل نفهمم،ول کن نیستم، یادگرفتن برام مثل بازیه، و نوشتن اینجا کمک می کنه تا چیزایی که یاد گرفتم رو با بقیه به شریک بشم، با هم پیشرفت کنیم.

دیدگاهتان را بنویسید

نشانی ایمیل شما منتشر نخواهد شد. بخش‌های موردنیاز علامت‌گذاری شده‌اند *