آموزش جامع SQL Server (جلسه ۲۱: stored procedureها – بخش۱)
1398/03/29 12:29 , میلاد صاحب نظر

آموزش جامع SQL Server (جلسه ۲۱: stored procedureها – بخش۱)

یک Stored Procedure در SQL Server یک یا تعداد بیشتری عبارات Transact-SQL را درون یک واحد منطقی گروه‌بندی کرده و به عنوان یک شیء در سرور پایگاه داده ذخیره می‌شود.

۱- نصب و آشنایی با SQL Server

۱۰-عملگرهای SET وCTEوPIVOT

۱۹-indexها بخش۱

۲۸-توابع تاریخ یا date بخش۲

۲-نوشتن query

۱۱-تغییر داده‌ها

۲۰-indexها بخش۲

۲۹-توابع رشته‌ای بخش۱

۳-محدود کردن ردیف‌ها

۱۲-تعریف داده‌ها بخش۱

۲۱-stored procedureها بخش۱

۳۰- توابع رشته‌ای بخش۲

۴-فیلتر کردن داده‌ها بخش ۱

۱۳-تعریف داده‌ها بخش۲

۲۲- stored procedureها بخش۲

۳۱- توابع رشته‌ای بخش۳

۵-فیلتر کردن داده‌ها بخش۲

۱۴-data typeهای بخش۱

۲۳- stored procedureها بخش۳

۳۲-توابع سیستمی بخش ۱

۶-اتصال جداول بخش۱

۱۵-data typeها بخش۲

۲۴-توابع تعریف شده توسط کاربر

۳۳-توابع سیستمی بخش۲

۷-اتصال جداول بخش۲

۱۶-ویژگی‌ها

۲۵-triggerها

۳۴-window functionها بخش۱

۸-گروه‌بندی داده‌ها

۱۷-expressionها

۲۶-توابع تجمعی

۳۵-window functionها بخش ۲

۹-subquery

۱۸-viewها

۲۷-توابع تاریخ یا date بخش۱

 

وقتی یک stored procedure برای اولین بار فراخوانی می‌شود، SQL Server یک طرح اجرایی ایجاد می‌کند و آن را در حافظه کش طرح ذخیره می‌کند.

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

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

بخش ۱: آغاز کار با stored procedureها در SQL Server
  • یک راهنمای مقدماتی برای کار با stored procedureها: چگونگی ایجاد، اجرا، تغییر و حذف stored procedure در SQL Server را به شما نشان می‌دهد.

  • پارامترها: چگونگی ایجاد stored procedureها با استفاده از پارامترها را آموزش می‌دهد. همچنین پارامترهای اختیاری را نیز پوشش می‌دهد.

  • متغیرها: شما را با متغیرهای Transact-SQL آشنا می‌کند و چگونگی دست‌کاری متغیرها در stored procedureها را نیز نشان می‌دهد.

  • پارامترهای خروجی: داده‌ها را با استفاده از پارامترهای خروجی، از یک stored procedure به سمت برنامه فراخواننده بازمی‌گرداند.
بخش ۲: عبارات کنترل جریان (Control-of-flow) و کرسرها (cursor)
  • BEGIN…END: یک قطعه دستور یا عبارت ایجاد می‌کند که از چندین عبارت Transact-SQL تشکیل شده است که همراه با یکدیگر اجرا می‌شوند.

  • IF ELSE: یک قطعه دستور یا عبارت را بر اساس یک شرط اجرا می‌کند.

  • WHILE: به صورت مکرر مجموعه‌ای از عبارات یا دستورات را بر اساس یک شرط اجرا می‌کند.

  • BREAK: سریعاً از حلقه خارج می‌شود.

  • CONTINUE: از اجرا یا تکرار فعلی حلقه سریعاً خارج می‌شود و به اجرای بعدی ادامه می‌دهد.

  • Cursor: آشنایی با کرسر در SQL Server.
بخش ۳: کنترل Exceptionها و SQL پویا
  • TRY CATCH: کنترل عالی exceptionها در stored procedureها.

  • RAISERROR: ایجاد پیام‌های ارور تعریف شده توسط کاربر و بازگرداندن آن به برنامه با استفاده از فرمت مشابه با ارور سیستمی.

  • THROW: وقوع یک Exsception و انتقال جریان اجرا به بلوک CATCH از یک ساختار TRY CATCH.

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

در ادامه به توضیح مباحث بخش ۱ خواهیم پرداخت.

راهنمای مقدماتی برای کار با Stored Procedureها در SQL Server

در این قسمت خواهید آموخت که چگونه می‌توانید stored procedureها را در SQL Server مدیریت کنید (از جمله ایجاد، اجرا، تغییر و حذف stored procedureها).

ایجاد یک stored procedure ساده

دستور SELECT زیر، لیستی از محصولات را از جدول poroducts در پایگاه داده نمونه Bikestores بازمی‌گرداند:

SELECT 
 product_name, 
 list_price
FROM 
 production.products
ORDER BY 
 product_name;

برای ایجاد یک stored procedure که این query را کامل در بر بگیرد، می‌توانید از عبارت CREATE PROCEDURE به این شکل استفاده کنید:

CREATE PROCEDURE uspProductList
AS
BEGIN
    SELECT 
        product_name, 
        list_price
    FROM 
        production.products
    ORDER BY 
        product_name;
END;

در این syntax:

  • uspProductList نام stored procedure است.

  • کلیدواژه AS هدینگ و بدنه stored procedure را از هم جدا می‌کند.

  • اگر stored procedure دارای یک عبارت یا کد باشد، کلیدواژه‌های BEGIN و AND که در اطراف عبارت یا کد قرار می‌گیرند اختیاری هستند. به هر حال، تمرین خوبی است که آن‌ها را در کد استفاده کنید تا بتوانید کد بهتر و مرتب‌تری داشته باشید.

توجه کنید که علاوه بر کلیدواژه‌های CREATE PROCEDURE، می‌توانید از کلیدواژه‌های CREATE PROC برای مختصر کردن کدها استفاده کنید.

برای کامپایل این stored procedure، باید آن را به عنوان یک کد SQL نرمال در SQL Server Management Studio همانند تصویر زیر اجرا کنید:

باید آن را به عنوان یک کد SQL نرمال در SQL Server Management Studio  اجرا کنید

اگر همه کارها درست پیش بروند، آنگاه پیام زیر را خواهید دید:

Commands completed successfully.

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

می‌توانید stored procedure را در پنجره Object Explorer در آدرس Programmability >> Stored Procedure بیابید که در تصویر زیر نیز مشاهده می‌کنید:

می‌توانید stored procedure را در پنجره Object Explorer در آدرس Programmability >> Stored Procedure بیابید

گاهی، لازم است روی کلید Refresh کلیک کنید تا به صورت دستی اشیاء پایگاه داده در Object Explorer به روز رسانی شوند.

اجرای یک stored procedure

برای اجرای یک stored procedure، از دستور EXECUTE یا EXEC استفاده می‌کنیم و پس از آن نام stored procedure را می‌نویسیم:

EXECUTE sp_name;

يا

EXEC sp_name;

که sp_name نام stored procedure مورد نظر ما برای اجرا است.

برای مثال، برای اجرای stored procedure به نام uspProdecutList، از دستور زیر استفاده می‌کنیم:

EXEC uspProductList;

stored procedure خروجی زیر را بازمی‌گرداند:

نتایج

تغییر یک stored procedure

برای تغییر یا اصلاح یک stored procedure موجود، از دستور ALTER PROCEDURE استفاده می‌کنیم.

ابتدا، فولدر stored procedure را باز کنید تا محتوای آن را ببینید، سپس روی نام stored procedure مورد نظر کلیک راست کنید و از آیتم‌های داخل منو گزینه Modify را انتخاب کنید:

روی نام stored procedure مورد نظر کلیک راست کنید و از آیتم‌های داخل منو گزینه Modify را انتخاب کنید

سپس، بدنه stored procedure را با مرتب‌سازی محصولات بر اساس لیست قیمت‌ها (به جای نام‌ محصولات) تغییر دهید:

ALTER PROCEDURE uspProductList
    AS
    BEGIN
        SELECT 
            product_name, 
            list_price
        FROM 
            production.products
        ORDER BY 
            list_price 
    END;

بعد، روی کلید Execute کلیک کنید. سیستم stored procedure را تغییر می‌دهد و خروجی زیر را بازمی‌گرداند:

Commands completed successfully.

حالا، اگر دوباره stored procedure را اجرا کنید، خواهید دید که تغییرات اعمال می‌شوند:

EXEC uspProductList;

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

نتایج

حذف یک stored procedure

برای حذف یک stored procedure، می‌توانید از دستور DROP PROCEDURE یا DROP PROC استفاده کنید:

DROP PROCEDURE sp_name;

یا

DROP PROC sp_name;    

که sp_name نام stored procedure مورد نظر شما است که می‌خواهید حذف شود.

برای مثال، برای حذف stored procedure به نام uspProductList، دستور زیر را باید اجرا کنیم:

DROP PROCEDURE uspProductList;

در نتیجه، چگونگی مدیریت stored procedureها در SQL Server (به خصوص اجرا، تغییر و حذف stored procedureها) را آموختید.

پارامترهای stored procedure در SQL Server

در قسمت قبلی، چگونگی ایجاد یک stored procedure ساده که یک دستور SELECT را پوشش می‌داد را آموختید. وقتی این stored procedure را فراخوانی می‌کنید، خیلی ساده query داخل آن اجرا شده و یک مجموعه نتیجه بازمی‌گرداند.

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

ایجاد یک stored procedure با یک پارامتر

Query زیر یک لیست محصول از جدول products در پایگاه داده نمونه Bikestores بازمی گرداند:

SELECT
    product_name,
    list_price
FROM 
    production.products
ORDER BY
    list_price;

می‌توانید یک stored procedure ایجاد کنید که با استفاده از دستور CREATE PROCEDURE این query را احاطه کند:

CREATE PROCEDURE uspFindProducts
AS
BEGIN
    SELECT
        product_name,
        list_price
    FROM 
        production.products
    ORDER BY
        list_price;
END;

به هر حال، این دفعه می‌توانیم یک پارامتر به stored procedure اضافه کنیم  تا محصولاتی که لیست قیمت‌های آن‌ها بیشتر از یک قیمت ورودی هستند را بیابد:

ALTER PROCEDURE uspFindProducts(@min_list_price AS DECIMAL)
AS
BEGIN
    SELECT
        product_name,
        list_price
    FROM 
        production.products
    WHERE
        list_price >= @min_list_price
    ORDER BY
        list_price;
END;

در این مثال:

  • ابتدا، یک پارامتر به نام min_list_price@ به stored procedure به نام uspFindProducts اضافه کردیم. هر پارامتر باید با علامت @ آغاز شود. کلیدواژه‌های AS DECIMAL نوع داده پارامتر min_list_price@ را مشخص می‌کنند. پارامتر باید با آکولادهای باز و بسته احاطه شوند.

  • سپس، از پارامتر min_list_price@ در دستور WHERE درون دستور SELECT برای فیلتر محصولاتی که لیست قیمت‌های آن‌ها بیشتر یا برابر با min_list_price@ هستند، استفاده کردیم.

اجرای یک stored procedure با یک پارامتر

برای اجرای stored procedure به نام uspFindProducts، باید به شکل زیر یک آرگومان به آن ارسال کنید:

EXEC uspFindProducts 100;

نتایج

stored procedure تمام محصولاتی که لیست قیمت‌های آن‌ها بیشتر یا برابر با ۱۰۰ هستند را بازمی‌گرداند.

اگر آرگومان را به ۲۰۰ تغییر دهید، مجموعه نتایج متفاوتی دریافت خواهید کرد:

EXEC uspFindProducts 200;

نتایج

ایجاد یک stored procedure با چندین پارامتر

stored procedureها می‌توانند یک یا چند پارامتر بگیرند. پارامترها با ویرگول از هم جدا می‌شوند.

کد زیر stored procedure به نام uspFindProducts را با اضافه کردن یک پارامتر دیگر به نام max_list_price@ به آن، تغییر می‌دهد:

ALTER PROCEDURE uspFindProducts(
    @min_list_price AS DECIMAL
    ,@max_list_price AS DECIMAL
)
AS
BEGIN
    SELECT
        product_name,
        list_price
    FROM 
        production.products
    WHERE
        list_price >= @min_list_price AND
        list_price <= @max_list_price
    ORDER BY
        list_price;
END;

وقتی stored procedure با موفقیت تغییر کرد، می‌توانید آن را با ارسال دو آرگومان (یکی برای min_list_price@ و دیگری برای max_list_price@) اجرا کنید:

EXECUTE uspFindProducts 900, 1000;

تصویر زیر خروجی را نشان می‌دهد:

نتایج

استفاده از پارامترهای دارای نام

در صورتی که stored procedureها چندین پارامتر داشته باشند، بهتر و به صرفه‌تر است که stored procedureها را با استفاده از پارامترهای دارای نام اجرا کنید.

برای مثال، کد زیر stored procedure به نام uspFindProducts را با استفاده از پارامتر‌های دارای نام (یعنی min_list_price@ و max_list_price@) اجرا می‌کند:

EXECUTE uspFindProducts 
    @min_list_price = 900, 
    @max_list_price = 1000;

نتیجه stored procedure همان نتیجه قبلی است، با این تفاوت که الآن کد ما خیلی واضح‌تر و مرتب‌تر است.

ایجاد پارامترهای متنی

کد زیر پارامتر name@ را به عنوان یک پارامتر رشته حروف به stored procedure اضافه می‌کند.

ALTER PROCEDURE uspFindProducts(
    @min_list_price AS DECIMAL
    ,@max_list_price AS DECIMAL
    ,@name AS VARCHAR(max)
)
AS
BEGIN
    SELECT
        product_name,
        list_price
    FROM 
        production.products
    WHERE
        list_price >= @min_list_price AND
        list_price <= @max_list_price AND
        product_name LIKE '%' + @name + '%'
    ORDER BY
        list_price;
END;

در دستور WHERE از کد SELECT، شرط زیر را اضافه می‌کنیم:

product_name LIKE '%' + @name + '%'

با این کار، stored procedure محصولاتی که لیست قیمت‌های آن‌ها در بازه حداقل و حداکثر لیست قیمت‌ها هستند را بازمی‌گرداند و نام محصولات نیز شامل یک قطعه متن هستند که شما ارسال می‌کنید.

وقتی stored procedure با موفقیت تغییر کرد، می‌توانید به شکل زیر آن را اجرا کنید:

EXECUTE uspFindProducts 
    @min_list_price = 900, 
    @max_list_price = 1000,
    @name = 'Trek';

در این کد، ما از stored procedure به نام uspFindProducts برای یافتن محصولاتی که لیست قیمت آن‌ها در بازه ۹۰۰ و ۱۰۰۰ و نام آن‌ها شامل واژه Trek باشد استفاده کردیم.

تصویر زیر خروجی را نشان می‌دهد:

نتایج

ایجاد پارامترهای اختیاری

وقتی uspFinProducts را اجرا می‌کنید، باید تمام سه آرگومان متناظر با سه پارامتر آن را نیز ارسال کنید.

SQL Server به شما اجازه می‌دهد مقادیر پیش‌فرض برای پارامترها مشخص کنید تا وقتی stored procedureها را فراخوانی می‌کنید، می‌توانید پارامترها را با مقادیر پیش‌فرض ارسال کنید.

stored procedure زیر را ببینید:

ALTER PROCEDURE uspFindProducts(
    @min_list_price AS DECIMAL = 0
    ,@max_list_price AS DECIMAL = 999999
    ,@name AS VARCHAR(max)
)
AS
BEGIN
    SELECT
        product_name,
        list_price
    FROM 
        production.products
    WHERE
        list_price >= @min_list_price AND
        list_price <= @max_list_price AND
        product_name LIKE '%' + @name + '%'
    ORDER BY
        list_price;
END;

در این stored procedure، ما 0 را به عنوان مقدار پیش‌فرض برای پارامتر min_list_price@ و 999.999 را به عنوان مقدار پیش‌فرض برای پارامتر max_list_price@ قرار داده‌ایم.

وقتی stored procedure کامپایل می‌شود، می‌توانید آن را بدون ارسال آرگومان به پارامترهای min_list_price@ و max_list_price@ اجرا کنید:

EXECUTE uspFindProducts 
    @name = 'Trek';

نتایج

در این صورت، stored procedure در زمان اجرای query از 0 برای پارامتر min_list_price@ و از 999.999 برای پارامتر max_list_price@ استفاده می‌کند.

پارامتر‌های min_list_price@ و max_list_price@ پارامترهای اختیاری فراخوانی کرده‌اند.

مسلماً، همچنین می‌توانید آرگومان‌ها را به پارامترهای اختیاری نیز ارسال کنیم. برای مثال، کد زیر تمام محصولاتی که لیست قیمت‌های آن‌ها بیشتر یا برابر با 6000 و نام آن‌ها شامل واژه Trek است را بازمی‌گرداند:

EXECUTE uspFindProducts 
    @min_list_price = 6000,
    @name = 'Trek';

نتایج

استفاده از NULL به عنوان مقدار پیش‌فرض

در uspFindProducts، ما از 999.999 به عنوان قیمت حداکثر پیش‌فرض استفاده کردیم. این کار جالبی نیست، چون در آینده ممکن است محصولات با قیمت‌های بیشتر از این مقدار داشته باشید.

یک تکنیک مرسم برای پرهیز از این امر، استفاده از NULL به عنوان مقدار پیش‌فرض برای پارامترها است:

ALTER PROCEDURE uspFindProducts(
    @min_list_price AS DECIMAL = 0
    ,@max_list_price AS DECIMAL = NULL
    ,@name AS VARCHAR(max)
)
AS
BEGIN
    SELECT
        product_name,
        list_price
    FROM 
        production.products
    WHERE
        list_price >= @min_list_price AND
        (@max_list_price IS NULL OR list_price <= @max_list_price) AND
        product_name LIKE '%' + @name + '%'
    ORDER BY
        list_price;
END;

در دستور WHERE، ما شرط را تغییر دادیم تا بتوانیم از مقدار NULL برای پارامتر max_list_price@ استفاده کنیم:

(@max_list_price IS NULL OR list_price <= @max_list_price) 

کد زیر uspFindProducts را برای یافتن محصولاتی که قیمت آن‌ها بزرگ‌تر یا برابر با 500 و نام آن‌ها شامل واژه Haro هستند، اجرا می‌کند.

EXECUTE uspFindProducts 
    @min_list_price = 500,
    @name = 'Haro';

نتایج

در این قسمت، چگونگی ایجاد و اجرای stored procedureها با یک یا چند پارامتر را آموختید. همچنین چگونگی ایجاد پارامترهای اختیاری و استفاده از NULL به عنوان مقادیر پیش‌فرض برای پارامترها را نیز آموختید.

متغیرها

در این قسمت راجع به متغیرها (به خصوص تعریف متغیرها،ع تنظیم مقادیر آن‌ها و اختصاص فیلدهای مقدار یک رکورد به متغیرها) خواهید آموخت.

متغیر چیست؟

متغیر، یک شیء است که یک مقدار از یک نوع مشخص (مثل date، integer یا رشته حروف متفاوت) را در خود نگه می‌دارد.

معمولاً از متغیرها برای موارد زیر استفاده می‌کنیم:

  • به عنوان یک شمارنده حلقه برای شمارش تعداد دفعاتی که حلقه اجرا شده است.

  • برای نگه داشتن یک مقدار که توسط یک دستور کنترل جریان (مانند WHILE) تست می‌شود.

  • برای ذخیره مقدار بازگشتی توسط یک stored procedure یا یک تابع.

تعریف یک متغیر

برای تعریف یک متغیر، می‌توانید از دستور DECLARE استفاده کنید. برای مثال، کد زیر یک متغیر به نام odel_year@ تعریف می‌کند:

DECLARE @model_year SMALLINT;

دستور DECLARE با اختصاص دادن یک نام و یک نوع داده، یک متغیر ایجاد می‌کند. نام متغیر باید با علامت @ آغاز شود. در این مثال، نوع داده متغیر model_year@ برابر با SMALLINT است.

به صورت پیش‌فرض، وقتی یک متغیر تعریف می‌شود، مقدار آن برابر با NULL خواهد بود.

بین نام متغیر و نوع داده، می‌توانید از کلیدواژه اختیاری AS به این شکل استفاده کنید:

DECLARE @model_year AS SMALLINT;

برای تعریف چندین متغیر، متغیرها را با ویرگول از هم جدا می‌کنیم:

DECLARE @model_year SMALLINT, 
        @product_name VARCHAR(MAX);

اختصاص یک مقدار به یک متغیر

برای اختصاص یک مقدار به یک متغیر، می‌توانید از دستور SET استفاده کنید. برای مثال، کد زیر 2018 را به متغیر model_year@ اختصاص می‌دهد:

SET @model_year = 2018;

استفاده از متغیرها در یک query

دستور SELECT زیر از متغیر model_year@ در دستور WHERE استفاده می‌کند تا محصولات یک مدل سال خاص را بیابد:

SELECT
    product_name,
    model_year,
    list_price 
FROM 
    production.products
WHERE 
    model_year = @model_year
ORDER BY
    product_name;

حالا، می‌توانید همه چیز را در کنار هم قرار دهید و قطعه کد زیر را اجرا کنید تا لیستی از محصولاتی که مدل سال آن‌ها 2018 است را به دست آورید:

DECLARE @model_year SMALLINT;
 
SET @model_year = 2018;
 
SELECT
    product_name,
    model_year,
    list_price 
FROM 
    production.products
WHERE 
    model_year = @model_year
ORDER BY
    product_name;

توجه کنید که برای اجرای کد، همان‌طور که در تصویر زیر مشاهده می‌کنید باید روی کلید Execute کلیک کنید:

 روی کلید Execute کلیک کنید

تصویر زیر خروجی را نشان می‌دهد:

نتایج

ذخیره نتیجه query در یک متغیر

مراحل زیر چگونگی ذخیره نتیجه query در یک متغیر را توضیح می‌دهند:

ابتدا، یک متغیر به نام product_count@ با نوع داده integer تعریف کنید:

DECLARE @product_count INT;

سپس، از دستور SET برای اختصاص مجموعه نتیجه query به متغیر استفاده کنید:

SET @product_count = (
    SELECT 
        COUNT(*) 
    FROM 
        production.products 
);

بعد، محتوای متغیر product_count@ را خروجی بگیرید:

SELECT @product_count;

یا می‌توانید از دستور PRINT برای چاپ محتوای یک متغیر استفاده کنید:

PRINT @product_count;

یا

PRINT 'The number of products is ' + CAST(@product_count AS VARCHAR(MAX));

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

The number of products is 204

برای پنهان کردن پیام‌های تعداد ردیف‌های تحت تأثیر قرار  گرفته، می‌توانید از دستور زیر استفاده کنید:

SET NOCOUNT ON;    

انتخاب یک رکورد از داخل متغیرها

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

اول، متغیرهایی که نام و قیمت محصولات را نگه می‌دارند را تعریف کنید:

DECLARE 
    @product_name VARCHAR(MAX),
    @list_price DECIMAL(10,2);

دوم، ستون‌ نام‌ها را به متغیرهای متناظر اختصاص دهید:

SELECT 
    @product_name = product_name,
    @list_price = list_price
FROM
    production.products
WHERE
    product_id = 100;

سوم، محتوای متغیرها را خروجی بگیرید:

SELECT 
    @product_name AS product_name, 
    @list_price AS list_price;

نتایج

تجمع مقادیر درون یک متغیر

Stored procedure زیر یک پارامتر را می‌گیرد و لیستی از محصولات را به شکل یک رشته بازمی‌گرداند:

CREATE  PROC uspGetProductList(
    @model_year SMALLINT
) AS 
BEGIN
    DECLARE @product_list VARCHAR(MAX);
 
    SET @product_list = '';
 
    SELECT
        @product_list = @product_list + product_name 
                        + CHAR(10)
    FROM 
        production.products
    WHERE
        model_year = @model_year
    ORDER BY 
        product_name;
 
    PRINT @product_list;
END;

در این stored procedure:

  • اول، یک متغیر به نام product_list@ با نوع رشته حروف متفاوت تعریف کردیم و مقدار را خالی گذاشتیم.

  • دوم، لیست نام محصولات را از جدول products بر اساس ورودی model_year@ انتخاب کردیم. در لیست انتخابی، ما نام محصولات را درون متغیر product_list@ انبار یا جمع کردیم. توجه کنید که (10)CHAR حروف کد اسکی (line feed) را بازمی‌گرداند.

  • سوم، از دستور PRINT برای چاپ لیست محصولات استفاده کردیم.

کد زیر stored procedure به نام uspGetProductList را اجرا می‌کند:

EXEC uspGetProductList 2018

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

نتایج

در این قسمت، راجع به متغیرها (به خصوص تعریف متغیرها، تنظیم مقادیر آن‌ها و اختصاص فیلدهای مقدار یک رکورد به متغیرها) آموختید.

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

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

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

برای ایجاد یک پارامتر خروجی برای یک stored procedure، از syntax زیر استفاده می‌کنیم:

parameter_name data_type OUTPUT

یک stored procedure ممکن است پارامترهای خرجی زیادی داشته باشد. علاوه بر این، پارامترهای خروجی می‌توانند هر نوع داده قابل قبولی (مانند date، integer و حروف مختلف)داشته  باشند.

برای مثال، stored procedure زیر محصولات را بر اساس مدل سال می‌یابد و تعداد محصولات را از طریق پارامتر خروجی product_count@ بازمی‌گرداند:

CREATE PROCEDURE uspFindProductByModel (
    @model_year SMALLINT,
    @product_count INT OUTPUT
) AS
BEGIN
    SELECT 
        product_name,
        list_price
    FROM
        production.products
    WHERE
        model_year = @model_year;
 
    SELECT @product_count = @@ROWCOUNT;
END;

در این stored procedure:

ابتدا، یک پارامتر خروجی به نام product_count@ ایجاد کردیم تا تعداد محصولات یافته شده را ذخیره کند:

@product_count INT OUTPUT

سپس، بعد از دستور SELECT، ما تعداد ردیف‌های بازگشتی توسط query (یعنیROWCOUNT@@) را به پارامتر product_count@ اختصاص داده‌ایم.

وقتی دستور CREATE PROCEDURE بالا را اجرا می‌کنید، stored procedure به نام uspFindProductByModel کامپایل شده و در کاتالوگ پایگاه داده ذخیره می‌شود.

اگر همه چیز درست پیش برود، SQL Server خروجی زیر را نشان می‌دهد:

Commands completed successfully.

فراخوانی stored procedureها با پارامترهای خروجی

برای فراخوانی یک stored procedure با پارامترهای خروجی، مراحل زیر را باید طی کنید:

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

  • سپس، از این متغیرها در فراخوانی stored procedure استفاده کنید.

برای مثال، کد زیر stored procedure به نام uspFindProductByModel را اجرا می‌کند:

DECLARE @count INT;
 
EXEC uspFindProductByModel
    @model_year = 2018,
    @product_count = @count OUTPUT;
 
SELECT @count AS 'Number of products found';

تصویر زیر خروجی را نشان می‌دهد:

نتایج

در نتیجه، چگونگی استفاده از پارامتر خروجی برای ارسال داده از stored procedure به سمت برنامه فراخواننده را آموختید.

خلاصه

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

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

با MUG همراه باشید!

منبع: sqlservertutorial

 مطالب مرتبط

۱۳ دستور مهم SQL که هر برنامه‌نویس باید بداند
چگونه از حملات نفوذ کننده به SQL جلوگیری کنیم؟
 طبقه بندی زبان های برنامه نویسی
روش‌های هوشمندانه برای استفاده از "تلفیق" در SQL
 LINQ(زبان جستجوی یکپارچه)
برنامه نویسی شیءگرا چیست؟

از آخرین دوره های آموزشی و تخفیف ها مطلع شوید

با تکمیل فرم زیر ، از اخبار و اطلاعات به روز برنامه نویسی و تکنولوژی عقب نمانید

آخرین مطالب

آموزش جامع SQL Server (جلسه ۱۲)
آموزش جامع SQL Server (جلسه ۱۲)

دستور UPDATE در SQL Server برای تغییر داده‌های موجود در یک جدول، از دستور UPDATE به شکل زیر استفاده ...

آموزش جامع SQL Server (جلسه ۱۵)
آموزش جامع SQL Server (جلسه ۱۵)

دستور DROP TABLE در SQL Server گاهی، لازم است یک جدول که دیگر استفاده‌ای ندارد را حذف کنید. برای ...

آموزش جامع SQL Server (جلسه ۳۵: Window Functionها – بخش ۲)
آموزش جامع SQL Server (جلسه ۳۵: Window Functionها – بخش ۲)

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

آخرین دیدگاه ها

دیدگاه خود را درباره این پست بنویسید

فرم ارسال نظرات