12 fevral 2020 - 04:00

CTE(Common table expression)

Azərbaycan dilində ümumi cədvəl ifadəsi kimi tərcümə olunur. SQL Server 2008-dən başlayaraq tətbiq olunmağa başlanmışdır. Bəzən mürəkkəb sorğuların yazılması zamanı verilənlərin müvəqqəti yadda saxlanılması üçün cədvələ ehityac duyulur. Bunun üçün “temporary table” anlayışından istifadə edərkən onu ilk öncə yaratmaq, sonra sorğunun nəticəsini həmin cədvələ insert etmək, sonra isə həmin cədvəli silməyə ehtiyac yarana bilər. Yaxud cədvəl tipli dəyişən anlayışından istifadə oluna bilər. Bu proseslərin hər ikisi zamanı cədvələ sorğunun nəticəsini insert etmək lazım gələcək. Lakin CTE istifadə edən zaman buna lüzum yoxdur. Select,insert,update,delete,create view əmrləri üçün istifadə etmək mümkündür. CTE bir obyekt kimi yadda saxlanılmır və yalnız sorğunun icra müddətində istifadə olunur. Bundan əlavə CTE öz – özünə müraciət edə bilir və eyni zamanda bir sorğu zamanı bir neçə dəfə öz -özünə müraciət edə bilər. Digər tərəfdən sorğuda CTE-in istifadə olunması skriptin daha rahat oxunaqlığını təmin edir.

CTE-in strukturu

Nümunə:Belə bir sorğumuz var

Sorğunun nəticəsinidə sərnişinlər və onların təyyarədə əyləşdiyi oturacaqlar göstərilir.

Biz eyni oturacaqda əyləşən sərnişinlərin  qarşılaşmasını çıxardaq.

Bundan əlavə bir neçə CTE bir sorğuda istifadə oluna bilər. Bir neçə CTE-nin istifadə olunmasının strukturu aşağıdakı kimidir.

Nəzərinizə çatdırım ki eyni anda bir neçə cte-dən istifadə etdikdə yalnız ilk CTE ifadəsinin adının  əvvəlində “with” açar sözü işlənir . Yuxarıdakı sorğunun nəticəsinidə eyni oturacaqda əyləşmiş sərnişinlərin siyahısını göstərdik. Qarşımıza belə bir məsələ qoyulub ki, iki və daha artıq müxtəlif sərnişinlərlə eyni oturacaqda əyləşmiş ( yerin iki sərnişin qarşılaşmasında eyni olmağı mütləqdir, digər qarşılaşma üçün isə eyni olmağı mütləq deyil. Yəni ki A və B sərnişini d oturacağında, B və C sərnişini isə f oturacağında oturmuşsa onda sorğumuzun nəticəsində B sərnişini göstərilməlidir ) sərnişinlərin siyahısın çıxardaq. Aşağıdakı sorğuya nəzər yetirək.

Qeyd:

bu skripti sorğumuzun nəticəsinin boş qayıtmaması üçün əlavə etdim.

Gördüyünüz kimi biz burda birdən daha artıq CTE ifadəsindən istifadə etdik. Sorğunu çalışdırdıqda aşağıdakı nəticəni alırıq.

Rekursiya

Müasir VBİS əksəriyyəti – münasibətlidir (relatioonal), yəni verilənlər cədvəllər və sətirlər şəklində təsvir olunub. Buna ikiölçülü təsvir deyilir. Lakin praktikada biz verilənlərin digər təşkilat növləri ilə də rast gəlirik, o cümlədən iyerarxik.

Kompüterimizdəki faylların siyahısına baxsaq onlar ağacvari şəkildə təşkil olnunasını görərik. Digər bir nümunə üçün kitabxananadakı kitabları göstərmək olar. Kitabxana- zal – Şkaf- rəf- kitab. Belə nümünələr çox gətirmək olar. Biz yuxarıdakı təsvirimiz üçün ayrı-ayrı bir neçə cədvəl yarada bilərik. Kitabxana cədvəli, zallar cədvələ, şkaflar cədvələ və s. Lakin bu iyerarxiyanı dərilnliyini biz dəqiq bilməsək yeganə çıxış yolu rekursiyadır.

Problem ondadır ki, iyerarxik şəklində təşkil olunmuş verilənlər relational verilənlər modelinə uyğun gəlmir. SQL -1999 stabdartında rekursiv sorğular dəstəklənməyə başlandı. Oracle VBİS –də bunlar bir qədər əvvəl 8-ci versiyasında istifadə edilməyə başlandı. MS SqL serverdə bir qədər gec SQL 2005 ci versiyasından başlayaraq istifadə edildi. Buna görə də terminologiya və sintaksisdə bir qədər fərqlər var. Məsələn Oracle da bunun adı iyerarxik sorğular gedir, digər VBİS-lərdə isə rekursiv sorğular lakin hər ikisi eyni şeydir.

Cədvəllərdə belə verilənlərin saxlanılması iki üsulu var

1. Ən çox istifadə olunan üsul. Hər bir cədvəl üçün əcdad obyektləri təyin etmək üçün əlavə sütun yaradılır. Beləliklə biz həmişə əcdad obyektləri asanlıqla təyin edə bilərik.
2. SQL Server 2008 –ci versiyada verilənləri ağacvari iyerarxiya şəklində saxlamaq üçün yeni verilənlər tipi hierarchyid tipi yaradılmışdır.

Yuxarıda sadaladığımız hallardan başqa CTE-ni rekursiv proseslər zamanı istifadə etmək olar. Tutaq ki, bizə son 45 günün siyahısını çıxartmaq lazımdır( bu gün də daxil olmaqla) . Təbii ki, alqoritm olaraq biz 44 gün əvvəli götürüb üzərinə hər dəfəsində 1 gün gəlməliyik. Bunu CTE ifadəsi ilə aşağıdakı şəkildə yazmaq olar (CTE-nin bu şəkildə yazılması bizi skriptdə “while” və ya “cursor” istifadə etməkdən azad edə bilər)

Sorğu necə çalışır? İlkin olaraq select  dateadd(dd,-44,cast(getdate() as date)) date_ bu skiripti işlətməklə 44 gün əvvəlki tarixi alırıq. Daha sonra isə

Union all

select dateadd(dd,1,date_) d from cte

skriptini çalışdırmaqla ilk tarix günündən başlayaraq hər dəfəsində yeni tarixin üzərinə 1 gün əlavə edəcək. Lakin burda bir detala diqqət yetirmək lazımdır – rekursiya nə vaxt tamamlanacaq. Bunun üçün isə where hissəsində şərtimizi göstəririk.

Bu çox sadə bir nümunə idi. Siz də özünüzdə olan rekursiya proseslərini bu şəkildə yaza bilərsiz.

CTE –dən istifadə edərkən aşağıdakı halları nəzərə almaq lazımdır.

• CTE ifadənin daxilində hər bir atributa (sütuna) ad verilməlidir. Əgər ad verməmisinizsə aşağıdakı xətanı alacaqsız.

• CTE ifadədən dərhal sonra ona müraciət olunmalıdır. Dərhal sonra müraciət olunmazsa aşağıdakı xətanı alacaqsız

• CTE ifadəyə iki və daha artıq ayrı ayrı sorğuda müraciət edə bilərsiniz lakin, nəticə olaraq yalnız ilkin müraciətin nəticəsini qaytaracaq. Digər müraciətlər isə xəta ilə nəticələnəcək.

• CTE-ni rekursiv məqsədlərlə istifadə etdikdə mütləq limit qoyulmalıdır. Yuxarıda istifadə etdiyimiz sorğuda limit qoymadıqda aşağıdakı xətanı almış oluruq. Susmaya görə limit qoyulmadıqda rekursiya sayı 100 götürülür.

Şəkil 8

 

100 dəfə müraciət etməklə nəticəni qaytarır (Şəkil 7) və xətanı (Şəkil 8) çıxardır. Rekursiyanın sayını manual olaraq təyin etmək mümkündür. Bunun üçün MAXRECURSION açar sözündən istifadə olunur. Nümunə üçün aşağıdakı skripti göstərmək olar.

Burada rekursiyanı 0 seçməklə rekursiyaların sayının limitsiz təyin edirik. Onu da qeyd edim ki, maksimum  rekursiyanın sayını 32767 təyin etmək olar.  Bu zaman xəta çıxsa da sorğunun nəticəsini göstərəcək. Əgər rekursiya sayını 32768 etsək  yalnız aşağıdakı xətanı alacağıq .

Sual oluna bilər ki, biz rekursiyanı sıfır təyin etməklə həmin sorğunu dövrə (loop-a) salmış oluruqsa, niyə 0 anlayışı mövcud olsun. Cavab olaraq onu deyim ki, bizim elə proseslərimiz ola bilər ki 32767 dəfədən çox rekursiya gedə bilər. O zaman biz where hissəsində şərtimizi təyin edərək və rekursiyanı 0 seçərək istədiyimiz nəticəni xətasız əldə edə bilərik.

Bu məqaləmdə CTE haqqında bildiklərimi sizinlə bölüşdüm. Oxuduğunuz üçün təşəkkürlər.

Müəllif : Natiq Rzazadə