10-12-2015, 03:13 AM
می خواهیم تابعی در SQL Server ایجاد نماییم که توسط آن بتوانیم تراز حساب اشخاص را به صورت بدهکار بستانکار و مانده بدست آوریم ، به این صورت که اطلاعات مربوط به حساب یک نفر را از یک جدول گرفته و مانده حساب ها ، بدهکار و بستانکار را محاسبه کند. در این روش هر عدد با عدد قبلی خود جمع می شود و در روبرای عدد جاری مقدار مانده نوشته می شود. به مثال زیر دقت فرمائید:
بدهکار بستانکار مانده
=========================
1000 0 1000
0 200- 800
450 0 1250
ابتدا یک جدول با مشخصات زیر ایجاد می کنیم.که اطلاعات مربوط به حساب افراد را نگه داری می کند.
فیلد RowID به صورت خودکار مقدار دهی می شود و کلید جدول است.
فیلد AcountStatus مقدار حساب را نشان می دهد.
فیلد Name نشان دهنده نام فرد دارای حساب است.
اطلاعات را به صورت زیر وارد جدول می کنیم.
تعریف تابع
عملکرد تابع به این صورت است که نام صاحب حساب را می گیرد و مقادیر حساب به همراه مانده آن را محاسبه می کند.
با استفاده از این تابع، اطلاعات را از جدول tblAcount دریافت کرده و در یک جدول جدید با مشخصات زیر وارد کنیم تا مقدار باقیمانده حساب را محاسبه کند.با این شرط که اگر مقدار AcountStatus مثبت باشد، مقدار آن در ستون debit قرار گرفته و مقدار credit صفر شود. اگر مقدار AcountStatus منفی باشد مقدار آن را در ستون credit قرار داده و مقدار فیلد debit صفر می شود.
قطعه کد زیر بررسی می کند که آیا مقدار AcountStatus مثبت است یا خیر؟ در صورتی که مقدار آن مثبت باشد مقدار AcountStatus را در ستون debit قرار می دهد و در صورت منفی بودن مقدار صفر را قرار می دهد.
قطعه کد زیر بررسی می کند که آیا مقدار AcountStatus منفی است یا خیر؟ در صورتی که مقدار آن منفی باشد مقدار AcountStatus را در ستون credit قرار می دهد و در صورت مثبت بودن مقدار صفر را قرار می دهد.
این قسمت نیز مقدار مانده را محاسبه می کند.برای محاسبه مانده برای هر ردیف باید مقدار حساب فعلی یک نفر را با حساب های قبلی او جمع کرد.
اجرای تابع
تابع را برای محاسبه مانده حساب فردی به نام ali اجرا می کنیم.
نتیجه به صورت زیر است.
(ارسال شده توسط آقای مهندس امین امیرخانی کارشناس ارشد نرم افزار)
بدهکار بستانکار مانده
=========================
1000 0 1000
0 200- 800
450 0 1250
ابتدا یک جدول با مشخصات زیر ایجاد می کنیم.که اطلاعات مربوط به حساب افراد را نگه داری می کند.
کد:
CREATE TABLE [dbo].[tblAcount]
(
[RowID] [int] PRIMARY key IDENTITY(1,1) NOT NULL,
[AcountStatus] [int] NOT NULL,
[Name] [nvarchar](20) NOT NULL
)
فیلد AcountStatus مقدار حساب را نشان می دهد.
فیلد Name نشان دهنده نام فرد دارای حساب است.
اطلاعات را به صورت زیر وارد جدول می کنیم.
کد:
INSERT INTO dbo.tblAcount ( AcountStatus, Name )VALUES ( 100, 'ali')
INSERT INTO dbo.tblAcount ( AcountStatus, Name )VALUES ( -520, 'amin')
INSERT INTO dbo.tblAcount ( AcountStatus, Name )VALUES ( 30, 'mohammad')
INSERT INTO dbo.tblAcount ( AcountStatus, Name )VALUES ( -90, 'ali')
INSERT INTO dbo.tblAcount ( AcountStatus, Name )VALUES ( 160, 'ali')
INSERT INTO dbo.tblAcount ( AcountStatus, Name )VALUES ( 95, 'amin')
INSERT INTO dbo.tblAcount ( AcountStatus, Name )VALUES ( 700, 'reza')
INSERT INTO dbo.tblAcount ( AcountStatus, Name )VALUES ( 50, 'mohammad')
INSERT INTO dbo.tblAcount ( AcountStatus, Name )VALUES ( -180, 'reza')
تعریف تابع
عملکرد تابع به این صورت است که نام صاحب حساب را می گیرد و مقادیر حساب به همراه مانده آن را محاسبه می کند.
کد:
CREATE function [dbo].[UDFAcount2](@name nvarchar(20))
returns @tblAcount table(
RowID int not null ,
debit decimal not null,
credit decimal not null,
remain decimal not null)
as
begin
insert into @tblAcount
select RowID,
Isnull((select AcountStatus from tblAcount
where RowID=ta.RowID and AcountStatus>=0),0),
Isnull((select AcountStatus from tblAcount
where RowID=ta.RowID and AcountStatus<0),0),
(select sum(AcountStatus) from tblAcount
where RowID<=ta.RowID and Name=@name)
from tblAcount ta where name=@name
return
end
با استفاده از این تابع، اطلاعات را از جدول tblAcount دریافت کرده و در یک جدول جدید با مشخصات زیر وارد کنیم تا مقدار باقیمانده حساب را محاسبه کند.با این شرط که اگر مقدار AcountStatus مثبت باشد، مقدار آن در ستون debit قرار گرفته و مقدار credit صفر شود. اگر مقدار AcountStatus منفی باشد مقدار آن را در ستون credit قرار داده و مقدار فیلد debit صفر می شود.
کد:
@tblAcount table(
RowID int not null ,
debit decimal not null,
credit decimal not null,
remain decimal not null)
قطعه کد زیر بررسی می کند که آیا مقدار AcountStatus مثبت است یا خیر؟ در صورتی که مقدار آن مثبت باشد مقدار AcountStatus را در ستون debit قرار می دهد و در صورت منفی بودن مقدار صفر را قرار می دهد.
کد:
Isnull((select AcountStatus from tblAcount
where RowID=ta.RowID and AcountStatus>=0),0)
قطعه کد زیر بررسی می کند که آیا مقدار AcountStatus منفی است یا خیر؟ در صورتی که مقدار آن منفی باشد مقدار AcountStatus را در ستون credit قرار می دهد و در صورت مثبت بودن مقدار صفر را قرار می دهد.
کد:
Isnull((select AcountStatus from tblAcount
where RowID=ta.RowID and AcountStatus<0),0)
این قسمت نیز مقدار مانده را محاسبه می کند.برای محاسبه مانده برای هر ردیف باید مقدار حساب فعلی یک نفر را با حساب های قبلی او جمع کرد.
کد:
(select sum(AcountStatus) from tblAcount
where RowID<=ta.RowID and Name=@name)
اجرای تابع
تابع را برای محاسبه مانده حساب فردی به نام ali اجرا می کنیم.
کد:
SELECT * FROM dbo.UDFAcount2('ali')
نتیجه به صورت زیر است.
(ارسال شده توسط آقای مهندس امین امیرخانی کارشناس ارشد نرم افزار)