فصل بیست و سوم :
User Defined Function
توابع تعریف شده توسط کاربر
مجموعه دستورالعمل هایی را در یک قالب یا ظرف قرار میدهیم
They allow modular programming
یکبار دستورات را مینویسیم و بارها می توانیم از آن استفاده کنیم . یعنی جلوی نوشتن کد تکراری یا duplicate code را گرفته ایم
They allow faster execution
اجرا سریعتر به نسبت اجرای دستورات بصورت عادی بدلیل عدم نیاز به کامپایل در هر بار اجرا
Execution Plan
در هنگام اجرای دستورات tsql ابتدا Query Parsing انجام میشود یعنی کنترل میشود که آیا دستورات اس کیوال بدرستی نوشته شده اند یا خیر. بعد از آن query Optimizer روشهای گوناگون اجرا یا همان (execution plan) را مشخص کرده و بهترین و سریعترین روش برای اجرای کوئری را می یابد . تصمیم میگیرد که ایا می توان به اطلاعات از طریق ایندکس ها دست یافت و غیره . مبنای انتخاب execution plan نهایی و بهینه هزینه یا COST اجرای یک کوئری است .یعنی کوئری برای اجرا به چه مقدار محاسبات cpu و یا دستیابی به io نیاز دارد. مبنای انتخاب روشی است که ارزانتر باشد یعنی پردازش کمتر cpu و دسترسی کمتر io را بطلبد.
هنگامی که query Optimizer یکبار بروشهای گوناگون اجرا را بررسی کرد و بهنترین روش را یافت آنرا بطور اتوماتیک ذخیره میکند. حال اگر بار دیگر آن کوئری اجرا شود . دیگر نیازی به بررسی تمامی روشهای اجرا نیست و از انچه قبلا محاسبه و ذخیره کرده استفاده میکند.
برای همین است که در اجرای دوباره یک کوئری نتایج را سریعتر می بینیم.
این مهمترین روشی است که می توان با کمک ان دیتابیس بسیار سریعتری داشت . دستورات را در یک ظرف و یا قالب قرار دهیم تا تنها یک بار parse شوند و در دفعات بعدی استفاده از execution plan قبلی استفاده شود.
• Scalar- Functions
یک مقدار دیتا را باز میگرداند که نوع داده بازگشتی برابر با نوع داده ایست که در عبارت RETURNSذکر میشود .
CREATE FUNCTION function_name
(
@parameter_name as parameter_data_type
)
RETURNS return_data_type
BEGIN
function_body
RETURN scalar_expression
END
Variable یا متغیر
تعریف متغیر :
DECLARE
نام متغیر با @ شروع شود
نوع داده متغیر
اختصاص مقدار به متغیر
SET
و یا از طریق select
مثال :
Declare @CDAteTime datetime
Set @CDAteTime=GETDATE()
Select @CDAteTime
مفهوم Batch
یک batch گروهی از یک و یا چندین دستور اس کیوال است که بطور یکباره از طریق یک اپلیکیشن به اس کیوال سرور ارسال میشود . اس کیو ال سرور دستورات یک batch را بعنوان یک execution plan کامپایل میکند.
هنگامی که کوئری ما شامل چندین دستور است انتهای هر دستور stall را باید با سمی کالن “;” مشخص نماییم البته اینکار فعلا در tsql الزامی نیست ولیکن در نسخه های اینده الزامی خواهد شد.
هنگامی که یکی از دستورات یک batch دچار مشکل شود کل ان batch اجرا نخواهد شد.
در tsql انتهای هر batch را با دستور GO مشخص میکنیم .
GO [count]
Count عدد integer ی است که تعداد دفعات اجرای یک batch را مشخص میکند .
تابعی بنویسید که id هر دانش آموز را بگیرد و رشته ایی شامل نام ، نام خانوادگی و سن او را باز گرداند
create function StudentConcat (@ID smallint)
returns nvarchar(500)
-- WITH ENCRYPTION
as
begin
declare @res nvarchar(500);
select @res=concat(FirstName,space(5),LastName,space(5),cast(Age as nvarchar(2)),' سال دارد ') from Student where ID=@ID
return @res
end
go
select dbo.studentconcat(1)
declare @res nvarchar(500)
exec @res=dbo.studentconcat @Id=1
print @res
--Returns the result in the message tab.
SET ANSI_NULLS ON
باعث میشود رفتار اپراتور های مقایسه ایی همانند = و <> در هنگام برخورد با null عوض شود
در حالت نرمال امکان استفاده از اپراتور = برای فیلتر کردن کردن مقادیر null در where وجود ندارد ولیکن اگر
SET ANSI_NULLS OFF
این رفتار پیشفرض عوض شده و از اپراتور های مذکور می توان برای مقایسه با null نیز بهره جست. در نسخه های آینده اس کیوال سرور
SET ANSI_NULLS ON
خواهد بود و امکان تغییر آن وجود ندارد لذا توصیه میشود این تنظیم را بصورت پیشفرض رها کنیم.
go
set ansi_nulls off
select * from Student where ContinuingEducation=null
set ansi_nulls on
select * from Student where ContinuingEducation is null
SET QUOTED_IDENTIFIER ON
در tsql بطور پیش فرض بایستی رشته ها را در single quotation قرار دهیم و اگر داخل یک رشته حاوی ‘ بود باید با دو single quotation نوشته شود .و همچنین اگر بخواهیم از کلمات کلیدی اس کیو ال و یا کاراکتر های غیر مجاز برای نام ابجکت ها و بعنوان مثال نام یک جدول استفاده کنیم می توانیم آنرا داخل double quotation قرار دهیم.
این رفتار پیشفرض را می توان تغییر داد
SET QUOTED_IDENTIFIER OFF
در این هنگام رشته های کاراکتری می توانند با single و یا double quotation محصور شوند
SET QUOTED_IDENTIFIER off
select "you d'not have permission"
SET QUOTED_IDENTIFIER OFF
GO
-- An attempt to create a table with a reserved keyword as a name
-- should fail.
CREATE TABLE "select" ("identity" INT IDENTITY NOT NULL, "order" INT NOT NULL);
GO
SET QUOTED_IDENTIFIER ON;
GO
-- Will succeed.
CREATE TABLE "select" ("identity" INT IDENTITY NOT NULL, "order" INT NOT NULL);
GO
SELECT "identity","order"
FROM "select"
ORDER BY "order";
GO
DROP TABLE "SELECT";
GO
SET QUOTED_IDENTIFIER OFF;
GO
میدان دید و یا Scope یک متغیر
اسکپ یک متغیر که نام آن با @ شروع شود در همان session و همان batch ی است که در آن تعریف شده است
امکان تعریف متغیر global یعنی متغیری که در session های دیگر قابل دسترسی باشد در اس کیوال سرور وجود ندارد.
(اینکار را می توان بوسیله temporary table ها با ## انجام داد که قبلا توضیح داده شده است .)
بسیاری از مفاهیم درفیلم اموزشی بطور مفصل شرح داده شده اند . لذا حتما فیلم آموزشی این فصل را ببینید .
مفاهیم و موضوعات این فصل عبارتند از :
-
User Defined Function
-
Execution Plan
-
Scalar- Functions
-
Variable یا متغیر
-
مفهوم Batch
-
SET ANSI_NULLS ON
-
SET QUOTED_IDENTIFIER ON
-
میدان دید و یا Scope یک متغیر