Oracle PL/SQL BULK COLLECT: ตัวอย่าง FORALL

BULK COLLECT คืออะไร?

BULK COLLECT ช่วยลดการสลับบริบทระหว่างกัน SQL และเอนจิน PL/SQL และอนุญาตให้เอนจิน SQL ดึงข้อมูลบันทึกทั้งหมดได้ในครั้งเดียว

Oracle PL / SQL ฟังก์ชันนี้ช่วยให้สามารถดึงข้อมูลจำนวนมากพร้อมกันได้ แทนที่จะดึงทีละรายการ คำสั่ง BULK COLLECT นี้สามารถใช้ในคำสั่ง SELECT เพื่อดึงข้อมูลจำนวนมาก หรือเพื่อดึงข้อมูลจำนวนหนึ่งได้ เคอร์เซอร์ เป็นจำนวนมาก เนื่องจาก BULK COLLECT ดึงข้อมูลมาเป็นจำนวนมาก ดังนั้นในส่วนของ INTO ควรมีตัวแปรประเภทคอลเลกชันเสมอ ข้อดีหลักของการใช้ BULK COLLECT คือช่วยเพิ่มประสิทธิภาพโดยลดการโต้ตอบระหว่างฐานข้อมูลและเอนจิน PL/SQL

ไวยากรณ์:

SELECT <column1> BULK COLLECT INTO bulk_variable FROM <table name>;
FETCH <cursor_name> BULK COLLECT INTO <bulk_variable>;

ในไวยากรณ์ข้างต้น BULK COLLECT ใช้เพื่อรวบรวมข้อมูลจากคำสั่ง SELECT และ FETCH

ข้อ FORALL

คำสั่ง FORALL ดำเนินการ การดำเนินการ DML คำสั่ง FORALL ใช้สำหรับประมวลผลข้อมูลจำนวนมาก คล้ายกับคำสั่ง FOR loop แต่ใน FOR loop การกระทำจะเกิดขึ้นที่ระดับเรคอร์ด ในขณะที่ FORALL ไม่มีแนวคิดเรื่องลูป แต่จะประมวลผลข้อมูลทั้งหมดที่อยู่ในช่วงที่กำหนดพร้อมกัน

ไวยากรณ์:

FORALL <loop_variable> in <lower range> .. <higher range>

<DML operations>;

ในไวยากรณ์ข้างต้น การดำเนินการ DML ที่กำหนดจะถูกดำเนินการกับข้อมูลทั้งหมดที่อยู่ระหว่างช่วงล่างและช่วงบน

ข้อ จำกัด

แนวคิดการรวบรวมข้อมูลแบบกลุ่ม (Bulk Collect) จะโหลดข้อมูลทั้งหมดลงในตัวแปรคอลเลกชันเป้าหมายในคราวเดียว แต่ไม่แนะนำให้ใช้วิธีนี้หากจำนวนระเบียนทั้งหมดที่ต้องโหลดมีจำนวนมาก เพราะเมื่อ PL/SQL พยายามโหลดข้อมูลทั้งหมด มันจะใช้หน่วยความจำเซสชันมากขึ้น ดังนั้นจึงควรจำกัดขนาดของการดำเนินการรวบรวมข้อมูลแบบกลุ่มนี้เสมอ

ข้อจำกัดด้านขนาดนี้สามารถทำได้ง่ายๆ โดยการใช้เงื่อนไข ROWNUM ในคำสั่ง SELECT ในขณะที่ในกรณีของเคอร์เซอร์นั้นไม่สามารถทำได้

เพื่อเอาชนะสิ่งนี้ Oracle ได้กำหนดเงื่อนไข LIMIT ไว้ ซึ่งระบุจำนวนระเบียนที่ต้องรวมอยู่ในกลุ่มข้อมูลขนาดใหญ่

ไวยากรณ์:

FETCH <cursor_name> BULK COLLECT INTO <bulk_variable> LIMIT <size>;

ในไวยากรณ์ข้างต้น คำสั่งดึงข้อมูลเคอร์เซอร์ใช้คำสั่ง BULK COLLECT ร่วมกับข้อกำหนด LIMIT

BULK รวบรวมคุณสมบัติ

เช่นเดียวกับแอตทริบิวต์ของเคอร์เซอร์ BULK COLLECT มี %BULK_ROWCOUNT(n) ที่ส่งคืนจำนวนแถวที่ได้รับผลกระทบในคำสั่ง DML ที่ n ของคำสั่ง FORALL กล่าวคือ จะให้จำนวนระเบียนที่ได้รับผลกระทบในคำสั่ง FORALL สำหรับแต่ละค่าจากตัวแปรคอลเลกชัน คำว่า 'n' ระบุลำดับของค่าในคอลเลกชันที่ต้องการนับจำนวนแถว

1 ตัวอย่าง: ในตัวอย่างนี้ เราจะดึงรายชื่อพนักงานทั้งหมดจากตาราง emp โดยใช้ BULK COLLECT และเราจะเพิ่มเงินเดือนของพนักงานทั้งหมดขึ้น 5000 โดยใช้ FORALL

ภาพหน้าจอด้านล่างแสดงตัวอย่างคำสั่ง BULK COLLECT และ FORALL พร้อมกับผลลัพธ์ที่ได้ Oracle.

DECLARE
CURSOR guru99_det IS SELECT emp_name FROM emp;
TYPE lv_emp_name_tbl IS TABLE OF VARCHAR2(50);
lv_emp_name lv_emp_name_tbl;
BEGIN
OPEN guru99_det;
FETCH guru99_det BULK COLLECT INTO lv_emp_name LIMIT 5000;
FOR c_emp_name IN lv_emp_name.FIRST .. lv_emp_name.LAST
LOOP
Dbms_output.put_line('Employee Fetched:'||c_emp_name);
END LOOP;
FORALL i IN lv_emp_name.FIRST .. lv_emp_name.LAST
UPDATE emp SET salary=salary+5000 WHERE emp_name=lv_emp_name(i);
COMMIT;
Dbms_output.put_line('Salary Updated');
CLOSE guru99_det;
END;
/

เอาท์พุต

Employee Fetched:BBB
Employee Fetched:XXX
Employee Fetched:YYY
Salary Updated

Code คำอธิบาย:

• Code สาย 2: ประกาศเคอร์เซอร์ guru99_det สำหรับคำสั่ง 'SELECT emp_name FROM emp'
• Code สาย 3: ประกาศ lv_emp_name_tbl เป็นชนิดตาราง VARCHAR2(50)
• Code สาย 4: ประกาศตัวแปร lv_emp_name เป็นชนิด lv_emp_name_tbl
• Code สาย 6: การเปิดเคอร์เซอร์
• Code สาย 7: ดึงข้อมูลเคอร์เซอร์โดยใช้คำสั่ง BULK COLLECT โดยกำหนดขนาด LIMIT เป็น 5000 และบันทึกไว้ในตัวแปร lv_emp_name
• Code บรรทัดที่ 8-11: ตั้งค่าลูป FOR เพื่อพิมพ์ข้อมูลทั้งหมดในคอลเลกชัน lv_emp_name
• Code สาย 12: ใช้คำสั่ง FORALL เพื่อปรับเงินเดือนของพนักงานทั้งหมดเพิ่มขึ้น 5000 บาท
• Code สาย 14: การกระทำ การทำธุกรรม.

คำถามที่พบบ่อย