SQL Server Cursor : สอน คำสั่ง ของ Cursor, เรียน คำสั่ง ของ Cursor
 

Reference

Reference ในเว็บไซต์ Function.in.th เป็นการนำเสนอความรู้ในรูปแบบของแหล่งอ้างอิงของคำสั่งต่าง ๆ ที่ใช้ในการเขียนโปรแกรม โดยผู้ที่สนใจสามารถเข้ามาเรียนรู้การใช้งานคำสั่งของการเขียนโปรแกรมเหล่านี้ได้ ผ่านทาง URL code.function.in.th ทั้งนี้ผู้อ่านยังสามารถร่วมแสดงความคิดเห็นบนเนื้อหาที่มีสอนได้ ซึ่งถือเป็นการส่งเสริมให้มีการแลกเปลี่ยนประสบการณ์และความรู้ระหว่างผู้อ่านด้วยกันเอง
  • ทำความรู้จักกับ Cursor ทำความรู้จักกับ Cursor
    ขั้นตอนการสร้าง Cursor ขั้นตอนการสร้าง Cursor
    DECLARE CURSOR ประกาศ Cursor
    DECLARE CURSOR [ แบบที่ 2 ] ประกาศ Cursor แบบที่ 2
    OPEN cursor เปิด Cursor
    FETCH cursor เข้าถึง Record ข้อมูลด้วย Cursor
    CLOSE cursor ปิด Cursor
    DEALLOCATE cursor คืนทรัพยากรที่ถูกใช้โดย Cursor
    @@CURSOR_ROWS ใช้ตรวจสอบจำนวน Records หลังการเกิด Cursor
    @@FETCH_STATUS ใช้ตรวจสอบสถานะในการเข้าถึงข้อมูลของ Curso
    ตัวอย่างการ UPDATE ข้อมูล ณ ตำแหน่งที่ Cursor ชี้อยู่ ตัวอย่างการ UPDATE ข้อมูล ณ ตำแหน่งที่ Cursor ชี้อยู่
    ตัวอย่างการ DELETE ข้อมูล ณ ตำแหน่งที่ Cursor ชี้อยู่ ตัวอย่างการ DELETE ข้อมูล ณ ตำแหน่งที่ Cursor ชี้อยู่
  • subject :
    ทำความรู้จักกับ Cursor

    content :
    Cursor ใช้เพื่อเข้าถึงกลุ่มผลลัพธ์ ( ResultSet ) และสามารถทำสิ่งต่างๆต่อไปนี้ได้
    1) สามารถเข้าถึงตำแหน่ง Record ของกลุ่มผลลัพธ์ได้
    2) สามารถดึงข้อมูลจาก Column ของ Record ที่ชี้อยู่ได้
    3) สามารถเปลี่ยนแปลงแก้ไขข้อมูลใน Record ที่ชี้อยู่ได้

  • subject :
    ขั้นตอนการสร้าง Cursor

    content :
    ขั้นตอนการสร้าง Cursor ดังนี้
    1) ประกาศ Cursor
    2) เปิด Cursor
    3) เข้าถึง Record ข้อมูลด้วย Cursor
    4) ปิด Cursor
    5) คืนทรัพยากรที่ถูกใช้โดย Cursor

    example :
    DECLARE @person_id int
    DECLARE @person_firstname varchar(200)
    DECLARE @person_lastname varchar(200)
    DECLARE @person_sex int
    
    DECLARE cur_person CURSOR FOR SELECT id, firstname, lastname, sex FROM person
    OPEN cur_person
    FETCH NEXT FROM cur_person INTO @person_id, @person_firstname, @person_lastname, @person_sex
    
    PRINT 'ID : ' + @person_id
    PRINT 'NAME : ' + @person_firstname + ' ' + @person_lastname
    PRINT 'SEX : ' + @person_sex
    
    CLOSE cur_person
    DEALLOCATE cur_person

  • subject :
    DECLARE CURSOR

    syntax :
    DECLARE cursor_name [ INSENSITIVE ] [ SCROLL ] CURSOR
    FOR <select_statement>
    [ FOR { READ ONLY | UPDATE [ OR column1 [, columnN ] ] } ]

    content :
    ใช้ในการประกาศ Cursor
    > โดย INSENSITIVE เป็นการกำหนดให้สร้างเป็นตารางชั่วคราวแยกต่างหากไว้ในฐานข้อมูลระบบ tempdb
    ดังนั้นการแก้ไขข้อมูลจากตารางหลัก จะไม่มีผลกับข้อมูลที่เกิดจาก Cursor นี้ และการแก้ไขข้อมูลที่ Cursor
    นี้ ไม่สามารถทำได้
    > โดย SCROLL เป็นการกำหนดให้สามารถใช้คำสั่ง FIRST, LAST, PRIOR, NEXT, RELATIVE และ ABSOLUTE
    เพื่อเข้าถึงแต่ละ Record ของ Cursor ได้ ( ถ้าไม่กำหนดคำสั่งนี้จะใช้ได้เพียง NEXT เท่านั้น )
    > โดย <select_statement> สามารถใช้คำสั่ง COMPUTE, COMPUTE BY, FOR BROWSE และ INTO ไม่ได้
    > โดย READ ONLY เป็นการกำหนดว่า Cursor นี้สามารถอ่านได้อย่างเดียว
    > โดย UPDATE เป็นการกำหนดว่า Cursor นี้สามารถ UPDATE ข้อมูลได้ และ UPDATE คอลัมน์ใดได้บ้าง
    ถ้าไม่ระบุ จะหมายถึงสามารถ UPDATE ได้ทุก COLUMN

    example :
    DECLARE @person_id int
    DECLARE @person_firstname varchar(200)
    DECLARE @person_lastname varchar(200)
    DECLARE @person_sex int
    
    DECLARE cur_person CURSOR FOR SELECT id, firstname, lastname, sex FROM person
    OPEN cur_person
    FETCH NEXT FROM cur_person INTO @person_id, @person_firstname, @person_lastname, @person_sex
    
    PRINT 'ID : ' + @person_id
    PRINT 'NAME : ' + @person_firstname + ' ' + @person_lastname
    PRINT 'SEX : ' + @person_sex
    
    CLOSE cur_person
    DEALLOCATE cur_person

  • subject :
    DECLARE CURSOR [ แบบที่ 2 ]

    syntax :
    DECLARE cursor_name CURSOR
    [ LOCAL | GLOBAL ]
    [ FORWARD_ONLY | SCROLL ]
    [ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ]
    [ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ]
    [ TYPE_WARNING ]
    FOR <select_statement>
    [ FOR UPDATE [ OR column1 [, columnN ] ] ]

    content :
    ใช้ในการประกาศ Cursor โดยปกติ Cursor จะเป็นแบบ GLOBAL
    ( สามารถเปลี่ยนได้โดย sp_dboption db_name, "default to local cursor", "TRUE" )
    > โดย FORWARD_ONLY จะทำให้ใช้ได้แค่คำสั่ง FETCH NEXT เท่านั้น
    > โดย SCROLL จะทำให้สามารถใช้คำสั่ง FIRST, LAST, PRIOR, NEXT, RELATIVE และ ABSOLUTE ได้
    
    > โดย STATIC คือเป็นการกำหนดให้สร้างเป็นตารางชั่วคราวแยกต่างหากไว้ในฐานข้อมูลระบบ tempdb
    ดังนั้นการแก้ไขข้อมูลจากตารางหลัก จะไม่มีผลกับข้อมูลที่เกิดจาก Cursor นี้ และการแก้ไขข้อมูลที่ Cursor
    นี้ ไม่สามารถทำได้
    > โดย KEYSET คือหมายความว่า Cursor นี้จะอาศัย Unique Indexes ในการเข้าถึงข้อมูลจริงที่เก็บไว้ในตารางหลัก
    ดังนั้นหากมีการเปลี่ยนแปลงข้อมูลในตารางหลักจะทำให้ข้อมูลจาก Cursor ได้รับผลกระทบตามไปด้วย
    ( ถ้าหาก Table ไม่มี Unique Indexes แล้วก็จะถูกเปลี่ยนไปเป็น STATIC โดยอัตโนมัติ )
    > โดย DYNAMIC เป็นการกำหนดว่าหากมีการเปลี่ยนแปลงข้อมูลที่ตารางหลักแล้ว จะมีผลต่อ Cursor ด้วย
    จะทำให้ไม่สามารถใช้คำสั่ง FETCH ABSOLUTE ได้ ( วิธีการทำงานของ Cursor นี้นั้น ทุกๆครั้งที่มีการ FETCH
    ข้อมูล Cursor จะถูกสร้างใหม่ ทำให้ข้อมูลมีการ UPDATE อยู่เสมอ แต่จะทำให้ใช้ Resource สูงมากตามลำดับ ) 
    > โดย FAST_FORWARD คือจะเข้าถึง Record แรกโดยอัตโนมัติหลังจากเปิด Cursor และจะปิด Cursor
    ดยอัตโนมัติทันทีเมื่อเข้าถึง Record สุดท้าย ( ไม่สามารถใช้ได้พร้อมกับคำสั่ง SCROLL, SCROLL_LOCKS, 
    OPTIMISTIC, FOR UPDATE ได้ )
    
    (
    เงื่อนไขของการใช้ FAST_FORWARD ดังนี้
    1) เมื่อมีการ Query ระหว่าง 2 ตามรางแล้ว FAST_FORWARD จะถูกเปลี่ยนเป็น STATIC
    2) เมื่อมีการ Query โดย Distributed Query แล้ว FAST_FORWARD จะถูกเปลี่ยนเป็น KEYSET
    3) เมื่อมีการใช้คีย์เวิร์ด FOR UPDATE แล้ว FAST_FORWARD จะถูกเปลี่ยนแป็น DYNAMIC
    4) ถ้า Query มีการอ้างถึง column ที่มีข้อมูลประเภท text, ntext หรือ image แล้ว FAST_FORWARD จะถูกเปลี่ยนเป็น DYNAMIC
    5) ถ้า Query มีการอ้างถึง column ที่มีข้อมูลประเภท text, ntext หรือ image และมีคีย์เวิร์ด TOP ด้วย แล้ว FAST_FORWARD จะถูกเปลี่ยนเป็น DYNAMIC
    )
    
    > โดย READ_ONLY เป็นการกำหนดให้ Cursor อ่านได้อย่างเดียว
    > โดย SCROLL_LOCKS เป็นการ Lock ข้อมูลในตารางจริง เมื่อมีการอ่านมายัง Cursor ทำให้ไม่สามารถแก้ไขข้อมูลในตารางจริงได้
    > โดย OPTIMISTIC เป็นการอ่านและบันทึกลงได้ และไม่มีการ Lock ใดๆ
    
    > โดย TYPE_WARNING กำหนดให้มีการแจ้งเตือน หากมีการเปลี่ยนแปลงประเภทของ Cursor ( ที่เปลี่ยนแปลงโดยอัตโนมัติตามเงื่อนไขต่างๆ )
    > โดย FOR UPDATE เป็นการกำหนดว่า Cursor นี้สามารถ UPDATE ข้อมูลได้ และ UPDATE คอลัมน์ใดได้บ้าง
    ถ้าไม่ระบุ จะหมายถึงสามารถ UPDATE ได้ทุก COLUMN

    example :
    DECLARE @person_id int
    DECLARE @person_firstname varchar(200)
    DECLARE @person_lastname varchar(200)
    DECLARE @person_sex int
    
    DECLARE cur_person CURSOR FOR SELECT id, firstname, lastname, sex FROM person
    OPEN cur_person
    FETCH NEXT FROM cur_person INTO @person_id, @person_firstname, @person_lastname, @person_sex
    
    PRINT 'ID : ' + @person_id
    PRINT 'NAME : ' + @person_firstname + ' ' + @person_lastname
    PRINT 'SEX : ' + @person_sex
    
    CLOSE cur_person
    DEALLOCATE cur_person

  • subject :
    OPEN cursor

    syntax :
    OPEN { { [ GLOBAL ] cursor_name } | cursor_variable_name }

    content :
    ใช้ในการเปิด Cursor

    example :
    DECLARE @person_id int
    DECLARE @person_firstname varchar(200)
    DECLARE @person_lastname varchar(200)
    DECLARE @person_sex int
    
    DECLARE cur_person CURSOR FOR SELECT id, firstname, lastname, sex FROM person
    OPEN cur_person
    FETCH NEXT FROM cur_person INTO @person_id, @person_firstname, @person_lastname, @person_sex
    
    PRINT 'ID : ' + @person_id
    PRINT 'NAME : ' + @person_firstname + ' ' + @person_lastname
    PRINT 'SEX : ' + @person_sex
    
    CLOSE cur_person
    DEALLOCATE cur_person

  • subject :
    FETCH cursor

    syntax :
    FETCH
    [ [ NEXT | PRIOR | FIRST | LAST | ABSOLUTE n | RELATIVE n ] FROM ]
    { { [ GLOBAL ] cursor_name } | cursor_variable_name }
    [ INTO @varname1 [, @varnameN ] ]

    content :
    ใช้ในการเข้าถึงข้อมูลของ Cursor

    example :
    DECLARE @person_id int
    DECLARE @person_firstname varchar(200)
    DECLARE @person_lastname varchar(200)
    DECLARE @person_sex int
    
    DECLARE cur_person CURSOR FOR SELECT id, firstname, lastname, sex FROM person
    OPEN cur_person
    FETCH NEXT FROM cur_person INTO @person_id, @person_firstname, @person_lastname, @person_sex
    
    PRINT 'ID : ' + @person_id
    PRINT 'NAME : ' + @person_firstname + ' ' + @person_lastname
    PRINT 'SEX : ' + @person_sex
    
    CLOSE cur_person
    DEALLOCATE cur_person

  • subject :
    CLOSE cursor

    syntax :
    CLOSE { { [ GLOBAL ] cursor_name } | cursor_variable_name }

    content :
    ใช้ในการปิด Cursor
    

    example :
    DECLARE @person_id int
    DECLARE @person_firstname varchar(200)
    DECLARE @person_lastname varchar(200)
    DECLARE @person_sex int
    
    DECLARE cur_person CURSOR FOR SELECT id, firstname, lastname, sex FROM person
    OPEN cur_person
    FETCH NEXT FROM cur_person INTO @person_id, @person_firstname, @person_lastname, @person_sex
    
    PRINT 'ID : ' + @person_id
    PRINT 'NAME : ' + @person_firstname + ' ' + @person_lastname
    PRINT 'SEX : ' + @person_sex
    
    CLOSE cur_person
    DEALLOCATE cur_person

  • subject :
    DEALLOCATE cursor

    syntax :
    DEALLOCATE { { [ GLOBAL ] cursor_name } | cursor_variable_name }

    content :
    ใช้ในการคืนทรัพยากร ในกรณีที่ไม่ได้ใช้ Cursor นั้นแ้ล้ว

    example :
    DECLARE @person_id int
    DECLARE @person_firstname varchar(200)
    DECLARE @person_lastname varchar(200)
    DECLARE @person_sex int
    
    DECLARE cur_person CURSOR FOR SELECT id, firstname, lastname, sex FROM person
    OPEN cur_person
    FETCH NEXT FROM cur_person INTO @person_id, @person_firstname, @person_lastname, @person_sex
    
    PRINT 'ID : ' + @person_id
    PRINT 'NAME : ' + @person_firstname + ' ' + @person_lastname
    PRINT 'SEX : ' + @person_sex
    
    CLOSE cur_person
    DEALLOCATE cur_person

  • subject :
    @@CURSOR_ROWS

    syntax :
    @@CURSOR_ROWS 

    content :
    ถ้าคืนค่า -n เพื่อบอกว่าพบจำนวน n record และจะมากขึ้นเรื่อยๆ เพราะยังโหลดไม่เสร็จ
    ถ้าคืนค่า n เพื่อบอกว่าพบจำนวน n record ( เพราะโหลดเสร็จแล้ว )
    ถ้าคืนค่า -1 เพื่อบอกว่าเป็นการเปิดแบบ DYNAMIC
    ถ้าคืนค่า 0 เพื่อบอกว่ามีจำนวน 0 record

    example :
    DECLARE @person_id int
    DECLARE @person_firstname varchar(200)
    DECLARE @person_lastname varchar(200)
    DECLARE @person_sex int
    
    DECLARE cur_person CURSOR FOR SELECT id, firstname, lastname, sex FROM person
    OPEN cur_person
    FETCH NEXT FROM cur_person INTO @person_id, @person_firstname, @person_lastname, @person_sex
    
    WHILE @@FETCH_STATUS = 0
    BEGIN
    	PRINT 'ID : ' + @person_id
    	PRINT 'NAME : ' + @person_firstname + ' ' + @person_lastname
    	PRINT 'SEX : ' + @person_sex
    
    	FETCH NEXT FROM cur_person INTO @person_id, @person_firstname, @person_lastname, @person_sex
    END
    
    CLOSE cur_person
    DEALLOCATE cur_person

  • subject :
    @@FETCH_STATUS

    syntax :
    @@FETCH_STATUS

    content :
    ถ้าคืนค่า 0 คือเข้าถึง Record สำเร็จ
    ถ้าคืนค่า -1 คือเข้าถึง Record ไม่สามารถกระทำได้ หรือได้ชี้เกิดกลุ่มของผลลัพธ์ไปแล้ว ( EOF )
    ถ้าคืนค่า -2 คือการเข้าถึง Record มีความผิดพลาดเกิดขึ้น

    example :
    DECLARE @person_id int
    DECLARE @person_firstname varchar(200)
    DECLARE @person_lastname varchar(200)
    DECLARE @person_sex int
    
    DECLARE cur_person CURSOR FOR SELECT id, firstname, lastname, sex FROM person
    OPEN cur_person
    FETCH NEXT FROM cur_person INTO @person_id, @person_firstname, @person_lastname, @person_sex
    
    WHILE @@FETCH_STATUS = 0
    BEGIN
    	PRINT 'ID : ' + @person_id
    	PRINT 'NAME : ' + @person_firstname + ' ' + @person_lastname
    	PRINT 'SEX : ' + @person_sex
    
    	FETCH NEXT FROM cur_person INTO @person_id, @person_firstname, @person_lastname, @person_sex
    END
    
    CLOSE cur_person
    DEALLOCATE cur_person

  • subject :
    ตัวอย่างการ UPDATE ข้อมูล ณ ตำแหน่งที่ Cursor ชี้อยู่

    example :
    UPDATE table_name
    SET column_name = value
    WHERE CURRENT OF { { [ GLOBAL ] cursor_name } | cursor_variable_name }
    
    UPDATE person
    SET sex = 2
    WHERE CURRENT OF cur_person

  • subject :
    ตัวอย่างการ DELETE ข้อมูล ณ ตำแหน่งที่ Cursor ชี้อยู่

    example :
    DELETE table_name
    WHERE CURRENT OF { { [ GLOBAL ] cursor_name } | cursor_variable_name }
    
    DELETE person
    WHERE CURRENT OF cur_person

 
Share This Chapter Login with Facebook