DELIMITER $$

CREATE PROCEDURE sp_advanced_demo()
BEGIN
    -- 局部变量声明
    DECLARE v_count INT DEFAULT 0;
    DECLARE v_name VARCHAR(50);
    DECLARE v_salary DECIMAL(10,2);
    DECLARE v_done INT DEFAULT FALSE;

    -- 游标结束标志
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done = TRUE;

    -- 赋值方式1：SET
    SET v_count = 100;

    -- 赋值方式2：SELECT INTO
    SELECT name, salary INTO v_name, v_salary
    FROM employee WHERE id = 1;

    -- 输出
    SELECT v_name, v_salary, v_count;
END$$

DELIMITER ;

DELIMITER $$

CREATE PROCEDURE sp_flow_control(IN p_score INT)
BEGIN
    DECLARE v_level VARCHAR(20);

    -- IF ... ELSEIF ... ELSE
    IF p_score >= 90 THEN
        SET v_level = '优秀';
    ELSEIF p_score >= 80 THEN
        SET v_level = '良好';
    ELSEIF p_score >= 60 THEN
        SET v_level = '及格';
    ELSE
        SET v_level = '不及格';
    END IF;

    SELECT v_level AS score_level;

    -- CASE 分支
    CASE
        WHEN p_score >= 90 THEN SELECT 'A' AS grade;
        WHEN p_score >= 80 THEN SELECT 'B' AS grade;
        WHEN p_score >= 70 THEN SELECT 'C' AS grade;
        ELSE SELECT 'D' AS grade;
    END CASE;
END$$

DELIMITER ;

DELIMITER $$

CREATE PROCEDURE sp_loop_demo(IN p_count INT)
BEGIN
    DECLARE i INT DEFAULT 1;
    DECLARE result INT DEFAULT 0;

    -- WHILE 循环
    WHILE i <= p_count DO
        SET result = result + i;
        SET i = i + 1;
    END WHILE;

    SELECT result AS sum_result;

    -- REPEAT 循环（类似 do-while）
    SET i = 1;
    SET result = 0;

    REPEAT
        SET result = result + i;
        SET i = i + 1;
    UNTIL i > p_count END REPEAT;

    SELECT result AS sum_result2;
END$$

DELIMITER ;

DELIMITER $$

-- 使用游标逐行处理数据
CREATE PROCEDURE sp_calc_bonus()
BEGIN
    DECLARE v_done INT DEFAULT FALSE;
    DECLARE v_emp_id INT;
    DECLARE v_salary DECIMAL(10,2);
    DECLARE v_bonus DECIMAL(10,2);

    -- 声明游标
    DECLARE cur CURSOR FOR
        SELECT id, salary FROM employee WHERE dept_id = 1;

    -- 声明继续处理器
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done = TRUE;

    -- 创建临时表存储结果
    CREATE TEMPORARY TABLE IF NOT EXISTS tmp_bonus (
        emp_id INT,
        salary DECIMAL(10,2),
        bonus DECIMAL(10,2)
    );

    -- 打开游标
    OPEN cur;

    -- 循环遍历
    read_loop: LOOP
        FETCH cur INTO v_emp_id, v_salary;

        IF v_done THEN
            LEAVE read_loop;
        END IF;

        -- 计算奖金（工资的 20%）
        SET v_bonus = ROUND(v_salary * 0.2, 2);

        INSERT INTO tmp_bonus (emp_id, salary, bonus)
        VALUES (v_emp_id, v_salary, v_bonus);
    END LOOP;

    -- 关闭游标
    CLOSE cur;

    -- 返回结果
    SELECT * FROM tmp_bonus;

    -- 清理临时表
    DROP TEMPORARY TABLE tmp_bonus;
END$$

DELIMITER ;

DELIMITER $$

CREATE PROCEDURE sp_safe_transfer(
    IN from_id INT,
    IN to_id INT,
    IN amount DECIMAL(12,2)
)
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        -- 发生异常时回滚
        ROLLBACK;
        SELECT '转账失败，已回滚' AS msg;
    END;

    -- 声明处理警告
    DECLARE EXIT HANDLER FOR SQLWARNING
    BEGIN
        ROLLBACK;
        SELECT '发生警告，已回滚' AS msg;
    END;

    START TRANSACTION;

    UPDATE account SET balance = balance - amount WHERE id = from_id;
    UPDATE account SET balance = balance + amount WHERE id = to_id;

    COMMIT;
    SELECT '转账成功' AS msg;
END$$

DELIMITER ;

-- 创建触发器
CREATE TRIGGER trigger_name
{BEFORE | AFTER} {INSERT | UPDATE | DELETE}
ON table_name
FOR EACH ROW
BEGIN
    -- 触发器逻辑
    -- OLD.column：修改前的值
    -- NEW.column：修改后的值
END;

-- 查看触发器
SHOW TRIGGERS;

-- 删除触发器
DROP TRIGGER IF EXISTS trigger_name;

-- 记录新增用户日志
DELIMITER $$

CREATE TRIGGER trg_user_after_insert
AFTER INSERT ON users
FOR EACH ROW
BEGIN
    INSERT INTO user_log(user_id, action, old_value, new_value, create_time)
    VALUES (NEW.id, 'INSERT', NULL,
            CONCAT('{"name":"', NEW.name, '","email":"', NEW.email, '"}'),
            NOW());
END$$

DELIMITER ;

-- 记录工资变更
DELIMITER $$

CREATE TRIGGER trg_salary_before_update
BEFORE UPDATE ON employee
FOR EACH ROW
BEGIN
    IF OLD.salary != NEW.salary THEN
        INSERT INTO salary_log(emp_id, old_salary, new_salary, change_time)
        VALUES (OLD.id, OLD.salary, NEW.salary, NOW());
    END IF;
END$$

DELIMITER ;

-- 删除用户时备份到回收站
DELIMITER $$

CREATE TRIGGER trg_user_before_delete
BEFORE DELETE ON users
FOR EACH ROW
BEGIN
    INSERT INTO users_deleted(id, name, email, deleted_at)
    VALUES (OLD.id, OLD.name, OLD.email, NOW());
END$$

DELIMITER ;

-- 1. 自动记录审计日志
-- 2. 数据完整性校验
-- 3. 冗余字段自动更新（如 order_count）
-- 4. 级联操作

-- 风险1：隐式逻辑，排查问题时容易被忽略
-- 风险2：触发器内操作多会拖慢 DML 性能
-- 风险3：嵌套触发器可能导致不可预知的行为
-- 风险4：无法在触发器中使用动态 SQL

-- 生产建议：触发器尽量少用，审计日志等场景建议在应用层实现

-- 查看事件调度器状态
SHOW VARIABLES LIKE 'event_scheduler';  -- 默认 OFF

-- 开启
SET GLOBAL event_scheduler = ON;

-- my.cnf 持久化
[mysqld]
event_scheduler = ON

-- 一次性事件：明天凌晨 3 点执行
CREATE EVENT ev_clean_temp_data
ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 DAY
DO
    DELETE FROM temp_data WHERE create_time < DATE_SUB(NOW(), INTERVAL 7 DAY);

-- 重复事件：每天凌晨 3 点执行
CREATE EVENT ev_daily_stats
ON SCHEDULE EVERY 1 DAY
STARTS '2024-01-01 03:00:00'
DO
BEGIN
    -- 插入每日统计
    INSERT INTO daily_stats(stats_date, user_count, order_count, revenue)
    SELECT
        CURDATE() - INTERVAL 1 DAY,
        COUNT(DISTINCT user_id),
        COUNT(*),
        SUM(amount)
    FROM orders
    WHERE DATE(create_time) = CURDATE() - INTERVAL 1 DAY;
END;

-- 带过期的事件：从 2024-01-01 到 2024-12-31，每天执行
CREATE EVENT ev_quarterly_cleanup
ON SCHEDULE EVERY 1 DAY
STARTS '2024-01-01 00:00:00'
ENDS '2024-12-31 23:59:59'
DO
    DELETE FROM logs WHERE create_time < DATE_SUB(NOW(), INTERVAL 90 DAY);

-- 查看事件
SHOW EVENTS;
-- 或
SELECT * FROM information_schema.EVENTS;

-- 修改事件
ALTER EVENT ev_daily_stats
ON SCHEDULE EVERY 6 HOUR
DO
    INSERT INTO daily_stats(...);

-- 启用/禁用事件
ALTER EVENT ev_daily_stats ENABLE;
ALTER EVENT ev_daily_stats DISABLE;

-- 删除事件
DROP EVENT IF EXISTS ev_daily_stats;

-- 需求：每个月 1 日凌晨 2 点，将 6 个月前已完成的订单归档到 orders_archive 表
DELIMITER $$

CREATE EVENT ev_archive_orders
ON SCHEDULE EVERY 1 MONTH
STARTS '2024-01-01 02:00:00'
DO
BEGIN
    DECLARE v_archived_count INT DEFAULT 0;

    -- 插入归档表
    INSERT INTO orders_archive
    SELECT * FROM orders
    WHERE status = '已完成'
      AND create_time < DATE_SUB(NOW(), INTERVAL 6 MONTH)
      AND is_archived = 0;

    SET v_archived_count = ROW_COUNT();

    -- 标记原表已归档
    UPDATE orders
    SET is_archived = 1
    WHERE status = '已完成'
      AND create_time < DATE_SUB(NOW(), INTERVAL 6 MONTH)
      AND is_archived = 0;

    -- 记录归档日志
    INSERT INTO archive_log(archive_time, table_name, record_count)
    VALUES (NOW(), 'orders', v_archived_count);
END$$

DELIMITER ;

-- 完整的薪资管理系统：触发器 + 存储过程 + 事件

-- 1. 薪资变更触发器：自动记录日志
DELIMITER $$

CREATE TRIGGER trg_salary_change
AFTER UPDATE ON employee
FOR EACH ROW
BEGIN
    IF OLD.salary != NEW.salary THEN
        INSERT INTO salary_change_log(
            emp_id, old_salary, new_salary,
            change_percent, operator, change_time
        ) VALUES (
            OLD.id,
            OLD.salary,
            NEW.salary,
            ROUND((NEW.salary - OLD.salary) / OLD.salary * 100, 2),
            USER(),
            NOW()
        );

        -- 如果降薪超过 20%，记录高优先级告警
        IF NEW.salary < OLD.salary * 0.8 THEN
            INSERT INTO alert_log(
                alert_level, alert_type, message
            ) VALUES (
                'HIGH', 'SALARY_DROP',
                CONCAT('员工', OLD.name, '降薪超过20%：', OLD.salary, '→', NEW.salary)
            );
        END IF;
    END IF;
END$$

-- 2. 批量调薪存储过程
CREATE PROCEDURE sp_batch_salary_adjust(
    IN p_dept_id INT,
    IN p_percentage DECIMAL(5,2),
    IN p_reason VARCHAR(200)
)
BEGIN
    DECLARE v_done INT DEFAULT FALSE;
    DECLARE v_emp_id INT;
    DECLARE v_old_salary DECIMAL(10,2);

    DECLARE cur CURSOR FOR
        SELECT id, salary FROM employee
        WHERE dept_id = p_dept_id AND status = 1;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done = TRUE;

    START TRANSACTION;

    OPEN cur;

    adjust_loop: LOOP
        FETCH cur INTO v_emp_id, v_old_salary;
        IF v_done THEN
            LEAVE adjust_loop;
        END IF;

        UPDATE employee
        SET salary = ROUND(v_old_salary * (1 + p_percentage / 100), 2)
        WHERE id = v_emp_id;
    END LOOP;

    CLOSE cur;

    -- 记录调薪批次
    INSERT INTO salary_adjust_batch(
        dept_id, percentage, reason, operator, create_time
    ) VALUES (
        p_dept_id, p_percentage, p_reason, USER(), NOW()
    );

    COMMIT;
    SELECT CONCAT('部门', p_dept_id, '调薪完成，幅度：', p_percentage, '%') AS msg;
END$$

-- 3. 月度薪资统计事件
CREATE EVENT ev_monthly_salary_stats
ON SCHEDULE EVERY 1 MONTH
STARTS '2024-02-01 02:00:00'
DO
BEGIN
    INSERT INTO salary_monthly_stats(
        stats_month, dept_id,
        emp_count, total_salary, avg_salary, max_salary, min_salary
    )
    SELECT
        DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 1 MONTH), '%Y-%m'),
        d.id,
        COUNT(e.id),
        COALESCE(SUM(e.salary), 0),
        COALESCE(ROUND(AVG(e.salary), 2), 0),
        COALESCE(MAX(e.salary), 0),
        COALESCE(MIN(e.salary), 0)
    FROM department d
    LEFT JOIN employee e ON d.id = e.dept_id AND e.status = 1
    GROUP BY d.id, d.dept_name;
END$$

DELIMITER ;