KAL
Menu

Stored Procedure dan Fungsi

Logika tersimpan di server dengan BEGIN...END, parameter, dan DELIMITER.

Daftar isi

Prosedur tersimpan (stored procedure) adalah kumpulan pernyataan SQL yang tersimpan di server di bawah sebuah nama dan dijalankan dengan satu panggilan CALL, sedangkan fungsi tersimpan (stored function) adalah program tersimpan serupa yang mengembalikan satu nilai dan dapat dipanggil dari dalam query. Keduanya memindahkan logika berulang ke sisi server, sehingga perhitungan yang sama cukup ditulis sekali dan dipanggil dari klien mana pun. Topik ini membahas struktur blok BEGIN...END, peran DELIMITER pada klien mysql, parameter IN dan OUT, variabel beserta alur kontrol IF, serta pembuatan fungsi tersimpan dengan CREATE FUNCTION. Sebagai bahan latihan, basis data perpustakaan dipakai kembali, kali ini untuk memindahkan perhitungan denda keterlambatan ke server.

Prosedur pertama dan peran DELIMITER#

CREATE PROCEDURE menyimpan blok pernyataan di bawah sebuah nama. Tubuh prosedur dibungkus BEGIN dan END, dan di dalamnya boleh terdapat banyak pernyataan yang masing-masing diakhiri ;. Masalahnya, klien mysql memakai ; sebagai pemisah antarpernyataan, sehingga klien mengirim potongan pertama tubuh prosedur sebelum bloknya selesai dan server menolaknya. Solusinya adalah DELIMITER, yaitu perintah klien yang mengganti pemisah pernyataan sementara, misalnya menjadi //, sampai prosedur selesai dikirim.

Jalankan persiapan berikut, kemudian amati apa yang terjadi ketika prosedur dibuat tanpa DELIMITER.

sql persiapan-stored-procedure.sql 
-- tidak-dijalankan
-- Skrip persiapan topik stored procedure dan fungsi: basis data perpustakaan

DROP DATABASE IF EXISTS perpustakaan;
CREATE DATABASE perpustakaan;
USE perpustakaan;

CREATE TABLE anggota (
  id   INT AUTO_INCREMENT PRIMARY KEY,
  nama VARCHAR(100) NOT NULL
);

CREATE TABLE buku (
  id      INT AUTO_INCREMENT PRIMARY KEY,
  judul   VARCHAR(200) NOT NULL,
  penulis VARCHAR(100) NOT NULL,
  stok    INT NOT NULL DEFAULT 0
);

CREATE TABLE peminjaman (
  id              INT AUTO_INCREMENT PRIMARY KEY,
  anggota_id      INT NOT NULL,
  buku_id         INT NOT NULL,
  tanggal_pinjam  DATE NOT NULL,
  jatuh_tempo     DATE NOT NULL,
  tanggal_kembali DATE NULL,
  FOREIGN KEY (anggota_id) REFERENCES anggota(id),
  FOREIGN KEY (buku_id) REFERENCES buku(id)
);

INSERT INTO anggota (nama) VALUES
  ('Ali'), ('Budi'), ('Citra'), ('Dewi'), ('Eka');

INSERT INTO buku (judul, penulis, stok) VALUES
  ('Dasar Astronomi untuk Pemula', 'Hartono', 3),
  ('Sejarah Maritim Nusantara', 'Wijaya', 2),
  ('Panduan Menanam Sayuran', 'Santoso', 4),
  ('Pengantar Fisika Klasik', 'Kusuma', 0),
  ('Cerita Rakyat Nusantara', 'Lestari', 3),
  ('Ilmu Gizi Sehari-hari', 'Pratiwi', 2);

INSERT INTO peminjaman (anggota_id, buku_id, tanggal_pinjam, jatuh_tempo, tanggal_kembali) VALUES
  (1, 1, '2026-05-02', '2026-05-16', '2026-05-20'),
  (1, 2, '2026-05-02', '2026-05-16', '2026-05-14'),
  (1, 3, '2026-06-01', '2026-06-15', NULL),
  (2, 4, '2026-05-10', '2026-05-24', '2026-05-22'),
  (2, 5, '2026-06-03', '2026-06-17', NULL),
  (2, 6, '2026-06-03', '2026-06-17', '2026-06-25'),
  (3, 1, '2026-05-20', '2026-06-03', '2026-06-01'),
  (3, 4, '2026-06-05', '2026-06-19', NULL),
  (4, 2, '2026-05-25', '2026-06-08', '2026-06-07'),
  (4, 3, '2026-06-12', '2026-06-26', '2026-07-02'),
  (5, 5, '2026-06-14', '2026-06-28', NULL);
bash 
# membuat ulang basis data perpustakaan dari nol
mysql -u root < persiapan-stored-procedure.sql
bash 
# CREATE PROCEDURE tanpa DELIMITER: klien berhenti pada ; pertama
mysql -u root perpustakaan <<'SQL'
CREATE PROCEDURE jumlah_anggota_rusak ()
BEGIN
  SELECT COUNT(*) FROM anggota;
SQL
text Keluaran 
ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 3

Server menerima perintah yang belum lengkap, yaitu CREATE PROCEDURE tanpa END, sehingga error 1064 muncul sebelum prosedur sempat tersimpan. Tulis ulang pembuatan prosedur dengan DELIMITER, lalu panggil hasilnya dengan CALL.

bash 
# DELIMITER // memindahkan pemisah sementara agar ; di dalam blok lolos
mysql -t -u root perpustakaan <<'SQL'
DELIMITER //

DROP PROCEDURE IF EXISTS jumlah_anggota //

CREATE PROCEDURE jumlah_anggota ()
BEGIN
  SELECT COUNT(*) AS jumlah_anggota FROM anggota;
END //

DELIMITER ;

CALL jumlah_anggota();
SQL
text Keluaran 
+----------------+
| jumlah_anggota |
+----------------+
|              5 |
+----------------+

Prosedur jumlah_anggota kini tersimpan di server dan dapat dipanggil berulang tanpa menulis ulang querynya. Pola DROP PROCEDURE IF EXISTS sebelum CREATE PROCEDURE membuat skrip tetap dapat dijalankan ulang, karena nama yang sudah ada dihapus terlebih dahulu. Prosedur pada contoh ini masih membungkus satu SELECT saja, sedangkan kegunaan sesungguhnya muncul ketika tubuhnya memuat parameter, variabel, dan alur kontrol.

Parameter IN dan OUT#

Parameter prosedur memiliki tiga mode, yaitu IN untuk masukan, OUT untuk keluaran, dan INOUT untuk keduanya sekaligus. Mode IN merupakan bawaan, sehingga parameter tanpa mode selalu berperilaku sebagai masukan. Perubahan terhadap parameter IN di dalam tubuh prosedur tidak terlihat oleh pemanggil. Parameter OUT berlaku sebaliknya, yaitu diisi oleh prosedur, lalu hasilnya ditampung ke dalam variabel sesi yang disediakan pemanggil.

Prosedur pertama pada bagian ini menerima satu parameter IN berupa nomor anggota, lalu mengirim daftar pinjaman yang masih berjalan untuk anggota tersebut.

bash 
# prosedur dengan parameter IN untuk menyaring daftar pinjaman aktif
mysql -t -u root perpustakaan <<'SQL'
DELIMITER //

DROP PROCEDURE IF EXISTS pinjam_aktif_anggota //

CREATE PROCEDURE pinjam_aktif_anggota (IN p_anggota_id INT)
BEGIN
  SELECT b.judul AS buku, p.tanggal_pinjam, p.jatuh_tempo
  FROM peminjaman AS p
  JOIN buku AS b ON b.id = p.buku_id
  WHERE p.anggota_id = p_anggota_id
    AND p.tanggal_kembali IS NULL
  ORDER BY p.id;
END //

DELIMITER ;

CALL pinjam_aktif_anggota(1);
CALL pinjam_aktif_anggota(2);
SQL
text Keluaran 
+-------------------------+----------------+-------------+
| buku                    | tanggal_pinjam | jatuh_tempo |
+-------------------------+----------------+-------------+
| Panduan Menanam Sayuran | 2026-06-01     | 2026-06-15  |
+-------------------------+----------------+-------------+
+-------------------------+----------------+-------------+
| buku                    | tanggal_pinjam | jatuh_tempo |
+-------------------------+----------------+-------------+
| Cerita Rakyat Nusantara | 2026-06-03     | 2026-06-17  |
+-------------------------+----------------+-------------+

Setiap CALL menjalankan SELECT yang ada di dalam tubuh prosedur, sehingga hasilnya dikirim langsung kepada pemanggil. Anggota bernomor satu dan dua menghasilkan daftar berbeda karena saringan WHERE mengikuti nilai parameter p_anggota_id pada setiap pemanggilan.

Prosedur kedua memakai parameter OUT untuk mengirim dua nilai sekaligus, yaitu jumlah pinjaman dan jumlah pinjaman yang terlambat. Kepala prosedur menyatakan kedua parameter keluaran setelah parameter masukan.

bash 
# prosedur dengan parameter OUT untuk statistik satu anggota
mysql -t -u root perpustakaan <<'SQL'
DELIMITER //

DROP PROCEDURE IF EXISTS statistik_anggota //

CREATE PROCEDURE statistik_anggota (IN p_anggota_id INT, OUT o_total INT, OUT o_telat INT)
BEGIN
  SELECT COUNT(*),
         COALESCE(SUM(tanggal_kembali > jatuh_tempo), 0)
  INTO o_total, o_telat
  FROM peminjaman
  WHERE anggota_id = p_anggota_id;
END //

DELIMITER ;

CALL statistik_anggota(2, @total, @telat);
SELECT @total AS total_pinjaman, @telat AS jumlah_telat;
SQL
text Keluaran 
+----------------+--------------+
| total_pinjaman | jumlah_telat |
+----------------+--------------+
|              3 |            1 |
+----------------+--------------+

Klausa SELECT ... INTO mengisi kedua parameter OUT dengan hasil agregasi, dan CALL menampungnya ke variabel sesi @total serta @telat. Variabel sesi berawalan @ hidup sampai koneksi ditutup, sehingga nilai hasilnya masih dapat dibaca dengan SELECT setelah prosedur selesai. Konvensi awalan p_ dan o_ pada contoh ini membedakan parameter masukan dari parameter keluaran, bukan bagian dari aturan bahasa.

Variabel dan alur kontrol#

Di dalam blok BEGIN...END, variabel lokal dideklarasikan dengan DECLARE beserta tipenya, dan aturannya tegas, yaitu DECLARE hanya boleh berada di awal blok sebelum pernyataan lain. Pengisian nilai dilakukan dengan SET atau dengan SELECT ... INTO. Alur kontrol disediakan dalam bentuk IF, CASE, LOOP, REPEAT, dan WHILE, sehingga percabangan dan perulangan dapat ditulis sepenuhnya di sisi server.

Prosedur berikut memeriksa status ketersediaan satu buku dengan menggabungkan variabel lokal dan percabangan IF.

bash 
# prosedur dengan variabel lokal dan percabangan IF
mysql -t -u root perpustakaan <<'SQL'
DELIMITER //

DROP PROCEDURE IF EXISTS cek_status_buku //

CREATE PROCEDURE cek_status_buku (IN p_buku_id INT)
BEGIN
  DECLARE v_stok INT;

  SELECT stok INTO v_stok FROM buku WHERE id = p_buku_id;

  IF v_stok IS NULL THEN
    SELECT 'buku tidak ditemukan' AS status, v_stok AS stok;
  ELSEIF v_stok > 0 THEN
    SELECT 'tersedia' AS status, v_stok AS stok;
  ELSE
    SELECT 'habis' AS status, v_stok AS stok;
  END IF;
END //

DELIMITER ;

CALL cek_status_buku(1);
CALL cek_status_buku(4);
CALL cek_status_buku(99);
SQL
text Keluaran 
+----------+------+
| status   | stok |
+----------+------+
| tersedia |    3 |
+----------+------+
+--------+------+
| status | stok |
+--------+------+
| habis  |    0 |
+--------+------+
+----------------------+------+
| status               | stok |
+----------------------+------+
| buku tidak ditemukan | NULL |
+----------------------+------+

Tiga pemanggilan menghasilkan tiga cabang yang berbeda. Buku bernomor satu memiliki stok tiga sehingga cabang ELSEIF yang terpenuhi, buku bernomor empat berstok nol dan jatuh ke cabang ELSE, sedangkan buku bernomor 99 tidak ada. Untuk buku yang tidak ada, SELECT ... INTO tidak mengisi apa pun, sehingga v_stok tetap bernilai NULL dan cabang IF v_stok IS NULL yang terpicu.

Variabel lokal hanya dikenali di dalam blok tempatnya dideklarasikan. Apabila namanya sama dengan nama kolom pada tabel yang dibaca, MySQL menafsirkan acuan tersebut sebagai variabel, sehingga konvensi awalan v_ pada contoh ini memisahkan variabel dari kolom, pelengkap konvensi p_ dan o_ pada parameter. Selain IF, perulangan WHILE dan LOOP juga tersedia untuk pengolahan berulang, sehingga batch kecil dapat dieksekusi sepenuhnya di sisi server tanpa mengirim pernyataan satu per satu dari klien.

Fungsi tersimpan#

CREATE FUNCTION menyimpan fungsi yang mengembalikan satu nilai dengan tipe yang dinyatakan pada klausa RETURNS, dan tubuhnya mengembalikan hasil lewat RETURN. Berbeda dari prosedur, fungsi tersimpan dipanggil dari dalam ekspresi, misalnya pada daftar kolom SELECT, WHERE, atau ORDER BY. Perbedaan peran ini membuat fungsi cocok untuk perhitungan, sedangkan prosedur cocok untuk rangkaian pernyataan yang mengirim banyak hasil.

Server dengan binary logging aktif menuntut deklarasi karakteristik pada fungsi tersimpan, seperti DETERMINISTIC atau READS SQL DATA, agar pencatatan biner tetap benar. Pembuatan fungsi tanpa karakteristik apa pun ditolak dengan error 1418.

bash 
# fungsi tanpa karakteristik ditolak saat binary logging aktif
mysql -u root perpustakaan <<'SQL'
DELIMITER //

DROP FUNCTION IF EXISTS denda //

CREATE FUNCTION denda (p_peminjaman_id INT) RETURNS DECIMAL(10,2)
BEGIN
  DECLARE v_telat INT;
  SELECT DATEDIFF(tanggal_kembali, jatuh_tempo) INTO v_telat FROM peminjaman WHERE id = p_peminjaman_id;
  RETURN GREATEST(v_telat, 0) * 1000;
END //
SQL
text Keluaran 
ERROR 1418 (HY000) at line 5: This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)

Fungsi denda membaca tabel peminjaman, sehingga karakteristik yang sesuai adalah READS SQL DATA, ditambah DETERMINISTIC karena hasilnya hanya ditentukan oleh isinya. Fungsi menghitung selisih hari keterlambatan dengan DATEDIFF, mengalikannya dengan 1000, dan IFNULL mengubah pinjaman yang belum dikembalikan menjadi tanpa denda.

bash 
# fungsi dengan karakteristik READS SQL DATA dan DETERMINISTIC
mysql -t -u root perpustakaan <<'SQL'
DELIMITER //

DROP FUNCTION IF EXISTS denda //

CREATE FUNCTION denda (p_peminjaman_id INT) RETURNS DECIMAL(10,2)
READS SQL DATA
DETERMINISTIC
BEGIN
  DECLARE v_telat INT;

  SELECT IFNULL(DATEDIFF(tanggal_kembali, jatuh_tempo), 0) INTO v_telat
  FROM peminjaman WHERE id = p_peminjaman_id;

  RETURN GREATEST(v_telat, 0) * 1000;
END //

DELIMITER ;

SELECT denda(1) AS denda_pinjaman_1, denda(2) AS denda_pinjaman_2, denda(3) AS denda_pinjaman_3;
SQL
text Keluaran 
+------------------+------------------+------------------+
| denda_pinjaman_1 | denda_pinjaman_2 | denda_pinjaman_3 |
+------------------+------------------+------------------+
|          4000.00 |             0.00 |             0.00 |
+------------------+------------------+------------------+

Pinjaman bernomor satu dikembalikan empat hari setelah jatuh tempo sehingga dendanya 4000, pinjaman bernomor dua dikembalikan lebih awal, dan pinjaman bernomor tiga belum dikembalikan. Karena fungsi dapat dipanggil dari dalam ekspresi, perhitungan yang sama langsung dipakai untuk laporan agregat tanpa menyalin rumusnya.

bash 
# fungsi tersimpan dipanggil dari dalam ekspresi agregat
mysql -t -u root perpustakaan <<'SQL'
SELECT a.nama, COUNT(*) AS jumlah_pinjaman, SUM(denda(p.id)) AS total_denda
FROM peminjaman AS p
JOIN anggota AS a ON a.id = p.anggota_id
GROUP BY a.nama
ORDER BY total_denda DESC, a.nama ASC;
SQL
text Keluaran 
+-------+-----------------+-------------+
| nama  | jumlah_pinjaman | total_denda |
+-------+-----------------+-------------+
| Budi  |               3 |     8000.00 |
| Dewi  |               2 |     6000.00 |
| Ali   |               3 |     4000.00 |
| Citra |               2 |        0.00 |
| Eka   |               1 |        0.00 |
+-------+-----------------+-------------+

Batas antara prosedur dan fungsi ditegakkan server. Prosedur tidak dapat dibaca dalam ekspresi, sedangkan fungsi tidak dapat dipanggil dengan CALL. Pencampuran keduanya menghasilkan error 1305, dan pesannya menyebut objek sesuai peran yang diminta pemanggil, bukan peran yang sebenarnya.

bash 
# prosedur dibaca sebagai ekspresi dan fungsi dipanggil dengan CALL
mysql -u root perpustakaan -e "SELECT jumlah_anggota();"
mysql -u root perpustakaan -e "CALL denda(1);"
text Keluaran 
ERROR 1305 (42000) at line 1: FUNCTION perpustakaan.jumlah_anggota does not exist
ERROR 1305 (42000) at line 1: PROCEDURE perpustakaan.denda does not exist

Kesalahan umum#

Kesalahan pertama adalah menjalankan CREATE PROCEDURE untuk nama yang sudah ada, misalnya saat skrip dijalankan ulang. Server menolaknya dengan error 1304 karena nama prosedur harus unik di dalam skemanya.

bash 
# CREATE PROCEDURE untuk nama yang sudah ada ditolak server
mysql -u root perpustakaan <<'SQL'
DELIMITER //

CREATE PROCEDURE jumlah_anggota ()
BEGIN
  SELECT COUNT(*) AS jumlah_anggota FROM anggota;
END //

DELIMITER ;
SQL
text Keluaran 
ERROR 1304 (42000) at line 3: PROCEDURE jumlah_anggota already exists

Perbaikannya sudah terlihat pada setiap contoh di topik ini, yaitu DROP PROCEDURE IF EXISTS sebelum pembuatannya, begitu pula DROP FUNCTION IF EXISTS untuk fungsi. Kesalahan kedua adalah lupa DELIMITER sehingga klien mengirim tubuh prosedur terpotong dan error 1064 muncul, seperti telah didemonstrasikan pada bagian pertama. Kesalahan ketiga adalah menempatkan DECLARE di tengah blok, padahal deklarasi hanya sah di awal BEGIN...END. Kesalahan keempat adalah menukar RETURNS dengan RETURN, karena RETURNS menyatakan tipe nilai balik pada kepala fungsi, sedangkan RETURN mengembalikan nilainya di dalam tubuh, dan menukar keduanya berujung error sintaks.

Kesalahan kelima berkenaan dengan SELECT ... INTO. Apabila query yang mengisi variabel mengembalikan lebih dari satu baris, server menghentikan prosedur dengan error 1172, sehingga saringan WHERE pada query pengisian wajib dipastikan menghasilkan satu baris saja. Apabila querynya tidak mengembalikan baris apa pun, variabel dibiarkan tanpa isi seperti kasus buku bernomor 99, dan percabangan setelahnya harus siap menghadapi NULL.

Ringkasan#

  • CREATE PROCEDURE menyimpan blok BEGIN...END di server dan menjalankannya dengan CALL, sedangkan CREATE FUNCTION menyimpan fungsi yang mengembalikan satu nilai dan dapat dipanggil dari dalam ekspresi query.
  • DELIMITER adalah perintah klien mysql yang mengganti pemisah pernyataan sementara, sehingga ; di dalam tubuh blok terkirim utuh ke server.
  • Parameter IN menerima masukan, parameter OUT mengeluarkan hasil melalui variabel sesi berawalan @, dan variabel lokal dideklarasikan dengan DECLARE di awal blok.
  • Server dengan binary logging aktif menuntut karakteristik seperti DETERMINISTIC atau READS SQL DATA pada fungsi tersimpan, dan pola DROP ... IF EXISTS membuat skrip dapat dijalankan ulang.

Referensi#