FUNGSI, PL/SQL dan TRIGER
1. Dasar Teori
A. Pendukung Fungsi
PostgreSQL memiliki fungsi yang dapat mengubah suatu nilai dalam suatu kolom
atau barismenjadi huruf besar. Fungsi tersebut bernama upper(nama_kolom), berfungsi memanggil fungsi upper dengan nama_kolom sebagai argumen sehingga menghasilkan nilai padakolom dalam huruf besar. Berikut Struktur SQL untuk menampilkan data dalam huruf besar semua :
SELECT upper ([nama kolom]) FROM [nama tabel];
Berikut struktur SQL untuk menampilkan data dimana huruf pertama saja yang besar :
SELECT initcap ([nama kolom]) FROM [nama tabel];
Struktur Fungsi
·
Statement
RETURN tipedata_fungsi menunjukkan
bahwa function akan mengembalikan nilai dengan tipe data tertentu.
· Statement RETURN nilai_fungsi berfungsi untuk mengembalikan nilai yang telah di proses dalam function.
Fungsi
Fungsi SQL adalah sebuah kumpulan query, biasanya query yang detail dan panjang yang dibungkus menjadi satu dan disimpan dalam database dan kemudian apabila diperlukan hanya tinggal mengaksesnya tanpa mengetikan query detail. Sedangkan untuk memunculkan fungsi dapat menggunakan query \df.
Berbagai Pengertian fungsi yaitu :
· Fungsi di PL / SQL adalah sub program yang dapat mengembalikan nilai (return).
· Fungsi terdiri dari dua bagian, yaitu spesifikasi dan body.
· Bagian spesifikan dimulai dari keyword function hingga keyword retur.
· Bagian body dimulai dari keyword IS atau AS hingga keyword AND. Hingga mencakup bagian deklarasi pada program dan exception.
Jenis-jenis fungsi :
· Function Tanpa Parameter
Dari perbandingan di atas dapat disimpulkan bahwa Mysql menggunakan delimiter sedangkan PotsgreSQL tidak.
Dari Hasil Praktikum yang sudah dilakukan, dapat diambil kesimpulan bahwa 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.
· Statement RETURN nilai_fungsi berfungsi untuk mengembalikan nilai yang telah di proses dalam function.
Fungsi
Fungsi SQL adalah sebuah kumpulan query, biasanya query yang detail dan panjang yang dibungkus menjadi satu dan disimpan dalam database dan kemudian apabila diperlukan hanya tinggal mengaksesnya tanpa mengetikan query detail. Sedangkan untuk memunculkan fungsi dapat menggunakan query \df.
Berbagai Pengertian fungsi yaitu :
· Fungsi di PL / SQL adalah sub program yang dapat mengembalikan nilai (return).
· Fungsi terdiri dari dua bagian, yaitu spesifikasi dan body.
· Bagian spesifikan dimulai dari keyword function hingga keyword retur.
· Bagian body dimulai dari keyword IS atau AS hingga keyword AND. Hingga mencakup bagian deklarasi pada program dan exception.
Jenis-jenis fungsi :
· Function Tanpa Parameter
Tidak memiliki parameter/argumen. Biasanya bersifat statis setiap kali di eksekusi.
·
Function
Dengan Parameter
Memiliki output yang dinamis sesuai dengan nilai yang di inputkan
ke parameter dalam fungsi tersebut.
Ada beberapa konsep yang menarik dari fungsi antara lain:
·
Bahasa
yang dipakai dapat didefenisikan sendiri dengan tersedianya parameter LANGUAGE,
tanpa harus mengkompilasi ulang PostgreSQL.
·
Kita
dapat membuat dua buah fungsi dengan nama yang sama namun parameter masukkannya
yang berbeda, baik tipe data maupun jumlahnya.
Ada beberapa faktor yang perlu diperhatikan dalam membuat fungsi
antara lain:
·
Nama
Fungsi
·
Nomor
dari fungsi argument
·
Tipe
data dari setiap argumen
·
Tipe
dari hasil fungsi
·
Fungsi
action
·
Bahasa
yang digunakan oleh fungsi action.
Berikut
contoh sederhana pembuatan fungsi SQL untuk perkalian dari tiga inputan :
Contoh yang lain dalam pembuatan
fungsi SQL untuk mencari jumlah pegawai dari tabel
pegawai berikut :
B. FUNGSI PL/SQL
PL/SQL
(Procedural Language/ Structured Query Language) merupakan sebuah penggabungan
antara bahasa pemrograman prosedural (PL) dan SQL syntax. PL/SQL membentuk
pemrograman terstruktur dalam memproses data. PL/PGSQL merupakan bahasa yang
lain untuk membuat sebuah fungsi, biasanya bahasa ini digunakan untuk menangani
fungsi yang lebih kompleks. Pl/pgsql sudah terdapat dalam instalasi PostgreSQL.
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 server.
3. Meningkatkan konsistensi data saat sejumlah
aplikasi memanggil prosedur yang sama;
Sebaliknya kelemahannya yaitu server
akan lebih terbebani karena banyak proses yang harus ditangani. Sedangkan query PL/PGSQL agar lebih mudah dipahami akan dibagi
menjadi 2 yaitu pembuatan fungsi dan pembuatan definisi.
·
Berikut
Struktur pembuatan fungsi dalam pl/pgsql
1.
Pembuatan
fungsi
2.
Pembuatan
definisi
·
Menghapus
Fungsi
DROP
FUNCTION nama_fungsi(paramater, parameter, parameter ... );
Contoh :
Berikut ini
implementasi dari contoh penggunaan fungsi dengan bahasa PL/PGSQL:
Contoh berikut
menunjukkan query percabangan yang akan menampilkan nilai terkecil dari dua
buah parameter :
Contoh berikut
menunjukkan query perulangan yang akan menampilkan akumulasi dari jumlah
perulangan :
C. TRIGER
Trigger digunakan untuk menyisipkan
sebuah fungsi pada saat suatu record di INSERT, UPDATE dan DELETE. Trigger
sangat ideal untuk 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 menghasilkan
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.
Variabel
tersebut antara lain:
·
NEW
: Variabel yang berisi nilai baru suatu record pada saat INSERT atau UPDATE, bertipe
RECORD.
·
OLD : Variabel yang berisi nilai lama suatu
record pada saat UPDATE atau DELETE, juga bertipe RECORD.
Berikut
ini beberapa contoh penggunaan fungsi sebagai trigger procedure:
Contoh
: trigger berikut ini memastikan isi field atau kolom nama pada tabel anggota
selalu
huruf besar.
Kemudian
lanjutkan dengan pembuatan trigger yang berfungsi untuk memanggil fungsi secara
otomatis ketika kita melakukan INSERT ataupun UPDATE pada tabel anggota.
tampilkan
isi dari tabel anggota, hasilnya seperti pada tabel di bawah ini. Jadi setiap
data yang kita INSERT walaupun dalam penulisannya menggunakan huruf kecil Primary
key namun secara otomatis trigger akan memanggil fungsi yang bertugas untuk
mengganti setiap data yang masuk agar hasilnya nanti selalu menjadi huruf
besar:
2. TUGAS PRAKTIKUM
Menggunakan DBMS MySQL
1.
Buatlah
fungsi konversi suhu dari Fahrenheit ke derajat celcius dengan rumus konversi sebagai
berikut : celcius= (5*(nilai Fahrenheit-32))/9! Kemudian jalankan hasilnya dengan menSELECT fungsi tersebut !
2. Buatlah fungsi untuk mencari alamat mahasiswa
dari tabel mahasiswa berdasarkan
nama mahasiswa. Kemudian jalankan dengan perintah SELECT!
3.
Buatlah fungsi untuk menghitung nilai dengan menggunakan bahasa
pl/sql !
Kemudian jalankan hasilnya dengan menSELECT fungsi tersebut !
4. Buatlah
fungsi menggunakan pl/pgsql untuk mencari bilangan ganjil atau genap dari
bilangan yang diinputkan. Kemudian jalankan dengan perintah SELECT !
5. Tambahkan kolom modifikasi pada tabel mahasiswa.
Dimana setiap ada insert atau update maka tanggal pada kolom modifikasi akan
menunjukkan tanggal perubahan tersebut dilakukan.
3. PERBANDINGAN ANTARA POSTGRESQL DAN MYSQL
|
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.
4 4. KESIMPULAN
Dari Hasil Praktikum yang sudah dilakukan, dapat diambil kesimpulan bahwa 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. Semoga di praktikum selanjutnya lebih gampang :D
DAFTAR PUSTAKA
- http://yunitanaxfarmasi.wordpress.com/2013/04/25/pengertian-trigger-dalam-mysql/
- http://irpantips4u.blogspot.com/2012/10/pengenalan-plsql-plsql-adalah.html
- Modul Praktikum DBD Bab VIII
