ساخت و استفاده از Stored Procedure در SQL Server
Loading...
Stored Procedure

این مقاله روش ساخت Stored Procedure و اجرای آنها را توضیح میدهد. این مقاله با ساخت یک Procedure ساده آغاز و سپس حرکت دادنش به سوی تکنیک Parameter passing و بازگرداندن مقادیر به فراخوان و … ادامه میابد.امید وارم مفید باشه و استفاده کنید.

معرفی

یک Stored Procedure در سرور SQL یک واحد برنامه ی قابل اجراست که میتواند به عنوان شئ در پایگاه داده ذخیره شود. این Stored Procedures میتواند از برنامه های کاربردی مشتری اجرا شود تا اجرای خوبی پیدا کند. Stored Procedure میتواند از صفحه های دیگر و یا حتی Stored Procedure های دیگر نیز خوانده شود.

ارتباط بین یک Stored Procedure سرور SQL و یک برنامه ی مشتری با استفاده از پارامتر ها و مقادیر بازگشتی روی میدهد. شکل زیر آن را نشان میدهد :

Stored Procedure

برنامه های مشتری در صورت دلخواه داده های ذخیره شده را در Stored Procedure ذخیره میکند و Stored Procedure پردازش ها را بر اساس داده های گرفته شده از برنامه مشتری انجام میدهد. پس از آنکه کار یکStored Procedure به اتمام رسید داده ها را به فراخوان برمیگرداند با استفاده از تکنیک های خروجی مانند :

فرستادن داده از طریق نوع پارامتر خروجی

فرستادن داده از طریق مقدار بازگشتی

فرستادن داده از طریق مقدار انتخابی

همه ی آن ها را در این مقاله خواهیم دید.

ساخت یک EXEC Stored Procedure در SQL

میتوانید یک Stored Procedure در SQL Server Management Studio بسازید. هرگاه Procedure ساخته شد در Programmablility node پایگاه داده لیست میشود و که Procedure ساخت مقدار اجرا شده است. برای اجرای Procedure از “EXEC” به همراه نام Procedure کنید.

ساختار Stored Procedure در شکل ۲ نشان داده شده است. زمانی که یک Procedure میسازیم نام آن در ابتدا به همراه یک یا دو پارامتر پاس داده شده به آن آورده میشود. پارامتر ها میتوانند انتخابی باشند. پس از پارامتر ها بدنه ی Procedure با توجه به عملیات های مورد نیاز نوشته میشود. بدنه میتواند شامل متغیرهای محلی که در آن اعلام شده اند باشد و این متغیر ها آشنا با Procedure ها هستند. به بیان دیگر آنها تنها میتوانند در داخل بدنه ی Procedure مشاهده شوند.

Stored Procedure در شکل ۳ در زیر یک Stored Procedure نمایش داده شده است که از همه بخش های انتخابی Procedure گذشته است. پس برای ساخت یک Procedure احتیاج به نام Procedure و حداقل یک مقدار به عنوان بدنه Procedure داریم. به خاطر داشته باشید که این تمرین خوبی است که Procedure را باSchema name qualifier بسازیم و اجرا کنیم.

Stored Procedure بدنه ی Procedure میتواند هر نوع مقدار SQL را دارا باشد مثل ساخت یک جدول , افزودن یک یا تعداد بیشتری ردیف های جدولی , تنظیم رفتار پایگاه داده و غیره. هرچند بدنه ی Procedure به عملیات های معینی محدود شده است. برخی از مهمترین محدودیت ها در زیر آورده شده اند :

بدنه نباید Stored Procedure های دیگری بسازد.

بدنه نباید View بسازد.

بدنه نباید هیچ Trigger ای بسازد.

ویدیوی زیر ساخت و اجرای یک Stored Procedure با استفاده از SQL Server Management Studio را نشان میدهد.

 

اعلام متغیرها در بدنه ی Procedure

شما میتوانید یک متغیر محلی در بدنه ی Procedure اعلام کنید و این متغیر ها درون بدنه ی Procedure قابل مشاهده هستند. تمرین خوب این است که متغیر های را در ابتدای Stored Procedure اعلام کنید. همانطور که در شکل ۲ نشان داده شده است. اما شما میتوانید متغیر های را در هرجای بدنه ی Stored Procedure اعلام نمایید. به مثال Procedure داده شده ی زیر نگاه کنید :

Stored Procedure در Procedure بالا میتوانید متغیر های Name1 و Name2 راکه در ابتدای Procedure اعلام شده اند را ببینید. در اینجا میتوانید ببینید که چندین متغیر در یک خط اعلام شده اند و هر اعلام با یک ویرگول از بعدی جدا شده است. همچنین به یاد داشته باشید متغیر ها دارای پیشوند @ هستند.در بدنه ی Procedure شما میتوانید یک متغیر را در هرجایی که خواستید اعلام کنید و در مثال متغیر @Name3 در انتهای بدنه ی Procedure اعلام شده است. برای نسبت دادن یک مقدار به یک متغیر اعلام شده یک سری از مقادیر استفاده میشوند و این کار در مثال با مقدار ۳ علامت زده شده است. برخلاف اعلام بیش از یک متغیر در یک خط تنها یک سری مقدار در یک خط مجاز میباشد. نتیجه ی اجرای Procedure بالا در زیر نمایش داده شده است :

Stored Procedure هی ! چطوری من میتونم چندین مقدار را در یک مقدار در بدنه ی Procedure نسبت دهم؟ خب. جواب استفاده از “Select Var=value” میباشد و شما میتوانید آن را با استفاده از ویرگول بصورت جدا استفاده کنید. تصویر زیر این کار را نمایش میدهد:

Stored Procedure پارامتر های SQL Stored Procedure

در مثال قبلی ساخت و اجرای یک Stored Procedure ساده را دیدیم. یک Procedure میتواند پارامترها را بپذیرد و فراخوان Procedure مقادیر را به آن پاس میدهد (اما نه همیشه ; به زودی آن را خواهیم دید). بر اساس مقادیر پاس داده شده Procedure کار های مرتبط با آن را در بدنه انجام میدهد. به عنوان مثال بگذارید یک Procedure که شهر و ایالت را از فراخوان میگیرد و تعداد نویسندگان به آن شهر و ایالت را بازمیگرداند. Procedure برای اجرای این شمارش نویسندگان جدول نویسندگان در پایگاه داده را مورد بررسی قرار میدهد. برای دریافت این پایگاه داده آنها را گوگل کنید یا SQL Script آنها را از صفحه ی SQL2005 دانلود نمایید. زیر مثال این Procedure آورده شده است :

Stored Procedure در مثال Procedure بالا Procedure دو پارامتر به نام های @State و @City تحت علامت ۱ میپیذیرد. نوع داده متناسب با نوع مشخص شده در جدول مشخص کننده برای اطمینان است. بدنه ی Procedure دارای مقدار داخلی @TotalAuthors میباشد و این متغیر برای نمایش تعداد نویسنگان استفاده میشود. پارامتر های پاس داده شده برای تشکیل “WHERE” یک شاخه ی انتخاب جستجو که تعداد نویسندگان را میشمارد, استفاده میشوند. این تحت علامت ۲ مشخص شده است. در نهایت مقدار شمرده شده در پنجره ی خروجی با استفاده از مقدار Print چاپ خواهد شد و خروجی نیز با علامت ۵ مشخص شده است.

دو راه برای اجرای Procedure وجود دارد. راه اول که با علامت ۳ نیز مشخص شده است نشان دهنده ی پاس دادن پارامتر ها با لیست ویرگول جداکننده بعد از نام Procedure است. در مثال ما مقدار CA و Berkeley را که توسط یک ویرگول جدا شده اند را پاس میدهیم. این مقادیر توسط متغیر های پارامتر Procedure , @State و @City جمع آوری میشوند. در این متد ترتیب پاس دادن پارامتر ها مهم است. اولین تکنیک “Passing Argument by Ordinal Position” نام دارد. در روش دوم که با علامت ۴ مشخص شده است پس از نام Procedure پارامتر ها به صورت مستقیم نسبت داده میشوند و در این زمینه ترتیب اهمیتی ندارد. این تکنیک دوم به نام “Passing Named Argument” شناخته میشود. این در ویدیوی زیر توضیح داده شده است :

http://www.c-sharpcorner.com/UploadFile/6897bc/executing-stored-proc-with-parameters/

تصویر زیر نمایش دهنده ی ساخت یک Stored Procedure با پارامتر های پیش فرض و اجرای آن است. Procedure مشابه همان Procedure ساخته شده در مثال قبل است و تنها پارامتر ها تغییر کرده اند. آنها پارامتر های @City در ایتدا و @State پس از آن با مقدار پیش فرض می آیند. پارامتر پیش فرض تحت علامت ۱ مشخص شده است. به اجرای Procedure نگاه کنید (۲و۳). و در اولین اجرا (۳) ما هر دو پارامتر را پاس داده ایم. در این زمینه پارامتر های ارائه شده “UT” با مقدیر پیش فرض “CA” جایگزین میشوند. در دومین اجرا ما تنها یک مقدار آرگومان “Covelo” برای پارامتر @City پاس میدهیم و پارامتر @State مقدار پیش فرض “CA” را درافت میکند. تمرین خوبیست که همه ی پارامتر های پیش فرض را تا انتهای لیست پارامتر ها داشته باشیم. در غیر اینصورت اجرای علامت ۲ امکان پذیر نیست و شما باید با پاس دادن آرگومان Named پیش بروید.

Stored Procedure داده ی بازگشتی SQL Stored Procedure

۳ راه مهم برای ارسال داده به فراخوان Stored Procedure در زیر آمده است:

مقدار بازگشتی Stored Procedure

پارامتر های خروجی Stored Procedure

انتخاب از Stored Procedure

به هر یک از این تکنیک ها جدا جدا نگاهی خواهیم انداخت.

مقدار بازگشتی SQL Stored Procedure

در این تکنیک Procedure یک مقدار را به متغیر محلی نسبت میدهد و آن را باز میگرداند. یک Procedure میتواند مستقیما یک مقدار ثابت را نیز برگرداند. در مثال زیر یک Procedure ساخته ایم که میتواند تعداد کل نویسندگان را برگرداند. زمانی که شما این Procedure را با قبلی مقایسه میکنید میتوانید ببینید که مقدار چاپی با مقدار بازگشتی جایگزین شده است.

Stored Procedure اوکی . حالا بیاید ببینیم که چگونه Procedure را اجرا کنیم و مقدار بازگشتی توسط Procedure را چاپ کنیم. اجرای Procedure نیاز به اعلام متغیر ها و چاپ آن بعد از اجرای Procedure دارد. به یاد داشته باشید که به جای چاپ مقدار میتوانید از مقدار انتخابی مانند “Select @RetValue as OutputValue” استفاده کنید. کد زیر نشان دهنده ی هر دو راه چک کردن Procedure و بازگرداندن مقدار میباشد :

Stored Procedure پارامتر خروجی SQL Stored Procedure

مقدار بازگشتی میتواند مورد استفاده باشد برای بازگرداندن یک متغیر که آن را ما در مثال قبل دیدیم. با استفاده از نوع پارامتر های خروجی یک Procedure میتوان یک یا تعداد بیشتری مقادیر متغیر ها را به فراخوان فرستاد. پارامتر های خروجی با نام کلیدی “Output” مشخص میشوند هرگاه که Procedure ساخته میشود.زمانی که یک پارامتر به عنوان یک پارامتر خروجی تعیین میشود بدنه ی Procedure باید مقداری به آن اختصاص دهد. نگاهی به مثال Procedure زیر بیاندازید:

Stored Procedure رمزیدر Procedure بالا دو پارامتر خروجی با نام های @TotalAuthors و @TotalNoContract در لیست پارامتر ها مشخص شده اند. متغیر ها , مقادیر را در داخل بدنه دریافت میکنند. زمانی که ما از پارامتر های خروجی استفاده میکنیم , فراخوان میتواند مقدار اختصاص داده شده را در داخل بدنه Procedure ببیند. عکس های زیر اجرای یک Procedure و دریافت مقادیر با استفاده از پارامتر های خروجی را نشان میدهد :

Stored Procedure در Script بالا دو متغیر تعیین شده اند تا مقادیر جمع شده توسط Stored Procedure را در پارامتر های خروجی ببیند و این با ۱ علامتگذاری شده است. سپس Procedure با تهیه ی مقادیر پارامتر های طبیعی CA و Berkeley اجرا میشود. سومین و چهارمین پارامتر ها پارامتر های خروجی هستند و از این رو متغیر های مشخص شده ی @OutputVar1 و @OutputVar2 به Procedure پاس داده میشوند(با علامت ۲). به خاطر داشته باشید که هنگام پاس دادن متغیر ها کلمه ی خروجی output نیز در اینجا مشخص است.هرگاه Procedure با موفقیت اجرا شود , مقادیر با استفاده از پارامتر های خروجی چاپ شده در پنجره ی پیام بازمیگردد و این توسط علامت ۳ مشخص شده است.

انتخاب از Stored Procedure

تکنیک ” انتخاب از” برای بازگرداندن یک سری مقادیر در فرم جدول داده ها به فراخوان Stored Procedure به کار میرود. در مثال پایینStored Procedure پارامتری با نام @AuthID را دریافت میکند و جدول نویسندگان را با فیلتر کردن سوابق بازگشتی توسط این پارامتر @AuthId , جستجو میکند. مقدار انتخابی با ۱ در شکل زیر علامتگذاری شده است که تصمیم میگیرد به چه چیز هایی برای بازگشت به فراخوان Stored Procedure نیاز دارد. هرگاه Stored Procedure در حال اجرا باشد , “Author Id” پاس داده خواهد شد(علامت با ۲). نتیجه ی اجرای Stored Procedure با ۳ علامتگذاری شده است. مثال Procedure در اینجا همیشه تنها یا یک مقدار برمیگرداند یا هیچ مقداری. اما یک Stored Procedure هیچ محدودیتی برای برگرداندن بیش از یک مقدار را ندارد. به شکل ۶ نگاه کنید که بازگشت یک داده با استفاده از یک انتخاب شامل متغیر های محاسباتی را نشان میدهد.

Stored Procedure رمزیجمع بندی

یک Stored Procedure در مقایسه با یک تابع در اتمام کاری مشابه قدرتمندتر است. از آنجایی که Stored Procedure خودش در سرور اجرا میشود , میتوان از تغییرات داده ای در مقادیر بسیار بزرگی بین سرور و برنامه مشتری (برای محاسبات معین) پیشگیری کرد. در این مقاله ترکیب داده های تکنیک بازگشتی (برای مثال یک Procedure با استفاده از یک مقدار بازگشتی تحت عنوان پارامتر خروجی داده ها را بازمیگرداند) نشان داده نشد. اما شما میتوانید خودتان آن را بیاموزید. تا بعد خدانگهدار !



avatar داریوش فرخی

داریوش فرخی هستم از سال 92 شروع به یادگیری برنامه نویسی و از سال 93 در بخش برنامه نویسی و تولید محتوای سایت mspsoft.com مشغول هستم. فعالیتم نیز بیشتر در زمینه های برنامه نویسی با سی شارپ و asp.net بوده است. اوقات فراغتم را هم غالبا با تماشای فیلم یا بازی های کامپیوتری پر میکنم .

آخرین مطالب و تخفیفات در کانال تلگرام :) کانال تلگرام ام اس پی سافت
مطالب مرتبط
ديدگاه خود را ارسال کنيد


۸ دیدگاه رو شما می توانید ببینید
  1. باسلام
    بسیار عالی بود متشکر

  2. احسنتم
    بسیار متشکر عزیزم
    ثواب آخرت بشود نسیت

  3. جامع و عالی خیلی ممنون از سایت خوبتون

  4. خیلی مفید بود خدا خیرتون بده ، امیدوارم همیشه از این کارا بکنید

  5. ممنون ….واقعا عالی بود .. من گیر کوچیکی توی کارم بود که ساعتها منو معطل کرده بود که با این پست اشکال کارم رو پیدا کردم. یک دنیا سپاسگزارم

  6. ممنونم عالی بود

محبوب ترين ويدئو هاي انلاين
دوره برنامه نویسی فروشگاه اینترنتی
  • تعداد اعضا 80k
  • قيمت دوره۱۰۰,۰۰۰ تومان
  • امتيازدهي
    1 vote, average: 5٫00 out of 51 vote, average: 5٫00 out of 51 vote, average: 5٫00 out of 51 vote, average: 5٫00 out of 51 vote, average: 5٫00 out of 5( 5٫00 از 1 رای )
    Loading...
دوره آموزشی سیستم ثبت سفارش آنلاین
  • تعداد اعضا 80k
  • قيمت دوره۵۰,۰۰۰ تومان
  • امتيازدهي
    1 vote, average: 5٫00 out of 51 vote, average: 5٫00 out of 51 vote, average: 5٫00 out of 51 vote, average: 5٫00 out of 51 vote, average: 5٫00 out of 5( 5٫00 از 1 رای )
    Loading...
دوره طراحی سیستم مدیریت مشتریان
  • تعداد اعضا 80k
  • قيمت دوره۵۰,۰۰۰ تومان
  • امتيازدهي
    1 vote, average: 5٫00 out of 51 vote, average: 5٫00 out of 51 vote, average: 5٫00 out of 51 vote, average: 5٫00 out of 51 vote, average: 5٫00 out of 5( 5٫00 از 1 رای )
    Loading...