Store Procedure
Definisi: Stored Procedure adalah sekumpulan perintah SQL yang disimpan di dalam database dan dapat dipanggil kembali kapan saja dibutuhkan.
- Disimpan permanen di dalam server database, bukan di aplikasi.
- Cukup dibuat sekali, bisa dipanggil berkali-kali dari mana saja.
- Dieksekusi langsung di server, mengurangi beban jaringan.
Mengapa Harus Pakai Stored Procedure?
keamanan : User tidak perlu akses langsung ke tabel. Hanya bisa akses melalui procedure yang sudah ditentukan.
Performa Lebih Cepat : SQL dicompile sekali dan disimpan. Eksekusi berikutnya jauh lebih cepat dibanding mengirim ulang query.
Mengurangi Traffic Jaringan : Hanya nama procedure dan parameter yang dikirim, bukan seluruh query panjang.
Skenario: Kasir ingin melihat semua varian & harga dari satu jenis alat musik (misal: Gitar).
DELIMITER $$
CREATE PROCEDURE
cari_varian_alat(
IN p_nama_alat VARCHAR(100))
BEGIN
SELECT
am.nama_alat,
v.nama_varian,
v.harga,
v.stok
FROM tb_varian_alat_musik v
JOIN tb_alat_musik am
ON v.id_alat_musik = am.id
WHERE am.nama_alat
LIKE CONCAT('%', p_nama_alat, '%');
END$$
DELIMITER ;
CALL cari_varian_alat('Gitar');
-------------
Skenario: Manajer ingin tahu berapa total transaksi yang sudah ditangani oleh seorang pegawai berdasarkan ID-nya
DELIMITER $$
CREATE PROCEDURE
total_transaksi_pegawai(
IN p_id_pegawai INT,
OUT p_total INT)
BEGIN
SELECT COUNT(*)
INTO p_total
FROM tb_header_transaksi
WHERE id_pegawai = p_id_pegawai;
END$$
DELIMITER ;
-- Memanggil:
CALL total_transaksi_pegawai(2, @hasil);
SELECT @hasil AS total_transaksi;
------
Skenario: Hitung total pendapatan (harga × qty) dari semua transaksi di sebuah cabang berdasarkan ID cabang.
DELIMITER $$
CREATE PROCEDURE
pendapatan_cabang(
IN p_id_cabang INT,
OUT p_pendapatan DECIMAL(15,2))
BEGIN
SELECT SUM(v.harga * dt.qty)
INTO p_pendapatan
FROM tb_detail_transaksi dt
JOIN tb_header_transaksi ht
ON dt.id_transaksi = ht.id
JOIN tb_pegawai pg
ON ht.id_pegawai = pg.id
JOIN tb_varian_alat_musik v
ON dt.id_varian_alat_musik = v.id
WHERE pg.id_cabang = p_id_cabang;
END$$
DELIMITER ;
CALL pendapatan_cabang(1, @hasil); SELECT @hasil;
----------
Latihan
Buat SP cari_pegawai_cabang dengan parameter IN id_cabang. Tampilkan nama_pegawai dan gaji semua pegawai di cabang tersebut.
DELIMITER $$
CREATE PROCEDURE cari_pegawai_cabang(IN p_id_cabang INT)
BEGIN
SELECT
nama_pegawai,
gaji
FROM tb_pegawai
WHERE id_cabang = p_id_cabang;
END$$
DELIMITER ;
-- Cara memanggil:
CALL cari_pegawai_cabang(1);
-------
Buat SP stok_varian dengan parameter IN id_varian dan OUT p_stok. Kembalikan jumlah stok dari varian alat musik tersebut.
DELIMITER $$
CREATE PROCEDURE stok_varian(
IN p_id_varian INT,
OUT p_stok INT
)
BEGIN
SELECT stok
INTO p_stok
FROM tb_varian_alat_musik
WHERE id = p_id_varian;
END$$
DELIMITER ;
-- Cara memanggil:
CALL stok_varian(2, @hasil_stok);
SELECT @hasil_stok AS stok_tersedia;
-------
