https://www.blogger.com/blogger.g?blogID=4022483530515295521#allposts/postNum=1
Senin, 16 Juni 2014
Stored Precedure Dan Function
MySQL dekenal sebagai database reasiaonal, terdapa sejumlah transaksi, subquery, view, stored procedure atau fungsi.
Apa yang dimaksud dengan prorgram stored procdure?
Database server telah mendukukung bebrapa proses transaksi yang dikerjakan oleh server.
Program tersbut diciptakan oleh user diletakkan dan dieksekusi dalam database server.
Terdapat 3 bagian penting Program yang tersimpan dalam database Server
1. Stored Procedure
Sejumlah perintah dalam kode program dan beberapa parameter input, dan sejumlah hasil atau keluaran. Cara pemanggilannya dengan mengeksekusi nama proscdure tersebut.
2. Stored Function
Fungsi hampir sama dengan stored procedure. Function mempunyai sejumlah paramter input dan hanya mengembalikan satu output, standar perintah yang digunakan menggunakan perintah standar SQL.
3. Trigger
Suatu program yang akan melakukan respon dalam aktivitas database, berkaitan perintah-perintah DML (insert, update, delete) operasional pada tabel. Biasanya digunakan untuk otomatisasi atau validasi.
Pemrograman Stored Prosedure dalam MySQL.
Stored procedure adalah permasalah yang cukup kompleks untuk dibahas, adapun bagian penting pada stored procedure :
Kapan program akan diabut, parameter yang akan dilewatkan dan hasil yang dikeluarkan, interaksi dalam basis data, menciptakan stored precedure, fungsi dan trigger dalam bahasa pemrograman.
Apa yang dibutuhkan?
• MySQL Server
• Teks editor vi, emacs,
• MySQL Query Browser, SQLyog dll
Manciptakan Stored Procedure
Beberapa perintah untuk membuat stored procedure:
CREATE PROCEDURE , CREATE FUNCTION , atau CREATE TRIGGER
Contoh :
DELIMITER $$
DROP PROCEDURE IF EXISTS Kata()$$;
CREATE PROCEDURE Kata()
BEGIN
SELECT 'STMIK AKAKOM OK';
END$$
DELIMITER ;
Penjelasan :
DELIMITER $$ akhir dari peritah body (badan/skrip mysql)
DROP PROCEDURE IF EXISTS Kata()$$; perintah untuk menghapus jika prosedurnya sudah pernah ada.
CREATE PROCEDURE Kata( ) mencitapkan prosedur degan nama Kata ( isi parameter kalau ada ),
BEGIN awal perintah/blok perintah dan END akhir perintah
SELECT perintah menapilkan
Cara memanggilanya :
Enter password: ****
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 6.0.4-alpha-community-log MySQL Community Server (GPL)
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the buffer.
mysql> use coba;
Database changed
mysql> CALL Kata();
+——–+
| AKAKOM |
+——–+
| STMIK AKAKOM OK|
+——–+
1 row in set (0.00 sec)
Variabel
Variabel dalam bahasa SQL, fungsinya sama dengan bahasa pemrograman paxda umumnya.
Perintahnya menggunakan DECLARE, dan untuk memberikan nilai menggunakan perintah SET.
Parameter
Parameter adalah suatu nilai yanga akan dilewatkan atau dimasukan untuk diproses dalam fungsi atau prosedure.
Contoh
DELIMITER$$
CREATE PROCEDURE Demo_Var()
BEGIN
DECLARE x int;
DECLARE y int;
SET x=10;
SET y=20;
SELECT x+y;
END$$;
DELIMITER;
CALL Demo_var
Hasil
+——+
| x+y |
+——+
| 30 |
+——+
1 row in set (0.00 sec)
Sebuah Procedure dengan mengunakan parameter masukan, akan diproses dalam fungsi dan kemudian timapilkan, hasilnya.
Contoh :
DELIMITER$$
CREATE PROCEDURE my_akar(masukan INT)
BEGIN
DECLARE hasil FLOAT;
SET hasil= SQRT(masukan);
SELECT hasil;
END$$;
DELIMITER;
Cara memanggil prosedur
CALL my_akar(25);
Hasil keluaran sebagai berikut :
mysql> CALL my_akar(25);
+——-+
| hasil |
+——-+
| 5 |
+——-+
1 row in set (0.03 sec)
Query OK, 0 rows affected (0.05 sec)
Prosedure menggunakan parameter masukan dan keluaran (INT dan OUT)
Contoh :
DELIMITER$$
CREATE PROCEDURE my_sqrt(masukan INT, OUT nilai_keluar FLOAT)
BEGIN
SET nilai_keluar= SQRT(masukan);
END$$;
DELIMITER;
Cara menjalankan prosedur ada dua tahap
CALL my_sqrt(9,@out_value)
SELECT @out_value
Penjelasan :
Memampilkan nilai keluaran :
mysql> CALL my_akar(25);
+——-+
| hasil |
+——-+
| 5 |
+——-+
1 row in set (0.03 sec)
Query OK, 0 rows affected (0.05 sec)
mysql> CALL my_sqrt(9,@out_value) ;
Query OK, 0 rows affected (0.05 sec)
mysql> SELECT @out_value;
+————+
| @out_value |
+————+
| 3 |
+————+
Memampilkan nilai keluaran :
Kondisional
Perintah kondisional, untuk melakukan uji kondisi terapat satu masukan dan dua piliahan dari keluaran, kondisi benar atau asalah. Perintahnya menggunakan IF, atau CASE.
DELIMITER$$
CREATE PROCEDURE Diskon(pembayaran NUMERIC(10,2),OUT diskon NUMERIC(8,2))
BEGIN
IF (pembayaran > 100000) THEN
SET diskon=pembayaran * 0.8;
ELSEIF (pembayaran > 500000) THEN
SET diskon=pembayaran * 0.9;
ELSE
SET diskon=pembayaran;
END IF;
END$$;
DELIMITER;
Cara memanggilnya :
mysql> CALL diskon(100000,@new_price) ;
Query OK, 0 rows affected (0.05 sec)
mysql> SELECT @new_price;
+————+
| @new_price |
+————+
| 100000.00 |
+————+
1 row in set (0.00 sec)
Loop
Loop adalah proses perulangan. Dalam MYSQL ada 3 type loop :
• Loop sederhana menggunakan LOOP dan END LOOP c
• Loop yang disertai kontrol kondisi benar atau salah jika benar akan loop, perintannya menggunakan WHILE dan END WHILE
• Perulangan yang menggunakan REAPEAT dan UNTIL
Contoh :
DELIMITER$$
CREATE PROCEDURE Loop_sederhana()
BEGIN
DECLARE konter INT DEFAULT 0;
atas:LOOP
SET konter=konter+1;
IF konter=10 THEN
LEAVE atas;
END IF;
SELECT konter;
END LOOP atas;
SELECT ‘Konter ke 10';
END$$;
DELIMITER;
LEAVE untuk mangarah ke label atas.
Interaktif dengan Database
Stored Prosedure juga basi melibatkan data dalam tabel. Ada 4 bagian tipe interaksi antara lian :
Hasil stored procedure dari perintah SQL menghasilkan nilai balik satu baris yang dimasukan ke dalam variabel lokal.
Create “cursor” hasil iterasi, yanng mengasilkan baris disimpan ke kursor
Eksekusi perintah SQL menghasilkan, dengan perintah CALL progarm akan menghasilkan sejumlah baris.
Untuk perintah INSERT, UPDATE, DELETE, tidak mengasilkan record set
SELECT INTO Menggunakan Variabel Lokal
Dalam Stored procedure seperti halnya pemroraman biasa, terdapat variabel lokal, yaitu variabel yang menampung data sementara. Untuk mengisikan nilai variabel tersebut dengan perintah INTO
Contoh :
Buatalah tabel JUALAN
CREATE TABLE JUALAN(no_tran char (5),
kd_plg char(5),
tgl date,
nil_tran int);
Tambahkan rekaman
INSERT INTO JUALAN VALUES(’00001',’P0001',’2009-07-10',4000000);
INSERT INTO JUALAN VALUES(’00002',’P0001',’2009-07-11',5000000);
INSERT INTO JUALAN VALUES(’00003',’P0002',’2009-07-11',700000);
INSERT INTO JUALAN VALUES(’00004',’P0003',’2009-09-10',4030000);
Menciptakan Stored procedure
DELIMITER$$
DROP PROCEDURE IF EXISTS pelanggan_jualan;
$$
CREATE PROCEDURE pelanggan_jualan(in_kd_plg char(5))
BEGIN
DECLARE Total_jual INT ;
SELECT SUM(nil_tran) INTO Total_jual
FROM JUALAN
WHERE kd_plg = in_kd_plg;
SELECT CONCATE(‘Jumlah Transaksi Pelanggan No,:’,in_kd_plg,’ adalah =’,Total_jual);
END;
$$
Penjelasan
Cara memanggil Stored precedure seperti pada gambar berkut :
sumber : http://www.akakom.ac.id/webakakom/v001/index.php/site/artikel/54
Apa yang dimaksud dengan prorgram stored procdure?
Database server telah mendukukung bebrapa proses transaksi yang dikerjakan oleh server.
Program tersbut diciptakan oleh user diletakkan dan dieksekusi dalam database server.
Terdapat 3 bagian penting Program yang tersimpan dalam database Server
1. Stored Procedure
Sejumlah perintah dalam kode program dan beberapa parameter input, dan sejumlah hasil atau keluaran. Cara pemanggilannya dengan mengeksekusi nama proscdure tersebut.
2. Stored Function
Fungsi hampir sama dengan stored procedure. Function mempunyai sejumlah paramter input dan hanya mengembalikan satu output, standar perintah yang digunakan menggunakan perintah standar SQL.
3. Trigger
Suatu program yang akan melakukan respon dalam aktivitas database, berkaitan perintah-perintah DML (insert, update, delete) operasional pada tabel. Biasanya digunakan untuk otomatisasi atau validasi.
Pemrograman Stored Prosedure dalam MySQL.
Stored procedure adalah permasalah yang cukup kompleks untuk dibahas, adapun bagian penting pada stored procedure :
Kapan program akan diabut, parameter yang akan dilewatkan dan hasil yang dikeluarkan, interaksi dalam basis data, menciptakan stored precedure, fungsi dan trigger dalam bahasa pemrograman.
Apa yang dibutuhkan?
• MySQL Server
• Teks editor vi, emacs,
• MySQL Query Browser, SQLyog dll
Manciptakan Stored Procedure
Beberapa perintah untuk membuat stored procedure:
CREATE PROCEDURE , CREATE FUNCTION , atau CREATE TRIGGER
Contoh :
DELIMITER $$
DROP PROCEDURE IF EXISTS Kata()$$;
CREATE PROCEDURE Kata()
BEGIN
SELECT 'STMIK AKAKOM OK';
END$$
DELIMITER ;
Penjelasan :
DELIMITER $$ akhir dari peritah body (badan/skrip mysql)
DROP PROCEDURE IF EXISTS Kata()$$; perintah untuk menghapus jika prosedurnya sudah pernah ada.
CREATE PROCEDURE Kata( ) mencitapkan prosedur degan nama Kata ( isi parameter kalau ada ),
BEGIN awal perintah/blok perintah dan END akhir perintah
SELECT perintah menapilkan
Cara memanggilanya :
Enter password: ****
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 6.0.4-alpha-community-log MySQL Community Server (GPL)
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the buffer.
mysql> use coba;
Database changed
mysql> CALL Kata();
+——–+
| AKAKOM |
+——–+
| STMIK AKAKOM OK|
+——–+
1 row in set (0.00 sec)
Variabel
Variabel dalam bahasa SQL, fungsinya sama dengan bahasa pemrograman paxda umumnya.
Perintahnya menggunakan DECLARE, dan untuk memberikan nilai menggunakan perintah SET.
Parameter
Parameter adalah suatu nilai yanga akan dilewatkan atau dimasukan untuk diproses dalam fungsi atau prosedure.
Contoh
DELIMITER$$
CREATE PROCEDURE Demo_Var()
BEGIN
DECLARE x int;
DECLARE y int;
SET x=10;
SET y=20;
SELECT x+y;
END$$;
DELIMITER;
CALL Demo_var
Hasil
+——+
| x+y |
+——+
| 30 |
+——+
1 row in set (0.00 sec)
Sebuah Procedure dengan mengunakan parameter masukan, akan diproses dalam fungsi dan kemudian timapilkan, hasilnya.
Contoh :
DELIMITER$$
CREATE PROCEDURE my_akar(masukan INT)
BEGIN
DECLARE hasil FLOAT;
SET hasil= SQRT(masukan);
SELECT hasil;
END$$;
DELIMITER;
Cara memanggil prosedur
CALL my_akar(25);
Hasil keluaran sebagai berikut :
mysql> CALL my_akar(25);
+——-+
| hasil |
+——-+
| 5 |
+——-+
1 row in set (0.03 sec)
Query OK, 0 rows affected (0.05 sec)
Prosedure menggunakan parameter masukan dan keluaran (INT dan OUT)
Contoh :
DELIMITER$$
CREATE PROCEDURE my_sqrt(masukan INT, OUT nilai_keluar FLOAT)
BEGIN
SET nilai_keluar= SQRT(masukan);
END$$;
DELIMITER;
Cara menjalankan prosedur ada dua tahap
CALL my_sqrt(9,@out_value)
SELECT @out_value
Penjelasan :
Memampilkan nilai keluaran :
mysql> CALL my_akar(25);
+——-+
| hasil |
+——-+
| 5 |
+——-+
1 row in set (0.03 sec)
Query OK, 0 rows affected (0.05 sec)
mysql> CALL my_sqrt(9,@out_value) ;
Query OK, 0 rows affected (0.05 sec)
mysql> SELECT @out_value;
+————+
| @out_value |
+————+
| 3 |
+————+
Memampilkan nilai keluaran :
Kondisional
Perintah kondisional, untuk melakukan uji kondisi terapat satu masukan dan dua piliahan dari keluaran, kondisi benar atau asalah. Perintahnya menggunakan IF, atau CASE.
DELIMITER$$
CREATE PROCEDURE Diskon(pembayaran NUMERIC(10,2),OUT diskon NUMERIC(8,2))
BEGIN
IF (pembayaran > 100000) THEN
SET diskon=pembayaran * 0.8;
ELSEIF (pembayaran > 500000) THEN
SET diskon=pembayaran * 0.9;
ELSE
SET diskon=pembayaran;
END IF;
END$$;
DELIMITER;
Cara memanggilnya :
mysql> CALL diskon(100000,@new_price) ;
Query OK, 0 rows affected (0.05 sec)
mysql> SELECT @new_price;
+————+
| @new_price |
+————+
| 100000.00 |
+————+
1 row in set (0.00 sec)
Loop
Loop adalah proses perulangan. Dalam MYSQL ada 3 type loop :
• Loop sederhana menggunakan LOOP dan END LOOP c
• Loop yang disertai kontrol kondisi benar atau salah jika benar akan loop, perintannya menggunakan WHILE dan END WHILE
• Perulangan yang menggunakan REAPEAT dan UNTIL
Contoh :
DELIMITER$$
CREATE PROCEDURE Loop_sederhana()
BEGIN
DECLARE konter INT DEFAULT 0;
atas:LOOP
SET konter=konter+1;
IF konter=10 THEN
LEAVE atas;
END IF;
SELECT konter;
END LOOP atas;
SELECT ‘Konter ke 10';
END$$;
DELIMITER;
LEAVE untuk mangarah ke label atas.
Interaktif dengan Database
Stored Prosedure juga basi melibatkan data dalam tabel. Ada 4 bagian tipe interaksi antara lian :
Hasil stored procedure dari perintah SQL menghasilkan nilai balik satu baris yang dimasukan ke dalam variabel lokal.
Create “cursor” hasil iterasi, yanng mengasilkan baris disimpan ke kursor
Eksekusi perintah SQL menghasilkan, dengan perintah CALL progarm akan menghasilkan sejumlah baris.
Untuk perintah INSERT, UPDATE, DELETE, tidak mengasilkan record set
SELECT INTO Menggunakan Variabel Lokal
Dalam Stored procedure seperti halnya pemroraman biasa, terdapat variabel lokal, yaitu variabel yang menampung data sementara. Untuk mengisikan nilai variabel tersebut dengan perintah INTO
Contoh :
Buatalah tabel JUALAN
CREATE TABLE JUALAN(no_tran char (5),
kd_plg char(5),
tgl date,
nil_tran int);
Tambahkan rekaman
INSERT INTO JUALAN VALUES(’00001',’P0001',’2009-07-10',4000000);
INSERT INTO JUALAN VALUES(’00002',’P0001',’2009-07-11',5000000);
INSERT INTO JUALAN VALUES(’00003',’P0002',’2009-07-11',700000);
INSERT INTO JUALAN VALUES(’00004',’P0003',’2009-09-10',4030000);
Menciptakan Stored procedure
DELIMITER$$
DROP PROCEDURE IF EXISTS pelanggan_jualan;
$$
CREATE PROCEDURE pelanggan_jualan(in_kd_plg char(5))
BEGIN
DECLARE Total_jual INT ;
SELECT SUM(nil_tran) INTO Total_jual
FROM JUALAN
WHERE kd_plg = in_kd_plg;
SELECT CONCATE(‘Jumlah Transaksi Pelanggan No,:’,in_kd_plg,’ adalah =’,Total_jual);
END;
$$
Penjelasan
- DELIMITER : awal skrip
- DROP PROCEDURE IF EXISTS : menghapus jika prosedure pelanggan_jualan ada, jika tidak diabaikan.
- BEGIN : awal blok perintah (proses)
- DECLARE Total_jual INT : bagian deklarasi variabel
- SELECT SUM(nil_tran) INTO Total_jual FROM JUALAN WHERE kd_plg = in_kd_plg; : perintah SQL menpilkan
- SELECT CONCATE(‘Jumlah Transaksi Pelanggan No,:’,in_kd_plg,’ adalah =’,Total_jual); : hasil ditampilkan kelayar dengan keterangannya
- END; akhir dari blok proses
Cara memanggil Stored precedure seperti pada gambar berkut :
sumber : http://www.akakom.ac.id/webakakom/v001/index.php/site/artikel/54
Perhitungan Tanggal Dan Waktu Dengan MySQL
Acap kali kita sering
behubungan perhitungan tanggal dan waktu. Perhitungan tanggal misalnya
untuk mengetahui jumlah hari antara tanggal ke tanggal, atau menghitung
jumlah hari dalam transaksi, keterlambatan (jatuh tempo). Kemudian jam,
atau perhitungan jam kerja karyawan, yang melibatkan jam datang dan jam
pulang, kemudian akan diolah menjadi jumlah jam perbulan dsb.
Pada bagian ini saya akan mencoba mabahas dan contoh penggunaan perhitungan tanggal dan waktu menggunakan MySQL.
Untuk merepresentasikan nilai tanggal, jam dan waktu, seperti pada tabel berikut :
MONTHNAME(date)
date : kolom atau variabel tanggal
Contoh:
Bulan ditampilkan dengan bahasa Inggris
DAYNAME(date)
date : kolom atau variabel tanggal
Contoh :
Date adalah untuk menyimpam tanggal system, dengan format ‘YYYY-MM-DD’ tahun-bulan-tanggal, range antara ’1000-01-01′ sampai ’9999-12-31′.CURRENT_DATE, CURRENT_DATE(), CURDATE();
Fungsi untuk membaca atau menampilkan tanggal sekarang pada sistem Contoh :
mysql> SELECT CURRENT_DATE;
+————–+
| CURRENT_DATE |
+————–+
| 2009-11-07 |
+————–+
1 row in set (0.00 sec)
Atau
mysql> SELECT CURRENT_DATE();
+—————-+
| CURRENT_DATE() |
+—————-+
| 2009-11-07 |
+—————-+
1 row in set (0.00 sec)
Bisa juga dengan fungsi berkut :
mysql> SELECT CURDATE();
+————+
| CURDATE() |
+————+
| 2009-11-07 |
+————+
1 row in set (0.00 sec)
Menambah Tanggal
Misalnya kati akan mencari 45 hari dari sekarang nanti jatuh pada tanggal berapa?. Tentunya untuk menhitungnya tidak sederhana, karena tiap bulan tidak semua bulan jumlah harinya 30, atau sebaliknya antara tanggal satu dengan yang lainya ada berapa hari.
Untuk menghitung jatuh tanggal berapa 5 dari sekarang menggunakan fungsi berkut :
ADDDATE(date,INTERVAL expr unit), ADDDATE(expr,days)
Date : tanggal
INTERVAL : perintah memberikan nilai interger ke sistem tanggal dalam hari
Contoh :
mysql>SELECT CURDATE(), DATE_ADD(CURDATE(), INTERVAL 31 DAY);
+————+————————————–+
| CURDATE() | DATE_ADD(CURDATE(), INTERVAL 31 DAY) |
+————+————————————–+
| 2009-11-07 | 2009-12-08 |
+————+————————————–+
1 row in set (0.00 sec) Penjelasan :
CURDATE() : tanggal sekarang
INTERVAL 31 DAY : ditambah 31 hari = 2009-12-08
Atau bisa juga
mysql> SELECT ADDDATE(’2008-02-29', INTERVAL 1 DAY);
+—————————————+
| ADDDATE(’2008-02-29', INTERVAL 1 DAY) |
+—————————————+
| 2008-03-01 |
+—————————————+
1 row in set (0.00 sec)
mysql>
atau
mysql> SELECT ADDDATE(’2008-01-02', 31);
+—————————+
| ADDDATE(’2008-01-02', 31) |
+—————————+
| 2008-02-02 |
+—————————+
1 row in set (0.00 sec) Pengurangan data tanggal
Pengurangan tanggal maksudnya adalah untuk menghitung mundur, jatuh tanggal berapa misal 10 hari sebelum tanggal tertentu/sekarang. Adapun fungsi untuk menghitungnya adalah :
SUBDATE(date,INTERVAL expr unit), SUBDATE(expr,days)
Contoh :
mysql> SELECT DATE_SUB(’2009-01-02', INTERVAL 31 DAY);
+—————————————–+
| DATE_SUB(’2009-01-02', INTERVAL 31 DAY) |
+—————————————–+
| 2008-12-02 |
+—————————————–+
1 row in set (0.00 sec)
Penjelasan :
date : tanggal sekarang/tertentu
INTERVAL 31 DAY : dikurang 31 hari = 2009-12-08
Atau
mysql> SELECT SUBDATE(’2009-01-02', INTERVAL 31 DAY);
+—————————————-+
| SUBDATE(’2009-01-02', INTERVAL 31 DAY) |
+—————————————-+
| 2008-12-02 |
+—————————————-+
1 row in set (0.00 sec)
Mengubah Format Tanggal
Format tampilan tanggal di MySQL umumnya tahun-bulan-tanggal, nah biasanya kebiasaan kita tanggal formatnya adalah tanggal-bulan-tahun. Nah untuk membuat tampilan ini telah tersedia fungsi STR_TO_DATE().
STR_TO_DATE(str,format)
Data tanggal ketika dikenakan perintah SELECT hasilnya teks dengan format dd-mm-yyyy, maka untuk mengubah format teks tersebut bisa menggunakan DATE_FORMAT
Contoh:
mysql> SELECT DATE_FORMAT(’2009-10-04 22:23:00', ‘%W %M %Y’);
+————————————————+
| DATE_FORMAT(’2009-10-04 22:23:00', ‘%W %M %Y’) |
+————————————————+
| Sunday October 2009 |
+————————————————+
1 row in set (0.00 sec)
%W : hari dalam bahasa Inggris
%M : bulan dalam bahasa Inggris
%Y : tahun 4 digit
Contoh berkutnya :
Mengubah tampilan tanggal ke dd-mm-yyyy
mysql> SELECT DATE_FORMAT(’2009-10-04 22:23:00', ‘%d-%m-%Y’);
+————————————————+
| DATE_FORMAT(’2009-10-04 22:23:00', ‘%d-%m-%Y’) |
+————————————————+
| 04-10-2009 |
+————————————————+
1 row in set (0.00 sec)
mysql> SELECT DAYNAME(’2007-02-03');
+———————–+
| DAYNAME(’2007-02-03') |
+———————–+
| Saturday |
+———————–+
1 row in set (0.00 sec) Mencari hari ke…. dalam minggu
Tanggal sekarang misalnya hari ke berapa? Dalam angka dari minggu sekarang. Untuk itu menggunakan fungsi DAYOFWEEK(date)
SELECT DAYOFWEEK(date)
Misalnya untuk menampilkan hari dalam bahasa Indonesia (Minggu, Senin, Selasa, Rabu…., Sabtu) kita bisa menciptakan fungsi sendiri dengan melibatkan fungsi DAYOFWEEK(), misalkan fungsinya duberi nama get_hari(date), lihat skrip berikut :
Contoh:
Untuk lebih lanjut penulisan format tanggal dan waktu seperti pada tabel berkut :
Hasil pengujian
mysql> SELECT now(), get_hari(now());
+———————+—————–+
| now() | get_hari(now()) |
+———————+—————–+
| 2009-11-09 14:45:29 | Senin |
+———————+—————–+
1 row in set, 1 warning (0.00 sec)
mysql>
Penjelasan:
get_hari(now()) : memanggil fungsi dengan parameter tanggal sekarang
Contoh Kasus Menghitung Jam Kerja Pegawai
Perhitungan jam kerja pegawai biasanya digunakan untuk sistem presensi. Ada permasalahan khusus jika jam datang dan jam pulang tanggalnya beda atau hari yang berbeda. Sebagai contoh untuk pegawai satpam misalnya untuk shift kerja yang malam biasanya datang jam 23.00 malam, dan pulang besok pagi jam 07.00, dengan demikian untuk menghitung jam kerjanya jadi negatif.
Jika anda menggunakan database MySQL jam kerja pegawai akan derekam sebaiknya menggunakan jenis field DateTime. Alasan penggunaan filed Datetime agar supaya peritungan jumlah jam bisa dihitung menggunakan perintah SQL. Jadi data yang tersimpan nanti berupa tanggal dan jam.
Contoh:
Buatlah rancangan tabel PEGAWAI seperti pada skript berkut :
CREATE TABLE pegawai (NIP char(4),
nama char(30),
job char(25),
primary key (NIP));
Tambahkan rekaman sebagai berkut :
INSERT INTO PEGAWAI VALUES('1001','Adel Untung','Staff');
INSERT INTO PEGAWAI VALUES('1002','Temon','OB');
INSERT INTO PEGAWAI VALUES('1003','Bejo ','Satpan');
INSERT INTO PEGAWAI VALUES('1004','Tukul Paimin','Staff');
INSERT INTO PEGAWAI VALUES('1005','Tunggul ','Satpam');
Tabel Presensi
CREATE TABLE absensi (
NIP char(4) DEFAULT NULL COMMENT 'Nomor Pegwai',
JAM_DTG datetime NOT NULL COMMENT 'Tanggal jam datang',
JAM_PLG datetime NOT NULL COMMENT 'Tanggal jam pulang',
shift int(11) DEFAULT '1' COMMENT 'kode shift kerja');
Tambahkan isi rekaman seperti tabel berikut :
insert into absensi(NIP,JAM_DTG,JAM_PLG,shift)
values ('1001','2009-10-17 09:20:22','2009-10-17 14:19:03',1),
('1002','2009-10-17 09:10:10','2009-10-17 10:20:50',1),
('1003','2009-10-17 23:19:50','2009-10-18 07:19:50',3),
('1005','2009-10-17 07:20:28','2009-10-17 15:13:26',1),
('1004','2009-10-17 07:30:00','2009-10-17 14:30:00',1),
('1001','2009-10-18 07:30:01','2009-10-18 15:22:12',1),
('1001','2009-10-19 07:20:11','2009-10-19 15:30:33',1),
('1002','2009-10-19 07:10:10','2009-10-19 14:40:30',1),
('1004','2009-10-19 08:00:30','2009-10-19 15:00:30',1),
('1003','2009-10-19 15:00:30','2009-10-19 23:00:30',2),
('1005','2009-10-19 23:00:30','2009-10-20 07:00:30',1);
Presensi datang
INSERT INTO absensi VALUES('1001',NOW(),NOW(),'1');
Presensi Pulang
UPDATE absensi SET JAM_PLG=NOW()
WHERE NIP=’1001' AND
SUBSTR(JAM_DTG,1,10)=SUBSTR(NOW(),1,10);
Penjelasan :
Hanya mengubah jam pulang saja dengan kunci pencarian seperti perintah berikut :
WHERE NIP=’1001' AND SUBSTR(JAM_DTG,1,10)=SUBSTR(NOW(),1,10);
Kunci pencarian adalah NIP dan tanggal presensidata yang telah direkam seperti pada tabel berikut :
Menampilkan Presensi semua pegawai tanggal ’2009-10-17'
SELECT * FROM ABSENSI WHERE SUBSTR(JAM_DTG,1,10)=’2009-10-17' ;
Penjelasan : SUBSTR(JAM_DTG,1,10)=’2009-10-17': adalah untuk membaca tanggalnya saja sebab yang terekam pada field JAM_DTG tanggal dan jam panjang fieldnya 20 digit. Menampilkan presensi semua pegawai tanggal '2009-10-17' dangan menghitung jumlah jam perhari.
SELECT nip,jam_dtg,jam_plg,TIMEDIFF(jam_plg,jam_dtg) as jam_menit_detik
FROM absensi
WHERE SUBSTR(JAM_DTG,1,10)='2009-10-17' ;
Hasil keluaran :
Penjalasan:
Perhitungan TIMEDIFF(jam_plg,jam_dtg):
adalah untuk mengurangi tanggal jam pulang dengan tanggal jam datang hasilnya jam:menit:detikMenampilkan presensi semua pegawai tanggal '2009-10-17' sampai dengan ‘2007-10-19’ untuk pegawai dengan NIP=’1001’ dan menghitung jumlah jam perhari.
SELECT nip,jam_dtg,jam_plg, TIMEDIFF(jam_plg,jam_dtg) as jam_menit_detik
FROM absensi
WHERE SUBSTR(JAM_DTG,1,10)>='2009-10-17' AND SUBSTR(JAM_DTG,1,10)<='2009-10-19'
AND NIP='1001' ;
Hasil keluaran :
Menampilkan presensi jumlah jam kerja semua pegawai periode tanggal '2009-10-17' sampai dengan '2007-10-19’
SELECT nip,
SUM(time_to_Sec(TIMEDIFF(jam_plg,jam_dtg))) as detik,
sec_to_time(SUM(time_to_Sec(TIMEDIFF(jam_plg,jam_dtg)))) as jumlah
FROM absensi
WHERE SUBSTR(JAM_DTG,1,10)>=’2009-10-17' AND SUBSTR(JAM_DTG,1,10)<=’2009-10-19'
GROUP BY NIP;
Hasil keluaran:
Penjelasan:TIMEDIFF(jam_plg,jam_dtg) fungsi jam pulang dikurangi jam datang
time_to_Sec(TIMEDIFF(jam_plg,jam_dtg)) konversi format waktu ke detik
dijumlahkan pakai SUM( ), kemudian dikembalikan sec_to_time( );
Menampilkan jam kerja beserta nama
Untuk perintah yang menggunakan GROUP BY NIP tidak bisa direlasikan secara langsung, karena perintah SELECT akan mengambil seluruh rekaman yang dipenuhi, dengan ada perintah GROUP BY NIP akan dikelompokan perpegawai, sedang rekaman satu pegawai memiliki sejumlah rekaman. Oleh karna itu perlu dibuat fungsi untuk memabaca nama.
Menampilkan presensi nip, nama dan jumlah jam kerja semua pegawai periode tanggal ’2009-10-17' sampai dengan ‘2007-10-19’Contoh :
SELECT nip,get_nama_peg(nip)as nama,
SUM(time_to_Sec(TIMEDIFF(j
am_plg,jam_dtg))) as detik,
sec_to_time(SUM(time_to_Sec(TIMEDIFF(jam_plg,jam_dtg)))) as jumlah
FROM absensi
WHERE SUBSTR(JAM_DTG,1,10)>=’2009-10-17' AND SUBSTR(JAM_DTG,1,10)<=’2009-10-19'
GROUP BY NIP;
Hasil keluaran :
Selamat mencoba:
sumber: http://www.akakom.ac.id/webakakom/v001/index.php/site/artikel/53
Pada bagian ini saya akan mencoba mabahas dan contoh penggunaan perhitungan tanggal dan waktu menggunakan MySQL.
Untuk merepresentasikan nilai tanggal, jam dan waktu, seperti pada tabel berikut :
Fungsi nama Bulan
Adapun fungsi untuk menampilkan nama bulan adalah MONTHNAME().MONTHNAME(date)
date : kolom atau variabel tanggal
Contoh:
mysql> SELECT MONTHNAME(’2009-11-03′);
+————————-+
| MONTHNAME(’2009-11-03′) |
+————————-+
| November |
+————————-+
1 row in set (0.00 sec)
Bulan ditampilkan dengan bahasa Inggris
Manpilkan nama hari
Sistem basis data untuk menampilkan nama hari menggunakan fungsi DAYNAME().DAYNAME(date)
date : kolom atau variabel tanggal
Contoh :
Date adalah untuk menyimpam tanggal system, dengan format ‘YYYY-MM-DD’ tahun-bulan-tanggal, range antara ’1000-01-01′ sampai ’9999-12-31′.CURRENT_DATE, CURRENT_DATE(), CURDATE();
Fungsi untuk membaca atau menampilkan tanggal sekarang pada sistem Contoh :
mysql> SELECT CURRENT_DATE;
+————–+
| CURRENT_DATE |
+————–+
| 2009-11-07 |
+————–+
1 row in set (0.00 sec)
Atau
mysql> SELECT CURRENT_DATE();
+—————-+
| CURRENT_DATE() |
+—————-+
| 2009-11-07 |
+—————-+
1 row in set (0.00 sec)
Bisa juga dengan fungsi berkut :
mysql> SELECT CURDATE();
+————+
| CURDATE() |
+————+
| 2009-11-07 |
+————+
1 row in set (0.00 sec)
Menambah Tanggal
Misalnya kati akan mencari 45 hari dari sekarang nanti jatuh pada tanggal berapa?. Tentunya untuk menhitungnya tidak sederhana, karena tiap bulan tidak semua bulan jumlah harinya 30, atau sebaliknya antara tanggal satu dengan yang lainya ada berapa hari.
Untuk menghitung jatuh tanggal berapa 5 dari sekarang menggunakan fungsi berkut :
ADDDATE(date,INTERVAL expr unit), ADDDATE(expr,days)
Date : tanggal
INTERVAL : perintah memberikan nilai interger ke sistem tanggal dalam hari
Contoh :
mysql>SELECT CURDATE(), DATE_ADD(CURDATE(), INTERVAL 31 DAY);
+————+————————————–+
| CURDATE() | DATE_ADD(CURDATE(), INTERVAL 31 DAY) |
+————+————————————–+
| 2009-11-07 | 2009-12-08 |
+————+————————————–+
1 row in set (0.00 sec) Penjelasan :
CURDATE() : tanggal sekarang
INTERVAL 31 DAY : ditambah 31 hari = 2009-12-08
Atau bisa juga
mysql> SELECT ADDDATE(’2008-02-29', INTERVAL 1 DAY);
+—————————————+
| ADDDATE(’2008-02-29', INTERVAL 1 DAY) |
+—————————————+
| 2008-03-01 |
+—————————————+
1 row in set (0.00 sec)
mysql>
atau
mysql> SELECT ADDDATE(’2008-01-02', 31);
+—————————+
| ADDDATE(’2008-01-02', 31) |
+—————————+
| 2008-02-02 |
+—————————+
1 row in set (0.00 sec) Pengurangan data tanggal
Pengurangan tanggal maksudnya adalah untuk menghitung mundur, jatuh tanggal berapa misal 10 hari sebelum tanggal tertentu/sekarang. Adapun fungsi untuk menghitungnya adalah :
SUBDATE(date,INTERVAL expr unit), SUBDATE(expr,days)
Contoh :
mysql> SELECT DATE_SUB(’2009-01-02', INTERVAL 31 DAY);
+—————————————–+
| DATE_SUB(’2009-01-02', INTERVAL 31 DAY) |
+—————————————–+
| 2008-12-02 |
+—————————————–+
1 row in set (0.00 sec)
Penjelasan :
date : tanggal sekarang/tertentu
INTERVAL 31 DAY : dikurang 31 hari = 2009-12-08
Atau
mysql> SELECT SUBDATE(’2009-01-02', INTERVAL 31 DAY);
+—————————————-+
| SUBDATE(’2009-01-02', INTERVAL 31 DAY) |
+—————————————-+
| 2008-12-02 |
+—————————————-+
1 row in set (0.00 sec)
Mengubah Format Tanggal
Format tampilan tanggal di MySQL umumnya tahun-bulan-tanggal, nah biasanya kebiasaan kita tanggal formatnya adalah tanggal-bulan-tahun. Nah untuk membuat tampilan ini telah tersedia fungsi STR_TO_DATE().
STR_TO_DATE(str,format)
Data tanggal ketika dikenakan perintah SELECT hasilnya teks dengan format dd-mm-yyyy, maka untuk mengubah format teks tersebut bisa menggunakan DATE_FORMAT
Contoh:
mysql> SELECT DATE_FORMAT(’2009-10-04 22:23:00', ‘%W %M %Y’);
+————————————————+
| DATE_FORMAT(’2009-10-04 22:23:00', ‘%W %M %Y’) |
+————————————————+
| Sunday October 2009 |
+————————————————+
1 row in set (0.00 sec)
%W : hari dalam bahasa Inggris
%M : bulan dalam bahasa Inggris
%Y : tahun 4 digit
Contoh berkutnya :
Mengubah tampilan tanggal ke dd-mm-yyyy
mysql> SELECT DATE_FORMAT(’2009-10-04 22:23:00', ‘%d-%m-%Y’);
+————————————————+
| DATE_FORMAT(’2009-10-04 22:23:00', ‘%d-%m-%Y’) |
+————————————————+
| 04-10-2009 |
+————————————————+
1 row in set (0.00 sec)
mysql> SELECT DAYNAME(’2007-02-03');
+———————–+
| DAYNAME(’2007-02-03') |
+———————–+
| Saturday |
+———————–+
1 row in set (0.00 sec) Mencari hari ke…. dalam minggu
Tanggal sekarang misalnya hari ke berapa? Dalam angka dari minggu sekarang. Untuk itu menggunakan fungsi DAYOFWEEK(date)
SELECT DAYOFWEEK(date)
Misalnya untuk menampilkan hari dalam bahasa Indonesia (Minggu, Senin, Selasa, Rabu…., Sabtu) kita bisa menciptakan fungsi sendiri dengan melibatkan fungsi DAYOFWEEK(), misalkan fungsinya duberi nama get_hari(date), lihat skrip berikut :
Contoh:
Untuk lebih lanjut penulisan format tanggal dan waktu seperti pada tabel berkut :
Hasil pengujian
mysql> SELECT now(), get_hari(now());
+———————+—————–+
| now() | get_hari(now()) |
+———————+—————–+
| 2009-11-09 14:45:29 | Senin |
+———————+—————–+
1 row in set, 1 warning (0.00 sec)
mysql>
Penjelasan:
get_hari(now()) : memanggil fungsi dengan parameter tanggal sekarang
Contoh Kasus Menghitung Jam Kerja Pegawai
Perhitungan jam kerja pegawai biasanya digunakan untuk sistem presensi. Ada permasalahan khusus jika jam datang dan jam pulang tanggalnya beda atau hari yang berbeda. Sebagai contoh untuk pegawai satpam misalnya untuk shift kerja yang malam biasanya datang jam 23.00 malam, dan pulang besok pagi jam 07.00, dengan demikian untuk menghitung jam kerjanya jadi negatif.
Jika anda menggunakan database MySQL jam kerja pegawai akan derekam sebaiknya menggunakan jenis field DateTime. Alasan penggunaan filed Datetime agar supaya peritungan jumlah jam bisa dihitung menggunakan perintah SQL. Jadi data yang tersimpan nanti berupa tanggal dan jam.
Contoh:
Buatlah rancangan tabel PEGAWAI seperti pada skript berkut :
CREATE TABLE pegawai (NIP char(4),
nama char(30),
job char(25),
primary key (NIP));
Tambahkan rekaman sebagai berkut :
INSERT INTO PEGAWAI VALUES('1001','Adel Untung','Staff');
INSERT INTO PEGAWAI VALUES('1002','Temon','OB');
INSERT INTO PEGAWAI VALUES('1003','Bejo ','Satpan');
INSERT INTO PEGAWAI VALUES('1004','Tukul Paimin','Staff');
INSERT INTO PEGAWAI VALUES('1005','Tunggul ','Satpam');
Tabel Presensi
CREATE TABLE absensi (
NIP char(4) DEFAULT NULL COMMENT 'Nomor Pegwai',
JAM_DTG datetime NOT NULL COMMENT 'Tanggal jam datang',
JAM_PLG datetime NOT NULL COMMENT 'Tanggal jam pulang',
shift int(11) DEFAULT '1' COMMENT 'kode shift kerja');
Tambahkan isi rekaman seperti tabel berikut :
insert into absensi(NIP,JAM_DTG,JAM_PLG,shift)
values ('1001','2009-10-17 09:20:22','2009-10-17 14:19:03',1),
('1002','2009-10-17 09:10:10','2009-10-17 10:20:50',1),
('1003','2009-10-17 23:19:50','2009-10-18 07:19:50',3),
('1005','2009-10-17 07:20:28','2009-10-17 15:13:26',1),
('1004','2009-10-17 07:30:00','2009-10-17 14:30:00',1),
('1001','2009-10-18 07:30:01','2009-10-18 15:22:12',1),
('1001','2009-10-19 07:20:11','2009-10-19 15:30:33',1),
('1002','2009-10-19 07:10:10','2009-10-19 14:40:30',1),
('1004','2009-10-19 08:00:30','2009-10-19 15:00:30',1),
('1003','2009-10-19 15:00:30','2009-10-19 23:00:30',2),
('1005','2009-10-19 23:00:30','2009-10-20 07:00:30',1);
Presensi datang
INSERT INTO absensi VALUES('1001',NOW(),NOW(),'1');
Presensi Pulang
UPDATE absensi SET JAM_PLG=NOW()
WHERE NIP=’1001' AND
SUBSTR(JAM_DTG,1,10)=SUBSTR(NOW(),1,10);
Penjelasan :
Hanya mengubah jam pulang saja dengan kunci pencarian seperti perintah berikut :
WHERE NIP=’1001' AND SUBSTR(JAM_DTG,1,10)=SUBSTR(NOW(),1,10);
Kunci pencarian adalah NIP dan tanggal presensidata yang telah direkam seperti pada tabel berikut :
Menampilkan Presensi semua pegawai tanggal ’2009-10-17'
SELECT * FROM ABSENSI WHERE SUBSTR(JAM_DTG,1,10)=’2009-10-17' ;
Penjelasan : SUBSTR(JAM_DTG,1,10)=’2009-10-17': adalah untuk membaca tanggalnya saja sebab yang terekam pada field JAM_DTG tanggal dan jam panjang fieldnya 20 digit. Menampilkan presensi semua pegawai tanggal '2009-10-17' dangan menghitung jumlah jam perhari.
SELECT nip,jam_dtg,jam_plg,TIMEDIFF(jam_plg,jam_dtg) as jam_menit_detik
FROM absensi
WHERE SUBSTR(JAM_DTG,1,10)='2009-10-17' ;
Hasil keluaran :
Penjalasan:
Perhitungan TIMEDIFF(jam_plg,jam_dtg):
adalah untuk mengurangi tanggal jam pulang dengan tanggal jam datang hasilnya jam:menit:detikMenampilkan presensi semua pegawai tanggal '2009-10-17' sampai dengan ‘2007-10-19’ untuk pegawai dengan NIP=’1001’ dan menghitung jumlah jam perhari.
SELECT nip,jam_dtg,jam_plg, TIMEDIFF(jam_plg,jam_dtg) as jam_menit_detik
FROM absensi
WHERE SUBSTR(JAM_DTG,1,10)>='2009-10-17' AND SUBSTR(JAM_DTG,1,10)<='2009-10-19'
AND NIP='1001' ;
Hasil keluaran :
Menampilkan presensi jumlah jam kerja semua pegawai periode tanggal '2009-10-17' sampai dengan '2007-10-19’
SELECT nip,
SUM(time_to_Sec(TIMEDIFF(jam_plg,jam_dtg))) as detik,
sec_to_time(SUM(time_to_Sec(TIMEDIFF(jam_plg,jam_dtg)))) as jumlah
FROM absensi
WHERE SUBSTR(JAM_DTG,1,10)>=’2009-10-17' AND SUBSTR(JAM_DTG,1,10)<=’2009-10-19'
GROUP BY NIP;
Hasil keluaran:
Penjelasan:TIMEDIFF(jam_plg,jam_dtg) fungsi jam pulang dikurangi jam datang
time_to_Sec(TIMEDIFF(jam_plg,jam_dtg)) konversi format waktu ke detik
dijumlahkan pakai SUM( ), kemudian dikembalikan sec_to_time( );
Menampilkan jam kerja beserta nama
Untuk perintah yang menggunakan GROUP BY NIP tidak bisa direlasikan secara langsung, karena perintah SELECT akan mengambil seluruh rekaman yang dipenuhi, dengan ada perintah GROUP BY NIP akan dikelompokan perpegawai, sedang rekaman satu pegawai memiliki sejumlah rekaman. Oleh karna itu perlu dibuat fungsi untuk memabaca nama.
Menampilkan presensi nip, nama dan jumlah jam kerja semua pegawai periode tanggal ’2009-10-17' sampai dengan ‘2007-10-19’Contoh :
SELECT nip,get_nama_peg(nip)as nama,
SUM(time_to_Sec(TIMEDIFF(j
am_plg,jam_dtg))) as detik,
sec_to_time(SUM(time_to_Sec(TIMEDIFF(jam_plg,jam_dtg)))) as jumlah
FROM absensi
WHERE SUBSTR(JAM_DTG,1,10)>=’2009-10-17' AND SUBSTR(JAM_DTG,1,10)<=’2009-10-19'
GROUP BY NIP;
Hasil keluaran :
Selamat mencoba:
sumber: http://www.akakom.ac.id/webakakom/v001/index.php/site/artikel/53
Langganan:
Postingan (Atom)