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

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

آموزش کامل Recursive Query در Oracle SQL با CONNECT BY و CTE

Recursive Query در Oracle SQL با CONNECT BY و CTE

مقدمه: چرا باید کوئری بازگشتی بلد باشیم؟

فرض کن یک ساختار سازمانی داری که می‌خوای ببینی چه کسی زیرمجموعه‌ی چه کسیه، یا مثلاً بخوای دسته‌بندی محصولات رو به‌صورت درختی نمایش بدی.

این‌جاها دقیقاً جاییه که کوئری بازگشتی (Recursive Query) به کمکت میاد.

در Oracle SQL، دو راه فوق‌العاده برای پیاده‌سازی این مدل‌های سلسله‌مراتبی وجود داره:

  1. روش قدیمی‌تر با CONNECT BY PRIOR
  2. روش مدرن‌تر و قابل کنترل‌تر با WITH ... RECURSIVE CTE

هر دو روش رو با مثال واقعی و اجرایی بررسی می‌کنیم.

اگر می خواهید با قابلیت و امکانات جدید زبان SQL در پایگاه داده اوراکل  بیشتر آشنا بشید نوشته زیر را مطالعه کنید:

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

ساختار داده ما: جدول کارمندان

برای اینکه بتونیم بهتر بفهمیم چطور کوئری بازگشتی کار می‌کنه، بیایم با یک جدول ساده از کارمندان شروع کنیم:

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

و درج چند داده فرضی:

				
					INSERT INTO employees VALUES (1, 'Ali', NULL);     -- Modir
INSERT INTO employees VALUES (2, 'Sara', 1);
INSERT INTO employees VALUES (3, 'Reza', 1);
INSERT INTO employees VALUES (4, 'Zahra', 2);
INSERT INTO employees VALUES (5, 'Omid', 2);
INSERT INTO employees VALUES (6, 'Nima', 4);
COMMIT;

روش اول: استفاده از CONNECT BY PRIOR

این روش مخصوص Oracle هست و از نسخه‌های خیلی قدیمی پشتیبانی می‌شه. کوئری به شکل زیره:

				
					SELECT 
    LEVEL AS level_no,
    emp_id,
    emp_name,
    manager_id
FROM 
    employees
START WITH 
    manager_id IS NULL
CONNECT BY 
    PRIOR emp_id = manager_id;

خروجی نمونه:

level_no emp_id emp_name manager_id
۱ ۱ Ali NULL
۲ ۲ Sara ۱
۳ ۴ Zahra ۲
۴ ۶ Nima ۴
۳ ۵ Omid ۲
۲ ۳ Reza ۱

چرا CONNECT BY مفیده؟

  •  سریع و ساده برای ساختارهای درختی
  • از LEVEL و SYS_CONNECT_BY_PATH برای مسیر استفاده می‌کنه

روش دوم: استفاده از WITH RECURSIVE CTE

اگر Oracle 11g به بالا داری، این روش عالیه. چون می‌تونی راحت‌تر شرط بذاری، فیلدهای دلخواه بسازی و مسیرهای پیچیده‌تر رو تحلیل کنی.

کد کامل:

				
					WITH employee_tree (emp_id, emp_name, manager_id, level_no, path) AS (
    SELECT 
        emp_id, emp_name, manager_id,
        ۱ AS level_no,
        TO_CHAR(emp_id) AS path
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    SELECT 
        e.emp_id, e.emp_name, e.manager_id,
        et.level_no + 1,
        et.path || ' > ' || e.emp_id
    FROM employees e
    JOIN employee_tree et ON e.manager_id = et.emp_id
)
SELECT * FROM employee_tree;

خروجی نمونه:

emp_id emp_name manager_id level_no path
۱ Ali NULL ۱ ۱
۲ Sara ۱ ۲ ۱ > ۲
۴ Zahra ۲ ۳ ۱ > ۲ > ۴
۶ Nima ۴ ۴ ۱ > ۲ > ۴ > ۶
۵ Omid ۲ ۳ ۱ > ۲ > ۵
۳ Reza ۱ ۲ ۱ > ۳

مزایای این روش:

  • خوانایی بالا
  • قابل کنترل با JOINهای دلخواه
  • امکان ساخت مسیر سفارشی (path)
  • مناسب برای کوئری‌های تحلیلی پیچیده

مثال واقعی: زیردستان یک مدیر خاص (مثلاً Sara)

				
					WITH employee_tree (emp_id, emp_name, manager_id) AS (
    SELECT emp_id, emp_name, manager_id
    FROM employees
    WHERE emp_name = 'Sara'
    
    UNION ALL
    
    SELECT e.emp_id, e.emp_name, e.manager_id
    FROM employees e
    JOIN employee_tree et ON e.manager_id = et.emp_id
)
SELECT * FROM employee_tree;

این کوئری تمام افراد زیر نظر «Sara» رو برات برمی‌گردونه.

مقایسه نهایی: CONNECT BY vs CTE

ویژگی CONNECT BY WITH CTE (RECURSIVE)
نسخه مورد نیاز Oracle قدیمی‌تر Oracle 11g+
خوانایی کمتر بیشتر
مسیر سفارشی با تابع خاص ساده‌تر با CONCAT
کنترل شرط‌ها محدودتر انعطاف‌پذیر
مناسب برای تحلیل نه خیلی بله، عالی

سوالات متداول درباره کوئری بازگشتی در اوراکل SQL

CONNECT BY PRIOR روش کلاسیک Oracle برای اجرای کوئری‌های سلسله‌مراتبی است که از نسخه‌های قدیمی‌تر هم پشتیبانی می‌شود.

در مقابل، WITH RECURSIVE CTE یک روش مدرن‌تر است که خوانایی بالاتر، انعطاف‌پذیری بیشتر و قابلیت‌های پیشرفته‌تری مانند تعریف مسیر سفارشی و اعمال شرط‌های پیچیده‌تر را فراهم می‌کند.

اگر از Oracle 11g به بالا استفاده می‌کنید، استفاده از CTE توصیه می‌شود.

برای نمایش مسیر سلسله‌مراتبی در CONNECT BY می‌توان از تابع SYS_CONNECT_BY_PATH استفاده کرد.

اما در CTE، می‌توانید مسیر دلخواه را با الحاق رشته‌ها (string concatenation) بسازید:

در CTE:

et.path || ‘ > ‘ || e.emp_id AS path

این روش در CTE بسیار انعطاف‌پذیرتر است و حتی می‌توان فیلترگذاری بر مسیر انجام داد.

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

کافی است کوئری اولیه (Anchor) را فیلتر کنید:

WITH employee_tree AS (
SELECT * FROM employees WHERE emp_name = ‘Sara’
UNION ALL
SELECT e.* FROM employees e
JOIN employee_tree et ON e.manager_id = et.emp_id
)
SELECT * FROM employee_tree;

این روش به صورت بازگشتی تمام زیردستان مستقیم و غیرمستقیم را بازیابی می‌کند.

بله. CTE بازگشتی در Oracle نه تنها برای بازیابی داده‌های سلسله‌مراتبی، بلکه برای تحلیل‌های عددی نیز مناسب است.

برای مثال می‌توانید در هر مرحله از بازگشت، مقدار عددی را به جمع قبلی اضافه کرده و مجموع نهایی را بسازید.

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

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

اگر دنبال سرعت و سادگی هستی و ساختار درختی‌ات پیچیده نیست، CONNECT BY ابزار خوبیه.

اما اگه می‌خوای تحلیل‌های حرفه‌ای، مسیرهای سفارشی، یا فیلترهای هوشمند اعمال کنی، قطعاً روش CTE بازگشتی (WITH RECURSIVE) انتخاب بهتر و آینده‌دارتریه.

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

سؤالی درباره این مقاله داری؟

اگر نکته‌ای در این مقاله برات مبهم بود یا خواستی بیشتر بدونی، همین حالا برام بنویس تا دقیق و صمیمی پاسخت رو بدم — مثل یه گفت‌وگوی واقعی 💬

برو به صفحه پرسش و پاسخ

میثم راد

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

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

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