Database and SQL Fundamental MySQL 4

🧩 Pertemuan 4 — Stored Procedure, Function & Trigger di MySQL

🎯 Tujuan Pembelajaran

Setelah pertemuan ini, kamu diharapkan mampu:

• Membuat dan menjalankan Stored Procedure dengan/tanpa parameter di MySQL.
• Menggunakan built-in Function: String, Date, dan Aggregate.
• Membuat Trigger untuk otomatisasi aksi di database.
• Menangani error menggunakan DECLARE HANDLER di MySQL.

---

📘 1. Stored Procedure di MySQL

Stored Procedure adalah sekumpulan perintah SQL yang disimpan di dalam database dan dapat dipanggil berulang kali.

Keuntungan Stored Procedure:

• Query dikompilasi sekali → eksekusi lebih cepat
• Mengurangi pengulangan kode
• Meningkatkan keamanan (user tidak perlu akses langsung ke tabel)
• Memudahkan pemeliharaan

Sintaks Dasar MySQL:

-- Ubah delimiter karena ; digunakan di dalam procedure
DELIMITER //

CREATE PROCEDURE nama_procedure()
BEGIN
    -- query SQL di sini
END //

DELIMITER ;

-- Menjalankan procedure
CALL nama_procedure();

⚠️ Penting di MySQL: Harus mengubah DELIMITER sebelum membuat stored procedure karena MySQL menggunakan ; sebagai penanda akhir perintah.

---

📘 2. Function di MySQL

MySQL menyediakan banyak fungsi bawaan:

Kategori	Fungsi	Contoh
String	UPPER(), LOWER(), LENGTH(), CONCAT(), SUBSTRING(), TRIM(), REPLACE()	UPPER('hello') → 'HELLO'
Date	NOW(), CURDATE(), DATE_FORMAT(), DATEDIFF(), DATE_ADD(), YEAR(), MONTH(), DAY()	DATEDIFF('2026-06-11', '2026-06-01') → 10
Aggregate	COUNT(), SUM(), AVG(), MAX(), MIN()	COUNT(*) → jumlah baris
Logika	IF(), IFNULL(), COALESCE(), NULLIF()	IF(stok=0, 'Habis', 'Tersedia')
Numerik	ROUND(), CEIL(), FLOOR(), ABS(), MOD()	ROUND(3.567, 2) → 3.57

---

📘 3. Trigger di MySQL

Trigger adalah prosedur yang berjalan otomatis ketika terjadi event tertentu pada tabel (INSERT, UPDATE, DELETE).

DELIMITER //
CREATE TRIGGER nama_trigger
BEFORE/AFTER INSERT/UPDATE/DELETE ON nama_tabel
FOR EACH ROW
BEGIN
    -- aksi yang dijalankan
END //
DELIMITER ;

Di dalam trigger, tersedia dua tabel virtual:

• NEW — nilai baru (tersedia di INSERT dan UPDATE)
• OLD — nilai lama (tersedia di UPDATE dan DELETE)

---

🧑‍💻 SETUP — Gunakan database perpustakaan dari pertemuan sebelumnya

USE db_perpustakaan;

---

🧑‍💻 LATIHAN 1 — String Function

-- 1. Ubah nama anggota menjadi huruf kapital semua
SELECT UPPER(nama) AS nama_kapital FROM anggota;

-- 2. Ubah nama menjadi huruf kecil
SELECT LOWER(nama) AS nama_kecil FROM anggota;

-- 3. Panjang nama setiap anggota
SELECT nama, LENGTH(nama) AS panjang_nama FROM anggota;

-- 4. Gabungkan judul dan penulis buku
SELECT CONCAT(judul, ' — oleh ', penulis) AS info_buku FROM buku;

-- 5. Ambil 15 karakter pertama judul buku
SELECT judul, SUBSTRING(judul, 1, 15) AS judul_singkat FROM buku;

-- 6. Ambil 3 huruf pertama nama untuk membuat username
SELECT nama, LOWER(SUBSTRING(nama, 1, 3)) AS username_awal FROM anggota;

-- 7. Ganti kata dalam teks
SELECT REPLACE(alamat, 'Jakarta', 'JKT') AS alamat_singkat FROM anggota;

-- 8. Hapus spasi di awal dan akhir
SELECT TRIM('  data dengan spasi  ') AS hasil_trim;

-- 9. Posisi kata dalam string
SELECT LOCATE('Hirata', penulis) AS posisi, penulis FROM buku;

-- 10. Gabungkan dengan separator menggunakan CONCAT_WS
SELECT CONCAT_WS(' | ', kode_ang, nama, email) AS info_anggota FROM anggota;

---

🧑‍💻 LATIHAN 2 — Date Function

-- 1. Tanggal dan waktu sekarang
SELECT NOW() AS sekarang, CURDATE() AS hari_ini, CURTIME() AS jam_ini;

-- 2. Komponen dari tanggal
SELECT
    tgl_pinjam,
    YEAR(tgl_pinjam)    AS tahun,
    MONTH(tgl_pinjam)   AS bulan,
    DAY(tgl_pinjam)     AS hari,
    DAYNAME(tgl_pinjam) AS nama_hari
FROM peminjaman;

-- 3. Hitung lama peminjaman dalam hari
SELECT
    a.nama,
    b.judul,
    p.tgl_pinjam,
    p.tgl_kembali,
    DATEDIFF(p.tgl_kembali, p.tgl_pinjam) AS lama_hari
FROM peminjaman p
JOIN anggota a ON p.id_anggota = a.id_anggota
JOIN buku b ON p.id_buku = b.id_buku;

-- 4. Tambahkan 14 hari dari tanggal pinjam (deadline)
SELECT
    tgl_pinjam,
    DATE_ADD(tgl_pinjam, INTERVAL 14 DAY) AS deadline_kembali
FROM peminjaman;

-- 5. Format tampilan tanggal
SELECT
    tgl_pinjam,
    DATE_FORMAT(tgl_pinjam, '%d %M %Y') AS format_indo,
    DATE_FORMAT(tgl_pinjam, '%d/%m/%Y') AS format_slash
FROM peminjaman;

-- 6. Hitung keterlambatan
SELECT
    a.nama,
    b.judul,
    p.tgl_kembali AS seharusnya,
    CURDATE() AS hari_ini,
    GREATEST(0, DATEDIFF(CURDATE(), p.tgl_kembali)) AS hari_terlambat,
    GREATEST(0, DATEDIFF(CURDATE(), p.tgl_kembali)) * 1000 AS denda_rp
FROM peminjaman p
JOIN anggota a ON p.id_anggota = a.id_anggota
JOIN buku b ON p.id_buku = b.id_buku
WHERE p.status = 'dipinjam';

---

🧑‍💻 LATIHAN 3 — Aggregate Function

-- 1. Hitung jumlah buku, anggota, dan peminjaman
SELECT
    (SELECT COUNT(*) FROM buku)       AS total_buku,
    (SELECT COUNT(*) FROM anggota)    AS total_anggota,
    (SELECT COUNT(*) FROM peminjaman) AS total_peminjaman;

-- 2. Statistik stok buku
SELECT
    COUNT(*)    AS jumlah_judul,
    SUM(stok)   AS total_stok,
    AVG(stok)   AS rata_stok,
    MAX(stok)   AS stok_max,
    MIN(stok)   AS stok_min
FROM buku;

-- 3. Jumlah peminjaman per anggota
SELECT
    a.nama,
    COUNT(p.id_pinjam) AS total_pinjam
FROM anggota a
LEFT JOIN peminjaman p ON a.id_anggota = p.id_anggota
GROUP BY a.id_anggota, a.nama
ORDER BY total_pinjam DESC;

-- 4. Jumlah buku per kategori
SELECT
    k.nama_kat,
    COUNT(b.id_buku) AS jumlah_buku,
    AVG(b.stok) AS rata_stok
FROM kategori k
LEFT JOIN buku b ON k.id_kategori = b.id_kategori
GROUP BY k.id_kategori, k.nama_kat;

-- 5. Anggota yang meminjam lebih dari 1 buku (HAVING)
SELECT
    a.nama,
    COUNT(p.id_pinjam) AS total_pinjam
FROM anggota a
JOIN peminjaman p ON a.id_anggota = p.id_anggota
GROUP BY a.id_anggota, a.nama
HAVING COUNT(p.id_pinjam) > 1;

---

🧑‍💻 LATIHAN 4 — Stored Procedure Tanpa Parameter

DELIMITER //

-- Procedure untuk menampilkan semua buku
CREATE PROCEDURE sp_tampil_semua_buku()
BEGIN
    SELECT
        b.isbn,
        b.judul,
        b.penulis,
        k.nama_kat AS kategori,
        b.stok
    FROM buku b
    JOIN kategori k ON b.id_kategori = k.id_kategori
    ORDER BY b.judul;
END //

-- Procedure untuk statistik perpustakaan
CREATE PROCEDURE sp_statistik()
BEGIN
    SELECT 'Statistik Perpustakaan' AS info;

    SELECT COUNT(*) AS total_buku FROM buku;
    SELECT COUNT(*) AS total_anggota FROM anggota;
    SELECT COUNT(*) AS peminjaman_aktif FROM peminjaman WHERE status = 'dipinjam';
    SELECT COUNT(*) AS buku_habis FROM buku WHERE stok = 0;
END //

DELIMITER ;

-- Jalankan procedure
CALL sp_tampil_semua_buku();
CALL sp_statistik();

-- Lihat semua procedure yang ada
SHOW PROCEDURE STATUS WHERE Db = 'db_perpustakaan';

---

🧑‍💻 LATIHAN 5 — Stored Procedure dengan Parameter

DELIMITER //

-- Parameter IN: cari buku berdasarkan keyword judul
CREATE PROCEDURE sp_cari_buku(IN p_keyword VARCHAR(100))
BEGIN
    SELECT isbn, judul, penulis, stok
    FROM buku
    WHERE judul LIKE CONCAT('%', p_keyword, '%')
       OR penulis LIKE CONCAT('%', p_keyword, '%');
END //

-- Parameter IN + OUT: tambah peminjaman dan kembalikan ID
CREATE PROCEDURE sp_pinjam_buku(
    IN  p_id_anggota    INT,
    IN  p_id_buku       INT,
    OUT p_id_pinjam     INT,
    OUT p_pesan         VARCHAR(200)
)
BEGIN
    DECLARE v_stok INT DEFAULT 0;

    -- Cek stok buku
    SELECT stok INTO v_stok FROM buku WHERE id_buku = p_id_buku;

    IF v_stok > 0 THEN
        -- Kurangi stok
        UPDATE buku SET stok = stok - 1 WHERE id_buku = p_id_buku;

        -- Tambah record peminjaman
        INSERT INTO peminjaman (id_anggota, id_buku, tgl_pinjam, tgl_kembali)
        VALUES (p_id_anggota, p_id_buku, CURDATE(), DATE_ADD(CURDATE(), INTERVAL 7 DAY));

        SET p_id_pinjam = LAST_INSERT_ID();
        SET p_pesan = 'Peminjaman berhasil!';
    ELSE
        SET p_id_pinjam = 0;
        SET p_pesan = 'Stok buku habis, tidak bisa dipinjam!';
    END IF;
END //

-- Procedure untuk mengembalikan buku
CREATE PROCEDURE sp_kembalikan_buku(
    IN  p_id_pinjam INT,
    OUT p_denda     INT,
    OUT p_pesan     VARCHAR(200)
)
BEGIN
    DECLARE v_tgl_kembali DATE;
    DECLARE v_id_buku INT;
    DECLARE v_hari_telat INT DEFAULT 0;

    SELECT tgl_kembali, id_buku
    INTO v_tgl_kembali, v_id_buku
    FROM peminjaman WHERE id_pinjam = p_id_pinjam;

    SET v_hari_telat = GREATEST(0, DATEDIFF(CURDATE(), v_tgl_kembali));

    -- Update status peminjaman
    UPDATE peminjaman
    SET status = IF(v_hari_telat > 0, 'terlambat', 'dikembalikan'),
        tgl_kembali_aktual = CURDATE()
    WHERE id_pinjam = p_id_pinjam;

    -- Kembalikan stok buku
    UPDATE buku SET stok = stok + 1 WHERE id_buku = v_id_buku;

    SET p_denda = v_hari_telat * 1000;
    SET p_pesan = IF(v_hari_telat > 0,
        CONCAT('Terlambat ', v_hari_telat, ' hari. Denda: Rp ', v_hari_telat * 1000),
        'Buku dikembalikan tepat waktu. Terima kasih!');
END //

DELIMITER ;

-- Uji coba procedure
CALL sp_cari_buku('Andrea');
CALL sp_cari_buku('Python');

-- Uji pinjam buku
SET @id_pinjam = 0;
SET @pesan = '';
CALL sp_pinjam_buku(3, 4, @id_pinjam, @pesan);
SELECT @id_pinjam AS id_peminjaman_baru, @pesan AS pesan;

-- Uji kembalikan buku
SET @denda = 0;
SET @pesan_kembali = '';
CALL sp_kembalikan_buku(1, @denda, @pesan_kembali);
SELECT @denda AS total_denda, @pesan_kembali AS pesan;

---

🧑‍💻 LATIHAN 6 — Trigger

-- Buat tabel log untuk mencatat aktivitas
CREATE TABLE log_aktivitas (
    id_log      INT             AUTO_INCREMENT PRIMARY KEY,
    tabel_terkait VARCHAR(50),
    aksi        ENUM('INSERT','UPDATE','DELETE'),
    id_record   INT,
    keterangan  TEXT,
    waktu       DATETIME        DEFAULT NOW()
);

DELIMITER //

-- Trigger: catat setiap peminjaman baru
CREATE TRIGGER trg_after_insert_pinjam
AFTER INSERT ON peminjaman
FOR EACH ROW
BEGIN
    INSERT INTO log_aktivitas (tabel_terkait, aksi, id_record, keterangan)
    VALUES ('peminjaman', 'INSERT', NEW.id_pinjam,
        CONCAT('Anggota ID=', NEW.id_anggota, ' meminjam Buku ID=', NEW.id_buku));
END //

-- Trigger: cegah peminjaman jika stok habis (BEFORE INSERT)
CREATE TRIGGER trg_before_insert_pinjam
BEFORE INSERT ON peminjaman
FOR EACH ROW
BEGIN
    DECLARE v_stok INT;
    SELECT stok INTO v_stok FROM buku WHERE id_buku = NEW.id_buku;

    IF v_stok <= 0 THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'ERROR: Stok buku habis, tidak bisa dipinjam!';
    END IF;
END //

-- Trigger: update status peminjaman menjadi terlambat otomatis
CREATE TRIGGER trg_before_update_pinjam
BEFORE UPDATE ON peminjaman
FOR EACH ROW
BEGIN
    IF NEW.tgl_kembali_aktual IS NOT NULL AND OLD.status = 'dipinjam' THEN
        IF NEW.tgl_kembali_aktual > OLD.tgl_kembali THEN
            SET NEW.status = 'terlambat';
        ELSE
            SET NEW.status = 'dikembalikan';
        END IF;
    END IF;
END //

-- Trigger: catat perubahan data anggota
CREATE TRIGGER trg_after_update_anggota
AFTER UPDATE ON anggota
FOR EACH ROW
BEGIN
    INSERT INTO log_aktivitas (tabel_terkait, aksi, id_record, keterangan)
    VALUES ('anggota', 'UPDATE', NEW.id_anggota,
        CONCAT('Data diubah: nama [', OLD.nama, ' → ', NEW.nama, ']'));
END //

DELIMITER ;

-- Uji trigger
-- 1. Test insert peminjaman → trigger log aktif
INSERT INTO peminjaman (id_anggota, id_buku, tgl_pinjam, tgl_kembali)
VALUES (5, 6, CURDATE(), DATE_ADD(CURDATE(), INTERVAL 7 DAY));
SELECT * FROM log_aktivitas;

-- 2. Test update anggota → trigger log aktif
UPDATE anggota SET nama = 'Andi Wijaya Updated' WHERE id_anggota = 1;
SELECT * FROM log_aktivitas;

-- Lihat semua trigger
SHOW TRIGGERS FROM db_perpustakaan;

---

🧑‍💻 LATIHAN 7 — Kasus Gabungan: Sistem Peminjaman Lengkap

Buat prosedur dan trigger untuk sistem berikut:

-- SOAL 1:
-- Buat procedure sp_laporan_harian(IN p_tanggal DATE)
-- yang menampilkan:
-- a. Total buku dipinjam pada tanggal tersebut
-- b. Total buku dikembalikan pada tanggal tersebut
-- c. Daftar anggota yang masih meminjam dengan status terlambat


-- SOAL 2:
-- Buat procedure sp_perpanjang_pinjam(IN p_id_pinjam INT, IN p_hari_tambah INT)
-- yang memperpanjang tanggal kembali sebanyak p_hari_tambah hari
-- Hanya bisa diperpanjang maksimal 1 kali


-- SOAL 3:
-- Buat trigger trg_after_delete_anggota
-- yang mencatat ke log_aktivitas setiap kali data anggota dihapus


-- SOAL 4:
-- Buat function (bukan procedure) untuk menghitung denda:
-- CREATE FUNCTION fn_hitung_denda(p_id_pinjam INT) RETURNS INT
-- Kembalikan total denda dalam rupiah (Rp 500/hari)

---

🧩 TUGAS PERORANGAN

Estimasi waktu: ±2 jam

Gunakan database dari pertemuan sebelumnya, buat:

1. 3 Stored Procedure minimum:

   • 1 procedure tanpa parameter
   • 1 procedure dengan parameter IN
   • 1 procedure dengan parameter IN dan OUT

2. 1 buah Trigger minimum untuk mencatat log perubahan

3. 5 query yang menggunakan minimal 3 jenis function berbeda (String, Date, Aggregate)

4. Setiap procedure/trigger harus disertai komentar penjelasan

💾 Kumpulkan: Pertemuan4_Nama.sql

---

🔍 Refleksi

1. Apa perbedaan antara Stored Procedure dan Function di MySQL?
2. Apa keuntungan menggunakan Trigger dibanding memanggil procedure dari aplikasi?
3. Kapan kita perlu menggunakan parameter OUT dalam stored procedure?
4. Mengapa DELIMITER perlu diubah sebelum membuat procedure/trigger di MySQL?