FUNGSI , PL/PGSQL DAN TRIGGER
A.
Landasan
Teori
Setiap database mempunyai
fasilitas yang memungkinkan aplikasi-aplikasi untuk menyimpan dan memanipulasi
data. Selain itu, database juga memberikan fasilitas lain yang lebih
spesifik yang diapakai untuk menjamin konsistensi hubungan antar tabel dan
integritas data di dalam database. Referential integrity
merupakan sebuah mekanisme untuk mencegah putusnya hubungan master/detail. Jika
user mencoba menghapus sebuah field pada tabel master sehingga record
di tabel detail menjadi yatim (tidak mempunyai induk), referential integrity
akan mencegahnya.
Trigger, Stored
Procedure/Function, dan View merupakan komponen dan fitur database,
yang dengan keunikan fungsi masing-masing dapat dimanfaatkan untuk menjaga,
mengelola dan membantu kinerja database engineer dalam upaya terjaminnya
integritas sebuah database.
1.
Fungsi
Secara default, routine (procedure/function) diasosiasikan dengan
database yang sednag aktif. Untuk dapat mengasosiasikan routine secara
eksplisit dengan database yang lain, buat routine dengan format :db_name.sp_name.
Fungsi SQL adalah
sebuah sekumpulan query, biasanya query yang detail dan panjang yang djadikan
satu dan disimpan dalam database dan kemudian apabila diperlukan hanya tinggal
mengaksesnya tanpa mengetikkan query detail. Sedangkan untuk memunculkan dapat
menggunakan query "\df".
Ada beberapa konsep dari fungsi :
o
Bahasa yang
dipakai dapat didefinisikan sendiri dengan tersedianya parameter LANGUAGE,
tanpa harus mengkompilasi ulang PostgreSQL.
o
Dapat
membuat dua buah fungsi dengan nama yang sama namun parametermasukkannya yang
berbeda, baik tipe data maupun jumlahnya.
Ada beberapa faktor yang perlu diperhatikan dalam membuat fungsi :
o
Nama
fungsi
o
Nomor
dari fungsi argument
o
Tipe
data dari hasil setiap argument
o
Tipe
dari hasil fungsi
o
Fungsi
action
o
Bahasa
yang digunakan oleh fungsi action
Penggunaan fungsi :
db_personal=>
CREATE FUNCTION perkalian (FLOAT, FLOAT, FLOAT)
db_personal->
RETURNS FLOAT
db_personal->
AS 'SELECT ($1 + $2) * $3;'
db_personal->
LANGUAGE 'sql';
CREATE
2.
Fungsi
PL/PGSQL
Merupakan bahasa lain untuk membuat fungsi, biasanya digunakan
untuk menangani masalah yang lebih kompleks.
Keuntungan penggunaan fungsi PL/PGSQL :
1)
Meningkatkan
kinerja karena mengurangi pengiriman kode dari klien ke server.
2)
Meningkatkan
keamanan karena pengaksesan data tertentu ditangani dalam serever.
3)
Meningkatkan
konsistensi data saat sejumlah aplikasi memanggil prosedur yang sama.
Berikut contoh pembuatan fungsi dalam pl/pgsql:
--pembuatan fungsi :
CREATE [OR REPLACE] FUNCTION nama_fungsi ( argtype , ... ])
RETURNS tipe_data
AS ‘definisi’
LANGUAGE ‘plpgsql’;
-- pembuatan definisi :
DECLARE nama_variable
tipe_data /* deklarasi variabel, type */
BEGIN
/* prosedural dan SQL masuk
disini seperti select, update dan sebagainya*/
Return nama_variable /* blok
ini yang wajib */
END;
Menghapus fungsi :
DROP FUNCTION nama_fungsi(paramater, parameter, parameter ... );
Contoh :
DROP FUNCTION pembagian(text);
Implementasinya dalam suatu kasus:
db_personal=> CREATE FUNCTION pl_caripegawai (integer)
db_personal-> RETURNS text
db_personal-> AS 'DECLARE hasil TEXT;
db_personal'> BEGIN
db_personal'> SELECT INTO hasil nama as nama_mahasiswa
db_personal'> FROM pegawai
db_personal'> WHERE id = $1;
db_personal'> RETURN hasil;
db_personal'> END;'
db_personal-> LANGUAGE 'plpgsql';
CREATE
3.
TRIGGER
Pernyataan CREATE TRIGGER digunakan untuk membuat trigger, termasuk
aksi apa yang dilakukan saat trigger diaktifkan. Trigger berisi program yang
dihubungkan dengan suatu tabel atau view yang secara otomatis melakukan suatu
aksi ketika suatu baris di dalam tabel atau view dikenai operasi INSERT, UPDATE
atau DELETE.
Sintak :
CREATE
[DEFINER = { user
| CURRENT USER}]
TRIGGER trigger_name
trigger_time trigger_event
ON tbl_name FOR EACH ROW trigger_stmt
Keterangan :
§ [DEFINER = { user | CURRENT_USER }] : Definisi user yang sedang aktif, sifatnya opsional
§ trigger_name : Nama trigger
§ trigger_time : waktu menjalankan trigger. Ini dapat berupa BEFORE atau
AFTER
ü BEFORE : Membuat trigger diaktifkan sebelum dihubungkan
dngan suatu operasi.
ü AFTER : Membuat trigger diaktifkan setelah dihubungkan
dengan suatu operasi.
§ trigger_event : berupa kejadian yang akan dijalankan trigger
§ trigger_event : dapat berupa salah satu dari berikut :
ü INSERT : trigger diaktifkan ketika sebuah record baru
disisipkan ke dalam tabel. Contoh : statemen INSERT< LOAD DATA dan REPLACE.
ü UPDATE : trigger diaktifkan ketika sebuah record dimodifikasi.
Contoh : statemen UPDATE.
ü DELETE : trigger diaktifkan ketika sebuah record
dihapus. Contoh : statemen DELETE dan REPLACE.
Catatan : trigger_event tidak merepresentasikan statemen SQL
yang diaktifkan trigger sebagai suatu operasi tabel. Sebagai contoh , trigger
BEFORE untuk INSERT tetapi juga statemen LOAD DATA.
§ tbl_name : Nama tabel yang berasosiasi dengan trigger
§ trigger_stmt : Statemen (tunggal atau jamak) yang akan dijalankan ketika
trigger aktif.
Dengan dapat disimpulkan bahwa kegunaan trigger adalah menyisipkan
fungsi pada suatu record di INSERT, UPDATE dan DELETE. Trigger sangat ideal
utnutk mengecek atau memodifikasi sebuah data pada kolom sebelum dimasukkan ke
dalam database, sehingga sebuah fungsi dapat dipanggil setiap saat
secara otomatis ketika sebuah row akan dimodifikasi. Ciri khas dari fungsi yang
diperuntukkan untuk trigger adalah meghasilkan output bertipe OPAQUE.
Tipe Opaque adalah sebuah tipe yang menginformasikan pada database bahwa
fungsi tersebut tidak menghasilkan satu dari tipe data yang ditetapkan SQL dan
tidak secara langsung dapat digunakan dalam statemen SQL Language (bahasa)
PL/PGSQL dapat digunakan untuk trigger procedure, fungsi untuk trigger ini
memiliki beberapa variabel khusus yang terdeklarasi secara otomatis.
Beberapa contoh penggunaan fungsi sebagai trigger procedure
:
--pertama buat fungsinya terlebih dahulu :
db_personal=>
CREATE FUNCTION tes_trigger()
db_personal->
RETURNS opaque
db_personal->
AS 'BEGIN
db_personal'>
NEW.nama := UPPER(NEW.nama);
db_personal'>
RETURN NEW;
db_personal'>
END;'
db_personal->
LANGUAGE 'plpgsql';
CREATE
--lanjut dengan membuat trigger yang berfungsi untuk memanggil
fungsi secara otomatis ketika melakukan INSERT ataupun UPDATE pada tabel
anggota
db_personal=> CREATE TRIGGER tes1_trigger
db_personal-> BEFORE INSERT
db_personal-> ON anggota
db_personal-> FOR EACH ROW
db_personal-> EXECUTE PROCEDURE tes_trigger();
CREATE
--cobalah insert beberapa data ke dalam tabel anggota :
db_personal=> INSERT INTO anggota (id, nama)
db_personal-> VALUES (26, 'andhie');
INSERT 70831 1
db_personal=> INSERT INTO anggota
db_personal-> VALUES (83, 'rWatia');
INSERT 70832 1
B.
Tujuan
1)
Mahasiswa
memahami dan menggunakan fungsi dalam database PostgreSQL
2)
Mahasiswa
memahami dasar penggunaan PL/PGSQL dan TRIGGER.
C.
Hasil
Praktikum
I.
Versi
PostgreSQL
o
Buatlah
fungsi konversi suhu dari Fahrenheit dari derajat ke celcius dengan rumus
konversi sebagai berikut : celcius = (5*(nilai Fahrenheit-32))/9 !
Kemudian jalankan hasilnya dengan menSELECT fungsi tersebut !
o
Buatlah
fungsi untuk mencari alamat mahasiswa dari tabel mahasiswa berdasarkan nama
mahasiswa.
Kemudian jalankan dengan perintah SELECT
o
Buatlah
fungsi untuk menghitung nilai dengan menggunakan bahasa pl/sql !
|
Nilai >100 atau Nilai <0
|
Nilai Salah
|
|
Nilai >= 90
|
Nilai A
|
|
70 <= Nilai <90
|
Nilai B
|
|
60<= Nilai <70
|
Nilai C
|
|
50 <= Nilai <60
|
Nilai D
|
|
0<= Nilai <50
|
Nilai E
|
Kemudian jalankan hasilnya dengan menSELECT fungsi tersebut !
o
Buatlah
fungsi menggunakan pl/pgsql untuk mencari bilangan ganjil atau genap dari
bilangan yang diinputkan.
Kemudian jalankan dengan perintah SELECT !
o
Tambahkan
kolom modifikasi pada tabel mahasiswa
Dimana setiap ada insert atau update maka tanggal pada kolom
modifikasi akan menunjukkan tanggal perubahan tersebut dilakukan
Buat fungsinya terlebih dahulu
Kemudian buat trigger
Cek apakah trigger sukses dengan memasukkan bebrapa data pada tabel
mahasiswa
D.
Tugas
Rumah
II.
Versi
MySQL
Dengan menambahkan fungsi harus dengan delimiter terlebih dahulu
Mengembalikan ke default
o
Buatlah
fungsi konversi suhu dari Fahrenheit dari derajat ke celcius dengan rumus
konversi sebagai berikut : celcius = (5*(nilai Fahrenheit-32))/9 !
Kemudian jalankan hasilnya dengan menSELECT fungsi tersebut !
o
Buatlah
fungsi untuk mencari alamat mahasiswa dari tabel mahasiswa berdasarkan nama
mahasiswa.
Kemudian jalankan dengan perintah SELECT
o
Buatlah
fungsi untuk menghitung nilai dengan menggunakan bahasa pl/sql !
|
Nilai >100 atau Nilai <0
|
Nilai Salah
|
|
Nilai >= 90
|
Nilai A
|
|
70 <= Nilai <90
|
Nilai B
|
|
60<= Nilai <70
|
Nilai C
|
|
50 <= Nilai <60
|
Nilai D
|
|
0<= Nilai <50
|
Nilai E
|
Kemudian jalankan hasilnya dengan menSELECT fungsi tersebut !
o
Buatlah
fungsi menggunakan pl/pgsql untuk mencari bilangan ganjil atau genap dari
bilangan yang diinputkan.
Kemudian jalankan dengan perintah SELECT !
o
Tambahkan
kolom modifikasi pada tabel mahasiswa
Dimana setiap ada insert atau update maka tanggal pada kolom
modifikasi akan menunjukkan tanggal perubahan tersebut dilakukan
Kemudian buat trigger
Cek apakah trigger sukses dengan memasukkan bebrapa data pada tabel
mahasiswa
E.
Evalusi
Perbandingan PostgreSQL dan MySQL Shell
Berdasarkan
query-querynya :
|
Instruksi
|
PostgreSQL
|
MySQL
|
|
Fungsi
|
Create function
<nama_fungsi>
returns <tipe data>
as ' select <nama_kolom>
from <nama_tabel> ;'
where <kondisi>
language 'sql';
|
Create function
<nama_fungsi>
returns <tipe data>
as 'select <nama_kolom> from
<nama_tabel> ;'
where <kondisi>
<simbol delimiter>
|
|
PL/PGSQL
|
Create function <nama _fungsi>
(argtype,...)
Returns <tipe_data>
As 'definisi
Language 'plpgsql';
|
Create function <nama _fungsi>
(argtype,...)
Returns <tipe_data>
As 'definisi
<simbol delimiter>
|
|
Fungsi
sebelum Trigger
|
Create function <nama_fgs>
Returns opaque
As ' begin
<definisi>
Return new;
End;'
Langauge 'plpgsql';
|
-----
|
|
Membuat
Trigger
|
Create trigger <nama_ trigger>
Before insert
On <nama_tabel
For each row
Execute procedure <nama_fungsi>;
|
Create trigger <nama_ trigger>
Before insert / before update
On <nama_tabel
For each row
begin
end;
<simbol delimiter>
|
Dari perbandingan di atas dapat disimpulkan bahwa Mysql menggunakan delimiter sedangkan PotsgreSQL tidak.
F.
Kesimpulan
Fungsi Secara default, routine
(procedure/function) diasosiasikan dengan database yang sednag aktif. Untuk
dapat mengasosiasikan routine secara eksplisit dengan database yang lain, buat
routine dengan format :db_name.sp_name. Fungsi SQL adalah sebuah
sekumpulan query, biasanya query yang detail dan panjang yang djadikan satu dan
disimpan dalam database dan kemudian apabila diperlukan hanya tinggal
mengaksesnya tanpa mengetikkan query detail.
PL/PGSQL Merupakan bahasa lain untuk
membuat fungsi, biasanya digunakan untuk menangani masalah yang lebih kompleks.
Pernyataan CREATE TRIGGER digunakan
untuk membuat trigger, termasuk aksi apa yang dilakukan saat trigger
diaktifkan. Trigger berisi program yang dihubungkan dengan suatu tabel atau
view yang secara otomatis melakukan suatu aksi ketika suatu baris di dalam
tabel atau view dikenai operasi INSERT, UPDATE atau DELETE.
Saran
Pada praktikum
ke delapan ini sangat menguras pikiran karena query di MySQL banyak sekali
perbedaan dengan PostGreSQL
G.
Daftar
Pustaka
o
http://www.rey1024.com/2008/05/pengenalan-trigger-function-procedure-dan-view-pada-mysql/
o
http://pusat-bahasa.info/relo/download/119542039mohriyan-MySQL2.pdf
o
Modul
praktikum 8
o Raharjo,Budi.Membuat
Database menggunakan MYSQL.Informatika : Bandung.2011
o
Sanjaya, Ridwan. Pengolahan
Database MySQL dengan Java 2. Yogyakarta: Andi. 2005.
Tidak ada komentar:
Posting Komentar