آموزش With clause

یکی از بزرگترین مشکلاتی که در اجرای دستور select با آن مواجه هستیم مربوط به دفعات مراجعه به دیسک به منظور واکشی داده های ذخیره شده است (I/O)
هر چقدر بتوانیم دفعات مراجعه را کاهش داده یا سرعت مراجعه و واکشی داده را افزایش دهیم دستور select عملکرد بهتری خواهد داشت

یکی از راهکارهای کاهش تعداد دفعات مراجعه به دیسک استفاده از with clause است که در واقع داده های مورد نیاز را از دیسک واکشی کرده و آنها را در جدول موقتی که به ازای محیط کاری جاری (session) قابل استفاده است نگهداری میکند

کاربرد این دستور مواقعی است که میخواهیم در یک دستور select چندین بار از داده ای یکسان به اشکال مختلف استفاده کنیم (reuse) و نحوه نگارش آن به صورت زیر می باشد

with tbl_name as
(select … from tbl_a
where …
group by …
Having … )
select field1,field2 , ….
from tbl_name
union all
select field1,dield2, min(..) , max(…), …
from tbl_name
group by field1,dield2

نکته1: میتوانید چندین بار از خروجی دستور select ی که در with قرار داده اید به اشکال مختلف استفاده کنید به عنوان مثال میتوانید خروجی tbl_name را با خودش join کنید و همچنین می توانید از تمامی قابلیت‌های دستور استفاده کنید
نکته2: میتوانید از چندین جدول در دستور with استفاده کنید

with
tbl_name1 as
(select * from tbl_a) , tbl_name2 as (select * from tbl_name1 where …)
select * from
tbl_name1 inner join tbl_name2
on ….
where …
group by …

نکته3: همانطور که مشاهده میکنید هر کدام از پرس و جوهای استفاده شده میتواند بصورت جداگانه دارای بخش هایی از قبیل

where
group by
having
…..

باشند ، توجه داشته باشید که هر کدام از این بخش ها قبل از اجرای پرس و جوی اصلی انجام خواهند شد

در ادامه به ارائه و توضیح مثالی کاربردی از دستور with خواهم پرداخت

with tbl_dep_sal as
(select nvl(to_char(e.department_id), ‘—‘) department_id,
sum(e.salary) salary
from employees e
group by e.department_id)

select department_id, salary
from tbl_dep_sal
union all
select ‘جمع سازمانهای 20 ، 50 ، 80′, sum(salary)
from tbl_dep_sal c
where c.department_id in (’20’, ’50’, ’80’)
union all
select ‘جمع کل’, sum(salary)
from tbl_dep_sal

order by 1

 در select ی که در بخش with نوشته شده است ابتدا از جدول کارمندان حقوق به تفکیک هر سازمان بدست آمده است (group by)
 در ادامه و در select اول تمامی اطلاعات خروحی with بدست استخراج شده است
 سپس در select دوم مجموع حقوق کارمندان شاغل در سازمانهای 20 و 50 و 80 استخراج شده است
 و در select سوم مجموع حقوق کارمندان تمامی سازمانها استخراج شده است
 و در انتها داده های استخراج شده توسط سه دستور select با استفاده از دستور union all با هم ترکیب شده و نمایش داده شده است

برای مشاهده کامل مطلب کلیک کنید