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

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 :

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.

Ketika menggunakan multi koneksi database yang berbeda-beda, maka dengan store procedure akan memberikan performa yang sama.

Tentu saja lebih aman. Karena programmer aplikasi tidak perlu mengetahui daftar tabel, dsb. Programmer aplikasi hanya memakai procedure yang telah disiapkan oleh Database Engineer.

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

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

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

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.

OPTIMASI QUERY

Teknik optimasi dapat dilakukan dengan beberapa cara. Terdapat dua pendekatan optimasi yang umum dipergunakan sebagaimana yang telah dikemukakan oleh Chanowich (2001), yakni:

a. Heuristik atau rule-based

Teknik ini mengaplikasikan aturan heuristik untuk mempercepat proses query. Optimasi jenis ini mentransformasikan query dengan sejumlah aturan yang akan meningkatkan kinerja eksekusi, yakni:

melakukan operasi selection di awal untuk mereduksi jumlah baris, melakukan operasi projection di awal untuk mengurangi jumlah atribut, mengkonversikan query dengan banyak join menjadi query dengan banyak subquery, dan

melakukan operasi selection dan join yang paling kecil keluarannya sebelum operasi yang lain.

b. Cost-based

Teknik ini mengestimasikan cost yang dipergunakan dari beberapa alternatif untuk kemudian dipilih salah satu yang memiliki cost terendah. Teknik ini mengoptimalkan urutan join terbaik yang dimungkinkan pada relasi-relasi r1 r2 . . . rn. Teknik ini dipergunakan untuk mendapatkan pohon left-deep join yang akan menghasilkan sebuah relasi sebenarnya pada node sebelah kanan yang bukan hasil dari sebuah intermediate join .

Obyektif :

1 Mengetahui konsep dasar replikasi

2 Mengetahui bagaimana merencanakan replikasi

3 Mengetahui bagaimana proses replikasi terjadi

4 Mengetahui alat yang digunakan

AS/400 hal. B.126

TSI Perbankan

4.1. KONSEP DASAR REPLIKASI

Replikasi adalah suatu teknik untuk melakukan copy dan pendistribusian data dan objek-objek database dari satu database ke database lain dan melaksanakan sinkronisasi antara database sehingga konsistensi data dapat terjamin.

Dengan menggunakan teknik replikasi ini, data dapat didistribusikan ke lokasi yang berbeda melalui koneksi jaringan lokal maupun internet. Replikasi juga memungkinkan untuk mendukung kinerja aplikasi, penyebaran data fisik sesuai dengan penggunaannya, seperti pemrosesan transaksi online dan DSS (Desiscion Support System) atau pemrosessan database terdistribusi melalui beberapa server.

Keuntungan replikasi tergantung dari jenis replikasi tetapi pada umumnya replikasi mendukung ketersediaan data setiap waktu dan dimanapun diperlukan.

Adapun keuntungan lainnya adalah :

1. Memungkinkan beberapa lokasi menyimpan data yang sama. Hal ini sangat berguna pada saat lokasi-lokasi tersebut membutuhkan data yang sama atau memerlukan server yang terpisah dalam pembuatan aplikasi laporan.

2. Aplikasi transaksi online terpisah dari aplikasi pembacaan seperti proses analisis database secara online, data smarts atau data warehouse.

3. Memungkinkan otonomi yang besar. Pengguna dapat bekerja dengan meng-copy data pada saat tidak terkoneksi kemudian melakukan perubahan untuk dibuat database baru pada saat terkoneksi

4. Data dapat ditampilkan seperti layaknya melihat data tersebut dengan menggunakan aplikasi berbasis Web

5. Meningkatkan kinerja pembacaan

6. Membawa data mendekati lokasi individu atau kelompok pengguna. Hal ini akan membantu mengurangi masalah karena modifikasi data dan pemrosesan query yang dilakukan oleh banyak pengguna karena data dapat

didistribusikan melalui jaringan dan data dapat dibagi berdasarkan kebutuhan masing-masing unit atau pengguna.

7. Penggunaan replikasi sebagai bagian dari strategi standby server.

Replikasi dapat digunakan apabila sebuah organisasi atau perusahaan didukung oleh hardware dan aplikasi sofware dalam sebuah sistem yang terdistribusi. Aplikasi yang berbeda mempunyai kebutuhan yang berbeda untuk otonomi dan konsistensi data. Replikasi diperlukan dalam sistem terdistibusi apabila berikut ini:

1. Mengcopy dan mendistribusikan data dari satu atau lebih lokasi

2. Mendistribusikan hasil copy data berdasarkan jadwal

3. Mendistribusikan perubahan data ke server lain

4. Memungkinkan beberapa pengguna di beberapa lokasi untuk melakukan perubahan dan kemudian menggabungkan data yang telah dimodifikasi

5. Membangun aplikasi data yang menggunakan perlengkapan online maupun offline

6. Membangun aplikasi Web sehingga pengguna dapat melihat volume data yang besar.

4.2. MERENCANAKAN REPLIKASI

Perencanaan yang baik sebelum replikasi dapat memaksimalkan konsistensi data, meminimalkan kebutuhan jaringan dan menghindari beberapa masalah.

Beberapa hal yang menjadi pertimbangan dalam perencanaan replikasi :

1. Kebutuhan data yang akan diubah dan siapa yang mengubah

2. Pendistribusian data memerlukan konsistensi, otonomi dan kesinambungan

3. Kelengkapan replikasi yang meliputi kebutuhan user, infra struktur teknik, jaringan dan keamanan serta karakteristik data

4. Jenis replikasi dan pilihannya

5. Topologi replikasi dan bagaimana mewujudkannya agar sesuai dengan jenis replikasi

Jenis – jenis Replikasi

1. Snapshot replication

Mendistribusikan data yang dapat dilihat pada saat tertentu tanpa melakukan update. Biasanya digunakan pada saat memerlukan tampilan data seperti : daftar harga, katalog, data yang digunakan untuk pengambilan keputusan. Data-data ini sifatnya hanya ‘read only’.

Replikasi ini membantu pada saat :

• data sebagian besar statis dan tidak sering berubah

• dapat menerima copy data yang telah melewati batas waktu yang ditentukan

• datanya sedikit

2. Transactional replication

Memelihara kekonsistenan transaksi yang terjadi

3. Merge replication

Merge replication memungkinkan pengguna bekerja dan merubah data sesuai dengan wewenangnya. Pada saat server tidak dikoneksikan ke seluruh lokasi dalam topologi, replikasi merubah ke nilai data yang sama.

4.3. PROSES REPLIKASI PADA IBM AS/400

Replikasi data terdiri dari 2 proses dasar, yaitu :

Menampung perubahan data dari tabel atau view dalam database sumber •

•

Meng-copy perubahan data dari tabel sumber ke satu atau beberapa tabel tujuan dalam database yang sama atau berbeda

Replikasi menentukan bagaimana perubahan data yang ditangkap dan bagai-mana data dicopy ke tujuan. Replication administration merupakan proses dari

AS/400 hal. B.129

TSI Perbankan

pembuatan dan mengelola kedua proses di atas. Replication Administration meliputi :

Pendefinisikan tabel, database atau view sebagai tabel sumber untuk merubah penangkapan dan replikasi

Pembuat sebuah tabel replikasi tujuan

Pendefinisian kondisi dimana data dari sumber yang telah ditentukan dicopy ke tujuan yang telah ditentukan

Perubah penangkapan dan replikasi

Pembuat sebuah tabel replikasi tujuan

Pendefinisian kondisi dimana data dari sumber yang telah ditentukan dicopy ke tujuan yang telah ditentukan

Perencanaan dilakukan sebelum melakukan setting perlengkapan. Pada tahap ini ditentukan skenario replikasi yang telah diimplementasikan oleh orang lain dan merencanakan pada tingat sistem dan aplikasi.

Perencanaan sistem akan menentukan kelayakan implementasi replikasi. Kelayakan ini ditentukan oleh kebutuhan sistem, seperti produk replikasi yang digunakan, kebutuhan software dan hardware, kebutuhan storage, kapasitas CPU, konfigurasi jaringan, autoritas keamanan dan user ID.

Perencanaan aplikasi akan mengevaluasi kebutuhan tingkat aplikasi terhadap kemampuan berbagai produk. Istilah kebutuhan tingkat aplikasi menunjukkan kebutuhan aplikasi tujuan, terutama kebutuhan jenis data dalam tabel tujuan (meliputi bagian dari setting, transformasi atau peningkatan terhadap data sumber), peredaran data hasil copy dan kekonsistenan data yang dicopy.

4.4. ALAT YANG DIGUNAKAN

Replikasi terdiri dari administrasi utama replikasi yaitu Control Center, Capture dan Apply Program. Control Center adalah alat administrasi database DB2 yang

AS/400 hal. B.130

TSI Perbankan

digunakan untuk administrasi replikasi. Control Center secara otomatis menge-nali fungsi seperti pembuatan tabel tujuan dan tabel kontrol pada saat menentukan informasi hasil copy tujuan. Capture Program merupakan alat replikasi yang merubah data dan membuat perubahan itu dapat dilakukan pada replikasi. Capture program berjalan pada database server tujuan.

Capture & Apply Program menangkap perubahan yang terjadi pada data dalam tabel sumber replikasi dengan cara membaca database log atau journal.

Administrasi replikasi mempunyai 2 tugas utama yaitu mendefinisikan sumber replikasi dan menentukan tujuan replikasi. Sumber replikasi berupa tabel atau view digunakan sebagai sumber untuk copy data ke satu atau beberapa tabel tujuan. Tujuan replikasi merupakan spesifikasi dari satu atau beberapa tabel tujuan beserta lokasinya, struktur dan waktu penjadwalan sebaik peningkatan SQL yang diperlukan.

Langkah praktis melakukan replikasi :

1. Merencanakan skenario replikasi pada tingkat sistem dan aplikasi

2. Mendefinisikan sumber replikasi

3. Mendefinisikan tujuan replikasi

4. Melakukan konfigurasi Capture dan Apply Program

5. Memulai Capture Program

6. Memulai Apply Program

STORE PROCEDURE DI MYSQL 5

Dalam membuat aplikasi database MySQL 5, kita bisa meletakkan fungsi-fungsi yang merupakan bisnis prosesnya dengan 2 pilihan

Meletakkan fungsi-fungsi bisnis di dalam bahasa programming yang digunakan

Cara ini mudah diimplementasikan. Untuk aplikasi yang sederhana lebih baik memakai metode ini.

Untuk aplikasi yang kompleks, cara ini memiliki kelemahan:

a. Jika processing banyak terjadi, performance akan turun

b. Jika ada pengembangan lain dan memakai bahasa pemrograman lain, maka fungsi bisnis harus ditulis ulang

c. Harus jeli mengenai konsistansi update suatu table yang mempunyai hubungan dengan tabel lain

Meletakkan fungsi-fungsi bisnis di dalam database sebagai store procedure, trigger, view dan object database yang lain

Peletakan fungsi bisnis di dalam store procedure dan trigger mempunyai keuntungan

a. Performance cepat

b. Konsistensi data table akan terjaga

c. Jika ada pengembangan lain yang memakai bahasa pemrograman berbeda, tidak perlu menulis ulang fungsi-fungsi bisnis tersebut

Tabel Sales Order

Sebagai contoh kita akan membuat tabel sales order dan diisi dengan sebuah data:

CREATE TABLE sales_order(

customer_name VARCHAR( 50 ) NOT NULL ,

total_price DECIMAL( 9, 0 ) NOT NULL DEFAULT '0',

total_hpp DECIMAL( 9, 0 ) NOT NULL DEFAULT '0',

profit DECIMAL( 9, 0 ) NOT NULL DEFAULT '0',

so_id INT NOT NULL AUTO_INCREMENT ,

PRIMARY KEY (so_id)

) ENGINE = InnoDB;

INSERT INTO sales_order (customer_name,so_id) VALUES ('Serba Prima', 1);

Hasilnya bisa dilihat di phpmyadmin :

http://www.proweb.co.id/pic/135-soawal.gif

Tabel Sales Item

Kemudian kita akan membuat tabel sales_item diisi dengan 2 data:

CREATE TABLE sales_item(

so_id INT NOT NULL ,

item_name VARCHAR( 100 ) NOT NULL ,

item_price DECIMAL( 8, 0 ) NOT NULL ,

hpp DECIMAL( 8, 0 ) NOT NULL ,

item_id INT NOT NULL AUTO_INCREMENT ,

PRIMARY KEY (item_id)

) ENGINE = InnoDB;

INSERT INTO sales_item(so_id,item_name,item_price,hpp) VALUES (1, 'Keyboard', 100000,85000);

INSERT INTO sales_item(so_id,item_name,item_price,hpp) VALUES (1, 'Mouse', 50000,40000);

Hasilnya :

http://www.proweb.co.id/pic/135-so_item.gif

Store Procedure Resume Sales Order

Kemudian Store Procedurenya

CREATE PROCEDURE resume_sales_order (IN p_so_id INT)

BEGIN

    DECLARE v_total_price DECIMAL;

    DECLARE v_total_hpp DECIMAL;

    SELECT SUM(item_price),SUM(hpp) INTO v_total_price,v_total_hpp FROM sales_item WHERE so_id=p_so_id;

    UPDATE sales_order SET total_price=v_total_price,total_hpp=v_total_hpp,profit=v_total_price-v_total_hpp WHERE so_id=p_so_id;

END

//

Untuk membuatnya pastikan delimiternya //

http://www.proweb.co.id/pic/135-create_sp.gif

Menjalankan Store Procedure

Untuk menjalankan store procedurnya dengan menjalankan 

CALL resume_sales_order(1) 

dan isi tabel sales_order akan menjadi :

http://www.proweb.co.id/pic/135-hasil_sp.gif

Di sini terlihat total_pricenya berubah dari 0 menjadi 150000, total_hpp menjadi 12500 dan profit menjadi 25000.

Langkah berikutnya adalah membuat Trigger di MySQL 5 .

Sumber : http://www.rey1024.com/2008/05/pengenalan-trigger-function-procedure-dan-view-pada-mysql/

Sunber : www.proweb.co.id