روال های ذخیره شده  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

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

مثال ۲: تریگری بنویسید که بعد از درج داده جدید در جدول sudent اگر داده ها بیشتر از ۱۰ رکورد شد یک پیغام خطا چاپ کند و رکورد های درج شده را حذف کند.

create trigger isert_trigger

on student

for  insert

as

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

begin

‘print  ‘you cannot insert

rollback

end

موفق باشید