function رو برای چی استفاده می کنند

[sadaf_]

طریقه استفاده function در sql به چه صورتی هست ؟
چه کاربردهایی داره ؟
ممنون

[مهدی عابدینی]

--Transact-SQL Scalar Function Syntax
CREATE FUNCTION [ schema_name. ] function_name
( [ { @parameter_name [ AS ][ type_schema_name. ] parameter_data_type
[ = default ] [ READONLY ] }
[ ,...n ]
]
)
RETURNS return_data_type
[ WITH <function_option> [ ,...n ] ]
[ AS ]
BEGIN
function_body
RETURN scalar_expression
END
[ ; ]

[مهدی عابدینی]

توابع (Functions) :

تعریف : در SQL دو نوع تابع به شرح زیر داریم:

T_SQL Functions
Clr.Net Functions
توابع نوع دوم در ٢٠٠۵ SQL اضافه شده و با زبانهای #C و VB.NET قابل نوشتن میباشد. هر کدام از انواع توابع فوق به سه گروه زیر تقسیم میگردند:

Scaler Valued user Define functions
Table Valued user Define functions Inline
Table Valued user Define functions Multi statement
1- توابع Scaler Valued user Define functions:

در ابتدا توابع گروه اول مورد بحث قرار میگیرند. فرمت کلی این توابع به شکل زیر است:

Create Fumnction F_name (parameters)
Returns Datatype as
Begin
...
...
Return V_Name
End

مثال : تابع زیر حاصلضرب دو عدد را بر میگرداند:

Create Function mymul(@a int, @b int)
returns int as
Begin
Declare @c int
Set @c=@a*@b
return @c
end

با اجرای برنامه فوق , تابع mymul بعنوان بخشی از دیتابیس فعال تعریف و ذخیره شده و برای رؤیت آن میتوان از منوی سمت چپ صفحه , بر روی نام دیتابیس کلیک کرده و مسیر زیر انتخاب شود. پس از آن فایل dbo.mymul مشاهده خواهد شد.

Programmability>Functions>Scalar-Valued Functions

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

مثال ۱ : در مثال زیر حاصلضرب برابر ۲۵۰ نشان داده میشود.

Select dbo.mymul(25,10)

مثال ۲ : در مثال زیر نیز حاصلضرب برابر ۲۵۰ نشان داده میشود.

Declare @x int
Set @x=dbo.mymul(25,10)
Print @x

مثال ۳ : در مثال زیر در ابتدا یک جدول شامل دو فیلد عددی ساخته شده و پس از ایجاد تعدادی رکورد , حاصلضرب آنها را چاپ می کند:

Create Table T(x int, y int)
insert into T Values(10,12)
insert into T Values(15,14)
insert into T Values(18,20)
Select x,y,dbo.mymul(x,y) as M from T

٢- جداول مجازی View : دستور View یک Virtual Table یا Stored Query است که بوسیله آن میتوان یک دستور Select را بشکل Object در SQL ذخیره کرد و فرمت آن بشکل زیر است:

Create View vw_name
As
Select statement
Go

مثال : دستور زیر پس از اجرا , یک فایل بنام dbo.AuthorofCA را در قسمت دیتابیس فعال (اگر در زمان اجرای دستور pubs فعال باشد در این دیتابیس) ایجاد مینماید.

Create View AuthorofCA
As
Select * from Authors where State='CA'
Go

پس از آن می توان برای دسترسی به نویسندگان کالیفرنیایی , از دستور زیر استفاده کرد:

Select * from AuthorofCA

یا می توان با دستور زیر کد کتابهای نویسندگان کالیفرنیایی را بدست آورد:

Select * from AuthorofCA inner join Titleauthor
on AuthorofCA.au_id=Titleauthor.au_id

نکته ۱ : چنانچه یک دستور Select در تعداد زیادی از دستورات مورد استفاده قرار می گیرد بهتر است به منظور Structured Programing بصورت View تعریف شود.

نکته ۲ : میتوان دسترسی برخی از کاربران را برای یک View خاص تعریف و مشخص کرد لذا دستور View میتواند به ایمن سازی سیستم کمک کند.

نکته ۳ : پشت سر View هیچ رکوردی ذخیره نمی شود بلکه با اجرای دستور View دستور Select داخل View اجرا شده و نتیجه آن بازگشت داده میشود. بنابراین فقط به اندازه دستور Select و Execution Plan مربوط به آن , فضای حافظه اشغال میگردد.

نکته ۴ : اگرچه برای تغییر یک دستور View میتوان ابتدا آنرا با دستور Drop حذف کرده و سپس به وسیله دستور Create ایجاد نمود ولیکن این روش مناسبی نیست چون با حذف آن , تمامی دسترسی های کاربران نیز حذف میگردد لذا توصیه میگردد اگر قبلاً View ایجاد شده باشد برای تغییر آن به جای Create از دستور Alter استفاده شود.

نکته ۵ : با توجه به احتمال تداخل و پیچیده شدن عملیات دستورات مختلف , استفاده از دستورات Insert و Delete و Update در داخل View به هیچ عنوان توصیه نمی گردد.

٣- توابع Table Valued user Define functions Inlin:

این توابع نیز شبیه View یک دستور Select را بصورت مجازی تعریف می نماید ولیکن چون پارامتر دریافت می کند جایگزین بسیار خوبی برای View می باشد. فرمت کلی ساخت این توابع به شکل زیر است:

Create Fumnction F_name (parameters)
Returns Table as
Return (Select Statement)

مثال : تابع زیر مشابه مثال View فوق میباشد با این تفاوت که میتوان کد ایالت را بصورت پارامتر مشخص نمود :

Create Function Authorof(@state char(2))
Returns Table As
Return(Select * from Authors where State=@state)

روش اجرای تابع نیز به شکل زیر است :

Select * from dbo.Authorof('CA')

برای نمایش کد کتابهای نویسندگان یک ایالت خاص از دستور زیر استفاده میشود:

Select * from dbo.Authorof('CA') as K
inner join Titleauthor on K.au_id=Titleauthor.au_id

۴- توابع Table Valued user Define functions Multi statement:

فرمت ساخت آن عبارتنداز:

Create Fumnction F_name (parameters)
Returns @t_name Table (field1 datatype, [, fieldn datatype])
As
Begin
…
Return
End

تمرین : تابعی از نوع multi statement بنویسید که نام همه نویسندگان , ناشرها و فروشگاههایی که در یک ایالت خاص که از طریق پارامتر دریافت میگردد را نمایش دهد.

Create Function x910(@State char(2))
returns @H table (Name varchar(200),Type char(1))
as
Begin
Insert into @H
SELECT au_lname + ' ' + au_fname as Name,'A' as type
FROM Authors
Where State = @State
Insert into @H
SELECT Pub_name,'P' as type
FROM Publishers
Where State=@State
Insert into @H
SELECT Stor_name,'S' as Type
FROM Stores
Where State = @State
return
end


SELECT * FROM dbo.x910('ca')




منبع:
http://powerboy2988.persianblog.ir