ایجاد کرسر در sql server
کرسرها(CURSORS) درSQL-Server
مقدمه
باتوجه به اينکه خروجي دستورSELECT معمولايک مجموعه (جدول) بوده ودرزبان هاي برنامه نويسي ، امکان کارکردن باجداول وجودندارد، به کمک کرسرها امکان دسترسي به رکوردهاي يک جدول به شکل رکوردبه رکورد وجوددارد .
قبل از معرفي نحوه تعريف يک کرسر و خصوصيات آن با توجه به زياد بودن دسته بندي هاي مختلف کرسرها برحسب خصويات مختلف آن بهتر است ابتدا به معرفي بعضي ويژگي هايي کرسر ها بپردازيم :
1- طول عمر(حوزه)کرسر:
کرسر هايي که ساخته مي شوند به صورت پيش فرض تا آخر پايدار بودن اتصال جاري به سرور باقي مي مانند مگر اين که صراحتا از بين برده شونديا توسط عبارتDEALLOCATE غيرفعال شوند.
2- قدرت تغيير و به روز آوري جداولي که کرسر روي آن ها حرکت مي کند:
به اين معني که بعد از اين که کرسري ساخته شد آيا مي تواند تغييري در تاپلي که روي آن قرار دارد ايجاد کنديا اين که فقط حق خواندن ازاين تاپل را دارد.
3- نحوه حرکت کرسر روي تاپل هاي جدول :
اين که آيا کرسر اين قدرت را دارد که مثلا بتواند علاوه بر جلو رفتن به سمت عقب هم حرکت کنديا اين که به ابتداي جدول پرش کند.
4- حساسيت به تغييرات ايجاد شده در جدول :
به اين معني که آياپس ازبازکردن کرسر، اگرتغييراتي درجداول منبع کرسربه وجودبيايد،درکرسرتاثير دارد ياندارد .
5- سرعت ايجاد و حرکت کرسر:
اين خصوصيت يک خصوصيت تر کيبي است و با توجه به اين که يک کرسر کدام يک از خصوصيات موارد قبل را داشته باشد تعيين مي شود .مثلا مسلما کرسري که هم رو به جلووهم روبه عقب حرکت مي کند از کرسري که تنها رو به جلو مي رود کند تر است.
نحوه استفاده از کرسرها
براي استفاده از يک کرسر بايد قدم هاي زير را طي نمود :
1- تعريف کردن کرسر: تعريف کرسربه شکل کلي زيرانجام مي شود :
DECLARE cursor_name CURSOR
[ LOCAL | GLOBAL ]
[ FORWARD_ONLY | SCROLL ]
[ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ]
[ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ]
FOR select_statement
[ FOR UPDATE [ OF column_name [ ,...n ] ] ]
بررسي بعضي پارامتر هاي تعريف فوق :
• کلمه کليدي Local ، ميدان استفاده از کرسر را به تابع ،رويه يا رهانايي که رويه در آن تعريف شده محدود مي کند .کلمه کليدي Globalبه اين معناست که کرسر تا پايدار بودن اتصال جاري به سرور باقي مي ماند مگر اين که صراحتا از بين برده شود .
• : FORWARD_ONLY به اين معني است که کرسر مي تواند تنها روبه جلو حرکت کند در حالي که SCROLL بودن کرسرهم حرکت رو به جلو وهم حرکت روبه عقب را براي کرسر ممکن مي کند .
• STATIC: اين کلمه کليدي باعث مي شود که کرسر اصلا نسبت به تغييرات ايجاد شده در پايگاه داده حساس نباشد. زيرا در اين حالت کرسر يک کپي از داده هارا به tempdbمنتقل مي نمايد .
• KEYSET : در اين حالت فقط ستون هاي کليدي تاپل هاي کرسر را مشخص مي کنند(اين مجموعه را KEYSET مي نامند) در tempdb نگهداري مي شوند.بعد از باز کردن کرسر مقادير اين کليد ها چون کپي از جدول اصلي است نسبت به تغييرات جداول اصلي غير حساس مي شوندولي بقيه ستون ها نسبت به تغييرات حساسند .
• DYNAMIC: در اين نوع کرسرهابا هر بار حرکت درکرسردستور SELECTآنها دوباره اجرا مي شود بنابراين کاملا نسبت به تغييرات پايگاه داده ها حساس است .
• FAST_FORWARD: اين کرسر ها سريع ترين کرسرها هستندوترکيبي از گزينه هاي FORWARD _ ONLY وREAD_ONLY هستند .
• SCROLL_LOCKS: در اين حالت بر خلاف حالت READ_ONLYامکان UPDATE وجود دارد. اينجاکليه تاپلهاي کرسر(ونه فقط تاپلي که جاري است) قفل مي شودوهيچ شي (Object) جز خود همين کرسرنمي تواند آن ها را تغيير دهد.در اين حالت مطمئنيم که INSERTوUPDATEبا مشخص کردن تاپل مورد نظر حتما با موفقيت انجام مي شود .
• OPTIMISTIC: تفاوت اين مورد با SCROLL_LOCKSدر قفل نکردن داده هااست .
• يادآور مي شويم امکان استفاده از بعضي گزينه هاي فوق که معمولا منطقاً متضاد هستند به صورت هم زمان وجود نداردبه عنوان مثال يک کرسر از نوع FAST_FORWARDنمي تواند خاصيتSCROLL _LOCKS يا OPTIMISTICراداشته باشد .
2- باز کردن کرسر:
OPEN { [ GLOBAL ] cursor_name }
در صورتي که دو کرسر همنام هم زمان هم به صورت محلي و هم به صورت سراسري وجود داشته باشند براي باز کردن کرسر سراسري بايدکلمه کليدي Globalاستفاده کرد .
3- خواندن سطور :
هر بار که دستور FETCH اجرا مي گردد در واقع يک رکورد جديد از کرسر خوانده مي شود .
FETCH
[ [ NEXT | PRIOR | FIRST | LAST
| ABSOLUTE n
| RELATIVE n
]
FROM
]
{ { [ GLOBAL ] cursor_name } }
[ INTO @variable_name [ ,...n ] ]
براي کنترل واکشي سطرها درهنگام استفاده از کرسرمتغيرسيستمي @@FETCH_STATUS به کاربرده مي شود . مقدار خروجي اين تابع وضعيت آخرين دستور FETCH را نمايش مي دهد،در صورتي که واکشي موفقيت آميز باشداين تابع مقدار صفر را بر مي گرداند.
• کلمه کليدي NEXT باعث مي شود تا سطربعداز سطر جاري واکشي شود.کلمات کليدي FIRSTوPRIORوLAST به ترتيب معادل اولي ، قبلي و آخرين مي باشند.
• ABSOLUTE nسبب مي شود تاپل nام از ابتداي کرسر واکشي شود .
• RELATIVE nسبب مي شود تاپل nام بعد از تاپل جاري کرسرواکشي شود.
• با استفاده از عبارت INTO @variable_name [ ,...n ]متغير هاي ذکر شده بعد از INTOبانتايج حاصل از واکشي کرسر مقداردهي مي شوند.انواع داده اي اين متغير ها بايدبا انواع داده اي ستون هاي ذکر شده در جلوي SELECTکرسر به ترتيب يکسان باشند .
4- بستن کرسر:
CLOSE { [ GLOBAL ] cursor_name }
5- رها کردن حافظه اشغال شده توسط آن
DEALLOCATE { [ GLOBAL ] cursor_name }
قدم هاي 2 تا4 مي تواند تکرار شود يعني يک کرسر را بعد از بستن دوباره باز کرد که در اين صورت کرسر مجدداً از جدول اصلي مقدار دهي مي شود.
مثال1 :حذف دروس ثبت نام شده دانشجويان که بدون رعايت هم نيازي اخذ نموده اند با استفاده از يک کرسر و رويه نوشته شده در مثال6 آزمايش 3 . ( با فرض اين که هر درس فقط يک درس هم نياز دارد.)
ابتدا کرسر مورد نظر را ايجاد مي نمائيم:
declare cr_delete_prereq_reg cursor
SCROLL_LOCKS
FOR select s# from std
open cr_delete_prereq_reg
declare @st# int
fetch next from delete_prereq_reg into @st#
while ( @@fetch_status = 0)
begin
execute pr14 @st#
fetch next from cr_delete_prereq_reg into @st#
end
close cr_delete_prereq_reg
deallocate cr_delete_prereq_reg
يکي از روش هاي ارتباط با رويه ها اين است که يک کرسر به عنوان پارامتر خروجي معرفي شود.در واقع خروجي رويه يک کرسر است که مي توان با آن کار کرد .
مثال2 : مي خواهيم رويه اي بنويسيم که شماره دانشجو را به عنوان ورودي دريافت نموده و نام دروسي که دانشجو ثبت نام نموده در پارامتر خروجي که يک کرسر است قرار بدهيم .
CREATE PROCEDURE pr_stdcourses_cursor
@s# int,
@stdcourses_cursor CURSOR VARYING OUTPUT
AS
SET @stdcourses_cursor = CURSOR
OPTIMISTIC
DYNAMIC FOR
SELECT cname from
FROM reg inner join crs
On crs.c#=reg.c#
Where reg.s#=@s#
OPEN @titles_cursor
کلمه کليدي VARYING را در مواردي که يک کرسر به عنوان پارامتر خروجي تعريف مي شود به کار برده مي شود . در رويه بالاابتداخواص کرسر @stdcourses_cursorراکه پارامتر خروجي اين رويه است را setکرده ايم . سپس اين کرسر را باز و آماده واکشي کرده ايم . در دستورات زير از اين رويه استفاده مي کنيم.
DECLARE @MyCursor CURSOR
EXEC pr_stdcourses_cursor @stdcourses_cursor = @MyCursor OUTPUT
Declare @cname varchar(25)
WHILE (@@FETCH_STATUS = 0)
BEGIN
FETCH NEXT FROM @MyCursor into @cname
Print 'the next course is:'+@cname
END
CLOSE @MyCursor
DEALLOCATE @MyCursor
GO
دستورکار
5- بااستفاده ازرويه TrmGpa Pr_ دردستورکارآزمايش 3ويک کرسر به نام Update_CR_TrmGpa ، معدل ترم (TrmGpa) دانشجويان را اصلاح نماييد .
6- بااستفاده ازرويه Pr_tot_stdtrm دردستورکارآزمايش 3ويک کرسر به نام Cr_tot_stdtrmمعدل کل (Gpa) ، کل واحدهاي گذرانده (TotpassUnit) وکل واحدهاي اخذشده(TotRegUnit) دانشجويان را اصلاح کنيد .
7- با استفاده از رويه Pr_Delete_Prereq_reg دردستورکارآزمايش 3و يک کرسربه نام Cr_Delete_Prereq_reg دروس دانشجوياني را که بدون رعايت پيش نيازي (در جدول REG)ثبت نام کرده اند حذف نمايد .
8- با استفاده از رويهPr_Mydelete دردستورکارآزمايش 3و يک کرسربه نام Cr_Mydelete ثبت نام هاي کليه دروس به حدنصاب نرسيده را حذف کند .
9- آيا مي توان کرسرهاي Dynamic را با استفاده از کرسرهاي Static شبيه سازي کرد؟بررسي کنيد .