ما وضعیتی را در نظر می گیریم که رویه های ذخیره شده می توانند عملکرد پرس و جو را کاهش دهند.

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

مرحله 1. ایجاد پایگاه داده
برای آزمایش، بیایید یک پایگاه داده جداگانه ایجاد کنیم.

ایجاد پایگاه داده test_sp_perf
روشن (NAME="test_data", FILENAME="c:\temp\test_data", SIZE=1, MAXSIZE=10,FILEGROWTH=1Mb)
LOG ON (NAME="test_log", FILENAME="c:\temp\test_log", SIZE=1, MAXSIZE=10,FILEGROWTH=1Mb)

گام 2.ایجاد جدول.
CREATE TABLE sp_perf_test (column1 int, column2 char (5000))

مرحله 3.پر کردن جدول با رشته های آزمایشی سطرهای تکراری عمداً به جدول اضافه می شوند. 10000 خط با اعداد از 1 تا 10000 و 10000 خط با اعداد 50000.

اعلام @i int
تنظیم @i=1
WHILE(@i<10000)
شروع
INSERT INTO sp_perf_test(column1, column2) VALUES(@i,"Test string #"+CAST(@i as char(8)))
INSERT INTO sp_perf_test(column1, column2) VALUES(50000,"Test string #"+CAST(@i as char(8)))
تنظیم @i= @i+1
پایان

COUNT(*) را از sp_perf_test انتخاب کنید
برو

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

CREATE NONCLUSTERED INDEX CL_perf_test ON sp_perf_test(column1)
برو

مرحله 5.یک رویه ذخیره شده ایجاد کنید. روال به سادگی دستور SELECT را با شرط اجرا می کند.

ایجاد PROC proc1 (@paramint)
مانند
ستون 1، ستون 2 را از sp_perf_test WHERE انتخاب کنید [ایمیل محافظت شده]
برو

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

EXEC proc1 1234
برو

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

EXEC proc1 50000
برو

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

ستون 1، ستون 2 از sp_perf_test WHERE ستون1=50000 را انتخاب کنید
برو

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

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

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

انواع مختلفی در SQL Server وجود دارد رویه های ذخیره شده.

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

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

ایجاد رویه ذخیره شدهشامل حل وظایف زیر است:

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

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

<определение_процедуры>::= (ایجاد | تغییر ) procedure_name [;شماره] [(@parameter_name نوع داده ) [=پیش فرض] ][,...n] AS sql_statement [...n]

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

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

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

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

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

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

کلمه کلیدی VARYING همراه با استفاده می شود

رویه ذخیره شدهذخیره شده) یک شیء برنامه پایگاه داده نامگذاری شده است. SQL Server انواع مختلفی از رویه های ذخیره شده دارد.

رویه های ذخیره شده در سیستمرویه ذخیره شده سیستم) توسط توسعه دهندگان DBMS عرضه می شوند و برای انجام اقدامات در کاتالوگ سیستم یا دریافت اطلاعات سیستم استفاده می شوند. نام آنها معمولا با پیشوند "sp_" شروع می شود. رویه های ذخیره شده از همه نوع با دستور EXECUTE اجرا می شوند که می تواند به EXEC کوتاه شود. به عنوان مثال، رویه ذخیره شده sp_hellogins که بدون پارامتر اجرا می شود، دو گزارش از نام حساب تولید می کند. (انگلیسی)ورود به سیستم) و کاربران مربوطه آنها در هر پایگاه داده (انگلیسی)کاربران).

EXEC sp_hellogins;

برای ارائه ایده ای از اقدامات انجام شده با استفاده از رویه های ذخیره شده در سیستم، در جدول. 10.6 چند نمونه را نشان می دهد. در کل، بیش از هزار رویه ذخیره شده در سیستم در SQL Server وجود دارد.

جدول 10.6

نمونه هایی از رویه های ذخیره شده سیستم SQL Server

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

اشیاء SQL Server قابل برنامه ریزی را می توان با استفاده از ابزار Transact-SQL یا با استفاده از مجموعه ها ایجاد کرد. (انگلیسی)اسمبلی) در محیط CRL (Common Language Runtime) Microsoft .Net Framework. در این آموزش تنها روش اول در نظر گرفته خواهد شد.

برای ایجاد رویه های ذخیره شده، از دستور CREATE PROCEDURE (که می توان به اختصار PROC نامیده می شود) استفاده کرد که فرمت آن در زیر آمده است:

CREATE (PROC I PROCEDURE) proc_name [ ; عدد]

[(gparameter data_type )

["پیش فرض] |

[با [ ,...ن ] ]

[برای تکرار]

AS ([ BEGIN ] sql_statement [;] [ ...n ] [ END ] )

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

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

EXECUTE AS زمینه امنیتی را تعریف می کند که رویه باید در آن اجرا شود. سپس یکی از مقادیر f CALLER | خود | مالک | "نام کاربری"). CALLER مقدار پیش فرض است و به این معنی است که کد در زمینه امنیتی کاربر که این ماژول را فراخوانی می کند اجرا می شود. بر این اساس، کاربر باید نه تنها برای خود شی قابل برنامه ریزی، بلکه برای سایر اشیاء پایگاه داده تحت تأثیر آن نیز مجوز داشته باشد. EXECUTE AS SELF به معنای استفاده از زمینه کاربر است که یک شی قابل برنامه ریزی را ایجاد یا تغییر می دهد. OWNER مشخص می کند که کد در زمینه مالک فعلی رویه اجرا می شود. اگر مالکی برای آن تعریف نشده باشد، مالک طرحواره ای که به آن تعلق دارد در نظر گرفته می شود. EXECUTE AS "user_name" به شما امکان می دهد نام کاربری را به صراحت (در گیومه های تک) مشخص کنید.

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

پارامترها را می‌توان مقادیر پیش‌فرض نسبت داد، که اگر مقدار پارامتر هنگام فراخوانی روال به‌صراحت مشخص نشده باشد، از آن‌ها استفاده می‌شود. یک مثال را در نظر بگیرید:

CREATE PROC surma (@a int، @b int=0،

©نتیجه int OUTPUT) AS

تنظیم @result=0a+0b

ما یک رویه با سه پارامتر ایجاد کرده‌ایم و پارامتر @b مقدار پیش‌فرض =0 دارد و پارامتر @result یک پارامتر خروجی است: از طریق آن مقدار به برنامه فراخوانی بازگردانده می‌شود. اقدامات انجام شده بسیار ساده هستند - پارامتر خروجی مقدار مجموع دو ورودی را دریافت می کند.

هنگام کار در SQL Server Management Studio، رویه ذخیره شده ایجاد شده را می توان در بخش اشیاء پایگاه داده قابل برنامه ریزی یافت. (انگلیسی)قابلیت برنامه ریزی) در بخش رویه های ذخیره شده (شکل 10.2).

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

برنج. 10.2.

اعلام @with int;

جمع EXEC 10,5,@c OUTPUT;

PRINT0c; - 15 نمایش داده خواهد شد

اعلام Gi int = 5;

- هنگام تماس، از مقدار پیش فرض استفاده کنید

جمع EXEC Gi, DEFAULT , 0s OUTPUT;

PRINT0c; - 5 نمایش داده خواهد شد

اکنون یک مثال با تجزیه و تحلیل کد بازگشتی که رویه با آن به پایان می رسد در نظر بگیرید. اجازه دهید محاسبه شود که در یک بازه زمانی معین، چند کتاب در جدول Bookl منتشر شده است. در این حالت، اگر سال شروع از سال پایانی بیشتر باشد، رویه "1" را برمی‌گرداند و حساب نمی‌شود، در غیر این صورت، تعداد کتاب‌ها را می‌شماریم و 0 برمی‌گردانیم:

CREATE PROC dbo.rownum(0FirsYear int، GLastYear int، 0result int OUTPUT) به عنوان

IF 0FirsYear>0 LastYear RETURN 1

SET @result= (انتخاب COUNT(*) FROM dbo.Bookl

کجا بین 0سال اول و 0سال گذشته)؛

گونه ای از فراخوانی این روش را در نظر بگیرید، که در آن کد بازگشتی در متغیر عدد صحیح 0ret ذخیره می شود، پس از آن مقدار آن تجزیه و تحلیل می شود (در این مورد 1 خواهد بود). تابع CAST مورد استفاده در دستور PRINT برای تبدیل مقدار یک متغیر عدد صحیح Gres به یک نوع رشته استفاده می شود:

DECLARE 0ret int, Gres int

EXEC Gret = rownum 2004، 2002، Gres OUT;

IF 0ret=l PRINT "سال شروع بزرگتر از سال پایانی است"

PRINT "تعداد کتاب" + CAST(Gres as varchar(20))

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

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

مثال زیر هم این احتمالات و هم مسائل مربوط به محدوده اشیاء موقت را نشان می دهد. رویه ذخیره شده زیر وجود یک جدول موقت #Tab2 را بررسی می کند. اگر این جدول وجود نداشته باشد، آن را ایجاد می کند. پس از آن، مقادیر دو ستون در جدول #Tab2 وارد می شود و محتوای جدول با دستور SELECT نمایش داده می شود:

ایجاد PROC My_Procl (@id int، @name varchar(30))

اگر OBJECT_ID ("tempdb.dbo.#Tab21) تهی است

درج در dbo.#Tab2 (id, name)VALUES (0id,0name)

انتخاب * از dbo. #Tab2 -#1

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

CREATE TABLE dbo.#Tab2 (id int، نام varchar(30));

EXEC My_Procl 0name="lvan", 0id=2;

SELECT * FROM dbo.#Tab2; -#2

در مثال بالا، دستور SELECT دو بار اجرا می شود: بار اول - در داخل رویه، بار دوم - از قطعه کد فراخوانی (که با نظر "شماره 2" مشخص شده است).

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

DROP TABLE dbo.#Tab2;

EXEC My_Procl 0name="Ivan", 0id=2;

SELECT * FROM dbo.#Tab2; -#2

در این حالت، فقط عبارت SELECT در داخل رویه (با نظر "Xa 1") داده ها را نمایش می دهد. اجرای SELECT "#2" منجر به خطا می شود، زیرا جدول موقت ایجاد شده در رویه ذخیره شده از قبل از پایگاه داده tempdb با بازگشت رویه حذف می شود.

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

DROP (PROC I PROCEDURE) (رویه) [

به عنوان مثال، بیایید روال جمع بندی قبلی ایجاد شده را حذف کنیم:

جمع بندی DROP PROC.

می توانید با استفاده از عبارت ALTER PROCEDURE (اجازه دهید) روی یک رویه موجود تغییراتی ایجاد کنید (در واقع، آن را لغو کنید)

مخفف PROC). به استثنای کلمه کلیدی ALTER، فرمت عبارت تقریباً با فرمت CREATE PROCEDURE یکسان است. به عنوان مثال، اجازه دهید رویه dbo را تغییر دهیم. rownum با تنظیم آن برای اجرا در زمینه امنیتی مالک:

ALTER PROC dbo.rownum(SFirsYear int,

SLastYear int، Sresult int OUTPUT)

WITH EXECUTE AS Owner - گزینه تنظیم کنید

IF 0FirsYear>0 LastYear RETURN 1 ELSE BEGIN

SET 0result= (انتخاب COUNT(*) FROM dbo.Bookl

WHERE BETWEEN SFirsYear و SLastYear)؛

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

DECLARE 0y int = 2000;

EXEC("SELECT * FROM dbo.Bookl WHERE = " [ایمیل محافظت شده]) ;

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

بیایید مثال قبلی را کمی تغییر دهیم:

DECLARE 0y varchar(100);

SET 0y="2OOO"; - این چیزی است که ما از کاربر دریافت کردیم

اگر فرض کنیم که مقدار رشته تخصیص داده شده در دستور SET را از کاربر دریافت کرده‌ایم (بدون توجه به اینکه چگونه، برای مثال، از طریق یک برنامه وب)، آن‌گاه این مثال رفتار «عادی» کد ما را نشان می‌دهد.

DECLARE 0y varchar(100);

SET 0y="2000; DELETE FROM dbo.Book2"; - تزریق

EXEC("SELECT * FROM dbo.Book2 WHERE="+0y);

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

EXECUTE sp_executesql

N"SELECT * FROM dbo.Bookl WHERE =0y"،

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

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

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

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

یک رویه ذخیره شده قبل از اینکه به عنوان یک شی در پایگاه داده ذخیره شود از قبل کامپایل می شود. فرم از پیش کامپایل شده رویه در پایگاه داده ذخیره می شود و هر بار که فراخوانی می شود استفاده می شود. این ویژگی رویه‌های ذخیره‌شده مزیت مهم حذف (تقریباً در همه موارد) کامپایل‌های مجدد رویه‌ها و بهبود عملکرد مربوطه را فراهم می‌کند. این ویژگی رویه های ذخیره شده نیز بر میزان داده های مبادله شده بین سیستم پایگاه داده و برنامه ها تأثیر مثبت دارد. به طور خاص، فراخوانی یک روش ذخیره شده چند هزار بایتی ممکن است به کمتر از 50 بایت نیاز داشته باشد. هنگامی که چندین کاربر وظایف تکراری را با استفاده از رویه های ذخیره شده انجام می دهند، اثر تجمعی این پس انداز می تواند قابل توجه باشد.

رویه های ذخیره شده نیز می توانند برای اهداف زیر استفاده شوند:

برای ایجاد گزارشی از گزارش ها در مورد اقدامات با جداول پایگاه داده.

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

رویه‌های ذخیره‌شده‌ای که لاگ نوشتن و/یا خواندن جداول را انجام می‌دهند، امنیت بیشتری را برای پایگاه داده فراهم می‌کنند. با استفاده از چنین رویه هایی، مدیر پایگاه داده می تواند تغییرات ایجاد شده در پایگاه داده توسط کاربران یا برنامه ها را پیگیری کند.

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

رویه های ذخیره شده با استفاده از عبارت ایجاد می شوند ایجاد رویه، که دارای نحو زیر است:

CREATE PROC proc_name [((@param1) type1 [ متغیر] [= پیش‌فرض1] )] (،…) دسته AS | EXTERNAL NAME متد_نام قراردادهای نحوی

پارامتر schema_name نام طرحی را مشخص می کند که توسط مالک رویه ذخیره شده تولید شده اختصاص داده شده است. پارامتر proc_name نام رویه ذخیره شده را مشخص می کند. پارامتر @param1 یک پارامتر رویه (یک آرگومان رسمی) است که نوع داده آن توسط پارامتر type1 مشخص می شود. پارامترهای رویه در یک رویه محلی هستند، همانطور که متغیرهای محلی درون یک بسته محلی هستند. پارامترهای رویه مقادیری هستند که توسط تماس گیرنده برای استفاده در آن به رویه ارسال می شود. پارامتر default1 مقدار پیش فرض را برای پارامتر رویه مربوطه مشخص می کند. (مقدار پیش فرض نیز می تواند NULL باشد.)

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

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

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

به طور پیش فرض، فقط اعضای نقش سرور ثابت sysadmin و نقش پایگاه داده ثابت db_owner یا db_ddladmin می توانند از دستور CREATE PROCEDURE استفاده کنند. با این حال، اعضای این نقش‌ها می‌توانند با استفاده از دستورالعمل، این حق را به سایر کاربران واگذار کنند رویه ایجاد کمک هزینه.

مثال زیر نحوه ایجاد یک رویه ذخیره شده ساده برای کار با جدول Project را نشان می دهد:

از SampleDb استفاده کنید. برو به ایجاد رویه IncreaseBudget (@percent INT=5) به عنوان به روز رسانی مجموعه پروژه بودجه = بودجه + بودجه * @percent/100;

همانطور که قبلا ذکر شد، برای جدا کردن دو بسته استفاده می شود دستورالعمل GO. دستور CREATE PROCEDURE را نمی توان با سایر دستورات Transact-SQL در همان دسته ترکیب کرد. رویه ذخیره شده IncreaseBudget بودجه همه پروژه ها را با درصد مشخصی که با پارامتر @percent مشخص شده است افزایش می دهد. رویه همچنین یک مقدار درصد پیش فرض (5) را تعریف می کند که اگر این آرگومان در طول اجرای رویه وجود نداشته باشد اعمال می شود.

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

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

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

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

[] [@return_status =] (proc_name | @proc_name_var) ([[@parameter1 =] مقدار | [@parameter1=] @variable ] | پیش‌فرض.. قراردادهای نحوی

به استثنای پارامتر return_status، همه پارامترهای دستور EXECUTE دارای مقدار بولی یکسانی با پارامترهای دستور CREATE PROCEDURE با همین نام هستند. پارامتر return_status یک متغیر عدد صحیح را تعریف می کند که وضعیت بازگشتی رویه را ذخیره می کند. یک مقدار را می توان با استفاده از یک ثابت (مقدار) یا یک متغیر محلی (متغیر@) به یک پارامتر اختصاص داد. ترتیب مقادیر پارامترهای نامگذاری شده مهم نیست، اما مقادیر پارامترهای بدون نام باید به ترتیبی که در دستور CREATE PROCEDURE تعریف شده اند ارائه شوند.

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

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

از SampleDb استفاده کنید. EXECUTE IncreaseBudget 10;

دستور EXECUTE در این مثال، رویه ذخیره شده IncreaseBudget را اجرا می کند که بودجه تمام پروژه ها را 10% افزایش می دهد.

مثال زیر نحوه ایجاد یک رویه ذخیره شده برای پردازش داده ها در جداول Employee و Works_on را نشان می دهد:

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

مثال زیر استفاده از عبارت OUTPUT را در یک رویه ذخیره شده نشان می دهد:

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

اعلام @quantityDeleteEmployee INT. EXECUTE DeleteEmployee @empId=18316, @ [ایمیل محافظت شده]خروجی PRINT N"کارمندان حذف شدند: " + convert(nvarchar(30), @quantityDeleteEmployee);

این رویه تعداد پروژه هایی را که یک کارمند با شماره پرسنل @empId روی آنها کار می کند شمارش می کند و مقدار حاصل را به پارامتر ©counter اختصاص می دهد. پس از حذف تمام ردیف‌های یک شماره پرسنل معین از جداول Employee و Works_on، مقدار محاسبه‌شده به متغیر quantityDeleteEmployee@ اختصاص داده می‌شود.

مقدار پارامتر تنها در صورتی به روال فراخوانی بازگردانده می شود که گزینه OUTPUT مشخص شده باشد. در مثال بالا، رویه DeleteEmployee پارامتر @counter را به رویه فراخوانی ارسال می کند، بنابراین رویه ذخیره شده مقدار را به سیستم برمی گرداند. بنابراین، پارامتر @counter باید هم در گزینه OUTPUT در هنگام اعلام رویه و هم در دستور EXECUTE در هنگام فراخوانی آن مشخص شود.

WITH SETS بند یک دستور EXECUTE

در SQL Server 2012، عبارت EXECUTE تایپ می شود با بند نتایج مجموعه A که تحت شرایط خاص، می تواند شکل مجموعه نتیجه رویه ذخیره شده را تغییر دهد.

دو مثال زیر به توضیح این جمله کمک می کند. مثال اول یک مثال مقدماتی است که نشان می دهد در صورت حذف عبارت WITH Results SETs، نتیجه ممکن است چگونه باشد:

رویه EmployeesInDept یک روش ساده است که شماره پرسنل و نام خانوادگی همه کارکنان شاغل در یک بخش خاص را نشان می دهد. شماره بخش یک پارامتر رویه است و باید هنگام فراخوانی رویه مشخص شود. با اجرای این رویه، جدولی با دو ستون به دست می‌آید که سرفصل‌های آنها با نام ستون‌های مربوطه در جدول پایگاه داده مطابقت دارد. شناسه و نام خانوادگی SQL Server 2012 برای تغییر هدرهای ستون نتیجه (و همچنین نوع داده آنها) از عبارت جدید WITH RESULTS SETS استفاده می کند. کاربرد این بند در مثال زیر نشان داده شده است:

از SampleDb استفاده کنید. EXEC EmployeesInDept "d1" WITH Result SETs ((INT NOT NULL، [نام خانوادگی] CHAR(20) NOT NULL));

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

همانطور که می بینید، اجرای یک رویه ذخیره شده با استفاده از عبارت WITH RESULT SETS در یک دستور EXECUTE به شما امکان می دهد نام و نوع داده ستون های مجموعه نتیجه تولید شده توسط رویه را تغییر دهید. بنابراین، این قابلیت جدید انعطاف پذیری بیشتری را در اجرای رویه های ذخیره شده و قرار دادن نتایج آنها در جدول جدید فراهم می کند.

تغییر ساختار رویه های ذخیره شده

موتور پایگاه داده نیز از این بیانیه پشتیبانی می کند تغییر رویهبرای اصلاح ساختار رویه های ذخیره شده دستور ALTER PROCEDURE معمولاً برای اصلاح دستورات Transact-SQL در یک رویه استفاده می شود. تمام پارامترهای دستور ALTER PROCEDURE همان معنی را با پارامترهای دستور CREATE PROCEDURE با همان نام دارند. هدف اصلی استفاده از این عبارت، جلوگیری از نادیده گرفتن مجوزهای رویه ذخیره شده موجود است.

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

برای حذف یک یا گروهی از رویه های ذخیره شده، استفاده کنید بیانیه DROP PROCEDURE. فقط مالک رویه ذخیره شده یا اعضای نقش های ثابت db_owner و sysadmin می توانند یک رویه ذخیره شده را حذف کنند.

رویه های ذخیره شده و زمان اجرا زبان رایج

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

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

از SampleDb استفاده کنید. EXEC sp_configure "clr_enabled"، 1 RECONFIGURE

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

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

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

یک رویه را با استفاده از دستور EXECUTE اجرا کنید.

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

ابتدا برنامه مورد نظر را در یک محیط توسعه مانند ویژوال استودیو ایجاد کنید. برنامه تمام شده را با استفاده از کامپایلر سی شارپ یا ویژوال بیسیک به کد شیء کامپایل کنید. این کد در یک فایل کتابخانه پیوند پویا (.dll) ذخیره می شود که به عنوان منبع دستور CREATE ASSEMBLY عمل می کند، که کد اجرایی میانی را ایجاد می کند. سپس، یک دستور CREATE PROCEDURE را صادر کنید تا کد در حال اجرا به عنوان یک شی پایگاه داده ذخیره شود. در نهایت، این روال را با استفاده از دستور EXECUTE اجرا کنید.

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

با استفاده از System.Data.SqlClient. با استفاده از Microsoft.SqlServer.Server. کلاس عمومی جزئی StoredProcedures ( عمومی static int CountEmployees() ( ردیف های int؛ اتصال SqlConnection = SqlConnection جدید ("Context Connection=true")؛ connection.Open(); SqlCommand cmd = connection.CreateCommand(); cmd.CommandText = " count(*) به عنوان "تعداد کارمندان" " + "از کارمند"؛ ردیف = (int)cmd.ExecuteScalar(); connection.Close()؛ سطرهای برگشتی؛ ) )

این رویه یک پرس و جو را برای شمارش تعداد ردیف ها در جدول Employee پیاده سازی می کند. استفاده از دستورات در ابتدای برنامه، فضاهای نام مورد نیاز برای اجرای آن را مشخص می کند. استفاده از این دستورالعمل ها به شما این امکان را می دهد که نام کلاس ها را در کد منبع بدون تعیین صریح فضاهای نام مربوطه مشخص کنید. سپس کلاس StoredProcedures تعریف می شود که برای آن ویژگی SqlProcedure، که به کامپایلر اطلاع می دهد که این کلاس یک رویه ذخیره شده است. در داخل کد کلاس، متد CountEmployees() تعریف شده است. اتصال به سیستم پایگاه داده از طریق یک نمونه از کلاس برقرار می شود SqlConnection. برای باز کردن یک اتصال، از متد Open() این نمونه استفاده می شود. آ متد CreateCommand().به شما امکان می دهد به نمونه ای از یک کلاس دسترسی داشته باشید SqlCommnd، که دستور SQL مورد نظر به آن ارسال می شود.

در قطعه کد زیر:

Cmd.CommandText = "تعداد کارمندان (*) را به عنوان "تعداد کارمندان" انتخاب کنید " + "از کارمند";

از دستور SELECT برای شمارش تعداد ردیف‌های جدول Employee و نمایش نتیجه استفاده می‌کند. متن فرمان با تنظیم ویژگی CommandText متغیر cmd به نمونه ای که توسط متد ()CreateCommand برگردانده شده است، مشخص می شود. بعدی نامیده می شود متد ExecuteScalar()نمونه ای از SqlCommand. این روش یک مقدار اسکالر را برمی گرداند که به نوع داده عدد صحیح int تبدیل شده و به متغیر ردیف ها اختصاص داده می شود.

اکنون می توانید این کد را با استفاده از ویژوال استودیو کامپایل کنید. من این کلاس را با نام CLRStoredProcedures به پروژه اضافه کردم، بنابراین ویژوال استودیو اسمبلی به همین نام را با پسوند *.dll کامپایل می کند. مثال زیر مرحله بعدی ایجاد یک رویه ذخیره شده را نشان می دهد: ایجاد کد برای اجرا. قبل از اجرای کد در این مثال، باید محل فایل .dll کامپایل شده (معمولاً در پوشه Debug پروژه قرار دارد) را بدانید.

از SampleDb استفاده کنید. GO CREATE ASSEMBLY CLRStoredProcedures FROM "D:\Projects\CLRStoredProcedures\bin\Debug\CLRStoredProcedures.dll" با PERMISSION_SET = SAFE

دستور CREATE ASSEMBLY کد مدیریت شده را به عنوان ورودی می گیرد و یک شی مناسب ایجاد می کند که می توانید رویه های ذخیره شده در زمان اجرا (CLR)، توابع تعریف شده توسط کاربر و تریگرها را برای آن ایجاد کنید. این دستورالعمل دارای نحو زیر است:

CREATE ASSEMBLY assembly_name [ AUTHORIZATION owner_name ] FROM (dll_file) قراردادهای نحوی

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

با بند PERMISSION_SETیک بند بسیار مهم از عبارت CREATE ASSEMBLY است و همیشه باید مشخص شود. مجموعه ای از حقوق دسترسی اعطا شده به کد اسمبلی را تعریف می کند. مجموعه حقوق SAFE محدودترین است. کد اسمبلی که این حقوق را دارد نمی تواند به منابع سیستم خارجی مانند فایل ها دسترسی داشته باشد. مجموعه حقوق EXTERNAL_ACCESS به کد اسمبلی اجازه می دهد تا به منابع سیستم خارجی خاصی دسترسی پیدا کند، در حالی که مجموعه حقوق UNSAFE دسترسی نامحدودی به منابع، هم در داخل و هم در خارج از سیستم پایگاه داده را فراهم می کند.

برای ذخیره اطلاعات کد اسمبلی، کاربر باید بتواند یک عبارت CREATE ASSEMBLY صادر کند. اسمبلی متعلق به کاربر (یا نقش) است که دستورالعمل را اجرا می کند. با استفاده از عبارت AUTHORIZATION در عبارت CREATE SCHEMA می توانید مالک یک اسمبلی را تغییر دهید.

موتور پایگاه داده همچنین از دستورات ALTER ASSEMBLY و DROP ASSEMBLY پشتیبانی می کند. بیانیه ALTER ASSEMBLYبرای به روز رسانی یک اسمبلی به آخرین نسخه استفاده می شود. این دستورالعمل همچنین فایل های مرتبط با اسمبلی مربوطه را اضافه یا حذف می کند. بیانیه مونتاژ حذفاسمبلی مشخص شده و تمام فایل های مرتبط را از پایگاه داده فعلی حذف می کند.

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

از SampleDb استفاده کنید. GO CREATE PROCEDURE CountEmployees به عنوان نام خارجی CLRSstoredProcedures.StoredProcedures.CountEmployees

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

assembly_name.class_name.method_name

assembly_name - نام اسمبلی را مشخص می کند.

class_name - نام کلاس عمومی را مشخص می کند.

method_name - قسمت اختیاری، نام متدی را که در داخل کلاس تنظیم شده است را مشخص می کند.

اجرای رویه CountEmployees در مثال زیر نشان داده شده است:

از SampleDb استفاده کنید. اعلام تعداد @INT EXECUTE @count = CountEmployees PRINT @count -- بازگشت 7

دستور PRINT تعداد ردیف‌های فعلی جدول Employee را برمی‌گرداند.

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

اما ابتدا، کمی تئوری برای اینکه متوجه شوید رویه های ذخیره شده چیست و در T-SQL چه کاربردی دارند.

توجه داشته باشید! برای برنامه نویسان تازه کار، مطالب مفید زیر را در مورد T-SQL توصیه می کنم:

• برای مطالعه دقیق تر زبان T-SQL، خواندن کتاب - راه برنامه نویس T-SQL را نیز توصیه می کنم. آموزش Transact-SQL.

رویه های ذخیره شده در T-SQL چیست؟

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

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

در رویه‌های ذخیره‌شده، برخلاف توابع، از قبل امکان انجام عملیات اصلاح داده‌ها مانند: UNSERT، UPDATE، DELETE وجود دارد. همچنین، در رویه‌ها، می‌توانید تقریباً از هر نوع دستور SQL استفاده کنید، به عنوان مثال، CREATE TABLE برای ایجاد جداول یا EXECUTE، i.e. فراخوانی رویه های دیگر استثنا چندین نوع دستورالعمل مانند: ایجاد یا تغییر توابع، نماها، تریگرها، ایجاد طرحواره ها و چند دستورالعمل مشابه دیگر است، به عنوان مثال، تغییر زمینه اتصال پایگاه داده (USE) در یک رویه ذخیره شده نیز غیرممکن است.

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

رویه های ذخیره شده بسیار مفید هستند، آنها به ما کمک می کنند بسیاری از عملیات ها را خودکار یا ساده کنیم، به عنوان مثال، شما دائماً نیاز دارید که گزارش های تحلیلی پیچیده مختلفی را با استفاده از جداول محوری تولید کنید. اپراتور PIVOT. برای ساده سازی تشکیل کوئری ها با این عملگر ( همانطور که می دانید، نحو PIVOT نسبتاً پیچیده است، می توانید رویه ای بنویسید که به صورت پویا گزارش های خلاصه برای شما تولید کند، به عنوان مثال، در ماده "Dynamic PIVOT in T-SQL"، نمونه ای از اجرای این ویژگی در قالب یک رویه ذخیره شده ارائه شده است.

نمونه هایی از کار با رویه های ذخیره شده در Microsoft SQL Server

داده های اولیه برای مثال

تمام مثال‌های زیر در Microsoft SQL Server 2016 Express اجرا می‌شوند. برای اینکه نشان دهیم رویه های ذخیره شده چگونه با داده های واقعی کار می کنند، به این داده ها نیاز داریم، بیایید آن را ایجاد کنیم. به عنوان مثال، بیایید یک جدول تست ایجاد کنیم و چند رکورد به آن اضافه کنیم، فرض کنید یک جدول حاوی لیستی از محصولات با قیمت آنها خواهد بود.

بیانیه ایجاد جدول CREATE TABLE TestTable( INT IDENTITY(1,1) NOT NULL, INT NOT NULL, VARCHAR(100) NOT NULL, MONEY NULL) GO -- افزودن بیانیه داده INSERT INTO TestTable(CategoryId, ProductName, Price) VALUES (1) , "Mouse", 100), (1, "Keyboard", 200), (2, "Phone", 400) GO -- SELECT * FROM TestTable query

داده ها وجود دارد، حالا بیایید به ایجاد رویه های ذخیره شده برویم.

ایجاد یک رویه ذخیره شده در بیانیه T-SQL - CREATE PROCEDURE

رویه های ذخیره شده با استفاده از عبارت ایجاد می شوند ایجاد رویه، پس از این دستورالعمل باید نام رویه خود را بنویسید، سپس در صورت لزوم، پارامترهای ورودی و خروجی را در براکت تعریف کنید. پس از آن، کلمه کلیدی AS را می نویسید و یک بلوک دستورالعمل را با کلمه کلیدی BEGIN باز می کنید، این بلوک را با کلمه END می بندید. در داخل این بلوک، تمام دستورالعمل هایی را که الگوریتم شما را پیاده سازی می کند یا نوعی محاسبات متوالی را می نویسید، به عبارت دیگر در T-SQL برنامه ریزی می کنید.

به عنوان مثال، اجازه دهید یک رویه ذخیره شده بنویسیم که یک رکورد جدید اضافه می کند، i.e. مورد جدید در نمودار آزمایشی ما برای انجام این کار، ما سه پارامتر ورودی را تعریف می کنیم: @CategoryId - شناسه دسته محصول، @ProductName - نام محصول و @Price - قیمت محصول، این پارامتر برای ما اختیاری خواهد بود، یعنی. نمی توان آن را به رویه منتقل کرد ( به عنوان مثال، ما هنوز قیمت را نمی دانیم)، برای این مقدار پیش فرض را در تعریف آن تعیین می کنیم. این پارامترها در بدنه رویه هستند، یعنی. در بلوک BEGIN…END را می توان به همان روشی که متغیرهای معمولی استفاده کرد ( همانطور که می دانید متغیرها با علامت @ نشان داده می شوند). اگر نیاز به تعیین پارامترهای خروجی دارید، پس از نام پارامتر، کلمه کلیدی OUTPUT را مشخص کنید ( یا به اختصار OUT).

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

در اینجا کد این روش است من هم در مورد آن نظر دادم).

رویه ایجاد کنید CREATE PROCEDURE TestProcedure (--پارامترهای ورودی @CategoryId INT، @ProductName VARCHAR(100)، @Price MONEY = 0) در ابتدا --دستورالعمل هایی که الگوریتم شما را پیاده سازی می کنند --پردازش پارامترهای ورودی --حذف فضاهای اضافی در ابتدا و در انتهای رشته متن SET @ProductName = LTRIM(RTRIM(@ProductName)); --افزودن ورودی جدید INSERT INTO TestTable(CategoryId، ProductName, Price) VALUES (@CategoryId، @ProductName، @Price) --داده برگردان را انتخاب کنید * از TestTable WHERE CategoryId = @CategoryId END GO

اجرای یک رویه ذخیره شده در T-SQL - EXECUTE Command

همانطور که قبلاً اشاره کردم، می توانید یک رویه ذخیره شده را با استفاده از دستور EXECUTE یا EXEC اجرا کنید. پارامترهای ورودی با شمارش ساده آنها و ارائه مقادیر مناسب پس از نام رویه به رویه ها منتقل می شوند. برای پارامترهای خروجی، باید دستور OUTPUT را نیز مشخص کنید). با این حال، ممکن است نام پارامترها مشخص نباشد، اما در این مورد لازم است دنباله تعیین مقادیر، یعنی. مقادیر را به ترتیب تعریف پارامترهای ورودی مشخص کنید ( این همچنین در مورد پارامترهای خروجی صدق می کند).

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

در اینجا چند روش متفاوت اما معادل برای اجرای رویه‌های ذخیره‌شده، به‌ویژه رویه تست ما وجود دارد.

1. رویه را بدون تعیین قیمت فراخوانی کنید EXECUTE TestProcedure @CategoryId = 1, @ProductName = "Test product 1" --2. ما رویه را با قیمت مشخص شده EXEC TestProcedure @CategoryId = 1، @ProductName = "محصول آزمایشی 2"، @Price = 300 --3 می نامیم. ما رویه را بدون تعیین نام پارامترهای EXEC TestProcedure 1، "Test item 3"، 400 فراخوانی می کنیم.

تغییر رویه ذخیره شده به T-SQL - بیانیه ALTER PROCEDURE

با استفاده از دستورالعمل ها می توانید تغییراتی در الگوریتم رویه ایجاد کنید تغییر رویه. به عبارت دیگر، برای تغییر یک رویه از قبل موجود، فقط باید به جای CREATE PROCEDURE، ALTER PROCEDURE را بنویسید و هر چیز دیگری را در صورت لزوم تغییر دهید.

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

رویه را تغییر دهید ALTER PROCEDURE TestProcedure (--پارامترهای ورودی @CategoryId INT، @ProductName VARCHAR(100)، @Price MONEY) در ابتدا --دستورالعمل هایی که الگوریتم شما را پیاده سازی می کنند --پردازش پارامترهای ورودی --حذف فضاهای اضافی در ابتدا و انتهای از خطوط متنی SET @ProductName = LTRIM(RTRIM(@ProductName)); --افزودن رکورد جدید INSERT INTO TestTable (CategoryId، ProductName، Price) VALUES (@CategoryId، @ProductName، @Price) END GO

حذف یک رویه ذخیره شده در بیانیه T-SQL - DROP PROCEDURE

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

به عنوان مثال، بیایید رویه آزمایشی را که ایجاد کردیم حذف کنیم.

روش رها کردن TestProcedure

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

من همه چیز دارم، امیدوارم مطالب برای شما جالب و مفید بوده باشد، خداحافظ!