ایجاد sproc ، شکل کلی

ایجاد sproc هم شباهت زیادی به سایر اجزای بانک اطلاعاتی دارد . حتماً شما هم متوجه شده ایدکه در SQL SERVER هر وقت صحبت از" ایجاد " یک شي بانک اطلاعاتی می شود پای جمله معروف CREATE به میان می آید . شکل کلی ایجاد sproc عبارت است.

CREATE PROCEDURE|PROC
[<نوع داده><نام پارامتر>[NARING][=<مقدار پیش فرض >][CUTPUT][و
<نوع داده><نام پارامتر>[VARYING][=<مقدار پیش فرض>][OUTPUT][و
...
...
]]
[WITH
RECOMEPILE|ENCRYPTION|RECOMPILE,ENCRYPTIONo]
[FOR REPLICTION]
AS

GO

در این جمله پس از CREATE هم می توانید از PROCEDURE و هم از PROC استفاده کنید. عملکرد هر دو مشابه است . و هیچ تفاوتی میان آنها نیست .پس از آن باید نام SPROC را بیاورید . قواعد نامگذاری آن ها هم دقیقاً مانند سایر اجزا ء بانک اطلاعاتی است .

پس از نام می توانید یک سری پارامتر اختیاری را تعیین کنید .پس از آن باید کلمه کلیدی AS آورده شود و در ادامه کد اصلی SPROC نوشته شود .

مثالی از یک SPROC ساده و اولیه

شاید ساده ترین وبهترین مثال SPROC ، استخراج تمام ردیف ها و ستون های یک جدول باشد . یعنی همه چیز بر اساس داده های یک جدول انجام شود . برای انجام این کار به یک جمله SELECT * FROM خیلی ساده نیاز داریم . کافی است این جمله را در بخش کد اصلی از جمله CREATE قرار دهیم :

USE Northwind
GO
CREAT proc spshippers
AS
SELECT*FROM shippers

حتماً تعجب کرده اید که چرا من GO را قبل از جمله CREATE قرار داده ام . دلیل این موضوع این است که اکثر جملات CREATE که برای ایجاد اجزاء بانک اطلاعاتی می نویسیم نمی توانند با سایر جملات در یک دسته قرار گیرند و حتماً باید خودشان به طور جداگانه در یک دسته باشند .

بنابراین اگر GO را بین USEو جمله CREATE SPROC قرار ندهید با خطا مواجه خواهید شد .

با اجرای کد فوق در Query Analyzer اولین sproc خودتان را ایجاد کرده اید . اکنون می خواهیم این sproc را ایجاد کنیم برای این کار از EXEC استفاده می کنیم .

EXEC Spshippers

نتیجه ای که برگردانده می شود دقیقاً حاصل اجرای جمله SELECT داخل sproc است .

Shiper ID companyname phone
1 speady express (503)555-9831
2 united package (503)555-9831
3 Federal shipping (503)555-9831
(3 row(s)effected)

مزایای استفاده از sproc و بهينه كردن آن

اکنون که مقداری از زمان خود را صرف نحوه ایجادsproc ها و همچنین اجرای آنها کردیم . ممکن است سؤالی برای ما پیش آید که چرا باید از sproc استفاده کنیم . بعضی از دلایل اساسی هستند اما یک سری دلایل دیگر خصوصاً اگر با دنیای RDBMS تازه آشنا شده اید دیر به یاد آورده می شوند. مزایای اصلی SPROC ها عبارتند از:

● ایجاد برنامه های قابل فراخوانی

● امنیت

● کارایی

ایجاد فرایند های قابل فراخوانی

SPROC شباهت زیادی به کد ها و برنامه های معمولی در بانک اطلاعاتی شما دارد . اما مزیت مهمی که SPROC دارداین است که چون خودش یکی از شيء های بانک اطلاعاتی است می توان آن را فراخوانی کرد و دیگر لازم نیست که آن را از یک فایل بخوانید و اجرا کنید .

هر SPROC می تواند SPROC های دیگر را فراخوانی کند (فراخوانی تودر تو) در SQL SERVER 2000 می توان تا 32 سطح فراخوانی تو در تو پیش رفت . این امکان هم قابلیت های سایر زبان های برنامه نویسی را در اینجا نیز به شما می بخشد و از نوشتن کد های تکراری جلوگیری می کند . در سایر زبان ها یک کد را در یک روتین جداگانه می نوشتید سپس هر جا که لازم می دانستید آن را فراخوانی می کردید . در اینجا هم دقیقاً به همان صورت کار می کنید . نحوه فراخوانی یک SPROC در داخل SPROC دیگر دقیقاً مانند فراخوانی SPROC از داخل یک برنامه است که نمونه های آن را بار ها دیده اید . به عنوان یک مثال یک SPROC ایجاد می کنیم که عملکرد آن همانند برنامه آزمایشی است . این SPROC جدید ، SPROC دیگرمان یعنی spinsertdatevalidated order را فراخوانی می کند :

Use northwind
GO
CREATE PROC aptestInsert
@MyDate smalldatetime
AS
DECLARE @myIdent int
DECLARE @Return int
EXEC @ Return=spInsertDate Validatedorder
@customerID='ALFKI'
@employeeID=5,
@orderDate=@MyDate,
@shipvid=3,
@Freight=500,
OrderID=@MyIdent output
IF@Return=0
SELECT orderID,@customerID,@EmployeeID,
@orderDate,@shipName
FROM order
WHERE order ID= My Ident
ELSE
PRINT "Error Returned was "+ convert (varchar, @ Return)

اکنون کافی است آن را یکبار با تاریخ خوب و یکبار هم با تاریخ بد فراخوانی کنیم .ابتدا تاریخ خوب ؛

DECLARE @ Today= small date time
SELECT @ Today=GETDATED
EXEC SP Test Insert
@MY Date=@Today

استفاده از تاریخ امروز همان نتیجه ای را خواهد داشت که خودمان هم انتظارش داریم :

The Time of Day in order Date was truncated
(1 row(s) affected)
Order ID customer Employ order Date shipman
00:00:00:000 NULL 2000-09-18 6 ALFK 11097
(1ROW(S)affected)

و سپس یک تاریخ بد :

EXEC spTestInsert'1/1/1999'

باز هم همان نتیجه ای که انتظارش را داشتیم – این بار یک پیغام خطا برگردانده می شود .

Msg 60000, level 1 , state 60000
Jan 1 1999 is not a valid order date
Order date must be within 7days of current date.
Error Returned was 60000

توضیح اینکه متغییر های محلی در فراخوانی های تو در تو هیچ تغییری نمی کنند و در داخل هر sproc محلی به حساب می آیند . یعنی شما می توانید در پنج تا sproc مختلف متغییری به نام @ MYdate داشته باشید ولی همه آن ها از همدیگر مستقل هستند و در سطح خودشان محلی به حساب می آیند . در یک کلام اینکه در اینجا متغییر های عمومی در سطح چند sproc نداریم .

استفاده از SPROC برای امنیت

بسیاری از افراد به نقش SPROC به عنوان ابزاری برای امنیت به خوبی پی نبرده اند . شبیه VIEW ها، می توانیم یک SPROC هم ایجاد کنیم که یک مجموعه رکورد را به عنوان خروجی به ما بدهد . بدون اینکه کاربر مجوز دسترسی به جدول پایه را داشته باشد . وقتی به یک کاربر حق دسترسی و اجرای یک SPROC را می دهیم ، در حقیقت به او اجازه انجام همه آنچه در داخل SPROC است را داده ایم . منظور از هر آنچه در داخل SPROC است همان کارها یی است که SPROC قرار است انجام دهد یعنی اینکه وقتی به یک نفر حق اجرای یک SPROC را که تمام رکورد های جدول customers را نداشته باشد هم این SPROC به درستی اجرا می شود و تمام رکورد های customers برگردانده می شوند. در حالیکه اگر همان کاربر با یک SELECT ساده بخواهد این رکورد را دریافت کند SQL SERVER در خواست او را رد می کند .

چیزی که به اصطلاح در این مقوله خیلی به درد می خورد این است که می توانیم به یک کاربر امکان تغییر داده ها از طریق SPROC را بدهیم . سپس با ابزار های مختلف نظیر نرم افزار های EXCEL,ACCESS و هر ابزار دیگری که امکان تهیه گزارش دارد به SQL SERVER متصل شوند و گزارش های سفارشی خودشان را ایجاد کنند.بنابراین ملاحظه می کنید که SPROC دست شما را در بالا بردن ضریب امنیت داده های بانک اطلاعاتی به میزان زیادی باز می کند و انعطاف پذیری زیادی به تعیین امنیت داده ها می دهد.

SPROC ها و کارایی

به طور کلی SPROC ها کمک قابل توجهی به کارایی سیستم شما می کنند . البته لازم به یاد آوری است که هیچ تضمینی بر بالا رفتن کارایی سیستم در صورت استفاده از SPROC وجود ندارد زیرا ممکن است SPROC طوری نوشته شود که سرعت آن بسیار کم بوده و باعث کاهش سرعت و کارایی سیستم شوند . اما در صورت استفاده صحیح از SPROC ها می توان کارایی سیستم را بهبود بخشید .

اما، واقعاً این کارایی یا بهتر بگویم ، بهبود کارایی ناشی از چیست ؟ چه چیزی باعث آن می شود ؟ وقتی یک SPROC ایجاد می کنیم ، فرایند آن طبق متن زیر کار می کند:

ما کار را با اجرای جمله CREATE PROC شروع می کنیم . این جمله ابتدا پرس و جوی داخل SPROC را تجزیه می کند تا ببیند آیا درست کار خواهد کرد یا نه . ممکن است سؤال کنید چه تفاوتی میان اجرای پرس و جو به طور مستقیم با اجرای آن از طریق یک SPROC وجود دارد ؟ نکته ای که بایستی به آن توجه شود این است که دستور CREATE PROC می تواند از چیزی به نام Differred name resolution استفاده کند . کار Differred name resolution این است که به شما اجازه می دهد در SPROC یک سری اجزای بانک اطلاعاتی که هنوز آن ها را ایجاد نکرده اید را هم ذکر کنید ، دقت کنید که در موقع اجرا باید تمام شی ء هایی که در SPROC استفاده کرده ایدایجاد شده وآماده باشند. در غیر این صورت با خطا متوقف خواهید شد .

پس اینکه SPROC ایجاد شد ، SQL SERVER نام آن را در جدول سیستمی sysobjects و متن آن را در جدول سیستمی sys comment قرار می دهد . پس از این مرحله ، SPROC منتظر می نشیند تا وقتی که اولین اجرای آن فرا برسد . در این موقع ، SPROC بهینه سازی می شود و یک طرح پرس و جو روی سیستم ترجمه می شود و در حافظه نهان قرار می گیرد . در دفعات بعدی که SPROC اجرا می شود در صورتی که با پارامتر Recompile with اجرا نشود به جای ایجاد یک طرح پرس و جوی جدید، از همان طرح پرس و جوی قبلی که در حافظه نهان قرار گرفته است استفاده می کند . این ویژگی باعث افزایش سرعت سیستم می شود . این به این معناست که یکبار که SPROC اجرا شد در دفعات بعدی نیاز به بهینه سازی و ترجمه آن نیست . اینکه این ویژگی چقدر یا در واقع چند ثانیه صرفه جویی زمان دارد بستگی به عواملی همچون درجه پیچیدگی دسته کد ، اندازه جدول هایی که دسته کد به آنها اشاره می کند و با آنها کار می کند و تعداد ایندکسهای روی جدول دارد.