MySQL 从入门到精通 10：存储过程

2025年8月6日 16点热度 0人点赞 0条评论

存储过程是事先经过编译并存储在数据库中的一段 SQL 语句的集合，调用存储过程可以简化应用开发，减少数据在数据库和应用之间的传输，提高数据处理效率。

语法

创建

CREATE PROCEDURE 存储过程名称（[参数列表]）
BEGIN
    -- SQL语句
END;

示例：

create procedure p1()
begin
    select count(*) from tb_user;
end;

需要注意的是，在命令行客户端中输入 SQL 时，是以 ; 作为结束符的，所以直接粘贴上面的语句会报错：

mysql> create procedure p1()
    -> begin
    ->     select count(*) from tb_user;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 3

需要先将结束符改为其它符号，再粘贴创建存储过程的语句：

mysql> delimiter $$
mysql> create procedure p1()
    -> begin
    ->     select count(*) from tb_user;
    -> end$$
Query OK, 0 rows affected (0.01 sec)

创建成功后不要忘记将结束符改回分号：

mysql> delimiter ;
mysql> call p1();
+----------+
| count(*) |
+----------+
|       24 |
+----------+
1 row in set (0.00 sec)

调用

CALL 名称（[参数列表]）

示例：

call p1();

查看

-- 询指定数据库的存储过程及状态信息
SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA ='数据库名';
-- 查询某个存储过程的定义
SHOW CREATE PROCEDURE 存储过程名称;

示例：

select * from information_schema.ROUTINES where ROUTINE_SCHEMA='itcast';

删除

DROP PROCEDURE [IFEXISTS] 存储过程名称;

示例：

drop procedure if exists p1;

变量

系统变量

系统变量是 MySQL 服务器提供的，分为全局变量（GLOBAL）、会话变量（SESSION）。

查看系统变量：

-- 查看所有系统变量
SHOW [SESSION|GLOBAL] VARIABLES ;
-- 可以通过LIKE模糊匹配方式查找变量
SHOW [SESSION|GLOBAL］VARIABLES LIKE '...';
-- 查看指定变量的值
SELECT @@[SESSION.|GLOBAL.]系统变量名;

如果不指定session或global，默认显示的是会话变量。

示例：

show global variables;

mysql> show global variables like 'auto%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| auto_generate_certs      | ON    |
| auto_increment_increment | 1     |
| auto_increment_offset    | 1     |
| autocommit               | ON    |
| automatic_sp_privileges  | ON    |
+--------------------------+-------+

mysql> select @@global.autocommit;
+---------------------+
| @@global.autocommit |
+---------------------+
|                   1 |
+---------------------+

设置系统变量：

SET [SESSION|GLOBAL］ 系统变量名 = 值;
SET @@[SESSION.|GLOBAL.]系统变量名 = 值;

示例：

mysql> set @@session.autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@session.autocommit;
+----------------------+
| @@session.autocommit |
+----------------------+
|                    0 |
+----------------------+

自定义变量

用户自定义变量是用户根据需要自己定义的变量，不需要提前声明，直接使用@变量名即可，其作用域为当前会话。

赋值：

SET @var_name1 = expr [,@var_name2 = expr, ...];
SET @var_name1 := expr [,@var_name2 := expr, ...];

SELECT @var_name := expr [,@var_name := expr, ...];
SELECT 字段名 INTO @var_name FROM 表名;

读取：

SELECT @var_name;

示例：

mysql> set @myname = 'icexmoon';
Query OK, 0 rows affected (0.00 sec)

mysql> select @myname;
+----------+
| @myname  |
+----------+
| icexmoon |
+----------+

因为 SQL 中比较运算符也使用=，因此更推荐使用:=符号进行赋值：

mysql> set @msg := 'Hello';
Query OK, 0 rows affected (0.00 sec)

mysql> select @msg;
+-------+
| @msg  |
+-------+
| Hello |
+-------+

可以一次性赋值多个变量：

mysql> set @myage := 15,@myhobby := 'java',@mycity := '南京';
Query OK, 0 rows affected (0.00 sec)

mysql> select @myage,@myhobby,@mycity;
+--------+----------+---------+
| @myage | @myhobby | @mycity |
+--------+----------+---------+
|     15 | java     | 南京    |
+--------+----------+---------+

还可以通过 SELECT 语句完成赋值：

mysql> select @my_name := 'Tom';
+-------------------+
| @my_name := 'Tom' |
+-------------------+
| Tom               |
+-------------------+
1 row in set, 1 warning (0.00 sec)

mysql> select @my_name;
+----------+
| @my_name |
+----------+
| Tom      |
+----------+

将 SQL 查询结果赋值给变量：

mysql> select count(*) into @student_count from tb_user;
Query OK, 1 row affected (0.00 sec)

mysql> select @student_count;
+----------------+
| @student_count |
+----------------+
|             24 |
+----------------+

最后，需要注意的是，MySQL 中并不会强制要求在使用变量前对变量进行声明和赋值，如果使用一个没有被赋值过的变量，会返回 null：

mysql> select @abc;
+------------+
| @abc       |
+------------+
| NULL       |
+------------+

局部变量

局部变量是根据需要定义在局部生效的变量，访问之前需要使用 DECLARE 进行声明。可用作存储过程内的局部变量和输入参数，局部变量的作用域是 BEGIN ... END 块。

声明：

DECLARE 变量名 变量类型 [DEFAULT ...];

赋值：

SET 变量名 = 值;
SET 变量名 := 值;
SELECT 字段名 INTO 变量名 FROM 表名 …;

示例：

create procedure p1()
begin
    declare user_count int;
    select count(*) into user_count
    from tb_user;
    select user_count;
end;

调用存储过程：

call p1();

条件控制

语法：

IF 条件1 THEN
ELSEIF 条件2 THEN  --可选
ELSE			   --可选
END IF;

示例：

create procedure p2()
begin
    declare score_level varchar(10);
    declare score int default 79;
    if score >= 85 then
        set score_level := '优秀';
    elseif score >= 60 then
        set score_level := '及格';
    else
        set score_level := '不及格';
    end if;
    select score_level;
end;

参数

类型	含义	备注
IN·	输入参数	默认
OUT	输出参数（返回值）
INOUT	既可以作为输入参数，也可以作为输出参数

语法：

CREATE PROCEDURE 存储过程名称([IN/OUT/INOUT 参数名 参数类型])
BEGIN
-- SQL语句
END;

使用参数改造之前的存储过程：

create procedure p3(in score int, out score_level varchar(10))
begin
    if score >= 85 then
        set score_level := '优秀';
    elseif score >= 60 then
        set score_level := '及格';
    else
        set score_level := '不及格';
    end if;
end;

现在该存储过程可以读取输入参数，并将结果通过输出参数返回。

调用：

call p3(85, @score_level);
select @score_level;

可以利用自定义变量接收存储过程的输出参数，并通过 SELECT 语句打印结果。

存储过程的参数可以即是输入参数也是输出参数：

create procedure p4(inout price double)
begin
    set price := price * 0.75;
end;

这是一个对价格打折的存储过程，传入的价格打折后进行返回，它既是输入参数也是输出参数。

调用：

set @price := 100;
call p4(@price);
select @price;

CASE

语法：

CASE case_value
    WHEN when_value1 THEN statement _list1
    [WHEN when_value2 THEN statement _list 2] ...
    [ELSE statement_list ]
END CASE;

CASE
    WHEN search_condition1 THEN statement_list1
    [WHEN search_condition2 THEN statement_list2] ...
    [ELSE statement_list] 
END CASE;

第一种写法类似于编程语言中的 switch...case...，第二种写法可以看做是条件控制语句 if...elseif... 的变种。

示例，将之前用条件控制语句编写的存储过程改写为使用 CASE 语句：

create procedure p5(in score int, out score_level varchar(10))
begin
    case
        when score >= 85 then
            set score_level := '优秀';
        when score >= 60 then
            set score_level := '及格';
        else
            set score_level := '不及格';
    end case;
end;

执行：

call p5(75, @score_level);
select @score_level;

循环

while

WHILE 条件 DO
    SQL逻辑...
END WHILE;

示例，计算从 1 累加到 n 的结果：

create procedure p6(in max int, out sum int)
begin
    declare i int default 1;
    set sum := 0;
    while i<=max do
        set sum := sum+i;
        set i := i+1;
    end while;
end;

调用：

call p6(6, @result);
select @result;

repeat

REPEAT
    SQL逻辑
UNTIL 条件
END REPEAT;

当满足 UNTIL 语句的条件时，循环退出。

类似其它编程语句在循环体中使用 if... break ...

用 repeat 语法重写之前的存储过程：

create procedure p7(in max int, out sum int)
begin
    declare i int default 1;
    set sum := 0;
    repeat
        set sum := sum+i;
        set i := i+1;
    until i>max
    end repeat;
end;

注意，until 语句后不能有;。

loop

LOOP 实现简单循环，如果不在 SQL 中增加退出循环的条件，是死循环。可以搭配使用：

LEAVE：退出循环
ITERATE：跳过当前循环的剩余部分，进入下次循环

[begin_label:] LOOP
	SQL逻辑...
END LOOP [end_label];

LEAVE label;
ITERATE Label;

示例，用 loop 改写之前的存储过程：

create procedure p8(in max int, out sum int)
begin
    declare i int default 1;
    set sum := 0;
    sum_loop:loop
        set sum := sum+i;
        set i := i+1;
        if i>max then
            leave sum_loop;
        end if;
    end loop sum_loop;
end;

调用：

call p8(10, @result);
select @result;

如果只累加偶数：

create procedure p9(in max int, out sum int)
begin
    declare i int default 1;
    set sum := 0;
    sum_loop:loop
        if i%2 = 1 then
            set i := i+1;
            iterate sum_loop;
        end if;
        set sum := sum+i;
        set i := i+1;
        if i>max then
            leave sum_loop;
        end if;
    end loop sum_loop;
end;

游标

前面介绍的，都是将查询到的一个 cell 中的值赋值给变量，如果查询到的是一个结果集，就需要使用游标遍历数据。

语法：

-- 声明游标
DECLARE 游标名称 CURSOR FOR 查询语句;
-- 打开游标
OPEN 游标名称;
-- 获取游标记录
FETCH 游标名称 INTO 变量［ 变量 ］;
-- 关闭游标
CLOSE 游标名称；

示例，将 tb_user 表中年龄小于等于某个值的用户信息保存到另一张表：

create procedure p10(in uage int)
begin
    declare v_id int;
    declare v_name varchar(50);
    declare v_age tinyint unsigned;
    declare v_profession varchar(11);
    declare c_user cursor for select id,name,age,profession from tb_user where age<=uage;
    drop table if exists tb_user_ext;
    create table if not exists tb_user_ext(
        id int unsigned,
        name varchar(50),
        age tinyint unsigned,
        profession varchar(11)
    ) comment '年龄小于某个值的用户';
    open c_user;
    while true do
        fetch c_user into v_id,v_name,v_age,v_profession;
        insert into tb_user_ext values (v_id,v_name,v_age,v_profession);
    end while;
    close c_user;
end;

需要注意的是，普通变量声明必须放在游标声明之前，否则会报错。

这个存储过程可以正常创建，但执行的时候会报错：

[02000][1329] No data - zero rows fetched, selected, or processed

原因是在游标读取完最后一条记录后没有正常退出循环，可以通过条件处理程序解决这个问题。

条件处理程序

语法：

DECLARE handler_action HANDLER FOR condition_value [, condition_value] ...  statement ;

handler_action
	CONTINUE:继续执行当前程序
	EXIT终止执行当前程序
condition_value
    SQLSTATE sqlstate_value:状态码，如02000
    SQLWARNING：所有以O1开头的SQLSTATE代码的简写
    NOTFOUND：所有以02开头的SQLSTATE代码的简写
    SQLEXCEPTION：所有没有被 SQLWARNING 或 NOTFOUND 捕获的 SQLSTATE 代码的简写

使用条件处理程序解决上面的问题：

create procedure p10(in uage int)
begin
    declare v_id int;
    declare v_name varchar(50);
    declare v_age tinyint unsigned;
    declare v_profession varchar(11);
    declare c_user cursor for select id,name,age,profession from tb_user where age<=uage;
    declare exit handler for sqlstate '02000' close c_user;
    drop table if exists tb_user_ext;
    create table if not exists tb_user_ext(
        id int unsigned,
        name varchar(50),
        age tinyint unsigned,
        profession varchar(11)
    ) comment '年龄小于某个值的用户';
    open c_user;
    while true do
        fetch c_user into v_id,v_name,v_age,v_profession;
        insert into tb_user_ext values (v_id,v_name,v_age,v_profession);
    end while;
    close c_user;
end;

之前的报错信息：

[02000][1329] No data - zero rows fetched, selected, or processed

头部的[02000]就是 SQL 的状态码，所以这里的条件处理程序：

declare exit handler for sqlstate '02000' close c_user;

意味着在存储过程出现 02000 状态码时执行退出操作，并且在退出前关闭游标（close c_user）。

除了指定具体的状态码，还可以使用：

SQLWARNING：所有以 01 开头的状态码
NOT FOUND：所有以 02 开头的状态码
SQLEXCEPTION：所有其他的状态码

所以示例中的条件处理程序也可以定义为：

declare exit handler for not found close c_user;

可以通过官方文档查看 MySQL 的状态码定义：

存储函数

存储函数是有返回值的存储过程，存储函数的参数值只能是 IN 类型的。

语法：

CREATE FUNCTION 存储函数名称（[参数列表])
RETURNS type [characteristlic...]
BECIN
    -- SQL语句
    RETURN ...
FND;

characteristic 说明：
    DETERMINISTIC：相同的输入参数总是产生相同的结果(幂等)
    NOSQL：不包含SQL语句。
    RFAD5SQLDATA：只包含读取数据的语句，不包含写入数据的语句。

示例，创建一个从 1 累加到 n 的存储函数：

create function f_sigma(n int unsigned)
returns int unsigned DETERMINISTIC
begin
    declare i int unsigned default 1;
    declare result int unsigned default 0;
    repeat
        set result:=result+i;
        set i:=i+1;
    until i>n end repeat;
    return result;
end;

执行函数：