Collections dan Records PL/SQL
5.10. Menggunakan Method-method CollectionMethod-method collection berikut ini membantu mengeneralisasi kode program, membuat collections lebih mudah untuk digunakan, dan membuat aplikasi-aplikasi kita lebih mudah untuk dipelihara:
EXISTS
COUNT
LIMIT
FIRST and LAST
PRIOR and NEXT
EXTEND
TRIM
DELETE
Method collection merupakan function dan procedure built-in (disediakan oleh Oracle) yang beroperasi terhadap collections dan dipanggil dengan menggunakan notasi titik. Sintaksnya adalah:
collection_name.method_name[(parameters)]
Method-method collection tidak dapat dipanggil dari perintah-perintah SQL. Juga, EXTEND dan TRIM tidak dapat digunakan bersama associative arrays. EXISTS, COUNT, LIMIT, FIRST, LAST, PRIOR, dan NEXT adalah function; EXTEND, TRIM, dan DELETE adalah procedure. EXISTS, PRIOR, NEXT, TRIM, EXTEND, dan DELETE mengambil parameter-parameter yang berhubungan dengan subscript-subscript collection, dimana biasanya integer namun dapat juga berupa string untuk associative arrays.
Hanya EXISTS yang dapat diterapkan secara atomik terhadap collections yang null. Juga kita menerapkan method lain terhadap collection tersebut, PL/SQL menampilkan COLLECTION_IS_NULL.
5.10.1. Memeriksa Apakah Elemen Collection Ada (Method EXISTS)
EXISTS(n) menghasilkan TRUE jika elemen ke n di dalam collection ada. Sebaliknya, EXISTS(n) menghasilkan FALSE. Sebagian besar, kita menggunakan EXISTS dengan DELETE untuk memelihara nested tables yang tipis atau jarang. Kita dapat juga menggunakan EXISTS untuk mengabaikan munculnya exception ketika kita mereferensi elemen yang tidak ada. Dalam contoh berikut ini, PL/SQL mengeksekusi perintah pemberian nilai (assignment statement) hanya jika elemen i ada:
IF courses.EXISTS(i) THEN courses(i) := new_course; END IF;
Ketika melewatkan subscript yang out-of-range, EXIST menghasilkan FALSE dibanding memunculkan exception SUBSCRIPT_OUTSIDE_LIMIT.
5.10.2. Menghitung Elemen di dalam Collection (Method COUNT)
COUNT menghasilkan jumlah elemen yang terkandung dalam collection. Sebagai contoh, jika varray projects mengandung 25 elemen, kondisi IF berikut ini bernilai TRUE:
IF projects.COUNT = 25 THEN ...
COUNT berguna karena ukuran terkini dari collection tidak selalu dapat diketahui. Sebagai contoh, jika kita meletakkan (fetch) kolom dari Oracle data ke dalam nested table, berapa banyak elemen yang dikandung oleh table tersebut? COUNT memberikan kita jawabannya.
Kita dapat menggunakan COUNT dimanapun ekspresi integer diperbolehkan. Dalam contoh selanjutnya, kita menggunakan COUNT untuk menentukan batas atas dari jangkauan perulangan:
FOR i IN 1..courses.COUNT LOOP ...
Untuk varrays, COUNT selalu sama dengan LAST. Untuk nested tables, COUNT secara normal sama dengan LAST. Namun, jika kita menghapus elemen-elemen dari tengah nested table, COUNT akan menjadi lebih kecil daripada LAST.
Ketika menghitung elemen, COUNT mengabaikan elemen-elemen yang telah terhapus.
5.10.3. Memeriksa Ukuran Maksimum dari Collection (Method LIMIT)
Untuk nested tables dan associative arrays, dimana kita tidak memiliki ukuran maksimum, LIMIT menghasilkan NULL. Untuk varrays, LIMIT menghasilkan jumlah maksimum elemen yang dapat dimiliki oleh varray (dimana kita harus menentukannya di dalam definisi type, dan dapat mengubahnya kemudian dengan method TRIM dan EXTEND). Sebagai contoh, jika ukuran maksimum dari varray PROJECTS adalah 25 elemen, kondisi IF berikut ini TRUE:
IF projects.LIMIT = 25 THEN ...
Kita dapat menggunakan LIMIT dimanapun ekspresi integer diperbolehkan. Dalam contoh berikut ini, kita menggunakan LIST untuk menentukan apakah kita dapat menambahkan 15 elemen ke varray projects:
IF (projects.COUNT + 15) < projects.LIMIT THEN ...
5.10.4.Mencari Elemen Collection Pertama atau Terakhir (Method FIRST dan LAST)
FIRST dan LAST menghasilkan nomor index pertama dan terakhir (terkecil dan terbesar) di dalam collection. Untuk associative array dengan nilai kunci VARCHAR2, nilai kunci terendah dan tertinggi dihasilkan; urutannya berdasarkan pada nilai-nilai biner dari karakter di dalam string, kecuali parameter NLS_COMP di atur ke ANSI, dimana pengurutan didasarkan pada pengurutan locale-specific yang ditentukan oleh parameter NLS_SORT.
Jika collection kosong, FIRST dan LAST menghasilkan NULL.
Jika collection hanya terdiri dari satu elemen, FIRST dan LAST menghasilkan nilai index yang sama:
IF courses.FIRST = courses.LAST THEN ... -- hanya satu elemen
Contoh berikutnya menunjukkan bahwa kita dapat menggunakan FIRST dan LAST untuk menentukan bahwa batas bawah dan atas dari jangkauan yang disediakan bagi setiap elemen di dalam jangkauan tersebut ada:
FOR i IN courses.FIRST..courses.LAST LOOP ...
Pada kenyataannya, kita dapat menggunakan FIRST atau LAST dimanapun ekspresi integer diperbolehkan. Dalam contoh berikut ini, kita menggunakan FIRST untuk menginisialisasi loop counter:
i := courses.FIRST; WHILE i IS NOT NULL LOOP ...
Untuk varrays, FIRST selalu menghasilkan 1 dan LAST selalu sama dengan COUNT. Untuk nested tables, FIRST normalnya menghasilkan 1. Namun, jika kita menghapus elemen-elemen dari awal nested table, FIRST menghasilkan angka lebih besar daripada 1. Juga untuk nested tables, LAST normalnya sama dengan COUNT. Namun, jika kita menghapus elemen-elemen dari tengah nested table, LAST menjadi lebih besar daripada COUNT.
Ketika membaca elemen-elemen, FIRST dan LAST mengabaikan elemen-elemen yang telah terhapus.
5.10.5. Perulangan Melewati Elemen-elemen Collection (Method PRIOR dan NEXT)
PRIOR(n) menghasilkan nomor index yang mendahului index n di dalam collection. NEXT(n) menghasilkan nomor index yang mengikuti index n. Jika n tidak memiliki pendahulu, PRIOR(n) menghasilkan NULL. Demikian juga, jika n tidak memiliki pengikut, NEXT(n) menghasilkan NULL.
Untuk associative arrays dengan kunci VARCHAR2, method-method ini menghasilkan nilai kunci yang sesuai; urutannya didasarkan pada nilai-nilai biner dari karakter-karakter di dalam string, kecuali parameter NLS_COMP di set ke ANSI, di mana dalam kasus ini urutan didasarkan pada order pengurutan local-specific yang ditentukan oleh parameter NLS_SORT.
Method-method ini lebih dapat diandalkan daripada perulangan melalui kumpulan tetap dari nilai-nilai subscript, karena elemen-elemen dapat ditambahkan dan dihapus dari collection selama perulangan. Hal ini benar khususnya untuk associative arrays, dimana subscript-subscript tidak boleh berada di dalam order yang bertalian dan sehingga rangkaian subscript-subscript mungkin (1,2,4,8,16) atau (‘A’,'E’,'I’,'O’,'U’).
PRIOR dan NEXT tidak mengikat dari satu akhir dari collection kepada lainnya. Sebagai contoh, perintah berikut ini memberikan NULL ke n karena elemen pertama di dalam collection tidak memiliki pendahulu:
n := courses.PRIOR(courses.FIRST); -- memberikan NULL kepada n
PRIOR adalah kebalikan dari NEXT untuk melintasi collection-collection yang diindex oleh suatu rangkaian subscript-subscript. Di dalam contoh berikut ini, kita menggunakan NEXT untuk melintasi nested table dimana beberapa elemen-elemen telah dihapus:
i := courses.FIRST; -- mendapatkan subscript dari elemen pertama WHILE i IS NOT NULL LOOP -- melakukan sesuatu dengan courses(i) i := courses.NEXT(i); -- mendapatkan subscript dari elemen berikutnya END LOOP;
Ketika melintasi elemen-elemen, PRIOR dan NEXT mengabaikan elemen-elemen yang telah terhapus.
5.10.6. Memperbesar Ukuran Collection (Method EXTEND)
Untuk memperbesar ukuran nested table atau varray, kita menggunakan EXTEND. Kita tidak dapat menggunakan EXTEND dengan index-by tables.
Procedure ini memiliki tiga bentuk:
- EXTEND menambahkan sebuah elemen null kepada collection.
- EXTEND(n) menambahkan n elemen null kepada collection.
- EXTEND(n,i) menambahkan n kopi dari elemen ke i kepada collection.
Sebagai contoh, perintah berikut ini menambahkan 5 kopi dari elemen 1 terhadap nested table courses:
courses.EXTEND(5,1);
Kita tidak dapat menggunakan EXTEND untuk menginisialisasi collection null secara atomik. Juga, jika kita memaksakan constraint NOT NULL pada tipe TABLE atau VARRAY, kita tidak dapat menerapkan dua bentuk pertama dari EXTEND untuk collection-collection dari tipe tersebut.
EXTEND beroperasi pada ukuran internal dari collection, yang termasuk beberapa elemen-elemen yang telah dihapus. Jadi, jika EXTEND menemui elemen-elemen yang telah dihapus, ia mengikutsertakannya di dalam hitungannya. PL/SQL memelihara tempat untuk elemen-elemen yang telah dihapus sehingga kita dapat menggantinya jika dikehendaki. Marilah kita perhatikan contoh berikut ini:
DECLARE
TYPE CourseList IS TABLE OF VARCHAR2(10);
courses CourseList;
BEGIN
courses:= CourseList('Biol 4412', 'Psyc 3112', 'Anth 3001');
courses.DELETE(3);-- menghapus elemen 3
/* PL/SQL menyediakan tempat untuk elemen 3. Sehingga, perintah berikutnya menambahkan elemen 4, bukan elemen 3. */
courses.EXTEND; -- menambahkan satu elemen null
/* Sekarang elemen 4 telah ada, sehingga perintah berikutnya tidak menyebabkan SUBSCRIPT_BEYOND_COUNT. */
courses(4):= 'Engl 2005';
Ketika ia mengandung elemen-elemen yang telah terhapus, ukuran internal dari nested table berbeda dengan nilai-nilai yang dihasilkan oleh COUNT dan LAST. Sebagai contoh, jika kita menginisialisasi nested table dengan lima elemen, lalu menghapus elemen 2 dan 5, ukuran internalnya adalah 5, COUNT menghasilkan 3, dan LAST menghasilkan 4. Seluruh elemen-elemen yang telah terhapus (baik di depan, tengah, atau belakang) diperlakukan sama.
5.10.7. Memperkecil Ukuran Collection (Method TRIM)
Procedure ini memiliki dua bentuk:
- TRIM menghapus elemen dari posisi akhir collection
- TRIM(n) menghapus n elemen dari posisi akhir collection
Sebagai contoh, perintah ini menghapus tiga elemen terakhir dari nested table courses:
courses.TRIM(3);
Jika n terlalu besar, TRIM(n) memunculkan SUBSCRIPT_BEYOND_COUNT.TRIM beroperasi pada ukuran internal collection. Jadi, jika TRIM berjumpa dengan elemen yang telah dihapus, ia menyertakannya dalam rangkaiannya. Perhatikan contoh berikut ini:
DECLARE
TYPE CourseList IS TABLE OF VARCHAR2(10);
courses CourseList;
BEGIN
courses := CourseList('Biol 4412', 'Psyc 3112', 'Anth 3001');
courses.DELETE(courses.LAST); -- menghapus elemen 3
/* Pada titik ini, COUNT sama dengan 2, jumlah elemen-elemen tetap. Sehingga, kita
mungkin berharap perintah berikutnya untuk nested table kosong dengan memotong elemen 1 dan 2.
Daripada itu, ia memotong elemen valid 2 dan menghapus elemen 3 karena TRIM menyertakan elemen-elemen
yang telah terhapus ke dalam hitungannya.
courses.TRIM(courses.COUNT); dbms_output.put_line(courses(1)); -- menampilkan 'Biol 4412'
Secara umum, tidak bergantung kepada interaksi antara TRIM dan DELETE. Adalah lebih baik untuk menganggap nested table seperti array berukuran tetap (fixed-size arrays) dan hanya menggunakan DELETE, atau menganggap mereka seperti stacks serta hanya menggunakan TRIM dan EXTEND.
PL/SQL tidak menyediakan tempat untuk elemen-elemen yang telah dipotong. Sehingga, kita tidak dapat mengganti elemen-elemen tersebut secara sederhana dengan memberikan nilai baru terhadapnya.
5.10.8. Menghapus Elemen-elemen Collection (Method DELETE)
Procedure ini memiliki berbagai bentuk:
- DELETE menghapus seluruh elemen dari collection
- DELETE (n) menghapus elemen ke-n dari associative array dengan kunci numeric atau dari nested table. Jika associative array memiliki kunci string, elemen yang berhubungan dengan nilai kunci tersebut ikut dihapus. Jika n adalah null, DELETE(n) tidak melakukan apapun
- DELETE(m,n) menghapus seluruh elemen di dalam jangkauan m..n dari associative array atau nested table. Jika m lebih besar dari n atau jika m atau n adaah null, DELETE(m,n) tidak melakukan apapun.
Sebagai contoh:
BEGIN
courses.DELETE(2); -- mengapus elemen 2
courses.DELETE(7,7); -- mengapus elemen 7
courses.DELETE(6,3); -- tidak melakukan apapun
courses.DELETE(3,6); -- mengapus elemen 3 sampai 6
projects.DELETE; -- mengapus seluruh elemen
nicknames.DELETE('Chip'); -- mengapus elemen yang ditunjuk oleh kunci ini
nicknames.DELETE('Buffy','Fluffy'); -- menghapus elemen-elemen dengan kunci
-- dalam jangkauan alphabetic ini
END;
Varray adalah padat, sehingga kita tidak dapat menghapus individual elemennya.
Jika elemen yang akan dihapus tidak ada, DELETE akan dilewati; tidak ada exception yang muncul. PL/SQL menyediakan tempat untuk elemen-elemen yang telah dihapus. Sehingga, kita dapat mengganti elemen tersebut secara sederhana dengan memberikannya nilai baru.
DELETE mengijinkan kita untuk memelihara nested table yang jarang. Dalam contoh berikut ini, kita menampilkan nested table prospects ke dalam table sementara, memangkasnya, lalu menyimpannya kembali ke dalam database:
DECLARE
my_prospects ProspectList;
revenue NUMBER;
BEGIN
SELECT prospects
INTO my_prospects
FROM customers WHERE ...
FOR i IN my_prospects.FIRST..my_prospects.LAST LOOP
estimate_revenue(my_prospects(i),revenue); -- memanggil procedure
IF revenue < 25000 THEN
my_prospects.DELETE(i);
END IF;
END LOOP;
UPDATE customers
SET prospects = my_prospects
WHERE ...
Jumlah memory yang dialokasikan untuk nested table dapat bertambah dan berkurang secara dinamis. Begitu kita menghapus elemen-elemen, memory dibebaskan halaman demi halaman. Jika kita menghapus seluruh table, seluruh memory dibebaskan.
5.10.9. Menerapkan Method-method untuk Parameter-parameter Collection
Bersama sebuah subprogram, parameter dari collection mengasumsikan properties dari argumen yang membatasi kepadanya. Sehingga, kita dapat menerapkan method-method collection built-in (FIRST, LAST, COUNT, dan seterusnya) untuk parameter-parameter seperti itu. Dalam contoh berikut ini, nested table dideklarasikan sebagai parameter formal dari procedure ter-package:
CREATE PACKAGE personnel AS TYPE Staff IS TABLE OF Employee;
...
PROCEDURE award_bonuses(members IN Staff);
END personnel;
CREATE PACKAGE BODY personnel AS
...
PROCEDURE award_bonuses(members IN Staff) IS
BEGIN
...
IF members.COUNT > 10 THEN -- menerapkan method
...
END IF;
END;
END personnel;
Catatan: Untuk paramter-parameter varray, nilai LIMIT selalu diturunkan dari definisi tipe parameter, tanpa memperhatikan mode parameter.
 |
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 […]