Petunjuk Praktis (Tidak Menyelam Terlalu Dalam) MySQL

Pada tulisan kali ini, akan di bahas sekilas tentang tipe data dan operator. Serta akan dibahas lebih dalam mengenai kontrol alir (flow control), view, rutin tersimpan (stored routine) dan pemicu (trigger). Manual ini berdasarkan manual yang dikeluarkan oleh MySQL versi 5.4.

MySQL adalah sebuah perangkat lunak sistem manajemen basis data SQL atau DBMS yang multithread, multi-user, dengan sekitar 6 juta instalasi di seluruh dunia. MySQL AB membuat MySQL tersedia sebagai perangkat lunak gratis dibawah lisensi GNU General Public License (GPL), tetapi mereka juga menjual dibawah lisensi komersial untuk kasus-kasus dimana penggunaannya tidak cocok dengan penggunaan GPL.

Tidak sama dengan proyek-proyek seperti Apache, dimana perangkat lunak dikembangkan oleh komunitas umum, dan hak cipta untuk kode sumber dimiliki oleh penulisnya masing-masing, MySQL dimiliki dan disponsori oleh sebuah perusahaan komersial Swedia MySQL AB, dimana memegang hak cipta hampir atas semua kode sumbernya. Kedua orang Swedia dan satu orang Finlandia yang mendirikan MySQL AB adalah: David Axmark, Allan Larsson, dan Michael "Monty" Widenius.

Tipe Data
Tipe data yang disediakan oleh MySQL terhitung banyak dan cukup lengkap. Dari tipe data tersebut, ada beberapa tipe data yang dapat digunakan sebagai bilangan bertanda (untuk tipe data numerik). Di bawah ini adalah bilangan bertanda di dalam MySQL.

Presedensi Operator
Berikut ini adalah presedensi operator yang disusun dari presedensi tertinggi hingga terendah:

INTERVAL
BINARY, COLLATE
!
- (unary minus), ~ (unary bit inversion)
^
*, /, DIV, %, MOD
-, +
<<, >>
&|
=, <=>, >=, >, <=, <, <>, !=, IS, LIKE, REGEXP, IN
BETWEEN, CASE, WHEN, THEN, ELSE
NOT
&&, AND
XOR
||, OR
:=

Operator || memiliki presedensi diantara ^ dan operator unari apabila mode PIPES_AS_CONCAT SQL diaktifkan.
Apabila mode HIGH_NOT_PRECEDENCE diaktifkan, maka presedensi NOT sama dengan operator !.

Kontrol Alir
IF (jika-maka-selain itu)

IF ekspresi_kondisi1 THEN pernyataan1;
[ELSEIF ekspresi_kondisi2 THEN pernyataan2;] ...
[ELSE pernyataan3;]
END IF;

Kontrol IF mengimplementasikan struktur dasar dari kondisi. Apabila ekspresi_kondisi1 bernilai benar, maka pernyataan1 akan dijalankan. Namun, apabila ekspresi_kondisi1 bernilai salah, maka klausa selanjutnya yang akan dieksekusi (jika ada). Hal yang sama terjadi pada ELSEIF. Apabila tidak ada kondisi yang cocok, maka klausa ELSE (jika ada) akan dieksekusi.

CASE (kasus)

CASE nilai_dicari
WHEN nilai1 THEN pernyataan 1
[WHEN nilai2 THEN pernyataan2] ...
[ELSE pernyataan3]
END CASE

Kontrol CASE mengimplementasikan struktur yang lebih kompleks dari kondisi. Kondisi berjalan dengan cara mencocokkan nilai_dicari pada klausa WHEN. Apabila cocok dengan nilai1, maka pernyataan1 akan dieksekusi. Namun, apabila tidak cocok dengan nilai1, maka klasusa selanjutnya akan dieksekusi (jika ada) sampai ketemu atau klausa ELSE (jika ada) akan dieksekusi. Apabila tidak ada nilai yang cocok, maka akan menghasilkan error "NOT FOUND FOR CASE STATEMENT".

LOOP (putaran)

[label:] LOOP
pernyataan
END LOOP [label]

Kontrol LOOP mengimplementasikan konstruksi yang sederhana, pernyataan akan dieksekusi terus menerus sampai dikeluarkan, biasanya dengan klausa LEAVE atau dilanjutkan dengan klausa ITERATE.

REPEAT (ulangan)

[label:] REPEAT
pernyataan
UNTIL kondisi_penghenti
END REPEAT [label]

Kontrol REPEAT akan mengeksekusi pernyataan sampai kondisi_penghenti terpenuhi atau bernilai benar.

WHILE (selama)

[label:] WHILE kondisi_perulangan DO
pernyataan
END WHILE [label]

Apabila kondisi_perulangan terpenuhi atau bernilai benar, maka WHILE akan mengeksekusi pernyataan sampai kondisi_perulangan tidak terpenuhi atau bernilai salah.

LEAVE (tinggalkan), ITERATE (iterasi) dan RETURN (kembali)
Klausa LEAVE digunakan untuk keluar dari kontrol alir (flow control) menuju label yang ditunjuk. LEAVE dapat digunakan di dalam BEGIN ... END atau perulangan (LOOP, REPEAT, WHILE).
Klausa ITERATE digunakan untuk mengulangi lagi label yang ditunjuk. ITERATE hanya dapat digunakan di dalam perulangan (LOOP, REPEAT, WHILE).
Klausa RETURN akan membuat keluar dari fungsi, kemudian mengembalikan nilai ke pemanggil fungsi. RETURN tidak dapat digunakan pada prosedur, pemicu (trigger) maupun penjadwalan (event).
Contoh:

DELIMITER //
CREATE FUNCTION penggunaan_leave_iterate_return(p1 INT) RETURNS INT
BEGIN
label1: LOOP
SET p1 = p1 + 1;
IF p1 < 10 THEN ITERATE label1; END IF; LEAVE label1; END LOOP label1; RETURN p1; END // DELIMITER ;

Apabila fungsi penggunaan_leave_iterate_return(8) dijalankan, maka pernyataan yang dieksekusi adalah p1 = p1 + 1 = 8 + 1 = 9. Kemudian p1 akan dites dan bernilai benar, maka akan kembali ke label1. Selanjutnya mengeksekusi p1 = p1 + 1 = 9 + 1 = 10. Kemudian p1 akan dites dan bernilai salah, maka baris berikutnya dieksekusi, yaitu: Leave label1 sehingga keluar dari perulangan dan mengembalikan nilai p1.


View
View adalah sebuah permintaan (query) yang tersimpan (di dalam basis data). Pada waktu dijalankan, view akan menghasilkan sebuah kumpulan hasil (result set). Sebuah view juga bertindak sebagai tabel maya (virtual table). Akan tetapi, view tidak dapat memiliki sebuah indeks. Oleh karena itu, hindari penggunaan view untuk melakukan join tabel.

View, termasuk view yang dapat diperbaharui (updatable view), tersedia pada MySQL penyedia (server) versi 5.1 ke atas. Sebuah view dapat dibuat dari berbagai macam pernyataan SELECT yang merujuk pada tabel atau view yang lain. Dapat juga menggunakan UNION dan sub-permintaan (subquery).

Ada beberapa pilihan algoritma (algorithm) yang dapat digunakan untuk membuat sebuah view, yang akan berpengaruh pada saat memproses sebuah view, yaitu: MERGE, TEMPTABLE, dan UNDEFINED. Secara bawaan (default), algoritma yang digunakan apabila tidak disebutkan algoritma yang akan digunakan adalah algoritma UNDEFINED.

Algoritma MERGE: pernyataan (statement) yang merujuk pada view dan definisi dari view digabungkan sehingga bagian dari definisi view akan mengganti bagian dari pernyataan. Algoritma ini biasanya lebih efisien dan datanya dapat diperbaharui (updatable).
Algoritma TEMPTABLE: hasil dari view akan disimpan ke dalam tabel sementara, yang akan digunakan untuk menjalankan pernyataan (statement). Salah satu alasan untuk memakai algoritma ini adalah lebih cepat untuk melepas kunci (release the lock) segera setelah tabel sementaranya jadi sebelum digunakan untuk menjalankan pernyataan (statement).

Apabila algoritma UNDEFINED yang digunakan, maka MySQL akan lebih memilih algoritma MERGE daripada TEMPTABLE pada kasus umumnya.

Berikut ini adalah penulisan/sintaks untuk pembuatan view:

CREATE
[OR REPLACE]
[ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
[DEFINER = { user | CURRENT_USER }]
[SQL SECURITY { DEFINER | INVOKER }]
VIEW view_name [(column_list)]
AS select_statement
[WITH [CASCADED | LOCAL] CHECK OPTION]

Contoh 1:

create algorithm=merge view v_contoh1 as select kol1, kol2 from db.tabel where kol3>1

Apabila view tersebut dijalankan pada pernyataan (statement) di bawah ini,

select * from db.v_contoh1

maka pernyataan tersebut akan diganti/digabung (merge) dengan view menjadi:

select kol1, kol2 from db.tabel where kol3>1

Contoh 2:
Apabile view tersebut dijalankan pada pernyataan (statement) di bawah ini,

select * from db.v_contoh1 where kol2=1

maka pernyataan tersebut akan diganti/digabung (merge) dengan view menjadi:

select kol1, kol2 from db.tabel where (kol3>1) and (kol2=1)

Batasan
Algoritma Merge tidak dapat digunakan pada pernyataan (statement) yang mengandung:

• aggregate function (sum, average, max, min, dsb...), group by, having
• distinct
• limit
• union atau union all
• sub-permintaan (subquery) pada select
• tidak ada referensi pada tabel
  

Data dari View tidak dapat diperbaharui (non updatable) pada pernyataan (statement) yang mengandung:

• aggregate function (sum, average, max, min, dsb...), group by, having
• distinct
• limit
• union atau union all
• sub-permintaan (subquery) pada select
• tidak ada referensi pada tabel
• join tipe tertentu
• view yang tidak dapat diperbaharui (non updatable) pada klausa from
• sub-permintaan (subquery) pada klausa where yang tabelnya ada pada klausa from
• menggunakan algoritma temptable
• multi-referensi (multi-reference) pada kolom manapun pada suatu tabel
  

Data dari View dapat disisipi (insertable) pada pernyataan (statement) yang memiliki kondisi:

• tidak memiliki nama kolom yang sama (duplicate column name)
• semua kolom yang tidak memiliki nilai bawaan (default value) harus disertakan
• bukan merupakan tabel turunan (derived table) dan merupakan referensi kolom yang simpel
• Data dari View dapat dihapus (deletable) apabila di dalam view tersebut hanya terdapat sebuah tabel saja

Rutin tersimpan (stored routine)
Rutin tersimpan (stored routine) adalah seperangkat pernyataan (statement) SQL yang dapat disimpan di penyedia (server), tersedia pada MySQL penyedia (server) versi 5.1 ke atas. Setelah rutin ini disimpan, maka klien tidak perlu lagi membuat rutin yang sama tetapi tinggal memanfaatkan rutin yang sebelumnya sudah disimpan di penyedia (server) tersebut. MySQL menggunakan sintaks SQL:2003 untuk rutin tersimpan (stored routine), yang juga digunakan oleh DB2 milik IBM.

Ada beberapa keuntungan yang dapat diperoleh apabila menggunakan rutin tersimpan (stored routine):

• Apabila ada banyak aplikasi klien yang dibangun dengan bahasa yang berbeda atau bekerja pada platform yang berbeda tetapi memiliki operasi basis data yang sama
• Apabila ada pembagian tugas pada pembuatan program yang melibatkan spesialis basis data dan spesialis program, maka spesialis basis data membuat semua rutin yang akan sering digunakan atau harus digunakan untuk memanipulasi basis data dan spesialis program tinggal memanfaatkannya saja
• Membuat program lebih efisien, karena spesialis program tidak perlu lagi untuk mengolah data pada program yang dibuat. Data diolah di penyedia (server) basis data dengan beberapa rutin, kemudian hasilnya tinggal ditampilkan di program
• Memperkuat keamanan, karena rutin tersimpan (stored routine) dapat diberikan hak akses
• Dapat meningkatkan performa, karena informasi yang dikirimkan antara penyedia (server) dan klien lebih sedikit. Pertukaran informasi ini akan meningkatkan kerja dari basis data karena akan semakin banyak yang harus dilakukan oleh penyedia (server).
  

Berikut ini adalah cara penulisan/sintaks untuk pembuatan prosedur (tidak ada nilai kembali):

CREATE
[DEFINER = { pengguna }]
PROCEDURE nama_db.nama_proc ([[ IN | OUT | INOUT ] nama_parameter tipe_data[,...]])
[karakteristik ...]
BEGIN
isi_rutin
END

Berikut ini adalah cara penulisan/sintaks untuk pembuatan fungsi (ada nilai kembali):

CREATE
[DEFINER = { pengguna }]
FUNCTION nama_db.nama_fungsi ([[ IN | OUT | INOUT ] nama_parameter tipe_data[,...]])
RETURNS tipe_data
[karakteristik ...]
BEGIN
isi_rutin
END

Pada dasarnya prosedur dan fungsi memiliki cara penulisan yang mirip, tetapi memiliki satu perbedaan, yaitu fungsi akan mengembalikan nilai (return value) sedangkan prosedur tidak mengembalikan nilai. Untuk memanggil suatu prosedur digunakan perintah:

CALL nama_db.sp_name([parameter[,...]])

Parameter dapat diisi atau tidak tergantung dari kebutuhan. Parameter tidak memperhatikan huruf besar dan huruf kecil (case insensitive/not case sensitive). Secara bawaan, setiap parameter sebagai IN. Mendefinisikan parameter sebagai IN, OUT atau INOUT hanya dapat dilakukan pada prosedur. Pada fungsi, parameter harus selalu IN.

Parameter IN akan menyebabkan parameter tersebut bersifat tidak terpengaruh dengan perubahan nilai variabel tersebut yang mungkin terjadi di dalam prosedur/fungsi.

Parameter OUT akan menyebabkan parameter awalnya selalu bernilai null atau kosong. Kemudian, parameter tersebut akan bernilai sesuai dengan perubahan nilai variabel tersebut yang terjadi di dalam prosedur

Parameter INOUT merupakan kombinasi dari parameter IN dan parameter OUT, yaitu nilai awalnya sesuai dengan yang diisi oleh pemanggil prosedur, kemudian nilai dari variabel tersebut dapat berubah sesuai dengan perubahan yang terjadi di dalam prosedur.
Karakteristik dapat berupa:

• COMMENT 'string'
• LANGUAGE SQL
• [NOT] DETERMINISTIC
• { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
• SQL SECURITY { DEFINER | INVOKER }
  

Setiap rutin tersimpan (stored routine) dapat berisi dari pernyataan SQL (SQL statement) yang mungkin dapat terdiri dari beberapa pernyataan (statement) yang dipisahkan oleh tanda titik koma (;). Secara bawaan, MySQL mengenali titik koma sebagai pemisah pernyataan (statement delimiter), sehingga harus mendefinisikan ulang pemisahnya secara sementara agar tidak terjadi masalah, kemudian harus dikembalikan lagi menjadi titik koma setelah selesai.
Contoh:

delimiter $$
create procedure db.p_prosedur1 (param_in int, out param_out int, inout param_inout int)
deterministic
reads sql data
begin
# deklarasi variabel internal
declare variabel_internal int;
# perhitungan
set variabel_internal := param_in + param_inout;
set @variabel_lokal := variabel_internal – 10;
set param_in := 100;
set param_out := @variabel_lokal + (select 5);
set param_inout := variabel_internal;
end $$
delimiter ;

Apabila prosedur tersebut dieksekusi, maka prosedur tersebut akan disimpan ke dalam penyedia (server). Apabila prosedur tersebut digunakan pada pernyataan (statement) berikut:

set @variabel_in := 15, @variabel_inout := 20;
call db.p_prosedur1(@variabel_in, @variabel_out, @variabel_inout);
select @variabel_in, @variabel_lokal, @variabel_out, @variabel_inout;

maka akan didapatkan nilai:

@variabel_lokal = 25, mula-mula bernilai null
@variabel_in = 15, mula-mula bernilai 15
@variabel_out = 30, mula-mula bernilai null
@variabel_inout = 35, mula-mula bernilai 20

Dari hasil eksekusi pernyataan (statement) di atas dapat dilihat bahwa variabel lokal (ditandai dengan simbol @) atau variabel global (ditandai dengan simbol @@) dapat dimanupilasi dari dalam fungsi atau prosedur. @variabel_lokal merupakan variabel lokal yang tidak digunakan sebagai parameter di dalam prosedur tersebut dan tidak dideklarasikan sebelumnya, sehingga nilai mula-mulanya adalah null dan nilainya akan menyesuaikan dengan perubahan yang terjadi di dalam p_prosedur1. @variabel_in menjadi parameter pertama di dalam prosedur dan sebagai parameter IN. Meskipun di dalam prosedur1 (10) nilai param_in mengalami perubahan, tetapi @variabel_in tetap tidak mengalami perubahan. Hal yang berbeda terjadi pada @variabel_out dan @variabel_inout.

Batasan

• Fungsi tidak dapat berbentuk rekursif
• Rekursi diperbolehkan di dalam prosedur, tetapi secara bawaan tidak aktif. Untuk mengaktifkannya, atur variabel sistem max_sp_recursion_depth lebih besar dari 0.
• Pernyataan (statement) yang tidak diperbolehkan adalah LOCK TABLES, UNLOCK TABLES, ALTER VIEW, LOAD DATA, LOAD TABLE, dan pernyataan (statement) yang tidak diperbolehkan pada pernyataan yang disiapkan (prepared statement).
• Pernyataan (statement) yang menggunakan commit atau rollback, pernyataan yang mengembalikan kumpulan hasil (result set) dan pernyataan (statement) FLUSH tidak diperbolehkan pada fungsi

Pemicu (Trigger)
Pemicu (trigger) adalah sebuah objek di dalam basis data yang berkaitan erat dengan aksi yang terjadi di dalam sebuah tabel. Pemicu (trigger) dapat dijalankan pada waktu suatu tabel diberikan permintaan (query) berupa penyisipan (insert), penghapusan (delete) atau pembaharuan data (insert); baik sebelum eksekusi permintaan (query) maupun setelahnya. Pemicu (trigger) biasanya digunakan untuk melakukan pengecekan pada data atau melakukan perhitungan tertentu.

Hal yang paling penting adalah pemicu (trigger) hanya dijalankan jika dan hanya jika permintaan (query) SQL dikirimkan ke penyedia (server) untuk diolah. Pemicu (trigger) tidak akan dijalankan apabila perubahan pada tabel dibuat oleh API yang tidak mengirimkan permintaan (query) ke penyedia (server).

Sintaks penulisan trigger

CREATE
[DEFINER = { user | CURRENT_USER }]
TRIGGER nama_pemicu waktu_pemicu aksi_pemicu
ON nama_db.nama_tabel FOR EACH ROW
pernyataan_aksi

waktu_pemicu adalah kapan pemicu (trigger) akan dijalankan. waktu_pemicu dapat berupa BEFORE (sebelum) atau AFTER (sesudah). aksi_pemicu mengindikasikan jenis pernyataan (statement) apa yang menyebabkan pemicu dijalankan atau mengaktifkan pemicu. aksi_pemicu dapat berupa:

• INSERT, yaitu ketika sebuah baris baru disisipkan melalui pernyataan (statement) INSERT, LOAD DATA dan REPLACE
• UPDATE, yaitu ketika sebuah baris dimodifikasi melalui pernyataan (statement) UPDATE
• DELETE, yaitu ketika sebuah baris dihapus melalui pernyataan (statement) DELETE dan REPLACE
  

Nilai sebuah kolom dari tabel (field) dapat digunakan di dalam pernyataan_aksi. Kolom tersebut dapat diakses dalam dua cara, yaitu menggunakan objek OLD untuk mendapatkan nilai kolom yang saat ini ada di penyedia (server) dan objek NEW untuk mendapatkan nilai kolom yang akan dikirim ke penyedia (server). Berikut ini adalah beberapa ketentuan yang berkaitan dengan objek OLD dan objek NEW:

• Objek OLD hanya dapat digunakan pada aksi penghapusan data (delete) dan pembaharuan data (update), sedangkan objek NEW hanya dapat digunakan pada aksi penyisipan data (insert) dan pembaharuan data (update)
• Objek OLD bersifat hanya dapat dibaca (read only)
• Objek NEW dapat dimanipulasi apabila menggunakan aksi_pemicu BEFORE
• Nilai auto_increment adalah 0 dan akan otomatis diberi nilai ketika penyisipan data (insert) sudah benar-benar terjadi
  

Contoh:

delimiter $$
create trigger t_pemicu1 before insert
on db.tabel1 for each row
begin
set @variabel_lokal = new.kol1;
insert into db.log (nama_pengguna) values (new.kol2);
end $$
delimiter ;

Apabila ada penyisipan data baru pada tabel1 berupa:

insert into db.tabel1 (kol1, kol2) values ("alpha", "beta");

maka pemicu (trigger) t_pemicu1 akan dijalankan sebelum datanya masuk ke penyedia (server), karena menggunakan BEFORE INSERT (sebelum disisipkan). Hal pertama yang terjadi adalah variabel @variabel_lokal akan bernilai "alpha", kemudian akan disisipkan nama_pengguna "beta" ke dalam tabel log.

Apabila ada penghapusan data dari tabel1 berupa:

delete from db.tabel1 where kol1="alpha";

maka pemicu (trigger) t_pemicu1 tidak akan dijalankan.

Batasan

• Tidak diperbolehkan terdapat dua buah pemicu (trigger) pada sebuah tabel dengan waktu dan aksi yang sama
• Pemicu (trigger) tidak diaktifkan akibat ada aksi pada kunci asing (foreign key)
• RETURN tidak boleh digunakan, gunakan LEAVE
• Tidak dapat digunakan/dibuat pada tabel-tabel di basis data "mysql"