CTE بازگشتی و ساختار آن

CTE چیست؟

CTE یک مجموعه نتیجه موقتی دارای نام است که از یک کوئری ساده مشتق شده و می‌تواند در محدوده اجرای یک عبارت SELECT ، INSERT ، UPDATE و یا DELETE تعریف شود.
می‌توان به CTE مانند جایگزینی برای جداول مشتق شده (subquery)، View و توابعی که توسط کاربران تعریف شده، اندیشید. از مهمترین تفاوت‌های CTE با دیگر ساختارها می‌توان به این مورد اشاره کرد که می‌توان آن را به صورت بازگشتی استفاده کرد که این مورد برای ساختارهای درختی بسیار مفید خواهد بود.
از دیگر مزایای آن کپسوله‌سازی کد است. ما می‌توانیم یک روال را در CTE نوشته و چندین بار به آن ارجاع دهیم.

ساختار CTE بازگشتی
این ساختار شبیه ساختارهای بازگشتی در زبان‌های برنامه‌نویسی دیگر است. اگرچه توابع بازگشتی در زبان‌های دیگر فقط یک مقدار عددی را باز می‌گرداند ولی یک CTE بازگشتی می‌تواند چندین سطر را بازگرداند.
هر CTE بازگشتی شامل سه عنصر است :
۱- فراخوانی روال :
اولین فراخوانی CTE بازگشتی شامل یک یا چند CTE_query_deefinition است که توسط عملگرهای UNION ALL ، UNION ، EXCEPT یا INTERSECT به هم ملحق شده‌اند.
چون این تعاریف ساختار پایه‌ای CTE را تشکیل می‌دهند، آن‌ها را  به عنوان اعضای لنگر  (anchor) می‌شناسند. CTE_query_definition ها به عنوان اعضای لنگر در نظر گرفته می‌شوند مگر اینکه به خودشان ارجاع دهند. تعیف تمام اعضای لنگر کوئری باید قبل از تعریف اولین عضو بازگشتی قرار بگیرند و یک عملگر UNION ALL باید برای الحاق آخرین عضو لنگر و اولین عضو بازگشتی قرار گیرد.

۲- فراخوانی بازگشتی روال :
فراخوانی بازگشتی شامل یک یا چند CTE_query_definition می‌باشد که با عملگرهای UNION ALL که به خود CTE ارجاع می‌دهند، به هم ملحق شده‌اند.
این بخش کوئری به عنوان اعضای بازگشتی شناخته می‌شوند.

۳- شرط خاتمه :
این عملیات ، یک عملیات ضمنی (implicit) است. زمانی که هیچ سطری از فراخوانی قبلی باز نگردد، عملیات بازگشت خاتمه می‌یابد.

نکته : استفاده ناصحیح از این روش ممکن است باعث شود که یک چرخه بی‌نهایت تشکیل شود و کوئری ما با مشکل برخورد نماید.
ساختار CTE بازگشتی باید حداقل شامل یک عنصر لنگر و یک عنصر بازگشتی باشد. شبه کد زی با فرض این حداقل‌ها، ساختار اولیه را به ما نشان می‌دهد :

WITH     cte_name    (column_name[,…n])
AS
(
CTE_query_definition
UNION   ALL
CTE_query_definition
)
SELECT  *  FROM cte_name

مراحل اجرای عملیات بازگشتی به شرح زیر است:
۱- عبارت CTE به دو بخش عضو لنگر و عضو بازگشتی تقسیم می‌گردد.
۲- اعضای لنگر اجرا شده و اولین فراخوانی یا مجموعه نتیجه پایه (T(0)) را می‌سازد.
۳- اعضای بازگشتی اجرا می‌شود که T(i) ورودی و T(i+1) خروجی است.
۴- مرحله ۳ تا زمانی که مجموعه‌ای خالی بازگردانده شود تکرار می‌گردد.
۵- مجموعه نتیجه بازگردانده می‌شود. این مجموعه اجتماعی ازT(0) تا T(n) است.