ایجاد روش (tranact-sql)

یک روش ذخیره شده Transact-SQL یا Common Language (CLR) را در SQL Server ، پایگاه داده Azure SQL و سیستم پلت فرم Analytics (PDW) ایجاد می کند. رویه های ذخیره شده مشابه رویه ها در سایر زبان های برنامه نویسی است که می توانند:

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

از این عبارت برای ایجاد یک روش دائمی در پایگاه داده فعلی یا یک روش موقت در پایگاه داده TEMPDB استفاده کنید.

ادغام چارچوب . NET CLR در SQL Server در این موضوع مورد بحث قرار گرفته است. ادغام CLR در پایگاه داده Azure SQL اعمال نمی شود.

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

نحو

نحو Trancact-SQL برای روشهای ذخیره شده در SQL Server و Azure SQL Database:

نحو tranact-sql برای روشهای ذخیره شده CLR:

نحو tranact-sql برای روشهای ذخیره شده بومی:

نحو tranact-sql برای روشهای ذخیره شده در تجزیه و تحلیل سیناپس لاجورد و انبار داده های موازی:

برای مشاهده نحو Transact-SQL برای SQL Server 2014 و قبل از آن ، به اسناد نسخه های قبلی مراجعه کنید.

استدلال

یا تغییر دهید

اعمال می شود: Azure SQL Database ، SQL Server (با شروع SQL Server 2016 (13. x) SP1).

اگر قبلاً وجود داشته باشد ، روش را تغییر می دهد.

schema_name

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

رویه_ نام

نام روشنامهای رویه باید با قوانین مربوط به شناسه ها مطابقت داشته باشند و باید در این طرح منحصر به فرد باشند.

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

روشهای موقت محلی یا جهانی را می توان با استفاده از یک علامت شماره ( #) قبل از رویه_ نام (#Procedure_Name) برای مراحل موقت محلی و دو علامت شماره برای روشهای موقت جهانی (## روش_ نام) ایجاد کرد. یک روش موقت محلی فقط برای اتصال ایجاد شده قابل مشاهده است و هنگام بسته شدن این اتصال از بین می رود. یک روش موقت جهانی برای همه اتصالات در دسترس است و در پایان جلسه آخر با استفاده از روش کاهش می یابد. نام های موقت را نمی توان برای مراحل CLR مشخص کرد.

نام کامل برای یک روش یا یک روش موقت جهانی ، از جمله ## ، نمی تواند از 128 کاراکتر تجاوز کند. نام کامل یک روش موقت محلی ، از جمله # ، نمی تواند از 116 کاراکتر تجاوز کند.

؛عدد

اعمال می شود: SQL Server 2008 (10. 0. x) و بعد از آن ، و Azure SQL Database.

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

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

روشهای شماره گذاری شده نمی توانند از انواع تعریف شده توسط کاربر XML یا CLR استفاده کنند و در یک راهنمای برنامه قابل استفاده نیست.

@ parameter_name

پارامتر اعلام شده در این روش. نام پارامتر را با استفاده از علامت AT ( @) به عنوان اولین کاراکتر مشخص کنید. نام پارامتر باید با قوانین مربوط به شناسه ها مطابقت داشته باشد. پارامترها به روش محلی هستند. از همان نام پارامترها می توان در سایر روشها استفاده کرد.

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

در صورت مشخص شدن برای تکرار ، پارامترها نمی توانند اعلام شوند.

[type_schema_name.] نوع داده

نوع داده پارامتر و طرحواره ای که نوع داده به آن تعلق دارد.

رهنمودهای مربوط به روشهای Trancact-SQL:

• تمام انواع داده های Transact-SQL می تواند به عنوان پارامترها استفاده شود.
• می توانید از نوع جدول تعریف شده توسط کاربر برای ایجاد پارامترهای با ارزش جدول استفاده کنید. پارامترهای با ارزش جدول فقط می توانند پارامترهای INPUT باشند و باید با کلمه کلیدی READONLY همراه شوند. برای اطلاعات بیشتر، به استفاده از پارامترهای با ارزش جدول (موتور پایگاه داده) مراجعه کنید.
• انواع داده های مکان نما فقط می توانند پارامترهای OUTPUT باشند و باید با کلمه کلیدی VARYING همراه شوند.

دستورالعمل های رویه های CLR:

همه انواع داده های SQL Server بومی که معادل کد مدیریت شده دارند می توانند به عنوان پارامتر استفاده شوند. برای اطلاعات بیشتر در مورد مطابقت بین انواع CLR و انواع داده های سیستم SQL Server، به نقشه برداری داده های پارامتر CLR مراجعه کنید. برای اطلاعات بیشتر در مورد انواع داده های سیستم SQL Server و نحو آنها، به انواع داده ها (Transact-SQL) مراجعه کنید.

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

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

متفاوت است

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

پیش فرض

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

مقادیر پیش فرض فقط برای رویه های CLR در ستون sys. parameters. default ثبت می شوند. آن ستون برای پارامترهای رویه Transact-SQL NULL است.

خارج |خروجی

نشان می دهد که پارامتر یک پارامتر خروجی است. از پارامترهای OUTPUT برای برگرداندن مقادیر به تماس گیرنده رویه استفاده کنید. پارامترهای text، ntext و image را نمی توان به عنوان پارامترهای OUTPUT استفاده کرد، مگر اینکه رویه یک رویه CLR باشد. یک پارامتر خروجی می تواند مکان نمای مکان نما باشد، مگر اینکه رویه یک رویه CLR باشد. یک نوع داده با مقدار جدول را نمی توان به عنوان پارامتر OUTPUT یک رویه مشخص کرد.

فقط خواندنی

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

دوباره کامپایل کنید

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

برای راهنمایی به موتور پایگاه داده برای از بین بردن برنامه های پرس و جو برای نمایش داده های فردی در یک روش ، از اشاره پرس و جو در تعریف پرس و جو استفاده کنید. برای اطلاعات بیشتر ، به Query نکات (Transact-SQL) مراجعه کنید.

رمز

اعمال می شود: SQL Server (SQL Server 2008 (10. 0. x) و بعد از آن) ، پایگاه داده Azure SQL.

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

این گزینه برای مراحل CLR معتبر نیست.

رویه های ایجاد شده با این گزینه نمی تواند به عنوان بخشی از SQL Server Replication منتشر شود.

به عنوان بند اجرا کنید

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

برای روشهای ذخیره شده بومی ، شروع SQL Server 2016 (13. x) و در پایگاه داده Azure SQL ، هیچ محدودیتی در بند به عنوان بند وجود ندارد. در SQL Server 2014 (12. x) بندهای خود ، مالک و "user_name" با روشهای ذخیره شده بومی گردآوری شده پشتیبانی می شوند.

برای تکثیر

اعمال می شود: SQL Server (SQL Server 2008 (10. 0. x) و بعد از آن) ، پایگاه داده Azure SQL.

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

A برای روش تکثیر دارای نوع RF شیء در Sys. Objects و sys. procedures است.

< [ BEGIN ] sql_statement [;] [ . n ] [ END ] >

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

نام خارجی ASSEMBLY_NAME. نام کلاس . method_name

اعمال می شود: SQL Server 2008 (10. 0. x) و بعداً ، پایگاه داده SQL.

روش مونتاژ چارچوب . NET را برای یک روش CLR برای مرجع مشخص می کند. class_name باید یک شناسه معتبر SQL Server باشد و باید به عنوان یک کلاس در مونتاژ وجود داشته باشد. اگر کلاس دارای یک نام واجد شرایط فضای نام است که از یک دوره (.) برای جدا کردن قطعات فضای نام استفاده می کند ، باید با استفاده از براکت ها ([]) یا علائم نقل قول ("") نام کلاس مشخص شود. روش مشخص شده باید یک روش استاتیک کلاس باشد.

به طور پیش فرض ، SQL Server نمی تواند کد CLR را اجرا کند. شما می توانید اشیاء پایگاه داده را ایجاد ، اصلاح و رها کنید که به ماژول های زمان اجرا زبان مشترک مراجعه می کنند. با این حال ، شما نمی توانید این منابع را در SQL Server اجرا کنید تا زمانی که گزینه فعال شده CLR را فعال نکنید. برای فعال کردن گزینه ، از sp_configure استفاده کنید.

روشهای CLR در یک پایگاه داده موجود پشتیبانی نمی شوند.

اتمی با

اعمال می شود: SQL Server 2014 (12. x) و بعد از آن ، و Azure SQL Database.

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

اگر این روش بازگردد (صریحاً از طریق بیانیه بازگشت یا به طور ضمنی با انجام اجرای) ، کار انجام شده توسط این روش انجام می شود. اگر این روش پرتاب شود ، کار انجام شده توسط این روش به عقب برگردانده می شود.

XACT_ABORT به طور پیش فرض در داخل یک بلوک اتمی روشن است و قابل تغییر نیست. XACT_ABORT مشخص می کند که آیا SQL Server به طور خودکار معامله فعلی را پس می گیرد وقتی یک عبارت Transact-SQL خطای زمان اجرا را افزایش می دهد.

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

• concat_null_yields_null
• به نقل از_آیدانه ، Arithabort
• عود
• ansi_nulls
• ansi_waings

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

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

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

تهی |تهی نیست

تعیین می کند که آیا مقادیر تهی در یک پارامتر مجاز هستند یا خیر. تهی پیش فرض است.

compilation native

اعمال می شود: SQL Server 2014 (12. x) و بعد از آن ، و Azure SQL Database.

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

طرح ریزی

اعمال می شود: SQL Server 2014 (12. x) و بعد از آن ، و Azure SQL Database.

تضمین می کند که جداول ارجاع شده توسط یک روش قابل کاهش یا تغییر نیست. Schemabinding در روشهای ذخیره شده بومی مورد نیاز است.(برای اطلاعات بیشتر ، به روشهای ذخیره شده بومی گردآوری شده مراجعه کنید.) محدودیت های طرح بندی همانند عملکردهای تعریف شده توسط کاربر است. برای اطلاعات بیشتر ، به بخش Schemabinding در ایجاد عملکرد (tranact-sql) مراجعه کنید.

زبان = [n] "زبان"

اعمال می شود: SQL Server 2014 (12. x) و بعد از آن ، و Azure SQL Database.

معادل گزینه Set Language (Transact-SQL). زبان = [n] "زبان" لازم است.

سطح جداسازی معامله

اعمال می شود: SQL Server 2014 (12. x) و بعد از آن ، و Azure SQL Database.

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

قابل تکرار خواندن

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

سریال پذیر

موارد زیر را مشخص می کند:

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

عکس فوری

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

datefirst = شماره

اعمال می شود: SQL Server 2014 (12. x) و بعد از آن ، و Azure SQL Database.

روز اول هفته را به شماره 1 تا 7 مشخص می کند. Datefirst اختیاری است. اگر مشخص نشده باشد ، تنظیم از زبان مشخص استنباط می شود.

dateformat = قالب

اعمال می شود: SQL Server 2014 (12. x) و بعد از آن ، و Azure SQL Database.

ترتیب قطعات تاریخ ، روز و سال را برای تفسیر تاریخ ، SmalldateTime ، DateTime ، DateTime2 و رشته های شخصیت DateTimeOffset مشخص می کند. DateFormat اختیاری است. اگر مشخص نشده باشد ، تنظیم از زبان مشخص استنباط می شود.

تأخیر_آموزیت =

اعمال می شود: SQL Server 2014 (12. x) و بعد از آن ، و Azure SQL Database.

تعهدات معامله سرور SQL می تواند کاملاً بادوام باشد ، پیش فرض یا با تاخیر با دوام باشد.

نمونه های ساده

برای کمک به شما در شروع کار ، در اینجا دو مثال سریع وجود دارد: db_name () را به عنوان thisdb انتخاب کنید. نام پایگاه داده فعلی را برمی گرداند. می توانید آن بیانیه را در یک روش ذخیره شده مانند:

با روش فروشگاه تماس بگیرید: EXEC WHOT_DB_IS_THIS ؛

کمی پیچیده تر ، تهیه یک پارامتر ورودی برای انعطاف پذیری تر این روش است. مثلا:

هنگام تماس با روش ، شماره شناسه پایگاه داده را ارائه دهید. به عنوان مثال ، EXEC WHAT_DB_IS_THAT 2 ؛tempdb را برمی گرداند.

مثالهایی را در پایان این مقاله برای مثال های دیگر مشاهده کنید.

بهترین روشها

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

• به عنوان اولین بیانیه در بدنه این روش از nocount set set استفاده کنید. یعنی آن را درست بعد از کلمه کلیدی قرار دهید. این پیام ها را خاموش می کند که SQL Server پس از هرگونه انتخاب ، درج ، به روزرسانی ، ادغام و حذف اظهارات به مشتری ارسال می شود. این باعث می شود خروجی تولید شده برای وضوح حداقل باشد. هیچ فایده عملکرد قابل اندازه گیری در سخت افزار امروز وجود ندارد. برای اطلاعات ، به مجموعه Nocount (Transact-SQL) مراجعه کنید.
• هنگام ایجاد یا مراجعه به اشیاء پایگاه داده در این روش از نام های طرحواره استفاده کنید. در صورتی که نیازی به جستجوی چندین طرح باشد ، موتور پایگاه داده کمتر می تواند نام اشیاء را برطرف کند. همچنین از ایجاد مجوز و دسترسی به مشکلات ناشی از طرح پیش فرض کاربر در هنگام ایجاد اشیاء بدون مشخص کردن طرح جلوگیری می کند.
• از بسته بندی توابع اطراف ستون های مشخص شده در محل و پیوستن به بندها خودداری کنید. انجام این کار باعث می شود ستون ها غیر تعیین کننده باشند و از پردازنده پرس و جو از استفاده از شاخص ها جلوگیری می کنند.
• از استفاده از توابع مقیاس در بیانیه های منتخب که بسیاری از ردیف های داده را باز می گردانند ، خودداری کنید. از آنجا که عملکرد مقیاس باید برای هر سطر اعمال شود ، رفتار حاصل مانند پردازش مبتنی بر ردیف و عملکرد تخریب می شود.
• از استفاده از انتخاب * خودداری کنید. در عوض ، نام ستون مورد نیاز را مشخص کنید. این می تواند از برخی خطاهای موتور پایگاه داده جلوگیری کند که اجرای روش را متوقف می کند. به عنوان مثال ، یک عبارت * انتخابی که داده ها را از یک جدول 12 ستون برمی گرداند و سپس آن داده ها را در یک جدول موقت 12 ستون درج می کند تا تعداد یا ترتیب ستون ها در هر جدول تغییر کند.
• از پردازش یا بازگشت داده های زیاد خودداری کنید. نتایج را در اسرع وقت در کد رویه باریک کنید تا هرگونه عملیات بعدی انجام شده توسط این روش با استفاده از کوچکترین مجموعه داده ممکن انجام شود. فقط داده های ضروری را به برنامه مشتری ارسال کنید. این کارآمدتر از ارسال داده های اضافی در شبکه و مجبور کردن برنامه مشتری برای کار از طریق مجموعه های نتیجه غیر ضروری است.
• از معاملات صریح با استفاده از معاملات شروع/تعهد استفاده کنید و معاملات را تا حد امکان کوتاه نگه دارید. معاملات طولانی تر به معنای قفل رکورد طولانی تر و پتانسیل بیشتر برای بن بست است.
• از Trancact-SQL Try استفاده کنید. ویژگی گرفتن برای رسیدگی به خطا در یک روش. تلاش كردن. Catch می تواند یک بلوک کامل از عبارات Transact-SQL را محاصره کند. این نه تنها عملکرد کمتری را ایجاد می کند ، بلکه باعث می شود گزارش خطا با برنامه نویسی به میزان قابل توجهی کمتر باشد.
• از کلمه کلیدی پیش فرض در تمام ستون های جدول که توسط ایجاد جدول یا تغییر جدول Transact-SQL در بدنه این روش استفاده می شود ، استفاده کنید. این مانع از انتقال تهی به ستون هایی می شود که مقادیر تهی را مجاز نمی دانند.
• برای هر ستون در یک جدول موقت از تهی یا تهی استفاده نکنید. گزینه های ANSI_DFLT_ON و ANSI_DFLT_OFF نحوه کنترل موتور پایگاه داده ویژگی های NULL یا NOLL را به ستون ها اختصاص نمی دهد ، هنگامی که این ویژگی ها در یک جدول ایجاد یا تغییر جدول مشخص نشده اند. اگر یک اتصال روشی را با تنظیمات مختلف برای این گزینه ها نسبت به اتصال ایجاد شده برای این روش انجام دهد ، ستون های جدول ایجاد شده برای اتصال دوم می توانند دارای باطل متفاوت باشند و رفتارهای متفاوتی را نشان دهند. اگر تهی یا نه تهی برای هر ستون به صراحت بیان شده باشد ، جداول موقت با استفاده از همان تهی برای همه اتصالی که روش را اجرا می کنند ، ایجاد می شوند.
• از عبارات اصلاح استفاده کنید که تهی ها را تبدیل می کند و شامل منطق است که ردیف هایی را با مقادیر تهی از نمایش داده ها از بین می برد. توجه داشته باشید که در Transact-SQL ، NULL یک ارزش خالی یا "هیچ" نیست. این یک مکان نگهدارنده برای یک مقدار ناشناخته است و می تواند باعث رفتار غیر منتظره شود ، به خصوص هنگام پرس و جو برای مجموعه های نتیجه یا استفاده از توابع کل.
• از اتحادیه به جای اتحادیه یا اپراتورها از اتحادیه استفاده کنید ، مگر اینکه نیاز خاصی به ارزشهای مجزا وجود داشته باشد. اتحادیه همه اپراتور نیاز به پردازش کمتری دارد زیرا کپی ها از مجموعه نتیجه فیلتر نمی شوند.

ملاحظات

حداکثر اندازه از پیش تعریف شده یک روش وجود ندارد.

متغیرهای مشخص شده در این روش می توانند متغیرهای تعریف شده توسط کاربر یا سیستم باشند ، مانند SPID.

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

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

رویه ها هنگامی که یک روش با دیگری تماس می گیرد یا کد مدیریت شده را با مراجعه به روال CLR ، نوع یا کل اجرا می کند ، توخالی می شوند. رویه ها و منابع کد مدیریت شده می توانند تا 32 سطح لانه شوند. هنگامی که روش فراخوانده یا مرجع کد مدیریت شده شروع به کار می کند ، سطح لانه سازی توسط یک افزایش می یابد و هنگامی که رویه نامیده شده یا مرجع کد مدیریت شده اجرا را انجام می دهد ، توسط یک کاهش می یابد. روشهای فراخوانی شده از درون کد مدیریت شده در برابر حد سطح لانه سازی حساب نمی شوند. با این حال ، هنگامی که یک روش ذخیره شده CLR عملیات دسترسی به داده ها را از طریق ارائه دهنده مدیریت SQL Server انجام می دهد ، در انتقال از کد مدیریت شده به SQL ، سطح لانه سازی اضافی اضافه می شود.

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

قابلیت همکاری

موتور پایگاه داده تنظیمات هر دو تنظیم شده را به صورت set_identifier ذخیره می کند و هنگامی که یک روش transact-sql ایجاد یا اصلاح می شود ، ANSI_NULLS را تنظیم می کند. این تنظیمات اصلی هنگام اجرای این روش استفاده می شود. بنابراین ، هر تنظیمات جلسه مشتری برای تنظیم شده_آیدانه تنظیم شده و تنظیم ANSI_NULL ها هنگام اجرای این روش نادیده گرفته می شود.

سایر گزینه های مجموعه ، مانند Set Arithabort ، Set ANSI_WARNINGS یا تنظیم ANSI_PADDINGS هنگام ایجاد یا اصلاح یک روش ذخیره نمی شوند. اگر منطق این روش به یک تنظیم خاص بستگی دارد ، در شروع روش برای تضمین تنظیم مناسب ، یک عبارت مشخص را درج کنید. هنگامی که یک بیانیه تعیین شده از یک روش اجرا می شود ، تنظیم فقط تا زمانی که رویه به پایان نرسد ، عملی می شود. سپس تنظیمات به مقدار رویه ای که هنگام فراخواندن آن انجام شده است بازگردانده می شود. این امر باعث می شود مشتری های فردی بدون تأثیر منطق روش ، گزینه های مورد نظر خود را تنظیم کنند.

هر عبارت SET را می توان در یک روش مشخص کرد ، به جز تنظیم showplan_text و setplan_all. اینها باید تنها اظهارات دسته ای باشند. گزینه SET انتخاب شده در حین اجرای این روش به کار می رود و سپس به تنظیم قبلی خود باز می گردد.

تنظیم ANSI_WARNINGS هنگام عبور از پارامترها در یک روش ، عملکرد تعریف شده توسط کاربر یا هنگام اعلام و تنظیم متغیرها در یک بیانیه دسته ای ، مورد تقدیر قرار نمی گیرد. به عنوان مثال ، اگر یک متغیر به عنوان char (3) تعریف شود ، و سپس روی یک مقدار بزرگتر از سه کاراکتر تنظیم شود ، داده ها به اندازه تعریف شده کوتاه می شوند و عبارت درج یا بروزرسانی موفق می شود.

محدودیت ها و محدودیت ها

بیانیه روش ایجاد نمی تواند با سایر اظهارات Transact-SQL در یک دسته واحد ترکیب شود.

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

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

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

اگر رویه در یک نمونه راه دور از SQL Server تغییراتی ایجاد کند، تغییرات را نمی توان برگرداند. رویه های از راه دور در معاملات شرکت نمی کنند.

برای اینکه موتور پایگاه داده هنگام بارگذاری بیش از حد در دات نت به روش صحیح ارجاع دهد، روش مشخص شده در عبارت EXTERNAL NAME باید دارای ویژگی های زیر باشد:

• به عنوان یک روش استاتیک اعلام شود.
• همان تعداد پارامتر را با تعداد پارامترهای رویه دریافت کنید.
• از انواع پارامترهایی استفاده کنید که با انواع داده های پارامترهای مربوطه رویه SQL Server سازگار هستند. برای اطلاعات در مورد تطبیق انواع داده های SQL Server با انواع داده های . NET Framework، به Mapping CLR Parameter Data مراجعه کنید.

فراداده

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

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

1 این شمارنده ها برای دسته های مختلفی از اشیاء حافظه پنهان از جمله Ad hoc Transact-SQL، Transact-SQL آماده شده، رویه ها، تریگرها و غیره در دسترس هستند. برای اطلاعات بیشتر به SQL Server، Plan Cache Object مراجعه کنید.

مجوزها

نیاز به مجوز CREATE PROCEDURE در پایگاه داده و مجوز ALTER در طرحی که رویه در آن ایجاد می شود، یا نیاز به عضویت در نقش پایگاه داده ثابت db_ddladmin دارد.

برای رویه های ذخیره شده CLR، نیاز به مالکیت اسمبلی ارجاع شده در عبارت EXTERNAL NAME یا مجوز REFERENCES در آن مجموعه است.

جدول های روال و حافظه بهینه شده را ایجاد کنید

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

نمونه زیر نحوه ایجاد یک روش ذخیره شده بومی را نشان می دهد که به یک جدول DBO. Departments بهینه شده حافظه دسترسی پیدا می کند:

روشی ایجاد شده بدون Native_compilation نمی تواند به یک روش ذخیره شده بومی گردآوری شود.

برای بحث در مورد برنامه نویسی در روشهای ذخیره شده بومی ، سطح پرس و جو پشتیبانی شده ، و اپراتورها ویژگی های پشتیبانی شده برای ماژول های T-SQL بومی گردآوری شده را مشاهده می کنند.

مثال ها

• = پیش فرض
• خروجی
• نوع پارامتر با ارزش جدول
• مکان نما متفاوت

نحو اساسی

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

الف-یک روش Transact-SQL ایجاد کنید

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

روش UspgetEmployes به روش های زیر قابل اجرا است:

ب) بیش از یک نتیجه را برگردانید

روش زیر دو مجموعه نتیجه را برمی گرداند.

ج - یک روش ذخیره شده CLR ایجاد کنید

مثال زیر روش GetPhotofromDB را ایجاد می کند که به روش GetPhotofromdb کلاس بزرگ کلاس در مونتاژ HandlingLobusingClr اشاره می کند. قبل از ایجاد این روش ، مونتاژ HandlingLobusingClr در پایگاه داده محلی ثبت می شود.

اعمال می شود: SQL Server 2008 (10. 0. x) و بعداً ، پایگاه داده SQL (در صورت استفاده از مونتاژ ایجاد شده از Assembly_Bits.

پارامترها را عبور دهید

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

D. روشی با پارامترهای ورودی ایجاد کنید

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

روش UspgetEmployes به روش های زیر قابل اجرا است:

E. از روشی با پارامترهای کارت ویزیت استفاده کنید

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

روش UspgetEmployees2 در بسیاری از ترکیبات قابل اجرا است. فقط چند ترکیب ممکن در اینجا نشان داده شده است.

F. از پارامترهای خروجی استفاده کنید

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

UspgetList را برای بازگشت لیستی از محصولات Adventure Works (دوچرخه) که کمتر از 700 دلار هزینه دارند ، اجرا کنید. پارامترهای خروجی cost و compareprices با زبان کنترل جریان برای بازگشت پیام در پنجره پیام ها استفاده می شوند.

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

در اینجا مجموعه نتیجه جزئی است:

G. از یک پارامتر با ارزش جدول استفاده کنید

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

ح. از یک پارامتر مکان نما خروجی استفاده کنید

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

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

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

با استفاده از یک روش ذخیره شده ، داده ها را اصلاح کنید

مثالهای موجود در این بخش نحوه درج یا اصلاح داده ها در جداول یا نماها را با درج بیانیه زبان دستکاری داده (DML) در تعریف روش نشان می دهد.

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

مثال زیر از یک عبارت به روزرسانی در یک روش ذخیره شده استفاده می کند. این روش یک پارامتر ورودی ، newhours و یک پارامتر خروجی RowCount را می گیرد. مقدار پارامتر NewHours در بیانیه به روزرسانی برای به روزرسانی ستون های تعطیلات در جدول HumanResource. employee استفاده می شود. پارامتر خروجی RowCount برای بازگشت تعداد ردیف های تحت تأثیر به یک متغیر محلی استفاده می شود. از یک عبارت موردی در بند تنظیم شده استفاده می شود تا به طور مشروط مقدار تعیین شده برای تعطیلات را تعیین کند. هنگامی که کارمند ساعتی پرداخت می شود (SalariedFlag = 0) ، تعطیلات به تعداد فعلی ساعت ها به علاوه مقدار مشخص شده در newhours تنظیم می شود. در غیر این صورت ، تعطیلات روی مقدار مشخص شده در newhours تنظیم شده است.

رسیدگی به خطا

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

J. استفاده از سعی کنید. گرفتن

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

تعریف روش

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

K. از گزینه با رمزگذاری استفاده کنید

مثال زیر روش انسانی را ایجاد می کند.

اعمال می شود: SQL Server 2008 (10. 0. x) و بعد از آن ، و Azure SQL Database.

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

این مجموعه ی نتایج است.

متن برای شیء "HumanResource. Uspencryptthis" رمزگذاری شده است.

به طور مستقیم نمایش کاتالوگ SYS. SQL_MODULES:

این مجموعه ی نتایج است.

روش ذخیره شده سیستم SP_HELPTEXT در تجزیه و تحلیل سیناپس لاجورد پشتیبانی نمی شود. در عوض ، از نمای کاتالوگ شیء SYS. SQL_MODULES استفاده کنید.

روش را مجبور به بازپس گیری کنید

مثالهای موجود در این بخش از بند با Repompile استفاده می کنند تا هر بار که اجرا شود ، رویه را مجبور به جبران مجدد کنید.

L. از گزینه با Recompile استفاده کنید

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

زمینه امنیتی را تنظیم کنید

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

M. از EXECUTE به عنوان بند استفاده کنید

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

N. مجموعه های مجوز سفارشی ایجاد کنید

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

مثال: سیستم پلت فرم تجزیه و تحلیل Azure Synapse Analytics و Analytics (PDW)

O. یک روش ذخیره شده ایجاد کنید که یک عبارت انتخابی را اجرا کند

این مثال نحو اساسی برای ایجاد و اجرای یک روش را نشان می دهد. هنگام اجرای یک دسته ، روش ایجاد باید اولین جمله باشد. به عنوان مثال ، برای ایجاد روش ذخیره شده زیر در AdventureWorkSpdw2022 ، ابتدا زمینه پایگاه داده را تنظیم کرده و سپس بیانیه Create Procedure را اجرا کنید.