Pengenalan Stored Routine MySQL

Image by Mark Duffel on Unsplash

{tocify} $title={Daftar Isi}

Pendahuluan

MySQL mendukung stored program yang dikenal sebagai stored routine yang terdiri dari dua jenis yaitu stored procedure dan stored function dimana keduanya merupakan sekumpulan pernyataan SQL yang disimpan di server MySQL itu sendiri dan digunakan untuk tujuan tertentu. Stored routine pertama kali diperkenalkan pada MySQL versi 5 dan umumnya saat ini banyak RDBMS juga sudah mendukung stored program. Artikel ini akan membahas bagaimana menggunakan stored routine baik itu stored procedure ataupun stored function, berikut adalah contoh untuk menggunakan stored routine MySQL:

---

Stored Procedure

Biasanya stored procedure dibuat untuk menyimpan statement sql yang dilakukan secara berulang atau mengerjakan tugas tertentu terlebih dahulu sebelum dilakukan insert/update/delete ke table atau untuk tujuan keamanan, dsb. Ada beberapa alasan dan keuntungan jika menggunakan stored procedure:

• Performa : Stored procedure hanya akan dikompilasi sebanyak satu kali ketika dieksekusi untuk pertama kalinya dan akan dikompilasi lagi secara otomatis jika terjadi perubahan, kumpulan statement yang dibuat dalam stored procedure kemudian disimpan dalam bentuk yang dapat dieksekusi sehingga pemanggilan procedure menjadi cepat dan efisien.
  Note: Beberapa RDBMS atau MySQL versi 5.6 mungkin masih menggunakan cache query dimana ketika sisi klien mengirim permintaan SELECT maka server akan mengkompilasinya dan hasil kompilasi akan disimpan dalam cache query hal ini akan berpengaruh pada memory server dan aplikasi dibangun hanya menggunakan satu atau beberapa server database
• Produktivitas : Pengkodean yang terlalu banyak dapat menurunkan produktivitas, stored procedure memberikan peningkatan produktivitas karena cukup dengan memanggil procedure yang telah dibuat untuk mengerjakan tugas tertentu dari statement yang sudah didefinisikan didalamnya
• Maintenance : Stored procedure dibangun di sisi server sehingga ketika perlu melakukan perubahan statement yang harus diterapkan maka kita cukup merubah statement yang ada dalam stored procedure dan tidak perlu melakukan perubahan apapun pada sisi aplikasi.
• Keamanan : Stored procedure meningkatkan keamanan karena modul aplikasi tidak dapat melihat tabel yang di akses dan dieksekusi

Adapun bentuk umum atau format dasar untuk membuat stored procedure adalah sebagai berikut:

DELIMITER &&
CREATE PROCEDURE procedure_name ([IN | OUT | INOUT] parameter_name tipe_data [, parameter datatype]) ]
BEGIN
    Routine body
END &&
DELIMITER ;

Contoh (tanpa input parameter):

mysql> DELIMITER $$ 
mysql> CREATE PROCEDURE hello()
    ->Begin
    ->Select "Hello World";
    ->END$$
mysql> DELIMITER ;

Contoh lainnya adalah dengan input parameter untuk menunjuk kondisi tertentu, contohnya sebagai berikut:

mysql> delimiter $$
mysql> create procedure mhs (id int)
    -> begin
    -> select id_mhs, nama, npm from mahasiswa where id_mhs = id;
    -> end $$
Query OK, 0 rows affected (0.17 sec)

mysql> delimiter ;

Kemudian untuk memanggil stored procedure yang telah dibuat maka kita perlu menggunakan statement CALL, format dasarnya adalah sebagai berikut:

CALL nama_sp;
CALL nama_sp (param);
mysql> call hello;
+-------------+
| Hello World |
+-------------+
| Hello World |
+-------------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.01 sec)
mysql> call nama (1);
+--------+-------------+----------+
| id_mhs | nama        | npm      |
+--------+-------------+----------+
|      1 | AA Aditya A | 51411136 |
+--------+-------------+----------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.01 sec)

Untuk melihat daftar procedure yang telah dibuat kita dapat menggunakan perintah sebagai berikut:

show procedure status; 
atau
show procedure status where db = "nama_db";

Sebaiknya untuk menjalankan statement diatas gunakan MySQL Workbench karena dengan command-line anda tidak dapat melihatnya dengan jelas karena keterbatasan ukuran command-line dengan hasil output yang ditampilkan, sedangkan untuk menghapus stored procedure adalah sebagai berikut:

drop procedure nama_procedure

Dan untuk melihat source code dari stored procedure yang dibuat adalah sebagai berikut:

show create procedure nama_sp

Stored procedure memiliki tiga jenis input parameter yakni IN, OUT dan INOUT dimana secara default menggunakan parameter IN meskipun tidak menyatakan ketiga jenis parameter yang ada.

---

Stored Function (Function)

Stored function merupakan stored program yang digunakan untuk mengerjakan tugas tertentu dan mengembalikannya dalam sebuah nilai, pada praktiknya stored function digunakan untuk membuat aturan bisnis atau rumus yang dapat digunakan kembali dalam stored program atau statement SQL. Adapun bentuk umum atau format dasar Stored Function adalah sebagai berikut:

CREATE FUNCTION nama_func(parameter tipe_data(length))
RETURNS tipe_data
[NOT] {Characteristics}
begin
    Declaration_section    
    Func_Body  
RETURN param
end $$
delimiter ;

Characteristic:
| LANGUAGE SQL      
| [NOT] DETERMINISTIC      
| { CONTAINS SQL | NO SQL | READS SQL DATA 
| MODIFIES SQL DATA }      
| SQL SECURITY { DEFINER | INVOKER }

Stored function tidak dapat menggunakan input parameter karena function harus mengembalikan sebuah nilai, function juga perlu menggunakan karakteristik agar dapat menerima statement. Adapun contohnya adalah sebagai berikut:

Contoh (tanpa input parameter)

DELIMITER $$
CREATE FUNCTION hello()
RETURNS TEXT
DETERMINISTIC
BEGIN
RETURN "Hello World";
END $$
DELIMITER ;

Contoh dengan input Parameter:

DELIMITER $$
CREATE FUNCTION hellow(kalimat TEXT)
  RETURNS TEXT
  DETERMINISTIC
BEGIN
  DECLARE jumlah INT;
  SET jumlah = LENGTH(kalimat);
  RETURN CONCAT('Hello ', kalimat, ' - Kalimatmu berjumlah ', jumlah, ' karakter');
END;
$$
DELIMITER ;

Kemudian untuk memanggil stored function adalah dengan select, format dasar dan contohnya sebagai berikut:

select nama_func();
select nama_func(parameter);

Contoh:

mysql> select hello();
+-------------+
| hello()     |
+-------------+
| Hello World |
+-------------+
1 row in set (0.06 sec)

mysql> select hellow("Geekasmedia");
+-----------------------------------------------------+
| hellow("Geekasmedia")                               |
+-----------------------------------------------------+
| Hello Geekasmedia - Kalimatmu berjumlah 11 karakter |
+-----------------------------------------------------+
1 row in set (0.00 sec)

Berikut adalah contoh untuk insert data melalui stored function:

delimiter $$
create function masuk (nama varchar(50), npm int, j_kelamin enum("L","P"), t_lahir date, alamat varchar(50), kota varchar(50), telepon varchar(12), email varchar(50))
returns int
deterministic
begin
insert into mahasiswa values (id_mhs, nama, npm, j_kelamin, t_lahir, alamat, kota, telepon, email);
return 1;
end $$
delimiter ;

select masuk ("Geekasmedia", 51221728, "L", "2013-12-30", "Jalan suka cita bersama", "Jambu", "081231717430", "geekasmedia@mail.com");
select masuk ("Geekasmedia", 52221728, "L", "2013-12-30", "Jalan suka cita bersama", "Jambu", "081231717430", "geekasmedia@mail.com");
select masuk ("Geekasmedia", 53221728, "L", "2013-12-30", "Jalan suka cita bersama", "Jambu", "081231717430", "geekasmedia@mail.com");
mysql> select id_mhs, nama, npm from mahasiswa;
+--------+-------------+----------+
| id_mhs | nama        | npm      |
+--------+-------------+----------+
|      1 | Geekasmedia | 51221728 |
|      2 | Geekasmedia | 52221728 |
|      3 | Geekasmedia | 53221728 |
+--------+-------------+----------+
3 rows in set (0.00 sec)

---

Penutup

Memanfaatkan stored routine pada database akan cukup membantu memastikan alur bisnis berjalan dengan baik selain itu peningkatan performa, produktivitas dan keamanan menjadi faktor pertimbangan untuk menggunakan stored routine selain itu, stored procedure ataupun function berisikan kumpulan statement menjadi logical proses untuk mengerjakan tugas tertentu, namun dimana sebenarnya stored routine disimpan? Keduanya disimpan di tabel mysql.routines dan mysql.parameters yang merupakan bagian dari data dictionary dan tidak memungkinkan untuk mengakses tabel tersebut meskipun menggunakan user root dengan semua priviliges yang ada.