Stored Procedures: Bikin Query yang Bisa Dipake Ulang

Halo SQL Explorers! Di materi sebelumnya kita udah belajar bikin query yang kompleks. Sekarang kita bakal belajar bikin stored procedure buat simpan query yang sering dipake!

Kenapa Perlu Stored Procedure?

Di materi sebelumnya, kita udah bikin query kompleks buat:

  • Analisis penjualan
  • Customer retention
  • Product recommendation

Tapi ada beberapa masalah:

  1. Query panjang dan susah diinget
  2. Banyak parameter yang bisa berubah
  3. Perlu dijalanin berkali-kali

Stored procedure bisa bantu:

  • Simpan query buat dipake ulang
  • Terima parameter yang bisa diubah
  • Handle error dengan baik
  • Lebih aman dan cepet

Basic Stored Procedure

1. Bikin Stored Procedure

DELIMITER //

CREATE PROCEDURE get_customer_orders(
    IN customer_id INT
)
BEGIN
    SELECT 
        o.id as order_id,
        o.tanggal,
        p.nama as product,
        oi.quantity,
        oi.price,
        (oi.quantity * oi.price) as total
    FROM orders o
    JOIN order_items oi ON o.id = oi.order_id
    JOIN products p ON oi.product_id = p.id
    WHERE o.customer_id = customer_id;
END //

DELIMITER ;

-- Panggil stored procedure
CALL get_customer_orders(1);

2. Input dan Output Parameter

DELIMITER //

CREATE PROCEDURE calculate_order_total(
    IN order_id INT,
    OUT total_amount DECIMAL(10,2)
)
BEGIN
    SELECT SUM(quantity * price)
    INTO total_amount
    FROM order_items
    WHERE order_id = order_id;
END //

DELIMITER ;

-- Panggil dan ambil hasilnya
SET @total = 0;
CALL calculate_order_total(1, @total);
SELECT @total;

Error Handling

1. Basic Error Handling

DELIMITER //

CREATE PROCEDURE create_order(
    IN customer_id INT,
    IN product_id INT,
    IN quantity INT,
    OUT order_id INT
)
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'Error creating order';
    END;

    START TRANSACTION;
        -- Cek stok
        IF NOT EXISTS (
            SELECT 1 FROM products 
            WHERE id = product_id AND stock >= quantity
        ) THEN
            SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'Insufficient stock';
        END IF;

        -- Bikin order
        INSERT INTO orders (customer_id, tanggal)
        VALUES (customer_id, NOW());
        
        SET order_id = LAST_INSERT_ID();
        
        -- Tambah order items
        INSERT INTO order_items (order_id, product_id, quantity)
        VALUES (order_id, product_id, quantity);
        
        -- Update stok
        UPDATE products 
        SET stock = stock - quantity
        WHERE id = product_id;
    COMMIT;
END //

DELIMITER ;

2. Custom Error Messages

DELIMITER //

CREATE PROCEDURE validate_order(
    IN customer_id INT,
    IN total_amount DECIMAL(10,2)
)
BEGIN
    DECLARE credit_limit DECIMAL(10,2);
    
    -- Cek customer exists
    IF NOT EXISTS (SELECT 1 FROM customers WHERE id = customer_id) THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'Customer not found';
    END IF;
    
    -- Cek credit limit
    SELECT credit_limit INTO credit_limit
    FROM customers 
    WHERE id = customer_id;
    
    IF total_amount > credit_limit THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = CONCAT(
            'Order exceeds credit limit. Limit: ', 
            credit_limit, 
            ', Order: ', 
            total_amount
        );
    END IF;
END //

DELIMITER ;

Complex Business Logic

1. Customer Segmentation

DELIMITER //

CREATE PROCEDURE update_customer_segment()
BEGIN
    -- Hitung metrics
    WITH customer_metrics AS (
        SELECT 
            c.id,
            COUNT(DISTINCT o.id) as frequency,
            SUM(oi.quantity * oi.price) as monetary,
            DATEDIFF(
                NOW(), 
                MAX(o.tanggal)
            ) as recency
        FROM customers c
        LEFT JOIN orders o ON c.id = o.customer_id
        LEFT JOIN order_items oi ON o.id = oi.order_id
        GROUP BY c.id
    )
    -- Update segment
    UPDATE customers c
    JOIN customer_metrics cm ON c.id = cm.id
    SET c.segment = 
        CASE 
            WHEN cm.recency <= 30 
                AND cm.frequency >= 3 
                AND cm.monetary >= 10000000 
                THEN 'VIP'
            WHEN cm.recency <= 90 
                AND cm.frequency >= 2 
                AND cm.monetary >= 5000000 
                THEN 'Regular'
            ELSE 'New'
        END;
END //

DELIMITER ;

2. Sales Report Generator

DELIMITER //

CREATE PROCEDURE generate_sales_report(
    IN start_date DATE,
    IN end_date DATE,
    IN group_by VARCHAR(10)  -- 'daily', 'weekly', 'monthly'
)
BEGIN
    CASE group_by
        WHEN 'daily' THEN
            SELECT 
                DATE(o.tanggal) as period,
                COUNT(DISTINCT o.id) as orders,
                COUNT(DISTINCT o.customer_id) as customers,
                SUM(oi.quantity * oi.price) as revenue
            FROM orders o
            JOIN order_items oi ON o.id = oi.order_id
            WHERE o.tanggal BETWEEN start_date AND end_date
            GROUP BY DATE(o.tanggal);
            
        WHEN 'weekly' THEN
            SELECT 
                DATE(DATE_SUB(o.tanggal, 
                    INTERVAL WEEKDAY(o.tanggal) DAY)
                ) as period,
                COUNT(DISTINCT o.id) as orders,
                COUNT(DISTINCT o.customer_id) as customers,
                SUM(oi.quantity * oi.price) as revenue
            FROM orders o
            JOIN order_items oi ON o.id = oi.order_id
            WHERE o.tanggal BETWEEN start_date AND end_date
            GROUP BY DATE(DATE_SUB(o.tanggal, 
                INTERVAL WEEKDAY(o.tanggal) DAY)
            );
            
        WHEN 'monthly' THEN
            SELECT 
                DATE_FORMAT(o.tanggal, '%Y-%m-01') as period,
                COUNT(DISTINCT o.id) as orders,
                COUNT(DISTINCT o.customer_id) as customers,
                SUM(oi.quantity * oi.price) as revenue
            FROM orders o
            JOIN order_items oi ON o.id = oi.order_id
            WHERE o.tanggal BETWEEN start_date AND end_date
            GROUP BY DATE_FORMAT(o.tanggal, '%Y-%m-01');
    END CASE;
END //

DELIMITER ;

Tips Stored Procedure

  1. Dokumentasi yang Jelas

    DELIMITER //

CREATE PROCEDURE process_order(
    IN customer_id INT,
    IN product_id INT,
    IN quantity INT,
    OUT order_id INT
)
COMMENT 'Process new order with stock validation'
BEGIN
    -- Input validation
    -- ...

    -- Create order
    -- ...

    -- Update inventory
    -- ...
END //

DELIMITER ;

  2. Modular dan Reusable

    -- Pecah jadi beberapa procedure
CALL validate_customer(customer_id);
CALL check_stock(product_id, quantity);
CALL create_order(customer_id, @order_id);
CALL add_order_item(@order_id, product_id, quantity);

  3. Testing

    -- Test happy path
CALL process_order(1, 1, 1, @order_id);

-- Test invalid customer
CALL process_order(999, 1, 1, @order_id);

-- Test insufficient stock
CALL process_order(1, 1, 999999, @order_id);

Latihan Stored Procedure

  1. Order Processing System
DELIMITER //

CREATE PROCEDURE process_order_v2(
    IN customer_id INT,
    IN product_ids JSON,  -- Array of product IDs
    IN quantities JSON,   -- Array of quantities
    OUT order_id INT
)
BEGIN
    DECLARE i INT DEFAULT 0;
    DECLARE n_items INT;
    DECLARE curr_product INT;
    DECLARE curr_quantity INT;
    
    -- Validate input
    IF JSON_LENGTH(product_ids) != JSON_LENGTH(quantities) THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'Product and quantity arrays must match';
    END IF;
    
    SET n_items = JSON_LENGTH(product_ids);
    
    -- Start transaction
    START TRANSACTION;
    
    -- Create order
    INSERT INTO orders (customer_id, tanggal)
    VALUES (customer_id, NOW());
    
    SET order_id = LAST_INSERT_ID();
    
    -- Add items
    WHILE i < n_items DO
        SET curr_product = JSON_EXTRACT(product_ids, CONCAT('$[', i, ']'));
        SET curr_quantity = JSON_EXTRACT(quantities, CONCAT('$[', i, ']'));
        
        -- Check stock
        IF NOT EXISTS (
            SELECT 1 FROM products 
            WHERE id = curr_product 
            AND stock >= curr_quantity
        ) THEN
            ROLLBACK;
            SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = CONCAT(
                'Insufficient stock for product ', 
                curr_product
            );
        END IF;
        
        -- Add item
        INSERT INTO order_items (
            order_id, 
            product_id, 
            quantity
        )
        VALUES (
            order_id, 
            curr_product, 
            curr_quantity
        );
        
        -- Update stock
        UPDATE products 
        SET stock = stock - curr_quantity
        WHERE id = curr_product;
        
        SET i = i + 1;
    END WHILE;
    
    COMMIT;
END //

DELIMITER ;

-- Test the procedure
SET @product_ids = JSON_ARRAY(1, 2, 3);
SET @quantities = JSON_ARRAY(1, 2, 1);
CALL process_order_v2(1, @product_ids, @quantities, @order_id);
  1. Report Generator
DELIMITER //

CREATE PROCEDURE generate_customer_report(
    IN customer_id INT,
    IN report_type VARCHAR(20)  -- 'summary', 'detailed'
)
BEGIN
    CASE report_type
        WHEN 'summary' THEN
            SELECT 
                c.nama,
                COUNT(DISTINCT o.id) as total_orders,
                SUM(oi.quantity * oi.price) as total_spent,
                AVG(oi.quantity * oi.price) as avg_order_value,
                MAX(o.tanggal) as last_order_date
            FROM customers c
            LEFT JOIN orders o ON c.id = o.customer_id
            LEFT JOIN order_items oi ON o.id = oi.order_id
            WHERE c.id = customer_id
            GROUP BY c.id;
            
        WHEN 'detailed' THEN
            SELECT 
                o.tanggal,
                p.nama as product,
                oi.quantity,
                oi.price as unit_price,
                (oi.quantity * oi.price) as total
            FROM orders o
            JOIN order_items oi ON o.id = oi.order_id
            JOIN products p ON oi.product_id = p.id
            WHERE o.customer_id = customer_id
            ORDER BY o.tanggal DESC;
    END CASE;
END //

DELIMITER ;

Preview Materi Selanjutnya

Di materi triggers kita bakal belajar:

  • Bikin trigger
  • Handle event database
  • Automate task
  • Dan masih banyak lagi!

Siap belajar bikin trigger? Yuk lanjut ke materi triggers!

Fatkul Nur Koirudin