A.
Materi/ Teori
1.
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 sebagaii
argumen sehingga menghasilkan nilai pada kolom 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];
2. Fungsi
PADA PGSQL
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.
Nah, dari definisi diatas, bisa kita perdalam lagi makna dari fungsi di PostgreSQL
Nah, dari definisi diatas, bisa kita perdalam lagi makna dari fungsi di PostgreSQL
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 parametermasukkannya 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 argument
• Tipe dari
hasil fungsi
• Fungsi action
• Bahasa yang
digunakan oleh fungsi action.
Berikut contoh
sederhana pembuatan fungsi SQL untuk perkalian dari tiga inputan:
db_personal=>
CREATE FUNCTION perkalian (FLOAT, FLOAT, FLOAT)
db_personal->
RETURNS FLOAT
db_personal->
AS 'SELECT ($1 + $2) * $3;'
db_personal->
LANGUAGE 'sql';
CREATE
db_personal=>
SELECT perkalian (10,10,10);
perkalian
-----------
200
(1 row)
3. Fungsi
PL/PGSQL
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.
Berikut adalah 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[1]
- 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;
- Menghapus
fungsi :
DROP FUNCTION
nama_fungsi(paramater, parameter, parameter ... );
Contoh :
DROP FUNCTION
pembagian(text); Sudah memahami
fungsi, ada tahap selanjutnya, yaitu trigger.
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.
langkah pertama buatlah 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
Kemudian
lanjutkan dengan pembuatan trigger yang berfungsi untuk memanggil fungsi
secara otomatis ketika kita 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
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:
db_personal=> SELECT * FROM anggota;
id | nama
--------+-------------
26 | ANDHIE
83 | RWATIA
(2 rows)
PADA MYSQL
Stored Function
(1) fungsi
aggregate adalah fungsi fungsi bawaan yang terdapat pada sistem database tersebut,
(2) fungsi yang diciptakan oleh user sendiri.
(2) fungsi yang diciptakan oleh user sendiri.
Pada bagian ini akan dibahas tentang cara
menciptakan, dang menggunakan fungsi yang dibuat oleh user sendiri. Cara kerja
fungsi fungsi “>hampir sama dengan Stored Procedure, namun fungsi mempunyai
ada yang parameter masukan ada yang tidak. Selain parameter fungsi
mengembalikan nilai balik lewat perintah RETURN.
DELIMITER $$
CREATE FUNCTION
nama_fungsi (parameter[,...])
RETURNS tipe_data_nilai_balik
/* COMMENT keterangan_string */
BEGIN
Deklasi variabel;
isi_perintah_fungsi;
RETRUN
data_nilai_balik;
END$$
DELIMITER ;
- Cara menciptakan function,
- Klik function, Klik kanan mouse
- Pilih dan Klik Create Function
Fungsi dengan
parameter
Fungsi dengan
Operator
Yaitu fungsi
yang didalamnya terdapat operator perhitungan
Fungsi Dikombinasikan
dengan SELECT
·
Secara umum penulisan program
dalam fungsi menggunakan bahasa PL/SQL, yaitu standar behasa SQL. Oleh karena
itu dalam fungsi dapat melibatkan perintah SELECT, INTO, FROM, WHERE dan
lainya. Sebagai contoh untuk mencari nama dengan peritah SELECT nama FROM
mhs WHERE no_mhs=’055410002′
·
Kemudian
perintah ini akan dikombinasikan kedalam fungsi, misal nama fungsinya diberi
nama get_nama(no_mhs);
Trigger sering
digunakan, antara lain untuk:
o Melakukan update data
otomatis jika terjadi perubahan. Contohnya adalah dalam sistem penjualan, jika
dientri barang baru maka stock akan bertambah secara otomatis.
o
Trigger dapat digunakan untuk mengimplementasikan suatu sistem log. Setiap
terjadi perubahan, secara otomatis akan menyimpan ke tabel log.
o
Trigger dapat digunakan untuk melakukan validasi dan verifikasi data sebelum
data tersebut disimpan.
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:
o
name, Nama trigger mengikuti peraturan penamaan variabel / identifier
dalam MySQL
o
[BEFORE | AFTER] digunakan untuk menentukan kapan proses secara
otomatis akan dieksekusi, sebelum atau sesudah proses.
o
[INSERT | UPDATE | DELETE] digunakan untuk menentukan event
(proses) yang dijadikan trigger (pemicu) untuk menjalankan perintah-perintah di
dalam triggers.
o
tablename, merupakan nama tabel dimana trigger berada.
o 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 me- nggunakan NEW dan OLD.
NEW digunakan untuk mengambil record yang akan diproses (insert atau
update), sedangkan OLD digunakan untuk mengakses record yang
sudah diproses (update atau delete).
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;
B.
Hasil Praktikum
a.
Hasil Praktikum PostgreSQL
1.
Membuat
fungsi yang berguna untuk mengonversi suhu dari Fahrenheit ke Celcius, dengan
rumus. Celcius = ((nilai Fahrenheit-32)/1.8). Dengan query sebagai berikut:
“CREATE FUNCTION konversi_suhu
(FLOAT) RETURNS FLOAT AS ‘SELECT (($1-32)/1.8);’ LANGUAGE ‘sql’;”. Kemudian
untuk mengaplikasikan fungsinya dengan query “SELECT konversi_suhu (32);”
2.
Membuat
fungsi untuk mencari alamat mahasiswa dengan mengetikkan nama
mahasiswanya.Querynya sebagai berikut:
“CREATE FUNCTION cari_alamat
(VARCHAR); RETURNS VARCHAR AS ‘SELECT alamat_mah FROM MAHASISWA WHERE
nama_mah=$1;’ LANGUAGE ‘sql’;”. Kemudian coba gunakan fungsi yang telah
dibuat “SELECT cari_alamat (‘binta’);”.
3.
Sekarang
kita buat pl/sql untuk menghitung nilai, querynya sebagai berikut:
“CREATE FUNCTION hitung_nilai
(INTEGER) RETURNS VARCHAR AS ‘DECLARE hasil VARCHAR; BEGIN CASE
WHEN $1 > 100 OR $1 < 0 THEN
SELECT INTO hasil ‘’Nilai Salah’’;
WHEN $1 >= 90 THEN SELECT INYO
hasil ‘’Nilai A’’;
WHEN $1 >= 70 OR $1 < 90 THEN
SELECT INTO hasil ‘’Nilai B’’;
WHEN $1 >= 60 OR $1 < 70 THEN
SELECT INTO hasil ‘’Nilai C’’;
WHEN $1 >= 50 OR $1 < 60 THEN
SELECT INTO hasil ‘’Nilai D’’;
WHEN $1 >= 0 OR $1 < 50 THEN
SELECT INTO hasil ‘’Nilai E’’;
END CASE; RETURN hasil; END;’
LANGUAGE ‘plpgsql’;”. Kemudian
coba gunakan pl/sql yang telah dibuat dengan query “SELECT hitung_nilai
(101);”
4.
Membuat
fungsi menggunakan pl/sql untuk menentukan angka ganjil atau genap yang
diinputkan, querynya sebagai berikut:
“CREATE FUNCTION ganjil_genap
(INTEGER) RETURNS VARCHAR AS ‘DECLARE hasil VARCHAR; BEGIN
IF $1%2 = 0 THEN SELECT INTO hasil
‘’genap’’;
ELSIF $1%2 != 0 THEN SELECT INTO
hasil ‘’ganjil’’;
END IF; RETURNS hasil; END;’ LANGUAGE
‘plpgsql’;”. Kemudian coba aplikasikan fungsi
yang telah dibuat dengan query “SELECT ganjil_genap (23);”
5.
Sekarang
buat fungsi trigger dengan menambahkan terlebih dahulu kolom modifikasi yang
isinya tanggal yang akan berubah setiap terjadi insert atau update, querynya
sebagai berikut:
“ALTER TABLE mahasiswa ADD COLUMN
modifikasi date; (menambah kolom modifikasi)
CREATE FUNCTION modifikasi ()
RETURNS OPAQUE AS ‘BEGIN new.modifikasi := current_date; RETURN new; END;’
LANGUAGE ‘plpgsql’; (membuat fungsi
terlebih dahulu)
CREATE TRIGGER modifikasi BEFORE
INSERT OR UPDATE ON MAHASISWA FOR EACH ROW EXECUTE PROCEDURE modifikasi ();”. Fungsi trigger sudah selesai, sekarang coba aplikasikan dengan
query insert “INSERT INTO MAHASISWA values (28,’icis’,’sidoarjo’,’087676767676’,3,’L’);”.
Lalu tampilkan tabelnya yang telah diinsertkan.
b.
Hasil Praktikum MySQL
1.
Kita
di MySQL harus mensetting delimiter terlebih dahulu. Misal kita pakai delimiter
‘//’
2.
Membuat
fungsi yang berguna untuk mengonversi suhu dari Fahrenheit ke Celcius, dengan
rumus. Celcius = ((nilai Fahrenheit-32)/1.8). Dengan query sebagai berikut:
“create function konversi2 ($1
FLOAT) returns float return (($1-32)/1.8); //”. Aplikasikan fungsinya
dengan “select konversi2(36); //”
3.
Membuat
fungsi untuk mencari alamat mahasiswa dengan mengetikkan nama
mahasiswanya.Querynya sebagai berikut:
“create function cari_alamat
(nama varchar(20)); returns varchar(20) begin declare hasil varchar(20); select
alamat_mah into hasil from mahasiswa where nama_mah=nama; return hasil; end;//”.
Kemudian coba gunakan fungsi yang telah dibuat “select cari_alamat
(‘binta’); //”.
4.
Sekarang
kita buat pl/sql untuk menghitung nilai, querynya sebagai berikut:
“create function hitung_nilai
(nilai integer) returns varchar(15) begin declare hasil varchar(15);
if nilai>100 or nilai<0 then
select “Nilai Salah” into hasil;
elseif nilai>=90 then select
“Nilai A” into hasil;
elseif nilai>=70 or nilai<90
then select “Nilai B” into hasil;
elseif nilai>=60 or nilai<70
then select “Nilai C” into hasil;
elseif nilai>=50 or nilai<60
then select “Nilai D” into hasil;
else select “Nilai E” into hasil;
end if; return hasil; end; //”. Kemudian coba gunakan pl/sql yang telah dibuat dengan query “select
hitung_nilai (78); //”
5.
Membuat
fungsi menggunakan pl/sql untuk menentukan angka ganjil atau genap yang
diinputkan, querynya sebagai berikut:
“create function ganjil_genap (nilai
integer) returns varchar(20) begin declare hasil varchar(20);
if nilai%2=0 then select “Genap”
into hasil;
else select “Ganjil” into hasil; end
if; return hasil; end; //”. Kemudian
coba aplikasikan fungsi yang telah dibuat dengan query “select ganjil_genap (44);”
6.
Sekarang
buat fungsi trigger dengan menambahkan terlebih dahulu kolom modifikasi yang
isinya tanggal yang akan berubah setiap terjadi insert atau update, querynya
sebagai berikut:
“alter table mahasiswa add column
modifikasi date; //” (menambah kolom modifikasi)
“create trigger modifikasi_insert
before insert on mahasiswa for each row begin set new.modifikasi=sysdate();
end; //”.
“create trigger modifikasi_insert
before update on mahasiswa for each row begin set new.modifikasi=sysdate();
end; //”
Fungsi trigger sudah selesai, sekarang coba
aplikasikan dengan query insert “insert into mahasiswa(nim_mah, nama_mah,
alamat_mah, no_telp, id_fak, gender) values (99,
‘kafan’,’jember’,’098989787878’,2,’P’);//”. Lalu coba update data juga “update
mahasiswa set nama_mah=’aris’ where nim_mah=7;//” Lalu tampilkan tabelnya
yang telah diinsertkan.
C.
Evaluasi Perbandingan
Banyak perbedaan diantara postgreSQL
dan MySQL dalam bab ini, utamanya dalam hal penulisan. Dalam pembuatan
fungsi, MySQL harus diikuti oleh
delimiter agar bisa dibuat. Beirkut perbedaannya.
-PostgresSQL
1. Perlu menuliskan Bahasa yang
dipakai seperti pl pgsql.
2. Pada tipe Char atau Varchar tidak
perklu diberi ukuran.
3. Tentang percabangan, setelah
query if, atau else if serta else maka menggunakan select into variable
(values).
4. Insert seperti biasanya.
-MySQL
1. Tidak perlu menuliskan Bahasa
yang dipakai dan bisa langsung meng eksekusi query.
2. Pada tipe Char atau Varchar tidak
diberi ukuran jika tidak maka akan error.
3. Perlu adanya concat (nilai
kembalian di akhir query).
4. Tentang percabangan, setelah
query if, atau else if serta else maka menggunakan then return concat (values).
5. Untuk melakukan insert terlebih
dahulu mendeklaraskan variabelnya.
D.
Kesimpulan, Kritik, Saran, dan Manfaat
Berdasarkan apa yang telah kita
pelajari pada praktikum ini tentang Fungsi, plpgsql dan trigger maka dapat
disimpulkan bahwa antara PostgresSQL dengan MySQL sangatlah berbeda. Dan itu
banyak juga perbedaannya. Namun, semua dapat dikerjakan dengan mudah.
Semoga sedikit ilmu yang kami
tularkan nantinya akan dapat mempermudah pembaca dalam memcahkan
masalah-masalah terutama dalam pembahasan di bab ini.
E.
Daftar Pustaka
Modul
Praktikum Desain Basis Data 2014
http://www.wirabumisoftware.com/in/kostumasi-openbravo/postgresqldevtutorial/25-pgsqlid/26
plpgsql
http://
Badiyanto's_Blog.blogspot.com/Tutorial/MySQL
http://87wz.blogspot.com/2013/12/trigger-pada-mysql.html
0 komentar:
Posting Komentar