مقدمه :
در اين مقاله به بررسي دقيق ترتوابع Multi Statements همراه با شرح يک مثال مي پردازم .
تعريف : Multi Statements توابعي هستند که مقدار برگشتب آنها از نوع Table مي باشد .
اين جمله را شرح مي دهم :
ابتدا ، متغير هايي از نوع Table :
در SQl Server ما مي توانيم متغير هايي از نوع Table اعلام کنيم . اعلام يک متغير از نوع Table به شکل زير است :
DECLARE @Table TABLE
(
UserID int NOT NULL,
UserName NVARCHAR(50)
)
|
براي مقدار دهي به متغيرهاي از نوع table نمي توانيم مانند ديگر انواع متغيرها از دستور SET استفاده کنيم .
براي مقدار دهي به اين متغيرها ، همانگونه که از مفهوم منطقي جدول (table) مشخص است ، بايد رکورد به جدول اضافه کنيم . پس ما براي کار کردن با اين متغيرها بايد از دستورات پايگاه داده اي(INSERT,UPDATE,DELETE,SELECT) استفاده کنيم و کلا به اين گونه متغيرها مانند يک جدول نگاه کنيم که ماهيت فيزيکي ندارند .
نمونه اي از مقدار دادن به يک متغير از نوع Table :
INSERT INTO @Table
(UserID ,UserName)
VALUES
(1,'MohammadZangeneh')
|
با اين توضيحات توابع Multi Statements توابعي هستند مانند ساير توابع با اين تفاوت که در داخل آنها ما يک متغير table را اعلام کرده و آنرا مقدار دهي کرده و آنرا برمي گردانيم .
حال براي جا افتادن مطالب به بررسي يک مثال عملي مي پردازيم که احتمالا کاربرد زيادي در برنامه هاي ما خواهد داشت (من که تو برنامه هام خيلي از اين تابع استفاده کردم ).
ما در برنامه هاي خود گاهي نياز داريم که يک عمل يا فرآيند را روي تعدادي از متغيرها يا موجوديت ها اعمال کنيم ، مانند حذف يا تائيد موجوديت ها بصورت دسته اي و يا دادن نمره هاي دروس مختلف يا دادن ترتيب يا اولويت به يک سري از موجوديت ها ...
در اين مواقع ما مي توانيم براي اين دسته از متغيرها يا موجوديت ها تک تک و هر بار همان عمل را انجام دهيم .به عنوان مثال در مورد حذف 4 موجوديت از جدول بايد هر بار يک دستور حذف روي جدول اجذا کنيم .
راه ديگر اين است که يکباره همه آن موجوديتها (کليدهاي موجوديت ها ) را در رشته اي به هم چسبانده (Concate ) و به وسيله يک کاراکتر جداکننده (Separator) از هم متمايز کنيم . سپس آنها را به سرور فرستاده و انجام آن فرآيند را بر روي اين دسته از موجوديت ها به SQL Server بسپاريم .
اين روش از کارآيي و سرعت بسيار بالاتري نسبت به روش قبل دارد و استاندارتر هم مي باشد .
تنها مسئله اي که مي ماند اين است که ما کليد اين موجوديت هايي که همگي داخل يک رشته هستند و به وسيله يک کاراکتر جدا کننده از هم متمايز شده اند را از هم جدا کرده يا اصطلاحا قطعه قطعه (Split) کنيم و اين موجوديت ها را از هم جدا کنيم . سپس براي هر يک از آنها عمليات را انجام دهيم .
تابع زير يک رشته ورودي مي گيرد که قرار است آنرا بر اساس کاراکتر جدا کننده اي (Separator) که به آن فرستاده شده است از هم جدا کند و جدولي را متشکل از قطعه هاي رشته برمي گرداند . شما مي توانيد اين تابع را در Stored Procedure ها يا Function هاي خود به خوبي استفاده کنيد .
-- =============================================
-- Author: Mohammad Zangeneh
-- alter date: 2007-05-15
-- Description: For Split String
-- =============================================
CREATE FUNCTION [dbo].[fn_Spliter]
(
@str_Source nvarchar(250),
@Separator nvarchar(50)
)
RETURNS
@tbl_Result TABLE (Word NVARCHAR(250) COLLATE Arabic_CI_AS )
AS
BEGIN
DECLARE @tbl_Temp TABLE(Word NVARCHAR(250) COLLATE Arabic_CI_AS )
DECLARE @str_Temp NVARCHAR(50)
SET @str_Temp = @str_Source
WHILE (len(@str_Temp)>0)
BEGIN
IF CHARINDEX(@Separator,@str_Temp) <> 0
BEGIN
IF SUBSTRING (@str_Temp,1,CHARINDEX(@Separator,@str_Temp)-1) <> ''
INSERT INTO @tbl_Temp (Word) VALUES (SUBSTRING (@str_Temp,1,CHARINDEX(@Separator,@str_Temp)-1))
SET @str_Temp = SUBSTRING (@str_Temp,CHARINDEX(@Separator,@str_Temp)+1,len(@str_Temp) - CHARINDEX(@Separator,@str_Temp))
END
ELSE
BREAK
END
IF (len(@str_Temp)>0 )
INSERT INTO @tbl_Temp (Word) VALUES (@str_Temp)
INSERT @tbl_Result SELECT WORD FROM @tbl_Temp
RETURN
END |
در رويه ذخيره شده زير شيوه استفاده از تابع fn_Spliter را مشاهده مي کنيد .
ايت تابع رشته اي متشکل از آدي يک سري از کاربران به همراه امتيازشان فرستاده مي شود و اين SP به هر کاربر امتيازش را تخصيص مي دهد .
CREATE PROCEDURE [dbo].[SP_Usre_AssignRateToUser]
@str_List VARCHAR(200)
AS
DECLARE CUR CURSOR FOR SELECT * FROM fn_spliter(@str_List,',')
DECLARE @str_Temp VARCHAR(50)
OPEN CUR
FETCH NEXT FROM CUR INTO @str_Temp
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @UserID INT
SET @UserID = CAST(SUBSTRING (@str_Temp,1,CHARINDEX('_',@str_Temp)-1) AS INT)
DECLARE @Rate INT
SET @Rate = SUBSTRING (@str_Temp,CHARINDEX('_',@str_Temp)+1,len(@str_Temp) - CHARINDEX('_',@str_Temp))
UPDATE tbl_UsersRate SET
UserRate = @Rate ,
Date = GETDATE()
WHERE UsreID = @UserID
FETCH NEXT FROM CUR INTO @str_Temp
END
CLOSE CUR
DEALLOCATE CUR
|
حال شما با دستور زير مي توانيد به ده ها کاربر سيستم امتياز مناسب را تخصيص دهيد .
EXEC [dbo].[SP_Users_AssignRateToUser] '1_2,2_4,3_10,4_5,6_5'
|
توضيح ضروري :
من عمدا خصوصيت Collate متغير Table را تنظيم کردم ، چون شما شايد در بعضي از استفاده ها از Multi Statements بخواهيد تابع تان را با جدول ديگري JOIN کنيد و ممکن است در برخي موارد به Error در رابطه با عدم تشابه Collate جداول برخورد کنيد .در اين گونه موارد Collate متغير جدولتان را با Collate مناسب تنظيم کنيد .
ومن الله التوفيق .