ขั้นตอนเคอร์เซอร์ในเซิร์ฟเวอร์ sql คำสั่ง DECLARE CURSOR เป็นกฎทั่วไป การจัดสรรหน่วยความจำ การจัดสรรคืน

เคอร์เซอร์ที่ชัดเจนคือคำสั่ง 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

ตัวอย่างการประกาศเคอร์เซอร์ที่ชัดเจน

• เคอร์เซอร์ที่ไม่มีพารามิเตอร์. ชุดผลลัพธ์ของแถวของเคอร์เซอร์นี้คือชุดรหัสบริษัทที่เลือกจากทุกแถวในตาราง:

CURSOR company_cur คือ SELECT company_id จากบริษัท;

• เคอร์เซอร์พร้อมพารามิเตอร์ชุดผลลัพธ์ของแถวสำหรับเคอร์เซอร์นี้มีแถวเดียวที่มีชื่อบริษัทตรงกับค่าของพารามิเตอร์ที่ส่ง:

CURSOR name_cur (company_id_in ใน NUMBER) คือชื่อที่เลือกจาก บริษัท โดยที่ company_id = company_id_in;

• เคอร์เซอร์ที่มีส่วนคำสั่ง RETURN. ชุดผลลัพธ์ของเคอร์เซอร์นี้มีข้อมูลทั้งหมดในตารางพนักงานสำหรับแผนก ID 10:

CURSOR emp_cur RETURN พนักงาน% ROWTYPE ถูกเลือก * จากพนักงาน WHERE department_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:

ประกาศเคอร์เซอร์ company_cur คือ SELECT ...; company_rec company_cur%ROWTYPE; เริ่มเปิด company_cur; FETCH company_cur เข้าสู่ company_rec;

• การดึงข้อมูลจากเคอร์เซอร์เข้าสู่ตัวแปร:

ดึง new_balance_cur เข้าสู่ new_balance_dollars;

• การดึงข้อมูลจากเคอร์เซอร์ไปยังแถวของตาราง PL/SQL, ตัวแปร และตัวแปรการผูกของ Oracle Forms:

FETCH emp_name_cur เข้าสู่ emp_name(1), จ้างงาน, :dept.min_salary;

ข้อมูลที่ดึงมาจากเคอร์เซอร์ควรถูกวางไว้ในบันทึกที่ประกาศโดยยึดตามเคอร์เซอร์เดียวกันกับแอตทริบิวต์ %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 (และอีกอย่าง คุณควรทำเช่นเดียวกันในส่วนข้อยกเว้น):

เริ่มเปิด my_package.my_cursor; ... การทำงานกับเคอร์เซอร์ CLOSE my_package.my_cursor; ยกเว้นในกรณีที่ mypackage.my_cursor%ISOPEN ปิด my_package.my_cursor; สิ้นสุดถ้า; จบ;

• เคอร์เซอร์สามารถปิดได้เฉพาะเมื่อเคอร์เซอร์ถูกเปิดไว้ก่อนหน้านี้เท่านั้น มิฉะนั้น ข้อยกเว้น INVALID_CURS0R จะถูกส่งออกไป สถานะเคอร์เซอร์ถูกตรวจสอบโดยใช้แอตทริบิวต์ %ISOPEN:

ถ้า company_cur%ISOPEN แล้วปิด company_cur; สิ้นสุดถ้า;

• หากมีเคอร์เซอร์ที่เปิดอยู่ในโปรแกรมมากเกินไป จำนวนของเคอร์เซอร์เหล่านั้นอาจเกินค่าของพารามิเตอร์ฐานข้อมูล 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

ในโค้ด T-SQL ของฉัน ฉันมักจะใช้การดำเนินการตามการตั้งค่าเสมอ ฉันได้รับแจ้งว่าการดำเนินการประเภทนี้เป็นสิ่งที่ SQL Server ได้รับการออกแบบมาเพื่อประมวลผลและควรจะเร็วกว่าการประมวลผลแบบอนุกรม ฉันรู้ว่ามีเคอร์เซอร์อยู่ แต่ฉันไม่แน่ใจว่าจะใช้มันอย่างไร คุณช่วยยกตัวอย่างเคอร์เซอร์ได้ไหม? คุณสามารถให้คำแนะนำเกี่ยวกับเวลาที่ควรใช้เคอร์เซอร์ได้หรือไม่? ฉันคิดว่า Microsoft รวมไว้ใน SQL Server ด้วยเหตุผลบางอย่างดังนั้นพวกเขาจึงต้องมีสถานที่ที่สามารถใช้งานได้อย่างมีประสิทธิภาพ

สารละลาย

เคอร์เซอร์ของวงกลมบางวงไม่เคยใช้เลย ในบางวงก็เป็นทางเลือกสุดท้ายและในกลุ่มอื่น ๆ ที่ใช้เป็นประจำ ในแต่ละค่ายเหล่านี้ พวกเขามีเหตุผลที่แตกต่างกันสำหรับจุดยืนในการใช้เคอร์เซอร์ ไม่ว่าคุณจะยืนบนเคอร์เซอร์ก็ตาม พวกเขาอาจ มีสถานที่ในสถานการณ์เฉพาะและไม่ได้อยู่ในสถานการณ์อื่น ดังนั้น มันขึ้นอยู่กับความเข้าใจของคุณเกี่ยวกับเทคนิคการเขียนโค้ดจากนั้นความเข้าใจของคุณเกี่ยวกับปัญหาที่มีอยู่เพื่อตัดสินใจว่าการประมวลผลด้วยเคอร์เซอร์นั้นเหมาะสมหรือไม่ เริ่มกันเลย ทำสิ่งต่อไปนี้:

• ดูตัวอย่างเคอร์เซอร์
• แบ่งส่วนประกอบของเคอร์เซอร์
• ให้ตัวอย่างเคอร์เซอร์เพิ่มเติม
• วิเคราะห์ข้อดีและข้อเสียของการใช้เคอร์เซอร์

วิธีสร้างเคอร์เซอร์เซิร์ฟเวอร์ SQL

การสร้างเคอร์เซอร์ SQL Server เป็นกระบวนการที่สอดคล้องกัน ดังนั้นเมื่อคุณเรียนรู้ขั้นตอนต่างๆ แล้ว คุณจะสามารถทำซ้ำขั้นตอนเหล่านั้นได้อย่างง่ายดายด้วยชุดลอจิกต่างๆ เพื่อวนซ้ำข้อมูล มาดูขั้นตอนกัน:

1. ขั้นแรก คุณต้องประกาศตัวแปรที่คุณต้องการในตรรกะ
2. ประการที่สอง คุณประกาศเคอร์เซอร์ด้วยชื่อเฉพาะที่คุณจะใช้ตลอดตรรกะ ตามด้วยการเปิดเคอร์เซอร์ทันที
3. ประการที่สาม คุณดึงข้อมูลบันทึกจากเคอร์เซอร์เพื่อเริ่มการประมวลผลข้อมูล
4. ประการที่สี่คือกระบวนการข้อมูลที่เป็นเอกลักษณ์ของตรรกะแต่ละชุด ซึ่งอาจเป็นการแทรก อัปเดต การลบ ฯลฯ สำหรับแต่ละแถวของข้อมูลที่ถูกดึงมา นี่คือชุดตรรกะที่สำคัญที่สุดในระหว่างกระบวนการนี้ซึ่งดำเนินการในแต่ละแถว
5. ประการที่ห้า คุณดึงเรกคอร์ดถัดไปจากเคอร์เซอร์เหมือนที่คุณทำในขั้นตอนที่ 3 จากนั้นขั้นตอนที่ 4 จะถูกทำซ้ำอีกครั้งโดยการประมวลผลข้อมูลที่เลือก
6. ประการที่หก เมื่อประมวลผลข้อมูลทั้งหมดแล้ว คุณจะปิดเคอร์เซอร์
7. ในขั้นตอนสุดท้ายและสำคัญ คุณจะต้องจัดสรรเคอร์เซอร์ใหม่เพื่อปล่อยทรัพยากรภายในทั้งหมดที่ SQL Server มีอยู่

จากที่นี่ โปรดดูตัวอย่างด้านล่างเพื่อเริ่มต้นทราบว่าเมื่อใดควรใช้เคอร์เซอร์ SQL Server และต้องทำอย่างไร

ตัวอย่างเคอร์เซอร์เซิร์ฟเวอร์ SQL

นี่คือตัวอย่างเคอร์เซอร์จากเคล็ดลับ Simple script เพื่อสำรองฐานข้อมูล SQL Server ทั้งหมดที่มีการสำรองข้อมูลในลักษณะอนุกรม:

ประกาศ @name VARCHAR(50) -- ชื่อฐานข้อมูล DECLARE @path VARCHAR(256) -- เส้นทางสำหรับไฟล์สำรอง DECLARE @fileName VARCHAR(256) -- ชื่อไฟล์สำหรับการสำรองข้อมูล DECLARE @fileDate VARCHAR(20) -- ใช้สำหรับชื่อไฟล์ SET @path = "C:\Backup\" SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) ประกาศ db_cursor เคอร์เซอร์สำหรับเลือกชื่อจาก MASTER.dbo.sysdatabases โดยที่ชื่อไม่อยู่ใน ("master", "model" ","msdb","tempdb") เปิด db_cursor ดึงข้อมูลถัดไปจาก db_cursor เข้าสู่ @name ในขณะที่ @@FETCH_STATUS = 0 เริ่มต้นตั้งค่า @fileName = @path + @name + "_" + @fileDate + ".BAK" ฐานข้อมูลสำรอง @ ชื่อ TO DISK = @fileName ดึงข้อมูลถัดไปจาก db_cursor เข้าสู่ @name END CLOSE db_cursor DEALLOCATE db_cursor

คอมโพเนนต์เคอร์เซอร์ของเซิร์ฟเวอร์ SQL

ตามตัวอย่างข้างต้น เคอร์เซอร์ประกอบด้วยส่วนประกอบเหล่านี้:

• คำสั่ง DECLARE - ประกาศตัวแปรที่ใช้ในบล็อคโค้ด
• คำสั่ง SET\SELECT - เตรียมใช้งานตัวแปรให้เป็นค่าเฉพาะ
• คำสั่งประกาศเคอร์เซอร์ - เติมเคอร์เซอร์ด้วยค่าที่จะถูกประเมิน
  • หมายเหตุ - มีตัวแปรในคำสั่ง DECLARE CURSOR FOR จำนวนเท่ากันกับที่มีอยู่ในคำสั่ง SELECT ซึ่งอาจเป็นตัวแปร 1 ตัวหรือหลายตัวและคอลัมน์ที่เกี่ยวข้องกัน
• คำสั่ง OPEN - เปิดเคอร์เซอร์เพื่อเริ่มการประมวลผลข้อมูล
• คำสั่ง FETCH NEXT - กำหนดค่าเฉพาะจากเคอร์เซอร์ให้กับตัวแปร
  • หมายเหตุ - ตรรกะนี้ใช้สำหรับประชากรเริ่มต้นก่อนคำสั่ง WHILE และอีกครั้งในระหว่างแต่ละวงในกระบวนการโดยเป็นส่วนหนึ่งของคำสั่ง WHILE
• คำสั่ง WHILE - เงื่อนไขในการเริ่มต้นและดำเนินการประมวลผลข้อมูลต่อ
• คำสั่ง BEGIN...END - เริ่มต้นและสิ้นสุดบล็อกโค้ด
  • หมายเหตุ - ขึ้นอยู่กับการประมวลผลข้อมูล สามารถใช้คำสั่ง BEGIN...END ได้หลายรายการ
• การประมวลผลข้อมูล - ในตัวอย่างนี้ ตรรกะนี้คือการสำรองข้อมูลฐานข้อมูลไปยังพาธและชื่อไฟล์ที่ระบุ แต่อาจเป็นเพียงเกี่ยวกับ DML หรือตรรกะการดูแลระบบใดๆ
• คำสั่ง CLOSE - เผยแพร่ข้อมูลปัจจุบันและการล็อคที่เกี่ยวข้อง แต่อนุญาตให้เคอร์เซอร์เปิดใหม่ได้
• คำสั่ง DEALLOCATE - ทำลายเคอร์เซอร์

แนะนำให้อ่าน

เรียนรู้เพิ่มเติมเกี่ยวกับเคอร์เซอร์เซิร์ฟเวอร์ SQL และทางเลือก:

ตัวอย่างเคอร์เซอร์เซิร์ฟเวอร์ SQL เพิ่มเติม

ในตัวอย่างข้างต้น การสำรองข้อมูลจะดำเนินการผ่านเคอร์เซอร์ โปรดดูเคล็ดลับอื่นๆ เหล่านี้ที่ใช้ประโยชน์จากตรรกะตามเคอร์เซอร์:

• สคริปต์เพื่อสร้างคำสั่งเพื่อปิดใช้งาน เปิดใช้งาน วาง และสร้างข้อจำกัด Foreign Key ใหม่ใน SQL Server

การวิเคราะห์เคอร์เซอร์เซิร์ฟเวอร์ SQL

การวิเคราะห์ด้านล่างนี้มีวัตถุประสงค์เพื่อใช้เป็นข้อมูลเชิงลึกในสถานการณ์ต่างๆ ที่ตรรกะที่ใช้เคอร์เซอร์อาจเป็นประโยชน์หรือไม่ก็ได้:

• การประมวลผลธุรกรรมออนไลน์ (OLTP) - ในสภาพแวดล้อม OLTP ส่วนใหญ่ ตรรกะที่ใช้ SET เหมาะสมที่สุดสำหรับธุรกรรมระยะสั้น ทีมงานของเราพบแอปพลิเคชันบุคคลที่สามที่ใช้เคอร์เซอร์ในการประมวลผลทั้งหมด ซึ่งทำให้เกิดปัญหา แต่เหตุการณ์นี้เกิดขึ้นไม่บ่อยนัก โดยทั่วไปแล้ว ตรรกะที่ใช้ SET นั้นเป็นไปได้มากกว่าที่เป็นไปได้ และแทบไม่จำเป็นต้องใช้เคอร์เซอร์เลย
• การรายงาน - โดยทั่วไปแล้วไม่จำเป็นต้องใช้เคอร์เซอร์ ขึ้นอยู่กับการออกแบบรายงานและการออกแบบพื้นฐาน อย่างไรก็ตาม ทีมงานของเราประสบปัญหาในการรายงานข้อกำหนดซึ่งไม่มี Referential Integrity ในฐานข้อมูลพื้นฐาน และจำเป็นต้องใช้เคอร์เซอร์เพื่อคำนวณค่าการรายงานอย่างถูกต้อง เรามีประสบการณ์เดียวกันเมื่อจำเป็นต้องรวบรวมข้อมูลสำหรับกระบวนการดาวน์สตรีม แนวทางที่ใช้เคอร์เซอร์ได้รับการพัฒนาอย่างรวดเร็วและดำเนินการในลักษณะที่ยอมรับได้เพื่อตอบสนองความต้องการ
• การประมวลผลแบบซีเรียลไลซ์ - หากคุณต้องการดำเนินกระบวนการแบบซีเรียลไลซ์ให้เสร็จสิ้น เคอร์เซอร์ก็เป็นตัวเลือกที่เหมาะสม
• งานด้านการดูแลระบบ - งานด้านการดูแลระบบจำนวนมากจำเป็นต้องดำเนินการในลักษณะอนุกรม ซึ่งเข้ากันได้ดีกับลอจิกแบบเคอร์เซอร์ แต่มีอ็อบเจ็กต์อื่น ๆ ที่อิงระบบอยู่เพื่อตอบสนองความต้องการ ในบางสถานการณ์ เคอร์เซอร์ถูกใช้เพื่อทำให้กระบวนการเสร็จสมบูรณ์
• ชุดข้อมูลขนาดใหญ่ - ด้วยชุดข้อมูลขนาดใหญ่ คุณอาจพบสิ่งใดสิ่งหนึ่งต่อไปนี้:
  • ตรรกะที่ใช้เคอร์เซอร์อาจไม่ปรับขนาดให้ตรงกับความต้องการในการประมวลผล
  • ด้วยการดำเนินการตามชุดขนาดใหญ่บนเซิร์ฟเวอร์ที่มีหน่วยความจำน้อยที่สุด ข้อมูลอาจถูกเพจหรือผูกขาดใน SQL Server ซึ่งใช้เวลานานอาจทำให้เกิดปัญหาความขัดแย้งและหน่วยความจำ ด้วยเหตุนี้ วิธีการที่ใช้เคอร์เซอร์จึงอาจตอบสนองความต้องการได้
  • เครื่องมือบางอย่างจะแคชข้อมูลโดยธรรมชาติไปยังไฟล์ภายใต้ฝาครอบ ดังนั้นการประมวลผลข้อมูลในหน่วยความจำอาจเป็นหรือไม่ก็ได้
  • ถ้าข้อมูลสามารถประมวลผลในฐานข้อมูล SQL Server ชั่วคราว ผลกระทบต่อสภาพแวดล้อมการใช้งานจริงจะเกิดขึ้นเฉพาะเมื่อมีการประมวลผลข้อมูลขั้นสุดท้ายเท่านั้น ทรัพยากรทั้งหมดบนเซิร์ฟเวอร์ชั่วคราวสามารถใช้สำหรับกระบวนการ ETL จากนั้นจึงสามารถนำเข้าข้อมูลสุดท้ายได้
  • SSIS รองรับชุดข้อมูลแบบแบตช์ซึ่งอาจแก้ไขความจำเป็นโดยรวมในการแบ่งชุดข้อมูลขนาดใหญ่ออกเป็นขนาดที่สามารถจัดการได้มากขึ้น และทำงานได้ดีกว่าการใช้เคอร์เซอร์แบบแถวต่อแถว
  • ขึ้นอยู่กับวิธีการเข้ารหัสเคอร์เซอร์หรือตรรกะ SSIS อาจเป็นไปได้ที่จะรีสตาร์ท ณ จุดที่ล้มเหลวโดยยึดตาม
  • ทำซ้ำแบทช์ด้วยคำสั่ง GO

  ขั้นตอนถัดไป

  • เมื่อคุณต้องเผชิญกับการตัดสินใจในการประมวลผลข้อมูล ให้พิจารณาว่าคุณยืนอยู่จุดใดในการใช้เคอร์เซอร์ SQL Server พวกเขาอาจมีหรือไม่มีที่ในใบสมัครหรือกระบวนการปฏิบัติงานของคุณ มีหลายวิธีในการทำงานให้เสร็จสิ้น ดังนั้นการใช้เคอร์เซอร์อาจเป็นทางเลือกที่สมเหตุสมผลหรือไม่ก็ได้ คุณเป็นผู้ตัดสิน
  • หากคุณประสบปัญหาเกี่ยวกับเทคนิคการเขียนโค้ดแบบอื่นและจำเป็นต้องทำอะไรบางอย่างให้เสร็จสิ้นอย่างรวดเร็ว การใช้เคอร์เซอร์อาจเป็นทางเลือกหนึ่งที่ใช้ได้ อาจใช้เวลานานในการประมวลผลข้อมูล แต่เวลาในการเขียนโค้ดอาจน้อยกว่ามาก หากคุณมีกระบวนการแบบครั้งเดียวหรือทุกคืน การทำเช่นนี้อาจช่วยได้
  • หากเคอร์เซอร์ถูกรังเกียจในสภาพแวดล้อมของคุณ ตรวจสอบให้แน่ใจว่าได้เลือกทางเลือกอื่นที่ใช้งานได้ เพียงให้แน่ใจว่ากระบวนการนี้จะไม่ทำให้เกิดปัญหาอื่น ๆ ตามตัวอย่าง หากใช้เคอร์เซอร์และมีการประมวลผลแถวหลายล้านแถว สิ่งนี้อาจล้างข้อมูลทั้งหมดออกจากแคชและทำให้เกิดการโต้แย้งเพิ่มเติมหรือไม่ หรือด้วยชุดข้อมูลขนาดใหญ่ ข้อมูลจะถูกเพจลงดิสก์หรือเขียนลงในไดเร็กทอรีชั่วคราว
  • เมื่อคุณประเมินวิธีการที่ใช้เคอร์เซอร์กับทางเลือกอื่นๆ ให้เปรียบเทียบเทคนิคต่างๆ อย่างยุติธรรมในแง่ของเวลา การโต้แย้ง และทรัพยากรที่จำเป็น หวังว่าปัจจัยเหล่านี้จะนำคุณไปสู่เทคนิคที่เหมาะสม

คำสั่ง 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 (Oracle, Microsoft SQL Server) ชุดผลลัพธ์ที่ได้รับเมื่อดำเนินการค้นหาและตัวชี้บันทึกปัจจุบันที่เกี่ยวข้อง จะบอกว่าเคอร์เซอร์เป็นตารางเสมือนซึ่งเป็นที่เก็บข้อมูลทางเลือก ในเวลาเดียวกัน เคอร์เซอร์ช่วยให้คุณเข้าถึงข้อมูลของคุณได้เหมือนกับว่าเป็นอาร์เรย์ปกติ
เคอร์เซอร์ถูกใช้ในขั้นตอนการจัดเก็บ ทฤษฎีพอแล้ว ลองดูตัวอย่าง:
เรามีฐานข้อมูล (ฐานข้อมูลไม่ค่อยดีนัก นี่เป็นหนึ่งในแล็บของฉัน แต่ครูฐานข้อมูลของเรายืนยันโครงสร้างดังกล่าว)

/* รายละเอียดธนาคาร */
สร้างตาราง `ธนาคาร` (

`ชื่อธนาคาร` VARCHAR (50) COLLATE utf8_bin ไม่เป็นค่าเริ่มต้น ""


คีย์หลัก (`BankId`)

)เครื่องยนต์=อินโนดีบี
ชุดอักขระ "utf8" เรียงกัน "utf8_bin" ;
/*ข้อมูลการฝาก*/
สร้างตาราง `การกระจายทางธนาคาร` (
`BankId` จำนวนเต็ม (11) ไม่เป็นโมฆะ
`จำนวนเต็มถาวร (11) ค่าเริ่มต้น NULL
`จำนวนการมีส่วนร่วม` ทศนิยม (10,0) ไม่เป็นโมฆะ
`ClientId` จำนวนเต็ม (11) ไม่เป็นโมฆะ
คีย์หลัก (`BankId`, `ClientId`),
คีย์ `BankId` (`BankId`)
คีย์ `ClientId` (`ClientId`)
ข้อจำกัด `bankdistribution_fk` คีย์ต่างประเทศ (`BankId`) การอ้างอิง `bank` (`BankId`)
ข้อจำกัด `bankdistribution_fk1` คีย์ต่างประเทศ (`ClientId`) การอ้างอิง `client` (`ClientId`)
)เครื่องยนต์=อินโนดีบี
/*ข้อมูลเกี่ยวกับผู้ร่วมให้ข้อมูล*/
สร้างตาราง `ลูกค้า` (
`ClientId` จำนวนเต็ม (3) ไม่เป็นโมฆะ AUTO_INCREMENT
`CreditCardId` BIGINT(10) ไม่เป็นโมฆะ
`นามสกุล` VARCHAR (50) COLLATE utf8_bin ไม่ใช่ค่าเริ่มต้น "" ,
`ชื่อ` VARCHAR (50) COLLATE utf8_bin ไม่เป็นค่าเริ่มต้น ""
`ชื่อ` VARCHAR (50) COLLATE utf8_bin ไม่เป็นค่าเริ่มต้น "" ,
`โทรศัพท์` VARCHAR (50) COLLATE utf8_bin ไม่เป็นค่าเริ่มต้น ""
`ที่อยู่ ` VARCHAR (50) COLLATE utf8_bin ไม่เป็นค่าเริ่มต้น "" ,
`SafeId` จำนวนเต็ม (5) ไม่เป็นโมฆะ
คีย์หลัก (`ClientId`, `CreditCardId`),
คีย์ `รหัสลูกค้า` (`รหัสลูกค้า`)

)เครื่องยนต์=อินโนดีบี
AUTO_INCREMENT=ชุดอักขระ 11 ตัว "utf8" COLLATE "utf8_bin"

สมมติว่าเราจำเป็นต้องได้รับธนาคารแต่ละแห่งตามลำดับและดำเนินการบางอย่างกับธนาคาร คำขอดังกล่าวสามารถช่วยเราได้ในเรื่องนี้

เลือก `ธนาคาร`.* จาก `ธนาคาร` LIMIT RECORD_NUMBER_WE NEED,1

. ดังนั้น เมื่อใช้ LIMIT NUMBER_NECESSARY_US_RECORD1 เราจะแยกแต่ละระเบียนออกจากตารางธนาคารตามลำดับและดำเนินการตามที่เราต้องการ ขณะเดียวกันก็เพิ่มค่า NUMBER_NECESSARY_US_RECORD ขึ้น 1 ตอนนี้เราจะทำเช่นเดียวกันแต่ใช้เคอร์เซอร์

เริ่ม
/* ตัวแปรที่เราดึงข้อมูล */
ประกาศจำนวนเต็ม vBankId ;
ประกาศ vBankName VARCHAR(50);
ประกาศ vAddress VARCHAR(50);
ประกาศ vPhone VARCHAR (50);
/* ตัวแปร hadler - a*/
ประกาศเสร็จแล้วจำนวนเต็มเริ่มต้น 0;
/*ประกาศเคอร์เซอร์*/
ประกาศเคอร์เซอร์ BankCursor เพื่อเลือก `bank`.`BankId`,`bank`.`BankName`,`bank`.`Address`,`bank`.`Phone`, จาก `bank` โดยที่ 1;
/*การมอบหมาย HANDLER ซึ่งจะอธิบายด้านล่าง */
ประกาศตัวจัดการต่อสำหรับ SQLSTATE "02000" SET เสร็จสิ้น = 1;
/* เปิดเคอร์เซอร์ */
OpenBankเคอร์เซอร์;
/*ดึงข้อมูล*/
ในขณะที่ทำเสร็จ = 0 DO

เราทำสิ่งที่เราต้องทำ
สิ้นสุดในขณะที่ ;
/* ปิดเคอร์เซอร์ */
ปิดเคอร์เซอร์ธนาคาร;
จบ ;

* ซอร์สโค้ดนี้ถูกเน้นด้วย Source Code Highlighter

ข้อผิดพลาด: 1329 SQLSTATE: 02000 (ER_SP_FETCH_NO_DATA)

ข้อความ: ไม่มีข้อมูล - ดึง เลือก หรือประมวลผลแถวเป็นศูนย์

SQLSTATE: 02000 เริ่มทำงานเมื่อถึงจุดสิ้นสุดของเคอร์เซอร์ หรือเมื่อการเลือกหรืออัปเดตส่งคืนสตริงว่าง

ในบรรทัดถัดไป เราประกาศเคอร์เซอร์ DECLARE cursor_name CURSOR FOR select_statement;
เปิดเคอร์เซอร์ เปิด cursor_name;
นอกจากนี้ จนกว่าเราจะไปถึงจุดสิ้นสุดของเคอร์เซอร์ (ในขณะที่ทำ = 0 DO) เราจะแยกข้อมูลและประมวลผล
ปิดเคอร์เซอร์ก่อนออกจากขั้นตอนการจัดเก็บ ปิด cursor_name;

ดูเหมือนไม่มีอะไรซับซ้อน แต่มีข้อผิดพลาดมากมายที่เกี่ยวข้องกับ SQLSTATE "02000"

ในขณะที่ทำเสร็จ = 0 DO
ดึง BankCursor เข้าสู่ vBankId, vBankName, vAddress, vPhone;

เลือก (ContributeAmount) ลงใน vContributeAmountSUM จากการกระจายทางธนาคาร โดยที่ BankId = vBankId จำกัด 1;
ดำเนินการบางอย่าง
สิ้นสุดในขณะที่ ;

* ซอร์สโค้ดนี้ถูกเน้นด้วย Source Code Highlighter

ทุกอย่างดีและถูกต้องจากมุมมองของไวยากรณ์ แต่ตามตรรกะแล้ว ไม่เลย อาจเกิดขึ้นได้ว่าผู้ร่วมให้ข้อมูลไม่ได้เปิดบัญชีในธนาคารใดๆ จากนั้นสำหรับ Select (ContributeAmount) INTO vContributeAmountSUM FROM bankdistribution โดยที่ BankId = vBankId Limit 1; SQLSTATE: 02000 จะถูกทริกเกอร์ ตัวแปรที่ทำเสร็จแล้วจะถูกตั้งค่าเป็น 1 และลูป while จะสิ้นสุดเร็วกว่าที่เราคาดไว้ สิ่งนี้สามารถหลีกเลี่ยงได้โดยทำดังต่อไปนี้

ในขณะที่ทำเสร็จ = 0 DO
ดึง BankCursor เข้าสู่ vBankId, vBankName, vAddress, vPhone;
/* ดึงจำนวนเงินฝากใด ๆ สำหรับธนาคาร */


ถ้า (vContributeAmountSUM > 0) แล้ว
/* ดึงจำนวนเงินฝากใด ๆ สำหรับธนาคาร */

สิ้นสุดถ้า ;
ดำเนินการบางอย่าง
สิ้นสุดในขณะที่ ;

* ซอร์สโค้ดนี้ถูกเน้นด้วย Source Code Highlighter

ในคำขอแรก เราได้ตรวจสอบว่ามีส่วนสนับสนุนหรือไม่ (หากไม่มี จากนั้น vContributeAmountSUM == 0) และหากมี เราจะแยกข้อมูลออกมาเท่านั้น

ตอนนี้ สมมติว่าเราจำเป็นต้องกู้คืนจำนวนเงินทั้งหมดในบัญชีในธนาคารที่แตกต่างกันสำหรับลูกค้าแต่ละราย
ประกาศเคอร์เซอร์ ClientSummCursor สำหรับผลรวมที่เลือก

ประกาศเคอร์เซอร์ ClientSummCursor สำหรับผลรวมที่เลือก (`bankdistribution`.`ContributeAmount`),`bankdistribution`.`ClientId` จาก `bankdistribution` เข้าร่วมภายในไคลเอนต์บน (client.ClientId = bankdistribution.`ClientId`) โดยที่ 1 กลุ่มโดย `bankdistribution` `รหัสลูกค้า`;

เปิด ClientSummCursor;
ในขณะที่ทำเสร็จ = 0 DO
ดึง BankCursor เข้าสู่ vBankId, vBankName, vAddress, vPhone;
/* ดึงจำนวนเงินฝากใด ๆ สำหรับธนาคาร */
เลือกจำนวน(ContributeAmount) ลงใน vContributeAmountSUM จากการกระจายทางธนาคาร โดยที่ BankId = vBankId จำกัด 1;
/* ตรวจสอบว่ามีเงินฝากในธนาคารนี้จริงหรือไม่ */
ถ้า (vContributeAmountSUM > 0) แล้ว
/* ดึงจำนวนเงินฝากใด ๆ สำหรับธนาคาร */
เลือก ContributeAmount ลงใน vContributeAmountSUM จากการกระจายทางธนาคาร โดยที่ BankId = ขีดจำกัด vBankId 1;
สิ้นสุดถ้า ;


เราดำเนินการบางอย่าง
สิ้นสุดในขณะที่ ;

* ซอร์สโค้ดนี้ถูกเน้นด้วย Source Code Highlighter

สถานการณ์เดียวกันนี้อาจเกิดขึ้นได้เมื่อข้อมูลใน ClientSummCursor สิ้นสุดก่อนข้อมูลใน BankCursor, SQLSTATE: 02000 ถูกทริกเกอร์, ตัวแปรที่ทำเสร็จแล้วถูกตั้งค่าเป็น 1 และ while loop สิ้นสุดเร็วกว่าที่เราคาดไว้ สิ่งนี้สามารถหลีกเลี่ยงได้โดยทำดังต่อไปนี้

เปิด ClientSummCursor;
ในขณะที่ทำเสร็จ = 0 DO
ดึง BankCursor เข้าสู่ vBankId, vBankName, vAddress, vPhone;
/* ดึงจำนวนเงินฝากใด ๆ สำหรับธนาคาร */
เลือกจำนวน(ContributeAmount) ลงใน vContributeAmountSUM จากการกระจายทางธนาคาร โดยที่ BankId = vBankId จำกัด 1;
/* ตรวจสอบว่ามีเงินฝากในธนาคารนี้จริงหรือไม่ */
ถ้า (vContributeAmountSUM > 0) แล้ว
/* ดึงจำนวนเงินฝากใด ๆ สำหรับธนาคาร */
เลือก ContributeAmount ลงใน vContributeAmountSUM จากการกระจายทางธนาคาร โดยที่ BankId = ขีดจำกัด vBankId 1;
สิ้นสุดถ้า ;
/* ก่อนที่จะดึงข้อมูลจากเคอร์เซอร์ตัวที่สอง ให้จำสถานะของ sqlstate */
SET old_status = เสร็จสิ้น;
/* ดึงข้อมูลที่เราต้องการ */
ดึง ClientSummCursor เข้าสู่ vSum, vClientId;
/* ตรวจสอบว่าดึงข้อมูลแล้วหรือยัง หาก sqlstate 0200 ล้มเหลว */
ถ้า (เสร็จสิ้น = 0) แล้ว
เราดำเนินการบางอย่าง
สิ้นสุดถ้า ;
/* ก่อนสิ้นสุด while ให้คืนค่าตัวแปรที่ทำเสร็จแล้ว */
ตั้งค่าเสร็จแล้ว = old_status;
สิ้นสุดในขณะที่ ;

* ซอร์สโค้ดนี้ถูกเน้นด้วย Source Code Highlighter

ขอบคุณทุกคนที่อ่านมาจนถึงตอนนี้ ฉันหวังว่าสิ่งนี้จะเป็นประโยชน์กับใครบางคน

วันนี้เราจะพิจารณาสิ่งที่น่าสนใจมากมาย เช่น วิธีเรียกใช้ขั้นตอนที่สร้างไว้แล้วซึ่งยอมรับพารามิเตอร์เป็นกลุ่ม เช่น ไม่เพียงแต่กับพารามิเตอร์คงที่เท่านั้น แต่ยังมีพารามิเตอร์ที่จะเปลี่ยนแปลง เช่น ขึ้นอยู่กับบางตาราง เช่น ฟังก์ชันปกติ และสิ่งนี้จะช่วยเราเพียง เคอร์เซอร์และลูปและตอนนี้เราจะดูวิธีใช้งานทั้งหมดนี้

ตามที่คุณเข้าใจ เราจะพิจารณาเคอร์เซอร์และลูปให้เหมาะกับงานเฉพาะ และงานอะไรตอนนี้ฉันจะบอก

มีขั้นตอนที่ทำสิ่งที่ฟังก์ชัน SQL ปกติไม่สามารถทำได้ เช่น การคำนวณและการแทรกตามการคำนวณเหล่านั้น และคุณรันมันแบบนี้:

EXEC test_PROCEDURE พาร์ 1, พาร์ 2

กล่าวอีกนัยหนึ่งคุณรันเฉพาะกับพารามิเตอร์ที่ระบุ แต่ถ้าคุณจำเป็นต้องรันโพรซีเดอร์นี้เช่น 100, 200 หรือมากกว่านั้นคุณจะเห็นด้วยว่าสิ่งนี้ไม่สะดวกมากเช่น เป็นเวลานาน. มันจะง่ายกว่ามากถ้าเราสามารถรันโพรซีเดอร์เหมือนกับฟังก์ชันปกติใน Select Queries ได้ เช่น:

เลือก my_fun(id) จาก test_table

กล่าวอีกนัยหนึ่ง ฟังก์ชันนี้จะทำงานกับแต่ละระเบียนของตาราง test_table แต่ดังที่คุณทราบ ขั้นตอนนี้ไม่สามารถใช้ในลักษณะนี้ได้ แต่มีวิธีที่จะช่วยให้เราดำเนินการตามแผนของเราได้แม่นยำยิ่งขึ้นแม้กระทั่งสองวิธี วิธีแรกคือการใช้เคอร์เซอร์และลูป และวิธีที่สองคือการใช้แค่ลูป แต่ไม่มีเคอร์เซอร์ ทั้งสองตัวเลือกบ่งบอกเป็นนัยว่าเราจะสร้างขั้นตอนเพิ่มเติมซึ่งเราจะดำเนินการในอนาคต

บันทึก!เราจะเขียนตัวอย่างทั้งหมดใน MSSql 2008 DBMS โดยใช้ Management Studio นอกจากนี้ การดำเนินการทั้งหมดที่แสดงด้านล่างจำเป็นต้องมีความรู้ที่จำเป็นใน SQL หรือในการเขียนโปรแกรม Transact-SQL ฉันอยากจะแนะนำให้อ่านบทความต่อไปนี้ก่อน:

มาเริ่มกันเลย และก่อนที่จะเขียนโพรซีเดอร์ ลองพิจารณาข้อมูลเบื้องต้นของตัวอย่างของเราก่อน

สมมติว่ามีตาราง test_table

สร้างตาราง .( (18, 0) NULL, (50) NULL, (50) NULL) ในระหว่างเดินทาง

จำเป็นต้องแทรกข้อมูลลงไปตามการคำนวณบางอย่างที่ขั้นตอนจะดำเนินการ my_proc_testในกรณีนี้ เพียงแทรกข้อมูล แต่ในทางปฏิบัติ คุณสามารถใช้ขั้นตอนของคุณเอง ซึ่งสามารถคำนวณได้หลายอย่าง ดังนั้นในกรณีของเรา ขั้นตอนนี้ไม่สำคัญ แต่เป็นเพียงตัวอย่างเท่านั้น มาสร้างมันกันเถอะ:

สร้างขั้นตอน (@number numeric, @pole1 varchar(50), @pole2 varchar(50)) AS BEGIN INSERT INTO dbo.test_table (number,pole1,pole2) VALUES (@number, @pole1, @pole2) END GO

เพียงใช้พารามิเตอร์สามตัวแล้วแทรกลงในตาราง

และสมมติว่าขั้นตอนนี้ เราต้องรันมันหลายครั้งตามที่มีแถวในตารางหรือมุมมอง (VIEWS) หรืออีกนัยหนึ่งคือรันเป็นกลุ่มสำหรับแต่ละแถวของแหล่งที่มา

และตัวอย่างเช่น เรามาสร้างแหล่งที่มากันเถอะ เราจะได้ตารางง่ายๆ test_table_vremและคุณสามารถมีได้ ตามที่ฉันกล่าวไว้ แหล่งที่มาของฉัน เช่น ตารางชั่วคราวหรือมุมมอง:

สร้างตาราง .( (18, 0) NULL, (50) NULL, (50) NULL) ในระหว่างเดินทาง

มาเติมข้อมูลทดสอบกัน:

และตอนนี้ขั้นตอนของเราจำเป็นต้องรันสำหรับแต่ละบรรทัด เช่น สามครั้งด้วยการตั้งค่าที่แตกต่างกัน เมื่อคุณเข้าใจว่าค่าของฟิลด์เหล่านี้เป็นพารามิเตอร์ของเรา กล่าวคือ หากเราเริ่มขั้นตอนด้วยตนเอง จะมีลักษณะดังนี้:

ดำเนินการ my_proc_test 1, 'pole1_str1', 'pole2_str1'

และอีกสามครั้งด้วยพารามิเตอร์ที่เหมาะสม

แต่เราไม่ต้องการทำเช่นนั้น ดังนั้นเราจะเขียนขั้นตอนเพิ่มเติมอีกหนึ่งขั้นตอน ซึ่งจะเรียกใช้ขั้นตอนหลักของเราบ่อยเท่าที่เราต้องการ

ตัวเลือกแรก

การใช้เคอร์เซอร์และลูปในขั้นตอน

มาตรงประเด็นแล้วเขียนขั้นตอน ( my_proc_test_all) ฉันแสดงความคิดเห็นรหัสเช่นเคย:

สร้างขั้นตอน AS -- ประกาศตัวแปร DECLARE @number bigint DECLARE @pole1 varchar(50) DECLARE @pole2 varchar(50) -- ประกาศเคอร์เซอร์ ประกาศ my_cur เคอร์เซอร์สำหรับหมายเลขที่เลือก, pole1, pole2 จาก test_table_vrem -- เปิดเคอร์เซอร์ OPEN my_cur -- อ่านข้อมูลก่อน สตริงไปยังตัวแปรของเรา FETCH NEXT FROM my_cur INTO @number, @pole1, @pole2 --ถ้ามีข้อมูลอยู่ในเคอร์เซอร์ ให้เข้าไปในวง -- และหมุนไปตรงนั้นจนกว่าแถวในเคอร์เซอร์จะหมด WHILE @@FETCH_STATUS = 0 BEGIN -- ในการวนซ้ำแต่ละครั้ง ให้รันโพรซีเดอร์หลักของเราด้วยพารามิเตอร์ที่จำเป็น exec dbo.my_proc_test @number, @pole1, @pole2 -- อ่านบรรทัดถัดไปของเคอร์เซอร์ FETCH NEXT FROM my_cur INTO @number, @pole1 , @pole2 END -- ปิดเคอร์เซอร์ CLOSE my_cur DEALLOCATE my_cur GO

และตอนนี้ก็ยังเหลือให้เราโทรหามันและตรวจสอบผลลัพธ์:

ก่อน SELECT * FROM test_table --call EXEC dbo.my_proc_test_all --after SELECT * FROM test_table

อย่างที่คุณเห็น ทุกอย่างทำงานให้เราอย่างที่ควรจะเป็น กล่าวคือ ขั้นตอน my_proc_test ทำงานทั้งสามครั้ง และเราเปิดใช้ขั้นตอนเพิ่มเติมเพียงครั้งเดียวเท่านั้น

ตัวเลือกที่สอง

การใช้เพียงการวนซ้ำในโพรซีเดอร์

ฉันต้องบอกทันทีว่าจำเป็นต้องมีการนับแถวในตารางชั่วคราวที่นี่เช่น แต่ละบรรทัดจะต้องมีหมายเลขเช่น 1, 2, 3 เขตข้อมูลดังกล่าวในตารางชั่วคราวของเราคือตัวเลข

การเขียนขั้นตอน my_proc_test_all_v2

สร้างขั้นตอน AS --การประกาศตัวแปร DECLARE @number bigint DECLARE @pole1 varchar(50) DECLARE @pole2 varchar(50) DECLARE @cnt int DECLARE @i int --รับจำนวนแถวในตารางชั่วคราว SELECT @cnt=count(*) จาก test_table_vrem - - ตั้งค่าเริ่มต้นของตัวระบุ SET @i=1 ในขณะที่ @cnt >= @i BEGIN - กำหนดค่าให้กับพารามิเตอร์ของเรา SELECT @number=number, @pole1= pole1, @pole2=pole2 FROM test_table_vrem WHERE number = @I --สำหรับการวนซ้ำแต่ละครั้ง ให้รันโพรซีเดอร์หลักของเราพร้อมกับพารามิเตอร์ที่ต้องการ EXEC dbo.my_proc_test @number, @pole1, @pole2 --increase step set @i= @i+1 END GO

และเราตรวจสอบผลลัพธ์ แต่ก่อนอื่นเราล้างตารางของเรา เนื่องจากเราเพิ่งกรอกมันลงไปโดยใช้โพรซีเดอร์ my_proc_test_all:

ล้างตาราง DELETE test_table -- ก่อนดำเนินการ SELECT * FROM test_table -- เรียกขั้นตอน EXEC dbo.my_proc_test_all_v2 -- หลังจากดำเนินการ SELECT * FROM test_table

ตามที่คาดไว้ ผลลัพธ์จะเหมือนเดิม แต่ไม่มีการใช้เคอร์เซอร์ ขึ้นอยู่กับคุณที่จะตัดสินใจว่าจะใช้ตัวเลือกใด ตัวเลือกแรกนั้นดี เพราะโดยหลักการแล้ว ไม่จำเป็นต้องใส่ตัวเลข แต่อย่างที่คุณทราบ เคอร์เซอร์จะทำงานเป็นเวลานานหากมีบรรทัดในเคอร์เซอร์จำนวนมาก และ ตัวเลือกที่สองนั้นดีเพราะมันใช้งานได้ดูเหมือนเร็วขึ้นสำหรับฉันอีกครั้งหากมีบรรทัดจำนวนมาก แต่จำเป็นต้องมีการกำหนดหมายเลขโดยส่วนตัวแล้วฉันชอบตัวเลือกที่มีเคอร์เซอร์ แต่โดยทั่วไปแล้วมันก็ขึ้นอยู่กับคุณที่จะเกิดขึ้น บางอย่างที่สะดวกกว่านี้ฉันเพิ่งแสดงพื้นฐานของวิธีใช้งาน ขอให้โชคดี!