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

Bersambung ke Mengenal Oracle PL/SQL (2): Struktur

Referensi
Oracle® Database PL/SQL User’s Guide and Reference 10g Release 2 (10.2)

This entry was posted in SQL and PL/SQL and tagged , , . Bookmark the permalink.

6 Responses to Mengenal Oracle PL/SQL (1): Contoh Kasus

  1. Pingback: STRUKTUR PEMROGRAMAN PL/SQL « INSYA ALLAH BERMANFAAT

  2. shandy says:

    haaii…

    mf ganggu ney,,
    aku ingin belajar sql,, tp ga tau musti mulai dari mana??
    sql aja ga tau fungsinya wat apa..
    jadi masih awam bangettzz

    mohon bantuannya donk,, aku harus mulai dari mana neh??
    mungkin dr perkenalan sql itu sendiri,,

    mohon bantuannya,, thankz..
    email ku : shandy.info@yahoo.co.id

  3. ihsan says:

    keren abis gan…
    mohon dilanjutkan …

  4. phipeace says:

    Lebih manjur menggunakan looping pada pemograman aplikasinya atau loopong pada database ?
    kan sama” bisa melakukan hal yang diatas

  5. tania says:

    Saya lagi binggung nih???
    Bisa tolong jelaskan, apa pengertian dari sorting data, single row function, dan caracter function??
    Trus kalau boleh juga saya minta berikan contoh dari masing-masing sintax nya…..

    Tugas kuliah yang memusingkan, saya nggak punya basic buat pemrograman, mohon bantuannya…

  6. makasih2,,, akhirnya saya nemuin solusi dari masalah kmaren 😉

Leave a Reply

Your email address will not be published. Required fields are marked *