1. Dasar
Teori
1.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];
a. Statement
RETURN tipedata_fungsi menunjukkan bahwa function akan
mengembalikan nilai dengan tipe data tertentu.
b. Statement RETURN nilai_fungsi
berfungsi untuk mengembalikan nilai yang telah di proses dalam function.
1.2.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.
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)
Contoh yang lain dalam pembuatan fungsi SQL untuk mencari jumlah pegawai dari tabel pegawai berikut :
1.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.
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;
- Menghapus fungsi :
DROP
FUNCTION nama_fungsi(paramater, parameter, parameter ... );
Contoh
:
DROP FUNCTION pembagian(text);
Berikut
ini implementasi dari contoh penggunaan fungsi dengan bahasa PL/PGSQL:
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
db_personal=> SELECT pl_caripegawai (1);
pl_caripegawai
-----------------
Hendro
(1 row)
db_personal=> SELECT pl_caripegawai (2);
pl_caripegawai
--------------------
Tika
(1 row)
Contoh berikut menunjukkan query percabangan yang akan menampilkan nilai terkecil dari dua buah parameter :
create
function percabangan (x integer,y integer)
returns
integer
as
‘declare nilai_terkecil integer;
begin
begin
if
x < y then
select
into nilai_terkecil x;
else
select
into nilai_terkecil y;
end
if;
return
nilai_terkecil;
end;’
language
‘plpgsql’;
Hasil sebagai berikut :
Select
percabangan (300,250);
Percabangan
-----------------
250
(1 row)
Contoh berikut menunjukkan query perulangan yang akan menampilkan akumulasi dari jumlah perulangan :
Create function perulangan (a integer,b integer)
Returns integer
As ‘ declare nilai_awal integer default a;
Hasil integer default 0;
Begin
Loop
If nilai_awal > b then
Exit;
End if;
Hasil := hasil+nilai_awal;
Nilai_awal := nilai_awal+1;
End loop;
Return hasil;
End loop;
Return hasil;
End;'
Language ‘plpgsql’;
Hasilnya sebagai berikut :
Select perulangan (1,5); Perulangan ---------------- 15 1 (row)
1.4. Trigger
Trigger merupakan sekumpulan perintah atau sintaks yang akan secara otomatis dijalankan jika terjadi operasi tertentu dalam tabel atau view. Trigger digunakan untuk memanggil satu atau beberapa perintah SQL secara otomatis sebelum atau sesudah terjadi proses INSERT, UPDATE atau DELETE dari suatu tabel. Sebagai contoh misalnya kita ingin menyimpan id pelanggan secara otomatis ke tabel ‘log’ sebelum menghapus data di tabel pelanggan.
Terdapat tiga kejadian (event) umum yang dapat menyebabkan trigger dipicu :
– INSERT, berarti trigger dijalankan ketika terdapat sejumlah data baru yang diinput
– UPDATE, berarti trigger dijalankan ketika terdapat data / record yang diubah
– DELETE, berarti trigger dijalankan ketika terdapat data / record yang dihapus
Manfaat dari penggunaan trigger :
– INSERT, berarti trigger dijalankan ketika terdapat sejumlah data baru yang diinput
– UPDATE, berarti trigger dijalankan ketika terdapat data / record yang diubah
– DELETE, berarti trigger dijalankan ketika terdapat data / record yang dihapus
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.
Contoh : Trigger berikut ini memastikan isi field atau kolom nama pada tabel anggota selalu huruf besar.
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 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)
2. Tugas Praktikum
2.1 Tugas Praktikum menggunakan PostgreSQL
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 !
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. Tugas Rumah
3.1. Buat laporan 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.
.png)
3.2 Evaluasi perbedaan kelebihan dan kemudahan dari postgreSQL dan MySQL
4. Kesimpulan dan Saran
4.1 Kesimpulan
Dari praktikum kali ini, dapat disimpulkan bahwa : - 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.
- 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.
- 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.
- Trigger merupakan sekumpulan perintah atau sintaks yang akan secara otomatis dijalankan jika terjadi operasi tertentu dalam tabel atau view. Trigger digunakan untuk memanggil satu atau beberapa perintah SQL secara otomatis sebelum atau sesudah terjadi proses INSERT, UPDATE atau DELETE dari suatu tabel.
4.2 Saran dan Manfaat bagi Pembaca
Dengan melakukan dua kali percobaan, membuat kita tahu bahwa pada
praktikum kali ini semua query pada postgreSQL dan MySQL sama. Semoga
pembaca dapat mengikutinya dirumah. Sekian :)
Daftar Pustaka
- Modul Praktikum Desain Basis Data
- Sanjaya, Ridwan. 2005. Pengolahan Database MySQL dengan Java 2. Yogyakarta: Andi
- Nugroho, Bunafit. 2005. Administrasi Database MySQL. Yogyakarta:Graha Ilmu
- http://richmadiyanti25.wordpress.com/2013/04/25/penggunaan-trigger-dalam-sql/
- http://www.unej.ac.id/pdf/owo-postgres.pdf
- http://geniuskomputer29.blogspot.com/2012/11/pengertian-dan-fungsi-sql-atau-mysql.html
- http://richmadiyanti25.wordpress.com/2013/04/25/penggunaan-trigger-dalam-sql/
- http://www.unej.ac.id/pdf/owo-postgres.pdf
- http://geniuskomputer29.blogspot.com/2012/11/pengertian-dan-fungsi-sql-atau-mysql.html
