เคอร์เซอร์ในขั้นตอนการจัดเก็บ MySQL กฎทั่วไปของคำสั่ง DECLARE CURSOR เคอร์เซอร์ sql คืออะไร
เคอร์เซอร์ที่ชัดเจนคือคำสั่ง SELECT ที่กำหนดไว้อย่างชัดเจนในส่วนการประกาศของโปรแกรม เมื่อมีการประกาศเคอร์เซอร์ที่ชัดเจน จะมีการตั้งชื่อเคอร์เซอร์นั้น สำหรับคำสั่ง INSERT, UPDATE, MERGE และ DELETE จะไม่สามารถกำหนดเคอร์เซอร์ที่ชัดเจนได้
ด้วยการกำหนดคำสั่ง SELECT ให้เป็นเคอร์เซอร์ที่ชัดเจน โปรแกรมเมอร์จะสามารถควบคุมขั้นตอนพื้นฐานของการดึงข้อมูลจากฐานข้อมูล Oracle โดยจะกำหนดเวลาเปิดเคอร์เซอร์ (OPEN) เมื่อใดที่จะดึงข้อมูลแถวจากเคอร์เซอร์ (FETCH) จำนวนแถวที่จะดึงข้อมูล และเมื่อใดที่ควรปิดเคอร์เซอร์ด้วยคำสั่ง CLOSE ข้อมูลเกี่ยวกับสถานะปัจจุบันของเคอร์เซอร์สามารถดูได้จากแอตทริบิวต์ มันเป็นความละเอียดของการควบคุมที่ทำให้เคอร์เซอร์ที่ชัดเจนเป็นเครื่องมืออันล้ำค่าสำหรับโปรแกรมเมอร์
ลองพิจารณาตัวอย่าง:
1 ฟังก์ชัน อิจฉา_ระดับ (2 NAME_IN ใน friends.NAME%TYPE) ส่งคืนหมายเลข 3 เป็น 4 เคอร์เซอร์ อิจฉา_cur 5 คือ 6 เลือกตำแหน่งจากเพื่อน 7 โดยที่ NAME = UPPER (NAME_IN); 8 8 อิจฉา_rec อิจฉา_cur%ROWTYPE; 9 ย้อนกลับ NUMBER; 10 เริ่มต้น 11 เปิดความอิจฉาริษยา; 13 12 ดึงความอิจฉา_cur เข้าสู่ความอิจฉา_rec; 15 13 IF อิจฉา_cur% พบ 14 แล้ว 15 IF อิจฉา_rec.location = "เปอร์โตริโก" 16 แล้ว retval:= 10; 17 ELSIF อิจฉา_rec.location = "ชิคาโก" 18 แล้ว retval:= 1; 19 เอนดิฟ; 20 จบ ถ้า; 24 21 ปิดความอิจฉาริษยา; 26 22 การส่งคืนกลับ; 23 ข้อยกเว้น 24 เมื่อคนอื่นแล้ว 25 ถ้า อิจฉา_cur%ISOPEN แล้ว 26 ปิด อิจฉา_cur; 27 สิ้นสุด ถ้า; 28END;
ในส่วนถัดไปจะกล่าวถึงแต่ละการดำเนินการเหล่านี้โดยละเอียด คำว่า "เคอร์เซอร์" ในคำเหล่านี้หมายถึงเคอร์เซอร์ที่ชัดเจน เว้นแต่ข้อความจะระบุไว้เป็นอย่างอื่นอย่างชัดเจน
ประกาศเคอร์เซอร์ที่ชัดเจน
เพื่อให้สามารถใช้เคอร์เซอร์ที่ชัดเจนได้ จะต้องประกาศไว้ในส่วนการประกาศของบล็อกหรือแพ็คเกจ PL/SQL:
เคอร์เซอร์ cursor_name [ ([ พารามิเตอร์ [, พารามิเตอร์...]) ] [ RETURN refEurn_spec ] IS SELECT_command ];
ชื่อเคอร์เซอร์ในที่นี้คือชื่อของเคอร์เซอร์ที่กำลังประกาศ special_te?um - ส่วน RETURN ที่เป็นทางเลือก KOMaHdaSELECT - คำสั่ง SQL SELECT ที่ถูกต้อง พารามิเตอร์ยังสามารถส่งผ่านไปยังเคอร์เซอร์ได้ (ดูส่วนพารามิเตอร์เคอร์เซอร์ด้านล่าง) สุดท้าย หลังจากคำสั่ง SELECT...FOR UPDATE คุณสามารถระบุรายการคอลัมน์ที่จะอัปเดตได้ (ดูด้านล่างด้วย) หลังจากประกาศเคอร์เซอร์แล้ว คำสั่ง OPEN จะเปิดขึ้นมา และการดึงแถวออกมาจะเสร็จสิ้นด้วยคำสั่ง FETCH
ตัวอย่างการประกาศเคอร์เซอร์ที่ชัดเจน
- เคอร์เซอร์ที่ไม่มีพารามิเตอร์. ชุดผลลัพธ์ของแถวของเคอร์เซอร์นี้คือชุดรหัสบริษัทที่เลือกจากทุกแถวในตาราง:
- เคอร์เซอร์พร้อมพารามิเตอร์ชุดผลลัพธ์ของแถวสำหรับเคอร์เซอร์นี้มีแถวเดียวที่มีชื่อบริษัทตรงกับค่าของพารามิเตอร์ที่ส่ง:
- เคอร์เซอร์ที่มีส่วนคำสั่ง RETURN. ชุดผลลัพธ์ของเคอร์เซอร์นี้มีข้อมูลทั้งหมดในตารางพนักงานสำหรับแผนก ID 10:
ชื่อเคอร์เซอร์
ชื่อเคอร์เซอร์ที่ชัดเจนต้องมีความยาวไม่เกิน 30 อักขระและเป็นไปตามกฎเดียวกันกับตัวระบุ PL/SQL อื่นๆ ชื่อของเคอร์เซอร์ไม่ใช่ตัวแปร แต่เป็นตัวระบุของตัวชี้ไปยังแบบสอบถาม ชื่อเคอร์เซอร์ไม่ได้กำหนดค่าและไม่สามารถใช้ในนิพจน์ได้ เคอร์เซอร์จะใช้เฉพาะในคำสั่ง OPEN, CLOSE และ FETCH เท่านั้น และเพื่อให้มีคุณสมบัติแอตทริบิวต์เคอร์เซอร์
ประกาศเคอร์เซอร์ในแพ็คเกจ
เคอร์เซอร์ที่ชัดเจนจะถูกประกาศในส่วนการประกาศของบล็อก PL/SQL เคอร์เซอร์สามารถประกาศได้ที่ระดับแพ็คเกจ แต่ไม่ใช่ในขั้นตอนหรือฟังก์ชันแพ็คเกจเฉพาะ ตัวอย่างการประกาศเคอร์เซอร์สองตัวในแพ็คเกจ:
PACKAGE book_info เป็น CURSOR titles_cur คือ SELECT ชื่อจากหนังสือ; CURSOR books_cur (title_filter_in IN books.title%TYPE) คืนหนังสือ%ROWTYPE IS SELECT * จากหนังสือ WHERE title LIKE title_filter_in; จบ;
เคอร์เซอร์ titles_cur ตัวแรกจะแสดงเฉพาะชื่อหนังสือเท่านั้น ประการที่สอง books_cur ส่งคืนแถวทั้งหมดในตารางหนังสือโดยที่ชื่อหนังสือตรงกับรูปแบบที่ระบุเป็นพารามิเตอร์เคอร์เซอร์ (เช่น "หนังสือทั้งหมดที่มีสตริง 'PL/SQL'") โปรดทราบว่าเคอร์เซอร์ตัวที่สองใช้คำสั่งย่อย RETURN ที่ประกาศโครงสร้างข้อมูลที่ส่งคืนโดยคำสั่ง FETCH
โครงสร้างข้อมูลใดๆ ต่อไปนี้สามารถระบุได้ในส่วน RETURN:
- เรกคอร์ดที่กำหนดตามแถวตารางข้อมูล โดยใช้แอตทริบิวต์ %ROWTYPE
- รายการที่กำหนดโดยยึดตามเคอร์เซอร์อื่นที่ประกาศไว้ก่อนหน้านี้ และใช้แอตทริบิวต์ %rowtype ด้วย
- บันทึกที่กำหนดโดยโปรแกรมเมอร์
จำนวนนิพจน์ในรายการที่เลือกของเคอร์เซอร์จะต้องตรงกับจำนวนคอลัมน์ในระเบียน table_name%ROWTYPE, Kypcop%ROWTYPE หรือประเภทระเบียน ประเภทข้อมูลขององค์ประกอบจะต้องเข้ากันได้ด้วย ตัวอย่างเช่น หากองค์ประกอบที่สองของรายการที่เลือกเป็นประเภท NUMBER คอลัมน์ที่สองของรายการในส่วน RETURN จะต้องไม่เป็นประเภท VARCHAR2 หรือ BOOLEAN
ก่อนที่เราจะเข้าไปดูรายละเอียดของส่วน RETURN และคุณประโยชน์ของส่วนนั้น ก่อนอื่นเรามาดูกันว่าอะไรคือจุดประสงค์ของการประกาศเคอร์เซอร์ในแพ็คเกจ? ทำไมไม่ประกาศเคอร์เซอร์ที่ชัดเจนในโปรแกรมที่ใช้งาน - ในโพรซีเดอร์, ฟังก์ชัน, หรือบล็อกที่ไม่ระบุชื่อ?
คำตอบนั้นง่ายและน่าเชื่อถือ ด้วยการกำหนดเคอร์เซอร์ในแพ็คเกจ คุณสามารถนำการสืบค้นที่กำหนดไว้ในแพ็คเกจกลับมาใช้ใหม่ได้ โดยไม่ต้องทำซ้ำโค้ดเดียวกันในตำแหน่งต่างๆ ในแอปพลิเคชัน การใช้แบบสอบถามในที่เดียวช่วยให้ปรับแต่งและบำรุงรักษาโค้ดได้ง่ายขึ้น ประหยัดเวลาได้บ้างโดยการลดจำนวนคำขอที่ประมวลผล
นอกจากนี้ยังควรพิจารณาสร้างฟังก์ชันที่ส่งคืนตัวแปรเคอร์เซอร์ตาม REF CURSOR โปรแกรมที่เรียกจะดึงข้อมูลแถวผ่านตัวแปรเคอร์เซอร์ ดูตัวแปรเคอร์เซอร์และเคอร์เซอร์อ้างอิงสำหรับข้อมูลเพิ่มเติม
มีสิ่งสำคัญสิ่งหนึ่งที่ต้องจำไว้เมื่อประกาศเคอร์เซอร์ในแพ็คเกจที่ใช้ซ้ำได้ โครงสร้างข้อมูลทั้งหมด รวมถึงเคอร์เซอร์ที่ประกาศที่ "ระดับแพ็กเกจ" (ไม่ใช่ภายในฟังก์ชันหรือขั้นตอนเฉพาะ) จะคงค่าไว้ตลอดเซสชัน ซึ่งหมายความว่าเคอร์เซอร์แบบแบตช์จะยังคงเปิดอยู่จนกว่าคุณจะปิดอย่างชัดเจน หรือจนกว่าเซสชันจะสิ้นสุด เคอร์เซอร์ที่ประกาศในบล็อกท้องถิ่นจะถูกปิดโดยอัตโนมัติเมื่อบล็อกเหล่านั้นสิ้นสุด
ตอนนี้เรามาจัดการกับส่วน RETURN กันดีกว่า การประกาศเคอร์เซอร์ในแพ็คเกจมีคุณสมบัติที่น่าสนใจอย่างหนึ่ง: ชื่อของเคอร์เซอร์สามารถแยกออกจากเนื้อความได้ ส่วนหัวดังกล่าวมีลักษณะคล้ายกับส่วนหัวของฟังก์ชันมากกว่า โดยมีข้อมูลที่โปรแกรมเมอร์จำเป็นต้องใช้ในการทำงาน ได้แก่ ชื่อของเคอร์เซอร์ พารามิเตอร์ และประเภทของข้อมูลที่ส่งคืน เนื้อความของเคอร์เซอร์คือคำสั่ง SELECT เทคนิคนี้แสดงให้เห็นในการประกาศเคอร์เซอร์ books_cur เวอร์ชันใหม่ในแพ็คเกจ book_info:
PACKAGE book_info IS CURSOR books_cur (title_filter_in IN books.title%TYPE) ส่งคืนหนังสือ%ROWTYPE; จบ; เนื้อหาแพคเกจ book_info IS CURSOR books_cur (title_filter_in IN books.title%TYPE) คืนหนังสือ%ROWTYPE IS SELECT * จากหนังสือ WHERE title LIKE title_filter_in; จบ;
อักขระทั้งหมดจนถึงคีย์เวิร์ด IS จะสร้างข้อกำหนด และหลังจาก IS จะเป็นเนื้อความของเคอร์เซอร์ การแยกการประกาศเคอร์เซอร์สามารถใช้เพื่อวัตถุประสงค์สองประการ
- การซ่อนข้อมูล. เคอร์เซอร์ในแพ็กเก็ตคือ "กล่องดำ" ซึ่งสะดวกสำหรับโปรแกรมเมอร์เพราะไม่จำเป็นต้องเขียนหรือดูคำสั่ง SELECT ก็เพียงพอแล้วที่จะทราบว่าเคอร์เซอร์นี้ส่งคืนเรคคอร์ดใด เรียงลำดับอะไร และประกอบด้วยคอลัมน์ใด โปรแกรมเมอร์แพ็คเกจใช้เคอร์เซอร์เหมือนกับองค์ประกอบสำเร็จรูปอื่นๆ
- การคอมไพล์ขั้นต่ำ. หากคุณซ่อนคำจำกัดความของคิวรีในเนื้อหาของแพ็คเกจ คุณสามารถเปลี่ยนแปลงคำสั่ง SELECT ได้โดยไม่ต้องเปลี่ยนส่วนหัวของเคอร์เซอร์ในข้อกำหนดของแพ็คเกจ ซึ่งช่วยให้โค้ดได้รับการปรับปรุง แก้ไข และคอมไพล์ใหม่โดยไม่ต้องคอมไพล์ใหม่ตามข้อกำหนดแพ็คเกจ ดังนั้นโปรแกรมที่ขึ้นอยู่กับแพ็คเกจจะไม่ถูกทำเครื่องหมายว่าไม่ถูกต้องและไม่จำเป็นต้องคอมไพล์ใหม่
การเปิดเคอร์เซอร์ที่ชัดเจน
การใช้เคอร์เซอร์เริ่มต้นด้วยคำจำกัดความในส่วนการประกาศ จากนั้นจะต้องเปิดเคอร์เซอร์ที่ประกาศไว้ ไวยากรณ์ของคำสั่ง OPEN นั้นง่ายมาก:
OPEN cursor_name [ (อาร์กิวเมนต์ [, อาร์กิวเมนต์...]) ];
ในที่นี้ชื่อเคอร์เซอร์คือชื่อของเคอร์เซอร์ที่ประกาศไว้ก่อนหน้านี้ และอาร์กิวเมนต์คือค่าที่ส่งไปยังเคอร์เซอร์หากมีการประกาศพร้อมกับรายการพารามิเตอร์
Oracle ยังสนับสนุนไวยากรณ์ FOR เมื่อเปิดเคอร์เซอร์ ซึ่งใช้สำหรับตัวแปรเคอร์เซอร์ทั้งสอง (ดูตัวแปรเคอร์เซอร์และ REF CURSOR ) และ SQL ไดนามิกแบบอินไลน์
เมื่อเปิดเคอร์เซอร์ PL/SQL จะดำเนินการค้นหาเคอร์เซอร์ที่มีอยู่ นอกจากนี้ยังระบุชุดข้อมูลที่ใช้งานอยู่ - แถวของตารางทั้งหมดที่เข้าร่วมในการสืบค้นที่ตรงกับเกณฑ์ WHERE และเงื่อนไขการรวม คำสั่ง OPEN ไม่ดึงข้อมูล - นั่นคืองานของคำสั่ง FETCH
ไม่ว่าการดึงข้อมูลครั้งแรกจะเกิดขึ้นเมื่อใด โมเดลความสมบูรณ์ของข้อมูลของ Oracle ช่วยให้มั่นใจได้ว่าการดำเนินการดึงข้อมูลทั้งหมดจะส่งคืนข้อมูลในสถานะ ณ เวลาที่เคอร์เซอร์ถูกเปิด กล่าวอีกนัยหนึ่ง ตั้งแต่การเปิดจนถึงการปิดเคอร์เซอร์ เมื่อดึงข้อมูลจากเคอร์เซอร์ การดำเนินการแทรก อัปเดต และลบที่ทำในช่วงเวลานี้จะถูกละเว้นโดยสิ้นเชิง
ยิ่งไปกว่านั้น หากคำสั่ง SELECT มีคำสั่งย่อย FOR UPDATE แถวทั้งหมดที่ระบุโดยเคอร์เซอร์จะถูกล็อคเมื่อเปิดขึ้นมา
หากคุณพยายามเปิดเคอร์เซอร์ที่เปิดอยู่แล้ว PL/SQL จะสร้างข้อความแสดงข้อผิดพลาดต่อไปนี้:
ORA-06511: PL/SQL: เคอร์เซอร์เปิดอยู่แล้ว
ดังนั้นก่อนที่จะเปิดเคอร์เซอร์ คุณควรตรวจสอบสถานะด้วยค่าของแอตทริบิวต์ %เปิด:
หากไม่ใช่ company_cur% ISOPEN ให้เปิด company_cur; สิ้นสุดถ้า;
คุณลักษณะเคอร์เซอร์ที่ชัดเจนอธิบายไว้ในส่วนด้านล่าง
หากโปรแกรมดำเนินการ FOR loop โดยใช้เคอร์เซอร์ เคอร์เซอร์นั้นไม่จำเป็นต้องเปิดอย่างชัดเจน (ดึงข้อมูล ปิด) เคอร์เนล PL/SQL ทำสิ่งนี้โดยอัตโนมัติ
กำลังดึงข้อมูลจากเคอร์เซอร์ที่ชัดเจน
คำสั่ง SELECT จะสร้างตารางเสมือน - ชุดของแถวที่กำหนดโดยส่วนคำสั่ง WHERE พร้อมด้วยคอลัมน์ที่กำหนดโดยรายการคอลัมน์ SELECT ดังนั้นเคอร์เซอร์จึงแทนตารางนี้ในโปรแกรม PL/SQL วัตถุประสงค์หลักของเคอร์เซอร์ในโปรแกรม PL/SQL คือการดึงแถวมาประมวลผล การดึงข้อมูลแถวเคอร์เซอร์ทำได้ด้วยคำสั่ง FETCH:
FETCH cursor_name เข้าสู่ record_or_var_list;
ในที่นี้ชื่อเคอร์เซอร์คือชื่อของเคอร์เซอร์ที่ใช้เลือกระเบียน และรายการบันทึกหรือตัวแปรคือโครงสร้างข้อมูล PL/SQL ที่จะคัดลอกแถวถัดไปของชุดระเบียนที่ใช้งานอยู่ ข้อมูลสามารถวางในบันทึก PL/SQL (ประกาศด้วยแอตทริบิวต์ %ROWTYPE หรือการประกาศ TYPE) หรือในตัวแปร (ตัวแปร PL/SQL หรือตัวแปรผูก - เช่นในองค์ประกอบ Oracle Forms)
ตัวอย่างของเคอร์เซอร์ที่ชัดเจน
ตัวอย่างต่อไปนี้แสดงวิธีต่างๆ ในการดึงข้อมูล
- การดึงข้อมูลจากเคอร์เซอร์ลงในบันทึก PL/SQL:
- การดึงข้อมูลจากเคอร์เซอร์เข้าสู่ตัวแปร:
- การดึงข้อมูลจากเคอร์เซอร์ไปยังแถวของตาราง PL/SQL, ตัวแปร และตัวแปรการผูกของ Oracle Forms:
ข้อมูลที่ดึงมาจากเคอร์เซอร์ควรถูกวางไว้ในบันทึกที่ประกาศโดยยึดตามเคอร์เซอร์เดียวกันกับแอตทริบิวต์ %ROWTYPE หลีกเลี่ยงการดึงข้อมูลลงในรายการตัวแปร การดึงข้อมูลเพื่อบันทึกทำให้โค้ดมีขนาดกะทัดรัดและยืดหยุ่นมากขึ้น ช่วยให้คุณสามารถเปลี่ยนรายการดึงข้อมูลได้โดยไม่ต้องเปลี่ยนคำสั่ง FETCH
ดึงข้อมูลหลังจากประมวลผลแถวสุดท้าย
เมื่อคุณเปิดเคอร์เซอร์ คุณจะเลือกแถวจากแถวนั้นทีละแถวจนกว่าแถวเหล่านั้นจะหมด อย่างไรก็ตาม คุณยังคงสามารถเรียกใช้คำสั่ง FETCH ได้หลังจากนั้น
น่าแปลกที่ PL/SQL ไม่มีข้อยกเว้นในกรณีนี้ เขาแค่ไม่ทำอะไรเลย เนื่องจากไม่มีอะไรให้เลือก ค่าของตัวแปรในส่วน INTO ของคำสั่ง FETCH จึงไม่เปลี่ยนแปลง กล่าวอีกนัยหนึ่ง คำสั่ง FETCH ไม่ได้ตั้งค่าตัวแปรเหล่านี้เป็น NULL
นามแฝงคอลัมน์เคอร์เซอร์ที่ชัดเจน
คำสั่ง SELECT ในการประกาศเคอร์เซอร์จะกำหนดรายการคอลัมน์ที่ส่งคืน นอกจากชื่อคอลัมน์ของตารางแล้ว รายการนี้ยังสามารถมีนิพจน์ที่เรียกว่าคอลัมน์จากการคำนวณหรือคอลัมน์เสมือนได้
นามแฝงของคอลัมน์เป็นชื่อสำรองที่ระบุในคำสั่ง SELECT สำหรับคอลัมน์หรือนิพจน์ ด้วยการระบุนามแฝงที่เหมาะสมใน SQL*Plus คุณสามารถแสดงผลลัพธ์ของการสืบค้นที่กำหนดเองในรูปแบบที่มนุษย์สามารถอ่านได้ ในสถานการณ์เช่นนี้ นามแฝงจะเป็นทางเลือก ในทางกลับกัน เมื่อใช้เคอร์เซอร์ที่ชัดเจน จำเป็นต้องใช้นามแฝงคอลัมน์จากการคำนวณในกรณีต่อไปนี้:
- เมื่อดึงข้อมูลจากเคอร์เซอร์ลงในบันทึกที่ประกาศด้วยแอตทริบิวต์ %ROWTYPE ตามเคอร์เซอร์เดียวกัน
- เมื่อโปรแกรมมีการอ้างอิงไปยังคอลัมน์จากการคำนวณ
พิจารณาคำถามต่อไปนี้ คำสั่ง SELECT เลือกชื่อของบริษัททั้งหมดที่สั่งซื้อสินค้าระหว่างปี 2544 รวมถึงจำนวนคำสั่งซื้อทั้งหมด (สมมติว่ารูปแบบมาสก์เริ่มต้นสำหรับอินสแตนซ์ฐานข้อมูลปัจจุบันคือ DD-MON-YYYY):
เลือก company_name, SUM (inv_amt) จากบริษัท c, ใบแจ้งหนี้ i WHERE c.company_id = i.company_id AND i.invoice_date ระหว่าง "01-JAN-2001" และ "31-DEC-2001";
การรันคำสั่งนี้ใน SQL*Plus จะสร้างเอาต์พุตต่อไปนี้:
ชื่อ บริษัท | ผลรวม (INV_AMT) |
แอคมี เทอร์โบ อิงค์ | 1000 |
วอชิงตัน แฮร์ บจก. | 25.20 |
อย่างที่คุณเห็น ส่วนหัวคอลัมน์ SUM (INV_AMT) ไม่เหมาะกับรายงานมากนัก แต่ก็เหมาะสำหรับการดูข้อมูลทั่วไป ตอนนี้เรามาเรียกใช้แบบสอบถามเดียวกันในโปรแกรม PL/SQL โดยใช้เคอร์เซอร์ที่ชัดเจนและเพิ่มนามแฝงของคอลัมน์:
ประกาศเคอร์เซอร์ comp_cur คือ SELECT c.name, SUM (inv_amt) Total_sales จากบริษัท C, ใบแจ้งหนี้ I WHERE C.company_id = I.company_id AND I.invoice_date BETWEEN "01-JAN-2001" AND "31-DEC-2001"; comp_rec comp_cur%ROWTYPE; เริ่มเปิด comp_cur; ดึงข้อมูล comp_cur เข้าสู่ comp_rec; จบ;
หากไม่มีนามแฝง ฉันจะไม่สามารถอ้างอิงคอลัมน์ในโครงสร้างบันทึก comp_rec ได้ ด้วยนามแฝง คุณสามารถทำงานกับคอลัมน์จากการคำนวณได้เหมือนกับคอลัมน์แบบสอบถามอื่นๆ:
หาก comp_rec.total_sales > 5000 แล้ว DBMS_OUTPUT.PUT_LINE (" คุณมีวงเงินเครดิตเกิน $5,000 ด้วย " || TO_CHAR (comp_rec.total_sales - 5,000, "$9999")); สิ้นสุดถ้า;
เมื่อดึงข้อมูลแถวลงในบันทึกที่ประกาศด้วยแอตทริบิวต์ %ROWTYPE คอลัมน์ที่คำนวณแล้วสามารถเข้าถึงได้ด้วยชื่อเท่านั้น เนื่องจากโครงสร้างของบันทึกจะถูกกำหนดโดยโครงสร้างของเคอร์เซอร์เอง
การปิดเคอร์เซอร์ที่ชัดเจน
ครั้งหนึ่งในวัยเด็ก เราได้รับการสอนให้ทำความสะอาดตัวเอง และนิสัยนี้จะคงอยู่กับเรา (แม้ว่าจะไม่ใช่ทั้งหมด) ไปตลอดชีวิต ปรากฎว่ากฎนี้มีบทบาทสำคัญในการเขียนโปรแกรมเช่นกัน และโดยเฉพาะอย่างยิ่งเมื่อพูดถึงการควบคุมเคอร์เซอร์ อย่าลืมปิดเคอร์เซอร์หากคุณไม่ต้องการมันอีกต่อไป!
ไวยากรณ์สำหรับคำสั่ง CLOSE คือ:
ปิดเคอร์เซอร์_ชื่อ;
ต่อไปนี้เป็นเคล็ดลับและข้อควรพิจารณาที่สำคัญในการปิดเคอร์เซอร์ที่ชัดเจน
- หากมีการประกาศเคอร์เซอร์และเปิดในโพรซีเดอร์ อย่าลืมปิดเคอร์เซอร์เมื่อคุณดำเนินการเสร็จแล้ว มิฉะนั้นรหัสของคุณจะทำให้หน่วยความจำรั่ว ตามทฤษฎีแล้ว เคอร์เซอร์ (เช่นเดียวกับโครงสร้างข้อมูลอื่นๆ) ควรถูกปิดและทำลายโดยอัตโนมัติเมื่ออยู่นอกขอบเขต โดยทั่วไปแล้ว เมื่อออกจากโพรซีเดอร์ ฟังก์ชัน หรือบล็อกที่ไม่ระบุชื่อ PL/SQL จะปิดเคอร์เซอร์ใดๆ ก็ตามที่เปิดอยู่ในนั้น อย่างไรก็ตาม กระบวนการนี้มีค่าใช้จ่าย ดังนั้นด้วยเหตุผลด้านประสิทธิภาพ บางครั้ง PL/SQL อาจทำให้การตรวจจับและการปิดเคอร์เซอร์ที่เปิดอยู่เกิดความล่าช้า REF CURSOR ตามคำจำกัดความ ไม่สามารถปิดโดยปริยายได้ สิ่งเดียวที่คุณแน่ใจได้คือเมื่อบล็อก PL/SQL "ด้านนอกสุด" สิ้นสุดลง เมื่อการควบคุมถูกส่งกลับไปยัง SQL หรือผู้เรียกอื่น PL/SQL จะปิดเคอร์เซอร์ใดๆ ที่เปิดโดยบล็อกนั้นหรือบล็อกที่ซ้อนกันโดยปริยาย ยกเว้น REF CURSOR . การใช้เคอร์เซอร์บทความของ Oracle Technology Network ซ้ำใน PL/SQL SQL แบบคงที่จะให้การวิเคราะห์โดยละเอียดว่า PL/SQL ปิดเคอร์เซอร์อย่างไรและเมื่อใด บล็อกที่ไม่ระบุชื่อที่ซ้อนกันเป็นตัวอย่างของสถานการณ์ที่ PL/SQL ไม่ได้ปิดเคอร์เซอร์โดยปริยาย สำหรับข้อมูลที่น่าสนใจในหัวข้อนี้ โปรดดูบทความของ Jonathan Gennick เรื่อง "Does PL/SQL Implicitly Close Cursors?"
- หากมีการประกาศเคอร์เซอร์ในแพ็คเกจที่ระดับแพ็คเกจและเปิดในบล็อกหรือโปรแกรมบางโปรแกรม เคอร์เซอร์นั้นจะยังคงเปิดอยู่จนกว่าคุณจะปิดอย่างชัดเจน หรือจนกว่าเซสชันจะสิ้นสุดลง ดังนั้น เมื่อคุณทำงานกับเคอร์เซอร์ระดับแบตช์เสร็จแล้ว คุณควรปิดเคอร์เซอร์ทันทีด้วยคำสั่ง CLOSE (และอีกอย่าง คุณควรทำเช่นเดียวกันในส่วนข้อยกเว้น):
- เคอร์เซอร์สามารถปิดได้เฉพาะเมื่อเคอร์เซอร์ถูกเปิดไว้ก่อนหน้านี้เท่านั้น มิฉะนั้น ข้อยกเว้น INVALID_CURS0R จะถูกส่งออกไป สถานะเคอร์เซอร์ถูกตรวจสอบโดยใช้แอตทริบิวต์ %ISOPEN:
- หากมีเคอร์เซอร์ที่เปิดอยู่ในโปรแกรมมากเกินไป จำนวนของเคอร์เซอร์เหล่านั้นอาจเกินค่าของพารามิเตอร์ฐานข้อมูล OPEN_CURSORS เมื่อคุณได้รับข้อความแสดงข้อผิดพลาด สิ่งแรกที่ต้องทำคือต้องแน่ใจว่าเคอร์เซอร์ที่ประกาศในแพ็คเกจถูกปิดเมื่อไม่จำเป็นต้องใช้อีกต่อไป
คุณลักษณะเคอร์เซอร์ที่ชัดเจน
Oracle รองรับแอตทริบิวต์สี่รายการ (%FOUND, %NOTFOUND, %ISOPEN, %ROWCOUNTM) เพื่อรับข้อมูลเกี่ยวกับสถานะของเคอร์เซอร์ที่ชัดเจน การอ้างอิงแอ็ตทริบิวต์มีไวยากรณ์ต่อไปนี้: cursor%attribute
เคอร์เซอร์ในที่นี้คือชื่อของเคอร์เซอร์ที่ประกาศ
ค่าที่ส่งคืนโดยแอตทริบิวต์เคอร์เซอร์ที่ชัดเจนจะแสดงในตารางที่ 1 1.
ตารางที่ 1.คุณลักษณะเคอร์เซอร์ที่ชัดเจน
ค่าของแอตทริบิวต์เคอร์เซอร์ก่อนและหลังดำเนินการต่างๆ จะแสดงในตาราง 2.
เมื่อทำงานกับแอตทริบิวต์เคอร์เซอร์ที่ชัดเจน โปรดคำนึงถึงสิ่งต่อไปนี้:
- หากคุณพยายามเข้าถึงแอตทริบิวต์ %FOUND, %NOTFOUND หรือ %ROWCOUNT ก่อนที่จะเปิดเคอร์เซอร์หรือหลังจากปิดแล้ว Oracle จะส่งข้อยกเว้น INVALID CURSOR (ORA-01001)
- หากชุดแถวผลลัพธ์ว่างเปล่าหลังจากการดำเนินการครั้งแรกของคำสั่ง FETCH แอ็ตทริบิวต์เคอร์เซอร์จะส่งกลับค่าต่อไปนี้: %FOUND = FALSE , %NOTFOUND = TRUE และ %ROWCOUNT = 0
- เมื่อใช้ BULK COLLECT แอตทริบิวต์ %ROWCOUNT จะส่งกลับจำนวนแถวที่ดึงข้อมูลในคอลเลกชันที่กำหนด
|
ตารางที่ 2.ค่าแอตทริบิวต์เคอร์เซอร์
การดำเนินการ | %พบ | %ไม่พบ | %เปิด | %ROWCOUNT |
ก่อนเปิด | ข้อยกเว้น โอรา-01001 |
ข้อยกเว้น โอรา-01001 |
เท็จ | ข้อยกเว้น โอรา-01001 |
หลังจากเปิด | โมฆะ | โมฆะ | จริง | 0 |
ก่อน FETCH ครั้งแรก | โมฆะ | โมฆะ | จริง | 0 |
หลังจากตัวอย่างแรก ดึงข้อมูล |
จริง | เท็จ | จริง | 1 |
ก่อนต่อมา ดึงข้อมูล |
จริง | เท็จ | จริง | 1 |
หลังจาก FETCH ตามมา | จริง | เท็จ | จริง | ขึ้นอยู่กับข้อมูล |
ก่อน FETCH สุดท้าย | จริง | เท็จ | จริง | ขึ้นอยู่กับข้อมูล |
หลังจากการ FETCH ครั้งล่าสุด | จริง | เท็จ | จริง | ขึ้นอยู่กับข้อมูล |
ก่อนปิด | เท็จ | จริง | จริง | ขึ้นอยู่กับข้อมูล |
หลังจากปิด | ข้อยกเว้น | ข้อยกเว้น | เท็จ | ข้อยกเว้น |
การใช้คุณลักษณะทั้งหมดเหล่านี้แสดงให้เห็นในตัวอย่างต่อไปนี้:
บล็อกก่อนหน้านี้ได้ยกตัวอย่างการใช้พารามิเตอร์ของขั้นตอนและฟังก์ชันซ้ำแล้วซ้ำอีก พารามิเตอร์เป็นวิธีการส่งข้อมูลเข้าและออกจากโมดูลโปรแกรม เมื่อใช้อย่างถูกต้อง จะทำให้โมดูลมีประโยชน์และยืดหยุ่นมากขึ้น
PL/SQL ช่วยให้คุณสามารถส่งพารามิเตอร์ไปยังเคอร์เซอร์ได้ ทำหน้าที่เหมือนกับพารามิเตอร์ของโมดูลโปรแกรมรวมถึงฟังก์ชันเพิ่มเติมบางส่วน
- การขยายการใช้เคอร์เซอร์ซ้ำ. แทนที่จะใช้ค่าฮาร์ดโค้ดในส่วนคำสั่ง WHERE ที่กำหนดเกณฑ์การเลือกข้อมูล คุณสามารถใช้พารามิเตอร์เพื่อส่งค่าใหม่ไปยังส่วนคำสั่ง WHERE ทุกครั้งที่เปิดเคอร์เซอร์
- การแก้ปัญหาการกำหนดขอบเขตเคอร์เซอร์. หากใช้พารามิเตอร์ในการสืบค้นแทนค่าฮาร์ดโค้ด ชุดผลลัพธ์ของแถวเคอร์เซอร์จะไม่เชื่อมโยงกับโปรแกรมหรือตัวแปรบล็อกเฉพาะ หากโปรแกรมของคุณมีบล็อกที่ซ้อนกัน คุณสามารถกำหนดเคอร์เซอร์ที่ระดับบนสุดและใช้ในบล็อกที่ซ้อนกันโดยมีการประกาศตัวแปรไว้
จำนวนพารามิเตอร์เคอร์เซอร์ไม่จำกัด เมื่อเรียก OPEN จะต้องตั้งค่าเคอร์เซอร์เป็นพารามิเตอร์ทั้งหมด (ยกเว้นพารามิเตอร์ที่มีค่าเริ่มต้น)
เคอร์เซอร์ต้องการพารามิเตอร์เมื่อใด กฎทั่วไปที่นี่เหมือนกับขั้นตอนและฟังก์ชัน: หากเคอร์เซอร์คาดว่าจะถูกใช้ในตำแหน่งที่แตกต่างกันและมีค่าต่างกันในส่วนคำสั่ง WHERE คุณควรกำหนดพารามิเตอร์สำหรับเคอร์เซอร์นั้น ลองเปรียบเทียบเคอร์เซอร์ที่มีและไม่มีพารามิเตอร์ ตัวอย่างของเคอร์เซอร์ที่ไม่มีพารามิเตอร์:
เคอร์เซอร์ joke_cur คือชื่อ, หมวดหมู่, Last_used_date จากเรื่องตลก;
ชุดผลลัพธ์ของเคอร์เซอร์จะรวมรายการทั้งหมดในตารางเรื่องตลก หากเราต้องการเพียงชุดย่อยของแถว ส่วนคำสั่ง WHERE จะรวมอยู่ในแบบสอบถาม:
เคอร์เซอร์ joke_cur คือ SELECT ชื่อ, หมวดหมู่, Last_used_date จากเรื่องตลก WHERE category = "สามี";
เพื่อให้งานนี้สำเร็จ เราไม่ได้ใช้พารามิเตอร์ และไม่จำเป็น ในกรณีนี้ เคอร์เซอร์จะส่งคืนแถวทั้งหมดที่อยู่ในหมวดหมู่ใดหมวดหมู่หนึ่ง แต่จะเกิดอะไรขึ้นหากหมวดหมู่เปลี่ยนแปลงทุกครั้งที่เข้าถึงเคอร์เซอร์นี้
เคอร์เซอร์พร้อมพารามิเตอร์
แน่นอนว่าเราจะไม่กำหนดเคอร์เซอร์แยกกันสำหรับแต่ละหมวดหมู่ ซึ่งไม่สอดคล้องกับหลักการพัฒนาแอปพลิเคชันที่ขับเคลื่อนด้วยข้อมูลโดยสิ้นเชิง เราต้องการเคอร์เซอร์เพียงตัวเดียว แต่มีเคอร์เซอร์ตัวเดียวที่เราสามารถเปลี่ยนหมวดหมู่ได้ และเคอร์เซอร์จะยังคงส่งคืนข้อมูลที่จำเป็น และวิธีแก้ปัญหาที่ดีที่สุด (แต่ไม่ใช่เพียงอย่างเดียว) สำหรับปัญหานี้คือการกำหนดเคอร์เซอร์แบบกำหนดพารามิเตอร์:
กระบวนการ expl_joke (main_category_in IN joke_category.category_id%TYPE) IS /* || เคอร์เซอร์ที่มีรายการพารามิเตอร์ประกอบด้วย || จากพารามิเตอร์สตริงเดียว */ CURSOR joke_cur (category_in IN VARCHAR2) คือ SELECT ชื่อ, หมวดหมู่, Last_used_date จาก Joke WHERE category = UPPER (category_in); joke_rec joke_cur%ROWTYPE; BEGIN /* ตอนนี้อาร์กิวเมนต์ถูกส่งไปยังเคอร์เซอร์เมื่อเปิดขึ้น */ OPEN joke_cur (main_category_in); ดึง joke_cur เข้าสู่ joke_rec;
ขณะนี้มีรายการตัวเลือกระหว่างชื่อเคอร์เซอร์และคำหลัก IS ค่า HUSBAND แบบฮาร์ดโค้ดในส่วนคำสั่ง WHERE ถูกแทนที่ด้วยการอ้างอิงถึงพารามิเตอร์ UPPER (category_in) เมื่อเปิดเคอร์เซอร์คุณสามารถตั้งค่าเป็น HUSBAND , สามี หรือ HuSbAnD - เคอร์เซอร์จะยังคงทำงานอยู่ ชื่อของหมวดหมู่ที่เคอร์เซอร์ควรส่งคืนแถวตารางตลกจะถูกระบุในคำสั่ง OPEN (ในวงเล็บ) ในรูปแบบตัวอักษร ค่าคงที่ หรือนิพจน์ ในขณะที่เคอร์เซอร์เปิดอยู่ คำสั่ง SELECT จะถูกแยกวิเคราะห์ และพารามิเตอร์จะเชื่อมโยงกับค่า จากนั้นชุดผลลัพธ์ของแถวจะถูกกำหนด - และเคอร์เซอร์ก็พร้อมที่จะดึงข้อมูล
การเปิดเคอร์เซอร์ด้วยพารามิเตอร์
เคอร์เซอร์ใหม่สามารถเปิดได้ในหมวดหมู่ใดก็ได้:
เปิด joke_cur(Jokes_pkg.category); เปิด joke_cur("สามี"); เปิด joke_cur("นักการเมือง"); เปิด joke_cur(Jokes_pkg.relation || "-IN-LAW");
โดยทั่วไปแล้วพารามิเตอร์เคอร์เซอร์จะใช้ในส่วนคำสั่ง WHERE แต่สามารถอ้างอิงถึงที่อื่นในคำสั่ง SELECT ได้:
ประกาศเคอร์เซอร์ joke_cur (category_in ใน ARCHAR2) คือชื่อที่เลือก, category_in, Last_used_date จากเรื่องตลก WHERE category = UPPER (category_in);
แทนที่จะอ่านหมวดหมู่จากตาราง เราเพียงแค่แทนที่พารามิเตอร์ category_in ลงในรายการที่เลือก ผลลัพธ์ยังคงเหมือนเดิมเนื่องจากส่วนคำสั่ง WHERE จำกัดหมวดหมู่การเลือกไว้ที่ค่าพารามิเตอร์
ขอบเขตพารามิเตอร์เคอร์เซอร์
ขอบเขตของพารามิเตอร์เคอร์เซอร์ถูกจำกัดไว้ที่เคอร์เซอร์นั้น ไม่สามารถอ้างอิงพารามิเตอร์เคอร์เซอร์ภายนอกคำสั่ง SELECT ที่เชื่อมโยงกับเคอร์เซอร์ได้ ข้อมูลโค้ด PL/SQL ด้านล่างไม่ได้คอมไพล์เนื่องจาก program_name ไม่ใช่ตัวแปรท้องถิ่นในบล็อก นี่คือพารามิเตอร์เคอร์เซอร์อย่างเป็นทางการที่กำหนดไว้ภายในเคอร์เซอร์เท่านั้น:
ประกาศเคอร์เซอร์ scariness_cur (program_name VARCHAR2) คือผลรวมที่เลือก (scary_level) Total_scary_level จาก tales_from_the_crypt โดยที่ prog_name = program_name; BEGIN program_name:= "THE BREATHING MUMMY"; /* การอ้างอิงไม่ถูกต้อง */ OPEN scariness_cur (program_name); .... ปิด Scariness_cur; จบ;
โหมดพารามิเตอร์เคอร์เซอร์
ไวยากรณ์สำหรับพารามิเตอร์เคอร์เซอร์จะคล้ายกันมากกับไวยากรณ์สำหรับขั้นตอนและฟังก์ชัน - ยกเว้นว่าพารามิเตอร์เคอร์เซอร์สามารถเป็นได้เฉพาะพารามิเตอร์ IN เท่านั้น พารามิเตอร์เคอร์เซอร์ไม่สามารถตั้งค่าเป็น OUT หรือ IN OUT โหมดเหล่านี้ช่วยให้คุณสามารถส่งและส่งคืนค่าจากโพรซีเดอร์ซึ่งไม่สมเหตุสมผลสำหรับเคอร์เซอร์ มีทางเดียวเท่านั้นที่จะรับข้อมูลจากเคอร์เซอร์: ดึงข้อมูลบันทึกและคัดลอกค่าจากรายการคอลัมน์ในส่วนคำสั่ง INTO
ค่าพารามิเตอร์เริ่มต้น
พารามิเตอร์เคอร์เซอร์สามารถกำหนดค่าเริ่มต้นได้ ตัวอย่างเคอร์เซอร์ที่มีค่าพารามิเตอร์เริ่มต้น:
CURSOR emp_cur (emp_id_in NUMBER:= 0) คือ SELECT Employee_id, emp_name จากพนักงาน WHERE Employee_id = emp_id_in;
เนื่องจากพารามิเตอร์ emp_id_in มีค่าเริ่มต้น คุณสามารถละค่าได้ในคำสั่ง FETCH ในกรณีนี้เคอร์เซอร์จะส่งคืนข้อมูลเกี่ยวกับพนักงานด้วยรหัส 0
คำสั่ง DECLARE CURSOR ช่วยให้คุณสามารถดึงข้อมูลบันทึกทีละแถวจากตารางเพื่อดำเนินการได้ ซึ่งช่วยให้สามารถประมวลผลแบบแถวต่อแถวแทนการประมวลผลชุดข้อมูลแบบเดิมที่ SQL ทำ
ในการประมาณค่าแรกสุด เมื่อทำงานกับเคอร์เซอร์ จะใช้ขั้นตอนต่อไปนี้
เคอร์เซอร์ถูกสร้างขึ้นด้วยคำสั่ง DECLARE เคอร์เซอร์เปิดขึ้นด้วยคำสั่ง OPEN
การดำเนินการเคอร์เซอร์ดำเนินการโดยใช้คำสั่ง FETCH เคอร์เซอร์ถูกปิดด้วยคำสั่ง CLOSE
คำสั่ง DECLARE CURSOR ระบุคำสั่ง SELECT แต่ละแถวที่ส่งคืนโดยคำสั่ง SELECT สามารถดึงข้อมูลและประมวลผลทีละรายการได้ ในตัวอย่าง Oracle ต่อไปนี้ เคอร์เซอร์จะถูกประกาศในบล็อกการประกาศพร้อมกับตัวแปรอื่นๆ อีกหลายตัว หลังจากนั้นในบล็อก BEGIN…END ต่อมา เคอร์เซอร์จะเปิดขึ้น ทำการเลือก และเคอร์เซอร์จะปิด
CURSOR title_price_cursor IS เลือกชื่อ ราคาจากชื่อ
โดยที่ราคาไม่เป็นโมฆะ title_price_val title_price_cursor ROWTYPE; ใหม่_ราคา NUMBER(10.2);
เปิด title_price_Cursor;
FETCH title_price_cur-sor เข้าสู่ title_price_val;
new_price:= "title_price_val.price" * 1.25 INSERT INTO new_title_price VALUES
(title_price_val.title, new_price) ปิด title_price_cursor; จบ;
เนื่องจากตัวอย่างนี้ใช้ PL/SQL โค้ดส่วนใหญ่จึงไม่ได้อธิบายไว้ในหนังสือเล่มนี้ อย่างไรก็ตาม การประกาศเคอร์เซอร์จะมองเห็นได้ชัดเจนในบล็อก DECLARE ในบล็อกปฏิบัติการ PL/SQL เคอร์เซอร์จะเริ่มต้นด้วยคำสั่ง OPEN ค่าจะถูกดึงข้อมูลด้วยคำสั่ง FETCH และสุดท้ายเคอร์เซอร์จะถูกปิดด้วยคำสั่ง CLOSE
คำสั่ง SELECT เป็นแกนหลักของเคอร์เซอร์ ดังนั้นจึงควรทดสอบอย่างละเอียดก่อนที่จะรวมไว้ในคำสั่ง DECLARE CURSOR คำสั่ง SELECT สามารถดำเนินการบนตารางหรือมุมมองพื้นฐานได้ ดังนั้นเคอร์เซอร์แบบอ่านอย่างเดียวสามารถทำงานกับมุมมองที่ไม่สามารถอัปเดตได้ คำสั่ง SELECT สามารถมีส่วนคำสั่งต่างๆ เช่น ORDER BY, GROUP BY และ HAVING ตราบใดที่ส่วนคำสั่งเหล่านั้นไม่ได้อัพเดตตารางต้นฉบับ หากเคอร์เซอร์ถูกกำหนดเป็น FOR UPDATE ขอแนะนำให้ลบส่วนคำสั่งดังกล่าวออกจากคำสั่ง SELECT
เคอร์เซอร์เฉพาะที่มักใช้เป็นพารามิเตอร์เอาต์พุตของกระบวนงานที่เก็บไว้ ดังนั้น ในกระบวนงานที่เก็บไว้ คุณสามารถกำหนดและเติมเคอร์เซอร์และส่งผ่านไปยังงานแบตช์หรือกระบวนงานเก็บไว้ที่เรียกเคอร์เซอร์นั้นได้
ในตัวอย่าง DB2 แบบง่ายต่อไปนี้ เราจะประกาศเคอร์เซอร์ที่ค้นหาหมายเลขแผนก ชื่อแผนก และหมายเลขผู้จัดการใน admin_group "XO1"
ประกาศ dept_cursor เคอร์เซอร์
สำหรับเลือก dept_nbr, dept_name, mgr_nbr
โดยที่ admin_group = "X01"
เรียงลำดับตาม d "ept_name ASC, dept_nbr DESC, mgr_nbr DESC;
ตัวอย่างต่อไปนี้สำหรับ Microsoft SQL Server ประกาศและเปิดเคอร์เซอร์บนตารางผู้เผยแพร่ เคอร์เซอร์จะเลือกระเบียนแรกจากตารางผู้เผยแพร่ที่ตรงกับคำสั่ง SELECT และแทรกลงในตารางอื่น จากนั้นจะย้ายไปยังระเบียนถัดไป จากนั้นไปยังระเบียนถัดไป จนกว่าระเบียนทั้งหมดจะได้รับการประมวลผล ในที่สุด เคอร์เซอร์จะปิดและหน่วยความจำว่าง (คำสั่ง DEALLOCATE ใช้ใน Microsoft SQL Server เท่านั้น)
ประกาศ @publisher_name VARCHAR (20)
ประกาศ pub_cursor CURSOR สำหรับ pub_name ที่เลือกจากผู้จัดพิมพ์ซึ่งประเทศ "USA"
ดึงข้อมูลถัดไปจาก pub_cursor เข้าสู่ vendor_name
ในขณะที่ @s>FETCH_STATUS=0
INSERT INTO Foreign_publishers VALUES("j>publisher_name)
ปิด pub_cursor DEALLOCATE pub_cursor
ในตัวอย่างนี้ คุณจะเห็นว่าเคอร์เซอร์เคลื่อนที่ผ่านชุดระเบียนอย่างไร (ตัวอย่างนี้มีไว้เพื่อแสดงแนวคิดนี้เท่านั้น เนื่องจากจริงๆ แล้วมีวิธีที่ดีกว่าในการทำงานนี้ให้สำเร็จ ซึ่งก็คือคำสั่ง INSERT, SELECT)
เคอร์เซอร์ใน SQL ซึ่งเป็นพื้นที่ในหน่วยความจำฐานข้อมูลที่มีไว้สำหรับจัดเก็บคำสั่ง SQL ล่าสุด ถ้าคำสั่งปัจจุบันเป็นการสืบค้นฐานข้อมูล สตริงข้อมูลการสืบค้นที่เรียกว่าค่าปัจจุบันหรือแถวปัจจุบันก็จะถูกจัดเก็บไว้ในหน่วยความจำเช่นกัน เคอร์เซอร์. พื้นที่ที่ระบุในหน่วยความจำได้รับการตั้งชื่อและพร้อมใช้งานสำหรับแอปพลิเคชันโปรแกรม
ตามมาตรฐาน SQL เมื่อทำงานด้วย เคอร์เซอร์หลักต่อไปนี้ การกระทำ:
- การสร้างหรือ การประกาศเคอร์เซอร์ ;
- การเปิดเคอร์เซอร์, เหล่านั้น. กรอกข้อมูลที่เก็บไว้ในหน่วยความจำหลายระดับ
- ดึงข้อมูลจากเคอร์เซอร์และ เปลี่ยนด้วยแถวข้อมูล
- เคอร์เซอร์ปิดหลังจากนั้นโปรแกรมผู้ใช้จะไม่สามารถเข้าถึงได้
- ปล่อยเคอร์เซอร์, เช่น. การกำจัด เคอร์เซอร์เป็นวัตถุเพราะว่า ปิดเลือกที่จะปล่อยหน่วยความจำที่เกี่ยวข้องกัน
SQL Server รองรับสาม ประเภทเคอร์เซอร์:
- เคอร์เซอร์ SQL ถูกใช้ภายในทริกเกอร์ ขั้นตอนการจัดเก็บ และสคริปต์เป็นหลัก
- เคอร์เซอร์เซิร์ฟเวอร์ทำงานบนเซิร์ฟเวอร์และใช้อินเทอร์เฟซการเขียนโปรแกรมแอปพลิเคชันสำหรับ ODBC, OLE DB, DB_Library;
- เคอร์เซอร์ไคลเอนต์ถูกนำไปใช้กับไคลเอนต์เอง โดยจะดึงชุดผลลัพธ์ทั้งหมดของแถวจากเซิร์ฟเวอร์และจัดเก็บไว้ในเครื่อง ซึ่งช่วยเพิ่มความเร็วในการประมวลผลข้อมูลโดยลดการสิ้นเปลืองของการดำเนินงานเครือข่าย
การควบคุมเคอร์เซอร์ในสภาพแวดล้อม MS SQL Server
การควบคุมเคอร์เซอร์ดำเนินการโดยดำเนินการคำสั่งต่อไปนี้:
- ประกาศ - สร้างหรือ การประกาศเคอร์เซอร์ ;
- เปิด- การเปิดเคอร์เซอร์, เช่น. กรอกข้อมูล;
- ดึงข้อมูล- ดึงข้อมูลจากเคอร์เซอร์และ เปลี่ยนแถวข้อมูลโดยใช้เคอร์เซอร์
- ปิด- เคอร์เซอร์ปิด ;
- จัดสรร- ปล่อยเคอร์เซอร์, เช่น. การลบเคอร์เซอร์เป็นวัตถุ
การประกาศเคอร์เซอร์
ในมาตรฐาน SQL ที่จะสร้าง เคอร์เซอร์มีคำสั่งต่อไปนี้:
<создание_курсора>::= ประกาศ cursor_name เคอร์เซอร์สำหรับ SELECT_statement ])]
การใช้คำสำคัญ INSENSITIVE จะสร้าง เคอร์เซอร์แบบคงที่. การเปลี่ยนแปลงข้อมูลไม่ได้รับอนุญาต ยิ่งกว่านั้น จะไม่แสดง การเปลี่ยนแปลงทำโดยผู้ใช้รายอื่น หากไม่มีคีย์เวิร์ด INSENSITIVE แสดงว่า เคอร์เซอร์แบบไดนามิก.
เมื่อระบุคีย์เวิร์ด SCROLL จะสร้าง เคอร์เซอร์สามารถเลื่อนไปในทิศทางใดก็ได้ทำให้สามารถใช้คำสั่งใดก็ได้ ตัวอย่าง. หากละเว้นข้อโต้แย้งนี้แล้ว เคอร์เซอร์จะ สม่ำเสมอ, เช่น. การรับชมจะเป็นไปได้ในทิศทางเดียวเท่านั้นตั้งแต่ต้นจนจบ
คำสั่ง SELECT ระบุเนื้อความของแบบสอบถาม SELECT ซึ่งกำหนดชุดผลลัพธ์ของแถว เคอร์เซอร์.
เมื่อมีการระบุอาร์กิวเมนต์ FOR READ_ONLY ก เคอร์เซอร์อ่านอย่างเดียว และไม่อนุญาตให้แก้ไขข้อมูล มันแตกต่างจาก คงที่แม้ว่าอย่างหลังจะไม่อนุญาตให้คุณเปลี่ยนแปลงข้อมูลก็ตาม เนื่องจากสามารถประกาศเคอร์เซอร์แบบอ่านอย่างเดียวได้ เคอร์เซอร์แบบไดนามิกซึ่งจะแสดง การเปลี่ยนแปลงสร้างโดยผู้ใช้รายอื่น
การสร้าง เคอร์เซอร์ด้วยอาร์กิวเมนต์ FOR UPDATE ช่วยให้คุณสามารถดำเนินการได้ ข้อมูลการเปลี่ยนแปลงเคอร์เซอร์ในคอลัมน์ที่ระบุ หรือในกรณีที่ไม่มีอาร์กิวเมนต์ column_name OF ในทุกคอลัมน์
ในสภาพแวดล้อม MS SQL Server ไวยากรณ์ต่อไปนี้สำหรับคำสั่ง create ได้รับการยอมรับ เคอร์เซอร์:
<создание_курсора>::= ประกาศ cursor_name เคอร์เซอร์สำหรับ SELECT_statement ]]
การใช้คำหลัก LOCAL จะสร้างท้องถิ่น เคอร์เซอร์ A ที่มองเห็นได้เฉพาะภายในแพ็คเกจ ทริกเกอร์ ขั้นตอนการจัดเก็บ หรือฟังก์ชันที่ผู้ใช้กำหนดที่สร้างขึ้น เมื่อแพ็กเกจ ทริกเกอร์ ขั้นตอน หรือฟังก์ชันเสร็จสมบูรณ์ เคอร์เซอร์ถูกทำลายโดยปริยาย ในการส่งเนื้อหา เคอร์เซอร์ภายนอกโครงสร้างที่สร้างขึ้น คุณต้องกำหนดอาร์กิวเมนต์ OUTPUT ให้กับพารามิเตอร์
หากระบุคีย์เวิร์ด GLOBAL คีย์เวิร์ด global เคอร์เซอร์; มีอยู่จนกว่าการเชื่อมต่อปัจจุบันจะปิด
การระบุ FORWARD_ONLY จะสร้าง เคอร์เซอร์ตามลำดับ ; การสุ่มตัวอย่างข้อมูลสามารถดำเนินการได้ในทิศทางจากบรรทัดแรกถึงบรรทัดสุดท้ายเท่านั้น
ระบุการสร้าง SCROLL เคอร์เซอร์แบบเลื่อนได้; สามารถเข้าถึงข้อมูลได้ในลำดับและทิศทางใดก็ได้
ระบุการสร้าง STATIC เคอร์เซอร์แบบคงที่.
การระบุ KEYSET จะสร้างเคอร์เซอร์คีย์
ระบุการสร้าง DYNAMIC เคอร์เซอร์แบบไดนามิก.
ถ้าเพื่อ เคอร์เซอร์ READ_ONLY ระบุอาร์กิวเมนต์ FAST_FORWARD ที่สร้างขึ้นแล้ว เคอร์เซอร์จะได้รับการปรับให้เหมาะสมเพื่อการเข้าถึงข้อมูลที่รวดเร็ว อาร์กิวเมนต์นี้ไม่สามารถใช้ร่วมกับอาร์กิวเมนต์ FORWARD_ONLY และ OPTIMISTIC
ใน เคอร์เซอร์ห้ามสร้างด้วยอาร์กิวเมนต์ OPTIMISTIC เปลี่ยนและ กำลังลบแถวซึ่งมีการเปลี่ยนแปลงหลังจากนั้น การเปิดเคอร์เซอร์.
โดยการระบุอาร์กิวเมนต์ TYPE_WARNING เซิร์ฟเวอร์จะแจ้งให้ผู้ใช้ทราบเกี่ยวกับการเปลี่ยนแปลงประเภทโดยนัย เคอร์เซอร์หากเข้ากันไม่ได้กับแบบสอบถาม SELECT
การเปิดเคอร์เซอร์
สำหรับ การเปิดเคอร์เซอร์และกรอกข้อมูลตามที่กำหนดระหว่างการสร้าง เคอร์เซอร์แบบสอบถาม SELECT ใช้คำสั่งต่อไปนี้:
เปิด ((cursor_name ) |@cursor_variable_name)
หลังจาก การเปิดเคอร์เซอร์คำสั่ง SELECT ที่เกี่ยวข้องจะถูกดำเนินการ ซึ่งเอาต์พุตจะถูกจัดเก็บไว้ในหน่วยความจำแบบเลเยอร์
กำลังดึงข้อมูลจากเคอร์เซอร์
ทันทีหลังจากที่ การเปิดเคอร์เซอร์คุณสามารถเลือกเนื้อหา (ผลลัพธ์ของแบบสอบถามที่เกี่ยวข้อง) ด้วยคำสั่งต่อไปนี้:
ดึงข้อมูล [ จาก ]((cursor_name )| @cursor_variable_name ) ]
การระบุ FIRST จะส่งกลับแถวแรกของชุดผลลัพธ์ที่สมบูรณ์ เคอร์เซอร์ซึ่งกลายเป็นบรรทัดปัจจุบัน
การระบุ LAST จะส่งกลับแถวล่าสุด เคอร์เซอร์. มันยังกลายเป็นเส้นปัจจุบัน
การระบุ NEXT จะส่งกลับแถวทันทีหลังจากแถวปัจจุบันในชุดผลลัพธ์ที่สมบูรณ์ ตอนนี้มันกลายเป็นปัจจุบัน คำสั่ง FETCH ใช้วิธีนี้เป็นค่าเริ่มต้น ตัวอย่างเส้น
คีย์เวิร์ด PRIOR ส่งคืนบรรทัดก่อนบรรทัดปัจจุบัน เธอกลายเป็นปัจจุบัน
ABSOLUTE(rownumber | @rownumbervariable) ส่งคืนแถวด้วยลำดับสัมบูรณ์ในชุดผลลัพธ์แบบเต็ม เคอร์เซอร์. หมายเลขบรรทัดสามารถระบุได้โดยใช้ค่าคงที่หรือเป็นชื่อของตัวแปรที่เก็บหมายเลขบรรทัด ตัวแปรจะต้องมีชนิดข้อมูลจำนวนเต็ม ระบุทั้งค่าบวกและค่าลบ หากคุณระบุค่าบวก สตริงจะถูกนับจากจุดเริ่มต้นของชุด ซึ่งเป็นค่าลบ - จากจุดสิ้นสุด เส้นที่เลือกจะกลายเป็นเส้นปัจจุบัน หากระบุเป็น null จะไม่มีการส่งคืนสตริง
อาร์กิวเมนต์ RELATIVE (line_count | @line_count_variable) ส่งคืนสตริงที่เป็นจำนวนบรรทัดที่ระบุหลังจากบรรทัดปัจจุบัน หากคุณระบุค่าลบสำหรับจำนวนบรรทัด ระบบจะส่งคืนบรรทัดที่เป็นจำนวนบรรทัดที่ระบุก่อนที่จะถึงบรรทัดปัจจุบัน การระบุค่าว่างจะส่งคืนแถวปัจจุบัน แถวที่ส่งคืนจะกลายเป็นแถวปัจจุบัน
ถึง เปิดเคอร์เซอร์ส่วนกลางโดยต้องมีคีย์เวิร์ด GLOBAL ก่อนชื่อ ชื่อ เคอร์เซอร์ยังสามารถระบุได้โดยใช้ตัวแปร
โครงสร้าง INTO @variable_name [,...n] ระบุรายการตัวแปรที่จะเก็บค่าคอลัมน์ที่สอดคล้องกันของสตริงที่ส่งคืน ลำดับที่ระบุตัวแปรจะต้องตรงกับลำดับของคอลัมน์ใน เคอร์เซอร์และประเภทข้อมูลของตัวแปรคือประเภทข้อมูลในคอลัมน์ เคอร์เซอร์. หากไม่ได้ระบุโครงสร้าง INTO พฤติกรรมของคำสั่ง FETCH จะคล้ายคลึงกับพฤติกรรมของคำสั่ง SELECT - ข้อมูลจะแสดงบนหน้าจอ
เคอร์เซอร์ถูกกำหนดไว้ มีการให้คำอธิบายประเภทและลักษณะการทำงาน: เคอร์เซอร์แบบคงที่ ไดนามิก ลำดับ และคีย์ มีการอธิบายหลักการของการควบคุมเคอร์เซอร์: การสร้างและการเปิดเคอร์เซอร์ การอ่านข้อมูล การปิดเคอร์เซอร์ มีตัวอย่างการเขียนโปรแกรมเคอร์เซอร์ให้แนวคิดของเคอร์เซอร์
โดยทั่วไปแบบสอบถามฐานข้อมูลเชิงสัมพันธ์จะส่งกลับข้อมูลหลายแถว (บันทึก) แต่แอปพลิเคชันจะประมวลผลเพียงครั้งละหนึ่งระเบียนเท่านั้น แม้ว่าจะเกี่ยวข้องกับหลายแถวในเวลาเดียวกัน (เช่น การส่งออกข้อมูลในรูปแบบของสเปรดชีต) ก็ยังคงมีจำนวนจำกัด นอกจากนี้ เมื่อแก้ไข ลบ หรือเพิ่มข้อมูล หน่วยของงานคือแถว ในสถานการณ์นี้ แนวคิดของเคอร์เซอร์จะปรากฏขึ้นข้างหน้า และในบริบทนี้ เคอร์เซอร์จะเป็นตัวชี้ไปยังแถว
เคอร์เซอร์ใน SQL คือขอบเขตในหน่วยความจำฐานข้อมูลที่มีไว้สำหรับจัดเก็บคำสั่ง SQL สุดท้าย ถ้าคำสั่งปัจจุบันเป็นการสืบค้นฐานข้อมูล สตริงข้อมูลการสืบค้นที่เรียกว่าค่าปัจจุบันหรือแถวเคอร์เซอร์ปัจจุบันก็จะถูกจัดเก็บไว้ในหน่วยความจำด้วย พื้นที่ที่ระบุในหน่วยความจำได้รับการตั้งชื่อและพร้อมใช้งานสำหรับแอปพลิเคชันโปรแกรม
โดยทั่วไปแล้ว เคอร์เซอร์จะใช้เพื่อเลือกชุดย่อยของข้อมูลที่จัดเก็บไว้ในฐานข้อมูล แอปพลิเคชันสามารถตรวจสอบบรรทัดเคอร์เซอร์ได้ครั้งละหนึ่งบรรทัด เคอร์เซอร์มักใช้ในคำสั่ง SQL ที่ฝังอยู่ในแอปพลิเคชันโปรแกรมที่เขียนด้วยภาษาขั้นตอน บางส่วนถูกสร้างขึ้นโดยเซิร์ฟเวอร์ฐานข้อมูลโดยปริยาย ในขณะที่บางส่วนถูกกำหนดโดยโปรแกรมเมอร์
ตามมาตรฐาน SQL เมื่อทำงานกับเคอร์เซอร์สามารถแยกแยะการกระทำหลักดังต่อไปนี้:
- การสร้างหรือ การประกาศเคอร์เซอร์;
- การเปิดเคอร์เซอร์, เช่น. กรอกข้อมูลที่เก็บไว้ในหน่วยความจำหลายระดับ
- ดึงข้อมูลจากเคอร์เซอร์และเปลี่ยนแถวข้อมูลด้วย
- เคอร์เซอร์ปิดหลังจากนั้นโปรแกรมผู้ใช้จะไม่สามารถเข้าถึงได้
- ปล่อยเคอร์เซอร์, เช่น. การลบเคอร์เซอร์ที่เป็นออบเจ็กต์ เนื่องจากการปิดเคอร์เซอร์ไม่จำเป็นต้องปล่อยหน่วยความจำที่เกี่ยวข้อง
ในการใช้งานที่แตกต่างกัน คำจำกัดความของเคอร์เซอร์อาจมีความแตกต่างบางประการ ตัวอย่างเช่น บางครั้งนักพัฒนาจะต้องปล่อยหน่วยความจำที่จัดสรรให้กับเคอร์เซอร์อย่างชัดเจน หลังจาก ปล่อยเคอร์เซอร์หน่วยความจำที่เกี่ยวข้องก็ถูกปลดปล่อยเช่นกัน ทำให้สามารถนำชื่อของเขากลับมาใช้ซ้ำได้ ในการใช้งานอื่นๆ เคอร์เซอร์ปิดหน่วยความจำถูกจัดสรรคืนโดยปริยาย ทันทีหลังจากการกู้คืน จะพร้อมใช้งานสำหรับการดำเนินการอื่นๆ: เปิดเคอร์เซอร์อื่นฯลฯ
ในบางกรณี การใช้เคอร์เซอร์เป็นสิ่งที่หลีกเลี่ยงไม่ได้ อย่างไรก็ตาม หากเป็นไปได้ ควรหลีกเลี่ยงและทำงานกับคำสั่งประมวลผลข้อมูลมาตรฐาน: SELECT , UPDATE , INSERT , DELETE นอกเหนือจากข้อเท็จจริงที่ว่าเคอร์เซอร์ไม่อนุญาตให้คุณดำเนินการแก้ไขกับจำนวนข้อมูลทั้งหมดแล้ว ความเร็วของการดำเนินการประมวลผลข้อมูลโดยใช้เคอร์เซอร์ยังต่ำกว่าความเร็วของเครื่องมือ SQL มาตรฐานอย่างเห็นได้ชัด
การใช้งานเคอร์เซอร์ในสภาพแวดล้อม MS SQL Server
SQL Server รองรับเคอร์เซอร์สามประเภท:
- เคอร์เซอร์ SQL ส่วนใหญ่จะใช้ภายในทริกเกอร์ ขั้นตอนการจัดเก็บ และสคริปต์
- เคอร์เซอร์เซิร์ฟเวอร์ทำงานบนเซิร์ฟเวอร์และใช้อินเทอร์เฟซการเขียนโปรแกรมแอปพลิเคชันสำหรับ ODBC, OLE DB, DB_Library
- เคอร์เซอร์ไคลเอนต์ถูกนำไปใช้กับไคลเอนต์เอง โดยจะดึงชุดผลลัพธ์ทั้งหมดของแถวจากเซิร์ฟเวอร์และจัดเก็บไว้ในเครื่อง ซึ่งช่วยเพิ่มความเร็วในการประมวลผลข้อมูลโดยลดการสิ้นเปลืองของการดำเนินงานเครือข่าย
แอปพลิเคชันที่มีผู้ใช้หลายรายประเภทต่างๆ ต้องการการเข้าถึงข้อมูลแบบขนานประเภทต่างๆ แอปพลิเคชั่นบางตัวจำเป็นต้องเข้าถึงข้อมูลเกี่ยวกับการเปลี่ยนแปลงฐานข้อมูลทันที นี่เป็นเรื่องปกติสำหรับระบบการจองตั๋ว ในกรณีอื่นๆ เช่น ในระบบการรายงานทางสถิติ ความเสถียรของข้อมูลเป็นสิ่งสำคัญ เพราะหากมีการแก้ไขอย่างต่อเนื่อง โปรแกรมจะไม่สามารถแสดงข้อมูลได้อย่างมีประสิทธิภาพ แอปพลิเคชันที่แตกต่างกันจำเป็นต้องมีการใช้งานเคอร์เซอร์ที่แตกต่างกัน
ในสภาพแวดล้อม SQL Server ประเภทของเคอร์เซอร์จะแตกต่างกันในแง่ของความสามารถที่มีให้ ประเภทเคอร์เซอร์จะถูกกำหนดในขั้นตอนการสร้างและไม่สามารถเปลี่ยนแปลงได้ เคอร์เซอร์บางประเภทสามารถตรวจจับการเปลี่ยนแปลงที่ทำโดยผู้ใช้รายอื่นในแถวที่รวมอยู่ในชุดผลลัพธ์ อย่างไรก็ตาม SQL Server ติดตามการเปลี่ยนแปลงในแถวดังกล่าวเฉพาะในขั้นตอนการเข้าถึงแถวเท่านั้น และไม่อนุญาตให้แก้ไขการเปลี่ยนแปลงหลังจากอ่านแถวแล้ว
เคอร์เซอร์แบ่งออกเป็นสองประเภท: ติดต่อกันและเลื่อนได้ ตามลำดับช่วยให้คุณสามารถเลือกข้อมูลได้ในทิศทางเดียวเท่านั้นตั้งแต่ต้นจนจบ เคอร์เซอร์แบบเลื่อนได้ให้อิสระในการดำเนินการมากขึ้น - คุณสามารถเคลื่อนที่ได้ทั้งสองทิศทางและข้ามไปยังบรรทัดใดก็ได้ของชุดผลลัพธ์ของเคอร์เซอร์ หากโปรแกรมสามารถแก้ไขข้อมูลที่เคอร์เซอร์ชี้ไปจะเรียกว่าเลื่อนได้และแก้ไขได้ เมื่อพูดถึงเคอร์เซอร์ เราไม่ควรลืมเกี่ยวกับการแยกธุรกรรม เมื่อผู้ใช้รายหนึ่งแก้ไขบันทึก อีกคนหนึ่งอ่านด้วยเคอร์เซอร์ของตนเอง และยิ่งกว่านั้น ผู้ใช้ยังสามารถแก้ไขบันทึกเดียวกันได้ ซึ่งทำให้จำเป็นต้องรักษาความสมบูรณ์ของข้อมูล
SQL Server รองรับ static , dynamic , ติดต่อกันและควบคุมด้วยชุดกุญแจ
ในโครงการด้วย เคอร์เซอร์แบบคงที่ข้อมูลจะถูกอ่านจากฐานข้อมูลหนึ่งครั้งและจัดเก็บเป็นสแน็ปช็อต ( ณ เวลาใดเวลาหนึ่ง) ดังนั้นการเปลี่ยนแปลงที่ทำกับฐานข้อมูลโดยผู้ใช้รายอื่นจึงไม่สามารถมองเห็นได้ เป็นเวลาหนึ่ง, ซักพัก การเปิดเคอร์เซอร์เซิร์ฟเวอร์ได้รับการล็อคในทุกแถวที่รวมอยู่ในชุดผลลัพธ์ที่สมบูรณ์ เคอร์เซอร์แบบคงที่จะไม่เปลี่ยนแปลงหลังจากการสร้างและแสดงชุดข้อมูลที่มีอยู่ ณ เวลาที่เปิดเสมอ
หากผู้ใช้รายอื่นเปลี่ยนข้อมูลที่รวมอยู่ในเคอร์เซอร์ในตารางต้นฉบับ สิ่งนี้จะไม่ส่งผลกระทบต่อ เคอร์เซอร์แบบคงที่.
ใน เคอร์เซอร์แบบคงที่คุณไม่สามารถทำการเปลี่ยนแปลงได้ ดังนั้นจึงจะเปิดในโหมดอ่านอย่างเดียวเสมอ
เคอร์เซอร์แบบไดนามิกรักษาข้อมูลให้คงอยู่ แต่ต้องใช้ทรัพยากรเครือข่ายและซอฟต์แวร์ โดยใช้ เคอร์เซอร์แบบไดนามิกไม่มีการสร้างสำเนาที่สมบูรณ์ของข้อมูลต้นฉบับ แต่การเลือกแบบไดนามิกจะดำเนินการจากตารางแหล่งที่มาเฉพาะเมื่อผู้ใช้เข้าถึงข้อมูลบางอย่างเท่านั้น ในระหว่างการดึงข้อมูล เซิร์ฟเวอร์จะล็อกแถว และการเปลี่ยนแปลงใดๆ ที่ผู้ใช้ทำกับชุดผลลัพธ์ทั้งหมดของเคอร์เซอร์จะมองเห็นได้ในเคอร์เซอร์ อย่างไรก็ตาม หากผู้ใช้รายอื่นทำการเปลี่ยนแปลงหลังจากที่เคอร์เซอร์ดึงข้อมูลแล้ว พวกเขาจะไม่ปรากฏในเคอร์เซอร์
เคอร์เซอร์ควบคุมโดยชุดคีย์อยู่ตรงกลางระหว่างความสุดขั้วเหล่านี้ บันทึกจะถูกระบุ ณ เวลาของการสุ่มตัวอย่าง และด้วยเหตุนี้จึงมีการติดตามการเปลี่ยนแปลง เคอร์เซอร์ประเภทนี้มีประโยชน์สำหรับการเลื่อนกลับ เพื่อไม่ให้มองเห็นการเพิ่มและการลบแถวจนกว่าข้อมูลจะได้รับการอัปเดต และไดรเวอร์จะเลือกเวอร์ชันใหม่ของรายการหากมีการเปลี่ยนแปลง
เคอร์เซอร์ตามลำดับไม่อนุญาตให้ดึงข้อมูลในทิศทางตรงกันข้าม ผู้ใช้สามารถเลือกได้เฉพาะแถวตั้งแต่ต้นจนจบเคอร์เซอร์เท่านั้น เคอร์เซอร์ตามลำดับไม่เก็บชุดของแถวทั้งหมด พวกเขาจะถูกอ่านจากฐานข้อมูลทันทีที่เลือกในเคอร์เซอร์ซึ่งช่วยให้คุณสามารถสะท้อนการเปลี่ยนแปลงทั้งหมดที่ผู้ใช้ทำกับฐานข้อมูลแบบไดนามิกโดยใช้คำสั่ง INSERT , UPDATE , DELETE เคอร์เซอร์แสดงสถานะล่าสุดของข้อมูล
เคอร์เซอร์แบบคงที่ให้มุมมองข้อมูลที่มั่นคง เหมาะสำหรับระบบ "คลังสินค้า" ข้อมูล: การใช้งานสำหรับระบบการรายงานหรือเพื่อวัตถุประสงค์ทางสถิติและการวิเคราะห์ นอกจาก, เคอร์เซอร์แบบคงที่ดีกว่าคนอื่น ๆ ในการสุ่มตัวอย่างข้อมูลจำนวนมาก ในทางตรงกันข้ามในระบบการซื้อทางอิเล็กทรอนิกส์หรือการจองตั๋วจำเป็นต้องรับรู้ข้อมูลที่อัปเดตแบบไดนามิกเมื่อมีการเปลี่ยนแปลง ในกรณีเช่นนี้ ให้ใช้ เคอร์เซอร์แบบไดนามิก. ในแอปพลิเคชันเหล่านี้ ปริมาณข้อมูลที่ถ่ายโอนมักจะมีปริมาณน้อย และการเข้าถึงจะดำเนินการที่ระดับแถว (บันทึกส่วนบุคคล) การเข้าถึงแบบกลุ่มนั้นหายากมาก
การควบคุมเคอร์เซอร์ในสภาพแวดล้อม MS SQL Server
การควบคุมเคอร์เซอร์ดำเนินการโดยดำเนินการคำสั่งต่อไปนี้:
- ประกาศ - สร้างหรือ การประกาศเคอร์เซอร์;
- เปิด- การเปิดเคอร์เซอร์, เช่น. กรอกข้อมูล;
- ดึงข้อมูล- ดึงข้อมูลจากเคอร์เซอร์และการเปลี่ยนแถวข้อมูลด้วยเคอร์เซอร์
- ปิด- เคอร์เซอร์ปิด;
- จัดสรร- ปล่อยเคอร์เซอร์, เช่น. การลบเคอร์เซอร์เป็นวัตถุ
การประกาศเคอร์เซอร์
มาตรฐาน SQL จัดเตรียมคำสั่งต่อไปนี้เพื่อสร้างเคอร์เซอร์:
การใช้คำสำคัญ INSENSITIVE จะสร้าง เคอร์เซอร์แบบคงที่. การเปลี่ยนแปลงข้อมูลไม่ได้รับอนุญาต นอกจากนี้ การเปลี่ยนแปลงที่ทำโดยผู้ใช้รายอื่นจะไม่แสดง หากไม่มีคีย์เวิร์ด INSENSITIVE แสดงว่า เคอร์เซอร์แบบไดนามิก.
ด้วยการระบุคีย์เวิร์ด SCROLL เคอร์เซอร์ที่สร้างขึ้นสามารถเลื่อนไปในทิศทางใดก็ได้ ทำให้สามารถใช้คำสั่งการเลือกใดๆ ได้ หากละเว้นอาร์กิวเมนต์นี้ เคอร์เซอร์จะเป็น สม่ำเสมอ, เช่น. การรับชมจะเป็นไปได้ในทิศทางเดียวเท่านั้นตั้งแต่ต้นจนจบ
คำสั่ง SELECT ระบุเนื้อความของแบบสอบถาม SELECT ซึ่งใช้ในการกำหนดชุดผลลัพธ์ของแถวเคอร์เซอร์
การระบุอาร์กิวเมนต์ FOR READ_ONLY จะสร้างเคอร์เซอร์แบบอ่านอย่างเดียวและไม่อนุญาตให้มีการแก้ไขข้อมูลใดๆ มันแตกต่างจากแบบคงที่ แม้ว่าอย่างหลังจะไม่อนุญาตให้เปลี่ยนแปลงข้อมูลก็ตาม เนื่องจากสามารถประกาศเคอร์เซอร์แบบอ่านอย่างเดียวได้ เคอร์เซอร์แบบไดนามิกซึ่งจะแสดงการเปลี่ยนแปลงที่ทำโดยผู้ใช้รายอื่น
การสร้างเคอร์เซอร์ด้วยอาร์กิวเมนต์ FOR UPDATE ช่วยให้คุณสามารถดำเนินการในเคอร์เซอร์ได้ การเปลี่ยนแปลงข้อมูลในคอลัมน์ที่ระบุ หรือในกรณีที่ไม่มีอาร์กิวเมนต์ OF column_name ในทุกคอลัมน์
ในสภาพแวดล้อม MS SQL Server ยอมรับไวยากรณ์ต่อไปนี้สำหรับคำสั่งสร้างเคอร์เซอร์:
<создание_курсора>::= ประกาศ cursor_name เคอร์เซอร์สำหรับ SELECT_statement ]]
การใช้คีย์เวิร์ด LOCAL จะสร้างเคอร์เซอร์ในเครื่องที่มองเห็นได้เฉพาะภายในแพ็คเกจ ทริกเกอร์ ขั้นตอนการจัดเก็บ หรือฟังก์ชันที่ผู้ใช้กำหนดซึ่งสร้างขึ้น เมื่อแพ็กเกจ ทริกเกอร์ ขั้นตอน หรือฟังก์ชันสิ้นสุดลง เคอร์เซอร์จะถูกทำลายโดยปริยาย หากต้องการส่งผ่านเนื้อหาของเคอร์เซอร์ไปนอกโครงสร้างที่สร้างขึ้น คุณต้องกำหนดอาร์กิวเมนต์ OUTPUT ให้กับพารามิเตอร์
หากระบุคีย์เวิร์ด GLOBAL เคอร์เซอร์ส่วนกลางจะถูกสร้างขึ้น มีอยู่จนกว่าการเชื่อมต่อปัจจุบันจะปิด
การระบุ FORWARD_ONLY จะสร้าง เคอร์เซอร์ตามลำดับ; ข้อมูลสามารถสุ่มตัวอย่างได้ในทิศทางจากแถวแรกไปสุดท้ายเท่านั้น
ระบุการสร้าง SCROLL เคอร์เซอร์แบบเลื่อนได้; สามารถเข้าถึงข้อมูลได้ในลำดับและทิศทางใดก็ได้
ระบุการสร้าง STATIC เคอร์เซอร์แบบคงที่.
การระบุ KEYSET จะสร้างเคอร์เซอร์คีย์
ระบุการสร้าง DYNAMIC เคอร์เซอร์แบบไดนามิก.
หากคุณระบุอาร์กิวเมนต์ FAST_FORWARD สำหรับเคอร์เซอร์ READ_ONLY เคอร์เซอร์ที่สร้างขึ้นจะได้รับการปรับให้เหมาะสมเพื่อการเข้าถึงข้อมูลที่รวดเร็ว อาร์กิวเมนต์นี้ไม่สามารถใช้ร่วมกับอาร์กิวเมนต์ FORWARD_ONLY และ OPTIMISTIC
เคอร์เซอร์ที่สร้างขึ้นด้วยอาร์กิวเมนต์ OPTIMISTIC ป้องกันการแก้ไขและการลบแถวที่ได้รับการแก้ไขตั้งแต่นั้นมา การเปิดเคอร์เซอร์.
ด้วยอาร์กิวเมนต์ TYPE_WARNING เซิร์ฟเวอร์จะแจ้งให้ผู้ใช้ทราบถึงการเปลี่ยนแปลงประเภทเคอร์เซอร์โดยนัย หากเข้ากันไม่ได้กับแบบสอบถาม SELECT
การเปิดเคอร์เซอร์
สำหรับ การเปิดเคอร์เซอร์และกรอกข้อมูลจากที่ระบุเมื่อสร้างเคอร์เซอร์แบบสอบถาม SELECT จะใช้คำสั่งต่อไปนี้:
หลังจาก การเปิดเคอร์เซอร์คำสั่ง SELECT ที่เกี่ยวข้องจะถูกดำเนินการ ซึ่งเอาต์พุตจะถูกจัดเก็บไว้ในหน่วยความจำแบบเลเยอร์
กำลังดึงข้อมูลจากเคอร์เซอร์
ทันทีหลังจากที่ การเปิดเคอร์เซอร์คุณสามารถเลือกเนื้อหา (ผลลัพธ์ของแบบสอบถามที่เกี่ยวข้อง) ด้วยคำสั่งต่อไปนี้:
การระบุ FIRST จะส่งกลับแถวแรกของชุดผลลัพธ์เคอร์เซอร์แบบเต็ม ซึ่งจะกลายเป็นแถวปัจจุบัน
การระบุ LAST จะส่งกลับแถวล่าสุดของเคอร์เซอร์ มันยังกลายเป็นเส้นปัจจุบัน
การระบุ NEXT จะส่งกลับแถวทันทีหลังจากแถวปัจจุบันในชุดผลลัพธ์ที่สมบูรณ์ ตอนนี้มันกลายเป็นปัจจุบัน ตามค่าเริ่มต้น คำสั่ง FETCH จะใช้วิธีการดึงข้อมูลแถวนี้
คีย์เวิร์ด PRIOR ส่งคืนบรรทัดก่อนบรรทัดปัจจุบัน เธอกลายเป็นปัจจุบัน
การโต้แย้ง สัมบูรณ์ (line_number | @line_number_variable)ส่งคืนแถวด้วยลำดับสัมบูรณ์ในชุดผลลัพธ์ทั้งหมดของเคอร์เซอร์ หมายเลขบรรทัดสามารถระบุได้โดยใช้ค่าคงที่หรือเป็นชื่อของตัวแปรที่เก็บหมายเลขบรรทัด ตัวแปรจะต้องมีชนิดข้อมูลจำนวนเต็ม ระบุทั้งค่าบวกและค่าลบ หากคุณระบุค่าบวก สตริงจะถูกนับจากจุดเริ่มต้นของชุด ซึ่งเป็นค่าลบ - จากจุดสิ้นสุด เส้นที่เลือกจะกลายเป็นเส้นปัจจุบัน หากระบุเป็น null จะไม่มีการส่งคืนสตริง
การโต้แย้ง สัมพันธ์กัน (row_number | @row_number_variable)ส่งคืนสตริงที่เป็นจำนวนบรรทัดที่ระบุหลังจากบรรทัดปัจจุบัน หากคุณระบุค่าลบสำหรับจำนวนบรรทัด ระบบจะส่งคืนบรรทัดที่เป็นจำนวนบรรทัดที่ระบุก่อนที่จะถึงบรรทัดปัจจุบัน การระบุค่าว่างจะส่งคืนแถวปัจจุบัน แถวที่ส่งคืนจะกลายเป็นแถวปัจจุบัน
ถึง เปิดเคอร์เซอร์ส่วนกลางโดยต้องมีคีย์เวิร์ด GLOBAL ก่อนชื่อ ชื่อเคอร์เซอร์สามารถระบุได้โดยใช้ตัวแปร
ในการก่อสร้าง เข้า @variable_name [,...n]มีการระบุรายการตัวแปรโดยจะจัดเก็บค่าคอลัมน์ที่สอดคล้องกันของแถวที่ส่งคืน ลำดับของตัวแปรจะต้องตรงกับลำดับของคอลัมน์ในเคอร์เซอร์ และประเภทข้อมูลของตัวแปรจะต้องตรงกับประเภทข้อมูลในคอลัมน์เคอร์เซอร์ หากไม่ได้ระบุโครงสร้าง INTO พฤติกรรมของคำสั่ง FETCH จะคล้ายคลึงกับพฤติกรรมของคำสั่ง SELECT - ข้อมูลจะแสดงบนหน้าจอ
การเปลี่ยนแปลงและการลบข้อมูล
หากต้องการเปลี่ยนแปลงโดยใช้เคอร์เซอร์ คุณต้องออกคำสั่ง UPDATE ในรูปแบบต่อไปนี้:
คอลัมน์หลายคอลัมน์ในแถวปัจจุบันของเคอร์เซอร์สามารถเปลี่ยนแปลงได้ในการดำเนินการครั้งเดียว แต่คอลัมน์ทั้งหมดจะต้องอยู่ในตารางเดียวกัน
หากต้องการลบข้อมูลโดยใช้เคอร์เซอร์ ให้ใช้คำสั่ง DELETE ในรูปแบบต่อไปนี้:
เป็นผลให้บรรทัดที่ตั้งค่าปัจจุบันในเคอร์เซอร์จะถูกลบ
เคอร์เซอร์ปิด
หลังจากปิดเคอร์เซอร์จะไม่สามารถเข้าถึงได้สำหรับผู้ใช้โปรแกรม เมื่อปิด ล็อคทั้งหมดที่ตั้งไว้ระหว่างการทำงานจะถูกลบออก การปิดสามารถใช้ได้เฉพาะกับเคอร์เซอร์ที่เปิดอยู่เท่านั้น ปิดแต่ไม่ ปล่อยเคอร์เซอร์สามารถเปิดใหม่ได้ ไม่อนุญาตให้ปิดเคอร์เซอร์ที่ยังไม่ได้เปิด
ปล่อยเคอร์เซอร์
เคอร์เซอร์ปิดเลือกที่จะปล่อยหน่วยความจำที่เกี่ยวข้องกัน ในการใช้งานบางอย่าง คุณต้องจัดสรรคืนอย่างชัดเจนด้วยคำสั่ง DEALLOCATE หลังจาก ปล่อยเคอร์เซอร์หน่วยความจำยังว่าง และชื่อเคอร์เซอร์สามารถนำมาใช้ซ้ำได้
เพื่อควบคุมเมื่อถึงจุดสิ้นสุดของเคอร์เซอร์ ขอแนะนำให้ใช้ฟังก์ชัน: @@FETCH_STATUS
ฟังก์ชัน @@FETCH_STATUS ส่งคืน:
0 หากการดึงข้อมูลสำเร็จ
1 หากการดึงข้อมูลล้มเหลวเนื่องจากพยายามดึงแถวนอกเคอร์เซอร์
2 หากการดึงข้อมูลล้มเหลวเนื่องจากพยายามเข้าถึงแถวที่ถูกลบหรือแก้ไข
ประกาศ @id_kl INT, @firm VARCHAR(50), @fam VARCHAR(50), @ข้อความ VARCHAR(80), @nam VARCHAR(50), @d DATETIME, @p INT, @s INT SET @s=0 พิมพ์ " รายการช็อปปิ้ง" ประกาศ klient_cursor เคอร์เซอร์ท้องถิ่นสำหรับเลือกรหัสลูกค้า บริษัท นามสกุลจากลูกค้า WHERE City="Moscow" เรียงลำดับตามบริษัท นามสกุลเปิด klient_cursor ดึงข้อมูลถัดไปจาก klient_cursor เข้าสู่ @id_kl, @firm, @fam ในขณะที่ @@FETCH_STATUS =0 BEGIN SELECT @message="Customer "+@fam+ " Company "+ @firm PRINT @message SELECT @message="ชื่อผลิตภัณฑ์ วันที่ ราคาซื้อ" PRINT @message DECLARE tovar_cursor CURSOR FOR SELECT Item.Name, Deal.Date, Item . ราคา* ธุรกรรม ปริมาณเป็นต้นทุนจากธุรกรรมการรวมสินค้าโภคภัณฑ์ภายในกับสินค้าโภคภัณฑ์ ItemID=Trade.ItemID WHERE Trade.CustomerID=@id_kl เปิด tovar_cursor ดึงข้อมูลถัดไปจาก tovar_cursor เข้าสู่ @nam, @d, @p IF @@FETCH_STATUS<>0 พิมพ์ " ไม่มีการซื้อ" ในขณะที่ @@FETCH_STATUS=0 BEGIN SELECT @message=" "+@nam+" "+ CAST(@d AS CHAR(12))+" "+ CAST(@p AS CHAR(6)) PRINT @message SET @s=@s+@p ดึงข้อมูลถัดไปจาก tovar_cursor เข้าสู่ @nam, @d, @p END ปิด tovar_cursor DEALLOCATE tovar_cursor SELECT @message="Total cost "+ CAST(@s AS CHAR(6)) PRINT @message -- ย้ายไปยังไคลเอนต์ถัดไป-- ดึงข้อมูลถัดไปจาก klient_cursor เข้าสู่ @id_kl, @firm, @fam END CLOSE klient_cursor DEALLOCATE klient_cursor ตัวอย่างที่ 13.6 เคอร์เซอร์สำหรับแสดงรายการสินค้าที่ลูกค้าซื้อจากมอสโกและต้นทุนรวม
ตัวอย่างที่ 13.7พัฒนาเคอร์เซอร์แบบเลื่อนได้สำหรับลูกค้าจากมอสโก หากหมายเลขโทรศัพท์ขึ้นต้นด้วย 1 ให้ลบลูกค้าด้วยหมายเลขนั้น และในรายการเคอร์เซอร์แรก ให้แทนที่หลักแรกในหมายเลขโทรศัพท์ด้วย 4
ประกาศ @บริษัท VARCHAR(50), @fam VARCHAR(50), @tel VARCHAR(8), @ข้อความ VARCHAR(80) พิมพ์ "รายชื่อลูกค้า" ประกาศ klient_cursor เคอร์เซอร์ GLOBAL SCROLL KEYSET สำหรับบริษัทที่เลือก, นามสกุล, โทรศัพท์จากลูกค้า WHERE City ="Moscow" เรียงลำดับตามบริษัท นามสกุลสำหรับการอัปเดต OPEN klient_cursor FETCH NEXT จาก klient_cursor INTO @firm, @fam, @tel WHILE @@FETCH_STATUS=0 BEGIN SELECT @message="Client "+@fam+" Company "+ @firm " Phone "+ @tel PRINT @message -- หากหมายเลขโทรศัพท์ขึ้นต้นด้วย 1, -- ลบไคลเอ็นต์ด้วยหมายเลขนั้น IF @tel LIKE '1%' DELETE Client WHERE CURRENT OF klient_cursor ELSE -- ข้ามไปยังไคลเอ็นต์ถัดไป FETCH NEXT จาก klient_cursor เข้าสู่ @firm, @fam, @tel END FETCH ABSOLUTE 1 จาก klient_cursor เข้าสู่ @firm, @fam, @tel -- ในรายการแรก แทนที่หลักแรกใน -- หมายเลขโทรศัพท์ด้วย 4 UPDATE Client SET Phone='4' + RIGHT(@ tel,LEN(@tel)-1)) โดยที่ปัจจุบันของ klient_cursor SELECT @message="Client "+@fam+" Firm "+ @firm " Phone "+ @tel PRINT @message CLOSE klient_cursor DEALLOCATE klient_cursor ตัวอย่างที่ 13.7 เคอร์เซอร์แบบเลื่อนได้สำหรับลูกค้าจากมอสโก
ตัวอย่างที่ 13.8การใช้งาน เคอร์เซอร์เป็นพารามิเตอร์เอาท์พุทของขั้นตอน. ขั้นตอนส่งคืนชุดข้อมูล - รายการสินค้า
การเรียกขั้นตอนและการพิมพ์ข้อมูลจากเคอร์เซอร์เอาต์พุตจะดำเนินการดังนี้:
ประกาศ @my_cur CURSOR ประกาศ @n VARCHAR(20) EXEC my_proc @cur=@my_cur เอาต์พุตดึงข้อมูลถัดไปจาก @my_cur เข้าสู่ @n SELECT @n ในขณะที่ (@@FETCH_STATUS=0) เริ่มดึงข้อมูลถัดไปจาก @my_cur เข้าสู่ @n SELECT @n จบ ปิด @my_cur DEALLOCATE @my_cur
1) แนวคิดของเคอร์เซอร์
ใน SQL แบบโต้ตอบ ไม่มีความแตกต่างระหว่างแบบสอบถามแถวเดียวและหลายแถว ใน SQL แบบฝัง การสืบค้นเหล่านี้จะถูกดำเนินการแตกต่างออกไป แบบสอบถามบรรทัดเดียวส่งคืนหนึ่งแถวและเราได้พิจารณาแล้ว เมื่อการสืบค้นส่งผลให้เกิดมากกว่าหนึ่งแถว SQL แบบฝังจะต้องอนุญาตให้แอปพลิเคชันดึงผลลัพธ์การสืบค้นทีละแถว สำหรับสิ่งนี้ จะใช้เคอร์เซอร์ เคอร์เซอร์เป็นตัวแปรที่เกี่ยวข้องกับแบบสอบถาม ค่าของมันคือแต่ละแถวที่ตรงกับแบบสอบถาม เช่นเดียวกับตัวแปร เคอร์เซอร์จะต้องได้รับการประกาศก่อนจึงจะสามารถใช้งานได้ เคอร์เซอร์ได้รับการออกแบบสำหรับการประมวลผลทีละบรรทัดต่างจากมุมมอง
2) การประกาศเคอร์เซอร์
ประกาศ [{}] [[เลขที่] เลื่อน] เคอร์เซอร์ [{ด้วย|ไม่มี} ถือ] สำหรับ [สำหรับ {อ่านอย่างเดียว|อัปเดต [ของ ]}]
3) คำหลัก
. ละเอียดอ่อน | ไม่ละเอียดอ่อน | ไม่ละเอียดอ่อน– การเปลี่ยนแปลงในชุดผลลัพธ์จะมองเห็นได้ | ห้าม (แก้ไขด้วยสำเนาของชุดข้อมูล)|DBMS ตัดสินใจเองว่าจะทำสำเนาหรือไม่ (ดำเนินการตามค่าเริ่มต้น)
. ด้วย|โดยไม่ต้องถือ– เปิดทิ้งไว้ | ปิดเคอร์เซอร์หากพบคำสั่ง COMMIT
. เลื่อน– [ห้าม] ดึงแถวผลลัพธ์แบบสุ่ม
. สำหรับการอ่านเท่านั้น– กำหนดเคอร์เซอร์แบบอ่านอย่างเดียว
. สำหรับการอัปเดตของ– บล็อกเฉพาะคอลัมน์ที่ระบุไม่ให้อัปเดต
4) การประกาศเคอร์เซอร์ใน SQL Server
ประกาศ เคอร์เซอร์ [ท้องถิ่น|ทั่วโลก] [FORWARD_ONLY|เลื่อน] [คงที่|ชุดคีย์|ไดนามิก|FAST_FORWARD] [อ่าน_เท่านั้น|SCROLL_LOCKS|ในแง่ดี] สำหรับ [สำหรับการอัปเดต [ของ ]]
. คงที่– กำหนดเคอร์เซอร์ที่สร้างสำเนาชั่วคราวของข้อมูลสำหรับใช้โดยเคอร์เซอร์ แบบสอบถามเคอร์เซอร์ทั้งหมดอ้างอิงถึงตารางชั่วคราวที่ระบุใน tempdb ดังนั้นการเปลี่ยนแปลงตารางฐานจะไม่ส่งผลกระทบต่อข้อมูลที่ส่งกลับโดยการเลือกสำหรับเคอร์เซอร์นั้น และตัวเคอร์เซอร์เองก็ไม่อนุญาตให้มีการเปลี่ยนแปลง
. คีย์เซ็ต– ระบุว่าความเป็นสมาชิกหรือลำดับของแถวในเคอร์เซอร์จะไม่เปลี่ยนแปลงหลังจากเปิดขึ้น ชุดคีย์ที่ระบุแถวโดยไม่ซ้ำกันจะถูกฝังอยู่ในตารางในฐานข้อมูล tempdb ชื่อชุดคีย์
. พลวัต– กำหนดเคอร์เซอร์ที่แสดงการเปลี่ยนแปลงข้อมูลทั้งหมดที่ทำกับแถวในชุดผลลัพธ์ขณะดูเคอร์เซอร์นี้ ค่าข้อมูล ลำดับ และสมาชิกของแถวในแต่ละตัวอย่างสามารถเปลี่ยนแปลงได้ ตัวเลือกการเลือกแบบสัมบูรณ์ไม่ได้รับการสนับสนุนโดยเคอร์เซอร์แบบไดนามิก
. เร็ว_ไปข้างหน้า– ระบุเคอร์เซอร์ FORWARD_ONLY, READ_ONLY ที่จะเปิดใช้งานการปรับประสิทธิภาพให้เหมาะสม ไม่สามารถระบุตัวเลือก FAST_FORWARD ด้วยตัวเลือก SCROLL หรือ FOR_UPDATE
. SCROLL_LOCKS– บ่งชี้ว่าการอัปเดตหรือการลบตำแหน่งที่ทำผ่านเคอร์เซอร์นั้นรับประกันว่าจะสำเร็จ SQL Server จะล็อกแถวขณะที่อ่านลงในเคอร์เซอร์เพื่อให้แน่ใจว่าแถวเหล่านั้นพร้อมสำหรับการแก้ไขในภายหลัง ไม่สามารถระบุตัวเลือก SCROLL_LOCKS ด้วยตัวเลือก FAST_FORWARD หรือ STATIC
. มองโลกในแง่ดี– ระบุว่าการอัปเดตหรือการลบตำแหน่งที่ทำผ่านเคอร์เซอร์จะไม่ถูกดำเนินการหากแถวได้รับการอัปเดตตั้งแต่อ่านลงในเคอร์เซอร์ SQL Server ไม่ล็อกแถวเมื่อมีการอ่านลงในเคอร์เซอร์ แต่จะมีการเปรียบเทียบคอลัมน์การประทับเวลา (หรือเช็คซัมหากตารางไม่มีคอลัมน์การประทับเวลา) เพื่อพิจารณาว่าแถวมีการเปลี่ยนแปลงหรือไม่นับตั้งแต่อ่านลงในเคอร์เซอร์ หากมีการแก้ไขแถว จะไม่สามารถแก้ไขหรือลบตำแหน่งได้ ไม่สามารถระบุตัวเลือก OPTIMISTIC ด้วยตัวเลือก FAST_FORWARD
5) การเปิดเคอร์เซอร์
6) แยกบรรทัดออกจากเคอร์เซอร์
ดึงข้อมูล [{ถัดไป|ก่อนหน้า|ครั้งแรก|สุดท้าย|{สัมบูรณ์ | ญาติ }}]
จาก เข้าไปข้างใน
7) ตัวเลือกการวางตำแหน่งเคอร์เซอร์
. ถัดไป|ก่อนหน้า|ครั้งแรก|สุดท้าย– ไปที่แถวถัดไป|ก่อนหน้า|แรก|แถวสุดท้ายของชุดผลลัพธ์
. ญาติ±N– เป็นเส้นที่มีการชดเชยบวกหรือลบสัมพันธ์กับเส้นปัจจุบัน
. สัมบูรณ์±N– ไปยังบรรทัดที่มีหมายเลขตำแหน่งสัมบูรณ์ที่ระบุอย่างชัดเจนจากจุดเริ่มต้นหรือจุดสิ้นสุดของเคอร์เซอร์
บันทึก: ใน SQL Server อนุญาตให้ใช้ตัวแปรจำนวนเต็ม @N แทน N
8) การปิดเคอร์เซอร์
9) หมายเหตุเกี่ยวกับเคอร์เซอร์
. หากเคอร์เซอร์มีมากกว่าหนึ่งบรรทัด จำเป็นต้องจัดระเบียบลูปเพื่อดึงข้อมูลออกมาด้วยการตรวจสอบเป็นระยะเพื่อดูว่าถึงบรรทัดสุดท้ายแล้วหรือไม่
. ต่างจากตารางและมุมมอง แถวเคอร์เซอร์จะถูกเรียงลำดับอย่างชัดเจนโดยใช้ส่วน สั่งโดยหรือตามอนุสัญญาที่นำมาใช้ใน DBMS โดยเฉพาะ
. เคอร์เซอร์ยังใช้เพื่อเลือกกลุ่มแถวจากตาราง ซึ่งสามารถอัปเดตหรือลบได้ทีละแถว
. เพื่อให้เคอร์เซอร์สามารถอัปเดตได้ จะต้องเป็นไปตามเกณฑ์เดียวกันกับมุมมอง กล่าวคือ จะต้องไม่มีส่วนต่างๆ ยูเนี่ยน, เรียงลำดับตาม, จัดกลุ่มตาม, แตกต่าง.
10) ตัวอย่างการลบข้อมูลจากเคอร์เซอร์
exec sql ประกาศเคอร์เซอร์ Cur1 เพื่อเลือก * จากลูกค้า
เรตติ้งที่ไหน
// พิมพ์ (@f1+' '+แปลง(Varchar(5),@f2))
exec sql ลบออกจากลูกค้า
โดยที่กระแสของ Cur1; ) – นำข้อมูลที่จะลบออกจากเคอร์เซอร์
ยังไม่เสร็จ:
exec sql ปิดเคอร์เซอร์ Cur1; - ปิดเคอร์เซอร์
ออก();
11) ตัวอย่างการเพิ่มค่าคอมมิชชั่น
exec sql ประกาศเคอร์เซอร์ CurCust สำหรับ select * จาก SalesPeople
โดยที่ SNum ใน (เลือก SNum จากลูกค้าโดยที่ Rating=300) - กำหนดเคอร์เซอร์
exec sql เปิดเคอร์เซอร์ CurCust; - ดำเนินการเคอร์เซอร์
ในขณะที่ (sqlca.sqlcode==0) ( - สร้างลูปเพื่ออัปเดตข้อมูลในตาราง
exec sql ดึง CurCust เข้าสู่: Id_num, :SalesPerson, :Loc, :Comm;
exec sql อัพเดต SalesPeople ตั้งค่า Comm=Comm+.01 โดยที่ปัจจุบัน
ของเคอร์คัส; ) – ข้อมูลที่จะอัปเดตถูกนำมาจากเคอร์เซอร์
exec sql ปิดเคอร์เซอร์ CurCust; - ปิดเคอร์เซอร์SELECT S.Name, MAX(S.City) AS City, SUM(O.Amt) AS Amt จาก SalesPeople S INNER JOIN คำสั่งซื้อ O ON S.SNum=O.SNum GROUP BY S.Name ORDER BY 2
ประกาศ Cur1 เลื่อนเคอร์เซอร์สำหรับ SELECT S.Name, MAX(S.City) AS City, SUM(O.Amt) AS Amt จาก SalesPeople S INNER JOIN orders O ON S.SNum=O.SNum GROUP BY S.Name ORDER BY 2
เปิดเคอร์1
ดึงข้อมูลถัดไปจาก Cur1
ในขณะที่ @@FETCH_STATUS=0
เริ่ม
ดึงข้อมูลถัดไปจาก Cur1
จบ
ปิด Cur1
จัดสรร Cur1
บทความที่คล้ายกัน
-
กฎทั่วไปของคำสั่ง DECLARE CURSOR เคอร์เซอร์ sql คืออะไร
เคอร์เซอร์ที่ชัดเจนคือคำสั่ง SELECT ที่กำหนดไว้อย่างชัดเจนในส่วนการประกาศของโปรแกรม เมื่อมีการประกาศเคอร์เซอร์ที่ชัดเจน จะมีการตั้งชื่อเคอร์เซอร์นั้น สำหรับคำสั่ง INSERT, UPDATE, MERGE และ DELETE ไม่สามารถกำหนดเคอร์เซอร์ที่ชัดเจนได้....
-
เครื่องมือค้นหาของรัสเซีย
ในช่วงเวลาที่การพัฒนาอินเทอร์เน็ตเพิ่งเริ่มต้น ปริมาณข้อมูลที่มีอยู่ค่อนข้างน้อย และมีผู้ใช้เครือข่ายน้อย ในขั้นเริ่มต้นของการพัฒนาเครือข่ายนั้น พนักงานของมหาวิทยาลัยและการวิจัย ...
-
สาเหตุและการเยียวยา
บางครั้งเบราว์เซอร์อาจแจ้งผู้ใช้ว่ามีข้อผิดพลาดของสคริปต์เกิดขึ้น นอกจากนี้ยังใช้กับ Internet Explorer มาตรฐานด้วย อย่างไรก็ตามความล้มเหลวดังกล่าวเกิดขึ้นบ่อยกว่าในเบราว์เซอร์อื่น สิ่งที่อาจทำให้เกิดปัญหานี้...
-
แท็กชื่อ - ชื่อหน้า
ฉันบอกทันทีว่าในบทความนี้คุณจะไม่พบเคล็ดลับโง่ ๆ เช่นการระบุขนาดของชื่อหรือค่าตัวเลขของความหนาแน่นของคีย์ ทั้งหมดนี้เป็นเกมหายากที่แพร่กระจายไปทั่วเครือข่ายเหมือนไวรัส ... พื้นฐานของการสะกดที่ถูกต้อง ...
-
เครื่องมือค้นหาของรัสเซีย
บนอินเทอร์เน็ต เว็บไซต์พิเศษที่ผู้ใช้สามารถรับลิงก์ไปยังเว็บไซต์ที่ตรงกับคำขอนี้ได้ตามคำขอ ระบบค้นหาประกอบด้วยสามองค์ประกอบ: หุ่นยนต์ค้นหา 1 ตัว; 2 ดัชนีระบบ และ 3...
-
ชื่อหน้า - คืออะไรและควรเป็นอย่างไร?
สวัสดีเพื่อน! ถึงเวลาสำหรับกระทู้เชิงทฤษฎีที่มีประโยชน์อีกครั้ง และวันนี้ฉันอยากจะพูดเกี่ยวกับแท็กที่สำคัญที่สุด - ชื่อเรื่อง ชื่อเพจคืออะไร มีผลกระทบอย่างไร วิธีเขียนชื่อเพจให้ถูกต้องจากมุมมองของ SEO สิ่งที่สามารถ ...