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

โดย: ฟิโอน่า บราวน์ ฟิโอน่า บราวน์
Hours วันที่อัพเดท

BULK COLLECT คืออะไร?

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

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

ไวยากรณ์:

SELECT <columnl> BULK COLLECT INTO bulk_varaible FROM <table name>;
FETCH <cursor_name> BULK COLLECT INTO <bulk_varaible >;

ในรูปแบบข้างต้น BULK COLLECT ใช้ในการรวบรวมข้อมูลจากคำสั่ง 'SELECT' และ 'FETCH'

ข้อ FORALL

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

ไวยากรณ์:

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

<DML operations>;

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

ข้อ จำกัด

แนวคิดการรวบรวมจำนวนมากจะโหลดข้อมูลทั้งหมดลงในตัวแปรการรวบรวมเป้าหมายเป็นกลุ่ม กล่าวคือ ข้อมูลทั้งหมดจะถูกเติมลงในตัวแปรการรวบรวมในคราวเดียว แต่ไม่แนะนำให้เลือกเมื่อยอดรวมที่ต้องโหลดมีขนาดใหญ่มากเพราะเมื่อใด 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) ที่ส่งคืนจำนวนแถวที่ได้รับผลกระทบใน nth คำสั่ง DML ของคำสั่ง FORALL กล่าวคือ จะให้จำนวนบันทึกที่ได้รับผลกระทบในคำสั่ง FORALL สำหรับทุกค่าเดียวจากตัวแปรคอลเลกชัน คำว่า 'n' บ่งบอกถึงลำดับของค่าในคอลเลกชัน ซึ่งจำเป็นต้องมีการนับแถว

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

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

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 salaiy=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

คำอธิบายรหัส:

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

ที่คุณอาจชอบ: