Fungsi PL/SQL
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
parameter masukkannya yang berbeda, baik tipe data maupun jumlahnya.
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)
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;
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)
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.
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
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
Primary key
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)
Hasil Praktikum
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 !
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.
Evaluasi
Perbandingan DBMS
1.
PostgreSQL
Pada
postgreSQL, tidak diperlukan awalan sintaks “delimiter” untuk membuat fungsi.
Untuk mengeksekusi perintah, harus diakhiri dengan tanda “;” Pada parameter di
fungsi yang diapit dengan tanda kurung hanya berisikan tipe tanpa harus ada
nilai pada tipe data tersebut. Kemudian pada PostgreSQL bisa membuat triger
update dan insert dalam satu query sekaligus
Contoh create
function penjumlahan (float, float).
2.
MySQL
Pada
MySQL, harus ada awalan sintaks “delimiter” untuk membuat fungsi. Untuk
mengeksekusi perintah, harus diakhiri dengan tanda “//” yang berarti delimiter.
Pada parameter di fungsi yang diapit dengan tanda kurung tidak hanya berisikan
tipe , tetapi juga menyertakan nilai pada tipe data tersebut. Pada MySQL tidak
bisa menggabungkan fungsi trigger before update dan before insert dalam satu
quey, harus membuat dua trigger.
Contoh: Create
function penjumlahan (float(2), float(2))
Daftar
Pustaka
Musthafa, Aziz. 2014. Modul
Praktikum Desain Basis Data.
Fakultas Sains Dan Teknologi UIN Malang.
Solichin,
Achmad. 2010. MySQL dari Pemula Hingga
Mahir diakses melalui http://achmatim.net/download/21/
