Oracle BỘ SƯU TẬP HÀNG LỚN PL/SQL: Ví dụ FORALL

THU THẬP LỚN là gì?

BULK COLLECT giảm thiểu việc chuyển đổi ngữ cảnh giữa các SQL và công cụ PL/SQL, cho phép công cụ SQL truy xuất các bản ghi cùng một lúc.

Oracle PL / SQL Chức năng này cho phép truy xuất hàng loạt bản ghi thay vì truy xuất từng bản ghi một. BULK COLLECT có thể được sử dụng trong câu lệnh SELECT để điền dữ liệu hàng loạt vào các bản ghi, hoặc để truy xuất một phần dữ liệu cần thiết. con trỏ Theo lô. Vì BULK COLLECT truy xuất các bản ghi theo lô, nên mệnh đề INTO luôn phải chứa một biến kiểu tập hợp. Ưu điểm chính của việc sử dụng BULK COLLECT là nó tăng hiệu suất bằng cách giảm sự tương tác giữa cơ sở dữ liệu và công cụ PL/SQL.

Cú pháp:

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

Trong cú pháp trên, BULK COLLECT được sử dụng để thu thập dữ liệu từ các câu lệnh SELECT và FETCH.

Điều khoản FORALL

Câu lệnh FORALL thực hiện Các thao tác DML Trên dữ liệu khối. Nó tương tự như câu lệnh vòng lặp FOR, ngoại trừ việc trong vòng lặp FOR, các thao tác diễn ra ở cấp độ bản ghi, trong khi ở FORALL không có khái niệm VÒNG LẶP. Thay vào đó, toàn bộ dữ liệu có trong phạm vi được chỉ định sẽ được xử lý cùng một lúc.

Cú pháp:

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

<DML operations>;

Trong cú pháp trên, thao tác DML đã cho sẽ được thực thi cho toàn bộ dữ liệu nằm giữa phạm vi dưới và phạm vi trên.

Điều khoản GIỚI HẠN

Khái niệm thu thập hàng loạt (bulk collect) tải toàn bộ dữ liệu vào biến tập hợp đích dưới dạng một khối, nghĩa là toàn bộ dữ liệu sẽ được điền vào biến tập hợp trong một lần. Tuy nhiên, điều này không được khuyến khích khi tổng số bản ghi cần tải rất lớn, vì khi PL/SQL cố gắng tải toàn bộ dữ liệu, nó sẽ tiêu tốn nhiều bộ nhớ phiên hơn. Do đó, luôn luôn nên giới hạn kích thước của thao tác thu thập hàng loạt này.

Giới hạn kích thước này có thể dễ dàng đạt được bằng cách đưa điều kiện ROWNUM vào câu lệnh SELECT, trong khi đó, đối với con trỏ thì điều này không thể thực hiện được.

Để khắc phục điều này, Oracle đã cung cấp mệnh đề LIMIT để xác định số lượng bản ghi cần được đưa vào khối dữ liệu.

Cú pháp:

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

Trong cú pháp trên, câu lệnh tìm nạp con trỏ sử dụng câu lệnh BULK COLLECT cùng với mệnh đề LIMIT.

BULK COLLECT Thuộc tính

Tương tự như các thuộc tính con trỏ, BULK COLLECT có %BULK_ROWCOUNT(n) trả về số lượng hàng bị ảnh hưởng trong câu lệnh DML thứ n của câu lệnh FORALL, tức là nó cung cấp số lượng bản ghi bị ảnh hưởng trong câu lệnh FORALL cho mỗi giá trị từ biến tập hợp. Thuật ngữ 'n' chỉ ra thứ tự của giá trị trong tập hợp mà cần đếm số hàng.

Ví dụ 1: Trong ví dụ này, chúng ta sẽ trích xuất tất cả tên nhân viên từ bảng emp bằng cách sử dụng BULK COLLECT, và chúng ta cũng sẽ tăng lương của tất cả nhân viên lên 5000 bằng cách sử dụng FORALL.

Ảnh chụp màn hình bên dưới hiển thị ví dụ về BULK COLLECT và FORALL cùng với kết quả đầu ra của nó. 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;
/

Đầu ra

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

Code Giải thích:

• Code dòng 2: Khai báo con trỏ guru99_det cho câu lệnh 'SELECT emp_name FROM emp'.
• Code dòng 3: Khai báo lv_emp_name_tbl là kiểu bảng VARCHAR2(50).
• Code dòng 4: Khai báo lv_emp_name là kiểu lv_emp_name_tbl.
• Code dòng 6: Đang mở con trỏ.
• Code dòng 7: Lấy dữ liệu từ con trỏ bằng lệnh BULK COLLECT với giới hạn kích thước là 5000 vào biến lv_emp_name.
• Code dòng 8-11: Thiết lập vòng lặp FOR để in tất cả các bản ghi trong tập hợp lv_emp_name.
• Code dòng 12: Sử dụng lệnh FORALL để cập nhật lương của tất cả nhân viên tăng thêm 5000.
• Code dòng 14: Thực hiện giao dịch.

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