آخرین بروزرسانی: 08/14/2017

غالباً ، یک عملیات داده مجموعه ای از دستورالعمل ها را نشان می دهد که باید در یک دنباله خاص تکمیل شوند. به عنوان مثال ، هنگام افزودن خرید محصول ، باید داده ها را در جدول سفارش وارد کنید. با این حال ، قبل از این ، باید بررسی کنید که کالاهای خریداری شده در انبار موجود هستند یا خیر. شاید لازم باشد تعدادی از شرایط اضافی را بررسی کنید. یعنی در حقیقت فرایند خرید یک محصول چندین عمل را در بر می گیرد که باید در یک سکانس خاص انجام شود. و در این حالت ، بهتر می توان همه این اقدامات را در یک جسم محصور کرد - روش ذخیره شده (روش ذخیره شده)

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

همچنین ، روشهای ذخیره شده به شما امکان می دهد دسترسی به داده ها را در جداول محدود کنید و از این طریق احتمال انجام اقدامات ناخواسته عمدی یا ناخودآگاه نسبت به این داده ها کاهش می یابد.

و جنبه مهم دیگر عملکرد است. روشهای ذخیره شده معمولاً سریعتر از عبارات SQL معمولی انجام می شوند. دلیل این امر این است که کد روش یک بار برای اولین بار اجرا می شود و سپس به صورت کامپایل ذخیره می شود.

برای ایجاد یک روش ذخیره شده ، از دستور CREATE PROCEDURE یا CREATE PROC استفاده کنید.

بنابراین ، یک روش ذخیره شده دارای سه ویژگی اصلی است: ساده سازی کد ، امنیت و عملکرد.

به عنوان مثال ، فرض کنید یک جدول در بانک اطلاعاتی وجود دارد که داده های مربوط به محصولات را ذخیره می کند:

تولید محصولات جدول (شناسه بین المللی اولیه کلید اصلی ، نام محصول NARARCHAR (30) نه NULL ، تولید کننده NVARCHAR (20) NULL ، ProductCount INT DEFAULT 0 ، قیمت پول NULL نیست.

یک روش ذخیره شده برای استخراج داده ها از این جدول ایجاد کنید:

استفاده از productsdb؛ GO CREATE PROCEDURE ProductSummary AS SELECT نام محصول به عنوان محصول ، تولید کننده ، قیمت از محصولات

از آنجا که دستور CREATE PROCEDURE باید در یک بسته جداگانه فراخوانی شود ، پس از دستور USE که پایگاه داده فعلی را تنظیم می کند ، از دستور GO برای تعیین بسته جدید استفاده می شود.

نام روش باید با کلید واژه AS دنبال شود.

برای جدا کردن بدنه رویه از بقیه اسکریپت ، کد روش اغلب در قسمت BEGIN ... END قرار می گیرد:

استفاده از productsdb؛ GO CREATE PROCEDURE ProductSummary AS BEGIN SELECT نام محصول به عنوان محصول ، تولید کننده ، قیمت از محصولات End؛

پس از افزودن روال ، می توانیم آن را در گره بانک اطلاعاتی در استودیوی مدیریت SQL سرور در زیرنود مشاهده کنیم قابلیت برنامه نویسی -\u003e رویه های ذخیره شده:

و ما قادر خواهیم بود که این روش را نیز از طریق رابط تصویری کنترل کنیم.

اجرای رویه

برای اجرای روش ذخیره شده ، دستور EXEC یا EXECUTE خوانده می شود:

EXEC ProductSummary

روش حذف

برای حذف یک روش ، از دستور DROP PROCEDURE استفاده کنید:

DROP PROCEDURE ProductSummary

1. برای مطالعه اپراتورهای توصیف مراحل ذخیره شده و اصول انتقال پارامترهای ورودی و خروجی آنها.
2. بیاموزید که چگونه مراحل ذخیره شده را در MS SQL Server 2000 ایجاد و اشکال زدایی کنید.
3. پنج روش اصلی ذخیره شده برای پایگاه داده آموزش کتابخانه تهیه کنید.
4. گزارش پیشرفت را بصورت الکترونیکی تهیه کنید.

1. اطلاعات عمومی در مورد روشهای ذخیره شده

روش ذخیره شده مجموعه ای از دستورات است که بر روی یک سرور ذخیره شده و به طور کلی اجرا می شود. روشهای ذخیره شده مکانیزمی است که با استفاده از آن می توانید روالهایی ایجاد کنید که روی سرور اجرا شده و توسط فرآیندهای آن کنترل می شوند. چنین روالهایی توسط برنامه فراخوانی فعال می شوند. علاوه بر این ، آنها می توانند با قوانینی که یکپارچگی داده ها را حفظ می کنند یا با محرک ها ایجاد شوند.

روشهای ذخیره شده می توانند مقادیر را برگردانند. در این روش می توانید مقادیر وارد شده توسط کاربر را با اطلاعات از قبل نصب شده در سیستم مقایسه کنید. روشهای ذخیره شده به راه حلهای سخت افزاری قدرتمند SQL Server کمک می کنند. آنها جهت یابی پایگاه داده هستند و از نزدیک با SQL Server Optimizer همکاری می کنند. این به شما امکان می دهد هنگام پردازش داده ها از کارایی بالایی برخوردار شوید.

می توانید مقادیر را به رویه های ذخیره شده منتقل کنید و نتایج کار را از آنها دریافت کنید ، که لزوماً به کاربرگ مربوط نمی شوند. یک روش ذخیره شده می تواند نتایج را در حین کار محاسبه کند.

دو نوع روش ذخیره شده وجود دارد: معمولی و تمدید شده. رویه های ذخیره شده منظم مجموعه ای از دستورات Transact-SQL هستند ، در حالی که رویه های ذخیره شده طولانی به عنوان کتابخانه های پویا (DLL) ارائه می شوند. چنین رویه هایی برخلاف روشهای معمولی ، پیشوند xp_ دارند. سرور دارای یک مجموعه استاندارد از مراحل پیشرفته است ، اما کاربران می توانند رویه های خود را به هر زبان برنامه نویسی بنویسند. نکته اصلی استفاده از رابط برنامه نویسی است API خدمات داده باز سرور SQL. مراحل ذخیره شده طولانی فقط می تواند در بانک اطلاعاتی Master باشد.

روشهای ذخیره شده معمولی نیز می توانند به دو دسته تقسیم شوند: سیستمیک و سفارشی. رویه های سیستم روش های استاندارد ای هستند که برای عملکرد سرور خدمت می کنند. کاربر - هر روش ایجاد شده توسط کاربر.

1.1. مزایای رویه های ذخیره شده

در حالت کلی ، روشهای ذخیره شده دارای مزایای زیر هستند:

• عملکرد بالا این نتیجه محل انجام مراحل ذخیره شده روی سرور است. به طور معمول سرور یک دستگاه قدرتمند تر است ، بنابراین زمان اجرای رویه روی سرور بسیار کوتاه تر از ایستگاه کاری است. علاوه بر این ، اطلاعات از پایگاه داده و روش ذخیره شده در یک سیستم هستند ، بنابراین ، تقریباً هیچ زمان برای انتقال سوابق از طریق شبکه طول نمی کشد. رویه های ذخیره شده دسترسی مستقیم به پایگاه های داده دارند ، که باعث می شود کار با اطلاعات بسیار سریع انجام شود.
• مزیت توسعه یک سیستم در معماری مشتری-سرور. این شامل امکان ایجاد جداگانه نرم افزارهای مشتری و سرور است. این مزیت در توسعه مهم است و به لطف آن ، می توانید زمان لازم برای تکمیل پروژه را به میزان قابل توجهی کاهش دهید. کد در حال اجرا روی سرور می تواند جداگانه از کد سمت مشتری ایجاد شود. در عین حال ، مؤلفه های سمت سرور می توانند توسط اجزای سمت مشتری به اشتراک گذاشته شوند.
• سطح امنیتی روشهای ذخیره شده می توانند به عنوان ابزاری برای بهبود امنیت عمل کنند. می توانید رویه های ذخیره شده ایجاد کنید که عملیات اضافه کردن ، اصلاح ، حذف و نمایش لیست ها را انجام دهند و بدین ترتیب کنترل هر جنبه دسترسی به اطلاعات را بدست آورند.
• تقویت قوانین سرور برای کار با داده ها. این یکی از مهمترین دلایل استفاده از موتور پایگاه داده هوشمند است. روشهای ذخیره شده به شما امکان می دهد قوانین و منطق دیگری را برای کمک به کنترل اطلاعات وارد شده به سیستم اعمال کنید.

اگرچه SQL به عنوان غیر روال تعریف می شود ، SQL Server از کلمات کلیدی مربوط به کنترل پیشرفت مراحل استفاده می کند. این کلمات کلیدی برای ایجاد رویه هایی استفاده می شود که می توانید برای اجرای بعدی ذخیره کنید. از روشهای ذخیره شده می توان به جای برنامه های ایجاد شده با استفاده از زبان های برنامه نویسی استاندارد (به عنوان مثال C یا Visual Basic) و انجام عملیات در پایگاه داده SQL Server استفاده کرد.

روشهای ذخیره شده در ابتدا اجرا و در جدول سیستم پایگاه داده فعلی ذخیره می شوند. هنگام تدوین ، آنها بهینه می شوند. در این حالت ، بهترین راه برای دسترسی به اطلاعات جدول انتخاب شده است. چنین بهینه سازی موقعیت واقعی داده ها را در جدول ، فهرست های موجود ، بارگذاری جدول و غیره در نظر می گیرد.

روشهای ذخیره شده کامپایل شده می توانند عملکرد سیستم را به میزان قابل توجهی بهبود بخشند. با این وجود شایان ذکر است که آمار داده ها از لحظه ایجاد روال تا لحظه اجرای آن منسوخ می شود و ایندکس ها می توانند ناکارآمد شوند. و اگرچه می توانید آمارها را به روز کنید و ایندکس های جدید و کارآمدتری را اضافه کنید ، نقشه رویه از قبل تهیه شده است ، یعنی این روش تدوین شده است ، و در نتیجه ، ممکن است دیگر روش دسترسی به داده ها مؤثر نباشد. بنابراین ، امکان جبران مجدد مراحل با هر تماس وجود دارد.

از طرف دیگر ، دوباره بارگذاری مجدد زمان لازم است. بنابراین ، مسئله اثربخشی بازپرداخت یک رویه یا یک برنامه یک زمانه برای اجرای آن بسیار ظریف است و برای هر مورد خاص باید جداگانه در نظر گرفته شود.

روشهای ذخیره شده را می توان یا بر روی دستگاه محلی یا بر روی سیستم SQL Server از راه دور انجام داد. این امر باعث می شود تا فرآیندهای موجود در سایر دستگاه ها فعال شود و نه تنها با پایگاه داده های محلی بلکه با اطلاعات در مورد چندین سرور نیز کار کنید.

برنامه های کاربردی نوشته شده در یکی از زبان های سطح بالا مانند C یا Visual Basic .NET همچنین می توانند مراحل ذخیره شده را بنامند که این راه حل بهینه برای تعادل بار بین نرم افزار سمت مشتری و سرور SQL است.

1.2 ایجاد روالهای ذخیره شده

برای ایجاد یک روش ذخیره شده ، از عبارت Create Procedure استفاده کنید. نام روش ذخیره شده می تواند حداکثر 128 کاراکتر باشد ، از جمله کاراکترهای # و ##. نحو تعریف روش:

CREATE PROC Procedure_name [؛ عدد]
[(پارامتر @ data_type) [\u003d default_value]] [، ... n]

مانند
<Инструкции_SQL>

گزینه های این دستور را در نظر بگیرید:

• Procedure_name - نام روش؛ باید برای شناسه ها قوانین را رعایت کند: طول آن ممکن است بیش از 128 نویسه نباشد. برای رویه های موقتی محلی ، از علامت # قبل از نام استفاده می شود ، و برای مراحل موقت جهانی - علائم ##؛
• شماره - یک عدد صحیح اختیاری است که برای گروه بندی چندین روش تحت یک نام استفاده می شود.
• @ نوع داده پارامتر - لیستی از نام پارامترهای رویه با نشان دادن نوع داده های مربوطه برای هر یک. می تواند تا 2100 پارامتر وجود داشته باشد. مجاز است NULL را به عنوان مقدار پارامتر منتقل کند. از کلیه انواع داده ها به جز متن ، متن و انواع تصویر قابل استفاده است. می توانید از نوع داده مکان نما به عنوان پارامتر خروجی (کلید واژه OUTPUT یا VARYING) استفاده کنید. پارامترهای با نوع داده مکان نما فقط می توانند پارامترهای خروجی باشند.
• VARYING - کلمه کلیدی که مشخص می کند از مجموعه نتیجه به عنوان پارامتر خروجی استفاده می شود (فقط برای نوع مکان نما استفاده می شود)؛
• OUTPUT - نشان می دهد که پارامتر مشخص شده می تواند به عنوان خروجی استفاده شود.
• مقدار پیش فرض - هنگامی که پارامتر در طول فراخوان رد می شود استفاده می شود. باید ثابت باشد و شامل علائم نقاب (٪، _، [،]، ^) و مقدار NULL باشد؛
• با RECOMPILE - کلمات کلیدی که نشان می دهد که SQL Server برنامه روش را روی حافظه پنهان نمی نویسد ، اما هر بار که اجرا شود آن را ایجاد می کند.
• با رمزگذاری - کلید واژه هایی که نشان می دهد SQL Server قبل از نوشتن در جدول سیستم Syscomments روش را رمزگذاری می کند. برای اینکه متن مراحل رمزگذاری شده بازیابی غیرممکن باشد ، لازم است پس از رمزگذاری ، نوارهای مربوطه را از جدول syscomments حذف کنید.
• برای تکرار - کلمات کلیدی نشان می دهد که این روش فقط برای تکثیر ایجاد می شود. این گزینه با کلمات کلیدی RECOMPILE سازگار نیست.
• ع - آغاز تعریف متن رویه؛
• <Инструкции_SQL> - مجموعه ای از عبارات معتبر SQL ، محدود به حداکثر اندازه روش ذخیره شده - 128 Kb. گفته های زیر معتبر نیستند: ALTER DATABASE ، ALTER PROCEDURE، ALTER TABLE، CREATE DEFAULT، CREATE PROCEDURE، ALTER TRIGGER، ALTER VIEW، ایجاد DATABASE، CREATE RUELE، CHEATE Schema، CREATE TRIGGER، CREATE VIEWC DROP DEFAULT ، DROP PROCEDURE ، DROP RULE ، DROP TRIGGER، DROP VIEW، RESOTRE DATABASE، RESTORE LOG، REKONFIGURE، آمارهای به روز.

یک نمونه از یک روش ذخیره شده را در نظر بگیرید. ما روشی ذخیره شده را توسعه خواهیم داد كه تعداد كتابهایی را كه در حال حاضر در كتابخانه وجود دارند ، شمارش و نمایش می دهد:

ایجاد تعداد روش_Ex1
- روش شمارش تعداد نسخه های کتاب ،
- در حال حاضر در کتابخانه ،
- و نه در دست خوانندگان
مانند
- متغیر محلی موقتی را تنظیم کنید
اعلام کنیدN int
N \u003d count (*) را از نمونه\u200cبرداری که در آن Yes_No \u003d "1" انتخاب کنید
N را انتخاب کنید
برو

از آنجا که روش ذخیره شده یک جزء کامل از پایگاه داده است ، همانطور که قبلاً فهمیدید ، می توانید یک روش جدید فقط برای پایگاه داده فعلی ایجاد کنید. هنگام کار در SQL Server Query Analyzer ، بانک اطلاعاتی فعلی با استفاده از دستور Use راه اندازی می شود و پس از آن نام بانک اطلاعاتی را که باید روش ذخیره شده ایجاد شود ، تنظیم می کنیم. همچنین می توانید با استفاده از لیست کشویی ، بانک اطلاعاتی فعلی را انتخاب کنید.

پس از ایجاد یک روش ذخیره شده بر روی سیستم ، SQL Server آن را کامپایل می کند و روال های موجود را بررسی می کند. در صورت بروز هرگونه مشكل ، این روش رد می شود. خطاها باید قبل از پخش مجدد برطرف شوند.

SQL Server 2000 از وضوح تاخیر نام استفاده می کند ، بنابراین اگر رویه ذخیره شده شامل یک تماس با روش دیگر ، که هنوز اجرا نشده است ، یک اخطار نمایش داده می شود ، اما تماس با روش غیر وجود ذخیره می شود.

اگر سیستم را با یک روش ذخیره نشده نامشخص رها کنید ، کاربر هنگام تلاش برای اجرای آن ، یک پیام خطا دریافت می کند.

همچنین می توانید یک روش ذخیره شده را با استفاده از SQL Server Enterprise Manager ایجاد کنید:

برای بررسی عملکرد روش ذخیره شده ایجاد شده ، لازم است به Query Analyzer بروید و مراحل اجرای آن توسط اپراتور آغاز شود. EXEC<имя процедуры> . نتایج شروع روشی که ایجاد کردیم در شکل ارائه شده است. 4

شکل. 4- روش ذخیره شده را در Query Analyzer اجرا کنید

شکل. 5- نتیجه عمل بدون عملگر صفحه

1.3 پارامترهای رویه ذخیره شده

روشهای ذخیره شده ابزاری بسیار قدرتمند هستند ، اما حداکثر بهره وری را فقط با ایجاد پویا می توان به دست آورد. توسعه دهنده باید بتواند مقادیر را به روندی ذخیره شده که با آن کار خواهد کرد ، یعنی پارامترها منتقل کند. در زیر دستورالعمل های اساسی برای استفاده از پارامترها در مراحل ذخیره شده وجود دارد.

• می توانید یک یا چند پارامتر را برای این روش تعریف کنید.
• پارامترها دقیقاً مانند متغیرهای موجود در زبانهای برنامه نویسی مانند C ، Visual Basic .NET به عنوان مکانهای ذخیره سازی داده نامگذاری می شوند.
• نام پارامتر لزوماً با نماد @ مقدم است.
• نام پارامترها در روشی که تعریف می شوند محلی هستند.
• پارامترها برای انتقال اطلاعات به روش در حین اجرای آن استفاده می شوند. آنها پس از نام روش روی خط فرمان گیر می کنند.
• اگر این روش دارای چندین پارامتر باشد ، آنها با کاما از هم جدا می شوند.
• برای تعیین نوع اطلاعات منتقل شده به عنوان پارامتر ، از سیستم یا انواع داده های کاربر استفاده می شود.

در زیر تعریف از روشی است که دارای یک پارامتر ورودی است. ما وظیفه قبلی را تغییر خواهیم داد و همه نسخه های کتاب را در نظر نمی گیریم بلکه فقط نسخه های یک کتاب خاص را در نظر خواهیم گرفت. کتاب های ما به طور منحصر به فرد توسط یک شابک منحصر به فرد مشخص می شوند ، بنابراین ما این پارامتر را به روال انتقال می دهیم. در این حالت متن رویه ذخیره شده تغییر خواهد کرد و به صورت زیر ظاهر می شود:

Procedure Count_Ex (ISBN varchar (14))
مانند
اعلام کنیدN int
N را انتخاب کنید
برو

هنگام شروع این روش برای اجرای ، باید مقدار پارامتر ورودی را از آن عبور دهیم (شکل 6).

شکل. 6. شروع روند با عبور یک پارامتر

برای ایجاد چندین نسخه از همین روال با همین نام ، یک عدد و یک عدد صحیح را بعد از نام اصلی قرار دهید. نحوه انجام این کار در مثال زیر نشان داده شده است ، که شرح ایجاد دو روش با همان نام ، اما با شماره های نسخه های مختلف (1 و 2) است. از این شماره برای کنترل نسخه در حال اجرا این رویه استفاده می شود. اگر شماره نسخه مشخص نشده باشد ، اولین نسخه رویه اجرا می شود. این گزینه در مثال قبلی نشان داده نشده است ، اما با این وجود ، برای برنامه شما در دسترس است.

برای چاپ پیام شناسایی نسخه ، هر دو روش از عبارت چاپ استفاده می کنند. نسخه اول تعداد نسخه های رایگان را در نظر می گیرد ، و نسخه دوم - تعداد نسخه های موجود در این کتاب.

متن هر دو نسخه رویه در زیر آورده شده است:

CREATE Procedure Count_Ex_all؛ 1
(ISBN varchar (14))
- روش شمارش نسخه های رایگان یک کتاب خاص
مانند
اعلام کنیدN int
N \u003d count (*) را از نمونهای مثال ISBN \u003dISBN و Yes_No \u003d "1" انتخاب کنید
N را انتخاب کنید
--
برو
--
CREATE Procedure Count_Ex_all؛ 2
(ISBN varchar (14))
- روش شمارش نسخه های رایگان یک کتاب خاص
مانند
اعلام کنید @ N1 int
@ N1 \u003d شمارش (*) را از نمونهای مثال ISBN \u003dISBN و Yes_No \u003d "0" انتخاب کنید
@ N1 را انتخاب کنید
برو

نتایج روش با نسخه های مختلف در شکل نشان داده شده است. 7

شکل. 7. نتایج شروع نسخه های مختلف از یک روش ذخیره شده مشابه

هنگام نوشتن چندین نسخه ، لازم است محدودیت های زیر را به خاطر بسپارید: از آنجا که تمام نسخه های رویه با هم گردآوری می شوند ، پس از آن همه متغیرهای محلی رایج در نظر گرفته می شوند. بنابراین ، اگر این مورد توسط الگوریتم پردازش مورد نیاز باشد ، لازم است از نامهای مختلف متغیرهای داخلی استفاده شود ، که ما در روش دوم نامگذاری متغیرN با نام @ N1 را انجام دادیم.

رویه هایی که نوشتیم یک پارامتر واحد را برنمی گرداند ، آنها فقط عدد نتیجه را نمایش می دهند. با این حال ، بیشتر اوقات ما نیاز به یک پارامتر برای پردازش بیشتر داریم. روش های مختلفی برای بازگرداندن پارامترها از یک روش ذخیره شده وجود دارد. ساده ترین استفاده از عملگر بازگشت RETURN است. این عملگر یک مقدار عددی واحد را برمی گرداند. اما باید نام متغیر یا عبارتی را که به پارامتر برگشتی اختصاص یافته است ، مشخص کنیم. در زیر مقادیر برگشت شده توسط بیانیه RETURN رزرو شده توسط سیستم ذکر شده است:

کد	مقدار
0	اشکالی نداره
-1	شی یافت نشد
–2	خطای نوع داده
–3	این روند قربانی "بن بست" شد
-4	خطای دسترسی
–5	اشتباه نوشتاری
-6	برخی اشتباهات
-7	خطا در منابع (فضا)
-8	خطای داخلی قابل اصلاح رخ داده است
-9	به حد سیستم رسیده است.
–10	نقض کشنده صداقت داخلی
–11	یکسان
–12	جدول یا فهرست تخریب
-13	تخریب پایگاه داده
-14	خطای سخت افزار

بنابراین ، برای عدم تضاد با سیستم ، ما فقط از طریق این پارامتر می توانیم اعداد صحیح مثبت را برگردانیم.

به عنوان مثال ، می توانیم متن رویه ذخیره شده Count_ex که قبلاً نوشته شده است را به شرح زیر تغییر دهیم:

Procedure Count_Ex2 (ISBN varchar (14))
مانند
اعلام کنیدN int
N \u003d count (*) را از نمونه انتخاب کنید
جایی که ISBN \u003dISBN و YES_NO \u003d "1"
- مقدار متغیرN را بازگردانید ،
- اگر مقدار متغیر تعریف نشده است ، 0 را برگردانید
بازگشت Coalesce (N ، 0)
برو

اکنون می توانیم مقدار متغیرN را بدست آوریم و از آن برای پردازش بیشتر استفاده کنیم. در این حالت ، مقدار برگشتی به خود روش ذخیره شده اختصاص داده می شود و برای تجزیه آن می توانید از قالب زیر از اپراتور فراخوانی فراخوان ذخیره شده استفاده کنید:

اجرا<переменная> = <имя_процедуры> <значение_входных_параметров>

نمونه ای از فراخوانی روش ما در شکل نشان داده شده است. 8

شکل. 8- انتقال مقدار برگشتی روش ذخیره شده به متغیر محلی

پارامترهای ورودی به رویه های ذخیره شده می توانند از مقدار پیش فرض استفاده کنند. اگر مقدار پارامتر هنگام فراخوانی روش مشخص نشده باشد از این مقدار استفاده می شود.

مقدار پیش فرض از طریق علامت مساوی پس از شرح پارامتر ورودی و نوع آن مشخص می شود. یک روش ذخیره شده را در نظر بگیرید که تعداد کتاب ها را در یک سال انتشار مشخص می کند. سال انتشار پیش فرض سال 2006 است.

CREATE PROCEDURE ex_books_now (year int \u003d 2006)
- شمارش تعداد نسخه های کتاب های سال انتشار
مانند
اعلام کنیدN_books int
از نمونه کتابها ،N_books \u003d تعداد (*) را انتخاب کنید
جایی که Books.ISBN \u003d shembullplar.ISBN و YEARIZD \u003dyear
بازگشت همبستگی (N_books ، 0)
برو

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

شکل. 9- فراخوانی یک روش ذخیره شده با و بدون پارامتر

تمام مثالهای فوق در استفاده از پارامترها در مراحل ذخیره شده تنها پارامترهای ورودی را ارائه می دهد. با این حال ، پارامترها ممکن است خروجی باشند. این بدان معنی است که مقدار پارامتر پس از اتمام مراحل به کسی که این روش را فراخوانده است (یک روش دیگر ، ماشه ، بسته فرمان و غیره) منتقل می شود. به طور طبیعی ، برای به دست آوردن پارامتر خروجی ، هنگام تماس ، شما باید یک ثابت ، بلکه یک متغیر را به عنوان پارامتر واقعی تعیین کنید.

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

عبارت OUTPUT برای نشان دادن خروجی پارامتر استفاده می شود. این کلمه کلیدی پس از شرح پارامتر نوشته شده است. هنگام توصیف پارامترهای رویه های ذخیره شده ، مطلوب است که مقادیر پارامترهای خروجی را پس از ورودی تنظیم کنید.

مثالی را با استفاده از پارامترهای خروجی در نظر بگیرید. ما یک روال ذخیره شده را برای شما می نویسیم که برای یک کتاب خاص تعداد کل نسخه های موجود در کتابخانه و تعداد نسخه های رایگان را محاسبه می کند. ما نمی توانیم از عملگر بازگرداندن RETURN در اینجا استفاده کنیم ، زیرا فقط یک مقدار را برمی گرداند ، بنابراین باید پارامترهای خروجی را در اینجا تعریف کنیم. متن رویه ذخیره شده ممکن است به شرح زیر باشد:

CREATE Procedure Count_books_all
(ISBN varchar (14) ، خروجیall int ، خروجیfree int)
- روش محاسبه تعداد کل نسخه های یک کتاب خاص
- و تعداد نسخه های رایگان
مانند
- محاسبه تعداد کل نسخه ها
از نمونه مثال Where ISBN \u003dISBN ،all \u003d count (*) را انتخاب کنید
از نمونه مثال Where ISBN \u003dISBN و Yes_No \u003d "1" گزینهfree \u003d count (*) را انتخاب کنید
برو

نمونه ای از این روش در شکل نشان داده شده است. ده

شکل. 10. تست یک روش ذخیره شده با پارامترهای خروجی

همانطور که قبلاً ذکر شد ، برای به دست آوردن مقادیر پارامترهای خروجی برای تجزیه و تحلیل ، باید آنها را به عنوان متغیر تعیین کنیم و این متغیرها باید توسط اپراتور Declare شرح داده شود. آخرین عبارت خروجی به ما اجازه می دهد مقادیر حاصل را به سادگی نمایش دهیم.

پارامترهای رویه حتی می توانند متغیرهایی از نوع Cursor باشند. برای این کار ، متغیر باید به عنوان یک نوع داده خاص VARYING ، بدون اتصال به انواع داده سیستم استاندارد توصیف شود. علاوه بر این ، باید مشخص شود که این یک متغیر از نوع Cursor است.

ما ساده ترین روشی را که لیستی از کتاب ها در کتابخانه ما به نمایش می گذارد خواهیم نوشت. علاوه بر این ، اگر بیش از سه کتاب وجود نداشته باشد ، ما نام آنها را در چارچوب رویه خود نمایش می دهیم ، و اگر لیست کتاب ها از یک عدد مشخص شده فراتر رود ، آنها را به شکل مکان نما به برنامه یا ماژول فراخوانی منتقل می کنیم.

متن رویه به شرح زیر است:

ایجاد GET3TITLES PROCEDURE
(MYCURSOR CURSOR متغیر خروجی)
- روش چاپ عناوین کتاب با مکان نما
مانند
- یک متغیر محلی از مکان نما نوع را در روش تعریف کنید
تنظیمMYCURSOR \u003d CURSOR
برای انتخاب عنوان TITLE
از کتاب ها
- مکان نما را باز کنید
MYCURSOR را باز کنید
- ما متغیرهای داخلی داخلی را توصیف می کنیم
DECLARETITLE VARCHAR (80) ،CNT INT
--- وضعیت اولیه پیشخوان کتاب را تنظیم کنید
تنظیمCNT \u003d 0
- به خط اول مکان نما بروید
- در حالی که خطوط مکان نما وجود دارد ،
- یعنی در حالی که انتقال به یک خط جدید درست است
WHILE (FETCH_STATUS \u003d 0) و (CNT<= 2) BEGIN
PRINTTITLE
دریافت بعدی ازMYCURSOR INTOTITLE
- تغییر وضعیت پیشخوان کتاب
تنظیمCNT \u003dCNT + 1
پایان
اگرCNT \u003d 0 چاپ "بدون کتاب های مناسب"
برو

نمونه ای از فراخوانی این روش ذخیره شده در شکل نشان داده شده است. یازده

در روش فراخوانی ، مکان نما باید به عنوان متغیر محلی توصیف شود. سپس روش خود را فراخوانی کردیم و نام آن را به یک متغیر محلی از نوع Cursor منتقل کردیم. این روش شروع به کار کرد و سه نام اول را روی صفحه نمایش داد و سپس کنترل را به روش فراخوانی منتقل کرد و پردازش مکان نما را ادامه داد. برای انجام این کار ، او چرخه ای از نوع در حالی که در متغیر جهانیFETCH_STATUS ، که وضعیت مکان نما را نظارت می کند ، ترتیب داده و سپس در چرخه ، تمام خطوط دیگر مکان نما را نشان می دهد.

در پنجره خروجی ، فاصله بین سه خط اول و نامهای بعدی را افزایش می دهیم. این فاصله فقط نشان می دهد که کنترل به یک برنامه خارجی منتقل شده است.

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

ساده ترین روش برای دیدن متن روش تغییر یا حذف آن با استفاده از رابط گرافیکی Enterprise Manager است. اما می توانید این کار را با روشهای ویژه ذخیره شده سیستم Transact-SQL انجام دهید. در Transact-SQL ، تعریف رویه با استفاده از روش سیستم sp_helptext مشاهده می شود ، و روش سیستم sp_help به شما امکان می دهد اطلاعات کنترل درباره این روش را نشان دهید. روال های سیستم sp_helptext و sp_help نیز برای مشاهده اشیاء پایگاه داده مانند جداول ، قوانین و تنظیمات پیش فرض استفاده می شود.

اطلاعات در مورد تمام نسخه های یک روش ، صرف نظر از تعداد ، بلافاصله نمایش داده می شود. حذف نسخه های مختلف از همان روش ذخیره شده نیز در همان زمان اتفاق می افتد. مثال زیر نشان می دهد که چگونه تعاریف مربوط به نسخه های 1 و 2 از روش Count_Ex_all نمایش داده می شود که نام آن به عنوان پارامتر روش سیستم sp_helptext مشخص شود (شکل 12).

شکل. 12. مشاهده متن رویه ذخیره شده با استفاده از یک روش ذخیره شده در سیستم

روش سیستم SP_HELP ویژگی ها و پارامترهای روش ایجاد شده را به شکل زیر نمایش می دهد:

نام صاحب تایپ کنید	Created_datetime
Count_books_all dbo روش ذخیره شده	2006-12-06 23:15:01.217
پارامتر_نام تایپ کنید طول دقیق	مقیاس	Param_order همبستگی
ISBN واچار 14 14	خالی	1	Cyrillic_General_CI_AS
@همه int 4 10	0	2	خالی
@رایگان int 4 10	0	3	خالی

سعی کنید این پارامترها را خودتان رمزگشایی کنید. آنها راجع به چه چیزی صحبت میکنند؟

1.4. تدوین یک روش ذخیره شده

مزیت استفاده از روشهای ذخیره شده برای اجرای مجموعه ای از عبارات Transact-SQL این است که آنها در اولین اجرای کامپایل می شوند. در حین تدوین ، عبارات Transact-SQL از نمایش شخصیت اصلی آنها به فرم قابل اجرا تبدیل می شوند. اشیاء دسترسی به روش نیز به نمایندگی جایگزین تبدیل می شوند. به عنوان مثال ، نام جدول به شناسه شی تبدیل می شود و نام ستون ها به شناسه های ستون تبدیل می شود.

یک طرح اعدام به همان روشی که برای اجرای یک عبارت Transact-SQL واحد ایجاد شده است. به عنوان مثال ، این طرح شامل فهرست هایی است که برای خواندن ردیف ها از جداول دسترسی به روش استفاده می شود. برنامه اجرای روش در حافظه پنهان ذخیره می شود و هر بار که فراخوانی می شود استفاده می شود.

توجه: اندازه حافظه نهان روش به گونه ای تعیین می شود که می تواند شامل بیشتر یا تمام مراحل موجود برای اجرا باشد. این باعث صرفه جویی در زمان مورد نیاز برای ترمیم برنامه درمانی می شود.

1.5 جبران خودکار

به طور معمول ، برنامه اجرا در حافظه نهان است. این به شما امکان می دهد عملکرد را هنگام انجام کار افزایش دهید. با این حال ، در برخی شرایط ، این روش بطور خودکار دوباره محاسبه می شود.

• این روش همیشه با شروع SQL Server دوباره محاسبه می شود. این معمولاً بعد از راه اندازی مجدد سیستم عامل و در اولین اجرای روش پس از ایجاد اتفاق می افتد.
• اگر فهرست جدول كه روشی به آن اشاره می شود حذف شود ، طرح اجرا یك روال به طور خودکار محاسبه می شود. از آنجا که طرح فعلی به فهرست جدول که قبلاً وجود ندارد ، اشاره دارد ، باید یک برنامه اعدام جدید ایجاد شود. درخواست های این روش فقط در صورت بروز شدن انجام می شود.
• تدوین برنامه اجرای همچنین در صورتی رخ می دهد که کاربر دیگری در حال حاضر با این برنامه حافظه پنهان کار می کند. یک نسخه جداگانه از برنامه اجرای برای کاربر دوم ایجاد می شود. اگر نسخه اول طرح گرفته نشده بود ، دیگر نیازی به ایجاد نسخه دوم نبود. هنگامی که کاربر مراحل را کامل کرد ، برنامه اجرای در حافظه پنهان برای کاربر دیگری با اجازه دسترسی مناسب در دسترس است.
• در صورت حذف و ایجاد مجدد ، این روش بطور خودکار جبران می شود. از آنجا که رویه جدید ممکن است با نسخه قدیمی متفاوت باشد ، کلیه نسخه های طرح اجرا در حافظه پنهان حذف شده و مجدداً نقشه کامپایل می شود.

SQL Server قصد دارد با ذخیره کردن روشهای بسیار سنگین ، روشهای ذخیره شده را بهینه کند. بنابراین می توان به جای طرح جدید از طرح اعدام قدیمی بارگذاری شده در حافظه نهان استفاده کرد. برای جلوگیری از این مشکل ، شما باید روش ذخیره شده را حذف کرده و از نو مجدداً سرور خود را متوقف کرده و دوباره فعال کنید. این باعث می شود حافظه نهان رویه پاک شود و امکان کار با برنامه اجرای قدیمی را از بین ببرد.

همچنین می توانید با گزینه WITH RECOMPILE روشی ایجاد کنید. در این حالت ، در هر اجرا به طور خودکار جبران می شود. از گزینه WITH RECOMPILE باید در مواردی استفاده شود که رویه به جداول بسیار پویا دسترسی داشته باشد که ردیف های آنها اغلب اضافه می شوند ، حذف می شوند یا به روز می شوند ، زیرا این امر منجر به تغییرات چشمگیر در شاخص های خاص جدول می شود.

اگر جبران مجدد مراحل به صورت خودکار انجام نشود ، می توان آن را به اجبار انجام داد. به عنوان مثال ، اگر آماری که برای تعیین اینکه آیا می توان از این شاخص در این پرس و جو استفاده کرد ، به روز شده یا اگر یک شاخص جدید ایجاد شده است ، باید یک جبران مجدد اجباری انجام شود. برای مجبور کردن تلفیق ، عبارت EXECUTE از بند WITH RECOMPILE استفاده می کند:

EXECUTE Procedure_name؛
مانند
<инструкции Transact-SQL>
با استفاده مجدد

اگر این روش با پارامترهایی کار می کند که دستور اجرای آن را کنترل می کنند ، از گزینه WITH RECOMPILE استفاده کنید. اگر پارامترهای رویه ذخیره شده می تواند بهترین روش برای اجرای آن را تعیین کند ، توصیه می شود در فرآیند کار یک برنامه اعدام ایجاد کنید ، و هنگامی که برای اولین بار در تمام تماس های بعدی فراخوانی می کنید ، آن را ایجاد نکنید.

توجه: تعیین برخی از موارد استفاده از گزینه WITH RECOMPILE در هنگام ایجاد روال ممکن است دشوار باشد. اگر شک دارید ، بهتر است از این گزینه استفاده نکنید ، زیرا تکرار روش در هر اجرای منجر به از دست رفتن زمان CPU بسیار با ارزش خواهد شد. اگر در آینده باید هنگام اجرای روال ذخیره شده مجدداً کامپایل شوید ، می توانید با افزودن بند WITH RECOMPILE به عبارت EXECUTE این کار را انجام دهید.

در عبارت CREATE PROCEDURE که شامل گزینه FOR REPLICATION است ، نمی توانید از گزینه WITH RECOMPILE استفاده کنید. از این گزینه برای ایجاد روشی استفاده می شود که در طول فرآیند تکثیر اجرا می شود.

1.6 روشهای ذخیره شده را لانه کنید

در روشهای ذخیره شده ، سایر روشهای ذخیره شده را می توان نام برد ، با این وجود محدودیتی در سطح لانه سازی وجود دارد. حداکثر سطح لانه سازی 32 است. سطح لانه سازی فعلی با استفاده از متغیر جهانیNESTLEVEL قابل تعیین است.

2. توابع تعریف شده توسط کاربر (UDF)

در MS SQL SERVER 2000 عملکردهای از پیش تعریف شده زیادی وجود دارد که به شما امکان می دهد اقدامات متنوعی را انجام دهید. با این وجود ، همیشه ممکن است استفاده از برخی کارکردهای خاص ضروری باشد. برای این کار ، با شروع نسخه 8.0 (2000) ، امکان توصیف توابع تعریف شده توسط کاربر (کاربر تعریف شده از توابع ، UDF) و ذخیره آنها به عنوان یک شیء کامل از پایگاه داده ، به همراه مراحل ذخیره شده ، نمایش ها و غیره امکان پذیر است.

قابلیت استفاده توابع تعریف شده توسط کاربر آشکار است. برخلاف روشهای ذخیره شده ، توابع می توانند مستقیماً در عبارت SELECT تعبیه شوند و از آنها برای دستیابی به مقادیر خاص (در بخش SELECT) و همچنین به عنوان منبع داده (در بخش FROM) استفاده کنید.

هنگام استفاده از UDF ها به عنوان منبع داده ، مزیت آنها نسبت به بازنمایی این است که UDF ها ، برخلاف بازنمودها ، می توانند پارامترهای ورودی داشته باشند که می توانند برای تأثیرگذاری در نتیجه یک عملکرد استفاده کنند.

توابع تعریف شده توسط کاربر می تواند از سه نوع باشد: توابع مقیاس, توابع درون خطی و توابع چند اپراتور که نتیجه جدول را برمی گرداند. بیایید تمام این نوع عملکردها را با جزئیات بیشتری در نظر بگیریم.

2.1. توابع مقیاس

توابع مقیاس یک نتیجه مقیاس پذیر را برمی گرداند. این نتیجه می تواند از هر نوع توصیف شده در بالا باشد ، بجز انواع متن ، متن ، متن و تصویر زمانی. این ساده ترین نوع عملکرد است. نحو آن به شرح زیر است:

RETURNS data_type مقیاس پذیر

شروع
function_body
بازگرداندن scalar_expression
پایان

• پارامتر ENCRYPTION قبلاً در بخش روشهای ذخیره شده توضیح داده شده است.
• طرحواره ای - عملکردی را به یک طرح بندی مرتبط می کند. این بدان معناست که حذف جدول ها یا نمایی که بر اساس آن عملکرد ایجاد شده است بدون حذف یا تغییر عملکرد خود امکان پذیر نخواهد بود. اگر قسمت متغیر توسط تابع استفاده شود ، نمی توانید ساختار این جداول را نیز تغییر دهید. بنابراین ، این گزینه اجازه می دهد تا موقعیت هایی را که تابع از برخی جداول یا نماها استفاده می کند ، حذف کند و کسی ، با آگاهی از آن ، آنها را حذف یا تغییر دهد.
• RETURNS data_type مقیاس پذیر - نوع داده ای را که عملکرد برمی گرداند توصیف می کند.
• scalar_expression - عبارتی که مستقیماً نتیجه عملکرد را برگرداند. این باید از همان روشی باشد که شرح داده شده پس از بازگشت است.
• function_body مجموعه ای از عبارات Transact-SQL است.

بیایید نمونه هایی از استفاده از توابع مقیاس را در نظر بگیریم.

تابعی را ایجاد کنید که کوچکترین دو عدد صحیح را که به ورودی به عنوان پارامترهای وارد شده انتخاب می کنند.

بگذارید عملکرد به صورت زیر باشد:

ایجاد FUNCTION min_num (a INT ،b INT)
بازگرداندن INT
شروع
DECLAREc INT
اگر یک< @b SET @c = @a
ELSE SETc \u003db
بازگرداندنc
پایان

بیایید این عملکرد را اجرا کنیم:

SELECT dbo.min_num (4 ، 7)

در نتیجه مقدار 4 را بدست می آوریم.

می توانید از این عملکرد برای یافتن کمترین مقدار در بین مقادیر ستون جدول استفاده کنید:

SELECT min_lvl، max_lvl، min_num (min_lvl، max_lvl)
از مشاغل

بیایید تابعی را ایجاد کنیم که پارامتر نوع نوع datetime را به عنوان ورودی دریافت کند و تاریخ و زمان مربوط به آغاز روز مشخص شده را برگردانیم. به عنوان مثال ، اگر پارامتر ورودی 09/20/03 1:31 p.m. باشد ، نتیجه 09/20/03 00:00 خواهد بود.

ایجاد FUNCTION dbo.daybegin (dat DATETIME)
بازگرداندن smalldatetime AS
شروع
بازگشت مجدد (datetime ، طبقه (تبدیل (FLOAT ،dat)))
پایان

در اینجا ، تابع CONVERT تبدیل نوع را انجام می دهد. ابتدا نوع تاریخ-زمان به نوع FLOAT ریخته می شود. با چنین کاهش ، قسمت عدد صحیح تعداد روزهایی است که از اول ژانویه سال 1900 شمارش می شود و بخش کسری آن زمان است. در مرحله بعد ، گرد با عدد صحیح کوچکتر با استفاده از عملکرد FLOOR و ریخته گری به نوع تاریخ-زمان انجام می شود.

عملکرد عملکرد را بررسی کنید:

انتخاب dbo.daybegin (دریافت ())

در اینجا GETDATE () تابعی است که تاریخ و زمان فعلی را برمی گرداند.

توابع قبلی فقط در محاسبه از پارامترهای ورودی استفاده می کردند. با این وجود می توانید از داده های ذخیره شده در دیتابیس استفاده کنید.

بیایید تابعی را ایجاد کنیم که دو تاریخ به عنوان پارامتر در نظر بگیرد: آغاز و پایان فاصله زمانی - و کل درآمد فروش را برای این بازه محاسبه کنیم. تاریخ فروش و مقدار آن از جدول فروش گرفته می شود و قیمت نشریات فروخته شده از جدول عنوان ها گرفته می شود.

ایجاد FUNCTION dbo.SumSales (datebegin DATETIME ، @ dateend DATETIME)
بازپرداخت پول
مانند
شروع
DECLARESum Money
SELECTSum \u003d جمع (t.price * s.qty)

RETURNSum
پایان

2.2. توابع درون خطی

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

عملکردهایی از این نوع در مواردی که لازم است جدول برگشتی به پارامترهای ورودی بستگی داشته باشد ، حتی بیشتر مفید است. همانطور که می دانید ، نماها نمی توانند پارامترهایی داشته باشند ، بنابراین فقط توابع درون خطی می توانند این نوع مشکل را حل کنند.

یکی از ویژگی های عملکردهای درون خطی این است که آنها می توانند فقط یک درخواست در بدن خود داشته باشند. بنابراین ، توابع از این نوع بسیار شبیه به بازنمایی ها هستند ، اما علاوه بر این ممکن است دارای پارامترهای ورودی نیز باشند. نحو عملکرد درون خطی:

ایجاد FUNCTION [مالک.] Function_name
([(@ parameter_name scalar_ data_type [\u003d default_value]) [، ... n]])
جدول بازگرداندن

برگشت [(<запрос>)]

تعریف عملکرد نشان می دهد که آن یک جدول برمی گردد.<запрос> - این پرس و جو است که نتیجه اجرای آن نتیجه عملکرد خواهد بود.

ما یک تابع شبیه به عملکرد مقیاس را از آخرین مثال خواهیم نوشت ، اما برگرداندن نه تنها یک نتیجه خلاصه ، بلکه خطوط فروش از جمله تاریخ فروش ، نام کتاب ، قیمت ، تعداد قطعه و میزان فروش را نیز مشاهده می کنید. فقط فروش هایی که در یک بازه زمانی معین قرار می گیرند باید انتخاب شوند. ما متن عملکرد را رمزگذاری می کنیم تا سایر کاربران بتوانند از آن استفاده کنند ، اما نمی توانند آن را بخوانند و تصحیح کنند:

CREATE FUNCTION Sales_Period (datebegin DATETIME ،dateend DATETIME)
جدول بازگرداندن
با رمزگذاری
مانند
برگشت (
SELECT t.title ، t.price ، s.qty ، ord_date ، t.price * s.qty as stoim
از عناوین t فروش شرکت در t.title_Id \u003d s.Title_ID
WHERE ord_date BETWEENdatebegin وdateend
)

اکنون با این عملکرد تماس بگیرید. همانطور که قبلاً نیز گفته شد ، می توانید آن را فقط در بخش FROM جمله SELECT صدا کنید:

SELECT * از Sales_Period ("01.09.94" ، "13.09.94")

2.3 عملکردهای چند اپراتور که نتیجه جدول را برمی گرداند

نوع اول عملکردی که در نظر گرفته شد به ما امکان می دهد تا حد ممکن از بسیاری از عبارات Transact-SQL استفاده کنیم ، اما این فقط نتایج مقیاس آور را برگرداند. نوع دوم عملکرد می تواند جداول را برگرداند ، اما بدن آن فقط یک سؤال را نشان می دهد. عملکردهای چند کاره که نتیجه جدول را برمی گردانند به شما امکان می دهند خصوصیات دو عملکرد اول را با هم ترکیب کنید ، یعنی می توانند حاوی بسیاری از عبارات Transact-SQL در بدن باشند و یک جدول را به عنوان نتیجه برگردانید. نحو عملکرد چند عملگر:

ایجاد FUNCTION [مالک.] Function_name
([(@ parameter_name scalar_ data_type [\u003d default_value]) [، ... n]])
RETURNS @ result_variable_name TABLE
<описание_таблицы>

شروع
<тело_функции>
برگشت
پایان

• جدول<описание_таблицы> - ساختار جدول بازگشتی را توصیف می کند؛
• <описание_таблицы> - شامل لیست ستون ها و محدودیت ها است.

اکنون مثالی را در نظر بگیرید که فقط با استفاده از توابع این نوع قابل انجام است.

بگذارید یک درخت دایرکتوری و پرونده های موجود در آنها وجود داشته باشد. بگذارید این ساختار به صورت جداول در بانک اطلاعاتی شرح داده شود (شکل 13). در واقع ، در اینجا ما یک ساختار سلسله مراتبی برای دایرکتوری ها داریم ، بنابراین نمودار رابطه جدول پوشه ها را با خودش نشان می دهد.

شکل. 13. ساختار بانک اطلاعاتی برای توصیف سلسله مراتب پرونده ها و دایرکتوری ها

اکنون تابعی را می نویسیم که یک شناسه دایرکتوری را در ورودی می گیرد و تمام پرونده هایی که در آن ذخیره شده اند و در کلیه دایرکتوری های پایین سلسله مراتب نمایش داده می شود. به عنوان مثال ، اگر کاتالوگ های موسسه دایرکتوری های School1 ، دانشکده 2 و غیره را ایجاد کرده است ، آنها حاوی دایرکتوری بخش ها هستند و هر دایرکتوری حاوی پرونده ها است ، پس از تعیین شناسه فهرست موسسه به عنوان پارامتر عملکرد ما ، لیستی از کلیه پرونده ها را برای همه این دایرکتوری ها برای هر پرونده باید نام ، اندازه و تاریخ ایجاد نمایش داده شود.

حل مسئله با استفاده از عملکرد درون خطی غیرممکن است ، زیرا SQL برای انجام سؤالات سلسله مراتبی طراحی نشده است ، بنابراین نیازی به انجام فقط یک سؤال SQL نیست. عملکرد مقیاس نیز نمی تواند اعمال شود ، زیرا نتیجه باید یک جدول باشد. در اینجا ، یک عملکرد چند عملگر به نجات باز می گردد و یک جدول را برمی گرداند:

CREATE FUNCTION dbo.GetFiles (Folder_ID int)
RETURNSfiles TABLE (نام VARCHAR (100) ، Date_Create DATETIME ، FileSize INT) AS
شروع
DECLAREtmp TABLE (پوشه_Id int)
DECLARECnt INT
مقادیر INSERT INTOtmp (Folder_ID)
تنظیمCnt \u003d 1
WHILECnt<> شروع کنید
INSERT INTOtmp SELECT پوشه_Id
FROM Folders f JOINtmp t ON f.parent \u003d t.Folder_ID
WHERE F.ID IN (SELECT Folder_ID FROMtmp)
تنظیمCnt \u003dROWCOUNT
پایان
INSERT INTO @ پرونده ها (نام ، تاریخ_ ایجاد ، FileSize)
SELECT F.Name ، F.Date_Create ، F.FileSize
FROM Files \u003d از FIN بپیوندید. پوشه ها روی f.Folder_id \u003d Fl.id
به عضویتtmp t در Fl.id \u003d t.Folder_Id
برگشت
پایان

در اینجا ، در یک حلقه ، تمام زیرشاخه ها در تمام سطوح لانه سازی به متغیرtmp اضافه می شوند تا جایی که هیچ زیرشاخه دیگری باقی نماند. سپس تمام ویژگیهای لازم فایلهای واقع در دایرکتوریهای ذکر شده در متغیرtmp به متغیر نتیجهFiles نوشته می شوند.

وظایف کار مستقل

شما باید پنج روش ذخیره شده را از لیست مورد نیاز زیر ایجاد و اشکال زدایی کنید:

روال 1. در صورتی که مهلت فعلی از سه روز تا تاریخ جاری به سه روز پس از تاریخ فعلی رسیده باشد مهلت تحویل نسخه های کتاب را به مدت یک هفته افزایش دهید.

روش 2. شمارش تعداد نسخه های رایگان یک کتاب خاص.

روش 3. بررسی وجود یک خواننده با نام خانوادگی و تاریخ تولد.

روال 4. ورود به یک خواننده جدید با تأیید وجود آن در بانک اطلاعاتی و تعیین شماره کارت کتابخانه جدید آن.

روال 5- محاسبه جریمه از نظر پولی برای خوانندگان-بدهکاران.

شرح مختصر رویه ها

روال 1. تمدید مهلت برای کتاب

برای هر ورودی از جدول نمونه ، بررسی می شود که آیا تاریخ تحویل کتاب در بازه زمانی مشخص قرار دارد یا خیر. در صورت انجام این کار ، تاریخ بازگشت کتاب تا یک هفته افزایش می یابد. هنگام انجام روش ، شما باید از عملکرد برای کار با خرما استفاده کنید:

DateAdd (روز ،<число добавляемых дней>, <начальная дата>)

روش 2. شمارش تعداد نسخه های رایگان یک کتاب خاص

پارامتر ورودی این روش ISBN است - رمزنگاری منحصر به فرد کتاب. اگر تمام نسخه های این کتاب در دست خوانندگان باشد ، 0 (صفر) برمی گردد. این روش مقدار N برابر با تعداد نسخه های کتاب را که در حال حاضر در دسترس خوانندگان است ، برمی گرداند.

اگر كتاب با ISBN مشخص شده در كتابخانه نباشد ، پس از آن مراحل -100 (منهاي صد) باز مي گردند.

روش 3. بررسی وجود یک خواننده با نام خانوادگی و تاریخ تولد

در صورت وجود یک خواننده با چنین داده هایی ، شماره کارت کتابخانه برمی گردد و در غیر این صورت 0 (صفر).

برای مقایسه تاریخ تولد ، لازم است برای تبدیل تاریخ تولد از تابع تبدیل Convert () استفاده کنید - متغیر کاراکتری از نوع Varchar (8) ، که به عنوان یک پارامتر ورودی از رویه استفاده می شود ، به داده های نوع داده نوع ، که در جدول Readers استفاده می شود. در غیر اینصورت ، عملیات مقایسه هنگام جستجو برای این خواننده کارایی نخواهد داشت.

روش 4. ورود به یک خواننده جدید

این روش دارای پنج پارامتر ورودی و سه خروجی است.

پارامترهای ورودی:

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

پارامترهای خروجی:

• شماره ی کارت کتابخانه؛
• نشانه اینکه آیا خواننده قبلاً در کتابخانه ضبط شده است (0 - نبود ، 1) بود.
• تعداد کتابهایی که توسط خواننده ذکر شده است.

روال 5- محاسبه جریمه از نظر پولی برای خوانندگان بدهکار

این روش با یک مکان نما که شامل لیستی از شماره کارتهای کتابخانه برای همه بدهکاران است ، کار می کند. در فرآیند کار ، باید یک جدول موقت جهانی ## DOLG ایجاد شود ، که در آن برای هر بدهکار کل بدهی های وی به لحاظ پولی برای کلیه کتابهایی که وی برای مدت بازپرداخت طولانی تر داشته است ، وارد می شود. غرامت نقدی 0.5٪ قیمت هر کتاب برای روز تأخیر محاسبه می شود.

سفارش کار

• نسخه هایی از صفحه نمایش (عکس) که تغییرات ایجاد شده در پایگاه داده را تأیید می کند.
• محتویات جداول بانک اطلاعاتی که برای تأیید عملکرد صحیح لازم است.
• متن رویه ذخیره شده با نظرات؛
• روند شروع یک روش ذخیره شده با خروجی کار.

کارهای اضافی

مراحل اضافی ذخیره شده زیر برای کارهای فردی است.

روش 6. شمارش تعداد كتاب ها در يك موضوع موضوعي خاص كه هم اكنون در كتابخانه حداقل در يك نسخه وجود دارد. منطقه موضوع به عنوان یک پارامتر ورودی منتقل می شود.

رویه 7. وارد کردن کتاب جدید که تعداد نسخه ها را نشان می دهد. هنگام وارد کردن نسخه های کتاب جدید ، فراموش نکنید که شماره موجودی صحیح آنها را وارد کنید. در مورد چگونگی انجام این کار فکر کنید. یادآوری می کنم که توابع Max و Min را دارید که به شما امکان می دهد حداکثر یا حداقل مقدار هر صفت عددی را با استفاده از ابزار Select query پیدا کنید.

رویه 8. تشکیل جدول با لیستی از خوانندگان بدهکار ، یعنی کسانی که قرار بود کتاب ها را به کتابخانه برگردانند ، اما هنوز برنگشته اند. هر خواننده بدهکار باید صرف نظر از تعداد کتابی که مدیون است ، فقط یک بار در جدول نتیجه ظاهر شود. علاوه بر نام کامل و شماره کارت کتابخانه ، آدرس و شماره تلفن باید در جدول نتیجه ذکر شده باشد.

رویه 9. جستجوی نسخه رایگان برای عنوان کتابی خاص. اگر یک نمونه آزاد وجود داشته باشد ، در این صورت شماره موجودی نمونه به دست می آید. اگر اینطور نیست ، رویه لیستی از خوانندگانی را که این کتاب را دارند ، برمی گرداند و تاریخ بازگشت کتاب و شماره تلفن خواننده را نشان می دهد.

رویه 10. لیست خوانندگانی را که در حال حاضر یک کتاب واحد در دست ندارند نگه دارد. در لیست نام و شماره تلفن را وارد کنید.

روال 11. نمایش لیستی از کتب که تعداد نسخه های این کتاب در کتابخانه و تعداد نسخه های رایگان در حال حاضر را نشان می دهد.

نسخه چاپی

روشهای ذخیره شده SQL یک ماژول برنامه اجرایی است که می تواند در قالب اشیاء مختلف ذخیره شود. به عبارت دیگر ، این شیئی است که شامل عبارات SQL است. این روشهای ذخیره شده را می توان در مشتری برنامه اجرا کرد تا عملکرد خوبی داشته باشد. علاوه بر این ، چنین اشیاء اغلب از اسکریپت های دیگر یا حتی از بخش دیگری خوانده می شوند.

مقدمه

بسیاری از مردم فکر می کنند که آنها مشابه روشهای مختلف هستند (به ترتیب ، به جز MS SQL). شاید این درست باشد. آنها پارامترهای مشابهی دارند ، می توانند مقادیر مشابهی تولید کنند. علاوه بر این ، در بعضی موارد با آنها در تماس هستند. به عنوان مثال ، آنها با پایگاه داده های DDL و DML و همچنین با توابع کاربر (نام کد - UDF) ترکیب می شوند.

در واقعیت ، روشهای ذخیره شده SQL دارای طیف گسترده ای از مزایا است که آنها را از فرآیندهای مشابه متمایز می کند. امنیت ، تغییرپذیری برنامه نویسی ، بهره وری - همه اینها کاربرانی که با پایگاه های داده کار می کنند ، بیشتر و بیشتر را جذب می کند. اوج محبوبیت رویه ها در سالهای 2005 تا 2005 اتفاق افتاد ، هنگامی که برنامه ای از مایکروسافت با نام "SQL Server Management Studio" منتشر شد. با کمک آن ، کار با پایگاه داده ها بسیار آسان تر ، کاربردی تر و راحت تر شده است. از سال به سال ، این محبوبیت در بین برنامه نویسان به دست آورد. امروز این یک برنامه کاملاً آشنا است ، که برای کاربرانی که با بانکهای اطلاعاتی "ارتباط برقرار می کنند" با Excel برابر هستند.

هنگامی که یک رویه فراخوانی می شود ، بلافاصله توسط خود سرور بدون پردازش های غیر ضروری و دخالت کاربر پردازش می شود. پس از آن می توانید هرگونه حذف ، اعدام ، تغییر را انجام دهید. اپراتور DDL مسئولیت همه این موارد را دارد که به تنهایی پیچیده ترین اقدامات را برای پردازش اشیاء انجام می دهد. علاوه بر این ، همه این اتفاقات خیلی سریع رخ می دهد ، و سرور در واقع لود نمی شود. این سرعت و عملکرد به شما امکان می دهد تا مقدار زیادی از اطلاعات را از کاربر به سرور و برعکس انتقال دهید.

برای اجرای این فناوری برای کار با اطلاعات ، چندین زبان برنامه نویسی وجود دارد. به عنوان مثال ، PL / SQL از سیستم های Oracle ، PSQL در سیستم های InterBase و Firebird و همچنین Transact-SQL کلاسیک مایکروسافت می باشد. همه آنها برای ایجاد و اجرای روشهای ذخیره شده طراحی شده اند ، این امکان را می دهد تا با استفاده از الگوریتم های بزرگ پایگاه داده ، از الگوریتم های خاص خود استفاده کنید. این امر برای اینكه كسانی كه چنین اطلاعاتی را مدیریت می كنند ، لازم هستند تا بتوانند از همه اشياء در برابر دسترسی غیرمجاز توسط اشخاص ثالث محافظت كنند و بر این اساس ، داده های خاصی را ایجاد ، اصلاح یا حذف كنند.

بهره وری

این اشیاء پایگاه داده می توانند به روش های مختلفی برنامه ریزی شوند. این امر به کاربران این امکان را می دهد تا نوع متد مورد استفاده را انتخاب کنند که مناسب ترین آنها خواهد بود و باعث صرفه جویی در وقت و تلاش می شود. علاوه بر این ، خود این فرآیند پردازش می شود که از زمان بسیار زیاد صرف شده برای مبادلات بین سرور و کاربر جلوگیری می کند. همچنین ، ماژول را می توان در هر لحظه مجدداً دوباره برنامه ریزی و در جهت مناسب تغییر داد. به خصوص قابل توجه سرعت اجرای روش ذخیره شده SQL: این روند سریعتر از سایرین مشابه آن است که باعث راحتی و جهانی شدن آن می شود.

ایمنی

این نوع پردازش اطلاعات با فرایندهای مشابه تفاوت دارد زیرا تضمین افزایش امنیت است. این امر با این واقعیت تأمین می شود که دسترسی سایر کاربران به رویه ها می تواند به طور کامل و کامل منتفی شود. این امر به مدیر این امکان را می دهد که بدون ترس از رهگیری اطلاعات یا دسترسی غیرمجاز به پایگاه داده ، عملیات مستقیمی با آنها انجام دهد.

انتقال اطلاعات

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

انتقال داده با استفاده از پارامتر نوع Output؛

انتقال داده ها با استفاده از عبارت Return؛

انتقال داده ها با استفاده از یک عبارت انتخاب شده.

و حالا بیایید ببینیم که این روند از درون چگونه به نظر می رسد.

1. ایجاد یک روش ذخیره شده EXEC در SQL

می توانید روی MS SQL (استودیوی Managment) ایجاد کنید. پس از ایجاد روش ، در گره پایگاه داده قابل برنامه ریزی که در آن روش ایجاد توسط اپراتور انجام می شود ، ذکر می شود. روشهای ذخیره شده SQL از یک فرآیند EXEC برای اجرای استفاده می کنند ، که حاوی نام خود شیء است.

هنگام ایجاد روال ، ابتدا نام آن ظاهر می شود ، پس از آن یک یا چند پارامتر به آن اختصاص می یابد. پارامترها ممکن است اختیاری باشند. پس از نوشتن پارامتر (ها) ، یعنی بدنه رویه ، لازم است برخی از اقدامات لازم انجام شود.

واقعیت این است که بدن می تواند متغیرهای موضعی در آن قرار داشته باشد و این متغیرها نیز با توجه به رویه ها محلی هستند. به عبارت دیگر ، آنها فقط می توانند در داخل بدن یک روش Microsoft SQL Server مشاهده شوند. سپس مراحل ذخیره شده محلی در نظر گرفته می شوند.

بنابراین ، برای ایجاد یک روش ، ما به نام روش و حداقل یک پارامتر به عنوان بدن روش نیاز داریم. توجه داشته باشید که یک گزینه عالی در این حالت ایجاد و اجرای یک رویه با نام مدار در طبقه بندیگر است.

بدنه رویه می تواند از هر نوع باشد ، به عنوان مثال مانند ایجاد جدول ، درج یک یا چند ردیف جدول ، تعیین نوع و ماهیت بانک اطلاعاتی و غیره. با این وجود ، بدنه رویه اجرای برخی از عملیات های موجود در آن را محدود می کند. برخی از محدودیت های مهم در زیر ذکر شده است:

بدن نباید روش ذخیره شده دیگری ایجاد کند.

بدن نباید ایده نادرستی از جسم ایجاد کند.

بدن نباید هیچ محرک ایجاد کند.

2. تنظیم متغیر در بدنه رویه

شما می توانید متغیرها را به صورت موضعی در بدن انجام دهید و سپس آنها منحصراً درون بدن عمل خواهند شد. این درست است که در ابتدای بدنه روند ذخیره شده ، متغیرها را ایجاد کنید. اما می توانید متغیرها را در هر نقطه از بدنه این شی نیز تنظیم کنید.

بعضی اوقات ممکن است متوجه شوید که چندین متغیر در همان خط تنظیم شده اند و هر پارامتر متغیر با کاما از هم جدا می شوند. همچنین توجه داشته باشید که متغیر دارای پیشوند @ است. در بدنه رویه ، می توانید متغیر را در جایی که می خواهید تنظیم کنید. به عنوان مثال ، متغیر @ NAME1 ممکن است به انتهای بدن روش نزدیکتر شود. به منظور اختصاص یک مقدار به متغیر اعلام شده ، از یک مجموعه داده شخصی استفاده می شود. برخلاف موقعیتی که بیش از یک متغیر در یک خط اعلام شده باشد ، در این شرایط فقط از یک مجموعه داده شخصی استفاده می شود.

اغلب کاربران این سؤال را می پرسند: "چگونه می توان چندین مقدار را در یک جمله در بدنه رویه اختصاص داد؟" خوب. سوال جالب است ، اما انجام آن بسیار ساده تر از آنچه فکر می کنید است. پاسخ: با استفاده از جفت هایی مانند "انتخاب Var \u003d Value". با جدا کردن آنها با کاما می توانید از این جفت ها استفاده کنید.

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

در مثال قبلی ، این رویه دو پارامتر را می پذیرد ، که به زبان انگلیسی به طور مشروط با نام هایState وCity خوانده می شوند. نوع داده با نوع تعریف شده در برنامه مطابقت دارد. بدنه روال دارای متغیرهای داخلیTotalAuthors (نویسندگان کل) است و از این متغیر برای نمایش تعداد آنها استفاده می شود. بعد ، یک بخش انتخاب پرس و جو ظاهر می شود که همه چیز را شمارش می کند. در آخر ، مقدار محاسبه شده با استفاده از دستور چاپ در پنجره خروجی نمایش داده می شود.

نحوه اجرای یک روش ذخیره شده در SQL

دو روش برای انجام روش وجود دارد. اولین مسیر ، پارامترهای عبوری ، نحوه اجرای لیست جدا شده با کاما را پس از نام روش نشان می دهد. فرض کنید ما دو مقدار داریم (مانند مثال قبلی). این مقادیر با استفاده از متغیرهای پارامترهای روشState وCity جمع آوری می شوند. در این روش عبور پارامترها ، ترتیب مهم است. به این روش گذر آرگومان متوالی گفته می شود. در روش دوم ، پارامترها قبلاً مستقیماً اختصاص داده شده اند ، که در این صورت ترتیب مهم نیست. این روش دوم به عنوان استدلال های نامگذاری شده شناخته می شود.

این روش ممکن است تا حدودی از حالت طبیعی منحرف شود. همه چیز همانند مثال قبلی است ، اما فقط در اینجا پارامترها جابجا می شوند. یعنی ابتدا پارامترCity ذخیره می شود وState در کنار مقدار پیش فرض ذخیره می شود. تنظیم پیش فرض معمولاً به طور جداگانه برجسته می شود. روشهای ذخیره شده SQL به عنوان پارامترهای ساده عبور می کنند. در این حالت ، به شرطی که پارامتر "UT" جایگزین مقدار پیش فرض "CA" شود. در مرحله دوم ، فقط یک مقدار آرگومان برای پارامترCity می گذرد و پارامترState مقدار پیش فرض "CA" را می گیرد. برنامه نویسان باتجربه توصیه می کنند که به طور پیش فرض تمام متغیرها در نزدیکی انتهای لیست پارامترها قرار بگیرند. در غیر این صورت ، اجرای امکان پذیر نیست ، و پس از آن شما باید با گذراندن استدلال های ذکر شده کار کنید ، که طولانی تر و پیچیده تر است.

4- روشهای ذخیره شده سرور SQL: روشهای برگشت

سه روش مهم برای ارسال داده ها به روشی ذخیره شده وجود دارد. آنها در زیر ذکر شده است:

مقدار روش ذخیره شده را برگردانید.

پارامترهای خروجی روشهای ذخیره شده.

یکی از روشهای ذخیره شده را انتخاب کنید.

4.1 بازگشت ارزش روش SQL ذخیره شده

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

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

4.2 خروجی پارامتر رویه ذخیره شده SQL

همانطور که در مثال قبلی دیدیم از مقدار پاسخ می توان برای بازگشت یک متغیر واحد استفاده کرد. استفاده از پارامتر Output به این روش امکان می دهد که یک یا چند مقدار متغیر را به تماس گیرنده ارسال کند. پارامتر خروجی دقیقاً توسط این کلمه کلیدی "Output" هنگام ایجاد روش مشخص می شود. اگر پارامتر به عنوان پارامتر خروجی مشخص شده باشد ، باید شیء عمل یک مقدار را به آن اختصاص دهد. روشهای ذخیره شده SQL که نمونه هایی از آنها در زیر مشاهده می شود ، در این حالت با اطلاعات خلاصه بازگردانده می شوند.

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

علاوه بر این ، در سناریوی قبلی ، دو متغیر اعلام شده است تا مقادیری را که روشهای ذخیره شده MS SQL Server در پارامتر خروجی تنظیم شده است ، ببینند. سپس این روش با ارسال مقدار طبیعی پارامتر "CA" انجام می شود. پارامترهای زیر خروجی هستند و بنابراین متغیرهای اعلام شده به ترتیب تعیین شده منتقل می شوند. توجه داشته باشید که هنگام انتقال متغیرها ، کلمه کلیدی خروجی نیز در اینجا تنظیم شده است. پس از موفقیت روش ، مقادیر برگشت یافته توسط پارامترهای خروجی در جعبه پیام نمایش داده می شوند.

4.3 انتخاب یکی از روشهای ذخیره شده SQL

این تکنیک برای بازگشت یک مجموعه داده (RecordSet) به روال ذخیره شده فراخوانی استفاده می شود. در این مثال SQL ، یک روش ذخیره شده با پارامترهایAuthID از جدول مؤلفان با فیلتر کردن سوابق برگشتی با استفاده از این پارامترAuthId پرس و جو می کند. عبارت Select تصمیم می گیرد که چه چیزی باید به تماس گیرنده روش ذخیره شده برگردد. هنگامی که روش ذخیره شده انجام شود ، AuthId به عقب برگردانده می شود. چنین روشی در اینجا همیشه فقط یک رکورد یا هیچکدام را برمی گرداند. اما رویه ذخیره شده هیچ محدودیتی برای بازگشت بیش از یک رکورد ندارد. شما اغلب می توانید نمونه هایی را پیدا کنید که در آن بازگرداندن داده ها با استفاده از پارامترهای منتخب با مشارکت متغیرهای محاسبه شده با ارائه چندین کل انجام می شود.

سرانجام

یک روش ذخیره شده یک ماژول نرم افزاری بسیار جدی است که بازگشت یا عبور می کند و همچنین به لطف برنامه مشتری ، متغیرهای لازم را تنظیم می کند. از آنجا که روش ذخیره شده روی خود سرور اجرا می شود ، می توان از تبادل داده عظیم بین سرور و برنامه مشتری (برای برخی محاسبات) جلوگیری کرد. این به شما امکان می دهد تا بار روی سرور SQL را کاهش دهید ، که البته به دست دارندگان آنها می رود. یکی از زیرگروهها روشهای ذخیره شده T SQL است ، اما افرادی که نیاز به ایجاد بانک اطلاعاتی چشمگیر دارند ، باید آنها را بیاموزند. همچنین تعداد زیادی تفاوت های ظریف و حتی بزرگ وجود دارد که می توانند هنگام مطالعه رویه های ذخیره شده مفید باشند ، اما این مورد بیشتر برای کسانی که قصد دارند از نزدیک با برنامه نویسی از جمله حرفه ای کار کنند ، بیشتر مورد نیاز است.

هدف، واقعگرایانه - نحوه ایجاد و استفاده از روشهای ذخیره شده را در سرور پایگاه داده بیاموزید.

1. مطالعه کلیه نمونه ها ، تجزیه و تحلیل نتایج عملکرد آنها در استودیوی مدیریت SQL سرور نرم افزار. بررسی مراحل ایجاد شده در پایگاه داده فعلی.

2. انجام تمام مثالها و وظایف در طول کار آزمایشگاهی.

3. انجام تکالیف فردی با توجه به گزینه ها.

توضیحات کار

برای تسلط بر برنامه نویسی رویه های ذخیره شده ، از نمونه ای از بانک اطلاعاتی به نام استفاده می کنیم DB_Books، که در کار آزمایشگاه شماره یک ایجاد شده است. هنگام انجام نمونه ها و کارها ، به مکاتبات نام پایگاه داده ، جداول و سایر اشیاء پروژه توجه کنید.

روشهای ذخیره شده آنها مجموعه ای از دستورات متشکل از یک یا چند عبارت SQL یا عملکرد هستند و در یک بانک اطلاعاتی به شکل کامپایل شده ذخیره می شوند.

انواع روشهای ذخیره شده

رویه های ذخیره شده سیستم برای انجام اقدامات مختلف اداری طراحی شده اند. تقریباً تمام اقدامات مدیریت سرور با استفاده از آنها انجام می شود. می توانیم بگوییم که رویه های ذخیره شده در سیستم ، رابطی هستند که کار با جداول سیستم را فراهم می کند. رویه های ذخیره شده سیستم با sp_ پیشوند شده اند ، که در بانک اطلاعاتی سیستم ذخیره می شوند و می توانند در متن هر پایگاه داده دیگری فراخوانی شوند.

روشهای ذخیره شده توسط کاربر اقدامات خاصی را پیاده سازی می کنند. مراحل ذخیره شده یک شیء کامل از پایگاه داده است. در نتیجه ، هر روش ذخیره شده در یک پایگاه داده خاص ، جایی که اجرا می شود ، قرار دارد.

روشهای ذخیره شده موقت فقط برای مدتی وجود دارند ، پس از آن بطور خودکار توسط سرور از بین می روند. آنها به محلی و جهانی تقسیم می شوند. روشهای ذخیره شده موقت محلی را می توان فقط از ارتباطی که در آن ایجاد شده است فراخوانی کرد. هنگام ایجاد چنین روشی ، باید نامی از آن شروع شود که با یک # کاراکتر واحد شروع شود. مانند همه اشیاء موقتی ، وقتی کاربر سرور را قطع ، مجدداً متوقف می کند یا متوقف می شود ، رویه های ذخیره شده از این نوع به طور خودکار حذف می شوند. رویه های ذخیره شده موقت جهانی برای کلیه اتصالات سرور که رویه یکسانی دارند ، در دسترس هستند. برای تعریف آن ، فقط با شروع کاراکترهای ## نامی به او بدهید. این روشها با راه اندازی مجدد یا متوقف شدن سرور حذف می شوند ، یا هنگامی که اتصال در زمینه ایجاد شده بسته می شود.

ایجاد ، تغییر رویه های ذخیره شده

ایجاد یک روش ذخیره شده شامل حل کارهای زیر است: برنامه ریزی حقوق دسترسی. هنگام ایجاد یک روش ذخیره شده ، باید در نظر داشت که این حق دسترسی به اشیاء پایگاه داده با کاربرانی که آن را ایجاد کرده اند ، خواهد داشت. تعریف پارامترهای یک روش ذخیره شده ؛ رویه های ذخیره شده ممکن است دارای پارامترهای ورودی و خروجی باشند. توسعه کد روش ذخیره شده کد رویه می تواند شامل توالی از هر دستور SQL باشد ، از جمله فراخوانی سایر مراحل ذخیره شده.

نحو برای ایجاد یک روش جدید یا اصلاح یک روش ذخیره شده موجود در نمادهای سرور MS SQL است:

(CREATE | ALTER) PROC [EDURE] procedure_name [؛ number] [(@ parameter_name data_type) [VARYING] [\u003d DEFAULT] [OUTPUT]] [، ... n] [with (recute | ENCRYPTION | نسخه ، ENCRYPTION)] [برای درخواست] AS sql_operator [... n]

پارامترهای این دستور را در نظر بگیرید.

با استفاده از پیشوندهای sp_، #، ## روش ایجاد شده می تواند به صورت سیستم یا موقتی تعریف شود. همانطور که از نحوی دستور مشاهده می شود ، مجاز نیست نام مالک را که رویه ایجاد شده به چه کسی تعلق خواهد گرفت ، و همچنین نام بانک اطلاعاتی را که باید در آن قرار داشته باشد ، مشخص کنید. بنابراین ، برای قرار دادن رویه ذخیره شده در یک پایگاه داده خاص ، لازم است دستور CREATE PROCEDURE را در بستر این پایگاه داده اجرا کنید. هنگام دسترسی به اشیاء همان بانک اطلاعاتی از بدنه رویه ذخیره شده ، می توانید از اسامی کوتاه شده استفاده کنید ، یعنی بدون مشخص کردن نام بانک اطلاعاتی. هنگامی که شما نیاز به مراجعه به اشیاء واقع در پایگاه داده های دیگر دارید ، تعیین نام بانک اطلاعاتی لازم است.

برای انتقال داده های ورودی و خروجی در روش ذخیره شده ، نام پارامترها باید با نماد @ شروع شوند. می توانید پارامترهای زیادی را در یک روش واحد ذخیره شده ، جدا شده با کاما مشخص کنید. بدنه رویه نباید از متغیرهای محلی استفاده کند که نام آنها با نام پارامترهای این روش مطابقت دارد. هر نوع داده SQL ، از جمله تعریف شده توسط کاربر ، برای تعیین نوع داده پارامترهای روش ذخیره شده مناسب است. با این حال ، نوع داده CURSOR فقط به عنوان پارامتر خروجی یک روش ذخیره شده ، می تواند مورد استفاده قرار گیرد. با کلمه کلیدی OUTPUT.

وجود کلید واژه OUTPUT بدین معنی است که پارامتر مربوطه برای بازگشت داده ها از رویه ذخیره شده در نظر گرفته شده است. با این حال ، این به هیچ وجه به معنای این نیست که پارامتر برای انتقال مقادیر به یک روش ذخیره شده مناسب نیست. مشخص کردن کلید واژه OUTPUT به سرور دستور می دهد تا از روش ذخیره شده خارج شود تا مقدار پارامتر فعلی را به یک متغیر محلی اختصاص دهد که هنگام فراخوانی این روش به عنوان مقدار پارامتر مشخص شده است. توجه داشته باشید که هنگام تعیین کلید واژه OUTPUT ، مقدار پارامتر مربوطه هنگام فراخوانی یک روش فقط با استفاده از یک متغیر محلی قابل تنظیم است. استفاده از هرگونه عبارت یا ثابت که برای پارامترهای عادی معتبر باشد مجاز نیست. کلمه کلیدی VARYING در رابطه با پارامتر OUTPUT از نوع CURSOR استفاده می شود. تعیین می کند که مجموعه حاصل پارامتر خروجی خواهد بود.

کلمه کلیدی DEFAULT مقداری است که پارامتر پیش فرض مربوطه از آن می گیرد. بنابراین ، هنگام فراخوانی یک رویه ، نمی توانید صریحاً پارامتر مربوطه را مشخص کنید.

از آنجا که سرور برنامه اجرای نمایش داده شد و کد کامپایل شده را ذخیره می کند ، از مقادیر آماده در طی فراخوانی رویه بعدی استفاده می شود. با این حال ، در برخی موارد ، هنوز لازم است کد روش را دوباره تلفیق کنید. مشخص کردن کلید واژه RECOMPILE به سیستم دستور می دهد تا هر بار که بخواهید یک برنامه برای اجرای روش ذخیره شده ایجاد کند.

پارامتر FOR REPLICATION هنگام تکثیر داده ها و از جمله روش ذخیره شده ایجاد شده به عنوان مقاله در انتشار ، تقاضا می شود. کلمه کلیدی ENCRYPTION به سرور دستور می دهد تا کد روش ذخیره شده را رمزگذاری کند ، که می تواند از محافظت در برابر استفاده از الگوریتم های اختصاصی اجرای روش ذخیره شده استفاده کند. کلمه کلیدی AS در ابتدای بدنه روش ذخیره شده قرار دارد. تقریباً تمام دستورات SQL می توانند در بدنه یک رویه استفاده شوند ، معاملات اعلام می شوند ، قفل ها تنظیم می شوند و سایر مراحل ذخیره شده نامیده می شوند. می توانید با استفاده از دستور RETURN از روش ذخیره شده خارج شوید.

روش ذخیره شده را حذف کنید

DROP PROCEDURE (Procedure_name) [، ... n]

اجرای روش ذخیره شده

برای اجرای روش ذخیره شده ، از این دستور استفاده کنید: [[EXEC [UTE] Procedure_name [؛ شماره] [[@ parameter_name \u003d] (مقدار | @ متغیر_نام) [OUTPUT] | [DEFAULT]] [، ... n]

اگر فراخوانی رویه ذخیره شده تنها دستور موجود در بسته نیست ، دستور EXECUTE لازم است. علاوه بر این ، این دستور موظف است یک روش را از بدن دیگر روش یا ماشه فراخوانی کند.

استفاده از کلید واژه OUTPUT هنگام فراخوانی یک روش فقط برای پارامترهایی که هنگام ایجاد مراحل با کلمه کلیدی OUTPUT اعلام شده اند مجاز است.

هنگامی که هنگام فراخوانی یک روش ، کلید واژه DEFAULT برای یک پارامتر مشخص می شود ، از مقدار پیش فرض استفاده می شود. به طور طبیعی ، کلمه مشخص شده DEFAULT فقط برای پارامترهایی که مقدار پیش فرض برای آنها تعریف شده مجاز است.

نحو دستور EXECUTE نشان می دهد که هنگام فراخوانی یک روش می توان نام پارامترها را حذف کرد. با این حال ، در این حالت ، کاربر باید مقادیر پارامترها را به همان ترتیبی که در هنگام ایجاد روال ذکر شده است ، مشخص کند. اختصاص یک مقدار پیش فرض به یک پارامتر با صرفنظر کردن از آن در هنگام شمارش غیرممکن است. در صورت نیاز به حذف پارامترهایی که مقدار پیش فرض برای آنها تعریف شده است ، صریحاً در هنگام فراخوانی با روش ذخیره شده ، نام پارامترها را به طور صریح مشخص کنید. علاوه بر این ، در این روش می توانید پارامترها و مقادیر آنها را به ترتیب دلخواه لیست کنید.

توجه داشته باشید که هنگام فراخوانی یک رویه ، هر دو نام پارامتر با مقادیر مشخص شده اند ، یا فقط مقادیر بدون نام پارامتر دارند. ترکیب آنها مجاز نیست.

استفاده از RETURN به روال ذخیره شده

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

CREATE PROCEDURE Count_Books AS SELECT COUNT (Code_book) FROM Books GO

تمرین 1.

تعداد EXEC_Books

نتیجه را بررسی کنید.

نمونه ای از ایجاد روشی با پارامتر ورودی:

ایجاد PROCEDURE Count_Books_PagesCount_pages به عنوان COUNT SELECT COUNT (Code_book) از کتابهایی که در آن صفحات\u003e \u003dCount_pages GO

وظیفه 2. این روش را در بخش Stored Procedures در پایگاه داده DB_Books با استفاده از ابزار SQL server Management Studio ایجاد کنید. آن را با دستور اجرا کنید

EXEC Count_Books_Pages 100

نتیجه را بررسی کنید.

نمونه ای از ایجاد روشی با پارامترهای ورودی:

ایجاد PROCEDURE Count_Books_TitleCount_pages AS INT ،Title AS CHAR (10) AS SELECT COUNT (Code_book) از کتاب های WHERE Pages\u003e \u003dCount_pages و عنوان_ کتاب LIKETitle GO

وظیفه 3. این روش را در بخش Stored Procedures در پایگاه داده DB_Books با استفاده از ابزار SQL server Management Studio ایجاد کنید. آن را با دستور اجرا کنید

EXEC Count_Books_Title 100 ، "P٪"

نتیجه را بررسی کنید.

نمونه ای از ایجاد روشی با پارامترهای ورودی و پارامتر خروجی:

ایجاد PROCEDURE Count_Books_ItogoCount_pages INT ،Title CHAR (10) ،Itogo INT OUTPUT AS SELECTItogo \u003d COUNT (Code_book) از کتابهایی که در آن صفحات\u003e \u003dCount_pages و عنوان_ کتاب LIKETitle GO

وظیفه 4. این روش را در بخش Stored Procedures در پایگاه داده DB_Books با استفاده از ابزار SQL server Management Studio ایجاد کنید. با استفاده از مجموعه دستورات اجرا کنید:

Sql\u003e اعلامq به عنوان int EXEC Count_Books_Itogo 100 ، "P٪" ،q خروجیq را انتخاب کنید

نتیجه را بررسی کنید.

نمونه ای از ایجاد روشی با پارامترهای ورودی و RETURN:

PROCEDURE را ایجاد کنید checknameparam را به عنوان (SELECT name_author از نویسندگان انتخاب کنید که در آن کد_author \u003dparam) \u003d "پوشکین A.S." RETURN 1 بازگرداندن 2

وظیفه 5. این روش را در بخش Stored Procedures در پایگاه داده DB_Books با استفاده از ابزار SQL server Management Studio ایجاد کنید. آن را با استفاده از دستورات اجرا کنید:

DECLAREreturn_status INT EXECreturn_status \u003d نام انتخاب 1 انتخاب "وضعیت بازگشت" \u003dreturn_status

نمونه ای از ایجاد روشی بدون پارامتر برای افزایش مقدار فیلد کلیدی در جدول خریدها 2 بار:

CREATE PROC update_proc را به عنوان به روز کردن خریدها تنظیم کنید Code_purchase \u003d کد_ خرید * 2

وظیفه 6. این روش را در بخش Stored Procedures در پایگاه داده DB_Books با استفاده از ابزار SQL server Management Studio ایجاد کنید. آن را با دستور اجرا کنید

EXEC update_proc

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

ایجاد PROC را انتخاب کنید_authork CHAR (30) به عنوان انتخاب * از نویسندگان که در آن نام_author \u003dk

وظیفه 7.

EXEC Select_author "پوشکین A.S." یا Select_author @ k \u003d "پوشکین A.S." یا EXEC Select_author @ k \u003d "پوشکین A.S."

نمونه ای از ایجاد یک رویه با یک پارامتر ورودی و یک مقدار پیش فرض برای افزایش مقدار فیلد کلیدی در جدول خریدها توسط تعداد معین بار (به طور پیش فرض 2 بار):

CREATE PROC update_procp INT \u003d 2 به عنوان به روز کردن خریدها تنظیم کنید Code_purchase \u003d Code_purchase *p

این روش داده ای را بر نمی گرداند.

وظیفه 8. این روش را در بخش Stored Procedures در پایگاه داده DB_Books با استفاده از ابزار SQL server Management Studio ایجاد کنید. آن را با استفاده از دستورات اجرا کنید:

EXEC update_proc 4 یا EXEC update_procp \u003d 4 یا EXEC update_proc - مقدار پیش فرض استفاده خواهد شد.

نمونه ای از ایجاد روشی با پارامترهای ورودی و خروجی. روشی را برای تعیین تعداد سفارشات تکمیل شده در یک دوره مشخص ایجاد کنید:

ایجاد PROC count_purchases @ d1 SMALLDATETIME ، @ d2 SMALLDATETIME ،c INT را خارج از خانه به عنوان SELECT @ c \u003d COUNT (Code_purchase) از خریدهای از کجا Date_order BETWEEN @ d1 و @ d2 SETc \u003d ISNULL (c ، 0)

وظیفه 9. این روش را در بخش Stored Procedures در پایگاه داده DB_Books با استفاده از ابزار SQL server Management Studio ایجاد کنید. آن را با استفاده از دستورات اجرا کنید:

DECLARE @ c2 INT EXEC count_purchases '01 - ژوئن 2006 "،" 01 - ژوئیه - 2006 "، @ c2 OUTPUT SELECT @ c2

گزینه هایی برای کارهای آزمایشگاهی №4

مقررات عمومی در برنامه SQL Server Management Studio ، صفحه جدیدی را برای کد ایجاد کنید (دکمه ایجاد درخواست). برای فعال کردن برنامه نویسی ، پایگاه داده DB_Books ایجاد شده با استفاده از عبارت Use را فعال کنید. روشهای ذخیره شده را با استفاده از عبارتهای ایجاد شده ایجاد کنید ، و نام مراحل را خودتان تعریف کنید. هر روش یک سؤال SQL را که در کار آزمایشگاه دوم انجام شده است ، انجام می دهد. علاوه بر این ، کد جستجوی SQL باید تغییر کند تا بتواند مقادیر فیلدهای جستجو شده را منتقل کند.

به عنوان مثال ، کار اولیه و درخواست در کار آزمایشگاه شماره 2:

/ * از فهرست فهرست تأمین کنندگان (تحویل جدول) نام شرکت ها ، تلفن ها و TIN (زمینه های Name_company ، تلفن و INN) را انتخاب کنید ، که نام شرکت (فیلد name_company) OJSC MIR است.

SELECT name_company ، تلفن ، INN از تحویل های WHEE Name_company \u003d "OJSC MIR"

* / - در این کار ، رویه ایجاد می شود:

CREATE PROC را انتخاب کنید name_name_companycomp CHAR (30) به عنوان SELECT name_company ، تلفن ، از طریق تحویل از کجا نام_company \u003dcomp

- برای شروع روش ، از دستور استفاده کنید:

EXEC select_name_company "OJSC MIR"

فهرست وظیفه یا لیست کار

در SQL Server Management Studio ، یک برنامه جدید ایجاد کنید. برای فعال ساختن برنامه نویسی ، با استفاده از عملگر Use ، یک بانک اطلاعاتی جداگانه ایجاد شده در کار آزمایشگاه شماره 1 را فعال کنید. روشهای ذخیره شده را با استفاده از عبارتهای ایجاد شده ایجاد کنید ، و نام مراحل را خودتان تعریف کنید. هر روش یک پرس و جو SQL را اجرا می کند ، که مطابق گزینه ها به عنوان کارهای جداگانه ارائه می شوند.

انتخاب 1

1. نمایش لیستی از کارمندانی که حداقل یک فرزند دارند.

2. لیستی از کودکانی را که در مدت زمان مشخص هدایایی به آنها داده شده است ، نمایش دهید.

3. لیستی از والدینی که فرزندان خردسال دارند را به نمایش بگذارید.

4. نمایش اطلاعات در مورد هدایا با ارزش بیشتر از تعداد مشخص شده ، مرتب شده بر اساس تاریخ.

گزینه 2

1. لیستی از دستگاه ها را با نوع مشخص شده نمایش دهید.

2. تعداد دستگاه های تعمیر شده و کل هزینه تعمیرات را از استاد مشخص شده چاپ کنید.

3- صاحبان دستگاه ها و تعداد تماس های آنها را ، بر اساس تعداد تماس ها به ترتیب نزولی ، لیست کنید.

4- نمایش اطلاعات در مورد استادان دارای ترشح بیشتر از تعداد مشخص شده یا با تاریخ اشتغال کمتر از تاریخ معین.

گزینه 3

2. لیستی از کدهای فروش را نشان دهید که برای آن گلها به ارزش بیش از تعداد مشخص شده فروخته شده اند.

3. چاپ تاریخ فروش ، مبلغ ، فروشنده و گل برای کد فروش مشخص شده.

4- لیستی از گلها و انواع گلها را با ارتفاع بیشتر از تعداد مشخص شده یا شکوفه ها به نمایش بگذارید.

گزینه 4

1. لیستی از داروهای دارای نشانگر ذکر شده برای استفاده را نمایش دهید.

2. لیستی از تاریخ های زایمان را نشان دهید که بیشتر از تعداد مشخص شده داروی با همین نام فروخته شده است.

3. تاریخ تحویل ، میزان ، نام رهبر از تأمین کننده و نام دارو را با کد دریافت بیشتر از شماره مشخص شده چاپ کنید.

گزینه 5

2. به دلایل ذکر شده لیستی از تجهیزات رفع شده را نمایش دهید.

3- تاریخ دریافت ، نام تجهیزات ، نام شخص مسئول و تاریخ خاموش کردن تجهیزات خاموش شده در دوره مشخص را نمایش دهید.

4- لیستی از تجهیزات با نوع مشخص یا با تاریخ ورود بیشتر از یک مقدار مشخص را نشان دهید

گزینه 6

1. لیستی از ظروف را با وزن بیشتر از تعداد مشخص شده نمایش دهید.

2. لیستی از محصولات را به نام خود نشان دهید که قطعه مشخص شده از کلمه در آن رخ می دهد.

3. نمایش حجم محصول ، نام ظرف ، نام محصول با کد ظرف از مقدار اولیه مشخص شده برای یک مقدار نهایی خاص.

4- ترتیب تهیه ظرف و نام ظرف را با مقدار کربوهیدرات بیشتر از یک مقدار مشخص یا تعداد کالری بیشتر از یک مقدار مشخص چاپ کنید.

گزینه 7

1. نمایش لیستی از کارمندان با موقعیت مشخص شده.

3. چاپ تاریخ ثبت ، نوع سند ، نام ثبت و نام سازمان برای اسناد ثبت شده در دوره معین.

4- لیستی از اسناد ثبت شده را با نوع خاصی از سند یا با تاریخ ثبت بیشتر از مقدار مشخص شده نشان دهید.

گزینه 8

1. نمایش لیستی از کارمندان با دلیل مشخص اخراج.

3. چاپ تاریخ ثبت نام ، دلیل اخراج ، نام کارمند برای اسناد ثبت شده در دوره معین.

گزینه 9

1. لیستی از کارمندانی که از نوع مشخص تعطیل شده اند را نمایش دهید.

2. لیستی از اسناد با تاریخ ثبت را در مدت زمان مشخص نمایش دهید.

3. چاپ تاریخ ثبت نام ، نوع تعطیلات ، نام کامل کارمند برای اسنادی که در مدت زمان مشخص شده ثبت شده اند.

4- لیستی از اسناد ثبت شده با کد اسناد را در محدوده مشخص شده نمایش دهید.

گزینه 10

1. نمایش لیستی از کارمندان با موقعیت مشخص شده.

2. لیستی از اسناد حاوی قطعه مشخص شده از کلمه را نمایش دهید.

3. چاپ تاریخ ثبت ، نوع سند ، نام فرستنده و نام سازمان برای اسناد ثبت شده در دوره معین.

4- لیستی از اسناد ثبت شده را با نوع سند مشخص یا با کد اسناد کمتر از یک مقدار مشخص نمایش دهید.

گزینه 11

1. لیستی از کارمندان منصوب به موقعیت مشخص شده را نمایش دهید.

2. لیستی از اسناد با تاریخ ثبت را در مدت زمان مشخص نمایش دهید.

3. چاپ تاریخ ثبت نام ، موقعیت ، نام کارمند برای اسناد ثبت شده در دوره مشخص شده.

4- لیستی از اسناد ثبت شده با کد اسناد را در محدوده مشخص شده نمایش دهید.

گزینه 12

3. افرادی که تجهیزات را اجاره داده اند و تعداد درخواست های آنها را مرتب کنید بر اساس تعداد درخواست ها به ترتیب نزولی.

گزینه 13

1. لیستی از تجهیزات را با نوع مشخص شده نمایش دهید. 2. تجهیزاتی را که یک کارمند خاص خاموش نوشته است ، لیست کنید.

3. تعداد تجهیزات جداشده گروه بندی شده براساس نوع تجهیزات را چاپ کنید.

4- نمایش اطلاعات در مورد کارمندان با تاریخ استخدام بیشتر از یک تاریخ معین.

گزینه 14

1. لیستی از گلها را با نوع مشخص شده برگ نشان دهید.

2. لیستی از کدهای دریافت را نشان دهید که گل های فروخته شده آنها بیش از یک مقدار مشخص ارزش دارند.

3. چاپ تاریخ دریافت ، مقدار ، نام تهیه کننده و رنگ برای کد مشخص تهیه کننده.

4- لیستی از گلها و انواع گلها را با ارتفاع بیشتر از تعداد مشخص یا شکوفه ها به نمایش بگذارید.

گزینه 15

1. لیستی از مشتریانی را که در دوره مشخص شده ثبت نام کرده اند ، نمایش دهید.

2. کل مبلغ پرداخت اتاق ها را برای هر مشتری چاپ کنید.

3. نمایش تاریخ ورود ، نوع اتاق ، نام کامل مشتریانی که در مدت زمان مشخص شده ثبت شده اند.

4- لیستی از مشتریان ثبت نام شده را در نوع خاصی از اتاق نمایش دهید.

گزینه 16

1. لیستی از تجهیزات را با نوع مشخص شده نمایش دهید.

2. تجهیزاتی را که مشتری خاص اجاره کرده است لیست کنید.

3. افرادی که تجهیزات را اجاره داده اند و تعداد درخواست های آنها را با توجه به تعداد درخواست ها به ترتیب نزولی لیست کنید.

4. نمایش اطلاعات در مورد مشتری طبقه بندی شده بر اساس آدرس.

گزینه 17

1. لیستی از مقادیر با ارزش خرید بیشتر از یک مقدار خاص یا با مدت ضمانت بیشتر از یک عدد مشخص را نمایش دهید.

2. مکان دارایی های مادی را به اسم کلمه مشخص شده در آن لیست کنید.

3. مقدار مقدار مقادیر را با یک کد در محدوده مشخص شده چاپ کنید.

4- لیستی از افراد مسئول مالی را با تاریخ اشتغال در محدوده مشخص شده نمایش دهید.

گزینه 18

1. لیستی از کارهای تعمیراتی را که توسط یک استاد خاص انجام شده است نمایش دهید.

2. لیستی از مراحل کار گنجانده شده در کار را نشان دهید ، به اسم آن کلمه مشخص شده ظاهر می شود.

۳- مبلغ هزینه مراحل کار تعمیر را برای کار با کد در محدوده مشخص شده چاپ کنید.

4- لیستی از اساتید با تاریخ اشتغال را در محدوده مشخص شده نمایش دهید.

گزینه 19

1. لیستی از داروها را با یک نشانه خاص نمایش دهید.

2. لیستی از شماره های چک را نشان دهید که بیش از تعداد مشخصی از داروها برای آنها فروخته شده است.

3. تاریخ فروش ، مبلغ ، نام کامل صندوق دار و دارو را روی چک با شماره مشخص شده چاپ کنید.

4- لیستی از داروها و واحدهای اندازه گیری داروها را با مقدار بیشتر از یک تعداد مشخص یا کد دارویی کمتر از یک مقدار مشخص نمایش دهید.

گزینه 20

1. نمایش لیستی از کارمندان با موقعیت مشخص شده.

2. لیستی از اسناد حاوی قطعه مشخص شده از کلمه را نمایش دهید.

3. چاپ تاریخ ثبت ، نوع سند ، نام پیمانکار و واقعیت اجرای اسناد ثبت شده در دوره معین.

4- لیستی از اسناد ثبت شده را با نوع سند مشخص یا با کد اسناد در محدوده مشخص نمایش دهید.

MySQL 5 دارای بسیاری از ویژگی های جدید است که یکی از مهمترین آنها ایجاد مراحل ذخیره شده است. در این درس ، من در مورد آنچه آنها هستند و چگونه آنها می توانند زندگی شما را آسان تر صحبت خواهم کرد.

مقدمه

یک روش ذخیره شده راهی برای کپسوله کردن اقدامات تکراری است. در مراحل ذخیره شده ، می توانید متغیرها را اعلام کنید ، جریان داده ها را کنترل کنید و سایر تکنیک های برنامه نویسی را اعمال کنید.

دلیل ایجاد آنها مشخص است و با استفاده مکرر تأیید می شود. از طرف دیگر ، اگر با افرادی که به طور نامنظم با آنها همکاری می کنند صحبت کنید ، نظرات به دو طرف کاملاً متضاد تقسیم می شوند. فراموش نکنید.

پشت

• منطق به اشتراک گذاشتن با برنامه های دیگر. روشهای ذخیره شده عملکردی را محاسبه می کنند. این اتصال به دسترسی به داده ها و مدیریت بین برنامه های مختلف را فراهم می کند.
• جداسازی کاربران از جداول بانک اطلاعاتی. این به شما امکان می دهد به مراحل ذخیره شده دسترسی داشته باشید ، اما نه به داده های جدول.
• مکانیسم محافظت را فراهم می کند. مطابق بند قبلی ، اگر فقط از طریق روشهای ذخیره شده می توانید به داده دسترسی داشته باشید ، هیچ کس دیگری نمی تواند داده های شما را از طریق فرمان SQL DELETE پاک کند.
• عملکرد بهبود یافته در نتیجه کاهش ترافیک شبکه. با استفاده از روشهای ذخیره شده ، می توان چندین نمایش داده را با هم ترکیب کرد.

در برابر

• افزایش بار در سرور پایگاه داده به دلیل این واقعیت است که بیشتر کارها در سمت سرور انجام می شود و کمتر در سمت مشتری.
• چیزهای زیادی برای یادگیری وجود دارد برای نوشتن رویه های ذخیره شده شما باید نحو عبارات MySQL را یاد بگیرید.
• شما منطق برنامه خود را در دو مکان کپی می کنید: کد سرور و کد روش های ذخیره شده ، در نتیجه روند دستکاری داده را پیچیده می کنید.
• مهاجرت از یک DBMS به دیگری (DB2 ، SQL Server و غیره) می تواند منجر به مشکلات شود.

ابزاری که در آن کار می کنم MySQL Query Browser نام دارد ، برای تعامل با بانکهای اطلاعاتی کاملاً استاندارد است. ابزار خط فرمان MySQL گزینه عالی دیگری است. من در این مورد به شما می گویم به این دلیل که phpMyAdmin مورد علاقه همه از اجرای مراحل ذخیره شده پشتیبانی نمی کند.

به هر حال من از یک ساختار جدول ابتدایی استفاده می کنم تا درک این موضوع برای شما راحت تر باشد. من در مورد روشهای ذخیره شده صحبت می کنم ، و آنها به اندازه کافی پیچیده هستند که به ساختار فشرده جدول ها نفوذ کنند.

مرحله 1: Limiter را تنظیم کنید

delimiter یک کاراکتر یا رشته کاراکتر است که برای گفتن مشتری MySQL استفاده می کند که شما نوشتن یک جمله SQL را تمام کرده اید. برای سنین پایین ، تعیین کننده شخصیت جمع و زمانه بوده است. با این حال ، مشکلات ممکن است رخ دهد ، از آنجا که در یک روش ذخیره شده می توانید چندین عبارت را داشته باشید که هر یک از آنها باید با یک جمع رنگ پایان یابد. در این آموزش از رشته "//" به عنوان تعیین کننده استفاده می کنم.

مرحله 2: نحوه کار با رویه های ذخیره شده

ایجاد یک روش ذخیره شده

DELIMITER // CREATE PROCEDURE `p2` () LANGUAGE SQL DETERMINISTIC SQL Security Definition COMMENT" یک روش "شروع" انتخاب "سلام جهانی!"؛ پایان //

قسمت اول کد روندی ذخیره شده ایجاد می کند. مورد بعدی شامل پارامترهای اختیاری است. سپس نام و سرانجام بدن روش انجام می شود.

نام روشهای ذخیره شده حساس به مورد هستند. شما همچنین نمی توانید چندین روش با همین نام ایجاد کنید. در روش ذخیره شده عبارتی وجود ندارد که خود پایگاه داده را تغییر دهد.

4 ویژگی یک روش ذخیره شده:

• زبان: برای اهداف قابلیت حمل ، SQL بصورت پیش فرض مشخص شده است.
• Deterministic: اگر رویه تمام مدت نتیجه مشابه را برگرداند و همان پارامترهای ورودی را بپذیرد این مربوط به روند تکثیر و ثبت نام است. مقدار پیش فرض DETERMINISTIC نیست.
• Security SQL: در حین تماس ، حقوق کاربر بررسی می شود. INVOKER کاربر استعلام روش ذخیره شده است. DEFINER "خالق" روش است. مقدار پیش فرض DEFINER است.
• نظر: برای اهداف مستند سازی ، مقدار پیش فرض ""

تماس با روش ذخیره شده

برای تماس با یک روش ذخیره شده ، باید کلمه کلیدی CALL ، سپس نام روش را وارد کنید و پارامترها (متغیرها یا مقادیر) را در براکت ها مشخص کنید. براکت لازم است.

CALL store_procedure_name (param1 ، param2 ، ....) روش فراخوان 1 (10 ، "پارامتر رشته" ،parameter_var)؛

روال ذخیره شده اصلاح شده

MySQL برای تغییر در روشها یک عبارت ALC PROCEDURE دارد ، اما برای اصلاح فقط چند ویژگی مناسب است. اگر نیاز به تغییر پارامترها یا بدنه رویه دارید ، باید آن را حذف کرده و دوباره از نو مجدداً استفاده کنید.

روش ذخیره شده را حذف کنید

PROCEDURE DROP در صورت وجود P2؛

این یک دستور ساده است. اگر چنین روشی وجود نداشته باشد ، عبارت IF EXISTS خطا را جلب می کند.

مرحله 3: گزینه ها

بیایید ببینیم چگونه می توانید پارامترها را به روشی ذخیره شده منتقل کنید.

• ایجاد فرآیند proc1 (): لیست پارامترهای خالی
• PROCEDURE PROCEDURE را ایجاد کنید (در Varname DATA-TYPE): یک پارامتر ورودی. کلمه IN اختیاری است زیرا پارامترهای پیش فرض IN (ورودی) هستند.
• PROCEDURE PROCEDURE را ایجاد کنید (OUT Varname DATA-TYPE): یک پارامتر بازگشت.
• PROCEDURE PROCEDURE را ایجاد کنید (INOUT varname DATA-TYPE): یک پارامتر ، ورودی و برگشتی.

به طور طبیعی ، می توانید چندین پارامتر در انواع مختلف تنظیم کنید.

به عنوان مثال پارامتر

DELIMITER // CREATE PROCEDURE `proc_IN` (IN var1 INT) BEGIN SELECT var1 + 2 AS نتیجه؛ پایان //

مثال پارامتر OUT

DELIMITER // CREATE PROCEDURE `proc_OUT` (OUT var1 VARCHAR (100)) BEGIN SET var1 \u003d" این یک تست است "؛ پایان //

به عنوان مثال پارامتر

DELIMITER // CREATE PROCEDURE `proc_INOUT` (OUT var1 INT) BEGIN SET var1 \u003d var1 * 2؛ پایان //

مرحله 4: متغیرها

اکنون من به شما می آموزم که چگونه متغیرها را ایجاد کنید و آنها را درون مراحل ذخیره کنید. شما باید آنها را صریحاً در ابتدای بلوک شروع / پایان ، به همراه انواع داده های آنها اعلام کنید. پس از اعلام متغیر ، می توانید از آن در همان مکان به عنوان متغیرهای جلسه ، الفبای یا نام ستونها استفاده کنید.

نحو متن متغیر به شرح زیر است:

اعلام نام و نام خانوادگی DATA-TYPE DEFAULT پیش فرض؛

بیایید چند متغیر را اعلام کنیم:

اعلامیه a ، b INT DEFAULT 5؛ دکور خیابان VARCHAR (50)؛ امروز اعلام کنید TIMESTAMP DEFAULT CURRENT_DATE؛ DECLARE v1، v2، v3 TINYINT؛

با متغیرها کار کنید

پس از اعلام متغیر ، می توانید مقدار آن را با استفاده از دستورات SET یا SELECT تنظیم کنید:

DELIMITER // ایجاد فرآیند `var_proc` (در پارامتر VARCHAR (20)) شروع اعلامیه a ، b INT DEFAULT 5؛ دکور خیابان VARCHAR (50)؛ امروز اعلام کنید TIMESTAMP DEFAULT CURRENT_DATE؛ DECLARE v1، v2، v3 TINYINT؛ درج جدول 1 ارزش ها (الف)؛ SET str \u003d "من رشته هستم"؛ CONCAT SELECT (خیابان ، پارامتر) ، امروز از جدول2 WHERE b\u003e \u003d 5؛ پایان //

مرحله 5: ساختارهای مدیریت جریان

MySQL از ساختارهای IF ، CASE ، ITERATE ، LEAVE LOOP ، WHILE و REPEAT برای مدیریت موضوعات در یک روش ذخیره شده پشتیبانی می کند. ما به نحوه استفاده از IF ، CASE و WHILE می پردازیم ، زیرا آنها معمولاً مورد استفاده قرار می گیرند.

در صورت ساخت

با استفاده از ساختار IF ، می توانیم کارهایی را انجام دهیم که شامل شرایطی باشند:

DELIMITER // CREATE PROCEDURE `proc_IF` (IN param1 INT) متغیر BEGIN DECLARE1 INT؛ متغیر SET1 \u003d param1 + 1؛ متغیر IF \u003d 1 THEN SELECT متغیر 1؛ پایان اگر اگر param1 \u003d 0 THEN SELECT "مقدار پارامتر \u003d 0"؛ ELSE SELECT "مقدار پارامتر<> 0 "؛ پایان اگر ؛ پایان //

ساخت و ساز مورد

CASE روش دیگری برای بررسی شرایط و انتخاب راه حل مناسب است. این یک روش عالی برای جایگزینی بسیاری از سازه های IF است. یک سازه را می توان از دو طریق توصیف کرد و انعطاف پذیری را برای کنترل بسیاری از عبارات شرطی فراهم می آورد.

DELIMITER // CREATE PROCEDURE `proc_CASE (IN param1 INT) متغیر شروع مقررات 1 INT؛ متغیر SET1 \u003d param1 + 1؛ متغیر مورد 1 WHEN 0 THEN INSERT INTO Table1 Values \u200b\u200b(param1)؛ WHEN 1 THEN INSERT Into Table1 Value (متغیر 1)؛ جدول های دیگر ELSE INSERT INTO (99)؛ مورد نهایی؛ پایان //

DELIMITER // CREATE PROCEDURE `proc_CASE (IN param1 INT) متغیر شروع مقررات 1 INT؛ متغیر SET1 \u003d param1 + 1؛ مورد WHEN متغیر 1 \u003d 0 بعد از آن وارد جدول 1 ارزش ها (param1)؛ WHEN متغیر 1 \u003d 1 THEN INSERT INTO جدول 1 ارزشها (متغیر 1)؛ جدول های دیگر ELSE INSERT INTO (99)؛ مورد نهایی؛ پایان //

ساخت WHILE

از نظر فنی سه نوع حلقه وجود دارد: حلقه WHILE ، حلقه LOOP و حلقه REPEAT. همچنین می توانید حلقه را با استفاده از تکنیک برنامه نویسی Darth Vader سازماندهی کنید: عبارت های GOTO. در اینجا یک حلقه مثال آورده شده است:

DELIMITER // CREATE PROCEDURE `proc_WHILE (IN param1 INT) BEGIN DECLARE متغیر 1 ، متغیر2 INT؛ متغیر SET1 \u003d 0؛ متغیر WHILE1< param1 DO INSERT INTO table1 VALUES (param1); SELECT COUNT(*) INTO variable2 FROM table1; SET variable1 = variable1 + 1; END WHILE; END //

مرحله ششم: مکان نما

مکان نما برای عبور از مجموعه ردیف های برگشت داده شده توسط پرس و جو و همچنین پردازش هر سطر استفاده می شود.

MySQL در مراحل ذخیره شده از مکان نما پشتیبانی می کند. در اینجا مختصر برای ایجاد و استفاده از مکان نما آورده شده است.

اعلام مکان نما با نام CURSOR FOR SELECT ...؛ / * اعلام مکان نما و پر کردن آن * / دکمه برای کنترل نشدن هندل ادامه دهید / * در صورت عدم ورود ورودی بیشتر ، * چه کاری انجام دهید. / * نشانگر باز * / FETCH متغیر مکان نما نام INTO [، متغیر]؛ / * مقدار را به متغیری برابر با مقدار ستون فعلی اختصاص دهید * / CLOSE نام مکان نما؛ / * بستن مکان نما * /

در این مثال ، ما با استفاده از مکان نما چندین عملیات ساده انجام خواهیم داد:

DELIMITER // CREATE PROCEDURE `proc_CURSOR (OUT param1 INT) شروع اعلامیه a ، b ، c INT؛ اعلام cur1 نتیجه برای انتخاب col1 از جدول 1؛ اعلام دستی را برای یافتن مجموعه b \u003d 1 اعلام نکنید. باز کردن cur1؛ تنظیم b \u003d 0؛ تنظیم c \u003d 0؛ WHILE b \u003d 0 آیا cur1 را وارد کنید؛ اگر b \u003d 0 THEN SET c \u003d c + a؛ پایان اگر پایان WHILE؛ CLOSE cur1؛ تنظیم param1 \u003d c؛ پایان //

مکان نما سه ویژگی دارد که شما باید آنها را بفهمید تا از نتایج غیر منتظره جلوگیری کنید:

• حساس نیست: با باز کردن مکان نما ، تغییر مکان در جدول که بعدا رخ داده است نشان نمی دهد. در واقع ، MySQL تضمین نمی کند که مکان نما به روز شود ، بنابراین به آن اعتماد نکنید.
• فقط خواندنی: مکان نما ها را نمی توان تغییر داد.
• بدون عقب برگرداندن: مکان نما فقط می تواند در یک جهت حرکت کند - به جلو ، شما نمی توانید خطوط را بدون انتخاب آنها جست و خیز کنید.

نتیجه

در این آموزش ، شما را با اصول کار با مراحل ذخیره شده و برخی از خصوصیات خاص مرتبط با آن آشنا کردم. البته قبل از تبدیل شدن به یک معلم واقعی از روالهای MySQL ، باید دانش خود را در زمینه هایی مانند امنیت ، بیان SQL و بهینه سازی عمیق تر کنید.

شما باید محاسبه کنید که استفاده از روشهای ذخیره شده در برنامه خاص شما چه مزایایی خواهد داشت و فقط در این صورت فقط مراحل لازم را ایجاد می کنید. به طور کلی ، من از رویه ها استفاده می کنم؛ به نظر من ، آنها باید به دلیل امنیت ، حفظ کد و طراحی کلی ، در پروژه ها اجرا شوند. همچنین ، فراموش نکنید که MySQL هنوز روی مراحل کار می کند. انتظار می رود پیشرفت های مربوط به عملکرد و پیشرفت ها را داشته باشید. لطفاً با نظرات خود به اشتراک بگذارید