存储过程

CREATE PROCEDURE 过程名 (参数列表)
BEGIN
    过程体（SQL 语句 + 流程控制）
END

DELIMITER //

CREATE PROCEDURE sp_hello()
BEGIN
    SELECT 'Hello, World';
END //

DELIMITER ;

-- 查询所有英雄
DELIMITER //
CREATE PROCEDURE sp_get_all_heros()
BEGIN
    SELECT id, name, hp_max FROM heros;
END //
DELIMITER ;

-- 调用
CALL sp_get_all_heros();

-- 查看已定义的过程
SHOW PROCEDURE STATUS WHERE Db = '数据库名';

-- 查看过程定义
SHOW CREATE PROCEDURE sp_get_all_heros;

-- 删除
DROP PROCEDURE IF EXISTS sp_get_all_heros;

-- 根据定位统计英雄数量，通过 OUT 返回
DELIMITER //
CREATE PROCEDURE sp_count_by_role(
    IN p_role VARCHAR(20),
    OUT p_count INT
)
BEGIN
    SELECT COUNT(*) INTO p_count
    FROM heros WHERE role_main = p_role;
END //
DELIMITER ;

-- 调用
CALL sp_count_by_role('战士', @cnt);
SELECT @cnt;   -- 取出 OUT 参数值

DELIMITER //
CREATE PROCEDURE sp_add(INOUT p_num INT)
BEGIN
    SET p_num = p_num + 10;
END //
DELIMITER ;

SET @n = 5;
CALL sp_add(@n);
SELECT @n;   -- 15

DELIMITER //
CREATE PROCEDURE sp_calc()
BEGIN
    DECLARE v_total INT DEFAULT 0;     -- 声明局部变量，默认 0
    DECLARE v_avg DECIMAL(10,2);

    SELECT SUM(hp_max), AVG(hp_max)
      INTO v_total, v_avg               -- INTO 赋值
    FROM heros;

    SELECT v_total AS total_hp, v_avg AS avg_hp;
END //
DELIMITER ;

SET @x = 10;
SELECT @x;

DELIMITER //
CREATE PROCEDURE sp_check(IN p_hp INT)
BEGIN
    IF p_hp > 7000 THEN
        SELECT '高血量';
    ELSEIF p_hp > 5000 THEN
        SELECT '中血量';
    ELSE
        SELECT '低血量';
    END IF;
END //
DELIMITER ;

DELIMITER //
CREATE PROCEDURE sp_role_type(IN p_role VARCHAR(20))
BEGIN
    CASE p_role
        WHEN '法师' THEN SELECT '远程/法术';
        WHEN '射手' THEN SELECT '远程/物理';
        WHEN '坦克' THEN SELECT '前排/肉盾';
        ELSE SELECT '其他';
    END CASE;
END //
DELIMITER ;

-- 循环插入数字
DELIMITER //
CREATE PROCEDURE sp_fill(IN p_n INT)
BEGIN
    DECLARE i INT DEFAULT 1;
    WHILE i <= p_n DO
        INSERT INTO numbers (val) VALUES (i);
        SET i = i + 1;
    END WHILE;
END //
DELIMITER ;

DELIMITER //
CREATE PROCEDURE sp_iterate()
BEGIN
    DECLARE v_name VARCHAR(50);
    DECLARE done INT DEFAULT 0;
    -- 声明游标：绑定查询
    DECLARE cur CURSOR FOR SELECT name FROM heros WHERE hp_max > 6000;
    -- 声明 NOT FOUND 处理器：遍历完置 done=1
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

    OPEN cur;
    loop_label: LOOP
        FETCH cur INTO v_name;          -- 取一行
        IF done THEN
            LEAVE loop_label;           -- 遍历完退出
        END IF;
        SELECT v_name;                   -- 处理该行
    END LOOP;
    CLOSE cur;
END //
DELIMITER ;

DELIMITER //
CREATE PROCEDURE sp_transfer(
    IN p_from INT, IN p_to INT, IN p_amt DECIMAL(10,2)
)
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;                        -- 出错回滚
        SELECT '转账失败';
    END;

    START TRANSACTION;
    UPDATE accounts SET balance = balance - p_amt WHERE id = p_from;
    UPDATE accounts SET balance = balance + p_amt WHERE id = p_to;
    COMMIT;
    SELECT '转账成功';
END //
DELIMITER ;