Membuat Procedure, Function dan Trigger di Oracle

Store Procedure (SP) dan Function adalah suatu blok program yang dapat dipanggil berulang-ulang. Dari sisi programmer, adanya store procedure akan mempersingkat pemrograman karena programmer cukup memanggil SP/Function disertai parameternya tanpa perlu mengetikan syntax SQL yang panjang. Apalagi jika syntax tsb sering dibutuhkan (dipanggil) maka akan sering diketik ulang oleh programmer. Cukup merepotkan bukan? Maka diciptakanlah SP dan Function. 

Gambar: Perbedaan store procedure & function di Oracle

  

Procedure, function & triger memiliki syntax yang sama, yaitu PL/SQL dan bisa mempunyai beberapa parameter input. Perbedaan antara procedure dan function adalah procedure adalah program yang setelah dijalankan tidak menghasilkan return value, sedangkan function ada. Anda bisa melihat pada contoh di bagian bawah artikel ini. Baik procedure dan juga function dapat di enkripsi untuk menghindari regular user mengetahui kodenya.

Store Procedure

Gambar: Membuat store procedure

Untuk membuat store procedure digunakan perintah :

1. CREATE OR REPLACE PROCEDURE (baris ke-1) artinya prosedur akan dibuat, bila sudah ada (exist) maka akan diganti dengan yang baru (replace/overwrite). Nama store procedure pada contoh adalah add_emp (tambah data pegawai) dengan parameter masukan/input berupa informasi data pegawai.
2. Baris ke-2 berisi parameter-parameter input yaitu nama_param1 tipe_data, nama_param2 tipe_data. Pada contoh di atas adalah (first VARCHAR2, last VARCHAR2) dan seterusnya 
3. Baris ke 5 dan 6 berisi deklarasi variabel lokal untuk menyimpan data-data perhitungan dalam sementara.
4. Baris ke-8 sd 15 adalah procedure yang berisi syntax PL/SQL.

Untuk melihat syntax pembuatan procedure yang sudah dibuat, gunakan perintah desc nama_store_procedure. Kolom argumen adalah parameter-parameter dari procedure baik sebagai input dan output.

Untuk menjalankankan store procedure, gunakan perintah EXEC nama_sp(parameter1, parameter2, dst). Setelah store procedure sukses dijalankan, kita dapat melihat bahwa isi tabel employees bertambah.

Function

Gambar: Membuat Function 

Untuk membuat function digunakan perintah :

1. CREATE OR REPLACE FUNCTION (baris ke-1) artinya function akan dibuat, bila sudah ada (exist) maka akan diganti dengan yang baru (replace/overwrite). Nama function pada contoh adalah sum_sals (fungsi untuk menghitung pengeluaran untuk gaji karyawan dari suatu departemen/bagian) dengan parameter masukan/input berupa informasi kode departemen.
2. Baris ke-1 berisi parameter-parameter input yaitu nama_parameter tipe_data. Pada contoh di atas adalah (id employees.departement_id%TYPE). Pada PL/SQL anda bisa juga tidak menyebutkan tipe data secara eksplisit namun digantikan dengan tipe data  field dari suatu tabel dalam  hal ini employees.departement_id%TYPE. Field departement_id pada tabel employee adalah bertipe NUMBER
3. Baris ke 2 berisi return value atau nilai kembalian ketika function dipanggil, pada contoh NUMBER.
4. Baris ke-3 adalah variable sementara yaitu total bertipe NUMBER untuk menghitung total pengeluaran gaji untuk suatu departemen.
5. Baris ke-5 sd 11 adalah syntax PL/SQL untuk menjalankan fungsi tersebut.

Untuk menjalankan Function, tidak menggunakan EXEC, namun perintah SELECT nama_function(param1, param2, dst) FROM dual/nama_tabel. Pada contoh di atas dijalankan perintah sum_sals(50) yaitu menghitung total pengeluaran gaji untuk departemen dengan kode=50, Hasilnya adalah $ 160.432,-

Trigger

Gambar: Syntax Trigger

Trigger adalah bentuk statement PL/SQL pasif, artinya statement tsb akan dieksekusi otomatis ketika terjadi event/kejadian. Contoh sederhananya, memakai jas hujan adalah sebuah kegiatan Trigger. Kita akan memakai jas hujan kalau ada kejadian/event hujan. 

Pelaksanaan Trigger:

1. Trigger bisa eksekusi otomatis sebelum kejadian/event terjadi (BEFORE) atau setelah kejadian (AFTER).
   
2. Trigger disulut karena event DML seperti ada event/kejadian DELETE/INSERT/UPDATE. Selain itu sebenarnya ada Trigger lain selain DML yang akan dibahas pada bagian ke-2
3. Trigger bisa terjadi jika Event mengenai suatu baris data [FOR EACH ROW] atau terjadi pada kolom tertentu saja.
4. Trigger akan menjalankan suatu statement/perintah pada blok BODY (lihat gambar atas).

Gambar: Penerapan Trigger untuk logging/perekaman jejak.

Gambar di atas merupakan contoh implementasi Trigger untuk aktifitas logging (perekaman jejak otomatis). Pada contoh di atas, awalnya penulis membuat tabel sal_hist yang mencatat aktifitas perubahan gaji. Kemudian penulis membuat statemen trigger (CREATE OR REPLACE TRIGGER sal_trig) dengan penjelasan sbb:

1. Trigger akan dijalankan setelah ada event/kejadian (AFTER) user melakukan perubahan data (UPDATE) pada tabel employees (lihat baris ke-2).
2. Event akan di fire/sulut jika ada perubahan pada suatu baris, tidak peduli kolom mana yang berubah.
3. Baris ke-5-8 adalah bagian body yang menjelaskan bagaimana trigger bereaksi. Ketika ada perubahan pada tabel employees (AFTER UPDATE), maka perubahan tersebut langsung tercatat di tabel sal_hist (lihat baris ke-6) yang mencatat informasi ID pegawai, kapan dilakukan perubahan (TIMESTAMP), siapa user yang mengubah (WHO), berapa gaji lama (OLD_SAL) dan berapa gaji terbaru (NEW_SAL).

Selanjutnya tunggu bagian ke-2 yang akan menjelaskan Store Procedure, Function dan Trigger secara lebih spesifik (kalo penulis sedang menganggur). Terima kasih

Copyright(c) Ahmad Juniar 2012