MySQL 存储过程详解与实践指南

1. 存储过程基础

1.1 创建简单存储过程

# 基础存储过程示例
DROP PROCEDURE IF EXISTS test;    # 如果存在同名存储过程则删除
DELIMITER //                      # 定义新的语句结束符
CREATE PROCEDURE test()           # 创建存储过程
BEGIN                             # 存储过程主体开始
    SELECT 'Hello World!';        # 执行SQL语句
END//                             # 存储过程结束

DELIMITER ;                       # 恢复默认语句结束符
CALL test();                      # 调用存储过程

2. 变量声明与使用

2.1 变量声明与赋值

# 变量声明与使用示例
DROP PROCEDURE IF EXISTS test;
DELIMITER //
CREATE PROCEDURE test()
BEGIN
    DECLARE aa VARCHAR(20);        # 声明字符串变量
    DECLARE bb INT DEFAULT 0;      # 声明整型变量并设置默认值
    SET aa = '张三';               # 变量赋值
    SELECT aa;                     # 输出变量值
    SELECT bb;                     # 输出变量值
END//
DELIMITER ;
CALL test();

3. 流程控制语句

3.1 IF条件判断

# 条件判断示例
DROP PROCEDURE IF EXISTS test_2;
DELIMITER //
CREATE PROCEDURE test_2()
BEGIN 
    DECLARE aa INT;
    DECLARE bb INT;
    SET aa = 20;
    SET bb = 20;
    IF aa > bb THEN                # 条件判断
        SELECT aa;
    ELSEIF aa < bb THEN            # 多重条件判断
        SELECT bb;
    ELSE                           # 默认情况
        SELECT aa+bb;
    END IF;                        # 结束条件判断
END//
DELIMITER ;
CALL test_2();

4. 循环控制

4.1 LOOP循环

# LOOP循环示例：计算1-100的和
DROP PROCEDURE IF EXISTS test_3;
DELIMITER //
CREATE PROCEDURE test_3()
BEGIN 
    DECLARE aa INT DEFAULT 0;      # 计数器变量
    DECLARE sums INT DEFAULT 0;    # 累加和变量
    loopName:LOOP                  # 定义循环
        SET aa = aa + 1;           # 计数器自增
        SET sums = sums + aa;      # 累加计算
        IF aa >= 100 THEN          # 循环终止条件
            LEAVE loopName;        # 退出循环
        END IF;
    END LOOP;                      # 结束循环
    SELECT sums;                   # 输出结果
END//
DELIMITER ;
CALL test_3();

5. 查询结果赋值

5.1 单行结果赋值

# 查询结果赋值示例
DROP TABLE IF EXISTS test_tbl;
CREATE TABLE test_tbl (
    name VARCHAR(20), 
    status INT(2)
);
INSERT INTO test_tbl VALUES
('abc', 1),
('edf', 2),
('xyz', 3);

DROP PROCEDURE IF EXISTS pro_test_3;
DELIMITER //
CREATE PROCEDURE pro_test_3()
BEGIN
    -- 方式1：使用INTO
    DECLARE cnt INT DEFAULT 0;
    SELECT COUNT(*) INTO cnt FROM test_tbl;
    SELECT cnt;

    -- 方式2：使用SET
    SET @cnt = (SELECT COUNT(*) FROM test_tbl);
    SELECT @cnt;

    -- 方式3：直接INTO用户变量
    SELECT COUNT(*) INTO @cnt1 FROM test_tbl;
    SELECT @cnt1;

    -- 多列赋值
    SELECT MAX(status), AVG(status) INTO @max, @avg FROM test_tbl;
    SELECT @max, @avg;
END//
DELIMITER ;

CALL pro_test_3();

6. 批量数据处理

6.1 批量插入数据

# 批量插入示例
DROP PROCEDURE IF EXISTS test;
DELIMITER //
CREATE PROCEDURE test()
BEGIN
    DECLARE i INT DEFAULT 1;
    WHILE i < 20 DO
        INSERT INTO `t_test_auto`(`id`, `name`,`age`) 
        VALUES (CONCAT('1',i), 'haha', ROUND((7+RAND()*6),2));
        SET i = i + 1;
    END WHILE;
END//
DELIMITER ;
CALL test();

7. 高级循环示例

7.1 循环更新数据

# 循环更新示例
DROP PROCEDURE IF EXISTS cunchu1;
DELIMITER //
CREATE PROCEDURE cunchu1(a INT)
BEGIN
    DECLARE count INT DEFAULT 0;
    DECLARE i INT DEFAULT 1;
    loop_name:LOOP
        IF i > a THEN
            LEAVE loop_name;
        END IF;
        UPDATE way_bridge_info_ext 
        SET uuid = REPLACE(UUID(),'-','') 
        WHERE uuid = i AND CREATE_TIME = '2018-4-26 15:49:40';
        SET count = i + 1;
        SET i = i + 1;
    END LOOP;
    SELECT count;
END//
DELIMITER ;

CALL cunchu1(29);
DROP PROCEDURE IF EXISTS cunchu1;

7.2 数值累加示例

# 数值累加示例
DROP PROCEDURE IF EXISTS sum1;
DELIMITER //
CREATE PROCEDURE sum1(a INT)
BEGIN
    DECLARE sum INT DEFAULT 0;
    DECLARE i INT DEFAULT 1;
    WHILE i <= a DO
        SET sum = sum + i;
        SET i = i + 1;
    END WHILE;
    SELECT sum;
END//
DELIMITER ;

CALL sum1(100);
DROP PROCEDURE IF EXISTS sum1;

8. 综合示例

8.1 数据操作综合示例

# 综合操作示例
DROP PROCEDURE IF EXISTS test;
DELIMITER //
CREATE PROCEDURE test()
BEGIN
    DECLARE sum INT DEFAULT 0;
    DECLARE i INT DEFAULT 1;
    DECLARE nums INT DEFAULT 2;

    -- 获取记录数
    SELECT COUNT(*) INTO @nums FROM test t;
    SELECT @nums;

    -- 循环处理数据
    WHILE i <= @nums DO
        UPDATE test SET a = i, b = 1 + i WHERE c = i;
        INSERT INTO `test`(`a`,`b`,`c`) VALUES (CONCAT('1',i),'2',i);
        SET sum = sum + i;
        SET i = i + 1;
    END WHILE;

    SELECT sum;  # 输出结果
END//
DELIMITER ;