Collections dan Records PL/SQL
5.11. Menghindari Exception CollectionDalam banyak kasus, jika kita mereferensi elemen collection yang tidak ada, PL/SL memunculkan predefined exception. Mari kita perhatikan contoh berikut ini:
DECLARE TYPE NumList IS TABLE OF NUMBER; nums NumList; -- secara atomik null BEGIN /* Assume execution continues despite the raised exceptions. */ nums(1) := 1; -- menyebabkan COLLECTION_IS_NULL (1) nums := NumList(1,2); -- menginisialisasi table nums(NULL) := 3 -- menyebabkan VALUE_ERROR (2) nums(0) := 3; -- menyebabkan SUBSCRIPT_OUTSIDE_LIMIT (3) nums(3) := 3; -- menyebabkan SUBSCRIPT_BEYOND_COUNT (4) nums.DELETE(1); -- menghapus elemen 1 IF nums(1) = 1 THEN ... -- menyebabkan NO_DATA_FOUND (5)
Di dalam kasus pertama, nested table secara atomik adalah null. Dalam kasus kedua, subscript adalah null. Dalam kasus ketiga, subscript berada di luar jangkauan yang diperbolehkan. Dalam kasus keempat, subscript melampaui jumlah elemen dalam table. Dalam kasus kelima, subscript menunjuk kepada elemen yang telah terhapus.
Daftar berikut ini menunjukkan kapan exception tertentu muncul:
- COLLECTION_IS_NULL kita berusaha beroperasi pada collection yang secara atomik null.
- NO_DATA_FOUND a subscript designates an element that was deleted, or a nonexistent element of an associative array.
- SUBSCRIPT_BEYOND_COUNT a subscript exceeds the number of elements in a collection.
- SUBSCRIPT_OUTSIDE_LIMIT a subscript is outside the allowed range.
- VALUE_ERROR a subscript is null or not convertible to the key type. This exception might occur if the key is defined as a
- PLS_INTEGER range, and the subscript is outside this range.
Dalam beberap kasus, kita dapat melewatkan subscript-subscript yang tidak benar (invalid) kepada method tanpa menyebabkan munculnya exception. Sebagai contoh, ketika kita melewatkan subscript null kepada procedure DELETE, ia tidak melakukan apapun. Juga, kita dapat mengganti elemen-elemen yang telah terhapus tanpa memunculkan NO_DATA_FOUND, seperti ditunjukkan oleh contoh di bawah ini:
DECLARE TYPE NumList IS TABLE OF NUMBER; nums NumList := NumList(10,20,30); -- menginisialisasi table BEGIN nums.DELETE(-1); -- tidak menyebabkan SUBSCRIPT_OUTSIDE_LIMIT nums.DELETE(3); -- menghapus elemen ke-3 dbms_output.put_line(nums.COUNT); -- menampilkan 2 nums(3) := 30; -- diperbolehkan; tidak menyebabkan NO_DATA_FOUND dbms_output.put_line(nums.COUNT); -- menampilkan 3 END;
Tipe-tipe collection ter-package dan tipe-tipe collection lokal tidak pernah kompatibel. Sebagai contoh, bayangkan kita ingin memanggil procedure ter-package berikut ini:
CREATE PACKAGE pkg1 AS TYPE NumList IS VARRAY(25) OF NUMBER(4); PROCEDURE delete_emps(emp_list NumList); END pkg1; CREATE PACKAGE BODY pkg1 AS PROCEDURE delete_emps(emp_list NumList) IS ... ... END pkg1;
Ketika kita menjalankan blok PL/SQL di bawah ini, pemanggilan procedure kedua gagal dengan pesan kesalahan wrong number atau types of arguments. Hal ini dikarenakan package dan tipe-tipe VARRAY lokal tidak kompatibel meskipun definisi-definisi mereka identik.
DECLARE TYPE NumList IS VARRAY(25) OF NUMBER(4); emps pkg1.NumList := pkg1.NumList(7369, 7499); emps2 NumList := NumList(7521, 7566); BEGIN pkg1.delete_emps(emps); pkg1.delete_emps(emps2); -- menyebabkan kesalahan kompilasi END;
5.12. Mengurangi Ongkos Perulangan untuk Collections dengan Bulk Binds
Seperti ditunjukkan oleh Gambar 5-3, PL/SQL engine mengeksekusi perintah-perintah prosedural namun mengirimkan perintah-perintah SQL kepada SQL engine, yang mengeksekusi perintah-perintah SQL dan, dalam beberapa kasus, menghasilkan data untuk dikirimkan ke PL/SQL engine.
Gambar 5-3 Perpindahan Konteks
Terlalu banyak perpindahan konteks di antara PL/SQL dan SQL engine dapat membahayakan performa. Hal ini dapat terjadi ketika loop mengeksekusi perintah SQL yang terpisah untuk setiap elemen dari collection, menentukan elemen collection sebagai bind variable. Sebagai contoh, perintah DELETE berikut ini dikirimkan ke SQL engine dengan setiap iterasi dari perulangan FOR:
DECLARE
TYPE NumList IS VARRAY(20) OF NUMBER;
depts NumList := NumList(10, 30, 70); -- department numbers
BEGIN
...
FOR i IN depts.FIRST..depts.LAST LOOP
DELETE FROM emp
WHERE deptno = depts(i);
END LOOP;
END;
Dalam kasus seperti ini, jika perintah SQL mempengaruhi empat atau lebih baris-baris data pada database, penggunaan bulk binds dapat dipertimbangkan untuk meningkatkan performa.
5.12.1. Bagaimana Bulk Binds Meningkatkan Performa?
Pemberian nilai kepada variable-variable PL/SQL di dalam perintah-perintah SQL disebut binding. Operasi-operasi binding PL/SQL terbagi menjadi tiga kategori:
- in-bind Ketika variable PL/SQL atau variable host disimpan di dalam database dengan perintah INSERT atau UPDATE.
- out-bind Ketika nilai database diberikan ke variable PL/SQL atau variable host dengan klausaRETURNING dari perintah-perintah INSERT, UPDATE, atau DELETE.
- define Ketika nilai database diberikan ke variable PL/SQL atau variable host dengan perintah SELECT atau FETCH.
Perintah DML dapat mentransfer seluruh elemen-elemen dari collection dalam sebuah operasi tunggal, dan proses ini disebut dengan bulk binding. Jika collection memiliki 20 elemen, bulk binding mengijinkan kita untuk melakukan sesuatu yang sama dengan 20 perintah-perintah SELECT, INSERT, UPDATE, atau DELETE dengan menggunakan operasi tunggal. Teknik ini meningkatkan performa dengan meminimalkan jumlah perpindahan konteks di antara PL/SQL engine dan SQL engine. Dengan bulk binds, seluruh collection, tidak hanya elemen-elemen individual, dilewatkan kembali dan seterusnya.
Untuk melakukan bulk binds dengan perintah-perintah INSERT, UPDATE, dan DELETE, kita mengapit perintah SQL di dalam perintah FORALL PL/SQL.
Untuk melakukan bulk binds dengan perintah-perintah SELECT, kita menyertakan klausa BULK COLLECT di dalam perintah SELECT dibandingkan menggunakan INTO.
Contoh: Melakukan Bulk Bind dengan DELETE
Perintah DELETE berikut ini dikirimkan ke SQL engine hanya sekali, walaupun ia melakukan tiga operasi DELETE:
DECLARE
TYPE NumList IS VARRAY(20) OF NUMBER;
depts NumList := NumList(10, 30, 70); -- department numbers
BEGIN
FORALL i IN depts.FIRST..depts.LAST
DELETE FROM emp
WHERE deptno = depts(i);
END;
Contoh: Melakukan Bulk Bind dengan INSERT
Dalam contoh di bawah ini, 5000 part number dan nama part di-load ke index-by tables. Seluruh elemen table ditambahkan ke database table sebanyak dua kali: pertama menggunakan perulangan FOR, lalu menggunakan perintah FORALL. Versi FORALL lebih cepat.
SQL> SET SERVEROUTPUT ON
SQL> CREATE TABLE parts(pnum NUMBER(4), pname CHAR(15)); Table created.
SQL> GET test.sql
1 DECLARE
2 TYPE NumTab IS TABLE OF NUMBER(4) INDEX BY BINARY_INTEGER;
3 TYPE NameTab IS TABLE OF CHAR(15) INDEX BY BINARY_INTEGER;
4 pnums NumTab;
5 pnames NameTab;
6 t1 NUMBER(5);
7 t2 NUMBER(5);
8 t3 NUMBER(5);
9
10
11 BEGIN
12 FOR j IN 1..5000 LOOP -- load index-by tables
13 pnums(j) := j;
14 pnames(j) := 'Part No. '|| TO_CHAR(j);
15 END LOOP;
16 t1 := dbms_utility.get_time;
17 FOR i IN 1..5000 LOOP -- menggunakan perulangan FOR
18 INSERT INTO parts VALUES(pnums(i), pnames(i));
19 END LOOP;
20 t2 := dbms_utility.get_time;
21 FORALL i IN 1..5000 -- menggunakan perintah FORALL
22 INSERT INTO parts VALUES(pnums(i), pnames(i));
23 get_time(t3);
24 dbms_output.put_line('Execution Time (secs)');
25 dbms_output.put_line('---------------------');
26 dbms_output.put_line('FOR loop: ' || TO_CHAR(t2 - t1));
27 dbms_output.put_line('FORALL: ' || TO_CHAR(t3 - t2));
28* END;
SQL> /
Execution Time (secs) --------------------- FOR loop: 32 FORALL: 3
PL/SQL procedure successfully completed.
 |
Buku ini membahas berbagai macam tip dan trik yang sangat berguna bagi Anda pembaca pengguna database Oracle. Pemula, mahasiswa, programmer, maupun database administrator yang mengharapkan solusi cepat dalam menangani permasalahan-permasalahan dalam penggunaan database Oracle dapat membaca buku ini. Anda tidak harus membaca buku ini secara urut karena buku ini di susun berdasarkan topik permasalahan, dan Anda dapat membaca topik-topik yang sesuai dengan permasalahan yang Anda hadapi. Dapat di beli di toko buku Gramedia, Gunung Agung, Uranus, Karisma dsb. Harga Rp. 24.800,- (157 halaman) |
[…] Silahkan melanjutkan membaca pembahasan Collections dan Records PL/SQL […]