Oracle คอลเลกชัน PL/SQL: วาร์เรย์ ซ้อนกัน และจัดทำดัชนีตามตาราง

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

คอลเลกชันคืออะไร?

คอลเลกชันคือกลุ่มขององค์ประกอบที่มีลำดับของประเภทข้อมูลเฉพาะ ซึ่งอาจเป็นคอลเลกชันของประเภทข้อมูลที่เรียบง่ายหรือประเภทข้อมูลที่ซับซ้อน (เช่น ประเภทที่ผู้ใช้กำหนดหรือประเภทระเบียน)

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

คอลเลกชันเป็นสิ่งที่มีประโยชน์มากที่สุดเมื่อต้องประมวลผลหรือจัดการข้อมูลขนาดใหญ่ประเภทเดียวกัน คอลเลกชันสามารถเติมและจัดการโดยรวมได้โดยใช้ตัวเลือก 'BULK' ใน Oracle.

คอลเลกชันจะถูกจัดประเภทตามโครงสร้าง ตัวห้อย และพื้นที่จัดเก็บดังที่แสดงด้านล่าง

  • ดัชนีต่อตาราง (หรือที่เรียกว่า Associative Array)
  • ตารางที่ซ้อนกัน
  • วาร์เรย์

ณ จุดใด ๆ ข้อมูลในคอลเลกชันสามารถอ้างอิงได้สามคำ ชื่อคอลเลกชัน ตัวห้อย ชื่อฟิลด์/คอลัมน์ เป็น “ - - - คุณจะได้เรียนรู้เกี่ยวกับหมวดหมู่คอลเลกชันที่กล่าวมาข้างต้นเพิ่มเติมในส่วนด้านล่าง

วาร์เรย์

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

  • ขนาดขีดจำกัดบนได้รับการแก้ไขแล้ว
  • เติมตามลำดับโดยเริ่มด้วยตัวห้อย '1'
  • ประเภทคอลเลกชันนี้มีความหนาแน่นอยู่เสมอ กล่าวคือ เราไม่สามารถลบองค์ประกอบอาร์เรย์ใดๆ ได้ Varray สามารถลบออกทั้งหมดหรือตัดออกตั้งแต่ตอนท้ายก็ได้
  • เนื่องจากมีลักษณะหนาแน่นอยู่เสมอ จึงมีความยืดหยุ่นน้อยมาก
  • เหมาะสมกว่าที่จะใช้เมื่อทราบขนาดอาเรย์และทำกิจกรรมที่คล้ายกันในองค์ประกอบอาเรย์ทั้งหมด
  • ตัวห้อยและลำดับจะคงที่เสมอ กล่าวคือ ตัวห้อยและจำนวนคอลเลกชันจะเท่ากันเสมอ
  • จำเป็นต้องเริ่มต้นใช้งานก่อนจึงจะใช้ในโปรแกรมได้ การดำเนินการใดๆ (ยกเว้นการดำเนินการ EXISTS) กับคอลเลกชันที่ไม่มีการเริ่มต้นใช้งานจะแสดงข้อผิดพลาด
  • สามารถสร้างเป็นวัตถุฐานข้อมูลซึ่งมองเห็นได้ทั่วทั้งฐานข้อมูลหรือภายในโปรแกรมย่อยซึ่งสามารถใช้ได้เฉพาะในโปรแกรมย่อยนั้นเท่านั้น

รูปด้านล่างจะอธิบายการจัดสรรหน่วยความจำของ Varray (หนาแน่น) แบบไดอะแกรม

ห้อย 1 2 3 4 5 6 7
ความคุ้มค่า xyz ดฟฟ สเด Cxs วบีซี นู Qwe

ไวยากรณ์สำหรับ VARRAY:

TYPE <type_name> IS VARRAY (<SIZE>) OF <DATA_TYPE>;
  • ในรูปแบบข้างต้น type_name จะถูกประกาศเป็น VARRAY ของประเภท 'DATA_TYPE' สำหรับขีดจำกัดขนาดที่กำหนด ประเภทข้อมูลสามารถเป็นประเภทง่ายหรือซับซ้อนก็ได้

ตารางที่ซ้อนกัน

ตารางที่ซ้อนกันคือคอลเลกชันที่ขนาดของอาร์เรย์ไม่คงที่ มีประเภทตัวห้อยเป็นตัวเลข ด้านล่างนี้เป็นคำอธิบายเพิ่มเติมเกี่ยวกับประเภทตารางที่ซ้อนกัน

  • ตารางที่ซ้อนกันไม่มีขีดจำกัดขนาดด้านบน
  • เนื่องจากขีดจำกัดขนาดด้านบนไม่ได้รับการแก้ไข คอลเลกชันและหน่วยความจำจึงต้องขยายในแต่ละครั้งก่อนที่เราจะใช้งาน เราสามารถขยายคอลเลกชันโดยใช้คำหลัก 'EXTEND'
  • เติมตามลำดับโดยเริ่มด้วยตัวห้อย '1'
  • ประเภทคอลเลกชันนี้สามารถเป็นได้ทั้งสองอย่าง หนาแน่นและเบาบางกล่าวคือ เราสามารถสร้างคอลเลกชันแบบหนาแน่นได้ และเรายังสามารถลบองค์ประกอบอาร์เรย์แต่ละรายการแบบสุ่ม ซึ่งทำให้คอลเลกชันเป็นแบบกระจัดกระจาย
  • มันให้ความยืดหยุ่นมากขึ้นในการลบองค์ประกอบอาร์เรย์
  • มันถูกเก็บไว้ในตารางฐานข้อมูลที่ระบบสร้างขึ้นและสามารถนำมาใช้ในแบบสอบถามแบบใช้เลือกข้อมูลเพื่อดึงค่า
  • ตัวห้อยและลำดับไม่เสถียร กล่าวคือ ตัวห้อยและจำนวนองค์ประกอบอาร์เรย์อาจแตกต่างกันไป
  • จำเป็นต้องเริ่มต้นใช้งานก่อนจึงจะใช้ในโปรแกรมได้ การดำเนินการใดๆ (ยกเว้นการดำเนินการ EXISTS) กับคอลเลกชันที่ไม่ได้เริ่มต้นใช้งานจะแสดงข้อผิดพลาด
  • สามารถสร้างเป็นวัตถุฐานข้อมูลซึ่งมองเห็นได้ทั่วทั้งฐานข้อมูลหรือภายในโปรแกรมย่อยซึ่งสามารถใช้ได้เฉพาะในโปรแกรมย่อยนั้นเท่านั้น

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

ห้อย 1 2 3 4 5 6 7
ค่า (หนาแน่น) xyz ดฟฟ สเด Cxs วบีซี นู Qwe
ค่า(เบาบาง) Qwe asd อัฟกานิสถาน asd wer

ไวยากรณ์สำหรับตารางที่ซ้อนกัน:

TYPE <tvpe name> IS TABLE OF <DATA TYPE>;
  • ในรูปแบบข้างต้น type_name ถูกประกาศให้เป็นคอลเลกชันตารางแบบซ้อนของประเภท 'DATA_TYPE' ประเภทข้อมูลสามารถเป็นประเภทข้อมูลแบบง่ายหรือแบบซับซ้อนก็ได้

บทความที่เกี่ยวข้อง

ดัชนีต่อตาราง

Index-by-table คือคอลเลกชันที่ขนาดอาร์เรย์ไม่คงที่ ไม่เหมือนกับคอลเลกชันประเภทอื่น ๆ ในคอลเลกชัน Index-by-table ผู้ใช้สามารถกำหนดดัชนีได้ ต่อไปนี้คือแอตทริบิวต์ของ Index-by-table

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

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

ตัวห้อย (varchar) FIRST นาปรัง ที่สาม FOURTH ที่ห้า หก เจ็ด
ค่า(เบาบาง) Qwe asd อัฟกานิสถาน asd wer

ไวยากรณ์สำหรับดัชนีต่อตาราง

TYPE <type_name> IS TABLE OF <DATA_TYPE> INDEX BY VARCHAR2 (10);
  • ในรูปแบบข้างต้น type_name จะถูกประกาศให้เป็นคอลเลกชันแบบดัชนีต่อตารางของประเภท 'DATA_TYPE' ประเภทข้อมูลสามารถเป็นประเภทง่ายหรือซับซ้อนก็ได้ ตัวแปร subsciprt/index ถูกกำหนดให้เป็นประเภท VARCHAR2 โดยมีขนาดสูงสุดที่ 10

คอนสตรัคเตอร์และแนวคิดการเริ่มต้นในคอลเลกชัน

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

  • สำหรับคอลเลกชัน ควรเรียกตัวสร้างเหล่านี้อย่างชัดเจนเพื่อเริ่มต้น
  • ทั้งตาราง Varray และ Nested จำเป็นต้องเริ่มต้นผ่าน Constructor เหล่านี้ก่อนที่จะอ้างอิงเข้าสู่โปรแกรม
  • ตัวสร้างขยายการจัดสรรหน่วยความจำสำหรับคอลเลกชันโดยปริยาย (ยกเว้น Varray) ดังนั้นตัวสร้างจึงสามารถกำหนดตัวแปรให้กับคอลเลกชันได้
  • การกำหนดค่าให้กับคอลเลกชันผ่านตัวสร้างจะไม่ทำให้คอลเลกชันกระจัดกระจาย

วิธีการเก็บรวบรวม

Oracle มีฟังก์ชันต่างๆ มากมายสำหรับจัดการและทำงานกับคอลเลกชัน ฟังก์ชันเหล่านี้มีประโยชน์มากในการกำหนดและปรับเปลี่ยนแอตทริบิวต์ต่างๆ ของคอลเลกชัน ตารางต่อไปนี้จะแสดงฟังก์ชันต่างๆ และคำอธิบายของฟังก์ชันเหล่านั้น

วิธี Descriptไอออน ซิงค์
มีอยู่ (n) วิธีนี้จะส่งคืนผลลัพธ์แบบบูลีน มันจะคืนค่า 'TRUE' ถ้า nth มีองค์ประกอบอยู่ในคอลเล็กชันนั้น มิเช่นนั้นจะส่งคืนค่า FALSE เฉพาะฟังก์ชัน EXISTS เท่านั้นที่สามารถนำมาใช้ในคอลเลกชันที่ไม่ได้เตรียมใช้งาน .มีอยู่(element_position)
COUNT แสดงจำนวนองค์ประกอบทั้งหมดที่มีอยู่ในคอลเลกชัน .นับ
LIMIT มันจะส่งกลับขนาดสูงสุดของคอลเลกชัน สำหรับ Varray มันจะส่งคืนขนาดคงที่ที่กำหนดไว้ สำหรับตารางที่ซ้อนกันและดัชนีต่อตาราง จะให้ค่า NULL .จำกัด
FIRST ส่งกลับค่าของตัวแปรดัชนีตัวแรก (ตัวห้อย) ของคอลเลกชัน .อันดับแรก
LAST ส่งกลับค่าของตัวแปรดัชนีล่าสุด (ตัวห้อย) ของคอลเลกชัน .ล่าสุด
ก่อน (n) ส่งกลับนำหน้าตัวแปรดัชนีในคอลเลกชันของ nth องค์ประกอบ. หากไม่มีค่าดัชนีนำหน้า NULL จะถูกส่งกลับ .ก่อน(n)
ถัดไป (n) ส่งคืนตัวแปรดัชนีสำเร็จในชุดของ nth องค์ประกอบ. หากไม่มีค่าดัชนีสำเร็จ NULL จะถูกส่งกลับ .ถัดไป(n)
ขยาย ขยายหนึ่งองค์ประกอบในคอลเลกชันที่ส่วนท้าย .ขยาย
ขยาย (n) ขยายองค์ประกอบ n ที่ส่วนท้ายของคอลเลกชัน .ขยาย(n)
ขยาย (n,i) ขยายสำเนาของ ith องค์ประกอบที่ส่วนท้ายของคอลเลกชัน .ขยาย(n,i)
TRIM ลบองค์ประกอบหนึ่งรายการออกจากจุดสิ้นสุดของคอลเลกชัน .ทริม
ตัดแต่ง (n) ลบองค์ประกอบ n ออกจากจุดสิ้นสุดของการรวบรวม .ทริม (n)
ลบ ลบองค์ประกอบทั้งหมดออกจากคอลเลกชัน ทำให้คอลเลกชันว่างเปล่า .ลบ
ลบ (n) ลบองค์ประกอบที่ n ออกจากคอลเลกชัน ถ้าไม่มีth องค์ประกอบเป็น NULL จากนั้นจะไม่ทำอะไรเลย .ลบ(n)
ลบ (นาที,n) ลบองค์ประกอบในช่วง mth ถึง nth ในคอลเลกชั่น .DELETE(ม,n)

ตัวอย่างที่ 1: ประเภทเรคคอร์ดในระดับโปรแกรมย่อย

ในตัวอย่างนี้ เราจะดูวิธีการเติมข้อมูลคอลเลกชันโดยใช้ 'รวบรวมจำนวนมาก' และวิธีการอ้างอิงข้อมูลการรวบรวม

ประเภทบันทึกในระดับโปรแกรมย่อย

DECLARE
TYPE emp_det IS RECORD
(
EMP_NO NUMBER,
EMP_NAME VARCHAR2(150),
MANAGER NUMBER,
SALARY NUMBER
);
TYPE emp_det_tbl IS TABLE OF emp_det; guru99_emp_rec emp_det_tbl:= emp_det_tbl(); 
BEGIN
INSERT INTO emp (emp_no,emp_name, salary, manager) VALUES (1000,’AAA’,25000,1000);
INSERT INTO emp (emp_no,emp_name, salary, manager) VALUES (1001,'XXX’,10000,1000);
INSERT INTO emp (emp_no, emp_name, salary, manager) VALUES (1002,'YYY',15000,1000);
INSERT INTO emp (emp_no,emp_name,salary, manager) VALUES (1003,’ZZZ’,'7500,1000);
COMMIT:
SELECT emp no,emp_name,manager,salary BULK COLLECT INTO guru99_emp_rec
FROM emp;
dbms_output.put_line (‘Employee Detail');
FOR i IN guru99_emp_rec.FIRST..guru99_emp_rec.LAST
LOOP
dbms_output.put_line (‘Employee Number: '||guru99_emp_rec(i).emp_no); 
dbms_output.put_line (‘Employee Name: '||guru99_emp_rec(i).emp_name); 
dbms_output.put_line (‘Employee Salary:'|| guru99_emp_rec(i).salary); 
dbms_output.put_line(‘Employee Manager Number:'||guru99_emp_rec(i).manager);
dbms_output.put_line('--------------------------------');
END LOOP;
END;
/

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

  • รหัสบรรทัด 2-8: ประเภทบันทึก 'emp_det' ถูกประกาศด้วยคอลัมน์ emp_no, emp_name, เงินเดือน และผู้จัดการประเภทข้อมูล NUMBER, VARCHAR2, NUMBER, NUMBER
  • รหัสบรรทัด 9: การสร้างคอลเลกชัน 'emp_det_tbl' ขององค์ประกอบประเภทบันทึก 'emp_det'
  • รหัสบรรทัด 10: ประกาศตัวแปร 'guru99_emp_rec' เป็นประเภท 'emp_det_tbl' และเริ่มต้นด้วยตัวสร้าง null
  • รหัสบรรทัด 12-15: การแทรกข้อมูลตัวอย่างลงในตาราง 'emp'
  • รหัสบรรทัด 16: ยืนยันธุรกรรมการแทรก
  • รหัสบรรทัด 17: ดึงข้อมูลบันทึกจากตาราง 'emp' และเติมตัวแปรคอลเลกชันเป็นกลุ่มโดยใช้คำสั่ง "BULK COLLECT" ตอนนี้ตัวแปร 'guru99_emp_rec' มีบันทึกทั้งหมดที่มีอยู่ในตาราง 'emp'
  • รหัสบรรทัด 19-26: การตั้งค่าการวนซ้ำ 'FOR' เพื่อใช้พิมพ์บันทึกทั้งหมดในคอลเลกชันทีละรายการ วิธีการรวบรวม FIRST และ LAST ใช้เป็นขีดจำกัดล่างและสูงกว่าของ ห่วง.

เอาท์พุต:ดังที่คุณเห็นในภาพหน้าจอด้านบน เมื่อดำเนินการโค้ดด้านบน คุณจะได้รับผลลัพธ์ดังต่อไปนี้

Employee Detail
Employee Number: 1000
Employee Name: AAA
Employee Salary: 25000
Employee Manager Number: 1000
----------------------------------------------
Employee Number: 1001
Employee Name: XXX
Employee Salary: 10000
Employee Manager Number: 1000
----------------------------------------------
Employee Number: 1002
Employee Name: YYY
Employee Salary: 15000
Employee Manager Number: 1000
----------------------------------------------
Employee Number: 1003
Employee Name: ZZZ
Employee Salary: 7500
Employee Manager Number: 1000
----------------------------------------------

สรุปโพสต์นี้ด้วย: