Cara Menggunakan Stored Procedure di MySQL

Saat aplikasi Anda memiliki logika SQL yang kompleks dan digunakan berulang kali, Stored Procedure adalah solusinya. Stored Procedure adalah sekumpulan pernyataan SQL yang disimpan di dalam database dan dapat dipanggil kapan saja dengan satu perintah. Artikel ini membahas cara membuat dan menggunakan Stored Procedure di MySQL 8 secara lengkap.

Keuntungan Menggunakan Stored Procedure

• Reusability: Tulis sekali, panggil berkali-kali dari mana saja.
• Performa: Query dikompilasi dan disimpan di server, eksekusi lebih cepat.
• Keamanan: Pengguna bisa dipanggil procedure tanpa akses langsung ke tabel.
• Pemisahan logika: Logika bisnis bisa disimpan di database, bukan hanya di kode aplikasi.

Persiapan: Mengubah DELIMITER

Karena body stored procedure berisi tanda titik koma ;, kita perlu mengubah delimiter sementara agar MySQL tidak salah mengakhiri perintah:

-- Ubah delimiter sebelum membuat procedure
DELIMITER $$

-- Setelah selesai, kembalikan delimiter ke ;
DELIMITER ;

Membuat Stored Procedure Sederhana

Berikut contoh paling dasar: procedure tanpa parameter yang menampilkan semua produk.

DELIMITER $$

CREATE PROCEDURE tampilkan_semua_produk()
BEGIN
    SELECT id, nama, kategori, harga, stok
    FROM produk
    ORDER BY nama;
END$$

DELIMITER ;

-- Cara memanggil procedure
CALL tampilkan_semua_produk();

Stored Procedure dengan Parameter IN

Parameter IN digunakan untuk mengirim nilai ke dalam procedure (read-only di dalam procedure):

DELIMITER $$

CREATE PROCEDURE cari_produk_by_kategori(IN p_kategori VARCHAR(50))
BEGIN
    SELECT id, nama, harga, stok
    FROM produk
    WHERE kategori = p_kategori
    ORDER BY harga ASC;
END$$

DELIMITER ;

-- Panggil dengan argumen
CALL cari_produk_by_kategori('Elektronik');
CALL cari_produk_by_kategori('Furnitur');

Stored Procedure dengan Parameter OUT

Parameter OUT digunakan untuk mengembalikan nilai dari procedure ke pemanggil:

DELIMITER $$

CREATE PROCEDURE hitung_stok_total(
    IN p_kategori VARCHAR(50),
    OUT p_total_stok INT,
    OUT p_jumlah_produk INT
)
BEGIN
    SELECT
        COALESCE(SUM(stok), 0),
        COUNT(*)
    INTO p_total_stok, p_jumlah_produk
    FROM produk
    WHERE kategori = p_kategori;
END$$

DELIMITER ;

-- Panggil dan ambil hasilnya
CALL hitung_stok_total('Elektronik', @total_stok, @jumlah_produk);
SELECT @total_stok AS total_stok, @jumlah_produk AS jumlah_produk;

Stored Procedure dengan Parameter INOUT

Parameter INOUT bisa digunakan untuk mengirim nilai ke procedure sekaligus menerima nilai balik:

DELIMITER $$

CREATE PROCEDURE hitung_diskon(INOUT p_harga DECIMAL(10,2), IN p_persen INT)
BEGIN
    SET p_harga = p_harga - (p_harga * p_persen / 100);
END$$

DELIMITER ;

-- Gunakan INOUT parameter
SET @harga_asal = 1000000;
CALL hitung_diskon(@harga_asal, 15);
SELECT @harga_asal AS harga_setelah_diskon;

Stored Procedure dengan Logika IF-ELSE

Stored Procedure mendukung struktur kontrol seperti IF, ELSE, dan CASE:

DELIMITER $$

CREATE PROCEDURE kategorikan_produk(IN p_harga DECIMAL(10,2), OUT p_kategori_harga VARCHAR(20))
BEGIN
    IF p_harga < 100000 THEN
        SET p_kategori_harga = 'Murah';
    ELSEIF p_harga < 1000000 THEN
        SET p_kategori_harga = 'Menengah';
    ELSE
        SET p_kategori_harga = 'Premium';
    END IF;
END$$

DELIMITER ;

CALL kategorikan_produk(750000, @kat);
SELECT @kat AS kategori_harga;

Stored Procedure dengan Loop dan Transaksi

DELIMITER $$

CREATE PROCEDURE update_harga_massal(IN p_kategori VARCHAR(50), IN p_persen_naik INT)
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE v_id INT;
    DECLARE v_harga DECIMAL(10,2);

    DECLARE cur CURSOR FOR
        SELECT id, harga FROM produk WHERE kategori = p_kategori;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    START TRANSACTION;

    OPEN cur;
    loop_label: LOOP
        FETCH cur INTO v_id, v_harga;
        IF done THEN
            LEAVE loop_label;
        END IF;
        UPDATE produk
        SET harga = v_harga + (v_harga * p_persen_naik / 100)
        WHERE id = v_id;
    END LOOP;
    CLOSE cur;

    COMMIT;
    SELECT ROW_COUNT() AS baris_diperbarui;
END$$

DELIMITER ;

Melihat dan Menghapus Stored Procedure

-- Lihat semua stored procedure di database aktif
SHOW PROCEDURE STATUS WHERE Db = DATABASE();

-- Lihat kode sumber procedure
SHOW CREATE PROCEDURE cari_produk_by_kategori;

-- Hapus stored procedure
DROP PROCEDURE IF EXISTS cari_produk_by_kategori;

Kesimpulan

Stored Procedure adalah fitur MySQL yang powerful untuk menyimpan dan menjalankan logika SQL yang kompleks langsung di level database. Dengan parameter IN, OUT, dan INOUT, Anda bisa membuat procedure yang fleksibel dan reusable. Gunakan stored procedure untuk operasi yang sering diulang, logika bisnis yang kompleks, atau ketika keamanan akses data menjadi prioritas. Pastikan selalu mengubah DELIMITER sebelum membuat procedure agar MySQL bisa memproses kode dengan benar.