Pertemuan 7: Stored Function dan Procedure

Pada pertemuan ini, mahasiswa akan mempelajari stored function dan stored procedure, yaitu sekumpulan statement SQL yang dapat disimpan di server dan dipanggil kembali saat dibutuhkan. Jika pada pertemuan sebelumnya mahasiswa berfokus pada trigger yang berjalan otomatis ketika event tertentu terjadi, maka pada pertemuan ini pembelajaran diarahkan pada cara menyimpan logika proses ke dalam database agar dapat digunakan kembali secara terstruktur.

Modul ini disusun untuk membantu mahasiswa memahami bahwa dalam praktik basis data, ada operasi-operasi tertentu yang sering digunakan berulang kali. Agar lebih rapi, aman, dan mudah dipanggil kembali, operasi tersebut dapat disimpan sebagai stored routine. Dengan pendekatan ini, client tidak perlu terus-menerus mengirim statement SQL yang sama, tetapi cukup memanggil function atau procedure yang telah dibuat.

Secara umum, modul ini memuat tujuan pembelajaran, gambaran umum materi, materi inti mengenai stored function dan stored procedure, serta aktivitas praktikum untuk mencoba pembuatan, penggunaan, peninjauan, dan penghapusan function maupun procedure secara langsung. Modul ini juga membahas pemrograman dasar di dalam routine, seperti variabel, kendali kondisional, dan perulangan.

Tujuan Pembelajaran

Setelah mengikuti praktikum pada pertemuan ini, mahasiswa diharapkan mampu:

1. menjelaskan konsep stored routine serta perbedaan function dan procedure,
2. membuat dan menggunakan stored function di dalam query,
3. membuat dan memanggil stored procedure dengan statement CALL,
4. memahami parameter IN, OUT, dan INOUT pada procedure,
5. menampilkan function dan procedure yang telah dibuat,
6. menghapus function dan procedure yang tidak diperlukan,
7. menggunakan variabel lokal di dalam function dan procedure,
8. menerapkan kendali kondisional IF dan CASE,
9. menerapkan perulangan WHILE, REPEAT ... UNTIL, dan LOOP.

Gambaran Umum

Topik utama pada pertemuan ini adalah stored function dan stored procedure. Keduanya termasuk ke dalam stored routine, yaitu kumpulan statement SQL yang disimpan di server dan dapat dipanggil kembali saat dibutuhkan. Dengan cara ini, logika tertentu dapat ditempatkan langsung di database, sehingga dapat dipakai oleh banyak aplikasi atau banyak pengguna tanpa harus menulis query yang sama berulang kali.

Pembahasan dimulai dari perbedaan mendasar antara function dan procedure. Function dapat dipanggil langsung di dalam statement SQL seperti SELECT, UPDATE, atau DELETE, dan hanya mengembalikan satu nilai. Sementara itu, procedure dipanggil menggunakan CALL dan dapat berisi berbagai operasi SQL, termasuk SELECT, INSERT, UPDATE, dan DELETE.

Setelah itu, mahasiswa akan mempelajari parameter pada procedure, yaitu IN, OUT, dan INOUT, kemudian dilanjutkan dengan cara melihat function dan procedure yang telah dibuat serta cara menghapusnya. Pada bagian berikutnya, pembahasan diperluas ke pemrograman dasar di dalam routine, seperti deklarasi variabel, pemberian nilai, penggunaan IF, CASE, dan bentuk-bentuk perulangan yang tersedia di MySQL.

Secara garis besar, pertemuan ini dirancang agar mahasiswa tidak hanya mampu membuat function dan procedure, tetapi juga memahami kapan function lebih tepat digunakan, kapan procedure lebih sesuai, serta bagaimana membangun logika pemrograman sederhana di dalam stored routine.

Materi Inti

1. Pengertian Stored Routine

Stored routine adalah sekumpulan statement SQL yang dapat disimpan di server MySQL. Setelah routine disimpan, client tidak perlu lagi memanggil statement individual berulang kali, tetapi cukup memanggil stored routine yang sesuai.

Stored routine bermanfaat dalam beberapa situasi berikut:

• ketika beberapa aplikasi client menggunakan bahasa atau platform yang berbeda, tetapi memerlukan operasi database yang sama,
• ketika keamanan lebih diutamakan, karena akses dapat dibatasi hanya melalui routine tertentu,
• ketika ingin mengurangi jumlah komunikasi antara client dan server.

2. Perbedaan Function dan Procedure

Stored routine dalam MySQL dibedakan menjadi dua jenis, yaitu function dan procedure.

Secara umum, function lebih cocok untuk menghasilkan nilai tertentu, sedangkan procedure lebih cocok untuk menjalankan proses atau rangkaian operasi.

3. Membuat Function

Function dibuat dengan sintaks berikut:

CREATE FUNCTION function_name ([func_parameter[,...]])
RETURNS type
routine_body

Function menerima parameter dan harus memiliki tipe nilai kembalian melalui klausa RETURNS. Isi function diletakkan dalam BEGIN ... END dan biasanya diakhiri dengan RETURN.

4. Menggunakan Function

Function dapat dipanggil langsung di dalam query, misalnya pada SELECT, untuk menghasilkan nilai tertentu. Karena function hanya mengembalikan satu nilai, maka penggunaannya sangat mirip dengan function bawaan MySQL.

5. Melihat dan Menghapus Function

Function yang telah dibuat dapat dilihat dengan:

SHOW FUNCTION STATUS;

Function yang sudah tidak diperlukan dapat dihapus dengan:

DROP FUNCTION nama_function;

6. Membuat Procedure

Procedure dibuat dengan sintaks berikut:

CREATE PROCEDURE sp_name ([proc_parameter[,...]])
routine_body

Procedure dipanggil menggunakan statement CALL nama_procedure(...). Berbeda dari function, procedure tidak digunakan di dalam ekspresi query, tetapi sebagai objek yang menjalankan proses tertentu.

7. Parameter Procedure: IN, OUT, dan INOUT

Procedure dapat memiliki tiga jenis parameter.

Jika jenis parameter tidak disebutkan, maka default-nya adalah IN.

a. IN

Parameter IN digunakan untuk mengirim nilai ke dalam procedure. Nilai ini bisa dipakai dalam query di dalam procedure, tetapi perubahan terhadap parameter tersebut tidak akan terlihat di luar procedure setelah procedure selesai.

b. OUT

Parameter OUT digunakan untuk mengirimkan hasil dari procedure ke pemanggil. Nilai hasil biasanya disimpan ke variabel session yang diawali dengan karakter @.

c. INOUT

Parameter INOUT diinisialisasi oleh pemanggil, dapat diubah oleh procedure, dan hasil akhirnya tetap dapat diakses oleh pemanggil setelah procedure selesai dijalankan.

8. Melihat dan Menghapus Procedure

Procedure yang telah dibuat dapat dilihat dengan:

SHOW PROCEDURE STATUS;

Procedure yang sudah tidak diperlukan dapat dihapus dengan:

DROP PROCEDURE nama_procedure;

9. Variabel Lokal

Seperti pada bahasa pemrograman umumnya, function dan procedure dapat menggunakan variabel lokal. Deklarasi variabel dilakukan dengan sintaks berikut:

DECLARE var_name [, var_name] ... type [DEFAULT value]

Jika DEFAULT tidak diberikan, maka nilai awal variabel adalah NULL. Nilai variabel dapat diubah menggunakan SET, dan hasil query juga dapat dimasukkan ke variabel dengan SELECT ... INTO.

10. Ruang Lingkup Variabel

Ruang lingkup variabel berlaku di dalam blok BEGIN ... END tempat variabel tersebut didefinisikan. Variabel dapat diakses oleh blok di dalamnya, kecuali ada deklarasi lain dengan nama yang sama.

Nama variabel lokal sebaiknya tidak sama dengan nama kolom tabel, karena MySQL dapat memprioritaskan nama variabel dalam statement SQL dan menimbulkan hasil yang tidak diinginkan.

11. Kendali Kondisional IF

MySQL mendukung kendali kondisional IF di dalam function dan procedure.

Bentuk dasarnya adalah:

IF search_condition THEN statement_list
 [ELSEIF search_condition THEN statement_list] ...
 [ELSE statement_list]
END IF;

IF digunakan ketika routine perlu mengambil keputusan berdasarkan suatu kondisi tertentu.

12. Kendali CASE

MySQL juga menyediakan kendali CASE dengan dua bentuk utama.

Bentuk pertama membandingkan suatu nilai terhadap beberapa kemungkinan nilai.

CASE case_value
 WHEN when_value THEN statement_list
 [WHEN when_value THEN statement_list] ...
 [ELSE statement_list]
END CASE

Bentuk kedua mengevaluasi beberapa kondisi logis.

CASE
 WHEN search_condition THEN statement_list
 [WHEN search_condition THEN statement_list] ...
 [ELSE statement_list]
END CASE

CASE berguna ketika pilihan kondisi yang ditangani lebih dari dua kemungkinan.

13. Perulangan WHILE

MySQL menyediakan perulangan WHILE dengan sintaks berikut:

WHILE search_condition DO
 statement_list
END WHILE

Statement di dalam WHILE akan dijalankan berulang selama kondisi bernilai TRUE.

14. Perulangan REPEAT … UNTIL

Perulangan REPEAT ... UNTIL memiliki bentuk berikut:

REPEAT
 statement_list
UNTIL search_condition
END REPEAT

Perulangan ini selalu dijalankan setidaknya satu kali, kemudian terus berulang sampai kondisi bernilai TRUE.

15. Perulangan LOOP

MySQL juga menyediakan perulangan LOOP.

[begin_label:] LOOP
 statement_list
END LOOP [end_label]

LOOP biasanya dihentikan dengan LEAVE, dan pengulangan ke awal loop dapat dilakukan dengan ITERATE.

16. Hal-Hal yang Perlu Diperhatikan

Beberapa hal penting yang perlu dipahami dalam stored routine adalah sebagai berikut:

• function hanya mengembalikan satu nilai,
• procedure dipanggil dengan CALL,
• function tidak dapat digunakan secara rekursif,
• procedure secara teori dapat rekursif, tetapi secara default dinonaktifkan,
• variabel lokal sebaiknya tidak memakai nama yang sama dengan kolom tabel,
• penulisan routine yang terdiri dari banyak statement biasanya memerlukan perubahan delimiter.

Penutup Pengantar

Sebelum masuk ke aktivitas praktikum, mahasiswa perlu memahami bahwa seluruh materi pada pertemuan ini saling berkaitan. Pembuatan function, pembuatan procedure, penggunaan parameter IN, OUT, INOUT, pemakaian variabel lokal, kendali IF dan CASE, serta perulangan WHILE, REPEAT, dan LOOP merupakan bagian dari satu proses yang sama, yaitu membangun logika pemrograman di dalam database. Dengan memahami materi inti ini terlebih dahulu, mahasiswa akan lebih mudah mengikuti percobaan praktikum secara runtut dan memahami alasan di balik setiap langkah yang dijalankan.