رویه های ذخیره شده چیست. بند رویه ایجاد کنید. اشکال زدایی ساده SQL
این خط را در مراحل خود بگنجانید - SET NOCOUNT ON:
با هر دستور DML، سرور SQL به دقت پیامی حاوی تعداد رکوردهای پردازش شده را به ما برمی‌گرداند. این اطلاعاتممکن است هنگام اشکال زدایی کد برای ما مفید باشد، اما پس از آن کاملاً بی فایده خواهد بود. با نوشتن SET NOCOUNT ON، این ویژگی را غیرفعال می کنیم. برای رویه های ذخیره شده حاوی چندین عبارت یا حلقه، این عمل می تواند عملکرد قابل توجهی را افزایش دهد، زیرا میزان ترافیک به میزان قابل توجهی کاهش می یابد.
تراکنش SQL
از نام طرحواره با نام شی استفاده کنید:
خب فکر کنم واضحه این عملیات به سرور می‌گوید کجا به دنبال اشیاء بگردد و به جای جستجوی تصادفی در سطل‌های آن، فوراً می‌داند کجا باید برود و چه چیزی را باید بردارد. با تعداد زیادی پایگاه داده، جداول و رویه های ذخیره شده، می تواند به میزان قابل توجهی در زمان و اعصاب ما صرفه جویی کند.
تراکنش SQL
SELECT * FROM dbo.MyTable --این کار خوبی است -- به جای SELECT * FROM MyTable --این کار بدی است --Call EXEC procedure dbo.MyProc --Good again --به جای EXEC MyProc -- بد!
از پیشوند "sp_" در نام رویه های ذخیره شده خود استفاده نکنید:
اگر نام رویه ما با "sp_" شروع شود، SQL Serverابتدا در پایگاه داده اصلی خود نگاه می کند. واقعیت این است که این پیشوند برای رویه های ذخیره شده داخلی شخصی سرور استفاده می شود. بنابراین، استفاده از آن ممکن است منجر به هزینه های اضافی و حتی نتایج نادرست در صورت یافتن رویه ای با همان نام شما در پایگاه داده آن شود.
از IF EXISTS (انتخاب 1) به جای IF EXISTS (انتخاب *) استفاده کنید:
برای بررسی اینکه آیا رکوردی در جدول دیگری وجود دارد، از دستور IF EXISTS استفاده می کنیم. این بیاناگر حداقل یک مقدار از عبارت داخلی برگردانده شود، مقدار true را برمی گرداند، مهم نیست "1"، همه ستون ها یا یک جدول. داده های برگشتی، در اصل، به هیچ وجه استفاده نمی شود. بنابراین، برای فشرده سازی ترافیک در حین انتقال داده، منطقی تر است که از "1" استفاده کنید، همانطور که در زیر نشان داده شده است.
رویه ذخیره شدهذخیره شده) یک شیء برنامه پایگاه داده نامگذاری شده است. SQL Server انواع مختلفی از رویه های ذخیره شده دارد.
رویه های ذخیره شده در سیستمرویه ذخیره شده سیستم) توسط توسعه دهندگان DBMS عرضه می شوند و برای انجام اقدامات در کاتالوگ سیستم یا دریافت اطلاعات سیستم استفاده می شوند. نام آنها معمولا با پیشوند "sp_" شروع می شود. رویه های ذخیره شده از همه نوع با دستور EXECUTE اجرا می شوند که می تواند به EXEC کوتاه شود. به عنوان مثال، رویه ذخیره شده sp_hellogins که بدون پارامتر اجرا می شود، دو گزارش از نام حساب تولید می کند. (انگلیسی)ورود به سیستم) و کاربران مربوطه آنها در هر پایگاه داده (انگلیسی)کاربران).
EXEC sp_hellogins;
برای ارائه ایده ای از اقدامات انجام شده با استفاده از رویه های ذخیره شده در سیستم، در جدول. 10.6 چند نمونه را نشان می دهد. در کل، بیش از هزار رویه ذخیره شده در سیستم در SQL Server وجود دارد.
جدول 10.6
نمونه های ذخیره شده در سیستم رویه های SQLسرور
کاربر می تواند رویه های ذخیره شده را در پایگاه داده های کاربر و در پایگاه داده برای اشیاء موقت ایجاد کند. در مورد دوم، رویه ذخیره شده خواهد بود زمانیمانند جداول موقت، نام رویه ذخیره شده موقت باید با پیشوند "#" شروع شود اگر یک رویه ذخیره شده موقت محلی است، یا "##" اگر جهانی باشد. یک رویه موقت محلی فقط می تواند در اتصالی که در آن ایجاد شده است استفاده شود، یک روش جهانی نیز می تواند در سایر اتصالات استفاده شود.
اشیاء 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 برمی‌گردانیم:
ایجاد 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 خواهد بود). در اپراتور استفاده می شود عملکرد PRINT CAST برای تبدیل مقدار یک متغیر 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) تهی است
INSERT INTO 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" قبل از علامت نقل قول نشان می دهد که این یک ثابت واقعی کلمه یونیکد است، همانطور که در این روش لازم است. به یک پارامتر می توان نه تنها یک مقدار ثابت، بلکه مقدار متغیر دیگری را نیز اختصاص داد.
1. خط را در مراحل خود بگنجانید - SET NOCOUNT ON:با هر دستور DML، سرور SQL به دقت پیامی حاوی تعداد رکوردهای پردازش شده را به ما برمی‌گرداند. این اطلاعات ممکن است در هنگام اشکال زدایی کد برای ما مفید باشد، اما پس از آن کاملاً بی فایده خواهد بود. با نوشتن SET NOCOUNT ON، این ویژگی را غیرفعال می کنیم. برای رویه های ذخیره شده حاوی چندین عبارت یا حلقه، این عمل می تواند عملکرد قابل توجهی را افزایش دهد، زیرا میزان ترافیک به میزان قابل توجهی کاهش می یابد.
PROC dbo.ProcName را ایجاد کنید
مانند
NOCOUNT ON را تنظیم کنید.
- کد رویه در اینجا
ستون 1 را از dbo.TblTable1 انتخاب کنید
- SET NOCOUNT را به حالت اولیه تغییر دهید
SET NOCOUNT OFF.
برو
2. از نام طرحواره با نام شی استفاده کنید:خب فکر کنم واضحه این عملیات به سرور می‌گوید کجا به دنبال اشیاء بگردد و به جای جستجوی تصادفی در سطل‌های آن، بلافاصله می‌داند کجا باید برود و چه چیزی را باید بردارد. با تعداد زیادی پایگاه داده، جداول و رویه های ذخیره شده، می تواند به میزان قابل توجهی در زمان و اعصاب ما صرفه جویی کند.
SELECT * FROM dbo.MyTable -- این یک راه خوب برای انجام آن است
-- بجای
SELECT * FROM MyTable -- این کار بدی است
-- فراخوان رویه
EXEC dbo.MyProc -- دوباره خوب است
--بجای
EXEC MyProc -- بد!
3. از پیشوند "sp_" در نام رویه های ذخیره شده خود استفاده نکنید:اگر نام رویه ما با "sp_" شروع شود، SQL Server ابتدا در پایگاه داده اصلی خود نگاه می کند. واقعیت این است که این پیشوند برای رویه های ذخیره شده داخلی شخصی سرور استفاده می شود. بنابراین، استفاده از آن ممکن است منجر به هزینه های اضافی و حتی نتایج نادرست در صورت یافتن رویه ای با همان نام شما در پایگاه داده آن شود.
4. از IF EXISTS (انتخاب 1) به جای IF EXISTS (انتخاب *) استفاده کنید:برای بررسی اینکه آیا رکوردی در جدول دیگری وجود دارد، از دستور IF EXISTS استفاده می کنیم. اگر حداقل یک مقدار از عبارت داخلی برگردانده شود، این عبارت true را برمی گرداند، مهم نیست "1"، همه ستون ها یا یک جدول. داده های برگشتی، در اصل، به هیچ وجه استفاده نمی شود. بنابراین، برای فشرده سازی ترافیک در حین انتقال داده، منطقی تر است که از "1" استفاده کنید، همانطور که در زیر نشان داده شده است:
IF EXISTS (1 را از sysobjects انتخاب کنید
WHERE name = "MyTable" و نوع = "U")
5. از TRY-Catch برای دریافت خطاها استفاده کنید:قبل از سرور 2005، پس از هر درخواست، تعداد زیادی بررسی خطا در رویه نوشته می شد. کد بیشتر همیشه منابع بیشتر و زمان بیشتری مصرف می کند. از سال 2005، SQL Server درست تر ظاهر شده است راه راحتراه حل های این مشکل:
شروع به امتحان کنید
--کد
پایان تلاش
شروع به گرفتن
- کد گرفتن خطا
END CATCH
نتیجه
در اصل، این تمام چیزی است که برای امروز دارم. یک بار دیگر تکرار می کنم که در اینجا فقط تکنیک هایی وجود دارد که من شخصاً در تمرین خود از آنها استفاده کردم و می توانم تأثیر آنها را تضمین کنم.
P.S.
اولین پست من، قاطعانه قضاوت نکنید.
رویه ذخیره شدهنوع خاصی از دسته دستور 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# و ویژوال بیسیک. زمان اجرا زبان مشترک همچنین اجازه می دهد تا این اشیاء با استفاده از سیستم زمان اجرا مشترک اجرا شوند.
زمان اجرا زبان مشترک از طریق گزینه فعال و غیرفعال می شود 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 = "sel 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برای به روز رسانی ساخت به استفاده می شود آخرین نسخه. این دستورالعمل همچنین فایل های مرتبط با اسمبلی مربوطه را اضافه یا حذف می کند. بیانیه DROPمونتاژاسمبلی مشخص شده و تمام فایل های مرتبط را از پایگاه داده فعلی حذف می کند.
مثال زیر نحوه ایجاد یک رویه ذخیره شده بر اساس کد مدیریت شده که قبلاً پیاده سازی شده را نشان می دهد:
از 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 را برمی‌گرداند.
رویه ذخیره شدهتنها در صورتی امکان پذیر است که در زمینه پایگاه داده ای که رویه در آن قرار دارد انجام شود.انواع رویه های ذخیره شده
انواع مختلفی در SQL Server وجود دارد رویه های ذخیره شده.
- سیستمیک رویه های ذخیره شدهطراحی شده برای انجام اقدامات اداری مختلف. تقریباً تمام اقدامات مدیریت سرور با کمک آنها انجام می شود. می توان گفت که سیستم رویه های ذخیره شدهیک رابط است که کار با جداول سیستم را فراهم می کند که در نهایت به تغییر، افزودن، حذف و بازیابی داده ها از جداول سیستمی پایگاه داده های کاربر و سیستم منجر می شود. سیستمیک رویه های ذخیره شدهدارای پیشوند sp_ هستند، در پایگاه داده سیستم ذخیره می شوند و می توانند در زمینه هر پایگاه داده دیگری فراخوانی شوند.
- سفارشی رویه های ذخیره شدهاقدامات خاصی را اجرا کنید. رویه های ذخیره شده- یک شی پایگاه داده کامل. در نتیجه هر کدام رویه ذخیره شدهدر یک پایگاه داده خاص قرار دارد و در آنجا اجرا می شود.
- موقت رویه های ذخیره شدهفقط برای مدت کوتاهی وجود دارند و پس از آن به طور خودکار توسط سرور از بین می روند. آنها به محلی و جهانی تقسیم می شوند. موقت محلی رویه های ذخیره شدهفقط از اتصالی که در آن ایجاد شده اند می توان فراخوانی کرد. هنگام ایجاد چنین رویه ای، باید نامی به آن داده شود که با یک کاراکتر # شروع شود. مانند همه اشیای موقت، رویه های ذخیره شدهاز این نوع زمانی که کاربر سرور را قطع، راه اندازی مجدد یا متوقف می کند، به طور خودکار حذف می شوند. جهانی موقت رویه های ذخیره شدهبرای هر اتصال سروری که رویه مشابهی دارد در دسترس است. برای تعریف آن کافی است نامی به آن بدهید که با کاراکترهای ## شروع شود. این رویه‌ها هنگام راه‌اندازی مجدد یا توقف سرور، یا زمانی که اتصالی که در زمینه آن ایجاد شده‌اند بسته می‌شود، حذف می‌شوند.
ایجاد، اصلاح و حذف رویه های ذخیره شده
ایجاد رویه ذخیره شدهشامل حل وظایف زیر است:
- تعریف نوع رویه ذخیره شده: موقت یا سفارشی. علاوه بر این، شما می توانید سیستم خود را ایجاد کنید رویه ذخیره شده، نامی با پیشوند sp_ داده و در پایگاه داده سیستم قرار دهید. چنین رویه ای در زمینه هر پایگاه داده در سرور محلی در دسترس خواهد بود.
- برنامه ریزی دسترسی در حین ایجاد رویه ذخیره شدهبه خاطر داشته باشید که همان حقوق دسترسی به اشیاء پایگاه داده را خواهد داشت که کاربری که آن را ایجاد کرده است.
- تعریف پارامترهای رویه ذخیره شده. مانند رویه های موجود در اکثر زبان های برنامه نویسی، رویه های ذخیره شدهممکن است پارامترهای ورودی و خروجی داشته باشد.
- توسعه کد رویه ذخیره شده. کد رویه می‌تواند شامل دنباله‌ای از هر دستور SQL، از جمله فراخوانی دیگران باشد. رویه های ذخیره شده.
ایجاد یک جدید و اصلاح یک موجود رویه ذخیره شدهبا دستور زیر انجام می شود:
<определение_процедуры>::= (ایجاد | تغییر ) procedure_name [;شماره] [(@parameter_name نوع داده ) [=پیش فرض] ][,...n] AS sql_statement [...n]
پارامترهای این دستور را در نظر بگیرید.
با استفاده از پیشوندهای sp_، #، ##، رویه ایجاد شده را می توان به عنوان یک رویه سیستمی یا موقت تعریف کرد. همانطور که از نحو دستور مشاهده می کنید، تعیین نام مالکی که رویه ایجاد شده به او تعلق دارد و همچنین نام پایگاه داده ای که باید در آن قرار گیرد مجاز نیست. بنابراین، به منظور تطبیق ایجاد شده رویه ذخیره شدهدر یک پایگاه داده خاص، باید دستور CREATE PROCEDURE را در زمینه آن پایگاه داده اجرا کنید. هنگامی که از بدن به کار گرفته می شود رویه ذخیره شدهنام های کوتاه شده را می توان برای اشیاء در یک پایگاه داده استفاده کرد، یعنی بدون تعیین نام پایگاه داده. هنگامی که می خواهید به اشیایی که در پایگاه داده های دیگر قرار دارند مراجعه کنید، تعیین نام پایگاه داده الزامی است.
شماره در نام شماره شناسایی است رویه ذخیره شده، که به طور منحصر به فرد آن را در گروهی از رویه ها تعریف می کند. برای راحتی مدیریت رویه ها، منطقاً همان نوع است رویه های ذخیره شدهرا می توان با دادن نام یکسان اما شماره شناسایی متفاوت گروه بندی کرد.
برای ارسال داده های ورودی و خروجی در ایجاد شده رویه ذخیره شدهمی توان از پارامترهایی استفاده کرد که نام آنها، مانند نام متغیرهای محلی، باید با علامت @ شروع شود. یکی رویه ذخیره شدهمی توانید چندین گزینه را مشخص کنید که با کاما از هم جدا شده اند. بدنه یک رویه نباید از متغیرهای محلی استفاده کند که نام آنها با نام پارامترهای رویه یکسان است.
برای تعیین نوع داده ای که مربوطه است پارامتر رویه ذخیره شده، هر نوع داده SQL خوب است، از جمله انواع تعریف شده توسط کاربر. با این حال، نوع داده CURSOR فقط می تواند به عنوان استفاده شود پارامتر خروجی رویه ذخیره شده، یعنی با کلمه کلیدی OUTPUT .
وجود کلمه کلیدی OUTPUT به این معنی است که پارامتر مربوطه برای برگرداندن داده ها از آن در نظر گرفته شده است رویه ذخیره شده. با این حال، این به هیچ وجه به این معنی نیست که پارامتر برای ارسال مقادیر به آن مناسب نیست رویه ذخیره شده. تعیین کلمه کلیدی OUTPUT به سرور دستور خروج از آن را می دهد رویه ذخیره شدهمقدار فعلی پارامتر را به متغیر محلی که هنگام فراخوانی رویه به عنوان مقدار پارامتر مشخص شده است، اختصاص دهید. توجه داشته باشید که هنگام تعیین کلمه کلیدی OUTPUT، مقدار پارامتر مربوطه هنگام فراخوانی رویه فقط با استفاده از یک متغیر محلی قابل تنظیم است. هر گونه عبارت یا ثابتی که برای پارامترهای عادی مجاز است مجاز نیست.
کلمه کلیدی VARYING همراه با استفاده می شود