مثال3 :شماره دانشجويي وسن دانشجوياني که سال تولد آنها بزرگ تر از سال 1986 است:

SELECT  s#,datediff (yy,getdate( ),birthdate)  FROM  STD     WHERE  Datepart(yy,birthdate)>1986

توابع تعريف شده توسط کاربر

User Defined Function

    يکي از ويژگي هاي SQL- Server  امکان تعريف توابع جديدتوسط کاربر ( (UDF مي باشدکه با استفاده از اين امکان ، مي توان عمليات خاص مورد نياز هربرنامه کاربردي را فقط يک باربه صورت يک تابع نوشت ودرموارد لازم،آن تابع را  فراخواند تا عمليات مورد نظر انجام شود.  

در ساختار آن مي توان از توابع سيستمي ،توابع تعريف شده توسط کاربر، همچنين دستورات  T-SQLو رويه هاي ذخيره شده استفاده کرد. قبل از بررسي نحوه تعريف و به کار گيري اين توابع به توضيح بعضي از ساختارهاي دستوري پرکاربرد T-sql مي پردازيم .

تعريف متغيرها 

مانند اکثر زبان ها ي برنامه نويسي در T-sqlنيز متغير ها بعد از تعريف و تعيين نوع قابل استفاده مي باشند . 

متغير ها در دو نوع   قابل دسته بندي هستند :

متغيرهاي عمومي : اين متغير ها با @@شروع مي شوند .تمامي اين متغير ها توسط Sql-serverتعريف و مقداردهي مي شوند. مثلا @@VERSIONکه نسخه ونوع پردازنده مورد استفاده سرور رانمايش مي دهد.از ديگر متغير هاي مهم اين گروه @@IDENTITYو@@ROWCOUNTهستند که به ترتيب آخرين مقدار IDENTITY اختصاص داده شده توسط سروروتعداد سطرهايي که تحت تاثير آخرين دستور قرا ر گرفته اند را نمايش مي دهند .

1- متغير هاي محلي:اين متغير ها با @ شروع مي شوند . 

نحوه تعريف :

DECLARE  @VARNAME DATA_TYPE|TABLE

                         ({TABLE-DEFINITION})       

دقت کنيد که نوع داده مي تواند  TABLEهم باشد.

مثال 4:

 DECLARE @citytable (cityname CHAR(16),citycode int)

نحوه مقداردهي: براي مقدار دهي يک متغير محلي روشهاي مختلفي وجود دارد از جمله:

       مثال 5 : استفاده از SETبراي مقدار دهي : 

SET @cityName='Isfahan'                                                        

       مثال 6: استفاده از SELECTبراي مقدار دهي:                      

  SELECT @myGpa=Gpa

               From STD where s#='4351'     

ياSELECT @myTable=Name,Family FROM STD                                                                                  

متغير@myTableراکه قبلا از نوع جدول تعريف شده را با نام ونام خانوادگي دانشجويان جدول  STDپر مي کند .

دستورات اجرايي

براي کنترل اجرا ي برنامه مي توانيد از ساختار هاي if—else و goto  استفاده کنيد.

براي بلوک بندي نيز T-sqlساختار  BEGIN---ENDرا در اختيار برنامه نويسان قرار داده است.

مثال7: 

   IF  exists(set @Name=select Name from STD

                                              Where s#=4585)

                        Select 'The studentname is:',@Name

 Else  

                     Print 'no students found'

براي حلقه سازي نيز مي توانيد از ساختار WHILEبا CONTINUE و BREAKاستفاده کنيد.

مثال8:

Declare @num1 smallint

Declare @num2 smallint

Set @num1=12

Set @num2=13

While(@num2!=20)

begin

Select name,family,'has got the max grade between',@num1,'and',@num2  

from std

Where s# in(select max(s#) from reg where (grade between @num1 and @num2))

set @num1=@num1+1

set @num2=@num2+1

end

ازديگر ساختار هاي پر کاربرد در نوشتن توابع جداول موقتي هستند که در آزمايش اول بحث شدند.

دسته بندي توابع UDF:

    توابع(UDF) به سه دسته تقسيم مي شوند : 

- توابع اسکالر(scalar)

- توابع جدولي تک خطي((inline

- توابع جدولي چند دستوري(multistatement)

توابع اسکالر 

خروجي هاياين توابع تک مقداري  مي با شدکه معمولا براي انجام محاسبات به کار برده مي شود 

ساختارکلي تابع به صورت زير تعريف مي شود:

CREATE  FUNCTION [ owner_name. ] function_name 

    ( [ { @parameter_name [AS] scalar_parameter_data_type [ = default ] } [ ,...n ] ] ) 

RETURNS scalar_return_data_type

 [ AS ]

BEGIN 

    function_body 

    RETURN scalar_expression

END

مثال9 : تابعي مي نويسيم که شماره يک دانشجو راگرفته و نام ونام خانوادگي وي را به فرمت خاصي (نام خانوادگي ،نام ) نمايش مي دهد .

Create FUNCTION fn_Nameret(@s# int)

 RETURNS NVARCHAR(37)

AS

BEGIN

        DECLARE @ret_Value  NVARCHAR(37)

        SELECT  @ret_Value=Name + '،' +Family

         FROM STD

        WHERE S# =@S#

         RETURN (@ret_Value)

End

تابع بالا را به صورت زير احضار مي شود.

select dbo.fn_Nameret(8008093)

مثال 10 :تابع اي مي نويسيم که يک شماره دانشجويي وشماره يکي از ترم هاي تحصيلي وي رادريافت نموده و معدل آن ترم((TrmGpa دانشجو را  برگرداند.

create function fn_TrmGpa(@s# int,@TrmNo char(4))

returns dec(5,1)

begin

DECLARE @TotGrade  dec(5,1)

DECLARE @TrmRegUnit dec(5,1)

select @TrmRegUnit=sum(CRS.Unit),@TotGrade=sum(REG.Grade*CRS.Unit)

    from REG,CRS  WHERE

         REG.c# = CRS.c#

          and

         REG.TrmNo = @TrmNo

          and

         REG.s#=@s#

return(@TotGrade/@TrmRegUnit)

end

تابع فوق را مي توان به شکل زير استفاده کرد:

SELECT  dbo.fn_TrmGpa(8008093,'3802')

توابع جدولي تک خطي ( Inline table-valued function) 

خروجي اين توابع يک جدول است . ساختار تابع به صورت زير تعريف مي شود:

CREATE FUNCTION [ owner_name. ] function_name 

    ( [ { @parameter_name [AS] scalar_parameter_data_type [ = default ] } [ ,...n ] ] ) 

RETURNS TABLE 

[ AS ] 

RETURN [ ( ] select-stmt [ ) ] 

مثال 11 : تابع زير  با دريافت شماره درس و شماره ترم ليست نام و جنسيت دانشجويان آن درس را در ترم مذکور بر         مي گرداند .

Create function fn_myreg(@C#  char(7),@TrmNo char(4))

Returns table

As

Return (select STD.Name,STD.Family,CODEFILE.[Desc]  From STD,CODEFILE

                        Where     CODEFILE.Field = 'sex'

                                   And

                                      CODEFILE.Type = STD.Sex

                                  And

                                       exists(select * from REG

                                                   Where     (REG.C# =@C#

                                                             and     STD.S#=REG.S#

                                                               and      REG.Trmno=@Trmno)                                                                      )

          )

تابع فوق را مي توان به شکل زير استفاده کرد:

Select  *  from  fn_myreg (8006534,'3821')

مي بينيد که تفاوت اصلي اين تعريف با توابع اسکالر در اين است که نوع خروجي يک جدول تعريف شده است.محدوديتي که روي اين نوع تعريف وجود دارد اين است که خروجي تابع بايد توسط يک دستور  selectايجادشود.

توابع جدولي چنددستوري( Multi-statement Table-valued Functions)

در اين نوع توابع اين محدوديت که خروجي بايد توسط يک دستور selectساخته شود وجود ندارد .

CREATE FUNCTION [ owner_name. ] function_name 

    ( [ { @parameter_name [AS] scalar_parameter_data_type [ = default ] } [ ,...n ] ] ) 

RETURNS @return_variable TABLE < table_type_definition > 

 [ AS ] 

BEGIN 

    function_body 

    RETURN

END

< table_type_definition > :: = 

    ( { column_definition | table_constraint } [ ,...n ] ) 

مثال12 : با استفاده از اين روش تابعي مي نويسيم که شماره يک دانشجو و شماره يک ترم وي را در يافت و در صورتي که معدل کل دانشجو بالاتر از 12 است شماره درس هايي  را که دانشجو در آن ترم ثبت نام کرده است را بر مي گرداند.در غير اين صورت حدول تهي برگرداند.

Create function Fn_crsnames(@S# int ,@TrmNo CHAR(4))

Returns  @crsnames table(c#  CHAR(7) not null)

As

Begin

Declare @mygpa  dec(5,2)

select @mygpa=Gpa from STD where S# = @S#

If @mygpa>12

    Begin

insert  @crsnames

Select  c#   from  REG    

                     Where  ( (S# = @S#) and (Trmno = @Trmno))        

    End

return      

End    

از تابع فوق مي توان به شکل زير استفاده نمود:

Select  *  from   Fn_crsnames(8008093,'3802')

تغيير وحذف تابع

براي تغييروحذف تعريف تابع(مانند ديگر اشياءبه ثبت رسيده يک پايگاه داده) به ترتيب از دستور هاي alterو dropاستفاده مي کنيم .

ALTER function   function _name   . . .

DROP function   function _name

نکته :چون در اينجا به انواع توابع اشاره کرديم بهتر است همين جا  به تفاوت نيازهاي اجرايي انواع توابع تفاوت آن ها با ديدها اشاره کنيم.گفتيم که براي محدود کردن دسترسي کاربران و برنامه هاي کاربردي به ستون ها يا سطر هاي مشخصي از جداول علاوه بر ديد توابع و روالها  مي توانند گزينه هاي مناسبي باشند.در مورد ديد مي توانيد بعد از ساختن  ديد با استفاده از select  مجوز استفاده از آن را به کاربرن اهدا کنيد.در مورد توابع وضعيت اندکي متفاوت است .براي استفاده از توابع اسکالر به مجوز Execute نياز داريد .براي استفاده از توابع جدولي که خروجي آن از نوع جدول است به مجوز select  هم نياز داريد . 

دستور کار

1- وارد محيط Query Analyzer شويد هريک از پرسشهاي زير را با استفاده از دستورات T-SQL پاسخ داده و اجرا        کنيد .

     (همه اسکريپت هاي نوشته شده را ذخيره نماييد.)

   - دانشجوياني را که بدون رعايت پيش نيازي در دروسي ثبت نام نموده اند ، استخراج کرده ،شماره و نام هر دانشجو              همراه با شماره و نام درس ثبت نام شده را نشان دهيد .

   - دانشجوياني را که بدون رعايت هم نيازي  در دروسي ثبت نام نموده اند ، استخراج کرده ،شماره و نام هر دانشجو همراه         با شماره و نام درس ثبت نام شده را نشان دهيد .

3- با احضار تابع مرحله قبل   TrmGpa را براي ترم آخر دانشجو در فايل  STDTRM اصلاح کند .

4- يک تابع به نام fn1_StdGpa تعريف کنيد که شماره يک دانشجو را دريافت نموده و با احضار تابع مرحله قبل ، معدل         کل (Gpa)  دانشجو راودر صورتي که چنين دانشجويي وجود نداشت مقدار-1 را برگرداند .

5- يک تابع به نام fn2_StdGpa تعريف کنيد که فقط با استفاده ازجداول CRSوREG شماره يک دانشجو را دريافت       نموده  و معدل کل (Gpa)  را محاسبه کرده و ، در صورتي که چنين دانشجويي وجود نداشت مقدار-1 را برگرداند .

6- با دريافت شماره دانشجويي و احضار تابع مرحله 5 Gpa  را در فايل STD اصلاح نمايد .

7- با استفاده از محيط Enterprise Manager يک تابع به نامfn_TotPasUnit ايجاد کنيد که شماره دانشجويي را          دريافت نموده و مجموع واحد هاي پاس شده  را برگرداند ، در صورتي که چنين دانشجويي وجود نداشت مقدار-1 را         برگرداند.

8 - با استفاده ازتب  Templateتابعي با نام fn_TotRegUnitايجاد کنيد که شماره دانشجويي را دريافت نموده و             مجموع واحد هاي ثبت شده (TotRegUnit) را برگرداند. در صورتي که چنين دانشجويي وجود نداشت مقدار-1 را        برگرداند.

9- با احضار توابع مراحل 7و8 فيلد هاي TotPasUnit و  TotPasUnitرا در جدولSTD ا اصلاح نماييد .

10-  يك تابع به نام fn_RegCtrl  بنويسيد که يـك شماره دانشجويي را دريافت نموده وصحت ثبت نام وي را بررسي              نموده و به عنوان نتيـجه يـكي از مقاديـر 0 تا 3 را به شرح زير برگرداند .

- 0، اشكال وجود ندارد .

- 1 ، يـعني  مشکل پيش نياز وجود دارد .

- 2 ،يـعني  مشکل هم نيازي وجود دارد .

- 3 ، يعني مشکل پيـش نيــاز ي و هم نيازي وجود دارد .

11- ا استفاده رويه ذخيره شده sp_helptext تابع نوشته شده در مرحله قبل را مشاهده نماييد .

12- نام دروس سه واحدي گروه کامپيوتر که دانشکده برق و کامپيوتر ارائه مي کند را استخراج کنيد .

 رويه هادر SQL-Server

  مقدمه 

رويه ها نيز مانندتوابع ابزارهاي مناسبي براي دسته بندي دستورات پرکاربرد هستند. رويه ها حتي مي توانند مقادير خروجي داشته باشند ولي تفاوت عمده اين دوساختاردرنحوه احضارآن ها براي گرفتن مقداربازگشتي است . احضار توابع صريح است بدين معني که در احضار توابع مي توان مقداري را مساوي با تابع قرار دادو بعداز احضار متغير با مقدار بازگشتي تابع مقدار دهي مي شود. ولي براي گرفتن خروجي از رويه ها بايد يک يا چندين پارامتر را به عنوان مقادير خروجي معرفي کنيم و رويه با مقدار گذاري و تغيير اين پارامتر ها مقدار خروجي را SET مي کند. تفاوت هاي اندک ديگري نيز بين اين دو ساختار وجود دارد که در حين بررسي  نحوه تعريف رويه ها به آن ها مي پردازيم .

       بعد از تعريف رويه ها ،آن ها معمولا توسط بخش بهينه ساز SQL-Server يک بار کامپايل شده وبهترين مسير اجرايي  براي آن ها ساخته مي شود. هنگامي که يک رويه احضار مي گرددتنها کاري که SQL-Serverانجام مي دهد جايگزيني پارامتر هاي فرستاده شده در رويه و استفاده از طرح اجرايي از پيش کامپايل شده رويه(cache)(بدون کامپايل وبهينه سازي مجددآن )براي اجراي احضار رويه است . 

ساختارتعريف رويه ها :

CREATE  PROC [ EDURE ]  procedure_name

    [ { @parameter data_type } 

         [ = default ] [ OUTPUT ] 

    ] [ ,...n ]

{ WITH  RECOMPILE}

AS sql_statement [ ...n ] 

بررسي پارامتر هاي تعريف فوق:

• ديده مي شودکه در تعريف  رويه ها نيز مانند توابع بعد از نام رويه ليست پارامتر هاي آن رويه مي آيدکه اين پارامتر ها مي توانند ورودي ،خروجي و يا ورودي-خروجي باشند .

• با استفاده ازکلمه کليدي Default مي توان مقداري را براي يک پارامتر تعيين کرد که در صورتي که در حين احضار رويه اين آرگومان احضار نگردد با مقدار پيش فرض جايگزين شود .(توجه کنيد که در اين صورت لازم است تااحضار رويه با تکنيک Call by nameصورت گيرد .)

• کلمه کليدي  Outputبدين معني است که پارامتر مورد نظر مي تواند به عنوان خروجي يا ورودي– خروجي مطرح با شد.

• گفتيم که رويه هاي ذخيره شده فقط يکبار کامپايل مي شوند .اگر بخواهيم که رويه در هر بار اجرا کامپايل شود ودر نتيجه نسبت به تغييرات ايجاد شده در پايگاه داده حساس باشد ،(مثلا اضافه شدن يک شاخص) مي توانيد از عبارت WITH  RECOMPILEاستفاده کنيد.

مثال1: رويه اي بنويسيد که شماره يک دانشجو وشماره يک ترم وشماره يک درس را دريافت ودرس مورد نظر را در ترم مذکوربراي آن دانشجو ثبت نام کند. اين رويه پارامتر چهارمي را هم دريافت مي کندکه به عنوان خروجي مجموع واحد هاي ثبت نام شده وي در ترم مذکوررادرآن برمي گرداندو در صورت داشتن هر گونه مشکل براي ثبت نام اين درس پارامتر چهارم  به شرح زير مقداردهي مي شود:

)1-(- دانشجو در آن ترم ثبت نام ندارد .

)2-(- دانشجو مشکل پيش نيازي براي ثبت نام درس دارد .

)3-(- دانشجو مشکل هم نيازي براي ثبت نام درس دارد .

Create proc pr_reg

@s# int,

@trmno char(4) default 3831,

@c# int ,

@trmregunit dec(3,1)output

As

if  exists(select * from stdtrm where s#=@s# and trmno=@trmno(

         if not exists (select cp# from prereq

                            where  c#=@c# and not exists(select c# from reg

                                                                                 where reg.s#=@s#

                                                                                 and    reg.c#=prereq.cp#))

              if not exists(select cc# from coreq

                            where  c#=@c# and not exists)select c# from reg

                                                                                 where reg.s#=@s#

                                                                                  and    reg.c#=coreq.cc#))

                       begin       

                          insert reg(s#,c#,trmno)  values(@s#,@c#,@trmno(

                          set @trmregunit=sum(unit) from crs inner join reg 

                                                       on crs.c#=reg.c#

                                                          Where s#=@s# and trmno=@trmno(

                       end 

            else

                    @trmregunit=-3.0

         else

                @trmregunit=-2.0

else  

               @trmregunit=-1.0

 روش هاي احضار رويه ها در Sql-server

در Sql-server دو روش براي احضار رويه ها وجود دارد در روش اول بعد از نام رويه ليست پارامتر هابا يک کاما بين آن ها  مي آيد و اگر پارامتري را نخواهيم بفرستيم جاي آن را خالي مي گذاريم .

مثال2 :با اين روش رويه pr_regرا احضار مي کنيم :

Declare @myvar dec(3,1)

pr_reg 8014681,1122323,@myvar

مثال 3 :پياده سازي روش دوم که آن را Call by nameنيز مي ناميم را در مثال زير مي بينيد:

           Declare @myvar dec(3,1)

pr_reg

@s#=8014681,

@c#= 11223323,

      @trmregunit=@myvar

تغيير وحذف رويه هاي ذخيره شده

براي تغييروحذف يک رويه ذخيره شده (مانند ديگر اشياءبه ثبت رسيده يک پايگاه داده) به ترتيب از دستور هاي AlterوDropاستفاده مي کنيم.

مثال4 : در مثال زير رويه   pr_regرا تغيير مي دهيم :

Alter proc pr_reg(

@name varchar(16),

@family varchar(20),

@trmno char(4),

@c# int,

@trmregunit dec(3,1) output 

As     …….

بررسي چند نکته :

1. مي توان از عبارت with recompile درجايي که يک رويه احضار مي شوداستفاده کرد. در اين صورت رويه دوباره کامپايل وبهينه سازي مي شود .

       مثال5 :

Execute pr_reg 

 @S#= 8014681,@TRMNO=3822,@C#=1116554,@trmregunit output  

with recompile

      2 .  احضاررويه به صورت صريح وبه کمک کلمه کليدي EXECUTE انجام مي شود .(درصورتي که احضار                     درابتداي دسته انجام گيرد ، نيازي به نوشتن اين کلمه نيست .) 

Declare @trmregunit dec(3,1) 

Execute pr_reg  8014681,3822 ,1116554,@trmregunit output

مثال6 : مي خواهيم رويه اي بنويسيم که دروس يک دانشجورا که بدون رعايت هم نيازي درترم جديدثبت نام کرده ، حذف نمايد .

create procedure pr_delete_coreq_reg (@st# int)

as

begin

 delete from reg  where

         reg.s# = @st#

         and

         reg.c#  in (select  coreq .c# from coreq

                      where

                         coreq.c#  = reg.c#

                         and

                         reg.s#  = @st#

                         and 

                            coreq.cp#  not in (select  reg. c#  from  reg

                                                   where 

                                                      reg.s#   =  @st#      ))

end

 رويه فوق را مي توان به شکل زير احضار نمود.

execute  pr_delete_coreq_reg  8004367

 دستورکار

1- يک رويه به نامTrmGpa Pr_ بنويسيد که شماره يک دانشجو و شماره ترم را دريافت نموده و معدل ترم دانشجو را محاسبه و بر گرداند .

2- يک رويه به نامPr_tot_stdtrm بنويسيد که شماره يک دانشجو را دريافت نموده ومعدل کل،کل واحدهاي گذرانده وکل واحدهاي اخذشده دانشجورا محاسبه نموده وبرگرداند .

3- يک رويه به نام Pr_Delete_Prereq_reg بنويسيد که شماره دانشجويي را گرفته ودروسي راکه دانشجو بدون رعايت پيش نيازي ثبت نام نموده ، حذف نمايد .

4- رويه اي به نام  Pr_Mydeleteبنويسيد که شماره درس و حداقل تعداد ثبت نام در يک درس براي  تشکيل شدن آن درس را دريافت و در صورتي که نعداد ثبت نام در درس به حد نصاب نرسيد ه است کليه ثبت نام هاي فعلي درس را حذف کند .