Stored Functions

Stored procedure memproses data dan bisa mengembalikan result set, tetapi ia tidak bisa berpartisipasi dalam ekspresi — kita tidak bisa menulis SELECT harga, diskon(harga) FROM produk dengan prosedur. Stored function mengisi peran itu: objek database yang menghitung dan mengembalikan tepat satu nilai skalar, sehingga bisa dipanggil di dalam SELECT, WHERE, ORDER BY, atau ekspresi apa pun layaknya fungsi bawaan ROUND() maupun CONCAT(). Pada chapter ini kita membuat function pertama, memakainya langsung di query, menguji nyata bagaimana attribute DETERMINISTIC berperan ketika binary log aktif dan ketika tidak, membuktikan larangan mengembalikan result set, lalu menutup dengan tabel keputusan function melawan procedure.

Menyiapkan Database Latihan #

Database dan tabel latihan dibangun ulang seperti biasa:

DROP DATABASE IF EXISTS belajar_sp;
CREATE DATABASE belajar_sp;
USE belajar_sp;

Output yang Dihasilkan:

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 1 row affected (0.00 sec)

Database changed

CREATE TABLE produk (
  id INT PRIMARY KEY,
  nama VARCHAR(50) NOT NULL,
  harga DECIMAL(10,2) NOT NULL,
  stok INT NOT NULL
);

INSERT INTO produk (id, nama, harga, stok) VALUES
  (1, 'Kopi Gayo', 55000.00, 10),
  (2, 'Teh Melati', 25000.00, 20),
  (3, 'Gula Aren', 15000.00, 30),
  (4, 'Susu Kental', 8000.00, 40),
  (5, 'Kopi Toraja', 62000.00, 5);

Output yang Dihasilkan:

Query OK, 0 rows affected (0.00 sec)

Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

Function Pertama: harga_diskon #

Dua bagian wajib membedakan CREATE FUNCTION dari CREATE PROCEDURE: klausa RETURNS tipe di kepala deklarasi dan statement RETURN nilai di tubuh. Kita buat function yang menghitung harga setelah dipotong 10%:

DROP FUNCTION IF EXISTS harga_diskon;
DELIMITER $$
CREATE FUNCTION harga_diskon(p_harga DECIMAL(10,2))
RETURNS DECIMAL(10,2)
DETERMINISTIC
BEGIN
  RETURN ROUND(p_harga * 0.9, 2);
END$$
DELIMITER ;

Output yang Dihasilkan:

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Mari kita bedah baris per barisnya:

1. DROP FUNCTION IF EXISTS harga_diskon; membersihkan versi lama — pola idempoten yang sama dengan prosedur, kini dengan DROP FUNCTION.
2. CREATE FUNCTION harga_diskon(p_harga DECIMAL(10,2)) mendeklarasikan function dengan satu parameter IN bernama p_harga. Function hanya mengenal parameter masukan; tidak ada OUT maupun INOUT karena keluaran function adalah nilai kembaliannya.
3. RETURNS DECIMAL(10,2) menyatakan tipe nilai yang dikembalikan — wajib ditulis di sini, dan tipe DECIMAL(10,2) kita pilih sama dengan tipe harga agar hasilnya presisi tanpa pembulatan mengejutkan.
4. DETERMINISTIC adalah attribute yang menjanjikan: “untuk masukan yang sama, function ini selalu mengembalikan hasil yang sama”. Perannya krusial untuk replication — kita bedah segera di bagian khusus.
5. RETURN ROUND(p_harga * 0.9, 2); mengakhiri function dan menyerahkan nilainya. Statement RETURN wajib ada; jalur eksekusi mana pun yang tidak berujung RETURN membuat server mengembalikan NULL dengan warning.

Memanggil Function di dalam SELECT #

Berbeda dari prosedur, function tidak dipanggil dengan CALL — ia ditempelkan langsung ke dalam ekspresi:

SELECT nama, harga, harga_diskon(harga) AS harga_diskon
FROM produk
ORDER BY id;

Output yang Dihasilkan:

+-------------+----------+--------------+
| nama        | harga    | harga_diskon |
+-------------+----------+--------------+
| Kopi Gayo   | 55000.00 |     49500.00 |
| Teh Melati  | 25000.00 |     22500.00 |
| Gula Aren   | 15000.00 |     13500.00 |
| Susu Kental |  8000.00 |      7200.00 |
| Kopi Toraja | 62000.00 |     55800.00 |
+-------------+----------+--------------+
5 rows in set (0.00 sec)

Function dievaluasi sekali per baris — layaknya ROUND() — sehingga kolom harga_diskon terisi untuk kelima produk. Karena function mengembalikan nilai, ia juga bebas dipakai di ekspresi yang lebih besar:

SELECT nama, harga, harga_diskon(harga) AS harga_diskon,
       harga - harga_diskon(harga) AS hemat
FROM produk
ORDER BY hemat DESC
LIMIT 3;

Output yang Dihasilkan:

+-------------+----------+--------------+---------+
| nama        | harga    | harga_diskon | hemat   |
+-------------+----------+--------------+---------+
| Kopi Toraja | 62000.00 |     55800.00 | 6200.00 |
| Kopi Gayo   | 55000.00 |     49500.00 | 5500.00 |
| Teh Melati  | 25000.00 |     22500.00 | 2500.00 |
+-------------+----------+--------------+---------+
3 rows in set (0.00 sec)

Kolom hemat menghitung selisihnya di tempat, alias harga_diskon ikut ORDER BY, dan LIMIT 3 menyisakan tiga produk dengan penghematan terbesar. Pola seperti inilah alasan function jauh lebih nyaman daripada prosedur untuk perhitungan kolom: satu definisi, dipakai di seluruh query tanpa CALL terpisah.

Attribute DETERMINISTIC, NO SQL, dan Binary Log #

Attribute karakteristik function bukan formalitas. MySQL mereplikasi lewat binary log, dan untuk merekam perubahan data yang dilakukan sebuah function, server harus tahu cara function berperilaku: hasilnya bisa diprediksi dari masukannya (DETERMINISTIC), tidak menyentuh data sama sekali (NO SQL), hanya membaca data (READS SQL DATA), atau mengubah data (MODIFIES SQL DATA). Tanpa satu pun attribute tersebut, server berlog-aktif menolak pembuatan function dengan ERROR 1418 karena tidak bisa menjamin konsistensi replika.

Sekarang kita uji perilaku nyatanya di server latihan kita. Pertama, kita periksa apakah binary log aktif:

SELECT @@log_bin AS log_bin_aktif;

Output yang Dihasilkan:

+---------------+
| log_bin_aktif |
+---------------+
|             0 |
+---------------+
1 row in set (0.00 sec)

Nilainya 0 — binary log tidak aktif di server ini. Konsekuensinya langsung bisa kita buktikan: function berikut dibuat tanpa attribute apa pun dan tetap diterima:

DROP FUNCTION IF EXISTS pajak;
DELIMITER $$
CREATE FUNCTION pajak(p_harga DECIMAL(10,2))
RETURNS DECIMAL(10,2)
BEGIN
  RETURN ROUND(p_harga * 0.11, 2);
END$$
DELIMITER ;
SELECT pajak(10000.00) AS pajak_dari_10000;

Output yang Dihasilkan:

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

+------------------+
| pajak_dari_10000 |
+------------------+
|          1100.00 |
+------------------+
1 row in set (0.00 sec)

CREATE FUNCTION tanpa DETERMINISTIC berhasil dan pemanggilannya mengembalikan 1100.00 — persis hasil nyata di server uji kita yang berjalan tanpa binary log. Di server produksi dengan log_bin aktif (pengaturan bawaan MySQL ketika replication dipakai), statement yang sama ditolak dengan ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled. Itulah alasan kebiasaan terbaiknya: selalu tulis attribute yang sesuai kenyataan function Anda, apa pun status log server hari ini.

Function Tidak Boleh Mengembalikan Result Set #

Batas tegas lain: function dipanggil di dalam ekspresi, dan ekspresi mengharapkan satu nilai — bukan tabel. Statement yang mengirim result set (SELECT tanpa INTO, SHOW) dilarang keras di tubuh function:

-- @error
DROP FUNCTION IF EXISTS salah_bentuk;
DELIMITER $$
CREATE FUNCTION salah_bentuk()
RETURNS INT
BEGIN
  SELECT 1;
  RETURN 1;
END$$
DELIMITER ;

Output yang Dihasilkan:

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

ERROR 1415 (0A000): Not allowed to return a result set from a function
Bye

ERROR 1415 (0A000): Not allowed to return a result set from a function menolak pembuatan function itu di tempat — MySQL memeriksa larangan ini saat CREATE, bukan saat pemanggilan. Bila sebuah function memang perlu membaca data tabel, jalannya satu: SELECT ... INTO ke variabel lokal lalu RETURN variabel tersebut — membaca diam-diam tanpa mengirim result set ke pemanggil. Prosedur tetap satu-satunya tempat bagi logika yang perlu mengirim banyak baris hasil.

Function vs Procedure: Kapan Memakai yang Mana #

Pola praktisnya: function untuk rumus yang dipakai berulang di banyak query (diskon, pajak, format label harga), prosedur untuk proses multi-langkah dan laporan. Function yang menghitung harga_diskon membuat seluruh laporan di aplikasi berhenti menyalin rumus harga * 0.9 — rumusnya tinggal di satu tempat di dalam database.

Membersihkan Database Latihan #

DROP FUNCTION harga_diskon;
DROP FUNCTION pajak;
DROP DATABASE belajar_sp;

Output yang Dihasilkan:

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

Referensi #

• CREATE FUNCTION Statement — MySQL 8.4 Reference Manual
• Stored Routines — MySQL 8.4 Reference Manual

Dua anggota utama stored program kini lengkap: prosedur untuk proses dan laporan, function untuk rumus di dalam ekspresi. Pertanyaan yang tersisa bukan lagi “bagaimana”, melainkan “kapan” — kapan logika sebaiknya pindah ke dalam database, dan kapan justru harus tetap di aplikasi. Chapter penutup section ini membedahnya: keuntungan nyata stored programs pada batch berat data dan validasi terpusat, biaya tersembunyinya pada pengujian dan portabilitas, serta panduan praktis untuk mengambil keputusan.