Pengenalan
Trigger, Function, Procedure Dan View Pada Mysql
Berikut
adalah dasar-dasar trigger, function, procedure serta view yang bisa
diimplementasikan pada MYSQL 5 keatas. Dengan menggunakan trigger, function,
procedure serta view, pemanfaatan MYSQL akan lebih bermanfaat. Contohnya : kita
bisa membuat urutan langkah-langkah tertentu setelah suatu even dilakukan (ex:
insert, update, delete). Tutorial ini ditujukan bagi yang belum mengenal
trigger, function, procedure serta view sama sekali. Bagi yang sudah bisa,
artikel ini boleh dan harus diabaikan .
Syntak
untuk membuat procedure dalam MySQL adalah sebagai berikut.
CREATE
[DEFINER = { user | CURRENT_USER }]
PROCEDURE sp_name ([proc_parameter[,...]])
[characteristic
...] routine_body
CREATE
[DEFINER = { user | CURRENT_USER }]
FUNCTION sp_name ([func_parameter[,...]])
RETURNS type
[characteristic
...] routine_body
Keterangan
1.
proc_parameter (hanya pada procedure):
[ IN |
OUT | INOUT ] param_name type
- IN
parameter dilewatkan ke dalam procedure tetapi modifikasi nilai dari
parameter ini tidak kelihatan setelah procedure tersebut dipanggil.
- Out
parameter merupakan parameter yang dilewatkan dari dalam procedure ke
pemanggil procedure tersebut. Nilai inisialisasi saat memanggil adalah
null dan hasil parameter akan kelihatan setelah procedure dipanggil.
- INOUT
parameter diinisilaisai oleh pemanggil procedure, kemudian hasil
modifikasinya tersebut akan kelihatan setelah procedure tersebut
dipanggil.
2.
characteristic
LANGUAGE SQL
| [NOT]
DETERMINISTIC
| { CONTAINS SQL |
NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY {
DEFINER | INVOKER }
| COMMENT 'string'
3.
routine_body:
Valid
SQL procedure statement
Routine
body berisi syntax-syntax sql yang valid, seperti insert atau select. Bisa juga
berisi compound. Compount diapit oleh begin… end. Syntax dari compound system
adalah sebagai berikut.
[begin_label:]
BEGIN
[statement_list]
END [end_label]
Compound
ini bisa diisi dengan label. Begin label dan end label harus sama. Di dalam
compound juga bisa berisi deklarai variabel, looping atau kontrol program
lainnya. Setiap Function harus punya return value untuk mengembalikan nilai
yang dihasilkan
Berikut
adalah contoh penggunaan procedure. Pertama kita buat terlebih dahulu database
dengan nama adakara. Kemudian silakan buat table berikut dengan isinya. Berikut
ini adalah hasil dumping dari database adakara.
CREATE TABLE `siswa` (
`NIS` varchar(20)
collate latin1_general_ci NOT NULL,
`Nama` varchar(30)
collate latin1_general_ci NOT NULL,
`Angkatan`
varchar(9) collate latin1_general_ci NOT NULL,
PRIMARY KEY
(`NIS`)
) ENGINE=MyISAM DEFAULT
CHARSET=latin1 COLLATE=latin1_general_ci;
LOCK TABLES `siswa`
WRITE;
/*!40000 ALTER TABLE
`siswa` DISABLE KEYS */;
INSERT INTO `siswa`
VALUES ('1024','resika
arthana','2005/2006'),('1','cxfbsdjg','2005/2006'),('2','dshfk','2005/2006'),('3','dsfku','2005/2006'),('11','Ari
damayanti','2005/2006'),('12','wedana','2005/2006'),('122','andika','2005/2006'),('123','mas
ayu','2005/2006');
/*!40000 ALTER TABLE
`siswa` ENABLE KEYS */;
UNLOCK TABLES;
-- Dump completed on
2007-03-21 7:47:58
Berikut
ini adalah proses pembuatan procedure, dibuat dalam command from.
mysql> delimiter //
mysql> CREATE
PROCEDURE jumlahSiswa (OUT param1 INT)
-> BEGIN
-> SELECT COUNT(*) INTO param1 FROM siswa;
-> END;
-> //
Query OK, 0 rows affected
(0.00 sec)
mysql> delimiter ;
mysql>
create function tmbhSaudara(nama char(20)) returns char(5)
->
return concat('Saudara ',nama);
Query
OK, 0 rows affected (0.36 sec)
Pemanggilan
procedure menggunakan syntax
CALL procedure_atau_function_name([parameter[,...]])
procedure_atau_function_name adalah nama procedure ata fungsi yang dipanggil dan
parameter adalah nama-nama parameter procedure atau fungsi tersebut
mysql> call
jumlahSiswa(@a);
Query OK, 0 rows affected
(0.00 sec)
mysql> select @a;
+------+
| @a |
+------+
| 8 |
+------+
1 row in set (0.00 sec)
mysql>
select tmbhSaudara(' resika arthana');
+--------------------------------+
|
tmbhSaudara(' resika arthana') |
+--------------------------------+
|
Saudara resika arthana |
+--------------------------------+
1
row in set (0.00 sec)
Untuk
menghapus procedure atau function digunakan perintah sebagai berikut
DROP {PROCEDURE |
FUNCTION} [IF EXISTS] procedure_or_function_name
CREATE
[DEFINER = { user | CURRENT_USER }]
TRIGGER trigger_name trigger_time trigger_event
ON tbl_name
FOR EACH ROW trigger_stmt
Keterangan
- DEFINER
menunjukkan nama user yang mempunyai hak akses untuk mengakses triger.
- Triger_time
menunjukkan saat triger tersebut dijalankan. Terdiri dari Before atau After
- Triger_time
menandakan saat keadaan bagaimana triger tersebut aktif. Terdiri dari
- Insert
: Triger aktif saat baris baru dimasukkan ke dalam tabel
- Update
: Triger aktif saat ada baris diperbaharui dimasukkan di dalam tabel
- Delete
:Triger aktif saat baris dihapus dalam tabel
CREATE TABLE test1(a1
INT);
CREATE TABLE test2(a2
INT);
CREATE TABLE test3(a3 INT
NOT NULL AUTO_INCREMENT PRIMARY KEY);
CREATE TABLE test4(
a4 INT NOT NULL
AUTO_INCREMENT PRIMARY KEY,
b4 INT DEFAULT 0
);
DELIMITER |
CREATE TRIGGER testref
BEFORE INSERT ON test1
FOR EACH ROW BEGIN
INSERT
INTO test2 SET a2 = NEW.a1;
DELETE
FROM test3 WHERE a3 = NEW.a1;
UPDATE
test4 SET b4 = b4 + 1 WHERE a4 = NEW.a1;
END;
|
DELIMITER ;
INSERT INTO test3 (a3)
VALUES
(NULL), (NULL),
(NULL), (NULL), (NULL),
(NULL), (NULL),
(NULL), (NULL), (NULL);
INSERT INTO test4 (a4)
VALUES
(0), (0), (0),
(0), (0), (0), (0), (0), (0), (0);
Silakan
masukkan nilai ke test1
mysql> INSERT INTO
test1 VALUES
->
(1), (3), (1), (7), (1), (8), (4), (4);
Query OK, 8 rows affected
(0.01 sec)
Records: 8
Duplicates: 0 Warnings: 0
As
a result, the data in the four tables will be as follows:
mysql> SELECT *
FROM test1;
+------+
| a1 |
+------+
| 1 |
| 3 |
| 1 |
| 7 |
| 1 |
| 8 |
| 4 |
| 4 |
+------+
8 rows in set (0.00 sec)
mysql> SELECT *
FROM test2;
+------+
| a2 |
+------+
| 1 |
| 3 |
| 1 |
| 7 |
| 1 |
| 8 |
| 4 |
| 4 |
+------+
8 rows in set (0.00 sec)
mysql> SELECT *
FROM test3;
+----+
| a3 |
+----+
| 2 |
| 5 |
| 6 |
| 9 |
| 10 |
+----+
5 rows in set (0.00 sec)
mysql> SELECT *
FROM test4;
+----+------+
| a4 | b4 |
+----+------+
| 1
| 3 |
| 2
| 0 |
| 3
| 1 |
| 4
| 2 |
| 5
| 0 |
| 6
| 0 |
| 7
| 1 |
| 8
| 1 |
| 9
| 0 |
| 10 |
0 |
+----+------+
10 rows in set (0.00 sec)
Cara Replikasi Database
Di MySQL
Definisi
:
Replication
Database adalah suatu fitur teknologi pada dunia pemrograman database yang
memungkinkan suatu database untuk mereplikasi atau mengopy datanya ke database
lain secara otomatis. Secara sederhana replikasi database adalah proses automatik
perintah dari database satu ke database lain yang tentunya terhubung dalam
jaringan.
Kegunaan
:
Kegunaan
dari proses replikasi ini adalah bila suatu database satu mengalami kerusakan
maka secara otomatis semua data yang telah disimpan pada database satu dapat
dilihat kembali di database lain. Kalau pada pembahasan diatas yaitu Proses
Backup dan Restore agak kurang praktis, maka konsep inilah solusi keefektifan
dalam menjawab kekurangpraktisan kedua proses diatas. Konsep ini merupakan
konsep teknologi yang unik, karena secara otomatis segala perintah yang ada
pada database satu akan tersimpan pada database lain sehingga bila kehilangan
data pada database satu, database masih tersimpan rapih di database lain.
Praktik
Proses Replikasi :
Praktik
proses replikasi database ini saya lakukan dengan database MySQL. Pada MySQL
sendiri sudah mendukung konsep teknologi Replikasi namun tidak secara default
langsung terseting untuk melakukan proses replikasi. Kita mesti menyeting
terlebih dahulu agar bisa melakukan proses ini.
Satu
hal yang penting saat penyetingan proses Replikasi adalah sudah ada 2 komputer
yang terkoneksi / terhubung dan 2 komputer tersebut sudah ter-install MySQL
sebagai proses kegiatan database.
Dalam
proses replikasi database dikenal 2 server database atau 2 MySQL pada komputer
yaitu sebagai Master server dan Slave server. Master server adalah server yang
biasa dipergunakan untuk melakukan kegiatan managing database. Sedangkan Slave
Server adalah database yang bertindak sebagai pencatat sekaligus pereplikasi /
pengopian data setiap melakukan kegiatan managing database pada Master Server.
Slave Server memiliki alamat IP 192.168.1.1 sedangkan alamat Master Server
memiliki alamat IP 192.168.1.2
Sebelum
itu pula kita mesti membuat user pada Server Slave yang dapat diakses oleh
siapapun dalam jaringan manapun. Disini kami membuat user dengan nama tamu dan
password tamu serta user tamu ini memiliki akses GRANT ALL pada semua database.
[
gambar 1 ]
Bila
sudah, mari kita mulai menyeting konfigurasinya :
Pada
Komputer Master Server :
Pada
komputer Master Server cari file my.ini, biasanya selalu ada pada installan
MySQL kita. Kalau pada komputer kami berada pada path C:\Program
Files\MySQL\MySQL Server
5.1\my.ini. Bila sudah ditemukan, coba buka dengan notepad, lalu lihat
konfigurasinya.
Lalu
ketikan dibawah [mysqld] dengan :
log
log-bin=mysql-log.bin
binlog-do-db=nama_database_replication
server-id=1
Lengkapnya
bisa dilihat gambar dibawah ini :
[
gambar2 ]
Pada
gambar diatas, dataku adalah nama database yang akan dipergunakan untuk proses
replikasi, database yang akan secara otomatis disimpan pada Slave Server. Bila
sudah, simpan perubahan tersebut, lalu restart service mysql dengan cara
mematikan dan menghidupkan lagi servicenya.
[
gambar3 ]
Bila
sudah, masuk ke konsol mysql dengan user root lalu buat user yang akan
melakukan proses replikasi. Caranya dengan mengetikan perintah ini :
mysql>
grant replication slave on *.* to ‘tamu’@’192.168.1.1’ identified by ‘tamu’;
Bila
sudah refresh perubahan user baru dengan perintah :
mysql>
flush privileges
Bila
sudah merefress, coba lihat status Master dengan cara mengetikan perintah ini :
[
gambar4 ]
Pada
gambar, terdapat file log yang berisi perintah-perintah Master Server disimpan,
posisi Master Server dan Database yang di replikasi. Sampai sini, sekarang kita
setting di Slave Server.
Pada Komputer Slave Server :
Cari
file my.ini pada Komputer Slave Server. Sama seperti pada komputer
Master Server, file my.ini saya berada pada path C:\Program Files\MySQL\MySQL
Server 5.1\my.ini. Bila sudah buka dengan notepad lalu tambahkan dibawah
[mysqld] beberapa setingan dibawah ini :
server-id=2
master-host=IP-MASTER
SERVER
master-user=user_slave
master-password=passwd_slave
master-connect=60
replicate-do-db=nama_database_replication
Yang
terpenting adalah samakan nama database replikasinya dengan setingan pada
Master Server dan server-id mesti berbeda valuenya dengan server-id pada
setingan di Master Server.
Sesuaikan
juga user_slave_server dan passwordnya yang telah kita buat pada langkah
diatas.
Lengkapnya
bisa dilihat disetingan kami pada gambar dibawah ini:
[
gambar5 ]
Bila
sudah simpan perubahan pada file my.ini, lalu lakukan restart service mysqlnya
seperti pada gambar 2.
Silakan
di testing dengan membuat atau memodifikasi pada database yang dijadikan
replikasi.
REPLIKASI DATABASE
MYSQL
Replicate atau replikasi adalah suatu
teknik untuk melakukan copy dan pendistribusian data dan objek-objek database
ke database lain dan melaksanakan sinkronisasi antardatabase sehingga
konsistensi data dapat terjamin. Jadi, 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 (Decision Support System) atau pemrosesan database
terdistribusi melalui beberapa server.
Dengan menggunakan teknik ini,
kehandalan database akan lebih terjamin karena data dapat didisitribusikan ke
server-server yang lain. Misalnya seperti transaksi yang terjadi pada Bank, apa
yang akan terjadi jika Bank hanya memiliki sebuah database server, apabila pada
database server tersebut terjadi kerusakan atau padamnya arus listrik? Pasti
transaksi perbankan akan berhenti total. Hal ini tidak demikian terjadi jika
kita menggunakan database terdistribusi yang pada setiap transaksi juga
direplikasi ke server yang lain.
Terdapat beberapa jenis replikasi akan
tetapi yang akan kita bahas di sini hanya sebuah teknik replikasi sederhana
saja. Replikasi dapat dilakukan dengan jumlah komputer minimal dua buah, satu
buah master dan satu buah slave. Komputer slave akan mengkopi secara otomatis
setiap perubahan database yang terjadi pada komputer master. Cara membuat
replikasi MySQL di sini hanya bisa digunakan untuk MySQL versi 5.1.x atau di
bawah ver si 5.2.x. Jika anda menggunakan MySQL versi yang lebih baru, ada
kemungkinan gagal. Karena jika menggunakan MySQL versi 5.5.x cara ini tidak
berhasil.
Langkah-langkahnya adalah sebagai
berikut:
1. KONFIGURASI KOMPUTER DATABASE MASTER
a. Menyeting IP ADDRESS: 192.168.2.2
pada komputer Master.
b. Setelah itu buatlah sebuah database
dengan nama sekolah, sebuah tabel dengan nama siswa dan isikan dua buah data
atau terserah pada tabel tersebut.
c. Matikan service MySQL, buka teks
editor misalnya: Notepad++ dan buka file yang terletak pada direktori
mysql\bin\my.cnf. Jika anda menggunakan XAMPP seperti saya, anda bisa membuka
file tersebut di direktori C:\xampp\mysql\bin\my.cnf
d. Tepat di bawah section [mysqld]
tambahkan baris berikut
log-bin= mysql-bin server-id= 1
binlog-do-db= sekolah log-slave-updates
e. Kemudian buatlah akses user slave ke
database master, misalnya dengan nama slave_user dengan perintah sebagai
berikut mysql> grant replication slave on *.* to ‘slave_user’@’%’ identified
by ‘asdf’; (asdf adalah passwordnya) mysql> flush privileges;
f. Kemudian berikan hak akses kepada
user tersebut agar dapat menggunakan database pada master, dengan perintah
sebagai berikut
mysql> grant all on *.* to
‘slave_user’@’%’ identified by ‘asdf’;
mysql> flush privileges;
g. Lihat status log pada master, tetapi
sebelumnya lock dulu tabelnya, perintahnya sebagai berikut mysql> flush
tables with read lock;
mysql> show master status; Pada
table akan terlihat File dan Position catat baik-baik nama file dan posisinya.
Kemudian ketikkan perintah untuk meng-unlock tabel kembali mysql> unlock
table;
2. KONFIGURASI KOMPUTER DATABASE SLAVE
a. Menyeting IP ADDRESS: 192.168.2.3
pada komputer slave.
b. Menyeting my.cnf pada databse slave,
tepat di bawah section [mysqld] tambahkan baris: server-id= 2 master-host=
192.168.2.2 master-user= slave_user master-password= asdf master-connect-retry=
30 replicate-do-db= sekolah
c. Start service MySQL dan copykan
database yang terdapat di master dengan perintah mysql> load data from
master;
d. setelah berhasil maka database
master (sekolah) akan terkopi semua ke slave.
e. Kemudian melakukan setting replikasi
pada slave. Sebelum melakakukan setting replikasi pada slave sebaiknya
menonaktifkan slave terlebih dahulu dengan perintah mysql> slave stop;
mysql> change master to master_host=’192.168.2.2′, -> master_user=’slave_user’,
-> master_password=’asdf’, -> master_log_file=’mysql-bin.000001′, <-
isikan sesuai dengan file log masternya -> master_log_pos=106; <- isikan
sesuai dengan posisi log masternya. (baca kembali langkah 1 g.)
f. Aktifkan slave dengan perintah
mysql> slave start;
g. Cek status slave dengan perintah
mysql> show slave status\G; dan melihat pada slave_sql_running = yes,
berarti slave telah berjalan dengan baik.
Setelah selesai langkah-langkah
tersebut di atas dan tidak terdapat kendala, sekarang saatnya menguji apakah
replicate telah berjalan dengan baik, yaitu dengan mengubah, menambah atau
menghapus tabel siswa pada database sekolah di komputer master, lalu cek pada
komputer slave apakah datanya juga ikut berubah seperti yang terjadi di
komputer master? Jika ya, maka anda telah berhasil membuat replikasinya.
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 :
- 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
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.
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 :
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
:
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 //
Menjalankan
Store Procedure
Untuk menjalankan store procedurnya dengan menjalankan
CALL resume_sales_order(1)
dan isi tabel sales_order akan menjadi :
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
.