Salin dan Bagikan

MySQL Stored Procedures Database Programming

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.

MySQL Stored Procedures dan Functions: Panduan Lengkap dengan Contoh Praktis

MySQL Stored Procedures dan Functions: Panduan Lengkap dengan Contoh Praktis

Stored Procedures vs Functions

Perbandingan

Kapan Menggunakan?

Basic Stored Procedure

1. Syntax Dasar

2. Simple Procedure

3. Procedure dengan Multiple Parameters

Advanced Stored Procedures

1. Procedure dengan OUT Parameters

2. Procedure dengan Transaction

3. Procedure dengan Dynamic SQL

Stored Functions

1. Simple Function

2. Function dengan Conditional Logic

3. Function dengan CURSOR

Error Handling

1. Exception Handlers

2. Custom Error Raising

Best Practices

1. Naming Conventions

2. Security

3. Documentation

4. Testing

Management Commands

Kesimpulan

Artikel Terkait