Store Procedure dalam MySQL

Stored procedures can provide improved performance because less information needs to be sent between the server and the client. The tradeoff is that this does increase the load on the database server system because more of the work is done on the server side and less is done on the client (application) side

Sebenarnya Sabtu kemarin saya menemukan (kembali) buku yang sesungguhnya sudah lama saya beli, tetapi belum selesai dibaca. Biasa, semangat untuk beli buku kadang tidak dibarengi dengan semangat untuk menyelesaikan membacanya, hehe. Menarik sekali – koq baru sadar ya –buku ini membahas tentang pemasaran, hal yang relatif baru bagi saya. Berjudul “The END of MARKETING as we know it MATINYA PEMASARAN” karya Sergio Zyman. Saya fikir perusahaan tempat saya bekerja sekarang sudah menerapkan beberapa hal dalam buku ini.

Tetapi dalam tulisan kali ini saya belum ingin membahas tentang itu. Saat ini saya juga baru tertarik dengan ‘mainan baru’ store procedure di dalam sistem database. Ini karena pekerjaan terakhir memang baru bersentuhan dengan store procedure dengan oracle sebagai databasenya. Terus terang selama ini saya menggunakan database hanya untuk penyimpanan data saja. Dengan demikian ada kekuatan oracle yang belum termanfaatkan, bahkan menjadi mubadzir. Padahal kita tahu bahwa kemubadziran adalah temanna Mr Satan, hehe. Tetapi (lagi-lagi) saya tidak ingin membahas store procedure dalam Oracle, saat ini saya ingin membahas store procedure dalam MySQL.

Bagan Database Kasir

Gambar di atas adalah database kasir yang saya punya, kalau tanda plus pada Tables diklik akan terlihat daftar tabel yang ada, namun Views sampai Triggers belum ada isinya sama sekali. Inilah kemubadziran itu…

Namun apa yang membuat store procedure menjadi menarik ? Apa sih keuntungan-keuntungan penggunaan store procedure ini, setidaknya ada beberapa hal yang bisa dicatat :

1. Menghilangkan kemubadziran. Seperti sudah saya ungapkan di atas. Oracle, Postgree dan tentu saja MySQL (versi 5.0 ke atas) sudah mendukung adanya Store Procedure, sungguh sayang jika kita lewatkan begitu saja. Kan yo kasihan pembuatnya sudah susah payah menyediakan fitur, jika tidak dimanfaatkan.
2. Ketika menggunakan multi koneksi database yang berbeda-beda, maka dengan store procedure akan memberikan performa yang sama.
3. Tentu saja lebih aman. Karena programmer aplikasi tidak perlu mengetahui daftar tabel, dsb. Programmer aplikasi hanya memakai procedure yang telah disiapkan oleh Database Engineer.
4. Meningkatkan performa. Karena akan meminimalkan komunikasi antara server (database) dengan client (aplikasi PHP).

Mungkin saja masih ada atau mungkin malah banyak keuntungan lainnya. Tetapi daftar di atas bagi saya sudah cukup alasan untuk mengeksplore dan menggunakan store procedure di aplikasi yang dibangun untuk ke depannya.

Dalam framework yang saya gunakan, GTFW dan CI berbasis MVC, dengan pemanfaatan Store Procedure ini saya fikir memungkinkan bagi kita untuk menghilangkan Model nya dan kita tarik dalam sistem database kita. Jadi aplikasi tinggal mengurusi tentang View dan Controller nya saja. Oks, mari kita mulai pembuatan store procedurenya. Dalam kasus ini saya ingin menyimpan store procedure untuk menampilkan data, yang sebelumnya berada di model dalam CI :

SELECT *

FROM kategori

WHERE KategoriNama like ? or KategoriKode like ?

ORDER BY KategoriNama

LIMIT ?, ?

Saya coba procedure-kan query di atas dengan menjalankan code

DELIMITER $$

CREATE PROCEDURE GetKategori(IN key_kat varchar(15),IN awal int,IN jumlah int)

BEGIN

SELECT *

FROM kategori

WHERE KategoriNama like key_kat or KategoriKode like key_kat

ORDER BY KategoriNama

LIMIT awal, jumlah

END$$

DELIMITER ;

Yes, langsung ketemu bug error. Dengan sukses mengeluarkan pesan error :

Error Code : 1064

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘awal, jumlah END’ at line 12 (0 ms taken)

Wew, kenapa pula ini, langsung deh tanya ke Pakde Google dan ternyata tersangkut ke-http://bugs.mysql.com/bug.php?id=11918 Baca-baca, dan mengejutkan, ternyata si MySQL belum bisa menggunakan limit dalam procedure-nya. Posisi terahir tentang isyu limit ini adalah

[20 Jan 2:55] Razvan Marescu

I just created a SP that was supposed to use this feature, but it seems that hasn’t been implemented (5.1.42-community-log).

Could we please get a status update on this request? Is there any chance to be implemented?

Thanks

Jadi, apakah ini berhubungan dengan terbelinya MySQL oleh perusahaan Oracle ? Kita tahu dalam query Oracle tidak ada fasilitas limit seperti halnya pada MySQL. Wallahu ‘alam.

Tetapi ternyata di forum tersebut diberikan penyelesaian atas masalah ini, walaupun terlihat menjadi lebih ribet. Untuk kasus di atas procedure-nya menjadi :

DELIMITER $$

DROP PROCEDURE IF EXISTS `kasir`.`GetKategori`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `GetKategori`(IN key_kat varchar(15),IN awal int,IN banyak int)

BEGIN

PREPARE STMT FROM

“SELECT *

FROM kategori

WHERE KategoriNama like ? or KategoriKode like ?

ORDER BY KategoriNama

LIMIT ?,?”;

SET @KEY = key_kat;

SET @START = awal;

SET @LIMIT = banyak;

EXECUTE STMT USING @KEY,@KEY,@START, @LIMIT;

END$$

DELIMITER ;

Dan saatnya untuk mencoba memanggil procedure di atas

call GetKategori(‘%agama%’,0,2)

Ternyata bisa, dan menghasilkan :

Menjalankan Procedure GetKategori

Di atas adalah procedure untuk menampilkan data, sekarang mari kita coba dengan procedure untuk memasukkan data. Jika hal ini bekerja, maka saya rasa menjadi tidak masalah untuk operasi update dan delete-nya. Dan inilah code pembuatan procedure-nya.

DELIMITER $$

CREATE PROCEDURE GetKategorii(IN nama varchar(15),IN kode varchar(5),IN isPpn char(1))

BEGIN

INSERT INTO kategori (KategoriNama,KategoriKode,KategoriIsPpn)

VALUES (nama,kode,isPpn);

END$$

DELIMITER ;

Berhasil.

List SP

Dan coba kita masukkan data kategori baru dengan procedure ini.

CALL DoAddKategori(‘pemasaran’,’021′,’0′);

Coba kita lihat apakah datanya dapat masuk dengan sukses ?

Hasil pemasukan data dari SP

Yes, berhasil. Tetapi tentu saja ini tidak cukup, jika hanya dapat dijalankan dari aplikasi SQL client. Maka saatnya mencari cara agar dapat dijalankan dari PHP dengan framework CI. Hmm, tapi koq tulisan ini saja sudah terlihat panjang ya ? Oks kalo begitu, penerapan procedure di PHP-nya menyusul saja nantinya, Insyaallah…

* Delimiter adalah karakter atau string yang memberi tahu MySQL bahwa kita telah selesai menulis statemen SQL. Ini diperlukan karena stored procedure dapat terdiri atas sejumlah statemen, dan setiap statemen harus diakhiri dengan semicolon.