نگاهی به برخی دستورات پر کاربرد در  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

 

 

در انتها شاید خودتان نیز متوجه شده باشید در مورادی که باید مکرراً میان برنامه و سرور پایگاه داده انتقالات انجام دهید، استفاده از ساختارهای فوق سربار این انتقالات را حذف می­کند و همچنین کدهای برنامه اصلی زیاد شلوغ نمی­شوند.

نظرات 0 + ارسال نظر
برای نمایش آواتار خود در این وبلاگ در سایت Gravatar.com ثبت نام کنید. (راهنما)
ایمیل شما بعد از ثبت نمایش داده نخواهد شد