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

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

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

۱- نصب و آشنایی با 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 بخش۱

 

در این جلسه با پرداختن به روش‌های کنترل Exceptionها و SQL پویا، مبحث  Stored Procedureها را تکمیل خواهیم کرد. با ما همراه باشید!

ساختار TRY CATCH در SQL Server

ساختار TRY CATCH به شما اجازه می‌دهد تا خیلی زیبا و شکیل، exceptionها را در SQL Server کنترل کنید. برای استفاده از ساختار TRY CATCH، ابتدا گروهی از عبارات Transact-SQL را که ممکن است باعث به بروز exception شوند را در یک قطعه دستور BEGIN TRY…END TRY به شکل زیر قرار می‌دهیم:

BEGIN TRY  
   -- شوند exception کدهایی که ممکن است باعث بروز 
END TRY  

سپس بلافاصله پس از قطعه TRY، از یک قطعه BEGIN CATCH…END CATCH استفاده می‌کنیم:

BEGIN CATCH  
   -- را کنترل می‌کنند exception کدهایی که 
END CATCH  

کد زیر یک ساختار کامل TRY CATCH را نشان می‌دهد:

BEGIN TRY  
   -- شوند exception کدهایی که ممکن است باعث بروز 
END TRY 
BEGIN CATCH  
   -- را کنترل می‌کنند exception کدهایی که 
END CATCH  

اگر کدهای بین قطعه TRY بدون هیچ اروری کامل اجرا شدند، آنگاه کدهای بین قطعه CATCH اجرا نخواهند شد. به هر حال، اگر کدهای داخل قطعه TRY باعث بروز Exception شوند، آنگاه سیستم کدهای داخل قطعه CATCH را اجرا خواهد کرد.

توابع قطعه CATCH

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

  • ()ERROR_LINE شماره خطی که در آن Exception‌رخ داده است را بازمی‌گرداند.

  • ()ERROR_MESSAGE متن کامل پیغام ارور پیش آمده را بازمی‌گرداند.

  • ()ERROR_PROCEDURE نام stored procedure یا trigger که در آن ارور اتفاق افتاده است را بازمی‌گرداند.

  • ()ERROR_NUMBER شماره اروری که پیش آمده است را بازمی‌گرداند.

  • ()ERROR_SEVERITY میزان و سطح سختی و شدت ارور پیش آمده را بازمی‌گرداند.

  • ()ERROR_STATE: شماره وضعیت اروری که اتفاق افتاده است را بازمی‌گرداند.

توجه کنید که فقط در قطعه CATCH از این توابع استفاده می‌کنیم. اگر در خارج از قطعه CATCH از آن‌ها استفاده کنید، همه این توابع مقدار NULL را بازمی‌گردانند.

ساختارهای TRY CATCH تودرتو

می‌توانید ساختار TRY CATCH را درون یک ساختار TRY CATCH دیگر به صورت تودرتو به کار ببرید. به هر حال، هم قطعه TRY و هم قطعه CATCH می‌توانند شامل یک ساختار TRY CATCH تودرتو باشند، برای مثال:

BEGIN TRY
    --- شوند exception کدهایی که ممکن است باعث بروز 
END TRY
BEGIN CATCH
    -- را کنترل کنند exception کدهایی که می‌توانند 
    BEGIN TRY
        --- تو در توTRY قطعه
    END TRY
    BEGIN CATCH
        --- تو در توCATCH قطعه
    END CATCH
END CATCH

مثال‌های TRY CATCH در SQL Server

ابتدا، یک stored procedure به نام usp_divide ایجاد کنید که دو عدد را بر هم تقسیم کند:

CREATE PROC usp_divide(
    @a decimal,
    @b decimal,
    @c decimal output
) AS
BEGIN
    BEGIN TRY
        SET @c = @a / @b;
    END TRY
    BEGIN CATCH
        SELECT  
            ERROR_NUMBER() AS ErrorNumber  
            ,ERROR_SEVERITY() AS ErrorSeverity  
            ,ERROR_STATE() AS ErrorState  
            ,ERROR_PROCEDURE() AS ErrorProcedure  
            ,ERROR_LINE() AS ErrorLine  
            ,ERROR_MESSAGE() AS ErrorMessage;  
    END CATCH
END;
GO

در این stored procedure، ما فرمول را درون قطعه TRY قرار دادیم و توابع ERROR_* مخصوص قطعه CATCH را درون قطعه CATCH فراخوانی کردیم.

سپس، usp_divide را فراخوانی کردیم تا ۱۰ را بر ۲ تقسیم کند:

DECLARE @r decimal;
EXEC usp_divide 10, 2, @r output;
PRINT @r;

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

5

چون هیچ exception در قطعه TRY رخ نداد، پس stored procedure در قطعه TRY کامل شد.

بعد، سعی کنید با فراخوانی usp_divide عدد ۲۰ را بر صفر تقسیم کنید:

DECLARE @r2 decimal;
EXEC usp_divide 10, 0, @r2 output;
PRINT @r2;

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

نتایج

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

ساختار TRY CATCH همراه با تراکنش یا transactionها در SQL Server

درون یک قطعه CATCH، می‌توانید وضعیت تراکنش یا transactionها را با استفاده از تابع ()XACT_STATE تست کنید.

  • اگر تابع ()XACT_STATE مقدار ۱- را بازگرداند، یعنی اینکه یک تراکنش غیر قابل تحمل (uncommittable) در انتظار اجرا است، پس باید یک دستور ROLLBACK TRANSACTION اجرا کنید.

  • در صورتی که تابع ()XACT_STATE مقدار ۱ را بازگرداند، یعنی اینکه یک تراکنش قابل تحمل (committable) در انتظار اجرا است. می‌توانید یک دستور COMMIT TRANSACTION در این حالت اجرا کنید.

  • اگر تابع ()XACT_STATE مقدار 0 را بازگرداند، یعنی اینکه هیچ تراکنشی در انتظار اجرا نیست، در نتیجه لازم نیست کار خاصی انجام دهید.

بهترین کار این است که وضعیت تراکنش خود را قبل از اجرای دستور COMMIT TRANSACATION یا ROLLBACK TRANSACTION در یک قطعه CATCH اجرا کنید تا از سالم و صحیح اجرا شدن آن اطمینان حاصل کنید.

مثال استفاده از TRY CATCH با تراکنش‌ها

ابتدا، دو جدول جدید به نام‌های sales.persons و sales.deals برای مثال ایجاد کنید:

CREATE TABLE sales.persons
(
    person_id  INT
    PRIMARY KEY IDENTITY, 
    first_name NVARCHAR(100) NOT NULL, 
    last_name  NVARCHAR(100) NOT NULL
);
 
CREATE TABLE sales.deals
(
    deal_id   INT
    PRIMARY KEY IDENTITY, 
    person_id INT NOT NULL, 
    deal_note NVARCHAR(100), 
    FOREIGN KEY(person_id) REFERENCES sales.persons(
    person_id)
);
 
insert into 
    sales.persons(first_name, last_name)
values
    ('John','Doe'),
    ('Jane','Doe');
 
insert into 
    sales.deals(person_id, deal_note)
values
    (1,'Deal for John Doe');

بعد، یک stored procedure به نام usp_report_error ایجاد کنید که در یک قطعه CATCH استفاده می‌شود تا جزئیات و اطلاعات دقیق یک ارور را گزارش دهد:

CREATE PROC usp_report_error
AS
    SELECT   
        ERROR_NUMBER() AS ErrorNumber  
        ,ERROR_SEVERITY() AS ErrorSeverity  
        ,ERROR_STATE() AS ErrorState  
        ,ERROR_LINE () AS ErrorLine  
        ,ERROR_PROCEDURE() AS ErrorProcedure  
        ,ERROR_MESSAGE() AS ErrorMessage;  
GO

سپس، یک stored procedure جدید توسعه می‌دهیم که یک ردیف را از جدول sales.persons حذف می‌کند:

CREATE PROC usp_delete_person(
    @person_id INT
) AS
BEGIN
    BEGIN TRY
        BEGIN TRANSACTION;
        -- حذف شخص
        DELETE FROM sales.persons 
        WHERE person_id = @person_id;
        -- اگر حذف با موفقیت انجام شود، تراکنش تحمل می‌شود
        COMMIT TRANSACTION;  
    END TRY
    BEGIN CATCH
        -- exception گزارش دادن 
        EXEC usp_report_error;
        
        --   تست کن ببین آیا تراکنش غیر قابل تحمل است یا خیر
        IF (XACT_STATE()) = -1  
        BEGIN  
            PRINT  N'The transaction is in an uncommittable state.' +  
                    'Rolling back transaction.'  
            ROLLBACK TRANSACTION;  
        END;  
        
        --  تست کن ببین تراکنش قابل تحمل است یا خیر
        IF (XACT_STATE()) = 1  
        BEGIN  
            PRINT N'The transaction is committable.' +  
                'Committing transaction.'  
            COMMIT TRANSACTION;     
        END;  
    END CATCH
END;
GO

در این stored procedure، از تابع ()XACT_STATE برای بررسی وضعیت تراکنش قبل از اجرای دستور COMMIT TRANSACTION یا ROLLBACK TRANSACTION درون قطعه CATCH استفاده می‌کنیم.

پس از آن، usp_delete_person را فراخوانی می‌کنیم تا شخص با id شماره 2 را حذف کند:

EXEC usp_delete_person 2;

هیچ exception به وجود نیامد.

در آخر، usp_delete_person را فراخوانی می‌کنیم تا شخص با id شماره 1 را حذف کند:

EXEC usp_delete_person 1;

حالا ارور زیر پیش می‌آید:

ارور

به این شکل، چگونگی استفاده از ساختار TRY CATCH برای کنترل exceptionها در stored procedureها را آموختید.

عبارت RAISERROR در SQL Server

در این قسمت چگونگی استفاده از بارت RAISERROR برای ایجاد پیام‌های ارور ایجاد شده توسط کاربر استفاده کنید.

نکته مهم: اگر یک اپلیکیشن جدید توسعه می‌دهید، باید در عوض از عبارت THROW استفاده کنید.

عبارت RAISERROR به شما اجازه می‌دهد پیام‌های ارور مخصوص به خودتان را ایجاد کنید و می‌توانید این ارور‌ها را با همان فرمتی که ارورهای سیستم یا پیام‌های هشدار توسط موتور پایگاه داده SQL Server نمایش داده می‌شوند، نمایش دهید.

علاوه بر این، عبارت RAISERROR به شما اجازه می‌دهد تا یک پیغام id، میزان و شدت سختی و وضعیت خاصی برای پیام‌های ارور خود ایجاد کنید.

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

RAISERROR ( { message_id | message_text | @local_variable }  
    { ,severity ,state }  
    [ ,argument [ ,...n ] ] )  
    [ WITH option [ ,...n ] ];

اجازه دهید syntax عبارت RAISERROR را بررسی کنیم تا بهتر متوجه شوید.

Essage_id

Message_id یک شماره پیغام ارور تعریف شده توسط کاربر است که در view کاتالوگ sys.messages ذخیره می‌شود.

برای اضافه کردن یک شماره پیغام ارور تعریف شده توسط کاربر جدید، می‌توانید از یک stored procedure به نام sp_addmessage استفاده کنید. یک شماره پیغام ارور تعریف شده توسط کاربر باید بزرگ‌تر از 50000 باشد.

چون به صورت پیش‌فرض، عبارت RAISERROR از message_id با مقدار 50000 برای انگیختن یک ارور استفاده می‌کند.

کد زیر یک پیغام ارور سفارشی برای view به نام sys.messages اضافه می‌کند:

EXEC sp_addmessage 
    @msgnum = 50005, 
    @severity = 1, 
    @msgtext = 'A custom error message';

برای تأیید درستی عبارت insert، می‌توانید از query زیر استفاده کنید:

SELECT    
    *
FROM    
    sys.messages
WHERE 
    message_id = 50005;

برای استفاده از این message_id، باید عبارت RAISERROR را به شکل زیر به کار ببرید:

RAISERROR ( 50005,1,1)

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

A custom error message
Msg 50005, Level 1, State 1

برای حذف یک پیغام از sys.messages، می‌توانید از stored procedure به نام sp_dropmessage استفاده کنید. برای مثال، عبارت زیر شماره پیغام 50005 را حذف می‌کند:

EXEC sp_dropmessage 
    @msgnum = 50005;  
Message_text

Message_text یک پیغام تعریف شده توسط کاربر با فرمت بندی همانند تابع printf در کتابخانه استاندارد C است. Message_text می‌تواند تا 2047 کاراکتر را در خود جای دهید، ۳ کاراکتر آخر برای سه نقطه‌ها (…) نگه داشته شده است.

اگر message_text شامل ۲۰۴۸ یا تعداد بیشتری کاراکتر باشد، به صورت خودکار کوتاه می‌شود و در انتهای آن یک سه نقطه قرار می‌گیرد.

وقتی message_text را به کار می‌برید، عبارت RAISERROR از message_id با مقدار 50000 برای انگیختن پیغام ارور استفاده می‌کند.

مثال زیر از عبارت RAISERROR برای انگیختن یک ارور با یک پیام متنی استفاده می‌کند:

RAISERROR ( 'Whoops, an error occurred.',1,1)

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

Whoops, an error occurred.
Msg 50000, Level 1, State 1
Severity

سطح سختی یا Severity یک عدد صحیح بین 0 تا 25 است و هر سطح نشان دهنده میزان جدی بودن آن ارور است.

0–10 Informational messages
11–18 Errors
19–25 Fatal errors
State

State یک عدد صحیح از 0 تا 255 است. اگر یک ارور تعریف شده توسط کاربر یکسان را در چندین موقعیت انگیخته کنید، می‌توانید از یک شماره وضعیت منحصر به فرد یا یکتا برای هر موقعیت استفاده کنید تا یافتن بخشی از کد که باعث بروز ارورها شده است خیلی آسان‌تر شود.

برای اکثر موارد، می‌توانید از 1 استفاده کنید.

آپشن WITH

این آپشن می‌تواند با NOWAIT، LOG یا SETERROR به کار رود:

  • WITH LOG ارور را در لیست گزارش ارور‌ها و لیست گزارش اپلیکیشن قرار می‌دهد تا به عنوان یک الگو و سرمشق در موتور پایگاه داده SQL Server باقی بماند.

  • WITH NOWAIT پیغام ارور را سریعاً به مشتری یا کلاینت ارسال می‌کند.

  • WITH SETERROR مقادیر ERROR_NUMBER و ERROR@@ را به message_id یا 50000 تغییر می‌دهد و مهم نیست سطح سختی چقدر باشد.

مثال‌های RAISERROR در SQL Server

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

الف) مثال استفاده از RAISERROR همراه با قطعه TRY CATCH

در این مثال، از RAISERROR در داخل یک قطعه TRY استفاده می‌کنیم تا باعث بروز exception شود و سیستم به قطعه CATCH مربوطه بپرد. درون قطعه CATCH، از RAISERROR استفاده می‌کنیم تا اطلاعات ارور پیش آمده در قطعه CATCH بازگردانده شود.

DECLARE 
    @ErrorMessage  NVARCHAR(4000), 
    @ErrorSeverity INT, 
    @ErrorState    INT;
 
BEGIN TRY
    RAISERROR('Error occurred in the TRY block.', 17, 1);
END TRY
BEGIN CATCH
    SELECT 
        @ErrorMessage = ERROR_MESSAGE(), 
        @ErrorSeverity = ERROR_SEVERITY(), 
        @ErrorState = ERROR_STATE();
 
    -- را بازمی‌گرداند CATCH ارور داخل قطعه
    RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState);
END CATCH;

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

Msg 50000, Level 17, State 1, Line 16
Error occurred in the TRY block.
ب) مثال استفاده از عبارت RAISERROR با یک پیام متنی پویا

مثال زیر چگونگی استفاده از یک متغیر محلی برای ایجاد یک پیام متنی برای عبارت RAISERROR را نشان می‌دهد:

DECLARE @MessageText NVARCHAR(100);
SET @MessageText = N'Cannot delete the sales order %s';
 
RAISERROR(
    @MessageText, -- متن پیغان
    16, -- شدت
    1, -- وضعیت
    N'2001' --اولین آرگومان برای متن پیغام 
);

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

Msg 50000, Level 16, State 1, Line 5
Cannot delete the sales order 2001

چه موقع باید از عبارت RAISERROR استفاده کنیم؟

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

  • مشکل یابی کد Transact-SQL.
  • بازگرداندن پیغام‌هایی که حاوی متن متغیر هستند.
  • امتحان مقادیر داده‌ها.
  • برای پریدن سیستم از قطعه TRY به قطعه CATCH مربوطه.
  • بازگرداندن اطلاعات ارور از قطعه CATCH به سمت فراخواننده‌ها، مهم نیست فراخوانی توسط batch یا اپلیکیشن باشد.

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

عبارت THROW در SQL Server

عبارت THROW یک exception به وجود می‌آورد و اجرا را به یک قطعه CATCH از یک ساختار TRY CATCH انتقال می‌دهد.

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

THROW [ error_number ,  
        message ,  
        state ];

در این syntax:

Error_number

Error_number یک رشته از نوع (2048)NVARCHAR است که exception را توضیح می‌دهد.

State

State یک TINYINT با مقدار بین 0 و 255 است. State وضعیت مرتبط با پیام را نشان می‌دهد.

اگر هیچ پارامتر خاصی برای عبارت THROW مشخص نکنید، آنگاه باید عبارت THROW را درون قطعه CATCH قرار دهید:

BEGIN TRY
    -- کدهایی که ممکن است باعث بروز ارور شوند
END TRY
BEGIN CATCH
    -- کد برای کنترل ارور ها
    THROW;   
END CATCH

در این حالت، عبارت THROW یک ارور می‌دهد که توسط قطعه CATCH گرفته می‌شود.

توجه کنید که عبارت موجود قبل از عبارت THROW باید با یک سمی کالن (;) پایان یابد.

THROW در برابر RAISERROR

جدول زیر تفاوت بین عبارت THROW و عبارت RAISERROR را نشان می‌دهد:

RAISERROR

THROW

Message_id که به RAISERROR ارسال می‌کنید باید در view به نام sys.messages تعریف شده باشد.

پارامتر error_number لازم نیست در View به نام sys.messages تعریف شود.

پارامتر message می‌تواند شامل استایل‌های فرمت بندی printf (مانند %s و %d) باشد.

پارامتر message فرمت بندی استایل printf را نمی‌پذیرد. از تابع ()FORMATMESSAGE برای جایگزینی پارامترها استفاده می‌کند.

پارامتر severity شدت و سختی Exception را نشان می‌دهد.

شدت و سختی Exception همیشه روی ۱۶ قرار دارد.

مثال‌های عبارت THROW در SQL Server

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

الف) استفاده از عبارت THROW برای انگیختن یک exception

مثال زیر از عبارت THROW استفاده می‌کند تا یک exception را برانگیزد:

THROW 50005, N'An error occurred', 1;

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

Msg 50005, Level 16, State 1, Line 1
An error occurred
ب) استفاده از عبارت THROW برای انگیزش مجدد Exception

ابتدا، یک جدول جدید به نام t1 برای مثال ایجاد می‌کنیم:

CREATE TABLE t1(
    id int primary key
);
GO

سپس، از عبارت THROW بدون هیچ آرگومانی در قطعه CATCH استفاده می‌کنیم تا ارور گرفته شده مجدداً انگیخته شد:

BEGIN TRY
    INSERT INTO t1(id) VALUES(1);
    --  بروز ارور
    INSERT INTO t1(id) VALUES(1);
END TRY
BEGIN CATCH
    PRINT('Raise the caught error again');
    THROW;
END CATCH

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

(1 row affected)
 
(0 rows affected)
Raise the caught error again
Msg 2627, Level 14, State 1, Line 10
Violation of PRIMARY KEY constraint 'PK__t1__3213E83F906A55AA'. Cannot insert duplicate key in object 'dbo.t1'. The duplicate key value is (1).

در این مثال، اولین دستور insert با موفقیت اجرا شد. به هر حال، دومین دستور insert به خاطر ویژگی کلید اصلی (primary key) موفق نمی‌شود. در نتیجه، اروری که توسط قطعه CATCH گرفته شده است، دوباره با عبارت THROW برانگیخته می‌شود.

پ) استفاده از عبارت THROW برای برانگیختن مجدد یک exception

بر خلاف عبارت RAISERROR، عبارت THROW به شما اجازه نمی‌دهد تا پارامترها را در پیغام متنی جایگزین کنید. از این رو، برای جبران این نقصان، می‌توانید از تابع ()FORMATMESSAGE استفاده کنید.

کد زیر یک پیغام سفارشی به view کاتالوگ sys.messagges اضافه می‌کند:

EXEC sys.sp_addmessage 
    @msgnum = 50010, 
    @severity = 16, 
    @msgtext =
    N'The order number %s cannot be deleted because it does not exist.', 
    @lang = 'us_english';   
GO

این عبارت از message_id با مقدار 50010 استفاده می‌کند و placeholder به نام %s را با یک id سفارشی مثل ‘1001’ جایگزین می‌کند:

DECLARE @MessageText NVARCHAR(2048);
SET @MessageText =  FORMATMESSAGE(50010, N'1001');   
 
THROW 50010, @MessageText, 1; 

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

Msg 50010, Level 16, State 1, Line 8
The order number 1001 cannot be deleted because it does not exist.

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

SQL پویا (Dynamic SQL) در SQL Server

SQL پویا یک تکنیک برنامه‌نویسی است که به شما اجازه می‌دهد دستورات و عبارات SQL را به صورت پویا در رانتایم بسازید.

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

برای مثال، می‌توانید با استفاده از SQL پویا یک stored procedure ایجاد کنید که وظیفه آن گرفتن داده‌ها از جدولی باشد که نام آن جدول تا زمان رانتایم مشخص نشود.

ایجاد یک SQL پویا بسیار ساده است، فقط کافی است یک رشته به شکل زیر بنویسید:

'SELECT * FROM production.products';

برای اجرای عبارت SQL پویا، یک stored procedure به نام sp_executesql را فراخوانی می‌کنیم که در کد زیر مشاهده می‌کنید:

EXEC sp_executesql N'SELECT * FROM production.products';

چون sp_exectesql کدهای SQL‌پویا را به عنوان یک رشته Unicode می‌پذیرد، پس باید قبل از آن‌ها از یک حرف N استفاده کنید.

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

مثال استفاده از SQL پویا برای به دست آوردن اطلاعات از هر جدولی

ابتدا، دو متغیر تعریف می‌کنیم، table@ برای نگه داشتن نام جدولی که می‌خواهید از آن داده به دست آورید و sql@ برای نگه داشتن SQL پویا.

DECLARE 
    @table NVARCHAR(128),
    @sql NVARCHAR(MAX);

دوم، به متغیر table@ مقدار production.products می‌دهیم.

SET @table = N'production.products';

سوم، با الحاق دستور SELECT به پارامتر نام جدول، SQL پویا را ایجاد می‌کنیم:

SET @sql = N'SELECT * FROM ' + @table;

چهارم، stored procedure به نام sp_executesql را با ارسال پارامتر sql@، فراخوانی می‌کنیم:

EXEC sp_executesql @sql;

همه کدها را کنار هم قرار می‌دهیم:

DECLARE 
    @table NVARCHAR(128),
    @sql NVARCHAR(MAX);
 
SET @table = N'production.products';
 
SET @sql = N'SELECT * FROM ' + @table;
 
EXEC sp_executesql @sql;

قطعه کد بالا دقیقاً همان نتایجی را ایجاد می‌کند که کد پایین نیز ایجاد می‌کند:

SELECT * FROM production.products;

برای به دست آوردن داده از یک جدول دیگر، می‌توانید مقدار متغیر table@ را تغییر دهید. به هر حال، اگر قطعه کد T-SQL بالا را در یک Stored procedure قرار دهیم، خیلی بهتر و به صرفه‌تر می‌شود.

SQL پویا و stored procedureها در SQL Server

این stored procedure هر جدولی را می‌پذیرد و مجموعه نتیجه را با استفاده از SQL پویا، از یک جدول مشخص شده بازمی‌گرداند:

CREATE PROC usp_query (
    @table NVARCHAR(128)
)
AS
BEGIN
 
    DECLARE @sql NVARCHAR(MAX);
    -- SQL ساخت
    SET @sql = N'SELECT * FROM ' + @table;
    --  SQL اجرای 
    EXEC sp_executesql @sql;
    
END;

کد زیر یک stored procedure به نام usp_query را فرامی‌خواند تا تمام ردیف‌های جدول production.brands را بازگرداند:

EXEC usp_query 'production.brands';

این stored procedure ۱۰ ردیف بالایی یک جدول را با استفاده از مقادیر یک ستون مشخص شده، بازمی‌گرداند:

CREATE OR ALTER PROC usp_query_topn(
    @table NVARCHAR(128),
    @topN INT,
    @byColumn NVARCHAR(128)
)
AS
BEGIN
    DECLARE 
        @sql NVARCHAR(MAX),
        @topNStr NVARCHAR(MAX);
 
    SET @topNStr  = CAST(@topN as nvarchar(max));
 
    -- SQL ساخت
    SET @sql = N'SELECT TOP ' +  @topNStr  + 
                ' * FROM ' + @table + 
                    ' ORDER BY ' + @byColumn + ' DESC';
    --  SQL اجرای 
    EXEC sp_executesql @sql;
    
END;

برای مثال، می‌توانید ۱۰ محصول گران قیمت را از جدول production.products به دست‌ آورید:

EXEC usp_query_topn 
        'production.products',
        10, 
        'list_price';

این کد ۱۰ محصول دارای بیشترین تعداد موجود در انبار را بازمی‌گرداند:

EXEC usp_query_topn 
        'production.tocks',
        10, 
        'quantity';

SQL‌ پویا و SQL Injection در SQL Server

اجازه دهید یک جدول جدید به نام sales.tests برای این مثال ایجاد کنیم:

CREATE TABLE sales.tests(id INT); 

این کد تمام ردیف‌های جدول production.brands را بازمی‌گرداند:

EXEC usp_query 'production.brands';

اما کاربران را از ارسال نام جدول به شکل زیر، باز نمی‌دارد:

EXEC usp_query 'production.brands;DROP TABLE sales.tests';

این تکنیک، SQL Injection نام دارد. وقتی کد اجرا می‌شود، جدول sales.tests حذف می‌شود، چون stored procedure به نام usp_query هر دو دستورات را اجرا می‌کند:

SELECT * FROM production.brands;DROP TABLE sales.tests

برای جلوگیری از پیش آمدن SQL Injection ، می‌توانید از تابع ()QUOTENAME به شکل زیر در query استفاده کنید:

CREATE OR ALTER PROC usp_query
(
    @schema NVARCHAR(128), 
    @table  NVARCHAR(128)
)
AS
    BEGIN
        DECLARE 
            @sql NVARCHAR(MAX);
        -- construct SQL ساخت
        SET @sql = N'SELECT * FROM ' 
            + QUOTENAME(@schema) 
            + '.' 
            + QUOTENAME(@table);
        --  SQL اجرای 
        EXEC sp_executesql @sql;
    END;

حالا، اگر شما و نام جدول را به Stored procedure ارسال کنید، به این شکل می‌شود:

EXEC usp_query 'production','brands';

به هر حال، اگر سعی کنید یک دستور دیگ به این شکل تزریق کنید:

EXEC usp_query 
        'production',
        'brands;DROP TABLE sales.tests';

یک ارور به شکل زیر پیش می‌آید:

Invalid object name 'production.brands;DROP TABLE sales.tests'.

نکات بیشتر راجع به stored procedure‌ به نام sp_exectesql

Sp_exectesql دارای syntax به شکل زیر است:

EXEC sp_executesql 
    sql_statement  
    parameter_definition
    @param1 = value1,
    @param2 = value2,
    ...

در این suntax:

  • Sql_statement یک رشته Unicode است که حاوی کد T-SQL است. Sql_statement می‌تواند شامل پارامترهایی مانند SELECT * FROM table_name WHERE id=@id.

  • Parameter_definition یک رشته است که حاوی تعریف تمام پارامترهای ادغام شده در Sql_statement است. هر تعریف پارامتر از یک نام پارامتر و نوع داده آن (مانند idINT@) تشکیل شده است. تعاریف پارامتر با یک ویرگول از هم مجزا می‌شوند.

  • …,param1 = value1, @param2 = value2@  یک مقدار برای هر پارامتر تعریف شده در رشته parameter_definition مشخص می‌کند.

این مثال از stored procedure به نام sp_executesql برای یافتن محصولاتی که دارای قیمت بیشتر از 100 و دسته 1 هستند، استفاده می‌کند:

EXEC sp_executesql
N'SELECT *
    FROM 
        production.products 
    WHERE 
        list_price> @listPrice AND
        category_id = @categoryId
    ORDER BY
        list_price DESC', 
N'@listPrice DECIMAL(10,2),
@categoryId INT'
,@listPrice = 100
,@categoryId = 1;

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

خلاصه

در این سه قسمت (قسمت‌های ۲۱، ۲۲ و ۲۳) به صورت کامل و شامل، کار با Stored procedure ها را آموختید. در نتیجه بر یکی از مهم‌ترین مباحث SQL Server تسلط دارید.

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

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

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

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

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