Collections dan Records PL/SQL

5.13. Menggunakan Perintah FORALL

Kata kunci FORALL menginstruksikan PL/SQL engine untuk melakukan bulk-bind terhadap kumpulan input sebelum mengirimkannya ke SQL engine. Meskipun perintah FORALL mengandung pola iterasi, ia bukan perulangan FOR. Sintaksnya adalah:

FORALL index IN lower_bound..upper_bound
sql_statement;

Index dapat direferensi hanya bersama perintah FORALL dan hanya sebagai subscript collection. Perinyah SQL harus perintah INSERT, UPDATE, atau DELETE yang mereferensi elemen-elemen collection. Dan, batas-batasnya harus menentukan jangkauan yang valid dari bilangan-bilangan index yang bertalian. SQL engine mengeksekusi perintah SQL sekali untuk setiap bilangan index di dalam jangkauan.

Contoh: Menggunakan FORALL dengan Bagian dari Collection

Seperti ditunjukkan oleh contoh berikut ini, batasan-batasan dari perulangan FORALL dapat diterapkan kepada bagian dari collection, tidak perlu kepada seluruh elemen:

DECLARE
  TYPE NumList IS VARRAY(10) OF NUMBER;
  depts NumList := NumList(20,30,50,55,57,60,70,75,90,92);
BEGIN
  FORALL j IN 4..7 -- bulk-bind hanya bagian dari varray
  UPDATE emp
  SET sal = sal * 1.10
  WHERE deptno = depts(j);
END;

Contoh: Bulk Bind Memerlukan Collection Ter-subscript

Perintah SQL dapat mereferensi lebih dari satu collection. Bagaimanapun, PL/SQL engine melakukan bulk-binds hanya terhadap collection ter-subscript. Sehingga, dalam contoh berikut ini, ia tidak melakukan bulk-bind terhadap collection sals, yang mana dilewatkan ke function median:

FORALL i IN 1..20
  INSERT INTO emp2
  VALUES (enums(i), names(i), median(sals), ...);

Contoh: Menambahkan Data ke Object Table dengan FORALL

Sebagai tambahan untuk table-table relasional, perintah FORALL dapat memanipulasi objek tables, seperti ditunjukkan oleh contoh berikut ini:

CREATE   TYPE PNum AS OBJECT (n NUMBER);
/
CREATE TABLE partno OF PNum;
DECLARE
  TYPE NumTab IS TABLE OF NUMBER;
  nums NumTab := NumTab(1, 2, 3, 4);
  TYPE PNumTab IS TABLE OF PNum;
  pnums PNumTab := PNumTab(PNum(1), PNum(2), PNum(3), PNum(4));
BEGIN
  FORALL i IN pnums.FIRST..pnums.LAST
  INSERT INTO partno VALUES(pnums(i));
  FORALL i IN nums.FIRST..nums.LAST
  DELETE FROM partno
  WHERE n = 2 * nums(i);
  FORALL i IN nums.FIRST..nums.LAST
    INSERT INTO partno
    VALUES(100 + nums(i));
END;

5.13.1. Bagaimana FORALL Mempengaruhi Rollbacks

Dalam perintah FORALL, jika suatu eksekusi dari perintah SQL menyebabkan munculnya unhandled exception, seluruh perubahan database yang dibuat selama sebelum eksekusi akan dibatalkan. Namun, jika exception yang muncul tertangkap dan dapat ditangani, perubahan akan dibatalkan ke titik savepoint implisit yang ditandai sebelum eksekusi perintah SQL. Perubahan yang dibuat selama eksekusi-eksekusi sebelumnya tidak akan dibatalkan. Sebagai contoh, andaikata kita menciptakan database table yang menyimpan nomor departemen dan jabatan, seperti contoh berikut ini:

CREATE TABLE emp2 (deptno NUMBER(2), job VARCHAR2(15));

Berikutnya, kita menambahkan beberapa baris data ke dalam table, seperti berikut:

INSERT INTO emp2 VALUES(10, 'Clerk');
INSERT INTO emp2 VALUES(10, 'Clerk');
INSERT INTO emp2 VALUES(20, 'Bookkeeper'); -- 10-karakter jabatan
INSERT INTO emp2 VALUES(30, 'Analyst');
INSERT INTO emp2 VALUES(30, 'Analyst');

Kemudian, kita berusaha menambahkan 7 karakter string ‘ (temp)’ kepada jabatan tertentu menggunakan perintah UPDATE berikut ini:

DECLARE
  TYPE NumList IS TABLE OF NUMBER;
  depts NumList := NumList(10, 20, 30);
BEGIN
  FORALL j IN depts.FIRST..depts.LAST
  UPDATE emp2 SET job = job || ' (temp)'
  WHERE deptno = depts(j); -- memunculkan exception "value too large"
  EXCEPTION
    WHEN OTHERS THEN
      COMMIT;
END;

SQL engine mengeksekusi perintah UPDATE tiga kali, sekali untuk setiap bilangan index di dalam jangkauan tertentu, yaitu, sekali untuk depts(10), sekali untuk depts(20), dan sekali untuk depts(30). Eksekusi pertama berhasil, namun eksekusi kedua gagal karena nilai string ‘Bookkeeper (temp)’ terlalu besar untuk kolom job. Dalam kasus ini, hanya eksekusi kedua yang dibatalkan (rolled back).

Ketika suatu eksekusi dari perintah SQL menyebabkan munculnya exception, perintah FORALL berhenti. Dalam contoh kita, eksekusi kedua dari perintah UPDATE menyebabkan munculnya exception, sehingga eksekusi ketiga tidak pernah selesai.

5.13.2. Menghitung Baris Data yang Terpengaruh oleh Iterasi FORALL dengan Attribute %BULK_ROWCOUNT

Untuk memproses perintah-perintah manipulasi data SQL, SQL engine membuka cursor implisit bernama SQL. Attribute scalar dari cursor ini, %FOUND, %ISOPEN, %NOTFOUND, dan %ROWCOUNT, menghasilkan informasi yang berguna mengenai perintah manipulasi data SQL yang paling sering dieksekusi.

Cursor SQL memiliki satu attribute komposit, %BULK_ROWCOUNT, yang didesain untuk penggunaan bersama perintah FORALL. Attribute ini memiliki semantik dari index-by table. Elemen ke i -nya menyimpan jumlah baris data yang diproses oleh eksekusi ke i dari perintah INSERT, UPDATE, atau DELETE. Jika eksekusi ke i tidak mempengaruhi baris data, %BULK_ROWCOUNT(i) menghasilkan nol. Contohnya adalah sebagai berikut:

DECLARE
  TYPE NumList IS TABLE OF NUMBER;
  depts NumList := NumList(10, 20, 50);
BEGIN
  FORALL j IN depts.FIRST..depts.LAST
  UPDATE emp
  SET sal = sal * 1.10
  WHERE deptno = depts(j);
  -- Apakah perintah UPDATE ke-3 berpengaruh terhadap suatu baris data?
  IF SQL%BULK_ROWCOUNT(3) = 0 THEN ...
END;

Perintah FORALL dan attribute %BULK_ROWCOUNT menggunakan subscript-subscript yang sama. Sebagai contoh, jika FORALL menggunakan jangkauan 0..5, begitu juga dengan %BULK_ROWCOUNT.

%BULK_ROWCOUNT biasanya sama dengan 1 untuk inserts, karena sifat operasi insert berpengaruh hanya terhadap baris data tunggal. Namun untuk bentuk INSERT … SELECT, %BULK_ROWCOUNT dapat lebih besar daripada 1. Sebagai contoh, perintah FORALL di bawah ini menambahkan jumlah baris data yang berbeda-beda pada setiap iterasi. Setelah setiap iterasi, %BULK_ROWCOUNT menghasilkan jumlah item-item yang dimasukkan:

SET SERVEROUTPUT ON;

DECLARE
  TYPE num_tab IS TABLE OF NUMBER;
  deptnums num_tab;
BEGIN
  SELECT deptno BULK COLLECT
  INTO deptnums
  FROM DEPT;
  FORALL i IN 1..deptnums.COUNT
    INSERT INTO emp_by_dept
    SELECT empno, deptno
    FROM emp WHERE deptno = deptnums(i);
  FOR i IN 1..deptnums.COUNT LOOP
    -- Menghitung berapa banyak baris data yang telah ditambahkan
    -- untuk setiap departemen; dan dengan demikian, berapa banyak
    -- karyawan di dalam setiap departemen.
    dbms_output.put_line('Dept'||deptnums(i)||': inserted '||
    SQL%BULK_ROWCOUNT(i)||'records');
  END LOOP;
  dbms_output.put_line('Total records inserted =' || SQL%ROWCOUNT);
END;
/

Kita dapat juga menggunakan attribute scalar %FOUND, %NOTFOUND, dan %ROWCOUNT dengan bulk binds. Sebagai contoh, %ROWCOUNT menghasilkan jumlah total baris data yang diproses oleh seluruh eksekusi perintah SQL.

%FOUND dan %NOTFOUND mengacu hanya kepada eksekusi terakhir dari perintah SQL. Namun, kita dapat menggunakan %BULK_ROWCOUNT untuk menyimpulkan nilai-nilai mereka untuk eksekusi-eksekusi individual. Sebagai contoh, ketika%BULK_ROWCOUNT(i) adalah nol, %FOUND dan %NOTFOUND adalah FALSE dan TRUE, secara berturut-turut.

5.13.3. Menangani Exceptions FORALL dengan Attribute %BULK_EXCEPTIONS

PL/SQL menyediakan mekanisme untuk menangani exceptions yang muncul selama eksekusi perintah FORALL. Mekanisme ini memungkinkan operasi bulk-bind untuk menyimpan informasi tentang exceptions dan melanjutkan pemrosesan.

Untuk membuat bulk bind diselesaikan meskipun terjadi errors, kita menambahkan kata kunci SAVE EXCEPTIONS terhadap perintah FORALL kita. Sintaksnya adalah sebagai berikut:

FORALL index IN lower_bound..upper_bound SAVE EXCEPTIONS
{insert_stmt | update_stmt | delete_stmt}

Seluruh exceptions yang muncul selama eksekusi disimpan di dalam attribute cursor baru %BULK_EXCEPTIONS, yang menyimpan kumpulan records. Setiap record memiliki dua field. Field pertama, %BULK_EXCEPTIONS(i).ERROR_INDEX, memegang “iterasi” dari perintah FORALL selama dimana exception muncul. Field kedua, %BULK_EXCEPTIONS(i).ERROR_CODE, memegang error code Oracle yang terkait.

Nilai-nilai yang disimpan oleh %BULK_EXCEPTIONS selalu mengacu kepada perintah FORALL yang paling sering dieksekusi. Jumlah exceptions disimpan di dalam attribute penghitung %BULK_EXCEPTIONS, yaitu %BULK_EXCEPTIONS.COUNT. Subscript-subscriptnya berjangkauan dari 1 hingga COUNT.

Jika kita mengabaikan kata kunci SAVE EXCEPTIONS, eksekusi dari perintah FORALL berhenti ketika exception muncul. Dalam kasus tersebut, SQL%BULK_EXCEPTIONS.COUNT menghasilkan 1, dan SQL%BULK_EXCEPTIONS berisi hanya satu record. Jika tidak terdapat exception yang muncul selama eksekusi, SQL%BULK_EXCEPTIONS.COUNT menghasilkan 0.

Contoh berikut ini menunjukkan bagaimana bergunanya attribute cursor %BULK_EXCEPTIONS dapat berupa:

DECLARE
  TYPE NumList IS TABLE OF NUMBER;
  num_tab NumList := NumList(10,0,11,12,30,0,20,199,2,0,9,1);
  errors NUMBER;
  dml_errors EXCEPTION;
  PRAGMA exception_init(dml_errors, -24381);
BEGIN
  FORALL i IN num_tab.FIRST..num_tab.LAST SAVE EXCEPTIONS
  DELETE FROM emp
  WHERE sal > 500000/num_tab(i);
  EXCEPTION
    WHEN dml_errors THEN
      errors:= SQL%BULK_EXCEPTIONS.COUNT;
      dbms_output.put_line('Number of errors is ' || errors);
  FOR i IN 1..errors LOOP
    dbms_output.put_line('Error' || i || ' occurred during '||'iteration' || SQL%BULK_EXCEPTIONS(i).ERROR_INDEX);
    dbms_output.put_line('Oracle error is ' ||SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE));
  END LOOP;
END;

Dalam contoh ini, PL/SQL memunculkan predefined exception ZERO_DIVIDE ketika I sama dengan 2, 6, 10. Setelah bulk-bind selesai, SQL%BULK_EXCEPTIONS.COUNT menghasilkan 3, dan isi dari SQL%BULK_EXCEPTIONS adalah (2,1476, (6,1476), dan (10,1476). Untuk mendapatkan Oracle error message (yang mengandung code), kita menegasikan nilai SQL%BULK_EXCEPTIONS(i).ERROR_CODE dan melewatkan hasilnya ke fungsi error-reporting SQLERRM, yang mengharapkan angka negatif. Berikut ini outputnya:

Number of errors is 3
Error 1 occurred during iteration 2
Oracle error is ORA-01476: divisor is equal to zero
Error 2 occurred during iteration 6
Oracle error is ORA-01476: divisor is equal to zero
Error 3 occurred during iteration 10
Oracle error is ORA-01476: divisor is equal to zero