آموزش جامعSQL Server(جلسه۲۲:Stored Procedureها بخش۲)
1398/04/01 16:09 , میلاد صاحب نظر

آموزش جامعSQL Server(جلسه۲۲:Stored Procedureها بخش۲)

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

۱- نصب و آشنایی با 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ها، تعریف پارامتر برای آن‌ها، استفاده از متغیرها و تعریف پارامترهای خروجی برای stored procedureها آشنا شدید. در این جلسه راجع به عبارات کنترل جریان (Control-of-flow) و کرسرها (curser) صحبت خواهیم کرد. با ما همراه باشید.

عبارت BEGIN END در SQL Server

عبارت BEGIN…END برای تعریف یک بلوک یا قطعه کد استفاده می‌شود. یک قطعه کد شامل مجموعه‌ای از کدهای SQL است که با هم اجرا می‌شوند. به قطعه کد همچنین batch نیز گفته می‌شود.

به عبارت دیگر، اگر کدها را به عنوان جمله فرض کنیم، آنگاه عبارت BEGIN…END به شما اجازه می‌دهد تا پاراگراف‌ها را تعریف کنید.

کد زیر syntax عبارت BEGIN…END را نشان می‌دهد:

BEGIN
    { sql_statement | statement_block}
END

در این syntax، یک مجموعه از کدهای SQL را بین کلیدواژه‌های BEGIN و END قرار گرفته‌اند، برای مثال:

BEGIN
    SELECT
        product_id,
        product_name
    FROM
        production.products
    WHERE
        list_price > 100000;
 
    IF @@ROWCOUNT=  0
        PRINT 'No product with price greater than 100000 found';
END

در این مثال:

  • ابتدا، قطعه کد ما با کلیدواژه BEGIN آغاز و با کلیدواژه END پایان یافته است.

  • سپس، درون قطعه، کد SELECT را داریم که محصولاتی را می‌یابد که قیمت‌های آن‌ها بیشتر از ۱۰۰۰۰۰ باشند. بعد، دستور IF را داریم که بررسی می‌کند آیا query محصولی بازمی‌گرداند یا خیر و اگر هیچ محصولی برای بازگردانده شدن وجود نداشته باشد، یک پیغام در خروجی چاپ می‌کند.

عبارت BEGIN…END یک قطعه منطقی از کدهای SQL را در کنار یکدیگر قرار می‌دهد. گاهی از BEGIN…END در ابتدا و انتهای یک stored procedure و تابع استفاده می‌شود. اما این کار لازم و ضروری نیست.

به هر حال، در صورتی که بخواهید چندین کد را یک جا به کار ببرید، عبارت BEGIN…END به کدهای IF ELSE، کدهای WHILE و غیره نیز نیاز دارد.  

BEGIN…END تودرتو

قطعه کد می‌تواند به صورت تودرتو نیز نوشته شود. به زبان ساده، یعنی اینکه می‌توانید یک عبارت BEGIN…END را درون یک عبارت BEGIN…END دیگر قرار دهید.

مثال زیر را در نظر بگیرید:

BEGIN
    DECLARE @name VARCHAR(MAX);
 
    SELECT TOP 1
        @name = product_name
    FROM
        production.products
    ORDER BY
        list_price DESC;
    
    IF @@ROWCOUNT <> 0
    BEGIN
        PRINT 'The most expensive product is ' + @name
    END
    ELSE
    BEGIN
        PRINT 'No product found';
    END;
END

در این مثال، ما از عبارت BEGIN…END برای پوشش کل قطعه کد استفاده کردیم. درون این قطعه، همچنین از BEGIN…END برای پوشش عبارت IF…ELSE نیز استفاده کردیم.

به این شکل، راجع به عبارت BEGIN…END و چگونگی پوشش دادن عبارت‌های Transact-SQL درون قطعات کد آموختید.

عبارت IF ELSE در SQL Server

عبارت IF…ELSE یک عبارت کنترل جریان است که بر اساس یک شرط خاص، به شما امکان اجرا یا گذر از یک قطعه کد را می‌دهد.

عبارت IF

کد زیر syntax عبارت IF را نشان می‌دهد:

IF boolean_expression   
BEGIN
    { statement_block }
END

در این syntax، اگر مقدار عبارت Boolean_expression برابر با TRUE شود، آنگاه statement_block در قطعه BEGIN…END اجرا خواهد شد. در غیر این صورت، سیستم از statement_block می‌گذرد و کنترل برنامه به دست عبارت موجود پس از کلیدواژه END می‌افتد.

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

مثال زیر ابتدا میزان فروش را از جدول sales.order_items در پایگاه داده نمونه Bikestores می‌گیرد. سپس، یک پیام چاپ می‌کند که آیا میزان فروش بیشتر از ۱ میلیون هست یا خیر.

BEGIN
    DECLARE @sales INT;
 
    SELECT 
        @sales = SUM(list_price * quantity)
    FROM
        sales.order_items i
        INNER JOIN sales.orders o ON o.order_id = i.order_id
    WHERE
        YEAR(order_date) = 2018;
 
    SELECT @sales;
 
    IF @sales > 1000000
    BEGIN
        PRINT 'Great! The sales amount in 2018 is greater than 1,000,000';
    END
END

خروجی قطعه کد به این شکل است:

Great! The sales amount in 2018 is greater than 1,000,000

عبارت IF ELSE

وقتی شرط موجود در عبارت IF برابر با FALSE شود و بخواهید یک قطعه کد دیگر اجرا کنید، می‌توانید از عبارت ELSE استفاده کنید.

کد زیر عبارت IF ELSE را نشان می‌دهد:

IF Boolean_expression
BEGIN
    -- باشد TRUE برابر با Boolean قطعه کد زمانی اجرا می‌شود که مقدار عبارت 
END
ELSE
BEGIN
    -- باشد FALSE برابر با Boolean این قطعه کد زمانی اجرا می‌شود که مقدار عبارت
END

هر عبارت IF دارای یک شرط است. اگر مقدار شرط برابر با TRUE باشد، آنگاه قطعه کد موجود در عبارت IF اجرا می‌شود. اگر شرط FALSE باشد، آنگاه قطعه کد موجود در عبارت ELSE اجرا می‌شود.

مثال زیر را ببینید:

BEGIN
    DECLARE @sales INT;
 
    SELECT 
        @sales = SUM(list_price * quantity)
    FROM
        sales.order_items i
        INNER JOIN sales.orders o ON o.order_id = i.order_id
    WHERE
        YEAR(order_date) = 2017;
 
    SELECT @sales;
 
    IF @sales > 10000000
    BEGIN
        PRINT 'Great! The sales amount in 2018 is greater than 10,000,000';
    END
    ELSE
    BEGIN
        PRINT 'Sales amount in 2017 did not reach 10,000,000';
    END
END

در این مثال، میزان فروش در سال ۲۰۱۷ به دست می‌آید. در عبارت IF، بررسی می‌شود که آیا میزان فروش بیشتر از ۱۰ میلیون هست یا نه. چون میزان فروش کمتر از آن مقدار است، قطعه کد موجود در عبارت ELSE اجرا می‌شود.

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

Sales amount did not reach 10,000,000

IF…ELSE تودرتو

می‌توانید یک عبارت IF…ELSE را درون یک عبارت IF…ELSE دیگر به صورت تودرتو به کار ببرید. مثال زیر را مشاهده کنید:

BEGIN
    DECLARE @x INT = 10,
            @y INT = 20;
 
    IF (@x > 0)
    BEGIN
        IF (@x < @y)
            PRINT 'x > 0 and x < y';
        ELSE
            PRINT 'x > 0 and x >= y';
    END 
END

البته بهتر آن است که یک عبارت IF را درون یک عبارت دیگر به کار نبرید، چون باعث می‌شود خواندن کد مشکل و رسیدگی به آن سخت شود.

به این صورت، چگونگی استفاده از عبارت IF…ELSE برای کنترل جریان اجرای کد را نیز آموختید.

عبارت WHILE در SQL Server

عبارت WHILE یک عبارت کنترل جریان است که به شما امکان می‌دهد تا زمانی که مقدار یک شرط خاص برابر با TRUE باشد، یک قطعه کد را بارها اجرا کنید.

کد زیر syntax عبارت WHILE را نشان می‌دهد:

WHILE Boolean_expression   
     { sql_statement | statement_block}  

در این syntax:

ابتدا، Boolean_expression یک عبارت است که مقدار آن می‌تواند TRUE یا FALSE باشد.

سپس، sql_statement | statement_block یک یا مجموعه‌ای از عبارات Transact-SQL است. قطعه کد با استفاده از عبارت BEGIN…END تعریف شده است.

اگر مقدار Boolean _expression در هنگام ورود به حلقه برابر با FALSE شود، هیچ عبارتی داخل حلقه WHILE اجرا نخواهد شد.

درون حلقه WHILE،، باید برخی مقادیر را تغییر دهید تا کاری کند که Boolean_expression در برخی مواقع مقدار FALSE را بازگرداند. در غیر این صورت، با یک حلقه بی‌پایان مواجه خواهید شد.

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

برای خروج سریع از تکرار فعلی حلقه، می‌توانید از عبارت BREAK استفاده کنید. برای گذر از تکرار فعلی حلقه و آغاز یک حلقه جدید می‌توانید از دستور CONTINUE استفاده کنید.

مثال WHILE در SQL Server

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

مثل زیر چگونگی استفاده از عبارت WHILE برای چاپ اعداد ۱ تا ۵ را نشان می‌دهد:

DECLARE @counter INT = 1;
 
WHILE @counter <= 5
BEGIN
    PRINT @counter;
    SET @counter = @counter + 1;
END

در این مثال:

ابتدا، متغیر counter@ را تعریف و مقدار آن را برابر با 1 قرار می‌دهیم.

سپس، درون شرط عبارت WHILE بررسی می‌کنیم که آیا counter@ کمتر یا برابر با 5 هست یا خیر. اگر این‌طور نبود، counter@ را چاپ کرده و مقدار آن را یکی‌یکی افزایش می‌دهیم.

بعد از ۵ بار تکرار، counter@ برابر با 6 می‌شد که باعث می‌شود شرط عبارت WHILE برابر با FALSE شود و در نتیجه حلقه متوقف خواهد شد.

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

1
2
3
4
5

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

دستور BREAK در SQL Server

در قسمت قبلی، چگونگی استفاده از عبارت WHILE برای ایجاد یک حلقه را آموختید. برای خروجی از تکرار فعلی یک حلقه، از دستور BREAK استفاده می‌کنیم.

کد زیر syntax معمولی دستور BREAK را نشان می‌دهد:

WHILE Boolean_expression
BEGIN
    -- کدها
   IF condition
        BREAK;
    -- کدهای دیگر    
END

در این syntax، وقتی condition موجود در عبارت IF صدق کند، دستور BREAK بلافاصله سیستم را از حلقه WHILE خارج می‌کند. همه کدهای بین کلیدواژه‌های BREAK و END نادیده گرفته می‌شوند.

فرض کنید یک حلقه WHILE داریم که درون یک حلقه WHILE دیگر به صورت تودرتو نوشته شده است:

WHILE Boolean_expression1
BEGIN
    -- statement
    WHILE Boolean_expression2
    BEGIN
        IF condition
            BREAK;
    END
END

در این مورد، دستور BREAK فقط از داخلی‌ترین حلقه موجود در عبارت WHILE خارج می‌شود.

توجه کنید که دستور BREAK می‌تواند فقط درون حلقه WHILE مورد استفاده قرار گیرد. عبارت IF گاهی همراه با دستور BREAK قرار می‌گیرد،‌ اما لزوماً نیازی به آن نیست.

مثال دستور BREAK در SQL Server

مثال زیر چگونگی استفاده از دستور BREAK را نشان می‌دهد:

DECLARE @counter INT = 0;
 
WHILE @counter <= 5
BEGIN
    SET @counter = @counter + 1;
    IF @counter = 4
        BREAK;
    PRINT @counter;
END

در این مثال:

ابتدا، یک متغیر به نام counter@ تعریف کردیم و مقدار آن را برابر با 0 گذاشتیم.

سپس، از عبارت WHILE برای افزایش یک به یک مقدار counter@ در هر تکرار استفاده کردیم و مقدار counter@ را تا زمانی که مقدار آن کمتر یا برابر با 5 باشد، در خروجی چاپ می‌کنیم.

درون حلقه، همچنین بررسی کردیم که آیا مقدار counter@ برابر چهار است یا خیر، سپس از حلقه خارج شدیم. در چهارمین تکرار، مقدار counter به ۴ می‌رسد، سپس حلقه نابود می‌شود. همچنین، عبارت PRINT پس از دستور BREAK نیز نادیده گرفته شد.

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

دستور CONTINUE در SQL Server

دستور CONTINUE تکرار فعلی حلقه را متوقف و یک حلقه جدید را آغاز می‌کند. کد زیر syntax دستور CONTINUE را نشان می‌دهد:

WHILE Boolean_expression
BEGIN
    -- کدی که اجرا می‌شود
    IF condition
        CONTINUE;
    -- کدی که اگر شرط برقرار شود، از آن صرف نظر خواهد شد
END

در این syntax، تکرار فعلی حلقه به محض اینکه مقدار شرط برابر با TRUE شود، متوقف خواهد شد. تکرار بعدی حلقه تا زمانی که مقدار Boolean_expression برابر با FALSE شود، ادامه می‌یابد.

همانند دستور BREAK، دستور CONTINUE نیز گاهی همراه با یک عبارت IF…ELSE به کار می‌رود. توجه کنید که این امر اجباری نیست.

مثال CONTINUE در SQL Server

مثال زیر چگونگی عملکرد دستور CONTINUE را نشان می‌دهد:

DECLARE @counter INT = 0;
 
WHILE @counter < 5
BEGIN
    SET @counter = @counter + 1;
    IF @counter = 3
        CONTINUE; 
    PRINT @counter;
END

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

1
2
4
5

در این مثال:

ابتدا، یک متغیر به نام counter@ تعریف کردیم و مقدار آن را برابر با 0 قرار دادیم.

سپس، حلقه WHILE شروع می‌شود. درون حلقه WHILE، مقدار counter را در هر تکرار یکی افزایش دادیم. اگر counter@ برابر با ۳ شود، با استفاده از دستور CONTINUE از چاپ آن مقدار صرف نظر می‌کنیم. به همین دلیل در خروجی، عدد سه را مشاهده نمی‌کنید.

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

CURSOR در SQL Server

SQL بر اساس مجموعه‌ها کار می‌کند، برای مثال دستور SELECT مجموعه‌ای از ردیف‌ها را بازمی‌گرداند که یک مجموعه نتیجه را فراخوانی می‌کنند.

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

Cursor در پایگاه داده چیست؟

Cursor در پایگاه داده یک شیء است که امکان گذر کردن از ردیف‌های یک مجموعه نتیجه را فراهم می‌کند. این امر به شما اجازه می‌دهد تا یک ردیف بازگردانده شده توسط یک query را بازگرداند.

چرخه عمر cursor در SQL Server

این‌ها مراحل استفاده از یک cursor هستند:

مراحل استفاده از یک cursor

ابتدا، یک cursor تعریف می‌کنیم.

DECLARE cursor_name CURSOR
    FOR select_statement;

برای تعریف یک cursor، نام آن را پس از کلیدواژه DECLARE همراه با نوع داده CURSOR مشخص کرده و یک دستور SELECT فراهم می‌کنیم که مجموعه نتیجه را برای cursor تعریف کند.

سپس، cursor را با اجرای دستور SELECT باز می‌کنیم:

OPEN cursor_name;

سپس، یک ردیف از cursor درون یک یا چند متغیر به دست می‌آوریم:

FETCH NEXT FROM cursor INTO variable_list;

SQL Server تابع FETCHSTATUS@@ را فراهم کرده است که وضعیت عبارت FETCH از آخرین cursor اجرا شده را بازمی‌گرداند. اگر تابع 0 را بازگرداند، یعنی بارت FETCH با موفقیت اجرا شده است.

می‌توانید از عبارت WHILE برای به دست آوردن تمام ردیف‌ها از cursor استفاده کنید، که در کد زیر مشاهده می‌کنید:

WHILE @@FETCH_STATUS = 0  
    BEGIN
        FETCH NEXT FROM cursor_name;  
    END;

پس از آن، cursor را ببندید:

CLOSE cursor_name;

در آخر، cursor را deallocate کنید:

DEALLOCATE cursor_name;

مثال cursor در SQL Server

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

DECLARE 
    @product_name VARCHAR(MAX), 
    @list_price   DECIMAL;
 
DECLARE cursor_product CURSOR
FOR SELECT 
        product_name, 
        list_price
    FROM 
        production.products;

سپس، cursor را باز کنید:

OPEN cursor_product;

بعد، هر ردیف از cursor را بگیرید و نام و قیمت محصول را چاپ کنید:

FETCH NEXT FROM cursor_product INTO 
    @product_name, 
    @list_price;
 
WHILE @@FETCH_STATUS = 0
    BEGIN
        PRINT @product_name + CAST(@list_price AS varchar);
        FETCH NEXT FROM cursor_product INTO 
            @product_name, 
            @list_price;
    END;

پس از آن، cursor را ببندید.

CLOSE cursor_product;

در آخر، cursor را deallocate کنید تا آزاد شود.

DEALLOCATE cursor_product;

قطعه کد زیر همه چیز را کنار هم قرار می‌دهد:

DECLARE 
    @product_name VARCHAR(MAX), 
    @list_price   DECIMAL;
 
DECLARE cursor_product CURSOR
FOR SELECT 
        product_name, 
        list_price
    FROM 
        production.products;
 
OPEN cursor_product;
 
FETCH NEXT FROM cursor_product INTO 
    @product_name, 
    @list_price;
 
WHILE @@FETCH_STATUS = 0
    BEGIN
        PRINT @product_name + CAST(@list_price AS varchar);
        FETCH NEXT FROM cursor_product INTO 
            @product_name, 
            @list_price;
    END;
 
CLOSE cursor_product;
 
DEALLOCATE cursor_product;

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

نتایج

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

خلاصه

در این جلسه به بررسی عبارات کنترل جریان و cursor در SQL Server پرداختیم. کار با دستورات نام آشنایی همچون BEGIN…END، عبارت IF…ELSE، عبارت WHILE، دستور Break، دستور CONTINUE و cursor‌ را آموختید.

در جلسه بعدی مبحث 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 در جلسه قبلی بررسی شد. این مبحث که ...

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

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

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