SQL Stored Procedures & Functions: Panduan Lengkap

📋 Daftar Isi

Pengenalan Stored Procedures & Functions
Stored Procedures — Dasar
Parameter Input, Output, dan INOUT
User-Defined Functions (UDF)
Triggers — Otomatisasi Event
Cursors — Iterasi Baris per Baris
Error Handling & Exception
Transaksi dalam Stored Procedures
Best Practices & Performance
Quiz Pemahaman

1. Pengenalan Stored Procedures & Functions

Stored Procedures dan Functions adalah blok kode SQL yang disimpan di dalam database dan dapat dipanggil berulang kali. Mereka memungkinkan Anda menyimpan logika bisnis langsung di database, bukan hanya di aplikasi.

Mengapa Menggunakan Stored Procedures?

Keuntungan	Penjelasan
Reusabilitas	Tulis sekali, panggil berkali-kali dari berbagai aplikasi
Performance	Database melakukan compile dan caching execution plan
Keamanan	Pengguna bisa menjalankan prosedur tanpa akses langsung ke tabel
Maintainability	Logika bisnis terpusat di database, mudah dikelola
Reduced Network Traffic	Hanya nama prosedur yang dikirim, bukan puluhan query
Konsistensi	Logika yang sama digunakan oleh semua aplikasi yang terhubung

Stored Procedure vs Function

Aspek	Stored Procedure	Function
Return Value	Bisa (via OUT parameter), tidak wajib	Wajib mengembalikan satu nilai
Dipanggil dari	CALL nama_prosedur()	SELECT nama_fungsi()
Digunakan dalam SELECT	Tidak bisa	Bisa
Side Effects	Boleh mengubah data (INSERT/UPDATE/DELETE)	Umumnya hanya membaca
Parameter	IN, OUT, INOUT	Hanya IN (default)
Transaction Control	Bisa COMMIT/ROLLBACK	Tidak bisa (di MySQL)

Diagram: Perbandingan Stored Procedure vs Function

+-------------------------------------------------------------------------+
|                    STORED PROCEDURE                                      |
|                                                                         |
|  Aplikasi --CALL prosedur()-->  +------------------+                    |
|                                 | Stored Procedure   |                    |
|                                 | +----------------+ |                    |
|                                 | | Logika Bisnis  | |                    |
|                                 | | INSERT/UPDATE  | |                    |
|                                 | | Validasi       | |                    |
|                                 | +----------------+ |                    |
|                                 | Bisa: OUT param    |                    |
|                                 +------------------+                    |
|                                          |                              |
|                                     Hasil/Status                        |
+-------------------------------------------------------------------------+

+-------------------------------------------------------------------------+
|                    FUNCTION                                               |
|                                                                         |
|  SELECT nama_fungsi(kolom) -->  +------------------+                    |
|     FROM tabel                   |    Function        |                    |
|                                  | +----------------+ |                    |
|                                  | | Kalkulasi      | |                    |
|                                  | | Return value   | |                    |
|                                  | +----------------+ |                    |
|                                  | Harus: RETURN      |                    |
|                                  +------------------+                    |
|                                          |                              |
|                                    Satu Nilai                           |
+-------------------------------------------------------------------------+

2. Stored Procedures — Dasar

Stored Procedure adalah kumpulan perintah SQL yang disimpan di database server dan dapat dijalankan dengan satu perintah CALL. Mari kita mulai dengan membuat prosedur sederhana.

Membuat Stored Procedure Pertama

MySQL — Stored Procedure Dasar

-- Ubah delimiter agar ; di dalam body tidak
-- mengakhiri perintah CREATE PROCEDURE
DELIMITER //

-- PROCEDURE: Ambil semua pelanggan aktif
CREATE PROCEDURE ambil_pelanggan_aktif()
BEGIN
    SELECT id_pelanggan, nama, email, kota, tanggal_daftar
    FROM pelanggan
    WHERE is_active = TRUE
    ORDER BY nama;
END //

DELIMITER ;

-- Memanggil Procedure
CALL ambil_pelanggan_aktif();


-- PROCEDURE: Laporan penjualan per kategori
DELIMITER //

CREATE PROCEDURE laporan_penjualan_kategori()
BEGIN
    SELECT
        p.kategori,
        COUNT(DISTINCT dp.id_pesanan) AS jumlah_pesanan,
        SUM(dp.subtotal)              AS total_penjualan,
        AVG(dp.subtotal)              AS rata_rata_per_item,
        SUM(dp.jumlah)                AS total_unit_terjual
    FROM detail_pesanan dp
    JOIN produk p ON dp.id_produk = p.id_produk
    JOIN pesanan ps ON dp.id_pesanan = ps.id_pesanan
    WHERE ps.status <> 'batal'
    GROUP BY p.kategori
    ORDER BY total_penjualan DESC;
END //

DELIMITER ;

CALL laporan_penjualan_kategori();

Menampilkan dan Menghapus Procedure

SQL — Manajemen Procedure

-- Menampilkan semua stored procedures
SHOW PROCEDURE STATUS WHERE Db = 'toko_online';

-- Menampilkan definisi procedure
SHOW CREATE PROCEDURE ambil_pelanggan_aktif;

-- Menghapus procedure
DROP PROCEDURE IF EXISTS ambil_pelanggan_aktif;

-- Mengubah procedure: DROP lalu CREATE
DROP PROCEDURE IF EXISTS ambil_pelanggan_aktif;

DELIMITER //
CREATE PROCEDURE ambil_pelanggan_aktif()
BEGIN
    SELECT id_pelanggan, nama, email, kota
    FROM pelanggan
    WHERE is_active = TRUE
    ORDER BY tanggal_daftar DESC;
END //
DELIMITER ;

PostgreSQL: CREATE FUNCTION sebagai Procedure

PostgreSQL — Procedure

-- PostgreSQL 11+ mendukung CREATE PROCEDURE
CREATE OR REPLACE PROCEDURE ambil_pelanggan_aktif()
LANGUAGE plpgsql
AS $$
BEGIN
    PERFORM * FROM pelanggan WHERE is_active = TRUE;
END;
$$;

CALL ambil_pelanggan_aktif();

-- FUNCTION untuk return table di PostgreSQL
CREATE OR REPLACE FUNCTION ambil_pelanggan_aktif()
RETURNS TABLE (
    id_pelanggan INT,
    nama         VARCHAR,
    email        VARCHAR,
    kota         VARCHAR
)
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY
    SELECT p.id_pelanggan, p.nama, p.email, p.kota
    FROM pelanggan p
    WHERE p.is_active = TRUE
    ORDER BY p.nama;
END;
$$;

SELECT * FROM ambil_pelanggan_aktif();

3. Parameter Input, Output, dan INOUT

Parameter memungkinkan stored procedure menerima input dan mengembalikan output. Ada tiga jenis: IN (default), OUT, dan INOUT.

Jenis Parameter	Fungsi	Default
IN	Menerima nilai dari pemanggil (read-only di dalam prosedur)	Ya
OUT	Mengembalikan nilai ke pemanggil	Tidak
INOUT	Menerima nilai awal dan mengembalikan nilai baru	Tidak

Parameter IN

MySQL — Parameter IN

-- PROCEDURE: Cari produk berdasarkan kategori dan harga
DELIMITER //

CREATE PROCEDURE cari_produk(
    IN p_kategori   VARCHAR(50),
    IN p_harga_max  DECIMAL(12, 2)
)
BEGIN
    SELECT nama_produk, harga, stok, kategori
    FROM produk
    WHERE kategori = p_kategori
      AND harga <= p_harga_max
      AND stok > 0
    ORDER BY harga ASC;
END //

DELIMITER ;

CALL cari_produk('Elektronik', 5000000);
CALL cari_produk('Fashion', 500000);


-- PROCEDURE: Pencarian pelanggan fleksibel
DELIMITER //

CREATE PROCEDURE cari_pelanggan(
    IN p_nama    VARCHAR(100),
    IN p_kota    VARCHAR(50),
    IN p_active  BOOLEAN
)
BEGIN
    SELECT id_pelanggan, nama, email, kota, tanggal_daftar
    FROM pelanggan
    WHERE (p_nama IS NULL OR nama LIKE CONCAT('%', p_nama, '%'))
      AND (p_kota IS NULL OR kota = p_kota)
      AND (p_active IS NULL OR is_active = p_active)
    ORDER BY nama;
END //

DELIMITER ;

CALL cari_pelanggan('Budi', NULL, TRUE);
CALL cari_pelanggan(NULL, 'Bandung', NULL);
CALL cari_pelanggan(NULL, NULL, FALSE);

Parameter OUT dan INOUT

MySQL — Parameter OUT & INOUT

-- PROCEDURE: Hitung statistik penjualan dengan OUT
DELIMITER //

CREATE PROCEDURE statistik_penjualan(
    IN  p_tahun         INT,
    OUT p_total_pesanan INT,
    OUT p_total_revenue DECIMAL(15, 2),
    OUT p_avg_order     DECIMAL(12, 2)
)
BEGIN
    SELECT COUNT(*), COALESCE(SUM(total), 0), COALESCE(AVG(total), 0)
    INTO p_total_pesanan, p_total_revenue, p_avg_order
    FROM pesanan
    WHERE YEAR(tanggal_pesanan) = p_tahun
      AND status <> 'batal';
END //

DELIMITER ;

CALL statistik_penjualan(2026, @jml, @rev, @avg);
SELECT @jml AS total_pesanan, @rev AS total_revenue, @avg AS rata_rata;


-- PROCEDURE dengan INOUT: Update counter
DELIMITER //

CREATE PROCEDURE update_counter(
    INOUT p_counter  INT,
    IN    p_tambahan INT
)
BEGIN
    SET p_counter = p_counter + p_tambahan;
END //

DELIMITER ;

SET @counter = 100;
CALL update_counter(@counter, 25);
SELECT @counter;  -- Hasil: 125

CALL update_counter(@counter, 50);
SELECT @counter;  -- Hasil: 175

Default Parameter Values (PostgreSQL)

PostgreSQL — Default Parameter

-- PostgreSQL mendukung default values untuk parameter
CREATE OR REPLACE FUNCTION cari_produk(
    p_kategori  VARCHAR DEFAULT NULL,
    p_harga_max DECIMAL DEFAULT NULL,
    p_limit     INT DEFAULT 50
)
RETURNS TABLE (nama_produk VARCHAR, harga DECIMAL, stok INT)
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY
    SELECT p.nama_produk, p.harga, p.stok
    FROM produk p
    WHERE (p_kategori IS NULL OR p.kategori = p_kategori)
      AND (p_harga_max IS NULL OR p.harga <= p_harga_max)
    ORDER BY p.harga
    LIMIT p_limit;
END;
$$;

SELECT * FROM cari_produk();
SELECT * FROM cari_produk('Elektronik');
SELECT * FROM cari_produk(NULL, 1000000);
SELECT * FROM cari_produk('Fashion', 500000, 10);

💡 Tips Parameter

Selalu gunakan prefix yang konsisten untuk parameter (misalnya p_) untuk membedakannya dari nama kolom. Ini mencegah ambiguitas dan membuat kode lebih mudah dibaca.

4. User-Defined Functions (UDF)

Function adalah blok kode yang wajib mengembalikan satu nilai dan bisa digunakan langsung dalam query SELECT, WHERE, ORDER BY, dan clause lainnya.

Function Sederhana

MySQL — User-Defined Functions

-- FUNCTION: Hitung diskon berdasarkan level
DELIMITER //

CREATE FUNCTION hitung_diskon(
    p_harga DECIMAL(12, 2),
    p_level VARCHAR(20)
)
RETURNS DECIMAL(12, 2)
DETERMINISTIC
READS SQL DATA
BEGIN
    DECLARE v_diskon DECIMAL(5, 2);

    CASE p_level
        WHEN 'bronze'   THEN SET v_diskon = 0.05;
        WHEN 'silver'   THEN SET v_diskon = 0.10;
        WHEN 'gold'     THEN SET v_diskon = 0.15;
        WHEN 'platinum' THEN SET v_diskon = 0.20;
        ELSE SET v_diskon = 0.00;
    END CASE;

    RETURN p_harga * v_diskon;
END //

DELIMITER ;

-- Menggunakan function dalam SELECT
SELECT
    nama_produk,
    harga,
    hitung_diskon(harga, 'gold') AS diskon_gold,
    harga - hitung_diskon(harga, 'gold') AS harga_setelah_diskon
FROM produk;


-- FUNCTION: Hitung umur dari tanggal lahir
DELIMITER //

CREATE FUNCTION hitung_umur(p_tanggal_lahir DATE)
RETURNS INT
DETERMINISTIC
BEGIN
    RETURN TIMESTAMPDIFF(YEAR, p_tanggal_lahir, CURDATE());
END //

DELIMITER ;

SELECT nama, tanggal_lahir, hitung_umur(tanggal_lahir) AS umur
FROM pelanggan
WHERE hitung_umur(tanggal_lahir) > 25;

Function yang Return Tabel (PostgreSQL)

PostgreSQL — Function Return Table

-- Function mengembalikan tabel
CREATE OR REPLACE FUNCTION laporan_pelanggan(p_kota VARCHAR)
RETURNS TABLE (
    nama_lengkap     VARCHAR,
    email_pelanggan  VARCHAR,
    total_belanja    DECIMAL,
    jumlah_transaksi BIGINT,
    rata_transaksi   DECIMAL
)
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY
    SELECT
        pl.nama, pl.email,
        COALESCE(SUM(ps.total), 0)::DECIMAL,
        COUNT(ps.id_pesanan),
        COALESCE(AVG(ps.total), 0)::DECIMAL
    FROM pelanggan pl
    LEFT JOIN pesanan ps ON pl.id_pelanggan = ps.id_pelanggan
        AND ps.status <> 'batal'
    WHERE pl.kota = p_kota
    GROUP BY pl.id_pelanggan, pl.nama, pl.email
    ORDER BY COALESCE(SUM(ps.total), 0) DESC;
END;
$$;

SELECT * FROM laporan_pelanggan('Jakarta');
SELECT * FROM laporan_pelanggan('Bandung')
WHERE total_belanja > 1000000;


-- Function dengan OUT parameter
CREATE OR REPLACE FUNCTION get_produk_stats(
    OUT total_produk INT,
    OUT harga_min    DECIMAL,
    OUT harga_max    DECIMAL,
    OUT harga_rata   DECIMAL
)
LANGUAGE plpgsql
AS $$
BEGIN
    SELECT COUNT(*), MIN(harga), MAX(harga), AVG(harga)
    INTO total_produk, harga_min, harga_max, harga_rata
    FROM produk;
END;
$$;

SELECT * FROM get_produk_stats();

5. Triggers — Otomatisasi Event

Trigger adalah stored procedure yang otomatis dijalankan ketika terjadi event tertentu pada tabel — INSERT, UPDATE, atau DELETE. Trigger berguna untuk audit trail, validasi data, dan menjaga konsistensi.

Jenis Trigger

Jenis	Event	Kegunaan
BEFORE INSERT	Sebelum data baru dimasukkan	Validasi atau modifikasi data
AFTER INSERT	Setelah data baru dimasukkan	Audit log atau update tabel lain
BEFORE UPDATE	Sebelum data diperbarui	Validasi perubahan
AFTER UPDATE	Setelah data diperbarui	Cascade update atau log
BEFORE DELETE	Sebelum data dihapus	Cegah penghapusan data penting
AFTER DELETE	Setelah data dihapus	Audit trail atau cleanup

Membuat Trigger

MySQL — Triggers

-- TABEL AUDIT untuk trigger logging
CREATE TABLE audit_log (
    id_log      INT PRIMARY KEY AUTO_INCREMENT,
    tabel       VARCHAR(50) NOT NULL,
    aksi        VARCHAR(10) NOT NULL,
    id_record   INT,
    data_lama   JSON,
    data_baru   JSON,
    diubah_oleh VARCHAR(100),
    waktu       TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- TRIGGER: Log perubahan harga produk
DELIMITER //

CREATE TRIGGER trg_produk_harga_update
BEFORE UPDATE ON produk
FOR EACH ROW
BEGIN
    IF OLD.harga <> NEW.harga THEN
        INSERT INTO audit_log (tabel, aksi, id_record, data_lama, data_baru)
        VALUES (
            'produk', 'UPDATE', NEW.id_produk,
            JSON_OBJECT('harga_lama', OLD.harga),
            JSON_OBJECT('harga_baru', NEW.harga)
        );
    END IF;
END //

DELIMITER ;

-- TRIGGER: Auto-update stok saat detail pesanan masuk
DELIMITER //

CREATE TRIGGER trg_after_detail_insert
AFTER INSERT ON detail_pesanan
FOR EACH ROW
BEGIN
    UPDATE produk
    SET stok = stok - NEW.jumlah
    WHERE id_produk = NEW.id_produk;
END //

DELIMITER ;

-- TRIGGER: Restore stok saat pesanan dibatalkan
DELIMITER //

CREATE TRIGGER trg_pesanan_batal
AFTER UPDATE ON pesanan
FOR EACH ROW
BEGIN
    IF NEW.status = 'batal' AND OLD.status <> 'batal' THEN
        UPDATE produk p
        JOIN detail_pesanan dp ON p.id_produk = dp.id_produk
        SET p.stok = p.stok + dp.jumlah
        WHERE dp.id_pesanan = NEW.id_pesanan;
    END IF;
END //

DELIMITER ;

Trigger di PostgreSQL

PostgreSQL — Triggers

-- PostgreSQL trigger membutuhkan FUNCTION trigger
CREATE OR REPLACE FUNCTION fn_log_harga_produk()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    IF OLD.harga IS DISTINCT FROM NEW.harga THEN
        INSERT INTO audit_log (tabel, aksi, id_record, data_lama, data_baru)
        VALUES (
            'produk', 'UPDATE', NEW.id_produk,
            jsonb_build_object('harga_lama', OLD.harga),
            jsonb_build_object('harga_baru', NEW.harga)
        );
    END IF;
    RETURN NEW;
END;
$$;

CREATE TRIGGER trg_produk_harga_update
BEFORE UPDATE ON produk
FOR EACH ROW
EXECUTE FUNCTION fn_log_harga_produk();


-- Function trigger untuk auto-update stok
CREATE OR REPLACE FUNCTION fn_update_stok()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    UPDATE produk SET stok = stok - NEW.jumlah
    WHERE id_produk = NEW.id_produk;
    RETURN NEW;
END;
$$;

CREATE TRIGGER trg_after_detail_insert
AFTER INSERT ON detail_pesanan
FOR EACH ROW
EXECUTE FUNCTION fn_update_stok();

⚠️ Perhatian Trigger

Trigger bisa menyebabkan cascading effect — trigger di tabel A bisa mengubah tabel B, yang memicu trigger di tabel B, dan seterusnya. Selalu perhatikan rantai trigger untuk menghindari loop tak terbatas atau performa buruk.

Mengelola Triggers

SQL — Manajemen Trigger

-- Melihat semua trigger di database (MySQL)
SHOW TRIGGERS FROM toko_online;

-- Melihat trigger untuk tabel tertentu
SHOW TRIGGERS FROM toko_online WHERE `Table` = 'produk';

-- Menghapus trigger
DROP TRIGGER IF EXISTS trg_produk_harga_update;

-- PostgreSQL: Melihat trigger
SELECT * FROM information_schema.triggers
WHERE event_object_table = 'produk';

-- Menonaktifkan trigger (PostgreSQL)
ALTER TABLE produk DISABLE TRIGGER trg_produk_harga_update;

-- Mengaktifkan kembali
ALTER TABLE produk ENABLE TRIGGER trg_produk_harga_update;

6. Cursors — Iterasi Baris per Baris

Cursor memungkinkan Anda memproses hasil query satu baris per satu baris. Berguna untuk logika kompleks, tapi lebih lambat dari query set-based.

Cara Kerja Cursor

Diagram: Cursor Lifecycle

+--------------------------------------------------------------+
|                   CURSOR LIFECYCLE                             |
|                                                              |
|  1. DECLARE cursor CURSOR FOR SELECT ...  <- Definisi        |
|                    |                                         |
|  2. OPEN cursor    <- Eksekusi query, siap iterasi           |
|                    |                                         |
|  3. FETCH cursor   <- Ambil baris berikutnya                 |
|     INTO var       <- Ulangi sampai NOT FOUND                |
|                    |                                         |
|  4. CLOSE cursor   <- Tutup dan bebaskan resource            |
|                                                              |
+--------------------------------------------------------------+

Contoh Cursor di MySQL

MySQL — Cursors

-- PROCEDURE: Proses pesanan satu per satu dengan cursor
DELIMITER //

CREATE PROCEDURE proses_pesanan_pending()
BEGIN
    DECLARE v_id_pesanan INT;
    DECLARE v_total DECIMAL(12, 2);
    DECLARE v_selesai BOOLEAN DEFAULT FALSE;

    -- Deklarasi cursor
    DECLARE cur_pesanan CURSOR FOR
        SELECT id_pesanan, total
        FROM pesanan
        WHERE status = 'pending'
        ORDER BY tanggal_pesanan;

    -- Handler untuk not found
    DECLARE CONTINUE HANDLER FOR NOT FOUND
        SET v_selesai = TRUE;

    OPEN cur_pesanan;

    proses_loop: LOOP
        FETCH cur_pesanan INTO v_id_pesanan, v_total;

        IF v_selesai THEN
            LEAVE proses_loop;
        END IF;

        -- Logika bisnis: update status berdasarkan total
        IF v_total >= 1000000 THEN
            UPDATE pesanan SET status = 'diproses'
            WHERE id_pesanan = v_id_pesanan;
        ELSE
            UPDATE pesanan SET status = 'dikirim'
            WHERE id_pesanan = v_id_pesanan;
        END IF;
    END LOOP;

    CLOSE cur_pesanan;

    SELECT 'Semua pesanan pending telah diproses' AS status;
END //

DELIMITER ;

CALL proses_pesanan_pending();

Cursor di PostgreSQL

PostgreSQL — Cursors

-- PostgreSQL: cursor dalam function
CREATE OR REPLACE FUNCTION proses_pesanan_pending()
RETURNS TEXT
LANGUAGE plpgsql
AS $$
DECLARE
    v_rec RECORD;
    v_count INT := 0;
    cur_pesanan CURSOR FOR
        SELECT id_pesanan, total
        FROM pesanan
        WHERE status = 'pending'
        ORDER BY tanggal_pesanan;
BEGIN
    OPEN cur_pesanan;

    LOOP
        FETCH cur_pesanan INTO v_rec;
        EXIT WHEN NOT FOUND;

        IF v_rec.total >= 1000000 THEN
            UPDATE pesanan SET status = 'diproses'
            WHERE id_pesanan = v_rec.id_pesanan;
        ELSE
            UPDATE pesanan SET status = 'dikirim'
            WHERE id_pesanan = v_rec.id_pesanan;
        END IF;

        v_count := v_count + 1;
    END LOOP;

    CLOSE cur_pesanan;
    RETURN format('Berhasil memproses %s pesanan', v_count);
END;
$$;

SELECT proses_pesanan_pending();

💡 Hindari Cursor Jika Bisa

Cursor memproses baris satu per satu (row-by-row) yang jauh lebih lambat dari operasi set-based. Sebisa mungkin gunakan UPDATE dengan WHERE, JOIN, atau subquery. Cursor hanya untuk kasus yang benar-benar membutuhkan iterasi baris.

7. Error Handling & Exception

Error handling memungkinkan stored procedure menangani kesalahan secara graceful — tidak langsung crash, tapi bisa log error, rollback transaksi, atau memberikan pesan yang informatif.

Error Handler di MySQL

MySQL — Error Handling

-- Handler Types:
--   CONTINUE — Lanjutkan eksekusi setelah handler
--   EXIT     — Keluar dari BEGIN..END block

DELIMITER //

CREATE PROCEDURE buat_pesanan(
    IN  p_id_pelanggan INT,
    IN  p_total        DECIMAL(12, 2),
    OUT p_status       VARCHAR(200)
)
BEGIN
    DECLARE v_pelanggan_ada INT DEFAULT 0;

    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SET p_status = 'ERROR: Terjadi kesalahan database';
    END;

    DECLARE EXIT HANDLER FOR 1452
    BEGIN
        ROLLBACK;
        SET p_status = 'ERROR: Pelanggan tidak ditemukan';
    END;

    DECLARE EXIT HANDLER FOR 1062
    BEGIN
        ROLLBACK;
        SET p_status = 'ERROR: Data duplikat';
    END;

    START TRANSACTION;

    SELECT COUNT(*) INTO v_pelanggan_ada
    FROM pelanggan
    WHERE id_pelanggan = p_id_pelanggan AND is_active = TRUE;

    IF v_pelanggan_ada = 0 THEN
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'Pelanggan tidak aktif atau tidak ditemukan';
    END IF;

    INSERT INTO pesanan (id_pelanggan, total, status)
    VALUES (p_id_pelanggan, p_total, 'pending');

    COMMIT;
    SET p_status = CONCAT('Pesanan berhasil: #', LAST_INSERT_ID());
END //

DELIMITER ;

CALL buat_pesanan(1, 500000, @hasil);
SELECT @hasil;

CALL buat_pesanan(9999, 500000, @hasil);
SELECT @hasil;  -- ERROR: Pelanggan tidak ditemukan

RAISE / EXCEPTION di PostgreSQL

PostgreSQL — Exception Handling

-- PostgreSQL menggunakan EXCEPTION block
CREATE OR REPLACE FUNCTION buat_pesanan(
    p_id_pelanggan INT,
    p_total DECIMAL
)
RETURNS TEXT
LANGUAGE plpgsql
AS $$
DECLARE
    v_id_baru INT;
    v_pelanggan_ada INT;
BEGIN
    SELECT COUNT(*) INTO v_pelanggan_ada
    FROM pelanggan WHERE id_pelanggan = p_id_pelanggan;

    IF v_pelanggan_ada = 0 THEN
        RAISE EXCEPTION 'Pelanggan % tidak ditemukan', p_id_pelanggan
            USING ERRCODE = 'foreign_key_violation';
    END IF;

    IF NOT EXISTS (
        SELECT 1 FROM pelanggan
        WHERE id_pelanggan = p_id_pelanggan AND is_active = TRUE
    ) THEN
        RAISE WARNING 'Pelanggan % tidak aktif', p_id_pelanggan;
        RAISE EXCEPTION 'Pelanggan tidak aktif'
            USING ERRCODE = 'check_violation';
    END IF;

    INSERT INTO pesanan (id_pelanggan, total, status)
    VALUES (p_id_pelanggan, p_total, 'pending')
    RETURNING id_pesanan INTO v_id_baru;

    RETURN format('Pesanan #%s berhasil dibuat', v_id_baru);

EXCEPTION
    WHEN foreign_key_violation THEN
        RETURN 'ERROR: Referensi data tidak valid';
    WHEN check_violation THEN
        RETURN 'ERROR: Data tidak memenuhi aturan validasi';
    WHEN OTHERS THEN
        RETURN format('ERROR: %s (SQLSTATE: %s)', SQLERRM, SQLSTATE);
END;
$$;

SELECT buat_pesanan(1, 750000);
SELECT buat_pesanan(9999, 750000);

8. Transaksi dalam Stored Procedures

Transaksi memungkinkan beberapa operasi database dijalankan sebagai satu unit atomik — semua berhasil atau semua gagal.

Konsep ACID

Properti	Penjelasan	Contoh
Atomicity	Semua operasi berhasil atau semua rollback	Transfer uang: debit + credit
Consistency	Data selalu dalam state yang valid	Stok tidak boleh negatif
Isolation	Transaksi tidak saling ganggu	Dua pembeli berebut 1 stok
Durability	Data yang sudah commit tetap tersimpan	Setelah commit, aman walau server mati

MySQL — Transaksi dalam Procedure

-- PROCEDURE: Transfer stok antar gudang
DELIMITER //

CREATE PROCEDURE transfer_stok(
    IN  p_id_produk   INT,
    IN  p_jumlah      INT,
    IN  p_dari_gudang INT,
    IN  p_ke_gudang   INT,
    OUT p_status      VARCHAR(200)
)
BEGIN
    DECLARE v_stok_asal INT;

    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SET p_status = 'ERROR: Transfer gagal, semua perubahan dibatalkan';
    END;

    START TRANSACTION;

    -- Cek stok di gudang asal (lock baris)
    SELECT stok INTO v_stok_asal
    FROM inventaris_gudang
    WHERE id_produk = p_id_produk AND id_gudang = p_dari_gudang
    FOR UPDATE;

    IF v_stok_asal < p_jumlah THEN
        ROLLBACK;
        SET p_status = CONCAT(
            'ERROR: Stok tidak cukup. Tersedia: ', v_stok_asal,
            ', Diminta: ', p_jumlah
        );
    ELSE
        UPDATE inventaris_gudang
        SET stok = stok - p_jumlah
        WHERE id_produk = p_id_produk AND id_gudang = p_dari_gudang;

        INSERT INTO inventaris_gudang (id_produk, id_gudang, stok)
        VALUES (p_id_produk, p_ke_gudang, p_jumlah)
        ON DUPLICATE KEY UPDATE stok = stok + p_jumlah;

        INSERT INTO log_transfer (id_produk, jumlah, dari_gudang, ke_gudang)
        VALUES (p_id_produk, p_jumlah, p_dari_gudang, p_ke_gudang);

        COMMIT;
        SET p_status = CONCAT(
            'Transfer berhasil: ', p_jumlah, ' unit dari gudang ',
            p_dari_gudang, ' ke gudang ', p_ke_gudang
        );
    END IF;
END //

DELIMITER ;

CALL transfer_stok(1, 50, 1, 2, @hasil);
SELECT @hasil;

Savepoint — Transaksi Parsial

SQL — Savepoint

-- Savepoint memungkinkan rollback parsial
-- Contoh: proses batch, skip item yang gagal

DELIMITER //

CREATE PROCEDURE proses_batch_pesanan()
BEGIN
    DECLARE v_id INT;
    DECLARE v_done BOOLEAN DEFAULT FALSE;
    DECLARE cur CURSOR FOR
        SELECT id_pesanan FROM pesanan WHERE status = 'pending';
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done = TRUE;
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK TO SAVEPOINT sp_item;
        INSERT INTO error_log (pesan)
        VALUES (CONCAT('Gagal proses pesanan #', v_id));
    END;

    START TRANSACTION;

    OPEN cur;
    loop_batch: LOOP
        FETCH cur INTO v_id;
        IF v_done THEN LEAVE loop_batch; END IF;

        SAVEPOINT sp_item;

        UPDATE pesanan SET status = 'diproses'
        WHERE id_pesanan = v_id;
    END LOOP;
    CLOSE cur;

    COMMIT;
END //

DELIMITER ;

9. Best Practices & Performance

Stored Procedures yang ditulis dengan baik bisa sangat meningkatkan performa aplikasi.

Tips Performa

Praktik	Penjelasan
Gunakan set-based operation	Hindari cursor dan loop; gunakan UPDATE/INSERT dengan JOIN atau subquery
Minimalisir jaringan	Lakukan semua operasi di dalam procedure, bukan bolak-balik dari aplikasi
Parameterize query	Hindari concatenation string yang bisa menyebabkan SQL injection
Gunakan EXPLAIN	Analisis execution plan dari query di dalam procedure
Modularisasi	Pecah procedure besar menjadi beberapa procedure kecil yang reusable
Test dengan data besar	Performa di development tidak mencerminkan production

SQL — Best Practices

-- 1. Gunakan SET-BASED, bukan cursor loop
-- BURUK:
-- OPEN cur; LOOP FETCH cur INTO v_id;
--   UPDATE pesanan SET status='diproses' WHERE id=v_id;
-- END LOOP;

-- BAIK:
UPDATE pesanan SET status = 'diproses'
WHERE status = 'pending'
  AND tanggal_pesanan < NOW() - INTERVAL 1 DAY;


-- 2. Hindari SELECT * dalam procedure
-- BURUK: SELECT * FROM produk WHERE kategori = p_kategori;
-- BAIK:
SELECT id_produk, nama_produk, harga
FROM produk WHERE kategori = p_kategori;


-- 3. Gunakan penamaan yang konsisten
-- Prefix parameter:    p_nama_param
-- Prefix variabel:     v_nama_variabel
-- Prefix cursor:       cur_nama_cursor
-- Prefix procedure:    sp_nama_prosedur
-- Prefix function:     fn_nama_fungsi
-- Prefix trigger:      trg_nama_trigger


-- 4. Dokumentasikan setiap procedure
DELIMITER //

-- Procedure: sp_hitung_revenue_bulanan
-- Deskripsi: Hitung revenue per bulan untuk tahun tertentu
-- Parameter:
--   IN  p_tahun — Tahun yang ingin dihitung
--   OUT p_hasil — JSON string berisi revenue per bulan
-- Contoh:
--   CALL sp_hitung_revenue_bulanan(2026, @r);
--   SELECT @r;
CREATE PROCEDURE sp_hitung_revenue_bulanan(
    IN  p_tahun INT,
    OUT p_hasil TEXT
)
BEGIN
    SELECT JSON_ARRAYAGG(
        JSON_OBJECT(
            'bulan', bulan,
            'revenue', revenue,
            'jumlah_pesanan', jml
        )
    )
    INTO p_hasil
    FROM (
        SELECT
            MONTH(tanggal_pesanan) AS bulan,
            SUM(total) AS revenue,
            COUNT(*) AS jml
        FROM pesanan
        WHERE YEAR(tanggal_pesanan) = p_tahun
          AND status <> 'batal'
        GROUP BY MONTH(tanggal_pesanan)
        ORDER BY bulan
    ) AS monthly_data;
END //

DELIMITER ;

10. Quiz Pemahaman

Uji pemahaman Anda tentang Stored Procedures dan Functions:

📝 Quiz: Stored Procedures & Functions

Apa perbedaan utama antara Stored Procedure dan Function?
Jawaban: Function wajib mengembalikan satu nilai dan bisa dipanggil dalam SELECT. Procedure tidak wajib return dan dipanggil dengan CALL.
Apa fungsi dari parameter OUT?
Jawaban: Mengembalikan nilai dari procedure ke pemanggil. Nilai di-set di dalam procedure dan diakses dengan variabel @ setelah pemanggilan.
Kapan trigger AFTER INSERT lebih tepat daripada BEFORE INSERT?
Jawaban: AFTER INSERT digunakan ketika kita butuh memproses data yang sudah tersimpan (misalnya update tabel lain, catat audit log). BEFORE INSERT untuk validasi atau modifikasi data sebelum tersimpan.
Mengapa cursor sebaiknya dihindari jika memungkinkan?
Jawaban: Cursor memproses data row-by-row yang jauh lebih lambat dari operasi set-based. Cursor juga memakan lebih banyak memori.
Apa itu DELIMITER dalam MySQL dan mengapa diperlukan?
Jawaban: DELIMITER mengubah karakter pembatas perintah (default ;) agar ; di dalam body procedure tidak mengakhiri CREATE PROCEDURE.
Apa perbedaan DETERMINISTIC dan NOT DETERMINISTIC pada function?
Jawaban: DETERMINISTIC berarti function selalu menghasilkan output yang sama untuk input yang sama. NOT DETERMINISTIC (default) berarti output bisa berbeda.
Bagaimana cara mencegah race condition dalam transaksi?
Jawaban: Gunakan SELECT ... FOR UPDATE untuk meng-lock baris yang sedang diproses.

🎯 Langkah Selanjutnya

Setelah menguasai Stored Procedures dan Functions, lanjutkan belajar tentang Database Migration Tools untuk mengelola perubahan skema database, atau Database Sharding untuk skalabilitas horizontal.