رویه های ذخیره شده اشیایی اجرا پذیر در بانک اطلاعاتی SQL Server

صفحه 1:
Stored 

صفحه 2:
* «یاعم6) یا به زبان‌فابسی" رمیه های‌ذفیره شده " لشیایی‌لجرا پذیر در بانکاطلاعاتی ‎Mime GGL Gerver‏ که شاملیکیا چندین‌دستور 6) می‌شود . لینرمیه ها میتولنند یارلمتر هایورودیه خرمجی‌دلشته باشند. همچنیزم یتولن‌دلخلینرمیه ها به نبان0) برنامه نویسی‌کرد . مهم تریز‌کاببر لیزرمیه ها نفیره کردن‌دستورلت ‎Geter‏ ‎Iosert , Opdate , Delete‏ هستیا) ترکیبیاز لینها . 

صفحه 3:
نموه سافت این رویه ها به صورت زیر می باشد ء وارد بانک اطاعاتی ‎GL Grrver‏ شده , يس از باز كردن بانك مورد نظر در قسمت ما7۷( وارد بخش ‎Cred Procrchee‏ شوید . . ‏سم را اتتغاب نمایید‎ Grored Provoke 9 09)5 Cushy GulS Grred Procechew 69) 32 امتمالا کد های پیشفرضی را در اين صفمه مشاهده مى كنيد . همه اين كد هارا يا كرده و به صورت زير كد نويسى كنيد : Create Procedure YourProcedureName(@FirstParamete DataType,@SecondParameter DataType....) As Begin SQLStatement End کد فوق شکل کلی ساخت رویه های ذخیره شده می باشد . 

صفحه 4:
به 54 ‎o6 Gy You ProvedurOuve‏ برای برای رویه انتخاب کنید . سعی کنید نامی که انتخاب می نمایید مفهوم رویه شما را برساند مثلا براى درج ‎Dewbers 9x9 )> 3999 3995) Ga‏ 04 6@ رام( رره(0۹<) را انتخاب می کنم و یا برای دریافت تمام ركورد هاى اين جدول نام جحامج()1720910) نام با مفهومى است . در يرانتز مقابل نام رويه يارامتر هاى رويه به همراه نوع يارامتر تعيين میشود اگر تعداد پارامتر ها بيشتر از يكى بود با ويركول آنها را جدا می کنیم . به جای ‎Orga on al) GQ! Olgiws GQLGtateweat‏ و لمع نوشته مى شوند تايب كنيد . دستوراتی مانند صعاع0) , صعطلم() , اسعوا , ‎Gelert‏ و.... 

صفحه 5:
* به عنوان مثال ۳ زیر اطلاعات کاربر را با دریافت پارامتر نام کاربری (۲1۵ ‎8b)s TOL_Dewbers Jo jl‏ می کند : create procedure GetMemberbyID(@ID nvarchar(100)) as begin select * from TBL_Members where ID=@ID end سيس بر روى ‎G0 S US Bertie HS‏ رویه شما ساخته و ذخیره گردد . 

صفحه 6:
برفی از قابلیت های رویه های ذفیره شده یا ‎Ctored Prooedure‏ عبارت است ‎Prevowpled‏ بچدن‌آتهای» لیزمعترک» به زیانماشیری سيار نزیکشموبه طور کاملاز املت رجمه نمیشود . لیرلمر باعطفزیشس رعح ابگز بی‌می‌گردد ‏قابلیت ذفیره داشته و میتوان از یک رویه چندین بار در کد نویسی استفاده کرد . ‏دارای ویرایشگر مب) می باشد (میتوانید به سادگی یک برعم) مرفه ای ایجاد کنید) ‎ دنک ‏خطاهای ,263) جلوگیری می‎ jl 9 Cane Gprtax Parser ‏داراى‎ ‏دارای امنیت ‎ally‏ است و از هک شدن سایت شما توسط متسه ‎GGL‏ در بسيارى موارد جلوگیری می کند ‏ابلیت درج چندین دستور ,963 را يشت سرهم داراست . ‏قابلیت ارسال ورودی و خرومی دارد . ‏بلیت برنامه نویسی ( شرط ۰ حلقه و...) به زبان ‎Praca SQL‏ دارد . ‎ ‎ ‎ ‎9 ‎ ‎ ‎ 

صفحه 7:
حال که با قابلیت ها ونموه ساخت عسلسسس<) لسم) آشنا شدید بد نیست که به نحوه استفاده از اين ابزار جالب در “05.0002 بيردازيم استفادة از اين ابزار بسيار ساده هست . اكراز «تصمحك-0) با©8) يا جحصحص:» جد ز01) استفاده مى كنيد يك ويزارد براي سافت و استفاده از جم لجدوو") لعسدت8» ها قرار داده شده كه به راحتى آن را مديريت كنيد . اكر از کد نویسی برای کار با بانک های اطلاعاتی استفاده می کنید کافیه بعد از تعریف اججسمم)را30) به جای دستورات ,96) نام 20۳ را مارد نموده و بعد از آن كد زير را اضافه نمایید : Cmd.CommandType = CommandType.StoredProcedur 

صفحه 8:
این کد به برنامه می گوید که دستورات نوشته شده از نوع سس 2۱ هستند و نیازی به ترجمه آن ها نیست در نهايت كد هاى اتصال ما به شکل زیر فیامد بو : ("دوق تعس ۱ ل تا ساملس م0 موز م()(: زر صلو0 وموقظك. از 

صفحه 9:
بهینه سازی ‎procedure‏ 510160 ها در ‎SQL‏ ‎Server‏ مورد ‎-١‏ در نامهاى 908 از _ مد استفاده نکنید. زیرا این علامت مخصوص ‎(slo my‏ سيستمي مون در جدول -جوتت ميباشد و هنكامى كه از ان استفاده میکنید سیستم آبتدا دبال ای نام در جداول سيستمى ميكردد. يس از اون اكه بيدا نكرد با 0)00)جعمسد به دنبال اون ميكرده که همین باعث ميشه كلى از سرعت اجراى ‎op‏ کاهش پیدا کن. «-در داغل یک 6۳) بهتر است بع جای اینکه داغل آن از دو دستور اسع9) استفاده کرد. هرگدام را در داغل یک 2) قرار داده و آنرا به هنگام نیاز اجرا کنیم. به مثال زیر توجه نمایید: كد pred Olored provecdre dbv.P Dest © ‏خا جد انا ردصمب‎ @query=O velevt * Prow: ‏تساه ماه مایت‎ * Prow publehers ‏دي‎ بهتر است از نمونه زیر استفاده شود. کد سس لست حل عب انا رسي ).سيك سلطا سس =|] reste rovedure cbv.spTesfrow@Qukors os select * Pro Dubos I Crexte Procedure dbv.sp/PestProoPubtshers a Ortert * Proxp Publishers yo 

صفحه 10:
۴ دلیل استفاده از کد زیر چیست و نسبت به کد بالا چه مزیتی دارد؟در داخل هر مد فقط يك ‎Query‏ میتواند در دافل ,90) ساسح قرار میگیرد . و هون در داخل 60 اول دو رصعب هستند هر دفعه كه اين 608 اجرا شود مجدد 50 كاميايل خواهد شد و همین سرعت آنرا خواهد كرفت نکته ا: در 96) هايي که نیاز نیست کاربر متوجه بشه چه تعداد ردیف تمت تاثیر قرار گرفته است,عتما در اول 96۳) دستور 0 د00( ‎3١‏ را بنويسيد. زيرا اكر این دستور را ننویسید هربار که عملیاتی صورت گرفته 963,۰) تعداد ردیفهای تحت تاثير قرار گرفته را برای کاربر ارسال میکند و همین باعث یک ترافیک الکی روک oie cheat, server * نکته ۲: تاجاییکه امکان داره دستورات داخل 96۳) را کوچک نگه دارید. این کمک میکنه که تعداد سا ها کم بشه و سرعت کلی برنامه شما بالا بره. دو راه برای کاهش طول دستورات ,963) موجود است. 

صفحه 11:
تفکیک کردن کارهای یکپارچه به مرامل کوچکتر که هر مرمله در مد امکان به سرعت ‎Courant‏ شود. سو استفاده از ‎GAL Gerver Graiewect Backes‏ . که رفت و برگشت بین اه و ‎Gerver‏ )| کم میکند : اگر دستورات داخل 36۳) هميشه ثابت هستند و بصورت تعریف نشده اند. فیلی خوب است و این 165 ‎at GIy GOL G spine Geb‏ یک 4۰ تشکیل دهد. ولی آگر بوسیله دستور عصو) دارای دير ميباشد و هردفعه عوض میشود اين يك هيز ايده آل نيست و هردفعه 0505 بايد هتما ‎sails Ladd ySaib solgdi Oprenize 9 sq Compre‏ كه هر دفعه كه 00500 نياز به ارا داشته باشد دائما متفیرهای آن تغییر خواهند کرد بهتر است آبتدای 96۳) دستور ات3 0 را بنویسید .این ‎GP‏ را ممبور میکنه که متما باید موقع اجرا دویاره عابس«() شود . در اینمال شما مطمئن شوید که هربار كه 8)8) اجرا شد خود به فود 0005 ) ميشود. إنامه فود (ا جورى طراى كنيد كه كارير امكان لغو يك عملي کاربر مجیور به سس كردن سيستم شود , که شود.نکته ۵: بیشتر *26) ها از تعدادی پارامترها استفاده میکنند. این به فودی فود چیز بدی نیست, ولی زمانی میتونه باعث مشکل بشه که اگر پارامترها اس باشند. و تعداد پارامترهای متفیر خیلی زیاد باشند هر زمان که ‎sp‏ اجرا ميشود. دوراه برأى هندل اين مشكلء هست یکی با بازده ارام و یکی ج بازده سریع.راه بهتر اینه که « به کار گیری منطق 1۳...//96۶ را دافل 696۳ هست. و ایجاد یک بسح رها که درون *3) تعریف شده اند. در اين راه. شما ان که اجرا ميشود کارآمد و موثر هست. ۱ داشته باشد. کاری نکنید كه شايد 

صفحه 12:
* اعضاء گروه: 

Stored Procedure  reیا Spیا به زبان فارسی " رویه های ذخیره شده " اشیایی اجرا پذیر در بانک اطالعاتی SQL Serverهستند که شامل یک یا چندین دستور SQLمی شود ،این رویه ها میتوانند پارامتر های ورودی و خروجی داشته باشند . همچنین میتوان داخل این رویه ها به زبان SQLبرنامه نویسی کرد . مهم ترین کاربر این رویه ها ذخیره کردن دستورات Select , Insert , Update , Deleteهست یا ترکیبی از اینها . نحوه ساخت این رویه ها به صورت زیر می باشد : وارد بانک اطالعاتی SQL Serverشده ،پس از باز کردن بانک مورد نظر در قسمت Programmabilityوارد بخش Stored Procedureشوید . بر روی Stored Procedureکلیک راست کرده و New Stored Procedureرا انتخاب نمایید . احتماال کد های پیشفرضی را در این صفحه مشاهده می کنید ،همه این کد هارا پا کرده و به صورت زیر کد نویسی کنید : ‏Create Procedure ‏YourProcedureName(@FirstParamete )DataType,@SecondParameter DataType,... ‏As ‏Begin ‏SQLStatement ‏End کد فوق شکل کلی ساخت رویه های ذخیره شده می باشد . به جای YourProcedureNameیک نام برای برای رویه انتخاب کنید . سعی کنید نامی که انتخاب می نمایید مفهوم رویه شما را برساند مثال برای درج یک رکورد جدید در جدول Membersمن نام AddNewMemberرا انتخاب می کنم و یا برای دریافت تمام رکورد های این جدول نام GetAllMembersنام با مفهومی است . در پرانتز مقابل نام رویه پارامتر های رویه به همراه نوع پارامتر تعیین میشود ،اگر تعداد پارامتر ها بیشتر از یکی بود با ویرگول آنها را جدا می کنیم . به جای SQLStatementدستورات SQlرا که بین Beginو End نوشته می شوند تایپ کنید . دستوراتی مانند Select , Insert , Update , Deleteو....  به عنوان مثال Spزیر اطالعات کاربر را با دریافت پارامتر نام کاربری (@ )IDاز جدول TBL_Membersدریافت می کند : ‏create procedure GetMemberbyID(@ID ))nvarchar(100 ‏as ‏begin ‏select * from TBL_Members where ‏ID=@ID ‏end سپس بر روی دکمه Executeکلیک کرده تا رویه شما ساخته و ذخیره گردد .  ‏ ‏ ‏ ‏ ‏ ‏ ‏ ‏ ‏ ‏ ‏ ‏ برخی از قابلیت های رویه های ذخیره شده یا Stored Procedureعبارت است از : Precompiledبودن آنها به این معنی که به زبان ماشین بسیار نزدیک شده و به طور کامل از اول ترجمه نمیشود ،این امر باعث افزایش سرعت بارگزاری می گردد . قابلیت ذخیره داشته و میتوان از یک رویه چندین بار در کد نویسی استفاده کرد . دارای ویرایشگر Queryمی باشد (میتوانید به سادگی یک Queryحرفه ای ایجاد کنید) دارای Syntax Parserهست و از خطاهای SQLجلوگیری می کند . دارای امنیت باالیی است و از هک شدن سایت شما توسط SQL Injectionدر بسیاری موارد جلوگیری می کند . قابلیت درج چندین دستور SQLرا پشت سرهم داراست . قابلیت ارسال ورودی و خروجی دارد . قابلیت برنامه نویسی ( شرط ،حلقه و )...به زبان TransactSQLدارد . و.....  ‏ ‏ حال که با قابلیت ها ونحوه ساخت Stored Procedureآشنا شدید بد نیست که به نحوه استفاده از این ابزار جالب در Asp.NETبپردازیم . استفاده از این ابزار بسیار ساده هست .اگر از SQL Datasourceیا ObjectDatasourceاستفاده می کنید یک ویزارد برای ساخت و استفاده از Stored Procedureها قرار داده شده که به راحتی آن را مدیریت کنید . اگر از کد نویسی برای کار با بانک های اطالعاتی استفاده می کنید کافیه بعد از تعریف SQLCommandبه جای دستورات SQLنام SPرا وارد نموده و بعد از آن کد زیر را اضافه نمایید : = Cmd.CommandType ;CommandType.StoredProcedure  هستند و نیازی به ترجمه آنStored Procedure این کد به برنامه می گوید که دستورات نوشته شده از نوع . ها نیست : در نهایت کد های اتصال ما به شکل زیر خواهد بود ;SqlConnection Con=new SqlConnection("YourConnectionString") ;SqlCommand Cmd = new SqlCommand("YourProcedureName", Con) ;Cmd.CommandType = CommandType.StoredProcedure ;)(Con.Open ...Some Code // ;)(Con.Close بهینه سازی stored procedureها در SQL ‏Server مورد -1در نامهاي SPاز _ spاستفاده نكنيد .زيرا اين عالمت مخصوص spهاي سيستمي موجود در جدول masterميباشد و هنگامي كه از اين اختصار استفاده ميكنيد سيستم ابتدا دنبال اين نام در جداول سيستمي ميگردد .پس از اون اگه پيدا نكرد با ownerDBOبه دنبال اون ميگرده كه همين باعث ميشه كلي از سرعت اجراي spكاهش پيدا كنه. -2در داخل يك SPبهتر است به جاي اينكه داخل آن از دو دستور Selectاستفاده كرد ،هركدام را در داخل يك SPقرار داده و آنرا به هنگام نياز اجرا كنيم .به مثال زير توجه نماييد: كد ‏create Stored procedure dbo.SPTest @query bit as if @query=0 select * from: ‏authors else select * from publishers go بهتر است از نمونه زير استفاده شود. كدcreate Stored procedure dbo.SPTest @query bit as if @query=0 Exec : ‏sptestFromauthors else Exec spTestfrompublishers go //-----------------// create ‏Procedure dbo.spTestfromAuthors as select * from Authors * go //-----------------// Create Procedure dbo.spTestFromPublishers as Select ‏from Publishers go  ‏ ‏ دليل استفاده از كد زير چيست و نسبت به كد باال چه مزيتي دارد؟در داخل هر sp فقط يك Queryميتواند در داخل cache SQLقرار ميگيرد .و چون در داخل SPاول دو queryهستند هر دفعه كه اين SPاجرا شود مجدد SPكامپايل خواهد شد و همين سرعت آنرا خواهد گرفت .نكته :1در SPهايي كه نياز نيست كاربر متوجه بشه چه تعداد رديف تحت تاثير قرار گرفته است،حتمًا در اول SPدستور Set NoCount Onرا بنويسيد .زيرا اگر اين دستور را ننويسيد هربار كه عملياتي صورت گرفته SQL،تعداد رديفهاي تحت تاثير قرار گرفته را براي كاربر ارسال ميكند و همين باعث يك ترافيك الكي روي client , serverميشود. نكته :2تاجاييكه امكان داره دستورات داخل SPرا كوچك نگه داريد .اين كمك ميكنه كه تعداد Lockها كم بشه و سرعت كلي برنامه شما باال بره .دو راه براي كاهش طول دستورات SQLموجود است. تفكيك كردن كارهاي يكپارچه به مراحل كوچكتر كه هر مرحله در حد امكان به سرعت Commitشود. سو استفاده از ، SQL Server Statement Batchesكه رفت و برگشت بين clientو Serverرا كم ميكند نكته :3اگر دستورات داخل SPهميشه ثابت هستند و بصورت ديناميك تعريف نشده اند ،خيلي خوب است و اين كار باعث ميشود تا SQLبراي آن يك Planتشكيل دهد .ولي اگر بوسيله دستور Whereداراي متغير ميباشد و هردفعه عوض ميشود اين يك چيز ايده آل نيست و هردفعه SPبايد حتمًا Compileشود و Optimizeنخواهد شد.اگر شما ميدانيد كه هر دفعه كه SPنياز به اجرا داشته باشد دائمًا متغيرهاي آن تغيير خواهند كرد ،بهتر است ابتداي SPدستور With Recompileرا بنويسيد .اين SPرا مجبور ميكنه كه حتمًا بايد موقع اجرا دوباره Compileشود ،در اينحال شما مطمئن شويد كه هربار كه SPاجرا شد خود به خود Optimizeميشود. نكته :4برنامه خود را جوري طراحي كنيد كه كاربر امكان لغو يك عمليات را داشته باشد .كاري نكنيد كه شايد كاربر مجبور به rebootكردن سيستم شود ،كه ميتواند باعث تجزيه نشدن مشكالت performance شود.نكته :5بيشتر SPها از تعدادي پارامترها استفاده ميكنند .اين به خودي خود چيز بدي نيست. ولي زماني ميتونه باعث مشكل بشه كه اگر پارامترها optionalباشند ،و تعداد پارامترهاي متغير خيلي زياد باشند هر زمان كه spاجرا ميشود .دوراه براي هندل اين مشكل هست يكي با بازده آرام و يكي با بازده سريع.راه بهتر اينه كه ،به كار گيري منطق If...ELSEرا داخل SPهست ،و ايجاد يك query مجزا براي هر تركيب ممكني از متغيرها كه درون SPتعريف شده اند .در اين راه ،شما ميتوانيد مطمئن باشيد كه queryشما هر زمان كه اجرا ميشود كارآمد و موثر هست.  اعضاء گروه: