Stored Procedures & Functions — Database

Stored Procedures vs Functions Stored procedures dan functions adalah kode SQL yang disimpan di database dan bisa dipanggil berulang kali. Function…

Stored procedure dan function adalah kode SQL yang disimpan di dalam server database, diberi nama, lalu dipanggil berulang kali oleh aplikasi mana pun. Bayangin resep standar yang ditempel di dinding dapur. Kalau tiap pelayan menghafal resepnya sendiri-sendiri, suatu hari ada yang hafalannya ketinggalan. Kalau resepnya ditempel di dapur, semua pelayan memasak dari kertas yang sama. Database adalah dapurnya, dan aplikasimu (web, kasir) adalah pelayannya.

🧪 Tebakan cepat: Kedai Senja punya dua aplikasi: web (JavaScript) dan kasir. Aturan baru "diskon 2% untuk total Rp200.000 ke atas" cuma ditambahkan ke kode web. Siti memesan katering Rp200.000. Diskon yang muncul di web dan di kasir sama atau beda? Tebak dulu, jawabannya ada di bagian "Satu aturan, dua aplikasi".

Aturan diskon katering Kedai Senja

Kedai Senja mulai menerima pesanan katering kantor. Pesanannya disimpan di tabel catering_orders, dan diskonnya bertingkat menurut total:

Total Diskon
Rp1.000.000 ke atas 10%
Rp500.000 ke atas 5%
Rp200.000 ke atas 2%
di bawah Rp200.000 0

Lima pesanan di Lab halaman ini, dihitung satu per satu:

id customer total tingkat diskon
1 Budi 100.000 di bawah 200.000 0
2 Siti 200.000 pas 200.000, ikut 2% 4.000
3 Andi 500.000 pas 500.000, ikut 5% 25.000
4 PT Maju 1.000.000 pas 1.000.000, ikut 10% 100.000
5 PT Sentosa 1.500.000 di atas 1.000.000, 10% 150.000

Perhatikan tiga baris yang pas di batas. "Ke atas" artinya batasnya ikut dihitung, jadi tandanya >=, bukan >.

Function: logika yang mengembalikan nilai

Function menerima input, menghitung, lalu mengembalikan satu nilai dengan RETURN. Karena hasilnya nilai, function bisa dipakai di dalam SELECT, seperti COUNT() atau UPPER(). Ini versi PostgreSQL (bahasanya PL/pgSQL):

CREATE FUNCTION hitung_diskon(total NUMERIC)
RETURNS NUMERIC AS $
BEGIN
  IF total >= 1000000 THEN
    RETURN total * 0.10;   -- 10%
  ELSIF total >= 500000 THEN
    RETURN total * 0.05;   -- 5%
  ELSIF total >= 200000 THEN
    RETURN total * 0.02;   -- 2%
  ELSE
    RETURN 0;
  END IF;
END;
$ LANGUAGE plpgsql;

SELECT id, customer, total, hitung_diskon(total) AS diskon
FROM catering_orders;

SQLite gak punya CREATE FUNCTION (function tambahan di SQLite ditulis di bahasa aplikasinya, bukan di SQL). Yang paling dekat dan bisa disimpan di database adalah view dengan CASE. Lab di halaman ini menjalankan versi ini di SQLite sungguhan:

CREATE VIEW pesanan_diskon AS
SELECT id, customer, total,
  CASE
    WHEN total >= 1000000 THEN total * 0.10
    WHEN total >= 500000 THEN total * 0.05
    WHEN total >= 200000 THEN total * 0.02
    ELSE 0
  END AS diskon
FROM catering_orders;

Procedure: rangkaian perintah yang dipanggil dengan CALL

Stored procedure adalah kumpulan perintah SQL yang disimpan dan dijalankan di server database. Dia gak mengembalikan nilai lewat RETURN. Tugasnya mengerjakan sesuatu: mengubah beberapa tabel sekaligus. Contohnya pesanan katering yang harus mengurangi stok kopi dulu:

CREATE PROCEDURE pesan_katering(p_customer TEXT, p_cups INTEGER)
LANGUAGE plpgsql AS $
BEGIN
  UPDATE stock SET cups_left = cups_left - p_cups WHERE item = 'kopi';
  IF (SELECT cups_left FROM stock WHERE item = 'kopi') < 0 THEN
    RAISE EXCEPTION 'Stok kopi tidak cukup';
  END IF;
  INSERT INTO catering_orders (customer, total) VALUES (p_customer, p_cups * 18000);
END;
$;

CALL pesan_katering('PT Maju', 30);

Jadi bedanya: function mengembalikan nilai dan bisa dipakai dalam SELECT, procedure tidak. Procedure dipanggil dengan CALL, dan (di PostgreSQL 11 ke atas dan MySQL) boleh berisi COMMIT atau ROLLBACK. MySQL juga punya CREATE FUNCTION dan CREATE PROCEDURE, dengan sintaks yang sedikit beda.

Dua hal yang sering tertukar dengannya: trigger adalah aturan yang dijalankan otomatis setiap kali sebuah baris berubah (bukan dipanggil), dan cache adalah hasil query yang disimpan di memori (pelajaran Redis berikutnya).

Satu aturan, dua aplikasi

Kasir Kedai Senja masih memakai kode lama, sebelum ada aturan 2%:

// aplikasi kasir: aturan lama, belum kenal diskon 2%
function hitungDiskon(total) {
  if (total >= 1000000) return total * 0.10;
  if (total >= 500000) return total * 0.05;
  return 0;
}

Web sudah ditambahi baris if (total >= 200000) return total * 0.02;. Pesanan Siti Rp200.000: web menghitung 4.000, kasir menghitung 0. Beda, dan gak ada error di mana pun. Itu jawaban tebakannya. Kalau aturannya tinggal di database (function, atau view di SQLite), kedua aplikasi memanggil hal yang sama dan sama-sama dapat 4.000. Inilah kekuatan utama logika di database: satu sumber untuk banyak aplikasi.

Logika di database atau di aplikasi?

Di database (procedure, function) Di aplikasi
Dipakai banyak aplikasi otomatis sama harus disalin dan dijaga sama
Round trip satu CALL beberapa query
Version control, debug, test sulit mudah: file di git, breakpoint, unit test
Pindah database ditulis ulang (vendor lock-in) tetap jalan
Beban CPU server database server aplikasi, gampang ditambah

Alasan terbesar banyak developer modern menghindari stored procedure: sulit di-version control, di-debug, dan di-test dibanding kode aplikasi. Kodenya tinggal di dalam database, bukan di file yang di-review lewat pull request. Mengubahnya berlaku seketika untuk semua aplikasi, termasuk kalau isinya salah. Makanya aturan bisnis yang sering berubah (promo, diskon) biasanya ditaruh di aplikasi, sedangkan database dipakai untuk penjaga data (constraint, trigger kecil) dan hitungan berat atas jutaan baris.

⚠️ Jebakan yang sering ketemu

TL;DR: Function dan stored procedure adalah kode SQL yang disimpan dan dijalankan di server database. Function mengembalikan nilai lewat RETURN dan bisa dipakai dalam SELECT; procedure dipanggil dengan CALL, mengerjakan beberapa perintah sekaligus, dan gak mengembalikan nilai. SQLite gak punya keduanya, jadi Lab-nya menyimpan aturan diskon sebagai view dengan CASE. Logika di database memberi satu sumber untuk banyak aplikasi dan lebih sedikit round trip, tapi sulit di-version control, di-debug dan di-test, dan mengikat kamu ke satu jenis database. Untuk diskon bertingkat: cek tingkat terbesar dulu dan pakai >=: 1.500.000 dapat 150.000, 500.000 dapat 25.000, 200.000 dapat 4.000, 100.000 dapat 0.

Yang akan kamu pelajari