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

Bộ sưu tập là một nhóm các phần tử được sắp xếp theo kiểu dữ liệu cụ thể. Nó có thể là một bộ sưu tập kiểu dữ liệu đơn giản hoặc kiểu dữ liệu phức tạp (như kiểu do người dùng xác định hoặc kiểu bản ghi).

Trong 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 trong bộ sưu tập được gán một chỉ số dưới duy nhất. Dữ liệu trong bộ sưu tập đó có thể được thao tác hoặc tìm nạp bằng cách tham chiếu đến chỉ số duy nhất đó.

Bộ sưu tập là thứ hữu ích nhất khi một dữ liệu lớn cùng loại cần được xử lý hoặc thao tác. Các bộ sưu tập có thể được điền và thao tác toàn bộ 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ố dưới và lưu trữ như dưới đây.

Chỉ mục theo bảng (còn được gọi là Mảng kết hợp)
Bảng lồng nhau
Biến thể

Tại bất kỳ thời điểm nào, dữ liệu trong bộ sưu tập có thể được gọi bằng ba thuật ngữ Tên bộ sưu tập, Chỉ số dưới, Tên trường/cột là “ ( ). ”. Bạn sẽ tìm hiểu thêm về các danh mục bộ sưu tập nêu trên trong phần bên dưới.

Biến thể

Varray là một phương pháp thu thập trong đó kích thước của mảng được cố định. Kích thước mảng không thể vượt quá giá trị cố định của nó. Chỉ số dưới của Varray là một giá trị số. Sau đây là các thuộc tính của Varray.

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

Hình dưới đây sẽ giải thích sơ đồ phân bổ bộ nhớ của Varray (dày đặc).

Subscript	1	2	3	4	5	6	7
Giá trị	XYZ	Dfv	Sde	Cx	vbc	Như	Qwe

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à VARRAY của kiểu 'DATA_TYPE' cho giới hạn kích thước đã cho. 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ố số. Dưới đây là mô tả thêm về loại bảng lồng nhau.

Bảng lồng nhau không có giới hạn kích thước trên.
Vì giới hạn kích thước trên không cố định nên bộ sưu tập, bộ nhớ cần được mở rộng mỗi lần trước khi chúng ta sử dụng. Chúng tôi có thể mở rộng bộ sưu tập bằng từ khóa 'EXTEND'.
Được điền tuần tự bắt đầu bằng chỉ số dưới '1'.
Loại bộ sưu tập này có thể có cả hai dày đặc và thưa thớt, tức là chúng ta có thể tạo bộ sưu tập dưới dạng dày đặc và chúng ta cũng có thể xóa ngẫu nhiên phần tử mảng riêng lẻ, điều này làm cho nó trở nên thưa thớt.
Nó mang lại sự linh hoạt hơn trong việc xóa phần tử mảng.
Nó được lưu trữ trong bảng cơ sở dữ liệu do hệ thống tạo và có thể được sử dụng trong truy vấn chọn để tìm nạp các giá trị.
Chỉ số dưới và thứ tự không ổn định, tức là chỉ số dưới và số lượng phần tử mảng có thể khác nhau.
Chúng cần được khởi tạo trước khi sử dụng chúng trong các chương trình. Bất kỳ thao tác nào (ngoại trừ thao tác EXISTS) trên bộ sưu tập chưa được khởi tạo sẽ gây ra lỗi.
Nó có thể được tạo dưới dạng 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 chương trình con, chỉ có thể được sử dụng trong chương trình con đó.

Hình dưới đây sẽ giải thích việc phân bổ bộ nhớ của Bảng lồng nhau (dày đặc và thưa thớt) theo sơ đồ. Không gian phần tử màu đen biểu thị phần tử trống trong bộ sưu tập tức là thưa thớt.

Subscript	1	2	3	4	5	6	7
Giá trị (dày đặc)	XYZ	Dfv	Sde	Cx	vbc	Như	Qwe
Giá trị (thưa thớt)	Qwe		Asd	afg		Asd	Wer

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

TYPE <tvpe name> IS TABLE OF <DATA TYPE>;

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

Chỉ mục theo bảng

Index-by-table 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 kiểu tập hợp khác, trong tập hợp index-by-table, subscript có thể được người dùng định nghĩa. Sau đây là các thuộc tính của index-by-table.

Chỉ số dưới có thể là số nguyên hoặc chuỗi. Tại thời điểm tạo bộ sưu tập, loại chỉ số dưới cần được đề cậ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 cột cơ sở dữ liệu. Chúng sẽ được tạo và sử dụng trong bất kỳ chương trình nào trong phiên 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ũng có thể thuộc chuỗi chỉ số âm.
Chúng thích hợp hơn khi sử dụng cho các giá trị tập thể tương đối nhỏ hơn trong đó bộ sưu tập có thể được khởi tạo và sử dụng trong cùng các chương trình con.
Chúng không cần phải được khởi tạo trước khi bắt đầu sử dụng chúng.
Nó không thể được tạo như một đối tượng cơ sở dữ liệu. Nó chỉ có thể được tạo bên trong chương trình con, chương trình con này chỉ có thể được sử dụng trong chương trình con đó.
Không thể sử dụng BULK COLLECT trong loại bộ sưu tập này vì chỉ số dưới phải được cung cấp rõ ràng cho từng bản ghi trong bộ sưu tập.

Hình dưới đây sẽ giải thích việc phân bổ bộ nhớ của Bảng lồng nhau (thưa thớt) theo sơ đồ. Không gian phần tử màu đen biểu thị phần tử trống trong bộ sưu tập tức là thưa thớt.

Chỉ số dưới (varchar)	ĐẦU TIÊN	THỨ HAI	THỨ BA	FOURTH	THỨ NĂM	SÁU	MÙA THU
Giá trị (thưa thớt)	Qwe		Asd	afg		Asd	Wer

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

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 index-by-table có kiểu 'DATA_TYPE'. Kiểu dữ liệu có thể là kiểu đơn giản hoặc phức tạp. Biến subsciprt/index được đưa ra là 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

Constructor là hàm dựng sẵn do oracle cung cấp có cùng tên với đối tượng hoặc bộ sưu tập. Chúng được thực thi đầu tiên bất cứ khi nào đối tượng hoặc bộ sưu tập được tham chiếu lần đầu tiên trong một phiên. Dưới đây là các chi tiết quan trọng của constructor trong ngữ cảnh bộ sưu tập:

Đối với các bộ sưu tậ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 nó.
Cả hai bảng Varray và Nested đều cần được khởi tạo thông qua các hàm tạo này trước khi được đưa vào chương trình.
Trình xây dựng ngầm mở rộng việc cấp phát bộ nhớ cho một bộ sưu tập (ngoại trừ Varray), do đó hàm tạo cũng có thể gán các biến cho các bộ sưu tập.
Việc gán giá trị cho bộ sưu tập thông qua các hàm tạo sẽ không bao giờ làm cho bộ sưu tậ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 bộ sưu tập. Các hàm này rất hữu ích trong chương trình để xác định và sửa đổi các thuộc tính khác nhau của các bộ sưu tập. Bảng sau đây sẽ cung cấp các hàm khác nhau và mô tả của chúng.

Phương pháp	Mô tả	TỔNG HỢP
Tồn tại (n)	Phương pháp này sẽ trả về kết quả Boolean. Nó sẽ trả về 'TRUE' nếu n^th phần tử tồn tại trong bộ sưu tập đó, nếu không nó sẽ trả về SAI. Chỉ có thể sử dụng các hàm EXISTS trong bộ sưu tập chưa được khởi tạo	.EXISTS(element_position)
ĐẾM	Cung cấp tổng số phần tử có trong một bộ sưu tập	.ĐẾM
LIMIT	Nó trả về kích thước tối đa của bộ sưu tập. Đối với Varray, nó sẽ trả về kích thước cố định đã được xác định. Đối với bảng lồng nhau và bảng chỉ mục, nó cho giá trị NULL	.GIỚI HẠN
ĐẦU TIÊN	Trả về giá trị của biến chỉ mục đầu tiên (chỉ số dưới) của bộ sưu tập	.ĐẦU TIÊN
LAST	Trả về giá trị của biến chỉ mục cuối cùng (chỉ số dưới) của bộ sưu tập	.CUỐI CÙNG
TRƯỚC (n)	Trả về biến chỉ số đứng trước trong tập hợp n^th yếu tố. Nếu không có giá trị chỉ mục đứng trước NULL được trả về	.PRIOR(n)
TIẾP THEO (n)	Trả về biến chỉ mục thành công trong tập hợp n^th yếu tố. Nếu không có giá trị chỉ mục thành công thì trả về NULL	.TIẾP THEO(n)
MỞ RỘNG	Mở rộng một phần tử trong bộ sưu tập ở cuối	.MỞ RỘNG
MỞ RỘNG (n)	Mở rộng n phần tử ở cuối bộ sưu tập	.MỞ RỘNG(n)
MỞ RỘNG (n,i)	Mở rộng n bản sao của i^th phần tử ở cuối bộ sưu tập	.EXTEND(n,i)
TRIM	Xóa một phần tử khỏi cuối bộ sưu tập	.TRIM
TRIM (n)	Xóa n phần tử khỏi cuối bộ sưu tập	.TRIM (n)
DELETE	Xóa tất cả các phần tử khỏi bộ sưu tập. Làm cho bộ sưu tập trống	.XÓA BỎ
XÓA (n)	Xóa phần tử thứ n khỏi bộ sưu tập. Nếu n^th phần tử là NULL, thì điều này sẽ không làm gì cả	.DELETE(n)
XÓA (m,n)	Xóa phần tử trong phạm vi m^th đến n^th trong bộ sưu tập	.DELETE(m,n)

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

Trong ví dụ này, chúng ta sẽ xem cách điền vào bộ sưu tập bằng cách sử dụng 'THU THẬP SỐ LƯỢNG' và cách tham khảo dữ liệu thu thậ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;
/

Giải thích mã:

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

Đầu ra: Như bạn có thể thấy trong ảnh chụp màn hình ở trên khi đoạn mã trên được thực thi, bạn sẽ nhận được kết quả 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
----------------------------------------------

Bạn có thể thích: