Hei hei… gimana kabarnya ni ? kali ini merta bakal share share dikit tentang Store Procedure, Function dan juga Trigger. Sejujurnya aku yakin diantara kalian pada ga mau pake cara ini, karena beberapa alasan yang ga bisa aku sebutin (takut ada yang ga terima ama argument ini v:). Tapi alasan ku tetep share tentang materi ini adalah karena tuntutan kurikulum ditempat aku belajar nuntut untuk tahu akan hal ini. Aku sempet explore di beberapa web dan masih agak susah memahaminya (iya aku tau itu karena emang akunya yang bego :v ). Jadi aku pikir mungkin ada yang senasib gitu ma aku disuruh pelajari materi ini dan akhirnya aku putuskan untuk bantu sebisa ku melalui tulisan ini. (bahasanya sok baik amat dah wkwkw v:)
ok biar ga lama lagi yuk terjun ke materi
Pengertian Stored Procedure
Stored Procedure adalah sebuah prosedur layaknya subprogram di dalam bahasa pemrograman regular yang tersimpan di dalam katalog basis data. Store procedure berisi sekumpulan perintah SQL yang ditunjukan untuk melaksanakan tugas tertentu dan diletakan pada server. Subprogram ini dapat dipanggil oleh client sedangkan pengeksekusiannya dilakukan pada server.
Kelebihan & Kekurangan Store Procedure
Beberapa kelebihan yang ditawarkan stored procedure antara lain:
- meningkatkan performa,
- meredukri trafik jaringan,
- reusable,
- meningkatkan kontrol sekuriti.
Dibalik kelibihan-kelebihannya, stored prosedur memiliki kekurangan, diantranya:
- berpotensi meningkatkan beban server
- penulisannya tidak mudah (memerlukan pengetahuan spesifik).
Parameter Store Procedure
Pada store procedure memiliki proc_parameter antara lain: (IN | OUT | INOUT) yang digunakan sebagai parameter untuk komunikasi data antara procedure dan program utama.
Penjelasannya sebagai berikut:
- IN
merupakan mode default ini mengindikasikan bahwa sebuah parameter dapat di pass(dikirim) ke stored procedure tetapi nilainya tidak dapat diubah (dari dalam stored procedure).
2. OUT
mode ini mengindikasikan bahwa stored procedure dapat mengubah nilai parameter dan mengirimkan kembali ke program yang memanggil.
3. INOUT
mode ini pada dasarnya merupakan kombinasi dari mode IN dan OUT.
Stored procedure berisi variasi dari operasi data, misalnya seleksi, penambahan, pengapusan, dan juga operasi-operasi DDL. Stored procedure juga dapat melibatkan variabel, pernyataan kondisional, dan pengulangan.
Cara membuat Store Procedure
Cara membuat store procedure
1. Klik menu routine
2. lalu pilih add routine
3. masukan nama store procedure yang ingin kalian buat
4. karena yang akan kita buat adalah store procedure maka biarkan saja kolom ini (kalau kalian membuat store function maka ubah nilai pada kolom ini menjadi function)
5. tentukan sifat parameter yang akan kalian berikan nanti(IN, OUT, INOUT)
6. beri nama untuk parameter tersebut
7. beri tipe data parameter yang dikirim
8. beri ukuran maksimal dari parameter
9. kalau kalian tidak menggunakan parameter bisa klik tombol drop di ujung kanan
10. kalau kalian memerlukan parameter lain bisa klik tombol add parameter
11. tulis isi fungsi kalian di bagian definition ini
12. kalau sudah selesai klik Go untuk membuat store procedure
Disini kita membuat sebuah store procedure dengan nama ‘getAgentName’ tanpa parameter. Store procedure ini berfungsi untuk menampilkan semua column ‘agent_name’ pada tabel ‘agents’. Dapat dilihat dibagian Definition code kalian diawali dengan ‘BEGIN’ dan diakhiri dengan ‘END’. Kalau sudah klik tombol ‘Go’ untuk mulai membuat store procedure.
Setelah selesai jika muncul tampilan seperti diatas maka store procedure kalian berhasil dibuat.
Untuk menjalankannya kalian bisa gunakan perintah CALL nama_store_procedure(parameter); ( contoh: CALL getAgentName(); ) lalu klik tombol ‘Go’.
Setelah menjalankan perintah tersebut maka store procedure dengan nama ‘getAgentName’ akan dijalankan dan menampilkan semua data colom ‘agent_name’ pada tabel ‘agents’.
Kalian juga bisa menjalankan store procedure ini dengan cara menekan tombol ‘execute’ pada list store procedure. Kalian juga bisa mengubahnya dengan menekan tombol ‘edit’ atau menghapusnya dengan menekan tombol ‘drop’ pada list store procedure.
Ok ambil nafas dulu v: , masih ada 2 materi lagi yang bakal kita bahas tapi sebelum itu kusaranin kalian break bentar biar otak fresh dulu baru lanjutin bacanya. kalo udah yuk ke materi berikutnya. semangat kawan :) .
Stored Function
Dalam database server ada 2 kategori 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, dan menggunakan fungsi yang bisa dibuat oleh user sendiri. Cara kerja store function hampir sama dengan stored procedure, namun yang membedakan adalah store function merupaka sebuah fungsi mengembalikan sebuah nilai balik (return value) melalui perintah return.
Cara membuat Store Function
1. Seperti yang dikatakan sebelumnya, untuk membuat store function maka ubah kolom type dari ‘procedure’ menjadi ‘function’ terlebih dahulu
Tampilan form akan berubah menjadi seperti gambar diatas.
1. Kita berikan nama untuk store function yang akan kita buat
2. Typenya biarkan function
3. Berikan nama parameter yang akan digunakan pada store function nanti
4. Tentukan type data dari parameter tersebut
5. Tentukan ukuran maksimal nilai dari parameter
6. Klik drop apabila fungsi ini tidak memerlukan parameter
7. Klik add parameter apabila memerlukan parameter lainnya
8. Tentukan return type / type data dari output yang dikeluarkan store function
9. Tentukan ukuran maksimal dari output
10. Biasanya return option akan otomatis terisi saat kalian menentukan return type, kalau tidak biarkan saja kosong
11. Pada bagian definition kalian akan menuliskan isi fungsi kalian
12. Setelah selesai klik tombol ‘Go’ untuk membuat store procedure
Disini kita membuat sebuah store function bernama ‘getCustomerName’. Fungsi ini menerima parameter dengan nama ‘cc’, type data ‘varchar’ dengan ukuran maksimal ‘10’. Output fungsi ini (return type) merupakan ‘varchar’ dengan ukuran maksimal ’20’. Dapat diperhatikan sama seperti saat membuat store procedure isi fungsi kita diawali dengan ‘BEGIN’ dan diakhiri dengan ‘END’.
Kemudian pada baris kedua kita mendeklarasikan sebuah variable bernama ‘cn’ dengan type data ‘varchar’ dan ukuran maksimal ‘20’ (DECLARE cn varchar(20)). Lalu pada baris ke 7 kita ingin agar hasil dari select data tersebut dimasukan ke variable ‘cn’ yang tadi kita buat (INTO cn). Terakhir kita men-RETURN variable ‘cn’ (RETURN cn).
Setelah semua selesai klik tombol ‘Go’
Apabila berhasil maka akan muncul tampilan seperti ini, dan nama store function yang tadi kalian buat akan ada pada list
Untuk menjalankan store function bisa dengan perintah SELECT store_function_name(parameter) ( contoh: SELECT getCustomerName(“C00020”); ) lalu klik tombol ‘Go’.
Setelah menjalankan perintah tersebut store function getCustomerName akan dijalankan, dimana fungsi ini akan menampilkan ‘cust_name’ dari tabel customers yang memiliki ‘cust_code’ ‘C000020’
Kalian juga bisa menjalankan store function dengan cara klik tombol execute pada list.
Jika store function yang kalian buat memerlukan parameter maka akan muncul modal form seperti diatas. Setelah mengisi nilai parameter klik ‘Go’
Maka hasilnya akan sama seperti memanggil store function melalui perintah sql.
Ayo gimana, udah cape belum? Sabar ya abis ini materi terakhir kok. aku ga nyangka bakal jadi sepanjang ini wkkwkw. Tapi salut deh ama kalian yang masih niat ngebaca sampai sejauh ini. kalo itu otak udah panes bisa dikasi break bentar biar fresh dulu.
kalo udah yuk lanjut ke last boss v: .
Trigger
Trigger adalah suatu objek database yang merupakan aksi atau prosedur yang dijalankan jika terjadi perubahan pada suatu data row akibat eksekusi perintah INSERT, UPDATE, DELETE. Trigger digunakan untuk menjaga konsistensi data.
Sebagai contoh, ketika sebuah record data pada tabel resep dihapus, trigger dapat diatur agar data pembayaran dari resep yang dihapus tadi yang terdapat pada tabel pembayaran agar ikut ikut terhapus sehingga tidak terjadi error dimasa mendatang.
Trigger dieksekusi apabila perintah INSERT/UPDATE/DELETE dijalankan:
1. INSERT Trigger dijalankan ketika terdapat operasi penambahan sebuah record data ke tabel bersangkutan.
2. UPDATE Trigger dijalankan ketika terdapat operasi pengubahan sebuah record data dalam bentuk tabel bersangkutan.
3. DELETE Trigger dijalankan ketika terdapat operasi penghapusan sebuah record data pada tabel bersangkutan.
Nilai yang dapat digunakan dalam trigger
Karena trigger digunakan pada saat terjadi perubahan row data, maka kita perlu referensi ke row sebelum dan sesudah perubahan. Untuk ini ada dua alias yang berfungsi untuk hal tersebut yaitu OLdan NEW. Sesuai namanya, OLD digunakan untuk referensi sebelum perubahan dan NEW untukreferensi sesudah perubahan.
Contohnya seperti kasus resep tadi. Jika kita membuat trigger yang dieksekusi saat sebuah resep dihapus maka kita dapat menggunakan nilai referensi ‘OLD’ untuk mendapatkan id_resep dari resep yang dihapus tadi. Nah id_resep inilah yang akan kita gunakan untuk menghapus data pembayaran yang memiliki id_resep yang sama
Cara membuat Trigger
Klik tab ‘more’ lalu pilih menu ‘trigger’
Selanjutnya akan muncul tampilan seperti ini, klik tombol ‘add trigger’ untuk mulai membuat trigger baru.
1. Masukan nama trigger yang akan kalian buat
2. Pilih table mana yang akan diawasi oleh trigger ini / trigger ini dieksekusi apabila terjadi manipulasi data pada tabel apa
3. Pilih waktu pengeksekusian trigger (apakah sebelum(BEFORE) atau sesudah(AFTER) terjadi manupulasi data)
4. Tentukan trigger berjalan apabila terjadi operasi INSERT/UPDATE/DELETE
5. Tuliskan perintah SQL yang ingin dijalankan saat trigger dieksekusi
6. Kalau sudah klik tombol ‘Go’ untuk mulai membuat trigger
Disini kita membuat trigger dengan nama ‘deleteOrderAndDaysOrder’. Kita ingin trigger ini dieksekusi apabila terjadi manipulasi data pada tabel ‘customer’. Trigger akan dijalankan setelah (AFTER) terjadi manipulasi data pada tabel customer. Trigger dieksekusi apabila operasi ‘DELETE’ dilakukan.
Trigger ini berfungsi untuk menghapus row data dari tabel ‘order’ dan ‘daysorder’ yang memiliki column ‘cust_code’ yang sama dengan ‘cust_code’ dari tabel customer yang dihapus tadi. Kita bisa mengakses nilai tersebut dengan referensi ‘OLD.cust_code’. Dapat kalian perhatikan bahwa perintah SQL yang kita tulis dibagian Definition diawali dengan ‘BEGIN’ dan diakhiri dengan ‘END’. Kalau sudah klik tombol ‘Go’ untuk membuat trigger.
Jika berhasil maka akan muncul tampilan seperti ini, kalian bisa melihat trigger yang kalian buat tadi di list trigger.
Sekarang mari kita cek apakah trigger kita berjalan sesuai keinginan atau tidak. (kita tidak bisa menjalankan trigger seperti store procedure/function)
Coba perhatikan disini kita memiliki salah satu row pada tabel ‘orders’ yang memiliki nilai ‘cust_code’ = ‘C00007’
Coba perhatikan disini kita juga memiliki salah satu row pada tabel ‘daysorder’ yang memiliki nilai ‘cust_code’ = ‘C00007’
Sekarang kita akan menghapus data customer pada pada tabel ‘customer’ yang memiliki nilai ‘cust_code’ = ‘C00007’
Penghapusan berhasil
Dapat dilihat sekarang sudah tidak ada lagi data pada tabel ‘orders’ yang memiliki cust_code = ‘C00007’. Itu karena trigger telah penghapusnya setelah terjadi penghapusan data dengan ‘cust_code’ = ‘C00007’ pada tabel ‘customer’
Dapat dilihat sekarang juga sudah tidak ada lagi data pada tabel ‘daysorder’ yang memiliki cust_code = ‘C00007’. Itu karena trigger telah penghapusnya setelah terjadi penghapusan data dengan ‘cust_code’ = ‘C00007’ pada tabel ‘customer’
Akhirnya selesai juga woi v: , serius kedepannya aku harus pecah materi panjang gini jadi beberapa sesi biar ga mumet liatnya wkwkkw. tenang aja bukan cuma kalian yang dibuat muak ama materi ini, aku yang bikin juga muak nyusunnya wkwkwk. karena terlalu panjang jadi mungkin ada beberapa kesalahan yang aku buat ( kalo ada dikasi tau ya v: ). Aku juga mau ngucapin terimakasih banyak buat yang udah baca materi panjang ini sampai selesai, salut cui v: semoga tulisan ini bisa sedikit membantu kalian yang memerlukan.
tapi sayang sekali masih banyak yang belum dibahas disini jadi jangan lupa explore lagi buat memperbanyak ilmu kalian :) . Ok seperti biasa kalau ada salah kata atau sejenisnya mohon dimaafkan dan kalau bisa dikoreksi agar aku bisa lebih baik lagi kedepannya.
Oke itu dulu untuk seri kali ini, saya Merta pamit undur diri dan ingat untuk ‘Haus akan ilmu baru’ :)
