نگاهی به برخی دستورات پر کاربرد در SQL Server
در این مقاله سعی بر آن داریم تا تعدادی از دستورات پرکاربرد و مفید مانند If، Case و... را در روالهای ذخیره شده مورد بررسی قرار دهیم. بسیاری از اوقات استفاده از روالهای ذخیره شده نسبت به نوشتن دستوراتی که درون آن قرار دارد مفیدتر است. زیرا روالهای ذخیره شده از نظر گرامری یکبار برای همیشه بررسی میشوند و هرگاه آنها را فراخوانی کنیم دیگر سربار زمانیای که برای چک کردن گرامر دستورات مورد نیاز است از میان برداشته میشود. همچنین برای استفاده از تراکنشها استفاده از روالهای ذخیره شده گزینهی مناسبی به نظر میرسد.
از اینرو استفاده از آنها در برنامه نویسی بسیار رواج دارد و برای برنامههایی که بر روی شبکه اجرا میشوند استفاده از روالهای ذخیره شده مؤکداً توصیه میشود.
در یک روال ذخیره شده میتوانید از تمامی دستورات SQL، بدون هیچ محدودیتی استفاده کنید. مضاف بر دستورات SQL میتوان از دستوراتی مانند ساختارهای کنترلی IF و CASE، ساختارهای تکرار مانند WHILE و... استفاده کنید.
ممکن است بگویید با توجه به اینکه برای کلیه موارد یاد شده در بالا در زبانهای برنامهنویسی معادلی وجود دارد، چه الزامی است که از آنها در روالهای ذخیره شده استفاده کرد. در جواب باید گفت که استفاده از این دستورات بسیاری از اوقات باعث افرایش کارآیی شده و عمل انتقال میان برنامه و سرور بانک اطلاعاتی را کاهش میدهد.
قبل از شروع به بررسی دستورات مورد نظر، باید توجه داشته باشید که در این مقاله از پایگاه داده Pubs که توسط SQL Server ارائه شده است استفاده میکنیم. همچنین کلیه دستورات در SQL Server 2000 تست شده اند.
IF .. Else
منطق این ساختار برای کلیه برنامه نویسان روشن است. از این ساختار هنگامی استفاده میکنیم که بخواهیم در صورت برقراری شرایطی خاصی دستورات مورد نظر ما اجرا شوند و در صورت عدم برقرار دستورات مربوطه اجرا نشوند.
فرم کلی:
IF condition
BEGIN
Instructions
…
….
END
[ELSE
BEGIN
Instructions
…
….
END]
در قسمت condition میتوانید از یک دستور SELECT و یا مقداری که به عنوان پارامتر برای روال ارسال شده است استفاده نمود. روال زیر را در نظر بگیرید:
CREATE PROCEDURE test AS
IF (SELECT AVG(price) FROM titles WHERE type = 'mod_cook') < $15
BEGIN
PRINT ' mod_cook books are very good'
END
ELSE
PRINT 'Average title price is more than $15.'
GO
در این روال بررسی میانگین price مربوط به نوع mod_cook با عدد 15 دلار پیام لازم را با دستور PRINT چاپ میکنیم. با اجرای روال خروجی زیر را مشاهده میکنید.
mod_cook books are very good
این بدان معناست که در روال فوق میانگین محاسبه شده بیشتر از 15 دلار بوده است. اکنون در این روال اندکی تغییرات اعمال میکنیم.
CREATE PROCEDURE test AS
IF (SELECT AVG(price) FROM titles WHERE type = 'mod_cook') < $15
BEGIN
SELECT titles FROM titles WHERE type = 'mod_cook'
END
ELSE
PRINT 'Average title price is more than $15.'
GO
در این روال میخواهیم اگر میانگین mod_cook بیشتر از 15 دلار باشد، کتابهایِ این نوع را بازیابی و نشان دهد. در غیر اینصورت پیامی مبنی بر کمتر بودن میانگین از 15 دلار چاپ شود. با اجرای روال خروجی زیر بدست میآید.
Title
-----------------------------------
Silicon Valley Gastronomic Treats
The Gourmet Microwave
حال میخواهیم با استفاده از یک پارامتر نحوه بازیابی اطلاعات را کنترل کنیم. در صورتیکه برای پارامتر @Flag مقدار 0 ارسال شود، کتابهایی با نوع mod_cook و در غیر اینصورت کتابهایی با نوع business بازیابی شوند. برای این منظور باید روال فوق را به صورت زیر تغییر داد.
CREATE PROCEDURE test @Flag SMALLINT
AS
IF @Flag=0
SELECT title FROM titles WHERE type = 'mod_cook'
ELSE
SELECT title FROM titles WHERE type = 'business'
GO
با فراخوانی روال به صورت test 2 اطلاعات زیر از جدول titles بازیابی می شوند.
Title
-------------------------------------------------
The Busy Executive's Database Guide
Cooking with Computers: Surreptitious Balance Sheets
You Can Combat Computer Stress!
Straight Talk About Computers
CASE
گاهی وقات مشاهده میشود که در جدوال برای جلوگیری از افزونگی دادهها از خلاصه نویسی استفاده میشود. به عنوان مثال در فیلدی که برای نشان دادن کشور به کار میرود به جای IRAN از IR و یا برای JAPAN از JP استفاده میشود. از عبارت CASE برای تعیین خروجی بر اساس مقدار فیلدها استفاده میشود.
فرم کلی
CASE field name
WHEN expression THEN result
WHEN expression THEN result
…
[ELSE else_result]
END
در روال زیر عبارت برای تعیین نوع دقیق کتابهای موجود در جدول titles استفاده میکنیم.
CREATE PROCEDURE test
AS
SELECT DISTINCT Category =
CASE type
WHEN 'popular_comp' THEN 'Popular Computing'
WHEN 'mod_cook' THEN 'Modern Cooking'
WHEN 'business' THEN 'Business'
WHEN 'psychology' THEN 'Psychology'
WHEN 'trad_cook' THEN 'Traditional Cooking'
ELSE 'Not yet categorized'
END
FROM titles
GO
با اجرای این روال عبارتهای خلاصه شدهای که در ستون type قرار دارند به صورت یک عبارت کامل و مفهوم به خروجی ارسال میشوند. خروجی به صورت زیر خواهد بود:
Category
--------------------------------
Business
Modern Cooking
Not yet categorized
Popular Computing
Psychology
Traditional Cooking
از این عبارت میتوان به منظور درجه بندی نیز استفاده کرد. به عنوان مثال برای درجه بندی نمراتی که از 20 میباشد بر اساس حروف الفبای لاتین. یک جدول جدید از پایگاه داده Pubs ایجاد کنید که فقط یک ستون به نام grades داشته باشد. ادر این ستون اعداد 20، 18، 10، 19، 13، 6 و 12 را وارد کنید. پس از این روال زیر را نیز ایجاد و فراخوانی کنید.
CREATE PROCEDURE test
AS
SELECT grade,Category =
CASE
WHEN grade >0 and grade<10 THEN 'D'
WHEN grade>=10 and grade <13THEN 'C'
WHEN grade>=13 and grade<17 THEN 'B'
WHEN grade>=17 and grade<=20 THEN 'A'
END
FROM [temp]
GO
در این روال بر اساس مقادیری که در ستون grade وجود دارد یک رشته تک حرفی به خروجی ارسال میشود. با اجرای روال خروجی زیر حاصل میشود:
grade Category
----------------------------
20 A
18 A
10 C
19 A
13 B
6 D
12 C
WHILE
از این ساختار برای انجام دستورات تا زمانیکه شرط آن برقرار میباشد استفاده میگردد. استفاده از این ساختار، مانند ساختارهای قبلی بسیار آسان است. در ابتدا برای آشنایی کامل با این ساختار یک مثال ساده را بررسی میکنیم.
CREATE PROCEDURE test
AS
DECLARE @i SMALLINT
SET @i=0
WHILE @i<5
BEGIN
PRINT @i
SET @i = @i + 1
END
GO
این روال مشابه مثالهای برنامه نویسی است. اما بررسی آن خالی از لطف نیست. در ابتدا یک متغیر به نام i تعریف شده است. برای تعریف متغیرها باید از فرم کلی زیر استفاده کنید:
DECLARE @var_name TYPE
در مرحله بعد برای مقداردهی متغیرها باید از کلمه کلیدی SET استفاده کنید. برخلاف زبانهای برنامه نویسی نمیتوان فقط با ذکر نام متغیرها آن را مقداردهی کرد.
پس از مقداردهی اولیه این متغیر از یک حلقه WHILE برای چاپ متغیر i تا هنگامیکه محتوای آن از 5 کمتر است استفاده کردهایم. با فراخوانی این روال خروجی زیر تولید میگردد.
0
1
2
3
4
به عنوان مثالی کاملتر در مورد این حلقه به مثال زیر توجه کنید. در این برنامه دوباره از جدولی که قبلاً ایجاد کردهایم استفاده میکنیم. فقط اطلاعات این جدول را به صورت زیر تغییر دهید. البته نوع ستون grade را به real تغییر دهید.
è 13, 17, 10, 15, 13, 6, 12
میخواهیم یک مدل از عمل زیر نمودار بردن که برای دانشجویان عزیز بسیار آشنا و دوست داشتنی است را انجام دهیم. البته در این مثال عمل فوق بر اساس موازین استاندارد انجام نگیرد اما برای توضیح عملکرد WHILE مناسب است. منطق روال به این صورت است تا هنگامی که نمره بالای کلاس از 19.5 کمتر باشد عمل اضافه کردن 2% هر نمره به خودش ادامه مییابد. توجه کنید که از کلمه تا هنگامیکه استفاده کردهایم. اکثر مواقع استفاده از این کلمه باعث به کارگیری حلقههای تکرار میشود.
CREATE PROCEDURE test
AS
WHILE (SELECT MAX(grade) FROM [temp]) <=19.5
BEGIN
UPDATE [temp] SET grade = grade + (grade * 0.02)
END
SELECT * FROM [temp]
GO
با فراخوانی این روال عمل اضافه کردن نمرات (دستور UPDATE به کار رفته در حلقه) مکرراً اجرا میشود تا یکی از نمرات به بالای 19.5 برسد. خروجی برنامه به صورت زیر است
grade
-----------------------
14.932913
19.527657
11.486856
17.230286
14.932913
6.8921137
13.784227
در انتها شاید خودتان نیز متوجه شده باشید در مورادی که باید مکرراً میان برنامه و سرور پایگاه داده انتقالات انجام دهید، استفاده از ساختارهای فوق سربار این انتقالات را حذف میکند و همچنین کدهای برنامه اصلی زیاد شلوغ نمیشوند.
