LAPORAN PRAKTIKUM DESAIN
BASIS DATA
FUNGSI, PL/PGSQL dan TRIGGER
Dosen Pengampu :
Asisten :
Landi
NAMA :Moh
Ikbalul Huda
NIM : 13650037
KELAS :
G
JURUSAN
TEKNIK INFORMATIKA
FAKULTAS
SAINS DAN TEKNOLOGI
UNIVERSITAS
ISLAM NEGERI MAULANA MALIK IBRAHIM MALANG
Tahun
Ajaran 2014/2015
- LANDASAN TEORI
- 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];
- Fungsi
- Fungsi pada PostgresSQL
Fungsi SQL
adalah sebuah kumpulan query, biasanya query yang detail dan
panjangyang dibungkus menjadi satu dan disimpan dalam database dan
kemudian apabiladiperlukan 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
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)
- Fungsi pada MySQL
Secara default,
routine (procedure/function) diasosiasikan dengan database yang
sedang aktif. Untuk dapat mengasosiasikan routine secara eksplisit
dengan database yang lain, buat routine dengan format:
db_name.sp_name.
MySQL
mengijinkan beberapa routine berisi statemen DDL, seperti CREATE dan
DROP. MySQL juga mengijinkan beberapa stored procedure (tetapi tidak
stored function) berisi statemen SQL transaction, seperti COMMIT.
Stored function juga berisi beberapa statemen baik yang secara
eksplisit atau implisit commit atau rollback.
Sintak :
CREATE FUNCTION
sp_name ([func_parameter[,...]])
RETURNS type
[characteristic ...] routine_body
proc_parameter:
[ IN | OUT | INOUT ] param_name type
func_parameter:
param_name type
type:
Any valid MySQL
data type
characteristic:
LANGUAGE SQL
| [NOT]
DETERMINISTIC
| { CONTAINS
SQL
| NO SQL
| READS SQL
DATA
| MODIFIES SQL
DATA }
| SQL SECURITY
{ DEFINER | INVOKER }
| COMMENT
'string'
routine_body:
Valid SQL
procedure statement or statements
Keterangan :
- sp_name: Nama routine yang akan dibuat
- proc_parameter: Spesifikasi parameter sebagai IN, OUT, atau INOUT valid hanya untuk
PROCEDURE.
(parameter FUNCTION selalu sebagai parameter IN)
- returns: Klausa RETURNS dispesifikan hanya untuk suatu FUNCTION. Klausa ini digunakan untuk mengembalikan tipe fungsi, dan routine_body harus berisi suatu statemen nilai RETURN.
- comment: Klausa COMMENT adalah suatu ekstensi MySQL, dan mungkin digunakan untuk mendeskripsikan stored procedure. Informasi ini ditampilkan dengan statemen SHOW CREATE PROCEDURE dan SHOW CREATE FUNCTION.
Contoh: mysql>
delimiter //
mysql>
create function fcNamaMHS(x char(25)) returns char(40)
-> return concat('Nama : ', x);
-> // Query OK, 0 rows affected (0.00 sec)
mysql>
select fcNamaMHS('Sholihun');
++tri
Dari contoh
diatas terlihat bahwa parameter “x” diperlakukan sebagai IN
karena sebagaimana dijelaskan sebelumnya bahwa fungsi hanya bisa
dilewatkan dengan
parameter IN.
Kemudian untuk pengembalian nilainya, digunakan tipe data dengan
kisaran nilai tertentu (dalam hal ini char(40)) dengan diawali
pernyataan returns.
Pernyataam
“concat('Nama : ', x)” merupakan routine_body yang akan
menghasilkan gabungan string “Nama :” dengan nilai dari parameter
“x” yang didapat ketika fungsi ini dieksekusi. Perintah yang
digunakan untuk mengeksekusi fungsi adalah “select
fcNamaMHS('Sholihun')”.
- 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);
- Triger
- Triger pada PostgresSQL
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.
- Triger pada MySQL
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.
Sintak :
CREATE
[DEFINER = { user | CURRENT_USER }]
TRIGGER
trigger_name trigger_time trigger_event
ON tbl_name FOR
EACH ROW trigger_stmt
Keterangan :
- [DEFINER = { user | CURRENT_USER }]: Definisi user yang sedang aktif, sifatnya opsional.
- trigger_name: Nama trigger.
- trigger_time: waktu menjalankan trigger. Ini dapat berupa BEFORE atau AFTER.
- BEFORE: Membuat trigger diaktifkan sebelum dihubungkan dengan suatu operasi.
- AFTER: Membuat trigger diaktifkan setelah dihubungkan dengan suatu operasi.
- trigger_event: berupa kejadian yang akan dijalankan trigger.
- trigger_event dapat berupa salah satu dari berikut:
- INSERT : trigger diaktifkan ketika sebuah record baru disisipkan ke dalam tabel. Contoh: statemen INSERT, LOAD DATA, dan REPLACE.
- UPDATE : trigger diaktifkan ketika sebuah record dimodifikasi. Contoh: statemen UPDATE.
- DELETE : trigger diaktifkan ketika sebuah record dihapus. Contoh: statemen DELETE dan REPLACE. Catatan : trigger_event tidak merepresentasikan statemen SQL yang diaktifkan trigger sebagai suatu operasi tabel. Sebagai contoh, trigger BEFORE untuk INSERT akan diaktifkan tidak hanya oleh statemen INSERT tetapi juga statemen LOAD DATA.
- tbl_name: Nama tabel yang berasosiasi dengan trigger.
- trigger_stmt: Statemen (tunggal atau jamak) yang akan dijalankan ketika trigger aktif.
Contoh yang
akan dibahas adalah mencatat kejadian-kejadian yang terjadi beserta
waktunya pada tabel mahasiswa, dan catatan-catatan tadi disimpan
dalam tabel yang lain, misal log_mhs. Misalkan struktur tabel log_mhs
adalah sebagai berikut.
mysql>
describe log_mhs;
Contoh 1:
mysql>
create trigger ins_mhs after insert on mahasiswa
-> for each row insert into log_mhs values('Tambah data',now());
mysql>
insert into mahasiswa values('00631','Hanif','Kalasan','P01');
mysql>
select * from log_mhs;
++tri2
Dari contoh
diatas dapat dilihat bahwa ketika satu record pada tabel mahasiswa
disisipkan (insert), maka secara otomatis tabel log_mhs akan
disisipkan satu record, yaitu kejadian ‘Tambah data’ dan waktu
saat record pada tabel mahasiswa disisipkan.
- HASIL PRAKTIKUM
- Buatlah fungsi konversi suhu dari Fahrenheit ke derajat celcius dengan rumus konversi sebagai berikut : celcius=5/9 * (nilai Fahrenheit-32) Kemudian jalankan hasilnya dengan menSELECT fungsi tersebut !
- Buatlah fungsi untuk mencari alamat mahasiswa dari tabel mahasiswa berdasarkan nama mahasiswa. Kemudian jalankan dengan perintah SELECT !
- Buatlah fungsi untuk menghitung nilai dengan menggunakan bahasa pl/sql !
Kemudian
jalankan hasilnya dengan menSELECT fungsi tersebut !
- Buatlah fungsi menggunakan pl/pgsql untuk mencari bilangan ganjil atau genap dari bilangan yang diinputkan. Kemudian jalankan dengan perintah SELECT !
- Tambahkan kolom modifikasi pada tabel mahasiswa. Dimana setiap ada insert atau update maka tanggal pada kolom modifikasi akan menunjukkan tanggal perubahan tersebut dilakukan.
namun Mysql yang saya gunakan tidak suport untuk menggunakan trigger lebih dari 1.
dan ini hasilnya,
- PERBEDAAN DBMS
- PostgresSQL
Perbedaan :
- Perlu menuliskan Bahasa yang dipakai seperti pl pgsql.
- Pada tipe Char atau Varchar tidak perklu diberi ukuran.
- Tentang percabangan, setelah query if, atau else if serta else maka menggunakan select into variable (values).
- MySQL
Perbedaan :
- Tidak perlu menuliskan Bahasa yang dipakai dan bisa langsung meng eksekusi query.
- Pada tipe Char atau Varchar tidak diberi ukuran jika tidak maka akan error.
- Perlu adanya concat (nilai kembalian di akhir query).
D. KESIMPULAN
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.
Semoga sedikit
ilmu yang kami tularkan nantinya akan dapat mempermudah pembaca dalam
memcahkan masalah-masalah atau mungkin tugas-tugas pembaca sekalian.
0 komentar:
Posting Komentar