Materi Stored Procedure Dan Function

Materi Stored Procedure dan Function 21OCT 21OCT File anda bisa download di sini Share on Facebook



StumbleUpon Digg



Reddit









COMMENTS COMMENTS Leave a Comment CATEGORIESTutorial MySQL Manajemen Transaksi 14DEC 14DEC Manajemen Transaksi Pengubahan, penambahan, penghapusan suatu tabel biasanya berdampak pada isi tabel satu dengan lainnya. Jika perubahan tabel satu dengan lainnya yang terkait, terjadi kagagalan, maka terjadi ketidakkonsistenan integrasi antar tabel. MySQL mendukung manajemen transaksi dengan syarat pada saat menciptakan tabel menggunakan mesin penyimpanan InnoDB. Pertama, yang kita bahas konsep dari level isolasi dan sesi Level Isolasi Sebelum kita berbicara transaksi dan level kelompok, kita perlu untuk jelaskan konsep dari suatu sesi. Suatu sesi database adalah suatu koneksi yang unik database yang memulai ketika anda login ke MySQL dan mengakhiri koneksi, dengan tegas ketika MySQL menberikan pesa bahwa program klien yang anda pakai terputus Tingkat isolasi suatu transaksi juga menentukan derajat tingkat bagi yang transaksi menenpati ACID yang akan uraikan dibagian ini. Masing-Masing dari empat tingkatan isolasi menghadirkan suatu keseimbangan yang berbeda antar isolasi dan concurrency dari transaksi. Di level isolasi yang paling tinggi, seluruh transaksi akan mampu melaksanakan secara bersamaan,. READ UNCOMMITTED Ini adalah tingkatan isolasi yang mungkin paling rendah. Kadang-kadang memanggil dirty read, tingkatan ini mengijinkan suatu transaksi membabaca rekaman yang belum di commit. Penggunaan tingkatan isolasi ini mungkin meningkatkan keberhasilan hanya satu pemakai yang mendapat kembali data yang diubah oleh pemakai lain..

READ COMMITTED Pada tingkatan isolasi ini, rekaman hanya dapat dilihat oleh suatu transaksi. Lagi pula, statemen hingga batas tertentu perubahan apapun yang dilakukan memulai eksekusi tidak bisa dilihat. Sebagai contoh, jika anda menjalankan perintah SELECT Suatu yang query dari tabel BUKU, dan sesi B memasukkan suatu baris ke dalam BUKU sedangkan suatu query masih menjalankan, baris yang baru itu tidak akan terlihat oleh perintah SELECT REPEATABLE READ Pada level isolasi ini tidak ada perubahan bagi database yang dibuat oleh lain sesi karena transaksi dapat dimulai dilihat di dalam transaksi, sampai transaksi dilakukan atau loop mundur atau ROLLBACK (pembatalan) jika Anda menjalankan ulang SELECT di dalam transaksi , akan selalu menunjukkan yang sama menghasilkan. SERIALIZABLE Pada tingkat isolasi, tiap-tiap transaksi dengan sepenuhnya terisolasi sedemikian rupa sehingga transaksi bertindak seolah-olah mereka telah mengeksekusi berturutan, satu demi satu; berturut-turut. Dalam rangka mencapai ini, RDBMS akan secara khusus mengunci tiaptiap baris yang dibaca, maka lain sesi tidak boleh memodifikasi data itu sampai transaksi telah selesai dengan itu. Kunci dilepaskan ketika Anda melakukan atau batalkan transaksi Bentuk Umum perintah tansakasi : SET TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED |REPEATABLE READ | SERIALIZABLE} Perintah Manajemen Transaction MySQL menggunakan yang berikut statemen manajemen transaksi : START TRANSACTION Marupakan awal dari blok perintah untuk melakukan transakasi COMMIT Melakukan perubahan atau secara fisik pada tebal ROLLBACK Perintah ini jika dijalankan suatu perubahan secara keseluruhan dalam blok transakasi dibatalakn. SAVEPOINT savepoint_name Menciptakan suatu savepoint yang dinamai identifier bahwa dapat target dari suatu ROLLBACK KE SAVEPOINT statemen. ROLLBACK TO SAVEPOINT savepoint_name Melaksanakan suatu rollback semua statemen yang telah dieksekusi sejak ditetapkan savepoint telah diciptakan. Dengan cara ini, kamu dapat mengulang mundur hanya bagian dari suatu transaksi, memelihara beberapa subset dari perubahan untuk tetap diselamatkan. SET TRANSACTION Ijinkan kamu untuk memilih level pengasingan terhadap transaksi. Secara detail terdapat pada bagian Level isolasi. LOCK TABLES Dengan tegas mengunci satu atau lebih tabel. Dicatat bahwa LOCK TABLES secara implisit

READ COMMITTED Pada tingkatan isolasi ini, rekaman hanya dapat dilihat oleh suatu transaksi. Lagi pula, statemen hingga batas tertentu perubahan apapun yang dilakukan memulai eksekusi tidak bisa dilihat. Sebagai contoh, jika anda menjalankan perintah SELECT Suatu yang query dari tabel BUKU, dan sesi B memasukkan suatu baris ke dalam BUKU sedangkan suatu query masih menjalankan, baris yang baru itu tidak akan terlihat oleh perintah SELECT REPEATABLE READ Pada level isolasi ini tidak ada perubahan bagi database yang dibuat oleh lain sesi karena transaksi dapat dimulai dilihat di dalam transaksi, sampai transaksi dilakukan atau loop mundur atau ROLLBACK (pembatalan) jika Anda menjalankan ulang SELECT di dalam transaksi , akan selalu menunjukkan yang sama menghasilkan. SERIALIZABLE Pada tingkat isolasi, tiap-tiap transaksi dengan sepenuhnya terisolasi sedemikian rupa sehingga transaksi bertindak seolah-olah mereka telah mengeksekusi berturutan, satu demi satu; berturut-turut. Dalam rangka mencapai ini, RDBMS akan secara khusus mengunci tiaptiap baris yang dibaca, maka lain sesi tidak boleh memodifikasi data itu sampai transaksi telah selesai dengan itu. Kunci dilepaskan ketika Anda melakukan atau batalkan transaksi Bentuk Umum perintah tansakasi : SET TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED |REPEATABLE READ | SERIALIZABLE} Perintah Manajemen Transaction MySQL menggunakan yang berikut statemen manajemen transaksi : START TRANSACTION Marupakan awal dari blok perintah untuk melakukan transakasi COMMIT Melakukan perubahan atau secara fisik pada tebal ROLLBACK Perintah ini jika dijalankan suatu perubahan secara keseluruhan dalam blok transakasi dibatalakn. SAVEPOINT savepoint_name Menciptakan suatu savepoint yang dinamai identifier bahwa dapat target dari suatu ROLLBACK KE SAVEPOINT statemen. ROLLBACK TO SAVEPOINT savepoint_name Melaksanakan suatu rollback semua statemen yang telah dieksekusi sejak ditetapkan savepoint telah diciptakan. Dengan cara ini, kamu dapat mengulang mundur hanya bagian dari suatu transaksi, memelihara beberapa subset dari perubahan untuk tetap diselamatkan. SET TRANSACTION Ijinkan kamu untuk memilih level pengasingan terhadap transaksi. Secara detail terdapat pada bagian Level isolasi. LOCK TABLES Dengan tegas mengunci satu atau lebih tabel. Dicatat bahwa LOCK TABLES secara implisit

menutup manapun transaksi yang sekarang ini terbuka. Kita merekomendasikan bahwa kamu dengan tegas melakukan atau loop ulang transaksi sebelum LOACK TABLES statemen apapun.. Contoh Transakasi Menggunakan SET AUTOCOMMIT dalam Procedure CREATE PROCEDURE tfer_funds (from_account int, to_account int,tfer_amount numeric(10,2)) BEGIN SET autocommit=0; UPDATE account_balance SET balance=balance-tfer_am bal ance=balance-tfer_amount ount WHERE account_id=from_account; UPDATE account_balance SET balance=balance+tfer_am balance=balance+tfer_amount ount WHERE account_id=to_account; COMMIT; END;

Transaksi menggunakan START TRANSACTION, dalam Procedure CREATE PROCEDURE tfer_funds (from_account int, to_account int,tfer_amount numeric(10,2)) BEGIN START TRANSACTION; UPDATE account_balance SET balance=balance-tfer_am bal ance=balance-tfer_amount ount WHERE account_id=from_account; UPDATE account_balance SET balance=balance+tfer_am balance=balance+tfer_amount ount WHERE account_id=to_account; COMMIT; END;

Seperti kita ketahui transaksi secara normal melengkapi, menjalankan perintah COMMIT maupun ROLLBACK statemen dieksekusi. Bagaimanapun, kita sadar bahwa beberapa statemen Data Definition Language ( DDL) mencari perintah menyebabkan COMMIT. Statemen yang secara implisit dilakukan, dan oleh karena itu dihindarkan ketika suatu transaksi barjalan, jika sedang menjalankan perintah berikut : ALTER FUNCTION ALTER PROCEDURE

ALTER TABLE BEGIN CREATE DATABASE CREATE FUNCTION CREATE INDEX CREATE PROCEDURE CREATE TABLE DROP DATABASE DROP FUNCTION DROP INDEX DROP PROCEDURE DROP TABLE UNLOCK TABLES LOAD MASTER DATA LOCK TABLES RENAME TABLE TRUNCATE TABLE SET AUTOCOMMIT=1 START TRANSACTION PRAKTIK CREATE TABLE mhs ( no_mhs char(4) DEFAULT NULL, nama char(25) DEFAULT NULL, alamat char(25) DEFAULT NULL ) ENGINE=InnoDB; CREATE TABLE `jurusan` ( `no_mhs` char(4) DEFAULT NULL, `kode` char(2) DEFAULT NULL ) ENGINE=InnoDB;

Transaksi penambahan reakaman menggunakan START TRANSACTION dan ROLLBACK; satu rekaman – Jalankan perintah transakasi

mysql> START TRANSACTION; Query OK, 0 rows affected (0.00 sec) – Penambahan rekama tabel mhs mysql> INSERT INTO mhs VALUES(‘0005′,‘Untung Raharja‘,'Bandung‘);

Query OK, 1 row affected (0.00 sec)

– Lihat hasil penambahan

mysql> select * from mhs; + ——– + ————— + ——— + | no_mhs | nama | alamat | + ——– + ————— -+ ——— + | 0001 | Agus | Solo | | 0002 | Budi | jogja | | 0003 | Bejo | Bantul | | 0004 | Ani | jogja | | 0005 | Untung Raharja | Bandung | + ——– + ————— -+ ——— + 5 rows in set (0.00 sec) – penjalesan – Mahasiswa untuk sumentara sudah direkam – Mambatalkan rekaman dengan perintah ROLLBACK; mysql> ROLLBACK; Query OK, 0 rows affected (0.05 sec) mysql> select * from mhs; + ——– + —— + ——– + | no_mhs | nama | alamat | + ——– + —— + ——– + | 0001 | Agus | Solo | | 0002 | Budi | jogja | | 0003 | Bejo | Bantul | | 0004 | Ani | jogja | + ——– + —— + ——– + 4 rows in set (0.00 sec) – Cek kembali mahasiswa bernama Untung tidak jadi direkam

Mari kita coba lagi penambahan lebih dari 1 rekamanan dengan blok transakasi Penambahan rekaman dengan START TRANSACTION dan ROLLBACK dua rekaman mysql> START TRANSACTION; mysql> INSERT INTO mhs VALUES(‘0005′,‘Untung Raharja‘,'Bandung‘); mysql> INSERT INTO mhs VALUES(‘0006′,‘Diah Ayu Subekti‘,'Semarang‘); mysql> select * from mhs; + ——– + —————— + ——— -+ | no_mhs | nama | alamat |

+ ——– + —————— + ——— -+ | 0001 | Agus | Solo | | 0002 | Budi | jogja | | 0003 | Bejo | Bantul | | 0004 | Ani | jogja | | 0005 | Untung Raharja | Bandung | | 0006 | Diah Ayu Subekti | Semarang | + ——– + —————— + ——— -+ 6 rows in set (0.00 sec) mysql> ROLLBACK; Query OK, 0 rows affected (0.05 sec) // lihat hasilnya mysql> select * from mhs; + ——– + —— + ——– + | no_mhs | nama | alamat | + ——– + —— + ——– + | 0001 | Agus | Solo | | 0002 | Budi | jogja | | 0003 | Bejo | Bantul | | 0004 | Ani | jogja | + ——– + —— + ——– + 4 rows in set (0.00 sec) mysql> Penjelasan : Telah ditambahkan 2 rekaman | 0005 | Untung Raharja | Bandung | | 0006 | Diah Ayu Subekti | Semarang | Kemudian pada saat menjalan perintah ROLLBACK 2 Rekaman tersebut dibatalkankan semua Penambahan rekaman dengan START TRANSACTION dan COMMIT dua rekaman atau lebih Perintah COMMIT, lihat contoh berikut : mysql> START TRANSACTION; mysql> INSERT INTO mhs VALUES(‘0005′,‘Untung Raharja‘,'Bandung‘); mysql> INSERT INTO mhs VALUES(‘0006′,‘Diah Ayu Subekti‘,'Semarang‘) mysql> COMMIT; Query OK, 0 rows affected (0.06 sec)

mysql> select * from mhs; + ——– + —————— + ——— -+ | no_mhs | nama | alamat | + ——– + —————— + ——— -+ | 0001 | Agus | Solo | | 0002 | Budi | jogja | | 0003 | Bejo | Bantul | | 0004 | Ani | jogja | | 0005 | Untung Raharja | Bandung | | 0006 | Diah Ayu Subekti | Semarang | + ——– + —————— + ——— -+ 6 rows in set (0.00 sec) Penjelasan : START TRANSACTION; : blok awal melakukan perintah transakasi Perintah : ISNERT, UPDATE, DELETE COMMIT; : perintah betul-betul dilakukan secara fisik ke penyimpan Pengubahan dengan rekaman START TRANSACTION dan ROLLBACK mysql> START TRANSACTION; mysql> UPDATE mhs SET nama=‘Agus Nefo‘,alamat=‘Pati‘ WHERE no_mhs=‘000 – lihat perubhan Agus namanya di ubah Agus Nefo, alamat Pati

mysql> select * from mhs; + ——– + —————— + ——— -+ | no_mhs | nama | alamat | + ——– + —————— + ——— -+ | 0001 | Agus Nefo | Pati | | 0002 | Budi | jogja | | 0003 | Bejo | Bantul | | 0004 | Ani | jogja | | 0005 | Untung Raharja | Bandung | | 0006 | Diah Ayu Subekti | Semarang | + ——– + —————— + ——— -+ 6 rows in set (0.00 sec) – batalkan perubahan

mysql> ROLLBACK; – lihat kembali hasilnya, setelah dibatalkan kembali nama dan alamat semula.

mysql> select * from mhs; + ——– + —————— + ——— -+

| no_mhs | nama | alamat | + ——– + —————— + ——— -+ | 0001 | Agus | Solo | | 0002 | Budi | jogja | | 0003 | Bejo | Bantul | | 0004 | Ani | jogja | | 0005 | Untung Raharja | Bandung | | 0006 | Diah Ayu Subekti | Semarang | + ——– + —————— + ——— -+ 6 rows in set (0.00 sec) Contoh berikutnya : Pengubahan dengan START TRANSACTION dan COMMIT mysql> START TRANSACTION; mysql> UPDATE mhs SET nama=‘Agus Nefo‘,alamat=‘Pati‘ WHERE no_mhs=‘0001′; mysql> COMMIT; mysql> select * from mhs; + ——– + —————— + ——— -+ | no_mhs | nama | alamat | + ——– + —————— + ——— -+ | 0001 | Agus Nefo | Pati | | 0002 | Budi | jogja | | 0003 | Bejo | Bantul | | 0004 | Ani | jogja | | 0005 | Untung Raharja | Bandung | | 0006 | Diah Ayu Subekti | Semarang | + ——– + —————— + ——— -+ 6 rows in set (0.00 sec) mysql> Perintah COMMIT akan benar-benar mengubah rekaman mhs. Transaksi Penghapusan START TRANSACTION dan ROLLBACK mysql> START TRANSACTION; mysql> DELETE FROM mhs WHERE no_mhs=‘0002′; mysql> DELETE FROM mhs WHERE no_mhs=‘0003′; mysql> select * from mhs; + ——– + —————— + ——— -+ | no_mhs | nama | alamat | + ——– + —————— + ——— -+ | 0001 | Agus Nefo | Pati |

| 0004 | Ani | jogja | | 0005 | Untung Raharja | Bandung | | 0006 | Diah Ayu Subekti | Semarang | + ——– + —————— + ——— -+ 4 rows in set (0.00 sec) mysql> ROLLBACK; mysql> select * from mhs; + ——– + —————— + ——— -+ | no_mhs | nama | alamat | + ——– + —————— + ——— -+ | 0001 | Agus Nefo | Pati | | 0002 | Budi | jogja | | 0003 | Bejo | Bantul | | 0004 | Ani | jogja | | 0005 | Untung Raharja | Bandung | | 0006 | Diah Ayu Subekti | Semarang | + ——– + —————— + ——— -+ 6 rows in set (0.00 sec) Perintah ROLLBACK membatalkan penghapusan Melakukan penghapusan START TRANSACTION dan COMMIT mysql> START TRANSACTION; mysql> DELETE FROM mhs WHERE no_mhs=‘0002′; mysql> DELETE FROM mhs WHERE no_mhs=‘0003′; mysql> COMMIT; Query OK, 0 rows affected (0.06 sec) // lihat penghapusan beer-benar dilakukan mysql> select * from mhs; + ——– + —————— + ——— -+ | no_mhs | nama | alamat | + ——– + —————— + ——— -+ | 0001 | Agus Nefo | Pati | | 0004 | Ani | jogja | | 0005 | Untung Raharja | Bandung | | 0006 | Diah Ayu Subekti | Semarang | + ——– + —————— + ——— -+ 4 rows in set (0.00 sec) mysql>

Transaksi dari satu tabel mysql> START TRANSACTION; mysql> INSERT INTO mhs VALUES(‘0002′,‘Badiyanto‘,'Yogyakarta‘); mysql> INSERT INTO jurusan(‘0002′,‘TI‘); mysql> INSERT INTO jurusan VALUES(‘0002′,‘TI‘);

mysql> select * from mhs,jurusan WHERE mhs.no_mhs=jurusan.no_mhs; + ——– + ———– + ———— + ——– + —— + | no_mhs | nama | alamat | no_mhs | kode | + ——– + ———– + ———— + ——– + —— + | 0001 | Agus Nefo | Pati | 0001 | TI | | 0004 | Ani | jogja | 0004 | KA | | 0002 | Badiyanto | Yogyakarta | 0002 | TI | + ——– + ———– + ———— + ——– + —— + 3 rows in set (0.00 sec) mysql> ROLLBACK; mysql> select * from mhs,jurusan WHERE mhs.no_mhs=jurusan.no_mhs; + ——– + ———– + ——– + ——– + —— + | no_mhs | nama | alamat | no_mhs | kode | + ——– + ———– + ——– + ——– + —— + | 0001 | Agus Nefo | Pati | 0001 | TI | | 0004 | Ani | jogja | 0004 | KA | + ——– + ———– + ——– + ——– + —— + 2 rows in set (0.00 sec) Share on Facebook 





StumbleUpon Digg Reddit







COMMENTS3 Comments CATEGORIESTutorial MySQL Trigger 14DEC Trigger Perubahan isi tabel terjadi pada saat pengguna melakukan transaksi. Transaksi ini biasanya juga berpengaruh terhadap tabel-tabel lain. Sebagai contoh : pada saat transaksi penjualan

sistem akan menambah rekaman tabel JUAL sebelum perintah INSERT misalnya mengecek jumlah stok di tabel BARANG, dan sesudahnya mengurangi stok di tabel BARANG. Oleh karena itu anda bisa menempatkan trigger yang merupakan prosedur pemicu untuk melakukan proses sesuai dengan keinginan. Trigger adalah suatu objek dalam basisdata yang terdapat pada bagian tabel. Trigger untuk menempatkan suatu prosedure berkaitan dengan perubahan pada isi tabel. Cara kerja trigger adalah merespon berkaitan dengan perintah DML (INSERT , UPDATE, DELETE) pada tabel. Adapun jenis perubahan yang dijalankan dalam trigger adalah : 1. BEFORE INSERT on TABEL1 : menjalankan proses di dalamnya pada saat sebelum melakukan operasi INSERT di TABEL1 2. AFTER INSERT on TABEL menjalankan proses di dalamnya pada saat sesudah melakukan operasi INSERT di TABEL1 3. BEFORE UPDATE on TABEL1 menjalankan proses di dalamnya pada saat sebelum melakukan operasi UPDATE di TABEL1 4. AFTER UPDATE on TABEL1 menjalankan proses di dalamnya pada saat sesudah melakukan operasi UPDATE di TABEL1 5. BEFORE DELETE on TABEL1 : menjalankan proses di dalamnya pada saat sebelum melakukan operasi DELETE di TABEL1 6. AFTER DELETE on TABEL1 : menjalankan proses di dalamnya pada saat sesudah melakukan operasi DELETE di TABEL1 Contoh : sederhana Transaksi penjualan : Perhatikan gambar dibawah

Gambar di atas pada saat melakukan transaksi penjualan, terjadi penambahan rekaman pada tabel jual, berisi no nota, tanggal transaksi, kode barang, jumlah barang. Pada saat penambahan rekaman di tabel jual berpengaruh langsung dengan isi tabel barang, yaitu mengurangi stok barang yang dijual. Proses ini anda bisa menggunakan perintah-printah dalam trigger /pemicu. Cara menciptakan triger adalah. Klik pada tabel jual, pilih dan klik kanan mouse ke Triggers, seperti gambar berikut :

Klik Create Trigger

Klik Create

Hingga seperti kode barikut :

Klik Execute All Queries Keterangan : AFTER INSERT on : pemicu akan menjalankan perintah seudah operasi INSERT pada tabel JUAL. UPDATE barang SET stok=stok-NEW.jumlah WHERE kd_brg=NEW.kd_brg; : isi stok barang dalam tabel BARANG akan dikurangi, dengan field NEW.jumlah (dari masukkan field jumlah pada tabel JUAL).

Lakukan Pengujian Lihat stok barang : mysql> SELECT * from barang; + ——– + ————— + —— + ——– + —— -+

| kd_brg | nm_brg | stok | satuan | harga | + ——– + ————— + —— + ——– + —— -+ | K0001 | Buku | 90 | Pcs | 5000 | | K0002 | Pesil 2B | 200 | Pcs | 3000 | | K0003 | Pengahpus | 100 | Pcs | 1000 | | K0004 | Kertas HVS | 200 | Rem | 30000 | | K0005 | Gunting kecil | 10 | Pcs | 6000 | + ——– + ————— + —— + ——– + —— -+ 5 rows in set (0.00 sec)Lihat tabel jual :

mysql> SELECT * from jual; + ——— + ———— + ——– + ——– + | no_nota | tgl | kd_brg | jumlah | + ——— + ———— + ——– + ——– + | 0001 | 2009-10-13 | K0001 | 2 | | 0002 | 2009-11-25 | K0001 | 8 | + ——— + ———— + ——– + ——– + 2 rows in set (0.01 sec)Transakasi penjualan : mysql> INSERT INTO jual VALUES(‘0003′,‘2009 -11-25′,‘K0002′,20); Query OK, 1 row affected (0.00 sec) Perhatikan barang dengan kd_brg=‘K0002‘ telah dijual 20, maka stok 200-20=180

Lihat di stok barang. mysql> SELECT * from barang W HERE kd_brg=‘K0002′; + ——– + ——— -+ —— + ——– + —— -+ | kd_brg | nm_brg | stok | satuan | harga | + ——– + ——— -+ —— + ——– + —— -+ | K0002 | Pesil 2B | 180 | Pcs | 3000 | + ——– + ——— -+ —— + ——– + —— -+ 1 row in set (0.00 sec) Penambahan di tebel JUAL mysql> SELECT * from jual; + ——— + ———— + ——– + ——– + | no_nota | tgl | kd_brg | jumlah | + ——— + ———— + ——– + ——– + | 0001 | 2009-10-13 | K0001 | 2 | | 0002 | 2009-11-25 | K0001 | 8 | | 0003 | 2009-11-25 | K0002 | 20 | + ——— + ———— + ——– + ——– + 3 rows in set (0.00 sec) Selamat mencoba RAJIN PANGKAL PANDAI Share on Facebook 








o o

COMMENTS4 Comments CATEGORIESTutorial MySQL Perhitungan Tanggal dan Waktu dengan MySQL 9NOV

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, biasaya digunakan untuk 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 :

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 dalam 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‘ tahunbulan-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) 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 pegewai (NIP char(4), nama char(30), job char(25), primary key (NIP); ambahkan 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 àbsensi` ( `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‘, `shitf` int(11) DEFAULT ‘1′ COMMENT ‗kode shift kerja‘);

Tambahkan isi rekaman seperti tabel berkut : 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′);

Hasilnya :

Presensi Pulang UPDATE absensi SET JAM_PLG=NOW() WHERE NIP=‘1001′ AND SUBSTR(JAM_DTG,1,10)=SUBSTR(NOW(),1,10); Hasilnya :

Penjelasan : Hanya mengubah jam pulang saja dengan kunci pencarian seperti perintah berikut : WHERE NIP=‘1001′ AND SUBSTR(JAM_DTG,1,10)=SUBST R(NOW(),1,10); Kunci pencarian adalah NIP dan tanggal presensi data 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 ter ekam 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:detik Menampilkan 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 semu a pegawai periode tanggal ‘200910-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: RAJIN PANKAL PANDAI Share on Facebook



StumbleUpon Digg



Reddit









COMMENTS6 Comments CATEGORIESTutorial MySQL View 2NOV View adalah tabel yang barisnya secara tidak eksplisit tersimpan dalam database tetapi jika diperlukan akan diperhitungkan dari definisi view. contoh perintah menciptakan View CREATE VIEW B_Strudent (name, sid, course) AS SELECT S.name, S.sid, E.cid FROM Student AS S, Enrolled E WHERE S.id = E.studid AND E.srade=‘B‘ View student memiliki tiga field yang disebut name, sid, dancourse dengan domain yang sama, jika terjadi pada perubahan di tabel Student dan Enrolled otomatis akan mengikuti. View, Independensi Data, Keamanan Di level abstraksi tabel yang dihasilkan oleh View, skema fisik relasional menjelaskan relasi skema konseptual yang disimpan, dalam hal ini digunakannya file dan indeks organisasi. Skema konseptual adalah kumpulan skema relasi yang disimpan dalam database. Beberapa relasi pada skema konseptual juga dapat digunakan dalam aplikasi, sehingga menjadi bagian dari bagian eksternal database, maka relasi tambahan pada skema eksternal dapat didefinisikan dengan menggunakan mekanisme view. Jadi mekanisme view memberikan dukungan untuk independensi data logika pada model relasioanl. View juga bermanfaat dalam bidang keamanan : user dapat menciptakan yang memberikan sekelompok pengguna ke informasi yang diizinkan untuk mereka lihat Manciptakan View Dengan SQLyog

Klik View

Klik Create, Tuliskan nama View, klik tombol Create

Isikan/ubah skrip SQL seperti pada contoh dibawah :

Klik Excute All Query mysql> select * from nama; + ———– + ————– + ———— + | nama

| kuliah

| alamat

|

+ ———– + ————– + ———— + | Badiyanto | STMIK AKAKOM | Jogyakarta | + ———– + ————– + ———— +

1 row in set (0.00 sec) Meciptakan View dengan Relasi tabel

mysql> SELECT * FROM data_prib_mhs; + ———– + ——– + ——— + ———– + ——— -+ | no_mhs | nama | alamat | Seks | agama | + ———– + ——– + ——— + ———– + ——— -+ | 055410001 | Ana | Jkt | Perembuan | Kristen | | 055410002 | Budi | Jogja | Laki-laki | Islam | | 055410004 | Agus | Bandung | Laki-laki | Kristen | | 055410003 | Bajo | Jkt | Laki-laki | Katholik | | 055610005 | Untung | Jkt | Laki-laki | Islam | | 065410010 | Tukul | Jkt | Laki-laki | Islam | | 065410002 | Alex | Band | Laki-laki | Islam | | 075410002 | Yuda | Medan | Laki-laki | Hindu + ———– + ——– + ——— + ———– + ——— -+ 8 rows in set (0.00 sec) mysql>

|

Sebuah laporan transkrip nilai perintah-perintahnya bisa disimpan ke dalam View yang merupakan rangkaian perintah SQL yang cukup komplek. Query tersebut bisa dikombinasikan dengan fungsi yang ada.

Contoh: CREATE VIEW trans_nilai AS SELECT n.no_mhs, n.kd_mk,m.nm_kul, n.nilai,m.sks,Skor_nilai(m.sks,n.nilai) as Skor FROM nilai as n, mkuliah as m WHERE n.kd_mk=m.kd_mk ORDER BY n.no_mhs, SUBSTR(m.kd_mk,2,1);

Lakukan Eksekusi Lihat hasil pada gambar berkut :

Cara menggunakan VIEW

View yang tersimpan secara permanen seperti tabel, hanya bedanya adalah kalau tabel bisa dimanipulasi dan untuk view hanya bisa dibaca dengan perintah SELECT. Keuntungan view memudahkan dalam pembuatan laporan

Cara mengil VIEW

SELECT * FROM trans_nilai;

Lihat hasilnya

Menampilkan transkrip nilai untuk no_mhs=‘05541002‘

SELECT * FROM trans_nilai WHERE no_mhs=‘055410002′;

Share on Facebook 



StumbleUpon Digg



Reddit







COMMENTS Leave a Comment CATEGORIESTutorial MySQL Stored Function 20OCT Stored Function Dalam database server ada 2 ketegori fungsi: (1) fungsi aggregate adalah fungsi fungsi bawaan yang terdapat pada sistem database tersebut, (2) fungsi yang diciptakan oleh user sendiri. Pada bagian ini akan dibahas tentang cara menciptakan, dang menggunakan fungsi yang dibuat oleh user sendiri. Cara kerja fungsi fungsi ―>hampir sama dengan Stored Procedure, namun fungsi mempunyai ada yang parameter masukan ada yang tidak. Selain parameter fungsi mengembalikan nilai balik lewat perintah RETURN. DELIMITER $$ CREATE FUNCTION nama_fungsi ( parameter [,...]) RETURNS tipe_data_nilai_balik /* COMMENT keterangan_string */ BEGIN Deklasi variabel; isi_perintah_fungsi; RETRUN data_nilai_balik; END$$ DELIMITER ;

o o o

Cara menciptakan function, Klik function, Klik kanan mouse Pilih dan Klik Create Function

Selanjuntnya masukkan nama fungisnya seperti pada gambar berkut :

Klik tombol Create, lihat mengubah skrip berkut :

mysql> SELECT Kata(); +-----------------------------------+ | Kata() | +-----------------------------------+ | BADIYANTO STMIK AKAKOM YOGYAKARTA | +-----------------------------------+ 1 row in set (0.00 sec) Fungsi dengan parameter Parameter adalah nilia mesukkan, yang kaan diproses dalam fungsi, biasanya cara memanggilnya seperi perintah berikut. SELECT Nama_fungsi(param_1, param_2, param_3, param_N); Contoh :

Menguji Hasilnya : mysql> SELECT Jenis_bil(3); +-----------------+ | Jenis_bil(3) | +-----------------+ | Bilangan Ganjil | +-----------------+ 1 row in set (0.00 sec) mysql> Fungsi dengan Operator

Hasil pengujian mysql> SELECT kali(10,2); +------------+

| kali(10,2) | +------------+ | 20 | + ———— + 1 row in set (0.00 sec) mysql> Menghitung Bobot Nilai Aturan skor perhitungan nilai sebagai berikut : o

Jika nilai=‖A‖ maka bobotnya 4

o

Jika nilai=‖B‖ maka bobotnya 3

o

Jika nilai=‖C‖ maka bobotnya 2

o

Jika nilai=‖D‖ maka bobotnya 1

o

Jika nilai=‖E‖ maka bobotnya 0 Misal nilai ―A‖ sks=3 maka perhitungan skornya =sks * bobot

Skornya = 3 * 4 = 12, untuk menciptakan fungsi hitung skor sebagai masukan adalah SKS dan NILAI, sebagai hasil keluaran fungsi atau nilai balik adalah SKOR Latihan :

Cara memanggil fungsi :

mysql> SELECT Skor_nilai(3,'B'); +-------------------+ | Skor_nilai(3,'B') | +-------------------+ | 9| +-------------------+ 1 row in set (0.00 sec) mysql> Fungsi Dikombinasikan dengan SELECT Secara umum penulisan program dalam fungsi menggunakan bahasa PL/SQL, yaitu standar behasa SQL. Oleh karena itu dalam fungsi dapat melibatkan perintah SELECT, INTO, FROM, WHERE dan lainya. Sebagai contoh untuk mencari nama dengan peritah SELECT nama FROM mhs WHERE no_mhs=‘055410002′ Kemudian perintah ini akan dikombinasikan kedalam fungsi, misal nama fungsinya diberi nama get_nama(no_mhs);

o o o o o

Penjelasan : DECLARE Lnama CHAR(25) : adalah diklarasi varibel nama SELECT nama INTO Lnama FROM mhs Nama: nama field/kolom nama pada tabel mhs INTO masukan nilianya ke Variabel nama Lnama WHERE no_mhs=in_no_mhs : jika no_mhs (field/kolom no_mhs pada tabel MHS) sama dengan in_no_mhs (parameter masukan dari fungsi get_nama(in_no_mhs) Hasil pengujian mysql> SELECT get_nama('055410002'); +-----------------------+ | get_nama('055410002') | +-----------------------+

| Budi | +-----------------------+ 1 row in set (0.00 sec) mysql> Meggunakan Fungsi dengan SQL Setelah fungsi diciptakan, maka secara permanen tersimapan pada sistem database, cara menggunakan dipanggil lewat perintah : SELECT nama(parameter/field/kolom), kolom FROM teblel Contoh : Melihat isi tabel MHS mysql> SELECT * FROM MHS; +-----------+--------+---------+------+------------+-------+ | no_mhs | nama | alamat | seks | tgl_lhr | agama | +-----------+--------+---------+------+------------+-------+ | 055410001 | Ana | Jkt | P | 1981-10-10 | 2 | | 055410002 | Budi | Jogja | L | 1980-10-10 | 1 | | 055410004 | Agus | Bandung | L | 1979-10-10 | 2 | | 055410003 | Bajo | Jkt | L | 1981-07-10 | 3 | | 055610005 | Untung | Jkt | L | 1981-08-10 | 1 | | 065410010 | Tukul | Jkt | L | 1981-11-10 | 1 | | 065410002 | Alex | Band | L | 1981-02-10 | 1 | | 075410002 | Yuda | Medan | L | 1984-10-10 | 4 | +-----------+--------+---------+------+------------+-------+ 8 rows in set (0.00 sec) Melihat isi tabel Tabel MKULIAH mysql> SELECT * FROM mkuliah; +-------+--------------------------+------+ | kd_mk | nm_kul | sks | +-------+--------------------------+------+ | T1001 | Bahasa Inggris I | 2| | P1201 | Prak. Pemprogaman Dasar | | TI201 | Kalkulus I | 3|

1|

| T2202 | Kalkulus II | 3| | TI401 | Algoritma dan Pemrogaman | 3 | | T3201 | Basis Data | 3| | P2202 | Prak. Pemprogaman Web 2 | 1 | +-------+--------------------------+------+ 7 rows in set (0.05 sec) Melihat isi tabel NILAI

mysql> SELECT * FROM nilai; +----------+-------+-------+ | no_mhs | kd_mk | nilai | +----------+-------+-------+ | 05541001 | T1001 | A | | 05541001 | P1201 | C | | 05541001 | T1201 | A | | 05541001 | T2202 | B | | 05541001 | T1401 | B | | 05541001 | P2202 | A | | 05541001 | T3201 | B | | 05541002 | T1001 | D | | 05541002 | P1201 | E | | 05541002 | T1201 | C

|

| 05541002 | T2202 | C | | 05541002 | T1401 | D | | 05541002 | P2202 | B | | 05541002 | T3201 | B | +----------+-------+-------+ 14 rows in set (0.03 sec) mysql> Membuat Trankrip Nilai dengan menggabungkan sejumlah tabel dan fungsi perhatikan skrip SQL dibawan ini : mysql> SELECT n.no_mhs,n.kd_mk,m.nm_kul, -> n.nilai,m.sks,Skor_nilai(m.sks,n.nilai) as Skor -> FROM nilai as n, mkuliah as m -> WHERE n.kd_mk=m.kd_mk AND n.no_mhs='055410001' -> ORDER BY SUBSTR(m.kd_mk,2,1); +-----------+-------+-------------------------+-------+------+------+ | no_mhs | kd_mk | nm_kul | nilai | sks | Skor | +-----------+-------+-------------------------+-------+------+------+ | 055410001 | T1001 | Bahasa Inggris I |A | 2| 8| | 055410001 | P1201 | Prak. Pemprogaman Dasar | C | 1 | 2 | | 055410001 | P2202 | Prak. Pemprogaman Web 2 | A | 1 | 4 | | 055410001 | T2202 | Kalkulus II |B | 3| 9| | 055410001 | T3201 | Basis Data |B | 3| 9| +----------+-------+-------------------------+-------+------+------+ 5 rows in set (0.00 sec) mysql> penjelasan : o

Fungsi Skor_nilai(m.sks,n.nilai), menggunakan masukan m.sks diambil dari klom sks pada tabelmkuliah,dan n.nilai diambil dari tabel nilai

mysql> SELECT n.no_mhs,get_nama(n.no_mhs), -> sum(skor_nilai(m.sks,n.nilai)) as skor, -> sum(m.sks) jum_sks, -> sum(skor_nilai(m.sks,n.nilai))/sum(m.sks) as ip -> FROM nilai as n, mkuliah as m -> WHERE n.kd_mk=m.kd_mk -> GROUP BY n.no_mhs; +-----------+--------------------+------+---------+--------+ | no_mhs | get_nama(n.no_mhs) | skor | jum_sks | ip +-----------+--------------------+------+---------+--------+ | 055410001 | Ana | 32 | 10 | 3.2000 | | 055410002 | Budi | 20 | 10 | 2.0000 | +-----------+--------------------+------+---------+--------+

|

2 rows in set (0.34 sec) mysql> Share on Facebook StumbleUpon Digg Reddit













COMMENTS Leave a Comment CATEGORIESTutorial MySQL Penanganan Kaslahan Menggunakan Stored Procedure MySQL 13OCT Program yang baik adalah jika terjadi kesalahan program tidak macet, kemacetan terjadi karena ada kesalahan dalam masukkan data, akibatnya kesalah tersebut membuat aplikasi menjadi macet. Database MySQL telah mendukung perintah untuk menghindari kesalah dengan cara meletakkan perint error handel ke dalam Stored Procedure. Berikut ini contoh stored procedure untuk menangani kesalahan: CREATE TABLE `mhs` ( `no_mhs` char(9) NOT NULL, `nama` char(30) DEFAULT NULL, àlamat` char(30) DEFAULT NULL, `seks` char(1) DEFAULT NULL, `tgl_lhr` date DEFAULT NULL, àgama` char(1) DEFAULT NULL , PRIMARY KEY (`no_mhs`) ) ; Buat Strored procedure untuk menambahkan rekamam tabl MHS

DELIMITER $$ DROP PROCEDURE IF EXISTS àkademik`.`Tbh_cek`$$ CREATE PROCEDURE `Tbh_cek`( p_no_mhs varchar(9), p_nama varchar(30), p_alamat varchar(30), p_seks varchar(1), p_tgl_lhr char(10), p_agama varchar(1),OUT pesan Varchar(255)) BEGIN DECLARE DUPLICATE_KEY CONDITION FOR 1062; DECLARE FOREIGN_KEY_VIOLATED CONDITION FOR 1452; DECLARE EXIT HANDLER FOR DUPLICATE_KEY BEGIN SET pesan = 'Kunci nomor mhs kembar!'; END; INSERT INTO mhs(no_mhs,nama,alamat,seks,tgl_lhr,agama) VALUES(p_no_mhs,p_nama,p_alamat,p_seks,p_tgl_lhr,p_agama); END$$ DELIMITER ; Cara memanggil strore procdure CALL tbh_cek('075410002','Yuda','Medan','L','1984-10-10','4',@p_result ); Tampilkan isi tabel : SELECT * FROM mhs

Rekaman bertambah 1, Pesan kesalahan jika kita tidak menggunakan penanganan kesalahan INSERT INTO MHS VALUES ('075410002','Yuda','Medan','L','1984-10-10','4'); Pesan kesalahan :

Ulangi jalankan SQL di atas CALL tbh_cek('075410002','Yuda','Medan','L','1984-10-10','4',@p_result ); SELECT @p_result;

DECLARE EXIT HANDLER FOR DUPLICATE_KEY BEGIN SET pesan = 'Kunci nomor mhs kembar!'; END; Proses di atas field no_mhs adalah primary key sehingga tidak boleh ada yang sama, jika ada penambahan data no_mhs sama akan terjadi kesalahan, untuk menghindari kesalahan tersebut, maka dibuat prosedure untuk menampilkan kesalahan. Pengecekan Kondisi Dalam stored procedure pengecekan kondosi adalah suatu kejadian yang secara spesifik memberikan pesan jika terjadi kesalahan. Bentuk perintah DECLARE HANDLER adalah DECLARE handler_type HANDLER FOR condition_value [, condition_value] … statement Deklarasi handler terdapat tiga ; • Tipe handler (CONTINUE, EXIT) • Kondisi handler (SQLSTATE, MySQL kode kesalahan, nama kondisi) • Aksi handler

Tipe Handler CONTINUE : melewatkan proses jika terjadi suatu kesalahkan EXIT : katika suatu blok proses terjadi kesalahan, mengalikan keluar dari proses tersebut Kemudian kode kesalah berupa kode angka misalnya 1062 adalah jika terjadi duplicate key Kondisi Handler Mendefinisikan suatu keadaan sesuai kode kesalahan yang terdapat dalam daftar kode MySQL atau standar kesalahan pada ANSI-standard SQLSTATE. Nama kondisi terdapat dalam sistem MySQL adalah : SQLWARNING,NOT FOUND, SQLEXCEPTION Contoh : DECLARE CONTINUE HANDLER FOR 1062 SET duplicate_key=1; Kode kesalahan pada SQLSTATE secara idependen menggunakan standar ANSI, mendukung misalnya pada sistem : Oracle, SQL Server, DB2, dan MySQL,.

Contoh berikut ini adalah kode kesalahan untuk SQLSTATE DECLARE CONTINUE HANDLER FOR SQLSTATE ‘23000′ SET duplicate_key=1; Berikut Tabel Kode Kesalahan : Kemudian kode kesalah berupa kode angka misalnya 1062 adalah jika terjadi duplicate key Tabel. Kode kesalahan MySQL dan SQLSTATE

Kode MySQL

Kode SQLSTATE

Pesan Kesalahan

1011

HY000

Error on delete of ‗%s‘ (errno: %d)

1021

HY000

Disk full (%s); waiting for someone to free some space . . .

1022

23000

Can‘t write; duplicate key in table ‗%s‘

1027

HY000

‗%s‘ is locked against change

1036

HY000

Table ‗%s‘ is read only

1048

23000

Column ‗%s‘ cannot be null

1062

23000

Duplicate entry ‗%s‘ for key %d

1099

HY000

Table ‗%s‘ was locked with a READ lock and can‘t be updated

1100

HY000

Table ‗%s‘ was not locked with LOCK TABLES

1104

42000

The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET SQL_MAX_JOIN_SIZE=# if the SELECT is okay

1106

42000

Incorrect parameters to procedure ‗%s‘

1114

HY000

The table ‗%s‘ is full

1150

HY000

Delayed insert thread couldn‘t get requested lock for table %s

1165

HY000

INSERT DELAYED can‘t be used with table ‗%s‘ because it is locked with LOCK TABLES

1242

21000

Subquery returns more than 1 row

Tabel. Kode kesalahan MySQL dan SQLSTATE

Kode MySQL

Kode SQLSTATE

Pesan Kesalahan

1263

22004

Column set to default value; NULL supplied to NOT NULL column ‗%s‘ at row %ld

1264

22003

Out of range value adjusted for column ‗%s‘ at row %ld

1265

1000

Data truncated for column ‗%s‘ at row %ld

1312

0A000

SELECT in a stored program must have INTO

1317

70100

Query execution was interrupted

1319

42000

Undefined CONDITION: %s

1325

24000

Cursor is already open

1326

24000

Cursor is not open

1328

HY000

Incorrect number of FETCHvariables

1329

2000

No data to FETCH

1336

42000

USE is not allowed in a stored program

1337

42000

Variable or condition declaration after cursor or handler declaration

1338

42000

Cursor declaration after handler declaration

1339

20000

Case not found for CASEstatement

1348

HY000

Column ‗%s‘ is not updatable

1357

HY000

Can‘t drop a %s from within another stored routine

1358

HY000

GOTO is not allowed in a stored program handler

1362

HY000

Updating of %s row is not allowed in %s trigger

1363

HY000

There is no %s row in %s trigger

Contoh : handel kasalahan DECLARE sqlcode INT DEFAULT 0; DECLARE status_message VARCHAR(50);

-- Error handler untuk duplikasi data pada primary key DECLARE CONTINUE HANDLER FOR duplicate_key BEGIN SET sqlcode=1052; SET status_message=‘Duplicate key error‘; END; -- Error handler untuk kesalahan pada FOREGN KEY tabel lain DECLARE CONTINUE HANDLER FOR foreign_key_violated BEGIN SET sqlcode=1216; SET status_message=‘Foreign key violated‘;

END; -- Error handler untuk kesalahan-kesalahan lain selain kedua handler diatas DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN SET Message = 'Error pada query!'; END; DECLARE CONTINUE HANDLER FOR NOT FOUND BEGIN SET sqlcode=1329; SET status_message=‘No record found‘; END; Contoh : Buatlah Stored Procedure untuk menambahkan rekaman tabel MHS, demgan nama Simpan_mhs(…..,………,…. Dst), seperti pada skript berikut :

DELIMITER $$ DROP PROCEDURE IF EXISTS àkademik`.`Simpan_mhs`$$ CREATE PROCEDURE Simpan_mhs(p_no_mhs varchar(9) ,p_nama varchar(30) ,p_alamat varchar(30) ,p_seks varchar(1) ,p_tgl_lhr char(10) ,p_agama varchar(1),OUT pesan Varchar(255)) BEGIN DECLARE DUPLICATE_KEY CONDITION FOR 1062; DECLARE FOREIGN_KEY_VIOLATED CONDITION FOR 1452; DECLARE EXIT HANDLER FOR DUPLICATE_KEY BEGIN

SET pesan = 'Kunci utama terjadi duplikasi!'; END; DECLARE EXIT HANDLER FOR FOREIGN_KEY_VIOLATED BEGIN SET pesan = 'Referensi data salah !'; END; DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN SET pesan = 'Kasalahan pada perintah query!'; END; INSERT INTO mhs(no_mhs,nama,alamat,seks,tgl_lhr,agama) VALUES(p_no_mhs,p_nama,p_alamat,p_seks,p_tgl_lhr,p_agama); END;$$ DELIMITER ; Lakukan eksekusi skrip Pengujian : mysql> CALL Simpan_mhs('075410002','Yuda','Medan','L','1984-10-10','4',@pesan ); Query OK, 0 rows affected (0.02 sec) mysql>SELECT @pesan; +-------------------------------+ | @pesan | +-------------------------------+ | Kunci utama terjadi duplikasi! | +-------------------------------+ 1 row in set (0.00 sec) mysq>; Latihan : Suatu transaksi penjualan secara sederhana misalnya melibatkan 2 tabel, antara lain tabel barang yang menyimpanan stok dan jenis barang, dan tabel jual merekam transakasi penjualan. Untuk proses merekaman transaksi penjualan menggunakan 2 stored prosedure, yang pertama perekaman jual, dam kedua pemotongan stok pada tabel barang. Adapun langkah-langkahnya sebagai berikut : Buatlah struktur tabel BARANG berikut : CREATE TABLE barang (kd_brg CHAR(5),

nm_brg CHAR(20), stok int, satuan CHAR(20), harga int, primary key (kd_brg)); Tambahkan isi rekaman sebagai berikut : INSERT INSERT INSERT INSERT INSERT

INTO barang VALUES('K0001','Buku ',100,'Pcs',5000); INTO barang VALUES('K0002','Pesil 2B',200,'Pcs',3000); INTO barang VALUES('K0003','Pengahpus',100,'Pcs',1000); INTO barang VALUES('K0004','Kertas HVS',200,'Rem',30000); INTO barang VALUES('K0005','Gunting kecil',10,'Pcs',6000);

Lihat isi tabel :

Buatlah struktur tabel JUAL sepeti pada perintah berikut: CREATE TABLE jual (no_nota char(4), tgl date, kd_brg CHAR(5), jumlah int); DELIMITER $$ Kemudian buatlah Stored Procedure Simpan_jual(………), isikan parameternya seperti pada skript berikut : DROP PROCEDURE IF EXISTS àkademik`.`Simpan_jual`$$ CREATE PROCEDURE àkademik`.`Simpan_jual`(in_no_nota char(4), in_tgl date, in_kd_brg CHAR(5), in_jumlah int) BEGIN INSERT INTO jual VALUES(in_no_nota,in_tgl,in_kd_brg,in_jumlah); CALL potong_stok(in_kd_brg,in_jumlah); END$$ DELIMITER ; Lakukan eksekusi

Kemudian buatlah Stored Procedure Potong_stok(………), isikan parameternya seperti pada

skript berikut : ELIMITER $$ DROP PROCEDURE IF EXISTS àkademik`.`potong_stok`$$ CREATE PROCEDURE àkademik`.`potong_stok`(in_kd_brg char(5),in_jumlah int) BEGIN UPDATE barang SET stok=stok-in_jumlah WHERE kd_brg=in_kd_brg; END$$ DELIMITER ; Lakukan eksekusi Jalankan Stored Procedure Simpan_jual CALL Simpan_jual('0001',CURRENT_DATE,'K0001',2) Hasil : SELECT * FROM jual ;

Lihat Stok SELECT * FROM barang;

Penjelasan : INSERT INTO jual VALUES(in_no_nota,in_tgl,in_kd_brg,in_jumlah); Skrip di atas proses perekaman penjualan pada tabel jual Selanjutnya pemotongan stok dilakukan oleh perintah di bawah CALL potong_stok(in_kd_brg,in_jumlah); Parameter in_kd_brg : kunci pencarian In_jumlah : nilai pengurangan stok Soal :

Tambahkan skrip di atas untuk menagani kesalahan/validasi 1. pengecekan barang, 2. stok habis atau stok tidak boleh minus. Share on Facebook 












COMMENTS2 Comments CATEGORIESTutorial MySQL Stored Precrdure dan Function 5OCT 1. Pendahuluan Stored Precrdure dan Function 2. Stored Procedure pada MySQL 3. Dasar Pemrogramanan 4. Struktur Block, Program Interaktif 5. Pemrograman SQL dan Pemrograman Stored Procedure 6. Error Handeling Pendahuluan 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 perintahperintah 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 ‗AKAKOM‘;

END$$ DELIMITER ;

sudah pernah ada. gan nama Kata ( isi parameter kalau ada ),

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 | + ——– + | AKAKOM | + ——– + 1 row in set (0.00 sec) Query OK, 0 rows affected (0.00 sec) mysql>

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 :

dimasukan ke dalam variabel lokal.

sejumlah baris. ngasilkan 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 PR OCEDURE 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 :

Melibatkan Tabel

DELIMITER$$ DROP PROCEDURE IF EXISTS cari; $$ CREATE PROCEDURE cari(in_no_mhs char(9)) BEGIN SELECT no_mhs,nama,alamat,agama FROM mhs WHERE no_mhs=in_no_mhs;

END; $$

Cara menjalankan Stored Procedure

Contoh kasus

Bualah tabel :

Tabel NILAI

CREATE TABLE nilai (no_mhs char(9), kd_mk char(5), nilai char(1), primary key (no_mhs,kd_mk));

INSERT INTO NILAI VALUES('05541001','T1001','A'); INSERT INTO NILAI VALUES('05541001','P1201','C'); INSERT INTO NILAI VALUES('05541001','T1201','A'); INSERT INTO NILAI VALUES('05541001','T2202','B'); INSERT INTO NILAI VALUES('05541001','T1401','B'); INSERT INTO NILAI VALUES('05541001','P2202','A'); INSERT INTO NILAI VALUES('05541001','T3201','B');

INSERT INTO NILAI VALUES('05541002','T1001','D'); INSERT INTO NILAI VALUES('05541002','P1201','E'); INSERT INTO NILAI VALUES('05541002','T1201','C'); INSERT INTO NILAI VALUES('05541002','T2202','C'); INSERT INTO NILAI VALUES('05541002','T1401','D'); INSERT INTO NILAI VALUES('05541002','P2202','B'); INSERT INTO NILAI VALUES('05541002','T3201','B');

Tabel MKULIAH

CREATE TABLE mkuliah (kd_mk char(5), nm_kul char(25), sks int, primary key (kd_mk));

INSERT INTO mkuliah VALUES('T10001','Bahasa Inggris I',2); INSERT INTO mkuliah VALUES('T10002','Bahasa Inggris II',2); INSERT INTO mkuliah VALUES('TI2001','Kalkulus I',3); INSERT INTO mkuliah VALUES('T22001','Kalkulus II',3); INSERT INTO mkuliah VALUES('TI4001','Algoritma dan Pemrogaman',3); INSERT INTO mkuliah VALUES('T22002','Jaringan Komputer',3); INSERT INTO mkuliah VALUES('T32001','Basis Data',3); INSERT INTO mkuliah VALUES('P12001','Prak. Pemprogaman Web 1',1);

INSERT INTO mkuliah VALUES('P22001','Prak. Pemprogaman Web 2',1); INSERT INTO mkuliah VALUES('P12001','Prak. Pemprogaman Dasar',1);

Menapilkan nilai no_mhs='05541001' SELECT nilai.no_mhs,nilai.kd_mk,mkuliah.nm_kul,nilai.nilai,mkuliah.sks FROM nilai,mkuliah WHERE nilai.kd_mk=mkuliah.kd_mk AND nilai.no_mhs='05541001'

mysql> use akademik; Database changed mysql> SELECT nilai.no_mhs,nilai.kd_mk,mkuliah.nm_kul,nilai.nilai,mkuliah.sks -> FROM nilai,mkuliah -> WHERE nilai.kd_mk=mkuliah.kd_mk AND nilai.no_mhs='05541001'; +----------+-------+-------------------------+-------+------+ | no_mhs | kd_mk | nm_kul

| nilai | sks |

+----------+-------+-------------------------+-------+------+ | 05541001 | P1201 | Prak. Pemprogaman Dasar | C

|

| 05541001 | P2202 | Prak. Pemprogaman Web 2 | A | 05541001 | T1001 | Bahasa Inggris I

|A

|

2|

| 05541001 | T2202 | Kalkulus II

|B

|

3|

| 05541001 | T3201 | Basis Data

|B

|

3|

1| |

1|

+----------+-------+-----------+----------+------+-------------------------+-------------------+-------+------+ +------+ 5 rows in set (0.00 sec)

mysql>

Manampilkan trasnkip dengan bobot nilai, sks dan jumlah dan bobot

mysql> SELECT nilai.no_mhs,nilai.kd_mk,mkuliah.nm_kul,nilai.nilai,mkuliah.sks ->

CASE WHEN nilai.nilai='A' THEN mkuliah.sks * 4

->

WHEN nilai.nilai='B' THEN mkuliah.sks * 3

->

WHEN nilai.nilai='C' THEN mkuliah.sks * 2

->

WHEN nilai.nilai='D' THEN mkuliah.sks * 1

->

ELSE 0

->

END bobot

-> FROM nilai,mkuliah -> WHERE nilai.kd_mk=mkuliah.kd_mk AND nilai.no_mhs='05541001'; +----------+-------+-----------+----------+------+-------------------------+-------------------+-------+------+-------+ +------+-------+ | no_mhs | kd_mk | nm_kul

| nilai | sks | bobot |

+----------+-------+-----------+----------+------+-------------------------+-------------------+-------+------+-------+ +------+-------+ | 0554100 05541001 1 | P1201 | Prak. Pemprogaman Dasar | C

|

| 0554100 05541001 1 | P2202 | Prak. Pemprogaman Web 2 | A | 0554100 05541001 1 | T1001 | Bahasa Inggris I

|A

|

2|

1| |

1| 8|

2| 4|

| 0554100 05541001 1 | T2202 | Kalkulus II

|B

|

3|

9|

| 0554100 05541001 1 | T3201 | Basis Data

|B

|

3|

9|

+----------+-------+-----------+----------+------+-------------------------+-------------------+-------+------+-------+ +------+-------+ 5 rows in set (0.00 sec)

mysql>

menghitung IP

mysql> SELECT nilai.no_mhs,SUM(mkuliah.sks) jum_sks, ->

SUM(CASE WHEN nilai.nilai='A' THEN mkuliah.sks * 4

->


->


->


->

ELSE 0

->

END) as jum_bobot

-> FROM nilai,mkuliah -> WHERE nilai.kd_mk=mkuliah.kd_mk AND nilai.no_mhs='05541001'; +----------+---------+-----------+ | no_mhs | jum_sks | jum_bobot | +----------+---------+-----------+ | 0554100 05541001 1|

10 |

32 |

+----------+---------+-----------+ 1 row in set (0.00 sec)

mysql>

Ip= jum_bobot/jum_sks

mysql> SELECT nilai.no_mhs,SUM(mkuliah.sks) jum_sks, ->

SUM(CASE WHEN nilai.nilai='A' THEN mkuliah.sks * 4

->


->


->


->

ELSE 0

-> ->

END) as jum_bobot, SUM(CASE WHEN nilai.nilai='A' THEN mkuliah.sks * 4

->


->


->


->

ELSE 0

->

END) /SUM(mkuliah.s /SUM(mkuliah.sks) ks) as ip

-> FROM nilai,mkuliah -> WHERE nilai.kd_mk=mkuliah.kd_mk AND nilai.no_mhs='05541001';

+----------+---------+-----------+--------+ | no_mhs | jum_sks | jum_bobot | ip

|

+----------+---------+-----------+--------+ | 05541001 |

10 |

32 | 3.2000 |

+----------+---------+-----------+--------+ 1 row in set (0.00 sec)

mysql>

Manggunakan Stored Procedure

CREATE DEFINER=`root`@`localhost` PROCEDURE `get_transkrip`(in_no_mhs char(9)) BEGIN SELECT nilai.no_mhs,nilai.kd_mk,mkuliah.nm_kul,nilai.nilai,mkuliah.sks, CASE WHEN nilai.nilai='A' THEN mkuliah.sks * 4 WHEN nilai.nilai='B' THEN mkuliah.sks * 3 WHEN nilai.nilai='C' THEN mkuliah.sks * 2 WHEN nilai.nilai='D' THEN mkuliah.sks * 1 ELSE 0 END bobot FROM nilai,mkuliah WHERE nilai.kd_mk=mkuliah.kd_mk AND nilai.no_mhs=in_no_mhs

/*urut semerter */ ORDER BY SUBSTR(nilai.kd_mk,2,1); END$$

DELIMITER ; CREATE DEFINER=`root`@`localhost` PROCEDURE `get_ip`(in_no_mhs char(9)) BEGIN SELECT nilai.no_mhs,SUM(mkuliah.sks) jum_sks, SUM(CASE WHEN nilai.nilai='A' THEN mkuliah.sks * 4 WHEN nilai.nilai='B' THEN mkuliah.sks * 3 WHEN nilai.nilai='C' THEN mkuliah.sks * 2 WHEN nilai.nilai='D' THEN mkuliah.sks * 1 ELSE 0 END) as jum_bobot, SUM(CASE WHEN nilai.nilai='A' THEN mkuliah.sks * 4 WHEN nilai.nilai='B' THEN mkuliah.sks * 3 WHEN nilai.nilai='C' THEN mkuliah.sks * 2 WHEN nilai.nilai='D' THEN mkuliah.sks * 1 ELSE 0 END) /SUM(mkuliah.sks) as ip FROM nilai,mkuliah WHERE nilai.kd_mk=mkuliah.kd_mk AND nilai.no_mhs=in_no_mhs;

END$$

DELIMITER ; Daftar Pustaka

Steven Feuerstein, Guy Harrison, 2006, MySQL Stored Procedure Programming, O‘Reilly













COMMENTS Leave a Comment CATEGORIESTutorial MySQL Fungsi dan Operator di MySQL 5OCT Operator Nama

Keterangan Logika AND

AND, && BETWEEN … AND …

Mengecek nilai dalam Mengubah nilai string ke biner string

BINARY Operasi bit AND & Membalik nilai bits ~ Operasi bit OR | Operasi bit XOR ^ Operator Case CASE Pembagian hasil nilai Integer DIV Operator pembagian /

Nama

Keterangan Operator loiga untuk karakter NULL

<=> Operator sama dengan = Lebih besar atau sama dengan >= Perator lebih besar > Menguji nilai NOT NULL IS NOT NULL Menguji boolean benar/salah IS NOT Mengujin nilai NULL IS NULL Menguji boolean benar/salah IS Operator geser kekiri << Lebih kecil atau sama dengan <= Lebih kecil < Pencocokan pola tesks /mirip LIKE Operator pengurangan operator modulus % NOT BETWEEN … AND …

Mengecak yang nilainya tidak sama dalam range Operator tidak sama dengan

!=, <> Pembanding yang tidak mirip/sama NOT LIKE Pembandingan pola yang tidak sama dengan ekspresi NOT REGEXP NOT, ! ||, OR + REGEXP >> RLIKE * XOR

Membalik nilai Logika OR Operator penambahan Pembandingan pola yang sama dengan ekspresi Geser kekanan Sama dengan operator REGEXP Times operator Jika didepan tanda bilangan nigatif logika XOR

Urutan/hirarki Operator Secara hirarki gabungan sejumlah operator seperti pada urutan dibawaw ini :

INTERVAL BINARY, COLLATE ! - (unary minus), ~ (unary bit inversion) ^ *, /, DIV, %, MOD -, + <<, >> & | =, <=>, >=, >, <=, <, <>, !=, IS, LIKE, REGEXP, IN BETWEEN, CASE, WHEN, THEN, ELSE NOT &&, AND XOR ||, OR := : mysql> SELECT 1+2*3; -> 7 mysql> SELECT (1+2)*3; -> 9 mysql> SELECT 1+’1′; -> 2 mysql> SELECT CONCAT(2,’ test’); -> ‘2 test‘ mysql> SELECT 38.8, CAST(38.8 AS CHAR); -> 38.8, ‘38.8′

mysql> SELECT 38.8, CONCAT(38.8); -> 38.8, ‘38.8′ Fungsi pembanding Nama

Keterangan

COALESCE()

Mencari nilai pertama nonNULL

GREATEST()

Return the largest argument

IN()

Mengecek nilai termasuk dealam anggota bagian himpunan

INTERVAL()

Nilai index urutan dari bilangan satu ke bilangan berkutnya

ISNULL()

Mengecek nilai NULL

LEAST()

Mengasilkan nilai yang terkecil

NOT IN()

Kebalikan dari IN( )

STRCMP()

Membandingkan 2 nilai string

Contoh : COALESCE(value,...) · mysql> SELECT COALESCE(NULL,1); · -> 1 · mysql> SELECT COALESCE(NULL,NULL,NULL); · -> NULL GREATEST(value1,value2,...) · · ·

mysql> SELECT GREATEST(2,0); -> 2 mysql> SELECT GREATEST(34.0,3.0,5.0,767.0);

· -> 767.0 · mysql> SELECT GREATEST('B','A','C'); · -> 'C' LEAST(value1,value2,...) ·

mysql> SELECT LEAST(2,0);

· ·

-> 0 mysql> SELECT LEAST(34.0,3.0,5.0,767.0);

· -> 3.0 · mysql> SELECT LEAST('B','A','C'); · -> 'A' INTERVAL( N , N1 ,N2, N3,...) · mysql> SELECT INTERVAL(23, 1, 15, 17, 30, 44, 200); · -> 3 · mysql> SELECT INTERVAL(10, 1, 10, 100, 1000); · -> 2 · mysql> SELECT INTERVAL(22, 23, 30, 44, 200); · -> 0 expr IN (value,...) · mysql> SELECT 2 IN (0,3,5,7); · · ·

-> 0 mysql> SELECT 'wefwf' IN ('wee','wefwf','weg'); -> 1

Logical Operators Nama

Keterangan

AND, &&

Logika AND

NOT, !

Logika value

||, OR

Logika OR

XOR

Logika XOR

Kontrol Kondisi Nama

Keterangan

CASE

Oprasi Case

IF()

Kondisi If/else

IFNULL()

Kondisi if/else nilai NULL

NULLIF()

hasil NULL jika expr1 = expr2 benar

CASE nilai WHEN [pembanding_1] THEN hasil_1 [WHEN [pembanding_2] THEN hasil_2…] [ELSE hasil_3] END

CASE WHEN [kondisi_2] THEN hasil_1 [WHEN [kondisi_2] THEN hasil_2 …] [ELSE hasil_n] END mysql> SELECT CASE 1 WHEN 1 THEN ‘one’ ->

WHEN 2 THEN ‘two’ ELSE ‘more’ END;

-> ‗one‘ mysql> SELECT CASE WHEN 1>0 THEN ‘true’ ELSE ‘false’ END; -> ‗true‘ mysql> SELECT CASE BINARY ‘B’ -> WHEN ‘a’ THEN 1 WHEN ‘b’ THEN 2 END; -> NULL IF(expr1,expr2,expr3) Jika expr1 benar hasil expr2, salah expr3 mysql> SELECT IF(1>2,2,3); -> 3 mysql> SELECT IF(1<2,'yes','no'); -> 'yes' mysql> SELECT IF(STRCMP('test','test1'),'no','yes'); -> 'no' SELECT IF (agama='1','Islam',IF(agama='2','Kristen',IF(agama='3','Katholik',IF(agama='4','Hindu','Budha' )))) as agm FROM mhs IFNULL(expr1,expr2) Jika expr1 tidak NULL hasil expr1 , jika tidak expr2 mysql> SELECT IFNULL(1,0); -> 1 mysql> SELECT IFNULL(NULL,10); -> 10 mysql> SELECT IFNULL(1/0,10); -> 10 mysql> SELECT IFNULL(1/0,'yes'); -> 'yes' mysql> CREATE TABLE tmp SELECT IFNULL(1,'test') AS test; mysql> DESCRIBE tmp; +-------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+-------+ | test | char(4) | | | | | +-------+---------+------+-----+---------+-------+ NULLIF(expr1,expr2) Akan menghasilkan nilai NULL jika expr1 = expr2 bernilai benar, jika tidak hasil sama dengan expr1. Ini sama dengan operasi CASE WHEN expr1 = expr2 THEN NULL ELSE expr1 END. mysql> SELECT NULLIF(1,1); -> NULL

mysql> SELECT NULLIF(1,2); -> 1 Fungsi String Nama

Keterangan

ASCII()

Mengubah nilai karakter menjadi nilai ASCII

BIN()

Bilangan integer ke bilangan biner

BIT_LENGTH()

Penjang bit bilangan

CHAR_LENGTH()

Memperoleh panjang karakter

CHAR()

Memperoleh nilai interger ke karakter ASCII

CHARACTER_LENGTH()

Sama dengan CHAR_LENGTH()

CONCAT_WS()

Penggabungan teks, dengan kerakter pemisah

CONCAT()

Penggabungan teks

ELT()

Menampilkan nilai string menurut index urutan

FORMAT()

Menampilkan format nilai anggka dengan pemisah titik atau koma

LCASE()

Sama dengan LOWER()

LENGTH()

Mencari nilai panjar string

SUBSTR()

Mengambil potongan sederitan karakter

SUBSTRING()

Sama dengan SUBSTR()

TRIM()

Menhilangkan karater blank/space

UCASE()

Sama dengan UPPER()

Nama UPPER()

Keterangan Sama dengan UCASE()













COMMENTS2 Comments CATEGORIESTutorial MySQL Database MySQL 3 30SEP MySQL – Struktur Bahasa MySQL Bahasa strutrektur di MySQL dikenal dengan SQL (Structure Query Language). Pada bagian ini akan dibahas tentang perintah dengan SQL pada MySQL. Nilai Literal Nilia literal antara lain : strings, angka desimal, angka hexadecimal , boolean dan NULL. Strings string adalah karakter yang dituliskan dengan menggunakan tanda petik tunggal (―‘‖) atau petik ganda (―)

Contoh: ‗a string‘ ―Badiyanto‖ SELECT N‘some text‘; SELECT n‘some text‘; SELECT _utf8′some text‘;

ada sejumlah karakter khusus yang mengandung arti dalam SQL atau juga desebut spesial kararakter. Maka untuk menuliskan karakter tersebut menggunakan awalan backslash(― \ ‖)

Angka Anga berupa anggka desimal bulat tipenya adalah integer, untuk angka pecahan dipisahkan dengan ―.‖ . Sebuah angka secara default bilangan postitf tanpa tanda, atau tanda ―+‖ dan untuk negatif dengan tanda ―-‖ Contoh angka integer 1221 0 -32 Contoh angka untuk bilangan pecahan: 294.42 -32032.6809e+10 148.00

Bilangan Hexadesimal Mysql juga mendukung penggunaan angka hexadesimal, Dalam konteks bilangan mengunakan bilangan integer (64-bit precision). Dituliskan dengan format string dan dikonversikan ke bilangan biner (string). Contoh: mysql> SELECT x‘4D7953514C‘; -> ‗MySQL‘ mysql> SELECT 0x0a+0; -> 10

Materi Stored Procedure Dan Function

Recommend Documents