Tuesday, November 24, 2020

Procedure dan Function

     No comments

 Procedure dan Function

Pada dasarnya, function dan stored procedure database adalah hal yang sama, yaitu kumpulan peritah atau query yang disimpan dan dijalankan di dalam server database. Function dan stored procedure diikat dengan sebuah nama, sehingga kita hanya perlu memanggil nama dari function dan stored procedure yang ingin dieksekusi.

Perbedaan mencolok antara keduanya terletak di CRUD (create, read, update dan delete) data, dimana function hanya bisa melakukan select data atau task sederhana lainnya, tanpa bisa menggunakan perintah insert, edit dan delete data. Sedangkan stored procedure dapat melakukan task yang lebih kompleks yang tidak bisa diakomodir oleh function, sehingga stored procedure banyak digunakan sebagai alternatif pengolahan data dalam bahasa pemrograman.

Contoh function :

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
DROP FUNCTION IF EXISTS hari;

CREATE FUNCTION hari() RETURNS DATETIME
BEGIN
DECLARE hari DATETIME;
SET hari = NOW();
RETURN (hari);
END;

SELECT hari();

Script di atas menjelaskan pembuatan function dengan nama hari yang bertujuan untuk menampilkan tanggal dan waktu saat ini, adapun penjelasan singkat dari beberapa sourcode code di atas :

- DROP FUNCTION IF EXISTS hari : menghapus function jika terdapat function dengan nama yang sama.

- RETURNS DATETIME : menjelaskan tipe data yang akan ditampilkan function tersebut, dapat juga menggunakan tipe data lain seperti INT, VARCHAR dan tipe data lainnya.

- DECLARE hari DATETIME : mentapkan tipe data yang digunakan pada variabel hari.

- SET hari = NOW() : membuat variabel bernama hari yang berisi nilai NOW() atau tanggal dan jam sekarang, dapat juga menyimpan nilai lain sesuai tipe data yang ditentukan.

- RETURN hari : memanggil variabel hari untuk ditampilkan.

- SELECT hari() : memangil function hari.

Contoh di atas merupakan implementasi sederhana dari sebuah function, kita dapat membuat function yang lebih rumit lagi seperti membut sebuah rumus atau menampilkan data dari sebuah tabel berdasarkan pencarin dari variabel tertentu, perlu diingat bahwa function hanya bisa sebatas melakukan perintah SELECT data tanpa bisa melakukan CREATE, UPDATE atau DELETE.

Berikut contoh pembuatan function untuk menampilkan data dari kolom nama_provinsi di dalam tabel lokasi berdasarkan id_lokasi 'LOK01' :

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
DROP FUNCTION IF EXISTS lokasi;

CREATE FUNCTION lokasi(kode VARCHAR(5)) RETURNS VARCHAR(100)
BEGIN
DECLARE cari VARCHAR(100);
SELECT nama_lokasi INTO cari FROM lokasi WHERE id_lokasi=kode;
RETURN (cari);
END;

SELECT lokasi('LOK01');

Contoh procedure :

1
2
3
4
5
6
7
8
9
DROP PROCEDURE IF EXISTS savelokasi;

CREATE PROCEDURE savelokasi(kode VARCHAR(5), nama VARCHAR(100)) 
SQL SECURITY INVOKER
BEGIN
INSERT INTO lokasi(id_lokasi, nama_lokasi) VALUES(kode, nama);
END;

CALL savelokasi('LOK03', 'Surabaya');

Script di atas menjelaskan pembutan procedure dengan nama savelokasi, berikut penjelasan beberapa source code penting di atas :

- DROP PROCEDURE IF EXISTS savelokasi : menghapus procedure jika terdapat procedure dengan nama yang sama.

- SQL SECURITY INVOKER : security code dari MYSQL.

- CREATE PROCEDURE savelokasi(kode VARCHAR(5), nama VARCHAR(100)) : membuat procedure dengan nama savelokasi dengan kebutuhan variabel kode dan nama berdasarkan tipe data dan panjang data yang ditentukan.

- INSERT INTO lokasi(id_lokasi, nama_lokasi) VALUES(kode, nama) : perintah untuk menambahkan data sesuai nama kolom dengan nilai dari variabel yang dipanggil.

- CALL savelokasi('LOK03', 'Surabaya') : memanggil procedure savelokasi.

Semua pembahasan di atas dapat kita praktekan kembali berdasarkan contoh kasus yang berbeda, serta dapat dikembangkan menjadi lebih kompleks lagi.

Sumber referensi :
Buku : "Belajar Otodidak MySQL (Teknik Pembuatan dan Pengelolaan Database)"
Penulis : Budi Raharjo
Penerbit : Informatika - Bandung
Praktek : Mohammad Fazlurrahman

Monday, November 23, 2020

Event Scheduler di Mysql

     No comments

Event Scheduler di Mysql

Event mysql adalah sekumpulan query atau perintah yang dijalankan dan diberhentikan pada periode waktu tertentu atau sesuai jadwal yang telah dibuat. Event sangat membantu kita untuk melakukan tugas-tugas yang bersifat rutin seperti check data atau melakukan CRUD (Create, Read, Update dan Delete) pada kondisi tertentu, konsep ini mirip dengan cron job service yang ada di Linux dan schedule task yang ada di Windows.

Bagi yang menggunakan XAMPP dan phpMyadmin dapat dilihat saat kita membuka salah satu database, sudah tersedia panel Events pada pojok kanan atas, untuk lebih jelasnya dapat dilihat pada gambar di bawah ini :


Berikut penjelasan dari setiap nomor yang ada di gambar atas :

1. Events, tombol ini berfungsi untuk menampilkan data event scheduler yang sudah kita buat. Pada panel ini kita dapat membuat, mengedit dan menghapus event, termasuk melakukan export sourcode dari sebuah event.

2. Events scheduler status, tombol ini berfungsi untuk mengaktifkan dan menonaktifkan semua events yang ada, pastikan statusnya ON agar semua event yang kita buat berjalan sesuai fungsinya. Usahakan juga service database kita tetap berjalan, karena status events scheduler akan OFF jika service database kita tidak berjalan.

3. Add event, tombol ini berfungsi untuk membuat event baru.

Kali ini kita akan mempraktekan pembuatan event yang sederhana yang bernama Rekap_Bulanan, yaitu event untuk menyimpan semua tanggal pada bulan berjalan, jika hari ini adalah tanggal 11 november 2020 maka kita akan menyimpang tanggal dari 2020-11-01 sampai 2020-11-30 (bulan berjalan) yang akan di jelaskan pada gambar di bawah ini.


Kita cukup menuliskan query pada panel SQL, berikut query yang bisa kita coba berdasarkan gambar di atas : 
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
DROP EVENT IF EXISTS Rekap_Bulanan;
CREATE EVENT Rekap_Bulanan ON SCHEDULE EVERY 1 DAY STARTS '2019-01-01 10:10:00' ON COMPLETION NOT PRESERVE ENABLE DO BEGIN
    DECLARE  BOOL DEFAULT 0;
    DECLARE period VARCHAR(40);
    SET period	= DATE_FORMAT(CURDATE(),'%Y-%m');
    -- Hari Kerja
    INSERT INTO rekap_absensi(id_ra,id_pegawai,periode,created_at)
    SELECT '' AS kode, idpegawai, period, NOW() FROM pegawai WHERE tgl_resign IS NULL AND alasan IS NULL AND idpegawai NOT IN('1', '2', '3') AND idpegawai NOT IN(SELECT rekap_absensi.id_pegawai FROM rekap_absensi WHERE rekap_absensi.periode=period);
IF  THEN
        ROLLBACK;
    ELSE
        COMMIT;
    END IF;
END

Fungsi Drop Event di atas berfungsi untuk menghapus event jika ada event dengan nama yang sama, hal ini untuk menghindari error. Event Rekap_Bulanan dijalankan setiap hari pada pukul 10:10:00 untuk menginput semua tanggal pada tabel rekap_absensi dengan struktur tabel seperti keterangan di bawah ini :
    
Tabel rekap_absensi
1. id_ra INT(11) Auto Increment (PK)
2. idpegawai VARCHAR(10)
3. periode DATE()
4. created_at DATETIME()

Jika sudah mengeksekusi query event di atas, jangan lupa untuk memastikan status events scheduler adalah ON atau kita bisa menggunakan query seperti di bawah ini : 
1
SET GLOBAL event_scheduler = 1;

Untuk status ON :
Untuk status OFF :
1
SET GLOBAL event_scheduler = 0;
Sumber referensi :
Website : https://dev.mysql.com/
Buku : "Belajar Otodidak MySQL (Teknik Pembuatan dan Pengelolaan Database)"
Penulis : Budi Raharjo
Penerbit : Informatika - Bandung
Praktek : Mohammad Fazlurrahman