MariaDB存储过程 #

存储过程概念
├── 预编译的SQL语句集合
├── 存储在数据库中
├── 可以接受参数
├── 可以返回结果
└── 可以包含流程控制

存储过程优点
├── 提高性能（预编译、减少网络传输）
├── 代码重用
├── 安全性（权限控制）
├── 减少网络流量
└── 业务逻辑封装

DELIMITER //

CREATE PROCEDURE procedure_name([parameters])
BEGIN
    -- SQL statements
END //

DELIMITER ;

DELIMITER //

CREATE PROCEDURE get_all_users()
BEGIN
    SELECT * FROM users;
END //

DELIMITER ;

-- 调用存储过程
CALL get_all_users();

DELIMITER //

-- IN参数
CREATE PROCEDURE get_user_by_id(IN user_id INT)
BEGIN
    SELECT * FROM users WHERE id = user_id;
END //

DELIMITER ;

CALL get_user_by_id(1);

-- OUT参数
DELIMITER //

CREATE PROCEDURE get_user_count(OUT total INT)
BEGIN
    SELECT COUNT(*) INTO total FROM users;
END //

DELIMITER ;

CALL get_user_count(@count);
SELECT @count;

-- INOUT参数
DELIMITER //

CREATE PROCEDURE double_value(INOUT num INT)
BEGIN
    SET num = num * 2;
END //

DELIMITER ;

SET @value = 10;
CALL double_value(@value);
SELECT @value;  -- 20

DELIMITER //

CREATE PROCEDURE variable_example()
BEGIN
    -- 声明变量
    DECLARE v_name VARCHAR(50);
    DECLARE v_count INT DEFAULT 0;
    DECLARE v_total DECIMAL(10,2);
    
    -- 赋值
    SET v_name = 'John';
    SELECT COUNT(*) INTO v_count FROM users;
    SELECT SUM(balance) INTO v_total FROM users;
    
    -- 使用变量
    SELECT v_name, v_count, v_total;
END //

DELIMITER ;

DELIMITER //

CREATE PROCEDURE user_variable_example()
BEGIN
    -- 用户变量（以@开头）
    SET @user_name = 'John';
    SET @user_count = (SELECT COUNT(*) FROM users);
    
    SELECT @user_name, @user_count;
END //

DELIMITER ;

DELIMITER //

CREATE PROCEDURE system_variable_example()
BEGIN
    -- 查看系统变量
    SELECT @@autocommit;
    SELECT @@max_connections;
    
    -- 设置会话变量
    SET SESSION sql_mode = 'STRICT_ALL_TABLES';
END //

DELIMITER ;

DELIMITER //

CREATE PROCEDURE check_user_status(IN user_id INT)
BEGIN
    DECLARE v_status INT;
    DECLARE v_message VARCHAR(50);
    
    SELECT status INTO v_status FROM users WHERE id = user_id;
    
    IF v_status = 1 THEN
        SET v_message = 'User is active';
    ELSEIF v_status = 0 THEN
        SET v_message = 'User is inactive';
    ELSE
        SET v_message = 'Unknown status';
    END IF;
    
    SELECT v_message AS result;
END //

DELIMITER ;

CALL check_user_status(1);

DELIMITER //

CREATE PROCEDURE get_grade(IN score INT)
BEGIN
    DECLARE v_grade CHAR(1);
    
    CASE 
        WHEN score >= 90 THEN SET v_grade = 'A';
        WHEN score >= 80 THEN SET v_grade = 'B';
        WHEN score >= 70 THEN SET v_grade = 'C';
        WHEN score >= 60 THEN SET v_grade = 'D';
        ELSE SET v_grade = 'F';
    END CASE;
    
    SELECT v_grade AS grade;
END //

DELIMITER ;

-- 另一种形式
DELIMITER //

CREATE PROCEDURE get_status_name(IN status INT)
BEGIN
    DECLARE v_name VARCHAR(20);
    
    CASE status
        WHEN 1 THEN SET v_name = 'Active';
        WHEN 0 THEN SET v_name = 'Inactive';
        ELSE SET v_name = 'Unknown';
    END CASE;
    
    SELECT v_name AS status_name;
END //

DELIMITER ;

DELIMITER //

CREATE PROCEDURE while_example(IN max_count INT)
BEGIN
    DECLARE i INT DEFAULT 0;
    DECLARE result VARCHAR(1000) DEFAULT '';
    
    WHILE i < max_count DO
        SET result = CONCAT(result, i, ' ');
        SET i = i + 1;
    END WHILE;
    
    SELECT result;
END //

DELIMITER ;

CALL while_example(5);  -- 0 1 2 3 4

DELIMITER //

CREATE PROCEDURE repeat_example(IN max_count INT)
BEGIN
    DECLARE i INT DEFAULT 0;
    DECLARE result VARCHAR(1000) DEFAULT '';
    
    REPEAT
        SET result = CONCAT(result, i, ' ');
        SET i = i + 1;
    UNTIL i >= max_count END REPEAT;
    
    SELECT result;
END //

DELIMITER ;

CALL repeat_example(5);  -- 0 1 2 3 4

DELIMITER //

CREATE PROCEDURE loop_example(IN max_count INT)
BEGIN
    DECLARE i INT DEFAULT 0;
    DECLARE result VARCHAR(1000) DEFAULT '';
    
    my_loop: LOOP
        SET result = CONCAT(result, i, ' ');
        SET i = i + 1;
        
        IF i >= max_count THEN
            LEAVE my_loop;
        END IF;
    END LOOP my_loop;
    
    SELECT result;
END //

DELIMITER ;

CALL loop_example(5);  -- 0 1 2 3 4

DELIMITER //

CREATE PROCEDURE iterate_example(IN max_count INT)
BEGIN
    DECLARE i INT DEFAULT 0;
    DECLARE result VARCHAR(1000) DEFAULT '';
    
    my_loop: LOOP
        SET i = i + 1;
        
        -- 跳过偶数
        IF i % 2 = 0 THEN
            ITERATE my_loop;
        END IF;
        
        SET result = CONCAT(result, i, ' ');
        
        IF i >= max_count THEN
            LEAVE my_loop;
        END IF;
    END LOOP my_loop;
    
    SELECT result;
END //

DELIMITER ;

CALL iterate_example(10);  -- 1 3 5 7 9

DELIMITER //

CREATE PROCEDURE cursor_example()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE v_id INT;
    DECLARE v_name VARCHAR(50);
    DECLARE v_email VARCHAR(100);
    
    -- 声明游标
    DECLARE cur CURSOR FOR SELECT id, name, email FROM users;
    
    -- 声明结束处理程序
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    
    -- 创建临时表存储结果
    CREATE TEMPORARY TABLE IF NOT EXISTS temp_results (
        id INT,
        name VARCHAR(50),
        email VARCHAR(100)
    );
    
    -- 打开游标
    OPEN cur;
    
    -- 循环读取
    read_loop: LOOP
        FETCH cur INTO v_id, v_name, v_email;
        
        IF done THEN
            LEAVE read_loop;
        END IF;
        
        -- 处理数据
        INSERT INTO temp_results VALUES (v_id, UPPER(v_name), LOWER(v_email));
    END LOOP;
    
    -- 关闭游标
    CLOSE cur;
    
    -- 返回结果
    SELECT * FROM temp_results;
    DROP TEMPORARY TABLE temp_results;
END //

DELIMITER ;

DELIMITER //

CREATE PROCEDURE cursor_with_conditions()
BEGIN
    DECLARE v_id INT;
    DECLARE v_balance DECIMAL(10,2);
    DECLARE v_total DECIMAL(10,2) DEFAULT 0;
    DECLARE done INT DEFAULT FALSE;
    
    DECLARE cur CURSOR FOR SELECT id, balance FROM users;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    
    OPEN cur;
    
    process_loop: LOOP
        FETCH cur INTO v_id, v_balance;
        
        IF done THEN
            LEAVE process_loop;
        END IF;
        
        -- 累加余额
        SET v_total = v_total + COALESCE(v_balance, 0);
    END LOOP;
    
    CLOSE cur;
    
    SELECT v_total AS total_balance;
END //

DELIMITER ;

DELIMITER //

CREATE PROCEDURE handler_example()
BEGIN
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
    BEGIN
        GET DIAGNOSTICS CONDITION 1
        @sqlstate = RETURNED_SQLSTATE, 
        @errno = MYSQL_ERRNO, 
        @text = MESSAGE_TEXT;
        
        SELECT @sqlstate, @errno, @text AS error;
    END;
    
    -- 故意产生错误
    INSERT INTO non_existent_table VALUES (1);
    
    SELECT 'Continue after error' AS result;
END //

DELIMITER ;

DELIMITER //

CREATE PROCEDURE insert_user(IN p_name VARCHAR(50), IN p_email VARCHAR(100))
BEGIN
    DECLARE EXIT HANDLER FOR 1062  -- 重复键错误
    BEGIN
        SELECT 'Duplicate email' AS error;
    END;
    
    INSERT INTO users (name, email) VALUES (p_name, p_email);
    SELECT 'User inserted' AS result;
END //

DELIMITER ;

DELIMITER //

CREATE PROCEDURE transfer_money(
    IN from_user INT,
    IN to_user INT,
    IN amount DECIMAL(10,2)
)
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SELECT 'Transfer failed' AS result;
    END;
    
    START TRANSACTION;
    
    UPDATE users SET balance = balance - amount WHERE id = from_user;
    UPDATE users SET balance = balance + amount WHERE id = to_user;
    
    COMMIT;
    SELECT 'Transfer successful' AS result;
END //

DELIMITER ;

DELIMITER //

CREATE PROCEDURE dynamic_query(IN table_name VARCHAR(50))
BEGIN
    SET @sql = CONCAT('SELECT * FROM ', table_name);
    
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END //

DELIMITER ;

CALL dynamic_query('users');

DELIMITER //

CREATE PROCEDURE dynamic_where(
    IN table_name VARCHAR(50),
    IN column_name VARCHAR(50),
    IN value VARCHAR(100)
)
BEGIN
    SET @sql = CONCAT('SELECT * FROM ', table_name, ' WHERE ', column_name, ' = ?');
    SET @value = value;
    
    PREPARE stmt FROM @sql;
    EXECUTE stmt USING @value;
    DEALLOCATE PREPARE stmt;
END //

DELIMITER ;

CALL dynamic_where('users', 'name', 'John');

-- 查看存储过程列表
SHOW PROCEDURE STATUS WHERE Db = 'mydb';

-- 查看存储过程定义
SHOW CREATE PROCEDURE get_user_by_id;

-- 从information_schema查询
SELECT 
    routine_name,
    routine_type,
    data_type,
    routine_definition
FROM information_schema.routines
WHERE routine_schema = 'mydb';

-- MariaDB不支持ALTER PROCEDURE修改内容
-- 需要删除后重新创建

DROP PROCEDURE IF EXISTS get_user_by_id;

DELIMITER //
CREATE PROCEDURE get_user_by_id(IN user_id INT)
BEGIN
    SELECT * FROM users WHERE id = user_id;
END //
DELIMITER ;

-- 删除存储过程
DROP PROCEDURE get_user_by_id;

-- 如果存在则删除
DROP PROCEDURE IF EXISTS get_user_by_id;

DELIMITER //

CREATE FUNCTION get_user_name(user_id INT) 
RETURNS VARCHAR(50)
DETERMINISTIC
READS SQL DATA
BEGIN
    DECLARE v_name VARCHAR(50);
    SELECT name INTO v_name FROM users WHERE id = user_id;
    RETURN v_name;
END //

DELIMITER ;

-- 使用函数
SELECT get_user_name(1);
SELECT id, get_user_name(id) AS name FROM orders;

DELIMITER //

CREATE FUNCTION calculate_tax(amount DECIMAL(10,2), rate DECIMAL(5,4))
RETURNS DECIMAL(10,2)
DETERMINISTIC    -- 相同输入返回相同结果
NO SQL           -- 不包含SQL语句
BEGIN
    RETURN amount * rate;
END //

DELIMITER ;

-- 函数特性说明
-- DETERMINISTIC: 确定性函数
-- NOT DETERMINISTIC: 非确定性函数（默认）
-- CONTAINS SQL: 包含SQL语句
-- NO SQL: 不包含SQL语句
-- READS SQL DATA: 只读SQL
-- MODIFIES SQL DATA: 修改数据

-- 存储过程命名
-- sp_ 或 proc_ 前缀
CREATE PROCEDURE sp_get_user_by_id(...);
CREATE PROCEDURE proc_calculate_total(...);

-- 函数命名
-- fn_ 或 func_ 前缀
CREATE FUNCTION fn_get_user_name(...);

DELIMITER //

CREATE PROCEDURE safe_insert_user(
    IN p_name VARCHAR(50),
    IN p_email VARCHAR(100),
    OUT p_result VARCHAR(100)
)
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        GET DIAGNOSTICS CONDITION 1
        @text = MESSAGE_TEXT;
        SET p_result = CONCAT('Error: ', @text);
        ROLLBACK;
    END;
    
    START TRANSACTION;
    
    -- 验证输入
    IF p_name IS NULL OR p_name = '' THEN
        SET p_result = 'Error: Name is required';
        ROLLBACK;
    ELSE
        INSERT INTO users (name, email) VALUES (p_name, p_email);
        SET p_result = 'Success';
        COMMIT;
    END IF;
END //

DELIMITER ;