آموزش SQl server- جلسه هفتم

روال هاي ذخيره شده  stored procedure

به مجموعه دستورات t.sql كه شامل تعريف متغيير ها ، عمليات روي آنها و دستورات ورودي و خروجي ميشود s.p گفته مي شود. هر روال ذخيره شده يك شي است كه مي تواند سيستمي يا غير سيستمي باشد.روال هاي سيستمي قبلا تعريف شده اند و با ايجاد يك بانك اطلاعاتي جديد به آن بانك اضافه مي شوند و ما فقط در T.sql ها آنها را فراخواني مي كنيم ولي  روال هاي  غير سيستمي توسط خود كاربر تعريف شده و همانند نوع سيستمي مي توانند بارها و بارها فرخواني شوند.

ايجاد يك روال ذخيره شده:

پارامتر هاي ورودي   نام  روال   create procedure

‍‌Output]  as ]   نوع پارامتر ها

دستورات  select

پارامتر هاي ورودي روال ها با علامت @ و نوع داده اي تعريف مي شوند و پارامتر هاي خروجي بعد از پارامتر هاي ورودي با علامت @ و نوع داده اي و كلمه كليدي output مي آيند.

مثال: (پارامتر هاي خروجي)

create proceduer  grdesum @sum int output

as

select @sum = sum(grade) from student

موقع فراخواني جمع نمرات به عنوان خروجي برگردانده مي شود.

(پارامتر هاي ورودي )

create    std_info   @name  nvarchar(20) , @family  nvarchar

as

select  std_name , std_family, age  from student

where  std_name = @name  and std_famil=@family

در موقع فراخواني اطلاعات دانش آموزاني برگردانده مي شود كه برابر با مقادير فرستاده شده مي باشد.

فراخواني  proceduer

اگر در قطعه كد  t.sql دستور فراخواني اولين دستور باشد مي توان فقط نام پروسيجر را نوشت ولي در صورتي كه اولين دستور نباشد بايد حتما عبارت exec يا execute را همراه آن نوشت.

فراخواني روال هايي كه ورودي دارند:

مقدار فرستاده شده براي روال=نام پارامترورودي @   نام پروسيجر   exec

مثال:

‘exec     std_info    @name=’anis’,@family=’barmar

فراخواني روال ها بدون ورودي:

نام پروسيجر  exec

توابع تعريف شده كاربرuser difine function-udf–

تابع مجموعه از t.sql هاست كه يك بار نوشته مي شود و چند باز فراخواني مي شود ولي تفاوت آن با پروسيجرهاي قبلي در اين است كه حتما و حداقل يك خروجي را دارد و بايد يك مقدار را به عنوان خروجي برگرداند.

نوع خروجي    returns    (پارامتر ها)        نام تابع    create   function

as

begin

….

نام متغيير خروجي   return

end

روش فراخواني :

‘نامي براي نمايش’  as   (پارامتر ها)نام تابع .نام مالك.نام بانك   select

مثال : تابعي بنويسيد كه  يك عدد را دريافت و فاكتوريل آن را حساب كند.

( create  function  fact (@num int

returns int

as

begin

declare  @sum int

set  @sum =1

declare  @counter

set @counter = 0

while  @counter<= @num

begin

set  @counter = @counter+1

set  @sum = @sum *@counter

end

return  @sum

end

فراخواني:

‘select    class.dbo.fact(3)   as  ‘fact

نكته:dbo يك مالك عمومي است.

توابع جدولي يا inline table:

اين نوع تابع نيز حتما خروجي دارد و مي توان بارها و بارها از آن استفاده كرد اما تفاوت ان با تابع قبلي در اين است كه توابع جدولي مجموعه اي از ركورد هاي جدول را برمي گرداند.

ايجاد يك تابع جدولي:

return   table    (نام پارامتر ها)نام تابع    create function

as

(دستورات  rreturn   (select

فراخواني توابع جدولي

(پارامتر ها)نام تابع    select    * from

مثال:اطلاعات دانش آموزاني را برگرداند كه معلمشان همان معلمي باشد كه به عنوان ورودي به تابع فرستاده مي شود.

create    function  std_info(@teacher    varchar) returns   table

as

return  (select student.std_name,student.std_family,register.teacher

where   student.std_num =register.std_num

(and    register.teacher = @teacher

روش فراخواني:

(‘select   *  from     std_info(‘ahmadi

Trigger :

نوعي روال ذخيره شده است كه در مواقع خاص اجرا مي شود مثلا در زمان درج داده جديد يا ويرايش داده ها اجرا مي شود . توجه كنيد كه trigger هيچ گاه فراخواني نمي شود و بلكه اتوماتيك اجرا ميشود(البته در زماني كه مشخص مي كنيد)  توسط دستور زير ايجاد ميشود.

نام  تريگر  create  trigger

نام جدولي كه تريگر روي ان عمل مي كند on

for    insert/update/delete

as

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

نكته: به جاي كلمه for  مي توان از كلمه after , inetred  af نيز استفاده كردو كلمه after پيش فرض است.

after: بعد از انجام موفقيت آميز عمليات (insert/update/delete)

for: در همان لحظه اجرا مي شود.

ineterd of: ترگير را به يك قطعه كد T.SQl عادي تبديل مي كند و مانند يك پروسيجر معمولي تريگر را مي سازد.

ايجاد يك تريگر به صورت ويزاردي:

راست كليك روي جدول مورد نظر/گزينهall taks/mange trigger و نوشتن كد هاي مورد نظر

مثال:

create  trigger std_trigger

on  student

for insert/update

as

if  (select max(grade)   from student) >20

‘print     ‘a range is not valid

نتيجه : در لحظه ورود يا تغيير داده ها اگر بزرگترين نمره در جدول از 20 بيشتر بود پيغام مناسب را نمايش مي دهد.

مثال 2: تريگري بنويسيد كه بعد از درج داده جديد در جدول sudent اگر داده ها بيشتر از 10 ركورد شد يك پيغام خطا چاپ كند و ركورد هاي درج شده را حذف كند.

create trigger isert_trigger

on student

for  insert

as

if    (select count(*)   from student) >10

begin

‘print  ‘you cannot insert

rollback

end

موفق باشيد