DO SELECT category_name, parent_category_id INTO cat_name, parent_id FROM categories WHERE id = parent_id; SET path = CONCAT(cat_name, ' > ', path); END WHILE; RETURN path; END$$ DELIMITER ; -- ======================================== -- AGGREGATE FUNCTION -- ======================================== DELIMITER $$ CREATE FUNCTION GetCustomerTier(customer_id INT) RETURNS VARCHAR(20) READS SQL DATA BEGIN DECLARE total_spent DECIMAL(10,2); DECLARE tier VARCHAR(20); SELECT SUM(total_amount) INTO total_spent FROM orders WHERE customer_id = customer_id AND status = 'completed'; CASE WHEN total_spent >= 10000 THEN SET tier = 'Platinum'; WHEN total_spent >= 5000 THEN SET tier = 'Gold'; WHEN total_spent >= 1000 THEN SET tier = 'Silver'; WHEN total_spent >= 100 THEN SET tier = 'Bronze'; ELSE SET tier = 'Basic'; END CASE; RETURN tier; END$$ DELIMITER ;

Cursors and Advanced Features

Advanced Stored Procedure Features

-- ========================================
-- CURSOR EXAMPLE
-- ========================================

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 cursor
    DECLARE product_cursor CURSOR FOR
        SELECT product_id, price
        FROM products
        WHERE is_active = TRUE;
    
    -- Declare handler for end of cursor
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    
    -- Open cursor
    OPEN product_cursor;
    
    -- Loop through results
    read_loop: LOOP
        FETCH product_cursor INTO v_product_id, v_current_price;
        
        IF done THEN
            LEAVE read_loop;
        END IF;
        
        -- Calculate new price
        SET v_new_price = v_current_price * (1 + increase_percent / 100);
        
        -- Update product
        UPDATE products
        SET price = v_new_price,
            last_updated = NOW()
        WHERE product_id = v_product_id;
        
        -- Log change
        INSERT INTO price_history (
            product_id, old_price, new_price, 
            change_date, change_reason
        ) VALUES (
            v_product_id, v_current_price, v_new_price,
            NOW(), CONCAT('Price increase: ', increase_percent, '%')
        );
    END LOOP;
    
    -- Close cursor
    CLOSE product_cursor;
    
    -- Return summary
    SELECT 
        COUNT(*) AS products_updated,
        AVG(increase_percent) AS avg_increase
    FROM price_history
    WHERE change_date >= DATE_SUB(NOW(), INTERVAL 1 MINUTE);
END$$

DELIMITER ;

-- ========================================
-- DYNAMIC SQL IN PROCEDURES
-- ========================================

DELIMITER $$

CREATE PROCEDURE DynamicReport(
    IN table_name VARCHAR(64),
    IN column_name VARCHAR(64),
    IN filter_value VARCHAR(100)
)
BEGIN
    DECLARE sql_query TEXT;
    
    -- Build dynamic query
    SET sql_query = CONCAT(
        'SELECT * FROM ',
        table_name,
        ' WHERE ',
        column_name,
        ' = ?'
    );
    
    -- Prepare and execute
    SET @sql = sql_query;
    SET @param = filter_value;
    
    PREPARE stmt FROM @sql;
    EXECUTE stmt USING @param;
    DEALLOCATE PREPARE stmt;
END$$

DELIMITER ;

-- ========================================
-- ERROR HANDLING
-- ========================================

DELIMITER $$

CREATE PROCEDURE SafeDivision(
    IN numerator DECIMAL(10,2),
    IN denominator DECIMAL(10,2),
    OUT result DECIMAL(10,2),
    OUT error_msg VARCHAR(100)
)
BEGIN
    -- Declare handlers
    DECLARE CONTINUE HANDLER FOR SQLSTATE '22012'
    BEGIN
        SET error_msg = 'Division by zero error';
        SET result = NULL;
    END;
    
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
    BEGIN
        GET DIAGNOSTICS CONDITION 1
            error_msg = MESSAGE_TEXT;
        SET result = NULL;
    END;
    
    -- Attempt division
    IF denominator = 0 THEN
        SIGNAL SQLSTATE '22012';
    ELSE
        SET result = numerator / denominator;
        SET error_msg = 'Success';
    END IF;
END$$

DELIMITER ;

-- ========================================
-- SCHEDULED PROCEDURES (Events)
-- ========================================

-- Enable event scheduler
SET GLOBAL event_scheduler = ON;

-- Create scheduled event
CREATE EVENT daily_cleanup
ON SCHEDULE EVERY 1 DAY
STARTS '2024-01-01 02:00:00'
DO
BEGIN
    -- Call cleanup procedure
    CALL CleanupOldData();
    
    -- Log execution
    INSERT INTO event_log (event_name, execution_time)
    VALUES ('daily_cleanup', NOW());
END;

-- ========================================
-- PROCEDURE MANAGEMENT
-- ========================================

-- List all procedures
SELECT 
    ROUTINE_NAME,
    ROUTINE_TYPE,
    CREATED,
    LAST_ALTERED,
    DEFINER
FROM information_schema.ROUTINES
WHERE ROUTINE_SCHEMA = DATABASE()
    AND ROUTINE_TYPE = 'PROCEDURE';

-- Show procedure definition
SHOW CREATE PROCEDURE ProcessOrder;

-- Drop procedure
DROP PROCEDURE IF EXISTS OldProcedure;

-- Grant execute permission
GRANT EXECUTE ON PROCEDURE mydb.ProcessOrder 
TO 'app_user'@'localhost';

Additional Resources