آموزش SQL Server – قسمت چهاردهم

در قسمت سیزدهم آموزش SQL Server ،مبحث  Aggregate Functions ها را به شما آموزش دادیم. دراین قسمت قصد داریم دستورات Case در برنامه SQL را به شما آموزش بدیم.

آموزش SQL Server – قسمت چهاردهم

توجه : برای مشاهده ی تصاویر در سایز اصلی بر روی آنها کلیک کنید.

دستورات Case

در این بخش ما میخواهیم دسنورات Case در برنامه SQL را بررسی کنیم به مثال های زیر توجه کنید :

آموزش SQL Server – قسمت چهارهم

در تصویر فوق همانگونه که مشاهده میکنید دستوراتی نوشته شده که بدین شرح میباشد : در ابتدا دستور Select و سپس نام ستونی که میخواهیم نمایش دهد و سپس کلمه کلیدی Case و در جلو این دستور نیز نام ستونی را مینویسیم که میخواهیم مقادیر ان ستون مورد بررسی قرار گیرد که در اینجا ما ستون ID_Group را نوشته ایم که به ستون ای دی گروه هر کتاب اشاره دارد در ادامه با کلمه کلیدی When مقادیر مورد نظر و عکس العمل برنامه را ذکر میکنیم بطور مثال در جلو اولین When عدد ۱ نوشته شده بدین معنی که هر جا ای دی گروه برابر ۱ بود عبارت جلو Then را قرار بده که در اینجا گفته شده هرجا ۱ بود رشته کامپیوتر را قرار بده و در دو شرط دیگر نیز به همین صورت.
در انتها که اگر هیچکدام از شروط بالا مورد قبول نبود عبارت پس از End اجرا میشود که در این مثال گفته شده اگر ای دی کتاب ۱ و ۲ و ۳ نبود پس جز هیچکدام از گروه های کامپیوتر وزیست و گیاه شناسی نمیباشد پس جز دیگر گروه هاست پس جلو End ما رشته دیگر را در تک کوتیشن(چون یک عبارت رشته ای میباشد) مینویسیم و در اخر هم میتوان یک نام برای ستون ایجاد شده در نظر گرفت که نام هم با کلمه As گفته میشود و در تک کوتیشن.
همچنین باید در انتها تمام کدنویسی ها و شروط گزاشته شده ذکر شود که این ستون ها در چه جدول یا جداولی میباشند که ما نیز در انتها مشخص کرده ایم با عبارت From Tbl_Ketab
پس از اجرا نیز نتیجه مطابق شروط گفته شده خواهیم دید.
حال میتوان این دستورات را به صورت دیگر نیز نوشت با همان نتیجه مانند زیر

Select Name_Ketab,Case
When ID_Group =1 Then 'کامپیوتر'
When ID_Group =2 Then 'زیست'
When ID_Group =3 Then 'گیاه شناسی'
Else 'دیگر'
End As 'Group Type'
From Tbl_Ketab

نتیجه دستورات بالا دقیقا همانند مثال قبلیست اما همانگونه که مشاهده میکنید یک تفاوت در این دستور وجو دارد این است که در جلو Case نام ستون مورد نظرمان را ننوشتیم بلکه هنگام بررسی شرط ستون را به همراه شرط نوشتیم بطور مثال در شرط اول گفته شده اگر ID_Group = 1 بود انگاه عبارت کامپیوتر را قرار ده و دو شرط دیگر نیز بدین ترتیب و بقیه دستورات نیز مانند قبل میباشد و تغییری نکرده است. همچنین میتوان از علامت های > < نیز برای این نوع شرط استفاده کرد برای مثال میتوان نوشت When ID_Group >2 Then ‘computer’ انگاه تمامی کتاب هایی که ای دی گروهشان بزرگتر از ۲ بود را برابر Computer قرار میدهد.
حال میخواهیم مثالی کامل تر و جامع تر شامل Case و جدول موقت و Procedure ها را بررسی کنیم به مثال زیر دقت کنید

Select *,DAY(Zaman_Daryaft) As DayNum
from Tbl_Ketab,Tbl_Amanat
where Tbl_Ketab.Code_Ketab=Tbl_Amanat.Code_Ketab And
Tbl_Amanat.Code_Ketab=23 And MONTH(Zaman_Daryaft)=11

در دستورات فوق از جدول Tbl_Ketab و Tbl_Amant تمامی ستون ها(*) به علاوه تنها روز فیلد Zaman_Daryaft را که ِDayNum نامگذاری شده را با دستور DAY() انتخاب کرده ایم سپس شرطی برای Join کردن دو جدول با فیلد های کلید اصلی و فرعی مشترک در دو جدول(که بارها در اموزش های قبل به طور کامل توضیح داده شده) به علاوه دو شرط دیگر هم ذکر شده که مشخص میکند تنها رکوردی را به ما نمایش بده که فیلد کدکتاب آن در جدول امانت برابر ۲۳ و ماه زمان دریافت نیز برابر۱۱(با استفاده از دستور MONTH که تمامی این دستورات را در اموزش های قبل توضیح داده ایم)
حال با اجرای این دستور نتیجه مورد نظر را به صورت زیر خواهیم دید

آموزش SQL Server – قسمت چهاردهم

همانگونه که در نتیجه ایجاد شده میبینید ما تنها در یک روز از ماه نتیجه را میبینیم حال شاید بخواهیم در روز های دیگر ماه هم مشاهده کنیم که ایا کتابی به امانت رفته یا نه یا اگر به امانت نرفته به ما مقدار ۰ یا یک عبارت رشته ای مشخص نشان دهد برای اینکار باید از جداول موقت استفاده کنیم که شامل یک ستون برای قرار گیری شماره روز هر ماه میباشد پس کدها ها بدین صورت تغییر میدهیم

Create Table #table3
(
DayNum int
)
Declare @c int=1

While(@c<32)
Begin
Insert #table3
Select @c
Set @c +=1
End

Select * from #table3 Left outer Join
(
Select Tbl_Amanat.*,DAY(Zaman_Daryaft) As DayNum
from Tbl_Ketab,Tbl_Amanat
where Tbl_Ketab.Code_Ketab=Tbl_Amanat.Code_Ketab And
Tbl_Amanat.Code_Ketab=23 And MONTH(Zaman_Daryaft)=11
) AS Temp
On #table3.DayNum=temp.DayNum
Drop Table #table3

دستورات بالا برای نشان دادن تمامی روز های ماه میباشد که اگر در ان روز ها اگر کتابی به امانت رفته نشان میدهد در غیر این صورت مقدار NULL را نمایش میدهد حال برای توضیحات دستورات بالا میتوان گفت در ابتدا که ما با دستور Create Table جدول موقتی با نام Table3 ساخته ایم که تنها شامل یک ستون با نام DayNum میباشد حال برای مقدار دهی به ستون جدول دو راه داریم یکی که مانند مثالی در اموزش قبل مقادیر را یکی یکی تایپ کرده و وارد ستون جدول موقتمان میکنیم که بدین صورت میباشد

insert #Table 3 values (1),(2),(3), … ,(۳۱)

که این روش بسیار وقتگیر و زمان بر میباشد پس از روش دوم که در دستورات بالا نیز امده استفاد بکنیم که باید از حلقه While() استفاده کنیم که ابتدا یک متغیر از جنس int با نام c و مقدار ۱ تعریف کرده ایم به عنوان کانتر یا شمارشگر سپس در قسمت شرط حلقه گفته شده تا زمانی این حلقه ادامه داشته باشد که مقدار متغیر ما از ۳۲ کمتر باشد یعنی از ۱ تا ۳۱ بار این حلقه اجرا میشود و در دستوراتش هم گفته شده که در هر بار اجرا مقدار متغیر درون جدول Insert یا درج شود سپس یک واحد به متغیر اضافه کند. و در اخر کدها نیز همانگونه که مشاهده میکنید جدول را Drop کرده ایم
پس از اتمام حلقه حال درون جدول موقت ما ۳۱ خانه با مقادیر ۱تا۳۱ وجود دارد پس باید یک ارتباط یا join بین جدول موقت و کوری ایجاد کنیم پی طبق اموزش های قبل برای ایجاد ارتباط بین جدول موقت و کوری باید کوری را نیز به حالتی تبدیل کنیم که رفتار یک جدول را داشته باشد پس ابتدا یک دستور Select و سپس * که یعنی تمامی ستون ها را انتخاب کن از جدول موقت و سپس عبارت Left Outer Join (در اموزش های قبلی مفصل توضیح داده شده) و سپس کوری که برای اینکه بتواند با جدول موقت ارتباط برقرار کند انرا در پرانتز قرار داده ایم و در انتها نیز نامی برایش در نظر گرفته ایم و در انتها ارتباط برقرار کردن نیز فیلد های مشترک هر دو طرف را با On برابر هم میکنیم.
حال پس از اجرا نتیجه زیر را مشاهده میکنیم

آموزش SQL Server – قسمت چهاردهم

همانگونه که مشاهده میکنید نتیجه دقیقا مطابق دستورات گفته شده میباشد که ۳۱ خانه دارد و فقط در روزی که کتابی به امانت رفته مقادیری داریم ولی در بقیه خانه ها مقدار NULL داریم حال اگر بخواهیم نمای دیگری داشته باشیم که فقط ستون های روز را با وضعیت ان روز را نشان دهد منظور از وضعیت این است که اگر کتابی به امانت رفته مقدار Rent و اگر در ان روز کتابی به امانت نرفته مقدار NoRent را بجای NULL نمایش دهد پس کدهارا بدین صورت تغییر خواهیم داد

Create Table #table3
(
DayNum int
)
Declare @c int=1

While(@c<32)
Begin
Insert #table3
Select @c
Set @c +=1
End
Select #table3.DayNum,Case
When Temp.DayNum is Null Then 'NoRent'
Else 'Rent'
End As Rent
from #table3 Left outer Join
(
Select Tbl_Amanat.*,DAY(Zaman_Daryaft) As DayNum
from Tbl_Ketab,Tbl_Amanat
where Tbl_Ketab.Code_Ketab=Tbl_Amanat.Code_Ketab And
Tbl_Amanat.Code_Ketab=23 And MONTH(Zaman_Daryaft)=11
) AS Temp
On #table3.DayNum=Temp.DayNum
Drop Table #table3

در دستورات فوق تنها تغییری که داده ایم این است که یک دستور Case که اتفاقا بحث اموزشی این قسمت نیز بود اضافه کرده ایم در Case ایجاد شده گفته شده مقدار فیلد DayNum که در کوری ایجاد کرده ایم و ان را تبدیل به جدول Temp کرده ایم اگر مقدار NULL داشت عبارت NoRent در غیر این صورت عبارت Rent را قرار ده بدین معنی که در ان روز کتابی به امانت گرفته شده نتیجه نیز به صورت زیر میباشد

آموزش SQL Server – قسمت چهاردهم

حال میتوان برای کارامد کردن این دستورات انهارا درون یک پروسیجر قرار داد تا مقدار ورودی کد کتاب و ماه را از کاربر در ورودی بگیریم پس کدهارا بدین صورت تغییر میدهیم

Create Procedure Test4(@code int , @month int)
AS
Begin
Create Table #table3
(
DayNum int
)
Declare @c int=1

While(@c<32)
Begin
Insert #table3
Select @c
Set @c +=1
End
Select #table3.DayNum,Case
When Temp.DayNum is Null Then 'NoRent'
Else 'Rent'
End As Rent
from #table3 Left outer Join
(
Select Tbl_Amanat.*,DAY(Zaman_Daryaft) As DayNum
from Tbl_Ketab,Tbl_Amanat
where Tbl_Ketab.Code_Ketab=Tbl_Amanat.Code_Ketab And
Tbl_Amanat.Code_Ketab=@code And MONTH(Zaman_Daryaft)=@month
) AS Temp
On #table3.DayNum=Temp.DayNum
Drop Table #table3
End

در دستورات فوق ما تنها با دستورات کلیدی ساخت پروسیجر یک پروسیجر با نام Test4 به همراه ۲ ورودی @code برای کد کتاب و @month برای ماه و سپس در شرط کوری هم بجای اعداد ثابت ۱۱ و ۲۳ این متغیر هارا قرار میدهیم حال این دستورات را با دکمه Execute اجرا میکنیم تا نتیجه Command(s) completed successfully. را به ما بدهد سپس در مسیر ذخیره سازی پروسیجر که بدین شرح میباشد Databases/Library/Programmability/Stored Procedure رفته و با کلیک راست کرده به روی پروسیجر ایجاد شده و کلیک کردن گزینه Execute Stored Procedure.. انرا به صورت زیر اجرا میکنیم

آموزش SQL Server – قسمت چهاردهم

پس از انتخاب گزینه گفته شده این پنجره باز میشود و سپس باید در قسمت Value در مقابل هر متغیر مقدار مربوط به انرا وارد میکنیم و سپس Ok سپس نتیجه دقیقا مشابه با بالا را خواهیم داشت.
این هم روش دیگری برای اجرای پروسیجر ها میباشد.

مدرس : خانم مهندس داودی نژاد

آموزش SQL Server – قسمت سیزدهم

آموزش SQL Server – قسمت پانزدهم