MySQL Stored Procedures dan Functions: Panduan Lengkap dengan Contoh Praktis

Stored procedures dan functions adalah fitur powerful untuk encapsulate business logic di database layer. Setelah mengimplementasikan ratusan stored procedures untuk client, saya akan berbagi patterns dan best practices yang terbukti efektif.

Stored Procedures vs Functions

Perbandingan

Kapan Menggunakan?

Stored Procedure:
– Batch operations
– Multi-step transactions
– Complex business logic
– Administrative tasks

Function:
– Calculations
– Data transformations
– Lookup operations
– Reusable dalam queries

Basic Stored Procedure

1. Syntax Dasar

DELIMITER $$
CREATE PROCEDURE procedure_name(
[IN | OUT | INOUT] param_name datatype,
...
)
BEGIN
-- Procedure body
DECLARE local_var datatype;
-- SQL statements
SELECT ... FROM ...;
INSERT INTO ... VALUES ...;
END$$
DELIMITER ;
2. Simple Procedure
DELIMITER $$
CREATE PROCEDURE GetCustomerById(
IN p_customer_id INT
)
BEGIN
SELECT * FROM customers WHERE id = p_customer_id;
END$$
DELIMITER ;
-- Call procedure
CALL GetCustomerById(123);
3. Procedure dengan Multiple Parameters
DELIMITER $$
CREATE PROCEDURE GetOrdersByDateRange(
IN p_start_date DATE,
IN p_end_date DATE,
IN p_status VARCHAR(20)
)
BEGIN
SELECT 
o.id,
o.order_date,
c.name as customer_name,
o.total_amount,
o.status
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE o.order_date BETWEEN p_start_date AND p_end_date
AND o.status = p_status
ORDER BY o.order_date DESC;
END$$
DELIMITER ;
-- Call
CALL GetOrdersByDateRange('2024-01-01', '2024-01-31', 'completed');
Advanced Stored Procedures
1. Procedure dengan OUT Parameters
DELIMITER $$
CREATE PROCEDURE GetOrderStatistics(
IN p_customer_id INT,
OUT p_total_orders INT,
OUT p_total_amount DECIMAL(12,2),
OUT p_avg_amount DECIMAL(10,2)
)
BEGIN
SELECT 
COUNT(*),
COALESCE(SUM(total_amount), 0),
COALESCE(AVG(total_amount), 0)
INTO 
p_total_orders,
p_total_amount,
p_avg_amount
FROM orders
WHERE customer_id = p_customer_id;
END$$
DELIMITER ;
-- Call dengan OUT params
CALL GetOrderStatistics(123, @total, @amount, @avg);
-- Get results
SELECT @total as total_orders, @amount as total_amount, @avg as avg_amount;
2. Procedure dengan Transaction
DELIMITER $$
CREATE PROCEDURE ProcessOrder(
IN p_customer_id INT,
IN p_product_id INT,
IN p_quantity INT,
IN p_unit_price DECIMAL(10,2),
OUT p_order_id INT,
OUT p_status VARCHAR(50)
)
BEGIN
DECLARE v_stock INT;
DECLARE v_total DECIMAL(10,2);
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
ROLLBACK;
SET p_status = 'ERROR: Transaction failed';
RESIGNAL;
END;
-- Calculate total
SET v_total = p_quantity * p_unit_price;

-- Check stock
SELECT stock_quantity INTO v_stock 
FROM products 
WHERE id = p_product_id;

IF v_stock < p_quantity THEN
    SET p_status = 'ERROR: Insufficient stock';
    SET p_order_id = NULL;
ELSE
    START TRANSACTION;

    -- Insert order
    INSERT INTO orders (customer_id, order_date, total_amount, status)
    VALUES (p_customer_id, NOW(), v_total, 'pending');

    SET p_order_id = LAST_INSERT_ID();

    -- Insert order item
    INSERT INTO order_items (order_id, product_id, quantity, unit_price, total_price)
    VALUES (p_order_id, p_product_id, p_quantity, p_unit_price, v_total);

    -- Update stock
    UPDATE products 
    SET stock_quantity = stock_quantity - p_quantity
    WHERE id = p_product_id;

    COMMIT;

    SET p_status = 'SUCCESS: Order processed';
END IF;
END$$
DELIMITER ;
3. Procedure dengan Dynamic SQL
DELIMITER $$
CREATE PROCEDURE SearchProducts(
IN p_search_term VARCHAR(100),
IN p_category_id INT,
IN p_min_price DECIMAL(10,2),
IN p_max_price DECIMAL(10,2)
)
BEGIN
SET @sql = 'SELECT id, name, category_id, price FROM products WHERE 1=1';
IF p_search_term IS NOT NULL THEN
    SET @sql = CONCAT(@sql, ' AND name LIKE ''%', p_search_term, '%''');
END IF;

IF p_category_id IS NOT NULL THEN
    SET @sql = CONCAT(@sql, ' AND category_id = ', p_category_id);
END IF;

IF p_min_price IS NOT NULL THEN
    SET @sql = CONCAT(@sql, ' AND price >= ', p_min_price);
END IF;

IF p_max_price IS NOT NULL THEN
    SET @sql = CONCAT(@sql, ' AND price <= ', p_max_price);
END IF;

SET @sql = CONCAT(@sql, ' ORDER BY name');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END$$
DELIMITER ;
-- Call
CALL SearchProducts('laptop', NULL, 500.00, 1500.00);
Stored Functions
1. Simple Function
DELIMITER $$
CREATE FUNCTION CalculateDiscount(
p_price DECIMAL(10,2),
p_discount_percent DECIMAL(5,2)
) RETURNS DECIMAL(10,2)
DETERMINISTIC
BEGIN
RETURN p_price - (p_price * p_discount_percent / 100);
END$$
DELIMITER ;
-- Use dalam query
SELECT 
name,
price,
CalculateDiscount(price, 10) as discounted_price
FROM products;
2. Function dengan Conditional Logic
DELIMITER $$
CREATE FUNCTION GetCustomerTier(
p_total_spent DECIMAL(12,2)
) RETURNS VARCHAR(20)
DETERMINISTIC
BEGIN
DECLARE v_tier VARCHAR(20);
IF p_total_spent >= 10000 THEN
    SET v_tier = 'Platinum';
ELSEIF p_total_spent >= 5000 THEN
    SET v_tier = 'Gold';
ELSEIF p_total_spent >= 1000 THEN
    SET v_tier = 'Silver';
ELSE
    SET v_tier = 'Bronze';
END IF;

RETURN v_tier;
END$$
DELIMITER ;
-- Use dalam query
SELECT
c.name,
COALESCE(SUM(o.total_amount), 0) as total_spent,
GetCustomerTier(COALESCE(SUM(o.total_amount), 0)) as tier
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
GROUP BY c.id, c.name;
3. Function dengan CURSOR
DELIMITER $$
CREATE FUNCTION CalculateOrderTotal(
p_order_id INT
) RETURNS DECIMAL(12,2)
READS SQL DATA
BEGIN
DECLARE v_total DECIMAL(12,2) DEFAULT 0;
DECLARE v_item_total DECIMAL(10,2);
DECLARE done INT DEFAULT FALSE;
DECLARE cur CURSOR FOR
    SELECT quantity * unit_price 
    FROM order_items 
    WHERE order_id = p_order_id;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

OPEN cur;

read_loop: LOOP
    FETCH cur INTO v_item_total;

    IF done THEN
        LEAVE read_loop;
    END IF;

    SET v_total = v_total + v_item_total;
END LOOP;

CLOSE cur;

RETURN v_total;
END$$
DELIMITER ;
Error Handling
1. Exception Handlers
DELIMITER $$
CREATE PROCEDURE SafeDeleteCustomer(
IN p_customer_id INT,
OUT p_status VARCHAR(100)
)
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
ROLLBACK;
GET DIAGNOSTICS CONDITION 1
p_status = MESSAGE_TEXT;
SET p_status = CONCAT('ERROR: ', p_status);
END;
DECLARE EXIT HANDLER FOR SQLWARNING
BEGIN
    ROLLBACK;
    SET p_status = 'WARNING: Operation cancelled';
END;

START TRANSACTION;

-- Check if customer has orders
IF EXISTS (SELECT 1 FROM orders WHERE customer_id = p_customer_id) THEN
    SET p_status = 'ERROR: Customer has orders, cannot delete';
ELSE
    DELETE FROM customers WHERE id = p_customer_id;
    COMMIT;
    SET p_status = 'SUCCESS: Customer deleted';
END IF;
END$$
DELIMITER ;
2. Custom Error Raising
DELIMITER $$
CREATE PROCEDURE ValidateOrder(
IN p_customer_id INT,
IN p_amount DECIMAL(10,2)
)
BEGIN
DECLARE v_credit_limit DECIMAL(10,2);
DECLARE v_total_owed DECIMAL(10,2);
SELECT credit_limit INTO v_credit_limit
FROM customers WHERE id = p_customer_id;

SELECT COALESCE(SUM(total_amount), 0) INTO v_total_owed
FROM orders 
WHERE customer_id = p_customer_id AND status = 'pending';

IF v_total_owed + p_amount > v_credit_limit THEN
    SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = 'Credit limit exceeded';
END IF;
END$$
DELIMITER ;
Best Practices
1. Naming Conventions
-- Consistent prefixing
sp_GetCustomerById          -- Stored Procedure prefix: sp_
sf_CalculateDiscount        -- Scalar Function prefix: sf_
tf_GetCustomerOrders        -- Table Function prefix: tf_ (MariaDB 10.3+)
-- Atau berdasarkan operation
usp
 (User Stored Procedure)
udf (User Defined Function)
2. Security
-- Definer security (default)
CREATE PROCEDURE GetSensitiveData()
SQL SECURITY DEFINER
BEGIN
    -- Runs dengan privileges pembuat procedure
END;
-- Invoker security
CREATE PROCEDURE GetUserData()
SQL SECURITY INVOKER
BEGIN
-- Runs dengan privileges caller
SELECT * FROM user_data WHERE user_id = CURRENT_USER();
END;
-- Restrict execution
GRANT EXECUTE ON PROCEDURE mydb.GetCustomerById TO 'app_user'@'%';
3. Documentation
DELIMITER $$
-- =============================================
-- Author:      Database Team
-- Create date: 2026-01-15
-- Description: Calculates monthly sales report
-- Parameters:
--   p_month: Target month (YYYY-MM)
--   p_revenue: OUT - Total revenue
--   p_orders: OUT - Total orders
-- Returns: None (uses OUT params)
-- Example:
--   CALL GetMonthlySales('2024-01', @rev, @ord);
--   SELECT @rev, @ord;
-- =============================================
CREATE PROCEDURE GetMonthlySales(
IN p_month VARCHAR(7),
OUT p_revenue DECIMAL(12,2),
OUT p_orders INT
)
BEGIN
-- Implementation
END$$
DELIMITER ;
4. Testing
-- Test procedure
CALL GetCustomerById(123);
-- Verify results
-- Test dengan invalid input
CALL GetCustomerById(-1);
-- Should handle gracefully
-- Test transaction procedure
CALL ProcessOrder(1, 1, 10, 100.00, @order_id, @status);
SELECT @order_id, @status;
-- Verify: order created, stock updated
Management Commands
-- List all procedures
SHOW PROCEDURE STATUS;
SHOW PROCEDURE STATUS WHERE Db = 'mydb';
-- Show procedure definition
SHOW CREATE PROCEDURE mydb.GetCustomerById;
-- List all functions
SHOW FUNCTION STATUS;
-- Show function definition
SHOW CREATE FUNCTION mydb.CalculateDiscount;
-- Alter procedure (drop dan recreate)
DROP PROCEDURE IF EXISTS GetCustomerById;
-- CREATE PROCEDURE baru
-- Grant execute privilege
GRANT EXECUTE ON PROCEDURE mydb.* TO 'app_user'@'%';
-- Revoke
REVOKE EXECUTE ON PROCEDURE mydb.* FROM 'app_user'@'%';
Kesimpulan
Stored procedures dan functions adalah tools powerful untuk:
– Encapsulate business logic
– Improve performance (reduced network round-trips)
– Enhance security (controlled access)
– Maintain consistency (centralized logic)
Dengan best practices yang tepat, mereka menjadi asset berharga dalam database architecture.