LAPORAN DESAIN BASIS DATA VIII (FUNGSI DAN PL/SQL TRIGGER)

Alhamdulillah, segala puji bagi Allah yang telah merahmati kita hingga saat ini. Semoga Asisten Lab dengan ‘senang hati’ menilai laporan desain basis data yang ke 8, tentang FUNGSI DAN PL/PGSQL, yang mana laporan ini akan memperbandingkan antara PL/PGSQL dan PL/SQL pada MySQL. Oke, Bismillah.. Semoga Laporannya bisa menggunggah hati untuk dinilai dengan nilai yang sesuai dengan usaha dan hasilnya.

1.1 Maksud

- Pada bab kali ini akan membahas tentang fungsi

- Selain hal itu akan dibahas juga mengenai pl/pgsql dan triger

1.2 Tujuan

1. Mahasiswa memahami dan menggunakan fungsi dalam database PostgreSQL

2. Mahasiswa memahami dasar penggunaan PL/PGSQL dan TRIGER

LANDASAN TEORI

Baiklah, kita harus mengetahui dasar dasar ilmu tentang fungsi dan PL agar kita lebih memahami bagaimana kinerja dan sintaks PL dalam 2 DBMS. Karena PostgreSQL dan MySQL memiliki perbedaan sintaks yang sangat signifikan, tapi ga terlalu juga sih, tapi banyak perbedaannya, jadi saya tandai dengan (keterangan : biru PosgreSQL, hitam MySQL)

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[1] :

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
Apa sih fungsi itu? bukannya fungsi itu sesuatu yang berguna? ya, tidak lebih seperti itu, tapi kalau di 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.
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[1].

Dalam pembuatan fungsi, kita ga boleh sembarangan ternyata, ada susunannya, dan ada aturannya juga lo
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[1].

Agar lebih paham, lihat contoh fungsi daibawah ini
Berikut contoh sederhana pembuatan fungsi SQL untuk perkalian dari tiga inputan[1]:

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

Lalu kalau fungsi PL/PGSQL? lah bedanya sama tadi apa?
Ya beda, kalau di PL/PGSQL itu lebih kompleks, lebih panjang sintaksnya. hehe
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.
Mengapa menggunaka PL/PGSQL? berikut adalah keuntungan penggunaan Fungsi PL/PGSQL[1]:

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 ... );[2]

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[1]:

• 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.

Wew, panjang sekali keterangannya?
Langsung ke contoh aja ya

Berikut ini beberapa contoh penggunaan fungsi sebagai trigger procedure[1]:

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)

Wiih, berhasil juga ya memahami trigger
Jadi intinya itu triggger dibuat untuk merelasikan fungsi dengan database yang bersangkutan dengan insert update delete :)

Well, well, well, setelah keliling ria di PGSQL, kita beranjak ke MYSQL

PADA MYSQL

Stored Function

Dalam database server ada 2 ketegori fungsi[3]:
(1) fungsi aggregate adalah fungsi fungsi bawaan yang terdapat pada sistem database tersebut,
(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[3].

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

Yaitu fungsi yang memiliki parameter untuk pemanggilan fungsi pada select[3]

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′[3]

· Kemudian perintah ini akan dikombinasikan kedalam fungsi, misal nama fungsinya diberi nama get_nama(no_mhs);

Trigger sering digunakan, antara lain untuk[3]:

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[4]:

CREATE TRIGGER name

[BEFORE|AFTER] [INSERT|UPDATE|DELETE]

ON tablename

FOR EACH ROW statement

Keterangan dari bentuk umum perintah membuat trigger[4]:

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)[4].

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[4].

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[5];

Sebenarnya sama saja ya seperti di PGSQL, Hanya saja di Mysql itu pada penulisan trigger bisa langsung di masukkan fungsinya dan dalam penulisan function dan trigger harus diawali dengan Delimiter :)

HASIL PRAKTIKUM

1) 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 !

Hasil dari PostgreSQL

Pada PGSQL Untuk membuat fungsi merubah celcius menjadi farenheit, maka yang harus diperhatikan :

1. penamaan fungsi (setelah create function) pada kotak merah

2. parameter (berisikan float karena inputan saat diselect nanti boleh menggunakan angka desimal)

3. returns float (yaitu untuk nilai kembalian float)

4. selanjutnya kita menentukan bagian body yang merupakan rumus dari fungsi ini, yang dibungkus oleh select

5. harus di beri language, dan pada fungsi ini hanya menggunakan language sql karena hanya mendeklarasi 1 select

Hasil dari MySQL

Sedangkan pada mysql, banyak perbedaannya

1. Sebelum membuat fungsi (create function) harus dituliskan delimiter ini merupakan satu variable untuk penggunaan pl/sql pada mysql akan mengartikan akhir dari statement yaitu $$

2. penulisan selanjutnya sama dengan pgsql

2) Buatlah fungsi untuk mencari alamat mahasiswa dari tabel mahasiswa berdasarkan nama mahasiswa. Kemudian jalankan dengan perintah SELECT !

Hasil dari PostgreSQL

Untuk menjawab soal no.2, yang harus diperhatikan:

1. kaidah penulisan sama dengan nomer 1, hanya saja pada bagian body kita tambahkan declare karena fungsi ini membutuhkan lebih dari satu perintah

2. parameternya berubah jadi varchar karena inputannya adalah sebuah nama

3. return juga berubah jadi text, karna outputnya adalah nama alamat yang berupa text, sebenarnya nilai kembalian ini bisa juga menggunakan varchar

4. pada bagian body ini, declare berfungsi untuk pendeklarasian suatu variable yang merupakan output dari fungsi

5. setelah declare pasti ada bagian yang diawali oleh begin dan diakhiri end dan ada query yang di apit oleh keduanya.

6. pada kondisi where nama_mah=$1, itu menunjukkan bahwa mengacu pada parameternya

7. bahasa yang digunakan adalah plpgsql karena querynya ga dikit

Hasil dari MySQL

Kalau di mysql sama saja sintaksnya, hanya ada penambahan delimiter

3) Buatlah fungsi untuk menghitung nilai dengan menggunakan bahasa pl/sql !

Nilai > 100 atau Nilai<0 Nilai Salah

Nilai ≥ 90 Nilai A

70 ≤ Nilai < 90 Nilai B

60 ≤ Nilai < 70 Nilai C

50 ≤ Nilai < 60 Nilai D

0 ≤ Nilai < 50 Nilai E

Kemudian jalankan hasilnya dengan menSELECT fungsi tersebut !

Hasil dari PostgreSQL

Pada jawaban no.3 soal percabangan, banyak hal yang harus diperhatikan, diantaranya:

1. penulisan bagian-bagiannya sama dengan nomer 2, tetapi dengan parameter integer karena inputannya integer dan return text karena outpunya berupa text

2. pada percabangan yang diapit oleh begin dan end, terdapat if dan else if, yang mana else if tersebut dirubah menjadi elsif.

3. pada query percabangan juga bisa memakai when case

Hasil dari MySQL

Sedangkan di Mysql

1. penulisan tetap menggunakan delimiter

2. end if nya diulangi sebanyak if yang kita buat

4) Buatlah fungsi menggunakan pl/pgsql untuk mencari bilangan ganjil atau genap dari bilangan yang diinputkan. Kemudian jalankan dengan perintah SELECT !

Hasil dari PostgreSQL

Soal no.4

1. sebenarnya sama saja dengan no.3, hanya saja percabangannya hanya ada dua kemungkinan, jadi hanya menggunakan if dan else.

Hasil dari MySQL

Sedangkan pada mysql

1. % bisa dituliskan mod (modulo)

5) Tambahkan kolom modifikasi pada tabel mahasiswa. Dimana setiap ada insert atau update maka tanggal pada kolom modifikasi akan menunjukkan tanggal perubahan tersebut dilakukan.

Hasil dari PostgreSQL

soal no.5 dengan pembuatan trigger

1. pertama-tama kita harus membuat fungsinya terlebih dahulu

2. parameter pada fungsi ini dikosongkan karena fungsi tersebut akan dipanggil setiap saat secara otomatis

3. tipe opaque adalah sebuah tipe yang menginfokan pada database bahwa fungsi terebut tidak menghasilkan satu tipe

4. penggunaan new merupakan suatu variable yang berisi nilai baru suatu record pada saat insert dan update

5. kemudian bentuk triggernya dengan mengexecute nama fungsi yang telah dibuat

Hasil dari MySQL

Jika di Mysql

1. didalam trigger bisa langsung diberikan fungsi dan tidak perlu tipe data opaque

2. juga pada insert on, otomatis bisa update datanya

3. untuk menselect, kita perlu menulisakan nama kolom terlebih dahulu karena kalo ga gitu kolom akan berisi null

4. dan jika insert manual, maka akan terjadi error

PERBANDINGAN 2 DBMS

Perintah	PGSQL	MYSQL
Membuat Fungsi	CREATE [OR REPLACE] FUNCTION nama_fungsi ( argtype , ... ]) RETURNS tipe_data AS ‘definisi’ LANGUAGE ‘plpgsql’;	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 ;
Membuat Trigger	CREATE TRIGGER tes1_trigger BEFORE INSERT ON anggota FOR EACH ROW	DELIMITER $$ CREATE TRIGGER nama_trigger [BEFORE/AFTER] [INSERT/UPDATE/DELETE] ON nama_table FOR EACH ROW BEGIN //defisi trigger END; $$ DELIMITER;

Kekurangan
Function PL/SQL		Trigger
PostgreSQL	MySQL	PostgreSQL	MySQL
Perlu menuliskan Language nya	Perlu menuliskan Delimiter	Menuliskan function terlebih dahulu sebelum melakukan trigger	Tetap perlu menggunakan delimiter

Kelebihan
Function PL/SQL		Trigger
PostgreSQL	MySQL	PostgreSQL	MySQL
Bisa menggunakan 2 language (pl/sql dan sql) sesuai kondisi	Konsisten terdapat begin, return dan end	Beberapa fungsi dapat digunakan di 1 trigger saja	Fungsi sudah dapat dituliskan pada pembuatan trigger

KESIMPULAN

Mengapa memakai PL/SQL dan Trigger ? Jawabannya, karena jika suatu database yang besar (dimisalkan siakad), tidak perlulah kita menuliskan sintaks sebanyak banyaknya untuk memanipulasi tabel dan juga dapat meningkatkan keamanan database tersebut

KRITIK, SARAN DAN MANFAAT

Pembelajaran ini sangat bermanfaat dalam pengaplikasian dalam sebuah program maupun sehari-hari. Jadi masih banyak fungsi yang harus kita ketahui seperti bertambahnya ALIAS, atau yang lainnya.

DAFTAR PUSTAKA
Mustofa, Aziz. Praktikum Desain Basis Data Bab VIII Fungsi dan PL/PGSQL 2013

http:// Badiyanto's_Blog.blogspot.com/Tutorial/MySQL

http://87wz.blogspot.com/2013/12/trigger-pada-mysql.html

[1] Mustofa, Aziz. Praktikum Desain Basis Data Bab VIII Fungsi dan PL/PGSQL 2013

[2] Mustofa, Aziz. Praktikum Desain Basis Data Bab VIII Fungsi dan PL/PGSQL 2013

[3] http:// Badiyanto's_Blog.blogspot.com/Tutorial/MySQL

[4] http://87wz.blogspot.com/2013/12/trigger-pada-mysql.html

[5] Ibid.,

Pages