محاسبه مقدار ستونها به صورت پویا

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

در SQL server 7 و نسخه های بعدی آن امکان استفاده از ستونهای محاسباتی فراهم گردیده است. ضمن آنکه sql server 2000 نیز قابلیت ایندکس گذاری بر روی ستونهای محاسباتی را نیز فراهم کرده است. ایندکس گذاری بر روی این ستونها باعث صرفه جویی در اشغال زمان پردازنده برای محاسبات شده و باعث میشود بتوانیم ذخیره سازی و فیلترینگ موثرتری داشته باشیم.

توسعه برنامه

برای توسعه برنامه، ابتدا بهتر است نگاهی به ساختار جدول مورد نظرمان داشته باشیم

ستون computation در جدول بالا، از نوع یک رشته یونیکد است که حاصل محاسباتی که بر روی مقادیر ستونهای arg1 و arg2 و arg3 میشود را ذخیره سازی میکند. برخی از محاسباتی که شما متیوانید انجام دهید را در زیر میبینید:

N'@arg1+@arg2+@arg3'
N'@arg1*@arg2-@arg3'
N'CEILING(@arg1+@arg2/@arg3)'

همچنین میخواهیم که در ستون rez نتایج یک عملیات درج/به روز آوری بر روی arg1 یا arg2 یا arg3 را ذخیره سازیم. این نتایج بر اساس فرمولی بدست می آیند که در ستون computation قرار دارد. البته ناگفته نماند که این فرمول برای هر سطر متفاوت خواهد بود.

بخش اصلی آنچه که در اینجا در زمانی که درج یا به روزآوری انجام داده میشود در یک تریگر به نام trgComputation در جدول انجام میگیرد:

CREATE TRIGGER trgComputation ON [dbo].[Computation] FOR INSERT, UPDATE
AS
declare @rows as int
set rows = @@rowcount
if not @rows > 0 return
if not update(arg1) and not update(arg2) and not update(arg3) return
declare
@key as int,
@arg1 as int,
@arg2 as int,
@arg3 as int,
@rez as int,
@comp as nvarchar(500),
@param as nvarchar(500)
select @key = min(id) from inserted
while @key is not null
begin
select
@arg1 = arg1,
@arg2 = arg2,
@arg3 = arg3,
@comp = computation
from inserted where @key = id
set @comp = N'set @rez= ' + @comp
set @param = N'@rez int output, @arg1 int, @arg2 int, @arg3 int'
exec sp_executesql @comp, @param, @rez output, @arg1, @arg2, @arg3
update computation set rez=@rez where id= @key
select @key = min(id) from inserted where id>@key
end

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

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

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

DECLARE @r_out as int
EXEC sp_executesql
N'set @r = @p1 * @p2',
N'@p1 int, @p2 int, @r int output',
@r = @r_out OUTPUT,
@p1 = 10,
@p2 = 5
SELECT @r_out

این کد از sp_executesql برای محاسبه حاصلضرب دو آرگومان ورودی و ذخیره کردن نتایج در یک پارامتر به نام @r بهره میبرد.
اولین آرگومانی که sp_executesql میپذیرد عبارتی است که شما میخواهید به صورت پویا اجرا کنید. دومین آرگومان رشته ای است که لیستی از پارامترهای ورودی و خروجی که عبارت ذکر شده از انها استفاده میکند را نگهداری میکند. همه دیگر آرگومانها یی که sp_executesql میپذیرد در حقیقت انتسابهای مقادیر به پارامترهایی هستند که عبارت مذکور ار آن‌ها استفاده میکند.
بیایید از رویه مذکور استفاده کنیم. جدول ما در این لحظه به این شکل است:
 

اکنون ست arg1 را توسط عبارت به روزآوری زیر تغییر میدهیم:

update computation set arg1 = arg1 +10;

نتایج چنین خواهند بود :

استفاده از رویه ذخیره شده سیستمی sp_executesql به شما امکان میدهد تا محاسبات خود را به صورت پویا انجام دهید و حتی از متغیرهای خروجی نیز بتوانید استفاده کنید.در صورتی که نخواهید از این رویه استفاده کنید، محاسبات این چنینی بسیار پیچیده تر و وقت گیرتر خواهند بود.
منبع : codeproject.com
مترجم: علیرضا شیرازی- ar.shirazi@gmail.com