Mengenal Oracle PL/SQL (1): Contoh Kasus

PL/SQL (Procedural Language/Structured Query Language) merupakan pengembagan SQL oleh Oracle. Prasyarat mempelajari PL/SQL adalah paling tidak mengetahui dasar-dasar SQL. Sebagai awalan belajar PLSQL, mari kita lihat contoh kasus dan contoh blok PL/SQL berikut ini.
Misalkan saya punya tabel MYTAB. Tabel ini berisi data transaksi. Kolom rcg_id (menjadi PRIMARY KEY) berisi transaksi ID yang digenerate oleh SEQUENCE, jadi nilainya urut (karena digenerate oleh sequence) dan unique (karena primary key).
Saya ingin menghapus data transaksi di bawah tanggal 17-JUN-08. Kita bisa saja men-delete dengan perintah SQL berikut:
delete from MYTAB where
SYS_CREATION_DATE < to_date('17-JUN-08','DD-MON-YY');
Karena datanya sangat banyak, maka akan diperlukan undo (rollback) segment yang besar karena COMMIT dilakukan setelah proses delete selesai. Saya tidak ingin ada konsumsi rollback segment yang besar, karena akan mempengaruhi performa database dan tentu saja perlu UNDO space yang besar. Sebagai alternatif lainnya, saya ingin mendelete (dan commit) data secara per record. Nah, sekarang saatnya saya memakai PL/SQL.
Saya akan mendelete per record (baris). Acuan yang saya gunakan adalah kolom rcg_id, karena nilainya unique (primary key) dan urut (digenerate oleh sequence). Ini langkah-langkahnya

1. Saya perlu mendapat rcg_id minimal dan maximal untuk data transaksi di bawah tanggal 17-JUN-08
   SQL> select min (rcg_id),max(rcg_ID) from MYTAB where
SYS_CREATION_DATE < to_date('17-JUN-08','DD-MON-YY');
2. Setelah mendapat rcg_id minimal dan maximal, selanjutnya saya akan buat PROSES-nya. Proses delete dimulai dari rcg_id minimal, kemudian rcg_id minimal + 1, kemudian rcg_id minimal + 2, dan seterusnya hingga mencapai rcg_id maximal
3. Selanjutnya saya akan buat program PL/SQL nya

Berikut ini block PL/SQL yang telah saya buat
DECLARE
V_rcg_min NUMBER;
v_rcg_max number;
V_iterasi NUMBER;
BEGIN
V_rcg_min:= &1;
v_rcg_max:= &2;
V_iterasi:=V_rcg_min;
WHILE V_iterasi <= v_rcg_max LOOP
delete from MYTAB where rcg_id=V_iterasi;
commit;
V_iterasi:=V_iterasi+1;
END LOOP;
dbms_output.put_line('Deleting sucess');
dbms_output.put_line('Min RCG_ID '||V_rcg_min);
dbms_output.put_line('Max RCG_ID '||V_rcg_max);
EXCEPTION
WHEN OTHERS THEN dbms_output.put_line('error here');
END;
/

Penjelasan
Block PL/SQL di atas bisa kita jalankan langsung di SQLPlus. Bisa juga kita taruh di file dan kemudian dari SQLPlus kita panggil file tersebut. Contoh, block PL/SQL ini saya taruh di file roh.sql. Berikut ini cara manggil dari SQLPlus: (Ups, jangan lupa untuk menjalankan perintah “set serveroutput on” agar hasil dari “dbms_output.put_line” bisa tampak di monitor)
SQL> set serveroutput on
SQL> @roh.sql
Begitu script roh.sql kita jalankan, maka kita akan diminta memasukkan nilai untuk parameter &1 dan &2. Seperti ini tampilannya
SQL> @roh.sql
Enter value for 1: 305206565
old 6: V_rcg_min:= &1;
new 6: V_rcg_min:= 305206565 ;
Enter value for 2: 305209524
old 7: v_rcg_max:= &2;
new 7: v_rcg_max:= 305209524;

Anda bisa juga langsung menyertakan nilai &1 (305206565) dan &2 (305209524) ketika memanggil roh.sql
SQL> @roh.sql 305206565 305209524
Hasilnya akan nampak di monitor seperti berikut ini
==========================
Deleting sucess
Min RCG_ID 305206565
Max RCG_ID 305209524
PL/SQL procedure successfully completed.
Bila kita tidak menjalankan “set serveroutput on” sebelumnya, maka yang nampak di monitor hanya
PL/SQL procedure successfully completed.
Bila tidak ingin muncul pesan “PL/SQL procedure successfully completed”, jalankan command “set feed off” di SQLPlus
SQL> set serveroutput on