Oracle Bộ sưu tập PL/SQL: Biến thể, lồng nhau & lập chỉ mục theo bảng

Bộ sưu tập là gì?

Tập hợp là một nhóm các phần tử được sắp xếp theo thứ tự thuộc một kiểu dữ liệu cụ thể. Nó có thể là một tập hợp của kiểu dữ liệu đơn giản hoặc kiểu dữ liệu phức tạp, chẳng hạn như kiểu dữ liệu do người dùng định nghĩa hoặc kiểu bản ghi.

Trong một tập hợp, mỗi phần tử được xác định bằng một thuật ngữ gọi là “chỉ số dưới.” Mỗi mục được gán một chỉ số duy nhất, và dữ liệu có thể được thao tác hoặc truy xuất bằng cách tham chiếu đến chỉ số duy nhất đó.

Các tập hợp (Collections) hữu ích nhất khi cần xử lý hoặc thao tác một lượng lớn dữ liệu cùng loại. Bạn có thể điền và thao tác toàn bộ tập hợp bằng cách sử dụng tùy chọn 'BULK' trong... Oracle.

Các bộ sưu tập được phân loại dựa trên cấu trúc, chỉ số phụ và cách lưu trữ, như minh họa bên dưới:

• Bảng chỉ mục (còn được gọi là mảng liên kết)
• Bảng lồng nhau
• Biến thể

Tại bất kỳ thời điểm nào, dữ liệu trong một tập hợp có thể được tham chiếu bằng ba thuật ngữ: tên tập hợp, chỉ số dưới và tên trường hoặc cột, như “ ( ). Bạn sẽ tìm hiểu về các danh mục bộ sưu tập này trong các phần bên dưới.

Tổng quan về các loại bộ sưu tập

Ba loại bộ sưu tập này có những sự đánh đổi khác nhau. Bảng dưới đây so sánh chúng trước khi mỗi loại được trình bày chi tiết.

Biến thể

Varray là một tập hợp có kích thước cố định và không thể vượt quá. Chỉ số của Varray là một giá trị số. Các thuộc tính của Varray bao gồm:

• Kích thước giới hạn trên là cố định.
• Được điền tuần tự bắt đầu từ chỉ số dưới '1'.
• Kiểu dữ liệu này luôn luôn dày đặc; chúng ta không thể xóa từng phần tử riêng lẻ của mảng. Một Varray có thể bị xóa toàn bộ hoặc bị cắt bớt từ cuối.
• Vì nó luôn đặc chắc nên có rất ít tính linh hoạt.
• Phương pháp này phù hợp hơn khi biết kích thước mảng và các hoạt động tương tự được thực hiện trên tất cả các phần tử.
• Chỉ số dưới và số lượng của tập hợp luôn luôn ổn định.
• Nó phải được khởi tạo trước khi sử dụng. Bất kỳ thao tác nào ngoại trừ EXISTS trên một tập hợp chưa được khởi tạo đều sẽ gây ra lỗi.
• Nó có thể được tạo ra như một đối tượng cơ sở dữ liệu hiển thị trong toàn bộ cơ sở dữ liệu, hoặc bên trong một chương trình con để chỉ sử dụng ở đó.

Hình dưới đây minh họa việc phân bổ bộ nhớ của một Varray (dense).

Cú pháp cho VARRAY:

TYPE <type_name> IS VARRAY (<SIZE>) OF <DATA_TYPE>;

• Trong cú pháp trên, type_name được khai báo là một VARRAY thuộc kiểu 'DATA_TYPE' với giới hạn kích thước cho trước. Kiểu dữ liệu có thể là kiểu đơn giản hoặc phức tạp.

Bảng lồng nhau

Bảng lồng nhau là một tập hợp trong đó kích thước của mảng không cố định. Nó có kiểu chỉ số là số. Thông tin chi tiết hơn về kiểu bảng lồng nhau:

• Bảng lồng nhau không có giới hạn kích thước tối đa.
• Vì giới hạn trên không cố định, nên mỗi lần sử dụng cần phải mở rộng bộ nhớ bằng từ khóa 'EXTEND'.
• Được điền tuần tự bắt đầu từ chỉ số dưới '1'.
• Loại bộ sưu tập này có thể là cả hai dày đặc và thưa thớtChúng ta có thể tạo ra nó dưới dạng tập trung cao độ, đồng thời xóa ngẫu nhiên từng phần tử riêng lẻ, khiến nó trở nên thưa thớt.
• Nó mang lại sự linh hoạt hơn khi xóa các phần tử mảng.
• Thông tin này được lưu trữ trong một bảng cơ sở dữ liệu do hệ thống tạo ra và có thể được sử dụng trong truy vấn SELECT để lấy giá trị.
• Chỉ số dưới và số lượng có thể thay đổi.
• Nó phải được khởi tạo trước khi sử dụng. Bất kỳ thao tác nào ngoại trừ EXISTS trên một tập hợp chưa được khởi tạo đều sẽ gây ra lỗi.
• Nó có thể được tạo ra như một đối tượng cơ sở dữ liệu hiển thị trong toàn bộ cơ sở dữ liệu, hoặc bên trong một chương trình con để chỉ sử dụng ở đó.

Hình dưới đây minh họa việc phân bổ bộ nhớ của một bảng lồng nhau (dày đặc và thưa). Khoảng trống phần tử biểu thị một phần tử thưa.

Cú pháp cho bảng lồng nhau:

TYPE <type_name> IS TABLE OF <DATA_TYPE>;

• Trong cú pháp trên, type_name được khai báo là một tập hợp bảng lồng nhau thuộc kiểu 'DATA_TYPE'. Kiểu dữ liệu có thể là đơn giản hoặc phức tạp.

Mục lục theo bảng

Bảng chỉ mục là một tập hợp trong đó kích thước mảng không cố định. Không giống như các loại tập hợp khác, chỉ số của bảng chỉ mục có thể được người dùng định nghĩa. Các thuộc tính của bảng chỉ mục bao gồm:

• Chỉ số có thể là số nguyên hoặc chuỗi ký tự. Kiểu dữ liệu của chỉ số cần được chỉ định khi tạo tập hợp.
• Những bộ sưu tập này không được lưu trữ tuần tự.
• Chúng luôn có tính chất thưa thớt.
• Kích thước mảng không cố định.
• Chúng không thể được lưu trữ trong một cột của cơ sở dữ liệu. Chúng được tạo ra và sử dụng trong một phiên làm việc cụ thể.
• Chúng mang lại sự linh hoạt hơn trong việc duy trì chỉ số dưới.
• Các chỉ số dưới có thể là một dãy số âm.
• Chúng phù hợp hơn với các giá trị tập hợp tương đối nhỏ được sử dụng trong cùng một chương trình con.
• Chúng không cần phải được khởi tạo trước khi sử dụng.
• Chúng không thể được tạo ra như một đối tượng cơ sở dữ liệu; chúng chỉ được tạo ra bên trong một chương trình con.
• Không thể sử dụng BULK COLLECT với loại tập hợp này, vì chỉ số phải được cung cấp rõ ràng cho từng bản ghi.

Hình dưới đây giải thích việc phân bổ bộ nhớ của một bảng chỉ mục (thưa). Khoảng trống phần tử biểu thị một phần tử thưa.

Cú pháp cho bảng chỉ mục theo:

TYPE <type_name> IS TABLE OF <DATA_TYPE> INDEX BY VARCHAR2 (10);

• Trong cú pháp trên, type_name được khai báo là một tập hợp bảng có chỉ mục theo kiểu 'DATA_TYPE'. Biến chỉ số được cung cấp dưới dạng kiểu VARCHAR2 với kích thước tối đa là 10.

Khái niệm xây dựng và khởi tạo trong bộ sưu tập

Các hàm tạo là các hàm tích hợp sẵn được cung cấp bởi Oracle Các phương thức này có cùng tên với đối tượng hoặc tập hợp. Chúng được thực thi đầu tiên mỗi khi một đối tượng hoặc tập hợp được tham chiếu lần đầu tiên trong một phiên làm việc. Chi tiết quan trọng về hàm tạo trong ngữ cảnh tập hợp:

• Đối với các tập hợp, các hàm tạo này phải được gọi một cách rõ ràng để khởi tạo tập hợp.
• Cả Varray và các bảng lồng nhau đều cần được khởi tạo thông qua các hàm tạo này trước khi được tham chiếu trong chương trình.
• Hàm khởi tạo ngầm định mở rộng việc cấp phát bộ nhớ cho một tập hợp (ngoại trừ Varray), do đó nó cũng có thể gán các biến cho tập hợp đó.
• Việc gán giá trị thông qua hàm tạo không bao giờ làm cho tập hợp trở nên thưa thớt.

Phương thức thu thập

Oracle Cung cấp nhiều hàm để thao tác và làm việc với các tập hợp. Các hàm này xác định và sửa đổi các thuộc tính khác nhau của một tập hợp. Bảng dưới đây đưa ra các hàm khác nhau và mô tả của chúng.

Ví dụ 1: Loại bản ghi ở cấp độ chương trình con

Trong ví dụ này, chúng ta thấy cách điền dữ liệu vào bộ sưu tập bằng cách sử dụng 'THU THẬP SỐ LƯỢNGvà cách tham chiếu đến dữ liệu bộ sưu tập.

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;
/

Code Giải thích

• Code dòng 2-8: Loại bản ghi Bảng 'emp_det' được khai báo với các cột emp_no, emp_name, manager và salary có kiểu dữ liệu NUMBER, VARCHAR2, NUMBER và NUMBER.
• Code dòng 9: Tạo bộ sưu tập 'emp_det_tbl' với kiểu bản ghi là phần tử 'emp_det'.
• Code dòng 10: Khai báo biến 'guru99_emp_rec' thuộc kiểu 'emp_det_tbl' và khởi tạo nó với hàm tạo rỗng.
• Code dòng 12-15: Chèn dữ liệu mẫu vào bảng 'emp'.
• Code dòng 16: Cam kết giao dịch chèn.
• Code dòng 17: Lấy các bản ghi từ bảng 'emp' và điền dữ liệu vào biến tập hợp hàng loạt bằng lệnh “BULK COLLECT”. Biến 'guru99_emp_rec' hiện chứa tất cả các bản ghi có trong bảng 'emp'.
• Code dòng 19-26: Thiết lập vòng lặp 'FOR' để in tất cả các bản ghi trong tập hợp từng cái một. Các phương thức FIRST và LAST của tập hợp được sử dụng làm giới hạn dưới và giới hạn trên của tập hợp. vòng lặp.

Đầu ra: Khi đoạn mã trên được thực thi, bạn sẽ nhận được kết quả đầu ra sau.

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

Câu Hỏi Thường Gặp