Oracle PL/SQL BULK COLLECT: Contoh FORALL

Oleh: Fiona Coklat Fiona Coklat
Hours Updated

⚡ Ringkasan Cerdas

PENGUMPULAN DALAM JUMLAH BESAR di Oracle PL/SQL mengambil banyak baris sekaligus ke dalam sebuah koleksi, sementara FORALL mengirimkan DML massal kembali ke basis data. Keduanya mengurangi peralihan konteks antara mesin SQL dan PL/SQL, sehingga meningkatkan kinerja.

  • 📦 PENGAMBILAN DALAM JUMLAH BESAR: Mengambil beberapa baris sekaligus ke dalam variabel koleksi, menggantikan pengambilan baris demi baris yang lambat.
  • 🔁 UNTUK SEMUA: Menjalankan satu perintah INSERT, UPDATE, atau DELETE di seluruh koleksi dengan satu kali peralihan konteks.
  • 📏 Klausul LIMIT: Membatasi jumlah baris yang dimuat oleh setiap pengambilan BULK COLLECT, sehingga melindungi memori sesi pada tabel berukuran besar.
  • 📊 Atribut PENGUMPULAN MASSAL: Atribut %BULK_ROWCOUNT(n) melaporkan berapa banyak baris yang terpengaruh oleh pernyataan DML FORALL ke-n.
  • ⚙️ Koleksi yang Dibutuhkan: Klausul INTO harus menargetkan tipe koleksi, seperti tabel bersarang atau larik asosiatif.
  • 🤖 Bantuan AI: Asisten AI seperti GitHub Copilot membuat draf blok BULK COLLECT dan FORALL serta menandai klausa LIMIT yang hilang.
Baca Selengkapnya

Oracle Gambaran umum PL/SQL BULK COLLECT dan FORALL dengan klausa LIMIT

Apa itu KUMPULKAN MASSAL?

BULK COLLECT mengurangi peralihan konteks di antara SQL dan mesin PL/SQL serta memungkinkan mesin SQL untuk mengambil data sekaligus.

Oracle PL / SQL menyediakan fungsi pengambilan data secara massal, bukan satu per satu. BULK COLLECT ini dapat digunakan dalam pernyataan SELECT untuk mengisi data secara massal, atau untuk mengambil data dalam jumlah besar. kursor secara massal. Karena BULK COLLECT mengambil data secara massal, klausa INTO harus selalu berisi variabel tipe koleksi. Keuntungan utama menggunakan BULK COLLECT adalah meningkatkan kinerja dengan mengurangi interaksi antara basis data dan mesin PL/SQL.

sintaks:

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

Dalam sintaks di atas, BULK COLLECT digunakan untuk mengumpulkan data dari pernyataan SELECT dan FETCH.

Klausul FORALL

Pernyataan FORALL melakukan Operasi DML pada data secara massal. Ini menyerupai pernyataan perulangan FOR, kecuali bahwa dalam perulangan FOR tindakan terjadi pada tingkat record, sedangkan dalam FORALL tidak ada konsep LOOP. Sebaliknya, seluruh data yang ada dalam rentang yang diberikan diproses secara bersamaan.

sintaks:

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

<DML operations>;

Dalam sintaks di atas, operasi DML yang diberikan akan dieksekusi untuk seluruh data yang ada di antara rentang bawah dan atas.

Klausul BATAS

Konsep bulk collect memuat seluruh data ke dalam variabel koleksi target secara massal, yaitu seluruh data akan dimasukkan ke dalam variabel koleksi sekaligus. Namun, hal ini tidak disarankan ketika jumlah total record yang perlu dimuat sangat besar, karena ketika PL/SQL mencoba memuat seluruh data, ia akan mengonsumsi lebih banyak memori sesi. Oleh karena itu, selalu lebih baik untuk membatasi ukuran operasi bulk collect ini.

Batasan ukuran ini dapat dengan mudah dicapai dengan memperkenalkan kondisi ROWNUM dalam pernyataan SELECT, sedangkan dalam kasus kursor hal ini tidak mungkin dilakukan.

Untuk mengatasinya, Oracle telah menyediakan klausa LIMIT yang mendefinisikan jumlah catatan yang perlu disertakan dalam pemrosesan massal.

sintaks:

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

Dalam sintaks di atas, pernyataan pengambilan kursor menggunakan pernyataan BULK COLLECT bersama dengan klausa LIMIT.

Atribut KUMPULKAN MASSAL

Mirip dengan atribut kursor, BULK COLLECT memiliki %BULK_ROWCOUNT(n) yang mengembalikan jumlah baris yang terpengaruh dalam pernyataan DML ke-n dari pernyataan FORALL, yaitu memberikan jumlah catatan yang terpengaruh dalam pernyataan FORALL untuk setiap nilai dari variabel koleksi. Istilah 'n' menunjukkan urutan nilai dalam koleksi yang jumlah barisnya dibutuhkan.

Contoh 1: Dalam contoh ini, kita akan memproyeksikan semua nama karyawan dari tabel emp menggunakan BULK COLLECT, dan kita juga akan menaikkan gaji semua karyawan sebesar 5000 menggunakan FORALL.

Tangkapan layar di bawah ini menunjukkan contoh BULK COLLECT dan FORALL beserta outputnya. Oracle.

Contoh pengumpulan massal dengan batasan dan untuk semua, memperbarui gaji karyawan di Oracle PL / SQL

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

ARTIKEL TERKAIT

Keluaran

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

Code Penjelasan:

  • Code baris 2: Mendeklarasikan kursor guru99_det untuk pernyataan 'SELECT emp_name FROM emp'.
  • Code baris 3: Mendeklarasikan lv_emp_name_tbl sebagai tipe tabel VARCHAR2(50).
  • Code baris 4: Mendeklarasikan lv_emp_name sebagai tipe lv_emp_name_tbl.
  • Code baris 6: Membuka kursor.
  • Code baris 7: Mengambil kursor menggunakan BULK COLLECT dengan ukuran LIMIT 5000 ke dalam variabel lv_emp_name.
  • Code baris 8-11: Membuat perulangan FOR untuk mencetak semua catatan dalam koleksi lv_emp_name.
  • Code baris 12: Menggunakan FORALL untuk memperbarui gaji semua karyawan sebesar 5000.
  • Code baris 14: Melakukan ..

Pertanyaan Umum Demo Slot

Tidak. Perintah BULK COLLECT SELECT tidak pernah menimbulkan NO_DATA_FOUND; melainkan mengembalikan koleksi kosong. Selalu uji koleksi dengan metode .COUNT sebelum melakukan pencarian.ping, jika tidak, Anda mungkin memproses nol baris secara diam-diam.

SAVE EXCEPTIONS memungkinkan FORALL tetap berjalan ketika baris individual gagal. Baris yang gagal disimpan di SQL%BULK_EXCEPTIONS, lalu Oracle meningkatkan ORA-24381, yang Anda jebak di dalam pengecualian penanganan untuk memeriksa setiap kesalahan.

Gunakan BULK COLLECT setiap kali sebuah loop membaca banyak baris. A kursor Perulangan FOR mengambil satu baris per pernyataan switch, jadi pengambilan data secara massal ditambah FORALL dapat berjalan berkali-kali lebih cepat pada kumpulan hasil yang besar.

BULK COLLECT mengembalikan banyak baris sekaligus, jadi memerlukan kontainer multi-baris. Target INTO harus berupa koleksi seperti tabel bersarang, VARRAY, atau array asosiatif, bukan variabel skalar tunggal.

Tidak. Header FORALL hanya menggerakkan satu perintah INSERT, UPDATE, DELETE, atau MERGE. Hanya nilai dalam klausa VALUES dan WHERE yang dapat berubah per iterasi. Untuk beberapa pernyataan, gunakan pernyataan FORALL terpisah.

Pemrosesan massal dapat beberapa kali hingga lebih dari seratus kali lebih cepat daripada kode baris demi baris, karena BULK COLLECT dan FORALL menggabungkan ribuan peralihan konteks mesin menjadi beberapa saja, sehingga secara signifikan mengurangi overhead pada volume data yang besar.

Ya. Kopilot GitHub Draf ini mengambil data dari komentar, termasuk perintah BULK COLLECT, perulangan DML FORALL, dan klausa LIMIT, serta menyarankan deklarasi tipe koleksi, meskipun Anda harus meninjau sendiri ukuran batch dan penanganan kesalahan.

Asisten AI memindai perulangan yang mengambil atau mengubah satu baris pada satu waktu dan merekomendasikan penulisan ulang dengan BULK COLLECT, LIMIT, dan FORALL. Tinjauan pembelajaran mesin ini mendeteksi batasan LIMIT yang hilang dan hambatan kinerja sebelum masuk ke tahap produksi.

Ringkaslah postingan ini dengan: