BAB 11 FUNGSI DAN PROSEDUR

FUNGSI DAN PROSEDUR

1. FUNGSI

Fungsi (function) pada MySQL sama dengan bahasa pemrograman yang lain. Fungsi mengembalikan suatu nilai, yang berbeda dengan prosedur. Berikut contoh penggunaan fungsi.

2. PROSEDUR

Prosedur (procedure) mirip dengan fungsi, dimana yang membedakan adalah suatu prosedur tidak mengembalikan nilai. Berikut contoh dari prosedur.

BAB 10 ORDERING, GROUPING dan HAVING

ORDERING, GROUPING & HAVING

1. ORDERING

Fungsi ordering digunakan untuk mengurtkan tampilan data berdasarkan atribut tertentu. Misal diminta untuk ditampilkan nama_produk dan no_np yang diurutkan berdasarkan no_np.

Secara default fungsi ordering mengurutkan data secara ascending, yaitu secara alfabetik dari A ke Z, atau secara angka dari yang terkecil ke terbesar. Pengurutan juga bisa dilakukan secara descending. Misalkan diminta untuk menampilkan data produk dan penerbitnya diurutkan berdasarkan stok terbesar.

2. GROUPING

Grouping artinya pengelompokkan, digunakan untuk mengelompokkan data berdasarkan atribut tertentu. Sebagai contoh Anda diminta untuk menampilkan banyaknya produk dan total stok dari produk untuk setiap nota_pembelian. Query yang dapat digunakan adalah:

3. HAVING

Having digunakan untuk menggantikan WHERE dan biasanya digunakan ketika harus dipasangkan dengan fungsi agregat. Sebagai contoh diminta untuk menampilkan data pengarang yang menulis lebih dari tiga produk.

BAB 9 VIEW

VIEW

1. DEFINISI VIEW

Tujuan dari pembuatan view adalah untuk kenyamanan (mempermudah penulisan query), untuk keamanan (menyembunyikan beberapa kolom yang bersifat rahasia), atau dalam beberapa kasus bisa digunakan untuk mempercepat proses menampilkan data (terutama jika kita akan menjalankan query tersebut secara berulang). Misal ingin dibuat dua buah tampilan. Tampilan pertama yaitu menampilkan daftar nota pembelian yang dimiliki oleh konsumen yang bernama Siiti.

2. MEMBANGUN VIEW

Disini akan dibagi menjadi dua sub bab lagi, yaitu view untuk data produk yang dibeli seorang konsumen dan data produk yang tidak dibeli seorang konsumen.

2.1 Kasus 1: View untuk data produk yang dibeli Konsumen

Untuk data produk yang dibeli konsumen, kita dapat membangun view sebagai berikut:

Sedangkan untuk menampilkan view yang sudah dibangun tadi, cukup tuliskan saja nama view nya:

Jika ada penambahan produk yang dibeli oleh seorang konsumen, maka Anda dapat dengan mudah menampilkan daftar produk yang dibeli oleh konsumen dengan menggunakan viewproduk_pupuk.

2.2 Kasus 2: View untuk data produk yang tidak dibeli konsumen

Untuk membangun tampilan ini lebih mudah dengan memanfaatkan viewproduk_notpupuk, dengan query sebagai berikut:

Sedangkan untuk menampilkan view sama seperti di atas.

3. MENGHAPUS VIEW

Untuk menghapus view dapat menggunakan perintah DROP. Sebelum dihapus, view dapat dilihat pada list tabel:

Jika dilakukan penghapusan, maka daftar view pun akan hilang.

JOIN

JOIN

1. NATURAL JOIN

Jika diperhatikan, penulisan query menggunakan SELECT untuk menampilkan data yang berrelasi menggunakan klausa WHERE memerlukan banyak energi. Query yang dituliskan panjang sehingga kemungkinan kesalahan dalam penulisan query pun semakin besar. Sebagai contoh perhatikan query berikut yang menggunakan klausa WHERE:

Sekarang Anda dapat membuat tampilan yang sama dengan query yang lebih singkat, yaitu menggunakan NATURAL JOIN. Seperti yang ditunjukkan pada gambar berikut:

Untuk membuktikan kondisi tersebut, di sini akan diberikan tambahan tabel yaitu tabel admin. Dimana tabel admin berrelasi dengan tabel lainnya dalam hal pengelolaan sehingga database memiliki tabel yang mencatat history pengelolaan data. Lebih jelasnya digambarkan pada ERD berikut:

Perhatikan pada tabel nota_pembelian PK nya berubah menjadi no_np (silakan diubah dengan menggunakan ALTER). Selain itu terdapat juga tambahan entitas yang lain, seperti admin, gudang. Berikut akan ditunjukkan pembuatan tabel petugas.

Sedangkan untuk tabel lainnya hanya akan dicontohkan pembuatan tabel gudang saja. Silakan tabel lainnya juga dibuat dengan cara yang sama.

Kemudian isikan tabel admin dan tabel gudang yang telah dibuat tadi dengan data sebagai berikut (tabel lainnya silakan diisi sendiri):

2. LEFT JOIN dan RIGHT JOIN

Left Join akan menampilkan semua record dari tabel sebelah kiri, dan record dari tabel sebelah kanan yang cocok. Hasilnya akan NULL untuk bagian kanan jika tidak ada kecocokan. Misal diminta untuk ditampilkan tabel kelola_pengarang yang menampilkan juga no HP admin yang mencatat. Query yang tepat jika menggunakan left join adalah sebagai berikut:

Akan tetapi jika posisi kedua tabel yang berhubungan diubah, maka tampilan datanya pun akan memiliki arti yang lain. Yaitu menampilkan pekerjaan setiap admin dalam mengelola data.

Right join adalah join yang serupa dengan left join, hanya saja posisi tabel yang kanan akan ditampilkan semua record nya. Sedangkan tabel yang kiri hanya record yang bersesuaian saja.

3. INNER JOIN

Versi aman dari left join maupun right join adalah inner join. Inner join tidak mementingkan posisi tabel pada query dan fungsinya sama dengan klausa where. Misalkan diperlukan tampilan dari pengelolaan pengarang, dimana menampilkan nama admin dan nama konsumen yang dikelola, query nya adalah sebagai berikut:

SUBQUERY BERSARANG

SUBQUERY BERSARANG

1. SELECT

Berdasarkan kasus yang sudah ada, ada beberapa permasalahan yang mungkin saja agak rumit jika diselesaikan dengan query SQL dasar. Misal Anda diminta untuk menampilkan data pengarang, yang pada tahun 2017 bukunya diterbitkan oleh Erlangga dan pada tahun 2016 bukunya diterbitkan oleh Andi. Untuk menyelesaikan ini Anda dapat menggunakan subquery bersarang:

DISTINCT digunakan untuk jika dalam suatu tampilan data terdapat baris yang duplikat (lebih dari satu baris dengan isi yang sama), maka hanya ditampilkan satu baris data saja.

2. INSERT

Jika Anda diminta untuk memasukkan data baru, dimana memiliki detail sebagai berikut:

Nama Produk: “Pupuk Basah”

Harga Jual : Rp 12.000

Stok : 50

No Nota Pembelian: NULL

Pengarang : Sri Hartati dan Agus Harjoko

Maka Anda dapat menggunakan query bersarang berikut untuk tabel buku:

Sementara untuk tabel nota_pembelian akan menjadi lebih rumit, misal :

3. UPDATE

4. DELETE

Sama seperti kasus sebelumnya, jika diminta untuk menghapus data produk yang diada di nota_pembelian, maka query yang dapat digunakan adalah sebagai berikut:

Jika data produk ada yang dihapus, lalu bagaimana dengan tabel nota_pembelian? Karena sebelumnya membangun tabel nota_pembelian menggunakan ON DELETE CASCADE, maka seharusnya data pada tabel tersebut akan otomatis ikut hilang. Mari kita buktikan, tampilkan data pada tabel produk

Terlihat pada tabel tersebut tidak ada nama_produk = pupuk basah, karena sudah dihapus menggunakan query sebelumnya. Sekarang kita tampilkan tabel nota_pembelian:

MULTIPLE RELATIONS

MULTIPLE RELATIONS

Pada pertemuan sebelumnya, telah dibangun dan diisi tabel produk dan tabel pupuk_rusak.

1.   Pada pertemuan kali ini, kedua tabel tersebut akan dihapus terlebih dahulu karena kedua tabel tersebut tidak dibangun atas dasar database relasional:

2.     Kemudian kita akan rancang database relasional sederhana untuk kasus gudang_logistik:

SET OPERATIONS

SET OPERATIONS

Pada pertemuan ini diperlukan lebih dari satu tabel untuk database gudang_logistik.

1.  Tabel tersebut memiliki karakteristik yang serupa dengan tabel produk. Misal tabel yang dimaksud adalah tabel yang menyimpan data-data pupuk rusak, sehingga memiliki nama pupuk_rusak:

2. Perhatikan pada CREATE TABLE yang dilakukan untuk pupuk_rusak diberikan AUTO_INCREMENT. Dengan demikian pada saat penginputan data baru, idpupuk tidak perlu diisikan karena akan terisi sendiri dengan otomatis dengan nilai yang selalu meningkat (increment). Sekarang Anda bisa mengisi tabel pupuk_rusak dengan data sebagai berikut:

3.  Operasi Union digunakan ketika mendapati kasus diperlukannya penggabungan data dari dua tabel yang memiliki kesesuaian. Format dari operasi ini adalah:

(SELECT …) UNION (SELECT …)

Sebagai contoh diperlukan data yang menggambarkan data seluruh produk, baik yang masih bagus ataupun yang sudah rusak. Dengan demikian diperlukan operasi union antara tabel produk dengan tabel pupuk_rusak:

4.   Operasi Intersect digunakan ketika mendapati kasus diperlukannya irisan data dari dua tabel yang bersesuaian. Pada MySQL tidak terdapat perintah INTERSECT, sehingga dapat digunakan format berikut untuk menggantikannya:

SELECT a FROM b WHERE a IN (SELECT a FROM c);

Sebagai contoh diperlukan data buku yang terdapat pada tabel produk dan juga terdapat pada tabel pupuk_rusak: