Fungsi PL/PGSQL dan TRIGER
Assalamualaikum
,, hai sobat gimana kabarnya ? semoga kita tetap dalam lindungan Alllah Swt.
Dalam kesempatan kali
ini saya akan mengajak kalian untuk belajar mengenai FUNGSI, PL/PGSQL dan
TRIGER. Hmm kelihatannya susah ya,emang agak susah .
hhe tapi jangan difikirin lah. Yang penting kita mencoba dulu. Ok yuk kita
simak .
Landasan Teori
FUNGSI, PL/PGSQL dan TRIGER
Pengertian 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.
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.
Pendukung
Fungsi
PostgreSQL memiliki fungsi yang dapat mengubah suatu
nilai dalam suatu kolom atau barismenjadi huruf besar. Fungsi tersebut bernama
upper(nama_kolom), berfungsi memanggilfungsi upper dengan nama_kolom sebagaii
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];
Ada
beberapa faktor yang perlu diperhatikan dalam membuat fungsi antara lain:
•
Nama Fungsi
•
Nomor dari fungsi argument
•
Tipe data dari setiap argument
• Tipe dari hasil fungsi
•
Fungsi action
•
Bahasa yang digunakan oleh fungsi action.
Berikut
contoh sederhana pembuatan fungsi SQL untuk perkalian dari tiga inputan :
Fungsi PL/PGSQL
Fungsi
PL/PGSQL menkombinasikan kekuatan dan kefleksibilitasan SQL dengan pemrograman
procedural .
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, namun kita perlu mendaftarkannya pada masing-
masing database yang akan menggunakannya. Untuk itu kita perlu menginstal
pl/pgsql pada databse yang diinginkan.
Ada
beberapa faktor yang harus diperhatikan pada databse ini antara lain:
DECLARE
: mendeklarasikan variable yang digunakan dalam fungsi
SELECT
INTO : sebuah from special (khusus) dari select yang memperbolehkan hasil query
menenmpati ke dalam variable.
RETURN
Exit:merupakan hasil value dari fungsi
Sama
seperti bahasa procedural yang lain, PL/SQL juga menyediakan :
Variabel,
Konstanta dan Tipe Data Conditional Statement dan Looping
Variable
Sama
seperti bahsa Prosedural lainnya , dalam blok PL/SQL kita juga dapat
mendeklarasikan Variabel dan Konstanta. Variabel ini di deklarasikan dan
diinisialisasi pada bagian DECLARE
Kegunaan
Variabel :
Sebagai
tempat Penyimpanan Data sementara
Dapat
di pakai berulang kali
Memanipulasi
nilai yang telah tersimpan dalam database
Identifier
Dalam
membuat sebuah variable , terlebih dahulu kita tentukan identifiernya ,dimana
salah satu kegunaan identifiar adalah sebagai nama dari suatu variable
Dalam
memberikan penamaan variable , terdapat aturan yang harus kita patuhi yaitu :
Tidak
diawali dengan angka dan harus diawali dengan huruf
Dapat
memuat angka
Dapat
memuat karakter special seperti _,$, dan #
Terbatas
hanya 30 karakter
Bukan
kata kunci (int, varchar, table, trigger)dalam oracle server
Konstanta
Digunakan
untuk menyimpan sebuah nilai di memor komputer. Nilai yang disimpan bersifat
tetap (konstan). Nilai ditentukan pada saat deklarasi dibuat sehingga nilainya
tetap pada saat program dieksekusikan.
Tipe
data dalam PL/SQL
Tipe
data umum dalam PL/SQL termasuk NOMOR, INTEGER, VARCHAR, DATE, TIMESTAMP,dll
Selain
itu juga terdapat tipe data lainnya dalam PL/SQL yaitu :
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 di pahami akan dibagi
menjadi 2 yaitu pembuatan fungsi dan pembuatan definisi
Berikut
Struktur pembuatan fungsi dalam pl/pgsql
1.
Pembuatan fungsi : CREATE [OR REPLACE] FUNCTION nama_fungsi ( argtype , ... ])
RETURNS tipe_data AS ‘definisi’ LANGUAGE ‘plpgsql’;
2.
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;
Berikut
contohnya :
Fungsi Trigger
Trigger sering digunakan, antara lain untuk:
· Melakukan
update data otomatis jika terjadi perubahan. Contohnya adalah dalam sistem
penjualan, jika dientri barang baru maka stock akan bertambah secara otomatis.
· Trigger
dapat digunakan untuk mengimplementasikan suatu sistem log. Setiap terjadi
perubahan, secara otomatis akan menyimpan ke tabel log.
· Trigger
dapat digunakan untuk melakukan validasi dan verifikasi data sebelum data
tersebut disimpan.
Statement
atau perintah dalam trigger dapat berupa satu perintah saja, dan dapat juga
beberapa perintah sekaligus. Jika terdapat beberapa perintah dalam trigger,
maka gunakan perintah BEGIN dan END untuk mengawali dan mengakhiri perintah.
Di
dalam statement trigger, kita dapat mengakses record tabel sebelum atau sesudah
proses dengan menggunakan NEW dan OLD.
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.
Manfaat dari penggunaan trigger :
·
Trigger merupakan salah satu mekanisme
untuk menjalankan aturan bisnis dan menjaga integritas data dalam sebuah basis
data. Mekanisme yang lainnya adalah penggunaan CONSTRAINT
·
Trigger dapat mengevaluasi status dari
sebuah tabel sebelum dan sesudah terjadi perubahan data dan melakukan aksinya
sesuai perbedaan yang terjadi
·
Trigger dapat melakukan perubahan
cascade melalui melalui relasi tabel pada database, akan tetapi perubahan ini
akan lebih efisien jika dieksekusi menggunakan referential integrity
constraints.
·
Trigger dapat melakukan pencegahan akses
atas data yang kompleks dibanding dengan menggunakan CHECK CONSTRAINT
·
Tidak seperti pada Check Constraint,
Trigger dapat mereferensikan kolom / field dalam tabel yang berbeda.
Syntax membuat Trigger :
CREATE TRIGGER
{Nama_Trigger}
ON {Nama_Table}
FOR {DELETE | INSERT |
UPDATE}
AS
{SQL Statement}
Syntax menghapus Trigger :
DROP TRIGGER
{Nama_Trigger}
ALTER TRIGGER
{Nama_Trigger}
ON [Nama_Table}
FOR {DELETE | INSERT |
UPDATE}
AS
{SQL Statement}
Syntax mendapatkan informasi mengenai trigger :
sp_helptext
{Nama_Trigger}
sp_help {Nama_Trigger}
sp_columns
{Nama_Trigger}
Contoh penggunaan :
DROP TRIGGER info
CREATE TRIGGER info
ON BARANG
FOR INSERT
AS
PRINT ‘Data Telah
Ditambahkan ke tabel Barang…!’
Adapun
untuk fungsi di mysql
FUNGSI DAN PROSEDUR MYSQL
Pengertian
Prosedur dan Fungsi Tersimpan
Yang
dimaksud dengan prosedur tersimpan (stored procedure) adalah suatu modul yang
berisi kumpulan pernyataan SQL yang ditujukan untuk melaksanakan tugas tertentu
dan letaknya ada pada server. Modul ini bisa dipanggil oleh klien, sedangkan
pengeksekusian dilakukan di server.
Keuntungan penggunaan prosedur tersimpan adalah sebagai berikut:
1.
Meningkatkan kinerja karena mengurangi pengiriman kode dari klien ke server,
mengingat modul berada pada server.
2.
Meningkatkan keamanan karena pengaksesan data tertentu ditangani dalam server,
tidak melalui pengaksesan secara langsung oleh klien.
3.
Meningkatkan integritas data (konsistensi data) saat sejumlah aplikasi
memanggil prosedur tersimpan yang sama.
Sebaliknya,
kelemahan yang terjadi sebagai akibat keuntungan-keuntungan tersebut, server
akan lebih terbebani, karena banyak proses yang harus ditangani oleh server.
Pada
MySQL, terdapat istilah prosedur tersimpan dan fungsi tersimpan (stored
function). Perbedaannya:
~ Prosedur tersimpan tidak menghasilkan nilai ketika dipanggil
~ Fungsi tersimpan menghasilkan nilai ketika dipanggil dan tetntu saja seperti
fungsi biasa bisa dipanggil di dalam suatu pernyataan (misalnya dalam SELECT)
~Nilai
yang dihasilkan oleh fungsi tersimpan biasa disebut nilai balik (return value)
~Prosedur
dan fungsi yang tersimpan dikenal dengan sebutan rutin (routine).
Cara Menciptakan Prosedur Tersimpan
CREATE
PROCEDURE nama_prosedurtersimpan ([parameter_prosedur [,…]])
bagian_kode
Dalam
hal ini:
•
nama_prosedur tersimpan menyatakan nama prosedur tersimpan
•
parameter_prosedur menyatakan definisi untuk parameter prosedur tersimpan
•
bagian_kode berupa pernyataan-pernyataan SQL
Adapun
bentuk parameter_prosedur berupa:
[
IN I OUT I INOUT ] nama_parameter tipe
Setiap
parameter secara opsional bisa diawali IN, OUT, atau INOUT. IN berarti
parameter sebagai masukan bagi prosedur. OUT berarti parameter sebagai
keluaran. INOUT berarti sebagai masukan sekaligus keluaran.
Menjalankan Prosedur Tersimpan
Untuk
memanggil prosedur tersimpan, gunakan pernyataan CALL. Pernyataan ini memiliki
bentuk:
CALL
nama_pt ([parameter[,…]])
Dalam
hal ini, nama_pt adalah nama prosedur tersimpan.
Cara
Menciptakan Fungsi Tersimpan
Fungsi
tersimpan diciptakan melalui pernyataan CREATE FUNCTION. Kaidah dasarnya
seperti berikut:
CREATE
FUNCTION nama_ft ([param_fung [,…]])
RETURNS
tipe
bagian_kode
Dalam
hal ini:
•
nama_ft menyatakan nama fungsi tersimpan
•
param_fung menyatakan definisi untuk parameter fungsi tersimpan
•
RETURNS tipe berguna untuk menentukan tipe nilai balik
•
Bagian_kode berupa pernyataan-pernyataan SQL
Berbeda
dengan prosedur tersimpan, semua parameter pada fungsi tersimpan hanya
berfungsi sebagai masukan.
Memperoleh
Informasi Prosedur dan Fungsi Tersimpan
MySQL
menyediakan perintah SHOW PROCEDURE dan SHOW FUNCTION untuk memperoleh
informasi tentang prosedur dan fungsi tersimpan. Kaidah pernyataan:
SHOW
{ PROCEDURE I FUNCTION } STATUS [ LIKE ‘pola’ ]
Bila
pola disertakan, informasi nama prosedur atau fungsi tersimpan yang cocok
dengan pola akan ditampilkan.
Menghapus Prosedur atau Fungsi Tersimpan
Bila
dikehendaki untuk menghapus suatu prosedur atau fungsi tersimpan, bisa
menggunakan pernyataan DROP PROCEDURE atau PROCEDURE FUNCTION. Kaidah kedua
pernyataan tersebut:
DROP
{ PROCEDURE I FUNCTION} [ IF EXISTS] nama
Tanda
{ I } berarti yang ada di dalamnya bisa berupa salah satu (yaitu PROCEDURE atau
FUNCTION). Bagian IF EXISTS bersifat opsional; artinya “jika ada”. Bila IF
EXISTS disertakan, pernyataan tidak akan menimbulkan kesalahan walaupun
prosedur atau fungsi tersimpan nama tidak pernah didefinisikan.
Ø Membuat Trigger
Baru
Berikut
ini bentuk umum perintah untuk membuat triggers:
CREATE
TRIGGER name
[BEFORE|AFTER]
[INSERT|UPDATE|DELETE]
ON
tablename
FOR
EACH ROW statement
Keterangan
dari bentuk umum perintah membuat trigger:
name, Nama trigger mengikuti peraturan penamaan variabel / identifier
dalam MySQL [BEFORE | AFTER] digunakan untuk menentukan kapan proses secara
otomatis akan dieksekusi, sebelum atau sesudah proses.
[INSERT
| UPDATE | DELETE] digunakan untuk menentukan event (proses) yang dijadikan
trigger
(pemicu) untuk menjalankan perintah-perintah di dalam triggers. tablename, merupakan nama tabel dimana
trigger berada. statement, merupakan
sekumpulan perintah atau query yang akan secara otomatis dijalankan jika event
/ proses yang didefinisikan sebelumnya aktif.
Statement atau perintah dalam trigger dapat berupa satu perintah saja,
dan dapat juga beberapa perintah sekaligus. Jika terdapat beberapa perintah
dalam trigger, maka gunakan perintah
BEGIN dan END untuk mengawali dan mengakhiri perintah. Di dalam statement trigger, kita dapat
mengakses record tabel sebelum atau sesudah proses dengan menggunakan NEW dan OLD.
Berikut ini contoh
trigger
yang akan mencatat aktivitas ke tabel
log setiap terjadi proses insert ke tabel pelanggan:
DELIMITER
$$
CREATE
TRIGGER penjualan.before_insert BEFORE INSERT ON penjualan.pelanggan
FOR
EACH ROW BEGIN
INSERT
INTO `log` (description, `datetime`, user_id)
VALUES
(CONCAT('Insert data ke tabel pelanggan id_plg = ', NEW.id_pelanggan), now(),
user());
END;
$$
DELIMITER ;
Menghapus Trigger
Untuk
menghapus trigger, dapat menggunakan perintah
DROP TRIGGER dengan diikuti
dengan nama tabel dan nama triggernya. Berikut ini bentuk umum dan contoh
perintah untuk menghapus trigger. Bentuk
umum dan contoh menghapus trigger:
DROP
TRIGGER tablename.triggername;
Contoh
menghapus trigger bernama „before_insert‟ yang ada di tabel pelanggan.
DROP
TRIGGER penjualan.before_insert;
Function
dan Stored Procedure
Function
dan Stored Procedure merupakan fitur utama yang paling penting di MySQL 5.
Function dan Stored Procedure merupakan suatu kumpulan perintah atau statement
yang disimpan dan dieksekusi di server database MySQL. Dengan SP (Stored
Procedure), kita dapat menyusun program seder hana berbasis sintaks SQL untuk
menjalankan fungsi tertentu. Hal ini menjadikan aplikasi yang kita buat lebih
efektif dan efisien.
Berikut
ini beberapa keuntungan menggunakan Stored Procedure:
- Lebih cepat. Hal ini karena kumpulan
perintah query dijalankan langsung
diserver. Berbeda dengan jika dijalankan secara sekuensial di bahasa
pemrograman, akan lebih lambat karena harus “bolak-balik” antara client dan
server.
- Menghilangkan duplikasi proses,
pemeliharaan yang mudah. Pada dasarnya operasi yang terjadi di suatu aplikasi terhadap database adalah sama.
Secara umum, di alam aplikasi biasanya terdapat operasi untuk validasi data
inputan, menambahkan record baru, mengubah record, menghapus record dan
sebagainya. Dengan SP, mungkin kita dapat menghindari adanya duplikasi proses yang kurang lebih sama,
sehingga pemeliharaannya juga jadi lebih mudah.
- Meningkatkan keamanan database. Dengan adanya SP, database akan lebih aman
karena aplikasi yang memanggil SP tidak perlu mengetahui isi di dalamnya.
Sebagai contoh, dalam proses menambahkan data (insert), kita membuat suatu SP
khusus. Dengan demikian, saat client atau aplikasi akan menambahkan data
(insert) maka tidak perlu tahu nama tabelnya, karena hanya cukup memanggil SP
tersebut dengan mengirimkan parameter yang diinginkan.
Selanjutnya,
Stored Procedure dari segi bentuk dan sifatnya terbagi menjadi 2 (dua), yaitu
FUNCTION dan PROCEDURE. Perbedaan utama antara function dan procedure adalah
terletak pada nilai yang dikembalikannya (di -return). Function memiliki
suatu nilai yang dikembalikan
(di-return), sedangkan procedure tidak.
Umumnya suatu procedure hanya berisi suatu kumpulan proses yang tidak
menghasilnya value, biasanya hanya menampilkan saja. istilah SP (Stored Procedure)
maka yang dimaksud adalah Function dan Procedure.
Selesai
PEMBAHASAN
Hasil
Praktikum PostgreSql dan MySql
1. Buatlah fungsi konversi suhu dari Fahrenheit ke
derajat celcius dengan rumus konversi sebagai berikut : celcius=(nilai
Fahrenheit-32)*5/9
Kemudian
jalankan hasilnya dengan menSELECT fungsi tersebut !
PostgreSql
MySql
2.
Buatlah fungsi untuk mencari alamat mahasiswa dari tabel mahasiswa berdasarkan
nama mahasiswa. Kemudian jalankan dengan perintah SELECT !
PostgreSql
MySql
3.
Buatlah fungsi untuk menghitung nilai dengan menggunakan bahasa pl/sql !
Kemudian jalankan
hasilnya dengan menSELECT fungsi tersebut !
PostgreSql
MySql
4.
Buatlah fungsi menggunakan pl/pgsql untuk mencari bilangan ganjil atau genap
dari bilangan yang diinputkan. Kemudian jalankan dengan perintah SELECT !
PostgreSql
MySql
5.Tambahkan
kolom modifikasi pada tabel mahasiswa. Dimana setiap ada insert atau update
maka tanggal pada kolom modifikasi akan menunjukkan tanggal perubahan tersebut
dilakukan.
PostgreSql
MYSQL
Evaluasi PostgreSQL dan MySQL
Tampak terlihat
dengansangat jelas pada hasil praktikum diatas antara postgresql dan mysql.
Hampir semuanya berbeda dalam penggunaan fungsinya. Adapun perbedaannya yaitu :
- Syntax di MySQL tanpa menggunakan
‘pl/sql’ sedangkan di PostgreSQL menggunakan syntax ‘pl/pgsql’.
- Syntax untuk membuat sebuah function
atau triger di MySQL ditambah dengan DELIMITER $$ dan diakhiri dengan $$.
- Untuk membuat triger di MySQL haru diberi
kolom/field terlebih dahulu untuk menampung triger sedangkan di PostgreSQL
tidak perlu.
-
Penamaan untuk membuat sebuah function
atau Triger di Mysql harus diberi tipe data dan panjang data (length) di
postgre hanya diberi tipe data saja sudah bisa di eksekusi.
Kesimpulan :
Praktikum
kedelapan ini sangat terlihat jelas perbedaan antara DBMS PostgreSQL dan MySQL
yaitu dari segi perintah dan syntax sudah sangat berbeda. MySQL harus
menggunaka DELIMITER $$ untuk memanggil Function dan Triger dan diakhiri dengan
$$
Saran
Pada
praktikum ke delapan ini sangat menguras pikiran karena query di MySQL banyak
sekali perbedaan dengan PostGreSQL, jadi tetep semangat ya dalammenjalaninya ,
buktinya juga bisa terselesaikan kok.
Jadi pantang menyerah !!
Kritik
Jangan
terlalu terpaku pada query yang ada di praktikum karena query yang ada di
praktikum bisa jadi sama dan bisa jadi berbeda. Dan buktinya pada kali ini
sangat terlihat tampak jelas perbedaannya.
Manfaat bagi pembaca
Dengan
adanya laporan praktikum desain basis data episode ke-8 ini bisa membuat para
pembaca mengetahui Fungsi,Pl/SQL dan TRIGER yang ada di MySQL dan juga di
postgresql.
Semoga
bermanfaat sobat
Daftar Rujukan
Modul
praktikum VIII
Tidak ada komentar:
Posting Komentar