הבלוג של גרי רשף

10/09/2010

השימוש ב-Cursor

תחילת דרכי בתחום בסיסי נתונים הייתה באקסס: נרשמתי לקורס, תרגלתי בבית, והתחלתי לעבוד באופן חלקי ובשכר זעום בחברה קטנה שפיתחה באקסס כדי שאוכל לצבור נסיון.
ההרגל הרע שרכשתי שם ולקח לי שנים להיפטר ממנו הוא לעבור עם Recordsets: כדי לעדכן / למחוק/ להוסיף נתונים היו פותחים שם רקורדסט, עוברים על כל הרשומות ומעדכנים; כל זה במקום להשתמש בפקודות SQL.
כנראה שאינני היחיד שעבר את "ההכשרע" הזאת, מכיוון שמדי פעם אני נתקל בשאלות של מתחילים שמנסים לעדכן טבלה ב-SQL Server בעזרת Cursor..
השימוש ב-Cursors מסורבל ואינו יעיל ולכן יש להימנע ממנו למעט מקרים חריגים, והכוונה בעיקר לבניית פקודות SQL דינאמיות, ולמעבר על טבלאות מערכת כדי לטפל באובייקטים שם; למשל- מעבר על טבלת sys.databases כדי לגבות את כל הדטבייסים, מעבר על כל האינדקסים בעזרת sys.sysindexes כדי לטפל בהם וכו'.
הפוסט הזה מוקדש לאותם מקרים חריגים שבהם אין ברירה אלא להשתמש ב-Cursor.

לצורך ההמחשה אצור שתי טבלאות- טבלה בת שלוש שורות בשם Try001 שעליה אעבור בעזרת Cursor,
וטבלת עצירה/שיחרור של הריצה בשם Wait שתכלול עמודה אחת ושורה אחת (הריצה של ה-Cursor תיעצר עד שהערך בטבלה ישתנה מ-1 ל-0).

Use tempdb;

Go


If Object_Id('Try001') Is Not Null Drop Table Try001;

Go


Create Table Try001(Mone Int Primary Key,

                    Teur Varchar(Max));

Go


Insert Into Try001 Select 1,'First line';

Insert Into Try001 Select 2,'Middle line';

Insert Into Try001 Select 3,'Last line';

Go


If Object_Id('Wait') Is Not Null Drop Table Wait;

Go


Create Table Wait(B Bit);

Go


Insert Into Wait Select 1;

Go

אפתח בדוגמה פשוטה- פתיחה של Cursor, המתנה עד שהערך בטבלה Wait יתאפס, במקביל לאיפוס- ערכי הטבלה ישתנו (כדי לבדוק מה "יראה" ה-cursor בהמשך הריצה),

כאשר שתי השורות הראשונות שיופיעו גם בדוגמאות בהמשך – נועדו לאתחל את הנתונים בשתי הטבלאות:

Update Wait Set B=1;

Update Try001 Set Teur=Replace(Teur,'*','');

Declare @I Int,

        @S VarChar(Max);

Declare Cr Cursor For Select * From Try001;

Open Cr;

Fetch Cr Into @I,@S;

While @@Fetch_Status = 0

    Begin

    Select @I Mone,@S Teur;

    While (Select B From Wait)=1

        WaitFor Delay '00:00:05';

    Fetch Cr Into @I,@S

    End

Close Cr;

Deallocate Cr;

Go

הקוד מתחיל לרוץ ונתקע ב-While הפנימי: כל חמש שניות הוא בודק האם הערך בטבלה Wait הוא עדיין 1.

נפתח במקביל חלון Query חדש, נעדכן את ערכי Try001 ב*כוכביות* מימין ומשמאל לערכים ונשנה את הערך בטבלה Wait ל-0:

Update Try001 Set Teur='*'+Teur+'*';

Update Wait Set B=0;

Go

הפלט שיתקבל (תוך שניות ספורות):

image

השורה הראשונה נקראה "נקייה" לפני השינוי, ובהמשך הריצה ה-Cursor "קרא" את הערכים המעודכנים ב*כוכביות*.

פקודת Fetch קוראת את נתוני השורה הפעילה לתוך המשתנים ועוברת לשורה הבאה.

משתנה המערכת Fetch_Status@@ מציין אם ה-Fetch האחרון שהתבצע הצליח (0), האם השורה האחרונה נקראה (1-), או האם היתה שגיאה (2-); וכך לולאת ה-While רצה כל עוד נקראים נתונים תקינים ולא הגענו לסוף.

אופציה חלופית היא להשתמש במשתנה המערכת Cursor_Rows@@ שמציין כמה שורות ה-Cursor קרא, ולהיעזר במשתנה שיספור כמה פעמים ביצענו Fetch וכך לזהות את הסוף; אך יש לאופציה זו חסרונות ומגבלות (תקפה רק עבור Cursor שהוא Local ו-Static) ולכן עדיף לא להשתמש בה.

בדוגמה הבאה ה-Cursor הוא מסוג Static ולכן הנתונים "שלו" לא יתעדכנו, ופקודת ה-Select בסוף תמחיש זאת:

Update Wait Set B=1;

Update Try001 Set Teur=Replace(Teur,'*','');

Declare @I Int,

        @S VarChar(Max);

Declare Cr Cursor Static For Select * From Try001;

Open Cr;

Fetch Cr Into @I,@S;

While @@Fetch_Status = 0

    Begin

    Select @I Mone,@S Teur;

    While (Select B From Wait)=1

        WaitFor Delay '00:00:05';

    Fetch Cr Into @I,@S

    End

Close Cr;

Deallocate Cr;

Select * From Try001;

Go

נשחרר את העצירה באותו אופן כמו בדוגמה הקודמת:

Update Try001 Set Teur='*'+Teur+'*';

Update Wait Set B=0;

Go

ונקבל את הפלט הבא:

image

הנתונים שה-Cursor קורא לא השתנו לאחר העדכון שהתבצע בין שורה 1 לשורה 2, למרות שנתוני הטבלה השתנו.

בדוגמה הבאה ה-Cursor מסוג Scroll_Locks נועל את הטבלה וכך מונע מהנתונים להשתנות במהלך הריצה:

Update Wait Set B=1;

Update Try001 Set Teur=Replace(Teur,'*','');

Declare @I Int,

        @S VarChar(Max);

Declare Cr Cursor Scroll_Locks For Select * From Try001;

Open Cr;

Fetch Cr Into @I,@S;

While @@Fetch_Status = 0

    Begin

    Select @I Mone,@S Teur;

    While (Select B From Wait)=1

        WaitFor Delay '00:00:05';

    Fetch Cr Into @I,@S

    End

Close Cr;

Deallocate Cr;

Go

ננסה לשחר את העצירה באותו אופן כמו קודם:

Update Try001 Set Teur='*'+Teur+'*';

Update Wait Set B=0;

Go

..אלא שכעת נסיון השחרור נתקע בעצמו: ה-Cursor נעל את הטבלה, לא ניתן לשנות אותה, וגם עדכון טבלה Wait ממתין לתורו..

נפתח חלון Query חדש – שלישי במספר – ונפעיל קודם כל את הפרוצדורה sp_who: אנו אמורים לראות בין הפעילויות השונות שתיים בסטטוס suspended-שני ה-Queries התקועים, כאשר הראשון מריץ פקודת WAITFOR, והשני פקודת UPDATE תוך שהוא נחסם (Blocked) על ידי הראשון.

את ה-SPID של כל Query ניתן לראות בשוליים התחתונים (SPID=58 במקרה זה):image

נריץ ב-Query השלישי – זה שאינו תקוע בשלב זה – את פקודת השחרור:

Update Wait Set B=0;

Go

ונקבל תוך שניות ספורות את הפלט:

image

הנתונים לא התעדכנו  במהלך הריצה כי הטבלה ננעלה, ורק לאחר שה-Cursor סיים את הריצה – הטבלה התעדכנה בערכים ה*מכוכבים*.

ניצור כעת Cursor מסוג Scroll שיכול לנוע קדימה ואחורה ונבחן את האפשרויות השונות (התחיל, נעבור לשורה הבאה, נחזור לקודמת, נעבור לראשונה, נעבור לשלישית, ננוע אחת אחורה, נעבור לאחרונה):

Update Try001 Set Teur=Replace(Teur,'*','');

Declare @I Int,

        @S VarChar(Max);

Declare Cr Cursor Scroll For Select * From Try001;

Open Cr;

--1 התחלה

Fetch Next From Cr Into @I,@S;

Select @I Mone,@S Teur;

--2 הבאה

Fetch Next From Cr Into @I,@S;

Select @I Mone,@S Teur;

--1 הקודמת

Fetch Prior From Cr Into @I,@S;

Select @I Mone,@S Teur;

--1 הראשונה

Fetch First From Cr Into @I,@S;

Select @I Mone,@S Teur;

--3 השלישית

Fetch Absolute 3 From Cr Into @I,@S;

Select @I Mone,@S Teur;

--2 אחת אחורה

Fetch Relative -1 From Cr Into @I,@S;

Select @I Mone,@S Teur;

--3 האחרונה

Fetch Last From Cr Into @I,@S;

Select @I Mone,@S Teur;

Close Cr;

Deallocate Cr;

Go

והפלט לווידוא ביצוע:

image

נבחן כעת את יכולתו של ה-Cursor לעדכן את הטבלה תוך כדי ריצה, ונפתח אותו For Update Of Teur:

Update Wait Set B=1;

Update Try001 Set Teur=Replace(Teur,'*','');

Declare @I Int,

        @S VarChar(Max);

Declare  Cr Cursor For Select * From Try001 For Update Of Teur;

Open Cr;

Fetch Cr Into @I,@S;

While @@Fetch_Status = 0

    Begin

    If @I=1

        Update Try001 Set Teur='*'+Teur+'*' Where Current Of Cr

    Else If @I=2

        Delete From Try001 Where Current Of Cr

    Select @I Mone,@S Teur;

    Fetch Cr Into @I,@S    End

While (Select B From Wait)=1

    WaitFor Delay '00:00:05'

Close Cr;

Deallocate Cr;

Select * From Try001;

Insert Into Try001 Select 2,'Middle line';

Go

הפעם הוא נעצר בהמתנה לשינוי ב-Wait לאחר שהוא שינה את שורה 1 ומחק את 2,

והפעם נבדוק  בחלון ה-Query השני ממה מצב הטבלה בנקודה זו בטרם הוא סיים את הריצה:

Select * From Try001;

Update Wait Set B=0;

Go

image

השינויים התבצעו תוך כדי ריצת ה-Cursor.

הפלט הצפוי בחלון ה-Cursor:

image

כדאי לציין שבמקרה זה הטבלה אינה ננעלת ולכן ניתן לשנותה במקביל,

ובנוסף- אופציית ה-Static שטופלה קודם מתנגשת עם אופציית For Update מכיוון שהראשונה היא Read Only בהגדרתה. כדאי להיות ערים לכך מכיוון שיש הגדרות סותרות ובמקרה כזה נקבל הודעת שגיאה.

בפוסט זה מופיעה טבלה המציגה את כל צירופי האופציות ומציינת היכן קיימות סתירות (בסמוך לכותרת Cursor Options Compatibility).

בניגוד למשתנים שמתקיימים רק במהלך ה-Batch בו הם מוגדרים ועם סיומו הם מתבטלים, Cursor יכול להיות מסוג Global ולהתקיים גם לאחר שה-Batch הסתיים:

Update Try001 Set Teur=Replace(Teur,'*','');

Declare Cr Cursor Global For Select * From Try001;

Open Cr;

Go

Declare @I Int,

        @S VarChar(Max);

Fetch Cr Into @I,@S;

Select @I Mone,@S Teur;

Go

Declare @I Int,

        @S VarChar(Max);

Fetch Cr Into @I,@S;

Select @I Mone,@S Teur;

Go

Declare @I Int,

        @S VarChar(Max);

Fetch Cr Into @I,@S;

Select @I Mone,@S Teur;

Go

Close Cr;

Deallocate Cr;

Go

הסקריפט הזה מורכב מחמישה Batches שכל אחד מסתיים במילה השמורה Go,

ולאחריו יש להגדיר מחדש את I@ ואת S@ אך לא את ה-Cursor.

ניתן להריץ את כל הסקריפט במכה אחת, וגם בהמשכים- כל Batch בנפרד.

הפלט מלמד שאין שום בעייה:

image

פקודת ה-Declare מגדירה את ה-Cursor ויוצרת לו מקום בזכרון, ומכיוון שהוא אינו מתבטל ומשתחרר בסוף ה-Batch כמו המשתנים – הזכרון משתחרר על ידי פקודת Deallocate שבסוף.

פקודת Open מבצעת את הקריאה בפועל של ה-Select והפעלת ה-Cursor, ופקודת ה-Close סוגרת אותו אך לא משחררת את הזכרון, וניתן לעשות לו Open מחדש.

להלן קוד להמחשה:

Update Wait Set B=1;

Update Try001 Set Teur=Replace(Teur,'*','');

Declare Cr Cursor Static For Select * From Try001;

Declare @I Int,

        @S VarChar(Max);

Open Cr;                --Cursor-פותח את ה

Fetch Cr Into @I,@S;    --קורא את השורה הראשונה

Select @I Mone,@S Teur;

Fetch Cr Into @I,@S;    --קורא את השורה השניה

Select @I Mone,@S Teur;

Close Cr;                --Cursor-סוגר את ה

While (Select B From Wait)=1

    WaitFor Delay '00:00:05';

Open Cr;                --Cursor-פותח מחדש את ה

Fetch Cr Into @I,@S;    --קורא שוב את השורה הראשונה

Select @I Mone,@S Teur;

Close Cr;

Deallocate Cr;

Go

הריצה תיעצר כרגיל ב-While הפנימי ותשתחרר בחלון של ה-Query השני על ידי:

Update Try001 Set Teur='*'+Teur+'*';

Update Wait Set B=0;

Go

הפלט המתקבל:

image

בפעם הראשונה שה-Cursor נפתח הטבלה נקראה והוצגו שורות 1 ו-2.

לאחר שהוא נסגר ונפתח מחדש – כשבין לבין הוא המתין לעדכון טבלה Wait ונתוני Try001 *כוכבו* – הוא שוב ניגש לשורה 1 וקורא אותה לאחר השינוי.

ה-Cursor במקרה זה הוא מסוג Static ובכל זאת הוא מציג את השינויים: הגדרה זו תופסת בין ה-Open ל-Close בלבד!

ניתן ליצור Cursor מקונן באופן סכימטי כך (הקטע המקונן הוא בין ה-Declare וה-Deallocate של Cr2):

Declare @I1 Int,

        @S1 VarChar(Max),

        @I2 Int,

        @S2 VarChar(Max);

Declare Cr1 Cursor For Select * From T1;

Open Cr1;

Fetch Cr1 Into @I1,@S1;

While @@Fetch_Status = 0

    Begin

    Declare Cr2 Cursor For Select * From T2;

    Open Cr2;

    Fetch Cr2 Into @I2,@S2;

    While @@Fetch_Status = 0

        Begin

        ....

        Fetch Cr1 Into @I1,@S1

        End

    Close Cr2;

    Deallocate Cr2;

    Fetch Cr1 Into @I1,@S1

    End

Close Cr1;

Deallocate Cr1;

Go

ב-Cursor פשוט ללא ציון מפורש של אף אחת מהאופציות (כמו בדוגמה הראשונה)- מופעלת ברירת המחדל:

Forward Only (להבדיל מ-scroll) – ה-Cursor יכול לנוע רק קדימה. אופציה עדיפה היכן שאפשר מבחינת חסכוניותה במשאבי מערכת, והמהדרין בוחרים ב-Fast_Forward שהיא גם Read Only.

Dynamic (להבדיל מ-Static) – הנתונים משתנים תוך כדי ריצה מבחינת ה-Cursor.

For Update (להבדיל מ-Read_Only) – ה-Cursor יכול לעדכן את הנתונים (לשיפור ביצועים- עדיף Read_Only היכן שאפשר).

Global (להבדיל מ-Local) – ה-Cursor מתקיים גם מחוץ לגבולות ה-Batch.

Optimistic (להבדיל מ-Scroll_Locks) – הטבלה עליה פועל ה-Cursor אינה ננעלת (עדיף לא לנעול).

בנוסף – כדאי לקרוא ל-Cursor רק את העמודות הנדרשות,

וכמובן- להימנע עד כמה שאפשר מלהשתמש ב-Cursors..

מודעות פרסומת

3 תגובות »

  1. […] פעם פוסט על Cursors והאופציות השונות שלהן. מכיוון שאחת לכמה שבועות אני […]

    פינגבאק של Template for Cursors - גרי רשף — 01/09/2011 @ 06:02

  2. […] פעם פוסט על Cursors והאופציות השונות שלהן. מכיוון שאחת לכמה שבועות אני […]

    פינגבאק של Template for Cursors « הבלוג של גרי רשף — 01/09/2011 @ 05:55

  3. […] תגים:Backup, Cursor, Fast_Forward, SQL Server, While Loop — גרי רשף @ 15:36 השימוש ב-Cursor הוא דרך המלך למשימות שלא ניתן לבצע בכלים הסטנדרטיים של […]

    פינגבאק של Cursor ללא Cursor « הבלוג של גרי רשף — 11/09/2010 @ 15:39


RSS feed for comments on this post. TrackBack URI

להשאיר תגובה

הזינו את פרטיכם בטופס, או לחצו על אחד מהאייקונים כדי להשתמש בחשבון קיים:

הלוגו של WordPress.com

אתה מגיב באמצעות חשבון WordPress.com שלך. לצאת מהמערכת / לשנות )

תמונת Twitter

אתה מגיב באמצעות חשבון Twitter שלך. לצאת מהמערכת / לשנות )

תמונת Facebook

אתה מגיב באמצעות חשבון Facebook שלך. לצאת מהמערכת / לשנות )

תמונת גוגל פלוס

אתה מגיב באמצעות חשבון Google+ שלך. לצאת מהמערכת / לשנות )

מתחבר ל-%s

בלוג בוורדפרס.קום.

%d בלוגרים אהבו את זה: