Mysql存储过程(附案例)

​

存储过程概述

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

存储过程思想上很简单，就是数据库 SQL 语言层面的代码封装与重用。

优点：

1. 减少网络传输：在数据库端执行，降低应用服务器与数据库间的数据交互。

2. 性能提升：预编译减少了重复解析和优化，提高执行效率

3. 简化开发：封装复杂逻辑，减少应用层代码量，便于调用

1、基本语法

• 创建：

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

举例：

-- 创建
CREATE PROCEDURE p1()
BEGIN

    SELECT COUNT(*) FROM student;

END

运行结果：
可以看到在functions下多了一个p1函数

• 调用：

CALL 存储名称 ([参数]);

​举例：

CALL p1();

运行结果：

• 查看

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

举例：

-- 查询指定数据库的存储过程及状态信息
SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = 'school_db';

-- 查询某个存储过程的定义
SHOW CREATE PROCEDURE p1;

运行结果：

分别是两条语句的运行结果：

• 删除

DROP PROCEDURE [IF EXISTS] 存储过程名称;

举例：
这样就删除成功了：

DROP PROCEDURE p1;

2、变量

①、系统变量

系统变量由Mysql服务器提供，不由用户定义，属于服务器层面，分为全局变量与会话变量。

全局变量：无论开多少个会话，变量值都是一样的，
会话变量：只在当前会话生效的变量。

像在navicat中，我们点击创建这三个query，就是三个不同的会话

查看语句：

-- 查看会话变量
SHOW SESSION VARIABLES;
-- 查看全局变量
SHOW GLOBAL VARIABLES;

-- 模糊查询
SHOW SESSION VARIABLES like 'auto%';
SHOW GLOBAL VARIABLES like 'auto%';

-- 直接选择具体的变量
SELECT @@global.autocommit;

查询结果：

变量可以看到有很多。

模糊查询结果：

具体查询结果：

更改语句：

-- 设置会话变量
SET SESSION autocommit = 0;
-- 设置全局变量
SET GLOBAL autocommit = 0;

②、用户自定义变量

用户定义变量是用户根据需要自己定义的变量，用户变量不用提前声明，在用的时候直接用“@变量名”使用就可以。其作用域为当前连接。

使用方式：

-- 设置变量

SET @user_name := 'zhangsan';

select COUNT(*) into @num from student;

-- 使用变量

SELECT @num, @user_name;

注意点：赋值时使用 := 来赋值，然后赋值的话，也可以将查询的结果赋值给一个变量，如第二条查询语句。最后如果要查看自定义变量则使用最后一条的语法查询，查询结果如下：

③、局部变量

局部变量是根据需要定义的在局部生效的变量，访问之前，需要DECLARE声明。可用作存储过程中的局部变量和输入参数，局部变量的范围是在其声明的BEGIN … END块

使用方式：

CREATE PROCEDURE p2()
BEGIN
    -- 局部变量定义，前面为变量名，后面为变量类型，如int、varchar等。
    DECLARE stu_count int;
    -- 对变量赋值
    select COUNT(*) INTO stu_count from student;
    -- 查询变量
    select stu_count;
END

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

调用结果：

3、流程控制语句

这部分其实和编程语言的流程控制基本相同，分支、循环，传参等，我们直接实战来演示。

①、if语句

根据分数判断是否合格。

-- if
CREATE PROCEDURE p3()
BEGIN
    DECLARE score INT DEFAULT 58;
    DECLARE result VARCHAR(10);
    -- if语句使用
    IF score >= 85 THEN
      SET result = '优秀';
    ELSEIF score >= 60 THEN
      SET result = '合格';
    ELSE
      SET result = '不合格';
    end IF;
    -- 查询结果
    SELECT result;
END
-- 调用流程
CALL p3();

运行结果：

②、参数

只需要知道三个声名参数的方法，分别是in、out、inout。

in用来声名输入参数，out用来声名输出参数，输出参数一般由一个变量来接收。

-- 参数
-- in表示声名输入参数，out表示声名返回结果
CREATE PROCEDURE p4(in score INT, out result VARCHAR(10))
BEGIN
    -- if语句使用
    IF score >= 85 THEN
      SET result = '优秀';
    ELSEIF score >= 60 THEN
      SET result = '合格';
    ELSE
      SET result = '不合格';
    end IF;
END
-- 调用过程
CALL p4(58, @result);

SELECT @result;

查询结果：

inout表明输入和输出都是同一个变量。

-- 将数字换成百分制的。
CREATE PROCEDURE p5(inout score DOUBLE)
BEGIN
   set score := score * 0.01
END;

set @SCORE = 78
-- 调用过程
CALL p5(@SCORE);

SELECT @SCORE;

运行结果：

③、case语句

根据月份判断第几季度。

-- case语句
CREATE PROCEDURE p6(in month INT)
BEGIN
    declare result VARCHAR(10);
   case
      when month >= 1 and month <= 3 then
        set result := '第一季度';
      when month >= 4 and month <= 6 then
        set result := '第二季度';
      when month >= 7 and month <= 9 then
        set result := '第三季度';
      when month >= 10 and month <= 12 then
        set result := '第四季度';
      else 
        set result := '非法参数';
    end case;
   
   select result;
END;


CALL p6(7);

运行结果：

④、while语句

求n到1的累加值。

CREATE PROCEDURE p7(in n INT)
BEGIN
    declare total INT DEFAULT 0;
    
    while n>0 do
        set total := total + n;
        set n := n - 1;
    end while;
    
    select total;
END;


CALL p7(10);

运行结果：

⑤、repeat语句

repeat是有条件的循环退出语句，类似c语言的do while语句

CREATE PROCEDURE p8(in n INT)
BEGIN
    declare total INT DEFAULT 0;
    
    repeat
        set total := total + n;
        set n := n - 1;
    until n <= 0
    end repeat;
    
    select total;
END;


CALL p8(10);

运行结果：

⑥、loop语句

里面注意需要给loop代码命个名，退出循环需要指定loop的名称。 leave等同于c语言中的break, iterate 效果等同于c语言中的continue

需求：从n 到 1中所有偶数相加的和

CREATE PROCEDURE p9(in n INT)
BEGIN
    declare total INT DEFAULT 0;
    
    sum:loop
        
        if n<=0 then
            leave sum;
        end if;
        
        if n%2=1 then
          set n := n - 1;
          iterate sum;
        end if;
        
        set total := total + n;
        set n := n - 1;
    end loop sum;
    
    select total;
END;

CALL p9(10);

运行结果：

⑦、cursor游标

游标 (CURSOR) 是用来存储查询结果集的游标类型，在存储过程和函数中可以使用游标来循环处理查询结果集中的每行记录。

游标可以存储sql查询的结果集合，而之前的参数只能传单行单列的数据。

声明游标：

DECLARE 游标名称 CURSOR FOR 查询语句;

打开游标：

OPEN 游标名称;

获取游标记录：

FETCH 游标名称 INTO 变量[变量];

关闭游标：

CLOSE 游标名称;

需求：将查询到的数据，存储到新表tb_user_pro中，游标语法如下：

CREATE PROCEDURE p10(in uid INT)
BEGIN
    -- 定义游标与变量
    declare u_name varchar(50);
    declare u_gender varchar(50);
    declare u_cursor cursor for select stu_name,gender from student where stu_id <= uid;
    
    
    -- 创建测试表
    create table if not exists tb_user_pro(
        id int PRIMARY key auto_increment,
        name2 VARCHAR(50),
        gender VARCHAR(50)
    );
    
    -- 开启游标
    open u_cursor; 
    
    WHile true do
        fetch u_cursor into u_name, u_gender; -- 获取游标中的数据并赋值给变量
        insert into tb_user_pro(name2, gender) VALUES (u_name, u_gender); -- 执行插入语句
    end while;
    
    -- 关闭游标
    close u_cursor;
END;

CALL p10(3);

运行结果：

可以看到，我们成功完成了功能，但是在执行的时候，有一些问题，在while true的时候，我们没有设置跳出循环的逻辑，导致结果正确，但执行会报错：

⑧、handler

条件处理程序 (Handler) 可以用来定义在流程控制结构过程中遇到问题时相应的处理步骤。具体语法为：

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

handler_action
CONTINUE: 继续执行当前程序
EXIT: 终止执行当前程序

condition_value
SQLSTATE sqlstate_value: 状态码，如 02000
SQLWARNING: 所有以01开头的SQLSTATE代码的警告
NOT FOUND: 所有以02开头的SQLSTATE代码的警告

SQLEXCEPTION: 所有没有被SQLWARNING 或 NOT FOUND捕获的SQLSTATE代码的警告

这里我们直接基于上一个案例来解释语法怎么用

我们在上一个案例的PROCEDURE中定义一个条件处理程序，在报错时就会执行这个程序：

-- exit表示退出程序，
-- SQLSTATE '02000' 等价于 not found 
-- 该程序最后执行的语句为：close u_cursor
declare exit handler for SQLSTATE '02000' close u_cursor;

最后运行的时候，发现程序就不再报错了，功能也正常了。

4、存储函数

存储函数是否有可能返回值的存储过程，存储函数的参数只能是IN类型的。具体语法如下：

CREATE FUNCTION 存储函数名称( [参数列表] )
RETURNS type [characteristic …]
BEGIN
    -- SQL语句
    RETURN …;
END;

characteristic说明：

DETERMINISTIC: 相同的输入参数总是产生相同的结果
NO SQL: 不包含SQL语句。
READS SQL DATA: 包含读取数据的语句，但不包含写入数据的语句。

举例说明，
功能：实现从n到1的累加：

create function fun1(n INT)
-- 必须指定返回类型 和 characteristic
returns int DETERMINISTIC

BEGIN
    declare total INT default 0;
    
    while n > 0 do
      set total := total + n;
      set n := n - 1;
    end while;
    
    return total;
END;

select fun1(100)

运行结果：