Table of Contents

MySQL Stored Procedure: Panduan Lengkap Membuat dan Menggunakan Procedure untuk Otomasi Database

Pernah nggak sih kamu capek menulis query MySQL yang sama berulang-ulang? Atau punya logic bisnis complex yang harus dijalankan di application code, padahal lebih efisien kalau jalan di database level? Nah, inilah saatnya kamu berkenalan dengan MySQL Stored Procedure—senjata rahasia untuk otomasi dan optimasi database operations!

Bayangin stored procedure itu seperti macro di Excel. Kamu bisa rekam serangkaian perintah complex, lalu jalankan dengan satu command sederhana. Bedanya, stored procedure jauh lebih powerful karena bisa handle logic programming lengkap dengan variables, conditions, loops, dan error handling.

Di panduan komprehensif ini, kita akan belajar stored procedure dari fundamental sampai advanced. Aku janji bakal jelasin dengan analogi yang mudah dimengerti dan contoh-contoh praktis yang langsung bisa diaplikasikan. Yuk, naik level skill database-mu!

Apa Itu Stored Procedure dan Kenapa Dia Penting?

Stored procedure adalah kumpulan SQL statements yang disimpan di database dan bisa dipanggil seperti function.

Analog Sederhana Stored Procedure

Stored procedure itu seperti resep masakan yang disimpan di dapur:

Bahan-bahan: Parameters (input values)
Langkah-langkah: SQL statements
Hasil akhir: Return values atau output parameters

Manfaat Menggunakan Stored Procedure

Keuntungan	Penjelasan	Impact
⚡ Performance	Pre-compiled, reduced network traffic	10-100x faster untuk complex operations
🛡️ Security	Application hanya perlu execute permission	Reduced SQL injection risk
🔧 Maintenance	Logic terpusat di satu tempat	Easy updates tanpa deploy aplikasi
💼 Business Logic	Complex logic di database level	Consistency across applications

Fundamental Stored Procedure Syntax

Mari mulai dengan struktur dasar pembuatan stored procedure.

1. Basic Stored Procedure Structure

DELIMITER //

CREATE PROCEDURE procedure_name(parameter_list)
BEGIN
    -- Procedure body
    -- SQL statements disini
END //

DELIMITER ;

2. Menggunakan DELIMITER dengan Benar

-- ❌ SALAH: Tanpa delimiter yang tepat
CREATE PROCEDURE test() BEGIN SELECT 'Hello'; END

-- ✅ BENAR: Dengan delimiter
DELIMITER //
CREATE PROCEDURE test()
BEGIN
    SELECT 'Hello World';
END //
DELIMITER ;

Membuat Stored Procedure Pertama

Mari buat procedure sederhana untuk memahami konsep dasar.

3. Procedure tanpa Parameters

DELIMITER //

CREATE PROCEDURE GetTotalUsers()
BEGIN
    SELECT COUNT(*) AS total_users FROM users;
END //

DELIMITER ;

-- Cara panggil
CALL GetTotalUsers();

4. Procedure dengan Input Parameters

DELIMITER //

CREATE PROCEDURE GetUserByEmail(IN user_email VARCHAR(100))
BEGIN
    SELECT * FROM users WHERE email = user_email;
END //

DELIMITER ;

-- Cara panggil
CALL GetUserByEmail('john@example.com');

5. Procedure dengan Output Parameters

DELIMITER //

CREATE PROCEDURE GetUserCountByStatus(
    IN status_filter VARCHAR(20),
    OUT total_count INT
)
BEGIN
    SELECT COUNT(*) INTO total_count 
    FROM users 
    WHERE status = status_filter;
END //

DELIMITER ;

-- Cara panggil
SET @count = 0;
CALL GetUserCountByStatus('active', @count);
SELECT @count AS active_users_count;

Variabel dan Control Flow dalam Stored Procedure

6. Deklarasi dan Penggunaan Variables

DELIMITER //

CREATE PROCEDURE CalculateOrderTotal(IN order_id INT)
BEGIN
    DECLARE subtotal DECIMAL(10,2);
    DECLARE tax_rate DECIMAL(5,2) DEFAULT 0.10;
    DECLARE total_amount DECIMAL(10,2);
    
    -- Hitung subtotal
    SELECT SUM(quantity * unit_price) INTO subtotal
    FROM order_items WHERE order_id = order_id;
    
    -- Hitung total dengan pajak
    SET total_amount = subtotal + (subtotal * tax_rate);
    
    SELECT subtotal, tax_rate, total_amount;
END //

DELIMITER ;

7. Conditional Statements (IF-ELSE)

DELIMITER //

CREATE PROCEDURE CheckUserMembership(IN user_id INT)
BEGIN
    DECLARE user_type VARCHAR(20);
    DECLARE purchase_count INT;
    
    -- Hitung total pembelian user
    SELECT COUNT(*) INTO purchase_count
    FROM orders WHERE user_id = user_id;
    
    -- Tentukan tipe user berdasarkan purchase count
    IF purchase_count >= 10 THEN
        SET user_type = 'VIP';
    ELSEIF purchase_count >= 5 THEN
        SET user_type = 'Regular';
    ELSE
        SET user_type = 'New';
    END IF;
    
    -- Update user type
    UPDATE users SET membership_type = user_type WHERE id = user_id;
    
    SELECT user_type AS membership_status;
END //

DELIMITER ;

8. CASE Statement untuk Multiple Conditions

DELIMITER //

CREATE PROCEDURE GetUserDiscount(IN user_id INT, OUT discount_rate DECIMAL(5,2))
BEGIN
    DECLARE user_rank VARCHAR(20);
    
    SELECT membership_type INTO user_rank FROM users WHERE id = user_id;
    
    CASE user_rank
        WHEN 'VIP' THEN SET discount_rate = 0.20;
        WHEN 'Regular' THEN SET discount_rate = 0.10;
        WHEN 'New' THEN SET discount_rate = 0.05;
        ELSE SET discount_rate = 0.00;
    END CASE;
END //

DELIMITER ;

Looping dan Iteration dalam Stored Procedure

9. WHILE Loop

DELIMITER //

CREATE PROCEDURE GenerateTestUsers(IN count INT)
BEGIN
    DECLARE i INT DEFAULT 1;
    DECLARE user_email VARCHAR(100);
    
    WHILE i <= count DO
        SET user_email = CONCAT('testuser', i, '@example.com');
        
        INSERT INTO users (email, name, created_at) 
        VALUES (user_email, CONCAT('Test User ', i), NOW());
        
        SET i = i + 1;
    END WHILE;
    
    SELECT CONCAT('Generated ', count, ' test users') AS result;
END //

DELIMITER ;

10. REPEAT Loop

DELIMITER //

CREATE PROCEDURE ProcessBatchOrders()
BEGIN
    DECLARE processed_count INT DEFAULT 0;
    DECLARE total_orders INT;
    
    SELECT COUNT(*) INTO total_orders FROM orders WHERE status = 'pending';
    
    REPEAT
        -- Process one order
        UPDATE orders 
        SET status = 'processing' 
        WHERE status = 'pending' 
        LIMIT 1;
        
        SET processed_count = processed_count + 1;
        
        -- Small delay untuk avoid overload
        DO SLEEP(0.1);
        
    UNTIL processed_count >= 10 OR processed_count >= total_orders
    END REPEAT;
    
    SELECT CONCAT('Processed ', processed_count, ' orders') AS result;
END //

DELIMITER ;

Error Handling dan Transaction Management

11. Basic Error Handling dengan HANDLER

DELIMITER //

CREATE PROCEDURE SafeUserDelete(IN user_id INT)
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SELECT 'Error: User deletion failed' AS result;
    END;
    
    START TRANSACTION;
    
    -- Delete user data dari multiple tables
    DELETE FROM user_sessions WHERE user_id = user_id;
    DELETE FROM user_preferences WHERE user_id = user_id;
    DELETE FROM users WHERE id = user_id;
    
    COMMIT;
    
    SELECT 'User deleted successfully' AS result;
END //

DELIMITER ;

12. Advanced Error Handling dengan Condition Handlers

DELIMITER //

CREATE PROCEDURE ProcessPayment(
    IN order_id INT,
    IN payment_amount DECIMAL(10,2)
)
BEGIN
    DECLARE order_total DECIMAL(10,2);
    DECLARE insufficient_funds CONDITION FOR SQLSTATE '45000';
    
    -- Handler untuk custom error
    DECLARE EXIT HANDLER FOR insufficient_funds
    BEGIN
        ROLLBACK;
        SELECT 'Error: Insufficient payment amount' AS result;
    END;
    
    -- Handler untuk duplicate entry
    DECLARE EXIT HANDLER FOR 1062
    BEGIN
        ROLLBACK;
        SELECT 'Error: Duplicate payment detected' AS result;
    END;
    
    START TRANSACTION;
    
    -- Get order total
    SELECT total_amount INTO order_total FROM orders WHERE id = order_id;
    
    -- Validate payment amount
    IF payment_amount < order_total THEN
        SIGNAL insufficient_funds;
    END IF;
    
    -- Process payment
    INSERT INTO payments (order_id, amount, payment_date)
    VALUES (order_id, payment_amount, NOW());
    
    -- Update order status
    UPDATE orders SET status = 'paid' WHERE id = order_id;
    
    COMMIT;
    
    SELECT 'Payment processed successfully' AS result;
END //

DELIMITER ;

Real-World Use Cases dan Examples

13. E-commerce: Complete Order Processing

DELIMITER //

CREATE PROCEDURE ProcessCompleteOrder(
    IN p_user_id INT,
    IN p_shipping_address TEXT,
    IN p_order_items JSON
)
BEGIN
    DECLARE v_order_id INT;
    DECLARE v_total_amount DECIMAL(10,2) DEFAULT 0;
    DECLARE i INT DEFAULT 0;
    DECLARE item_count INT;
    DECLARE v_product_id INT;
    DECLARE v_quantity INT;
    DECLARE v_unit_price DECIMAL(10,2);
    
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        RESIGNAL;
    END;
    
    START TRANSACTION;
    
    -- Validate and parse order items
    SET item_count = JSON_LENGTH(p_order_items);
    
    IF item_count = 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'No order items provided';
    END IF;
    
    -- Create order
    INSERT INTO orders (user_id, shipping_address, total_amount, status)
    VALUES (p_user_id, p_shipping_address, 0, 'pending');
    
    SET v_order_id = LAST_INSERT_ID();
    
    -- Process each order item
    WHILE i < item_count DO
        SET v_product_id = JSON_EXTRACT(p_order_items, CONCAT('$[', i, '].product_id'));
        SET v_quantity = JSON_EXTRACT(p_order_items, CONCAT('$[', i, '].quantity'));
        
        -- Get product price and validate stock
        SELECT price, stock_quantity INTO v_unit_price, @stock 
        FROM products WHERE id = v_product_id FOR UPDATE;
        
        IF @stock < v_quantity THEN
            SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Insufficient stock';
        END IF;
        
        -- Calculate item total
        SET v_total_amount = v_total_amount + (v_quantity * v_unit_price);
        
        -- Add order item
        INSERT INTO order_items (order_id, product_id, quantity, unit_price)
        VALUES (v_order_id, v_product_id, v_quantity, v_unit_price);
        
        -- Update product stock
        UPDATE products 
        SET stock_quantity = stock_quantity - v_quantity 
        WHERE id = v_product_id;
        
        SET i = i + 1;
    END WHILE;
    
    -- Update order total
    UPDATE orders SET total_amount = v_total_amount WHERE id = v_order_id;
    
    COMMIT;
    
    SELECT v_order_id AS order_id, v_total_amount AS total_amount;
END //

DELIMITER ;

14. Reporting: Monthly Sales Report

DELIMITER //

CREATE PROCEDURE GenerateMonthlySalesReport(
    IN report_year INT,
    IN report_month INT
)
BEGIN
    DECLARE start_date DATE;
    DECLARE end_date DATE;
    
    -- Calculate date range
    SET start_date = DATE(CONCAT(report_year, '-', report_month, '-01'));
    SET end_date = LAST_DAY(start_date);
    
    -- Create temporary table for report data
    CREATE TEMPORARY TABLE IF NOT EXISTS monthly_sales_report (
        product_category VARCHAR(100),
        total_quantity INT,
        total_revenue DECIMAL(12,2),
        avg_price DECIMAL(10,2)
    );
    
    -- Truncate existing data
    TRUNCATE TABLE monthly_sales_report;
    
    -- Insert report data
    INSERT INTO monthly_sales_report
    SELECT 
        c.name AS product_category,
        SUM(oi.quantity) AS total_quantity,
        SUM(oi.quantity * oi.unit_price) AS total_revenue,
        AVG(oi.unit_price) AS avg_price
    FROM order_items oi
    JOIN orders o ON oi.order_id = o.id
    JOIN products p ON oi.product_id = p.id
    JOIN categories c ON p.category_id = c.id
    WHERE o.status = 'completed'
        AND o.order_date BETWEEN start_date AND end_date
    GROUP BY c.name
    ORDER BY total_revenue DESC;
    
    -- Return report data
    SELECT * FROM monthly_sales_report;
    
    -- Cleanup
    DROP TEMPORARY TABLE monthly_sales_report;
END //

DELIMITER ;

Best Practices Stored Procedure Development

15. Naming Convention

-- ✅ Good naming conventions
CREATE PROCEDURE sp_Users_GetByEmail -- Prefix sp_ untuk stored procedure
CREATE PROCEDURE CalculateOrderTotal -- Clear, descriptive names
CREATE PROCEDURE Report_MonthlySales -- Consistent naming pattern

-- ❌ Poor naming
CREATE PROCEDURE proc1 -- Tidak descriptive
CREATE PROCEDURE get_data -- Terlalu vague
CREATE PROCEDURE CalculateTotal -- Tidak jelas total apa

16. Parameter Validation

CREATE PROCEDURE GetUserOrders(
    IN p_user_id INT,
    IN p_limit INT
)
BEGIN
    -- Validate parameters
    IF p_user_id IS NULL OR p_user_id <= 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Invalid user ID';
    END IF;
    
    IF p_limit IS NULL OR p_limit <= 0 THEN SET p_limit = 10; -- Default value END IF; IF p_limit > 100 THEN
        SET p_limit = 100; -- Maximum limit
    END IF;
    
    -- Main logic
    SELECT * FROM orders 
    WHERE user_id = p_user_id 
    ORDER BY order_date DESC 
    LIMIT p_limit;
END

17. Commenting dan Documentation

DELIMITER //

/**
 * PROCEDURE: ProcessUserRegistration
 * DESCRIPTION: Handles complete user registration process including
 *              validation, profile creation, and welcome email
 * PARAMETERS:
 *   - p_email: User email address
 *   - p_password: User password (plain text, will be hashed)
 *   - p_full_name: User's full name
 * RETURNS: New user ID
 * AUTHOR: Database Team
 * CREATED: 2024-01-01
 * VERSION: 1.0
 */
CREATE PROCEDURE ProcessUserRegistration(
    IN p_email VARCHAR(100),
    IN p_password VARCHAR(255),
    IN p_full_name VARCHAR(100),
    OUT p_user_id INT
)
BEGIN
    -- Procedure body here
END //

DELIMITER ;

Advanced Techniques dan Optimization

18. Dynamic SQL dalam Stored Procedure

DELIMITER //

CREATE PROCEDURE DynamicSearch(
    IN p_table_name VARCHAR(50),
    IN p_search_column VARCHAR(50),
    IN p_search_value VARCHAR(100)
)
BEGIN
    DECLARE v_sql_query TEXT;
    
    -- Validate table and column names untuk prevent SQL injection
    IF p_table_name NOT IN ('users', 'products', 'orders') THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Invalid table name';
    END IF;
    
    -- Build dynamic SQL
    SET @v_sql_query = CONCAT(
        'SELECT * FROM ', p_table_name,
        ' WHERE ', p_search_column, ' LIKE ?'
    );
    
    -- Prepare and execute dynamic SQL
    PREPARE stmt FROM @v_sql_query;
    SET @search_pattern = CONCAT('%', p_search_value, '%');
    EXECUTE stmt USING @search_pattern;
    DEALLOCATE PREPARE stmt;
END //

DELIMITER ;

19. Cursor untuk Row-by-Row Processing

DELIMITER //

CREATE PROCEDURE UpdateProductPrices(IN increase_percent DECIMAL(5,2))
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE v_product_id INT;
    DECLARE v_current_price DECIMAL(10,2);
    DECLARE v_new_price DECIMAL(10,2);
    
    DECLARE product_cursor CURSOR FOR 
        SELECT id, price FROM products WHERE category_id = 1;
    
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    
    OPEN product_cursor;
    
    price_loop: LOOP
        FETCH product_cursor INTO v_product_id, v_current_price;
        IF done THEN
            LEAVE price_loop;
        END IF;
        
        -- Calculate new price
        SET v_new_price = v_current_price * (1 + increase_percent / 100);
        
        -- Update product price
        UPDATE products SET price = v_new_price WHERE id = v_product_id;
        
    END LOOP;
    
    CLOSE product_cursor;
END //

DELIMITER ;

Management dan Maintenance Stored Procedure

20. Melihat List Stored Procedures

-- Show all procedures in database
SHOW PROCEDURE STATUS WHERE Db = DATABASE();

-- Show procedure creation code
SHOW CREATE PROCEDURE GetUserByEmail;

-- Search procedures by name
SELECT ROUTINE_NAME 
FROM information_schema.ROUTINES 
WHERE ROUTINE_TYPE = 'PROCEDURE' 
AND ROUTINE_NAME LIKE '%user%';

21. Modifying dan Dropping Procedures

-- Modify procedure (harus DROP dulu)
DROP PROCEDURE IF EXISTS GetUserByEmail;

CREATE PROCEDURE GetUserByEmail(IN user_email VARCHAR(100))
BEGIN
    -- New implementation
    SELECT * FROM users WHERE email = user_email AND status = 'active';
END

-- Check if procedure exists sebelum create
DROP PROCEDURE IF EXISTS MyProcedure;
CREATE PROCEDURE MyProcedure() BEGIN ... END

Debugging dan Troubleshooting

22. Debugging Techniques

CREATE PROCEDURE DebugExample()
BEGIN
    DECLARE debug_var INT DEFAULT 0;
    
    -- Debug output menggunakan SELECT
    SELECT 'Starting procedure' AS debug_info;
    
    SET debug_var = 10;
    SELECT debug_var AS current_value;
    
    -- Atau menggunakan temporary table
    CREATE TEMPORARY TABLE debug_log (
        log_time TIMESTAMP,
        message VARCHAR(255)
    );
    
    INSERT INTO debug_log VALUES (NOW(), 'Procedure started');
    -- ... more code
    INSERT INTO debug_log VALUES (NOW(), 'Procedure completed');
    
    SELECT * FROM debug_log;
    DROP TEMPORARY TABLE debug_log;
END

Security Considerations

23. SQL Injection Prevention

-- ❌ Vulnerable to SQL injection
CREATE PROCEDURE UnsafeSearch(IN search_term VARCHAR(100))
BEGIN
    SET @query = CONCAT('SELECT * FROM users WHERE name LIKE ''%', search_term, '%''');
    PREPARE stmt FROM @query;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END

-- ✅ Safe parameterized approach
CREATE PROCEDURE SafeSearch(IN search_term VARCHAR(100))
BEGIN
    SELECT * FROM users WHERE name LIKE CONCAT('%', search_term, '%');
END

Performance Optimization

24. Index Optimization untuk Stored Procedures

-- Pastikan columns yang sering di-query dalam procedures ter-index
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_orders_user_date ON orders(user_id, order_date);
CREATE INDEX idx_products_category_price ON products(category_id, price);

Kesimpulan: Stored Procedure Mastery untuk Database yang Lebih Powerful

Dengan menguasai stored procedure, kamu bisa membangun database yang lebih intelligent, secure, dan efficient. Yang sudah kita pelajari:

✅ Fundamental syntax dan structure
✅ Parameter handling dan variable management
✅ Control flow dengan conditional statements dan loops
✅ Error handling dan transaction management
✅ Real-world use cases dan best practices
✅ Advanced techniques dan optimization

Stored procedure adalah investasi jangka panjang untuk architecture database-mu. Mulailah dengan procedure sederhana, lalu secara bertahap bangun menuju complex business logic.

Sekarang waktunya practice! Convert salah satu complex query di aplikasimu menjadi stored procedure, dan measure performance improvement-nya. Happy coding! 🚀

“Stored procedures turn your database from a passive data storage into an active business rules engine.” – Database Architect