آموزش 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
نتیجه : در لحظه ورود یا تغییر داده ها اگر بزرگترین نمره در جدول از ۲۰ بیشتر بود پیغام مناسب را نمایش می دهد.
مثال ۲: تریگری بنویسید که بعد از درج داده جدید در جدول sudent اگر داده ها بیشتر از ۱۰ رکورد شد یک پیغام خطا چاپ کند و رکورد های درج شده را حذف کند.
create trigger isert_trigger
on student
for insert
as
if (select count(*) from student) >10
begin
‘print ‘you cannot insert
rollback
end
موفق باشید
