MySQL Stored Procedures dan Functions: Panduan Lengkap dengan Contoh Praktis
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
| Aspek | Stored Procedure | Function |
|---|---|---|
| Return Value | Bisa multiple via OUT params | Single return value |
| Call Method | CALL statement | Dari SELECT/SET |
| Transaction Control | Bisa COMMIT/ROLLBACK | Tidak bisa |
| DML Operations | Bisa INSERT/UPDATE/DELETE | Hanya SELECT (kecuali di log tables) |
| Use Case | Complex operations, transactions | Calculations, lookups |
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.
Artikel Terkait
Link Postingan: https://www.tirinfo.com/mysql-stored-procedures-functions-panduan-lengkap/