1 Stored Procedure & Trigger In SQL مهندس محمد زوار WebGard@Outlook.com
2 ھا Stored Procedure
3 SP چيست SP سر نام کلمات Stored Procedure و به معناي رويه هاي ذخيره شده ميباشد که اشيايي اجرا پذير در بانک اطلاعاتي SQL Server هستند و شامل يک يا چندين دستور SQLمي باشند اين رويه ها ميتوانند پارامتر هاي ورودي و خروجي نيز داشته باشند. همچنين ميتوان داخل اين رويه ها به زبان ترين کاربرد اين رويه ها ذخيره کردن مهم SQLبرنامه نويسي کرد. يا ترکيبي از Select,Insert, Update, Delete اين موارد ميباشد. شرکت Sybase در اواخر دهه 1980 براي اولين بار روالهاي ذخيره شده را در SQL Server به کار برد. اين روال ها مانند جداول و ايندکس ها پس از ساخته شدن در پايگاه داده ها ذخيره ميشوند.
4 برخي از قابليت هاي رويه هاي ذخيره شده يا Storedعبارت Procedure است از : Precompiledبودن ا نها به اين معني که به زبان ماشين بسيار نزديک شده و به طور کامل از اول ترجمه نميشود اين امر باعث افزايش سرعت بارگزاري مي گردد. قابليت ذخيره داشته و ميتوان از يک رويه چندين بار در کد نويسي استفاده کرد. داراي ويرايشگر Queryمي باشد (ميتوانيد به سادگي يک Queryحرفه اي ايجاد کنيد)
5 داراي Syntaxهست Parser و از خطاهاي SQLجلوگيري مي کند. داراي امنيت بالايي است و از هک شدن سايت شما توسط SQLدر Injection بسياري موارد جلوگيري مي کند. قابليت درج چندين دستور SQLرا پشت سرهم داراست. قابليت ارسال ورودي و خروجي دارد. زبان شرط حلقه و...) به ) برنامه نويسي قابليت و.... TransactSQLدارد
6 نحوه ايجاد Stored Procedure وارد نرم افزار SQLشده Server و جهت درج Queryجديد اقدام ميکنيم. سپس به صورت زير Storedمورد Procedure نظر را ايجاد مينماي يم. (...,نوع داده پارامتر دوم@, نوع داده پارامتراول@) نام روال Create Procedure As Begin دستورات SQL End
7 مشاهده Stored Procedure هاي ايجاد شده جهت مشاهده Storedهاي Procedure ايجاد شده ميتوانيد وارد نرم افزار SQLشويد Server و در پنجره Object Explorer از مسير زير اقدام فرماييد. DataBaeses Ł عنوان پايگاه داده Ł Programmability Ł Stored Procedures
8 انتخاب نام دانش ا موز از جدول Student Create Procedure Student_SelectName(@ID int) As Begin Select Name From Student Where ID=@ID Return (@@RowCount) End همچنين با استفاده از دستور Return ميتوان يک مقدار از نوع Int را به خروجي فرستاد که ما در اينجا تعداد رکوردهاي حاصل از دستور Select را بازگشت داده ايم.
9 استفاده از پارامتر Outpout Create Procedure Student_SelectName (@ID int, @Name Nvarchar(50) Output) As Begin Select @Name=Name From Student Where ID=@ID Return(@@RowCount) End از نوع پارامتر Outputميباشد @Name و با استفاده از ا ن نام دانش ا موز به خروجي فرستاده داده خواهد شد.
10 استفاده از پارامتر اختياري Create Procedure Student_Insert (@NameNvarchar(50), @Sex bit = 1 ) As Begin Insert Into Student(Name,Sex) Values(@Name,@Sex) Return(@@Identity) End پارامتر @Sex اختياري ميباشد و درصورت عدم مقداردهي مقدار پيشفرض 1 براي ا ن در نظر گرفته خواهد شد. همچنين توسط دستور( Identity @@) Return مقدار ID رکوردي که در حال درج کردن ا ن هستيم بازگردانده ميشود @@identity به فيلدي که در جدول از نوع AutoNumber در نظر گرفته شده است اشاره ميکند
11 اجراي Stored Procedure در Storedديگر Procedure Create Procedure Student_SelectByName(@NameNvarchar(50) ) As Begin Return(Select Count(*) from StudentWhere Name=@Name ) End Create Procedure Student_MyQuery As Begin Declare @Result int Exec @Result=Student_SelectByName@Name= MohammadZavvar If (@Result=0) Exec Student_Insert@Name= MohammadZavvar Return(@@RowCount) End روال Student_MyQuery روال ديگري با نام Student_SelectByName را اجرا ميکند در صورتي که دانش ا موزي به نام MohammadZavvarوجود نداشته باشد دنش ا موزي با اين نام درج خواهد شد. لازم به ذکر است که اجراي SP با کلمه کليدي Exec انجام ميگردد
12 بهينه سازي Stored Procedure ها در نامهاي SPاز _ spاستفاده نكنيد. زيرا اين علامت مخصوص spهاي سيستمي موجود در جدول masterميباشد و هنگامي كه از اين اختصار استفاده ميكنيد سيستم ابتدا دنبال اين نام در جداول سيستمي ميگردد كه اين امر باعث کاهش سرعت اجراي sp خواهد شد. در داخل يك SPبهتر است به جاي اينكه داخل ا ن از دو دستور Select استفاده كرد هركدام را در داخل يك SPقرار داده و ا نرا به هنگام نياز اجرا كنيم. Cache در داخل Queryميتواند يك spفقط استفاده از اين روش اين است که در داخل هر دليل SQLقرار گيرد و اگر از چند دستور Sqlدر داخل يک SPاستفاده نماي يد هر دفعه كه اين SPاجرا شود مجدد كامپايل خواهد شد و اين امر موجب کاهش سرعت اجرا ميگردد. در SPهايي كه نياز نيست كاربر متوجه شود چه تعداد رديف تحت تاثير قرار گرفته است حتما در اول SPدستور Set NoCount On را بنويسيد. زيرا اگر اين دستور را ننويسيد هربار كه عملياتي صورت گرفته SQLتعداد رديفهاي تحت تاثير قرار گرفته را براي كاربر ارسال ميكند و همين باعث ايجاد ترافيك بين clientميشود., server
13 ويرايش دستورات Stored Procedure (...,نوع داده پارامتر دوم@, نوع داده پارامتراول@) نام روال Alter Procedure As Begin دستورات SQL End با استفاده از کلمه کليدي Alter ميتوانيم روال مورد نظر خود را ويرايش نماي يم
14 حذف Stored Procedure روال Drop Procedure نام با استفاده از کلمه کليدي Drop ميتوانيم روال مورد نظر خود را حذف نماي يم
15 ھا Trigger
16 Trigger چيست تريگر در واقع نوعي روال ذخيره شده است که در پاسخ به بروز يکي از شرايط زير اجرا خواهد شد Insert Delete Update اجراي Trigger انواع تريگري که بعد از رويداد Insert/Delete/Update اجرا ميشود و در تعريف ا ن از کلمه کليدي After استفاده ميگردد. (در نسخه هاي قديمي از کلمه For به جاي After استفاده ميشده است) تريگري که به جاي Insert/Delete/Update اجرا ميشود و در تعريف ا ن از کلمه کليدي Instead Of استفاده ميگردد
17 نحوه ايجاد Trigger وارد نرم افزار SQLشده Server و جهت درج Queryجديد اقدام ميکنيم. سپس به صورت زير Trigger مورد نظر را ايجاد مينماي يم. Create Trigger نام تريگر ON نام جدول After/InsteadOf Insert/Update/Delete As Begin دستورات SQL End
18 مشاهده Triggerهاي ايجاد شده جهت مشاهده Trigger هاي ايجاد شده ميتوانيد وارد Object نرم افزار SQLشويد Server و در پنجره Explorer از مسير زير اقدام فرماييد. DataBaeses Ł عنوان پايگاه داده Ł Tables Ł عنوان جدول Ł Triggers
19 جلوگيري از حذف رکورد توسط Trigger Create Trigger Student_NoDelete ON StudentInstead Of Delete As Begin Print Was Not Deleted End تريگر بالا روي جدول Student ايجاد ميشود و از نوع Instead Of Delete ميباشد. يعني زماني که دستور حذف يک رکورد از جدول Student داده شود به جاي دستور Delete تريگر بالا اجرا ميگردد و پيغام Was Not Deleted نمايش داده ميشود.
20 نمايش تعداد سطرهاي جدول Student پس از درج رکورد جديد Create Trigger Student_Insert ON StudentAfter Insert As Begin Select Count(*) FromStudent End تريگر بالا بعد از درج رکورد در جدول Student اجرا ميشود و تعداد سطرهاي جدول را برميگرداند.
21 نمايش رکوردهاي Delete شده Create Trigger Student_Delete ON StudentAfter Delete As Begin Select * From Deleted End تريگر بالا بعد از حذف رکورد در جدول Student اجرا ميشود و رکوردهاي حذف شده را نمايش ميدهد. لازم به ذکر است که در تريگرها پس از عمليات Delete رکوردهاي حذف شده در جدول Deleted و پس از عمليات Insert رکودهاي درج شده در جدول Inserted قرار ميگيرد.
22 نمايش رکوردهاي Update شده Create Trigger Student_Update ON StudentAfter Update As Begin Select * From Deleted End تريگر بالا بعد از بروزرساني رکورد در جدول Student اجرا ميشود و داده هاي قديمي(داده هاي قبل از بروزرساني) را نمايش ميدهد. لازم به ذکر است که در تريگرها پس از عمليات Update داده هاي قديمي در جدول Deleted و داده هاي جديد(داده هاي جايگزين شده) در جدول Inserted قرار ميگيرد.
23 جلوگيري از اعمال تغييرات روي پايگاه داده Create Trigger DataBase_MyTrigger ON DataBase After Drop_Table,Create_Table,Alter_Table,Create_Procedure, Alter_Procedure, Drop_Procedure, Drop_Trigger, Create_Trigger, Alter_Trigger, Drop_View, Create_View, Alter_View AS Begin شما نمي توانيد تغييراتي در پايگاه ايجاد کنيد ' PRINT ROLLBACk End تريگر بالا روي DataBase اجرا ميشود و مانع از انجام تغييرات در پاياه داده ميگردد
24 غير فعال کردن Trigger نام جدول ON نام تريگر DisableTrigger مثال : DisableTrigger Student_Delete ON Student فعال کردن Trigger نام جدول ON نام تريگر EnableTrigger مثال : Enable Trigger Student_Delete ON Student
25 Alter Table نام جدول Disable Trigger All Alter Table Student Disable Trigger All غير فعال کردن Trigger هاي يک جدول مثال : Alter Table نام جدول Enable Trigger All Alter Table Student Enable Trigger All فعال کردن Trigger هاي يک جدول مثال :
26 ويرايش Trigger Alter Trigger نام تريگر ON نام جدول After/InsteadOf Insert/Update/Delete As Begin SQL دستورات End با استفاده از کلمه کليدي Alter ميتوانيم تريگر مورد نظر خود را ويرايش نماي يم
27 حذف Trigger تريگر Drop Trigger نام با استفاده از کلمه کليدي Drop ميتوانيم تريگر مورد نظر خود را حذف نماي يم
28 منابع کتاب ا موزش SQLدر 21 ابراهيمي روز. نويسنده: رايان استنفز. مترجم: مهندس سيروسيان و مهندس کتاب ا موزش VB.NET در. Visual Studio.NET 2010 نويسنده: مهندس محسن مدحج