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

Bởi: Fiona Nâu Fiona Nâu
Hours cập nhật

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

BULK COLLECT giảm chuyển đổi ngữ cảnh giữa SQL và công cụ PL/SQL, đồng thời cho phép công cụ SQL tìm nạp các bản ghi cùng một lúc.

Oracle PL/SQL cung cấp chức năng tìm nạp hàng loạt bản ghi thay vì tìm nạp từng bản ghi một. BULK COLLECT này có thể được sử dụng trong câu lệnh 'SELECT' để điền hàng loạt bản ghi hoặc tìm nạp con trỏ hàng loạt. Vì BULK COLLECT tìm nạp bản ghi ở dạng BULK nên mệnh đề INTO phải luôn chứa biến loại bộ sưu tập. Ưu điểm chính của việc sử dụng BULK COLLECT là 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 <columnl> BULK COLLECT INTO bulk_varaible FROM <table name>;
FETCH <cursor_name> BULK COLLECT INTO <bulk_varaible >;

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

Điều khoản FORALL

FORALL cho phép thực hiện hàng loạt các thao tác DML trên dữ liệu. Nó tương tự như câu lệnh vòng lặp FOR ngoại trừ vòng lặp FOR mọi thứ xảy ra ở mức kỷ lục trong khi ở FORALL không có khái niệm LOOP. Thay vào đó, toàn bộ dữ liệu có trong phạm vi nhất đị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 thấp hơn và cao hơn.

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

Khái niệm thu thập hàng loạt tải toàn bộ dữ liệu vào biến thu thập mục tiêu dưới dạng hàng loạt, tức là toàn bộ dữ liệu sẽ được đưa vào biến thu thập chỉ trong một lần. Nhưng đ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 đó, việc giới hạn quy mô của hoạt động thu thập số lượng lớn này luôn là điều tốt.

Tuy nhiên, giới hạn kích thước này có thể dễ dàng đạt được bằng cách đưa ra điều kiện ROWNUM trong câu lệnh 'SELECT', trong khi đó trong trường hợp con trỏ thì điều này là không thể.

Để 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 hàng loạt.

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ư con trỏ thuộc tính BULK COLLECT có %BULK_ROWCOUNT(n) trả về số lượng hàng bị ảnh hưởng trong nth Câu lệnh DML của câu lệnh FORALL, tức là nó sẽ đưa ra 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' cho biết chuỗi giá trị trong bộ sưu tập mà số lượng hàng là cần thiết.

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

BULK COLLECT Thuộc tính

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

NHỮNG BÀI VIẾT LIÊN QUAN

Đầu ra

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

Giải thích mã:

  • Dòng mã 2: Khai báo con trỏ guru99_det cho câu lệnh 'SELECT emp_name FROM emp'.
  • Dòng mã 3: Khai báo lv_emp_name_tbl là loại bảng của VARCHAR2(50)
  • Dòng mã 4: Khai báo lv_emp_name là kiểu lv_emp_name_tbl.
  • Dòng mã 6: Đang mở con trỏ.
  • Dòng mã 7: Tìm nạp con trỏ bằng cách sử dụng BULK COLLECT với kích thước LIMIT là biến 5000 intl lv_emp_name.
  • Dòng mã 8-11: Thiết lập vòng lặp FOR để in tất cả bản ghi trong bộ sưu tập lv_emp_name.
  • Dòng mã 12: Sử dụng FORALL cập nhật lương của tất cả nhân viên lên 5000.
  • Dòng mã 14: Cam kết giao dịch.

Bạn có thể thích: