DBMS ไทย บทที่ 17 การสร้าง...

40
บบบบบ 17 บบบบบบบบ บบบบบบบบบ บบบบบบบบบบ (Cursors) จจจจจจจจจจจจจจจจจจจจจจจจ จจจจจจจจจจจจจ จ จจจจจจจจจจจจ จจ จจจจจจจจจจจจจจจจจจ จจ จจจจจจจจจจจจจจจจจจจจจจจจจจจจจจจจจจจจจจจจจ จจจจจจจจจจ จจจจจจจจจจจจจจจจจจจจจจจจจจจจจจ จจจจจจจจจจ SELECT จจจจจจจจจจจจจจจจจจจจจจจจ จจจจจจจจจจจจจจจจจจจจจจจจจจจจจจจจจจจจจจจจจจ จจจจจจจจจจจจจจจจจจจจจจจจ จจจ SQL Server จจ จจจจจจจจจจจจจจจจจจ จจจจจจจจจจ (Cursors) จจจจจจจจจจจจจจจจจจจจจจจจจจจจจจจจจจจจจจจจจ จจจจจจจจจจ จจจจจจจจจจจจจจจจจจจจจจจจจจจจ จจจจจจจจจจจจจจจจจจจจจจจจจจจจ จจจจจจจจจจจจจ จจจจจจจจจจจจจจจจจจจจจจจจจจจจจจจจจจจจจจจจจจจ จจจจจจจจจจจจจจจจจจจจ บบบบบบบบบบบบบบบบบบบ (Cursors) จจ SQL Server จจจจจจจจจจจจจจจจจจจจจจ 3 จจจจ จจจ Transact – SQL Cursors จจจจจจจจจจจจจ จจจจจจจจจจจ DECLARE CURSOR จจจจจจจจ จจจจจจจจจจจจจจจ Transact – SQL, Stored Procedure จจจจจจจจจจจจ จจจจจจจจจจจจจจ จจจจจจจจจจจจจจจจจจจจจจจจจจจจ จจจจจจจจจ จจจจจจจจจจจจจ จจจจจจจจจจจจจจจจจจ จจจจจจจจจจจจจจจจจ จจจจจจจจจจจจจจจจจจ จจจจจจจจจจจจจจจจจจจจจจจจจจจจจจจจจจจ 2 จจจ 3 จจจจจจจจจจจจจจจจจจจจจจจจจจจจจจ จจจจจจจจจจจจจจจจจจจจจจจจจจ จ

description

DBMS ไทย บทที่ 17 การสร้าง และใช้งานเคอร์เซอร์

Transcript of DBMS ไทย บทที่ 17 การสร้าง...

Page 1: DBMS ไทย บทที่ 17 การสร้าง และใช้งานเคอร์เซอร์

บทท�� 17 การสราง และใช้งานเคอร�เซอร� (Cursors)

จากที่��ผ่�านมาเราจะเห็�นว่�า การที่�างานต่�าง ๆ ที่��งการแก�ไข ลบ ห็ร�อการคิ!ว่ร�ข�อม"ล จะกระที่�าในแบบกล$�มของผ่ลล�พที่ธ์'ที่��เราเร�ยกว่�า เรคิอร'ต่เซต่ ซ*�งเรคิร'ต่เซต่น��เราได้�มาจากการใช้�คิ�าสั่��ง SELECT เม��อโปรแกรมไคิลเอ�นต่'ได้�ร�บเรคิอร'ด้เซต่เห็ล�าน��แล�ว่จะต่�องม�ว่!ธ์�การในการที่�างานก�บแต่�ละเรคิอร'ด้ โด้ย SQL Server ม�เคิร��องม�อเร�ยกว่�า เคิอร'เซอร' (Cursors) สั่�าห็ร�บใช้�ในการจ�ด้การข�อม"ลเรคิอร'ด้เซต่ในระด้�บเรคิอร'ด้ โด้ยจะม�พอยเต่อร'ที่��ช้��ไปย�งเรคิอร'ด้ที่��เราที่�างานด้�ว่ยขณะน��น คิว่ามสั่ามารถน��ม�คิว่ามจ�าเป2นมากสั่�าห็ร�บงานที่��ต่�องเข�าถ*งการที่�างานในระด้�บเรคิอร'ด้

ร�จั�กก�บเคอร�เซอร� (Cursors)ใน SQL Server น��นจะม�เคิอร'เซอร'อย"� 3 ช้น!ด้ คิ�อ

Transact – SQL Cursors จะสั่ร�างข*�นมาด้�ว่ยคิ�าสั่��ง DECLARE CURSOR และจะใช้�ที่��ว่ไปในสั่คิร!ปต่' Transact

– SQL, Stored Procedure และที่ร!กเกอร' เคิอร'เซอร'ช้น!ด้น��จะที่�างานอย"�บนเซ!ร'ฟเว่อร' สั่�าห็ร�บในคิ"�ม�อเล�มน�� เราจะกล�าว่ถ*งเฉพาะเคิอร'เซอร'ช้น!ด้น�� สั่�ว่นที่�านที่��ต่�องการที่ราบรายละเอ�ยด้ของเคิอร'เซอร'ในแบบที่�� 2 และ 3 ที่�านสั่ามารถอ�านได้�จากห็น�งสั่�อรายละเอ�ยด้เก��ยว่ก�บเร��องน��น ๆ

Application programming interface (API) server Cursors SQL Server จะสั่น�บสั่น$น API

Cursor ใน OLE DB (ADO ที่��ปรากฏใน Visual Basic),

ODBC และ DB – Library ซ*�งแต่�ละคิร��งที่��โปรแกรมฝั่7� งไคิลเอ�นต่'เร�ยกใช้�งาน API OLE DB, ODBC และ DB –

Library ก�จะสั่�งคิ�าร�องขอไปย�งเซ!ร'ฟเว่อร' เพ��อจ�ด้การก�บเคิอร'เซอร'น��น

Page 2: DBMS ไทย บทที่ 17 การสร้าง และใช้งานเคอร์เซอร์

Client Cursors เคิอร'เซอร'ช้น!ด้น��จะที่�างานโด้ยการเก�บเรคิอร'ด้เซต่ที่��งห็มด้ที่��ได้�จากเซ!ร'ฟเว่อร'ไว่�ที่��ไคิลเอ�นต่' และจะที่�างานที่��ไคิลเอ�นต่'

ข้อดี�ข้องการใช้เคอร�เซอร�ข�อด้�ของการใช้�เคิอร'เซอร'ก�คิ�อ เราสั่ามารถที่��จะที่�างานอย�างม�

เง��อนไขบนแต่�ละเรคิอร'ด้ได้�แต่กต่�างก�น ซ*�งการที่�างานอย�างน��จะถ"กน�าไปใช้�ในโปรแกรมที่��ซ�บซ�อน และนอกจากน��เคิอร'เซอร'ย�งม�ข�อด้�ด้�งต่�อไปน��

การใช้�เคิอร'เซอร'ในสั่ถานการณ'ที่��การที่�างานแบบเรคิอร'ด้ จะม�ประสั่!ที่ธ์!ภาพมากกว่�าการใช้�คิ�าสั่��ง UPDATE เพ��อแก�ไขเรคิอร'ด้แบบเป2นกล$�ม

สั่ามารถคิว่บคิ$มที่รานแซคิช้��นได้�ด้�กว่�า เน��องจากเราสั่ามารถที่�างานก�บข�อม"ลในแต่�ละเรคิอร'ด้ได้�อย�างอ!สั่ระ

ม�ร"ปแบบคิ�าสั่��ง DELETE, UPDATE ช้น!ด้พ!เศษที่��ใช้�คิ�ย'เว่!ร'ด้ WHERE CURRENT OF ที่�าให็�เราสั่ามารถแก�ไข ห็ร�อลบเรคิคิอร'ด้ป7จจ$บ�นของเคิอร'เซอร'น��น ๆ ได้� ซ*�งเราจะกล�าว่ถ*งต่�อไป

การท�างานก�บ Transact – SQL Cursorsสั่�าห็ร�บการใช้�งานเคิอร'เซอร' จะม�ข� �นต่อนต่�าง ๆ ด้�งต่�อไปน��1.ประกาศเคอร�เซอร�ดีวยการใช้ค�าส��ง DECLARE ซ*�งเรา

จะต่�องก�าห็นด้ช้น!ด้ของเคิอร'เซอร'ที่��เราจะใช้� ซ*�งเราจะอธ์!บายถ*งรายละเอ�ยด้ของแต่�ละช้น!ด้ในต่อนต่�อไป และเราจะต่�องก�าห็นด้เรคิอร'ด้เซต่ที่��เป2นผ่ลล�พธ์'ของเคิอร'เซอร' ด้�ว่ยคิ�าสั่��ง SELECT สั่�าห็ร�บต่�ว่อย�างคิ�าสั่��งที่��ใช้�ในการประกาศเคิอร'เซอร' เช้�น

DECLARE cust_cursor CURSOR FOR - ประกาศเคิอร'เซอร'ช้��อ cust_cursor

Page 3: DBMS ไทย บทที่ 17 การสร้าง และใช้งานเคอร์เซอร์

SELECT * FROM TblCustomers ORDER by CustomerName - และม�เรคิอร'ด้เซต่จากต่าราง TblCostomers

2.เมื่"�อเราไดีประกาศเคอร�เซอร�ดีวยค�าส��ง DECLARE

แล�ว่ให็�เราเป;ด้เคิอร'เซอร'ข*�นมาด้�ว่ยคิ�าสั่��ง OPEN

OPEN cust_cursor - ท�าการเป$ดีเรคอร�ดีเซตข้&'นมื่า3.หล�งจัากท��เราไดีเป$ดีเคอร�เซอร�ดีวยค�าส��ง OPEN แลว

เราสามื่ารถใช้ค�าส��ง FETCH ยายเคอร�เซอร�ไปย�งต�าแหน*งเรคอร�ดีท��เราตองการไดี เช้�น ไปเรคิอร'ด้ต่�อไป, เรคิอร'ด้ก�อนห็น�าน�� ห็ร�อไปย�งเรคิอร'ด้สั่$ด้ที่�ายก�ได้� ข*�นอย"�ก�บช้น!ด้ของเคิอร'เซอร'ที่��เราได้�ประกาศด้�ว่ยคิ�าสั่��ง DECLARE โด้ยห็ล�งจากที่��เราได้�ใช้�คิ�าสั่��ง FETCH แล�ว่เราสั่ามารถต่รว่จสั่อบสั่ถานการณ'ที่�างานได้�ด้�ว่ยต่�ว่แปร โกลบอลช้��อ @@FETCH_STATUS ด้�งต่�ว่อย�างต่�อไปน��จะที่�าการ FETCH เรคิอร'ด้ต่�อไปเร��อยจนถ*งเรคิอร'ด้สั่$ด้ที่�าย

WHILE (@@FETCH_STATUS = 0) - ต่รว่จสั่อบว่�าสั่ถานะเป2น 0 คิ�อ FETCH ได้�

- ก�ให็�ที่�าต่�อไป จนกระที่��งถ*งเรคิอร'ด้สั่$ด้ที่�ายFETCH NEXT FROM cust_cursor - ย�ายไปเรคิอร'ด้ต่�อไปในเรคิอร'ด้เซต่4.การแกไข้ หร"อลบเรคอร�ดีในตาราง ข้ณะท��เคอร�เซอร�ยาย

ไปมื่าในระหว*างเรคอร�ดี เราที่�าได้�โด้ยใช้�คิ�าสั่��งด้�งต่�อไปน��UPDATE <table_name> SET <column_name>

= <value> WHERE CURRENT OF <cursor_name>DELETE <table_name> WHERE CURRENT OF

<cursor_name>ห็ล�งจากที่��เคิอร'เซอร'ช้��ที่��เรคิอร'ด้ที่��เราต่�องการแล�ว่ เราสั่ามารถ

ใช้�คิ�าสั่��งข�างต่�น แก�ไข ห็ร�อลยเรคิอร'ด้ ณ ต่�าแห็น�งที่��เคิอร'เซอร' ช้��อ cursor_name น��นได้�ช้��ไปได้�

Page 4: DBMS ไทย บทที่ 17 การสร้าง และใช้งานเคอร์เซอร์

5.เมื่"�อเราไมื่*ตองการใช้เคอร�เซอร�แลว ใหใช้ค�าส��ง CLOSE

ป$ดีเคอร�เซอร�ไดี และเราย�งคิงสั่ามารถเป;ด้เคิอร'เซอร'น��นได้�อ�กด้�ว่ยคิ�าสั่��ง OPEN ต่ราบใด้ที่��เราย�งไม�ได้�ปลด้ปล�อยเคิอร'เซอร'ด้�ว่ยคิ�าสั่��ง DEALLOCATE ซ*�งจะยกเล!กการที่�างานของเคิอร'เซอร'น��นอย�างสั่มบ"รณ' (คิ�าสั่��ง DEALLOCATE เป2นคิ�าสั่��งที่��ต่รงก�นข�ามก�บคิ�าสั่��ง DECLARE)

สั่�าห็ร�บต่�ว่อย�างการใช้�งานเคิอร'เซอร'พ��นฐานจะเป2นด้�งต่�อไปน��

คิ�าสั่��งข�างต่�น จะอ�านเรคิอร'ด้ที่��งห็มด้มาจากต่าราง TblCustomers ซ*�งจะเห็ม�อนก�บคิ�าสั่��ง SELECT * FROM

TblCustomers เราจะเห็�นว่�าการใช้�เคิอร'เซอร'น��นจะไม�เห็มาะสั่ม เน��องจาก เคิอร'เซอร'น��นใช้�ร�ซอร'ด้ของระบบมากกว่�า และย�งที่�าให็�ประสั่!ที่ธ์!ภาพในการที่�างานของระบบลด้ลงอ�กด้�ว่ย ด้�งน��น ก�อนที่��เราจะใช้�งานเคิอร'เซอร' เราคิว่รจะแน�ใจว่�าสั่ถานการณ'น��นเห็มาะสั่มที่��จะใช้�เคิอร'เซอร'จร!ง ๆ แล�ว่เที่�าน��น สั่�ว่นรายละเอ�ยด้เราจะกล�าว่ถ*งในต่อนต่�อไป

การประกาศเคอร�เซอร�ดีวยค�าส��ง DECLAREใน SQL Server เราสั่ามารถใช้�เคิอร'เซอร'ได้�ต่ามมาต่รฐานที่��

ก�าห็นด้ใน ANSI – 92 และใน SQL Server ก�ม�คิ�าสั่��งที่��ใช้�ประกาศ

Page 5: DBMS ไทย บทที่ 17 การสร้าง และใช้งานเคอร์เซอร์

เคิอร'เซอร'เป2นของต่�ว่เอง ที่��เราเร�ยกว่�า Transact – SQL

Extended Syntax ซ*�งจะม�ช้น!ด้เคิอร'เซอร'ที่��เพ!�มข*�นมา นอกเห็น�อจากมาต่รฐาน ANSI – 92 อ�กด้�ว่ย ที่�าให็�เราม�คิว่ามย�ด้ห็ย$�น และสั่ามารถคิว่บคิ$มสั่!�งต่�าง ๆ ได้�มากข*�น ซ*�งเราจะอธ์!บายในแต่�ละแบบด้�งต่�อไปน��

1.การประกาศเคอร�เซอร�ตามื่แบบ ANSI – 92

ล�กษณะการที่�างานของเคิเซอร'ต่ามมาต่รฐาน ANSI – 92 จะม�ด้�งรายละเอ�ยด้ด้�งต่�อไปน��

ส*วนประกอบ

ค�าอธิ-บาย

cursor_nameINSENSITIVE

SCROLL

Select_statement

เป2นช้��อของเคิอร'เซอร'เป2นการสั่ร�างต่ารางช้��ว่คิราว่เก�บเรคิอร'ด้เซต่ที่��เราได้�ก�าห็นด้ในคิ�าสั่��ง select_statement ด้�งน��นการแก�ไขข�อม"ลในต่ารางจร!ง จะไม�ม�ผ่ลกระที่บก�บข�อม"ลที่��เคิอร'เซอร'น��ช้��อย"�ที่�าให็�คิ�าสั่��ง FETCH สั่ามารถย�ายไปมาแบบ NEXT, PRIOR, FIRST, LAST, RELATIVE, ABSOLUTE ซ*�งจะที่�าให็�เราสั่ามารถเล��อนเคิอร'เซอร'ไปย�งเรคิอร'ที่��ต่�องการได้� แต่�ถ�าเราไม�ก�าห็นด้จะที่�าให็�เราสั่ามารถใช้�ได้�เพ�ยงคิ�าสั่��ง NEXT เที่�าน��นเป2นคิ�าสั่��งที่��ใช้�สั่ร�างเรคิอร'ด้เซต่ให็�ก�บเคิอร'เซอร' โด้ยคิ�าสั่��ง SELECT น��จะต่�องไม�ม�คิ�ย'เว่!ร'ด้ COMPUTE,

ร�ปแบบDECLARE cursor_nam

[INSENSITIVE] [SCROLL] CURSOR

Page 6: DBMS ไทย บทที่ 17 การสร้าง และใช้งานเคอร์เซอร์

COMPUTE BY FOR BROWSE และ INTO ในการประกาศเคิอร'เซอร'

UPDATE เป2นการก�าห็นด้เคิอร'เซอร'ที่��สั่ามารถแก�ไขข�อม"ลได้� โด้ยที่��เราก�าห็นด้ช้��อคิอล�มภ'ที่��แก�ไขได้� ถ�าเราไม�ก�าห็นด้จะถ�อว่�าสั่ามารถแก�ไขข�อม"ลได้�ที่$กคิอล�มน'

2.การประกาศเคอร�เซอร�ตามื่แบบ Transact – SQL Extended 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 {READ ONLY | UPDATE [OF column_list]}]

คิ�าสั่��งข�างต่�นน�� เราจะอธ์!บายในแต่�ละสั่�ว่นอย�างละเอ�ยด้ต่�อไป ซ*�งเราจะเห็�นได้�ว่�า ร"ปแบบคิ�าสั่��งของ Transact – SQL

Extended จะสั่น�บสั่น$นเคิอร'เซอร'ช้น!ด้ต่�าง ๆ ด้�งต่�อไปน��

เคอร�เซอร�แบบ STATICเคิอร'เซอร'ช้น!ด้น��จะสั่ร�างต่ารางช้��ว่คิราว่ในฐานข�อม"ล tempdb

ข*�นมา เพ��อเก�บข�อม"ลใน เรคิอร'ด้เซต่ที่��ต่รงก�บเง��อนไขที่��เราก�าห็นด้ และเม��อเราใช้�คิ�าสั่��งใด้ก�บเคิอร'เซอร' ม�นก�จะที่�างานก�บต่ารางช้��ว่คิราว่น��แที่น และเน��องจากเราที่�าการเร�ยกข�อม"ลจากเคิอร'เซอร'ที่��ที่�างานก�บข�อม"ลสั่�าเนาซ*�งไม�ใช้�ข�อม"ลจร!ง ด้�งน��น เคิอร'เซอร'ช้น!ด้น��จ*ง

Page 7: DBMS ไทย บทที่ 17 การสร้าง และใช้งานเคอร์เซอร์

เป2นแบบอ�านได้�อย�างเด้�ยว่เที่�าน��น (เพราะไม�ม�สั่าเห็ต่$ที่��ต่�องที่�าการเข�าไปแก�ไขข�อม"ลในต่ารางช้��ว่คิราว่น�� เรพาะไม�ใช้�ต่ารางข�อม"ลจร!ง)

ถ�าเราประกาศเคิอร'เซอร'น��ในแบบ ANSI เราจะต่�องก�าห็นด้คิ�ย'เว่!ร'ด้ INSENSITIVE แต่�ถ�าใช้� Transact – SQL เราสั่ามารถที่��จะประกาศเคิอร'เซอร'น�� โด้ยใช้�คิ�ย'เว่!ร'ด้ STATIC ได้�เลย

- ร�ปแบบการก�าหนดีเคอร�เซอร�แบบ ANSIDECLARE my_cursor1 INSENSITIVE CURSORFOR SELECT ProductID, ProductName FROM TblProductsWHERE CategoryID = 2

- ร�ปแบบการก�าหนดีเคอร�เซอร�แบบ Transact - SQLDECLARE my_cursor1 CURSOR STATICFOR SELECT ProductID, ProductName FROm TbIProductsWHERE CategoryID = 2

ผ่ลล�พธ์'จากเคิอร'เซอร'ที่��งสั่องจะเห็ม�อนก�น แต่�จะแต่กต่�างก�นเล�กน�อย คิ�อ เคิอร'เซอร'แบบ ANSI จะใช้�คิ�าว่�า INSENSITIVE ที่�าให็�พอยเต่อร'เล��อนไปเรคิอร'ด้ข�างห็น�าได้�เพ�ยงอย�างเด้�ยว่เที่�าน��น ถ�าราต่�องการให็�เคิอร'เซอร'สั่ามารถย�ายไปมาได้� (Scrollable) เราจะต่�องก�าห็นด้คิ�ย'เว่!ร'ด้ SCROLL ด้�ว่ยสั่�ว่นการประกาศแบบ Transact –

SQL น��น จะให็�ผ่ลล�พธ์'เป2นเคิอร'เซอร'แบบย�ายไปมาได้�โด้ยอ�ต่โนม�ต่!อย"�แล�ว่

เคอร�เซอร�แบบ KEYSET

เคิอร'เซอร'ช้น!ด้น��จะก�าห็นด้ล�าด้�บต่ายต่�ว่ ด้�ว่ยการสั่ร�างช้$ด้คิ�ย'ที่��ไม�ซ��า (Unique Identifier) ที่��เร�ยกว่�า keyset ในการก�าห็นด้ล�าด้�บ โด้ยจะสั่ร�างเฉพาะสั่�ว่น keyset ในต่ารางช้��ว่คิราว่ในฐานข�อม"ล

Page 8: DBMS ไทย บทที่ 17 การสร้าง และใช้งานเคอร์เซอร์

tempdb เที่�าน��น ไม�เห็ม�อนแบบ STATIC ที่��น�าข�อม"ลที่��งห็มด้มาสั่ร�างเป2นต่ารางช้��ว่คิราว่ในฐานข�อม"ล tempdb

เม��อเราได้�ใช้�คิ�าสั่��ง OPEN เพ��อเป;ด้เคิอร'เซอร'ข*�นมา keyset

จะถ"กสั่ร�างข*�น และก�าห็นด้เป2นล�าด้�บของแต่�ละเรคิอร'ด้ในเคิอร'เซอร'ช้น!ด้น�� ซ*�งถ�าม�การเพ!�ม (Insert) เรคิอร'ด้จากผ่"�ใช้�คิ�าอ��นภายห็ล�งเรคิอร'ด้น��นจะมองไม�เห็�น เพราะไม�ม� keyset ช้��ไปที่��เรคิอร'ด้น��น แต่�ถ�าเป2นการแก�ไข (Update) เรคิอร'ด้จากผ่"�ใช้�คิ�าอ��นในภายห็ล�ง เราจะเห็�นการแก�ไขน��น สั่�ว่นการลบ (Delete) เรคิอร'ด้จากผ่"�ใช้�คินอ��นในภายห็ล�ง ถ�าเราเร�ยกด้"เรคิอร'ด้น��นจะไม�ม� และคิ�าต่�ว่แปร @@FETCH_STATUS จะเป2น – 2

สั่�าห็ร�บต่�ว่อย�างการประกาศเคิอร'เซอร'แบบ KEYSET จะเป2นด้�งต่�อไปน��

DECLARD my_cursor1 CURSOR KEYSETFOR SELECT ProductID, ProductName FROM

TbIProductsWHERE CategoryID = 2

เพราะว่�า keyset จะถ"กเก�บอย"�ใน tempdb เม��อเคิอร'เซอร'ถ"กเป;ด้ข*�นมา และล�าด้�บของแต่�ละเรคิอร'ด้ในเคิอร'เซอร'ไม�ม�การเปล��ยนแปลงเก!ด้ข*�น ที่�าให็�เราสั่ามารถเล��อนเคิอร'เซอร'ช้น!ด้น��ไปย�งต่�าแห็น�งที่��ต่�องการได้�ที่�นที่�ด้�ว่ยคิ�าสั่��ง เช้�น

FETCH ABSOLUTE 10 FROM my_cursor1

จะเห็�นได้�ว่�าเคิอร'เซอร'ช้น!ด้น��จะที่�างานได้�รว่ด้เร�ว่ โด้ยเฉพาะการเร�ยกด้"และแก�ไข เพราะม� keyset เป2นอ!นเด้�กซ'ช้�� และสั่ามารถข�ามมาเรคิอร'ด้ที่��ไม�ใช้� ไปสั่"�เรคิอร'ด้ที่��ต่�องการได้�ที่�นที่�

เคิอร'เซอร'แบบ KEYSET จะต่�องม�ช้$ด้ของคิ�ย'ที่��ไม�ซ��าก�นที่��สั่ามารถอ�างอ!งถ*งแต่�ละเรคิอร'ด้ได้� ด้�งน��น ในที่$ก ๆ เรคิอร'ด้เซต่ของ

Page 9: DBMS ไทย บทที่ 17 การสร้าง และใช้งานเคอร์เซอร์

เคิอร'เซอร'ช้น!ด้น��จะต่�องม� Unique Index (การสั่ร�าง Unique

Index ศ*กษาได้�ในบที่ที่��สั่อนในการที่�าอ!นเด้�กซ')แต่�ถ�าเราประกาศเคิอร'เซอร'ช้น!ด้ KEYSET บนต่ารางที่��ไม�ม�

Unique Index เราจะไม�ได้�เคิอร'เซอร'แบบ KEYSET แต่�ะจได้�เคิอร'เซอร'แบบ STATIC แที่น โด้ยที่��เราจะไม�ได้�ร�บข�อคิว่ามแสั่ด้งข�อผ่!ด้พลาด้ใด้ ๆ นอกจากว่�าเราได้�ใสั่�คิ�ย'เว่!ร'ด้ TYPE_WARNING ลงไปด้�ว่ย ด้�งคิ�าสั่��งต่�ว่อย�างต่�อไปน�� ที่��จะสั่ร�างเคิอร'เซอร'แบบ KEYSET

ข*�นมา ซ*�งถ�าต่าราง TbIProducts ไม�ม� Unique Index SQL

Server ก�จะสั่ร�างเคิอร'เซอร'แบบ STATIC แที่น

DECLARE my_cursor1 CURSOR KEYSET TYPE_WARNING

FOR SELECT ProductID, ProductName FROM TbIProducts

WHERE CategoryID=2

สั่�าห็ร�บใน SQL Server ต่��งแต่�เว่อร'ช้��น 7 เป2นต่�นมา ที่$ก ๆ Clustered Index จะถ"กคิ!ด้ว่�าเป2น Unique เสั่มอด้�ว่ยเห็ต่$ผ่ลน�� เราสั่ามารถที่��จะใช้�เคิอร'เซอร'ช้น!ด้น��บนต่ารางที่$กต่ารางที่��ม� Clustered Index ได้�

เคอร�เซอร�แบบ DYNAMIC

เม��อเราใช้�คิ�าสั่��งที่�างานก�บเคิอร'เซอร'ช้น!ด้น�� คิ�าของเรคิอร'ด้ในเรคิอร'ด้เซต่จะเป2นข�อม"ลล�าสั่$ด้ที่��ถ"กต่�องต่ามข�อม"ลที่��ม�อย"�จร!ง ไม�เห็ม�อนก�บเคิอร'เซอร'แบบ KEYSET ที่��เม��อเราใช้�คิ�าสั่��ง OPEN ก�าห็นด้เรคิอร'ด้เซต่แล�ว่ ถ�าผ่"�ใช้�คินอ��นได้�เพ!�ม ห็ร�อลบเรคิอร'ด้ไป ก�จะไม�ม�ผ่ลกระที่บก�บเรคิอร'ด้เซต่ของเราเลยด้�งที่��ได้�กล�าว่มาแล�ว่ ซ*�งห็มายคิว่ามว่�า จ�านว่นสั่มาช้!กของเคิอร'เซอร'ช้น!ด้น�� จะม�การเปล��ยนแปลงต่ลอด้เว่ลาต่ามข�อม"ลในขณะน��น ซ*�งการเร�ยกข�อม"ลคิร��ง

Page 10: DBMS ไทย บทที่ 17 การสร้าง และใช้งานเคอร์เซอร์

ต่�อไป อาจจะม�เรคิอร'ด้ให็ม�ที่��ต่รงก�บเง��อนไขเพ!�มเข�ามาก�ได้� และเรคิอร'ด้ที่��เคิยเป2นสั่มาช้!กมาก�อน อาจจะไม�เป2นก�ได้� เน��องจากได้�ถ"กแก�ไขให็�ไม�ต่รงต่ามเง��อนไขจากผ่"�ใช้�คินอ��น รว่มถ*งสั่!�งที่��ผ่"�ใช้�งานคินอ��นได้�ที่�าการเปล��ยนแปลงไป ก�จะปรากฏในเคิอร'เซอร'ด้�ว่ย

เน��องจากสั่มาช้!กในเคิอร'เซอร'แบบ DYNAMIC จะเปล��ยนแปลงได้�ต่ลอด้เว่ลาที่��เราที่�างานด้�ว่ย ด้�งน��น จ*งไม�สั่ามารถร�บประก�นได้�ว่�า การเร�ยกห็าข�อม"ลคิร��งต่�อไปที่��ต่�าแห็น�งเด้!ม จะได้�ข�อม"ลที่��เห็ม�อนก�น ด้�ว่ยเห็ต่$น�� ที่�าให็�เราไม�สั่ามารถใช้�คิ�าสั่��ง FETCH

ABSOLUTE ได้� แต่�เคิอร'เซอร'ประเภที่น��ย�งคิงสั่น�บสั่น$นคิ�าสั่��ง FETCH RELATIVE อย"�

สั่�าห็ร�บการประกาศเคิอร'เซอร'แบบ DYNAMIC ให็�เราใช้�คิ�าสั่��งด้�งต่�อไปน��

DECLARE my_cursor1 CURSOR DYNAMICFOR SELECT ProductID, ProductName FROM

TbIProductsWHERE CategoryID=2

เคอร�เซอร�แบบ Fast Forward – Only

คิ�อ เคิอร'เซอร'ที่��รว่มเคิอร'เซอร'แบบ FORWARD_ONLY และเคิอร'เซอร'แบบ READ_ONLY ไว่� เห็มาะสั่�าห็ร�บการอ�านรอบเด้�ยว่ไม�ย�อนกล�บเพราะจะที่�างานได้�เร�ว่มา

DECLARE my_cursor1 CURSOR FAST_FORWARD

FOR SELECT ProductID, ProductName FROM TbIProducts

WHERE CategoryID=2

Note

Page 11: DBMS ไทย บทที่ 17 การสร้าง และใช้งานเคอร์เซอร์

สั่�าห็ร�บการประกาศเคิอร'เซอร'แบบ Transact – SQL น�� ในบางคิร��งเราอาจจะได้�ช้น!ด้ของเคิอร'เซอร'ที่��ไม�ต่รงต่ามที่��เราต่�องการ ห็ากเก!ด้สั่ถานการณ' ด้�งต่�อไปน��

ถ�าคิ�าสั่��ง SELECT ในคิ�าสั่��ง OPEN ที่��ใช้�สั่ร�างเคิอร'เซอร' ได้�อ�านข�อม"ลจากต่าราง 2 ต่ารางข*�นไป โด้ยที่��ม�ต่ารางห็น*�งม�ที่ร!กเกอร'เช้��อมไปย�งอ�กต่ารางห็น*�งที่��ไม�ม�ที่ร!กเกอร' เคิอร'เซอร'น��นก�จะเปล��ยนไปเป2นเคิอร'เซอร'แบบ STATIC

ถ�าคิ�าสั่��ง SELECT ในคิ�าสั่��ง OPEN ที่��ใช้�สั่ร�างเคิอร'เซอร' เป2นคิ!ว่ร�แบบกระจาย (Distributed Query) ที่��อ�างอ!งถ*งต่ารางบนเซ!ร'ฟเว่อร'เคิร��องอ��น เคิอร'เซอร'ที่�ได้�ก�จะเป2นแบบ KEYSET

ถ�าคิ�าสั่��ง SELECT ในคิ�าสั่��ง OPEN ที่��ใช้�สั่ร�างเคิอร'เซอร' ม�การอ�างอ!งถ*งคิอล�มน'ที่��ม�ช้น!ด้ข�อม"ลเป2น text, ntext และ image เคิอร'เซอร'ที่��ได้�ก�จะเป2นเคิอร'เซอร'แบบ DYNAMIC

ถ�าคิ�าสั่��ง SELECT ม�การอ�างอ!งถ*งคิอล�มน'ที่��ม�ช้น!ด้ข�อม"ลเป2น text, ntext และ image และม�คิ�าว่�า TOP อย"� เคิอร'เซอร'ที่�ได้�ก�จะเป2นเคิอร'เซอร'แบบ KEYSET

ถ�าเราสั่งสั่�ยว่�า SQL Server ได้�สั่ร�างช้น!ด้เคิอร'เซอร'ต่ามที่��เราได้�เร�ยกร�องไปห็ร�อไม� เราสั่ามารถใช้� Stored Procedure ที่��เก��ยว่ก�บเคิอร'เซอร' ที่��เราจะกล�าว่ถ*งในสั่�ว่นต่�อไป เพ��อต่รว่จสั่อบคิ$ณสั่มบ�ต่!ของเคิอร'เซอร'ได้�

ร�จั�กก�บ Global ก�บ Local Cursors

โด้ยด้�ฟอลต่�แล�ว่ Transact – SQL Cursors จะเป2นแบบ Global ซ*�งห็มายคิว่ามว่�า เราสั่ามารถที่��จะประกาศม�นในแบต่สั่'ห็น*�งได้� และใช้�ม�นได้�ในอ�กแบต่สั่'ห็น*�งได้� และย�งม�คิว่ามห็มายอ�กว่�า เราสั่ามารถประกาศเคิอร'เซอร'ใน Stored Procedure และเข�าถ*งเคิอร'เซอร'จากภายนอก Stored Procedure ได้� ซ*�งจะไม�เป2นผ่ลด้� เน��องจากว่�า ถ�าม�การเร�ยก Stored Procedure น��นให็ม�อ�กคิร��ง

Page 12: DBMS ไทย บทที่ 17 การสร้าง และใช้งานเคอร์เซอร์

ห็น*�ง จะที่�าให็�เก!ด้ข�อผ่!ด้พลาด้ข*�น เน��องจาก Stored Procedure

จะม�การประกาศเคิอร'เซอร'เด้!มซ��าอ�กนอกจากน�� เราสั่ามารถประกาศเคิอร'เซอร'ให็�เป2นแบบ Local ได้�

ซ*�งจะที่�าให็�เคิอร'เซอร'น��ใช้�ได้�เฉพาะในแบต่สั่', Stored Procedure

ห็ร�อที่ร!กเกอร'ที่��ประกาศไว่�เที่�าน��น จะไม�สั่ามารถอ�างอ!งที่��อ�นได้� สั่�าห็ร�บเคิอร'เซอร'ช้น!ด้ Local น��จะถ"กเคิล�ยร'ออกจากห็น�ว่ยคิว่ามจ�าที่��ม�นใช้�โด้ยอ�ต่โนม�ต่! เม��อแบต่สั่', Store Procedure และ Trigger ที่��ประกาศเคิอร'เซอร'น��น จบการที่�างานแล�ว่

สั่�าห็ร�บการประกาศแบบ Local ให็�เราใสั่�คิ�าสั่��งต่�อไปน��

DECLARE my_cursor1 CURSOR LOCAL STATICFOR SELECT ProductID, ProductName FROM

TbIProductsWHERE CategoryID=2

การก�าหนดีเรคอร�ดีเซตใหก�บเคอร�เซอร�เคิอร'เซอร'จะสั่ามารถใช้�เรคิอร'ด้เซต่ที่��สั่ร�างจากคิ�าสั่��ง SELECT

ห็ร�อเรคิอร'ด้เซต่ที่��ได้�จาก Stored Procedure ก�ได้� สั่�าห็ร�บคิ�าสั่��ง SELECT ที่��สั่ร�างเรคิอร'ด้เซต่ให็�ก�บเคิอร'เซอร'จะไม�อน$ญาต่ให็�ใช้�คิ�ย'เว่!ร'ด้ COMPUTE, COMPUTER BY FOR BROWSE และ INTO

การก�าหนดีล�กษณะอ"�น ๆ ข้องเคอร�เซอร�

ส*วนท��ก�าหนดีเรคอร�ดีใหก�บเคอร�เซอร�

Page 13: DBMS ไทย บทที่ 17 การสร้าง และใช้งานเคอร์เซอร์

จากการประกาศเคิอร'เซอร'แบบ Transact – SQL เราสั่ามารถก�าห็นด้ล�กษณะการย�าย เคิอร'เซอร'ในเรคิอร'ด้เซต่ได้� ซ*�งจะม�อย"� 2 ว่!ธ์� คิ�อ SCROLL และ FORWARD_ONLY โด้ยที่��ด้�ฟอลต่'ของเคิอร'เซอร'แบบ STATIC, KEYSET และ DYNAMIC

จะเป2น SCROLL นอกน��นด้�ฟอลต่'จะเป2น FORWARD_ONLY

(FORWARD_ONLY จะใช้�ได้�เฉพาะคิ�าสั่��ง FETCH NEXT เที่�าน��น สั่�ว่น SCROLL ใช้�ได้�ห็มด้)

READ_ONLY จะที่�าให็�เคิอร'เซอร'น��นไม�สั่ามารถแก�ไข เปล��ยนแปลงข�อม"ลได้� ม�ผ่ลที่�าให็�คิ�าสั่��ง UPDATE ห็ร�อ DELETE ใช้�ก�บเคิอร'เซอร'น��ไม�ได้� ปกต่!เคิอร'เซอร'แบบ FAST_FORWARD จะเป2นคิ�าน��อย"�แล�ว่ แต่�ก�บเคิอร'เซอร'แบบอ��น เช้�น KEYSET ถ�าระบ$คิ�าน��จะที่�าให็�เคิอร'เซอร'แบบ KEYSET น��นกลายเป2นแบบแก�ไขข�อม"ลไม�ได้� (คิ�าน��เป2นด้�เฟอลต่'ของเคิอร'เซอร'แบบ STATIC และ FAST_FORWARD)

SCROLL_LOCKS จะที่�าให็�การแก�ไขห็ร�อลบข�อม"ลผ่�านเคิอร'เซอร'น��ได้�ร�บการการ�นต่�จากระบบว่�าสั่�าเร�จแน�นอน เพราะแถว่เรคิอร'ด้น��นจะถ"กล�อกไว่�เพ��อที่�างานก�บเคิอร'เซอร'น��เที่�าน��น การแก�ไขจากผ่"�อ��นจ*งไม�สั่ามารถที่�าได้� ข�อม"ลที่��แสั่ด้งจะเป2นข�อม"ลที่��แก�ไขล�าสั่$ด้เสั่มอ เราไม�สั่ามารถใช้�ต่�ว่เล�อกน��ก�บเคิอร'เซอร'แบบ FAST_FORWARD

ได้�OPTIMISTIC จะที่�าให็�การแก�ไขห็ร�อลบข�อม"ลผ่�าน

เคิอร'เซอร'น��ล�มเห็ลว่ ถ�าม�ผ่"�อ��นต่�ด้ห็น�าแก�ไขก�อน ต่�ว่เล�อกน��เรคิอร'ด้จะไม�ถ"กล�อก คินอ��น ๆ สั่ามารถเข�ามาอ�านข�อม"ลได้�อ!สั่ระ สั่�ว่นการแก�ไขห็ร�อลบข�อม"ลจะต่รว่จสั่อบคิ�า Timestamp ห็ร�อคิ�า Checksurn ของต่ารางก�อน ซ*�งถ�าคิ�าไม�ต่รงคิ�าเด้!มที่��อ�านมาต่อนแรก การเปล��ยนแปลงแก�ไขห็ร�อลยข�อม"ลผ่�านเคิอร'เซอร'น��ก�จะล�มเห็ลว่ แต่�ถ�าเป2นคิ�าเด้!ม การเปล��ยนแปลงแก�ไขห็ร�อลบข�อม"ลผ่�านเคิอร'เซอร'น��ก�จะสั่�าเร�จ

Page 14: DBMS ไทย บทที่ 17 การสร้าง และใช้งานเคอร์เซอร์

เช้�นก�นเราไม�สั่ามารถใช้�ต่�ว่เล�อกน��ก�บเคิอร'เซอร'แบบ FAST_FORWARD ได้� และคิ�าน��จะเป2นด้�ฟอลต่'ของเคิอร'เซอร'แบบ DYNAMIC และ KEYSET

การเป$ดีเคอร�เซอร�ดีวยค�าส��ง OPENห็ล�งจากที่��เราได้�ประกาศเคิอร'เซอร'ด้�ว่ยคิ�าสั่��ง DECLARE แล�ว่

การใช้�คิ�าสั่��ง OPEN จะเป2นการเป;ด้เคิอร'เซอร'ข*�นมาเพ��อใช้�งาน ซ*�งคิ�าสั่��งน��ม�ร"ปแบบคิ�าสั่��งด้�งต่�อไปน��

ถ�าเราประกาศเคิอร'เซอร'ด้�ว่ยต่�ว่เล�อก INSENSITIVE ห็ร�อ STATIC คิ�าสั่��ง OPEN จะที่�าการสั่ร�างต่ารางช้��ว่คิราว่เพ��อเก�บเรคิอร'ด้เซต่ แต่�ถ�าเคิอร'เซอร'เป2นแบบ KEYSET คิ�าสั่��ง OPEN ก�จะสั่ร�างต่ารางที่��เก�บคิ�าคิ�ย'ที่��ใช้�อ�างอ!งถ*งแต่�ละเรคิอร'ด้ในต่ารางแที่น

เราสั่ามารถใช้�ต่�ว่แปรโกลบอลช้��อ @@CURSOR_ROWS

ห็ล�งจากคิ�าสั่��ง OPEN เพ��อต่รว่จสั่อบจ�านว่นเรคิอร'ด้ที่��ได้�จากการเป;ด้เคิอร'เซอร'

สั่�าห็ร�บคิว่ามห็มายของคิ�า @@CURSOR_ROWS จะเป2นด้�งต่ารางต่�อไปน��

ค*าท��ส*งกล�บมื่า

ค�าอธิ-บาย

ร�ปแบบOPEN

Page 15: DBMS ไทย บทที่ 17 การสร้าง และใช้งานเคอร์เซอร์

- m

- 1

0

n

คิ�อ เคิอร'เซอร'แบบ STATIC ห็ร�อ KEYSET ที่��ถ"กสั่ร�างข*�นมาแบบอะซ!งโคิรไนซ' ที่�าให็�คิ�าที่��ได้�ต่!ด้ลย (m คิ�อ จ�านว่นของเรคิอร'ด้ในเคิอร'เซอร')คิ�อ เคิอร'เซอร'แบบ DYNAMIC ที่��ม�จ�านว่นของเรคิอร'ด้เปล��ยนแปลงได้�ต่ลอด้ ที่�าให็�เราจะไม�สั่ามารถระบ$ได้�ช้�ด้เจนว่�าม�จ�านว่นเรคิอร'ด้เที่�าไรก�นแน�คิ�อ เคิอร'เซอร'น��นไม�ได้�เป;ด้อย"� ห็ร�อไม�ม�เรคิอร'ด้ที่��ต่รงต่ามเง��อนไขของเคิอร'เซอร'น��นคิ�อ เคิอร'เซอร'แบบ STATIC ห็ร�อ KEYSET ที่��ได้�ถ"กสั่ร�างอย�างสั่มบ"รณ'แล�ว่ คิ�าที่��ได้�ต่!ด้บว่ก (n คิ�อ จ�านว่นของเรคิอร'ด้ในเคิอร'เซอร')

เคอร�เซอร�แบบซ-งโครไนซ� และอะซ-งโครไนซ�โด้ยปกต่!เม��อม�การใช้�คิ�าสั่��ง OPEN เราต่�องรอให็�เคิอร'เซอร'น��น

เป;ด้สั่�าเร�จข*�นมาก�อน จ*งที่�างานต่�อไปได้� แต่�ถ�าเราเป;ด้เคิอร'เซอร'ที่��ม�เรคิอร'ด้เซต่จ�านว่นมาก คิ�าสั่��ง OPEN น��จะก!นเว่ลาในการที่�างานนาน ซ*�งเราอาจจะก�าห็นด้ให็� SQL Server สั่ร�างเคิอร'เซอร'แบบอะซ!งโคิรไนซ' (Asynchronous) ก�ได้� ซ*�งจะที่�าให็�เม��อเราใช้�คิ�าสั่��ง OPEN

แล�ว่ ก�สั่ามารถที่�างานต่�อไปได้�ที่�นที่� ถ*งแม�ว่�าเคิอร'เซอร'จะย�งสั่ร�างข*�นมาไม�เสั่ร�จสั่มบ"รณ'ก�ต่าม

สั่�าห็ร�บการสั่ร�างเคิอร'เซอร'แบบอะซ!งโคิรไนซ' ให็�เราใช้�คิ�าสั่��ง sp_configure เปล��ยนแปลงคิ�า Cursor Threshold ให็�เป2นต่ามแบบที่��เราต่�องการ เน��องจากต่�ว่เล�อกน��เป2นต่�ว่เล�อกระด้�บสั่"ง ซ*�งโด้ยที่��ว่ไปจะไม�ปรากฏให็�เห็�น เราจ*งต่�องใช้�คิ�าสั่��งต่�อไปน��ก�าห็นด้ ให็�แสั่ด้งต่�ว่เล�อกระด้�บสั่"งก�อน

EXEC sp_configure ‘show advanced option’, ‘1’/* ก�าห็นด้ให็�แสั่ด้งต่�ว่เล�อกระด้�บสั่"ง */

Page 16: DBMS ไทย บทที่ 17 การสร้าง และใช้งานเคอร์เซอร์

RECONFIGURE /* ที่�าให็�คิ�าที่��ก�าห็นด้ม�ผ่ลใช้�ที่�นที่� */

EXEC sp_configure /* แสั่ด้งรายช้��อต่�ว่เล�อกระด้�บสั่"ง */

โด้ยด้�ฟอลต่�แล�ว่ คิ�า Cursor Threshold น��จะถ"กก�าห็นด้ให็�ม�คิ�าเป2น – 1 ซ*�งห็มายคิว่ามว่�า จะต่�องสั่ร�างเรคิอร'ด้เซต่ให็�เร�ยบร�อยก�อนที่��คิ�าสั่��ง OPEN จะสั่�าเร�จ แล�ว่จ*งให็�ที่�างานต่�อไปได้� ซ*�งเราเร�ยกว่�าเป2นเคิอร'เซอร'แบบซ!งโคิรไนซ' (synchronous) ด้�งคิ�าสั่��งต่�อไปน��

Sp_configure ‘cursor threshold’, -1

แต่�สั่�าห็ร�บเคิอร'เซอร'แบบอะซ!งโคิรไนซ'น�� คิ�าสั่��ง OPEN จะสั่�าเร�จเก�อบที่�นที่�ที่��เร�ยก และเราสั่ามารถใช้�คิ�าสั่��ง Fetch ต่�อได้�ที่�นที่� สั่�าห็ร�บการเป;ด้แบบอะซ!งโคิรไนซ'จะต่�องก�าห็นด้ด้�งต่�อไปน��

Sp_configure ‘cursor thresthole’, 0

อย�างไรก�ต่าม การเป;ด้เคิอร'เซอร�แบบอะซ!งโคิรไนซ'อาจที่�าให็�เราไม�สั่ามารถใช้�คิ�าสั่��งบางคิ�าสั่��งได้� เช้�น เราไม�สั่ามารถเร�ยกคิ�าสั่��ง FETCH LAST ได้�จนกว่�าจะได้�ร�บเรคิอร'ด้จนห็มด้แล�ว่ ซ*�งสั่!�งน��อาจจะไม�จ�าเป2นน�ก ถ�าเราไม�ได้�สั่ร�างเคิอร'เซอร'ที่��ม�ขนาด้ให็ญ� เน��องจากโด้ยที่��ว่ไปเคิอร'เซอร'แบบซ!งโคิรไนซ'ก�ให็�เว่ลาในการต่อบสั่นองการที่�างานที่��ยอมร�บได้�อย"�แล�ว่ แต่�ถ�าเราต่�องการจะเป;ด้เคิอร'เซอร'ขนาด้ให็ญ�จร!ง การที่�าเช้�นน��จะเป2นว่!ธ์�ที่��ที่�าให็�ประสั่!ที่ธ์!ภาพในการที่�างานด้�ข*�น สั่�าห็ร�บการสั่ร�างเคิอร'เซอร'แบบน�� เราจะใช้�ได้�เม��อเราสั่ร�างเคิอร'เซอร'ช้น!ด้ KEYSET ห็ร�อ STATIC เที่�าน��น

Page 17: DBMS ไทย บทที่ 17 การสร้าง และใช้งานเคอร์เซอร์

การเข้าถ&งข้อมื่�ลในเคอร�เซอร�ดีวยค�าส��ง FETCHคิ�าสั่��ง FETCH จะเป2นการเล��อนเคิอร'เซอร'ไปย�งต่�าแห็น�งที่��เรา

ต่�องการในเรคิอร'ด้เซต่ ซ*�งม�ร"ปแบบคิ�าสั่��งต่�อไปน��

ในสั่�ว่น row_selector จะเป2น NEXT, PRIO, FIRST,

ABSOLUTE n ห็ร�อ RELATIVE n ก�ได้� ซ*�งจะม�ล�กษณะการที่�างานของแต่�ละแบบด้�งต่�อไปน��

NEXT ซ*�งเป2นคิ�าด้�ฟอลต่'ของ row_selector โด้ย FETCH NEXT เป2นการเข�าถ*งเรคิอร'ด้ ถ�ด้ไปจากต่�าแห็น�งที่��เคิอร'เซอร'ช้��อย"� ต่�ว่อย�างเช้�นFETCH NEXT FROM Cur_Products

PRIOR เป2นการเข�าถ*งเรคิอร'ด้ก�อนห็น�าต่�าแห็น�งที่��เคิอร'เซอร'ช้��อย"� ต่�ว่อย�างเช้�น

ร�ปแบบFETCH [row_selector FROM] cursor_name

[INTO @variablel, @variable2, …]

Page 18: DBMS ไทย บทที่ 17 การสร้าง และใช้งานเคอร์เซอร์

FETCH PRIOR FROM Cur_Products

FIRST เป2นการเข�าถ*งเรคิอร'ด้แรกสั่$ด้ในเรคิอร'ด้เซต่ ต่�ว่อย�างเช้�นFETCH FIRST FROM Cur_Products

LAST เป2นการเข�าถ*งเรคิอร'ด้สั่$ด้ที่�ายในเรคิอร'ด้เซต่ ต่�ว่อย�างเช้�นFETCHLAST FROM Cur_Products

ABSOLUTE n เป2นการสั่�งเรคิอร'ด้ล�าด้�บที่�� n น�บจากเรคิอร'ด้แรกกล�บมา โด้ยที่��ถ�าเราก�าห็นด้คิ�า n เป2นลบ จะเป2นการน�บล�าด้�บเรคิอร'ด้จากต่�าแห็น�งสั่$ด้ที่�ายของเรคิอร'ด้เซต่ที่��ได้�แที่น ต่�ว่อย�างเช้�นFETCH ABSOLUTE 3 FROM Cur_Products /*

จะเป2นการเข�าถ*งเรคิอร'ด้ที่�� 3 จาก เรคิอร'ด้แรก */FETCH ABSOLUTE -1 FROM Cur_Products /*

จะเป2นการเข�าถ*งเรคิอร'ด้ที่�� 1 จาก เรคิอร'ด้สั่$ด้ที่�าย */

Page 19: DBMS ไทย บทที่ 17 การสร้าง และใช้งานเคอร์เซอร์

RELATIVE n จะเป2นการสั่�งเรคิอร'ด้ที่�� n น�บจากเรคิอร'ด้ที่��เคิอร'เซอร'ช้��อย"� ณ ป7จจ$บ�นกล�บมา โด้ยถ�าเราก�าห็นด้คิ�า n

เป2นลบ จะเป2นการน�บถอยห็ล�งจากเรคิอร'ด้ที่��ถ"กช้��อย"� ด้�งต่�ว่อย�างต่�อไปน��FETCH RELATIVE 5 FROM Cur_Products /*

จะเป2นการเข�าถ*งเรคิอร'ด้ที่�� 5 ห็ล�งจากเรคิอร'ด้ป7จจ$บ�น */FETCH RELATIVE -3 FROM Cur_Products /*

จะเป2นการเข�าถ*งเรคิอร'ด้ที่�� 3 ก�อนห็น�าเรคิอร'ด้ป7จจ$บ�น */

คิ�าสั่��ง FETCH แบบ NEXT, PRIOR ก�บ RELATIVE จะเป2นเล��อนต่�าแห็น�งเคิอร'เซอร'โด้ยเปร�ยบเที่�ยบก�บต่�าแห็น�งของเคิอร'เซอร'ในป7จจ$บ�น

สั่�าห็ร�บคิ�า n ที่��เราก�าห็นด้ใน RELATIVE และ ABSOLUTE

จะสั่ามารถเป2นต่�ว่แปรแบบโลคิอล ห็ร�อพาราม!เต่อร'ที่��ม�ช้น!ด้ข�อม"ลเป2น bitint, integer, smallint ห็ร�อ tinyint ก�ได้�

เม��อเคิอร'เซอร'ได้�ที่�าการเป;ด้ข*�นมา เคิอร'เซอร'จะอย"�ที่��ต่�าแห็น�งก�อนเรคิอร'ด้แรก ซ*�งจะที่�าให็�เก!ด้สั่!�งต่�าง ๆ ด้�งต่�อไปน��

คิ�าสั่��ง FETCH NEXT จะให็�ผ่ลการที่�างานเห็ม�อนก�บคิ�าสั่��ง FETCH FIRST คิ�อ จะไปย�งเรคิอร'ด้แรก

คิ�าสั่��ง FETCH PRIOR จะไม�สั่�งเรคิอร'ด้กล�บมา FETCH RELATIVE จะเห็ม�อนก�บ FETCH

ABSOLUTE ถ�าคิ�า n เป2นคิ�าบว่ก แต่�ถ�าคิ�า n เป2นคิ�าลบ ห็ร�อ 0 ก�จะไม�ม�เรคิอร'ด้ใด้สั่�งกล�บมา

การที่��เคิอร'เซอร'เล��อนไปจนอย"�ที่��ต่�าแห็น�งก�อนเรคิอร'ด้แรกห็ร�อห็ล�งเรคิอร'ด้สั่$ด้ที่�าย ก�จะที่�าให็�คิ�า @@FETCH_STATUS สั่�งคิ�า – 1 กล�บมา ซ*�งคิา @@FETCH_STATUS น��จะม�คิว่ามห็มายด้�งต่�อไปน��

ค*าท��ส*ง ค�าอธิ-บาย

Page 20: DBMS ไทย บทที่ 17 การสร้าง และใช้งานเคอร์เซอร์

กล�บมื่า0

- 1

- 2

คิ�าสั่��ง FETCH ที่��เราเร�ยกที่�างานได้�สั่�าเร�จคิ�าสั่��ง FETCH ที่�างานไม�สั่�าเร�จ ซ*�งอาจจะเก!ด้จากการที่��เคิอร'เซอร'ไปอย"�ที่��ต่�าแห็น�งเรคิคิอร'ด้แรก ห็ร�อเรคิอร'ด้สั่$ด้ที่�ายไปแล�ว่ แล�ว่เล��อนไปย�งเรคิอร'ด้ก�อนห็น�า ห็ร�อเรคิอร'ด้ต่�อไปไม�ได้�เรคิอร'ด้ที่��เราจะเข�าถ*งได้�ถ"กลบโด้ยผ่"�ใช้�งานคินอ��นไปแล�ว่ ถ�าเราต่�องการให็�ม�นไม�ปรากฏ เม��อผ่"�ใช้�คินอ��นได้�ที่�าการแก�ไขไป เราก�คิว่รจะใช้�เคิอร'เซอร'แบบ DYNAMIC แที่น

การแกไข้สมื่าช้-กในเคอร�เซอร�ดีวยค�าส��ง UPDATEคิ�าสั่��ง UPDATE จะที่�าการแก�ไขเรคิอร'ด้ที่��ต่�าแห็น�งป7จจ$บ�น

ของเคิอร'เซอร' ซ*�งช้น!ด้ของ เคิอร'เซอร'ที่��เราแก�ไขข�อม"ลได้� ก�คิ�อ เคิอร'เซอร'แบบ KEYSET ก�บ DYNAMIC โด้ยม�ร"ปแบบคิ�าสั่��งด้�งน��

คิ�าสั่��งน��จะที่�างานก�บเรคิอร'ด้ที่��ต่�าแห็น�งป7จจ$บ�นของเคิอร'เซอร' และจะม�ผ่ลกระที่บเพ�ยงแคิ� เรคิอร'ด้เด้�ยว่เที่�าน��น ด้�งต่�ว่อย�างต่�อไปน�� ซ*�งใช้�คิ�าสั่��ง FETCH ABSOLUTE เล��อนไปเรคิอร'ด้ที่�� 3 ในเรคิอร'ด้เซต่และที่�าการเพ!�มคิ�าฟ;ลด้' UnitPrice ข*�นไป 10 เปอร'เซ�นต่'

ร�ปแบบUPDAT table_name SET assignmet_list WHERE CURRENT OF cursor_name

Page 21: DBMS ไทย บทที่ 17 การสร้าง และใช้งานเคอร์เซอร์

การลบสมื่าช้-กในเคอร�เซอร�ดีวยค�าส��ง DELETEคิ�าสั่��ง DELETE จะเป2นการลบเรคิอร'ด้ที่��ต่�าแห็น�งป7จจ$บ�นของ

เคิอร'เซอร' ซ*�งช้น!ด้ของ เคิอร'เซอร'ที่��เราลบข�อม"ลได้� ก�คิ�อ เคิอร'เซอร'แบบ KEYSET ก�บ DYNAMIC โด้ยม�ร"ปแบบคิ�าสั่��งด้�งน��

ร�ปแบบDELETE FROM table_name WHERE CURRENT

OF cursor_nameต่�ว่อย�างการใช้�งานคิ�าสั่��งข�างต่�น เช้�นDELETE FROM TbIProducts WHERE CURRENT

OF Cur_Productsคิ�าสั่��งข�างต่�นน��จะลบเรคิอร'ด้ ณ ต่�าแห็น�งป7จจ$บ�นในเคิอร'เซอร'

ช้��อ Cur_Products ออกไปจากต่าราง TbIProducts

Note

Page 22: DBMS ไทย บทที่ 17 การสร้าง และใช้งานเคอร์เซอร์

จากต่�ว่อย�างที่��ผ่�านมาจะเห็�นว่�า เม��อถ*งเว่ลาที่�างานก�บข�อม"ล เราก�จะใช้�คิ�าสั่��งเด้!ม ๆ ที่��เราคิ$�นเคิย เช้�น UPDATE ห็ร�อ DELETE แล�ว่ใช้�เคิอร'เซอร'ช้�ว่ยช้��ต่�าแห็น�งแถว่ที่��ต่�องการแก�ไข (สั่�ว่น INSERT เราไม�ต่�องระบ$แถว่ด้�ว่ยเคิอร'เซอร' เพราะเป2นการสั่ร�างแถว่เพ!�ม ไม�ใช้�การที่�างานก�บแถว่เด้!ม) ด้�งน��น ในต่�ว่เคิอร'เซอร'จ*งเก�บเพ�ยงข�อม"ลต่�าแห็น�งแถว่ เพ��อใช้�อ�างอ!งไปถ*งแถว่ข�อม"ลจร!งเที่�าน��น ไม�ได้�เก�บแถว่ข�อม"ลจร!งเอาไว่� การป$ดีเคอเซอร�ดีวยค�าส��ง CLOSE

คิ�าสั่��ง CLOSE จะป;ด้เคิอร'เซอร'ที่��ได้�เป;ด้ข*�นมาด้�ว่ยคิ�าสั่��ง OPEN ซ*�งม�ร"ปแบบคิ�าสั่��งต่�อไปน��

ห็ล�งจากที่��ได้�สั่� �งป;ด้เคิอร'เซอร'แล�ว่ เราจะไม�สั่ามารถที่�างานใด้ ๆ ก�บเรคิอร'ด้เซต่ในเคิอร'เซอร'น��นได้�อ�กต่�อไป แต่�เราสั่ามารถที่��จะ OPEN เคิอร'เซอร'น��นซ��าได้� โด้ยไม�ต่�อง DECLARE ให็ม�

ต่�ว่อย�างการใช้�คิ�าสั่��งน�� เช้�นCLOSE Cur_Products

มาต่รฐาน ANSI ได้�ก�าห็นด้ไว่�ว่�า เคิอร'เซอร'จะต่�องป;ด้เม��อม�การเร�ยกคิ�าสั่��ง COMMIT TRANSACTION แต่�สั่!�งน��ไม�ใช้�คิ�าด้�ฟอลต่'ของ SQL Server เน��องจากม�นไม�เห็มาะสั่มที่��จะให็� เคิอร'เซอร'ป;ด้ไปโด้ยอ�ต่โนม�ต่! ในขณะที่��การที่�างานในที่รานเซคิช้��นจบลง แต่�ถ�าเราต่�องการให็�การใช้�คิ�าสั่��งสั่อด้คิล�องก�บมาต่รฐานของที่าง ANSI ก�สั่ามรถที่��จะใช้�คิ�าสั่��ง SET ด้�งต่�อไปน��

ร�ปแบบCLOSE

ร�ปแบบSET

Note

Page 23: DBMS ไทย บทที่ 17 การสร้าง และใช้งานเคอร์เซอร์

การบอกเล-กใช้เคอร�เซอร�ดีวยค�าส��ง DEALLOCATEคิ�าสั่��ง DEALLOCATE จะที่�าการลบเคิอร'เซอร'ออกจากห็น�ว่ย

คิว่ามจ�า ซ*�งม�ร"ปแบบคิ�าสั่��งด้�งต่�อไปน��

ร�ปแบบDEALLOCATE cursor_nameต่�ว่อย�างการใช้�คิ�าสั่��งน�� เช้�นDEALLOCAT Cur_Products

ถ�าเคิอร'เซอร'ได้�ถ"กปลด้ปล�อยแล�ว่ เราจะไม�สั่ามารถ OPEN

เคิอร'เซอร'น��นได้�อ�ก จนกว่�าเราจะได้�ประกาศเคิอร'เซอร'น��นให็ม�ด้�ว่ยคิ�าสั่��ง DECLARE

คิ�าสั่��ง DEALLOCATE ใช้�สั่�าห็ร�บคิ�นพ��นที่��ในห็น�ว่ยคิว่ามจ�าที่��เคิอร'เซอร'ใช้�ไปคิ�นกล�บมา เพ��อน�าไปใช้�งานอ��นต่�อได้�

ต�วอย*างการใช้งานเคอร�เซอร�ในแบบต*าง ๆสั่�าห็ร�บในห็�ว่ข�อน�� เราจะน�าคิว่ามร" �ต่�าง ๆ ที่�ได้�เร�ยนไปแล�ว่ มา

ประย$กต่'ใช้�งานในล�กษณะต่�าง ๆ ด้�งต่�ว่อย�างต่�อไปน��

ต�วอย*างท�� 1 การประกาศเคอร�เซอร�ท��ง*ายท��ส1ดีสั่�าห็ร�บการประกาศเคิอร'เซอร'แบบที่��ง�ายที่��สั่$ด้ ในต่�ว่อย�างน��จะ

เป2นการประกาศเคิอร'เซอร'ช้น!ด้ DYNAMIC ซ*�งเคิอร'เซอร'แบบน��จะเป2นแบบด้�ฟอลต่' ถ�าเราไม�ก�าห็นด้ช้น!ด้ของเคิอร'เซอร'ที่��เราต่�องการไป

เราสั่ามารถประกาศเคิอร'เซอร'ได้� ด้�งคิ�าสั่��งต่�อไปน��

Page 24: DBMS ไทย บทที่ 17 การสร้าง และใช้งานเคอร์เซอร์

สั่�าห็ร�บเคิอร'เซอร'ในต่�ว่อย�างน�� เราไม�ได้�ก�าห็นด้ร"ปแบบการขย�บของเคิอร'เซอร' ด้�งน��น โด้ยด้�ฟอลต่'จะเป2น FORWARD_ONLY และถ�าเราพยายามที่�าการ FETCH ใด้ ๆ นอกจากคิ�าสั่��ง FETCH NEXT

จากเคิอร'เซอร'น�� เราจะได้�ร�บข�อผ่!ด้พลาด้ด้�งต่�อไปน��

เคิอร'เซอร'ช้น!ด้น��สั่ามารถเล��อนเคิอร'เซอร'ไปย�งเรคิอร'ด้ต่�อไปได้�เพ�ยงอย�างเด้�ยว่ ไม�สั่ามารถเล��อนกล�บไปย�งเรคิอร'ด้ก�อนห็น�าน��ได้� นอกจากว่�าเราได้�ก�าห็นด้ใช้�คิ�ย'เว่!ร'ด้ SCROLL ลงไปในต่�ว่อย�างต่�อไป

Page 25: DBMS ไทย บทที่ 17 การสร้าง และใช้งานเคอร์เซอร์

ต�วอย*างท�� 2 การประกาศเคอร�เซอร�ท��สามื่ารถเล"�อนเคอร�เซอร�ไปมื่าไดีอย*างอ-สระ

ต่�ว่อย�างน�� เราจะประกาศเคิอร'เซอร'แบบใช้�คิ�ย'เว่!ร'ด้ SCROLL

m ที่�าให็�เราสั่ามารถใช้�คิ�าสั่��ง FETCH ได้�ที่$กแบบ เพ��อย�ายเคิอร'เซอร'ไปย�งต่�าแห็น�งที่��เราต่�องการในเรคิอร'ด้เซต่ได้�

ต�วอย*างท�� 3 การประกาศเคอร�เซอร�แบบ KEYSET

Page 26: DBMS ไทย บทที่ 17 การสร้าง และใช้งานเคอร์เซอร์

ต่�ว่อย�างน�� เป2นการประกาศเคิอร'เซอร'แบบ KEYSET ซ*�งเราสั่ามารถใช้�คิ�าสั่��ง FETCH ได้�ที่��งห็มด้ รว่มที่��งสั่ามารถแก�ไข และลบข�อม"ลได้� โด้ยเม��อใช้�คิ�าสั่��ง OPEN เป;ด้เคิอร'เซอร'น�� สั่มาช้!กในเคิอร'เซอร'จะไม�ม�การเปล��ยนแปลง ถ*งแม�จะม�ผ่"�ใช้�งานคินอ��นเพ!�มเรคิอร'ด้ที่��ต่รงก�บเง��อนไขเข�ามาก�ต่าม ถ�าเราต่�องการให็�เคิอร'เซอร'เปล��ยนแปลงข�อม"ลต่ามที่��ผ่"�ใช้�คินอ��นได้�แก�ไขน��น เราจะต่�องใช้� เคิอร'เซอร'แบบ DYNAMIC แที่น

ต�วอย*างท�� 4 การประกาศเคอร�เซอร�แบบ DYNAMIC

ต่�ว่อย�างน�� เป2นการประกาศเคิอร'เซอร'ช้น!ด้ DYNAMIC ซ*�งเราสั่ามารถใช้�คิ�าสั่��ง FETCH ได้�ที่��งห็มด้ รว่มที่��งสั่ามารถแก�ไขและลบข�อม"ลได้� สั่�าห็ร�บต่�ว่อย�างน��จะแสั่ด้งให็�เห็�นว่�า เม��อเราใช้�คิ�าสั่��ง OPEN

เป;ด้เคิอร'เซอร'แบบน��แล�ว่ สั่มาช้!กในเคิอร'เซอร'จะเปล��ยนแปลงไปต่าม

Page 27: DBMS ไทย บทที่ 17 การสร้าง และใช้งานเคอร์เซอร์

ข�อม"ลจร!งในขณะน��น โด้ยถ�าม�ผ่"�ใช้�งานคินอ��นเพ!�มเรคิอร'ด้ที่��ต่รงก�บเง��อนไขเข�ามา เราก�จะเห็�นในเคิอร'เซอร'ของเราได้�ด้�ว่ย ด้�งร"ป

การสรางต�วแปรเคอร�เซอร� (Cursor variables)SQL Server อน$ญาต่ให็�เราประกาศต่�ว่แปรช้น!ด้เคิอร'เซอร'ได้�

นอกจากช้น!ด้ข�อม"ลพ��นฐานที่��เราได้�กล�าว่มาแล�ว่ในบที่ต่�น ๆ ในการก�าห็นด้คิ�าให็�ก�บต่�ว่แปรเคิอร'เซอร' เราจะใช้�คิ�าสั่��ง SET ด้�งต่�อไปน�� โด้ยที่��ต่�ว่แปรเคิอร'เซอร'จะไม�สั่ามารถก�าห็นด้คิ�าได้�ด้�ว่ยคิ�าสั่��ง SELECT อย�างที่��ต่�ว่แปรที่��ว่ไปที่�าได้�

SET @cursor_var = declared_cursor-- ก�าห็นด้ให็�ต่�ว่แปรเคิอร'เซอร' cursor_var

-- ช้��ไปย�งเคิอร'เซอร'ช้��อ declard_cursor

Page 28: DBMS ไทย บทที่ 17 การสร้าง และใช้งานเคอร์เซอร์

ต่�ว่แปรเคิอร'เซอร'สั่ามารถใช้�เป2นพาราม!เต่อร'เอาที่'พ$ต่ใน Stored procedure ได้� และสั่มารถใช้�คิ�าสั่��งต่�าง ๆ เก��ยว่ก�บการจ�ด้การเคิอร'เซอร'ที่��เรากล�าว่มาแล�ว่ได้� แต่�เราจะไม�สั่ามารถก�าห็นด้ให็�คิอล�มน'ในต่ารางม�ช้น!ด้ข�อม"ลเป2นเคิอร'เซอร'ได้� และพาราม!เต่อร'แบบอ!นพ$ต่ใน Stored Procedure จะไม�สั่ามารถม�ช้น!ด้ข�อม"ลเป2นเคิอร'เซอร'ได้�

คิ�าสั่��งต่�อไปน��จะประกาศเคิอร'เซอร' และก�าห็นด้ให็�ต่�ว่แปรเคิอร'เซอร'อ�างอ!งถ*งเรคิอร'ด้เซต่เด้�ยว่ก�น

DECLAR declard_cursor CURSOr -- ประกาศเคิอร'เซอร'ช้��อ declared_cursor

FOR SELECT au_lname FROM authorsDECLARE @cursor_var cursor -- ประกาศ

ต่�ว่แปรเคิอร'เซอร'ช้��อ cursor_var

SET @cursor_var = declared_cursor--ก�าห็นด้ให็�ต่�ว่แปรเคิอร'เซอร' cursor_var

-- ช้��ไปย�งเคิอร'เซอร'ช้��อ declared_cursor

เคิอร'เซอร'ที่��ถ"กประกาศแบบธ์รรมด้าและต่�ว่แปรเคิอร'เซอร'จะเห็ม�อนก�นมาก เราสั่ามารถที่�างานก�บเคิอร'เซอร' ห็ร�อต่�ว่แปรเคิอร'เซอร'ก�ได้� เพราะที่��งสั่องแบบจะอ�างอ!งไปที่��โคิรงสั่ร�างเด้�ยว่ก�น เราสั่ามารถเป;ด้เคิอร'เซอร'โด้ยใช้�คิ�าสั่��งต่�อไปน��

OPEN @cursor_var -- เป;ด้เคิอร'เซอร'ผ่�านต่�ว่แปรเคิอร'เซอร'

ห็ล�งจากที่��ได้�ที่�าการเป;ด้เคิอร'เซอร'ไปแล�ว่ ถ�าเราพยายามที่��จะเป;ด้เคิอร'เซอร'อ��นที่��ช้��ไปที่��โคิรงสั่ร�างเด้�ยว่ก�นด้�ว่ยคิ�าสั่��งน�� ก�จะที่�าให็�เก!ด้ข�อผ่!ด้พลาด้ที่��บอกว่�าเคิอร'เซอร'น��นได้�ถ"กเป;ด้อย"�แล�ว่ เน��องจากม�นได้�อ�างอ!งไปที่��โคิรงสั่ร�างอ�นเด้�ยว่ก�น

Page 29: DBMS ไทย บทที่ 17 การสร้าง และใช้งานเคอร์เซอร์

OPEN declared_cursor

ส�าหร�บค�าส��งต*าง ๆ ท��ท�างานก�บเคอร�เซอร� เมื่"�อน�ามื่าใช้ก�บต�วแปรเคอร�เซอร�จัะเป2นดี�งต*อไปน�'

เราสั่ามารถใช้�คิ�าสั่��ง FETCH ก�บต่�ว่แปรเคิอร'เซอร'ได้�เห็ม�อนก�บเคิอร'เซอร'ที่��เราประกาศที่��ว่ไป

คิ�าสั่��ง CLOSE ก�ใช้�ได้� และเราจะไม�สั่ามารถที่�าการ FETCH

ต่�อได้�เม��อเราป;ด้ม�นไปแล�ว่ คิ�าสั่��ง DEALLOCATE ถ�าใช้�ก�บต่�ว่แปรเคิอร'เซอร'ใด้ ก�จะ

ห็มายคิว่ามว่�า เราไม�สั่ามารถเข�าใช้�ต่�ว่แปรเคิอร'เซอร'ต่�ว่น��นได้� แต่�โคิรงสั่ร�างของเคิอร'เซอร'ในห็น�ว่ยคิว่ามจ�าจะไม�ถ"กปล�อย ต่�องรอจนกระที่��งต่�ว่แปรเคิอร'เซอร'ต่�ว่สั่$ด้ที่�ายที่��ช้��ไปที่��โคิรงสั่ร�างน��นถ"กปลด้ปล�อย ต่�ว่อย�างเช้�น ถ�าเราใช้�คิ�าสั่��ง DEALLOCATE ก�บ declared_cursor แล�ว่ เราย�งสั่ามารถที่�างานก�บโคิรงสั่ร�างของ declared_cursor ได้�โด้ยอ�างอ!งผ่�านเคิอร'เซอร' @var_a_cursor ห็ร�อ @var_b_cursor

การแสดีงรายละเอ�ยดีเก��ยวก�บเคอร�เซอร�

Page 30: DBMS ไทย บทที่ 17 การสร้าง และใช้งานเคอร์เซอร์

ต่ามที่��เราได้�ศ*กษาเก��ยว่ก�บเคิอร'เซอร'มาจนถ*งต่อนน�� เราสั่ามารถใช้�ฟ7งก'ช้�นต่�าง ๆ ที่��ให็�ข�อม"ลเก��ยว่ก�บเคิอร'เซอร' เพ��อต่รว่จสั่อบสั่ถานการณ'ที่�างานของเคิอร'เซอร'ได้� ซ*�งฟ7งก'ช้�นต่�าง ๆ ม�ด้�งต่�อไปน��

ตรวจัสอบสถานะข้องเคอร�เซอร�ดีวยฟั4งก�ช้�น CURSOR_STATUS

ฟ7งก'ช้�น CURSOR_STATUS จะบอกสั่ถานะของเคิอร'เซอร' ห็ร�อต่�ว่แปรเคิอร'เซอร' โด้ยจะสั่�งคิ�าที่��แต่กต่�างก�นกล�บมาได้� 5 คิ�า ซ*�งคิว่ามห็มายแต่�ละคิ�าจะข*�นอย"�ก�บว่�าพาราม!เต่อร'ที่��สั่�งไปเป2นช้��อเคิอร'เซอร' ห็ร�อต่�ว่แปรเคิอร'เซอร'

ฟ7งก'ช้�นน��ม�พาราม!เต่อร' 2 ต่�ว่ โด้ยพาราม!เต่อร'ต่�ว่แรกจะบอกว่�า เคิอร'เซอร'ที่��เราต่�องการที่ราบข�อม"ลเป2นเคิอร'เซอร'แบบ Global,

Local ห็ร�อเป2นต่�ว่แปรเคิอร'เซอร' ด้�งร"ปแบบต่�อไปน��

ร�ปแบบCURSOR_STATUS ({‘local’, ‘cursor_name’}|

{‘globla’, ‘cursor_name’} | {‘variable’, ‘cursor_variable’})

คิว่ามห็มายของคิ�าที่��สั่�งกล�บจาก CURSOR_STATUS จะเป2นด้�งต่ารางต่�อไปน��

ค*าท��ส*งกล�บมื่า

ความื่หมื่าย

1 เคิอร'เซอร'เป;ด้ข*�นมาอย"� และสั่�าห็ร�บเคิอร'เซอร'ช้น!ด้

Page 31: DBMS ไทย บทที่ 17 การสร้าง และใช้งานเคอร์เซอร์

0- 1

STATIC ห็ร�อ KEYSET เรคิอร'ด้เซต่จะม�อย�างน�อยห็น*�งเรคิอร'ด้ สั่�าห็ร�บเคิอร'เซอร'แบบ DYNAMIC เรคิอร'ด้เซต่อาจจะม�จ�านว่นเป2น 0, 1 ห็ร�อมากกว่�าก�ได้�เคิอร'เซอร'ถ"กเป;ด้อย"� แต่�ไม�เรคิอร'ด้ในเรคิอร'ด้เซต่เคิอร'เซอร'ถ"กป;ด้อย"�

- 2- 3

ไม�สั่ามารถบอกสั่ถานะที่��แน�นอนของเคิอร'เซอร'ได้�เคิอร'เซอร'ช้��อน��ไม�ได้�ประกาศข*�นมาด้�ว่ยคิ�าสั่��ง DECLARE

การแสดีงรายละเอ�ยดีข้องเคอร�เซอร�ดีวยค�าส��ง sp_describe-cursor

เราสั่ามารถใช้� Stored Procedure ช้��อ sp_describe_cursor แสั่ด้งรายละเอ�ยด้เก��ยว่ก�บเคิอร'เซอร'ได้� โด้ยที่�� Stored Procedure น�� จะสั่�งผ่ลล�พธ์'กล�บมาอย"�ในร"ปของต่�ว่แปรเคิอร'เซอร' ที่��เราจะต่�องแปลคิว่ามห็มายจากเคิอร'เซอร'ที่��สั่�งกล�บมาอ�กที่�ห็น*�ง

สั่�าห็ร�บร"ปแบบของ Stored Procedure เป2นด้�งต่�อไปน��

Page 32: DBMS ไทย บทที่ 17 การสร้าง และใช้งานเคอร์เซอร์

ร�ปแบบsp_describe_cursor [@cursor_return =]

output_cursor_variable OUTPUT{ [, [@cursor_source =] ‘local’,

[@cursor_identity =] ‘local_cursor_name’] | --สั่�าห็ร�บเคิอร'เซอร'แบบ local

[ [, [@cursor_source =] ‘global’, [@cursor_identity =] ‘local_cursor_name’] | --สั่�าห็ร�บเคิอร'เซอร'แบบ global

{ [, [@cursor_source =] ‘variable’, [@cursor_identity =] ‘input_cursor_variable] | -- สั่�าห็ร�บต่�ว่แปรเคิอร'เซอร'

ต่�อไปน��จะเป2นต่�ว่อย�างของการใช้� Stored Procedure ข�างต่�น และแสั่ด้งข�อม"ลในต่�ว่แปรเคิอร'เซอร'ที่��สั่�งกล�บมา โด้ยที่�� Cur_Products เป2นเคิอร'เซอร'ที่��เราต่�องการที่ราบรายละเอ�ยด้ และ @Report เป2นต่�ว่แปรเคิอร'เซอร'ที่��จะเก�บผ่ลล�พธ์'

Page 33: DBMS ไทย บทที่ 17 การสร้าง และใช้งานเคอร์เซอร์

สั่�าห็ร�บผ่ลล�พธ์'ของคิ�าสั่��งข�าต่�น จะเป2นด้�งน��

ต่ารางต่�อไปน�� จะแสั่ด้งคิว่ามห็มายของคิ�าแต่�ละฟ;ล'มที่��สั่�งกล�บมาจาก sp_describe_cursor

ฟั4งก�ช้��น ค*าท��ส*งกล�บมื่าCURSOR_SCOP 1 = LOCAL 2 = GLOBAL

Page 34: DBMS ไทย บทที่ 17 การสร้าง และใช้งานเคอร์เซอร์

ESTATUS

MODELCONCURRENCYSCROLLABLEOPEN_STATUSCURSOR_ROWS

FETCH_STATUS

จะม�คิ�าเด้�ยว่ก�บที่��รายงานด้�ว่ยฟ7งก'ช้�น CURSOR_STATUS ที่��ได้�กล�าว่มาแล�ว่1 = Static 2 = Scroll licks 3 = Optimistic1 = Read – only 2 = Scroll locks 3 = Optimistic0 = Forward – only 1 = Scrollabel0 = Closed 1 = Openจ�านว่นของเรคิอร'ด้ในเรคิอร'ด้เซต่ ซ*�งเห็ม�อนก�บคิ�า @@CURSOR_ROWS

สั่ถานะของการ Fetch คิร��งสั่$ด้ที่�ายบนเคิอร'เซอร'น�� ซ*�งเห็ม�อนก�บคิ�า

COLUMN_COUNTROW_COUNT

LAST_OPERATION

@@FETCH_STATUS โด้ยที่��0 = การ Fetch เสั่ร�จสั่มบ"รณ'- 1 = การ Fetch ไม�สั่�าเร�จ- 2 = เรคิอร'ด้ที่��เร�ยกไม�ถ"กลบออกจากเรคิอร'ด้เซต่ โด้ยผ่"�ใช้�งานคินอ��น- 9 = ไม�ม�การ Fetch เก!ด้ข*�นจ�านว่นของคิอล�มน'ในเรคิอร'ด้เซต่จ�านว่นของเรคิอร'ด้ที่��กระที่บจากคิ�าสั่��งคิร��งสั่$ด้ที่�ายบนเคิอร'เซอร' ซ*�งเห็ม�อนก�บฟ7งก'ช้�น @@ROWCOUNTการกระที่�าคิร��งสั่$ด้ที่�ายที่��ที่�าบนเคิอร'เซอร'0 = ย�งไม�ม�การที่�าอะไรก�บเคิอร'เซอร'1 = OPEN 2 = FETCH3 = INSERT 4 = UPDATE5 = DELETE 6 = CLOSE7 = DEALLOCATE

Page 35: DBMS ไทย บทที่ 17 การสร้าง และใช้งานเคอร์เซอร์